From 33a55a6d558e1e539cf8551d9c2d4f2b8c5733dc Mon Sep 17 00:00:00 2001 From: mjollnir_ Date: Fri, 19 Nov 2004 03:13:05 +0000 Subject: [PATCH] Merged from MOODLE_14_STABLE: Indexes for lesson and version bump (honoring partial freeze) --- mod/lesson/db/mysql.php | 19 +++++++++++++++++++ mod/lesson/db/mysql.sql | 17 ++++++++++++----- mod/lesson/db/postgres7.php | 26 ++++++++++++++++++++++++-- mod/lesson/db/postgres7.sql | 11 +++++++++++ mod/lesson/version.php | 2 +- 5 files changed, 67 insertions(+), 8 deletions(-) diff --git a/mod/lesson/db/mysql.php b/mod/lesson/db/mysql.php index 4adbc1e002..2d5232c35d 100644 --- a/mod/lesson/db/mysql.php +++ b/mod/lesson/db/mysql.php @@ -146,6 +146,25 @@ function lesson_upgrade($oldversion) { // CDC-FLAG end + if ($oldversion < 2004111200) { + execute_sql("ALTER TABLE {$CFG->prefix}lesson DROP INDEX course;",false); + execute_sql("ALTER TABLE {$CFG->prefix}lesson_answers DROP INDEX lessonid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}lesson_attempts DROP INDEX lessonid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}lesson_attempts DROP INDEX pageid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}lesson_grades DROP INDEX lessonid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}lesson_grades DROP INDEX userid;",false); + execute_sql("ALTER TABLE {$CFG->prefix}lesson_pages DROP INDEX lessonid;",false); + + modify_database('','ALTER TABLE prefix_lesson ADD INDEX course (course);'); + modify_database('','ALTER TABLE prefix_lesson_answers ADD INDEX lessonid (lessonid);'); + modify_database('','ALTER TABLE prefix_lesson_attempts ADD INDEX lessonid (lessonid);'); + modify_database('','ALTER TABLE prefix_lesson_attempts ADD INDEX pageid (pageid);'); + modify_database('','ALTER TABLE prefix_lesson_grades ADD INDEX lessonid (lessonid);'); + modify_database('','ALTER TABLE prefix_lesson_grades ADD INDEX userid (userid);'); + modify_database('','ALTER TABLE prefix_lesson_pages ADD INDEX lessonid (lessonid);'); + } + + return true; } diff --git a/mod/lesson/db/mysql.sql b/mod/lesson/db/mysql.sql index ddb39659fa..0a3bd1cd20 100644 --- a/mod/lesson/db/mysql.sql +++ b/mod/lesson/db/mysql.sql @@ -36,7 +36,8 @@ CREATE TABLE `prefix_lesson` ( `available` int(10) unsigned NOT NULL default '0', `deadline` int(10) unsigned NOT NULL default '0', `timemodified` int(10) unsigned NOT NULL default '0', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `course` (`course`) ) COMMENT='Defines lesson'; # -------------------------------------------------------- @@ -53,7 +54,8 @@ CREATE TABLE `prefix_lesson_pages` ( `timemodified` int(10) unsigned NOT NULL default '0', `title` varchar(255) NOT NULL default '', `contents` text NOT NULL default '', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `lessonid` (`lessonid`) ) COMMENT='Defines lesson_pages'; # -------------------------------------------------------- @@ -70,7 +72,8 @@ CREATE TABLE `prefix_lesson_answers` ( `answer` text NOT NULL default '', `response` text NOT NULL default '', PRIMARY KEY (`id`), - KEY (`pageid`) + KEY (`pageid`), + KEY `lessonid` (`lessonid`) ) COMMENT='Defines lesson_answers'; # -------------------------------------------------------- @@ -85,7 +88,9 @@ CREATE TABLE `prefix_lesson_attempts` ( `useranswer` text NOT NULL default '', `timeseen` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), - KEY (`userid`) + KEY (`userid`), + KEY `lessonid` (`lessonid`), + KEY `pageid` (`pageid`) ) COMMENT='Defines lesson_attempts'; # -------------------------------------------------------- @@ -96,7 +101,9 @@ CREATE TABLE `prefix_lesson_grades` ( `grade` int(3) unsigned NOT NULL default '0', `late` int(3) unsigned NOT NULL default '0', `completed` int(10) unsigned NOT NULL default '0', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `lessonid` (`lessonid`), + KEY `userid` (`userid`) ) COMMENT='Defines lesson_grades'; # -------------------------------------------------------- diff --git a/mod/lesson/db/postgres7.php b/mod/lesson/db/postgres7.php index 57df935ed8..7a1bf0e82c 100644 --- a/mod/lesson/db/postgres7.php +++ b/mod/lesson/db/postgres7.php @@ -189,8 +189,30 @@ function lesson_upgrade($oldversion) { //execute_sql(" ALTER TABLE `{$CFG->prefix}lesson_default` ADD `modattempts` tinyint(3) unsigned NOT NULL default '0' AFTER practice"); table_column('lesson_default', '', 'modattempts', 'INT', '4', 'unsigned', '0', 'NOT NULL', 'practice'); } - - return true; + + if ($oldversion < 2004111200) { + execute_sql("DROP INDEX {$CFG->prefix}lesson_course_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_answers_lessonid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_answers_pageid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_attempts_lessonid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_attempts_pageid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_attempts_userid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_grades_lessonid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_grades_userid_idx;",false); + execute_sql("DROP INDEX {$CFG->prefix}lesson_pages_lessonid_idx;",false); + + modify_database('','CREATE INDEX prefix_lesson_course_idx ON prefix_lesson (course);'); + modify_database('','CREATE INDEX prefix_lesson_answers_lessonid_idx ON prefix_lesson_answers (lessonid);'); + modify_database('','CREATE INDEX prefix_lesson_answers_pageid_idx ON prefix_lesson_answers (pageid);'); + modify_database('','CREATE INDEX prefix_lesson_attempts_lessonid_idx ON prefix_lesson_attempts (lessonid);'); + modify_database('','CREATE INDEX prefix_lesson_attempts_pageid_idx ON prefix_lesson_attempts (pageid);'); + modify_database('','CREATE INDEX prefix_lesson_attempts_userid_idx ON prefix_lesson_attempts (userid);'); + modify_database('','CREATE INDEX prefix_lesson_grades_lessonid_idx ON prefix_lesson_grades (lessonid);'); + modify_database('','CREATE INDEX prefix_lesson_grades_userid_idx ON prefix_lesson_grades (userid);'); + modify_database('','CREATE INDEX prefix_lesson_pages_lessonid_idx ON prefix_lesson_pages (lessonid);'); + } + + return true; } ?> diff --git a/mod/lesson/db/postgres7.sql b/mod/lesson/db/postgres7.sql index e6959cd3dd..c4ecbbac62 100644 --- a/mod/lesson/db/postgres7.sql +++ b/mod/lesson/db/postgres7.sql @@ -32,6 +32,8 @@ CREATE TABLE prefix_lesson ( timemodified INT8 NOT NULL default '0' ); +CREATE INDEX prefix_lesson_course_idx ON prefix_lesson (course); + CREATE TABLE prefix_lesson_pages ( id SERIAL8 PRIMARY KEY, lessonid INT8 NOT NULL default '0', @@ -47,6 +49,8 @@ CREATE TABLE prefix_lesson_pages ( contents text NOT NULL default '' ) ; +CREATE INDEX prefix_lesson_pages_lessonid_idx ON prefix_lesson_pages (lessonid); + CREATE TABLE prefix_lesson_answers ( id SERIAL8 PRIMARY KEY, lessonid INT8 NOT NULL default '0', @@ -60,7 +64,9 @@ CREATE TABLE prefix_lesson_answers ( answer text NOT NULL default '', response text NOT NULL default '' ) ; + CREATE INDEX prefix_lesson_answers_pageid_idx ON prefix_lesson_answers (pageid); +CREATE INDEX prefix_lesson_answers_lessonid_idx ON prefix_lesson_answers (lessonid); CREATE TABLE prefix_lesson_attempts ( id SERIAL8 PRIMARY KEY, @@ -73,6 +79,8 @@ CREATE TABLE prefix_lesson_attempts ( useranswer text NOT NULL default '', timeseen INT8 NOT NULL default '0' ) ; +CREATE INDEX prefix_lesson_attempts_lessonid_idx ON prefix_lesson_attempts (lessonid); +CREATE INDEX prefix_lesson_attempts_pageid_idx ON prefix_lesson_attempts (pageid); CREATE INDEX prefix_lesson_attempts_userid_idx ON prefix_lesson_attempts (userid); CREATE TABLE prefix_lesson_grades ( @@ -84,6 +92,9 @@ CREATE TABLE prefix_lesson_grades ( completed INT8 NOT NULL default '0' ) ; +CREATE INDEX prefix_lesson_grades_lessonid_idx ON prefix_lesson_grades (lessonid); +CREATE INDEX prefix_lesson_grades_userid_idx ON prefix_lesson_grades (userid); + CREATE TABLE prefix_lesson_default ( id SERIAL8 PRIMARY KEY, course INT8 NOT NULL default '0', diff --git a/mod/lesson/version.php b/mod/lesson/version.php index 43fdf76242..8d70d3e5c1 100644 --- a/mod/lesson/version.php +++ b/mod/lesson/version.php @@ -5,7 +5,7 @@ /// This fragment is called by moodle_needs_upgrading() and /admin/index.php ///////////////////////////////////////////////////////////////////////////////// -$module->version = 2004102600; // The current module version (Date: YYYYMMDDXX) +$module->version = 2004111200; // The current module version (Date: YYYYMMDDXX) $module->requires = 2004091700; // Requires this Moodle version $module->cron = 0; // Period for cron to check this module (secs) -- 2.39.5