From ffa5f71b4bebc64ba676233440d4cb7035894f59 Mon Sep 17 00:00:00 2001 From: mjollnir_ Date: Wed, 17 Nov 2004 07:47:03 +0000 Subject: [PATCH] Merged from MOODLE_14_STABLE: Add a unique key to guarantee that course,sortorder is unique. Allows fix_course_sortorder and ordering operations to be much faster. (martinlanghoff) Plus more indexes for user table (mjollnir_) arch-eduforge@catalyst.net.nz--2004/moodle--eduforge--1.3.3--patch-231 arch-eduforge@catalyst.net.nz--2004/moodle--eduforge--1.3.3--patch-236 --- lib/db/mysql.php | 31 +++++++++++++++++++++++++++++++ lib/db/mysql.sql | 10 +++++++++- lib/db/postgres7.php | 31 ++++++++++++++++++++++++++++++- lib/db/postgres7.sql | 8 ++++++++ 4 files changed, 78 insertions(+), 2 deletions(-) diff --git a/lib/db/mysql.php b/lib/db/mysql.php index ebcac41ba2..40ff6d6483 100644 --- a/lib/db/mysql.php +++ b/lib/db/mysql.php @@ -939,6 +939,37 @@ function main_upgrade($oldversion=0) { modify_database('','ALTER TABLE prefix_user_admins ADD INDEX userid (userid);'); modify_database('','ALTER TABLE prefix_user_coursecreators ADD INDEX userid (userid);'); } + + if ($oldversion < 2004111700) { // replace index on course + fix_course_sortorder(0,0,1); + execute_sql("ALTER TABLE `prefix_course` DROP KEY category",false); + + execute_sql("ALTER TABLE `prefix_course` DROP KEY category_sortorder;",false); + modify_database('', "ALTER TABLE `prefix_course` ADD UNIQUE KEY category_sortorder(category,sortorder)"); + + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_deleted_idx;",false); + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_confirmed_idx;",false); + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_firstname_idx;",false); + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_lastname_idx;",false); + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_city_idx;",false); + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_country_idx;",false); + execute_sql("ALTER TABLE `prefix_user` DROP INDEX prefix_user_lastaccess_idx;",false); + + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_deleted_idx (deleted)"); + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_confirmed_idx (confirmed)"); + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_firstname_idx (firstname)"); + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_lastname_idx (lastname)"); + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_city_idx (city)"); + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_country_idx (country)"); + modify_database("", "ALTER TABLE `prefix_user` ADD INDEX prefix_user_lastaccess_idx (lastaccess)"); + } + + if ($oldversion < 2004111700) { // one more index for email (for sorting) + execute_sql('ALTER TABLE `prefix_user` DROP INDEX prefix_user_email_idx;',false); + modify_database('','ALTER TABLE `prefix_user` ADD INDEX prefix_user_email_idx (email);'); + } + + return $result; diff --git a/lib/db/mysql.sql b/lib/db/mysql.sql index 340c8b793c..09f2daf0da 100644 --- a/lib/db/mysql.sql +++ b/lib/db/mysql.sql @@ -369,7 +369,15 @@ CREATE TABLE `prefix_user` ( `timemodified` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), - UNIQUE KEY `username` (`username`) + UNIQUE KEY `username` (`username`), + KEY `user_deleted` (`deleted`), + KEY `user_confirmed` (`confirmed`), + KEY `user_firstname` (`firstname`), + KEY `user_lastname` (`lastname`), + KEY `user_city` (`city`), + KEY `user_country` (`country`), + KEY `user_lastaccess` (`lastaccess`), + KEY `user_email` (`email`) ) TYPE=MyISAM COMMENT='One record for each person'; ALTER TABLE `prefix_user` ADD INDEX `auth` (`auth`); diff --git a/lib/db/postgres7.php b/lib/db/postgres7.php index cdd3f6726d..61929150bb 100644 --- a/lib/db/postgres7.php +++ b/lib/db/postgres7.php @@ -680,7 +680,36 @@ function main_upgrade($oldversion=0) { modify_database('','CREATE INDEX prefix_user_coursecreators_userid_idx ON prefix_user_coursecreators (userid);'); } - + if ($oldversion < 2004111700) { // make new indexes on user table. + fix_course_sortorder(0,0,1); + + execute_sql("DROP INDEX prefix_course_category_idx;",false); + execute_sql("DROP INDEX prefix_course_category_sortorder_uk;",false); + modify_database('', "CREATE UNIQUE INDEX prefix_course_category_sortorder_uk ON prefix_course(category,sortorder)"); + + execute_sql("DROP INDEX prefix_user_deleted_idx;",false); + execute_sql("DROP INDEX prefix_user_confirmed_idx;",false); + execute_sql("DROP INDEX prefix_user_firstname_idx;",false); + execute_sql("DROP INDEX prefix_user_lastname_idx;",false); + execute_sql("DROP INDEX prefix_user_city_idx;",false); + execute_sql("DROP INDEX prefix_user_country_idx;",false); + execute_sql("DROP INDEX prefix_user_lastaccess_idx;",false); + + modify_database("","CREATE INDEX prefix_user_deleted_idx ON prefix_user (deleted)"); + modify_database("","CREATE INDEX prefix_user_confirmed_idx ON prefix_user (confirmed)"); + modify_database("","CREATE INDEX prefix_user_firstname_idx ON prefix_user (firstname)"); + modify_database("","CREATE INDEX prefix_user_lastname_idx ON prefix_user (lastname)"); + modify_database("","CREATE INDEX prefix_user_city_idx ON prefix_user (city)"); + modify_database("","CREATE INDEX prefix_user_country_idx ON prefix_user (country)"); + modify_database("","CREATE INDEX prefix_user_lastaccess_idx ON prefix_user (lastaccess)"); + } + + if ($oldversion < 2004111700) { // one more index for email (for sorting) + execute_sql('DROP INDEX prefix_user_email_idx;',false); + + modify_database('','CREATE INDEX prefix_user_email_idx ON prefix_user (email);'); + } + return $result; } diff --git a/lib/db/postgres7.sql b/lib/db/postgres7.sql index 78fdde1f95..ebc0b9c526 100644 --- a/lib/db/postgres7.sql +++ b/lib/db/postgres7.sql @@ -261,6 +261,14 @@ CREATE TABLE prefix_user ( CREATE INDEX prefix_user_idnumber_idx ON prefix_user (idnumber); CREATE INDEX prefix_user_auth_idx ON prefix_user (auth); +CREATE INDEX prefix_user_deleted_idx ON prefix_user (deleted); +CREATE INDEX prefix_user_confirmed_idx ON prefix_user (confirmed); +CREATE INDEX prefix_user_firstname_idx ON prefix_user (firstname); +CREATE INDEX prefix_user_lastname_idx ON prefix_user (lastname); +CREATE INDEX prefix_user_city_idx ON prefix_user (city); +CREATE INDEX prefix_user_country_idx ON prefix_user (country); +CREATE INDEX prefix_user_lastaccess_idx ON prefix_user (lastaccess); +CREATE INDEX prefix_user_email_idx ON prefix_user (email); CREATE TABLE prefix_user_admins ( id SERIAL PRIMARY KEY, -- 2.39.5