/**
* Get a Table object for the given name
* TODO table/column properties should not be public
* @param Database $database
* @param String $name
* @throws \Exception
* @return Table
*/
public function buildTable(Database $database, $name)
{
$objectInfo = $this->fetchAll("SELECT object_id FROM sys.objects WHERE type IN ('U ', 'V ') AND name = ?", [$name]);
if (isset($objectInfo[0]['object_id'])) {
$objectId = $objectInfo[0]['object_id'];
$columns = $this->fetchAll(<<<SQL
SELECT
\tcolumns.name,
\ttypes.name AS type,
\tcolumns.max_length AS length,
\tcolumns.is_nullable AS allowNull,
\tdefault_constraints.definition AS [default],
\tcolumns.is_identity AS isAutoIncrement,
\tCOALESCE(indexes.is_primary_key, 0) AS isPrimaryKey
FROM
\tsys.columns
\tINNER JOIN sys.types ON columns.user_type_id = types.user_type_id
\tLEFT JOIN sys.default_constraints
\t\tON columns.default_object_id = default_constraints.object_id
\t\tAND columns.object_id = default_constraints.parent_object_id
\tLEFT JOIN sys.index_columns
\t\tON index_columns.column_id = columns.column_id
\t\tAND index_columns.object_id = columns.object_id
\tLEFT JOIN sys.indexes
\t\tON indexes.index_id = index_columns.index_id
\t\tAND indexes.object_id = columns.object_id
\t\tAND indexes.is_primary_key = 1
WHERE
\tcolumns.object_id = ?
SQL
, [$objectId]);
$constraints = $this->fetchAll(<<<SQL
SELECT
\tforeign_keys.name constraintName,
\tlocalColumns.name AS localColumnName,
\tforeignTables.name AS foreignTableName,
\tforeignColumns.name AS foreignColumnName
FROM
\tsys.foreign_keys
\tINNER JOIN sys.foreign_key_columns ON foreign_keys.object_id = foreign_key_columns.constraint_object_id
\tINNER JOIN sys.objects AS foreignTables ON foreignTables.object_id = foreign_key_columns.referenced_object_id
\tINNER JOIN sys.columns AS localColumns
\t\tON localColumns.column_id = foreign_key_columns.parent_column_id
\t\tAND localColumns.object_id = foreign_key_columns.parent_object_id
\tINNER JOIN sys.columns AS foreignColumns
\t\tON foreignColumns.column_id = foreign_key_columns.referenced_column_id
\t\tAND foreignColumns.object_id = foreign_key_columns.referenced_object_id
WHERE
\tforeign_keys.parent_object_id = ?
ORDER BY
\tforeign_keys.parent_object_id,
\tforeign_keys.object_id
SQL
, [$objectId], null, 'constraintName');
$tableClass = $database->getClassMapper()->getClassForTable($name);
/** @var Table $table */
$table = new $tableClass($name, $database);
foreach ($columns as $ordinal => $columnInfo) {
$column = new Column($table);
$default = null;
if ($columnInfo['default'] !== null) {
// we need to parse the SQL default value
$defaultExpression = $this->getSQLExpressionForType($columnInfo['default'], $columnInfo['type'], '[default]');
$defaultResult = $this->fetchAll('SELECT ' . $defaultExpression);
$default = current(current($defaultResult));
}
$column->setName($columnInfo['name'])->setOrdinal($ordinal)->setType($columnInfo['type'])->setDefault($default)->setAllowNull((bool) $columnInfo['allowNull'])->setPrimaryKey((bool) $columnInfo['isPrimaryKey'])->setAutoIncrement((bool) $columnInfo['isAutoIncrement']);
if ($columnInfo['isPrimaryKey']) {
$table->primaryKeys[] = $columnInfo['name'];
}
$length = null;
switch ($columnInfo['type']) {
case 'text':
$length = 2147483647;
break;
case 'ntext':
$length = 1073741823;
break;
case 'varchar':
case 'nvarchar':
case 'char':
case 'nchar':
$length = $columnInfo['length'];
break;
}
$column->setLength($length);
$table->columns[$column->getName()] = $column;
}
// enumerate relationships
$offset = strlen($database->getPrefix());
foreach ($constraints as $constraintName => $mappings) {
$localColumns = [];
$relatedColumns = [];
$mapping = null;
foreach ($mappings as $mapping) {
$localColumns[] = $mapping['localColumnName'];
$relatedColumns[] = $mapping['foreignColumnName'];
if (!isset($table->foreignKeys[$mapping['localColumnName']])) {
// columns can have multiple foreign keys; we can only use one of them
$table->foreignKeys[$mapping['localColumnName']] = $constraintName;
}
}
$foreignTableName = substr($mapping['foreignTableName'], $offset);
if (!isset($table->relatedTables[$foreignTableName])) {
// tables can be related to another table multiple times; we can only use one of them
$table->relatedTables[$foreignTableName] = $constraintName;
}
$table->constraints[$constraintName] = ['localColumns' => $localColumns, 'relatedTable' => $foreignTableName, 'relatedColumns' => $relatedColumns];
}
return $table;
} else {
throw new \Exception('Could not find table ' . $name);
}
}
/**
* Get a Table object for the given name
* TODO table/column properties should not be public
* @param Database $database
* @param String $name
* @throws \Exception
* @return Table
*/
public function buildTable(Database $database, $name)
{
$columns = $this->fetchAll("PRAGMA table_info(`{$database->getPrefix()}{$name}`)");
if ($columns) {
$tableClass = $database->getClassMapper()->getClassForTable($name);
/** @var Table $table */
$table = new $tableClass($name, $database);
foreach ($columns as $ordinal => $columnInfo) {
$column = new Column($table);
$column->setName($columnInfo['name'])->setOrdinal($ordinal)->setType(strtolower($columnInfo['type']))->setDefault($columnInfo['dflt_value'])->setAllowNull($columnInfo['notnull'] === '0')->setPrimaryKey($columnInfo['pk'] === '1')->setAutoIncrement(strpos($columnInfo['type'], 'INT') === 0 && $columnInfo['pk']);
if ($columnInfo['pk']) {
$table->primaryKeys[] = $columnInfo['name'];
}
$table->columns[$column->getName()] = $column;
}
// enumerate relationships
$offset = strlen($database->getPrefix());
$foreignKeys = $this->fetchAll("PRAGMA foreign_key_list(`{$database->getPrefix()}{$name}`)", [], null, 'id');
foreach ($foreignKeys as $mappings) {
$localColumns = [];
$relatedColumns = [];
$constraintName = null;
$mapping = null;
foreach ($mappings as $mapping) {
$localColumns[] = $mapping['from'];
$relatedColumns[] = $mapping['to'];
$constraintName = 'fk_' . $mapping['id'];
if (!isset($table->foreignKeys[$mapping['from']])) {
// columns can have multiple foreign keys; we can only use one of them
$table->foreignKeys[$mapping['from']] = $constraintName;
}
}
$foreignTableName = substr($mapping['table'], $offset);
if (!isset($table->relatedTables[$foreignTableName])) {
// tables can be related to another table multiple times; we can only use one of them
$table->relatedTables[$foreignTableName] = $constraintName;
}
$table->constraints[$constraintName] = ['localColumns' => $localColumns, 'relatedTable' => $foreignTableName, 'relatedColumns' => $relatedColumns];
}
return $table;
} else {
throw new \Exception('Could not parse table definition');
}
}
