/**
 * returns an object containing a list of ids of notifications the user has
 * received and the tables where to find the dataelements. Also returns the
 * count of the found notifications
 *
 * @global User $USER
 * @param string $type
 * @param int $limit
 * @param int $offset
 * @return \stdClass
 */
function activitylistin($type = 'all', $limit = 10, $offset = 0)
{
    global $USER;
    $result = new stdClass();
    $userid = $USER->get('id');
    $typesql = '';
    if ($type != 'all') {
        // Treat as comma-separated list of activity type names
        $types = split(',', preg_replace('/[^a-z,]+/', '', $type));
        if ($types) {
            $typesql = ' at.name IN (' . join(',', array_map('db_quote', $types)) . ')';
            if (in_array('adminmessages', $types)) {
                $typesql = '(' . $typesql . ' OR at.admin = 1)';
            }
            $typesql = ' AND ' . $typesql;
        }
    }
    $notificationtargetcolumn = 'usr';
    $notificationtargetrole = 'recipient';
    if (is_postgres()) {
        $readsqlstr = 'CAST(b.read AS INT)';
    } else {
        $readsqlstr = 'b.read';
    }
    $msgidquery = "\n        (\n        SELECT a.id, a.read, a.ctime, 'notification_internal_activity' AS msgtable, subject\n        FROM {notification_internal_activity} AS a\n        INNER JOIN {activity_type} AS at ON a.type = at.id\n        WHERE a." . $notificationtargetcolumn . " = ?\n        " . $typesql . "\n        )\n        UNION\n        (\n        SELECT a.id, " . $readsqlstr . ", a.ctime, 'artefact_multirecipient_notification' AS msgtable, subject\n        FROM {artefact_multirecipient_notification} AS a\n        INNER JOIN {artefact_multirecipient_userrelation} AS b\n            ON a.id = b.notification\n        INNER JOIN {activity_type} AS at ON a.type = at.id\n        WHERE b.usr = ?\n        AND b.deleted = '0'\n        AND b.role = '" . $notificationtargetrole . "'\n        " . $typesql . "\n        )";
    $countquery = 'SELECT COUNT(*) FROM (' . $msgidquery . ') AS dummytable';
    $result->count = count_records_sql($countquery, array($userid, $userid));
    $msgidquery .= "\n    ORDER BY \"read\" ASC, ctime DESC, subject ASC";
    $result->msgidrecords = get_records_sql_array($msgidquery, array($userid, $userid), $offset, $limit);
    if (!is_array($result->msgidrecords)) {
        $result->msgidrecords = array();
    }
    return $result;
}

