/**
* This function takes an xmlized array and put it into one xmldb_structure
* @param array $xmlarr
* @return xmldb_structure
*/
public function arr2xmldb_structure($xmlarr)
{
$structure = new xmldb_structure($this->path);
$structure->arr2xmldb_structure($xmlarr);
return $structure;
}
/**
* This function will return the SQL code needed to create db tables and statements.
*
* @param xmldb_structure $xmldb_structure An xmldb_structure instance.
*
* @see xmldb_structure
*/
public function getCreateStructureSQL($xmldb_structure)
{
$results = array();
if ($tables = $xmldb_structure->getTables()) {
foreach ($tables as $table) {
$results = array_merge($results, $this->getCreateTableSQL($table));
}
}
return $results;
}
/**
* Checks the database schema against a schema specified by an xmldb_structure object
* @param xmldb_structure $schema export schema describing all known tables
* @param array $options
* @return array keyed by table name with array of difference messages as values
*/
public function check_database_schema(xmldb_structure $schema, array $options = null)
{
$alloptions = array('extratables' => true, 'missingtables' => true, 'extracolumns' => true, 'missingcolumns' => true, 'changedcolumns' => true);
$typesmap = array('I' => XMLDB_TYPE_INTEGER, 'R' => XMLDB_TYPE_INTEGER, 'N' => XMLDB_TYPE_NUMBER, 'F' => XMLDB_TYPE_NUMBER, 'C' => XMLDB_TYPE_CHAR, 'X' => XMLDB_TYPE_TEXT, 'B' => XMLDB_TYPE_BINARY, 'T' => XMLDB_TYPE_TIMESTAMP, 'D' => XMLDB_TYPE_DATETIME);
$options = (array) $options;
$options = array_merge($alloptions, $options);
// Note: the error descriptions are not supposed to be localised,
// it is intended for developers and skilled admins only.
$errors = array();
/** @var string[] $dbtables */
$dbtables = $this->mdb->get_tables(false);
/** @var xmldb_table[] $tables */
$tables = $schema->getTables();
foreach ($tables as $table) {
$tablename = $table->getName();
if ($options['missingtables']) {
// Missing tables are a fatal problem.
if (empty($dbtables[$tablename])) {
$errors[$tablename][] = "table is missing";
continue;
}
}
/** @var database_column_info[] $dbfields */
$dbfields = $this->mdb->get_columns($tablename, false);
/** @var xmldb_field[] $fields */
$fields = $table->getFields();
foreach ($fields as $field) {
$fieldname = $field->getName();
if (empty($dbfields[$fieldname])) {
if ($options['missingcolumns']) {
// Missing columns are a fatal problem.
$errors[$tablename][] = "column '{$fieldname}' is missing";
}
} else {
if ($options['changedcolumns']) {
$dbfield = $dbfields[$fieldname];
if (!isset($typesmap[$dbfield->meta_type])) {
$errors[$tablename][] = "column '{$fieldname}' has unsupported type '{$dbfield->meta_type}'";
} else {
$dbtype = $typesmap[$dbfield->meta_type];
$type = $field->getType();
if ($type == XMLDB_TYPE_FLOAT) {
$type = XMLDB_TYPE_NUMBER;
}
if ($type != $dbtype) {
if ($expected = array_search($type, $typesmap)) {
$errors[$tablename][] = "column '{$fieldname}' has incorrect type '{$dbfield->meta_type}', expected '{$expected}'";
} else {
$errors[$tablename][] = "column '{$fieldname}' has incorrect type '{$dbfield->meta_type}'";
}
} else {
if ($field->getNotNull() != $dbfield->not_null) {
if ($field->getNotNull()) {
$errors[$tablename][] = "column '{$fieldname}' should be NOT NULL ({$dbfield->meta_type})";
} else {
$errors[$tablename][] = "column '{$fieldname}' should allow NULL ({$dbfield->meta_type})";
}
}
if ($dbtype == XMLDB_TYPE_TEXT) {
// No length check necessary - there is one size only now.
} else {
if ($dbtype == XMLDB_TYPE_NUMBER) {
if ($field->getType() == XMLDB_TYPE_FLOAT) {
// Do not use floats in any new code, they are deprecated in XMLDB editor!
} else {
if ($field->getLength() != $dbfield->max_length or $field->getDecimals() != $dbfield->scale) {
$size = "({$field->getLength()},{$field->getDecimals()})";
$dbsize = "({$dbfield->max_length},{$dbfield->scale})";
$errors[$tablename][] = "column '{$fieldname}' size is {$dbsize}, expected {$size} ({$dbfield->meta_type})";
}
}
} else {
if ($dbtype == XMLDB_TYPE_CHAR) {
// This is not critical, but they should ideally match.
if ($field->getLength() != $dbfield->max_length) {
$errors[$tablename][] = "column '{$fieldname}' length is {$dbfield->max_length}, expected {$field->getLength()} ({$dbfield->meta_type})";
}
} else {
if ($dbtype == XMLDB_TYPE_INTEGER) {
// Integers may be bigger in some DBs.
$length = $field->getLength();
if ($length > 18) {
// Integers are not supposed to be bigger than 18.
$length = 18;
}
if ($length > $dbfield->max_length) {
$errors[$tablename][] = "column '{$fieldname}' length is {$dbfield->max_length}, expected at least {$field->getLength()} ({$dbfield->meta_type})";
}
} else {
if ($dbtype == XMLDB_TYPE_BINARY) {
// Ignore binary types.
continue;
} else {
if ($dbtype == XMLDB_TYPE_TIMESTAMP) {
$errors[$tablename][] = "column '{$fieldname}' is a timestamp, this type is not supported ({$dbfield->meta_type})";
continue;
} else {
if ($dbtype == XMLDB_TYPE_DATETIME) {
$errors[$tablename][] = "column '{$fieldname}' is a datetime, this type is not supported ({$dbfield->meta_type})";
continue;
} else {
// Report all other unsupported types as problems.
$errors[$tablename][] = "column '{$fieldname}' has unknown type ({$dbfield->meta_type})";
continue;
}
}
}
}
}
}
}
// Note: The empty string defaults are a bit messy...
if ($field->getDefault() != $dbfield->default_value) {
$default = is_null($field->getDefault()) ? 'NULL' : $field->getDefault();
$dbdefault = is_null($dbfield->default_value) ? 'NULL' : $dbfield->default_value;
$errors[$tablename][] = "column '{$fieldname}' has default '{$dbdefault}', expected '{$default}' ({$dbfield->meta_type})";
}
}
}
}
}
unset($dbfields[$fieldname]);
}
// Check for extra columns (indicates unsupported hacks) - modify install.xml if you want to pass validation.
foreach ($dbfields as $fieldname => $dbfield) {
if ($options['extracolumns']) {
$errors[$tablename][] = "column '{$fieldname}' is not expected ({$dbfield->meta_type})";
}
}
unset($dbtables[$tablename]);
}
if ($options['extratables']) {
// Look for unsupported tables - local custom tables should be in /local/xxxx/db/install.xml file.
// If there is no prefix, we can not say if table is ours, sorry.
if ($this->generator->prefix !== '') {
foreach ($dbtables as $tablename => $unused) {
if (strpos($tablename, 'pma_') === 0) {
// Ignore phpmyadmin tables.
continue;
}
if (strpos($tablename, 'test') === 0) {
// Legacy simple test db tables need to be eventually removed,
// report them as problems!
$errors[$tablename][] = "table is not expected (it may be a leftover after Simpletest unit tests)";
} else {
$errors[$tablename][] = "table is not expected";
}
}
}
}
return $errors;
}
/**
* Checks the database schema against a schema specified by an xmldb_structure object
* @param xmldb_structure $schema export schema describing all known tables
* @return array keyed by table name with array of difference messages as values
*/
public function check_database_schema(xmldb_structure $schema)
{
$errors = array();
$dbtables = $this->mdb->get_tables();
$tables = $schema->getTables();
//TODO: maybe add several levels error/warning
// make sure that current and schema tables match exactly
foreach ($tables as $table) {
$tablename = $table->getName();
if (empty($dbtables[$tablename])) {
if (!isset($errors[$tablename])) {
$errors[$tablename] = array();
}
$errors[$tablename][] = "Table {$tablename} is missing in database.";
//TODO: localize
continue;
}
// a) check for required fields
$dbfields = $this->mdb->get_columns($tablename);
$fields = $table->getFields();
foreach ($fields as $field) {
$fieldname = $field->getName();
if (empty($dbfields[$fieldname])) {
if (!isset($errors[$tablename])) {
$errors[$tablename] = array();
}
$errors[$tablename][] = "Field {$fieldname} is missing in table {$tablename}.";
//TODO: localize
}
unset($dbfields[$fieldname]);
}
// b) check for extra fields (indicates unsupported hacks) - modify install.xml if you want the script to continue ;-)
foreach ($dbfields as $fieldname => $info) {
if (!isset($errors[$tablename])) {
$errors[$tablename] = array();
}
$errors[$tablename][] = "Field {$fieldname} is not expected in table {$tablename}.";
//TODO: localize
}
unset($dbtables[$tablename]);
}
// look for unsupported tables - local custom tables should be in /local/xxxx/db/install.xml ;-)
// if there is no prefix, we can not say if tale is ours :-(
if ($this->generator->prefix !== '') {
foreach ($dbtables as $tablename => $unused) {
if (strpos($tablename, 'pma_') === 0) {
// ignore phpmyadmin tables for now
continue;
}
if (strpos($tablename, 'test') === 0) {
// ignore broken results of unit tests
continue;
}
if (!isset($errors[$tablename])) {
$errors[$tablename] = array();
}
$errors[$tablename][] = "Table {$tablename} is not expected.";
//TODO: localize
}
}
return $errors;
}
function cleanup()
{
global $DB;
$dbman = $DB->get_manager();
$tablename = student::TABLE;
$tempname = $tablename . '_temp';
$table = new xmldb_table($tempname);
if ($dbman->table_exists($table)) {
if (!$dbman->drop_table($table)) {
mtrace(' <<< Could not remove temporary table: ' . $tempname);
exit;
}
}
// Create a temporary table by reading in the XMLDB file that defines the student enrolment table
$xmldb_file = new xmldb_file(elispm::file('db/install.xml'));
if (!$xmldb_file->fileExists() or !$xmldb_file->loadXMLStructure()) {
continue;
}
$structure = $xmldb_file->getStructure();
$tables = $structure->getTables();
foreach ($tables as $table) {
if ($table->getName() == $tablename) {
$xml_temptable = $table;
$xml_temptable->setName($tempname);
$xml_temptable->setPrevious(null);
$xml_temptable->setNext(null);
$tempstructure = new xmldb_structure('temp');
$tempstructure->addTable($xml_temptable);
try {
$dbman->install_from_xmldb_structure($tempstructure);
} catch (ddl_change_structure_exception $e) {
mtrace(' <<< Could not create temporary table: ' . $tempname);
exit;
}
mtrace(' >>> Created temporary table: ' . $tempname);
}
}
$xml_table = new xmldb_table($tablename);
// Step 1. -- attempt to move unique values into the temporary table in a way that should leave some duplicates but
// will remove the vast majority of the them
$sql = "INSERT INTO {{$tempname}}\n SELECT id, classid, userid, enrolmenttime, MIN(completetime) AS completetime, endtime, completestatusid,\n grade, credits, locked\n FROM {{$tablename}}\n GROUP BY classid, userid, completestatusid, grade, credits, locked";
try {
$DB->execute($sql);
} catch (dml_exception $e) {
mtrace(' <<< Could not move duplicate records from: ' . $tablename . ' into: ' . $tempname);
exit;
}
mtrace(' >>> Moved duplicate records from: ' . $tablename . ' into: ' . $tempname);
// Step 2. -- detect if we still have any duplicates remaining
$sql = "SELECT id, COUNT(*) AS count, classid, userid, enrolmenttime, completetime, completestatusid, grade, credits, locked\n FROM {{$tempname}}\n GROUP BY classid, userid\n ORDER BY count DESC, classid ASC, userid ASC";
if ($dupcount = $DB->get_records_sql($sql, array(), 0, 1)) {
$dupe = current($dupcount);
if ($dupe->count > 1) {
mtrace(' <<< Duplicate records still exist in temporary table');
} else {
mtrace(' >>> No duplicate records exist in temporary table');
}
}
// Step 3. -- at this point duplicate data was found, so we will need to process each record in turn to find the first
// legitimate record that should be kept
if ($rs = $DB->get_recordset_sql($sql)) {
foreach ($rs as $dupe) {
if ($dupe->count <= 1) {
continue;
}
$classid = $dupe->classid;
$userid = $dupe->userid;
$goodid = 0;
// The ID of the record we will keep
// Find the record marked "complete" or "failed" and locked with the earliest completion time
$sql2 = "SELECT id, completestatusid, grade locked\n FROM {{$tempname}}\n WHERE userid = {$userid}\n AND classid = {$classid}\n ORDER BY completetime ASC, completestatusid ASC, locked ASC";
if ($rs2 = $DB->get_recordset_sql($sql2)) {
foreach ($rs2 as $rec) {
// Store the last record ID just in case we need it for cleanup
$lastid = $rec->id;
// Don't bother looking at remaining records if we have found a record to keep
if (!empty($goodid)) {
continue;
}
if ($rec->completestatusid != 0 && ($rec->locked = 1)) {
$goodid = $rec->id;
}
}
$rs2->close();
// We need to make sure we have a record ID to keep, if we found no "complete" and locked
// records, let's just keep the last record we saw
if (empty($goodid)) {
$goodid = $lastid;
}
$select = 'classid = ' . $classid . ' AND userid = ' . $userid . ' AND id != ' . $goodid;
}
// If we have some records to clean up, let's do that now
if (!empty($select)) {
$status = true;
try {
$DB->delete_records_select($tempname, $select);
} catch (dml_exception $e) {
mtrace(' <<< Could not clean up duplicate ' . $tempname . ' records for userid = ' . $userid . ', classid = ' . $classid);
$status = false;
}
if ($status) {
mtrace(' >>> Cleaned up duplicate ' . $tempname . ' records for userid = ' . $userid . ', classid = ' . $classid);
}
}
}
$rs->close();
}
// Step 4. -- drop the table containing duplicate values and rename the temporary table to take it's place
try {
$dbman->drop_table($xml_table);
} catch (ddl_change_structure_exception $e) {
mtrace(' <<< Could not drop table: ' . $tablename);
exit;
}
mtrace(' >>> Successfully dropped table: ' . $tablename);
// Rename the temporary table to allow it to replace the real table
try {
$dbman->rename_table($xml_temptable, $tablename);
} catch (ddl_change_structure_exception $e) {
mtrace(' <<< Could not rename table: ' . $tempname . ' to: ' . $tablename);
exit;
}
mtrace(' >>> Successfully renamed table: ' . $tempname . ' to: ' . $tablename);
}