/**
* Gets the CREATE TABLE command of a given table
*
* @param DriverBase $dbi The db connection to the INFORMATION_SCHEMA db
* @param string $table_name The name of the table
* @param string $table_abstract The abstract name of the table
* @param \stdClass $table_object The TABLES object for this table
* @param array $dependencies Dependency tracking information
*
* @return string The CREATE TABLE definition
*/
protected function get_create_table(&$dbi, $table_name, $table_abstract, $table_object, &$dependencies)
{
$configuration = Factory::getConfiguration();
$notracking = $configuration->get('engine.dump.native.nodependencies', 0);
$useabstract = Factory::getEngineParamsProvider()->getScriptingParameter('db.abstractnames', 1);
$columns_sql = array();
$keys_sql = array();
$indexes_sql = array();
$constraints_sql = array();
// =====================================================================
// ========== GENERATE SQL FOR COLUMNS
// =====================================================================
// Get columns
$query = 'SELECT * FROM information_schema.columns WHERE "table_catalog" = ' . $dbi->quote($this->database) . ' AND "table_schema" = \'public\'' . ' AND "table_name" = ' . $dbi->quote($table_name) . ' ORDER BY "ordinal_position" ASC';
$dbi->setQuery($query);
$allColumns = $dbi->loadObjectList();
foreach ($allColumns as $oColumn) {
$default = $oColumn->column_default;
if (!empty($default) && strstr($default, 'nextval(') !== false && strstr($default, '::regclass') !== false && strstr($oColumn->data_type, 'int') !== false) {
$line = $dbi->quoteName($oColumn->column_name) . ' serial NOT NULL';
} else {
$line = $dbi->quoteName($oColumn->column_name) . ' ' . $oColumn->data_type . ' ';
if (!empty($oColumn->character_maximum_length) && !in_array($oColumn->data_type, array('text'))) {
$line .= '(' . $oColumn->character_maximum_length . ') ';
}
$line .= $oColumn->is_nullable == 'YES' ? 'NULL ' : 'NOT NULL ';
if (!empty($default)) {
$line .= ' DEFAULT ' . $default;
}
}
$columns_sql[] = $line;
}
// =====================================================================
// ========== GENERATE SQL FOR KEYS AND INDICES
// =====================================================================
// Get the primary and unique key names
$query = 'SELECT "constraint_name", "constraint_type" FROM information_schema.table_constraints WHERE "table_catalog" = ' . $dbi->quote($this->database) . ' AND "table_schema" = \'public\'' . ' AND "table_name" = ' . $dbi->quote($table_name) . ' AND "constraint_type" IN(' . $dbi->quote('PRIMARY KEY') . ', ' . $dbi->quote('UNIQUE') . ')';
$dbi->setQuery($query);
$specialKeys = $dbi->loadObjectList('constraint_name');
// Get the columns per key and key information
$query = 'SELECT * FROM information_schema.constraint_column_usage WHERE "table_catalog" = ' . $dbi->quote($this->database) . ' AND "table_schema" = \'public\'' . ' AND "table_name" = ' . $dbi->quote($table_name);
$dbi->setQuery($query);
$allColumns = $dbi->loadObjectList();
$rawKeys = array();
if (!empty($allColumns)) {
foreach ($allColumns as $oColumn) {
if (!array_key_exists($oColumn->constraint_name, $rawKeys)) {
$entry = array('name' => $oColumn->constraint_name, 'def' => 'INDEX', 'columns' => array());
if ($useabstract) {
$entry['name'] = $this->getAbstract($entry['name']);
}
if (array_key_exists($oColumn->constraint_name, $specialKeys)) {
$entry['def'] = $specialKeys[$oColumn->constraint_name]->constraint_type;
if ($entry['def'] == 'PRIMARY') {
$entry['def'] = 'PRIMARY KEY';
}
}
$rawKeys[$oColumn->constraint_name] = $entry;
}
// Add the column to the index
$rawKeys[$oColumn->constraint_name]['columns'][] = '"' . $oColumn->column_name . '"';
}
}
// Piece together the keys' SQL statements
if (!empty($rawKeys)) {
foreach ($rawKeys as $keydef) {
$line = ' ' . $keydef['def'] . ' ';
if (!in_array($keydef['def'], array('PRIMARY KEY', 'UNIQUE'))) {
$line = 'CREATE ' . $line;
$thistable = $useabstract ? $table_abstract : $table_name;
$line .= "\"{$keydef['name']}\" ON \"{$thistable}\"";
}
$line .= '(';
$line .= implode(',', $keydef['columns']);
$line .= ')';
if (!in_array($keydef['def'], array('PRIMARY KEY', 'UNIQUE'))) {
$indexes_sql[] = $line;
} else {
$keys_sql[] = $line;
}
}
}
// =====================================================================
// ========== GENERATE SQL FOR CONSTRAINTS
// =====================================================================
// Get the foreign key names
$query = 'SELECT ccu.table_name as ftable, ccu.column_name as fcolumn, ' . 'kku.table_name as ltable, kku.column_name as lcolumn,' . 'ccu.constraint_name AS constraint_name, ' . 'rc.match_option, rc.update_rule, rc.delete_rule ' . 'FROM ' . 'information_schema.constraint_column_usage as ccu ' . 'INNER JOIN information_schema.key_column_usage AS kku ON ' . '(kku.constraint_name = ccu.constraint_name) ' . 'INNER JOIN information_schema.referential_constraints AS rc ON (rc.constraint_name = ccu.constraint_name) ' . 'WHERE ccu.table_name <> kku.table_name ' . 'AND kku.table_name = ' . $dbi->quote($table_name) . ' AND kku.constraint_catalog = ' . $dbi->quote($this->database) . ' AND kku.constraint_schema = \'public\'';
$dbi->setQuery($query);
$allFKColumns = $dbi->loadObjectList();
$rawConstraints = array();
if (!empty($allFKColumns)) {
foreach ($allFKColumns as $oColumn) {
if (!array_key_exists($oColumn->constraint_name, $rawConstraints)) {
$entry = array('name' => $oColumn->constraint_name, 'cols' => array(), 'refcols' => array(), 'reftable' => '', 'update' => '', 'delete' => '');
if ($useabstract) {
$entry['name'] = $this->getAbstract($entry['name']);
}
$entry['update'] = $oColumn->update_rule;
$entry['delete'] = $oColumn->delete_rule;
$reftable = $oColumn->ftable;
// Add a reference hit
$this->dependencies[$reftable][] = $table_name;
// Add the dependency to this table's metadata
$dependencies[] = $reftable;
if ($useabstract) {
$reftable = $this->getAbstract($reftable);
}
$entry['reftable'] = $reftable;
$rawConstraints[$oColumn->constraint_name] = $entry;
}
$rawConstraints[$oColumn->constraint_name]['cols'][] = '"' . $oColumn->lcolumn . '"';
$rawConstraints[$oColumn->constraint_name]['refcols'][] = '"' . $oColumn->fcolumn . '"';
}
}
// Piece together the constraints' SQL statements
if (!empty($rawConstraints)) {
foreach ($rawConstraints as $keydef) {
$line = ' CONSTRAINT ';
if ($keydef['name']) {
$line .= "\"{$keydef['name']}\" ";
}
$line .= 'FOREIGN KEY (';
$line .= implode(',', $keydef['cols']);
$line .= ') REFERENCES "' . $keydef['reftable'] . '" (';
$line .= implode(',', $keydef['refcols']);
$line .= ')';
if ($keydef['delete']) {
$line .= ' ON DELETE ' . $keydef['delete'];
}
if ($keydef['update']) {
$line .= ' ON UPDATE ' . $keydef['update'];
}
$constraints_sql[] = $line;
}
}
// =====================================================================
// ========== CONSTRUCT THE TABLE CREATE STATEMENT
// =====================================================================
// Create the SQL output
if ($useabstract) {
$table_sql = "CREATE TABLE \"{$table_abstract}\" (";
} else {
$table_sql = "CREATE TABLE \"{$table_name}\" (";
}
$table_sql .= implode(',', $columns_sql);
if (count($keys_sql)) {
$table_sql .= ',' . implode(',', $keys_sql);
}
if (count($constraints_sql)) {
$table_sql .= ',' . implode(',', $constraints_sql);
}
$table_sql .= ")";
$table_sql .= ";\n";
if (!empty($indexes_sql)) {
foreach ($indexes_sql as $index) {
$table_sql .= $index . ";\n";
}
}
return $table_sql;
}
/**
* Gets the CREATE TABLE command of a given table
*
* @param DriverBase $dbi The db connection to the INFORMATION_SCHEMA db
* @param string $table_name The name of the table
* @param string $table_abstract The abstract name of the table
* @param \stdClass $table_object The SYS.OBJECTS record for this table
* @param array $dependencies Dependency tracking information
*
* @return string The CREATE TABLE definition
*/
protected function get_create_table(&$dbi, $table_name, $table_abstract, $table_object, &$dependencies)
{
$configuration = Factory::getConfiguration();
$notracking = $configuration->get('engine.dump.native.nodependencies', 0);
$useabstract = Factory::getEngineParamsProvider()->getScriptingParameter('db.abstractnames', 1);
$columns_sql = array();
$keys_sql = array();
$constraints_sql = array();
$indexes_sql = array();
// =====================================================================
// ========== GENERATE SQL FOR COLUMNS
// =====================================================================
// Get identity columns for this table
$sysObjectID = $table_object->object_id;
$query = 'SELECT COUNT(*) FROM sys.identity_columns WHERE object_id = ' . $dbi->quote($sysObjectID);
$dbi->setQuery($query);
$countIdentityColumns = $dbi->loadResult();
if ($countIdentityColumns) {
/**
* $query = 'SELECT column_id, name, seed_value, increment_value FROM sys.identity_columns WHERE object_id = ' . $dbi->quote($sysObjectID)
* . ' ORDER BY column_id ASC';
**/
$query = 'select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ' . $dbi->quote($table_name) . 'and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, \'IsIdentity\') = 1';
$dbi->setQuery($query);
$identityColumns = $dbi->loadAssocList('COLUMN_NAME');
} else {
$identityColumns = array();
}
// Get columns
$query = 'SELECT * FROM information_schema.columns WHERE table_catalog = ' . $dbi->quote($this->database) . ' AND table_name = ' . $dbi->quote($table_name) . ' ORDER BY ordinal_position ASC';
$dbi->setQuery($query);
$allColumns = $dbi->loadObjectList();
foreach ($allColumns as $oColumn) {
$line = '[' . $oColumn->COLUMN_NAME . '] [' . $oColumn->DATA_TYPE . ']';
switch ($oColumn->DATA_TYPE) {
case 'bigint':
case 'int':
case 'smallint':
case 'tinyint':
/* NOT SUPPORTED *
$precision = $oColumn->NUMERIC_PRECISION;
$scale = $oColumn->NUMERIC_SCALE;
if ($precision)
{
$line .= '(';
if ($precision && $scale)
{
$line .= $precision . ', ' . $scale;
}
else
{
$line .= $precision;
}
$line .= ')';
}
*/
break;
case 'nvarchar':
case 'nchar':
$len = $oColumn->CHARACTER_MAXIMUM_LENGTH;
if ($len < 0) {
$len = 'max';
}
$line .= '(' . $len . ')';
break;
case 'datetime':
case 'datetime2':
/* NOT SUPPORTED *
$precision = $oColumn->DATETIME_PRECISION;
if ($precision)
{
$line .= '(' . $precision . ')';
}
*/
break;
case 'float':
case 'real':
break;
}
$line .= $oColumn->IS_NULLABLE == 'YES' ? 'NULL ' : 'NOT NULL ';
if (array_key_exists($oColumn->COLUMN_NAME, $identityColumns)) {
/**
* $seed = $identityColumns[$oColumn->COLUMN_NAME]['seed_value'];
* $increment = $identityColumns[$oColumn->COLUMN_NAME]['increment_value'];
**/
// fake the seed and increment because Microsoft sucks and their API is broken!
$qLala = 'SELECT MAX(' . $oColumn->COLUMN_NAME . ') FROM ' . $dbi->quoteName($table_name);
$dbi->setQuery($qLala);
$seed = (int) $dbi->loadResult();
$increment = 1;
$line .= ' IDENTITY (' . $seed . ', ' . $increment . ')';
}
$line .= $oColumn->COLUMN_DEFAULT == '' ? '' : ' DEFAULT ' . $oColumn->COLUMN_DEFAULT . ' ';
$columns_sql[] = $line;
}
// =====================================================================
// ========== GENERATE SQL FOR KEYS AND INDICES
// =====================================================================
// Get the primary and unique key names
$query = 'SELECT * from sys.indexes where object_id = OBJECT_ID(' . $dbi->q($table_name) . ')';
$dbi->setQuery($query);
$allKeys = $dbi->loadObjectList('name');
// Get the columns per key and key information
$query = 'select c.name, ic.* from sys.index_columns as ic inner join sys.columns as c on(c.object_id = ic.object_id and c.column_id = ic.column_id) ' . 'where ic.object_id = OBJECT_ID(' . $dbi->q($table_name) . ') order by index_id ASC, index_column_id ASC';
$dbi->setQuery($query);
$allColumns = $dbi->loadObjectList();
$rawKeys = array();
if (!empty($allKeys)) {
foreach ($allKeys as $currentKey) {
if (empty($currentKey->name)) {
continue;
}
$isUnique = $currentKey->is_unique == 1;
$isPrimary = $currentKey->is_primary_key == 1;
$keyName = $currentKey->name;
if ($useabstract && strlen($this->prefix) && substr($this->prefix, -1) == '_') {
$keyName = str_replace($this->prefix, '#__', $keyName);
}
if ($isPrimary) {
$line = 'CONSTRAINT [' . $keyName . '] ';
$line .= 'PRIMARY KEY ' . $currentKey->type_desc;
} elseif ($isUnique) {
$line = 'CONSTRAINT [' . $keyName . '] ';
$line .= 'UNIQUE ' . $currentKey->type_desc;
} else {
//$line = 'CREATE ' . $currentKey->type_desc . ' INDEX [' . $this->getAbstract($currentKey->name) . '] ON [' . $table_abstract . ']';
if ($useabstract) {
$line = 'CREATE INDEX [' . $keyName . '] ON [' . $table_abstract . ']';
} else {
$line = 'CREATE INDEX [' . $keyName . '] ON [' . $table_name . ']';
}
}
$line .= '(';
// Add columns
$cols = array();
foreach ($allColumns as $oColumn) {
if ($oColumn->index_id != $currentKey->index_id) {
continue;
}
$cols[] = '[' . $oColumn->name . '] ' . ($oColumn->is_descending_key ? 'DESC' : 'ASC');
}
$line .= implode(', ', $cols);
$line .= ')';
// append WITH (...blah...) ON [PRIMARY]
$line .= ' WITH (';
if ($isPrimary || $isUnique) {
$line .= 'PAD_INDEX = ' . ($currentKey->is_padded ? 'ON' : 'OFF');
$line .= ', STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]';
} else {
$line .= 'STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)';
}
// Add primary/unique keys to $keys_sql, add indices to $indexes_sql and paste them as new lines below the create command
if ($isPrimary || $isUnique) {
$keys_sql[] = $line;
} else {
$indexes_sql[] = $line . ';';
}
}
}
// =====================================================================
// ========== GENERATE SQL FOR FOREIGN KEYS
// =====================================================================
// Get the foreign key names
$query = 'SELECT * FROM sys.foreign_keys WHERE parent_object_id = ' . $dbi->quote($sysObjectID) . ' AND [type] = ' . $dbi->quote('F');
$dbi->setQuery($query);
$foreignKeyInfo = $dbi->loadObjectList('name');
$rawConstraints = array();
if (!empty($foreignKeyInfo)) {
foreach ($foreignKeyInfo as $oKey) {
// Get the columns per key and key information
$query = 'SELECT * FROM sys.foreign_key_columns WHERE parent_object_id = ' . $dbi->quote($sysObjectID) . ' AND constraint_object_id = ' . $dbi->q($oKey->object_id);
$dbi->setQuery($query);
$allFKColumns = $dbi->loadObjectList();
// Get referenced table's name
$refID = $oKey->referenced_object_id;
$query = 'SELECT name FROM sys.objects WHERE object_id = ' . $dbi->q($refID);
$dbi->setQuery($query);
$refObjectName = $dbi->loadResult();
// Initialise column maps
$FKcolumnMap = array();
// Loop through each column and map parent_object_id / parent_column_id, referenced_object_id / referenced_column_id to column names
foreach ($allFKColumns as $oColumn) {
$objectIDs = array($oColumn->parent_object_id, $oColumn->referenced_object_id);
foreach ($objectIDs as $oid) {
if (!array_key_exists($oid, $FKcolumnMap)) {
$query = $dbi->getQuery(true)->select(array('name', 'column_id'))->from('sys.columns')->where('object_id = ' . $dbi->q($oid));
$dbi->setQuery($query);
$FKcolumnMap[$oid] = $dbi->loadObjectList('column_id');
}
}
}
$keyName = $oKey->name;
if (strlen($this->prefix) && substr($this->prefix, -1) == '_') {
$keyName = str_replace($this->prefix, '#__', $keyName);
}
$line = 'CONSTRAINT [' . $keyName . '] FOREIGN KEY (';
$tempCols = array();
foreach ($allFKColumns as $oColumn) {
$oid = $oColumn->parent_object_id;
$cid = $oColumn->parent_column_id;
$tempCols[] = '[' . $FKcolumnMap[$oid][$cid]->name . ']';
}
$line .= implode(', ', $tempCols);
// Add a reference hit
$this->dependencies[$refObjectName][] = $table_name;
// Add the dependency to this table's metadata
$dependencies[] = $refObjectName;
if ($useabstract) {
$refObjectName = $this->getAbstract($refObjectName);
}
$line .= ') REFERENCES [' . $refObjectName . '] (';
$tempCols = array();
foreach ($allFKColumns as $oColumn) {
$oid = $oColumn->referenced_object_id;
$cid = $oColumn->referenced_column_id;
$tempCols[] = '[' . $FKcolumnMap[$oid][$cid]->name . ']';
}
$line .= implode(', ', $tempCols);
$line .= ') ';
// Tuck the delete and update actions
$line .= ' ON DELETE ';
switch ($oKey->delete_referential_action_desc) {
case 'NO_ACTION':
$line .= 'NO ACTION';
break;
case 'CASCADE':
$line .= 'CASCADE';
break;
case 'SET_NULL':
$line .= 'SET NULL';
break;
case 'SET_DEFAULT':
$line .= 'SET DEFAULT';
break;
}
$line .= ' ON UPDATE ';
switch ($oKey->update_referential_action_desc) {
case 'NO_ACTION':
$line .= 'NO ACTION';
break;
case 'CASCADE':
$line .= 'CASCADE';
break;
case 'SET_NULL':
$line .= 'SET NULL';
break;
case 'SET_DEFAULT':
$line .= 'SET DEFAULT';
break;
}
if ($oKey->is_not_for_replication) {
$line .= ' NOT FOR REPLICATION';
}
// add to the $constraints_sql array
$constraints_sql[] = $line;
}
}
// =====================================================================
// ==========CONSTRUCT THE TABLE CREATE STATEMENT
// =====================================================================
// Create the SQL output
if ($useabstract) {
$table_sql = "CREATE TABLE [{$table_abstract}] (";
} else {
$table_sql = "CREATE TABLE [{$table_name}] (";
}
$table_sql .= implode(',', $columns_sql);
if (count($keys_sql)) {
$table_sql .= ',' . implode(',', $keys_sql);
}
if (count($constraints_sql)) {
$table_sql .= ',' . implode(',', $constraints_sql);
}
$table_sql .= ")";
$table_sql .= ";\n";
$table_sql .= implode(";\n", $indexes_sql);
if (count($indexes_sql) >= 1) {
$table_sql .= "\n";
}
return $table_sql;
}
/**
* Gets the CREATE TABLE command of a given table
*
* @param DriverBase $dbi The db connection to the INFORMATION_SCHEMA db
* @param string $table_name The name of the table
* @param string $table_abstract The abstract name of the table
* @param \stdClass $table_object The TABLES object for this table
* @param array $dependencies Dependency tracking information
*
* @return string The CREATE TABLE definition
*/
protected function get_create_table(&$dbi, $table_name, $table_abstract, $table_object, &$dependencies)
{
$configuration = Factory::getConfiguration();
$notracking = $configuration->get('engine.dump.native.nodependencies', 0);
$useabstract = Factory::getEngineParamsProvider()->getScriptingParameter('db.abstractnames', 1);
$columns_sql = array();
$keys_sql = array();
$constraints_sql = array();
// =====================================================================
// ========== GENERATE SQL FOR COLUMNS
// =====================================================================
// Get columns
$query = 'SELECT * FROM `columns` WHERE `table_schema` = ' . $dbi->quote($this->database) . ' AND `table_name` = ' . $dbi->quote($table_name) . ' ORDER BY `ordinal_position` ASC';
$dbi->setQuery($query);
$allColumns = $dbi->loadObjectList();
foreach ($allColumns as $oColumn) {
$line = $dbi->quoteName($oColumn->COLUMN_NAME) . ' ' . $oColumn->COLUMN_TYPE . ' ';
$line .= $oColumn->IS_NULLABLE == 'YES' ? 'NULL ' : 'NOT NULL ';
if (!in_array($oColumn->EXTRA, array('CURRENT_TIMESTAMP'))) {
$line .= $oColumn->EXTRA == '' ? '' : $oColumn->EXTRA . ' ';
}
if (in_array($oColumn->COLUMN_DEFAULT, array('CURRENT_TIMESTAMP'))) {
$line .= ' DEFAULT ' . $oColumn->COLUMN_DEFAULT;
} else {
$line .= $oColumn->COLUMN_DEFAULT == '' ? '' : ' DEFAULT ' . $dbi->quote($oColumn->COLUMN_DEFAULT) . ' ';
}
$line .= $oColumn->COLUMN_COMMENT == '' ? '' : ' COMMENT ' . $dbi->quote($oColumn->COLUMN_COMMENT);
$columns_sql[] = $line;
}
// =====================================================================
// ========== GENERATE SQL FOR KEYS AND INDICES
// =====================================================================
// Get the primary and unique key names
$query = 'SELECT `constraint_name`, `constraint_type` FROM `table_constraints` WHERE `table_schema` = ' . $dbi->quote($this->database) . ' AND `table_name` = ' . $dbi->quote($table_name) . ' AND `constraint_type` IN(' . $dbi->quote('PRIMARY KEY') . ', ' . $dbi->quote('UNIQUE') . ')';
$dbi->setQuery($query);
$specialKeys = $dbi->loadObjectList('constraint_name');
// Get the columns per key and key information
$query = 'SELECT * FROM `statistics` WHERE `table_schema` = ' . $dbi->quote($this->database) . ' AND `table_name` = ' . $dbi->quote($table_name) . ' ORDER BY `INDEX_NAME` ASC, SEQ_IN_INDEX ASC';
$dbi->setQuery($query);
$allColumns = $dbi->loadObjectList();
$rawKeys = array();
if (!empty($allColumns)) {
foreach ($allColumns as $oColumn) {
if (!array_key_exists($oColumn->INDEX_NAME, $rawKeys)) {
$entry = array('name' => $oColumn->INDEX_NAME, 'def' => 'KEY', 'columns' => array(), 'type' => 'BTREE');
if (array_key_exists($oColumn->INDEX_NAME, $specialKeys)) {
$entry['def'] = $specialKeys[$oColumn->INDEX_NAME]->constraint_type;
if ($entry['def'] == 'UNIQUE') {
$entry['def'] = 'UNIQUE KEY';
} elseif ($entry['def'] == 'PRIMARY') {
$entry['def'] = 'PRIMARY KEY';
}
}
$rawKeys[$oColumn->INDEX_NAME] = $entry;
}
// This is the optional key length for each column
$subpart = '';
if ($oColumn->SUB_PART) {
$subpart = '(' . $oColumn->SUB_PART . ')';
}
// Add the column to the index
$rawKeys[$oColumn->INDEX_NAME]['columns'][] = '`' . $oColumn->COLUMN_NAME . '`' . $subpart;
// Setup the index type
$rawKeys[$oColumn->INDEX_NAME]['type'] = $oColumn->INDEX_TYPE;
}
}
// Piece together the keys' SQL statements
if (!empty($rawKeys)) {
foreach ($rawKeys as $keydef) {
$line = ' ' . $keydef['def'] . ' ';
if ($keydef['type'] == 'FULLTEXT') {
$line = ' ' . $keydef['type'] . $line;
$keydef['type'] = '';
}
if ($keydef['def'] != 'PRIMARY KEY') {
$line .= "`{$keydef['name']}` ";
}
$line .= '(';
$line .= implode(',', $keydef['columns']);
$line .= ')';
if (!empty($keydef['type']) && $keydef['def'] != 'PRIMARY KEY') {
$line .= ' USING ' . $keydef['type'];
}
$keys_sql[] = $line;
}
}
// =====================================================================
// ========== GENERATE SQL FOR CONSTRAINTS
// =====================================================================
// Get the foreign key names
$query = 'SELECT * FROM `referential_constraints` WHERE `constraint_schema` = ' . $dbi->quote($this->database) . ' AND `table_name` = ' . $dbi->quote($table_name);
$dbi->setQuery($query);
$foreignKeyInfo = $dbi->loadObjectList('CONSTRAINT_NAME');
// Get the columns per key and key information
$query = 'SELECT * FROM `key_column_usage` WHERE `constraint_schema` = ' . $dbi->quote($this->database) . ' AND `table_name` = ' . $dbi->quote($table_name) . ' AND `referenced_table_name` IS NOT NULL';
$dbi->setQuery($query);
$allFKColumns = $dbi->loadObjectList();
$rawConstraints = array();
if (!empty($allFKColumns)) {
foreach ($allFKColumns as $oColumn) {
if (!array_key_exists($oColumn->CONSTRAINT_NAME, $rawConstraints)) {
$entry = array('name' => $oColumn->CONSTRAINT_NAME, 'cols' => array(), 'refcols' => array(), 'reftable' => '', 'update' => '', 'delete' => '');
if ($useabstract) {
$entry['name'] = $this->getAbstract($entry['name']);
}
if (array_key_exists($oColumn->CONSTRAINT_NAME, $foreignKeyInfo)) {
$entry['update'] = $foreignKeyInfo[$oColumn->CONSTRAINT_NAME]->UPDATE_RULE;
$entry['delete'] = $foreignKeyInfo[$oColumn->CONSTRAINT_NAME]->DELETE_RULE;
$reftable = $foreignKeyInfo[$oColumn->CONSTRAINT_NAME]->REFERENCED_TABLE_NAME;
// Add a reference hit
$this->dependencies[$reftable][] = $table_name;
// Add the dependency to this table's metadata
$dependencies[] = $reftable;
if ($useabstract) {
$reftable = $this->getAbstract($reftable);
}
$entry['reftable'] = $reftable;
}
$rawConstraints[$oColumn->CONSTRAINT_NAME] = $entry;
}
$rawConstraints[$oColumn->CONSTRAINT_NAME]['cols'][] = '`' . $oColumn->COLUMN_NAME . '`';
$rawConstraints[$oColumn->CONSTRAINT_NAME]['refcols'][] = '`' . $oColumn->REFERENCED_COLUMN_NAME . '`';
}
}
// Piece together the constraints' SQL statements
if (!empty($rawConstraints)) {
foreach ($rawConstraints as $keydef) {
$line = ' CONSTRAINT ';
if ($keydef['name']) {
$line .= "`{$keydef['name']}` ";
}
$line .= 'FOREIGN KEY (';
$line .= implode(',', $keydef['cols']);
$line .= ') REFERENCES `' . $keydef['reftable'] . '` (';
$line .= implode(',', $keydef['refcols']);
$line .= ')';
if ($keydef['delete']) {
$line .= ' ON DELETE ' . $keydef['delete'];
}
if ($keydef['update']) {
$line .= ' ON UPDATE ' . $keydef['update'];
}
$constraints_sql[] = $line;
}
}
// =====================================================================
// ========== CONSTRUCT THE TABLE CREATE STATEMENT
// =====================================================================
// Create the SQL output
if ($useabstract) {
$table_sql = "CREATE TABLE `{$table_abstract}` (";
} else {
$table_sql = "CREATE TABLE `{$table_name}` (";
}
$table_sql .= implode(',', $columns_sql);
if (count($keys_sql)) {
$table_sql .= ',' . implode(',', $keys_sql);
}
if (count($constraints_sql)) {
$table_sql .= ',' . implode(',', $constraints_sql);
}
$table_sql .= ")";
// Engine and stuff must also be exported here
if ($table_object->ENGINE) {
$table_sql .= ' ENGINE=' . $table_object->ENGINE;
}
if ($table_object->TABLE_COLLATION) {
$table_sql .= ' DEFAULT COLLATE ' . $table_object->TABLE_COLLATION;
}
$table_sql .= ";\n";
return $table_sql;
}
