mod/hotpot/db/update_to_v2.php

   1 <?PHP
   2 if (file_exists("$CFG->dirroot/lib/ddllib.php")) {
   3     // Moodle 1.8+
   4     include_once "$CFG->dirroot/lib/ddllib.php";
   5 }
   6
   7 function hotpot_update_to_v2_2() {
   8     global $CFG;
   9     $ok = true;
  10
  11     // remove the index on hotpot_questions.name
  12     $table = 'hotpot_questions';
  13     $field = 'name';
  14     if (strtolower($CFG->dbfamily)=='postgres') {
  15         $index = "{$CFG->prefix}{$table}_{$field}_idx";
  16     } else {
  17         $index = "{$table}_{$field}_idx";
  18         }
  19     hotpot_db_delete_index("{$CFG->prefix}$table", $index);
  20
  21     // add new hotpot_questions.md5key field (and index)
  22     $table = 'hotpot_questions';
  23     $field = 'md5key';
  24     $ok = $ok && hotpot_db_update_field_type($table, '', $field, 'VARCHAR', 32, '', 'NOT NULL', '');
  25     $ok = $ok && hotpot_db_add_index($table, $field);
  26
  27     // add new values hotpot_questions.md5key
  28     $table = 'hotpot_questions';
  29     if ($records = get_records($table)) {
  30         foreach ($records as $record) {
  31             $ok = $ok && set_field($table, 'md5key', md5($record->name), 'id', $record->id);
  32         }
  33     }
  34
  35     // remove the index on hotpot_strings.string
  36     $table = 'hotpot_strings';
  37     $field = 'string';
  38     if (strtolower($CFG->dbfamily)=='postgres') {
  39         $index = "{$CFG->prefix}{$table}_{$field}_idx";
  40     } else {
  41         $index = "{$table}_{$field}_idx";
  42         }
  43     hotpot_db_delete_index("{$CFG->prefix}$table", $index);
  44
  45     // add new hotpot_strings.md5key field (and index)
  46     $table = 'hotpot_strings';
  47     $field = 'md5key';
  48     $ok = $ok && hotpot_db_update_field_type($table, '', $field, 'VARCHAR', 32, '', 'NOT NULL', '');
  49     $ok = $ok && hotpot_db_add_index($table, $field);
  50
  51     // add new values hotpot_strings.md5key
  52     $table = 'hotpot_strings';
  53     if ($records = get_records($table)) {
  54         foreach ($records as $record) {
  55             $ok = $ok && set_field($table, 'md5key', md5($record->string), 'id', $record->id);
  56         }
  57     }
  58
  59     return $ok;
  60 }
  61 function hotpot_update_to_v2_1_21() {
  62     global $CFG;
  63     $ok = true;
  64
  65     if (strtolower($CFG->dbfamily)=='postgres') {
  66         // ensure setting of default values on certain fields
  67         // this was originally done in postgres7.php, but was found to be incompatible with PG7 :-(
  68         $table="hotpot";
  69         execute_sql("UPDATE {$CFG->prefix}$table SET studentfeedbackurl = '' WHERE studentfeedbackurl IS NULL");
  70         $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedbackurl', 'VARCHAR', 255, '',         'NOT NULL', '');
  71         $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedback',    'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  72         $ok = $ok && hotpot_db_update_field_type($table, '', 'clickreporting',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  73
  74         $table="hotpot_attempts";
  75         $ok = $ok && hotpot_db_update_field_type($table, '', 'score',     'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
  76         $ok = $ok && hotpot_db_update_field_type($table, '', 'penalties', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
  77         $ok = $ok && hotpot_db_update_field_type($table, '', 'status',    'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
  78
  79         $table="hotpot_questions";
  80         $ok = $ok && hotpot_db_update_field_type($table, '', 'type',      'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
  81
  82         $table="hotpot_responses";
  83         $ok = $ok && hotpot_db_update_field_type($table, '', 'score',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  84         $ok = $ok && hotpot_db_update_field_type($table, '', 'weighting', 'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  85         $ok = $ok && hotpot_db_update_field_type($table, '', 'correct',   'VARCHAR', 255, '',         'NOT NULL', '');
  86         execute_sql("UPDATE {$CFG->prefix}$table SET wrong = '' WHERE wrong IS NULL");
  87         $ok = $ok && hotpot_db_update_field_type($table, '', 'wrong',     'VARCHAR', 255, '',         'NOT NULL', '');
  88         execute_sql("UPDATE {$CFG->prefix}$table SET ignored = '' WHERE ignored IS NULL");
  89         $ok = $ok && hotpot_db_update_field_type($table, '', 'ignored',   'VARCHAR', 255, '',         'NOT NULL', '');
  90         $ok = $ok && hotpot_db_update_field_type($table, '', 'hints',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  91         $ok = $ok && hotpot_db_update_field_type($table, '', 'clues',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  92         $ok = $ok && hotpot_db_update_field_type($table, '', 'checks',    'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
  93
  94         $table="hotpot_strings";
  95         $ok = $ok && hotpot_db_update_field_type($table, '', 'string', 'TEXT', '', '', 'NOT NULL', '');
  96     }
  97
  98     return $ok;
  99 }
 100 function hotpot_update_to_v2_1_18() {
 101     $ok = true;
 102
 103     // remove all orphan records (there shouldn't be any, but if there are they can mess up the utfdbmigrate)
 104
 105     $ok = $ok && hotpot_remove_orphans('hotpot_attempts', 'hotpot', 'hotpot');
 106     $ok = $ok && hotpot_remove_orphans('hotpot_questions', 'hotpot', 'hotpot');
 107     $ok = $ok && hotpot_remove_orphans('hotpot_responses', 'attempt', 'hotpot_attempts');
 108     $ok = $ok && hotpot_remove_orphans('hotpot_responses', 'question', 'hotpot_questions');
 109     $ok = $ok && hotpot_remove_orphans('hotpot_details', 'attempt', 'hotpot_attempts');
 110
 111     // allow negative weighting and scores
 112
 113     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '6', false);
 114     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6', false);
 115
 116     return $ok;
 117 }
 118 function hotpot_remove_orphans($secondarytable, $secondarykeyfield, $primarytable, $primarykeyfield='id') {
 119     global $CFG,$db;
 120     $ok = true;
 121
 122     // save and switch off SQL message echo
 123     $debug = $db->debug;
 124     $db->debug = false;
 125
 126     $records = get_records_sql("
 127         SELECT
 128             t2.$secondarykeyfield, t2.$secondarykeyfield
 129         FROM
 130             {$CFG->prefix}$secondarytable t2 LEFT JOIN {$CFG->prefix}$primarytable t1
 131             ON (t2.$secondarykeyfield = t1.id)
 132         WHERE
 133             t1.$primarykeyfield IS NULL
 134     ");
 135
 136     // restore SQL message echo setting
 137     $db->debug = $debug;
 138
 139     if ($records) {
 140         $keys = implode(',', array_keys($records));
 141         print "removing orphan record(s) from {$CFG->prefix}$secondarytable<br/>";
 142         $ok = $ok && execute_sql("DELETE FROM {$CFG->prefix}$secondarytable WHERE $secondarykeyfield IN ($keys)");
 143     }
 144
 145     return $ok;
 146 }
 147 function hotpot_update_to_v2_1_17() {
 148     global $CFG;
 149     $ok = true;
 150
 151     // convert and disable null values on certain numeric fields
 152
 153     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'starttime', '10');
 154     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'endtime', '10');
 155     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'score', '6');
 156     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'penalties',  '6');
 157     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'timestart', '10');
 158     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'timefinish', '10');
 159     $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'clickreportid', '10');
 160
 161     $ok = $ok && hotpot_denull_int_field('hotpot_questions', 'type', '4');
 162     $ok = $ok && hotpot_denull_int_field('hotpot_questions', 'text', '10');
 163
 164     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '6', false);
 165     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6', false);
 166     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'hints', '6');
 167     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'clues', '6');
 168     $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'checks', '6');
 169     return $ok;
 170 }
 171 function hotpot_denull_int_field($table, $field, $size, $unsigned=true) {
 172     global $CFG;
 173     $ok = true;
 174
 175     $ok = $ok && execute_sql("UPDATE {$CFG->prefix}$table SET $field=0 WHERE $field IS NULL", false);
 176     if ($unsigned) {
 177         $ok = $ok && execute_sql("UPDATE {$CFG->prefix}$table SET $field=0 WHERE $field<0", false);
 178     }
 179     $ok = $ok && hotpot_db_update_field_type($table, $field, $field, 'INTEGER', $size, $unsigned, 'NOT NULL', 0);
 180
 181     return $ok;
 182 }
 183 function hotpot_update_to_v2_1_16() {
 184     global $CFG;
 185     $ok = true;
 186
 187     // remove the questions name index
 188     hotpot_db_delete_index("{$CFG->prefix}hotpot_questions", "hotpot_questions_name_idx");
 189     hotpot_db_delete_index("{$CFG->prefix}hotpot_questions", "{$CFG->prefix}hotpot_questions_name_idx");
 190
 191     // make sure type of 'name' is a text field (not varchar 255)
 192     $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'name', 'name', 'TEXT',   '',  '', 'NOT NULL', '');
 193
 194     if (strtolower($CFG->dbfamily)=='mysql') {
 195
 196         // set default values on certain VARCHAR(255) fields
 197         $fields = array(
 198             'hotpot' => 'studentfeedbackurl',
 199             'hotpot_responses' => 'correct',
 200             'hotpot_responses' => 'wrong',
 201             'hotpot_responses' => 'ignored'
 202         );
 203         foreach ($fields as $table=>$field) {
 204             execute_sql("UPDATE {$CFG->prefix}$table SET $field='' WHERE $field IS NULL");
 205             $ok = $ok && hotpot_db_update_field_type($table, $field, $field, 'VARCHAR', 255, '', 'NOT NULL', '');
 206         }
 207
 208         // remove $CFG->prefix from all index names
 209         $ok = $ok && hotpot_index_remove_prefix('hotpot_attempts', 'hotpot');
 210         $ok = $ok && hotpot_index_remove_prefix('hotpot_attempts', 'userid');
 211         $ok = $ok && hotpot_index_remove_prefix('hotpot_details', 'attempt');
 212         $ok = $ok && hotpot_index_remove_prefix('hotpot_questions', 'hotpot');
 213         $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'attempt');
 214         $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'question');
 215     }
 216     return $ok;
 217 }
 218 function hotpot_index_remove_prefix($table, $field) {
 219     global $CFG;
 220     hotpot_db_delete_index("{$CFG->prefix}$table", "{$CFG->prefix}{$table}_{$field}_idx");
 221     hotpot_db_delete_index("{$CFG->prefix}$table", "{$table}_{$field}_idx");
 222     return hotpot_db_add_index($table, $field);
 223 }
 224
 225 function hotpot_update_to_v2_1_8() {
 226     global $CFG;
 227     $ok = true;
 228     if (strtolower($CFG->dbfamily)=='postgres') {
 229         // add, delete and rename certain fields and indexes
 230         // that were not correctly setup by postgres7.sql
 231
 232         // hotpot
 233         $table = 'hotpot';
 234         if (hotpot_db_field_exists($table, 'microreporting')) {
 235             $ok = $ok && hotpot_db_update_field_type($table, 'microreporting', 'clickreporting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
 236         }
 237     }
 238     return $ok;
 239 }
 240 function hotpot_update_to_v2_1_6() {
 241     global $CFG;
 242     $ok = true;
 243
 244     if (strtolower($CFG->dbfamily)=='postgres') {
 245         // add, delete and rename certain fields and indexes
 246         // that were not correctly setup by postgres7.sql
 247
 248         // hotpot
 249         $table = 'hotpot';
 250         if (hotpot_db_field_exists($table, 'studentfeedback') && !hotpot_db_field_exists($table, 'studentfeedbackurl')) {
 251             $ok = $ok && hotpot_db_update_field_type($table, 'studentfeedback', 'studentfeedbackurl', 'VARCHAR', 255, '', 'NULL');
 252             $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedback', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
 253         }
 254
 255         // hotpot_attempts
 256         $table = 'hotpot_attempts';
 257         $ok = $ok && hotpot_db_remove_field($table, 'groupid');
 258         if (hotpot_db_field_exists($table, 'microreportid') && !hotpot_db_field_exists($table, 'clickreportid')) {
 259             $ok = $ok && hotpot_db_update_field_type($table, 'microreportid', 'clickreportid', 'INTEGER', 10, 'UNSIGNED', 'NULL');
 260         }
 261     }
 262
 263     return $ok;
 264 }
 265 function hotpot_update_to_v2_1_2() {
 266     global $CFG, $db;
 267     $ok = true;
 268
 269     // save and switch off SQL message echo
 270     $debug = $db->debug;
 271     $db->debug = false;
 272
 273     // extract info about attempts by each user on each hotpot (cases where
 274     // the user has only one attempt, or no "in progess" attempt are ignored)
 275     $rs = $db->Execute("
 276         SELECT userid, hotpot, COUNT(*), MIN(status)
 277         FROM {$CFG->prefix}hotpot_attempts
 278         GROUP BY userid, hotpot
 279         HAVING COUNT(*)>1 AND MIN(status)=1
 280     ");
 281     if ($rs && $rs->RecordCount()) {
 282         $records = $rs->GetArray();
 283
 284         // start message to browser
 285         print "adjusting status of ".count($records)." &quot;in progress&quot; attempts ... ";
 286
 287         // loop through records
 288         foreach ($records as $record) {
 289
 290             // get all attempts by this user at this hotpot
 291             $attempts = get_records_sql("
 292                 SELECT id, userid, hotpot, score, timestart, timefinish, status
 293                 FROM {$CFG->prefix}hotpot_attempts
 294                 WHERE userid = ".$record['userid']." AND hotpot=".$record['hotpot']."
 295                 ORDER BY timestart DESC, id DESC
 296             ");
 297
 298             unset($previous_timestart);
 299
 300             foreach ($attempts as $attempt) {
 301                 // if this attempt has a status of "in progress" and is not
 302                 // the most recent one in the group, set the status to "abandoned"
 303                 if ($attempt->status==1 && isset($previous_timestart)) {
 304                     $values = 'status=3';
 305                     if (empty($attempt->score)) {
 306                         $values .= ',score=0';
 307                     }
 308                     if (empty($attempt->timefinish)) {
 309                         $values .= ",timefinish=$previous_timestart";
 310                     }
 311                     execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET $values WHERE id=$attempt->id", false);
 312                     print ".";
 313                     hotpot_flush(300);
 314                 }
 315                 $previous_timestart = $attempt->timestart;
 316             } // end foreach $attempts
 317         } // end foreach $records
 318
 319         // finish message to browser
 320         print $ok ? get_string('success') : 'failed';
 321         print "<br />\n";
 322     }
 323
 324     // restore SQL message echo setting
 325     $db->debug = $debug;
 326
 327     return $ok;
 328 }
 329 function hotpot_update_to_v2_1() {
 330     global $CFG, $db;
 331     $ok = true;
 332     // hotpot_questions: reduce size of "type" field to "4"
 333     $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'type', 'type', 'INTEGER', 4,  'UNSIGNED', 'NULL');
 334     // hotpot_questions: change type of "name" field to "text"
 335     $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'name', 'name', 'TEXT',   '',  '', 'NOT NULL', '');
 336     // hotpot_questions: nullify empty and non-numeric (shouldn't be any) values in "text" field
 337     switch (strtolower($CFG->dbfamily)) {
 338         case 'mysql' :
 339             $NOT_REGEXP = 'NOT REGEXP';
 340         break;
 341         case 'postgres' :
 342             $NOT_REGEXP = '!~';
 343         break;
 344         default:
 345             $NOT_REGEXP = '';
 346         break;
 347     }
 348     if ($NOT_REGEXP) {
 349         $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_questions SET text=NULL WHERE text $NOT_REGEXP '^[0-9]+$'");
 350     }
 351     // hotpot_questions: change type of "text" field to "INT(10)"
 352     $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'text', 'text', 'INTEGER', 10, 'UNSIGNED', 'NULL');
 353     // hotpot_attempts
 354     // hotpot_attempts: move "details" to separate table
 355     $table = 'hotpot_details';
 356     if (hotpot_db_table_exists($table)) {
 357         // do nothing
 358     } else {
 359         $ok = $ok && hotpot_create_table($table);
 360         switch (strtolower($CFG->dbfamily)) {
 361             case 'mysql' :
 362             case 'postgres' :
 363                 $sql = "
 364                     INSERT INTO {$CFG->prefix}$table (attempt, details)
 365                     SELECT a.id AS attempt, a.details AS details
 366                         FROM {$CFG->prefix}hotpot_attempts a
 367                         WHERE
 368                             a.details IS NOT NULL AND a.details <> ''
 369                             AND a.details LIKE '<?xml%' AND a.details LIKE '%</hpjsresult>'
 370                 ";
 371             break;
 372             default:
 373                 $sql = '';
 374             break;
 375         }
 376         if ($sql) {
 377             $ok = $ok && execute_sql($sql);
 378         }
 379     }
 380     // hotpot_attempts: remove the "details" field
 381     $ok = $ok && hotpot_db_remove_field('hotpot_attempts', 'details');
 382     // hotpot_attempts: create and set status field (1=in-progress, 2=timed-out, 3=abandoned, 4=completed)
 383     $ok = $ok && hotpot_db_update_field_type('hotpot_attempts', '', 'status', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
 384     $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET status=1 WHERE timefinish=0 AND SCORE IS NULL");
 385     $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET status=3 WHERE timefinish>0 AND SCORE IS NULL");
 386     $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET status=4 WHERE timefinish>0 AND SCORE IS NOT NULL");
 387     // hotpot_attempts: create and set clickreport fields
 388     $ok = $ok && hotpot_db_update_field_type('hotpot', '', 'clickreporting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
 389     $ok = $ok && hotpot_db_update_field_type('hotpot_attempts', '', 'clickreportid', 'INTEGER', 10, 'UNSIGNED', 'NULL');
 390     $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET clickreportid=id WHERE clickreportid IS NULL");
 391     // hotpot_attempts: create and set studentfeedback field (0=none, 1=formmail, 2=moodleforum, 3=moodlemessaging)
 392     $ok = $ok && hotpot_db_update_field_type('hotpot', '', 'studentfeedback', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
 393     $ok = $ok && hotpot_db_update_field_type('hotpot', '', 'studentfeedbackurl', 'VARCHAR', 255, '', 'NULL');
 394     // add indexes
 395     $ok = $ok && hotpot_db_add_index('hotpot_attempts', 'hotpot');
 396     $ok = $ok && hotpot_db_add_index('hotpot_attempts', 'userid');
 397     $ok = $ok && hotpot_db_add_index('hotpot_details', 'attempt');
 398     $ok = $ok && hotpot_db_add_index('hotpot_questions', 'hotpot');
 399     $ok = $ok && hotpot_db_add_index('hotpot_responses', 'attempt');
 400     $ok = $ok && hotpot_db_add_index('hotpot_responses', 'question');
 401     // hotpot_string: correct double-encoded HTML entities
 402     $ok = $ok && execute_sql("
 403         UPDATE {$CFG->prefix}hotpot_strings
 404         SET string = REPLACE(string, '&amp;','&')
 405         WHERE string LIKE '%&amp;#%'
 406         AND (string LIKE '<' OR string LIKE '>')
 407     ");
 408     // hotpot_question: remove questions which refer to deleted hotpots
 409     if ($ok) {
 410         // try and get all hotpot records
 411         if ($records = get_records('hotpot')) {
 412             $ids = implode(',', array_keys($records));
 413             $sql = "DELETE FROM {$CFG->prefix}hotpot_questions WHERE hotpot NOT IN ($ids)";
 414         } else {
 415             // remove all question records (because there are no valid hotpot ids)
 416             $sql = "TRUNCATE {$CFG->prefix}hotpot_questions";
 417         }
 418         print "Removing unused question records ...";
 419         execute_sql($sql);
 420     }
 421     if ($ok) {
 422         // remove old 'v6' templates folder (replaced by 'template' folder)
 423         $ds = DIRECTORY_SEPARATOR;
 424         $dir = "mod{$ds}hotpot{$ds}v6";
 425         print "removing old templates ($dir) ... ";
 426         if (hotpot_rm("$CFG->dirroot{$ds}$dir", false)) {
 427             print get_string('success');
 428         } else {
 429             print "failed<br/>Please remove '$CFG->dirroot{$ds}$dir' manually";
 430         }
 431         print "<br />\n";
 432     }
 433     return $ok;
 434 }
 435 function hotpot_update_to_v2_from_v1() {
 436     global $CFG;
 437     $ok = true;
 438     // remove, alter and add fields in database
 439     $table = 'hotpot';
 440     if (hotpot_db_table_exists($table)) {
 441         $ok = $ok && hotpot_update_fields($table);
 442     } else {
 443         $ok = $ok && hotpot_create_table($table);
 444     }
 445     $table = 'hotpot_attempts';
 446     $oldtable = 'hotpot_events';
 447     if (hotpot_db_table_exists($oldtable)) {
 448         $ok = $ok && hotpot_update_fields($oldtable);
 449         $ok = $ok && hotpot_db_append_table($oldtable, $table);
 450     } else {
 451         $ok = $ok && hotpot_create_table($table);
 452     }
 453     // create new tables (from mysql.sql)
 454     $ok = $ok && hotpot_create_table('hotpot_questions');
 455     $ok = $ok && hotpot_create_table('hotpot_responses');
 456     $ok = $ok && hotpot_create_table('hotpot_strings');
 457     // remove redundant scripts
 458     $files = array('coursefiles.php', 'details.php', 'dummy.html', 'hotpot.php', 'hotpot2db.php');
 459     foreach ($files as $file) {
 460         $filepath = "$CFG->dirroot/mod/hotpot/$file";
 461         if (file_exists($filepath)) {
 462             @unlink($filepath); // don't worry about errors
 463         }
 464     }
 465     return $ok;
 466 }
 467 function hotpot_update_to_v2_from_hotpotatoes() {
 468     global $CFG;
 469     $ok = true; // hope for the best!
 470     // check we have the minimum required hotpot module
 471     $minimum = 2005031400;
 472     $module = get_record("modules", "name", "hotpot");
 473     if (empty($module) || $module->version<$minimum) {
 474         if ($module) {
 475             print ("<p>The update to the HotPotatoes module requires at least version $minimum of the HotPot module.</p>");
 476             print ("<p>The current version of the HotPot module on this site is $module->version.</p>");
 477         }
 478         print ("<p>Please install the latest version of the HotPot module and then try the update again.</p>");
 479         $ok = false;
 480     } else {
 481         // arrays to map foreign keys
 482         $new = array();
 483         $new['hotpot'] = array();
 484         $new['attempt'] = array();
 485         $new['question'] = array();
 486         $new['string'] = array();
 487         // save and switch off SQL message echo
 488         global $db;
 489         $debug = $db->debug;
 490         $db->debug = false;
 491         // import hotpotatoes (and save old ids)
 492         $ok = $ok && hotpot_update_fields('hotpotatoes');
 493         $ok = $ok && hotpot_transfer_records('hotpotatoes', 'hotpot', array(), 'hotpot', $new);
 494         // update course modules and logs
 495         $ok = $ok && hotpot_update_course_modules('hotpotatoes', 'hotpot', $new);
 496         // import hotpotatoes_strings (and save old ids)
 497         $ok = $ok && hotpot_transfer_records('hotpotatoes_strings', 'hotpot_strings', array(), 'string', $new);
 498         // import hotpotatoes_attempts (and save old ids)
 499         $ok = $ok && hotpot_transfer_records('hotpotatoes_attempts', 'hotpot_attempts', array('hotpotatoes'=>'hotpot'), 'attempt', $new);
 500         // import hotpotatoes_questions (and save old ids)
 501         $ok = $ok && hotpot_transfer_records('hotpotatoes_questions', 'hotpot_questions', array('hotpotatoes'=>'hotpot'), 'question', $new);
 502         // import hotpotatoes_responses
 503         $ok = $ok && hotpot_transfer_records('hotpotatoes_responses', 'hotpot_responses', array('attempt'=>'attempt', 'question'=>'question'), 'response', $new);
 504         // restore SQL message echo setting
 505         $db->debug = $debug;
 506         // remove the hotpotatoes tables, if the update went ok
 507         if ($ok) {
 508         //  hotpot_db_remove_table('hotpotatoes');
 509         //  hotpot_db_remove_table('hotpotatoes_attempts');
 510         //  hotpot_db_remove_table('hotpotatoes_questions');
 511         //  hotpot_db_remove_table('hotpotatoes_responses');
 512         //  hotpot_db_remove_table('hotpotatoes_strings');
 513         }
 514         // hide the hotpotatoes module (see admin/modules.php))
 515         if ($ok && ($module = get_record("modules", "name", "hotpotatoes"))) {
 516             set_field("modules", "visible", "0", "id", $module->id);
 517             print '<p>All HotPotatoes activities have been imported to the HotPot module.<br />'."\n";
 518             print 'The HotPotatoes module has been hidden and can safely be deleted from this Moodle site.<br />'."\n";
 519             print ' &nbsp; &nbsp; <a href="'.$CFG->wwwroot.'/'.$CFG->admin.'/modules.php">Configuration -> Modules</A>, then click &quot;Delete&quot; for &quot;Hot Potatoes XML Quiz&quot;</p>'."\n";
 520         }
 521     }
 522     if ($ok) {
 523         print '<p align="center">Thank you for using the HotPotatoes module.<br />';
 524         print 'The HotPotatoes module has been replaced by<br />version 2 of the HotPot module. Enjoy!</p>';
 525     }
 526     return $ok;
 527 }
 528 function hotpot_create_table($table) {
 529     global $CFG;
 530
 531     static $sql;
 532     static $xmldb_file;
 533
 534     // check table does not already exist
 535     if (hotpot_db_table_exists($table)) {
 536         return true;
 537     }
 538
 539     if (! isset($xmldb_file)) { // first time only
 540         if (class_exists('XMLDBFile')) {
 541             $xmldb_file = new XMLDBFile("$CFG->dirroot/mod/hotpot/db/install.xml");
 542             if (! $xmldb_file->fileExists() || !$xmldb_file->loadXMLStructure() || !$xmldb_file->isLoaded()) {
 543                 unset($xmldb_file);
 544             }
 545         }
 546         if (empty($xmldb_file)) {
 547             $xmldb_file = false;
 548         }
 549     }
 550
 551     if ($xmldb_file) {
 552         // Moodle 1.8 (and later)
 553         $ok = false;
 554         foreach ($xmldb_file->xmldb_structure->tables as $xmldb_table) {
 555             if ($xmldb_table->name==$table) {
 556                 $ok = create_table($xmldb_table);
 557                 break;
 558             }
 559         }
 560         return $ok;
 561     }
 562
 563     // Moodle 1.7 (and earlier)
 564
 565     if (! isset($sql)) { // first time only
 566         $sqlfilepath = "$CFG->dirroot/mod/hotpot/db/$CFG->dbtype.sql";
 567         if (file_exists($sqlfilepath)) {
 568             if (function_exists('file_get_contents')) {
 569                 $sql = file_get_contents($sqlfilepath);
 570             } else { // PHP < 4.3
 571                 $sql = file($sqlfilepath);
 572                 if (is_array($sql)) {
 573                      $sql = implode('', $sql);
 574                 }
 575             }
 576         }
 577         if (empty($sql)) {
 578             $sql = '';
 579         }
 580     }
 581
 582     // extract and execute all CREATE statements relating to this table
 583     if (preg_match_all("/CREATE (TABLE|INDEX)(\s[^;]*)? prefix_{$table}(\s[^;]*)?;/s", $sql, $strings)) {
 584         $ok = true;
 585         foreach ($strings[0] as $string) {
 586             $ok = $ok && modify_database('', $string);
 587         }
 588         return $ok;
 589     }
 590
 591     // table could not be created
 592     return false;
 593 }
 594 function hotpot_transfer_records($oldtable, $table, $foreignkeys, $primarykey, &$new) {
 595     global $db;
 596     $ok = true;
 597     // get the records, if any
 598     if (hotpot_db_table_exists($oldtable) && ($records = get_records($oldtable))) {
 599         // start progress report
 600         $i = 0;
 601         $count = count($records);
 602         hotpot_update_print("Transferring $count records from &quot;$oldtable&quot; to &quot;$table&quot; ... ");
 603         // transfer all $records
 604         foreach ($records as $record) {
 605             switch ($table) {
 606                 case 'hotpot' :
 607                     $record->summary = addslashes($record->summary);
 608                     break;
 609                 case 'hotpot_attempts' :
 610                     $record->details = addslashes($record->details);
 611                     break;
 612                 case 'hotpot_questions' :
 613                     $record->name = addslashes($record->name);
 614                     hotpot_update_string_id_list($table, $record, 'TEXT', $new);
 615                     break;
 616                 case 'hotpot_responses' :
 617                     hotpot_update_string_id_list($table, $record, 'correct', $new);
 618                     hotpot_update_string_id_list($table, $record, 'ignored', $new);
 619                     hotpot_update_string_id_list($table, $record, 'wrong', $new);
 620                     break;
 621                 case 'hotpot_strings' :
 622                     $record->string = addslashes($record->string);
 623                     break;
 624             }
 625             // update foreign keys, if any
 626             foreach ($foreignkeys as $oldkey=>$key) {
 627                 // transfer (and update) key
 628                 $value = $record->$oldkey;
 629                 if (isset($new[$key][$value])) {
 630                     $record->$key = $new[$key][$value];
 631                 } else {
 632                     // foreign key could not be updated
 633                     $ok = hotpot_update_print_warning($key, $value, $oldtable, $record->id) && $ok;
 634                     unset($record->id);
 635                 }
 636             }
 637             if ($ok && isset($record->id)) {
 638                 // store and remove old primary key
 639                 $id = $record->id;
 640                 unset($record->id);
 641                 // add the updated record and store the new id
 642                 $new[$primarykey][$id] = insert_record($table, $record, true);
 643                 // check id is numeric
 644                 if (!is_numeric($new[$primarykey][$id])) {
 645                     hotpot_update_print("<li>Record could not added to $table table ($oldtable id=$id)</li>\n");
 646                     //$ok = false;
 647                 }
 648             }
 649             $i++;
 650             hotpot_update_print_progress($i);
 651         }
 652         // finish progress report
 653         hotpot_update_print_ok($ok);
 654     }
 655     return $ok;
 656 }
 657 function hotpot_update_course_modules($oldmodulename, $modulename, &$new) {
 658     $ok = true;
 659     $oldmoduleid = get_field('modules', 'id', 'name', $oldmodulename);
 660     $moduleid = get_field('modules', 'id', 'name', $modulename);
 661     if (is_numeric($oldmoduleid) && is_numeric($moduleid)) {
 662         // get module records
 663         if ($records = get_records('course_modules', 'module', $oldmoduleid)) {
 664             // start progress report
 665             $count = count($records);
 666             hotpot_update_print("Updating $count course modules from &quot;$oldmodulename&quot; to &quot;$modulename&quot; ... ");
 667             // update foreign keys in all $records
 668             foreach ($records as $record) {
 669                 // update instance
 670                 $instance = $record->instance;
 671                 if (isset($new[$modulename][$instance])) {
 672                     $record->instance = $new[$modulename][$instance];
 673                 } else if ($record->deleted) {
 674                     unset($record->id);
 675                 } else {
 676                     // could not find new id of course module
 677                     $ok = hotpot_update_print_warning("$modulename instance", $instance, 'course_modules', $record->id) && $ok;
 678                     unset($record->id);
 679                 }
 680                 // update module id
 681                 if ($ok && isset($record->id)) {
 682                     $record->module = $moduleid;
 683                     $ok = update_record('course_modules', $record);
 684                 }
 685             }
 686             // finish progress report
 687             hotpot_update_print_ok($ok);
 688         }
 689         // update logs
 690         $ok = $ok && hotpot_update_logs($oldmodulename, $modulename, $moduleid, $new);
 691     }
 692     return $ok;
 693 }
 694 function hotpot_update_logs($oldmodulename, $modulename, $moduleid, &$new) {
 695     $table = 'log';
 696     $ok = true;
 697     // get log records for the oldmodule
 698     if ($records = get_records($table, 'module', $oldmodulename)) {
 699         // start progress report
 700         $i = 0;
 701         $count = count($records);
 702         hotpot_update_print("Updating $count log records ... ");
 703         // update foreign keys in all $records
 704         foreach ($records as $record) {
 705             // update course module name
 706             $record->module = $modulename;
 707             // check if module id was given (usually it is)
 708             if ($record->cmid) {
 709                 // update course module id, if necessary
 710                 if (isset($new[$modulename][$record->cmid])) {
 711                     $record->cmid = $new[$modulename][$record->cmid];
 712                 } else {
 713                     // could not update course module id
 714                     $ok = hotpot_update_print_warning('cmid', $record->cmid, 'log', $record->id) && $ok;
 715                     unset($record->id);
 716                 }
 717                 // update url and info
 718                 switch ($record->action) {
 719                     case "add":
 720                     case "update":
 721                     case "view":
 722                         $record->url = "view.php?id=".$record->cmid;
 723                         $record->info = $moduleid;
 724                         break;
 725                     case "view all":
 726                         // do nothing
 727                         break;
 728                     case "report":
 729                         $record->url = "report.php?id=".$record->cmid;
 730                         $record->info = $moduleid;
 731                         break;
 732                     case "attempt":
 733                     case "submit":
 734                     case "review":
 735                         $id = substr(strrchr($record->url,"="),1);
 736                         if (isset($new->attempt[$id])) {
 737                             $id = $new->attempt[$id];
 738                         }
 739                         $record->url = "review.php?id=".$record->cmid."&attempt=$id";
 740                         $record->info = $moduleid;
 741                         break;
 742                     default:
 743                         // unknown log action
 744                         $ok = hotpot_update_print_warning('action', $record->action, 'log', $record->id) && $ok;
 745                         unset($record->id);
 746                 } // end switch
 747             }
 748             if (isset($record->id)) {
 749                 $ok = $ok && update_record($table, $record);
 750             }
 751             $i++;
 752             hotpot_update_print_progress($i);
 753         } // end foreach
 754         // finish progress report
 755         hotpot_update_print_ok($ok);
 756     }
 757     return $ok;
 758 }
 759 function hotpot_update_fields($table, $feedback=false) {
 760     global $CFG, $db;
 761     $ok = true;
 762     // check the table exists
 763     if (hotpot_db_table_exists($table)) {
 764         switch ($table) {
 765             case 'hotpot' :
 766                 // == ADD ==
 767                 hotpot_db_update_field_type($table, '', 'location',     'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
 768                 hotpot_db_update_field_type($table, '', 'navigation',   'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
 769                 hotpot_db_update_field_type($table, '', 'outputformat', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);
 770                 hotpot_db_update_field_type($table, '', 'shownextquiz', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
 771                 hotpot_db_update_field_type($table, '', 'forceplugins', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
 772                 hotpot_db_update_field_type($table, '', 'password',     'VARCHAR', 255, '',       'NOT NULL', '');
 773                 hotpot_db_update_field_type($table, '', 'subnet',       'VARCHAR', 255, '',       'NOT NULL', '');
 774                 // == ALTER ==
 775                 hotpot_db_update_field_type($table, 'summary',   'summary',   'TEXT',    '',  '', 'NOT NULL', '');
 776                 hotpot_db_update_field_type($table, 'reference', 'reference', 'VARCHAR', 255, '', 'NOT NULL', '');
 777                 // == REMOVE ==
 778                 hotpot_db_remove_field($table, 'intro');
 779                 hotpot_db_remove_field($table, 'attemptonlast');
 780                 hotpot_db_remove_field($table, 'sumgrades');
 781                 hotpot_db_set_table_comment($table, 'details about Hot Potatoes quizzes');
 782             break;
 783             case 'hotpot_events' :
 784                 // == ADD ==
 785                 hotpot_db_update_field_type($table, '', 'hotpot',     'INTEGER', 10, 'UNSIGNED', 'NOT NULL');
 786                 hotpot_db_update_field_type($table, '', 'attempt',    'INTEGER', 6,  'UNSIGNED', 'NOT NULL');
 787                 hotpot_db_update_field_type($table, '', 'details',    'TEXT',    '', '', '', '');
 788                 hotpot_db_update_field_type($table, '', 'timestart',  'INTEGER', 10, 'UNSIGNED', 'NOT NULL', 0);
 789                 hotpot_db_update_field_type($table, '', 'timefinish', 'INTEGER', 10, 'UNSIGNED', 'NOT NULL', 0);
 790                 // == ALTER ==
 791                 hotpot_db_update_field_type($table, 'score',     'score',      'INTEGER', 6,  'UNSIGNED', 'NULL');
 792                 hotpot_db_update_field_type($table, 'wrong',     'penalties',  'INTEGER', 6,  'UNSIGNED', 'NULL');
 793                 hotpot_db_update_field_type($table, 'starttime', 'starttime',  'INTEGER', 10, 'UNSIGNED', 'NULL');
 794                 hotpot_db_update_field_type($table, 'endtime',   'endtime',    'INTEGER', 10, 'UNSIGNED', 'NULL');
 795                 // save and switch off SQL message echo
 796                 $debug = $db->debug;
 797                 $db->debug = $feedback;
 798                 // get array mapping course module ids to hotpot ids
 799                 $hotpotmoduleid = get_field('modules', 'id', 'name', 'hotpot');
 800                 $coursemodules = get_records('course_modules', 'module', $hotpotmoduleid, 'id', 'id, instance');
 801                 // get all event records
 802                 if (hotpot_db_field_exists($table, 'hotpotid')) {
 803                     $records = get_records($table, '', '', 'userid,hotpotid,time');
 804                 } else {
 805                     $records = false; // table has already been updated
 806                 }
 807                 if ($records) {
 808                     $count = count($records);
 809                     hotpot_update_print("Updating $count records in $table ... ");
 810                     $ids = array_keys($records);
 811                     foreach ($ids as $i=>$id) {
 812                         // reference to current record
 813                         $record = &$records[$id];
 814                         // set timestart and timefinish (the times recorded by Moodle)
 815                         if (empty($record->timestart) && $record->time) {
 816                             $record->timestart = $record->time;
 817                         }
 818                         if (empty($record->timefinish) && $record->timestart) {
 819                             if ($record->starttime && $record->endtime) {
 820                                 $duration = ($record->endtime - $record->starttime);
 821                             } else {
 822                                 if (($i+1)>=$count) {
 823                                     $nextrecord = NULL;
 824                                 } else {
 825                                     $nextrecord = &$records[$ids[$i+1]];
 826                                 }
 827                                 if (isset($nextrecord) && $nextrecord->userid==$record->userid && $nextrecord->hotpotid==$record->hotpotid) {
 828                                     $duration = $nextrecord->time - $record->time;
 829                                 } else {
 830                                     $duration = NULL;
 831                                 }
 832                             }
 833                             if (isset($duration)) {
 834                                 $record->timefinish = $record->timestart + $duration;
 835                             }
 836                         }
 837                         // unset score and penalties, if quiz was abandoned
 838                         if (empty($record->endtime) || (empty($record->penalties) && empty($record->score))) {
 839                             unset($record->score);
 840                             unset($record->penalties);
 841                         }
 842                         // get last (=previous) record
 843                         if ($i==0) {
 844                             $lastrecord = NULL;
 845                         } else {
 846                             $lastrecord = &$records[$ids[$i-1]];
 847                         }
 848                         // increment or reset $attempt number
 849                         if (isset($lastrecord) && $lastrecord->userid==$record->userid && $lastrecord->hotpotid==$record->hotpotid) {
 850                             $attempt++;
 851                         } else {
 852                             $attempt = 1;
 853                         }
 854                         // set $record->$attempt, if necessary
 855                         if (empty($record->attempt) || $record->attempt<$attempt) {
 856                             $record->attempt = $attempt;
 857                         } else {
 858                             $attempt = $record->attempt;
 859                         }
 860                         // set hotpot id and update record
 861                         if (isset($record->hotpotid) && isset($record->id)) {
 862                             if (isset($coursemodules[$record->hotpotid])) {
 863                                 $record->hotpot = $coursemodules[$record->hotpotid]->instance;
 864                                 hotpot_db_update_record($table, $record, true);
 865                             } else {
 866                                 // hotpotid is invalid (shouldn't happen)
 867                                 $ok = hotpot_update_print_warning('hotpotid', $record->hotpotid, $table, $record->id) && $ok;
 868                                 delete_records($table, 'id', $record->id);
 869                             }
 870                         } else {
 871                                 // empty record (shouldn't happen)
 872                         }
 873                         hotpot_update_print_progress($i);
 874                     }
 875                     // finish progress report
 876                     hotpot_update_print_ok($ok);
 877                 }
 878                 // restore SQL message echo setting
 879                 $db->debug = $debug;
 880                 // == REMOVE ==
 881                 hotpot_db_remove_field($table, 'hotpotid');
 882                 hotpot_db_remove_field($table, 'course');
 883                 hotpot_db_remove_field($table, 'time');
 884                 hotpot_db_remove_field($table, 'event');
 885                 hotpot_db_set_table_comment($table, 'details about Hot Potatoes quiz attempts');
 886             break;
 887             case 'hotpotatoes' :
 888                 // == ALTER ==
 889                 hotpot_db_update_field_type($table, 'intro', 'summary', 'TEXT', '', '', '', 'NULL');
 890             break;
 891         }
 892     }
 893     return $ok;
 894 }
 895 function hotpot_update_string_id_list($table, &$record, $field, &$new) {
 896     $ok = true;
 897     if (isset($record->$field)) {
 898         $oldids = explode(',', $record->$field);
 899         $newids = array();
 900         foreach ($oldids as $id) {
 901             if (isset($new['string'][$id])) {
 902                 $newids[] = $new['string'][$id];
 903             } else if (is_numeric($id)) {
 904                 // string id could not be updated
 905                 $ok = hotpot_update_print_warning("string id in $field", $id, $table, $record->id) && $ok;
 906             } else {
 907                 // ignore non-numeric ids (e.g. blanks)
 908             }
 909         }
 910         if ($ok) {
 911             $record->$field = implode(',', $newids);
 912         }
 913     }
 914     return $ok;
 915 }
 916 ///////////////////////////
 917 //     print functions
 918 ///////////////////////////
 919 function hotpot_update_print($msg=false, $n=300) {
 920     // this function prints $msg and flush output buffer
 921     if ($msg) {
 922         if (is_string($msg)) {
 923             print $msg;
 924         } else {
 925             print strftime("%X", time());
 926         }
 927     }
 928     // fill output buffer
 929     if ($n) {
 930         print str_repeat(" ", $n);
 931     }
 932     // some browser's require newline to flush
 933     print "\n";
 934     // flush PHP's output buffer
 935     flush();
 936 }
 937 function hotpot_update_print_progress($i) {
 938     if ($i%10==0) {
 939         $msg = '.';
 940         hotpot_update_print($msg);
 941     }
 942 }
 943 function hotpot_update_print_ok($ok) {
 944     if ($ok) {
 945         hotpot_update_print('<font color="green">'.get_string('success')."</font><br />\n");
 946     } else {
 947         hotpot_update_print('<font color="red">'.get_string('error')."</font><br />\n");
 948     }
 949 }
 950 function hotpot_update_print_warning($field, $value, $table, $id) {
 951     hotpot_update_print("<li><b>Warning:</b> invalid $field field (value=$value) in $table (id=$id)</li>\n");
 952     return true;
 953 }
 954 ///////////////////////////
 955 //     database functions
 956 ///////////////////////////
 957 function hotpot_db_index_exists($table, $index, $feedback=false) {
 958     global $CFG, $db;
 959     $exists = false;
 960     // save and switch off SQL message echo
 961     $debug = $db->debug;
 962     $db->debug = $feedback;
 963     switch (strtolower($CFG->dbfamily)) {
 964         case 'mysql' :
 965             $rs = $db->Execute("SHOW INDEX FROM `$table`");
 966             if ($rs && $rs->RecordCount()>0) {
 967                 $records = $rs->GetArray();
 968                 foreach ($records as $record) {
 969                     if (isset($record['Key_name']) && $record['Key_name']==$index) {
 970                         $exists = true;
 971                         break;
 972                     }
 973                 }
 974             }
 975         break;
 976         case 'postgres' :
 977             $rs = $db->Execute("SELECT relname FROM pg_class WHERE relname = '$index' AND relkind='i'");
 978             if ($rs && $rs->RecordCount()>0) {
 979                 $exists = true;
 980             }
 981         break;
 982     }
 983     // restore SQL message echo
 984     $db->debug = $debug;
 985     return $exists;
 986 }
 987 function hotpot_db_delete_index($table, $index, $feedback=false) {
 988     global $CFG, $db;
 989     $ok = true;
 990     // check index exists
 991     if (hotpot_db_index_exists($table, $index)) {
 992         switch (strtolower($CFG->dbfamily)) {
 993             case 'mysql' :
 994                 $sql = "ALTER TABLE `$table` DROP INDEX `$index`";
 995             break;
 996             case 'postgres' :
 997                 $sql = "DROP INDEX $index";
 998             break;
 999             default: // unknown database type
1000                 $sql = '';
1001             break;
1002         }
1003         if ($sql) {
1004             // save and switch off SQL message echo
1005             $debug = $db->debug;
1006             $db->debug = $feedback;
1007             $ok = $db->Execute($sql) ? true : false;
1008             // restore SQL message echo
1009             $db->debug = $debug;
1010         } else { // unknown database type
1011             $ok = false;
1012         }
1013     }
1014     return $ok;
1015 }
1016 function hotpot_db_add_index($table, $field, $length='') {
1017     global $CFG, $db;
1018
1019     if (strtolower($CFG->dbfamily)=='postgres') {
1020         $index = "{$CFG->prefix}{$table}_{$field}_idx";
1021     } else {
1022         // mysql (and others)
1023         $index = "{$table}_{$field}_idx";
1024     }
1025     $table = "{$CFG->prefix}$table";
1026
1027     // delete $index if it already exists
1028     $ok = hotpot_db_delete_index($table, $index);
1029
1030     switch (strtolower($CFG->dbfamily)) {
1031         case 'mysql' :
1032             $ok = $ok && $db->Execute("ALTER TABLE `$table` ADD INDEX `$index` (`$field`)");
1033         break;
1034         case 'postgres' :
1035             $ok = $ok && $db->Execute("CREATE INDEX $index ON $table (\"$field\")");
1036         break;
1037         default: // unknown database type
1038             $ok = false;
1039         break;
1040     }
1041     return $ok;
1042 }
1043 function hotpot_db_table_exists($table, $feedback=false) {
1044     return hotpot_db_object_exists($table, '', $feedback);
1045 }
1046 function hotpot_db_field_exists($table, $field, $feedback=false) {
1047     return
1048         hotpot_db_object_exists($table, '', $feedback) &&
1049         hotpot_db_object_exists($table, $field, $feedback)
1050     ;
1051 }
1052 function hotpot_db_object_exists($table, $field='', $feedback=false) {
1053     global $CFG,$db;
1054     // expand table name
1055     $table = "{$CFG->prefix}$table";
1056     // set $sql
1057     switch (strtolower($CFG->dbfamily)) {
1058         case 'mysql' :
1059             if (empty($field)) {
1060                 $sql = "SHOW TABLES LIKE '$table'";
1061             } else {
1062                 $sql = "SHOW COLUMNS FROM `$table` LIKE '$field'";
1063             }
1064         break;
1065         case 'postgres' :
1066             if (empty($field)) {
1067                 $sql = "SELECT relname FROM pg_class WHERE relname = '$table' AND relkind='r'";
1068             } else {
1069                 $sql = "
1070                     SELECT attname FROM pg_attribute WHERE attname = '$field'
1071                     AND attrelid = (SELECT oid FROM pg_class WHERE relname = '$table')
1072                 ";
1073             }
1074         break;
1075     }
1076     // save and switch off SQL message echo
1077     $debug = $db->debug;
1078     $db->debug = $feedback;
1079     // execute sql
1080     $rs = $db->Execute($sql);
1081     // restore SQL message echo setting
1082     $db->debug = $debug;
1083     // report error if required
1084     if (empty($rs) && debugging()) {
1085         notify($db->ErrorMsg()."<br /><br />$sql");
1086     }
1087     return ($rs && $rs->RecordCount()>0);
1088 }
1089 function hotpot_db_remove_table($table, $feedback=true) {
1090     global $CFG;
1091     if (hotpot_db_table_exists($table)) {
1092         $ok = execute_sql("DROP TABLE {$CFG->prefix}$table", $feedback);
1093     } else {
1094         $ok = true;
1095     }
1096     return $ok;
1097 }
1098 function hotpot_db_rename_table($oldtable, $table, $feedback=true) {
1099     global $CFG;
1100     if (hotpot_db_table_exists($oldtable)) {
1101         $ok = execute_sql("ALTER TABLE {$CFG->prefix}$oldtable RENAME TO {$CFG->prefix}$table", $feedback);
1102     } else {
1103         $ok = true;
1104     }
1105     return $ok;
1106 }
1107 function hotpot_db_append_table($oldtable, $table, $feedback=true) {
1108     global $CFG, $db;
1109     if (hotpot_db_table_exists($oldtable)) {
1110         if (hotpot_db_table_exists($table)) {
1111             // expand table names
1112             $table = "{$CFG->prefix}$table";
1113             $oldtable = "{$CFG->prefix}$oldtable";
1114             // get field info
1115             $fields = $db->MetaColumns($table);
1116             $oldfields = $db->MetaColumns($oldtable);
1117             $fieldnames = array();
1118             if (!empty($fields) || !empty($oldfields)) {
1119                 foreach ($fields as $field) {
1120                     if ($field->name!='id' && isset($oldfields[strtoupper($field->name)])) {
1121                         $fieldnames[] = $field->name;
1122                     }
1123                 }
1124             }
1125             $fieldnames = implode(',', $fieldnames);
1126             if (empty($fieldnames)) {
1127                 $ok = false;
1128             } else {
1129                 switch (strtolower($CFG->dbfamily)) {
1130                     case 'mysql':
1131                         $ok = execute_sql("INSERT INTO `$table` ($fieldnames) SELECT $fieldnames FROM `$oldtable` WHERE 1");
1132                         break;
1133                     case 'postgres':
1134                         $ok = execute_sql("INSERT INTO $table ($fieldnames) SELECT $fieldnames FROM $oldtable");
1135                         break;
1136                     default:
1137                         $ok = false;
1138                         break;
1139                 }
1140             }
1141         } else { // $table does not exist
1142             $ok = hotpot_db_rename_table($oldtable, $table, $feedback);
1143         }
1144     } else { // $oldtable does not exist
1145         $ok = hotpot_db_table_exists($table, $feedback);
1146     }
1147     return $ok;
1148 }
1149 function hotpot_db_set_table_comment($table, $comment, $feedback=true) {
1150     global $CFG;
1151     $ok = true;
1152     switch (strtolower($CFG->dbfamily)) {
1153         case 'mysql' :
1154             $ok = execute_sql("ALTER TABLE {$CFG->prefix}$table COMMENT='$comment'");
1155             break;
1156         case 'postgres' :
1157             $ok = execute_sql("COMMENT ON TABLE {$CFG->prefix}$table IS '$comment'");
1158             break;
1159     }
1160     return $ok;
1161 }
1162 function hotpot_db_remove_field($table, $field, $feedback=true) {
1163     global $CFG;
1164     if (hotpot_db_field_exists($table, $field)) {
1165         $ok = execute_sql("ALTER TABLE {$CFG->prefix}$table DROP COLUMN $field", $feedback);
1166     } else {
1167         $ok = true;
1168     }
1169     return $ok;
1170 }
1171 function hotpot_db_update_field_type($table, $oldfield, $field, $type, $size, $unsigned, $notnull, $default=NULL, $after=NULL) {
1172     $ok = true;
1173     global $CFG,$db;
1174     // check validity of arguments, and adjust if necessary
1175     if ($oldfield && !hotpot_db_field_exists($table, $oldfield)) {
1176         $oldfield = '';
1177     }
1178     if (empty($oldfield) && hotpot_db_field_exists($table, $field)) {
1179         $oldfield = $field;
1180     }
1181     if (is_string($unsigned)) {
1182         $unsigned = (strtoupper($unsigned)=='UNSIGNED');
1183     }
1184     if (is_string($notnull)) {
1185         $notnull = (strtoupper($notnull)=='NOT NULL');
1186     }
1187     if (isset($default)) {
1188         if (!is_numeric($default) && strtoupper($default)!='NULL' && !preg_match("|^'.*'$|", $default)) {
1189             $default = "'$default'";
1190         }
1191     }
1192     // set full table name
1193     $table = "{$CFG->prefix}$table";
1194     // update the field in the database
1195     switch (strtolower($CFG->dbfamily)) {
1196         case 'mysql':
1197             // optimize integer types
1198             switch (strtoupper($type)) {
1199                 case 'TEXT':
1200                     $size = '';
1201                     $unsigned = false;
1202                 break;
1203                 case 'INTEGER' :
1204                     if (!is_numeric($size)) {
1205                         $size = '';
1206                     } else if ($size <= 4) {
1207                         $type = "TINYINT";   // 1 byte
1208                     } else if ($size <= 6) {
1209                         $type = "SMALLINT";  // 2 bytes
1210                     } else if ($size <= 8) {
1211                         $type = "MEDIUMINT"; // 3 bytes
1212                     } else if ($size <= 10) {
1213                         $type = "INTEGER";   // 4 bytes (=INT)
1214                     } else if ($size > 10) {
1215                         $type = "BIGINT";    // 8 bytes
1216                     }
1217                 break;
1218                 case 'VARCHAR':
1219                     $unsigned = false;
1220                 break;
1221             }
1222             // set action
1223             if (empty($oldfield)) {
1224                 $action = "ADD";
1225             } else {
1226                 $action = "CHANGE `$oldfield`";
1227             }
1228             // set fieldtype
1229             $fieldtype = $type;
1230             if ($size) {
1231                 $fieldtype .= "($size)";
1232             }
1233             if ($unsigned) {
1234                 $fieldtype .= ' UNSIGNED';
1235             }
1236             if ($notnull) {
1237                 $fieldtype .= ' NOT NULL';
1238             }
1239             if (isset($default)) {
1240                 $fieldtype .= " DEFAULT $default";
1241             }
1242             if (!empty($after)) {
1243                 $fieldtype .= " AFTER `$after`";
1244             }
1245             $ok = $ok && execute_sql("ALTER TABLE `$table` $action `$field` $fieldtype");
1246         break;
1247         case 'postgres':
1248             // get db version
1249             //    N.B. $db->ServerInfo() usually returns blank
1250             //    (except lib/adodb/drivers/adodb-postgre64-inc.php)
1251             $dbversion = '';
1252             $rs = $db->Execute("SELECT version()");
1253             if ($rs && $rs->RecordCount()>0) {
1254                 $records = $rs->GetArray();
1255                 if (preg_match('/\d+\.\d+/', $records[0][0], $matches)) {
1256                     $dbversion = $matches[0];
1257                 }
1258             }
1259             $tmpfield = 'temporary_'.$field.'_'.time();
1260             switch (strtoupper($type)) {
1261                 case "INTEGER":
1262                     if (!is_numeric($size)) {
1263                         $fieldtype = "INTEGER";
1264                     } else if ($size <= 4) {
1265                         $fieldtype = "INT2"; // 2 bytes
1266                     } else if ($size <= 10) {
1267                         $fieldtype = "INT4"; // 4 bytes (=INTEGER)
1268                     } else if ($size > 10) {
1269                         $fieldtype = "INT8"; // 8 bytes
1270                     }
1271                 break;
1272                 case "VARCHAR":
1273                     $fieldtype = "VARCHAR($size)";
1274                 break;
1275                 default:
1276                     $fieldtype = $type;
1277             }
1278             // start transaction
1279             execute_sql('BEGIN');
1280             // create temporary field
1281             execute_sql('ALTER TABLE '.$table.' ADD COLUMN "'.$tmpfield.'" '.$fieldtype);
1282             // set default
1283             if (isset($default)) {
1284                 execute_sql('UPDATE '.$table.' SET "'.$tmpfield.'" = '.$default);
1285                 execute_sql('ALTER TABLE '.$table.' ALTER COLUMN "'.$tmpfield.'" SET DEFAULT '.$default);
1286             } else {
1287                 execute_sql('ALTER TABLE '.$table.' ALTER COLUMN "'.$tmpfield.'" DROP DEFAULT');
1288             }
1289             // set not null
1290             if ($dbversion=='' || $dbversion >= "7.3") {
1291                 $notnull = ($notnull ? 'SET NOT NULL' : 'DROP NOT NULL');
1292                 execute_sql('ALTER TABLE '.$table.' ALTER COLUMN "'.$tmpfield.'" '.$notnull);
1293             } else {
1294                 execute_sql("
1295                     UPDATE pg_attribute SET attnotnull=".($notnull ? 'TRUE' : 'FALSE')."
1296                     WHERE attname = '$tmpfield'
1297                     AND attrelid = (SELECT oid FROM pg_class WHERE relname = '$table')
1298                 ");
1299             }
1300             // transfer $oldfield values, if necessary
1301             if ( $oldfield != '' ) {
1302                 execute_sql('UPDATE '.$table.' SET "'.$tmpfield.'" = CAST ("'.$oldfield.'" AS '.$fieldtype.')');
1303                 execute_sql('ALTER TABLE '.$table.' DROP COLUMN "'.$oldfield.'"');
1304             }
1305             // rename $tmpfield to $field
1306             execute_sql('ALTER TABLE '.$table.' RENAME COLUMN "'.$tmpfield.'" TO "'.$field.'"');
1307             // do the transaction
1308             execute_sql('COMMIT');
1309             // reclaim disk space (must be done outside transaction)
1310             if ($oldfield != '' && $dbversion >= "7.3") {
1311                 execute_sql('UPDATE '.$table.' SET "'.$field.'" = "'.$field.'"');
1312                 execute_sql('VACUUM FULL '.$table);
1313             }
1314         break;
1315     } // end switch $CGF->dbfamily
1316     return $ok;
1317 }
1318 function hotpot_db_update_record($table, $record, $forcenull=false) {
1319     global $CFG, $db;
1320     $ok = true;
1321     // set full table name
1322     $table = "{$CFG->prefix}$table";
1323     // get field names
1324     $fields = $db->MetaColumns($table);
1325     if (empty($fields)) {
1326         $ok = false;
1327     } else {
1328         // get values
1329         $values = array();
1330         foreach ($fields as $field) {
1331             $fieldname = $field->name;
1332             if ($fieldname!='id' && ($forcenull || isset($record->$fieldname))) {
1333                 $value = isset($record->$fieldname) ? "'".$record->$fieldname."'" : 'NULL';
1334                 $values[] = "$fieldname = $value";
1335             }
1336         }
1337         $values = implode(',', $values);
1338         // update values (if there are any)
1339         if ($values) {
1340             $sql = "UPDATE $table SET $values WHERE id='$record->id'";
1341             $rs = $db->Execute($sql);
1342             if (empty($rs)) {
1343                 $ok = false;
1344                 debugging($db->ErrorMsg()."<br /><br />$sql");
1345             }
1346         }
1347     }
1348     return $ok;
1349 }
1350 function hotpot_rm($target, $output=true) {
1351     $ok = true;
1352     if (!empty($target)) {
1353         if (is_file($target)) {
1354             if ($output) {
1355                 print "removing file: $target ... ";
1356             }
1357             $ok = @unlink($target);
1358         } else if (is_dir($target)) {
1359             $dir = dir($target);
1360             while(false !== ($entry = $dir->read())) {
1361                 if ($entry!='.' && $entry!='..') {
1362                     $ok = $ok && hotpot_rm($target.DIRECTORY_SEPARATOR.$entry, $output);
1363                 }
1364             }
1365             $dir->close();
1366             if ($output) {
1367                 print "removing folder: $target ... ";
1368             }
1369             $ok = $ok && @rmdir($target);
1370         } else { // not a file or directory (probably doesn't exist)
1371             $output = false;
1372         }
1373         if ($output) {
1374             if ($ok) {
1375                 print '<font color="green">OK</font><br />';
1376             } else {
1377                 print '<font color="red">Failed</font><br />';
1378             }
1379         }
1380     }
1381     return $ok;
1382 }
1383 function hotpot_flush($n=0, $time=false) {
1384     if ($time) {
1385         $t = strftime("%X",time());
1386     } else {
1387         $t = "";
1388     }
1389     echo str_repeat(" ", $n) . $t . "\n";
1390     flush();
1391 }
1392 ?>