/**
* Gets the CREATE TABLE command of a given table
*
* @param AEAbstractDriver $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 = AEFactory::getConfiguration();
$notracking = $configuration->get('engine.dump.native.nodependencies', 0);
$useabstract = AEUtilScripting::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;
}
