From eb7df5bd7ec3557acbe42c17775ffb2cd2e69c36 Mon Sep 17 00:00:00 2001 From: skotlex Date: Tue, 12 Sep 2006 18:22:54 +0000 Subject: - Added manual deletion of sc_data entries when deleting a character (char-SQL) - Updated main.sql to use pure MyISAM tables, removed all foreign keys. - Added Jaguar's sql script to convert all tables to MyISAM. It's located in sql-files/convert_engine.sql - Removed convert_guild_tables.sql as we don't use InnoDB anymore. git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@8722 54d463be-8e91-2dee-dedb-b68131a5f0ec --- sql-files/convert_engine.sql | 22 ++++++++++ sql-files/convert_guild_tables.sql | 82 -------------------------------------- sql-files/main.sql | 47 +++++++++------------- 3 files changed, 40 insertions(+), 111 deletions(-) create mode 100644 sql-files/convert_engine.sql delete mode 100644 sql-files/convert_guild_tables.sql (limited to 'sql-files') diff --git a/sql-files/convert_engine.sql b/sql-files/convert_engine.sql new file mode 100644 index 000000000..ecff06072 --- /dev/null +++ b/sql-files/convert_engine.sql @@ -0,0 +1,22 @@ +-- +-- eAthena Database Converter ( InnoDB -> MyISAM ) by Jaguar +-- + +ALTER TABLE `sc_data` DROP FOREIGN KEY `scdata_ibfk_1`, DROP FOREIGN KEY `scdata_ibfk_2`; +ALTER TABLE `guild` DROP FOREIGN KEY `guild_ibfk_1`; +ALTER TABLE `guild_alliance` DROP FOREIGN KEY `guild_alliance_ibfk_1`, DROP FOREIGN KEY `guild_alliance_ibfk_2`; +ALTER TABLE `guild_expulsion` DROP FOREIGN KEY `guild_expulsion_ibfk_1`; +ALTER TABLE `guild_member` DROP FOREIGN KEY `guild_member_ibfk_1`, DROP FOREIGN KEY `guild_member_ibfk_2`; +ALTER TABLE `guild_position` DROP FOREIGN KEY `guild_position_ibfk_1`; +ALTER TABLE `guild_skill` DROP FOREIGN KEY `guild_skill_ibfk_1`; +ALTER TABLE `guild_storage` DROP FOREIGN KEY `guild_storage_ibfk_1`; +ALTER TABLE `sc_data` ENGINE = MYISAM; +ALTER TABLE `login` ENGINE = MYISAM; +ALTER TABLE `char` ENGINE = MYISAM; +ALTER TABLE `guild` ENGINE = MYISAM; +ALTER TABLE `guild_alliance` ENGINE = MYISAM; +ALTER TABLE `guild_expulsion` ENGINE = MYISAM; +ALTER TABLE `guild_member` ENGINE = MYISAM; +ALTER TABLE `guild_position` ENGINE = MYISAM; +ALTER TABLE `guild_skill` ENGINE = MYISAM; +ALTER TABLE `guild_storage` ENGINE = MYISAM; diff --git a/sql-files/convert_guild_tables.sql b/sql-files/convert_guild_tables.sql deleted file mode 100644 index 9763f5928..000000000 --- a/sql-files/convert_guild_tables.sql +++ /dev/null @@ -1,82 +0,0 @@ -################################################################################################### -# This one is also necessary, since foreign keys may only reference -# InnoDB tables. - -ALTER TABLE `char` TYPE=InnoDB; - -################################################################################################### -# Add the new guild column char_id and populate it with Guild Master ids -# Note that the auto-fill is case sensitive! - -ALTER TABLE `guild` ADD COLUMN `char_id` int(11) NOT NULL DEFAULT '10000' AFTER `name`; -UPDATE `guild`,`char` SET `guild`.`char_id`=`char`.`char_id` WHERE `guild`.`master` = `char`.`name`; - -################################################################################################### -# Now we go on altering stuff - dropping old keys (just in case), -# converting table types, and then creating new keys. - -ALTER TABLE guild DROP PRIMARY KEY; -ALTER TABLE guild TYPE=InnoDB; -ALTER TABLE guild - ADD PRIMARY KEY (guild_id,char_id), - MODIFY COLUMN `guild_id` INTEGER NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 10000, - ADD KEY char_id (char_id), - ADD UNIQUE KEY guild_id (guild_id), - ADD CONSTRAINT `guild_ibfk_1` FOREIGN KEY (`char_id`) REFERENCES `char` -(`char_id`) ON DELETE CASCADE; - -ALTER TABLE guild_alliance DROP INDEX `guild_id`; -ALTER TABLE guild_alliance TYPE=InnoDB; -ALTER TABLE guild_alliance - ADD PRIMARY KEY (guild_id,alliance_id), - ADD KEY alliance_id (alliance_id), - ADD CONSTRAINT `guild_alliance_ibfk_1` FOREIGN KEY (`guild_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE, - ADD CONSTRAINT `guild_alliance_ibfk_2` FOREIGN KEY (`alliance_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE; - -ALTER TABLE guild_castle DROP PRIMARY KEY, DROP INDEX `guild_id`; -ALTER TABLE guild_castle TYPE=InnoDB; -ALTER TABLE guild_castle - ADD PRIMARY KEY (castle_id); - -ALTER TABLE guild_expulsion DROP INDEX `guild_id`; -ALTER TABLE guild_expulsion TYPE=InnoDB; -ALTER TABLE guild_expulsion - ADD PRIMARY KEY (guild_id,name), - ADD CONSTRAINT `guild_expulsion_ibfk_1` FOREIGN KEY (`guild_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE; - -ALTER TABLE guild_member DROP INDEX `guild_id`, DROP INDEX `account_id`; -ALTER TABLE guild_member TYPE=InnoDB; -ALTER TABLE guild_member DROP INDEX `char_id`; -ALTER TABLE guild_member - ADD PRIMARY KEY (guild_id,char_id), - ADD KEY char_id (char_id), - ADD CONSTRAINT `guild_member_ibfk_1` FOREIGN KEY (`guild_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE, - ADD CONSTRAINT `guild_member_ibfk_2` FOREIGN KEY (`char_id`) -REFERENCES `char` (`char_id`) ON DELETE CASCADE; - -ALTER TABLE guild_position DROP INDEX `guild_id`; -ALTER TABLE guild_position TYPE=InnoDB; -ALTER TABLE guild_position - ADD PRIMARY KEY (guild_id,position), -ADD KEY guild_id (guild_id), -ADD CONSTRAINT `guild_position_ibfk_1` FOREIGN KEY (`guild_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE; - -ALTER TABLE guild_skill DROP INDEX `guild_id`; -ALTER TABLE guild_skill TYPE=InnoDB; -ALTER TABLE guild_skill - ADD PRIMARY KEY (guild_id,id), - ADD CONSTRAINT `guild_skill_ibfk_1` FOREIGN KEY (`guild_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE; - -ALTER TABLE guild_storage DROP INDEX `guild_id`; -ALTER TABLE guild_storage TYPE=InnoDB; -ALTER TABLE guild_storage - ADD KEY guild_id (guild_id), - ADD CONSTRAINT `guild_storage_ibfk_1` FOREIGN KEY (`guild_id`) -REFERENCES `guild` (`guild_id`) ON DELETE CASCADE; - diff --git a/sql-files/main.sql b/sql-files/main.sql index 17846b319..2edb747e3 100644 --- a/sql-files/main.sql +++ b/sql-files/main.sql @@ -82,7 +82,7 @@ CREATE TABLE `char` ( KEY `account_id` (`account_id`), KEY `party_id` (`party_id`), KEY `guild_id` (`guild_id`) -) TYPE=InnoDB AUTO_INCREMENT=150000; +) TYPE=MyISAM AUTO_INCREMENT=150000; -- -- Table structure for table `charlog` @@ -156,9 +156,8 @@ CREATE TABLE `guild` ( `emblem_data` blob, PRIMARY KEY (`guild_id`,`char_id`), UNIQUE KEY `guild_id` (`guild_id`), - KEY `char_id` (`char_id`), - CONSTRAINT `guild_ibfk_1` FOREIGN KEY (`char_id`) REFERENCES `char` (`char_id`) ON DELETE CASCADE -) TYPE=InnoDB; + KEY `char_id` (`char_id`) +) TYPE=MyISAM; -- -- Table structure for table `guild_alliance` @@ -171,10 +170,8 @@ CREATE TABLE `guild_alliance` ( `alliance_id` int(11) unsigned NOT NULL default '0', `name` varchar(24) NOT NULL default '', PRIMARY KEY (`guild_id`,`alliance_id`), - KEY `alliance_id` (`alliance_id`), - CONSTRAINT `guild_alliance_ibfk_1` FOREIGN KEY (`guild_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE, - CONSTRAINT `guild_alliance_ibfk_2` FOREIGN KEY (`alliance_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE -) TYPE=InnoDB; + KEY `alliance_id` (`alliance_id`) +) TYPE=MyISAM; -- -- Table structure for table `guild_castle` @@ -226,9 +223,8 @@ CREATE TABLE `guild_expulsion` ( `rsv1` int(11) unsigned NOT NULL default '0', `rsv2` int(11) unsigned NOT NULL default '0', `rsv3` int(11) unsigned NOT NULL default '0', - PRIMARY KEY (`guild_id`,`name`), - CONSTRAINT `guild_expulsion_ibfk_1` FOREIGN KEY (`guild_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE -) TYPE=InnoDB; + PRIMARY KEY (`guild_id`,`name`) +) TYPE=MyISAM; -- -- Table structure for table `guild_member` @@ -252,10 +248,8 @@ CREATE TABLE `guild_member` ( `rsv2` int(11) unsigned NOT NULL default '0', `name` varchar(24) NOT NULL default '', PRIMARY KEY (`guild_id`,`char_id`), - KEY `char_id` (`char_id`), - CONSTRAINT `guild_member_ibfk_1` FOREIGN KEY (`guild_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE, - CONSTRAINT `guild_member_ibfk_2` FOREIGN KEY (`char_id`) REFERENCES `char` (`char_id`) ON DELETE CASCADE -) TYPE=InnoDB; + KEY `char_id` (`char_id`) +) TYPE=MyISAM; -- -- Table structure for table `guild_position` @@ -269,9 +263,8 @@ CREATE TABLE `guild_position` ( `mode` tinyint(11) unsigned NOT NULL default '0', `exp_mode` tinyint(11) unsigned NOT NULL default '0', PRIMARY KEY (`guild_id`,`position`), - KEY `guild_id` (`guild_id`), - CONSTRAINT `guild_position_ibfk_1` FOREIGN KEY (`guild_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE -) TYPE=InnoDB; + KEY `guild_id` (`guild_id`) +) TYPE=MyISAM; -- -- Table structure for table `guild_skill` @@ -282,9 +275,8 @@ CREATE TABLE `guild_skill` ( `guild_id` int(11) unsigned NOT NULL default '0', `id` smallint(11) unsigned NOT NULL default '0', `lv` tinyint(11) unsigned NOT NULL default '0', - PRIMARY KEY (`guild_id`,`id`), - CONSTRAINT `guild_skill_ibfk_1` FOREIGN KEY (`guild_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE -) TYPE=InnoDB; + PRIMARY KEY (`guild_id`,`id`) +) TYPE=MyISAM; -- -- Table structure for table `guild_storage` @@ -305,9 +297,8 @@ CREATE TABLE `guild_storage` ( `card2` smallint(11) NOT NULL default '0', `card3` smallint(11) NOT NULL default '0', PRIMARY KEY (`id`), - KEY `guild_id` (`guild_id`), - CONSTRAINT `guild_storage_ibfk_1` FOREIGN KEY (`guild_id`) REFERENCES `guild` (`guild_id`) ON DELETE CASCADE -) TYPE=InnoDB; + KEY `guild_id` (`guild_id`) +) TYPE=MyISAM; -- -- Table structure for table `homunculus` @@ -407,7 +398,7 @@ CREATE TABLE `login` ( `state` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`account_id`), KEY `name` (`userid`) -) TYPE=InnoDB AUTO_INCREMENT=2000000; +) TYPE=MyISAM AUTO_INCREMENT=2000000; -- added standard accounts for servers, VERY INSECURE!!! -- inserted into the table called login which is above @@ -442,10 +433,8 @@ CREATE TABLE `sc_data` ( `val3` int(11) NOT NULL default '0', `val4` int(11) NOT NULL default '0', KEY (`account_id`), - KEY (`char_id`), - CONSTRAINT `scdata_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `login` (`account_id`) ON DELETE CASCADE, - CONSTRAINT `scdata_ibfk_2` FOREIGN KEY (`char_id`) REFERENCES `char` (`char_id`) ON DELETE CASCADE -) TYPE=InnoDB; + KEY (`char_id`) +) TYPE=MyISAM; -- -- Table structure for table `loginlog` -- cgit v1.2.3-60-g2f50