• Combine Friend/Ignore lists

Posted: **Tue Dec 29, 2009 6:40 pm**

LE/Scat,

I'd like to propose making a single table for friends/ignore (character_social?) that holds both friends and ignore lists. Current tables look like this:

Code: Select all

CREATE TABLE `character_friendlist` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `char_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `name` VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `CharIDNameIdx` (`char_id`,`name`)
) ENGINE=MYISAM

Code: Select all

CREATE TABLE `character_ignorelist` (               
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,    
  `char_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',  
  `name` VARCHAR(64) NOT NULL DEFAULT '',           
  PRIMARY KEY  (`id`),                              
  UNIQUE KEY `CharNameIdx` (`char_id`,`name`)       
) ENGINE=INNODB

Combine the two into a single table, named accordingly. Engine InnoDB. Add a column called "flag" or "option" or something, where 0 = friend, 1 = ignore.

New table (just a suggestion)

Code: Select all

CREATE TABLE `character_social` (               
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,    
  `char_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',  
  `name` VARCHAR(64) NOT NULL DEFAULT '',           
  `type` TINYINT(1) NOT NULL DEFAULT '0',           
  PRIMARY KEY  (`id`),                              
  UNIQUE KEY `CharNameIdx` (`char_id`,`name`)       
) ENGINE=INNODB

I am ok with leaving a varchar(64) for name, because this prevents anyone from deleting and re-creating an annoying character who can then start chatting at the ignorer again

(you know how I prefer int's to varchar's) but yeah, i can see name being the best approach here.

If you do not see any issues with doing this, please go for it. Just trying to minimize the tables we're creating. We're out of control!

though imo, there can never be too many tables

Posted: **Tue Dec 29, 2009 11:41 pm**

Should be an easy change. Once we get our DB stuff worked or for guilds (looked at IRC), I can make the change so LE can work on more uber stuff

Posted: **Wed Dec 30, 2009 7:58 pm**

What about people who are neither friend, nor ignored? If everyone starts out on your friend list, all during the game you'll get spammed with "Friend has logged in." If everyone starts out on your ignore list, then you can't chat to anyone. We need some neutral ground on this one.

Posted: **Wed Dec 30, 2009 11:04 pm**

No one starts out on any list. If in game you type /friend blah, they'll get put into the friends list. If you type /ignore blah they'd get put into the ignore list. The table would simply keep an additional field (I think) to determine which list you are on.

Posted: **Thu Dec 31, 2009 4:20 am**

Oh I see...sometimes I can be pretty thick...but yes, anything that makes complex things more simple is definitely better

Posted: **Fri Jan 01, 2010 9:10 pm**

Hey John, that is a great idea, but I would suggest making the type an ENUM ('FRIEND', 'IGNORED'), that way it is easy for people modifying the database to understand what the type values are for. I have tried to do this with the newer tables to avoid having to constantly look in the code to check with value goes in the db for a particular type.

Posted: **Sat Jan 02, 2010 2:46 pm**

Good idea. I'll see if Scat can tweak that code once he's got a free moment - if he survived New Year's Eve

Posted: **Sat Jan 02, 2010 2:49 pm**

Yup survived!

Posted: **Mon Jan 11, 2010 9:22 am**

The new table for combining Friends/Ignore is as follows:

Code: Select all

CREATE TABLE `character_social` (               
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,   
  `char_id` INT(10) UNSIGNED NOT NULL DEFAULT '0', 
  `name` VARCHAR(64) NOT NULL DEFAULT '',           
  `type` ENUM('FRIEND','IGNORE') NOT NULL DEFAULT 'FRIEND',           
  PRIMARY KEY  (`id`),                             
  UNIQUE KEY `CharNameIdx` (`char_id`,`name`)       
) ENGINE=INNODB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

...and is minor version 100, but hold off on bumping the version in code just yet. I have about 50 more updates to apply

Posted: **Mon Jan 11, 2010 11:40 am**

Finished.

Posted: **Mon Jan 11, 2010 2:47 pm**

Good work Scatman. It's Miller Time!

Combine Friend/Ignore lists

Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists

Re: Combine Friend/Ignore lists