mod/hotpot/db/update_to_v2.php

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