mod/quiz/db/mysql.php

   1 <?php // $Id$
   2
   3 // THIS FILE IS DEPRECATED!  PLEASE DO NOT MAKE CHANGES TO IT!
   4 //
   5 // IT IS USED ONLY FOR UPGRADES FROM BEFORE MOODLE 1.7, ALL
   6 // LATER CHANGES SHOULD USE upgrade.php IN THIS DIRECTORY.
   7
   8 function quiz_upgrade($oldversion) {
   9 // This function does anything necessary to upgrade
  10 // older versions to match current functionality
  11
  12     global $CFG, $QTYPES, $db;
  13     $success = true;
  14
  15     require_once("$CFG->dirroot/mod/quiz/locallib.php");
  16
  17     if ($success && $oldversion < 2002101800) {
  18         $success = $success && execute_sql(" ALTER TABLE `quiz_attempts` ".
  19                     " ADD `timestart` INT(10) UNSIGNED DEFAULT '0' NOT NULL AFTER `sumgrades` , ".
  20                     " ADD `timefinish` INT(10) UNSIGNED DEFAULT '0' NOT NULL AFTER `timestart` ");
  21         $success = $success && execute_sql(" UPDATE `quiz_attempts` SET timestart = timemodified ");
  22         $success = $success && execute_sql(" UPDATE `quiz_attempts` SET timefinish = timemodified ");
  23     }
  24     if ($success && $oldversion < 2002102101) {
  25         $success = $success && execute_sql(" DELETE FROM log_display WHERE module = 'quiz' ");
  26         $success = $success && execute_sql(" INSERT INTO log_display (module, action, mtable, field) VALUES ('quiz', 'view', 'quiz', 'name') ");
  27         $success = $success && execute_sql(" INSERT INTO log_display (module, action, mtable, field) VALUES ('quiz', 'report', 'quiz', 'name') ");
  28         $success = $success && execute_sql(" INSERT INTO log_display (module, action, mtable, field) VALUES ('quiz', 'attempt', 'quiz', 'name') ");
  29         $success = $success && execute_sql(" INSERT INTO log_display (module, action, mtable, field) VALUES ('quiz', 'submit', 'quiz', 'name') ");
  30     }
  31     if ($success && $oldversion < 2002102600) {
  32         $success = $success && execute_sql(" ALTER TABLE `quiz_answers` CHANGE `feedback` `feedback` TEXT NOT NULL ");
  33     }
  34
  35     if ($success && $oldversion < 2002122300) {
  36         $success = $success && execute_sql("ALTER TABLE `quiz_grades` CHANGE `user` `userid` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
  37         $success = $success && execute_sql("ALTER TABLE `quiz_attempts` CHANGE `user` `userid` INT(10) UNSIGNED DEFAULT '0' NOT NULL ");
  38     }
  39
  40     // prefixes required from here on, or use table_column()
  41
  42     if ($success && $oldversion < 2003010100) {
  43         $success = $success && execute_sql(" ALTER TABLE {$CFG->prefix}quiz ADD review TINYINT(4) UNSIGNED DEFAULT '0' NOT NULL AFTER `grademethod` ");
  44     }
  45
  46     if ($success && $oldversion < 2003010301) {
  47         $success = $success && table_column("quiz_truefalse", "true", "trueanswer", "INTEGER", "10", "UNSIGNED", "0", "NOT NULL", "");
  48         $success = $success && table_column("quiz_truefalse", "false", "falseanswer", "INTEGER", "10", "UNSIGNED", "0", "NOT NULL", "");
  49         $success = $success && table_column("quiz_questions", "type", "qtype", "INTEGER", "10", "UNSIGNED", "0", "NOT NULL", "");
  50     }
  51
  52     if ($success && $oldversion < 2003022303) {
  53         $success = $success && modify_database ("", "CREATE TABLE `prefix_quiz_randommatch` (
  54                              `id` int(10) unsigned NOT NULL auto_increment,
  55                              `question` int(10) unsigned NOT NULL default '0',
  56                              `choose` INT UNSIGNED DEFAULT '4' NOT NULL,
  57                              PRIMARY KEY ( `id` )
  58                           );");
  59     }
  60
  61     if ($success && $oldversion < 2003030303) {
  62         $success = $success && table_column("quiz_questions", "", "defaultgrade", "INTEGER", "6", "UNSIGNED", "1", "NOT NULL", "image");
  63     }
  64
  65     if ($success && $oldversion < 2003032601) {
  66         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_answers` ADD INDEX(question) ");
  67         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_attempts` ADD INDEX(quiz) ");
  68         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_attempts` ADD INDEX(userid) ");
  69         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_grades` ADD INDEX(quiz) ");
  70         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_grades` ADD INDEX(userid) ");
  71         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_question_grades` ADD INDEX(quiz) ");
  72         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_question_grades` ADD INDEX(question) ");
  73         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_randommatch` ADD INDEX(question) ");
  74         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_responses` ADD INDEX(attempt) ");
  75         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz_responses` ADD INDEX(question) ");
  76     }
  77
  78     if ($success && $oldversion < 2003033100) {
  79         $success = $success && modify_database ("", "ALTER TABLE prefix_quiz_randommatch RENAME prefix_quiz_randomsamatch ");
  80         $success = $success && modify_database ("", "CREATE TABLE `prefix_quiz_match` (
  81                              `id` int(10) unsigned NOT NULL auto_increment,
  82                              `question` int(10) unsigned NOT NULL default '0',
  83                              `subquestions` varchar(255) NOT NULL default '',
  84                              PRIMARY KEY  (`id`),
  85                              KEY `question` (`question`)
  86                            );");
  87
  88         $success = $success && modify_database ("", "CREATE TABLE `prefix_quiz_match_sub` (
  89                              `id` int(10) unsigned NOT NULL auto_increment,
  90                              `question` int(10) unsigned NOT NULL default '0',
  91                              `questiontext` text NOT NULL,
  92                              `answertext` varchar(255) NOT NULL default '',
  93                              PRIMARY KEY  (`id`),
  94                              KEY `question` (`question`)
  95                            );");
  96     }
  97
  98     if ($success && $oldversion < 2003040901) {
  99         $success = $success && table_column("quiz", "", "shufflequestions", "INTEGER", "4", "UNSIGNED", "0", "NOT NULL", "review");
 100         $success = $success && table_column("quiz", "", "shuffleanswers", "INTEGER", "4", "UNSIGNED", "0", "NOT NULL", "shufflequestions");
 101     }
 102
 103     if ($success && $oldversion < 2003071001) {
 104
 105         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_numerical` (
 106                                `id` int(10) unsigned NOT NULL auto_increment,
 107                                `question` int(10) unsigned NOT NULL default '0',
 108                                `answer` int(10) unsigned NOT NULL default '0',
 109                                `min` varchar(255) NOT NULL default '',
 110                                `max` varchar(255) NOT NULL default '',
 111                                PRIMARY KEY  (`id`),
 112                                KEY `answer` (`answer`)
 113                              ) TYPE=MyISAM COMMENT='Options for numerical questions'; ");
 114     }
 115
 116     if ($success && $oldversion < 2003072400) {
 117         $success = $success && execute_sql(" INSERT INTO {$CFG->prefix}log_display (module, action, mtable, field) VALUES ('quiz', 'review', 'quiz', 'name') ");
 118     }
 119
 120     if ($success && $oldversion < 2003072901) {
 121         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_multianswers` (
 122                                `id` int(10) unsigned NOT NULL auto_increment,
 123                                 `question` int(10) unsigned NOT NULL default '0',
 124                                 `answers` varchar(255) NOT NULL default '',
 125                                 `positionkey` varchar(255) NOT NULL default '',
 126                                 `answertype` smallint(6) NOT NULL default '0',
 127                                 `norm` int(10) unsigned NOT NULL default '1',
 128                                 PRIMARY KEY  (`id`),
 129                                 KEY `question` (`question`)
 130                               ) TYPE=MyISAM COMMENT='Options for multianswer questions'; ");
 131     }
 132
 133     if ($success && $oldversion < 2003080301) {
 134         $success = $success && execute_sql(" ALTER TABLE {$CFG->prefix}quiz ADD eachattemptbuildsonthelast TINYINT(4) DEFAULT '0' NOT NULL AFTER `attempts` ");
 135     }
 136
 137     if ($success && $oldversion < 2003080400) {
 138         $success = $success && table_column("quiz", "eachattemptbuildsonthelast", "attemptonlast", "TINYINT", "4", "UNSIGNED", "0", "NOT NULL", "");
 139     }
 140
 141     if ($success && $oldversion < 2003082300) {
 142         $success = $success && table_column("quiz_questions", "", "stamp", "varchar", "255", "", "", "not null", "qtype");
 143     }
 144
 145     if ($success && $oldversion < 2003082301) {
 146         $success = $success && table_column("quiz_questions", "stamp", "stamp", "varchar", "255", "", "", "not null");
 147         $success = $success && table_column("quiz_questions", "", "version", "integer", "10", "", "1", "not null", "stamp");
 148         if ($questions = get_records("quiz_questions")) {
 149             foreach ($questions as $question) {
 150                 $stamp = make_unique_id_code();
 151                 if (!($success = $success && set_field("quiz_questions", "stamp", $stamp, "id", $question->id))) {
 152                     notify("Error while adding stamp to question id = $question->id");
 153                     break;
 154                 }
 155             }
 156         }
 157     }
 158
 159     if ($success && $oldversion < 2003082700) {
 160         $success = $success && table_column("quiz_categories", "", "stamp", "varchar", "255", "", "", "not null");
 161         if ($categories = get_records("quiz_categories")) {
 162             foreach ($categories as $category) {
 163                 $stamp = make_unique_id_code();
 164                 if (!($success = $success && set_field("quiz_categories", "stamp", $stamp, "id", $category->id))) {
 165                     notify("Error while adding stamp to category id = $category->id");
 166                     break;
 167                 }
 168             }
 169         }
 170     }
 171
 172     if ($success && $oldversion < 2003111100) {
 173         $duplicates = get_records_sql("SELECT stamp as id,count(*) as cuenta
 174                                        FROM {$CFG->prefix}quiz_questions
 175                                        GROUP BY stamp
 176                                        HAVING count(*)>1");
 177
 178         if ($duplicates) {
 179             notify("You have some quiz questions with duplicate stamps IDs.  Cleaning these up.");
 180             foreach ($duplicates as $duplicate) {
 181                 $questions = get_records("quiz_questions","stamp",$duplicate->id);
 182                 $add = 1;
 183                 foreach ($questions as $question) {
 184                     echo "Changing question id $question->id stamp to ".$duplicate->id.$add."<br />";
 185                     $success = $success && set_field("quiz_questions","stamp",$duplicate->id.$add,"id",$question->id);
 186                     $add++;
 187                 }
 188             }
 189         } else {
 190             notify("Checked your quiz questions for stamp duplication errors, but no problems were found.", "green");
 191         }
 192     }
 193
 194     if ($success && $oldversion < 2004021300) {
 195         $success = $success && table_column("quiz_questions", "", "questiontextformat", "integer", "2", "", "0", "not null", "questiontext");
 196     }
 197
 198     if ($success && $oldversion < 2004021900) {
 199         $success = $success && modify_database("","INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('quiz', 'add', 'quiz', 'name');");
 200         $success = $success && modify_database("","INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('quiz', 'update', 'quiz', 'name');");
 201     }
 202
 203     if ($success && $oldversion < 2004051700) {
 204         include_once("$CFG->dirroot/mod/quiz/lib.php");
 205         $success = $success && quiz_refresh_events();
 206     }
 207
 208     if ($success && $oldversion < 2004060200) {
 209         $success = $success && execute_sql(" ALTER TABLE {$CFG->prefix}quiz ADD timelimit INT(2) UNSIGNED DEFAULT '0' NOT NULL ");
 210     }
 211
 212     if ($success && $oldversion < 2004070700) {
 213         $success = $success && table_column("quiz", "", "password", "varchar", "255", "", "", "not null", "");
 214         $success = $success && table_column("quiz", "", "subnet", "varchar", "255", "", "", "not null", "");
 215     }
 216
 217     if ($success && $oldversion < 2004073001) {
 218         // Six new tables:
 219
 220         // One table for handling units for numerical questions
 221         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_numerical_units` (
 222                                `id` int(10) unsigned NOT NULL auto_increment,
 223                                `question` int(10) unsigned NOT NULL default '0',
 224                                `multiplier` decimal(40,20) NOT NULL default '1.00000000000000000000',
 225                                `unit` varchar(50) NOT NULL default '',
 226                                PRIMARY KEY  (`id`)
 227                 ) TYPE=MyISAM COMMENT='Optional unit options for numerical questions'; ");
 228
 229         // Four tables for handling distribution and storage of
 230         // individual data for dataset dependent question types
 231         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_attemptonlast_datasets` (
 232                                `id` int(10) unsigned NOT NULL auto_increment,
 233                                `category` int(10) unsigned NOT NULL default '0',
 234                                `userid` int(10) unsigned NOT NULL default '0',
 235                                `datasetnumber` int(10) unsigned NOT NULL default '0',
 236                                PRIMARY KEY  (`id`),
 237                                UNIQUE KEY `category` (`category`,`userid`)
 238             ) TYPE=MyISAM COMMENT='Dataset number for attemptonlast attempts per user'; ");
 239         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_dataset_definitions` (
 240                                `id` int(10) unsigned NOT NULL auto_increment,
 241                                `category` int(10) unsigned NOT NULL default '0',
 242                                `name` varchar(255) NOT NULL default '',
 243                                `type` int(10) NOT NULL default '0',
 244                                `options` varchar(255) NOT NULL default '',
 245                                `itemcount` int(10) unsigned NOT NULL default '0',
 246                                PRIMARY KEY  (`id`)
 247             ) TYPE=MyISAM COMMENT='Organises and stores properties for dataset items'; ");
 248         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_dataset_items` (
 249                                `id` int(10) unsigned NOT NULL auto_increment,
 250                                `definition` int(10) unsigned NOT NULL default '0',
 251                                `number` int(10) unsigned NOT NULL default '0',
 252                                `value` varchar(255) NOT NULL default '',
 253                                PRIMARY KEY  (`id`),
 254                                KEY `definition` (`definition`)
 255                              ) TYPE=MyISAM COMMENT='Individual dataset items'; ");
 256         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_question_datasets` (
 257                                `id` int(10) unsigned NOT NULL auto_increment,
 258                                `question` int(10) unsigned NOT NULL default '0',
 259                                `datasetdefinition` int(10) unsigned NOT NULL default '0',
 260                                PRIMARY KEY  (`id`),
 261                                KEY `question` (`question`,`datasetdefinition`)
 262             ) TYPE=MyISAM COMMENT='Many-many relation between questions and dataset definitions'; ");
 263
 264         // One table for new question type calculated
 265         //  - the first dataset dependent question type
 266         $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_calculated` (
 267                                `id` int(10) unsigned NOT NULL auto_increment,
 268                                `question` int(10) unsigned NOT NULL default '0',
 269                                `answer` int(10) unsigned NOT NULL default '0',
 270                                `tolerance` varchar(20) NOT NULL default '0.0',
 271                                `tolerancetype` int(10) NOT NULL default '1',
 272                                `correctanswerlength` int(10) NOT NULL default '2',
 273                                PRIMARY KEY  (`id`),
 274                                KEY `question` (`question`)
 275                 ) TYPE=MyISAM COMMENT='Options for questions of type calculated'; ");
 276     }
 277
 278     if ($success && $oldversion < 2004111400) {
 279         $success = $success && table_column("quiz_responses", "answer", "answer", "text", "", "", "", "not null");
 280     }
 281
 282     if ($success && $oldversion < 2004111700) {
 283         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz DROP INDEX course;",false);
 284         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_calculated DROP INDEX answer;",false);
 285         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_categories DROP INDEX course;",false);
 286         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_dataset_definitions DROP INDEX category;",false);
 287         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_numerical DROP INDEX question;",false);
 288         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_numerical_units DROP INDEX question;",false);
 289         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_questions DROP INDEX category;",false);
 290
 291         $success = $success && modify_database('','ALTER TABLE prefix_quiz ADD INDEX course (course);');
 292         $success = $success && modify_database('','ALTER TABLE prefix_quiz_calculated ADD INDEX answer (answer);');
 293         $success = $success && modify_database('','ALTER TABLE prefix_quiz_categories ADD INDEX course (course);');
 294         $success = $success && modify_database('','ALTER TABLE prefix_quiz_dataset_definitions ADD INDEX category (category);');
 295         $success = $success && modify_database('','ALTER TABLE prefix_quiz_numerical ADD INDEX question (question);');
 296         $success = $success && modify_database('','ALTER TABLE prefix_quiz_numerical_units ADD INDEX question (question);');
 297         $success = $success && modify_database('','ALTER TABLE prefix_quiz_questions ADD INDEX category (category);');
 298     }
 299
 300     if ($success && $oldversion < 2004120501) {
 301         $success = $success && table_column("quiz_calculated", "", "correctanswerformat", "integer", "10", "", "2", "not null", "correctanswerlength");
 302     }
 303
 304     if ($success && $oldversion < 2004121400) {  // New field to determine popup window behaviour
 305         $success = $success && table_column("quiz", "", "popup", "integer", "4", "", "0", "not null", "subnet");
 306     }
 307
 308     if ($success && $oldversion < 2005010201) {
 309         $success = $success && table_column('quiz_categories', '', 'parent');
 310         $success = $success && table_column('quiz_categories', '', 'sortorder', 'integer', '10', '', '999');
 311     }
 312
 313     if ($success && $oldversion < 2005010300) {
 314         $success = $success && table_column("quiz", "", "questionsperpage", "integer", "10", "", "0", "not null", "review");
 315     }
 316
 317     if ($success && $oldversion < 2005012700) {
 318         $success = $success && table_column('quiz_grades', 'grade', 'grade', 'real', 2, '');
 319     }
 320
 321     if ($success && $oldversion < 2005021400) {
 322         $success = $success && table_column("quiz", "", "decimalpoints", "integer", "4", "", "2", "not null", "grademethod");
 323     }
 324
 325     if($success && $oldversion < 2005022800) {
 326         $success = $success && table_column('quiz_questions', '', 'hidden', 'integer', '1', 'unsigned', '0', 'not null', 'version');
 327         $success = $success && table_column('quiz_responses', '', 'originalquestion', 'integer', '10', 'unsigned', '0', 'not null', 'question');
 328         $success = $success && modify_database ('', "CREATE TABLE `prefix_quiz_question_version` (
 329                               `id` int(10) unsigned NOT NULL auto_increment,
 330                               `quiz` int(10) unsigned NOT NULL default '0',
 331                               `oldquestion` int(10) unsigned NOT NULL default '0',
 332                               `newquestion` int(10) unsigned NOT NULL default '0',
 333                               `userid` int(10) unsigned NOT NULL default '0',
 334                               `timestamp` int(10) unsigned NOT NULL default '0',
 335                               PRIMARY KEY  (`id`)
 336                             ) TYPE=MyISAM COMMENT='The mapping between old and new versions of a question';");
 337     }
 338
 339     if ($success && $oldversion < 2005032000) {
 340         $success = $success && execute_sql(" INSERT INTO {$CFG->prefix}log_display (module, action, mtable, field) VALUES ('quiz', 'editquestions', 'quiz', 'name') ");
 341     }
 342
 343     if ($success && $oldversion < 2005032300) {
 344         $success = $success && modify_database ('', 'ALTER TABLE prefix_quiz_question_version RENAME prefix_quiz_question_versions;');
 345     }
 346
 347     if ($success && $oldversion < 2005041200) { // replace wiki-like with markdown
 348         include_once( "$CFG->dirroot/lib/wiki_to_markdown.php" );
 349         $wtm = new WikiToMarkdown();
 350         $sql = "select course from {$CFG->prefix}quiz_categories c, {$CFG->prefix}quiz_questions q ";
 351         $sql .= "where c.id = q.category ";
 352         $sql .= "and q.id = ";
 353         $wtm->update( 'quiz_questions', 'questiontext', 'questiontextformat', $sql );
 354     }
 355
 356     if ($success && $oldversion < 2005041304) {
 357         // make random questions hidden
 358         $success = $success && modify_database('', "UPDATE prefix_quiz_questions SET hidden = '1' WHERE qtype ='".RANDOM."';");
 359     }
 360
 361     if ($success && $oldversion < 2005042002) {
 362         $success = $success && table_column('quiz_answers', 'answer', 'answer', 'text', '', '', '', 'not null', '');
 363     }
 364
 365     if ($success && $oldversion < 2005042400) {
 366
 367     // Changes to quiz table
 368
 369         // The bits of the optionflags field will hold various option flags
 370         $success = $success && table_column('quiz', '', 'optionflags', 'integer', '10', 'unsigned', '0', 'not null', 'timeclose');
 371
 372         // The penalty scheme
 373         $success = $success && table_column('quiz', '', 'penaltyscheme', 'integer', '4', 'unsigned', '0', 'not null', 'optionflags');
 374
 375         // The review options are now all stored in the bits of the review field
 376         $success = $success && table_column('quiz', 'review', 'review', 'integer', 10, 'unsigned', 0, 'not null', '');
 377
 378     /// Changes to quiz_attempts table
 379
 380         // The preview flag marks teacher previews
 381         $success = $success && table_column('quiz_attempts', '', 'preview', 'tinyint', '2', 'unsigned', '0', 'not null', 'timemodified');
 382
 383         // The layout is the list of questions with inserted page breaks.
 384         $success = $success && table_column('quiz_attempts', '', 'layout', 'text', '', '', '', 'not null', 'timemodified');
 385         // For old quiz attempts we will set this to the repaginated question list from $quiz->questions
 386
 387     /// The following updates of field values require a loop through all quizzes
 388         // This is because earlier versions of mysql don't allow joins in UPDATE
 389         if ($quizzes = get_records('quiz')) {
 390
 391             // turn reporting off temporarily to avoid one line output per set_field
 392             $olddebug = $db->debug;
 393             $db->debug = false;
 394             echo 'Now updating '.count($quizzes).' quizzes';
 395             foreach ($quizzes as $quiz) {
 396
 397                 // repaginate
 398                 if ($quiz->questionsperpage) {
 399                     $quiz->questions = quiz_repaginate($quiz->questions, $quiz->questionsperpage);
 400                     $success = $success && set_field('quiz', 'questions', $quiz->questions, 'id', $quiz->id);
 401                 }
 402                 $success = $success && set_field('quiz_attempts', 'layout', $quiz->questions, 'quiz', $quiz->id);
 403
 404                 // set preview flag
 405                 if ($teachers = get_course_teachers($quiz->course)) {
 406                     $teacherids = implode(',', array_keys($teachers));
 407                     $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_attempts SET preview = 1 WHERE userid IN ($teacherids)");
 408                 }
 409
 410                 // set review flags in quiz table
 411                 $review = (QUIZ_REVIEW_IMMEDIATELY & (QUIZ_REVIEW_RESPONSES + QUIZ_REVIEW_SCORES));
 412                 if ($quiz->feedback) {
 413                     $review += (QUIZ_REVIEW_IMMEDIATELY & QUIZ_REVIEW_FEEDBACK);
 414                 }
 415                 if ($quiz->correctanswers) {
 416                     $review += (QUIZ_REVIEW_IMMEDIATELY & QUIZ_REVIEW_ANSWERS);
 417                 }
 418                 if ($quiz->review & 1) {
 419                     $review += QUIZ_REVIEW_CLOSED;
 420                 }
 421                 if ($quiz->review & 2) {
 422                     $review += QUIZ_REVIEW_OPEN;
 423                 }
 424                 $success = $success && set_field('quiz', 'review', $review, 'id', $quiz->id);
 425             }
 426             $db->debug = $olddebug;
 427         }
 428
 429         // We can now drop the fields whose data has been moved to the review field
 430         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz` DROP feedback");
 431         $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz` DROP correctanswers");
 432
 433     /// Renaming tables
 434
 435         // rename the quiz_question_grades table to quiz_question_instances
 436         $success = $success && modify_database ('', 'ALTER TABLE prefix_quiz_question_grades RENAME prefix_quiz_question_instances;');
 437
 438         // rename the quiz_responses table quiz_states
 439         $success = $success && modify_database ('', 'ALTER TABLE prefix_quiz_responses RENAME prefix_quiz_states;');
 440
 441     /// add columns to quiz_states table
 442
 443         // The sequence number of the state.
 444         $success = $success && table_column('quiz_states', '', 'seq_number', 'integer', '6', 'unsigned', '0', 'not null', 'originalquestion');
 445         // For existing states we leave this at 0 because in the old quiz code there was only one response allowed
 446
 447         // The time the state was created.
 448         $success = $success && table_column('quiz_states', '', 'timestamp', 'integer', '10', 'unsigned', '0', 'not null', 'answer');
 449         // For existing states we will below set this to the timemodified field of the attempt
 450
 451         // The type of event that led to the creation of the state
 452         $success = $success && table_column('quiz_states', '', 'event', 'integer', '4', 'unsigned', '0', 'not null', 'timestamp');
 453
 454         // The raw grade
 455         $success = $success && table_column('quiz_states', '', 'raw_grade', 'varchar', '10', '', '', 'not null', 'grade');
 456         // For existing states (no penalties) this is equal to the grade
 457         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_states SET raw_grade = grade");
 458
 459         // The penalty that the response attracted
 460         $success = $success && table_column('quiz_states', '', 'penalty', 'varchar', '10', '', '0.0', 'not null', 'raw_grade');
 461         // For existing states this can stay at 0 because penalties did not exist previously.
 462
 463     /// New table for pointers to newest and newest graded states
 464
 465         $success = $success && modify_database('', "CREATE TABLE `prefix_quiz_newest_states` (
 466                              `id` int(10) unsigned NOT NULL auto_increment,
 467                              `attemptid` int(10) unsigned NOT NULL default '0',
 468                              `questionid` int(10) unsigned NOT NULL default '0',
 469                              `new` int(10) unsigned NOT NULL default '0',
 470                              `newgraded` int(10) unsigned NOT NULL default '0',
 471                              `sumpenalty` varchar(10) NOT NULL default '0.0',
 472                              PRIMARY KEY  (`id`),
 473                              UNIQUE KEY `attemptid` (`attemptid`,`questionid`)
 474                            ) TYPE=MyISAM COMMENT='Gives ids of the newest open and newest graded states';");
 475
 476     /// Now upgrade some fields in states and newest_states tables where necessary
 477         // to save time on large sites only do this for attempts that have not yet been finished.
 478         if ($attempts = get_records_select('quiz_attempts', 'timefinish = 0')) {
 479             echo 'Update the states for the '.count($attempts).' open attempts';
 480             // turn reporting off temporarily to avoid one line output per set_field
 481             $olddebug = $db->debug;
 482             $db->debug = false;
 483             foreach ($attempts as $attempt) {
 484                 quiz_upgrade_states($attempt);
 485             }
 486             $db->debug = $olddebug;
 487         }
 488
 489     /// Entries for the log_display table
 490
 491         $success = $success && modify_database('', " INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('quiz', 'preview', 'quiz', 'name');");
 492         $success = $success && modify_database('', " INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('quiz', 'start attempt', 'quiz', 'name');");
 493         $success = $success && modify_database('', " INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('quiz', 'close attempt', 'quiz', 'name');");
 494
 495     /// update the default settings in $CFG
 496         $review = (QUIZ_REVIEW_IMMEDIATELY & (QUIZ_REVIEW_RESPONSES + QUIZ_REVIEW_SCORES));
 497         if (!empty($CFG->quiz_feedback)) {
 498             $review += (QUIZ_REVIEW_IMMEDIATELY & QUIZ_REVIEW_FEEDBACK);
 499         }
 500         if (!empty($CFG->quiz_correctanswers)) {
 501             $review += (QUIZ_REVIEW_IMMEDIATELY & QUIZ_REVIEW_ANSWERS);
 502         }
 503         if (isset($CFG->quiz_review) and ($CFG->quiz_review & 1)) {
 504             $review += QUIZ_REVIEW_CLOSED;
 505         }
 506         if (isset($CFG->quiz_review) and ($CFG->quiz_review & 2)) {
 507             $review += QUIZ_REVIEW_OPEN;
 508         }
 509         $success = $success && set_config('quiz_review', $review);
 510
 511     /// Use tolerance instead of min and max in numerical question type
 512         $success = $success && table_column('quiz_numerical', '', 'tolerance', 'varchar', '255', '', '0.0', 'not null', 'answer');
 513         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_numerical SET tolerance = (max-min)/2");
 514         $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_numerical` DROP `min`'); // Replaced by tolerance
 515         $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_numerical` DROP `max`'); // Replaced by tolerance
 516
 517     /// Tables for Remote Questions
 518         $success = $success && modify_database ('', "CREATE TABLE `prefix_quiz_rqp` (
 519                               `id` int(10) unsigned NOT NULL auto_increment,
 520                               `question` int(10) unsigned NOT NULL default '0',
 521                               `type` int(10) unsigned NOT NULL default '0',
 522                               `source` longblob NOT NULL default '',
 523                               `format` varchar(255) NOT NULL default '',
 524                               `flags` tinyint(3) unsigned NOT NULL default '0',
 525                               `maxscore` int(10) unsigned NOT NULL default '1',
 526                               PRIMARY KEY  (`id`),
 527                               KEY `question` (`question`)
 528                               ) TYPE=MyISAM COMMENT='Options for RQP questions';");
 529
 530         $success = $success && modify_database ('', "CREATE TABLE `prefix_quiz_rqp_type` (
 531                               `id` int(10) unsigned NOT NULL auto_increment,
 532                               `name` varchar(255) NOT NULL default '',
 533                               `rendering_server` varchar(255) NOT NULL default '',
 534                               `cloning_server` varchar(255) NOT NULL default '',
 535                               `flags` tinyint(3) NOT NULL default '0',
 536                               PRIMARY KEY  (`id`),
 537                               UNIQUE KEY `name` (`name`)
 538                               ) TYPE=MyISAM COMMENT='RQP question types and the servers to be used to process them';");
 539
 540         $success = $success && modify_database ('', "CREATE TABLE `prefix_quiz_rqp_states` (
 541                               `id` int(10) unsigned NOT NULL auto_increment,
 542                               `stateid` int(10) unsigned NOT NULL default '0',
 543                               `responses` text NOT NULL default '',
 544                               `persistent_data` text NOT NULL default '',
 545                               `template_vars` text NOT NULL default '',
 546                               PRIMARY KEY  (`id`)
 547                               ) TYPE=MyISAM COMMENT='RQP question type specific state information';");
 548     }
 549
 550     if ($success && $oldversion < 2005050300) {
 551         // length of question determines question numbering. Currently all questions require one
 552         // question number except for DESCRIPTION questions.
 553         $success = $success && table_column('quiz_questions', '', 'length', 'integer', '10', 'unsigned', '1', 'not null', 'qtype');
 554         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_questions SET length = 0 WHERE qtype ='7'");
 555     }
 556
 557     if ($success && $oldversion < 2005050408) {
 558         $success = $success && table_column('quiz_questions', '', 'penalty', 'float', '', '', '0.1', 'not null', 'defaultgrade');
 559         $success = $success && table_column('quiz_newest_states', 'new', 'newest', 'integer', '10', 'unsigned', '0', 'not null');
 560     }
 561
 562     if ($success && $oldversion < 2005051400) {
 563         $success = $success && modify_database('', 'ALTER TABLE prefix_quiz_rqp_type RENAME prefix_quiz_rqp_types;');
 564         $success = $success && modify_database('', "CREATE TABLE `prefix_quiz_rqp_servers` (
 565                       id int(10) unsigned NOT NULL auto_increment,
 566                       typeid int(10) unsigned NOT NULL default '0',
 567                       url varchar(255) NOT NULL default '',
 568                       can_render tinyint(2) unsigned NOT NULL default '0',
 569                       can_author tinyint(2) unsigned NOT NULL default '0',
 570                       PRIMARY KEY  (id)
 571                     ) TYPE=MyISAM COMMENT='Information about RQP servers';");
 572         if ($types = get_records('quiz_rqp_types')) {
 573             foreach($types as $type) {
 574                 $server = new stdClass;
 575                 $server->typeid = $type->id;
 576                 $server->url = $type->rendering_server;
 577                 $server->can_render = 1;
 578                 $success = $success && insert_record('quiz_rqp_servers', $server);
 579             }
 580         }
 581         $success = $success && modify_database('', 'ALTER TABLE prefix_quiz_rqp_types DROP rendering_server');
 582         $success = $success && modify_database('', 'ALTER TABLE prefix_quiz_rqp_types DROP cloning_server');
 583         $success = $success && modify_database('', 'ALTER TABLE prefix_quiz_rqp_types DROP flags');
 584     }
 585
 586     if ($success && $oldversion < 2005051401) {
 587         // Some earlier changes are undone here, so we need another condition
 588         if ($oldversion >= 2005042900) {
 589             // Restore the answer field
 590             $success = $success && table_column('quiz_numerical', '', 'answer', 'integer', '10', 'unsigned', '0', 'not null', 'answers');
 591             $singleanswer = array();
 592             if ($numericals = get_records('quiz_numerical')) {
 593                 $numericals = array_values($numericals);
 594                 $n = count($numericals);
 595                 for ($i = 0; $i < $n; $i++) {
 596                     $numerical =& $numericals[$i];
 597                     if (strpos($numerical->answers, ',')) { // comma separated list?
 598                         // Back this up to delete the record after the new ones are created
 599                         $id = $numerical->id;
 600                         unset($numerical->id);
 601                         // We need to create a record for each answer id
 602                         $answers = explode(',', $numerical->answers);
 603                         foreach ($answers as $answer) {
 604                             $numerical->answer = $answer;
 605                             $success = $success && insert_record('quiz_numerical', $numerical);
 606                         }
 607                         // ... and get rid of the old record
 608                         $success = $success && delete_records('quiz_numerical', 'id', $id);
 609                     } else {
 610                         $singleanswer[] = $numerical->id;
 611                     }
 612                 }
 613             }
 614
 615             // Do all of these at once
 616             if (!empty($singleanswer)) {
 617                 $singleanswer = implode(',', $singleanswer);
 618                 $success = $success && modify_database('', "UPDATE prefix_quiz_numerical SET answer = answers WHERE id IN ($singleanswer);");
 619             }
 620
 621             // All answer fields are set, so we can delete the answers field
 622             $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_numerical` DROP `answers`');
 623
 624         // If the earlier changes weren't made we can safely do only the
 625         // bits here.
 626         } else {
 627             // Comma separated questionids will be stored as sequence
 628             $success = $success && table_column('quiz_multianswers', '', 'sequence',  'varchar', '255', '', '', 'not null', 'question');
 629             // Change the type of positionkey to int, so that the sorting works!
 630             $success = $success && table_column('quiz_multianswers', 'positionkey', 'positionkey',  'integer', '10', 'unsigned', '0', 'not null', '');
 631             $success = $success && table_column('quiz_questions', '', 'parent', 'integer', '10', 'unsigned', '0', 'not null', 'category');
 632             $success = $success && modify_database('', "UPDATE prefix_quiz_questions SET parent = id WHERE qtype ='".RANDOM."';");
 633
 634             // Each multianswer record is converted to a question object and then
 635             // inserted as a new question into the quiz_questions table.
 636             // After that the question fields in the quiz_answers table and the
 637             // qtype specific tables are updated to point to the new question id.
 638             // Note: The quiz_numerical table is different as it stores one record
 639             //       per defined answer (to allow different tolerance values for
 640             //       different possible answers. (Currently multiple answers are
 641             //       not supported by the numerical editing interface, but
 642             //       all processing code does support that possibility.
 643             if ($multianswers = get_records_sql("SELECT m.id, q.category, " .
 644                                             "q.id AS parent, " . // question id (of multianswer question) as parent
 645                                             "q.name, q.questiontextformat, " .
 646                                             "m.norm AS defaultgrade, " . // norm is snow stored as defaultgrade
 647                                             "m.answertype AS qtype, " .  // just rename this
 648                                             "q.version, q.hidden, m.answers, " .
 649                                             "m.positionkey " .
 650                                             "FROM {$CFG->prefix}quiz_questions q, " .
 651                                             "     {$CFG->prefix}quiz_multianswers m " .
 652                                             "WHERE q.qtype = '".MULTIANSWER."' " .
 653                                             "AND   q.id = m.question " .
 654                                             "ORDER BY q.id ASC, m.positionkey ASC")) { // ordered by positionkey
 655                 $multianswers = array_values($multianswers);
 656                 $n        = count($multianswers);
 657                 $parent   = $multianswers[0]->parent;
 658                 $sequence = array();
 659                 $positions = array();
 660
 661                 // Turn reporting off temporarily to avoid one line output per set_field
 662                 global $db;
 663                 $olddebug = $db->debug;
 664                 $db->debug = false;
 665                 echo 'Now updating '.$n.' cloze questions.';
 666                 for ($i = 0; $i < $n; $i++) {
 667                     // Backup these two values before unsetting the object fields
 668                     $answers = $multianswers[$i]->answers; unset($multianswers[$i]->answers);
 669                     $pos = $multianswers[$i]->positionkey; unset($multianswers[$i]->positionkey);
 670
 671                     // Needed for substituting multianswer ids with position keys in the $state->answer field
 672                     $positions[$multianswers[$i]->id] = $pos;
 673
 674                 // Create questions for all the multianswer victims
 675                     unset($multianswers[$i]->id);
 676                     $multianswers[$i]->length = 0;
 677                     $multianswers[$i]->questiontext = '';
 678                     $multianswers[$i]->stamp = make_unique_id_code();
 679                     $multianswers[$i]->name = addslashes($multianswers[$i]->name);
 680                     // $multianswers[$i]->parent is set in the query
 681                     // $multianswers[$i]->defaultgrade is set in the query
 682                     // $multianswers[$i]->qtype is set in the query
 683                     $id = insert_record('quiz_questions', $multianswers[$i]);
 684                     $success = $success && $id;
 685                     $sequence[$pos] = $id;
 686
 687                 // Update the quiz_answers table to point to these new questions
 688                 $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_answers SET question = '$id' WHERE id IN ($answers)", false);
 689                 // Update the questiontype tables to point to these new questions
 690
 691                     if (SHORTANSWER == $multianswers[$i]->qtype) {
 692                         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_shortanswer SET question = '$id' WHERE answers = '$answers'", false);
 693                     } else if (MULTICHOICE == $multianswers[$i]->qtype) {
 694                         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_multichoice SET question = '$id' WHERE answers = '$answers'", false);
 695                     } else if (NUMERICAL == $multianswers[$i]->qtype) {
 696                         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_numerical SET question = '$id' WHERE answer IN ($answers)", false);
 697                     }
 698
 699                     // Whenever we're through with the subquestions of one multianswer
 700                     // question we delete the old records in the multianswers table,
 701                     // store a new record with the sequence in the multianswers table
 702                     // and point $parent to the next multianswer question.
 703                     if (!isset($multianswers[$i+1]) || $parent != $multianswers[$i+1]->parent) {
 704
 705                         // Substituting multianswer ids with position keys in the $state->answer field
 706                         if ($states = get_records('quiz_states', 'question', $parent)) {
 707                             foreach ($states as $state) {
 708                                 $reg = array();
 709                                 preg_match_all('/(?:^|,)([0-9]+)-([^,]*)/', $state->answer, $reg);
 710                                 $state->answer = '';
 711                                 $m = count($reg[1]);
 712                                 for ($j = 0; $j < $m; $j++) {
 713                                     if (isset($positions[$reg[1][$j]])) {
 714                                         $state->answer .= $positions[$reg[1][$j]] . '-' . $reg[2][$j] . ',';
 715                                     } else {
 716                                         notify("Undefined multianswer id ({$reg[1][$j]}) used in state #{$state->id}!");
 717                                         $state->answer .= $j+1 . '-' . $reg[2][$j] . ',';
 718                                     }
 719                                 }
 720                                 $state->answer = rtrim($state->answer, ','); // strip trailing comma
 721                                 $success = $success && update_record('quiz_states', $state);
 722                             }
 723                         }
 724
 725                         $success = $success && delete_records('quiz_multianswers', 'question', $parent);
 726                         $multi = new stdClass;
 727                         $multi->question = $parent;
 728                         $multi->sequence = implode(',', $sequence);
 729                         $success = $success && insert_record('quiz_multianswers', $multi);
 730
 731                         if (isset($multianswers[$i+1])) {
 732                             $parent    = $multianswers[$i+1]->parent;
 733                             $sequence  = array();
 734                             $positions = array();
 735                         }
 736                     }
 737                 }
 738                 $db->debug = $olddebug;
 739             }
 740
 741             // Remove redundant fields from quiz_multianswers
 742             $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_multianswers` DROP `answers`');
 743             $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_multianswers` DROP `positionkey`');
 744             $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_multianswers` DROP `answertype`');
 745             $success = $success && modify_database('', 'ALTER TABLE `prefix_quiz_multianswers` DROP `norm`');
 746         }
 747     }
 748
 749     if ($success && $oldversion < 2005052004) {
 750         // We need to remove some duplicate entries that may be present in some databases
 751         // due to a faulty restore script
 752
 753         // Remove duplicate entries from quiz_numerical
 754         if ($dups = get_records_sql("
 755                 SELECT question, answer, count(*) as num
 756                 FROM {$CFG->prefix}quiz_numerical
 757                 GROUP BY question, answer
 758                 HAVING count(*) > 1"
 759             )) {
 760             foreach ($dups as $dup) {
 761                 $ids = get_records_sql("
 762                     SELECT id, id
 763                     FROM {$CFG->prefix}quiz_numerical
 764                     WHERE question = '$dup->question'
 765                     AND answer = '$dup->answer'"
 766                 );
 767                 $skip = true;
 768                 foreach ($ids as $id) {
 769                     if ($skip) {
 770                         $skip = false;
 771                     } else {
 772                         $success = $success && delete_records('quiz_numerical','id', $id->id);
 773                     }
 774                 }
 775             }
 776         }
 777
 778         // Remove duplicate entries from quiz_shortanswer
 779         if ($dups = get_records_sql("
 780                 SELECT question, answers, count(*) as num
 781                 FROM {$CFG->prefix}quiz_shortanswer
 782                 GROUP BY question, answers
 783                 HAVING count(*) > 1"
 784             )) {
 785             foreach ($dups as $dup) {
 786                 $ids = get_records_sql("
 787                     SELECT id, id
 788                     FROM {$CFG->prefix}quiz_shortanswer
 789                     WHERE question = '$dup->question'
 790                     AND answers = '$dup->answers'"
 791                 );
 792                 $skip = true;
 793                 foreach ($ids as $id) {
 794                     if ($skip) {
 795                         $skip = false;
 796                     } else {
 797                         $success = $success && delete_records('quiz_shortanswer','id', $id->id);
 798                     }
 799                 }
 800             }
 801         }
 802
 803         // Remove duplicate entries from quiz_multichoice
 804         if ($dups = get_records_sql("
 805                 SELECT question, answers, count(*) as num
 806                 FROM {$CFG->prefix}quiz_multichoice
 807                 GROUP BY question, answers
 808                 HAVING count(*) > 1"
 809             )) {
 810             foreach ($dups as $dup) {
 811                 $ids = get_records_sql("
 812                     SELECT id, id
 813                     FROM {$CFG->prefix}quiz_multichoice
 814                     WHERE question = '$dup->question'
 815                     AND answers = '$dup->answers'"
 816                 );
 817                 $skip = true;
 818                 foreach ($ids as $id) {
 819                     if ($skip) {
 820                         $skip = false;
 821                     } else {
 822                         $success = $success && delete_records('quiz_multichoice','id', $id->id);
 823                     }
 824                 }
 825             }
 826         }
 827     }
 828
 829     if ($success && $oldversion < 2005060300) {
 830         //Search all the orphan categories (those whose course doesn't exist)
 831         //and process them, deleting or moving them to site course - Bug 2459
 832
 833         //Set debug to false
 834         $olddebug = $db->debug;
 835         $db->debug = false;
 836
 837         //Iterate over all the quiz_categories records to get their course id
 838         if ($courses = get_records_sql ("SELECT DISTINCT course as id, course
 839                                          FROM {$CFG->prefix}quiz_categories")) {
 840             //Iterate over courses
 841             foreach ($courses as $course) {
 842                 //If the course doesn't exist, orphan category found!
 843                 //Process it with question_delete_course(). It will do all the hard work.
 844                 if (!record_exists('course', 'id', $course->id)) {
 845                     require_once("$CFG->libdir/questionlib.php");
 846                     $success = $success && question_delete_course($course);
 847                 }
 848             }
 849         }
 850         //Reset rebug to its original state
 851         $db->debug = $olddebug;
 852     }
 853
 854     if ($success && $oldversion < 2005062600) {
 855         $success = $success && modify_database ('', "
 856             CREATE TABLE `prefix_quiz_essay` (
 857                 `id` int(10) unsigned NOT NULL auto_increment,
 858                 `question` int(10) unsigned NOT NULL default '0',
 859                 `answer` varchar(255) NOT NULL default '',
 860                 PRIMARY KEY  (`id`),
 861                 KEY `question` (`question`)
 862            ) TYPE=MyISAM COMMENT='Options for essay questions'");
 863
 864         $success = $success && modify_database ('', "
 865             CREATE TABLE `prefix_quiz_essay_states` (
 866               `id` int(10) unsigned NOT NULL auto_increment,
 867               `stateid` int(10) unsigned NOT NULL default '0',
 868               `graded` tinyint(4) unsigned NOT NULL default '0',
 869               `fraction` varchar(10) NOT NULL default '0.0',
 870               `response` text NOT NULL,
 871               PRIMARY KEY  (`id`)
 872             ) TYPE=MyISAM COMMENT='essay question type specific state information'");
 873     }
 874
 875     if ($success && $oldversion < 2005070202) {
 876         // add new unique id to prepare the way for lesson module to have its own attempts table
 877         $success = $success && table_column('quiz_attempts', '', 'uniqueid', 'integer', '10', 'unsigned', '0', 'not null', 'id');
 878         // initially we can use the id as the unique id because no other modules use attempts yet.
 879         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_attempts SET uniqueid = id", false);
 880         // we set $CFG->attemptuniqueid to the next available id
 881         $record = get_record_sql("SELECT max(id)+1 AS nextid FROM {$CFG->prefix}quiz_attempts");
 882         $success = $success && set_config('attemptuniqueid', empty($record->nextid) ? 1 : $record->nextid);
 883     }
 884
 885     if ($success && $oldversion < 2006020801) {
 886         // add new field to store time delay between the first and second quiz attempt
 887         $success = $success && table_column('quiz', '', 'delay1', 'integer', '10', 'unsigned', '0', 'not null', 'popup');
 888         // add new field to store time delay between the second and any additional quizes
 889         $success = $success && table_column('quiz', '', 'delay2', 'integer', '10', 'unsigned', '0', 'not null', 'delay1');
 890     }
 891
 892     if ($success && $oldversion < 2006021101) {
 893         // set defaultgrade field properly (probably not necessary, but better make sure)
 894         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_questions SET defaultgrade = '1' WHERE defaultgrade = '0'", false);
 895         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_questions SET defaultgrade = '0' WHERE qtype = '7'", false);
 896     }
 897
 898     if ($success && $oldversion < 2006021103) {
 899         // add new field to store the question-level shuffleanswers option
 900         $success = $success && table_column('quiz_match', '', 'shuffleanswers', 'tinyint', '4', 'unsigned', '1', 'not null', 'subquestions');
 901         $success = $success && table_column('quiz_multichoice', '', 'shuffleanswers', 'tinyint', '4', 'unsigned', '1', 'not null', 'single');
 902         $success = $success && table_column('quiz_randomsamatch', '', 'shuffleanswers', 'tinyint', '4', 'unsigned', '1', 'not null', 'choose');
 903     }
 904
 905     if ($success && $oldversion < 2006021104) {
 906         // add originalversion field for the new versioning mechanism
 907         $success = $success && table_column('quiz_question_versions', '', 'originalquestion', 'int', '10', 'unsigned', '0', 'not null', 'newquestion');
 908     }
 909
 910     if ($success && $oldversion < 2006021301) {
 911         $success = $success && modify_database('','ALTER TABLE prefix_quiz_attempts ADD UNIQUE INDEX uniqueid (uniqueid);');
 912     }
 913
 914     if ($success && $oldversion < 2006021302) {
 915         $success = $success && table_column('quiz_match_sub', '', 'code', 'int', '10', 'unsigned', '0', 'not null', 'id');
 916         $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_match_sub SET code = id", false);
 917     }
 918     if ($success && $oldversion < 2006021304) {
 919         // convert sequence field to text to accomodate very long sequences, see bug 4257
 920         $success = $success && table_column('quiz_multianswers', 'sequence', 'sequence',  'text', '', '', '', 'not null', 'question');
 921     }
 922
 923     if ($success && $oldversion < 2006021501) {
 924         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_newest_states RENAME {$CFG->prefix}question_sessions", false);
 925     }
 926
 927     if ($success && $oldversion < 2006022200) {
 928         // convert grade fields to float
 929         $success = $success && set_field('quiz_attempts', 'sumgrades', 0, 'sumgrades', '');
 930         $success = $success && table_column('quiz_attempts', 'sumgrades', 'sumgrades',  'float', '', '', '0', 'not null');
 931
 932         $success = $success && set_field('quiz_answers', 'fraction', 0, 'fraction', '');
 933         $success = $success && table_column('quiz_answers', 'fraction', 'fraction',  'float', '', '', '0', 'not null');
 934
 935         $success = $success && set_field('quiz_essay_states', 'fraction', 0, 'fraction', '');
 936         $success = $success && table_column('quiz_essay_states', 'fraction', 'fraction',  'float', '', '', '0', 'not null');
 937
 938         $success = $success && set_field('quiz_states', 'grade', 0, 'grade', '');
 939         $success = $success && table_column('quiz_states', 'grade', 'grade',  'float', '', '', '0', 'not null');
 940
 941         $success = $success && set_field('quiz_states', 'raw_grade', 0, 'raw_grade', '');
 942         $success = $success && table_column('quiz_states', 'raw_grade', 'raw_grade',  'float', '', '', '0', 'not null');
 943
 944         $success = $success && set_field('quiz_states', 'penalty', 0, 'penalty', '');
 945         $success = $success && table_column('quiz_states', 'penalty', 'penalty',  'float', '', '', '0', 'not null');
 946
 947         $success = $success && set_field('question_sessions', 'sumpenalty', 0, 'sumpenalty', '');
 948         $success = $success && table_column('question_sessions', 'sumpenalty', 'sumpenalty',  'float', '', '', '0', 'not null');
 949     }
 950
 951     if ($success && $oldversion < 2006022400) {
 952         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_questions RENAME {$CFG->prefix}question", false);
 953     }
 954
 955     if ($success && $oldversion < 2006022402) {
 956         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_states RENAME {$CFG->prefix}question_states", false);
 957     }
 958
 959     if ($success && $oldversion < 2006022800) {
 960         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_answers RENAME {$CFG->prefix}question_answers", false);
 961         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_categories RENAME {$CFG->prefix}question_categories", false);
 962     }
 963
 964     if ($success && $oldversion < 2006031202) {
 965         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_truefalse RENAME {$CFG->prefix}question_truefalse", false);
 966         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_shortanswer RENAME {$CFG->prefix}question_shortanswer", false);
 967         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_multianswers RENAME {$CFG->prefix}question_multianswer", false);
 968         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_multichoice RENAME {$CFG->prefix}question_multichoice", false);
 969         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_numerical RENAME {$CFG->prefix}question_numerical", false);
 970         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_numerical_units RENAME {$CFG->prefix}question_numerical_units", false);
 971         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_randomsamatch RENAME {$CFG->prefix}question_randomsamatch", false);
 972         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_match RENAME {$CFG->prefix}question_match", false);
 973         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_match_sub RENAME {$CFG->prefix}question_match_sub", false);
 974         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_calculated RENAME {$CFG->prefix}question_calculated", false);
 975         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_dataset_definitions RENAME {$CFG->prefix}question_dataset_definitions", false);
 976         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_dataset_items RENAME {$CFG->prefix}question_dataset_items", false);
 977         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_question_datasets RENAME {$CFG->prefix}question_datasets", false);
 978         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_rqp RENAME {$CFG->prefix}question_rqp", false);
 979         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_rqp_servers RENAME {$CFG->prefix}question_rqp_servers", false);
 980         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_rqp_states RENAME {$CFG->prefix}question_rqp_states", false);
 981         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_rqp_types RENAME {$CFG->prefix}question_rqp_types", false);
 982         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_essay RENAME {$CFG->prefix}question_essay", false);
 983         $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_essay_states RENAME {$CFG->prefix}question_essay_states", false);
 984     }
 985
 986     if ($success && $oldversion < 2006032100) {
 987         // change from the old questiontype numbers to using the questiontype names
 988         $success = $success && table_column('question', 'qtype', 'qtype',  'varchar', 20, '', '', 'not null');
 989         $success = $success && set_field('question', 'qtype', 'shortanswer', 'qtype', 1);
 990         $success = $success && set_field('question', 'qtype', 'truefalse', 'qtype', 2);
 991         $success = $success && set_field('question', 'qtype', 'multichoice', 'qtype', 3);
 992         $success = $success && set_field('question', 'qtype', 'random', 'qtype', 4);
 993         $success = $success && set_field('question', 'qtype', 'match', 'qtype', 5);
 994         $success = $success && set_field('question', 'qtype', 'randomsamatch', 'qtype', 6);
 995         $success = $success && set_field('question', 'qtype', 'description', 'qtype', 7);
 996         $success = $success && set_field('question', 'qtype', 'numerical', 'qtype', 8);
 997         $success = $success && set_field('question', 'qtype', 'multianswer', 'qtype', 9);
 998         $success = $success && set_field('question', 'qtype', 'calculated', 'qtype', 10);
 999         $success = $success && set_field('question', 'qtype', 'rqp', 'qtype', 11);
1000         $success = $success && set_field('question', 'qtype', 'essay', 'qtype', 12);
1001     }
1002
1003     if ($success && $oldversion < 2006032200) {
1004         // set version for all questiontypes that already have their tables installed
1005         $success = $success && set_config('qtype_calculated_version', 2006032100);
1006         $success = $success && set_config('qtype_essay_version', 2006032100);
1007         $success = $success && set_config('qtype_match_version', 2006032100);
1008         $success = $success && set_config('qtype_multianswer_version', 2006032100);
1009         $success = $success && set_config('qtype_multichoice_version', 2006032100);
1010         $success = $success && set_config('qtype_numerical_version', 2006032100);
1011         $success = $success && set_config('qtype_randomsamatch_version', 2006032100);
1012         $success = $success && set_config('qtype_rqp_version', 2006032100);
1013         $success = $success && set_config('qtype_shortanswer_version', 2006032100);
1014         $success = $success && set_config('qtype_truefalse_version', 2006032100);
1015     }
1016
1017     if ($success && $oldversion < 2006040600) {
1018         $success = $success && table_column('question_sessions', '', 'comment', 'text', '', '', '', 'not null', 'sumpenalty');
1019     }
1020
1021     if ($success && $oldversion < 2006040900) {
1022         $success = $success && modify_database('', "UPDATE prefix_question SET parent = id WHERE qtype ='random';");
1023     }
1024
1025     if ($success && $oldversion < 2006041000) {
1026         $success = $success && modify_database('', " INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('quiz', 'continue attempt', 'quiz', 'name');");
1027     }
1028
1029     if ($success && $oldversion < 2006041001) {
1030         $success = $success && table_column('question', 'version', 'version', 'varchar', 255);
1031     }
1032
1033     if ($success && $oldversion < 2006042800) {
1034         // Check we have some un-renamed tables (verified in some servers)
1035         if ($tables = $db->MetaTables('TABLES')) {
1036             if (in_array($CFG->prefix.'quiz_randommatch', $tables) &&
1037                 !in_array($CFG->prefix.'question_randomsamatch', $tables)) {
1038                 $success = $success && modify_database ("", "ALTER TABLE prefix_quiz_randommatch RENAME prefix_question_randomsamatch ");
1039             }
1040             // Check for one possible missing field in one table
1041             if ($columns = $db->MetaColumnNames($CFG->prefix.'question_randomsamatch')) {
1042                 if (!in_array('shuffleanswers', $columns)) {
1043                     $success = $success && table_column('question_randomsamatch', '', 'shuffleanswers', 'tinyint', '4', 'unsigned', '1', 'not null', 'choose');
1044                 }
1045             }
1046         }
1047     }
1048
1049     if ($oldversion < 2006051300) {
1050         // The newgraded field must always point to a valid state
1051         $success = $success && modify_database("","UPDATE prefix_question_sessions SET newgraded = newest where newgraded = '0'");
1052
1053         // Only perform this if hasn't been performed before (in MOODLE_16_STABLE branch - bug 5717)
1054         $tables = $db->MetaTables('TABLES');
1055         if (!in_array($CFG->prefix . 'question_attempts', $tables)) {
1056             // The following table is discussed in bug 5468
1057             $success = $success && modify_database ("", "CREATE TABLE prefix_question_attempts (
1058                                       id int(10) unsigned NOT NULL auto_increment,
1059                                       modulename varchar(20) NOT NULL default 'quiz',
1060                                       PRIMARY KEY  (id)
1061                                     ) TYPE=MyISAM COMMENT='Student attempts. This table gets extended by the modules';");
1062             // create one entry for all the existing quiz attempts
1063             $success = $success && modify_database ("", "INSERT INTO prefix_question_attempts (id)
1064                                        SELECT uniqueid
1065                                        FROM prefix_quiz_attempts;");
1066         }
1067     }
1068
1069     if ($success && $oldversion < 2006060700) { // fix for 5720
1070
1071         // Copy the teacher comments from the question_essay_states table to the new
1072         // question_sessions table.
1073
1074         // Get the attempt unique ID, teacher comment, graded flag, state ID, and question ID
1075         // based on the quesiont_essay_states
1076         if ($results = get_records_sql("SELECT a.uniqueid, es.response AS essaycomment, es.graded AS isgraded,
1077                                                qs.id AS stateid, qs.question AS questionid
1078                                         FROM {$CFG->prefix}question_states as qs,
1079                                              {$CFG->prefix}question_essay_states es,
1080                                              {$CFG->prefix}quiz_attempts a
1081                                         WHERE es.stateid = qs.id AND a.uniqueid = qs.attempt")) {
1082             foreach ($results as $result) {
1083                 // Create a state object to be used for updating
1084                 $state = new stdClass;
1085                 $state->id = $result->stateid;
1086
1087                 if ($result->isgraded) {
1088                     // Graded - save comment to the sessions and change state event to QUESTION_EVENTMANUALGRADE
1089                     if (!($success = $success && set_field('question_sessions', 'comment', $result->essaycomment, 'attemptid', $result->uniqueid, 'questionid', $result->questionid))) {
1090                         notify("Essay Table Migration: Cannot save comment");
1091                         break;
1092                     }
1093                     $state->event = 9; //QUESTION_EVENTMANUALGRADE;
1094                 } else {
1095                     // Not graded
1096                     $state->event = 7; //QUESTION_EVENTSUBMIT;
1097                 }
1098
1099                 // Save the event
1100                 if (!($success = $success && update_record('question_states', $state))) {
1101                     notify("Essay Table Migration: Cannot update state");
1102                     break;
1103                 }
1104             }
1105         }
1106
1107         // dropping unused tables
1108         $success = $success && execute_sql('DROP TABLE '.$CFG->prefix.'question_essay_states');
1109         $success = $success && execute_sql('DROP TABLE '.$CFG->prefix.'question_essay');
1110         $success = $success && execute_sql('DROP TABLE '.$CFG->prefix.'quiz_attemptonlast_datasets', false);
1111     }
1112
1113     if ($oldversion < 2006081000) {
1114         // Add a column to the the question table to store the question general feedback.
1115         $success = $success && table_column('question', '', 'commentarytext', 'text', '', '', '', 'not null', 'image');
1116
1117         // Adjust the quiz review options so that general feedback is displayed whenever feedback is.
1118         $success = $success && execute_sql('UPDATE ' . $CFG->prefix . 'quiz SET review = ' .
1119                 '(review & ~' . QUIZ_REVIEW_GENERALFEEDBACK . ') | ' . // Clear any existing junk from the commenary bits.
1120                 '((review & ' . QUIZ_REVIEW_FEEDBACK . ') * 8)'); // Set the general feedback bits to be the same as the feedback ones.
1121
1122         // Same adjustment to the defaults for new quizzes.
1123         $success = $success && set_config('quiz_review', ($CFG->quiz_review & ~QUIZ_REVIEW_GENERALFEEDBACK) |
1124                 (($CFG->quiz_review & QUIZ_REVIEW_FEEDBACK) << 3));
1125     }
1126
1127     if ($success && $oldversion < 2006081400) {
1128         $success = $success && modify_database('', "
1129             CREATE TABLE prefix_quiz_feedback (
1130                 id int(10) unsigned NOT NULL auto_increment,
1131                 quizid int(10) unsigned NOT NULL default '0',
1132                 feedbacktext text NOT NULL default '',
1133                 mingrade double NOT NULL default '0',
1134                 maxgrade double NOT NULL default '0',
1135                 PRIMARY KEY (id),
1136                 KEY quizid (quizid)
1137             ) TYPE=MyISAM COMMENT='Feedback given to students based on their overall score on the test';
1138         ");
1139
1140         $success = $success && execute_sql("
1141             INSERT INTO {$CFG->prefix}quiz_feedback (quizid, feedbacktext, maxgrade, mingrade)
1142             SELECT id, '', grade + 1, 0 FROM {$CFG->prefix}quiz;
1143         ");
1144     }
1145
1146     if ($success && $oldversion < 2006082400) {
1147         $success = $success && table_column('question_sessions', 'comment', 'manualcomment', 'text', '', '', '');
1148     }
1149
1150     if ($success && $oldversion < 2006091900) {
1151         $success = $success && table_column('question_dataset_items', 'number', 'itemnumber', 'integer');
1152     }
1153
1154     if ($success && $oldversion < 2006091901) {
1155         $success = $success && table_column('question', 'commentarytext', 'generalfeedback', 'text', '', '', '');
1156     }
1157
1158     //////  DO NOT ADD NEW THINGS HERE!!  USE upgrade.php and the lib/ddllib.php functions.
1159
1160     return $success;
1161 }
1162
1163 ?>