function xmldb_blocktype_textbox_upgrade($oldversion = 0)
{
    if ($oldversion < 2011082200) {
        // Convert all textbox html content to artefacts
        $tbcount = count_records('block_instance', 'blocktype', 'textbox');
        $sql = '
            SELECT b.id, b.title, b.configdata, b.view,
                v.owner, v.group, v.institution, v.ctime, v.mtime, v.atime
            FROM {block_instance} b JOIN {view} v ON b.view = v.id
            WHERE b.id > ? AND b.blocktype = ?
            ORDER BY b.id';
        $done = 0;
        $lastid = 0;
        if (is_mysql()) {
            $mp = mysql_get_variable('max_allowed_packet');
            $limit = $mp && is_numeric($mp) && $mp > 1048576 ? $mp / 8192 : 100;
        } else {
            $limit = 5000;
        }
        while ($records = get_records_sql_array($sql, array($lastid, 'textbox'), 0, $limit)) {
            // Create the new artefacts
            $values = array();
            foreach ($records as $r) {
                $configdata = unserialize($r->configdata);
                array_push($values, 'html', $r->ctime, $r->mtime, $r->atime, $r->title, isset($configdata['text']) ? $configdata['text'] : '', $r->owner, $r->group, $r->institution, $r->owner > 0 ? $r->owner : null, $r->owner > 0 ? null : '?', $r->id);
                // Dumping the block id in the note column makes it easier to update block_instance later
            }
            $insertsql = "\n                INSERT INTO {artefact}\n                    (artefacttype, ctime, mtime, atime, title, description, owner, \"group\", institution, author, authorname, note)\n                VALUES ";
            $insertsql .= join(',', array_fill(0, count($records), '(?,?,?,?,?,?,?,?,?,?,?,?)'));
            execute_sql($insertsql, $values);
            // Update block_instance configdata to point at the new artefacts
            if (is_postgres()) {
                execute_sql("\n                    UPDATE {block_instance}\n                    SET configdata = 'a:1:{s:10:\"artefactid\";i:' || a.id::text || ';}'\n                    FROM (\n                        SELECT id, note FROM {artefact} WHERE artefacttype = 'html' AND note IS NOT NULL\n                    ) a\n                    WHERE blocktype = 'textbox' AND {block_instance}.id::text = a.note");
                // Update view_artefact table
                execute_sql("\n                    INSERT INTO {view_artefact} (view, block, artefact)\n                    SELECT b.view, b.id, a.id\n                    FROM {block_instance} b, {artefact} a\n                    WHERE b.blocktype = 'textbox' AND a.artefacttype = 'html' AND a.note IS NOT NULL AND CAST(b.id AS TEXT) = a.note", array());
            } else {
                if (is_mysql()) {
                    execute_sql("\n                    UPDATE {block_instance}, {artefact}\n                    SET {block_instance}.configdata = CONCAT('a:1:{s:10:\"artefactid\";i:', {artefact}.id, ';}')\n                    WHERE\n                        {artefact}.artefacttype = 'html'\n                        AND {artefact}.note IS NOT NULL\n                        AND {block_instance}.blocktype = 'textbox'\n                        AND {block_instance}.id = {artefact}.note");
                    // Update view_artefact table
                    execute_sql("\n                    INSERT INTO {view_artefact} (view, block, artefact)\n                    SELECT b.view, b.id, a.id\n                    FROM {block_instance} b, {artefact} a\n                    WHERE b.blocktype = 'textbox' AND a.artefacttype = 'html' AND a.note IS NOT NULL AND b.id = a.note", array());
                }
            }
            // Remove the dodgy block id in the note column
            execute_sql("UPDATE {artefact} SET note = NULL WHERE artefacttype = 'html' AND note IS NOT NULL");
            $done += count($records);
            log_debug("Upgrading textbox blocks: {$done}/{$tbcount}");
            $last = end($records);
            $lastid = $last->id;
        }
    }
    return true;
}

 public static function postinst($prevversion)
 {
     if ($prevversion == 0) {
         // MySQL can't handle uniqueness of > 255 chars
         if (is_postgres()) {
             execute_sql('CREATE UNIQUE INDEX {blocextedata_url_uix} ON {blocktype_externalfeed_data}(url);');
         } else {
             if (is_mysql()) {
                 execute_sql('ALTER TABLE {blocktype_externalfeed_data} ADD UNIQUE {blocextedata_url_uix} (url(255))');
             } else {
                 // TODO: support other databases
             }
         }
     }
 }

 public static function postinst($prevversion)
 {
     if ($prevversion == 0) {
         if (is_postgres()) {
             $table = new XMLDBTable('blocktype_externalfeed_data');
             $index = new XMLDBIndex('urlautautix');
             $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('url', 'authuser', 'authpassword'));
             add_index($table, $index);
         } else {
             if (is_mysql()) {
                 // MySQL needs size limits when indexing text fields
                 execute_sql('ALTER TABLE {blocktype_externalfeed_data} ADD INDEX
                            {blocextedata_urlautaut_ix} (url(255), authuser(255), authpassword(255))');
             }
         }
     }
 }

function xmldb_blocktype_externalfeed_upgrade($oldversion = 0)
{
    if ($oldversion < 2008042100) {
        // Add the 'image' column so that information about a feed's image can
        // be stored
        $table = new XMLDBTable('blocktype_externalfeed_data');
        $field = new XMLDBField('image');
        $field->setAttributes(XMLDB_TYPE_TEXT);
        add_field($table, $field);
    }
    if ($oldversion < 2008042101) {
        // We hit the 255 character limit for feed URLs
        if (is_postgres()) {
            execute_sql('ALTER TABLE {blocktype_externalfeed_data} ALTER COLUMN url TYPE TEXT');
        } else {
            if (is_mysql()) {
                // If 2 URLs > 255 chars have the same first 255 characters then mahara will error - this is a MySQL issue though, their unique key length limit is to blame
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} DROP KEY {blocextedata_url_uix}');
                // We have to remove then add the constraint again else the change will make MySQL cry
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} MODIFY COLUMN "url" text');
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} add unique {blocextedata_url_uix} (url(255))');
            }
        }
    }
    if ($oldversion < 2009121600) {
        if (is_mysql()) {
            // Make content column wider (TEXT is only 65kb in mysql)
            $table = new XMLDBTable('blocktype_externalfeed_data');
            $field = new XMLDBField('content');
            $field->setAttributes(XMLDB_TYPE_TEXT, "big", null, null);
            change_field_precision($table, $field);
        }
    }
    if ($oldversion < 2010073000) {
        execute_sql('
            UPDATE {blocktype_cron}
            SET minute = ?
            WHERE minute = ? AND hour = ? AND plugin = ? AND callfunction = ?', array('30', '0', '3', 'externalfeed', 'cleanup_feeds'));
    }
    return true;
}

