From 192e8f665045fc3d06d0b2568b0fc46edf8ce165 Mon Sep 17 00:00:00 2001 From: gbateson Date: Fri, 28 Apr 2006 00:56:14 +0000 Subject: [PATCH] remove orphans records, if any, from hotpot tables, reduce size of "score" and "weighting" fields in "hotpot_responses" table, and set default value on ALL string fields in HotPot tables. New version number is 2006042800 (v2.1.18) --- mod/hotpot/db/mysql.php | 37 ++++++++++-------------- mod/hotpot/db/mysql.sql | 4 +-- mod/hotpot/db/postgres7.php | 37 ++++++++++-------------- mod/hotpot/db/update_to_v2.php | 51 ++++++++++++++++++++++++++++++++-- mod/hotpot/version.php | 4 +-- 5 files changed, 82 insertions(+), 51 deletions(-) diff --git a/mod/hotpot/db/mysql.php b/mod/hotpot/db/mysql.php index 746d3e37aa..a867684f77 100644 --- a/mod/hotpot/db/mysql.php +++ b/mod/hotpot/db/mysql.php @@ -2,49 +2,42 @@ function hotpot_upgrade($oldversion) { global $CFG; $ok = true; + if ($oldversion < 2004021400) { execute_sql(" ALTER TABLE `{$CFG->prefix}hotpot_events` ADD `starttime` INT(10) unsigned NOT NULL DEFAULT '0' AFTER `time`"); execute_sql(" ALTER TABLE `{$CFG->prefix}hotpot_events` ADD `endtime` INT(10) unsigned NOT NULL DEFAULT '0' AFTER `time`"); } + + // set path to update functions + $update_to_v2 = "$CFG->dirroot/mod/hotpot/db/update_to_v2.php"; + // update from HotPot v1 to HotPot v2 if ($oldversion < 2005031400) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_from_v1(); } - // update to HotPot v2.1 if ($oldversion < 2005090700) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1(); } - // update to from HotPot v2.1.0 or v2.1.1 to HotPot v2.1.2 if ($oldversion > 2005031419 && $oldversion < 2005090702) { - $ok = $ok && hotpot_get_update_to_v2(); + // update to from HotPot v2.1.0 or v2.1.1 + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_2(); } - // update to HotPot v2.1.16 if ($oldversion < 2006042103) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_16(); } - - // update to HotPot v2.1.17 if ($oldversion < 2006042601) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_17(); } - - return $ok; -} -function hotpot_get_update_to_v2() { - global $CFG; - $filepath = "$CFG->dirroot/mod/hotpot/db/update_to_v2.php"; - if (file_exists($filepath) && is_readable($filepath)) { - include_once $filepath; - $ok = true; - } else { - $ok = false; + if ($oldversion < 2006042800) { + require_once $update_to_v2; + $ok = $ok && hotpot_update_to_v2_1_18(); } + return $ok; } - ?> diff --git a/mod/hotpot/db/mysql.sql b/mod/hotpot/db/mysql.sql index 635ba00b41..747c7d0d00 100644 --- a/mod/hotpot/db/mysql.sql +++ b/mod/hotpot/db/mysql.sql @@ -77,8 +77,8 @@ CREATE TABLE prefix_hotpot_responses ( id int(10) unsigned NOT NULL auto_increment, attempt int(10) unsigned NOT NULL default '0', question int(10) unsigned NOT NULL default '0', - score smallint(8) unsigned NOT NULL default '0', - weighting smallint(8) unsigned NOT NULL default '0', + score smallint(6) unsigned NOT NULL default '0', + weighting smallint(6) unsigned NOT NULL default '0', correct varchar(255) NOT NULL default '', wrong varchar(255) NOT NULL default '', ignored varchar(255) NOT NULL default '', diff --git a/mod/hotpot/db/postgres7.php b/mod/hotpot/db/postgres7.php index d50172947c..ac898bc32f 100644 --- a/mod/hotpot/db/postgres7.php +++ b/mod/hotpot/db/postgres7.php @@ -2,51 +2,44 @@ function hotpot_upgrade($oldversion) { global $CFG; $ok = true; + + // set path to update functions + $update_to_v2 = "$CFG->dirroot/mod/hotpot/db/update_to_v2.php"; + // update from HotPot v1 to HotPot v2 if ($oldversion < 2005031400) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_from_v1(); } // update to HotPot v2.1 if ($oldversion < 2005090700) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1(); } - // update to from HotPot v2.1.0 or v2.1.1 to HotPot v2.1.2 if ($oldversion > 2005031419 && $oldversion < 2005090702) { - $ok = $ok && hotpot_get_update_to_v2(); + // update to from HotPot v2.1.0 or v2.1.1 + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_2(); } - // update to HotPot v2.1.6 if ($oldversion < 2005090706) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_6(); } - // update to HotPot v2.1.8 if ($oldversion < 2005090708) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_8(); } - // update to HotPot v2.1.16 if ($oldversion < 2006042103) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_16(); } - // update to HotPot v2.1.17 if ($oldversion < 2006042602) { - $ok = $ok && hotpot_get_update_to_v2(); + require_once $update_to_v2; $ok = $ok && hotpot_update_to_v2_1_17(); } - return $ok; -} -function hotpot_get_update_to_v2() { - global $CFG; - $filepath = "$CFG->dirroot/mod/hotpot/db/update_to_v2.php"; - if (file_exists($filepath) && is_readable($filepath)) { - include_once $filepath; - $ok = true; - } else { - $ok = false; + if ($oldversion < 2006042800) { + require_once $update_to_v2; + $ok = $ok && hotpot_update_to_v2_1_18(); } return $ok; } diff --git a/mod/hotpot/db/update_to_v2.php b/mod/hotpot/db/update_to_v2.php index f0eb1ced3a..d6ce9a2a60 100644 --- a/mod/hotpot/db/update_to_v2.php +++ b/mod/hotpot/db/update_to_v2.php @@ -1,8 +1,54 @@ debug; + $db->debug = false; + + $records = get_records_sql(" + SELECT + t2.$secondarykeyfield, t2.$secondarykeyfield + FROM + {$CFG->prefix}$secondarytable AS t2 LEFT JOIN {$CFG->prefix}$primarytable AS t1 + ON (t2.$secondarykeyfield = t1.$primarykeyfield) + WHERE + t1.$primarykeyfield IS NULL + ORDER BY + t2.$secondarykeyfield + "); + + // restore SQL message echo setting + $db->debug = $debug; + + if ($records) { + $ids = implode(',', array_keys($records)); + print 'removing '.count($ids).' orphan record(s) from {$CFG->prefix}$secondarytable (key=$secondarykeyfield) ...
'; + $ok = $ok && execute_sql("DELETE FROM {$CFG->prefix}$secondarytable WHERE $secondarykeyfield IN ($ids)"); + } + + return $ok; +} function hotpot_update_to_v2_1_17() { global $CFG; $ok = true; + // convert and disable null values on certain numeric fields + $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'starttime', '10'); $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'endtime', '10'); $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'score', '6'); @@ -14,12 +60,11 @@ function hotpot_update_to_v2_1_17() { $ok = $ok && hotpot_denull_int_field('hotpot_questions', 'type', '4'); $ok = $ok && hotpot_denull_int_field('hotpot_questions', 'text', '10'); - $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '8'); - $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '8'); + $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '6'); + $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6'); $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'hints', '6'); $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'clues', '6'); $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'checks', '6'); - return $ok; } function hotpot_denull_int_field($table, $field, $size) { diff --git a/mod/hotpot/version.php b/mod/hotpot/version.php index 17d7ccffd5..25644e8925 100644 --- a/mod/hotpot/version.php +++ b/mod/hotpot/version.php @@ -3,8 +3,8 @@ /// Code fragment to define the version of hotpot /// This fragment is called by moodle_needs_upgrading() and /admin/index.php ///////////////////////////////////////////////////////////////////////////////// -$module->version = 2006042602; // release date of this version (see note below) -$module->release = 'v2.1.17'; // human-friendly version name (used in mod/hotpot/lib.php) +$module->version = 2006042800; // release date of this version (see note below) +$module->release = 'v2.1.18'; // human-friendly version name (used in mod/hotpot/lib.php) $module->cron = 0; // period for cron to check this module (secs) // interpretation of YYYYMMDDXY version numbers // YYYY : year -- 2.39.5