Combine Friend/Ignore lists

[John Adams]

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

[Scatman]

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

[Arremis]

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.

[Scatman]

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.

[Arremis]

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

[LethalEncounter]

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.

[John Adams]

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

[Scatman]

Yup survived!

[John Adams]

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

[Scatman]

Finished.

[Arremis]

Good work Scatman. It's Miller Time!