function xmldb_notification_internal_upgrade($oldversion = 0)
{
    if ($oldversion < 2011112300) {
        execute_sql("\n            UPDATE {notification_internal_activity}\n            SET url = REPLACE(url, ?, '')\n            WHERE url IS NOT NULL", array(get_config('wwwroot')));
    }
    if ($oldversion < 2012021000) {
        // Populate the unread count on the usr table
        if (is_postgres()) {
            execute_sql('
                UPDATE {usr} SET unread = n.unread FROM (
                    SELECT usr, SUM(1 - read) AS unread FROM {notification_internal_activity} GROUP BY usr
                ) n WHERE {usr}.id = n.usr;');
        } else {
            if (is_mysql()) {
                execute_sql('
                UPDATE {usr} u, (SELECT usr, SUM(1 - "read") AS unread FROM {notification_internal_activity} GROUP BY usr) n
                SET u.unread = n.unread
                WHERE u.id = n.usr');
            }
        }
        // Create triggers to maintain the unread count
        db_create_trigger('update_unread_insert', 'AFTER', 'INSERT', 'notification_internal_activity', '
            IF NEW.read = 0 THEN
                UPDATE {usr} SET unread = unread + 1 WHERE id = NEW.usr;
            END IF;');
        db_create_trigger('update_unread_update', 'AFTER', 'UPDATE', 'notification_internal_activity', '
            IF OLD.read = 0 AND NEW.read = 1 THEN
                UPDATE {usr} SET unread = unread - 1 WHERE id = NEW.usr;
            ELSEIF OLD.read = 1 AND NEW.read = 0 THEN
                UPDATE {usr} SET unread = unread + 1 WHERE id = NEW.usr;
            END IF;');
        db_create_trigger('update_unread_delete', 'AFTER', 'DELETE', 'notification_internal_activity', '
            IF OLD.read = 0 THEN
                UPDATE {usr} SET unread = unread - 1 WHERE id = OLD.usr;
            END IF;');
    }
    return true;
}

function groupquotasform_submit(Pieform $form, $values)
{
    global $SESSION;
    $oldquota = get_field('group', 'quota', 'id', $values['groupid']);
    $group = new StdClass();
    $group->id = $values['groupid'];
    $group->quota = $values['quota'];
    update_record('group', $group);
    if (!empty($values['quota']) && $values['quota'] != $oldquota) {
        // We need to alert group admins that the group may now be over the threshold
        $quotanotifylimit = get_config_plugin('artefact', 'file', 'quotanotifylimit');
        $sqlwhere = " ((g.quotaused / g.quota) * 100) ";
        if (is_postgres()) {
            $sqlwhere = " ((CAST(g.quotaused AS float) / CAST(g.quota AS float)) * 100) ";
        }
        if ($groups = get_records_sql_assoc("SELECT g.id, g.name, g.quota, " . $sqlwhere . " AS quotausedpercent FROM {group} g WHERE " . $sqlwhere . " >= ? AND id = ?", array($quotanotifylimit, $values['groupid']))) {
            require_once get_config('docroot') . 'artefact/file/lib.php';
            ArtefactTypeFile::notify_groups_threshold_exceeded($groups);
        }
    }
    $SESSION->add_ok_msg(get_string('groupquotaupdated', 'admin'));
    redirect(get_config('wwwroot') . 'admin/groups/groups.php');
}

/**
 * Cronjob to expire the event_log table.
 */
function cron_event_log_expire()
{
    if ($expiry = get_config('eventlogexpiry')) {
        delete_records_select('event_log', 'time < CURRENT_DATE - INTERVAL ' . (is_postgres() ? "'" . $expiry . " seconds'" : $expiry . ' SECOND'));
    }
}

/**
 * Return current foreign key constraints in given table
 *
 * @param string $tablename not including the dbprefix
 * @return array of array(
 *     'constraintname' => string
 *     'table' => string
 *     'fields' => array
 *     'reftable' => string
 *     'reffields' => array
 *     )
 */
function get_foreign_keys($tablename)
{
    global $CFG;
    $tablename = $CFG->dbprefix . $tablename;
    $foreignkeys = array();
    // Get foreign key constraints from information_schema tables
    if (is_postgres()) {
        $dbfield = 'catalog';
        // The query to find all the columns for a foreign key constraint
        $fkcolsql = "\n            SELECT\n                ku.column_name,\n                ccu.table_name AS reftable_name,\n                ccu.column_name AS refcolumn_name\n            FROM\n                information_schema.key_column_usage ku\n                INNER JOIN information_schema.constraint_column_usage ccu\n                    ON ku.constraint_name = ccu.constraint_name\n                    AND ccu.constraint_schema = ku.constraint_schema\n                    AND ccu.constraint_catalog = ku.constraint_catalog\n                    AND ccu.table_catalog = ku.constraint_catalog\n                    AND ccu.table_schema = ku.constraint_schema\n            WHERE\n                ku.constraint_catalog = ?\n                AND ku.constraint_name = ?\n                AND ku.table_name = ?\n                AND ku.table_catalog = ?\n            ORDER BY ku.ordinal_position, ku.position_in_unique_constraint\n        ";
    } else {
        $dbfield = 'schema';
        // The query to find all the columns for a foreign key constraint
        $fkcolsql = '
            SELECT
                ku.column_name,
                ku.referenced_table_name AS reftable_name,
                ku.referenced_column_name AS refcolumn_name
            FROM information_schema.key_column_usage ku
            WHERE
                ku.constraint_schema = ?
                AND ku.constraint_name = ?
                AND ku.table_name = ?
                AND ku.table_schema = ?
            ORDER BY ku.ordinal_position, ku.position_in_unique_constraint
        ';
    }
    $sql = "\n        SELECT tc.constraint_name\n        FROM information_schema.table_constraints tc\n        WHERE\n            tc.table_name = ?\n            AND tc.table_{$dbfield} = ?\n            AND tc.constraint_{$dbfield} = ?\n            AND tc.constraint_type = ?\n    ";
    $dbname = get_config('dbname');
    if ($constraintrec = get_records_sql_array($sql, array($tablename, $dbname, $dbname, 'FOREIGN KEY'))) {
        // Get foreign key constraint info
        foreach ($constraintrec as $c) {
            $fields = array();
            $reftable = '';
            $reffields = array();
            if ($colrecs = get_records_sql_array($fkcolsql, array($dbname, $c->constraint_name, $tablename, $dbname))) {
                foreach ($colrecs as $colrec) {
                    if (empty($reftable)) {
                        $reftable = $colrec->reftable_name;
                    }
                    $fields[] = $colrec->column_name;
                    $reffields[] = $colrec->refcolumn_name;
                }
            }
            if (!empty($fields) && !empty($reftable) && !empty($reffields)) {
                $foreignkeys[] = array('table' => $tablename, 'constraintname' => $c->constraint_name, 'fields' => $fields, 'reftable' => $reftable, 'reffields' => $reffields);
            }
        }
    }
    return $foreignkeys;
}

function xmldb_core_upgrade($oldversion = 0)
{
    ini_set('max_execution_time', 120);
    // Let's be safe
    raise_memory_limit('256M');
    $INNODB = is_mysql() ? ' TYPE=innodb' : '';
    $status = true;
    // We discovered that username case insensitivity was not being enforced at
    // most of the entry points to the system at which users can be created.
    // This problem manifested itself as users who had the same LOWER(username)
    // as another not being able to log in. The fix is to implement the checks,
    // rename the "duplicate" users and add a constraint on the database so it
    // can't happen again
    if ($oldversion < 2008040202) {
        $renamed = $newusernames = $oldusernames = array();
        $allusers = get_records_array('usr', '', '', 'id', 'id, username');
        $usernamemapping = array();
        foreach ($allusers as $user) {
            $oldusernames[] = $user->username;
            $usernamemapping[strtolower($user->username)][] = array('id' => $user->id, 'username' => $user->username);
        }
        foreach ($usernamemapping as $lcname => $users) {
            if (count($users) == 1) {
                continue;
            }
            // Uhohes. Rename the user(s) who were created last
            $skippedfirst = false;
            foreach ($users as $user) {
                if (!$skippedfirst) {
                    $skippedfirst = true;
                    continue;
                }
                $userobj = new User();
                $userobj->find_by_id($user['id']);
                // Append digits keeping total length <= 30
                $i = 1;
                $newname = substr($user['username'], 0, 29) . $i;
                while (isset($newusernames[$newname]) || isset($oldusernames[$newname])) {
                    $i++;
                    $newname = substr($user['username'], 0, 30 - floor(log10($i) + 1)) . $i;
                }
                set_field('usr', 'username', $newname, 'id', $user['id']);
                $newusernames[$newname] = true;
                $renamed[$newname] = $userobj;
                log_debug(" * Renamed {$user['username']} to {$newname}");
            }
        }
        if (!empty($renamed)) {
            // Notify changed usernames to administrator
            $report = '# Each line in this file is in the form "old_username new_username"' . "\n";
            $message = "Mahara now requires usernames to be unique, case insensitively.\n";
            $message .= "Some usernames on your site were changed during the upgrade:\n\n";
            foreach ($renamed as $newname => $olduser) {
                $report .= "{$olduser->username} {$newname}\n";
                $message .= "Old username: {$olduser->username}\n" . "New username: {$newname}\n\n";
            }
            $sitename = get_config('sitename');
            $file = get_config('dataroot') . 'user_migration_report_2.txt';
            if (file_put_contents($file, $report)) {
                $message .= "\n" . 'A copy of this list has been saved to the file ' . $file;
            }
            global $USER;
            email_user($USER, null, $sitename . ': User migration', $message);
            // Notify changed usernames to users
            $usermessagestart = "Your username at {$sitename} has been changed:\n\n";
            $usermessageend = "\n\nNext time you visit the site, please login using your new username.";
            foreach ($renamed as $newname => $olduser) {
                if ($olduser->email == '') {
                    continue;
                }
                log_debug("Attempting to notify {$newname} ({$olduser->email}) of their new username...");
                email_user($olduser, null, $sitename . ': User name changed', $usermessagestart . "Old username: {$olduser->username}\nNew username: {$newname}" . $usermessageend);
            }
        }
        // Now we know all usernames are unique over their lowercase values, we
        // can put an index in so data doesn't get all inconsistent next time
        if (is_postgres()) {
            execute_sql('DROP INDEX {usr_use_uix}');
            execute_sql('CREATE UNIQUE INDEX {usr_use_uix} ON {usr}(LOWER(username))');
        } else {
            // MySQL cannot create indexes over functions of columns. Too bad
            // for it. We won't drop the existing index because that offers a
            // large degree of protection, but when MySQL finally supports this
            // we will be able to add it
        }
        // Install a cron job to delete old session files
        $cron = new StdClass();
        $cron->callfunction = 'auth_remove_old_session_files';
        $cron->minute = '30';
        $cron->hour = '20';
        $cron->day = '*';
        $cron->month = '*';
        $cron->dayofweek = '*';
        insert_record('cron', $cron);
    }
    if ($oldversion < 2008040203) {
        // Install a cron job to recalculate user quotas
        $cron = new StdClass();
        $cron->callfunction = 'recalculate_quota';
        $cron->minute = '15';
//.........这里部分代码省略.........

function db_random()
{
    if (is_postgres()) {
        return 'RANDOM()';
    } else {
        if (is_mysql()) {
            return 'RAND()';
        }
    }
}

    /**
     * This function returns a list of browsable items.
     *
     * @param limit how many items to display per page
     * @param offset current page to display
     * @return array (count: integer, data: array)
     */

    public static function get_browsable_items($filters, $offset=0, $limit=20) {
        global $USER;

        $contents = array();
        $texttitletrim = 20;
        $onetimeclause = false;
        $count = 0;

        if (is_postgres()) {
            $selectclause =  'SELECT *
                              FROM (
                                    SELECT DISTINCT ON (va.view) va.view, a.id, v.mtime, v.owner';
            $grouporderclause = ') p
                                  ORDER BY mtime DESC';
        }
        else if (is_mysql()) {
            $selectclause =  'SELECT va.view, a.id, v.mtime, v.owner';
            $grouporderclause = 'GROUP BY va.view
                                 ORDER BY a.mtime DESC';
        }
        $fromclause =       ' FROM {view_access} va';
        $joinclause =       " INNER JOIN {view} v ON (va.view = v.id AND v.type = 'profile')";
        $join2clause =      " JOIN {view_artefact} var ON v.id = var.view";
        $join3clause =      " JOIN {artefact} a ON (a.artefacttype = 'image' AND a.id = var.artefact)";
        $join4clause =      '';
        $whereclause =      ' WHERE (v.owner > 0)';
        $andclause =        " AND (v.startdate IS NULL OR v.startdate < current_timestamp)
        AND (v.stopdate IS NULL OR v.stopdate > current_timestamp)
        AND (va.startdate IS NULL OR va.startdate < current_timestamp)
        AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)";

        foreach ($filters as $filterkey => $filterval) {

            switch ($filterkey) {

                case 'keyword':
                    $join4clause = ' LEFT JOIN {usr} u ON u.id = v.owner';
                    // replace spaces with commas so that we can search on each term separately
                    $filterval = str_replace(' ', ',', $filterval);
                    $keywords = explode(",", $filterval);
                    if (count($keywords) == 1 ) {
                        $andclause .= " AND (
                        LOWER(u.firstname) LIKE LOWER('%$filterval%')
                        OR LOWER(u.lastname) LIKE LOWER('%$filterval%')
                        OR LOWER(u.preferredname) LIKE LOWER('%$filterval%')
                        )";
                    } else {
                        foreach($keywords as $key => $word) {
                            if ($key == 0) {
                                $andclause .= " AND ((
                                LOWER(u.firstname) LIKE LOWER('%$word%')
                                OR LOWER(u.lastname) LIKE LOWER('%$word%')
                                OR LOWER(u.preferredname) LIKE LOWER('%$word%')
                                )";
                            } else {
                                $andclause .= " AND (
                                LOWER(u.firstname) LIKE LOWER('%$word%')
                                OR LOWER(u.lastname) LIKE LOWER('%$word%')
                                OR LOWER(u.preferredname) LIKE LOWER('%$word%')
                                )";
                            }
                            if ($key == count($keywords)-1) {
                                $andclause .= ')';
                            }
                        }
                    }
                    break;
                case 'college' :
                    if (!empty($filterval) && !$onetimeclause) {
                        $join4clause .= ' JOIN {usr_enrolment} e ON e.usr = v.owner';
                        $selectclause .= ', e.college, e.course';
                        $ontimeclause = true;
                    }
                    $andclause .= " AND e.college IN ($filterval)";
                    break;
                case 'course' :
                     if (!empty($filterval) && !$onetimeclause) {
                        $join4clause .= ' JOIN {usr_enrolment} e ON e.usr = v.owner';
                        $selectclause .= ', e.college, e.course';
                        $ontimeclause = true;
                    }
                    $courseidgroups = explode(";", $filterval);
                    if (count($courseidgroups) == 1) {
                        // one course submitted, could have multiple csv ids if selected by name
                        $courseids = explode(",", $courseidgroups[0]);
                        if (count($courseids) == 1 ) {
                            $andclause .= " AND (e.course LIKE '%$courseids[0]%' AND e.usr = v.owner)";
                        } else if (count($courseids) > 1 ) {
                            foreach($courseids as $key => $id) {
                                if ($key == 0) {
                                    $andclause .= " AND (e.course LIKE '%$id%'";
                                } else {
//.........这里部分代码省略.........

function xmldb_blocktype_externalfeed_upgrade($oldversion = 0)
{
    if ($oldversion < 2008042101) {
        // We hit the 255 character limit for feed URLs
        if (is_postgres()) {
            execute_sql('ALTER TABLE {blocktype_externalfeed_data} ALTER COLUMN url TYPE TEXT');
        } else {
            if (is_mysql()) {
                // If 2 URLs > 255 chars have the same first 255 characters then mahara will error - this is a MySQL issue though, their unique key length limit is to blame
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} DROP KEY {blocextedata_url_uix}');
                // We have to remove then add the constraint again else the change will make MySQL cry
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} MODIFY COLUMN "url" text');
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} add unique {blocextedata_url_uix} (url(255))');
            }
        }
    }
    if ($oldversion < 2009121600) {
        if (is_mysql()) {
            // Make content column wider (TEXT is only 65kb in mysql)
            $table = new XMLDBTable('blocktype_externalfeed_data');
            $field = new XMLDBField('content');
            $field->setAttributes(XMLDB_TYPE_TEXT, "big", null, null);
            change_field_precision($table, $field);
        }
    }
    if ($oldversion < 2010073000) {
        execute_sql('
            UPDATE {blocktype_cron}
            SET minute = ?
            WHERE minute = ? AND hour = ? AND plugin = ? AND callfunction = ?', array('30', '0', '3', 'externalfeed', 'cleanup_feeds'));
    }
    if ($oldversion < 2011091400) {
        // Add columns for HTTP basic auth
        $table = new XMLDBTable('blocktype_externalfeed_data');
        $field1 = new XMLDBField('authuser');
        $field1->setAttributes(XMLDB_TYPE_TEXT);
        $field2 = new XMLDBField('authpassword');
        $field2->setAttributes(XMLDB_TYPE_TEXT);
        add_field($table, $field1);
        add_field($table, $field2);
        // Change unique constraint that's no longer valid
        $table = new XMLDBTable('blocktype_externalfeed_data');
        $index = new XMLDBIndex('url_uix');
        $index->setAttributes(XMLDB_INDEX_UNIQUE, array('url'));
        drop_index($table, $index);
        if (is_postgres()) {
            $index = new XMLDBIndex('urlautautix');
            $index->setAttributes(XMLDB_INDEX_NOTUNIQUE, array('url', 'authuser', 'authpassword'));
            add_index($table, $index);
        } else {
            if (is_mysql()) {
                // MySQL needs size limits when indexing text fields
                execute_sql('ALTER TABLE {blocktype_externalfeed_data} ADD INDEX
                           {blocextedata_urlautaut_ix} (url(255), authuser(255), authpassword(255))');
            }
        }
    }
    if ($oldversion < 2011091401) {
        // Add columns for insecure SSL mode
        $table = new XMLDBTable('blocktype_externalfeed_data');
        $field = new XMLDBField('insecuresslmode');
        $field->setAttributes(XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, null, null, 0);
        add_field($table, $field);
    }
    if ($oldversion < 2012090700) {
        // Reset all feeds to reset themselves
        set_field('blocktype_externalfeed_data', 'lastupdate', db_format_timestamp('0'));
        safe_require('blocktype', 'externalfeed');
        call_static_method('PluginBlocktypeExternalfeed', 'refresh_feeds');
    }
    if ($oldversion < 2014041500) {
        log_debug('Cleaning up duplicate feeds in the externalfeed blocktype');
        log_debug('1. Find the duplicate feed urls');
        // Setting these to be empty strings instead of NULL will make our SQL a lot simpler in the next section
        execute_sql("update {blocktype_externalfeed_data} set authuser='' where authuser is null");
        execute_sql("update {blocktype_externalfeed_data} set authpassword='' where authpassword is null");
        if ($duplicatefeeds = get_records_sql_array("SELECT COUNT(url), url, authuser, authpassword FROM {blocktype_externalfeed_data} GROUP BY url, authuser, authpassword HAVING COUNT(url) > 1 ORDER BY url, authuser, authpassword", array())) {
            log_debug('2. Get all feed ids for the duplicated feed urls');
            // Use the 1st one found to be the feed id for the block instances that need updating
            $feedstoupdate = array();
            foreach ($duplicatefeeds as $feed) {
                $feedids = get_column('blocktype_externalfeed_data', 'id', 'url', $feed->url, 'authuser', $feed->authuser, 'authpassword', $feed->authpassword);
                $feedstoupdate[$feed->url] = $feedids;
            }
            log_debug('3. Updating blocks to use correct feed id');
            // Find the block instances using external feeds. Check to see if they are not using the 'true' id and update them accordingly
            require_once get_config('docroot') . 'blocktype/lib.php';
            $blockids = get_records_array('block_instance', 'blocktype', 'externalfeed', 'id ASC', 'id');
            foreach ($blockids as $blockid) {
                $blockinstance = new BlockInstance($blockid->id);
                $configdata = $blockinstance->get('configdata');
                if (!empty($configdata['feedid'])) {
                    foreach ($feedstoupdate as $url => $ids) {
                        foreach ($ids as $key => $id) {
                            if ($id == $configdata['feedid'] && $key != '0') {
                                $configdata['feedid'] = $ids[0];
                                $blockinstance->set('configdata', $configdata);
                                $blockinstance->set('dirty', true);
                                $blockinstance->commit();
                                break;
//.........这里部分代码省略.........

function xmldb_artefact_file_upgrade($oldversion = 0)
{
    $status = true;
    if ($oldversion < 2009033000) {
        if (!get_record('artefact_config', 'plugin', 'file', 'field', 'uploadagreement')) {
            insert_record('artefact_config', (object) array('plugin' => 'file', 'field' => 'uploadagreement', 'value' => 1));
            insert_record('artefact_config', (object) array('plugin' => 'file', 'field' => 'usecustomagreement', 'value' => 1));
        }
    }
    if ($oldversion < 2009091700) {
        execute_sql("DELETE FROM {artefact_file_files} WHERE artefact IN (SELECT id FROM {artefact} WHERE artefacttype = 'folder')");
    }
    if ($oldversion < 2009091701) {
        $table = new XMLDBTable('artefact_file_files');
        $key = new XMLDBKey('artefactpk');
        $key->setAttributes(XMLDB_KEY_PRIMARY, array('artefact'));
        add_key($table, $key);
        $table = new XMLDBTable('artefact_file_image');
        $key = new XMLDBKey('artefactpk');
        $key->setAttributes(XMLDB_KEY_PRIMARY, array('artefact'));
        add_key($table, $key);
    }
    if ($oldversion < 2009092300) {
        insert_record('artefact_installed_type', (object) array('plugin' => 'file', 'name' => 'archive'));
        // update old files
        if (function_exists('zip_open')) {
            $files = get_records_select_array('artefact_file_files', "filetype IN ('application/zip', 'application/x-zip')");
            if ($files) {
                $checked = array();
                foreach ($files as $file) {
                    $path = get_config('dataroot') . 'artefact/file/originals/' . $file->fileid % 256 . '/' . $file->fileid;
                    $zip = zip_open($path);
                    if (is_resource($zip)) {
                        $checked[] = $file->artefact;
                        zip_close($zip);
                    }
                }
                if (!empty($checked)) {
                    set_field_select('artefact', 'artefacttype', 'archive', "artefacttype = 'file' AND id IN (" . join(',', $checked) . ')', array());
                }
            }
        }
    }
    if ($oldversion < 2010012702) {
        if ($records = get_records_sql_array("SELECT * FROM {artefact_file_files} WHERE filetype='application/octet-stream'", array())) {
            require_once 'file.php';
            foreach ($records as &$r) {
                $path = get_config('dataroot') . 'artefact/file/originals/' . $r->fileid % 256 . '/' . $r->fileid;
                set_field('artefact_file_files', 'filetype', file_mime_type($path), 'fileid', $r->fileid, 'artefact', $r->artefact);
            }
        }
    }
    if ($oldversion < 2011052500) {
        // Set default quota to 50MB
        set_config_plugin('artefact', 'file', 'defaultgroupquota', 52428800);
    }
    if ($oldversion < 2011070700) {
        // Create an images folder for everyone with a profile icon
        $imagesdir = get_string('imagesdir', 'artefact.file');
        $imagesdirdesc = get_string('imagesdirdesc', 'artefact.file');
        execute_sql("\n            INSERT INTO {artefact} (artefacttype, container, owner, ctime, mtime, atime, title, description, author)\n            SELECT 'folder', 1, owner, current_timestamp, current_timestamp, current_timestamp, ?, ?, owner\n            FROM {artefact} WHERE owner IS NOT NULL AND artefacttype = 'profileicon'\n            GROUP BY owner", array($imagesdir, $imagesdirdesc));
        // Put profileicons into the images folder and update the description
        $profileicondesc = get_string('uploadedprofileicon', 'artefact.file');
        if (is_postgres()) {
            execute_sql("\n                UPDATE {artefact}\n                SET parent = f.folderid, description = ?\n                FROM (\n                    SELECT owner, MAX(id) AS folderid\n                    FROM {artefact}\n                    WHERE artefacttype = 'folder' AND title = ? AND description = ?\n                    GROUP BY owner\n                ) f\n                WHERE artefacttype = 'profileicon' AND {artefact}.owner = f.owner", array($profileicondesc, $imagesdir, $imagesdirdesc));
        } else {
            execute_sql("\n                UPDATE {artefact}, (\n                    SELECT owner, MAX(id) AS folderid\n                    FROM {artefact}\n                    WHERE artefacttype = 'folder' AND title = ? AND description = ?\n                    GROUP BY owner\n                ) f\n                SET parent = f.folderid, description = ?\n                WHERE artefacttype = 'profileicon' AND {artefact}.owner = f.owner", array($imagesdir, $imagesdirdesc, $profileicondesc));
        }
    }
    if ($oldversion < 2011082200) {
        // video file type
        if (!get_record('artefact_installed_type', 'plugin', 'file', 'name', 'video')) {
            insert_record('artefact_installed_type', (object) array('plugin' => 'file', 'name' => 'video'));
        }
        // update existing records
        $videotypes = get_records_sql_array('
            SELECT DISTINCT description
            FROM {artefact_file_mime_types}
            WHERE mimetype ' . db_ilike() . ' \'%video%\'', array());
        if ($videotypes) {
            $mimetypes = array();
            foreach ($videotypes as $type) {
                $mimetypes[] = $type->description;
            }
            $files = get_records_sql_array('
                SELECT *
                FROM {artefact_file_files}
                WHERE filetype IN (
                    SELECT mimetype
                    FROM {artefact_file_mime_types}
                    WHERE description IN (' . join(',', array_map('db_quote', array_values($mimetypes))) . ')
                )', array());
            if ($files) {
                $checked = array();
                foreach ($files as $file) {
                    $checked[] = $file->artefact;
                }
                if (!empty($checked)) {
                    set_field_select('artefact', 'artefacttype', 'video', "artefacttype = 'file' AND id IN (" . join(',', $checked) . ')', array());
                }
//.........这里部分代码省略.........

 /**
  * Drop the whole test database
  * @static
  * @param bool $displayprogress
  */
 protected static function drop_database($displayprogress = false)
 {
     // Drop triggers
     try {
         db_drop_trigger('update_unread_insert', 'notification_internal_activity');
         db_drop_trigger('update_unread_update', 'notification_internal_activity');
         db_drop_trigger('update_unread_delete', 'notification_internal_activity');
         db_drop_trigger('update_unread_insert2', 'module_multirecipient_userrelation');
         db_drop_trigger('update_unread_update2', 'module_multirecipient_userrelation');
         db_drop_trigger('update_unread_delete2', 'module_multirecipient_userrelation');
         db_drop_trigger('unmark_quota_exceed_upd_usr_set', 'usr');
     } catch (Exception $e) {
         exit(1);
     }
     // Drop plugins' tables
     // Find all plugins from the code base
     // and drop their tables from database if exists
     $plugins = array();
     $pluginstocheck = plugin_types();
     foreach ($pluginstocheck as $plugin) {
         $dirhandle = opendir(get_config('docroot') . $plugin);
         while (false !== ($dir = readdir($dirhandle))) {
             if (strpos($dir, '.') === 0 or 'CVS' == $dir) {
                 continue;
             }
             if (!is_dir(get_config('docroot') . $plugin . '/' . $dir)) {
                 continue;
             }
             $plugins[] = array($plugin, $dir);
             if ($plugin == 'artefact') {
                 // go check it for blocks as well
                 $btlocation = get_config('docroot') . $plugin . '/' . $dir . '/blocktype';
                 if (!is_dir($btlocation)) {
                     continue;
                 }
                 $btdirhandle = opendir($btlocation);
                 while (false !== ($btdir = readdir($btdirhandle))) {
                     if (strpos($btdir, '.') === 0 or 'CVS' == $btdir) {
                         continue;
                     }
                     if (!is_dir(get_config('docroot') . $plugin . '/' . $dir . '/blocktype/' . $btdir)) {
                         continue;
                     }
                     $plugins[] = array('blocktype', $dir . '/' . $btdir);
                 }
             }
         }
     }
     foreach ($plugins as $plugin) {
         $plugintype = $plugin[0];
         $pluginname = $plugin[1];
         $pluginpath = "{$plugin['0']}/{$plugin['1']}";
         $pluginkey = "{$plugin['0']}.{$plugin['1']}";
         if ($plugintype == 'blocktype' && strpos($pluginname, '/') !== false) {
             // sigh.. we're a bit special...
             $bits = explode('/', $pluginname);
             $pluginpath = 'artefact/' . $bits[0] . '/blocktype/' . $bits[1];
         }
         log_info("Uninstalling {$plugintype}.{$pluginname}");
         $location = get_config('docroot') . $pluginpath . '/db';
         if (is_readable($location . '/install.xml')) {
             uninstall_from_xmldb_file($location . '/install.xml');
         }
     }
     // These constraints must be dropped manually a