/mod/hotpot/db/update_to_v2.php
PHP | 1392 lines | 1153 code | 62 blank | 177 comment | 362 complexity | 30d4a50b8990119fc4c4be6ef4d3638d MD5 | raw file
Possible License(s): BSD-3-Clause, LGPL-2.0, LGPL-2.1
Large files files are truncated, but you can click here to view the full file
- <?PHP
- if (file_exists("$CFG->dirroot/lib/ddllib.php")) {
- // Moodle 1.8+
- include_once "$CFG->dirroot/lib/ddllib.php";
- }
-
- function hotpot_update_to_v2_2() {
- global $CFG;
- $ok = true;
-
- // remove the index on hotpot_questions.name
- $table = 'hotpot_questions';
- $field = 'name';
- if (strtolower($CFG->dbfamily)=='postgres') {
- $index = "{$CFG->prefix}{$table}_{$field}_idx";
- } else {
- $index = "{$table}_{$field}_idx";
- }
- hotpot_db_delete_index("{$CFG->prefix}$table", $index);
-
- // add new hotpot_questions.md5key field (and index)
- $table = 'hotpot_questions';
- $field = 'md5key';
- $ok = $ok && hotpot_db_update_field_type($table, '', $field, 'VARCHAR', 32, '', 'NOT NULL', '');
- $ok = $ok && hotpot_db_add_index($table, $field);
-
- // add new values hotpot_questions.md5key
- $table = 'hotpot_questions';
- if ($records = get_records($table)) {
- foreach ($records as $record) {
- $ok = $ok && set_field($table, 'md5key', md5($record->name), 'id', $record->id);
- }
- }
-
- // remove the index on hotpot_strings.string
- $table = 'hotpot_strings';
- $field = 'string';
- if (strtolower($CFG->dbfamily)=='postgres') {
- $index = "{$CFG->prefix}{$table}_{$field}_idx";
- } else {
- $index = "{$table}_{$field}_idx";
- }
- hotpot_db_delete_index("{$CFG->prefix}$table", $index);
-
- // add new hotpot_strings.md5key field (and index)
- $table = 'hotpot_strings';
- $field = 'md5key';
- $ok = $ok && hotpot_db_update_field_type($table, '', $field, 'VARCHAR', 32, '', 'NOT NULL', '');
- $ok = $ok && hotpot_db_add_index($table, $field);
-
- // add new values hotpot_strings.md5key
- $table = 'hotpot_strings';
- if ($records = get_records($table)) {
- foreach ($records as $record) {
- $ok = $ok && set_field($table, 'md5key', md5($record->string), 'id', $record->id);
- }
- }
-
- return $ok;
- }
- function hotpot_update_to_v2_1_21() {
- global $CFG;
- $ok = true;
-
- if (strtolower($CFG->dbfamily)=='postgres') {
- // ensure setting of default values on certain fields
- // this was originally done in postgres7.php, but was found to be incompatible with PG7 :-(
- $table="hotpot";
- execute_sql("UPDATE {$CFG->prefix}$table SET studentfeedbackurl = '' WHERE studentfeedbackurl IS NULL");
- $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedbackurl', 'VARCHAR', 255, '', 'NOT NULL', '');
- $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedback', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'clickreporting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
-
- $table="hotpot_attempts";
- $ok = $ok && hotpot_db_update_field_type($table, '', 'score', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'penalties', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'status', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
-
- $table="hotpot_questions";
- $ok = $ok && hotpot_db_update_field_type($table, '', 'type', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
-
- $table="hotpot_responses";
- $ok = $ok && hotpot_db_update_field_type($table, '', 'score', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'weighting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'correct', 'VARCHAR', 255, '', 'NOT NULL', '');
- execute_sql("UPDATE {$CFG->prefix}$table SET wrong = '' WHERE wrong IS NULL");
- $ok = $ok && hotpot_db_update_field_type($table, '', 'wrong', 'VARCHAR', 255, '', 'NOT NULL', '');
- execute_sql("UPDATE {$CFG->prefix}$table SET ignored = '' WHERE ignored IS NULL");
- $ok = $ok && hotpot_db_update_field_type($table, '', 'ignored', 'VARCHAR', 255, '', 'NOT NULL', '');
- $ok = $ok && hotpot_db_update_field_type($table, '', 'hints', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'clues', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type($table, '', 'checks', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
-
- $table="hotpot_strings";
- $ok = $ok && hotpot_db_update_field_type($table, '', 'string', 'TEXT', '', '', 'NOT NULL', '');
- }
-
- return $ok;
- }
- function hotpot_update_to_v2_1_18() {
- $ok = true;
-
- // remove all orphan records (there shouldn't be any, but if there are they can mess up the utfdbmigrate)
-
- $ok = $ok && hotpot_remove_orphans('hotpot_attempts', 'hotpot', 'hotpot');
- $ok = $ok && hotpot_remove_orphans('hotpot_questions', 'hotpot', 'hotpot');
- $ok = $ok && hotpot_remove_orphans('hotpot_responses', 'attempt', 'hotpot_attempts');
- $ok = $ok && hotpot_remove_orphans('hotpot_responses', 'question', 'hotpot_questions');
- $ok = $ok && hotpot_remove_orphans('hotpot_details', 'attempt', 'hotpot_attempts');
-
- // allow negative weighting and scores
-
- $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '6', false);
- $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6', false);
-
- return $ok;
- }
- function hotpot_remove_orphans($secondarytable, $secondarykeyfield, $primarytable, $primarykeyfield='id') {
- global $CFG,$db;
- $ok = true;
-
- // save and switch off SQL message echo
- $debug = $db->debug;
- $db->debug = false;
-
- $records = get_records_sql("
- SELECT
- t2.$secondarykeyfield, t2.$secondarykeyfield
- FROM
- {$CFG->prefix}$secondarytable t2 LEFT JOIN {$CFG->prefix}$primarytable t1
- ON (t2.$secondarykeyfield = t1.id)
- WHERE
- t1.$primarykeyfield IS NULL
- ");
-
- // restore SQL message echo setting
- $db->debug = $debug;
-
- if ($records) {
- $keys = implode(',', array_keys($records));
- print "removing orphan record(s) from {$CFG->prefix}$secondarytable<br/>";
- $ok = $ok && execute_sql("DELETE FROM {$CFG->prefix}$secondarytable WHERE $secondarykeyfield IN ($keys)");
- }
-
- 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');
- $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'penalties', '6');
- $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'timestart', '10');
- $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'timefinish', '10');
- $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'clickreportid', '10');
-
- $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', '6', false);
- $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6', false);
- $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, $unsigned=true) {
- global $CFG;
- $ok = true;
-
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}$table SET $field=0 WHERE $field IS NULL", false);
- if ($unsigned) {
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}$table SET $field=0 WHERE $field<0", false);
- }
- $ok = $ok && hotpot_db_update_field_type($table, $field, $field, 'INTEGER', $size, $unsigned, 'NOT NULL', 0);
-
- return $ok;
- }
- function hotpot_update_to_v2_1_16() {
- global $CFG;
- $ok = true;
-
- // remove the questions name index
- hotpot_db_delete_index("{$CFG->prefix}hotpot_questions", "hotpot_questions_name_idx");
- hotpot_db_delete_index("{$CFG->prefix}hotpot_questions", "{$CFG->prefix}hotpot_questions_name_idx");
-
- // make sure type of 'name' is a text field (not varchar 255)
- $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'name', 'name', 'TEXT', '', '', 'NOT NULL', '');
-
- if (strtolower($CFG->dbfamily)=='mysql') {
-
- // set default values on certain VARCHAR(255) fields
- $fields = array(
- 'hotpot' => 'studentfeedbackurl',
- 'hotpot_responses' => 'correct',
- 'hotpot_responses' => 'wrong',
- 'hotpot_responses' => 'ignored'
- );
- foreach ($fields as $table=>$field) {
- execute_sql("UPDATE {$CFG->prefix}$table SET $field='' WHERE $field IS NULL");
- $ok = $ok && hotpot_db_update_field_type($table, $field, $field, 'VARCHAR', 255, '', 'NOT NULL', '');
- }
-
- // remove $CFG->prefix from all index names
- $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_responses', 'attempt');
- $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'question');
- }
- return $ok;
- }
- function hotpot_index_remove_prefix($table, $field) {
- global $CFG;
- hotpot_db_delete_index("{$CFG->prefix}$table", "{$CFG->prefix}{$table}_{$field}_idx");
- hotpot_db_delete_index("{$CFG->prefix}$table", "{$table}_{$field}_idx");
- return hotpot_db_add_index($table, $field);
- }
-
- function hotpot_update_to_v2_1_8() {
- global $CFG;
- $ok = true;
- if (strtolower($CFG->dbfamily)=='postgres') {
- // add, delete and rename certain fields and indexes
- // that were not correctly setup by postgres7.sql
-
- // hotpot
- $table = 'hotpot';
- if (hotpot_db_field_exists($table, 'microreporting')) {
- $ok = $ok && hotpot_db_update_field_type($table, 'microreporting', 'clickreporting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
- }
- }
- return $ok;
- }
- function hotpot_update_to_v2_1_6() {
- global $CFG;
- $ok = true;
-
- if (strtolower($CFG->dbfamily)=='postgres') {
- // add, delete and rename certain fields and indexes
- // that were not correctly setup by postgres7.sql
-
- // hotpot
- $table = 'hotpot';
- if (hotpot_db_field_exists($table, 'studentfeedback') && !hotpot_db_field_exists($table, 'studentfeedbackurl')) {
- $ok = $ok && hotpot_db_update_field_type($table, 'studentfeedback', 'studentfeedbackurl', 'VARCHAR', 255, '', 'NULL');
- $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedback', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
- }
-
- // hotpot_attempts
- $table = 'hotpot_attempts';
- $ok = $ok && hotpot_db_remove_field($table, 'groupid');
- if (hotpot_db_field_exists($table, 'microreportid') && !hotpot_db_field_exists($table, 'clickreportid')) {
- $ok = $ok && hotpot_db_update_field_type($table, 'microreportid', 'clickreportid', 'INTEGER', 10, 'UNSIGNED', 'NULL');
- }
- }
-
- return $ok;
- }
- function hotpot_update_to_v2_1_2() {
- global $CFG, $db;
- $ok = true;
-
- // save and switch off SQL message echo
- $debug = $db->debug;
- $db->debug = false;
-
- // extract info about attempts by each user on each hotpot (cases where
- // the user has only one attempt, or no "in progess" attempt are ignored)
- $rs = $db->Execute("
- SELECT userid, hotpot, COUNT(*), MIN(status)
- FROM {$CFG->prefix}hotpot_attempts
- GROUP BY userid, hotpot
- HAVING COUNT(*)>1 AND MIN(status)=1
- ");
- if ($rs && $rs->RecordCount()) {
- $records = $rs->GetArray();
-
- // start message to browser
- print "adjusting status of ".count($records)." "in progress" attempts ... ";
-
- // loop through records
- foreach ($records as $record) {
-
- // get all attempts by this user at this hotpot
- $attempts = get_records_sql("
- SELECT id, userid, hotpot, score, timestart, timefinish, status
- FROM {$CFG->prefix}hotpot_attempts
- WHERE userid = ".$record['userid']." AND hotpot=".$record['hotpot']."
- ORDER BY timestart DESC, id DESC
- ");
-
- unset($previous_timestart);
-
- foreach ($attempts as $attempt) {
- // if this attempt has a status of "in progress" and is not
- // the most recent one in the group, set the status to "abandoned"
- if ($attempt->status==1 && isset($previous_timestart)) {
- $values = 'status=3';
- if (empty($attempt->score)) {
- $values .= ',score=0';
- }
- if (empty($attempt->timefinish)) {
- $values .= ",timefinish=$previous_timestart";
- }
- execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET $values WHERE id=$attempt->id", false);
- print ".";
- hotpot_flush(300);
- }
- $previous_timestart = $attempt->timestart;
- } // end foreach $attempts
- } // end foreach $records
-
- // finish message to browser
- print $ok ? get_string('success') : 'failed';
- print "<br />\n";
- }
-
- // restore SQL message echo setting
- $db->debug = $debug;
-
- return $ok;
- }
- function hotpot_update_to_v2_1() {
- global $CFG, $db;
- $ok = true;
- // hotpot_questions: reduce size of "type" field to "4"
- $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'type', 'type', 'INTEGER', 4, 'UNSIGNED', 'NULL');
- // hotpot_questions: change type of "name" field to "text"
- $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'name', 'name', 'TEXT', '', '', 'NOT NULL', '');
- // hotpot_questions: nullify empty and non-numeric (shouldn't be any) values in "text" field
- switch (strtolower($CFG->dbfamily)) {
- case 'mysql' :
- $NOT_REGEXP = 'NOT REGEXP';
- break;
- case 'postgres' :
- $NOT_REGEXP = '!~';
- break;
- default:
- $NOT_REGEXP = '';
- break;
- }
- if ($NOT_REGEXP) {
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_questions SET text=NULL WHERE text $NOT_REGEXP '^[0-9]+$'");
- }
- // hotpot_questions: change type of "text" field to "INT(10)"
- $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'text', 'text', 'INTEGER', 10, 'UNSIGNED', 'NULL');
- // hotpot_attempts
- // hotpot_attempts: move "details" to separate table
- $table = 'hotpot_details';
- if (hotpot_db_table_exists($table)) {
- // do nothing
- } else {
- $ok = $ok && hotpot_create_table($table);
- switch (strtolower($CFG->dbfamily)) {
- case 'mysql' :
- case 'postgres' :
- $sql = "
- INSERT INTO {$CFG->prefix}$table (attempt, details)
- SELECT a.id AS attempt, a.details AS details
- FROM {$CFG->prefix}hotpot_attempts a
- WHERE
- a.details IS NOT NULL AND a.details <> ''
- AND a.details LIKE '<?xml%' AND a.details LIKE '%</hpjsresult>'
- ";
- break;
- default:
- $sql = '';
- break;
- }
- if ($sql) {
- $ok = $ok && execute_sql($sql);
- }
- }
- // hotpot_attempts: remove the "details" field
- $ok = $ok && hotpot_db_remove_field('hotpot_attempts', 'details');
- // hotpot_attempts: create and set status field (1=in-progress, 2=timed-out, 3=abandoned, 4=completed)
- $ok = $ok && hotpot_db_update_field_type('hotpot_attempts', '', 'status', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET status=1 WHERE timefinish=0 AND SCORE IS NULL");
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET status=3 WHERE timefinish>0 AND SCORE IS NULL");
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET status=4 WHERE timefinish>0 AND SCORE IS NOT NULL");
- // hotpot_attempts: create and set clickreport fields
- $ok = $ok && hotpot_db_update_field_type('hotpot', '', 'clickreporting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- $ok = $ok && hotpot_db_update_field_type('hotpot_attempts', '', 'clickreportid', 'INTEGER', 10, 'UNSIGNED', 'NULL');
- $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET clickreportid=id WHERE clickreportid IS NULL");
- // hotpot_attempts: create and set studentfeedback field (0=none, 1=formmail, 2=moodleforum, 3=moodlemessaging)
- $ok = $ok && hotpot_db_update_field_type('hotpot', '', 'studentfeedback', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
- $ok = $ok && hotpot_db_update_field_type('hotpot', '', 'studentfeedbackurl', 'VARCHAR', 255, '', 'NULL');
- // add indexes
- $ok = $ok && hotpot_db_add_index('hotpot_attempts', 'hotpot');
- $ok = $ok && hotpot_db_add_index('hotpot_attempts', 'userid');
- $ok = $ok && hotpot_db_add_index('hotpot_details', 'attempt');
- $ok = $ok && hotpot_db_add_index('hotpot_questions', 'hotpot');
- $ok = $ok && hotpot_db_add_index('hotpot_responses', 'attempt');
- $ok = $ok && hotpot_db_add_index('hotpot_responses', 'question');
- // hotpot_string: correct double-encoded HTML entities
- $ok = $ok && execute_sql("
- UPDATE {$CFG->prefix}hotpot_strings
- SET string = REPLACE(string, '&','&')
- WHERE string LIKE '%&#%'
- AND (string LIKE '<' OR string LIKE '>')
- ");
- // hotpot_question: remove questions which refer to deleted hotpots
- if ($ok) {
- // try and get all hotpot records
- if ($records = get_records('hotpot')) {
- $ids = implode(',', array_keys($records));
- $sql = "DELETE FROM {$CFG->prefix}hotpot_questions WHERE hotpot NOT IN ($ids)";
- } else {
- // remove all question records (because there are no valid hotpot ids)
- $sql = "TRUNCATE {$CFG->prefix}hotpot_questions";
- }
- print "Removing unused question records ...";
- execute_sql($sql);
- }
- if ($ok) {
- // remove old 'v6' templates folder (replaced by 'template' folder)
- $ds = DIRECTORY_SEPARATOR;
- $dir = "mod{$ds}hotpot{$ds}v6";
- print "removing old templates ($dir) ... ";
- if (hotpot_rm("$CFG->dirroot{$ds}$dir", false)) {
- print get_string('success');
- } else {
- print "failed<br/>Please remove '$CFG->dirroot{$ds}$dir' manually";
- }
- print "<br />\n";
- }
- return $ok;
- }
- function hotpot_update_to_v2_from_v1() {
- global $CFG;
- $ok = true;
- // remove, alter and add fields in database
- $table = 'hotpot';
- if (hotpot_db_table_exists($table)) {
- $ok = $ok && hotpot_update_fields($table);
- } else {
- $ok = $ok && hotpot_create_table($table);
- }
- $table = 'hotpot_attempts';
- $oldtable = 'hotpot_events';
- if (hotpot_db_table_exists($oldtable)) {
- $ok = $ok && hotpot_update_fields($oldtable);
- $ok = $ok && hotpot_db_append_table($oldtable, $table);
- } else {
- $ok = $ok && hotpot_create_table($table);
- }
- // create new tables (from mysql.sql)
- $ok = $ok && hotpot_create_table('hotpot_questions');
- $ok = $ok && hotpot_create_table('hotpot_responses');
- $ok = $ok && hotpot_create_table('hotpot_strings');
- // remove redundant scripts
- $files = array('coursefiles.php', 'details.php', 'dummy.html', 'hotpot.php', 'hotpot2db.php');
- foreach ($files as $file) {
- $filepath = "$CFG->dirroot/mod/hotpot/$file";
- if (file_exists($filepath)) {
- @unlink($filepath); // don't worry about errors
- }
- }
- return $ok;
- }
- function hotpot_update_to_v2_from_hotpotatoes() {
- global $CFG;
- $ok = true; // hope for the best!
- // check we have the minimum required hotpot module
- $minimum = 2005031400;
- $module = get_record("modules", "name", "hotpot");
- if (empty($module) || $module->version<$minimum) {
- if ($module) {
- print ("<p>The update to the HotPotatoes module requires at least version $minimum of the HotPot module.</p>");
- print ("<p>The current version of the HotPot module on this site is $module->version.</p>");
- }
- print ("<p>Please install the latest version of the HotPot module and then try the update again.</p>");
- $ok = false;
- } else {
- // arrays to map foreign keys
- $new = array();
- $new['hotpot'] = array();
- $new['attempt'] = array();
- $new['question'] = array();
- $new['string'] = array();
- // save and switch off SQL message echo
- global $db;
- $debug = $db->debug;
- $db->debug = false;
- // import hotpotatoes (and save old ids)
- $ok = $ok && hotpot_update_fields('hotpotatoes');
- $ok = $ok && hotpot_transfer_records('hotpotatoes', 'hotpot', array(), 'hotpot', $new);
- // update course modules and logs
- $ok = $ok && hotpot_update_course_modules('hotpotatoes', 'hotpot', $new);
- // import hotpotatoes_strings (and save old ids)
- $ok = $ok && hotpot_transfer_records('hotpotatoes_strings', 'hotpot_strings', array(), 'string', $new);
- // import hotpotatoes_attempts (and save old ids)
- $ok = $ok && hotpot_transfer_records('hotpotatoes_attempts', 'hotpot_attempts', array('hotpotatoes'=>'hotpot'), 'attempt', $new);
- // import hotpotatoes_questions (and save old ids)
- $ok = $ok && hotpot_transfer_records('hotpotatoes_questions', 'hotpot_questions', array('hotpotatoes'=>'hotpot'), 'question', $new);
- // import hotpotatoes_responses
- $ok = $ok && hotpot_transfer_records('hotpotatoes_responses', 'hotpot_responses', array('attempt'=>'attempt', 'question'=>'question'), 'response', $new);
- // restore SQL message echo setting
- $db->debug = $debug;
- // remove the hotpotatoes tables, if the update went ok
- if ($ok) {
- // hotpot_db_remove_table('hotpotatoes');
- // hotpot_db_remove_table('hotpotatoes_attempts');
- // hotpot_db_remove_table('hotpotatoes_questions');
- // hotpot_db_remove_table('hotpotatoes_responses');
- // hotpot_db_remove_table('hotpotatoes_strings');
- }
- // hide the hotpotatoes module (see admin/modules.php))
- if ($ok && ($module = get_record("modules", "name", "hotpotatoes"))) {
- set_field("modules", "visible", "0", "id", $module->id);
- print '<p>All HotPotatoes activities have been imported to the HotPot module.<br />'."\n";
- print 'The HotPotatoes module has been hidden and can safely be deleted from this Moodle site.<br />'."\n";
- print ' <a href="'.$CFG->wwwroot.'/'.$CFG->admin.'/modules.php">Configuration -> Modules</A>, then click "Delete" for "Hot Potatoes XML Quiz"</p>'."\n";
- }
- }
- if ($ok) {
- print '<p align="center">Thank you for using the HotPotatoes module.<br />';
- print 'The HotPotatoes module has been replaced by<br />version 2 of the HotPot module. Enjoy!</p>';
- }
- return $ok;
- }
- function hotpot_create_table($table) {
- global $CFG;
-
- static $sql;
- static $xmldb_file;
-
- // check table does not already exist
- if (hotpot_db_table_exists($table)) {
- return true;
- }
-
- if (! isset($xmldb_file)) { // first time only
- if (class_exists('XMLDBFile')) {
- $xmldb_file = new XMLDBFile("$CFG->dirroot/mod/hotpot/db/install.xml");
- if (! $xmldb_file->fileExists() || !$xmldb_file->loadXMLStructure() || !$xmldb_file->isLoaded()) {
- unset($xmldb_file);
- }
- }
- if (empty($xmldb_file)) {
- $xmldb_file = false;
- }
- }
-
- if ($xmldb_file) {
- // Moodle 1.8 (and later)
- $ok = false;
- foreach ($xmldb_file->xmldb_structure->tables as $xmldb_table) {
- if ($xmldb_table->name==$table) {
- $ok = create_table($xmldb_table);
- break;
- }
- }
- return $ok;
- }
-
- // Moodle 1.7 (and earlier)
-
- if (! isset($sql)) { // first time only
- $sqlfilepath = "$CFG->dirroot/mod/hotpot/db/$CFG->dbtype.sql";
- if (file_exists($sqlfilepath)) {
- if (function_exists('file_get_contents')) {
- $sql = file_get_contents($sqlfilepath);
- } else { // PHP < 4.3
- $sql = file($sqlfilepath);
- if (is_array($sql)) {
- $sql = implode('', $sql);
- }
- }
- }
- if (empty($sql)) {
- $sql = '';
- }
- }
-
- // extract and execute all CREATE statements relating to this table
- if (preg_match_all("/CREATE (TABLE|INDEX)(\s[^;]*)? prefix_{$table}(\s[^;]*)?;/s", $sql, $strings)) {
- $ok = true;
- foreach ($strings[0] as $string) {
- $ok = $ok && modify_database('', $string);
- }
- return $ok;
- }
-
- // table could not be created
- return false;
- }
- function hotpot_transfer_records($oldtable, $table, $foreignkeys, $primarykey, &$new) {
- global $db;
- $ok = true;
- // get the records, if any
- if (hotpot_db_table_exists($oldtable) && ($records = get_records($oldtable))) {
- // start progress report
- $i = 0;
- $count = count($records);
- hotpot_update_print("Transferring $count records from "$oldtable" to "$table" ... ");
- // transfer all $records
- foreach ($records as $record) {
- switch ($table) {
- case 'hotpot' :
- $record->summary = addslashes($record->summary);
- break;
- case 'hotpot_attempts' :
- $record->details = addslashes($record->details);
- break;
- case 'hotpot_questions' :
- $record->name = addslashes($record->name);
- hotpot_update_string_id_list($table, $record, 'TEXT', $new);
- break;
- case 'hotpot_responses' :
- hotpot_update_string_id_list($table, $record, 'correct', $new);
- hotpot_update_string_id_list($table, $record, 'ignored', $new);
- hotpot_update_string_id_list($table, $record, 'wrong', $new);
- break;
- case 'hotpot_strings' :
- $record->string = addslashes($record->string);
- break;
- }
- // update foreign keys, if any
- foreach ($foreignkeys as $oldkey=>$key) {
- // transfer (and update) key
- $value = $record->$oldkey;
- if (isset($new[$key][$value])) {
- $record->$key = $new[$key][$value];
- } else {
- // foreign key could not be updated
- $ok = hotpot_update_print_warning($key, $value, $oldtable, $record->id) && $ok;
- unset($record->id);
- }
- }
- if ($ok && isset($record->id)) {
- // store and remove old primary key
- $id = $record->id;
- unset($record->id);
- // add the updated record and store the new id
- $new[$primarykey][$id] = insert_record($table, $record, true);
- // check id is numeric
- if (!is_numeric($new[$primarykey][$id])) {
- hotpot_update_print("<li>Record could not added to $table table ($oldtable id=$id)</li>\n");
- //$ok = false;
- }
- }
- $i++;
- hotpot_update_print_progress($i);
- }
- // finish progress report
- hotpot_update_print_ok($ok);
- }
- return $ok;
- }
- function hotpot_update_course_modules($oldmodulename, $modulename, &$new) {
- $ok = true;
- $oldmoduleid = get_field('modules', 'id', 'name', $oldmodulename);
- $moduleid = get_field('modules', 'id', 'name', $modulename);
- if (is_numeric($oldmoduleid) && is_numeric($moduleid)) {
- // get module records
- if ($records = get_records('course_modules', 'module', $oldmoduleid)) {
- // start progress report
- $count = count($records);
- hotpot_update_print("Updating $count course modules from "$oldmodulename" to "$modulename" ... ");
- // update foreign keys in all $records
- foreach ($records as $record) {
- // update instance
- $instance = $record->instance;
- if (isset($new[$modulename][$instance])) {
- $record->instance = $new[$modulename][$instance];
- } else if ($record->deleted) {
- unset($record->id);
- } else {
- // could not find new id of course module
- $ok = hotpot_update_print_warning("$modulename instance", $instance, 'course_modules', $record->id) && $ok;
- unset($record->id);
- }
- // update module id
- if ($ok && isset($record->id)) {
- $record->module = $moduleid;
- $ok = update_record('course_modules', $record);
- }
- }
- // finish progress report
- hotpot_update_print_ok($ok);
- }
- // update logs
- $ok = $ok && hotpot_update_logs($oldmodulename, $modulename, $moduleid, $new);
- }
- return $ok;
- }
- function hotpot_update_logs($oldmodulename, $modulename, $moduleid, &$new) {
- $table = 'log';
- $ok = true;
- // get log records for the oldmodule
- if ($records = get_records($table, 'module', $oldmodulename)) {
- // start progress report
- $i = 0;
- $count = count($records);
- hotpot_update_print("Updating $count log records ... ");
- // update foreign keys in all $records
- foreach ($records as $record) {
- // update course module name
- $record->module = $modulename;
- // check if module id was given (usually it is)
- if ($record->cmid) {
- // update course module id, if necessary
- if (isset($new[$modulename][$record->cmid])) {
- $record->cmid = $new[$modulename][$record->cmid];
- } else {
- // could not update course module id
- $ok = hotpot_update_print_warning('cmid', $record->cmid, 'log', $record->id) && $ok;
- unset($record->id);
- }
- // update url and info
- switch ($record->action) {
- case "add":
- case "update":
- case "view":
- $record->url = "view.php?id=".$record->cmid;
- $record->info = $moduleid;
- break;
- case "view all":
- // do nothing
- break;
- case "report":
- $record->url = "report.php?id=".$record->cmid;
- $record->info = $moduleid;
- break;
- case "attempt":
- case "submit":
- case "review":
- $id = substr(strrchr($record->url,"="),1);
- if (isset($new->attempt[$id])) {
- $id = $new->attempt[$id];
- }
- $record->url = "review.php?id=".$record->cmid."&attempt=$id";
- $record->info = $moduleid;
- break;
- default:
- // unknown log action
- $ok = hotpot_update_print_warning('action', $record->action, 'log', $record->id) && $ok;
- unset($record->id);
- } // end switch
- }
- if (isset($record->id)) {
- $ok = $ok && update_record($table, $record);
- }
- $i++;
- hotpot_update_print_progress($i);
- } // end foreach
- // finish progress report
- hotpot_update_print_ok($ok);
- }
- return $ok;
- }
- function hotpot_update_fields($table, $feedback=false) {
- global $CFG, $db;
- $ok = true;
- // check the table exists
- if (hotpot_db_table_exists($table)) {
- switch ($table) {
- case 'hotpot' :
- // == ADD ==
- hotpot_db_update_field_type($table, '', 'location', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- hotpot_db_update_field_type($table, '', 'navigation', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
- hotpot_db_update_field_type($table, '', 'outputformat', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
- hotpot_db_update_field_type($table, '', 'shownextquiz', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- hotpot_db_update_field_type($table, '', 'forceplugins', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
- hotpot_db_update_field_type($table, '', 'password', 'VARCHAR', 255, '', 'NOT NULL', '');
- hotpot_db_update_field_type($table, '', 'subnet', 'VARCHAR', 255, '', 'NOT NULL', '');
- // == ALTER ==
- hotpot_db_update_field_type($table, 'summary', 'summary', 'TEXT', '', '', 'NOT NULL', '');
- hotpot_db_update_field_type($table, 'reference', 'reference', 'VARCHAR', 255, '', 'NOT NULL', '');
- // == REMOVE ==
- hotpot_db_remove_field($table, 'intro');
- hotpot_db_remove_field($table, 'attemptonlast');
- hotpot_db_remove_field($table, 'sumgrades');
- hotpot_db_set_table_comment($table, 'details about Hot Potatoes quizzes');
- break;
- case 'hotpot_events' :
- // == ADD ==
- hotpot_db_update_field_type($table, '', 'hotpot', 'INTEGER', 10, 'UNSIGNED', 'NOT NULL');
- hotpot_db_update_field_type($table, '', 'attempt', 'INTEGER', 6, 'UNSIGNED', 'NOT NULL');
- hotpot_db_update_field_type($table, '', 'details', 'TEXT', '', '', '', '');
- hotpot_db_update_field_type($table, '', 'timestart', 'INTEGER', 10, 'UNSIGNED', 'NOT NULL', 0);
- hotpot_db_update_field_type($table, '', 'timefinish', 'INTEGER', 10, 'UNSIGNED', 'NOT NULL', 0);
- // == ALTER ==
- hotpot_db_update_field_type($table, 'score', 'score', 'INTEGER', 6, 'UNSIGNED', 'NULL');
- hotpot_db_update_field_type($table, 'wrong', 'penalties', 'INTEGER', 6, 'UNSIGNED', 'NULL');
- hotpot_db_update_field_type($table, 'starttime', 'starttime', 'INTEGER', 10, 'UNSIGNED', 'NULL');
- hotpot_db_update_field_type($table, 'endtime', 'endtime', 'INTEGER', 10, 'UNSIGNED', 'NULL');
- // save and switch off SQL message echo
- $debug = $db->debug;
- $db->debug = $feedback;
- // get array mapping course module ids to hotpot ids
- $hotpotmoduleid = get_field('modules', 'id', 'name', 'hotpot');
- $coursemodules = get_records('course_modules', 'module', $hotpotmoduleid, 'id', 'id, instance');
- // get all event records
- if (hotpot_db_field_exists($table, 'hotpotid')) {
- $records = get_records($table, '', '', 'userid,hotpotid,time');
- } else {
- $records = false; // table has already been updated
- }
- if ($records) {
- $count = count($records);
- hotpot_update_print("Updating $count records in $table ... ");
- $ids = array_keys($records);
- foreach ($ids as $i=>$id) {
- // reference to current record
- $record = &$records[$id];
- // set timestart and timefinish (the times recorded by Moodle)
- if (empty($record->timestart) && $record->time) {
- $record->timestart = $record->time;
- }
- if (empty($record->timefinish) && $record->timestart) {
- if ($record->starttime && $record->endtime) {
- $duration = ($record->endtime - $record->starttime);
- } else {
- if (($i+1)>=$count) {
- $nextrecord = NULL;
- } else {
- $nextrecord = &$records[$ids[$i+1]];
- }
- if (isset($nextrecord) && $nextrecord->userid==$record->userid && $nextrecord->hotpotid==$record->hotpotid) {
- $duration = $nextrecord->time - $record->time;
- } else {
- $duration = NULL;
- }
- }
- if (isset($duration)) {
- $record->timefinish = $record->timestart + $duration;
- }
- }
- // unset score and penalties, if quiz was abandoned
- if (empty($record->endtime) || (empty($record->penalties) && empty($record->score))) {
- unset($record->score);
- unset($record->penalties);
- }
- // get last (=previous) record
- if ($i==0) {
- $lastrecord = NULL;
- } else {
- $lastrecord = &$records[$ids[$i-1]];
- }
- // increment or reset $attempt number
- if (isset($lastrecord) && $lastrecord->userid==$record->userid && $lastrecord->hotpotid==$record->hotpotid) {
- $attempt++;
- } else {
- $attempt = 1;
- }
- // set $record->$attempt, if necessary
- if (empty($record->attempt) || $record->attempt<$attempt) {
- $record->attempt = $attempt;
- } else {
- $attempt = $record->attempt;
- }
- // set hotpot id and update record
- if (isset($record->hotpotid) && isset($record->id)) {
- if (isset($coursemodules[$record->hotpotid])) {
- $record->hotpot = $coursemodules[$record->hotpotid]->instance;
- hotpot_db_update_record($table, $record, true);
- } else {
- // hotpotid is invalid (shouldn't happen)
- $ok = hotpot_update_print_warning('hotpotid', $record->hotpotid, $table, $record->id) && $ok;
- delete_records($table, 'id', $record->id);
- }
- } else {
- // empty record (shouldn't happen)
- }
- hotpot_update_print_progress($i);
- }
- // finish progress report
- hotpot_update_print_ok($ok);
- }
- // restore SQL message echo setting
- $db->debug = $debug;
- // == REMOVE ==
- hotpot_db_remove_field($table, 'hotpotid');
- hotpot_db_remove_field($table, 'course');
- hotpot_db_remove_field($table, 'time');
- hotpot_db_remove_field($table, 'event');
- hotpot_db_set_table_comment($table, 'details about Hot Potatoes quiz attempts');
- break;
- case 'hotpotatoes' :
- // == ALTER ==
- hotpot_db_update_field_type($table, 'intro', 'summary', 'TEXT', '', '', '', 'NULL');
- break;
- }
- }
- return $ok;
- }
- function hotpot_update_string_id_list($table, &$record, $field, &$new) {
- $ok = true;
- if (isset($record->$field)) {
- $oldids = explode(',', $record->$field);
- $newids = array();
- foreach ($oldids as $id) {
- if (isset($new['string'][$id])) {
- $newids[] = $new['string'][$id];
- } else if (is_numeric($id)) {
- // string id could not be updated
- $ok = hotpot_update_print_warning("string id in $field", $id, $table, $record->id) && $ok;
- } else {
- // ignore non-numeric ids (e.g. blanks)
- }
- }
- if ($ok) {
- $record->$field = implode(',', $newids);
- }
- }
- return $ok;
- }
- ///////////////////////////
- // print functions
- ///////////////////////////
- function hotpot_update_print($msg=false, $n=300) {
- // this function prints $msg and flush output buffer
- if ($msg) {
- if (is_string($msg)) {
- print $msg;
- } else {
- print strftime("%X", time());
- }
- }
- // fill output buffer
- if ($n) {
- print str_repeat(" ", $n);
- }
- // some browser's require newline to flush
- print "\n";
- // flush PHP's output buffer
- flush();
- }
- function hotpot_update_print_progress($i) {
- if ($i%10==0) {
- $msg = '.';
- hotpot_update_print($msg);
- }
- }
- function hotpot_update_print_ok($ok) {
- if ($ok) {
- hotpot_update_print('<font color="green">'.get_string('success')."</font><br />\n");
- } else {
- hotpot_update_print('<font color="red">'.get_string('error')."</font><br />\n");
- }
- }
- function hotpot_update_print_warning($field, $value, $table, $id) {
- hotpot_update_print("<li><b>Warning:</b> invalid $field field (value=$value) in $table (id=$id)</li>\n");
- return true;
- }
- ///////////////////////////
- // database functions
- ///////////////////////////
- function hotpot_db_index_exists($table, $index, $feedback=false) {
- global $CFG, $db;
- $exists = false;
- // save and switch off SQL message echo
- $debug = $db->debug;
- $db->debug = $feedback;
- switch (strtolower($CFG->dbfamily)) {
- case 'mysql' :
- $rs = $db->Execute("SHOW INDEX FROM `$table`");
- if ($rs && $rs->RecordCount()>0) {
- $records = $rs->GetArray();
- foreach ($records as $record) {
- if (isset($record['Key_name']) && $record['Key_name']==$index) {
- $exists = true;
- break;
- }
- }
- }
- break;
- case 'postgres' :
- $rs = $db->Execute("SELECT relname FROM pg_class WHERE relname = '$index' AND relkind='i'");
- if ($rs && $rs->RecordCount()>0) {
- $exists = true;
- }
- break;
- }
- // restore SQL message echo
- $db->debug = $debug;
- return $exists;
- }
- function hotpot_db_delete_index($table, $index, $feedback=false) {
- global $CFG, $db;
- $ok = true;
- // check index exists
- if (hotpot_db_index_exists($table, $index)) {
- switch (strtolower($CFG->dbfamily)) {
- case 'mysql' :
- $sql = "ALTER TABLE `$table` DROP INDEX `$index`";
- break;
- case 'postgres' :
- $sql = "DROP INDEX $index";
- break;
- default: // unknown database type
- $sql = '';
- break;
- }
- if ($sql) {
- // save and switch off SQL message echo
- $debug = $db->debug;
- $db->debug = $feedback;
- $ok = $db->Execute($sql) ? true : false;
- // restore SQL message echo
- $db->debug = $debug;
- } else { // unknown database type
- $ok = false;
- }
- }
- return $ok;
- }
- function hotpot_db_add_index($table, $field, $length='') {
- global $CFG, $db;
-
- if (strtolower($CFG->dbfamily)=='postgres') {
- $index = "{$CFG->prefix}{$table}_{$field}_idx";
- } else {
- // mysql (and others)
- $index = "{$table}_{$field}_idx";
- }
- $table = "{$CFG->prefix}$table";
-
- // delete $index if it already exists
- $ok = hotpot_db_delete_index($table, $index);
-
- switch (strtolower($CFG->dbfamily)) {
- case 'mysql' :
- $ok = $ok && $db->Execute("ALTER TABLE `$table` ADD INDEX `$index` (`$field`)");
- break;
- case 'postgres' :
- $ok = $ok && $db->Execute("CREATE INDEX $index ON $table (\"$field\")");
- break;
- default: // unknown database type
- $ok = false;
- break;
- }
- return $ok;
- }
- function hotpot_db_table_exists($table, $feedback=false) {
- return hotpot_db_object_exists($table, '', $feedback);
- }
- function hotpot_db_field_exists($table, $field, $feedback=false) {
- return
- hotpot_db_object_exists($table, '', $feedback) &&
- hotpot_db_object_exists($table, $field, $feedback)
- ;
- }
- function hotpot_db_object_exists($table, $field='', $feedback=false) {
- global $CFG,$db;
- // expand table name
- $table = "{$CFG->prefix}$table";
- // set $sql
- switch (strtolower($CFG->dbfamily)) {
- case 'mysql' :
- if (empty($field)) {
- $sql = "SHOW TABLES LIKE '$table'";
- } else {
- $sql = "SHOW COLUMNS FROM `$table` LIKE '$field'";
- }
- break;
- case 'postgres' :
- if (empty($field)) {
- $sql = "SELECT relname FROM pg_class WHERE relname = '$table' AND relkind='r'";
- } else {
- $sql = "
- SELECT attname FROM pg_attribute WHERE attname = '$field'
- AND attrelid = (SELECT oid FROM pg_class WHERE relname = '$table')
- ";
- }
- break;
- }
- // save and switch off SQL message echo
- $debug = $db->debug;
- $db->debug = $feedback;
- // execute sql
- $rs = $db->Execute($sql);
- // restore SQL message echo setting
- $db->debug = $debug;
- // report error if required
- if (empty($rs) && debugging()) {
- notify($db->ErrorMsg()."<br /><br />$sql");
- }
- return ($rs && $rs->RecordCount()>0);
- }
- function hotpot_db_remove_table($table, $feedback=true) {
- global $CFG;
- if (hotpot_db_table_exists($table)) {
- $ok = execute_sql("DROP TABLE {$CFG->prefix}$table", $feedback);
- } else {
- $ok = true;
- }
- return $ok;
- }
- function hotpot_db_rename_table($oldtable, $table, $feedback=true) {
- global $CFG;
- if (hotpot_db_table_exists($oldtable)) {
- $ok = execute_sql("ALTER TABLE {$CFG->prefix}$oldtable RENAME TO {$CFG->prefix}$table", $feedback);
- } else {
- $ok = true;
- }
- return $ok;
- }
- function hotpot_db_append_table($oldtable, $table, $feedback=true) {
- global $CFG, $db;
- if (hotpot_db_table_exists($oldtable)) {
- if (hotpot_db_table_exists($table)) {
- // expand table names
- $table = "{$CFG->prefix}$table";
- $oldtable = "{$CFG->prefix}$oldtable";
- // get field info
- $fields = $db->MetaColumns($table);
- $oldfields = $db->MetaColumns($oldtable);
- $fieldnames = array();
- if (!empty($fields) || !empty($oldfields)) {
- foreach ($fields as $field) {
- if ($field->name!='id' && isset($oldfields[strtoupper($field->name)])) {
- $fieldnames[] = $field->name;
- }
- }
- }
- $fieldnames = implode(',', $fieldnames);
- if (empty($fieldnam…
Large files files are truncated, but you can click here to view the full file