diff options
Diffstat (limited to 'sql')
10 files changed, 81 insertions, 40 deletions
diff --git a/sql/base/auth_database.sql b/sql/base/auth_database.sql index 305ba679bf2..5803529da1e 100644 --- a/sql/base/auth_database.sql +++ b/sql/base/auth_database.sql @@ -1,8 +1,8 @@ --- MySQL dump 10.13 Distrib 5.5.21, for Win64 (x86) +-- MySQL dump 10.13 Distrib 5.5.22, for Win64 (x86) -- -- Host: localhost Database: auth -- ------------------------------------------------------ --- Server version 5.5.21 +-- Server version 5.5.22 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -23,27 +23,27 @@ DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `account` ( - `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Identifier', + `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Identifier', `username` varchar(32) NOT NULL DEFAULT '', `sha_pass_hash` varchar(40) NOT NULL DEFAULT '', - `sessionkey` longtext, - `v` longtext, - `s` longtext, - `email` text, + `sessionkey` varchar(80) NOT NULL DEFAULT '', + `v` varchar(64) NOT NULL DEFAULT '', + `s` varchar(64) NOT NULL DEFAULT '', + `email` varchar(254) NOT NULL DEFAULT '', `joindate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `last_ip` varchar(30) NOT NULL DEFAULT '127.0.0.1', - `failed_logins` int(11) unsigned NOT NULL DEFAULT '0', + `last_ip` varchar(15) NOT NULL DEFAULT '127.0.0.1', + `failed_logins` int(10) unsigned NOT NULL DEFAULT '0', `locked` tinyint(3) unsigned NOT NULL DEFAULT '0', `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `online` tinyint(4) NOT NULL DEFAULT '0', + `online` tinyint(3) unsigned NOT NULL DEFAULT '0', `expansion` tinyint(3) unsigned NOT NULL DEFAULT '2', - `mutetime` bigint(40) NOT NULL DEFAULT '0', + `mutetime` bigint(20) NOT NULL DEFAULT '0', `locale` tinyint(3) unsigned NOT NULL DEFAULT '0', - `os` varchar(4) NOT NULL DEFAULT '', - `recruiter` int(11) NOT NULL DEFAULT '0', + `os` varchar(3) NOT NULL DEFAULT '', + `recruiter` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `idx_username` (`username`) -) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Account System'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account System'; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -63,11 +63,11 @@ DROP TABLE IF EXISTS `account_access`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `account_access` ( - `id` int(11) unsigned NOT NULL, + `id` int(10) unsigned NOT NULL, `gmlevel` tinyint(3) unsigned NOT NULL, `RealmID` int(11) NOT NULL DEFAULT '-1', PRIMARY KEY (`id`,`RealmID`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; +) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -87,14 +87,14 @@ DROP TABLE IF EXISTS `account_banned`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `account_banned` ( - `id` int(11) NOT NULL DEFAULT '0' COMMENT 'Account id', + `id` 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 (`id`,`bandate`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Ban List'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ban List'; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -120,7 +120,7 @@ CREATE TABLE `ip_banned` ( `bannedby` varchar(50) NOT NULL DEFAULT '[Console]', `banreason` varchar(255) NOT NULL DEFAULT 'no reason', PRIMARY KEY (`ip`,`bandate`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Banned IPs'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Banned IPs'; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -140,11 +140,11 @@ DROP TABLE IF EXISTS `logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `logs` ( - `time` int(14) NOT NULL, - `realm` int(4) NOT NULL, - `type` int(4) NOT NULL, - `string` text -) ENGINE=MyISAM DEFAULT CHARSET=latin1; + `time` int(10) unsigned NOT NULL, + `realm` int(10) unsigned NOT NULL, + `type` tinyint(3) unsigned NOT NULL, + `string` text CHARACTER SET latin1 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -164,12 +164,12 @@ DROP TABLE IF EXISTS `realmcharacters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `realmcharacters` ( - `realmid` int(11) unsigned NOT NULL DEFAULT '0', - `acctid` bigint(20) unsigned NOT NULL, + `realmid` int(10) unsigned NOT NULL DEFAULT '0', + `acctid` int(10) unsigned NOT NULL, `numchars` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`realmid`,`acctid`), KEY `acctid` (`acctid`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Realm Character Tracker'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Realm Character Tracker'; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -189,19 +189,19 @@ DROP TABLE IF EXISTS `realmlist`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `realmlist` ( - `id` int(11) unsigned NOT NULL AUTO_INCREMENT, + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL DEFAULT '', - `address` varchar(32) NOT NULL DEFAULT '127.0.0.1', - `port` int(11) NOT NULL DEFAULT '8085', + `address` varchar(15) NOT NULL DEFAULT '127.0.0.1', + `port` smallint(5) unsigned NOT NULL DEFAULT '8085', `icon` tinyint(3) unsigned NOT NULL DEFAULT '0', `flag` tinyint(3) unsigned NOT NULL DEFAULT '2', `timezone` tinyint(3) unsigned NOT NULL DEFAULT '0', `allowedSecurityLevel` tinyint(3) unsigned NOT NULL DEFAULT '0', `population` float unsigned NOT NULL DEFAULT '0', - `gamebuild` int(11) unsigned NOT NULL DEFAULT '12340', + `gamebuild` int(10) unsigned NOT NULL DEFAULT '12340', PRIMARY KEY (`id`), UNIQUE KEY `idx_name` (`name`) -) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Realm System'; +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='Realm System'; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -222,14 +222,13 @@ DROP TABLE IF EXISTS `uptime`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `uptime` ( - `realmid` int(11) unsigned NOT NULL, - `starttime` bigint(20) unsigned NOT NULL DEFAULT '0', - `startstring` varchar(64) NOT NULL DEFAULT '', - `uptime` bigint(20) unsigned NOT NULL DEFAULT '0', + `realmid` int(10) unsigned NOT NULL, + `starttime` int(10) unsigned NOT NULL DEFAULT '0', + `uptime` int(10) unsigned NOT NULL DEFAULT '0', `maxplayers` smallint(5) unsigned NOT NULL DEFAULT '0', `revision` varchar(255) NOT NULL DEFAULT 'Trinitycore', PRIMARY KEY (`realmid`,`starttime`) -) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Uptime system'; +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Uptime system'; /*!40101 SET character_set_client = @saved_cs_client */; -- @@ -250,4 +249,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2012-03-26 18:21:51 +-- Dump completed on 2012-03-28 18:26:06 diff --git a/sql/updates/auth/2012_03_28_00_auth_account.sql b/sql/updates/auth/2012_03_28_00_auth_account.sql new file mode 100644 index 00000000000..199baac5178 --- /dev/null +++ b/sql/updates/auth/2012_03_28_00_auth_account.sql @@ -0,0 +1,13 @@ +ALTER TABLE `account` + CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identifier', + CHANGE `sessionkey` `sessionkey` VARCHAR(80) NOT NULL DEFAULT '', + CHANGE `v` `v` VARCHAR(64) NOT NULL DEFAULT '', + CHANGE `s` `s` VARCHAR(64) NOT NULL DEFAULT '', + CHANGE `email` `email` VARCHAR(254) NOT NULL DEFAULT '', + CHANGE `last_ip` `last_ip` VARCHAR(15) NOT NULL DEFAULT '127.0.0.1', + CHANGE `failed_logins` `failed_logins` INT(10) UNSIGNED NOT NULL DEFAULT '0', + CHANGE `online` `online` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', + CHANGE `mutetime` `mutetime` BIGINT(20) NOT NULL DEFAULT '0', + CHANGE `os` `os` VARCHAR(3) NOT NULL DEFAULT '', + CHANGE `recruiter` `recruiter` INT(10) UNSIGNED NOT NULL DEFAULT '0', + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_01_auth_account_access.sql b/sql/updates/auth/2012_03_28_01_auth_account_access.sql new file mode 100644 index 00000000000..456dbf6c104 --- /dev/null +++ b/sql/updates/auth/2012_03_28_01_auth_account_access.sql @@ -0,0 +1,3 @@ +ALTER TABLE `account_access` + CHANGE `id` `id` INT(10) UNSIGNED NOT NULL, + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_02_auth_account_banned.sql b/sql/updates/auth/2012_03_28_02_auth_account_banned.sql new file mode 100644 index 00000000000..917cea1a684 --- /dev/null +++ b/sql/updates/auth/2012_03_28_02_auth_account_banned.sql @@ -0,0 +1,4 @@ +ALTER TABLE `account_banned` + CHANGE `id` `id` INT(10) UNSIGNED DEFAULT '0' NOT NULL COMMENT 'Account id', + CHANGE `active` `active` TINYINT(3) UNSIGNED DEFAULT '1' NOT NULL, + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_03_auth_ip_banned.sql b/sql/updates/auth/2012_03_28_03_auth_ip_banned.sql new file mode 100644 index 00000000000..2e754effc60 --- /dev/null +++ b/sql/updates/auth/2012_03_28_03_auth_ip_banned.sql @@ -0,0 +1,2 @@ +ALTER TABLE `ip_banned` + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_04_auth_logs.sql b/sql/updates/auth/2012_03_28_04_auth_logs.sql new file mode 100644 index 00000000000..4c837a47ed1 --- /dev/null +++ b/sql/updates/auth/2012_03_28_04_auth_logs.sql @@ -0,0 +1,5 @@ +ALTER TABLE `logs` + CHANGE `time` `time` INT(10) UNSIGNED NOT NULL, + CHANGE `realm` `realm` INT(10) UNSIGNED NOT NULL, + CHANGE `type` `type` TINYINT(3) UNSIGNED NOT NULL, + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_05_auth_realmcharacters.sql b/sql/updates/auth/2012_03_28_05_auth_realmcharacters.sql new file mode 100644 index 00000000000..e50b3c3f331 --- /dev/null +++ b/sql/updates/auth/2012_03_28_05_auth_realmcharacters.sql @@ -0,0 +1,4 @@ +ALTER TABLE `realmcharacters` + CHANGE `realmid` `realmid` INT(10) UNSIGNED DEFAULT 0 NOT NULL, + CHANGE `acctid` `acctid` INT(10) UNSIGNED NOT NULL, + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_06_auth_realmlist.sql b/sql/updates/auth/2012_03_28_06_auth_realmlist.sql new file mode 100644 index 00000000000..725702a9803 --- /dev/null +++ b/sql/updates/auth/2012_03_28_06_auth_realmlist.sql @@ -0,0 +1,6 @@ +ALTER TABLE `realmlist` + CHANGE `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + CHANGE `address` `address` VARCHAR(15) DEFAULT '127.0.0.1' NOT NULL, + CHANGE `port` `port` SMALLINT(5) UNSIGNED DEFAULT '8085' NOT NULL, + CHANGE `gamebuild` `gamebuild` INT(10) UNSIGNED DEFAULT '12340' NOT NULL, + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/auth/2012_03_28_07_auth_uptime.sql b/sql/updates/auth/2012_03_28_07_auth_uptime.sql new file mode 100644 index 00000000000..0ea4e3321df --- /dev/null +++ b/sql/updates/auth/2012_03_28_07_auth_uptime.sql @@ -0,0 +1,6 @@ +ALTER TABLE `uptime` + DROP COLUMN `startstring`, + CHANGE `realmid` `realmid` INT(10) UNSIGNED NOT NULL, + CHANGE `starttime` `starttime` INT(10) UNSIGNED DEFAULT 0 NOT NULL, + CHANGE `uptime` `uptime` INT(10) UNSIGNED DEFAULT 0 NOT NULL, + ROW_FORMAT=DEFAULT ENGINE=INNODB; diff --git a/sql/updates/characters/2012_03_01_00_characters_character_queststatus.sql b/sql/updates/characters/2012_03_01_00_characters_character_queststatus.sql deleted file mode 100644 index da3a0f786b8..00000000000 --- a/sql/updates/characters/2012_03_01_00_characters_character_queststatus.sql +++ /dev/null @@ -1 +0,0 @@ -DELETE FROM `character_queststatus` WHERE `status`=0; |