mysql.php

很棒的在线教学系统

        // One table for handling units for numerical questions
        $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_numerical_units` (
                               `id` int(10) unsigned NOT NULL auto_increment,
                               `question` int(10) unsigned NOT NULL default '0',
                               `multiplier` decimal(40,20) NOT NULL default '1.00000000000000000000',
                               `unit` varchar(50) NOT NULL default '',
                               PRIMARY KEY  (`id`)
                ) TYPE=MyISAM COMMENT='Optional unit options for numerical questions'; ");

        // Four tables for handling distribution and storage of
        // individual data for dataset dependent question types
        $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_attemptonlast_datasets` (
                               `id` int(10) unsigned NOT NULL auto_increment,
                               `category` int(10) unsigned NOT NULL default '0',
                               `userid` int(10) unsigned NOT NULL default '0',
                               `datasetnumber` int(10) unsigned NOT NULL default '0',
                               PRIMARY KEY  (`id`),
                               UNIQUE KEY `category` (`category`,`userid`)
            ) TYPE=MyISAM COMMENT='Dataset number for attemptonlast attempts per user'; ");
        $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_dataset_definitions` (
                               `id` int(10) unsigned NOT NULL auto_increment,
                               `category` int(10) unsigned NOT NULL default '0',
                               `name` varchar(255) NOT NULL default '',
                               `type` int(10) NOT NULL default '0',
                               `options` varchar(255) NOT NULL default '',
                               `itemcount` int(10) unsigned NOT NULL default '0',
                               PRIMARY KEY  (`id`)
            ) TYPE=MyISAM COMMENT='Organises and stores properties for dataset items'; ");
        $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_dataset_items` (
                               `id` int(10) unsigned NOT NULL auto_increment,
                               `definition` int(10) unsigned NOT NULL default '0',
                               `number` int(10) unsigned NOT NULL default '0',
                               `value` varchar(255) NOT NULL default '',
                               PRIMARY KEY  (`id`),
                               KEY `definition` (`definition`)
                             ) TYPE=MyISAM COMMENT='Individual dataset items'; ");
        $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_question_datasets` (
                               `id` int(10) unsigned NOT NULL auto_increment,
                               `question` int(10) unsigned NOT NULL default '0',
                               `datasetdefinition` int(10) unsigned NOT NULL default '0',
                               PRIMARY KEY  (`id`),
                               KEY `question` (`question`,`datasetdefinition`)
            ) TYPE=MyISAM COMMENT='Many-many relation between questions and dataset definitions'; ");

        // One table for new question type calculated
        //  - the first dataset dependent question type
        $success = $success && modify_database ("", " CREATE TABLE `prefix_quiz_calculated` (
                               `id` int(10) unsigned NOT NULL auto_increment,
                               `question` int(10) unsigned NOT NULL default '0',
                               `answer` int(10) unsigned NOT NULL default '0',
                               `tolerance` varchar(20) NOT NULL default '0.0',
                               `tolerancetype` int(10) NOT NULL default '1',
                               `correctanswerlength` int(10) NOT NULL default '2',
                               PRIMARY KEY  (`id`),
                               KEY `question` (`question`)
                ) TYPE=MyISAM COMMENT='Options for questions of type calculated'; ");
    }

    if ($success && $oldversion < 2004111400) {
        $success = $success && table_column("quiz_responses", "answer", "answer", "text", "", "", "", "not null");
    }

    if ($success && $oldversion < 2004111700) {
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz DROP INDEX course;",false);
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_calculated DROP INDEX answer;",false);
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_categories DROP INDEX course;",false);
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_dataset_definitions DROP INDEX category;",false);
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_numerical DROP INDEX question;",false);
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_numerical_units DROP INDEX question;",false);
        $success = $success && execute_sql("ALTER TABLE {$CFG->prefix}quiz_questions DROP INDEX category;",false);

        $success = $success && modify_database('','ALTER TABLE prefix_quiz ADD INDEX course (course);');
        $success = $success && modify_database('','ALTER TABLE prefix_quiz_calculated ADD INDEX answer (answer);');
        $success = $success && modify_database('','ALTER TABLE prefix_quiz_categories ADD INDEX course (course);');
        $success = $success && modify_database('','ALTER TABLE prefix_quiz_dataset_definitions ADD INDEX category (category);');
        $success = $success && modify_database('','ALTER TABLE prefix_quiz_numerical ADD INDEX question (question);');
        $success = $success && modify_database('','ALTER TABLE prefix_quiz_numerical_units ADD INDEX question (question);');
        $success = $success && modify_database('','ALTER TABLE prefix_quiz_questions ADD INDEX category (category);');
    }

    if ($success && $oldversion < 2004120501) {
        $success = $success && table_column("quiz_calculated", "", "correctanswerformat", "integer", "10", "", "2", "not null", "correctanswerlength");
    }

    if ($success && $oldversion < 2004121400) {  // New field to determine popup window behaviour
        $success = $success && table_column("quiz", "", "popup", "integer", "4", "", "0", "not null", "subnet");
    }

    if ($success && $oldversion < 2005010201) {
        $success = $success && table_column('quiz_categories', '', 'parent');
        $success = $success && table_column('quiz_categories', '', 'sortorder', 'integer', '10', '', '999');
    }

    if ($success && $oldversion < 2005010300) {
        $success = $success && table_column("quiz", "", "questionsperpage", "integer", "10", "", "0", "not null", "review");
    }

    if ($success && $oldversion < 2005012700) {
        $success = $success && table_column('quiz_grades', 'grade', 'grade', 'real', 2, '');
    }

    if ($success && $oldversion < 2005021400) {
        $success = $success && table_column("quiz", "", "decimalpoints", "integer", "4", "", "2", "not null", "grademethod");
    }

    if($success && $oldversion < 2005022800) {
        $success = $success && table_column('quiz_questions', '', 'hidden', 'integer', '1', 'unsigned', '0', 'not null', 'version');
        $success = $success && table_column('quiz_responses', '', 'originalquestion', 'integer', '10', 'unsigned', '0', 'not null', 'question');
        $success = $success && modify_database ('', "CREATE TABLE `prefix_quiz_question_version` (
                              `id` int(10) unsigned NOT NULL auto_increment,
                              `quiz` int(10) unsigned NOT NULL default '0',
                              `oldquestion` int(10) unsigned NOT NULL default '0',
                              `newquestion` int(10) unsigned NOT NULL default '0',
                              `userid` int(10) unsigned NOT NULL default '0',
                              `timestamp` int(10) unsigned NOT NULL default '0',
                              PRIMARY KEY  (`id`)
                            ) TYPE=MyISAM COMMENT='The mapping between old and new versions of a question';");
    }

    if ($success && $oldversion < 2005032000) {
        $success = $success && execute_sql(" INSERT INTO {$CFG->prefix}log_display (module, action, mtable, field) VALUES ('quiz', 'editquestions', 'quiz', 'name') ");
    }

    if ($success && $oldversion < 2005032300) {
        $success = $success && modify_database ('', 'ALTER TABLE prefix_quiz_question_version RENAME prefix_quiz_question_versions;');
    }

    if ($success && $oldversion < 2005041200) { // replace wiki-like with markdown
        include_once( "$CFG->dirroot/lib/wiki_to_markdown.php" );
        $wtm = new WikiToMarkdown();
        $sql = "select course from {$CFG->prefix}quiz_categories c, {$CFG->prefix}quiz_questions q ";
        $sql .= "where c.id = q.category ";
        $sql .= "and q.id = ";
        $wtm->update( 'quiz_questions', 'questiontext', 'questiontextformat', $sql );
    }

    if ($success && $oldversion < 2005041304) {
        // make random questions hidden
        $success = $success && modify_database('', "UPDATE prefix_quiz_questions SET hidden = '1' WHERE qtype ='".RANDOM."';");
    }

    if ($success && $oldversion < 2005042002) {
        $success = $success && table_column('quiz_answers', 'answer', 'answer', 'text', '', '', '', 'not null', '');
    }

    if ($success && $oldversion < 2005042400) {

    // Changes to quiz table

        // The bits of the optionflags field will hold various option flags
        $success = $success && table_column('quiz', '', 'optionflags', 'integer', '10', 'unsigned', '0', 'not null', 'timeclose');

        // The penalty scheme
        $success = $success && table_column('quiz', '', 'penaltyscheme', 'integer', '4', 'unsigned', '0', 'not null', 'optionflags');

        // The review options are now all stored in the bits of the review field
        $success = $success && table_column('quiz', 'review', 'review', 'integer', 10, 'unsigned', 0, 'not null', '');

    /// Changes to quiz_attempts table

        // The preview flag marks teacher previews
        $success = $success && table_column('quiz_attempts', '', 'preview', 'tinyint', '2', 'unsigned', '0', 'not null', 'timemodified');

        // The layout is the list of questions with inserted page breaks.
        $success = $success && table_column('quiz_attempts', '', 'layout', 'text', '', '', '', 'not null', 'timemodified');
        // For old quiz attempts we will set this to the repaginated question list from $quiz->questions

    /// The following updates of field values require a loop through all quizzes
        // This is because earlier versions of mysql don't allow joins in UPDATE
        if ($quizzes = get_records('quiz')) {

            // turn reporting off temporarily to avoid one line output per set_field
            $olddebug = $db->debug;
            $db->debug = false;
            echo 'Now updating '.count($quizzes).' quizzes';
            foreach ($quizzes as $quiz) {

                // repaginate
                if ($quiz->questionsperpage) {
                    $quiz->questions = quiz_repaginate($quiz->questions, $quiz->questionsperpage);
                    $success = $success && set_field('quiz', 'questions', $quiz->questions, 'id', $quiz->id);
                }
                $success = $success && set_field('quiz_attempts', 'layout', $quiz->questions, 'quiz', $quiz->id);

                // set preview flag
                if ($teachers = get_course_teachers($quiz->course)) {
                    $teacherids = implode(',', array_keys($teachers));
                    $success = $success && execute_sql("UPDATE {$CFG->prefix}quiz_attempts SET preview = 1 WHERE userid IN ($teacherids)");
                }

                // set review flags in quiz table
                $review = (QUIZ_REVIEW_IMMEDIATELY & (QUIZ_REVIEW_RESPONSES + QUIZ_REVIEW_SCORES));
                if ($quiz->feedback) {
                    $review += (QUIZ_REVIEW_IMMEDIATELY & QUIZ_REVIEW_FEEDBACK);
                }
                if ($quiz->correctanswers) {
                    $review += (QUIZ_REVIEW_IMMEDIATELY & QUIZ_REVIEW_ANSWERS);
                }
                if ($quiz->review & 1) {
                    $review += QUIZ_REVIEW_CLOSED;
                }
                if ($quiz->review & 2) {
                    $review += QUIZ_REVIEW_OPEN;
                }
                $success = $success && set_field('quiz', 'review', $review, 'id', $quiz->id);
            }
            $db->debug = $olddebug;
        }

        // We can now drop the fields whose data has been moved to the review field
        $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz` DROP feedback");
        $success = $success && execute_sql(" ALTER TABLE `{$CFG->prefix}quiz` DROP correctanswers");

    /// Renaming tables

        // rename the quiz_question_grades table to quiz_question_instances
        $success = $success && modify_database ('', 'ALTER TABLE prefix_quiz_question_grades RENAME prefix_quiz_question_instances;');

        // rename the quiz_responses table quiz_states