diff options
| author | Valaris <Valaris@54d463be-8e91-2dee-dedb-b68131a5f0ec> | 2006-01-29 16:10:48 +0000 |
|---|---|---|
| committer | Valaris <Valaris@54d463be-8e91-2dee-dedb-b68131a5f0ec> | 2006-01-29 16:10:48 +0000 |
| commit | 620e60eebce2c1f35c5c9a82f6ca365b316587f5 (patch) | |
| tree | 38a39e0415f419d9a49ae456ed0e26654c23d559 /sql-files/convert_guild_tables.sql | |
| parent | a2675f07d7da22a7c6ae11f545bf8f671e785a82 (diff) | |
| download | hercules-620e60eebce2c1f35c5c9a82f6ca365b316587f5.tar.gz hercules-620e60eebce2c1f35c5c9a82f6ca365b316587f5.tar.bz2 hercules-620e60eebce2c1f35c5c9a82f6ca365b316587f5.tar.xz hercules-620e60eebce2c1f35c5c9a82f6ca365b316587f5.zip | |
AS OF SVN REV. 5901, WE ARE NOW USING TRUNK. ALL UNTESTED BUGFIXES/FEATURES GO INTO TRUNK.
IF YOU HAVE A WORKING AND TESTED BUGFIX PUT IT INTO STABLE AS WELL AS TRUNK. EVERYTHING ELSE
GOES INTO TRUNK AND WILL BE MERGED INTO STABLE BY VALARIS AND WIZPUTER. -- VALARIS
git-svn-id: https://rathena.svn.sourceforge.net/svnroot/rathena/trunk@5094 54d463be-8e91-2dee-dedb-b68131a5f0ec
Diffstat (limited to 'sql-files/convert_guild_tables.sql')
| -rw-r--r-- | sql-files/convert_guild_tables.sql | 82 |
1 files changed, 82 insertions, 0 deletions
diff --git a/sql-files/convert_guild_tables.sql b/sql-files/convert_guild_tables.sql new file mode 100644 index 000000000..9763f5928 --- /dev/null +++ b/sql-files/convert_guild_tables.sql @@ -0,0 +1,82 @@ +###################################################################################################
+# 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;
+
|