From 009317d2a0c1bdea7a7a1e40a7ecbe7a31c4dcc3 Mon Sep 17 00:00:00 2001 From: Nay Date: Sun, 28 Jul 2013 16:54:13 +0100 Subject: SQLS/Old: Move some misplaced files --- .../2012_12_18_00_characters_worldstates.sql | 4 + .../world/2012_12_18_00_characters_worldstates.sql | 4 - .../auth/2013_01_27_00_auth_realmlist.sql | 3 + .../auth/2013_02_04_00_auth_misc.sql | 179 +++++++++++++++++++++ .../auth/2013_02_04_01_auth_account.sql | 3 + .../auth/2013_02_05_00_auth_account.sql | 2 + .../auth/2013_02_07_00_auth_account.sql | 3 + .../auth/2013_02_08_00_auth_account.sql | 1 + .../world/2013_01_27_00_auth_realmlist.sql | 3 - .../world/2013_02_04_00_auth_misc.sql | 179 --------------------- .../world/2013_02_04_01_auth_account.sql | 3 - .../world/2013_02_05_00_auth_account.sql | 2 - .../world/2013_02_07_00_auth_account.sql | 3 - .../world/2013_02_08_00_auth_account.sql | 1 - 14 files changed, 195 insertions(+), 195 deletions(-) create mode 100644 sql/old/3.3.5a/TDB49_to_TDB50_updates/characters/2012_12_18_00_characters_worldstates.sql delete mode 100644 sql/old/3.3.5a/TDB49_to_TDB50_updates/world/2012_12_18_00_characters_worldstates.sql create mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_01_27_00_auth_realmlist.sql create mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_00_auth_misc.sql create mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_01_auth_account.sql create mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_05_00_auth_account.sql create mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_07_00_auth_account.sql create mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_08_00_auth_account.sql delete mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_01_27_00_auth_realmlist.sql delete mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_00_auth_misc.sql delete mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_01_auth_account.sql delete mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_05_00_auth_account.sql delete mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_07_00_auth_account.sql delete mode 100644 sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_08_00_auth_account.sql (limited to 'sql/old') diff --git a/sql/old/3.3.5a/TDB49_to_TDB50_updates/characters/2012_12_18_00_characters_worldstates.sql b/sql/old/3.3.5a/TDB49_to_TDB50_updates/characters/2012_12_18_00_characters_worldstates.sql new file mode 100644 index 00000000000..98fabde6776 --- /dev/null +++ b/sql/old/3.3.5a/TDB49_to_TDB50_updates/characters/2012_12_18_00_characters_worldstates.sql @@ -0,0 +1,4 @@ +DELETE FROM `worldstates` WHERE `entry` IN (20004,20007); +INSERT INTO `worldstates` (`entry`, `value`, `comment`) VALUES +(20004,0,'CleaningFlags'), +(20007,0,'NextMonthlyQuestResetTime'); diff --git a/sql/old/3.3.5a/TDB49_to_TDB50_updates/world/2012_12_18_00_characters_worldstates.sql b/sql/old/3.3.5a/TDB49_to_TDB50_updates/world/2012_12_18_00_characters_worldstates.sql deleted file mode 100644 index 98fabde6776..00000000000 --- a/sql/old/3.3.5a/TDB49_to_TDB50_updates/world/2012_12_18_00_characters_worldstates.sql +++ /dev/null @@ -1,4 +0,0 @@ -DELETE FROM `worldstates` WHERE `entry` IN (20004,20007); -INSERT INTO `worldstates` (`entry`, `value`, `comment`) VALUES -(20004,0,'CleaningFlags'), -(20007,0,'NextMonthlyQuestResetTime'); diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_01_27_00_auth_realmlist.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_01_27_00_auth_realmlist.sql new file mode 100644 index 00000000000..0c3b18448e6 --- /dev/null +++ b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_01_27_00_auth_realmlist.sql @@ -0,0 +1,3 @@ +ALTER TABLE `realmlist` + ADD `localAddress` varchar(255) NOT NULL DEFAULT '127.0.0.1' AFTER `address`, + ADD `localSubnetMask` varchar(255) NOT NULL DEFAULT '255.255.255.0' AFTER `localAddress`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_00_auth_misc.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_00_auth_misc.sql new file mode 100644 index 00000000000..d8a508e4e7b --- /dev/null +++ b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_00_auth_misc.sql @@ -0,0 +1,179 @@ +-- Explicitly set the account-table to use INNODB-engine (to allow foreign keys and transactions) +ALTER TABLE account ENGINE=InnoDB; + +-- Delete bad data from the DB before adding foreign keys +DELETE FROM `account_access` WHERE `id` NOT IN (SELECT `id` FROM `account`); + +-- Need them first in case of re-execute due to foreign keys +DROP TABLE IF EXISTS `rbac_account_permissions`; +DROP TABLE IF EXISTS `rbac_account_roles`; +DROP TABLE IF EXISTS `rbac_account_groups`; +DROP TABLE IF EXISTS `rbac_role_permissions`; +DROP TABLE IF EXISTS `rbac_group_roles`; +DROP TABLE IF EXISTS `rbac_security_level_groups`; +DROP TABLE IF EXISTS `rbac_permissions`; +DROP TABLE IF EXISTS `rbac_roles`; +DROP TABLE IF EXISTS `rbac_groups`; + +CREATE TABLE IF NOT EXISTS `rbac_groups` ( + `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Group id', + `name` varchar(50) NOT NULL COMMENT 'Group name', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Group List'; + +CREATE TABLE IF NOT EXISTS `rbac_roles` ( + `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Role id', + `name` varchar(50) NOT NULL COMMENT 'Role name', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Roles List'; + +CREATE TABLE IF NOT EXISTS `rbac_permissions` ( + `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Permission id', + `name` varchar(100) NOT NULL COMMENT 'Permission name', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Permission List'; + +CREATE TABLE IF NOT EXISTS `rbac_group_roles` ( + `groupId` int(10) unsigned NOT NULL COMMENT 'group id', + `roleId` int(10) unsigned NOT NULL COMMENT 'Role id', + PRIMARY KEY (`groupId`, `roleId`), + CONSTRAINT `fk__rbac_group_roles__rbac_roles` + FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT, + CONSTRAINT `fk__rbac_group_roles__rbac_groups` + FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Group Role relation'; + +CREATE TABLE IF NOT EXISTS `rbac_role_permissions` ( + `roleId` int(10) unsigned NOT NULL COMMENT 'Role id', + `permissionId` int(10) unsigned NOT NULL COMMENT 'Permission id', + PRIMARY KEY (`roleId`, `permissionId`), + CONSTRAINT `fk__role_permissions__rbac_roles` + FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT, + CONSTRAINT `fk__role_permissions__rbac_permissions` + FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role Permission relation'; + +CREATE TABLE IF NOT EXISTS `rbac_security_level_groups` ( + `secId` int(10) unsigned NOT NULL COMMENT 'Security Level id', + `groupId` int(10) unsigned NOT NULL COMMENT 'group id', + PRIMARY KEY (`secId`, `groupId`), + CONSTRAINT `fk__rbac_security_level_groups__rbac_groups` + FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Default groups to assign when an account is set gm level'; + +CREATE TABLE IF NOT EXISTS `rbac_account_groups` ( + `accountId` int(10) unsigned NOT NULL COMMENT 'Account id', + `groupId` int(10) unsigned NOT NULL COMMENT 'Group id', + `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all', + PRIMARY KEY (`accountId`, `groupId`, `realmId`), + CONSTRAINT `fk__rbac_account_groups__account` + FOREIGN KEY (`accountId`) REFERENCES `account`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT, + CONSTRAINT `fk__rbac_account_groups__rbac_groups` + FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Group relation'; + +CREATE TABLE IF NOT EXISTS `rbac_account_roles` ( + `accountId` int(10) unsigned NOT NULL COMMENT 'Account id', + `roleId` int(10) unsigned NOT NULL COMMENT 'Role id', + `granted` tinyint(1) NOT NULL default 1 COMMENT 'Granted = 1, Denied = 0', + `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all', + PRIMARY KEY (`accountId`, `roleId`, `realmId`), + CONSTRAINT `fk__rbac_account_roles__account` + FOREIGN KEY (`accountId`) REFERENCES `account`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT, + CONSTRAINT `fk__rbac_account_roles__rbac_roles` + FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Role relation'; + +CREATE TABLE IF NOT EXISTS `rbac_account_permissions` ( + `accountId` int(10) unsigned NOT NULL COMMENT 'Account id', + `permissionId` int(10) unsigned NOT NULL COMMENT 'Permission id', + `granted` tinyint(1) NOT NULL default 1 COMMENT 'Granted = 1, Denied = 0', + `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all', + PRIMARY KEY (`accountId`, `permissionId`, `realmId`), + CONSTRAINT `fk__rbac_account_permissions__account` + FOREIGN KEY (`accountId`) REFERENCES `account`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT, + CONSTRAINT `fk__rbac_account_roles__rbac_permissions` + FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions`(`id`) + ON DELETE CASCADE ON UPDATE RESTRICT +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Permission relation'; + +DELETE FROM `rbac_permissions` WHERE `id` BETWEEN 1 AND 10; +INSERT INTO `rbac_permissions` (`id`, `name`) VALUES +(1, 'Instant logout'), +(2, 'Skip Queue'), +(3, 'Join Normal Battleground'), +(4, 'Join Random Battleground'), +(5, 'Join Arenas'), +(6, 'Join Dungeon Finder'), +(7, 'Player Commands (Temporal till commands moved to rbac)'), +(8, 'Moderator Commands (Temporal till commands moved to rbac)'), +(9, 'GameMaster Commands (Temporal till commands moved to rbac)'), +(10, 'Administrator Commands (Temporal till commands moved to rbac)'); + +DELETE FROM `rbac_roles` WHERE `id` BETWEEN 1 AND 7; +INSERT INTO `rbac_roles` (`id`, `name`) VALUES +(1, 'Player Commands'), +(2, 'Moderator Commands'), +(3, 'GameMaster Commands'), +(4, 'Administrator Commands'), +(5, 'Quick Login/Logout'), +(6, 'Use Battleground/Arenas'), +(7, 'Use Dungeon Finder'); + +DELETE FROM `rbac_groups` WHERE `id` BETWEEN 1 AND 4; +INSERT INTO `rbac_groups` (`id`, `name`) VALUES +(1, 'Player'), +(2, 'Moderator'), +(3, 'GameMaster'), +(4, 'Administrator'); + +DELETE FROM `rbac_role_permissions` WHERE `roleId` BETWEEN 1 AND 7; +INSERT INTO `rbac_role_permissions` (`roleId`, `permissionId`) VALUES +(5, 1), +(5, 2), +(6, 3), +(6, 4), +(6, 5), +(7, 6), +(1, 7), +(2, 8), +(3, 9), +(4, 10); + +DELETE FROM `rbac_group_roles` WHERE `groupId` BETWEEN 1 AND 4; +INSERT INTO `rbac_group_roles` (`groupId`, `roleId`) VALUES +(1, 1), +(1, 6), +(1, 7), +(2, 2), +(2, 5), +(3, 3), +(4, 4); + +TRUNCATE `rbac_account_groups`; +INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 1, -1 FROM `account`; -- Add Player group to all accounts +INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 2, `RealmID` FROM `account_access` WHERE `gmlevel` > 0; -- Add Moderator group to all Moderator or higher GM level +INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 3, `RealmID` FROM `account_access` WHERE `gmlevel` > 1; -- Add GameMaster group to all GameMasters or higher GM level +INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 4, `RealmID` FROM `account_access` WHERE `gmlevel` > 2; -- Add Administrator group to all Administrators + +TRUNCATE `rbac_security_level_groups`; +INSERT INTO `rbac_security_level_groups` (`secId`, `groupId`) VALUES +(0, 1), +(1, 1), +(1, 2), +(2, 1), +(2, 2), +(2, 3), +(3, 1), +(3, 2), +(3, 3), +(3, 4); diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_01_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_01_auth_account.sql new file mode 100644 index 00000000000..9af73d41ee3 --- /dev/null +++ b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_04_01_auth_account.sql @@ -0,0 +1,3 @@ +ALTER TABLE `account` + ADD COLUMN `mutereason` VARCHAR(255) NOT NULL DEFAULT '' AFTER `mutetime`, + ADD COLUMN `muteby` VARCHAR(50) NOT NULL DEFAULT '' AFTER `mutereason`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_05_00_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_05_00_auth_account.sql new file mode 100644 index 00000000000..2f32c5e3e3e --- /dev/null +++ b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_05_00_auth_account.sql @@ -0,0 +1,2 @@ +-- Explicitly set the account-table to use INNODB-engine (to allow foreign keys and transactions) +ALTER TABLE account ENGINE=InnoDB; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_07_00_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_07_00_auth_account.sql new file mode 100644 index 00000000000..03bdf8cdcd5 --- /dev/null +++ b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_07_00_auth_account.sql @@ -0,0 +1,3 @@ +UPDATE `account` SET `sessionkey`=''; +ALTER TABLE `account` +CHANGE `sessionkey` `sessionkey` varchar(80) NOT NULL DEFAULT '' COMMENT 'Temporary storage of session key used to pass data from authserver to worldserver' AFTER `sha_pass_hash`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_08_00_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_08_00_auth_account.sql new file mode 100644 index 00000000000..49948781444 --- /dev/null +++ b/sql/old/3.3.5a/TDB50_to_TDB51_updates/auth/2013_02_08_00_auth_account.sql @@ -0,0 +1 @@ +ALTER TABLE `account` CHANGE `sessionkey` `sessionkey` varchar(80) NOT NULL DEFAULT '' AFTER `sha_pass_hash`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_01_27_00_auth_realmlist.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_01_27_00_auth_realmlist.sql deleted file mode 100644 index 0c3b18448e6..00000000000 --- a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_01_27_00_auth_realmlist.sql +++ /dev/null @@ -1,3 +0,0 @@ -ALTER TABLE `realmlist` - ADD `localAddress` varchar(255) NOT NULL DEFAULT '127.0.0.1' AFTER `address`, - ADD `localSubnetMask` varchar(255) NOT NULL DEFAULT '255.255.255.0' AFTER `localAddress`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_00_auth_misc.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_00_auth_misc.sql deleted file mode 100644 index d8a508e4e7b..00000000000 --- a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_00_auth_misc.sql +++ /dev/null @@ -1,179 +0,0 @@ --- Explicitly set the account-table to use INNODB-engine (to allow foreign keys and transactions) -ALTER TABLE account ENGINE=InnoDB; - --- Delete bad data from the DB before adding foreign keys -DELETE FROM `account_access` WHERE `id` NOT IN (SELECT `id` FROM `account`); - --- Need them first in case of re-execute due to foreign keys -DROP TABLE IF EXISTS `rbac_account_permissions`; -DROP TABLE IF EXISTS `rbac_account_roles`; -DROP TABLE IF EXISTS `rbac_account_groups`; -DROP TABLE IF EXISTS `rbac_role_permissions`; -DROP TABLE IF EXISTS `rbac_group_roles`; -DROP TABLE IF EXISTS `rbac_security_level_groups`; -DROP TABLE IF EXISTS `rbac_permissions`; -DROP TABLE IF EXISTS `rbac_roles`; -DROP TABLE IF EXISTS `rbac_groups`; - -CREATE TABLE IF NOT EXISTS `rbac_groups` ( - `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Group id', - `name` varchar(50) NOT NULL COMMENT 'Group name', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Group List'; - -CREATE TABLE IF NOT EXISTS `rbac_roles` ( - `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Role id', - `name` varchar(50) NOT NULL COMMENT 'Role name', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Roles List'; - -CREATE TABLE IF NOT EXISTS `rbac_permissions` ( - `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Permission id', - `name` varchar(100) NOT NULL COMMENT 'Permission name', - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Permission List'; - -CREATE TABLE IF NOT EXISTS `rbac_group_roles` ( - `groupId` int(10) unsigned NOT NULL COMMENT 'group id', - `roleId` int(10) unsigned NOT NULL COMMENT 'Role id', - PRIMARY KEY (`groupId`, `roleId`), - CONSTRAINT `fk__rbac_group_roles__rbac_roles` - FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT, - CONSTRAINT `fk__rbac_group_roles__rbac_groups` - FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Group Role relation'; - -CREATE TABLE IF NOT EXISTS `rbac_role_permissions` ( - `roleId` int(10) unsigned NOT NULL COMMENT 'Role id', - `permissionId` int(10) unsigned NOT NULL COMMENT 'Permission id', - PRIMARY KEY (`roleId`, `permissionId`), - CONSTRAINT `fk__role_permissions__rbac_roles` - FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT, - CONSTRAINT `fk__role_permissions__rbac_permissions` - FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role Permission relation'; - -CREATE TABLE IF NOT EXISTS `rbac_security_level_groups` ( - `secId` int(10) unsigned NOT NULL COMMENT 'Security Level id', - `groupId` int(10) unsigned NOT NULL COMMENT 'group id', - PRIMARY KEY (`secId`, `groupId`), - CONSTRAINT `fk__rbac_security_level_groups__rbac_groups` - FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Default groups to assign when an account is set gm level'; - -CREATE TABLE IF NOT EXISTS `rbac_account_groups` ( - `accountId` int(10) unsigned NOT NULL COMMENT 'Account id', - `groupId` int(10) unsigned NOT NULL COMMENT 'Group id', - `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all', - PRIMARY KEY (`accountId`, `groupId`, `realmId`), - CONSTRAINT `fk__rbac_account_groups__account` - FOREIGN KEY (`accountId`) REFERENCES `account`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT, - CONSTRAINT `fk__rbac_account_groups__rbac_groups` - FOREIGN KEY (`groupId`) REFERENCES `rbac_groups`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Group relation'; - -CREATE TABLE IF NOT EXISTS `rbac_account_roles` ( - `accountId` int(10) unsigned NOT NULL COMMENT 'Account id', - `roleId` int(10) unsigned NOT NULL COMMENT 'Role id', - `granted` tinyint(1) NOT NULL default 1 COMMENT 'Granted = 1, Denied = 0', - `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all', - PRIMARY KEY (`accountId`, `roleId`, `realmId`), - CONSTRAINT `fk__rbac_account_roles__account` - FOREIGN KEY (`accountId`) REFERENCES `account`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT, - CONSTRAINT `fk__rbac_account_roles__rbac_roles` - FOREIGN KEY (`roleId`) REFERENCES `rbac_roles`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Role relation'; - -CREATE TABLE IF NOT EXISTS `rbac_account_permissions` ( - `accountId` int(10) unsigned NOT NULL COMMENT 'Account id', - `permissionId` int(10) unsigned NOT NULL COMMENT 'Permission id', - `granted` tinyint(1) NOT NULL default 1 COMMENT 'Granted = 1, Denied = 0', - `realmId` int(11) NOT NULL DEFAULT '-1' COMMENT 'Realm Id, -1 means all', - PRIMARY KEY (`accountId`, `permissionId`, `realmId`), - CONSTRAINT `fk__rbac_account_permissions__account` - FOREIGN KEY (`accountId`) REFERENCES `account`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT, - CONSTRAINT `fk__rbac_account_roles__rbac_permissions` - FOREIGN KEY (`permissionId`) REFERENCES `rbac_permissions`(`id`) - ON DELETE CASCADE ON UPDATE RESTRICT -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Account-Permission relation'; - -DELETE FROM `rbac_permissions` WHERE `id` BETWEEN 1 AND 10; -INSERT INTO `rbac_permissions` (`id`, `name`) VALUES -(1, 'Instant logout'), -(2, 'Skip Queue'), -(3, 'Join Normal Battleground'), -(4, 'Join Random Battleground'), -(5, 'Join Arenas'), -(6, 'Join Dungeon Finder'), -(7, 'Player Commands (Temporal till commands moved to rbac)'), -(8, 'Moderator Commands (Temporal till commands moved to rbac)'), -(9, 'GameMaster Commands (Temporal till commands moved to rbac)'), -(10, 'Administrator Commands (Temporal till commands moved to rbac)'); - -DELETE FROM `rbac_roles` WHERE `id` BETWEEN 1 AND 7; -INSERT INTO `rbac_roles` (`id`, `name`) VALUES -(1, 'Player Commands'), -(2, 'Moderator Commands'), -(3, 'GameMaster Commands'), -(4, 'Administrator Commands'), -(5, 'Quick Login/Logout'), -(6, 'Use Battleground/Arenas'), -(7, 'Use Dungeon Finder'); - -DELETE FROM `rbac_groups` WHERE `id` BETWEEN 1 AND 4; -INSERT INTO `rbac_groups` (`id`, `name`) VALUES -(1, 'Player'), -(2, 'Moderator'), -(3, 'GameMaster'), -(4, 'Administrator'); - -DELETE FROM `rbac_role_permissions` WHERE `roleId` BETWEEN 1 AND 7; -INSERT INTO `rbac_role_permissions` (`roleId`, `permissionId`) VALUES -(5, 1), -(5, 2), -(6, 3), -(6, 4), -(6, 5), -(7, 6), -(1, 7), -(2, 8), -(3, 9), -(4, 10); - -DELETE FROM `rbac_group_roles` WHERE `groupId` BETWEEN 1 AND 4; -INSERT INTO `rbac_group_roles` (`groupId`, `roleId`) VALUES -(1, 1), -(1, 6), -(1, 7), -(2, 2), -(2, 5), -(3, 3), -(4, 4); - -TRUNCATE `rbac_account_groups`; -INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 1, -1 FROM `account`; -- Add Player group to all accounts -INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 2, `RealmID` FROM `account_access` WHERE `gmlevel` > 0; -- Add Moderator group to all Moderator or higher GM level -INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 3, `RealmID` FROM `account_access` WHERE `gmlevel` > 1; -- Add GameMaster group to all GameMasters or higher GM level -INSERT INTO `rbac_account_groups` (`accountId`, `groupId`, `realmId`) SELECT `id`, 4, `RealmID` FROM `account_access` WHERE `gmlevel` > 2; -- Add Administrator group to all Administrators - -TRUNCATE `rbac_security_level_groups`; -INSERT INTO `rbac_security_level_groups` (`secId`, `groupId`) VALUES -(0, 1), -(1, 1), -(1, 2), -(2, 1), -(2, 2), -(2, 3), -(3, 1), -(3, 2), -(3, 3), -(3, 4); diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_01_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_01_auth_account.sql deleted file mode 100644 index 9af73d41ee3..00000000000 --- a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_04_01_auth_account.sql +++ /dev/null @@ -1,3 +0,0 @@ -ALTER TABLE `account` - ADD COLUMN `mutereason` VARCHAR(255) NOT NULL DEFAULT '' AFTER `mutetime`, - ADD COLUMN `muteby` VARCHAR(50) NOT NULL DEFAULT '' AFTER `mutereason`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_05_00_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_05_00_auth_account.sql deleted file mode 100644 index 2f32c5e3e3e..00000000000 --- a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_05_00_auth_account.sql +++ /dev/null @@ -1,2 +0,0 @@ --- Explicitly set the account-table to use INNODB-engine (to allow foreign keys and transactions) -ALTER TABLE account ENGINE=InnoDB; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_07_00_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_07_00_auth_account.sql deleted file mode 100644 index 03bdf8cdcd5..00000000000 --- a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_07_00_auth_account.sql +++ /dev/null @@ -1,3 +0,0 @@ -UPDATE `account` SET `sessionkey`=''; -ALTER TABLE `account` -CHANGE `sessionkey` `sessionkey` varchar(80) NOT NULL DEFAULT '' COMMENT 'Temporary storage of session key used to pass data from authserver to worldserver' AFTER `sha_pass_hash`; diff --git a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_08_00_auth_account.sql b/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_08_00_auth_account.sql deleted file mode 100644 index 49948781444..00000000000 --- a/sql/old/3.3.5a/TDB50_to_TDB51_updates/world/2013_02_08_00_auth_account.sql +++ /dev/null @@ -1 +0,0 @@ -ALTER TABLE `account` CHANGE `sessionkey` `sessionkey` varchar(80) NOT NULL DEFAULT '' AFTER `sha_pass_hash`; -- cgit v1.2.3