Ejemplo n.º 1
0
 /**
  * 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');
     }
 }
Ejemplo n.º 2
0
    /**
     * 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);
        }
    }
Ejemplo n.º 3
0
 /**
  * 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');
     }
 }