From 1ad49e00d9a5e665ffb77c78235bf627d574780d Mon Sep 17 00:00:00 2001 From: mjollnir_ Date: Thu, 18 Nov 2004 04:38:52 +0000 Subject: [PATCH] Merged from MOODlE_14_STABLE: Indexes for glossary and version bump --- mod/glossary/db/mysql.php | 29 +++++++++++++++++++++++++++++ mod/glossary/db/mysql.sql | 27 +++++++++++++++++++-------- mod/glossary/db/postgres7.php | 28 ++++++++++++++++++++++++++++ mod/glossary/db/postgres7.sql | 20 ++++++++++++++++++++ mod/glossary/version.php | 2 +- 5 files changed, 97 insertions(+), 9 deletions(-) diff --git a/mod/glossary/db/mysql.php b/mod/glossary/db/mysql.php index 73695fdeef..830e6949bc 100644 --- a/mod/glossary/db/mysql.php +++ b/mod/glossary/db/mysql.php @@ -344,6 +344,35 @@ function glossary_upgrade($oldversion) { if ( $oldversion < 2004080900) { set_field('glossary','editalways','1','mainglossary','0'); } + + if ($oldversion < 2004111200) { + execute_sql("ALTER TABLE {$CFG->prefix}glossary DROP INDEX course;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_alias DROP INDEX entryid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_categories DROP INDEX glossaryid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_comments DROP INDEX entryid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_comments DROP INDEX userid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_entries DROP INDEX glossaryid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_entries DROP INDEX userid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_entries DROP INDEX concept;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_entries_categories DROP INDEX entryid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_entries_categories DROP INDEX categoryid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_ratings DROP INDEX userid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}glossary_ratings DROP INDEX entryid;",false); + + modify_database('','ALTER TABLE prefix_glossary ADD INDEX course (course);'); + modify_database('','ALTER TABLE prefix_glossary_alias ADD INDEX entryid (entryid);'); + modify_database('','ALTER TABLE prefix_glossary_categories ADD INDEX glossaryid (glossaryid);'); + modify_database('','ALTER TABLE prefix_glossary_comments ADD INDEX entryid (entryid);'); + modify_database('','ALTER TABLE prefix_glossary_comments ADD INDEX userid (userid);'); + modify_database('','ALTER TABLE prefix_glossary_entries ADD INDEX glossaryid (glossaryid);'); + modify_database('','ALTER TABLE prefix_glossary_entries ADD INDEX userid (userid);'); + modify_database('','ALTER TABLE prefix_glossary_entries ADD INDEX concept (concept);'); + modify_database('','ALTER TABLE prefix_glossary_entries_categories ADD INDEX entryid (entryid);'); + modify_database('','ALTER TABLE prefix_glossary_entries_categories ADD INDEX categoryid (categoryid);'); + modify_database('','ALTER TABLE prefix_glossary_ratings ADD INDEX userid (userid);'); + modify_database('','ALTER TABLE prefix_glossary_ratings ADD INDEX entryid (entryid);'); + + } return true; } diff --git a/mod/glossary/db/mysql.sql b/mod/glossary/db/mysql.sql index 404a51b220..e4091ec770 100644 --- a/mod/glossary/db/mysql.sql +++ b/mod/glossary/db/mysql.sql @@ -34,7 +34,8 @@ CREATE TABLE prefix_glossary ( scale int(10) NOT NULL default '0', timecreated int(10) unsigned NOT NULL default '0', timemodified int(10) unsigned NOT NULL default '0', - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY course (course) ) TYPE=MyISAM COMMENT='all glossaries'; # @@ -57,7 +58,10 @@ CREATE TABLE prefix_glossary_entries ( casesensitive tinyint(2) unsigned NOT NULL default '0', fullmatch tinyint(2) unsigned NOT NULL default '1', approved tinyint(2) unsigned NOT NULL default '1', - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY glossaryid (glossaryid), + KEY userid (userid), + KEY concept (concept) ) TYPE=MyISAM COMMENT='all glossary entries'; # @@ -68,7 +72,8 @@ CREATE TABLE prefix_glossary_alias ( id int(10) unsigned NOT NULL auto_increment, entryid int(10) unsigned NOT NULL default '0', alias varchar(255) NOT NULL, - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY entryid (entryid) ) TYPE=MyISAM COMMENT='entries alias'; # @@ -80,7 +85,8 @@ CREATE TABLE prefix_glossary_categories ( glossaryid int(10) unsigned NOT NULL default '0', name varchar(255) NOT NULL default '', usedynalink tinyint(2) unsigned NOT NULL default '1', - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY glossaryid (glossaryid) ) TYPE=MyISAM COMMENT='all categories for glossary entries'; # @@ -91,7 +97,9 @@ CREATE TABLE prefix_glossary_entries_categories ( id int(10) unsigned NOT NULL auto_increment, categoryid int(10) unsigned NOT NULL default '0', entryid int(10) unsigned NOT NULL default '0', - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY entryid (entryid), + KEY categoryid (categoryid) ) TYPE=MyISAM COMMENT='categories of each glossary entry'; CREATE TABLE prefix_glossary_comments ( @@ -101,8 +109,9 @@ CREATE TABLE prefix_glossary_comments ( comment text NOT NULL, format tinyint(2) unsigned NOT NULL default '0', timemodified int(10) unsigned NOT NULL default '0', - - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY userid (userid), + KEY entryid (entryid) ) TYPE=MyISAM COMMENT='comments on glossary entries'; CREATE TABLE prefix_glossary_formats ( @@ -128,7 +137,9 @@ CREATE TABLE prefix_glossary_ratings ( entryid int(10) unsigned NOT NULL default '0', time int(10) unsigned NOT NULL default '0', rating tinyint(4) NOT NULL default '0', - PRIMARY KEY (id) + PRIMARY KEY (id), + KEY userid (userid), + KEY entryid (entryid) ) COMMENT='Contains user ratings for entries'; # -------------------------------------------------------- diff --git a/mod/glossary/db/postgres7.php b/mod/glossary/db/postgres7.php index c846dbe456..88e053565f 100644 --- a/mod/glossary/db/postgres7.php +++ b/mod/glossary/db/postgres7.php @@ -106,6 +106,34 @@ function glossary_upgrade($oldversion) { set_field('glossary','editalways','1','mainglossary','0'); } + if ($oldversion < 2004111200) { + execute_sql("DROP INDEX {$CFG->prefix}glossary_course_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_alias_entryid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_categories_glossaryid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_comments_entryid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_comments_userid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_entries_glossaryid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_entries_userid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_entries_concept_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_entries_categories_category_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_entries_categories_entryid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_ratings_userid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}glossary_ratings_entryid_idx;",false); + + modify_database('','CREATE INDEX prefix_glossary_course_idx ON prefix_glossary (course);'); + modify_database('','CREATE INDEX prefix_glossary_alias_entryid_idx ON prefix_glossary_alias (entryid);'); + modify_database('','CREATE INDEX prefix_glossary_categories_glossaryid_idx ON prefix_glossary_categories (glossaryid);'); + modify_database('','CREATE INDEX prefix_glossary_comments_entryid_idx ON prefix_glossary_comments (entryid);'); + modify_database('','CREATE INDEX prefix_glossary_comments_userid_idx ON prefix_glossary_comments (userid);'); + modify_database('','CREATE INDEX prefix_glossary_entries_glossaryid_idx ON prefix_glossary_entries (glossaryid);'); + modify_database('','CREATE INDEX prefix_glossary_entries_userid_idx ON prefix_glossary_entries (userid);'); + modify_database('','CREATE INDEX prefix_glossary_entries_concept_idx ON prefix_glossary_entries (concept);'); + modify_database('','CREATE INDEX prefix_glossary_entries_categories_category_idx ON prefix_glossary_entries_categories (categoryid);'); + modify_database('','CREATE INDEX prefix_glossary_entries_categories_entryid_idx ON prefix_glossary_entries_categories (entryid);'); + modify_database('','CREATE INDEX prefix_glossary_ratings_userid_idx ON prefix_glossary_ratings (userid);'); + modify_database('','CREATE INDEX prefix_glossary_ratings_entryid_idx ON prefix_glossary_ratings (entryid);'); + } + return true; } diff --git a/mod/glossary/db/postgres7.sql b/mod/glossary/db/postgres7.sql index 3840cef6d3..580a3b8a7b 100644 --- a/mod/glossary/db/postgres7.sql +++ b/mod/glossary/db/postgres7.sql @@ -37,6 +37,9 @@ CREATE TABLE prefix_glossary ( PRIMARY KEY (id) ); +CREATE INDEX prefix_glossary_course_idx ON prefix_glossary (course); + + # # Table structure for table `glossary_entries` # @@ -60,6 +63,10 @@ CREATE TABLE prefix_glossary_entries ( PRIMARY KEY(id) ); +CREATE INDEX prefix_glossary_entries_glossaryid_idx ON prefix_glossary_entries (glossaryid); +CREATE INDEX prefix_glossary_entries_userid_idx ON prefix_glossary_entries (userid); +CREATE INDEX prefix_glossary_entries_concept_idx ON prefix_glossary_entries (concept); + # # Table structure for table `glossary_cageories` # @@ -72,6 +79,8 @@ CREATE TABLE prefix_glossary_categories ( PRIMARY KEY (id) ); +CREATE INDEX prefix_glossary_categories_glossaryid_idx ON prefix_glossary_categories (glossaryid); + # # Table structure for table `glossary_alias` # @@ -83,6 +92,8 @@ CREATE TABLE prefix_glossary_alias ( PRIMARY KEY (id) ); +CREATE INDEX prefix_glossary_alias_entryid_idx ON prefix_glossary_alias (entryid); + # # Table structure for table `glossary_entries_category` # @@ -94,6 +105,9 @@ CREATE TABLE prefix_glossary_entries_categories ( PRIMARY KEY (id) ); +CREATE INDEX prefix_glossary_entries_categories_category_idx ON prefix_glossary_entries_categories (categoryid); +CREATE INDEX prefix_glossary_entries_categories_entryid_idx ON prefix_glossary_entries_categories (entryid); + # # Table structure for table `glossary_comments` # @@ -108,6 +122,9 @@ CREATE TABLE prefix_glossary_comments ( PRIMARY KEY (id) ); +CREATE INDEX prefix_glossary_comments_entryid_idx ON prefix_glossary_comments (entryid); +CREATE INDEX prefix_glossary_comments_userid_idx ON prefix_glossary_comments (userid); + # # Table structure for table `glossary_formats` # @@ -139,6 +156,9 @@ CREATE TABLE prefix_glossary_ratings ( PRIMARY KEY (id) ); +CREATE INDEX prefix_glossary_ratings_userid_idx ON prefix_glossary_ratings (userid); +CREATE INDEX prefix_glossary_ratings_entryid_idx ON prefix_glossary_ratings (entryid); + # # Dumping data for table `log_display` # diff --git a/mod/glossary/version.php b/mod/glossary/version.php index 2fcb05005f..6428102adf 100644 --- a/mod/glossary/version.php +++ b/mod/glossary/version.php @@ -5,7 +5,7 @@ /// This fragment is called by moodle_needs_upgrading() and /admin/index.php ///////////////////////////////////////////////////////////////////////////////// -$module->version = 2004111000; +$module->version = 2004111200; $module->requires = 2004091700; // Requires this Moodle version $module->cron = 0; // Period for cron to check this module (secs) -- 2.39.5