summaryrefslogtreecommitdiff
path: root/sql-files
diff options
context:
space:
mode:
authorskotlex <skotlex@54d463be-8e91-2dee-dedb-b68131a5f0ec>2006-09-12 18:22:54 +0000
committerskotlex <skotlex@54d463be-8e91-2dee-dedb-b68131a5f0ec>2006-09-12 18:22:54 +0000
commiteb7df5bd7ec3557acbe42c17775ffb2cd2e69c36 (patch)
tree05906bcfcf71133222b7e51f1c145b2707584c5e /sql-files
parent18b8ec2594cd4a8748d8c69a82f61c8637ef400e (diff)
downloadhercules-eb7df5bd7ec3557acbe42c17775ffb2cd2e69c36.tar.gz
hercules-eb7df5bd7ec3557acbe42c17775ffb2cd2e69c36.tar.bz2
hercules-eb7df5bd7ec3557acbe42c17775ffb2cd2e69c36.tar.xz
hercules-eb7df5bd7ec3557acbe42c17775ffb2cd2e69c36.zip
- 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
Diffstat (limited to 'sql-files')
-rw-r--r--sql-files/convert_engine.sql22
-rw-r--r--sql-files/convert_guild_tables.sql82
-rw-r--r--sql-files/main.sql47
3 files changed, 40 insertions, 111 deletions
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`