Database cleanup

[eevee]

• Lots of columns need to be renamed.
• pokemoves -> pokemon_moves
• Migrate non-English names out of the main tables, and go with the assumption that they will not be used all that much: pull them out of FuzzyMatches, create a foreign_names table with all of them (id, type, language, name), and search that as a last resort? ...or skip right to it when the incoming entry has non-ASCII characters (i.e. utf-8). Also import French/German/etc names I suppose.
• Figure out how to make UTF-8 VARCHARs work with Catalyst or whatever's breaking.
• Anything else?

[eevee]

• Remove any unused columns, e.g. the evchains.baby columns.
• Rewrite schema files to have consistent formatting.

[eevee]

Schema file cleanup done by r350.

[eevee]

• Every table should be InnoDB with UTF-8 if at all possible.

[eevee]

• Do something with 'kind'. Something more flexible that makes moves actually searchable would be awesome.

[eevee]

• Type effects should possibly be factored out.

[eevee]

To update existing database to what I am doing in PART ONE:

ALTER TABLE abilities CHANGE COLUMN `gameblurb` `blurb_dp` TINYTEXT NOT NULL,
 CHANGE COLUMN `effect` `description` TINYTEXT NOT NULL;
ALTER TABLE contestmoves RENAME TO contest_effects,
 CHANGE COLUMN `description` `blurb_rusa` TINYTEXT NOT NULL,
 CHANGE COLUMN `effect` `description` TINYTEXT NOT NULL;
ALTER TABLE edits
 CHANGE COLUMN `postid` `post_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `userid` `user_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `oldmessage` `old_content` TEXT NOT NULL,
 DROP INDEX `POSTID`,
 ADD INDEX `POSTID` USING BTREE(`post_id`);
ALTER TABLE errorlog RENAME TO error_log,
 CHANGE COLUMN `userid` `user_id` INTEGER UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE evchains RENAME TO evo_chains,
 CHANGE COLUMN `base` `base_pokemon_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `growth` `growth_rate` ENUM('slow','medium','fast','poly','superfast','superslow') DEFAULT NULL,
 DROP COLUMN `chain`,
 DROP COLUMN `babygs`,
 DROP COLUMN `babyc`,
 DROP COLUMN `babyrusa`;
ALTER TABLE flavortext RENAME TO flavor_text,
 CHANGE COLUMN `pokeid` `pokemon_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`pokemon_id`, `generation`);
ALTER TABLE forums CHANGE COLUMN `lastpostid` `last_post_id` INTEGER UNSIGNED DEFAULT NULL,
 CHANGE COLUMN `threadct` `thread_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `postct` `post_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `blurb` `description` VARCHAR(255) NOT NULL
, ROW_FORMAT = DYNAMIC;
ALTER TABLE grouppermissions RENAME TO group_permissions,
 CHANGE COLUMN `groupid` `group_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`group_id`, `permission`, `scope`);
ALTER TABLE items CHANGE COLUMN `dpblurb` `blurb_dp` VARCHAR(128) NOT NULL
, ROW_FORMAT = DYNAMIC;
ALTER TABLE machines CHANGE COLUMN `moveid` `move_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE move_effects CHANGE COLUMN `blurb` `short_description` VARCHAR(128) NOT NULL;
ALTER TABLE moves CHANGE COLUMN `acc` `accuracy` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `gameblurb` `blurb_rusa` TINYTEXT NOT NULL,
 CHANGE COLUMN `dpblurb` `blurb_dp` TINYTEXT NOT NULL,
 CHANGE COLUMN `contype` `contest_type` ENUM('cute','tough','smart','cool','beauty') NOT NULL DEFAULT 'cute',
 CHANGE COLUMN `coneffect` `contest_effect_id` TINYINT(4) NOT NULL DEFAULT 0,
 CHANGE COLUMN `newflags` `XXX_new_flags` CHAR(13) DEFAULT NULL;
DROP TABLE permissions;
ALTER TABLE pokemon CHANGE COLUMN `evid` `evo_chain_id` SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `evparent` `evo_parent` SMALLINT(3) NOT NULL,
 CHANGE COLUMN `evmethod` `evo_method` ENUM('level','levelmale','levelfemale','levelarea','trade','item','itemmale','itemfemale','holdday','holdnight','move','happiness','happinessday','happinessnight','beauty','divineintervention','level+attack','level+defense','level+equal','dnadigivolve','none') DEFAULT NULL,
 CHANGE COLUMN `evparam` `evo_param` VARCHAR(32) NOT NULL,
 CHANGE COLUMN `oldgs` `gameshark_rby` TINYINT(3) UNSIGNED DEFAULT NULL,
 CHANGE COLUMN `caprate` `capture_rate` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `baseexp` `base_exp` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `gender` `gender_rate` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `happiness` `base_happiness` TINYINT(3) UNSIGNED NOT NULL,
 DROP COLUMN `root`,
 DROP COLUMN `eventred`,
 DROP COLUMN `eventblue`,
 CHANGE COLUMN `real_id` `real_pokemon_id` INTEGER UNSIGNED NOT NULL,
 DROP INDEX `REALID`,
 ADD INDEX `REALID` USING BTREE(`real_pokemon_id`);
