From df795ee9508f865f90d03b294232faca53e30a1e Mon Sep 17 00:00:00 2001 From: gbateson Date: Sun, 23 Apr 2006 11:53:38 +0000 Subject: [PATCH] two fixes connected with UTF migration: (i) remove $CFG->prefix from all index names, (ii) force type of "name" field in "hotpot_questions" table to "text" --- mod/hotpot/db/mysql.php | 15 ++++++------- mod/hotpot/db/mysql.sql | 14 ++++++------ mod/hotpot/db/postgres7.php | 5 +++++ mod/hotpot/db/update_to_v2.php | 40 +++++++++++++++++++++++++++++++--- 4 files changed, 56 insertions(+), 18 deletions(-) diff --git a/mod/hotpot/db/mysql.php b/mod/hotpot/db/mysql.php index 4a028b463e..4f5321beb9 100644 --- a/mod/hotpot/db/mysql.php +++ b/mod/hotpot/db/mysql.php @@ -21,17 +21,15 @@ function hotpot_upgrade($oldversion) { $ok = $ok && hotpot_get_update_to_v2(); $ok = $ok && hotpot_update_to_v2_1_2(); } - - if ($oldversion < 2006042100) { - $result = execute_sql("ALTER TABLE {$CFG->prefix}hotpot_questions DROP INDEX {$CFG->prefix}hotpot_questions_name_idx"); - $result = execute_sql("ALTER TABLE {$CFG->prefix}hotpot_questions ADD INDEX hotpot_questions_name_idx (name(20))"); - - } - - + // update to HotPot v2.1.16 + if ($oldversion < 2006042101) { + $ok = $ok && hotpot_get_update_to_v2(); + $ok = $ok && hotpot_update_to_v2_1_16(); + } return $ok; } + function hotpot_get_update_to_v2() { global $CFG; $filepath = "$CFG->dirroot/mod/hotpot/db/update_to_v2.php"; @@ -43,4 +41,5 @@ function hotpot_get_update_to_v2() { } return $ok; } + ?> diff --git a/mod/hotpot/db/mysql.sql b/mod/hotpot/db/mysql.sql index 14b7ef2323..788cb117ea 100644 --- a/mod/hotpot/db/mysql.sql +++ b/mod/hotpot/db/mysql.sql @@ -44,8 +44,8 @@ CREATE TABLE prefix_hotpot_attempts ( status tinyint(4) unsigned NOT NULL default '1', clickreportid int(10) unsigned default NULL, PRIMARY KEY (id), - KEY prefix_hotpot_attempts_hotpot_idx (hotpot), - KEY prefix_hotpot_attempts_userid_idx (userid) + KEY hotpot_attempts_hotpot_idx (hotpot), + KEY hotpot_attempts_userid_idx (userid) ) TYPE=MyISAM COMMENT='details about Hot Potatoes quiz attempts'; # # Table structure for table `hotpot_details` @@ -55,7 +55,7 @@ CREATE TABLE prefix_hotpot_details ( attempt int(10) unsigned NOT NULL default '0', details text default '', PRIMARY KEY (id), - KEY prefix_hotpot_details_attempt_idx (attempt) + KEY hotpot_details_attempt_idx (attempt) ) TYPE=MyISAM COMMENT='raw details (as XML) of Hot Potatoes quiz attempts'; # # Table structure for table `hotpot_questions` @@ -68,7 +68,7 @@ CREATE TABLE prefix_hotpot_questions ( hotpot int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY hotpot_questions_name_idx (name(20)), - KEY prefix_hotpot_questions_hotpot_idx (hotpot) + KEY hotpot_questions_hotpot_idx (hotpot) ) TYPE=MyISAM COMMENT='details about questions in Hot Potatoes quiz attempts'; # # Table structure for table `hotpot_responses` @@ -86,8 +86,8 @@ CREATE TABLE prefix_hotpot_responses ( clues smallint(6) default NULL, checks smallint(6) default NULL, PRIMARY KEY (id), - KEY prefix_hotpot_responses_attempt_idx (attempt), - KEY prefix_hotpot_responses_question_idx (question) + KEY hotpot_responses_attempt_idx (attempt), + KEY hotpot_responses_question_idx (question) ) TYPE=MyISAM COMMENT='details about responses in Hot Potatoes quiz attempts'; # # Table structure for table `hotpot_strings` @@ -96,6 +96,6 @@ CREATE TABLE prefix_hotpot_strings ( id int(10) unsigned NOT NULL auto_increment, string text NOT NULL default '', PRIMARY KEY (id), - KEY prefix_hotpot_strings_string_idx (string(20)) + KEY hotpot_strings_string_idx (string(20)) ) TYPE=MyISAM COMMENT='strings used in Hot Potatoes questions and responses'; diff --git a/mod/hotpot/db/postgres7.php b/mod/hotpot/db/postgres7.php index 8d1b54a633..c147c42bb9 100644 --- a/mod/hotpot/db/postgres7.php +++ b/mod/hotpot/db/postgres7.php @@ -27,6 +27,11 @@ function hotpot_upgrade($oldversion) { $ok = $ok && hotpot_get_update_to_v2(); $ok = $ok && hotpot_update_to_v2_1_8(); } + // update to HotPot v2.1.16 + if ($oldversion < 2006042101) { + $ok = $ok && hotpot_get_update_to_v2(); + $ok = $ok && hotpot_update_to_v2_1_16(); + } return $ok; } function hotpot_get_update_to_v2() { diff --git a/mod/hotpot/db/update_to_v2.php b/mod/hotpot/db/update_to_v2.php index 39c5170e16..e3b18aaf9f 100644 --- a/mod/hotpot/db/update_to_v2.php +++ b/mod/hotpot/db/update_to_v2.php @@ -1,4 +1,31 @@ dbtype)=='mysql') { + $ok = $ok && hotpot_index_remove_prefix('hotpot_attempts', 'hotpot'); + $ok = $ok && hotpot_index_remove_prefix('hotpot_attempts', 'userid'); + $ok = $ok && hotpot_index_remove_prefix('hotpot_details', 'attempt'); + $ok = $ok && hotpot_index_remove_prefix('hotpot_questions', 'hotpot'); + $ok = $ok && hotpot_index_remove_prefix('hotpot_questions', 'name', 20); + $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'attempt'); + $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'question'); + $ok = $ok && hotpot_index_remove_prefix('hotpot_strings', 'string', 20); + } + return $ok; +} +function hotpot_index_remove_prefix($table, $field, $length=0) { + global $CFG; + $index = "{$table}_{$field}_idx"; + hotpot_db_delete_index("{$CFG->prefix}$table", "{$CFG->prefix}$index"); + hotpot_db_delete_index("{$CFG->prefix}$table", $index); + return hotpot_db_add_index($table, $field, $length); +} + function hotpot_update_to_v2_1_8() { global $CFG; $ok = true; @@ -776,13 +803,20 @@ function hotpot_db_delete_index($table, $index, $feedback=false) { } function hotpot_db_add_index($table, $field, $length='') { global $CFG, $db; - // expand $table and $index names + + if (strtolower($CFG->dbtype)=='postgres7') { + $index = "{$CFG->prefix}{$table}_{$field}_idx"; + } else { + // mysql (and others) + $index = "{$table}_{$field}_idx"; + } $table = "{$CFG->prefix}$table"; - $index = "{$table}_{$field}_idx"; + // delete $index if it already exists $ok = hotpot_db_delete_index($table, $index); + switch (strtolower($CFG->dbtype)) { - case 'mysql' : + case 'mysql' : $length = empty($length) ? '' : " ($length)"; $ok = $ok && $db->Execute("ALTER TABLE `$table` ADD INDEX `$index` (`$field`$length)"); break; -- 2.39.5