- 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

5 files changed, 59 insertions, 111 deletions

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);