/**
* 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;
/// Do the job, setting result as needed
/// Get the dir containing the file
$dirpath = required_param('dir', PARAM_PATH);
$dirpath = $CFG->dirroot . stripslashes_safe($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();
}
/// ADD YOUR CODE HERE
$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->MetaTables('TABLES');
$selecttables = array();
foreach ($dbtables as $dbtable) {
$dbtable = strtolower(str_replace($CFG->prefix, '', $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 .= ' <table id="formelements" class="boxaligncenter" cellpadding="5">';
$o .= ' <tr><td><label for="table" accesskey="t">' . $this->str['createtable'] . ' </label>' . choose_from_menu($selecttables, 'table', '', 'choose', '', 0, true) . '<label for="after" accesskey="a">' . $this->str['aftertable'] . ' </label>' . choose_from_menu($aftertables, 'after', '', 'choose', '', 0, true) . '</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 XMLDBTable
$table = new XMLDBTable(strtolower(trim($tableparam)));
$table->setComment($table->getName() . ' table retrofitted from MySQL');
/// Get fields info from ADODb
if (!($dbfields = $db->MetaColumns($CFG->prefix . $tableparam))) {
///Try it without prefix if doesn't exist
$dbfields = $db->MetaColumns($tableparam);
}
if ($dbfields) {
foreach ($dbfields as $dbfield) {
/// Create new XMLDB field
$field = new XMLDBField(strtolower($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->MetaIndexes($CFG->prefix . $tableparam, true);
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 XMLDBKey (primaries + uniques)
if ($dbindex['unique']) {
$key = new XMLDBKey(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 XMLDBIndex (non-uniques)
} else {
$index = new XMLDBIndex(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;
}
/**
* Return the DB name of the key described in XMLDBKey, if it exists.
*
* @uses, $db
* @param XMLDBTable the table to be searched
* @param XMLDBKey the key to be searched
* @return string key name of false
*/
function find_key_name(XMLDBTable $table, XMLDBKey $key)
{
/* @var $db ADOConnection */
global $CFG, $db;
// Do this function silently to avoid output during the install/upgrade process
$olddbdebug = $db->debug;
$db->debug = false;
if (!table_exists($table)) {
$db->debug = $olddbdebug;
return false;
}
$tablename = get_config('dbprefix') . $table->getName();
$dbname = get_config('dbname');
// TODO: upstream this to ADODB?
// Postgres puts the database name in the "catalog" field. Mysql puts it in "schema"
if (is_postgres()) {
$dbfield = 'catalog';
// The query to find all the columns for a foreign key constraint
$fkcolsql = "\n SELECT\n ku.column_name,\n ccu.column_name AS refcolumn_name\n FROM\n information_schema.key_column_usage ku\n INNER JOIN information_schema.constraint_column_usage ccu\n ON ku.constraint_name = ccu.constraint_name\n AND ccu.constraint_schema = ku.constraint_schema\n AND ccu.constraint_catalog = ku.constraint_catalog\n AND ccu.table_catalog = ku.constraint_catalog\n AND ccu.table_schema = ku.constraint_schema\n WHERE\n ku.constraint_catalog = ?\n AND ku.constraint_name = ?\n AND ku.table_name = ?\n AND ku.table_catalog = ?\n AND ccu.table_name = ?\n ORDER BY ku.ordinal_position, ku.position_in_unique_constraint\n ";
} else {
$dbfield = 'schema';
// The query to find all the columns for a foreign key constraint
$fkcolsql = '
SELECT
ku.column_name,
ku.referenced_column_name AS refcolumn_name
FROM information_schema.key_column_usage ku
WHERE
ku.constraint_schema = ?
AND ku.constraint_name = ?
AND ku.table_name = ?
AND ku.table_schema = ?
AND ku.referenced_table_name = ?
ORDER BY ku.ordinal_position, ku.position_in_unique_constraint
';
}
// Foreign keys have slightly different logic than primary and unique
$isfk = $key->getType() == XMLDB_KEY_FOREIGN || $key->getType() == XMLDB_KEY_FOREIGN_UNIQUE;
$fields = $key->getFields();
if ($isfk) {
$reffields = $key->getRefFields();
$reftable = get_config('dbprefix') . $key->getRefTable();
// If the XMLDBKey is a foreign key without a ref table, or non-matching fields & ref fields,
// then it's an invalid XMLDBKey and we know it won't match
if (!$key->getRefTable() || count($fields) != count($reffields)) {
log_debug('Invalid XMLDBKey foreign key passed to find_key_name()');
$db->debug = $olddbdebug;
return false;
}
}
// Get the main record for the constraint
$sql = "\n SELECT tc.constraint_name\n FROM\n information_schema.table_constraints tc\n WHERE\n tc.table_name = ?\n AND tc.table_{$dbfield} = ?\n AND tc.constraint_{$dbfield} = ?\n AND tc.constraint_type = ?\n ";
$keytypes = array(XMLDB_KEY_PRIMARY => 'PRIMARY KEY', XMLDB_KEY_UNIQUE => 'UNIQUE', XMLDB_KEY_FOREIGN => 'FOREIGN KEY', XMLDB_KEY_FOREIGN_UNIQUE => 'FOREIGN KEY');
$params = array($tablename, $dbname, $dbname, $keytypes[$key->getType()]);
$constraintrec = get_records_sql_array($sql, $params);
// No constraints of the correct type on this table
if (!$constraintrec) {
$db->debug = $olddbdebug;
return false;
}
// Check each constraint to see if it has the right columns
foreach ($constraintrec as $c) {
if ($isfk) {
$colsql = $fkcolsql;
$colparams = array($dbname, $c->constraint_name, $tablename, $dbname, $reftable);
} else {
$colsql = "SELECT ku.column_name\n FROM information_schema.key_column_usage ku\n WHERE\n ku.table_name = ?\n AND ku.table_{$dbfield} = ?\n AND ku.constraint_{$dbfield} = ?\n AND ku.constraint_name = ?\n ORDER BY ku.ordinal_position, ku.position_in_unique_constraint\n ";
$colparams = array($tablename, $dbname, $dbname, $c->constraint_name);
}
$colrecs = get_records_sql_array($colsql, $colparams);
// Make sure they've got the same number of columns
if (!$colrecs || count($fields) != count($colrecs)) {
// No match, try the next one
continue;
}
// Make sure the columns match.
reset($fields);
reset($colrecs);
if ($isfk) {
reset($reffields);
}
while (($field = current($fields)) && ($col = current($colrecs))) {
if (!$field == $col->column_name) {
// This constraint has a non-matching column; try the next constraint
continue 2;
}
if ($isfk) {
$reffield = current($reffields);
if (!$reffield == $col->refcolumn_name) {
// This constraint has a non-matching column; try the next constraint
continue 2;
}
next($reffields);
}
next($fields);
next($colrecs);
}
// If they made it this far, then it's a match!
$db->debug = $olddbdebug;
return $c->constraint_name;
}
// None matched, so return false
$db->debug = $olddbdebug;
return false;
}
