diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/characters.sql | 21 | ||||
| -rw-r--r-- | sql/updates/5354_8402_characters_guild_bank_eventlog.sql | 23 | ||||
| -rw-r--r-- | sql/updates/5354_8402_characters_guild_eventlog.sql | 20 |
3 files changed, 54 insertions, 10 deletions
diff --git a/sql/characters.sql b/sql/characters.sql index ac92557d07d..870545cc065 100644 --- a/sql/characters.sql +++ b/sql/characters.sql @@ -23,7 +23,7 @@ DROP TABLE IF EXISTS `character_db_version`; CREATE TABLE `character_db_version` ( - `required_8397_03_characters_character_spell` bit(1) default NULL + `required_8402_02_characters_guild_bank_eventlog` bit(1) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Last applied sql update to DB'; -- @@ -1137,16 +1137,16 @@ UNLOCK TABLES; DROP TABLE IF EXISTS `guild_bank_eventlog`; CREATE TABLE `guild_bank_eventlog` ( - `guildid` int(11) unsigned NOT NULL default '0', - `LogGuid` int(11) unsigned NOT NULL default '0', - `LogEntry` tinyint(1) unsigned NOT NULL default '0', - `TabId` tinyint(1) unsigned NOT NULL default '0', + `guildid` int(11) unsigned NOT NULL default '0' COMMENT 'Guild Identificator', + `LogGuid` int(11) unsigned NOT NULL default '0' COMMENT 'Log record identificator - auxiliary column', + `TabId` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Guild bank TabId', + `EventType` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Event type', `PlayerGuid` int(11) unsigned NOT NULL default '0', `ItemOrMoney` int(11) unsigned NOT NULL default '0', `ItemStackCount` tinyint(3) unsigned NOT NULL default '0', - `DestTabId` tinyint(1) unsigned NOT NULL default '0', - `TimeStamp` bigint(20) unsigned NOT NULL default '0', - PRIMARY KEY (`guildid`,`LogGuid`), + `DestTabId` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Destination Tab Id', + `TimeStamp` bigint(20) unsigned NOT NULL default '0' COMMENT 'Event UNIX time', + PRIMARY KEY (`guildid`,`LogGuid`,`TabId`), KEY `guildid_key` (`guildid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -1238,12 +1238,13 @@ UNLOCK TABLES; DROP TABLE IF EXISTS `guild_eventlog`; CREATE TABLE `guild_eventlog` ( `guildid` int(11) NOT NULL COMMENT 'Guild Identificator', - `LogGuid` int(11) NOT NULL COMMENT 'Log entry identificator', + `LogGuid` int(11) NOT NULL COMMENT 'Log record identificator - auxiliary column', `EventType` tinyint(1) NOT NULL COMMENT 'Event type', `PlayerGuid1` int(11) NOT NULL COMMENT 'Player 1', `PlayerGuid2` int(11) NOT NULL COMMENT 'Player 2', `NewRank` tinyint(2) NOT NULL COMMENT 'New rank(in case promotion/demotion)', - `TimeStamp` bigint(20) NOT NULL COMMENT 'Event UNIX time' + `TimeStamp` bigint(20) NOT NULL COMMENT 'Event UNIX time', + PRIMARY KEY (`guildid`, `LogGuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'Guild Eventlog'; -- diff --git a/sql/updates/5354_8402_characters_guild_bank_eventlog.sql b/sql/updates/5354_8402_characters_guild_bank_eventlog.sql new file mode 100644 index 00000000000..deda8036003 --- /dev/null +++ b/sql/updates/5354_8402_characters_guild_bank_eventlog.sql @@ -0,0 +1,23 @@ +-- ALTER TABLE character_db_version CHANGE COLUMN required_8402_01_characters_guild_eventlog required_8402_02_characters_guild_bank_eventlog bit; + + +-- THIS SCRIPT DELETES table `guild_bank_eventlog` - MAKE BACKUP, if you need it. + +DROP TABLE IF EXISTS `guild_bank_eventlog`; +CREATE TABLE `guild_bank_eventlog` ( + `guildid` int(11) unsigned NOT NULL default '0' COMMENT 'Guild Identificator', + `LogGuid` int(11) unsigned NOT NULL default '0' COMMENT 'Log record identificator - auxiliary column', + `TabId` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Guild bank TabId', + `EventType` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Event type', + `PlayerGuid` int(11) unsigned NOT NULL default '0', + `ItemOrMoney` int(11) unsigned NOT NULL default '0', + `ItemStackCount` tinyint(3) unsigned NOT NULL default '0', + `DestTabId` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Destination Tab Id', + `TimeStamp` bigint(20) unsigned NOT NULL default '0' COMMENT 'Event UNIX time', + PRIMARY KEY (`guildid`,`LogGuid`,`TabId`), + KEY `guildid_key` (`guildid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +-- The reason i decided for such dramatic change is that old guild_bank_eventlog table used `TabId` = 0 for Money events and +-- used `LogGuid` from 0 to infinity +-- New system uses `LogGuid` from 0 to number defined in config.
\ No newline at end of file diff --git a/sql/updates/5354_8402_characters_guild_eventlog.sql b/sql/updates/5354_8402_characters_guild_eventlog.sql new file mode 100644 index 00000000000..71030c5a609 --- /dev/null +++ b/sql/updates/5354_8402_characters_guild_eventlog.sql @@ -0,0 +1,20 @@ +-- ALTER TABLE character_db_version CHANGE COLUMN required_8397_03_characters_character_spell required_8402_01_characters_guild_eventlog bit; + + +-- THIS SCRIPT DELETES table `guild_eventlog` - MAKE BACKUP, if you need it. + +DROP TABLE IF EXISTS `guild_eventlog`; +CREATE TABLE `guild_eventlog` ( + `guildid` int(11) NOT NULL COMMENT 'Guild Identificator', + `LogGuid` int(11) NOT NULL COMMENT 'Log record identificator - auxiliary column', + `EventType` tinyint(1) NOT NULL COMMENT 'Event type', + `PlayerGuid1` int(11) NOT NULL COMMENT 'Player 1', + `PlayerGuid2` int(11) NOT NULL COMMENT 'Player 2', + `NewRank` tinyint(2) NOT NULL COMMENT 'New rank(in case promotion/demotion)', + `TimeStamp` bigint(20) NOT NULL COMMENT 'Event UNIX time', + PRIMARY KEY (`guildid`, `LogGuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'Guild Eventlog'; + +-- The reason i decided for such dramatic change is that old guild_eventlog table didn't have Primary key and +-- used LogGuids from 0 to infinity +-- New system uses LogGuids from 0 to number defined in config.
\ No newline at end of file |