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 --- Changelog-Trunk.txt | 9 +++++ sql-files/convert_engine.sql | 22 ++++++++++ sql-files/convert_guild_tables.sql | 82 -------------------------------------- sql-files/main.sql | 47 +++++++++------------- src/char_sql/char.c | 10 +++++ 5 files changed, 59 insertions(+), 111 deletions(-) create mode 100644 sql-files/convert_engine.sql delete mode 100644 sql-files/convert_guild_tables.sql diff --git a/Changelog-Trunk.txt b/Changelog-Trunk.txt index 3efcc1791..ae6f03114 100644 --- a/Changelog-Trunk.txt +++ b/Changelog-Trunk.txt @@ -4,6 +4,15 @@ AS OF SVN REV. 5091, WE ARE NOW USING TRUNK. ALL UNTESTED BUGFIXES/FEATURES GO IF YOU HAVE A WORKING AND TESTED BUGFIX PUT IT INTO STABLE AS WELL AS TRUNK. 2006/09/12 + * Added Jaguar's sql script to convert all tables to MyISAM. It's located + in sql-files/convert_engine.sql WARNING: Using MyISAM is much faster than + InnoDB, but you are strongly recommended to backup your database before + switching engines since we don't know if eA is fully coded to work + correctly with MyISAM yet. [Skotlex] + * Added manual deletion of sc_data entries when deleting a character + (char-SQL) [Skotlex] + * Updated main.sql to use pure MyISAM tables, removed all foreign keys. + [Skotlex] * Updated battle_switch to use strncmpi instead of strcmpi, it makes it so using "yessir" will match "yes", this is actually needed because if you set a config setting to "yes " (notice the trailing space), then the map config 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` diff --git a/src/char_sql/char.c b/src/char_sql/char.c index 3a21f2ebb..cb0ebba89 100644 --- a/src/char_sql/char.c +++ b/src/char_sql/char.c @@ -1619,6 +1619,16 @@ int delete_char_sql(int char_id, int partner_id) ShowDebug("at %s:%d - %s\n", __FILE__,__LINE__,tmp_sql); } +#ifdef ENABLE_SC_SAVING + /* status changes */ + sprintf(tmp_sql, "DELETE FROM `%s` WHERE `account_id` = '%d' AND `char_id`='%d'", + scdata_db, account_id, char_id); + if(mysql_query(&mysql_handle, tmp_sql)) { + ShowSQL("DB error - %s\n",mysql_error(&mysql_handle)); + ShowDebug("at %s:%d - %s\n", __FILE__,__LINE__,tmp_sql); + } +#endif + if (log_char) { sprintf(tmp_sql,"INSERT INTO `%s`(`time`, `account_id`,`char_num`,`char_msg`,`name`) VALUES (NOW(), '%d', '%d', 'Deleted char (CID %d)', '%s')", charlog_db, account_id, 0, char_id, t_name); -- cgit v1.2.3-70-g09d2