/**
* 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)
{
$createTableInfo = $this->fetchAll("SHOW CREATE TABLE `{$database->getPrefix()}{$name}`");
if (!isset($createTableInfo[0]['Create Table'])) {
throw new \Exception('Could not find table ' . $name);
}
$createTableSql = $createTableInfo[0]['Create Table'];
$columns = [];
$primaryKeys = [];
$constraints = [];
$lowerToRealCaseTableNames = [];
$actualTableNames = [];
foreach ($this->getTables($database) as $tableName) {
$actualTableNames[$tableName] = true;
$lowerToRealCaseTableNames[strtolower($tableName)] = $tableName;
}
// parse columns
if (preg_match_all(self::CREATE_TABLE_SQL_COLUMNS_REGEX, $createTableSql, $columns, PREG_SET_ORDER)) {
$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 (isset($columnInfo['default']) && $columnInfo['default'] !== 'NULL') {
if ($columnInfo['default'] === '') {
$default = '';
} else {
if ($columnInfo['default'] !== 'CURRENT_TIMESTAMP' && $columnInfo['default'] !== 'CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP') {
// we need to parse the SQL default value
$defaultResult = $this->fetchAll('SELECT ' . $columnInfo['default']);
$default = current(current($defaultResult));
}
}
}
$column->setName($columnInfo['name'])->setOrdinal($ordinal)->setType(strtolower($columnInfo['type']))->setDefault($default)->setAllowNull(!isset($columnInfo['allowNull']) || $columnInfo['allowNull'] === 'NULL')->setAutoIncrement(!empty($columnInfo['autoIncrement']));
if ($column->getType() == 'set' || $column->getType() == 'enum') {
// we need to parse the SQL options
$optionsResult = $this->fetchAll('SELECT ' . $columnInfo['length']);
$options = [];
$i = 0;
foreach (current($optionsResult) as $option) {
$options[$column->getType() == 'set' ? pow(2, $i) : $i] = $option;
$i++;
}
$column->setOptions($options);
} else {
$column->setLength(isset($columnInfo['length']) && $columnInfo['length'] !== '' ? $columnInfo['length'] : null);
}
$table->columns[$column->getName()] = $column;
}
// parse primary keys
preg_match(self::CREATE_TABLE_SQL_PRIMARY_KEY_REGEX, $createTableSql, $primaryKeys);
$primaryKeys = explode('`,`', trim($primaryKeys[1], '`'));
foreach ($primaryKeys as $primaryKey) {
$table->primaryKeys[] = $primaryKey;
$table->columns[$primaryKey]->setPrimaryKey(true);
}
// parse relationships
preg_match_all(self::CREATE_TABLE_SQL_CONSTRAINT_REGEX, $createTableSql, $constraints, PREG_SET_ORDER);
$offset = strlen($database->getPrefix());
foreach ($constraints as $constraint) {
$localColumns = explode('`, `', trim($constraint['localColumns'], '`'));
$relatedColumns = explode('`, `', trim($constraint['relatedColumns'], '`'));
foreach ($localColumns as $localColumn) {
if (!isset($table->foreignKeys[$localColumn])) {
// columns can have multiple foreign keys; we can only use one of them
$table->foreignKeys[$localColumn] = $constraint['name'];
}
}
$foreignTableName = substr($constraint['relatedTable'], $offset);
// workaround for http://bugs.mysql.com/bug.php?id=6555
// map lower case table names to actual case table names
if (!isset($actualTableNames[$foreignTableName]) && isset($lowerToRealCaseTableNames[$foreignTableName])) {
$foreignTableName = $lowerToRealCaseTableNames[$foreignTableName];
}
if (!isset($table->relatedTables[$foreignTableName])) {
// tables can be related to another table multiple times; we can only use one of them
$table->relatedTables[$foreignTableName] = $constraint['name'];
}
$table->constraints[$constraint['name']] = ['localColumns' => $localColumns, 'relatedTable' => $foreignTableName, 'relatedColumns' => $relatedColumns];
}
return $table;
} else {
throw new \Exception('Could not parse table definition');
}
}
/**
* 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');
}
}