public function test_reorder_rows()
{
global $DB;
$dbman = $DB->get_manager();
$this->resetAfterTest();
$table = new xmldb_table('test_table');
$table->setComment("This is a test'n drop table. You can drop it safely");
$tablename = $table->getName();
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('otherid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
$table->add_field('sortorder', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
$table->add_field('otherdata', XMLDB_TYPE_TEXT, 'big', null, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->add_key('unique', XMLDB_KEY_UNIQUE, array('otherid', 'sortorder'));
$dbman->create_table($table);
// Rows intentionally added in a slightly 'random' order.
// Note we are testing hat the otherid = 1 rows don't get messed up,
// as well as testing that the otherid = 2 rows are updated correctly.
$DB->insert_record($tablename, array('otherid' => 2, 'sortorder' => 1, 'otherdata' => 'To become 4'));
$DB->insert_record($tablename, array('otherid' => 2, 'sortorder' => 2, 'otherdata' => 'To become 1'));
$DB->insert_record($tablename, array('otherid' => 1, 'sortorder' => 1, 'otherdata' => 'Other 1'));
$DB->insert_record($tablename, array('otherid' => 1, 'sortorder' => 2, 'otherdata' => 'Other 2'));
$DB->insert_record($tablename, array('otherid' => 2, 'sortorder' => 3, 'otherdata' => 'To stay at 3'));
$DB->insert_record($tablename, array('otherid' => 2, 'sortorder' => 4, 'otherdata' => 'To become 2'));
update_field_with_unique_index($tablename, 'sortorder', array(1 => 4, 2 => 1, 3 => 3, 4 => 2), array('otherid' => 2));
$this->assertEquals(array(3 => (object) array('id' => 3, 'otherid' => 1, 'sortorder' => 1, 'otherdata' => 'Other 1'), 4 => (object) array('id' => 4, 'otherid' => 1, 'sortorder' => 2, 'otherdata' => 'Other 2')), $DB->get_records($tablename, array('otherid' => 1), 'sortorder'));
$this->assertEquals(array(2 => (object) array('id' => 2, 'otherid' => 2, 'sortorder' => 1, 'otherdata' => 'To become 1'), 6 => (object) array('id' => 6, 'otherid' => 2, 'sortorder' => 2, 'otherdata' => 'To become 2'), 5 => (object) array('id' => 5, 'otherid' => 2, 'sortorder' => 3, 'otherdata' => 'To stay at 3'), 1 => (object) array('id' => 1, 'otherid' => 2, 'sortorder' => 4, 'otherdata' => 'To become 4')), $DB->get_records($tablename, array('otherid' => 2), 'sortorder'));
}
public function setUp()
{
global $CFG, $DB, $UNITTEST;
if (isset($UNITTEST->func_test_db)) {
$this->tdb = $UNITTEST->func_test_db;
} else {
$this->tdb = $DB;
}
unset($CFG->xmldbreconstructprevnext);
// remove this unhack ;-)
$dbman = $this->tdb->get_manager();
$table = new xmldb_table('test_table0');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('type', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL, null, 'general');
$table->add_field('name', XMLDB_TYPE_CHAR, '255', null, XMLDB_NOTNULL, null);
$table->add_field('intro', XMLDB_TYPE_TEXT, 'small', null, XMLDB_NOTNULL, null, null);
$table->add_field('logo', XMLDB_TYPE_BINARY, 'big', null, XMLDB_NOTNULL, null);
$table->add_field('assessed', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('assesstimestart', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('assesstimefinish', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('scale', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
$table->add_field('maxbytes', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('forcesubscribe', XMLDB_TYPE_INTEGER, '1', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('trackingtype', XMLDB_TYPE_INTEGER, '2', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '1');
$table->add_field('rsstype', XMLDB_TYPE_INTEGER, '2', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('rssarticles', XMLDB_TYPE_INTEGER, '2', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('timemodified', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('grade', XMLDB_TYPE_NUMBER, '20,0', XMLDB_UNSIGNED, null, null, null);
$table->add_field('percent', XMLDB_TYPE_NUMBER, '5,2', null, null, null, null);
$table->add_field('warnafter', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('blockafter', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('blockperiod', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->add_key('type-name', XMLDB_KEY_UNIQUE, array('type', 'name'));
$table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
$table->add_index('rsstype', XMLDB_INDEX_UNIQUE, array('rsstype'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
// Second, smaller table
$table = new xmldb_table('test_table1');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_field('name', XMLDB_TYPE_CHAR, '30', null, null, null, 'Moodle');
$table->add_field('secondname', XMLDB_TYPE_CHAR, '30', null, XMLDB_NOTNULL, null, null);
$table->add_field('intro', XMLDB_TYPE_TEXT, 'medium', null, XMLDB_NOTNULL, null, null);
$table->add_field('avatar', XMLDB_TYPE_BINARY, 'medium', null, null, null, null);
$table->add_field('grade', XMLDB_TYPE_NUMBER, '20,10', null, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
// make sure no tables are present!
$this->tearDown();
}
protected function check_table(xmldb_table $xmldb_table, array $metacolumns)
{
global $DB;
$o = '';
$wrong_fields = array();
// Get and process XMLDB fields
if ($xmldb_fields = $xmldb_table->getFields()) {
$o .= '<ul>';
foreach ($xmldb_fields as $xmldb_field) {
// Get the type of the column, we only will process CHAR (VARCHAR2) ones
if ($xmldb_field->getType() != XMLDB_TYPE_CHAR) {
continue;
}
$o .= '<li>' . $this->str['field'] . ': ' . $xmldb_field->getName() . ' ';
// Get current semantic from dictionary, we only will process B (BYTE) ones
// suplying the SQL code to change them to C (CHAR) semantic
$params = array('table_name' => core_text::strtoupper($DB->get_prefix() . $xmldb_table->getName()), 'column_name' => core_text::strtoupper($xmldb_field->getName()), 'data_type' => 'VARCHAR2');
$currentsemantic = $DB->get_field_sql('
SELECT char_used
FROM user_tab_columns
WHERE table_name = :table_name
AND column_name = :column_name
AND data_type = :data_type', $params);
// If using byte semantics, we'll need to change them to char semantics
if ($currentsemantic == 'B') {
$info = '(' . $this->str['expected'] . " 'CHAR', " . $this->str['actual'] . " 'BYTE')";
$o .= '<font color="red">' . $this->str['wrong'] . " {$info}</font>";
// Add the wrong field to the list
$obj = new stdClass();
$obj->table = $xmldb_table;
$obj->field = $xmldb_field;
$wrong_fields[] = $obj;
} else {
$o .= '<font color="green">' . $this->str['ok'] . '</font>';
}
$o .= '</li>';
}
$o .= '</ul>';
}
return array($o, $wrong_fields);
}
/**
* Given one correct xmldb_table, returns the SQL statements
* to create temporary table (inside one array).
*
* @param xmldb_table $xmldb_table The xmldb_table object instance.
* @return array of sql statements
*/
public function getCreateTempTableSQL($xmldb_table)
{
// Do we know collation?
$collation = $this->mdb->get_dbcollation();
$this->temptables->add_temptable($xmldb_table->getName());
$sqlarr = parent::getCreateTableSQL($xmldb_table);
// Let's inject the extra MySQL tweaks.
foreach ($sqlarr as $i => $sql) {
if (strpos($sql, 'CREATE TABLE ') === 0) {
// We do not want the engine hack included in create table SQL.
$sqlarr[$i] = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE TEMPORARY TABLE $1', $sql);
if ($collation) {
if (strpos($collation, 'utf8_') === 0) {
$sqlarr[$i] .= " DEFAULT CHARACTER SET utf8";
}
$sqlarr[$i] .= " DEFAULT COLLATE {$collation}";
}
}
}
return $sqlarr;
}
/**
* Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
*
* PostgreSQL has some severe limits:
* - Any change of type or precision requires a new temporary column to be created, values to
* be transfered potentially casting them, to apply defaults if the column is not null and
* finally, to rename it
* - Changes in null/not null require the SET/DROP NOT NULL clause
* - Changes in default require the SET/DROP DEFAULT clause
*
* @param xmldb_table $xmldb_table The table related to $xmldb_field.
* @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
* @param string $skip_type_clause The type clause on alter columns, NULL by default.
* @param string $skip_default_clause The default clause on alter columns, NULL by default.
* @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
* @return string The field altering SQL statement.
*/
public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL)
{
$results = array();
// To store all the needed SQL commands
// Get the normal names of the table and field
$tablename = $xmldb_table->getName();
$fieldname = $xmldb_field->getName();
// Take a look to field metadata
$meta = $this->mdb->get_columns($tablename);
$metac = $meta[$xmldb_field->getName()];
$oldmetatype = $metac->meta_type;
$oldlength = $metac->max_length;
$olddecimals = empty($metac->scale) ? null : $metac->scale;
$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
$olddefault = empty($metac->has_default) ? null : $metac->default_value;
$typechanged = true;
//By default, assume that the column type has changed
$precisionchanged = true;
//By default, assume that the column precision has changed
$decimalchanged = true;
//By default, assume that the column decimal has changed
$defaultchanged = true;
//By default, assume that the column default has changed
$notnullchanged = true;
//By default, assume that the column notnull has changed
// Detect if we are changing the type of the column
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') {
$typechanged = false;
}
// Detect if we are changing the precision
if ($xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || $oldlength == -1 || $xmldb_field->getLength() == $oldlength) {
$precisionchanged = false;
}
// Detect if we are changing the decimals
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER || $xmldb_field->getType() == XMLDB_TYPE_CHAR || $xmldb_field->getType() == XMLDB_TYPE_TEXT || $xmldb_field->getType() == XMLDB_TYPE_BINARY || !$xmldb_field->getDecimals() || !$olddecimals || $xmldb_field->getDecimals() == $olddecimals) {
$decimalchanged = false;
}
// Detect if we are changing the default
if ($xmldb_field->getDefault() === null && $olddefault === null || $xmldb_field->getDefault() === $olddefault) {
$defaultchanged = false;
}
// Detect if we are changing the nullability
if ($xmldb_field->getNotnull() === $oldnotnull) {
$notnullchanged = false;
}
// Get the quoted name of the table and field
$tablename = $this->getTableName($xmldb_table);
$fieldname = $this->getEncQuoted($xmldb_field->getName());
// Decide if we have changed the column specs (type/precision/decimals)
$specschanged = $typechanged || $precisionchanged || $decimalchanged;
// if specs have changed, need to alter column
if ($specschanged) {
// Always drop any exiting default before alter column (some type changes can cause casting error in default for column)
if ($olddefault !== null) {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT';
// Drop default clause
}
$alterstmt = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $this->getEncQuoted($xmldb_field->getName()) . ' TYPE' . $this->getFieldSQL($xmldb_table, $xmldb_field, null, true, true, null, false);
// Some castings must be performed explicitly (mainly from text|char to numeric|integer)
if (($oldmetatype == 'C' || $oldmetatype == 'X') && ($xmldb_field->getType() == XMLDB_TYPE_NUMBER || $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) {
$alterstmt .= ' USING CAST(' . $fieldname . ' AS NUMERIC)';
// from char or text to number or float
} else {
if (($oldmetatype == 'C' || $oldmetatype == 'X') && $xmldb_field->getType() == XMLDB_TYPE_INTEGER) {
$alterstmt .= ' USING CAST(CAST(' . $fieldname . ' AS NUMERIC) AS INTEGER)';
// From char to integer
}
}
$results[] = $alterstmt;
}
// If the default has changed or we have performed one change in specs
if ($defaultchanged || $specschanged) {
$default_clause = $this->getDefaultClause($xmldb_field);
if ($default_clause) {
$sql = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET' . $default_clause;
// Add default clause
$results[] = $sql;
} else {
if (!$specschanged) {
// Only drop default if we haven't performed one specs change
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP DEFAULT';
// Drop default clause
}
}
}
// If the not null has changed
if ($notnullchanged) {
if ($xmldb_field->getNotnull()) {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' SET NOT NULL';
} else {
$results[] = 'ALTER TABLE ' . $tablename . ' ALTER COLUMN ' . $fieldname . ' DROP NOT NULL';
}
}
// Return the results
return $results;
}
protected function check_table(xmldb_table $xmldb_table, array $metacolumns)
{
global $DB;
$dbman = $DB->get_manager();
$strictchecks = optional_param('strict', false, PARAM_BOOL);
$o = '';
$violatedkeys = array();
// Keys
if ($xmldb_keys = $xmldb_table->getKeys()) {
$o .= ' <ul>';
foreach ($xmldb_keys as $xmldb_key) {
// We are only interested in foreign keys.
if (!in_array($xmldb_key->getType(), array(XMLDB_KEY_FOREIGN, XMLDB_KEY_FOREIGN_UNIQUE))) {
continue;
}
$o .= ' <li>' . $this->str['key'] . ': ' . $xmldb_key->readableInfo() . ' ';
// Work out the SQL to find key violations.
$keyfields = $xmldb_key->getFields();
$reffields = $xmldb_key->getRefFields();
$joinconditions = array();
$nullnessconditions = array();
$params = array();
foreach ($keyfields as $i => $field) {
$joinconditions[] = 't1.' . $field . ' = t2.' . $reffields[$i];
$xmldb_field = $xmldb_table->getField($field);
$default = $xmldb_field->getDefault();
if (!$xmldb_field->getNotNull()) {
$nullnessconditions[] = 't1.' . $field . ' IS NOT NULL';
} else {
if (!$strictchecks && ($default == '0' || !$default)) {
// We have a default of 0 or '' or something like that.
// These generate a lot of false-positives, so ignore them
// for now.
$nullnessconditions[] = 't1.' . $field . ' <> ?';
$params[] = $xmldb_field->getDefault();
}
}
}
$nullnessconditions[] = 't2.id IS NULL';
$sql = 'SELECT count(1) FROM {' . $xmldb_table->getName() . '} t1 LEFT JOIN {' . $xmldb_key->getRefTable() . '} t2 ON ' . implode(' AND ', $joinconditions) . ' WHERE ' . implode(' AND ', $nullnessconditions);
// Check there are any problems in the database.
$violations = $DB->count_records_sql($sql, $params);
if ($violations == 0) {
$o .= '<font color="green">' . $this->str['ok'] . '</font>';
} else {
$o .= '<font color="red">' . $this->str['violations'] . '</font>';
// Add the missing index to the list
$violation = new stdClass();
$violation->table = $xmldb_table;
$violation->key = $xmldb_key;
$violation->numviolations = $violations;
$violation->numrows = $DB->count_records($xmldb_table->getName());
$violation->sql = str_replace('count(1)', '*', $sql);
if (!empty($params)) {
$violation->sqlparams = '(' . implode(', ', $params) . ')';
} else {
$violation->sqlparams = '';
}
$violatedkeys[] = $violation;
}
$o .= '</li>';
}
$o .= ' </ul>';
}
return array($o, $violatedkeys);
}
/**
* Validates the index restrictions.
*
* The error message should not be localised because it is intended for developers,
* end users and admins should never see these problems!
*
* @param xmldb_table $xmldb_table optional when object is table
* @return string null if ok, error message if problem found
*/
function validateDefinition(xmldb_table $xmldb_table=null) {
if (!$xmldb_table) {
return 'Invalid xmldb_index->validateDefinition() call, $xmldb_table si required.';
}
$total = 0;
foreach ($this->getFields() as $fieldname) {
if (!$field = $xmldb_table->getField($fieldname)) {
// argh, we do not have the fields loaded yet, this should not happen during install
continue;
}
switch ($field->getType()) {
case XMLDB_TYPE_INTEGER:
$total += 8; // big int
break;
case XMLDB_TYPE_NUMBER:
$total += 12; // this is just a guess
break;
case XMLDB_TYPE_FLOAT:
$total += 8; // double precision
break;
case XMLDB_TYPE_CHAR:
if ($field->getLength() > self::INDEX_MAX_BYTES / 3) {
return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_CHAR field "'.$field->getName().'" can not be indexed because it is too long.'
.' Limit is '.(self::INDEX_MAX_BYTES/3).' chars.';
}
$total += ($field->getLength() * 3); // the most complex utf-8 chars have 3 bytes
break;
case XMLDB_TYPE_TEXT:
return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_TEXT field "'.$field->getName().'" can not be indexed';
break;
case XMLDB_TYPE_BINARY:
return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_BINARY field "'.$field->getName().'" can not be indexed';
break;
case XMLDB_TYPE_DATETIME:
$total += 8; // this is just a guess
break;
case XMLDB_TYPE_TIMESTAMP:
$total += 8; // this is just a guess
break;
}
}
if ($total > self::INDEX_COMPOSED_MAX_BYTES) {
return 'Invalid index definition in table {'.$xmldb_table->getName(). '}: the composed index on fields "'.implode(',', $this->getFields()).'" is too long.'
.' Limit is '.self::INDEX_COMPOSED_MAX_BYTES.' bytes / '.(self::INDEX_COMPOSED_MAX_BYTES/3).' chars.';
}
return null;
}
/**
* Given one correct xmldb_key, returns its specs.
*
* @param xmldb_table $xmldb_table The table related to $xmldb_key.
* @param xmldb_key $xmldb_key The xmldb_key's specifications requested.
* @return string SQL statement about the xmldb_key.
*/
public function getKeySQL($xmldb_table, $xmldb_key)
{
$key = '';
switch ($xmldb_key->getType()) {
case XMLDB_KEY_PRIMARY:
if ($this->primary_keys) {
if ($this->primary_key_name !== null) {
$key = $this->getEncQuoted($this->primary_key_name);
} else {
$key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'pk');
}
$key .= ' PRIMARY KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
}
break;
case XMLDB_KEY_UNIQUE:
if ($this->unique_keys) {
$key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'uk');
$key .= ' UNIQUE (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
}
break;
case XMLDB_KEY_FOREIGN:
case XMLDB_KEY_FOREIGN_UNIQUE:
if ($this->foreign_keys) {
$key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'fk');
$key .= ' FOREIGN KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
$key .= ' REFERENCES ' . $this->getEncQuoted($this->prefix . $xmldb_key->getRefTable());
$key .= ' (' . implode(', ', $this->getEncQuoted($xmldb_key->getRefFields())) . ')';
}
break;
}
return $key;
}
/**
* Given one xmldb_table, returns it's correct name, depending of all the parametrization
* Overridden to allow change of names in temp tables
*
* @param xmldb_table table whose name we want
* @param boolean to specify if the name must be quoted (if reserved word, only!)
* @return string the correct name of the table
*/
public function getTableName(xmldb_table $xmldb_table, $quoted = true)
{
/// Get the name, supporting special mssql names for temp tables
if ($this->temptables->is_temptable($xmldb_table->getName())) {
$tablename = $this->temptables->get_correct_name($xmldb_table->getName());
} else {
$tablename = $this->prefix . $xmldb_table->getName();
}
/// Apply quotes optionally
if ($quoted) {
$tablename = $this->getEncQuoted($tablename);
}
return $tablename;
}
/**
* Given one xmldb_table, returns it's correct name, depending of all the parametrization
*
* @param xmldb_table table whose name we want
* @param boolean to specify if the name must be quoted (if reserved word, only!)
* @return string the correct name of the table
*/
public function getTableName(xmldb_table $xmldb_table, $quoted = true)
{
/// Get the name
$tablename = $this->prefix . $xmldb_table->getName();
/// Apply quotes optionally
if ($quoted) {
$tablename = $this->getEncQuoted($tablename);
}
return $tablename;
}
/**
* Invoke method, every class will have its own
* returns true/false on completion, setting both
* errormsg and output as necessary
*/
function invoke()
{
parent::invoke();
$result = true;
/// Set own core attributes
//$this->does_generate = ACTION_NONE;
$this->does_generate = ACTION_GENERATE_HTML;
/// These are always here
global $CFG, $XMLDB;
/// Do the job, setting result as needed
if (!data_submitted()) {
///Basic prevention
print_error('wrongcall', 'error');
}
/// Get parameters
$dirpath = required_param('dir', PARAM_PATH);
$dirpath = $CFG->dirroot . $dirpath;
$tableparam = strtolower(required_param('table', PARAM_PATH));
$name = substr(trim(strtolower(required_param('name', PARAM_PATH))), 0, 28);
$comment = required_param('comment', PARAM_CLEAN);
$comment = $comment;
$dbdir =& $XMLDB->dbdirs[$dirpath];
$editeddir =& $XMLDB->editeddirs[$dirpath];
$structure =& $editeddir->xml_file->getStructure();
$table =& $structure->getTable($tableparam);
$errors = array();
/// To store all the errors found
/// Perform some checks
/// Check empty name
if (empty($name)) {
$errors[] = $this->str['tablenameempty'];
}
/// Check incorrect name
if ($name == 'changeme') {
$errors[] = $this->str['incorrecttablename'];
}
/// Check duplicatename
if ($tableparam != $name && $structure->getTable($name)) {
$errors[] = $this->str['duplicatetablename'];
}
if (!empty($errors)) {
$temptable = new xmldb_table($name);
/// Prepare the output
$o = '<p>' . implode(', ', $errors) . '</p>
<p>' . $temptable->getName() . '</p>';
$o .= '<a href="index.php?action=edit_table&table=' . $tableparam . '&dir=' . urlencode(str_replace($CFG->dirroot, '', $dirpath)) . '">[' . $this->str['back'] . ']</a>';
$this->output = $o;
/// Continue if we aren't under errors
} else {
if (empty($errors)) {
/// If there is one name change, do it, changing the prev and next
/// atributes of the adjacent tables
if ($tableparam != $name) {
$table->setName($name);
if ($table->getPrevious()) {
$prev =& $structure->getTable($table->getPrevious());
$prev->setNext($name);
$prev->setChanged(true);
}
if ($table->getNext()) {
$next =& $structure->getTable($table->getNext());
$next->setPrevious($name);
$next->setChanged(true);
}
/// Table has changed
$table->setChanged(true);
}
/// Set comment
if ($table->getComment() != $comment) {
$table->setComment($comment);
/// Table has changed
$table->setChanged(true);
}
/// Recalculate the hash
$structure->calculateHash(true);
/// If the hash has changed from the original one, change the version
/// and mark the structure as changed
$origstructure =& $dbdir->xml_file->getStructure();
if ($structure->getHash() != $origstructure->getHash()) {
$structure->setVersion(userdate(time(), '%Y%m%d', 99, false));
$structure->setChanged(true);
}
/// Launch postaction if exists (leave this here!)
if ($this->getPostAction() && $result) {
return $this->launch($this->getPostAction());
}
}
}
/// Return ok if arrived here
return $result;
}
/**
* Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
*
* @param xmldb_table $xmldb_table The table related to $xmldb_field.
* @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
* @param string $skip_type_clause The type clause on alter columns, NULL by default.
* @param string $skip_default_clause The default clause on alter columns, NULL by default.
* @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
* @return string The field altering SQL statement.
*/
public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL)
{
$results = array();
// To store all the needed SQL commands
// Get the quoted name of the table and field
$tablename = $xmldb_table->getName();
$fieldname = $xmldb_field->getName();
// Take a look to field metadata
$meta = $this->mdb->get_columns($tablename);
$metac = $meta[$fieldname];
$oldmetatype = $metac->meta_type;
$oldlength = $metac->max_length;
$olddecimals = empty($metac->scale) ? null : $metac->scale;
$oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
//$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':');
$typechanged = true;
//By default, assume that the column type has changed
$lengthchanged = true;
//By default, assume that the column length has changed
// Detect if we are changing the type of the column
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I' || $xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N' || $xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F' || $xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C' || $xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X' || $xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B') {
$typechanged = false;
}
// If the new field (and old) specs are for integer, let's be a bit more specific differentiating
// types of integers. Else, some combinations can cause things like MDL-21868
if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') {
if ($xmldb_field->getLength() > 9) {
// Convert our new lenghts to detailed meta types
$newmssqlinttype = 'I8';
} else {
if ($xmldb_field->getLength() > 4) {
$newmssqlinttype = 'I';
} else {
$newmssqlinttype = 'I2';
}
}
if ($metac->type == 'bigint') {
// Convert current DB type to detailed meta type (our metatype is not enough!)
$oldmssqlinttype = 'I8';
} else {
if ($metac->type == 'smallint') {
$oldmssqlinttype = 'I2';
} else {
$oldmssqlinttype = 'I';
}
}
if ($newmssqlinttype != $oldmssqlinttype) {
// Compare new and old meta types
$typechanged = true;
// Change in meta type means change in type at all effects
}
}
// Detect if we are changing the length of the column, not always necessary to drop defaults
// if only the length changes, but it's safe to do it always
if ($xmldb_field->getLength() == $oldlength) {
$lengthchanged = false;
}
// If type or length have changed drop the default if exists
if ($typechanged || $lengthchanged) {
$results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field);
}
// Some changes of type require multiple alter statements, because mssql lacks direct implicit cast between such types
// Here it is the matrix: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx
// Going to store such intermediate alters in array of objects, storing all the info needed
$multiple_alter_stmt = array();
$targettype = $xmldb_field->getType();
if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'I') {
// integer to text
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'N') {
// decimal to text
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'F') {
// float to text
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_INTEGER && $oldmetatype == 'X') {
// text to integer
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
$multiple_alter_stmt[1] = new stdClass();
// and also needs conversion to decimal
$multiple_alter_stmt[1]->type = XMLDB_TYPE_NUMBER;
// without decimal positions
$multiple_alter_stmt[1]->length = 10;
} else {
if ($targettype == XMLDB_TYPE_NUMBER && $oldmetatype == 'X') {
// text to decimal
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
} else {
if ($targettype == XMLDB_TYPE_FLOAT && $oldmetatype == 'X') {
// text to float
$multiple_alter_stmt[0] = new stdClass();
// needs conversion to varchar
$multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
$multiple_alter_stmt[0]->length = 255;
}
}
}
}
}
}
// Just prevent default clauses in this type of sentences for mssql and launch the parent one
if (empty($multiple_alter_stmt)) {
// Direct implicit conversion allowed, launch it
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
} else {
// Direct implicit conversion forbidden, use the intermediate ones
$final_type = $xmldb_field->getType();
// Save final type and length
$final_length = $xmldb_field->getLength();
foreach ($multiple_alter_stmt as $alter) {
$xmldb_field->setType($alter->type);
// Put our intermediate type and length and alter to it
$xmldb_field->setLength($alter->length);
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
}
$xmldb_field->setType($final_type);
// Set the final type and length and alter to it
$xmldb_field->setLength($final_length);
$results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
}
// Finally, process the default clause to add it back if necessary
if ($typechanged || $lengthchanged) {
$results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field));
}
// Return results
return $results;
}
/**
* Test behaviour of create_table()
*/
public function test_create_table() {
$DB = $this->tdb; // do not use global $DB!
$dbman = $this->tdb->get_manager();
// create table
$table = $this->tables['test_table1'];
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
// basic get_tables() test
$tables = $DB->get_tables();
$this->assertTrue(array_key_exists('test_table1', $tables));
// basic get_columns() tests
$columns = $DB->get_columns('test_table1');
$this->assertEqual($columns['id']->meta_type, 'R');
$this->assertEqual($columns['course']->meta_type, 'I');
$this->assertEqual($columns['name']->meta_type, 'C');
$this->assertEqual($columns['secondname']->meta_type, 'C');
$this->assertEqual($columns['thirdname']->meta_type, 'C');
$this->assertEqual($columns['intro']->meta_type, 'X');
$this->assertEqual($columns['avatar']->meta_type, 'B');
$this->assertEqual($columns['grade']->meta_type, 'N');
$this->assertEqual($columns['percentfloat']->meta_type, 'N');
$this->assertEqual($columns['userid']->meta_type, 'I');
// some defaults
$this->assertTrue($columns['course']->has_default);
$this->assertEqual($columns['course']->default_value, 0);
$this->assertTrue($columns['name']->has_default);
$this->assertEqual($columns['name']->default_value, 'Moodle');
$this->assertTrue($columns['secondname']->has_default);
$this->assertEqual($columns['secondname']->default_value, '');
$this->assertTrue($columns['thirdname']->has_default);
$this->assertEqual($columns['thirdname']->default_value, '');
$this->assertTrue($columns['percentfloat']->has_default);
$this->assertEqual($columns['percentfloat']->default_value, 99.9);
$this->assertTrue($columns['userid']->has_default);
$this->assertEqual($columns['userid']->default_value, 0);
// basic get_indexes() test
$indexes = $DB->get_indexes('test_table1');
$courseindex = reset($indexes);
$this->assertEqual($courseindex['unique'], 1);
$this->assertEqual($courseindex['columns'][0], 'course');
// check sequence returns 1 for first insert
$rec = (object)array(
'course' => 10,
'secondname' => 'not important',
'intro' => 'not important');
$this->assertIdentical($DB->insert_record('test_table1', $rec), 1);
// check defined defaults are working ok
$dbrec = $DB->get_record('test_table1', array('id' => 1));
$this->assertEqual($dbrec->name, 'Moodle');
$this->assertEqual($dbrec->thirdname, '');
// check exceptions if multiple R columns
$table = new xmldb_table ('test_table2');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('rid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->add_key('primaryx', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertTrue($e instanceof ddl_exception);
}
// check exceptions missing primary key on R column
$table = new xmldb_table ('test_table2');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertTrue($e instanceof ddl_exception);
}
}
/**
* Returns the code needed to create one trigger for the xmldb_table and xmldb_field passed
*
* @param xmldb_table $xmldb_table The xmldb_table object instance.
* @param xmldb_field $xmldb_field The xmldb_field object instance.
* @param string $sequence_name
* @return array Array of SQL statements to create the sequence.
*/
public function getCreateTriggerSQL($xmldb_table, $xmldb_field, $sequence_name)
{
$trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
$trigger = "CREATE TRIGGER " . $trigger_name;
$trigger .= "\n BEFORE INSERT";
$trigger .= "\nON " . $this->getTableName($xmldb_table);
$trigger .= "\n FOR EACH ROW";
$trigger .= "\nBEGIN";
$trigger .= "\n IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
$trigger .= "\n SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
$trigger .= "\n END IF;";
$trigger .= "\nEND;";
return array($trigger);
}
/**
* Test behaviour of create_table()
*/
public function test_create_table()
{
$DB = $this->tdb;
// do not use global $DB!
$dbman = $this->tdb->get_manager();
// create table
$table = $this->tables['test_table1'];
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
// basic get_tables() test
$tables = $DB->get_tables();
$this->assertTrue(array_key_exists('test_table1', $tables));
// basic get_columns() tests
$columns = $DB->get_columns('test_table1');
$this->assertEquals($columns['id']->meta_type, 'R');
$this->assertEquals($columns['course']->meta_type, 'I');
$this->assertEquals($columns['name']->meta_type, 'C');
$this->assertEquals($columns['secondname']->meta_type, 'C');
$this->assertEquals($columns['thirdname']->meta_type, 'C');
$this->assertEquals($columns['intro']->meta_type, 'X');
$this->assertEquals($columns['avatar']->meta_type, 'B');
$this->assertEquals($columns['grade']->meta_type, 'N');
$this->assertEquals($columns['percentfloat']->meta_type, 'N');
$this->assertEquals($columns['userid']->meta_type, 'I');
// some defaults
$this->assertTrue($columns['course']->has_default);
$this->assertEquals($columns['course']->default_value, 0);
$this->assertTrue($columns['name']->has_default);
$this->assertEquals($columns['name']->default_value, 'Moodle');
$this->assertTrue($columns['secondname']->has_default);
$this->assertEquals($columns['secondname']->default_value, '');
$this->assertTrue($columns['thirdname']->has_default);
$this->assertEquals($columns['thirdname']->default_value, '');
$this->assertTrue($columns['percentfloat']->has_default);
$this->assertEquals($columns['percentfloat']->default_value, 99.90000000000001);
$this->assertTrue($columns['userid']->has_default);
$this->assertEquals($columns['userid']->default_value, 0);
// basic get_indexes() test
$indexes = $DB->get_indexes('test_table1');
$courseindex = reset($indexes);
$this->assertEquals($courseindex['unique'], 1);
$this->assertEquals($courseindex['columns'][0], 'course');
// check sequence returns 1 for first insert
$rec = (object) array('course' => 10, 'secondname' => 'not important', 'intro' => 'not important');
$this->assertSame($DB->insert_record('test_table1', $rec), 1);
// check defined defaults are working ok
$dbrec = $DB->get_record('test_table1', array('id' => 1));
$this->assertEquals($dbrec->name, 'Moodle');
$this->assertEquals($dbrec->thirdname, '');
// check exceptions if multiple R columns
$table = new xmldb_table('test_table2');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('rid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->add_key('primaryx', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertTrue($e instanceof ddl_exception);
}
// check exceptions missing primary key on R column
$table = new xmldb_table('test_table2');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '0');
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertTrue($e instanceof ddl_exception);
}
// long table name names - the largest allowed
$table = new xmldb_table('test_table0123456789_____xyz');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
$dbman->drop_table($table);
// table name is too long
$table = new xmldb_table('test_table0123456789_____xyz9');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// invalid table name
$table = new xmldb_table('test_tableCD');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// weird column names - the largest allowed
$table = new xmldb_table('test_table3');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('abcdef____0123456789_______xyz', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
$dbman->drop_table($table);
// Too long field name - max 30
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('abcdeabcdeabcdeabcdeabcdeabcdez', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid field name
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('abCD', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid integer length
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '21', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid integer default
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 'x');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid decimal length
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_NUMBER, '21,10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid decimal decimals
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_NUMBER, '10,11', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid decimal default
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_NUMBER, '10,5', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 'x');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid float length
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_FLOAT, '21,10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid float decimals
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_FLOAT, '10,11', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
// Invalid float default
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_FLOAT, '10,5', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 'x');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (Exception $e) {
$this->assertSame(get_class($e), 'coding_exception');
}
}
/**
* Add one table to the structure, allowing to specify the desired order
* If it's not specified, then the table is added at the end.
* @param xmldb_table $table
* @param mixed $after
*/
public function addTable($table, $after = null)
{
// Calculate the previous and next tables
$prevtable = null;
$nexttable = null;
if (!$after) {
if ($this->tables) {
end($this->tables);
$prevtable = $this->tables[key($this->tables)];
}
} else {
$prevtable = $this->getTable($after);
}
if ($prevtable && $prevtable->getNext()) {
$nexttable = $this->getTable($prevtable->getNext());
}
// Set current table previous and next attributes
if ($prevtable) {
$table->setPrevious($prevtable->getName());
$prevtable->setNext($table->getName());
}
if ($nexttable) {
$table->setNext($nexttable->getName());
$nexttable->setPrevious($table->getName());
}
// Some more attributes
$table->setLoaded(true);
$table->setChanged(true);
// Add the new table
$this->tables[] = $table;
// Reorder the whole structure
$this->orderTables($this->tables);
// Recalculate the hash
$this->calculateHash(true);
// We have one new table, so the structure has changed
$this->setVersion(userdate(time(), '%Y%m%d', 99, false));
$this->setChanged(true);
}
/**
* This function IS NOT IMPLEMENTED. ONCE WE'LL BE USING RELATIONAL
* INTEGRITY IT WILL BECOME MORE USEFUL. FOR NOW, JUST CALCULATE "OFFICIAL"
* KEY NAMES WITHOUT ACCESSING TO DB AT ALL.
* Given one xmldb_key, the function returns the name of the key in DB (if exists)
* of false if it doesn't exist
*
* @param xmldb_table $xmldb_table The table to be searched.
* @param xmldb_key $xmldb_key The key to be searched.
* @return string key name if found
*/
public function find_key_name(xmldb_table $xmldb_table, xmldb_key $xmldb_key)
{
$keycolumns = $xmldb_key->getFields();
// Get list of keys in table
// first primaries (we aren't going to use this now, because the MetaPrimaryKeys is awful)
//TODO: To implement when we advance in relational integrity
// then uniques (note that Moodle, for now, shouldn't have any UNIQUE KEY for now, but unique indexes)
//TODO: To implement when we advance in relational integrity (note that AdoDB hasn't any MetaXXX for this.
// then foreign (note that Moodle, for now, shouldn't have any FOREIGN KEY for now, but indexes)
//TODO: To implement when we advance in relational integrity (note that AdoDB has one MetaForeignKeys()
//but it's far from perfect.
// TODO: To create the proper functions inside each generator to retrieve all the needed KEY info (name
// columns, reftable and refcolumns
// So all we do is to return the official name of the requested key without any confirmation!)
// One exception, hardcoded primary constraint names
if ($this->generator->primary_key_name && $xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
return $this->generator->primary_key_name;
} else {
// Calculate the name suffix
switch ($xmldb_key->getType()) {
case XMLDB_KEY_PRIMARY:
$suffix = 'pk';
break;
case XMLDB_KEY_UNIQUE:
$suffix = 'uk';
break;
case XMLDB_KEY_FOREIGN_UNIQUE:
case XMLDB_KEY_FOREIGN:
$suffix = 'fk';
break;
}
// And simply, return the official name
return $this->generator->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), $suffix);
}
}
/**
* Test behaviour of create_table()
*/
public function test_create_table()
{
$DB = $this->tdb;
// Do not use global $DB!
$dbman = $this->tdb->get_manager();
// Create table.
$table = $this->tables['test_table1'];
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
// Basic get_tables() test.
$tables = $DB->get_tables();
$this->assertArrayHasKey('test_table1', $tables);
// Basic get_columns() tests.
$columns = $DB->get_columns('test_table1');
$this->assertSame('R', $columns['id']->meta_type);
$this->assertSame('I', $columns['course']->meta_type);
$this->assertSame('C', $columns['name']->meta_type);
$this->assertSame('C', $columns['secondname']->meta_type);
$this->assertSame('C', $columns['thirdname']->meta_type);
$this->assertSame('X', $columns['intro']->meta_type);
$this->assertSame('B', $columns['avatar']->meta_type);
$this->assertSame('N', $columns['grade']->meta_type);
$this->assertSame('N', $columns['percentfloat']->meta_type);
$this->assertSame('I', $columns['userid']->meta_type);
// Some defaults.
$this->assertTrue($columns['course']->has_default);
$this->assertEquals(0, $columns['course']->default_value);
$this->assertTrue($columns['name']->has_default);
$this->assertSame('Moodle', $columns['name']->default_value);
$this->assertTrue($columns['secondname']->has_default);
$this->assertSame('', $columns['secondname']->default_value);
$this->assertTrue($columns['thirdname']->has_default);
$this->assertSame('', $columns['thirdname']->default_value);
$this->assertTrue($columns['percentfloat']->has_default);
$this->assertEquals(99.90000000000001, $columns['percentfloat']->default_value);
$this->assertTrue($columns['userid']->has_default);
$this->assertEquals(0, $columns['userid']->default_value);
// Basic get_indexes() test.
$indexes = $DB->get_indexes('test_table1');
$courseindex = reset($indexes);
$this->assertEquals(1, $courseindex['unique']);
$this->assertSame('course', $courseindex['columns'][0]);
// Check sequence returns 1 for first insert.
$rec = (object) array('course' => 10, 'secondname' => 'not important', 'intro' => 'not important');
$this->assertSame(1, $DB->insert_record('test_table1', $rec));
// Check defined defaults are working ok.
$dbrec = $DB->get_record('test_table1', array('id' => 1));
$this->assertSame('Moodle', $dbrec->name);
$this->assertSame('', $dbrec->thirdname);
// Check exceptions if multiple R columns.
$table = new xmldb_table('test_table2');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('rid', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->add_key('primaryx', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('ddl_exception', $e);
}
// Check exceptions missing primary key on R column.
$table = new xmldb_table('test_table2');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('ddl_exception', $e);
}
// Long table name names - the largest allowed.
$table = new xmldb_table('test_table0123456789_____xyz');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
$dbman->drop_table($table);
// Table name is too long.
$table = new xmldb_table('test_table0123456789_____xyz9');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid table name.
$table = new xmldb_table('test_tableCD');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Weird column names - the largest allowed.
$table = new xmldb_table('test_table3');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('abcdef____0123456789_______xyz', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
$dbman->create_table($table);
$this->assertTrue($dbman->table_exists($table));
$dbman->drop_table($table);
// Too long field name - max 30.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('abcdeabcdeabcdeabcdeabcdeabcdez', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid field name.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('abCD', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid integer length.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '21', null, XMLDB_NOTNULL, null, '2');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid integer default.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, 'x');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid decimal length.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_NUMBER, '21,10', null, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid decimal decimals.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_NUMBER, '10,11', null, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid decimal default.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_NUMBER, '10,5', null, XMLDB_NOTNULL, null, 'x');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid float length.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_FLOAT, '21,10', null, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid float decimals.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_FLOAT, '10,11', null, XMLDB_NOTNULL, null, null);
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
// Invalid float default.
$table = new xmldb_table('test_table4');
$table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
$table->add_field('num', XMLDB_TYPE_FLOAT, '10,5', null, XMLDB_NOTNULL, null, 'x');
$table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
$table->setComment("This is a test'n drop table. You can drop it safely");
$this->tables[$table->getName()] = $table;
try {
$dbman->create_table($table);
$this->fail('Exception expected');
} catch (moodle_exception $e) {
$this->assertInstanceOf('coding_exception', $e);
}
}
/**
* Callback function. Outputs table opening tag.
*
* @param xmldb_table $table - XMLDB object for the exported table
* @return void
*/
public function begin_table_export(xmldb_table $table)
{
$this->output('<table name="' . $table->getName() . '" schemaHash="' . $table->getHash() . '">');
}
/**
* Callback function. Calls importer's finish_table_import callback method.
* @param xmldb_table $table - XMLDB object for the exported table
* @return void
*/
public function finish_table_export(xmldb_table $table)
{
$this->feedback->output(get_string('done', 'core_dbtransfer', $table->getName()), 2);
$this->importer->finish_table_import($table->getName());
}
/**
* Validates the field restrictions.
*
* The error message should not be localised because it is intended for developers,
* end users and admins should never see these problems!
*
* @param xmldb_table $xmldb_table optional when object is table
* @return string null if ok, error message if problem found
*/
function validateDefinition(xmldb_table $xmldb_table = null)
{
if (!$xmldb_table) {
return 'Invalid xmldb_field->validateDefinition() call, $xmldb_table si required.';
}
switch ($this->getType()) {
case XMLDB_TYPE_INTEGER:
break;
case XMLDB_TYPE_NUMBER:
break;
case XMLDB_TYPE_FLOAT:
break;
case XMLDB_TYPE_CHAR:
if ($this->getLength() > self::CHAR_MAX_LENGTH) {
return 'Invalid field definition in table {' . $xmldb_table->getName() . '}: XMLDB_TYPE_CHAR field "' . $this->getName() . '" is too long.' . ' Limit is ' . self::CHAR_MAX_LENGTH . ' chars.';
}
break;
case XMLDB_TYPE_TEXT:
break;
case XMLDB_TYPE_BINARY:
break;
case XMLDB_TYPE_DATETIME:
break;
case XMLDB_TYPE_TIMESTAMP:
break;
}
return null;
}
/**
* Invoke method, every class will have its own
* returns true/false on completion, setting both
* errormsg and output as necessary
*/
function invoke()
{
parent::invoke();
$result = true;
// Set own core attributes
$this->does_generate = ACTION_GENERATE_HTML;
// These are always here
global $CFG, $XMLDB, $DB, $OUTPUT;
// Do the job, setting result as needed
// Get the dir containing the file
$dirpath = required_param('dir', PARAM_PATH);
$dirpath = $CFG->dirroot . $dirpath;
// Get the correct dirs
if (!empty($XMLDB->dbdirs)) {
$dbdir = $XMLDB->dbdirs[$dirpath];
} else {
return false;
}
if (!empty($XMLDB->editeddirs)) {
$editeddir = $XMLDB->editeddirs[$dirpath];
$structure = $editeddir->xml_file->getStructure();
}
$tableparam = optional_param('table', NULL, PARAM_CLEAN);
// If no table, show form
if (!$tableparam) {
// No postaction here
$this->postaction = NULL;
// Get list of tables
$dbtables = $DB->get_tables();
$selecttables = array();
foreach ($dbtables as $dbtable) {
$i = $structure->findTableInArray($dbtable);
if ($i === NULL) {
$selecttables[$dbtable] = $dbtable;
}
}
// Get list of after tables
$aftertables = array();
if ($tables = $structure->getTables()) {
foreach ($tables as $aftertable) {
$aftertables[$aftertable->getName()] = $aftertable->getName();
}
}
if (!$selecttables) {
$this->errormsg = 'No tables available to be retrofitted';
return false;
}
// Now build the form
$o = '<form id="form" action="index.php" method="post">';
$o .= '<div>';
$o .= ' <input type="hidden" name ="dir" value="' . str_replace($CFG->dirroot, '', $dirpath) . '" />';
$o .= ' <input type="hidden" name ="action" value="new_table_from_mysql" />';
$o .= ' <input type="hidden" name ="postaction" value="edit_table" />';
$o .= ' <input type="hidden" name ="sesskey" value="' . sesskey() . '" />';
$o .= ' <table id="formelements" class="boxaligncenter" cellpadding="5">';
$o .= ' <tr><td><label for="menutable" accesskey="t">' . $this->str['createtable'] . ' </label>' . html_writer::select($selecttables, 'table') . '<label for="menuafter" accesskey="a">' . $this->str['aftertable'] . ' </label>' . html_writer::select($aftertables, 'after') . '</td></tr>';
$o .= ' <tr><td colspan="2" align="center"><input type="submit" value="' . $this->str['create'] . '" /></td></tr>';
$o .= ' <tr><td colspan="2" align="center"><a href="index.php?action=edit_xml_file&dir=' . urlencode(str_replace($CFG->dirroot, '', $dirpath)) . '">[' . $this->str['back'] . ']</a></td></tr>';
$o .= ' </table>';
$o .= '</div></form>';
$this->output = $o;
// If table, retrofit information and, if everything works,
// go to the table edit action
} else {
// Get some params (table is mandatory here)
$tableparam = required_param('table', PARAM_CLEAN);
$afterparam = required_param('after', PARAM_CLEAN);
// Create one new xmldb_table
$table = new xmldb_table(strtolower(trim($tableparam)));
$table->setComment($table->getName() . ' table retrofitted from MySQL');
// Get fields info from ADODb
$dbfields = $DB->get_columns($tableparam);
if ($dbfields) {
foreach ($dbfields as $dbfield) {
// Create new XMLDB field
$field = new xmldb_field($dbfield->name);
// Set field with info retrofitted
$field->setFromADOField($dbfield);
// Add field to the table
$table->addField($field);
}
}
// Get PK, UK and indexes info from ADODb
$dbindexes = $DB->get_indexes($tableparam);
if ($dbindexes) {
$lastkey = NULL;
//To temp store the last key processed
foreach ($dbindexes as $indexname => $dbindex) {
// Add the indexname to the array
$dbindex['name'] = $indexname;
// We are handling one xmldb_key (primaries + uniques)
if ($dbindex['unique']) {
$key = new xmldb_key(strtolower($dbindex['name']));
// Set key with info retrofitted
$key->setFromADOKey($dbindex);
// Set default comment to PKs
if ($key->getType() == XMLDB_KEY_PRIMARY) {
}
// Add key to the table
$table->addKey($key);
// We are handling one xmldb_index (non-uniques)
} else {
$index = new xmldb_index(strtolower($dbindex['name']));
// Set index with info retrofitted
$index->setFromADOIndex($dbindex);
// Add index to the table
$table->addIndex($index);
}
}
}
// Finally, add the whole retroffited table to the structure
// in the place specified
$structure->addTable($table, $afterparam);
}
// Launch postaction if exists (leave this here!)
if ($this->getPostAction() && $result) {
return $this->launch($this->getPostAction());
}
// Return ok if arrived here
return $result;
}
/**
* Validates the field restrictions.
*
* The error message should not be localised because it is intended for developers,
* end users and admins should never see these problems!
*
* @param xmldb_table $xmldb_table optional when object is table
* @return string null if ok, error message if problem found
*/
function validateDefinition(xmldb_table $xmldb_table=null) {
if (!$xmldb_table) {
return 'Invalid xmldb_field->validateDefinition() call, $xmldb_table is required.';
}
$name = $this->getName();
if (strlen($name) > self::NAME_MAX_LENGTH) {
return 'Invalid field name in table {'.$xmldb_table->getName().'}: field "'.$this->getName().'" name is too long.'
.' Limit is '.self::NAME_MAX_LENGTH.' chars.';
}
if (!preg_match('/^[a-z][a-z0-9_]*$/', $name)) {
return 'Invalid field name in table {'.$xmldb_table->getName().'}: field "'.$this->getName().'" name includes invalid characters.';
}
switch ($this->getType()) {
case XMLDB_TYPE_INTEGER:
$length = $this->getLength();
if (!is_number($length) or $length <= 0 or $length > self::INTEGER_MAX_LENGTH) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_INTEGER field "'.$this->getName().'" has invalid length';
}
$default = $this->getDefault();
if (!empty($default) and !is_number($default)) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_INTEGER field "'.$this->getName().'" has invalid default';
}
break;
case XMLDB_TYPE_NUMBER:
$maxlength = self::NUMBER_MAX_LENGTH;
if ($xmldb_table->getName() === 'question_numerical_units' and $name === 'multiplier') {
//TODO: remove after MDL-32113 is resolved
$maxlength = 40;
}
$length = $this->getLength();
if (!is_number($length) or $length <= 0 or $length > $maxlength) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_NUMBER field "'.$this->getName().'" has invalid length';
}
$decimals = $this->getDecimals();
$decimals = empty($decimals) ? 0 : $decimals; // fix missing decimals
if (!is_number($decimals) or $decimals < 0 or $decimals > $length) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_NUMBER field "'.$this->getName().'" has invalid decimals';
}
$default = $this->getDefault();
if (!empty($default) and !is_numeric($default)) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_NUMBER field "'.$this->getName().'" has invalid default';
}
break;
case XMLDB_TYPE_FLOAT:
$length = $this->getLength();
$length = empty($length) ? 6 : $length; // weird, it might be better to require something here...
if (!is_number($length) or $length <= 0 or $length > self::FLOAT_MAX_LENGTH) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_FLOAT field "'.$this->getName().'" has invalid length';
}
$decimals = $this->getDecimals();
$decimals = empty($decimals) ? 0 : $decimals; // fix missing decimals
if (!is_number($decimals) or $decimals < 0 or $decimals > $length) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_FLOAT field "'.$this->getName().'" has invalid decimals';
}
$default = $this->getDefault();
if (!empty($default) and !is_numeric($default)) {
return 'Invalid field definition in table {'.$xmldb_table->getName().'}: XMLDB_TYPE_FLOAT field "'.$this->getName().'" has invalid default';
}
break;
case XMLDB_TYPE_CHAR:
if ($this->getLength() > self::CHAR_MAX_LENGTH) {
return 'Invalid field definition in table {'.$xmldb_table->getName(). '}: XMLDB_TYPE_CHAR field "'.$this->getName().'" is too long.'
.' Limit is '.self::CHAR_MAX_LENGTH.' chars.';
}
break;
case XMLDB_TYPE_TEXT:
break;
case XMLDB_TYPE_BINARY:
break;
case XMLDB_TYPE_DATETIME:
break;
case XMLDB_TYPE_TIMESTAMP:
break;
}
return null;
}