ALTER TABLE pokemon_abilities CHANGE COLUMN `pokeid` `pokemon_id` INTEGER UNSIGNED NOT NULL,
 CHANGE COLUMN `abilityid` `ability_id` INTEGER UNSIGNED NOT NULL,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`pokemon_id`, `ability_id`, `slot`),
 DROP INDEX `pokeid`,
 ADD INDEX `pokeid` USING BTREE(`pokemon_id`),
 DROP INDEX `abilityid`,
 ADD INDEX `abilityid` USING BTREE(`ability_id`);
ALTER TABLE pokemon_breeds CHANGE COLUMN `pokeid` `pokemon_id` INTEGER UNSIGNED NOT NULL,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`pokemon_id`, `breed`),
 DROP INDEX `pokeid`,
 ADD INDEX `pokeid` USING BTREE(`pokemon_id`);
ALTER TABLE pokemoves RENAME TO pokemon_moves,
 CHANGE COLUMN `pokeid` `pokemon_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `moveid` `move_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `version` `versions` SET('rb','y','gs','c','rusa','e','frlg','dp') NOT NULL,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`pokemon_id`, `move_id`, `level`, `versions`, `method`),
 DROP INDEX `POKEID`,
 ADD INDEX `POKEID` USING BTREE(`pokemon_id`),
 DROP INDEX `MOVEID`,
 ADD INDEX `MOVEID` USING BTREE(`move_id`),
 DROP INDEX `VERSION`,
 ADD INDEX `VERSION` USING BTREE(`versions`);
ALTER TABLE posts CHANGE COLUMN `threadid` `thread_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `userid` `user_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `message` `content` TEXT NOT NULL,
 CHANGE COLUMN `lasteditid` `last_edit_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 DROP INDEX `THREAD`,
 ADD INDEX `THREAD` USING BTREE(`thread_id`),
 DROP INDEX `USER`,
 ADD INDEX `USER` USING BTREE(`user_id`)
, ROW_FORMAT = FIXED;
ALTER TABLE sessions CHANGE COLUMN `userid` `user_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `expires` `time_expires` INTEGER UNSIGNED DEFAULT NULL,
 ADD PRIMARY KEY  USING BTREE(`id`)
, DROP INDEX `sessid`
, DROP INDEX `userid`,
 ADD INDEX `USER_ID` USING BTREE(`user_id`);
ALTER TABLE shoutbox CHANGE COLUMN `userid` `user_id` INTEGER UNSIGNED DEFAULT NULL,
 CHANGE COLUMN `message` `content` TEXT NOT NULL;
ALTER TABLE threads CHANGE COLUMN `forumid` `forum_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `firstpostid` `first_post_id` INTEGER UNSIGNED NOT NULL,
 CHANGE COLUMN `lastpostid` `last_post_id` INTEGER UNSIGNED NOT NULL,
 CHANGE COLUMN `lasttime` `last_post_time` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `postct` `post_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `hitct` `view_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 DROP INDEX `FORUMID`,
 ADD INDEX `FORUMID` USING BTREE(`forum_id`)
, ROW_FORMAT = DYNAMIC;
ALTER TABLE types CHANGE COLUMN `internalid` `internal_id` TINYINT(4) NOT NULL DEFAULT 0;
ALTER TABLE usergroups RENAME TO user_groups,
 CHANGE COLUMN `userid` `user_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `groupid` `group_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`user_id`, `group_id`, `priority`)
, DROP INDEX `userid`
, DROP INDEX `groupid`,
 ADD INDEX `user_id` USING BTREE(`user_id`),
 ADD INDEX `group_id` USING BTREE(`group_id`);
ALTER TABLE users CHANGE COLUMN `joindate` `time_joined` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `lastactive` `time_active` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `postct` `post_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 CHANGE COLUMN `newspic` `news_pic` VARCHAR(64) DEFAULT NULL,
 CHANGE COLUMN `pmicon` `pm_icon` ENUM('bead','bellossom','dream','duskull','eatmail','gorgeous','letter','magnemite','pika','retro','slakoth','vee','wailmer','wingull','zigzagoon') NOT NULL DEFAULT 'letter',
 CHANGE COLUMN `customtitle` `custom_title` VARCHAR(32) NOT NULL;

