update_to_v2.php

很棒的在线教学系统

<?PHP
if (file_exists("$CFG->dirroot/lib/ddllib.php")) {
    // Moodle 1.8+
    include_once "$CFG->dirroot/lib/ddllib.php";
}

function hotpot_update_to_v2_2() {
    global $CFG;
    $ok = true;

    // remove the index on hotpot_questions.name
    $table = 'hotpot_questions';
    $field = 'name';
    if (strtolower($CFG->dbfamily)=='postgres') {
        $index = "{$CFG->prefix}{$table}_{$field}_idx";
    } else {
        $index = "{$table}_{$field}_idx";
	}
    hotpot_db_delete_index("{$CFG->prefix}$table", $index);

    // add new hotpot_questions.md5key field (and index)
    $table = 'hotpot_questions';
    $field = 'md5key';
    $ok = $ok && hotpot_db_update_field_type($table, '', $field, 'VARCHAR', 32, '', 'NOT NULL', '');
    $ok = $ok && hotpot_db_add_index($table, $field);

    // add new values hotpot_questions.md5key
    $table = 'hotpot_questions';
    if ($records = get_records($table)) {
        foreach ($records as $record) {
            $ok = $ok && set_field($table, 'md5key', md5($record->name), 'id', $record->id);
        }
    }

    // remove the index on hotpot_strings.string
    $table = 'hotpot_strings';
    $field = 'string';
    if (strtolower($CFG->dbfamily)=='postgres') {
        $index = "{$CFG->prefix}{$table}_{$field}_idx";
    } else {
        $index = "{$table}_{$field}_idx";
	}
    hotpot_db_delete_index("{$CFG->prefix}$table", $index);

    // add new hotpot_strings.md5key field (and index)
    $table = 'hotpot_strings';
    $field = 'md5key';
    $ok = $ok && hotpot_db_update_field_type($table, '', $field, 'VARCHAR', 32, '', 'NOT NULL', '');
    $ok = $ok && hotpot_db_add_index($table, $field);

    // add new values hotpot_strings.md5key
    $table = 'hotpot_strings';
    if ($records = get_records($table)) {
        foreach ($records as $record) {
            $ok = $ok && set_field($table, 'md5key', md5($record->string), 'id', $record->id);
        }
    }

    return $ok;
}
function hotpot_update_to_v2_1_21() {
    global $CFG;
    $ok = true;

    if (strtolower($CFG->dbfamily)=='postgres') {
        // ensure setting of default values on certain fields
        // this was originally done in postgres7.php, but was found to be incompatible with PG7 :-(
        $table="hotpot";
        execute_sql("UPDATE {$CFG->prefix}$table SET studentfeedbackurl = '' WHERE studentfeedbackurl IS NULL");
        $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedbackurl', 'VARCHAR', 255, '',         'NOT NULL', '');
        $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedback',    'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'clickreporting',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);

        $table="hotpot_attempts";
        $ok = $ok && hotpot_db_update_field_type($table, '', 'score',     'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'penalties', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'status',    'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 1);

        $table="hotpot_questions";
        $ok = $ok && hotpot_db_update_field_type($table, '', 'type',      'INTEGER', 4, 'UNSIGNED', 'NOT NULL', 0);

        $table="hotpot_responses";
        $ok = $ok && hotpot_db_update_field_type($table, '', 'score',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'weighting', 'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'correct',   'VARCHAR', 255, '',         'NOT NULL', '');
        execute_sql("UPDATE {$CFG->prefix}$table SET wrong = '' WHERE wrong IS NULL");
        $ok = $ok && hotpot_db_update_field_type($table, '', 'wrong',     'VARCHAR', 255, '',         'NOT NULL', '');
        execute_sql("UPDATE {$CFG->prefix}$table SET ignored = '' WHERE ignored IS NULL");
        $ok = $ok && hotpot_db_update_field_type($table, '', 'ignored',   'VARCHAR', 255, '',         'NOT NULL', '');
        $ok = $ok && hotpot_db_update_field_type($table, '', 'hints',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'clues',     'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);
        $ok = $ok && hotpot_db_update_field_type($table, '', 'checks',    'INTEGER', 4,   'UNSIGNED', 'NOT NULL', 0);

        $table="hotpot_strings";
        $ok = $ok && hotpot_db_update_field_type($table, '', 'string', 'TEXT', '', '', 'NOT NULL', '');
    }

    return $ok;
}
function hotpot_update_to_v2_1_18() {
    $ok = true;

    // remove all orphan records (there shouldn't be any, but if there are they can mess up the utfdbmigrate)

    $ok = $ok && hotpot_remove_orphans('hotpot_attempts', 'hotpot', 'hotpot');
    $ok = $ok && hotpot_remove_orphans('hotpot_questions', 'hotpot', 'hotpot');
    $ok = $ok && hotpot_remove_orphans('hotpot_responses', 'attempt', 'hotpot_attempts');
    $ok = $ok && hotpot_remove_orphans('hotpot_responses', 'question', 'hotpot_questions');
    $ok = $ok && hotpot_remove_orphans('hotpot_details', 'attempt', 'hotpot_attempts');

    // allow negative weighting and scores

    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '6', false);
    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6', false);

    return $ok;
}
function hotpot_remove_orphans($secondarytable, $secondarykeyfield, $primarytable, $primarykeyfield='id') {
    global $CFG,$db;
    $ok = true;

    // save and switch off SQL message echo
    $debug = $db->debug;
    $db->debug = false;

    $records = get_records_sql("
        SELECT
            t2.$secondarykeyfield, t2.$secondarykeyfield
        FROM
            {$CFG->prefix}$secondarytable t2 LEFT JOIN {$CFG->prefix}$primarytable t1
            ON (t2.$secondarykeyfield = t1.id)
        WHERE
            t1.$primarykeyfield IS NULL
    ");

    // restore SQL message echo setting
    $db->debug = $debug;

    if ($records) {
        $keys = implode(',', array_keys($records));
        print "removing orphan record(s) from {$CFG->prefix}$secondarytable<br/>";
        $ok = $ok && execute_sql("DELETE FROM {$CFG->prefix}$secondarytable WHERE $secondarykeyfield IN ($keys)");
    }

    return $ok;
}
function hotpot_update_to_v2_1_17() {
    global $CFG;
    $ok = true;

    // convert and disable null values on certain numeric fields

    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'starttime', '10');
    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'endtime', '10');
    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'score', '6');
    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'penalties',  '6');
    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'timestart', '10');
    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'timefinish', '10');
    $ok = $ok && hotpot_denull_int_field('hotpot_attempts', 'clickreportid', '10');

    $ok = $ok && hotpot_denull_int_field('hotpot_questions', 'type', '4');
    $ok = $ok && hotpot_denull_int_field('hotpot_questions', 'text', '10');

    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'weighting', '6', false);
    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'score', '6', false);
    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'hints', '6');
    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'clues', '6');
    $ok = $ok && hotpot_denull_int_field('hotpot_responses', 'checks', '6');
    return $ok;
}
function hotpot_denull_int_field($table, $field, $size, $unsigned=true) {
    global $CFG;
    $ok = true;

    $ok = $ok && execute_sql("UPDATE {$CFG->prefix}$table SET $field=0 WHERE $field IS NULL", false);
    if ($unsigned) {
        $ok = $ok && execute_sql("UPDATE {$CFG->prefix}$table SET $field=0 WHERE $field<0", false);
    }
    $ok = $ok && hotpot_db_update_field_type($table, $field, $field, 'INTEGER', $size, $unsigned, 'NOT NULL', 0);

    return $ok;
}
function hotpot_update_to_v2_1_16() {
    global $CFG;
    $ok = true;

    // remove the questions name index
    hotpot_db_delete_index("{$CFG->prefix}hotpot_questions", "hotpot_questions_name_idx");
    hotpot_db_delete_index("{$CFG->prefix}hotpot_questions", "{$CFG->prefix}hotpot_questions_name_idx");

    // make sure type of 'name' is a text field (not varchar 255)
    $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'name', 'name', 'TEXT',   '',  '', 'NOT NULL', '');

    if (strtolower($CFG->dbfamily)=='mysql') {

        // set default values on certain VARCHAR(255) fields
        $fields = array(
            'hotpot' => 'studentfeedbackurl',
            'hotpot_responses' => 'correct',
            'hotpot_responses' => 'wrong',
            'hotpot_responses' => 'ignored'
        );
        foreach ($fields as $table=>$field) {
            execute_sql("UPDATE {$CFG->prefix}$table SET $field='' WHERE $field IS NULL");
            $ok = $ok && hotpot_db_update_field_type($table, $field, $field, 'VARCHAR', 255, '', 'NOT NULL', '');
        }

        // remove $CFG->prefix from all index names
        $ok = $ok && hotpot_index_remove_prefix('hotpot_attempts', 'hotpot');
        $ok = $ok && hotpot_index_remove_prefix('hotpot_attempts', 'userid');
        $ok = $ok && hotpot_index_remove_prefix('hotpot_details', 'attempt');
        $ok = $ok && hotpot_index_remove_prefix('hotpot_questions', 'hotpot');
        $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'attempt');
        $ok = $ok && hotpot_index_remove_prefix('hotpot_responses', 'question');
    }
    return $ok;
}
function hotpot_index_remove_prefix($table, $field) {
    global $CFG;
    hotpot_db_delete_index("{$CFG->prefix}$table", "{$CFG->prefix}{$table}_{$field}_idx");
    hotpot_db_delete_index("{$CFG->prefix}$table", "{$table}_{$field}_idx");
    return hotpot_db_add_index($table, $field);
}

