PHP execute_sql函数代码示例

function ouwiki_update_instance($ouwiki)
{
    global $CFG;
    $ok = true;
    $ouwiki->id = $ouwiki->instance;
    // Set up null values
    $nullvalues = array('editbegin', 'editend', 'timeout');
    foreach ($nullvalues as $nullvalue) {
        if (empty($ouwiki->{$nullvalue})) {
            unset($ouwiki->{$nullvalue});
            $ok &= execute_sql("UPDATE {$CFG->prefix}ouwiki SET {$nullvalue}=NULL WHERE id={$ouwiki->id}", false);
        }
    }
    if (strlen(preg_replace('/(<.*?>)|(&.*?;)|\\s/', '', $ouwiki->summary)) == 0) {
        unset($ouwiki->summary);
        $ok &= execute_sql("UPDATE {$CFG->prefix}ouwiki SET summary=NULL WHERE id={$ouwiki->id}", false);
    }
    ouwiki_check_groups($ouwiki);
    // insitu editing
    if (class_exists('ouflags') && has_capability('local/course:revisioneditor', get_context_instance(CONTEXT_COURSE, $ouwiki->course), null, false)) {
        include_once $CFG->dirroot . '/local/insitu/lib.php';
        return oci_mod_make_backup_and_save_instance($ouwiki);
    }
    // Update main record
    $ok &= update_record("ouwiki", $ouwiki);
    return $ok;
}

