/** * 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; }
/** * 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 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 = 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(); // ===================================================================== // ========== 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; }
/** * 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 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 = AEFactory::getConfiguration(); $notracking = $configuration->get('engine.dump.native.nodependencies', 0); $useabstract = AEUtilScripting::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; }