Something like that, anyway. This puts all the column and table names in the same consistent underscore-delimited format -- the first two bullets from the original description. Unused columns are also gone, as mentioned in the comment on 8/7. Done in r406.

Ideas for remaining refactoring work:

• Do something with 'kind'. Something more flexible that makes moves actually searchable would be awesome. Move table in general needs a bit of an overhaul.
• Type effects should possibly be factored out.

And for text encoding:

• Migrate non-English names out of the main tables, and go with the assumption that they will not be used all that much: pull them out of FuzzyMatches, create a foreign_names table with all of them (id, type, language, name), and search that as a last resort? ...or skip right to it when the incoming entry has non-ASCII characters (i.e. utf-8). Also import French/German/etc names I suppose.
• Figure out how to make UTF-8 VARCHARs work with Catalyst or whatever's breaking.
• Every table should be InnoDB with UTF-8 if at all possible.

[eevee]

Missed changing the blurb -> short_description move API change in dex/common.tt. Fixed in r411.

[eevee]

Missed firstpost/lastpost occurances in forum/common.tt; r413.

[eevee]

Wait, why the fuck is this closed?

[eevee]

All columns changed to UTF-8 as of r420. SQL:

ALTER TABLE edits CONVERT TO CHARACTER SET utf8;
ALTER TABLE forums CONVERT TO CHARACTER SET utf8;
ALTER TABLE group_permissions CONVERT TO CHARACTER SET utf8;
ALTER TABLE messages CONVERT TO CHARACTER SET utf8;
ALTER TABLE posts CONVERT TO CHARACTER SET utf8;
ALTER TABLE sessions CONVERT TO CHARACTER SET utf8;
ALTER TABLE shoutbox CONVERT TO CHARACTER SET utf8;
ALTER TABLE threads CONVERT TO CHARACTER SET utf8;
ALTER TABLE user_groups CONVERT TO CHARACTER SET utf8;
ALTER TABLE users CONVERT TO CHARACTER SET utf8;

ALTER TABLE gallery CONVERT TO CHARACTER SET utf8;
ALTER TABLE gallery_keywords CONVERT TO CHARACTER SET utf8;
ALTER TABLE item_keywords CONVERT TO CHARACTER SET utf8;

ALTER TABLE evo_chains CONVERT TO CHARACTER SET utf8;
ALTER TABLE machines CONVERT TO CHARACTER SET utf8;
ALTER TABLE moves CONVERT TO CHARACTER SET utf8;
ALTER TABLE move_effects CONVERT TO CHARACTER SET utf8;
ALTER TABLE pokemon CONVERT TO CHARACTER SET utf8;
ALTER TABLE pokemon_breeds CONVERT TO CHARACTER SET utf8;
ALTER TABLE pokemon_moves CONVERT TO CHARACTER SET utf8;
ALTER TABLE moves MODIFY name_jp VARCHAR(7) CHARACTER SET utf8;
ALTER TABLE pokemon MODIFY name_jp VARCHAR(5) CHARACTER SET utf8;

[eevee]

All tables changed to InnoDB with r422. SQL:

ALTER TABLE edits ENGINE InnoDB;
ALTER TABLE error_log ENGINE InnoDB;
ALTER TABLE group_permissions ENGINE InnoDB;
ALTER TABLE messages ENGINE InnoDB;
ALTER TABLE sessions ENGINE InnoDB;
ALTER TABLE shoutbox ENGINE InnoDB;
ALTER TABLE user_groups ENGINE InnoDB;
ALTER TABLE users ENGINE InnoDB;

ALTER TABLE gallery ENGINE InnoDB;
ALTER TABLE gallery_keywords ENGINE InnoDB;
ALTER TABLE item_keywords ENGINE InnoDB;

Pokedex bits omitted this time as they can be safely done with just a db reload.

This leaves:

• Do something with 'kind'. Something more flexible that makes moves actually searchable would be awesome. Move table in general needs a bit of an overhaul.
• Type effects should possibly be factored out.
• Migrate non-English names out of the main tables, and go with the assumption that they will not be used all that much: pull them out of FuzzyMatches, create a foreign_names table with all of them (id, type, language, name), and search that as a last resort? ...or skip right to it when the incoming entry has non-ASCII characters (i.e. utf-8). Also import French/German/etc names I suppose.