function xmldb_artefact_calendar_upgrade($oldversion = 0)
{
    if ($oldversion < 2013062404) {
        $table = new XMLDBTable('artefact_calendar_reminder');
        drop_table($table);
        $table->addFieldInfo('user', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL);
        $table->addFieldInfo('reminder_type', XMLDB_TYPE_INTEGER, '2', null, XMLDB_NOTNULL);
        $table->addFieldInfo('reminder_date', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, null, null, '-1', null);
        $table->addKeyInfo('reminder_pk', XMLDB_KEY_PRIMARY, array('user'));
        if (!create_table($table)) {
            throw new SQLException($table . " could not be created, check log for errors.");
        }
        execute_sql('ALTER TABLE {artefact_calendar_calendar} DROP COLUMN {reminder_status}');
    }
    if ($oldversion < 2013062501) {
        execute_sql('ALTER TABLE {artefact_calendar_calendar} change {reminder_date} {reminder_date} int(4) NOT NULL;');
    }
    if ($oldversion < 2013063001) {
        $table = new XMLDBTable('artefact_calendar_event');
        $table->addFieldInfo('eventid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL);
        $table->addFieldInfo('begin', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL);
        $table->addFieldInfo('end', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL);
        $table->addFieldInfo('whole_day', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL);
        $table->addFieldInfo('repeat_type', XMLDB_TYPE_INTEGER, '1', null, XMLDB_NOTNULL);
        $table->addFieldInfo('repeats_every', XMLDB_TYPE_INTEGER, '3', null, XMLDB_NOTNULL);
        $table->addFieldInfo('end_date', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL);
        $table->addFieldInfo('ends_after', XMLDB_TYPE_INTEGER, '3', null, XMLDB_NOTNULL);
        $table->addKeyInfo('event_pk', XMLDB_KEY_PRIMARY, array('eventid'));
        if (!create_table($table)) {
            throw new SQLException($table . " could not be created, check log for errors.");
        }
    }
    return true;
}

function xmldb_artefact_comment_upgrade($oldversion = 0)
{
    $success = true;
    if ($oldversion < 2011011201) {
        $table = new XMLDBTable('artefact_comment_comment');
        $field = new XMLDBField('rating');
        $field->setAttributes(XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED);
        $success = $success && add_field($table, $field);
    }
    if ($oldversion < 2013072400) {
        $table = new XMLDBTable('artefact_comment_comment');
        $field = new XMLDBField('lastcontentupdate');
        $field->setAttributes(XMLDB_TYPE_DATETIME);
        $success = $success && add_field($table, $field);
        $success = $success && execute_sql('update {artefact_comment_comment} acc
            set lastcontentupdate = (
                select a.mtime
                from {artefact} a
                where a.id = acc.artefact
            )');
    }
    if ($oldversion < 2015081000) {
        // Set default maxindent for threaded comments
        set_config_plugin('artefact', 'comment', 'maxindent', 5);
    }
    return $success;
}

function xmldb_assignment_upgrade($oldversion = 0)
{
    global $CFG, $THEME, $db;
    $result = true;
    if ($result && $oldversion < 2007091900) {
        /// MDL-11268
        /// Changing nullability of field data1 on table assignment_submissions to null
        $table = new XMLDBTable('assignment_submissions');
        $field = new XMLDBField('data1');
        $field->setAttributes(XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null, 'numfiles');
        /// Launch change of nullability for field data1
        $result = $result && change_field_notnull($table, $field);
        /// Changing nullability of field data2 on table assignment_submissions to null
        $field = new XMLDBField('data2');
        $field->setAttributes(XMLDB_TYPE_TEXT, 'small', null, null, null, null, null, null, 'data1');
        /// Launch change of nullability for field data2
        $result = $result && change_field_notnull($table, $field);
    }
    if ($result && $oldversion < 2007091902) {
        // add draft tracking default to existing upload assignments
        $sql = "UPDATE {$CFG->prefix}assignment SET var4=1 WHERE assignmenttype='upload'";
        $result = execute_sql($sql);
    }
    //===== 1.9.0 upgrade line ======//
    if ($result && $oldversion < 2007101511) {
        notify('Processing assignment grades, this may take a while if there are many assignments...', 'notifysuccess');
        // change grade typo to text if no grades MDL-13920
        require_once $CFG->dirroot . '/mod/assignment/lib.php';
        // too much debug output
        $db->debug = false;
        assignment_update_grades();
        $db->debug = true;
    }
    return $result;
}

function xmldb_artefact_resume_upgrade($oldversion = 0)
{
    $status = true;
    if ($oldversion < 2009122100) {
        $table = new XMLDBTable('artefact_resume_employmenthistory');
        $field = new XMLDBField('employeraddress');
        $field->setAttributes(XMLDB_TYPE_TEXT);
        add_field($table, $field);
        $table = new XMLDBTable('artefact_resume_educationhistory');
        $field = new XMLDBField('institutionaddress');
        $field->setAttributes(XMLDB_TYPE_TEXT);
        add_field($table, $field);
    }
    if ($oldversion < 2010020300) {
        $table = new XMLDBTable('artefact_resume_educationhistory');
        $field = new XMLDBField('qualtype');
        $field->setAttributes(XMLDB_TYPE_TEXT);
        change_field_notnull($table, $field);
        $table = new XMLDBTable('artefact_resume_educationhistory');
        $field = new XMLDBField('qualname');
        $field->setAttributes(XMLDB_TYPE_TEXT);
        change_field_notnull($table, $field);
    }
    if ($oldversion < 2013071300) {
        $table = new XMLDBTable('artefact_resume_book');
        $field = new XMLDBField('url');
        $field->setAttributes(XMLDB_TYPE_TEXT);
        add_field($table, $field);
    }
    if ($oldversion < 2013072900) {
        execute_sql("UPDATE {blocktype_installed_category} SET category = 'internal' WHERE category = 'resume'");
    }
    return $status;
}

function log($log)
{
    //Insert new log to the database
    $date = new date(DATE_ATOM);
    $sql = "INSERT INTO  mdl_deva_log (description, date) VALUES ('" . $log . "','" . $date . "');";
    execute_sql($sql);
}

function backup_upgrade($oldversion = 0)
{
    global $CFG;
    $result = true;
    if ($oldversion < 2006011600 and $result) {
        $result = execute_sql("DROP TABLE {$CFG->prefix}backup_files");
        if ($result) {
            $result = execute_sql("CREATE TABLE {$CFG->prefix}backup_files (\n                          id SERIAL PRIMARY KEY,\n                          backup_code integer NOT NULL default '0',\n                          file_type varchar(10) NOT NULL default '',\n                          path varchar(255) NOT NULL default '',\n                          old_id integer default NULL,\n                          new_id integer default NULL,\n                          CONSTRAINT {$CFG->prefix}backup_files_uk UNIQUE (backup_code, file_type, path))");
        }
        if ($result) {
            $result = execute_sql("DROP TABLE {$CFG->prefix}backup_ids");
        }
        if ($result) {
            $result = execute_sql("CREATE TABLE {$CFG->prefix}backup_ids (\n                          id SERIAL PRIMARY KEY,\n                          backup_code integer NOT NULL default '0',\n                          table_name varchar(30) NOT NULL default '',\n                          old_id integer NOT NULL default '0',\n                          new_id integer default NULL,\n                          info text,\n                          CONSTRAINT {$CFG->prefix}backup_ids_uk UNIQUE (backup_code, table_name, old_id))");
        }
    }
    if ($oldversion < 2006042801) {
        table_column('backup_log', 'time', 'time', 'integer', '', '', '0');
        table_column('backup_log', 'laststarttime', 'laststarttime', 'integer', '', '', '0');
        table_column('backup_log', 'courseid', 'courseid', 'integer', '', '', '0');
        table_column('backup_courses', 'lastendtime', 'lastendtime', 'integer', '', '', '0');
        table_column('backup_courses', 'laststarttime', 'laststarttime', 'integer', '', '', '0');
        table_column('backup_courses', 'courseid', 'courseid', 'integer', '', '', '0');
        table_column('backup_courses', 'nextstarttime', 'nextstarttime', 'integer', '', '', '0');
    }
    //////  DO NOT ADD NEW THINGS HERE!!  USE upgrade.php and the lib/ddllib.php functions.
    //Finally, return result
    return $result;
}

function game_insert_glossaries_ids($course, $backup_unique_code, $instances = null)
{
    global $CFG;
    // Insert categories used by games
    $status = execute_sql("INSERT INTO {$CFG->prefix}backup_ids\n                                   (backup_code, table_name, old_id, info)\n                               SELECT DISTINCT {$backup_unique_code}, 'glossary', g.glossaryid, ''\n                               FROM {$CFG->prefix}game g\n                               WHERE g.course={$course}", false);
    return $status;
}

function certificate_upgrade($oldversion)
{
    global $CFG;
    if ($oldversion < 2006081700) {
        execute_sql(" ALTER TABLE `{$CFG->prefix}certificate_issues` CHANGE `classname` `classname` VARCHAR( 254 )");
    }
    return true;
}

function log($log)
{
    //Insert new log to the database
    //$date = new date(DATE_ATOM);
    $date = new date("yyyy-mm-dd HH:ii:ss");
    $sql = "INSERT INTO  mdl_shoppingcart_log (description, date) VALUES ('" . $log . "','" . $date . "');";
    execute_sql($sql);
}

function email_upgrade($oldversion = 0)
{
    global $CFG;
    $result = true;
    if ($oldversion < 2005012800 && $result) {
        execute_sql(" create table " . $CFG->prefix . "block_quickmail_log\n                    ( id int(10) unsigned not null auto_increment,\n                      courseid int(10) unsigned not null,\n                      userid int(10) unsigned not null,\n                      mailto text not null,\n                      subject varchar(255) not null,\n                      message text not null,\n                      attachment varchar(255) not null,\n                      format tinyint(3) unsigned not null default 1,\n                      timesent int(10) unsigned not null,\n                      PRIMARY KEY  (`id`)\n                    )");
    }
    return $result;
}

function db_setUserTimeZone($userId, $timeZoneId)
{
    try {
        $sql = "UPDATE mdl_user_info_data SET data ='" . $timeZoneId . "' WHERE userid = " . $userId . " and fieldid = 4";
        execute_sql($sql, false);
    } catch (Exception $e) {
        echo $e->getMessage();
    }
}

function hotpot_upgrade($oldversion)
{
    global $CFG;
    $ok = true;
    // set path to update functions
    $update_to_v2 = "{$CFG->dirroot}/mod/hotpot/db/update_to_v2.php";
    // if the version number indicates this could be an early HotPot v2.1 (Moodle 1.6),
    // check this is not actually HotPot v1 or v2.0 (Moodle 1.5) with an overly advanced version number
    if ($oldversion > 2005031400 && $oldversion <= 2006082899) {
        require_once $update_to_v2;
        if (hotpot_db_table_exists('hotpot_attempts')) {
            if (hotpot_db_field_exists('hotpot_attempts', 'details')) {
                // HotPot v2.0 (Moodle 1.5)
                $oldversion = 2005031400;
            }
        } else {
            // HotPot v1
            $oldversion = 2004122000;
        }
    }
    if ($oldversion < 2004021400) {
        execute_sql(" ALTER TABLE `{$CFG->prefix}hotpot_events` ADD `starttime` INT(10) unsigned NOT NULL DEFAULT '0' AFTER `time`");
        execute_sql(" ALTER TABLE `{$CFG->prefix}hotpot_events` ADD `endtime` INT(10) unsigned NOT NULL DEFAULT '0' AFTER `time`");
    }
    // update from HotPot v1 to HotPot v2
    if ($oldversion < 2005031400) {
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_from_v1();
    }
    if ($oldversion < 2005090700) {
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_1();
    }
    if ($oldversion > 2005031419 && $oldversion < 2005090702) {
        // update to from HotPot v2.1.0 or v2.1.1
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_1_2();
    }
    if ($oldversion < 2006042103) {
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_1_16();
    }
    if ($oldversion < 2006042601) {
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_1_17();
    }
    if ($oldversion < 2006042803) {
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_1_18();
    }
    if ($oldversion < 2006083101) {
        require_once $update_to_v2;
        $ok = $ok && hotpot_update_to_v2_2();
    }
    //////  DO NOT ADD NEW THINGS HERE!!  USE upgrade.php and the lib/ddllib.php functions.
    return $ok;
}

function resource_upgrade($oldversion)
{
    // This function does anything necessary to upgrade
    // older versions to match current functionality
    global $CFG;
    if ($oldversion < 2004013101) {
        modify_database("", "INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('resource', 'update', 'resource', 'name');");
        modify_database("", "INSERT INTO prefix_log_display (module, action, mtable, field) VALUES ('resource', 'add', 'resource', 'name');");
    }
    if ($oldversion < 2004071000) {
        table_column("resource", "", "popup", "text", "", "", "", "", "alltext");
        if ($resources = get_records_select("resource", "type='3' OR type='5'", "", "id, alltext")) {
            foreach ($resources as $resource) {
                $resource->popup = addslashes($resource->alltext);
                $resource->alltext = "";
                if (!update_record("resource", $resource)) {
                    notify("Error updating popup field for resource id = {$resource->id}");
                }
            }
        }
        require_once "{$CFG->dirroot}/course/lib.php";
        rebuild_course_cache();
    }
    if ($oldversion < 2004071300) {
        table_column("resource", "", "options", "varchar", "255", "", "", "", "popup");
    }
    if ($oldversion < 2004071303) {
        table_column("resource", "type", "type", "varchar", "30", "", "", "", "");
        modify_database("", "UPDATE prefix_resource SET type='reference' WHERE type='1';");
        modify_database("", "UPDATE prefix_resource SET type='file', options='frame' WHERE type='2';");
        modify_database("", "UPDATE prefix_resource SET type='file' WHERE type='3';");
        modify_database("", "UPDATE prefix_resource SET type='text', options='0' WHERE type='4';");
        modify_database("", "UPDATE prefix_resource SET type='file' WHERE type='5';");
        modify_database("", "UPDATE prefix_resource SET type='html' WHERE type='6';");
        modify_database("", "UPDATE prefix_resource SET type='file' WHERE type='7';");
        modify_database("", "UPDATE prefix_resource SET type='text', options='3' WHERE type='8';");
        modify_database("", "UPDATE prefix_resource SET type='directory' WHERE type='9';");
    }
    if ($oldversion < 2004080801) {
        modify_database("", "UPDATE prefix_resource SET alltext=reference,type='html' WHERE type='reference';");
        rebuild_course_cache();
    }
    if ($oldversion < 2004111200) {
        //drop first to avoid conflicts when upgrading
        execute_sql("DROP INDEX {$CFG->prefix}resource_course_idx;", false);
        modify_database('', 'CREATE INDEX prefix_resource_course_idx ON prefix_resource (course);');
    }
    if ($oldversion < 2005041100) {
        // replace wiki-like with markdown
        include_once "{$CFG->dirroot}/lib/wiki_to_markdown.php";
        $wtm = new WikiToMarkdown();
        $wtm->update('resource', 'alltext', 'options');
    }
    //////  DO NOT ADD NEW THINGS HERE!!  USE upgrade.php and the lib/ddllib.php functions.
    return true;
}

function xmldb_assignment_type_poodllonline_upgrade($oldversion = 0)
{
    global $CFG, $THEME, $db;
    if ($oldversion < 2010030312) {
        // add field to store media comments (audio or video) filename to students submissions
        $sql = "ALTER TABLE {$CFG->prefix}assignment_submissions ADD poodllfeedback TEXT";
        $result = execute_sql($sql);
    }
    return $result;
}