mod/hotpot/db/update_to_v2.php

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