From c306acf298ade5547512e04351a8f9ae4fdbae6d Mon Sep 17 00:00:00 2001 From: leak Date: Wed, 19 Jan 2011 14:04:33 +0100 Subject: SQL: Characters db storage type cleanup No. 2 --- sql/base/characters_database.sql | 104 ++++++++++----------- sql/updates/2011_01_19_0_characters_bug_report.sql | 1 - .../2011_01_19_0_characters_character_action.sql | 4 + ...11_01_19_0_characters_character_arena_stats.sql | 3 + .../2011_01_19_0_characters_character_aura.sql | 4 + .../2011_01_19_0_characters_character_banned.sql | 6 ++ ...19_0_characters_character_battleground_data.sql | 9 ++ ..._0_characters_character_battleground_random.sql | 2 + ...1_01_19_0_characters_character_declinedname.sql | 3 + ..._01_19_0_characters_character_equipmentsets.sql | 23 +++++ .../2011_01_19_0_characters_character_gifts.sql | 5 + .../2011_01_19_0_characters_character_glyphs.sql | 8 ++ 12 files changed, 119 insertions(+), 53 deletions(-) delete mode 100644 sql/updates/2011_01_19_0_characters_bug_report.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_action.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_arena_stats.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_aura.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_banned.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_battleground_data.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_battleground_random.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_declinedname.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_equipmentsets.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_gifts.sql create mode 100644 sql/updates/2011_01_19_0_characters_character_glyphs.sql (limited to 'sql') diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql index 9fb176d16d2..c1437dcb847 100644 --- a/sql/base/characters_database.sql +++ b/sql/base/characters_database.sql @@ -314,10 +314,10 @@ DROP TABLE IF EXISTS `character_action`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_action` ( - `guid` int(11) unsigned NOT NULL DEFAULT '0', + `guid` int(10) unsigned NOT NULL DEFAULT '0', `spec` tinyint(3) unsigned NOT NULL DEFAULT '0', `button` tinyint(3) unsigned NOT NULL DEFAULT '0', - `action` int(11) unsigned NOT NULL DEFAULT '0', + `action` int(10) unsigned NOT NULL DEFAULT '0', `type` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`guid`,`spec`,`button`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -341,7 +341,7 @@ DROP TABLE IF EXISTS `character_arena_stats`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_arena_stats` ( `guid` int(10) NOT NULL, - `slot` smallint(1) NOT NULL, + `slot` tinyint(3) NOT NULL, `personal_rating` int(10) NOT NULL, `matchmaker_rating` int(10) NOT NULL, PRIMARY KEY (`guid`,`slot`) @@ -365,10 +365,10 @@ DROP TABLE IF EXISTS `character_aura`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_aura` ( - `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', + `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', `caster_guid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Full Global Unique Identifier', `item_guid` bigint(20) unsigned NOT NULL DEFAULT '0', - `spell` int(11) unsigned NOT NULL DEFAULT '0', + `spell` mediumint(8) unsigned NOT NULL DEFAULT '0', `effect_mask` tinyint(3) unsigned NOT NULL DEFAULT '0', `recalculate_mask` tinyint(3) unsigned NOT NULL DEFAULT '0', `stackcount` tinyint(3) unsigned NOT NULL DEFAULT '1', @@ -402,12 +402,12 @@ DROP TABLE IF EXISTS `character_banned`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_banned` ( - `guid` int(11) NOT NULL DEFAULT '0' COMMENT 'Account id', - `bandate` bigint(40) NOT NULL DEFAULT '0', - `unbandate` bigint(40) NOT NULL DEFAULT '0', + `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Account id', + `bandate` int(10) unsigned NOT NULL DEFAULT '0', + `unbandate` int(10) unsigned NOT NULL DEFAULT '0', `bannedby` varchar(50) NOT NULL, `banreason` varchar(255) NOT NULL, - `active` tinyint(4) NOT NULL DEFAULT '1', + `active` tinyint(3) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`guid`,`bandate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ban List'; /*!40101 SET character_set_client = @saved_cs_client */; @@ -429,17 +429,17 @@ DROP TABLE IF EXISTS `character_battleground_data`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_battleground_data` ( - `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', - `instance_id` int(11) unsigned NOT NULL DEFAULT '0', - `team` int(11) unsigned NOT NULL DEFAULT '0', + `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', + `instance_id` int(10) unsigned NOT NULL, + `team` smallint(5) unsigned NOT NULL, `join_x` float NOT NULL DEFAULT '0', `join_y` float NOT NULL DEFAULT '0', `join_z` float NOT NULL DEFAULT '0', `join_o` float NOT NULL DEFAULT '0', - `join_map` int(11) NOT NULL DEFAULT '0', - `taxi_start` int(11) NOT NULL DEFAULT '0', - `taxi_end` int(11) NOT NULL DEFAULT '0', - `mount_spell` int(11) NOT NULL DEFAULT '0', + `join_map` smallint(5) unsigned NOT NULL DEFAULT '0', + `taxi_start` int(10) unsigned NOT NULL DEFAULT '0', + `taxi_end` int(10) unsigned NOT NULL DEFAULT '0', + `mount_spell` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Player System'; /*!40101 SET character_set_client = @saved_cs_client */; @@ -461,7 +461,7 @@ DROP TABLE IF EXISTS `character_battleground_random`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_battleground_random` ( - `guid` int(11) unsigned NOT NULL DEFAULT '0', + `guid` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; @@ -483,7 +483,7 @@ DROP TABLE IF EXISTS `character_declinedname`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_declinedname` ( - `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', + `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier', `genitive` varchar(15) NOT NULL DEFAULT '', `dative` varchar(15) NOT NULL DEFAULT '', `accusative` varchar(15) NOT NULL DEFAULT '', @@ -510,30 +510,30 @@ DROP TABLE IF EXISTS `character_equipmentsets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_equipmentsets` ( - `guid` int(11) NOT NULL DEFAULT '0', + `guid` int(10) NOT NULL DEFAULT '0', `setguid` bigint(20) NOT NULL AUTO_INCREMENT, - `setindex` tinyint(4) NOT NULL DEFAULT '0', - `name` varchar(100) NOT NULL, + `setindex` tinyint(3) unsigned NOT NULL DEFAULT '0', + `name` varchar(31) NOT NULL, `iconname` varchar(100) NOT NULL, - `item0` int(11) NOT NULL DEFAULT '0', - `item1` int(11) NOT NULL DEFAULT '0', - `item2` int(11) NOT NULL DEFAULT '0', - `item3` int(11) NOT NULL DEFAULT '0', - `item4` int(11) NOT NULL DEFAULT '0', - `item5` int(11) NOT NULL DEFAULT '0', - `item6` int(11) NOT NULL DEFAULT '0', - `item7` int(11) NOT NULL DEFAULT '0', - `item8` int(11) NOT NULL DEFAULT '0', - `item9` int(11) NOT NULL DEFAULT '0', - `item10` int(11) NOT NULL DEFAULT '0', - `item11` int(11) NOT NULL DEFAULT '0', - `item12` int(11) NOT NULL DEFAULT '0', - `item13` int(11) NOT NULL DEFAULT '0', - `item14` int(11) NOT NULL DEFAULT '0', - `item15` int(11) NOT NULL DEFAULT '0', - `item16` int(11) NOT NULL DEFAULT '0', - `item17` int(11) NOT NULL DEFAULT '0', - `item18` int(11) NOT NULL DEFAULT '0', + `item0` int(10) unsigned NOT NULL DEFAULT '0', + `item1` int(10) unsigned NOT NULL DEFAULT '0', + `item2` int(10) unsigned NOT NULL DEFAULT '0', + `item3` int(10) unsigned NOT NULL DEFAULT '0', + `item4` int(10) unsigned NOT NULL DEFAULT '0', + `item5` int(10) unsigned NOT NULL DEFAULT '0', + `item6` int(10) unsigned NOT NULL DEFAULT '0', + `item7` int(10) unsigned NOT NULL DEFAULT '0', + `item8` int(10) unsigned NOT NULL DEFAULT '0', + `item9` int(10) unsigned NOT NULL DEFAULT '0', + `item10` int(10) unsigned NOT NULL DEFAULT '0', + `item11` int(10) unsigned NOT NULL DEFAULT '0', + `item12` int(10) unsigned NOT NULL DEFAULT '0', + `item13` int(10) unsigned NOT NULL DEFAULT '0', + `item14` int(10) unsigned NOT NULL DEFAULT '0', + `item15` int(10) unsigned NOT NULL DEFAULT '0', + `item16` int(10) unsigned NOT NULL DEFAULT '0', + `item17` int(10) unsigned NOT NULL DEFAULT '0', + `item18` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`setguid`), UNIQUE KEY `idx_set` (`guid`,`setguid`,`setindex`), KEY `Idx_setindex` (`setindex`) @@ -557,10 +557,10 @@ DROP TABLE IF EXISTS `character_gifts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_gifts` ( - `guid` int(20) unsigned NOT NULL DEFAULT '0', - `item_guid` int(11) unsigned NOT NULL DEFAULT '0', - `entry` int(20) unsigned NOT NULL DEFAULT '0', - `flags` int(20) unsigned NOT NULL DEFAULT '0', + `guid` int(10) unsigned NOT NULL DEFAULT '0', + `item_guid` int(10) unsigned NOT NULL DEFAULT '0', + `entry` int(10) unsigned NOT NULL DEFAULT '0', + `flags` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`item_guid`), KEY `idx_guid` (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -583,14 +583,14 @@ DROP TABLE IF EXISTS `character_glyphs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `character_glyphs` ( - `guid` int(11) unsigned NOT NULL, + `guid` int(10) unsigned NOT NULL, `spec` tinyint(3) unsigned NOT NULL DEFAULT '0', - `glyph1` int(11) unsigned NOT NULL DEFAULT '0', - `glyph2` int(11) unsigned DEFAULT '0', - `glyph3` int(11) unsigned DEFAULT '0', - `glyph4` int(11) unsigned DEFAULT '0', - `glyph5` int(11) unsigned DEFAULT '0', - `glyph6` int(11) unsigned DEFAULT '0', + `glyph1` smallint(5) unsigned NOT NULL DEFAULT '0', + `glyph2` smallint(5) unsigned DEFAULT '0', + `glyph3` smallint(5) unsigned DEFAULT '0', + `glyph4` smallint(5) unsigned DEFAULT '0', + `glyph5` smallint(5) unsigned DEFAULT '0', + `glyph6` smallint(5) unsigned DEFAULT '0', PRIMARY KEY (`guid`,`spec`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; @@ -2198,4 +2198,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2011-01-19 4:18:09 +-- Dump completed on 2011-01-19 16:00:01 diff --git a/sql/updates/2011_01_19_0_characters_bug_report.sql b/sql/updates/2011_01_19_0_characters_bug_report.sql deleted file mode 100644 index e9c40877e69..00000000000 --- a/sql/updates/2011_01_19_0_characters_bug_report.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE `addons` ROW_FORMAT=DEFAULT; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_action.sql b/sql/updates/2011_01_19_0_characters_character_action.sql new file mode 100644 index 00000000000..d2d235e270c --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_action.sql @@ -0,0 +1,4 @@ +ALTER TABLE `character_action` +ROW_FORMAT=DEFAULT, +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `action` `action` INT(10) UNSIGNED DEFAULT '0' NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_arena_stats.sql b/sql/updates/2011_01_19_0_characters_character_arena_stats.sql new file mode 100644 index 00000000000..e68048d2f0e --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_arena_stats.sql @@ -0,0 +1,3 @@ +ALTER TABLE `character_arena_stats` +ENGINE=InnoDB, +CHANGE `slot` `slot` TINYINT(3) NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_aura.sql b/sql/updates/2011_01_19_0_characters_character_aura.sql new file mode 100644 index 00000000000..072c88235e7 --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_aura.sql @@ -0,0 +1,4 @@ +ALTER TABLE `character_aura` +ROW_FORMAT=DEFAULT, +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL COMMENT 'Global Unique Identifier', +CHANGE `spell` `spell` MEDIUMINT(8) UNSIGNED DEFAULT '0' NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_banned.sql b/sql/updates/2011_01_19_0_characters_character_banned.sql new file mode 100644 index 00000000000..da3d46d9929 --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_banned.sql @@ -0,0 +1,6 @@ +ALTER TABLE `character_banned` +ROW_FORMAT=DEFAULT, +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL COMMENT 'Account id', +CHANGE `bandate` `bandate` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `unbandate` `unbandate` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `active` `active` TINYINT(3) UNSIGNED DEFAULT '1' NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_battleground_data.sql b/sql/updates/2011_01_19_0_characters_character_battleground_data.sql new file mode 100644 index 00000000000..09f24ed8bf9 --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_battleground_data.sql @@ -0,0 +1,9 @@ +ALTER TABLE `character_battleground_data` +ROW_FORMAT=DEFAULT, +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL COMMENT 'Global Unique Identifier', +CHANGE `instance_id` `instance_id` INT(10) UNSIGNED NOT NULL, +CHANGE `team` `team` SMALLINT(5) UNSIGNED NOT NULL, +CHANGE `join_map` `join_map` SMALLINT(5) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `taxi_start` `taxi_start` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `taxi_end` `taxi_end` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `mount_spell` `mount_spell` MEDIUMINT(8) UNSIGNED DEFAULT '0' NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_battleground_random.sql b/sql/updates/2011_01_19_0_characters_character_battleground_random.sql new file mode 100644 index 00000000000..cf76a17fc0c --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_battleground_random.sql @@ -0,0 +1,2 @@ +ALTER TABLE `character_battleground_random` +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_declinedname.sql b/sql/updates/2011_01_19_0_characters_character_declinedname.sql new file mode 100644 index 00000000000..912db55712b --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_declinedname.sql @@ -0,0 +1,3 @@ +ALTER TABLE `character_declinedname` +ROW_FORMAT=DEFAULT, +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL COMMENT 'Global Unique Identifier'; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_equipmentsets.sql b/sql/updates/2011_01_19_0_characters_character_equipmentsets.sql new file mode 100644 index 00000000000..3f4fe76d189 --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_equipmentsets.sql @@ -0,0 +1,23 @@ +ALTER TABLE `character_equipmentsets` +CHANGE `guid` `guid` INT(10) DEFAULT '0' NOT NULL, +CHANGE `setindex` `setindex` TINYINT(3) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `name` `name` VARCHAR(31) NOT NULL, +CHANGE `item0` `item0` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item1` `item1` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item2` `item2` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item3` `item3` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item4` `item4` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item5` `item5` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item6` `item6` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item7` `item7` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item8` `item8` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item9` `item9` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item10` `item10` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item11` `item11` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item12` `item12` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item13` `item13` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item14` `item14` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item15` `item15` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item16` `item16` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item17` `item17` INT(10) UNSIGNED NOT NULL DEFAULT '0', +CHANGE `item18` `item18` INT(10) UNSIGNED NOT NULL DEFAULT '0'; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_gifts.sql b/sql/updates/2011_01_19_0_characters_character_gifts.sql new file mode 100644 index 00000000000..2012eb96315 --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_gifts.sql @@ -0,0 +1,5 @@ +ALTER TABLE `character_gifts` +CHANGE `guid` `guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `item_guid` `item_guid` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `entry` `entry` INT(10) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `flags` `flags` INT(10) UNSIGNED DEFAULT '0' NOT NULL; \ No newline at end of file diff --git a/sql/updates/2011_01_19_0_characters_character_glyphs.sql b/sql/updates/2011_01_19_0_characters_character_glyphs.sql new file mode 100644 index 00000000000..73bb65f4ef9 --- /dev/null +++ b/sql/updates/2011_01_19_0_characters_character_glyphs.sql @@ -0,0 +1,8 @@ +ALTER TABLE `character_glyphs` +CHANGE `guid` `guid` INT(10) UNSIGNED NOT NULL, +CHANGE `glyph1` `glyph1` SMALLINT(5) UNSIGNED DEFAULT '0' NOT NULL, +CHANGE `glyph2` `glyph2` SMALLINT(5) UNSIGNED DEFAULT '0' NULL , +CHANGE `glyph3` `glyph3` SMALLINT(5) UNSIGNED DEFAULT '0' NULL , +CHANGE `glyph4` `glyph4` SMALLINT(5) UNSIGNED DEFAULT '0' NULL , +CHANGE `glyph5` `glyph5` SMALLINT(5) UNSIGNED DEFAULT '0' NULL , +CHANGE `glyph6` `glyph6` SMALLINT(5) UNSIGNED DEFAULT '0' NULL ; \ No newline at end of file -- cgit v1.2.3