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