From bcf214dfc4e1b09bd694233ed1db96b55557c0da Mon Sep 17 00:00:00 2001 From: toyomoyo Date: Thu, 10 Aug 2006 08:20:16 +0000 Subject: [PATCH] adding keys for new tables, added migration support too --- lib/db/migrate2utf8.xml | 48 +++++++++++++++++++++++++++++++++++++++++ lib/db/mysql.php | 36 ++++++++++++++++++++++++++++--- lib/db/mysql.sql | 19 ++++++++++++++++ lib/db/postgres7.php | 22 +++++++++++++++++++ lib/db/postgres7.sql | 35 +++++++++++++++++++++++------- 5 files changed, 149 insertions(+), 11 deletions(-) diff --git a/lib/db/migrate2utf8.xml b/lib/db/migrate2utf8.xml index 8a48ae532f..df8ad789c8 100755 --- a/lib/db/migrate2utf8.xml +++ b/lib/db/migrate2utf8.xml @@ -8,6 +8,54 @@ + + + + + + migrate2utf8_role_name(RECORDID) + + + + + migrate2utf8_role_description(RECORDID) + + + +
+ + +
+
+ + + +
+ + + + + +
+ + + + + + + +
+ + + + + + migrate2utf8_role_names_text(RECORDID) + + + +
+
diff --git a/lib/db/mysql.php b/lib/db/mysql.php index c14228879e..b610b609c4 100644 --- a/lib/db/mysql.php +++ b/lib/db/mysql.php @@ -2002,7 +2002,7 @@ function main_upgrade($oldversion=0) { } if ($oldversion < 2006080400) { - execute_sql("CREATE TABLE {$CFG->prefix}role ( + execute_sql("CREATE TABLE {$CFG->prefix}role ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `description` text NOT NULL default '', @@ -2010,7 +2010,7 @@ function main_upgrade($oldversion=0) { PRIMARY KEY (`id`) )", true); - execute_sql("CREATE TABLE {$CFG->prefix}context ( + execute_sql("CREATE TABLE {$CFG->prefix}context ( `id` int(10) unsigned NOT NULL auto_increment, `level` int(10) unsigned NOT NULL default '0', `instanceid` int(10) unsigned NOT NULL default '0', @@ -2064,12 +2064,42 @@ function main_upgrade($oldversion=0) { `roleid` int(10) unsigned NOT NULL default '0', `contextid` int(10) unsigned NOT NULL default '0', `text` text NOT NULL default '', + KEY `roleid` (`roleid`), + KEY `contextid` (`roleid`), + UNIQUE KEY `roleid-contextid` (`roleid`, `contextid`), PRIMARY KEY (`id`) )", true); } - + if ($oldversion < 2006081000) { + + execute_sql("ALTER TABLE `{$CFG->prefix}role` ADD INDEX `sortorder` (`sortorder`)",true); + + execute_sql("ALTER TABLE `{$CFG->prefix}context` ADD INDEX `instanceid` (`instanceid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}context` ADD UNIQUE INDEX `level-instanceid` (`level`, `instanceid`)",true); + + execute_sql("ALTER TABLE `{$CFG->prefix}role_assignments` ADD INDEX `roleid` (`roleid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_assignments` ADD INDEX `contextid` (`contextid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_assignments` ADD INDEX `userid` (`userid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_assignments` ADD UNIQUE INDEX `contextid-roleid-userid` (`contextid`, `roleid`, `userid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_assignments` ADD INDEX `sortorder` (`sortorder`)",true); + + execute_sql("ALTER TABLE `{$CFG->prefix}role_capabilities` ADD INDEX `roleid` (`roleid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_capabilities` ADD INDEX `contextid` (`contextid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_capabilities` ADD INDEX `modifierid` (`modifierid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_capabilities` ADD UNIQUE INDEX `roleid-contextid-capability` (`roleid`, `contextid`, `capability`)",true); + + execute_sql("ALTER TABLE `{$CFG->prefix}role_deny_grant` ADD INDEX `roleid` (`roleid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_deny_grant` ADD INDEX `unviewableroleid` (`unviewableroleid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_deny_grant` ADD UNIQUE INDEX `roleid-unviewableroleid` (`roleid`, `unviewableroleid`)",true); + + execute_sql("ALTER TABLE `{$CFG->prefix}capabilities` ADD INDEX `name` (`name`)",true); + + execute_sql("ALTER TABLE `{$CFG->prefix}role_names` ADD INDEX `roleid` (`roleid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_names` ADD INDEX `contextid` (`contextid`)",true); + execute_sql("ALTER TABLE `{$CFG->prefix}role_names` ADD UNIQUE INDEX `roleid-contextid` (`roleid`, `contextid`)",true); + } return $result; } diff --git a/lib/db/mysql.sql b/lib/db/mysql.sql index 44e630a959..0657b86eef 100644 --- a/lib/db/mysql.sql +++ b/lib/db/mysql.sql @@ -908,6 +908,7 @@ CREATE TABLE prefix_role ( `name` varchar(255) NOT NULL default '', `description` text NOT NULL default '', `sortorder` int(10) unsigned NOT NULL default '0', + KEY `sortorder` (`sortorder`), PRIMARY KEY (`id`) ) TYPE=MyISAM COMMENT ='moodle roles'; @@ -915,6 +916,8 @@ CREATE TABLE prefix_context ( `id` int(10) unsigned NOT NULL auto_increment, `level` int(10) unsigned NOT NULL default '0', `instanceid` int(10) unsigned NOT NULL default '0', + KEY `instanceid` (`instanceid`), + UNIQUE KEY `level-instanceid` (`level`, `instanceid`), PRIMARY KEY (`id`) ) TYPE=MyISAM COMMENT ='one of these must be set'; @@ -930,6 +933,11 @@ CREATE TABLE prefix_role_assignments ( `modifierid` int(10) unsigned NOT NULL default '0', `enrol` varchar(20) NOT NULL default '', `sortorder` int(10) unsigned NOT NULL default '0', + KEY `roleid` (`roleid`), + KEY `contextid` (`contextid`), + KEY `userid` (`userid`), + UNIQUE KEY `contextid-roleid-userid` (`contextid`, `roleid`, `userid`), + KEY `sortorder` (`sortorder`), PRIMARY KEY (`id`) ) TYPE=MyISAM COMMENT ='assigning roles to different context'; @@ -941,6 +949,10 @@ CREATE TABLE prefix_role_capabilities ( `permission` int(10) unsigned NOT NULL default '0', `timemodified` int(10) unsigned NOT NULL default '0', `modifierid` int(10) unsigned NOT NULL default '0', + KEY `roleid` (`roleid`), + KEY `contextid` (`contextid`), + KEY `modifierid` (`modifierid`), + UNIQUE KEY `roleid-contextid-capability` (`roleid`, `contextid`, `capability`), PRIMARY KEY (`id`) ) TYPE=MYISAM COMMENT ='overriding a capability for a particular role in a particular context'; @@ -948,6 +960,9 @@ CREATE TABLE prefix_role_deny_grant ( `id` int(10) unsigned NOT NULL auto_increment, `roleid` int(10) unsigned NOT NULL default '0', `unviewableroleid` int(10) unsigned NOT NULL default '0', + KEY `roleid` (`roleid`), + KEY `unviewableroleid` (`unviewableroleid`), + UNIQUE KEY `roleid-unviewableroleid` (`roleid`, `unviewableroleid`), PRIMARY KEY (`id`) ) TYPE=MYISAM COMMENT ='this defines what role can touch (assign, override) what role'; @@ -957,6 +972,7 @@ CREATE TABLE prefix_capabilities ( `captype` varchar(50) NOT NULL default '', `contextlevel` int(10) unsigned NOT NULL default '0', `component` varchar(100) NOT NULL default '', + KEY `name` (`name`), PRIMARY KEY (`id`) ) TYPE=MYISAM COMMENT ='this defines all capabilities'; @@ -965,6 +981,9 @@ CREATE TABLE prefix_role_names ( `roleid` int(10) unsigned NOT NULL default '0', `contextid` int(10) unsigned NOT NULL default '0', `text` text NOT NULL default '', + KEY `roleid` (`roleid`), + KEY `contextid` (`contextid`), + UNIQUE KEY `roleid-contextid` (`roleid`, `contextid`), PRIMARY KEY (`id`) ) TYPE=MYISAM COMMENT ='role names in native strings'; diff --git a/lib/db/postgres7.php b/lib/db/postgres7.php index d5cbecebfb..c33ce8706a 100644 --- a/lib/db/postgres7.php +++ b/lib/db/postgres7.php @@ -1679,6 +1679,28 @@ function main_upgrade($oldversion=0) { );"); } + + if ($oldversion < 2006081000) { + modify_database('',"CREATE INDEX prefix_role_sortorder_idx ON prefix_role (sortorder);"); + modify_database('',"CREATE INDEX prefix_context_instanceid_idx ON prefix_context (instanceid);"); + modify_database('',"CREATE UNIQUE INDEX prefix_context_levelinstanceid_idx ON prefix_context (level, instanceid);"); + modify_database('',"CREATE INDEX prefix_role_assignments_roleid_idx ON prefix_role_assignments (roleid);"); + modify_database('',"CREATE INDEX prefix_role_assignments_contextidid_idx ON prefix_role_assignments (contextid);"); + modify_database('',"CREATE INDEX prefix_role_assignments_userid_idx ON prefix_role_assignments (userid);"); + modify_database('',"CREATE UNIQUE INDEX prefix_role_assignments_contextidroleiduserid_idx ON prefix_role_assignments (contextid, roleid, userid);"); + modify_database('',"CREATE INDEX prefix_role_assignments_sortorder_idx ON prefix_role_assignments (sortorder);"); + modify_database('',"CREATE INDEX prefix_role_capabilities_roleid_idx ON prefix_role_capabilities (roleid);"); + modify_database('',"CREATE INDEX prefix_role_capabilities_contextid_idx ON prefix_role_capabilities (contextid);"); + modify_database('',"CREATE INDEX prefix_role_capabilities_modifierid_idx ON prefix_role_capabilities (modifierid);"); + modify_database('',"CREATE UNIQUE INDEX prefix_role_capabilities_roleidcontextidcapability_idx ON prefix_role_capabilities (roleid, contextid, capability);"); + modify_database('',"CREATE INDEX prefix_role_deny_grant_roleid_idx ON prefix_role_deny_grant (roleid);"); + modify_database('',"CREATE INDEX prefix_role_deny_grant_unviewableroleid_idx ON prefix_role_deny_grant (unviewableroleid);"); + modify_database('',"CREATE UNIQUE INDEX prefix_role_deny_grant_roleidunviewableroleid_idx ON prefix_role_deny_grant (roleid, unviewableroleid);"); + modify_database('',"CREATE INDEX prefix_capabilities_name_idx ON prefix_capabilities (name);"); + modify_database('',"CREATE INDEX prefix_role_names_roleid_idx ON prefix_role_names (roleid);"); + modify_database('',"CREATE INDEX prefix_role_names_contextid_idx ON prefix_role_names (contextid);"); + modify_database('',"CREATE UNIQUE INDEX prefix_role_names_roleidcontextid_idx ON prefix_role_names (roleid, contextid);"); + } return $result; } diff --git a/lib/db/postgres7.sql b/lib/db/postgres7.sql index 89fbab8bb6..c31ede6288 100644 --- a/lib/db/postgres7.sql +++ b/lib/db/postgres7.sql @@ -677,12 +677,15 @@ CREATE TABLE prefix_role ( description text NOT NULL default '', sortorder integer NOT NULL default '0' ); +CREATE INDEX prefix_role_sortorder_idx ON prefix_role (sortorder); CREATE TABLE prefix_context ( id SERIAL PRIMARY KEY, level integer NOT NULL default 0, instanceid integer NOT NULL default 0 -); +); +CREATE INDEX prefix_context_instanceid_idx ON prefix_context (instanceid); +CREATE UNIQUE INDEX prefix_context_levelinstanceid_idx ON prefix_context (level, instanceid); CREATE TABLE prefix_role_assignments ( id SERIAL PRIMARY KEY, @@ -696,8 +699,13 @@ CREATE TABLE prefix_role_assignments ( modifierid integer NOT NULL default 0, enrol varchar(20) NOT NULL default '', sortorder integer NOT NULL default '0' -); - +); +CREATE INDEX prefix_role_assignments_roleid_idx ON prefix_role_assignments (roleid); +CREATE INDEX prefix_role_assignments_contextidid_idx ON prefix_role_assignments (contextid); +CREATE INDEX prefix_role_assignments_userid_idx ON prefix_role_assignments (userid); +CREATE UNIQUE INDEX prefix_role_assignments_contextidroleiduserid_idx ON prefix_role_assignments (contextid, roleid, userid); +CREATE INDEX prefix_role_assignments_sortorder_idx ON prefix_role_assignments (sortorder); + CREATE TABLE prefix_role_capabilities ( id SERIAL PRIMARY KEY, contextid integer NOT NULL default 0, @@ -707,28 +715,39 @@ CREATE TABLE prefix_role_capabilities ( timemodified integer NOT NULL default 0, modifierid integer NOT NULL default 0 ); - +CREATE INDEX prefix_role_capabilities_roleid_idx ON prefix_role_capabilities (roleid); +CREATE INDEX prefix_role_capabilities_contextid_idx ON prefix_role_capabilities (contextid); +CREATE INDEX prefix_role_capabilities_modifierid_idx ON prefix_role_capabilities (modifierid); +CREATE UNIQUE INDEX prefix_role_capabilities_roleidcontextidcapability_idx ON prefix_role_capabilities (roleid, contextid, capability); + CREATE TABLE prefix_role_deny_grant ( id SERIAL PRIMARY KEY, roleid integer NOT NULL default '0', unviewableroleid integer NOT NULL default '0' ); - +CREATE INDEX prefix_role_deny_grant_roleid_idx ON prefix_role_deny_grant (roleid); +CREATE INDEX prefix_role_deny_grant_unviewableroleid_idx ON prefix_role_deny_grant (unviewableroleid); +CREATE UNIQUE INDEX prefix_role_deny_grant_roleidunviewableroleid_idx ON prefix_role_deny_grant (roleid, unviewableroleid); + CREATE TABLE prefix_capabilities ( id SERIAL PRIMARY KEY, name varchar(150) NOT NULL default '', captype varchar(50) NOT NULL default '', contextlevel integer NOT NULL default 0, component varchar(100) NOT NULL default '' -); - +); +CREATE INDEX prefix_capabilities_name_idx ON prefix_capabilities (name); + CREATE TABLE prefix_role_names ( id SERIAL PRIMARY KEY, roleid integer NOT NULL default 0, contextid integer NOT NULL default 0, text text NOT NULL default '' ); - +CREATE INDEX prefix_role_names_roleid_idx ON prefix_role_names (roleid); +CREATE INDEX prefix_role_names_contextid_idx ON prefix_role_names (contextid); +CREATE UNIQUE INDEX prefix_role_names_roleidcontextid_idx ON prefix_role_names (roleid, contextid); + INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('user', 'view', 'user', 'firstname||\' \'||lastname'); INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('course', 'user report', 'user', 'firstname||\' \'||lastname'); INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('course', 'view', 'course', 'fullname'); -- 2.39.5