/** * 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; }