/** * Reset all database sequences to initial values. * * @static * @param array $empties tables that are known to be unmodified and empty * @return void */ public static function reset_all_database_sequences(array $empties = null) { global $DB; if (!($data = self::get_tabledata())) { // Not initialised yet. return; } if (!($structure = self::get_tablestructure())) { // Not initialised yet. return; } $updatedtables = self::$tableupdated; // If all starting Id's are the same, it's difficult to detect coding and testing // errors that use the incorrect id in tests. The classic case is cmid vs instance id. // To reduce the chance of the coding error, we start sequences at different values where possible. // In a attempt to avoid tables with existing id's we start at a high number. // Reset the value each time all database sequences are reset. if (defined('PHPUNIT_SEQUENCE_START') and PHPUNIT_SEQUENCE_START) { self::$sequencenextstartingid = PHPUNIT_SEQUENCE_START; } else { self::$sequencenextstartingid = 100000; } $dbfamily = $DB->get_dbfamily(); if ($dbfamily === 'postgres') { $queries = array(); $prefix = $DB->get_prefix(); foreach ($data as $table => $records) { // If table is not modified then no need to do anything. if (!isset($updatedtables[$table])) { continue; } if (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { $nextid = self::get_next_sequence_starting_value($records, $table); $queries[] = "ALTER SEQUENCE {$prefix}{$table}_id_seq RESTART WITH {$nextid}"; } } if ($queries) { $DB->change_database_structure(implode(';', $queries)); } } else { if ($dbfamily === 'mysql') { $queries = array(); $sequences = array(); $prefix = $DB->get_prefix(); $rs = $DB->get_recordset_sql("SHOW TABLE STATUS LIKE ?", array($prefix . '%')); foreach ($rs as $info) { $table = strtolower($info->name); if (strpos($table, $prefix) !== 0) { // incorrect table match caused by _ continue; } if (!is_null($info->auto_increment)) { $table = preg_replace('/^' . preg_quote($prefix, '/') . '/', '', $table); $sequences[$table] = $info->auto_increment; } } $rs->close(); $prefix = $DB->get_prefix(); foreach ($data as $table => $records) { // If table is not modified then no need to do anything. if (!isset($updatedtables[$table])) { continue; } if (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { if (isset($sequences[$table])) { $nextid = self::get_next_sequence_starting_value($records, $table); if ($sequences[$table] != $nextid) { $queries[] = "ALTER TABLE {$prefix}{$table} AUTO_INCREMENT = {$nextid}"; } } else { // some problem exists, fallback to standard code $DB->get_manager()->reset_sequence($table); } } } if ($queries) { $DB->change_database_structure(implode(';', $queries)); } } else { if ($dbfamily === 'oracle') { $sequences = self::get_sequencenames(); $sequences = array_map('strtoupper', $sequences); $lookup = array_flip($sequences); $current = array(); list($seqs, $params) = $DB->get_in_or_equal($sequences); $sql = "SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name {$seqs}"; $rs = $DB->get_recordset_sql($sql, $params); foreach ($rs as $seq) { $table = $lookup[$seq->sequence_name]; $current[$table] = $seq->last_number; } $rs->close(); foreach ($data as $table => $records) { // If table is not modified then no need to do anything. if (!isset($updatedtables[$table])) { continue; } if (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { $lastrecord = end($records); if ($lastrecord) { $nextid = $lastrecord->id + 1; } else { $nextid = 1; } if (!isset($current[$table])) { $DB->get_manager()->reset_sequence($table); } else { if ($nextid == $current[$table]) { continue; } } // reset as fast as possible - alternatively we could use http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle $seqname = $sequences[$table]; $cachesize = $DB->get_manager()->generator->sequence_cache_size; $DB->change_database_structure("DROP SEQUENCE {$seqname}"); $DB->change_database_structure("CREATE SEQUENCE {$seqname} START WITH {$nextid} INCREMENT BY 1 NOMAXVALUE CACHE {$cachesize}"); } } } else { // note: does mssql support any kind of faster reset? // This also implies mssql will not use unique sequence values. if (is_null($empties) and empty($updatedtables)) { $empties = self::guess_unmodified_empty_tables(); } foreach ($data as $table => $records) { // If table is not modified then no need to do anything. if (isset($empties[$table]) or !isset($updatedtables[$table])) { continue; } if (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { $DB->get_manager()->reset_sequence($table); } } } } } }