function hotpot_update_to_v2_1_8() {
    global $CFG;
    $ok = true;
    if (strtolower($CFG->dbfamily)=='postgres') {
        // add, delete and rename certain fields and indexes
        // that were not correctly setup by postgres7.sql

        // hotpot
        $table = 'hotpot';
        if (hotpot_db_field_exists($table, 'microreporting')) {
            $ok = $ok && hotpot_db_update_field_type($table, 'microreporting', 'clickreporting', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
        }
    }
    return $ok;
}
function hotpot_update_to_v2_1_6() {
    global $CFG;
    $ok = true;

    if (strtolower($CFG->dbfamily)=='postgres') {
        // add, delete and rename certain fields and indexes
        // that were not correctly setup by postgres7.sql

        // hotpot
        $table = 'hotpot';
        if (hotpot_db_field_exists($table, 'studentfeedback') && !hotpot_db_field_exists($table, 'studentfeedbackurl')) {
            $ok = $ok && hotpot_db_update_field_type($table, 'studentfeedback', 'studentfeedbackurl', 'VARCHAR', 255, '', 'NULL');
            $ok = $ok && hotpot_db_update_field_type($table, '', 'studentfeedback', 'INTEGER', 4, 'UNSIGNED', 'NOT NULL', '0');
        }

        // hotpot_attempts
        $table = 'hotpot_attempts';
        $ok = $ok && hotpot_db_remove_field($table, 'groupid');
        if (hotpot_db_field_exists($table, 'microreportid') && !hotpot_db_field_exists($table, 'clickreportid')) {
            $ok = $ok && hotpot_db_update_field_type($table, 'microreportid', 'clickreportid', 'INTEGER', 10, 'UNSIGNED', 'NULL');
        }
    }

    return $ok;
}
function hotpot_update_to_v2_1_2() {
    global $CFG, $db;
    $ok = true;

    // save and switch off SQL message echo
    $debug = $db->debug;
    $db->debug = false;

    // extract info about attempts by each user on each hotpot (cases where
    // the user has only one attempt, or no "in progess" attempt are ignored)
    $rs = $db->Execute("
        SELECT userid, hotpot, COUNT(*), MIN(status)
        FROM {$CFG->prefix}hotpot_attempts
        GROUP BY userid, hotpot
        HAVING COUNT(*)>1 AND MIN(status)=1
    ");
    if ($rs && $rs->RecordCount()) {
        $records = $rs->GetArray();

        // start message to browser
        print "adjusting status of ".count($records)." &quot;in progress&quot; attempts ... ";

        // loop through records
        foreach ($records as $record) {

            // get all attempts by this user at this hotpot
            $attempts = get_records_sql("
                SELECT id, userid, hotpot, score, timestart, timefinish, status
                FROM {$CFG->prefix}hotpot_attempts
                WHERE userid = ".$record['userid']." AND hotpot=".$record['hotpot']."
                ORDER BY timestart DESC, id DESC
            ");

            unset($previous_timestart);

            foreach ($attempts as $attempt) {
                // if this attempt has a status of "in progress" and is not
                // the most recent one in the group, set the status to "abandoned"
                if ($attempt->status==1 && isset($previous_timestart)) {
                    $values = 'status=3';
                    if (empty($attempt->score)) {
                        $values .= ',score=0';
                    }
                    if (empty($attempt->timefinish)) {
                        $values .= ",timefinish=$previous_timestart";
                    }
                    execute_sql("UPDATE {$CFG->prefix}hotpot_attempts SET $values WHERE id=$attempt->id", false);
                    print ".";
                    hotpot_flush(300);
                }
                $previous_timestart = $attempt->timestart;
            } // end foreach $attempts
        } // end foreach $records

        // finish message to browser
        print $ok ? get_string('success') : 'failed';
        print "<br />\n";
    }

    // restore SQL message echo setting
    $db->debug = $debug;

    return $ok;
}
function hotpot_update_to_v2_1() {
    global $CFG, $db;
    $ok = true;
    // hotpot_questions: reduce size of "type" field to "4"
    $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'type', 'type', 'INTEGER', 4,  'UNSIGNED', 'NULL');
    // hotpot_questions: change type of "name" field to "text"
    $ok = $ok && hotpot_db_update_field_type('hotpot_questions', 'name', 'name', 'TEXT',   '',  '', 'NOT NULL', '');
    // hotpot_questions: nullify empty and non-numeric (shouldn't be any) values in "text" field
    switch (strtolower($CFG->dbfamily)) {
        case 'mysql' :
            $NOT_REGEXP = 'NOT REGEXP';
        break;
        case 'postgres' :
            $NOT_REGEXP = '!~';
        break;
        default:
            $NOT_REGEXP = '';
        break;
    }
    if ($NOT_REGEXP) {
        $ok = $ok && execute_sql("UPDATE {$CFG->prefix}hotpot_questions SET text=NULL WHERE text $NOT_REGEXP '^[0-9]+$'");