From 2171c83396cf259e5ee41c58658710771e64b0ce Mon Sep 17 00:00:00 2001 From: mjollnir_ Date: Thu, 18 Nov 2004 01:57:04 +0000 Subject: [PATCH] Merged from MOODLE_14_STABLE: Indexes for exercise and version bump --- mod/exercise/db/mysql.php | 16 ++++++++++++++++ mod/exercise/db/mysql.sql | 18 ++++++++++++------ mod/exercise/db/postgres7.php | 14 ++++++++++++++ mod/exercise/db/postgres7.sql | 11 +++++++++++ mod/exercise/version.php | 2 +- 5 files changed, 54 insertions(+), 7 deletions(-) diff --git a/mod/exercise/db/mysql.php b/mod/exercise/db/mysql.php index 86b1d26575..fccbabc302 100644 --- a/mod/exercise/db/mysql.php +++ b/mod/exercise/db/mysql.php @@ -40,6 +40,22 @@ function exercise_upgrade($oldversion) { exercise_refresh_events(); } + if ($oldversion < 2004111200) { + execute_sql('ALTER TABLE prefix_exercise DROP INDEX course;',false); + execute_sql('ALTER TABLE prefix_exercise_submissions DROP INDEX exerciseid;',false); + execute_sql('ALTER TABLE prefix_exercise_assessments DROP INDEX exerciseid;',false); + execute_sql('ALTER TABLE prefix_exercise_elements DROP INDEX exerciseid;',false); + execute_sql('ALTER TABLE prefix_exercise_rubrics DROP INDEX exerciseid;',false); + execute_sql('ALTER TABLE prefix_exercise_grades DROP INDEX exerciseid;',false); + + modify_database('','ALTER TABLE prefix_exercise ADD INDEX course (course);'); + modify_database('','ALTER TABLE prefix_exercise_submissions ADD INDEX exerciseid (exerciseid);'); + modify_database('','ALTER TABLE prefix_exercise_assessments ADD INDEX exerciseid (exerciseid);'); + modify_database('','ALTER TABLE prefix_exercise_elements ADD INDEX exerciseid (exerciseid);'); + modify_database('','ALTER TABLE prefix_exercise_rubrics ADD INDEX exerciseid (exerciseid);'); + modify_database('','ALTER TABLE prefix_exercise_grades ADD INDEX exerciseid (exerciseid);'); + } + return true; } diff --git a/mod/exercise/db/mysql.sql b/mod/exercise/db/mysql.sql index d15b518903..27fd5c91cc 100644 --- a/mod/exercise/db/mysql.sql +++ b/mod/exercise/db/mysql.sql @@ -20,7 +20,8 @@ CREATE TABLE `prefix_exercise` ( `showleaguetable` tinyint(3) unsigned NOT NULL default '0', `usepassword` tinyint(3) unsigned NOT NULL default '0', `password` varchar(32) NOT NULL default '', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `course` (`course`) ) COMMENT='Defines exercise'; # -------------------------------------------------------- @@ -39,7 +40,8 @@ CREATE TABLE `prefix_exercise_submissions` ( `isexercise` tinyint(3) unsigned NOT NULL default '0', `late` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`id`), - INDEX `userid` (`userid`) + INDEX `userid` (`userid`), + INDEX `exerciseid` (`exerciseid`) ) COMMENT='Info about submitted work from teacher and students'; # -------------------------------------------------------- @@ -61,7 +63,8 @@ CREATE TABLE `prefix_exercise_assessments` ( `teachercomment` text NOT NULL default '', PRIMARY KEY (`id`), INDEX (`submissionid`), - INDEX (`userid`) + INDEX (`userid`), + INDEX (`exerciseid`) ) COMMENT='Info about assessments by teacher and students'; # -------------------------------------------------------- @@ -77,7 +80,8 @@ CREATE TABLE `prefix_exercise_elements` ( `scale` tinyint(3) unsigned NOT NULL default '0', `maxscore` tinyint(3) unsigned NOT NULL default '1', `weight` tinyint(3) unsigned NOT NULL default '11', - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `exerciseid` (`exerciseid`) ) COMMENT='Info about marking scheme of assignment'; # -------------------------------------------------------- @@ -92,7 +96,8 @@ CREATE TABLE `prefix_exercise_rubrics` ( `elementno` int(10) unsigned NOT NULL default '0', `rubricno` tinyint(3) unsigned NOT NULL default '0', `description` text NOT NULL, - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + KEY `exerciseid` (`exerciseid`) ) COMMENT='Info about the rubrics marking scheme'; # -------------------------------------------------------- @@ -108,7 +113,8 @@ CREATE TABLE `prefix_exercise_grades` ( `feedback` text NOT NULL default '', `grade` tinyint(3) NOT NULL default '0', PRIMARY KEY (`id`), - INDEX (`assessmentid`) + INDEX (`assessmentid`), + INDEX `exerciseid` (`exerciseid`) ) COMMENT='Info about individual grades given to each element'; # -------------------------------------------------------- diff --git a/mod/exercise/db/postgres7.php b/mod/exercise/db/postgres7.php index 55a63691fc..a12881fc33 100644 --- a/mod/exercise/db/postgres7.php +++ b/mod/exercise/db/postgres7.php @@ -30,6 +30,20 @@ function exercise_upgrade($oldversion) { exercise_refresh_events(); } + if ($oldversion < 2004111200) { + execute_sql('DROP INDEX prefix_exercise_course_idx;',false); + execute_sql('DROP INDEX prefix_exercise_submissions_exerciseid_idx;',false); + execute_sql('DROP INDEX prefix_exercise_assessments_exerciseid_idx;',false); + execute_sql('DROP INDEX prefix_exercise_rubrics_exerciseid_idx;',false); + execute_sql('DROP INDEX prefix_exercise_grades_exerciseid_idx;',false); + + modify_database('','CREATE INDEX prefix_exercise_course_idx ON prefix_exercise (course);'); + modify_database('','CREATE INDEX prefix_exercise_submissions_exerciseid_idx ON prefix_exercise_submissions (exerciseid);'); + modify_database('','CREATE INDEX prefix_exercise_assessments_exerciseid_idx ON prefix_exercise_assessments (exerciseid);'); + modify_database('','CREATE INDEX prefix_exercise_rubrics_exerciseid_idx ON prefix_exercise_rubrics (exerciseid);'); + modify_database('','CREATE INDEX prefix_exercise_grades_exerciseid_idx ON prefix_exercise_grades (exerciseid);'); + } + return true; } diff --git a/mod/exercise/db/postgres7.sql b/mod/exercise/db/postgres7.sql index 0483624902..6c5c7e077d 100644 --- a/mod/exercise/db/postgres7.sql +++ b/mod/exercise/db/postgres7.sql @@ -22,6 +22,9 @@ CREATE TABLE prefix_exercise ( password VARCHAR(32) NOT NULL default '' ); + +CREATE INDEX prefix_exercise_course_idx ON prefix_exercise (course); + # -------------------------------------------------------- # @@ -39,6 +42,8 @@ CREATE TABLE prefix_exercise_submissions ( isexercise INT NOT NULL default '0' ); CREATE INDEX prefix_exercise_submissions_userid_idx ON prefix_exercise_submissions (userid); +CREATE INDEX prefix_exercise_submissions_exerciseid_idx ON prefix_exercise_submissions (exerciseid); + # -------------------------------------------------------- # @@ -61,6 +66,7 @@ CREATE TABLE prefix_exercise_assessments ( # -------------------------------------------------------- CREATE INDEX prefix_exercise_assessments_submissionid_idx ON prefix_exercise_assessments (submissionid); CREATE INDEX prefix_exercise_assessments_userid_idx ON prefix_exercise_assessments (userid); +CREATE INDEX prefix_exercise_assessments_exerciseid_idx ON prefix_exercise_assessments (exerciseid); # Table structure for table exercise_elements # @@ -88,6 +94,9 @@ CREATE TABLE prefix_exercise_rubrics ( rubricno INT NOT NULL default '0', description text NOT NULL ); + +CREATE INDEX prefix_exercise_rubrics_exerciseid_idx ON prefix_exercise_rubrics (exerciseid); + # -------------------------------------------------------- # @@ -104,6 +113,8 @@ CREATE TABLE prefix_exercise_grades ( ); CREATE INDEX prefix_exercise_grades_assessmentid_idx ON prefix_exercise_grades (assessmentid); +CREATE INDEX prefix_exercise_grades_exerciseid_idx ON prefix_exercise_grades (exerciseid); + # -------------------------------------------------------- diff --git a/mod/exercise/version.php b/mod/exercise/version.php index 63c196398f..41abc368e4 100644 --- a/mod/exercise/version.php +++ b/mod/exercise/version.php @@ -5,7 +5,7 @@ // This fragment is called by /admin/index.php //////////////////////////////////////////////////////////////////////////////// -$module->version = 2004100800; +$module->version = 2004111200; $module->requires = 2004091700; // Requires this Moodle version $module->cron = 60; -- 2.39.5