/** * 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; } $dbfamily = $DB->get_dbfamily(); if ($dbfamily === 'postgres') { $queries = array(); $prefix = $DB->get_prefix(); foreach ($data as $table => $records) { if (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { if (empty($records)) { $nextid = 1; } else { $lastrecord = end($records); $nextid = $lastrecord->id + 1; } $queries[] = "ALTER SEQUENCE {$prefix}{$table}_id_seq RESTART WITH {$nextid}"; } } if ($queries) { $DB->change_database_structure(implode(';', $queries)); } } else { if ($dbfamily === 'mysql') { $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 (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { if (isset($sequences[$table])) { if (empty($records)) { $nextid = 1; } else { $lastrecord = end($records); $nextid = $lastrecord->id + 1; } if ($sequences[$table] != $nextid) { $DB->change_database_structure("ALTER TABLE {$prefix}{$table} AUTO_INCREMENT = {$nextid}"); } } else { // some problem exists, fallback to standard code $DB->get_manager()->reset_sequence($table); } } } } else { if ($dbfamily === 'oracle') { $sequences = phpunit_util::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 (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? if (is_null($empties)) { $empties = self::guess_unmodified_empty_tables(); } foreach ($data as $table => $records) { if (isset($empties[$table])) { continue; } if (isset($structure[$table]['id']) and $structure[$table]['id']->auto_increment) { $DB->get_manager()->reset_sequence($table); } } } } } }