Schema::getColumnInfo() - Code Metrics - Inspection of "migrate script :: fixed eav fk, made column drop r..." - hfw/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( e266f8...ec8004 )

by y

created 2021-06-15 12:08 UTC

Schema::getColumnInfo() A

↳ Parent: Schema

Complexity

Conditions	2
Paths	2

Size

Total Lines	15
Code Lines	13

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	1

Metric	Value
eloc	13
c	1
b	0
f	1
dl	0
loc	15
rs	9.8333
cc	2
nc	2
nop	1

<?php

namespace Helix\DB;

use ArrayAccess;
use Helix\DB;
use LogicException;

/**
 * Schema control and metadata.
 *
 * The column definition constants are two bytes each, used in bitwise composition.
 * - The high-byte (`<I_CONST>`) is used for the specific index type.
 *      - Descending index priority. For example, `I_PRIMARY` is `0x8000` (highest bit)
 * - The low-byte (`<T_CONST>`) is used for the specific storage type.
 *      - Inverse size complexity. For example, `BOOL` is `0x0080`, and `T_BLOB` is `0x0002`
 *      - The final bit `0x0001` flags nullability.
 *      - Bit `0x0010` is reserved for future use (probably `DateTime`).
 * - The literal values may change in the future, do not hard code them.
 * - The values may expand to use a total of 4 or 8 bytes to accommodate more stuff.
 *
 * @method static static factory(DB $db)
 */
class Schema implements ArrayAccess {

    use FactoryTrait;

    /**
     * `<TABLE_CONST>`: Multi-column primary key.
     */
    const TABLE_PRIMARY = 0;

    /**
     * `<TABLE_CONST>`: Associative foreign keys.
     */
    const TABLE_FOREIGN = 1;

    /**
     * `<TABLE_CONST>`: Groups of columns are unique together.
     */
    const TABLE_UNIQUE = 2;

    /**
     * Higher byte mask (column index type).
     */
    protected const I_MASK = 0xFF00;

    /**
     * `<I_CONST>`: Column is the primary key.
     */
    const I_PRIMARY = 0x8000;

    /**
     * Partial definition for `T_AUTOINCREMENT`, use that in compositions instead of this.
     */
    protected const I_AUTOINCREMENT = 0x4000 | self::I_PRIMARY;

    /**
     * `<I_CONST>`: Column is unique.
     */
    const I_UNIQUE = 0x2000;

    /**
     * Lower-byte mask (column storage type).
     */
    protected const T_MASK = 0x00FF;

    /**
     * `<T_CONST>`: Column is the primary key and auto-increments (8-byte signed integer).
     */
    const T_AUTOINCREMENT = self::I_AUTOINCREMENT | self::T_INT_STRICT;

    /**
     * Flags whether a type is `NOT NULL`
     */
    protected const T_STRICT = 0x0001;

    /**
     * `<T_CONST>`: Boolean analog (numeric).
     */
    const T_BOOL = 0x0080;
    const T_BOOL_STRICT = self::T_BOOL | self::T_STRICT;

    /**
     * `<T_CONST>`: 8-byte signed integer.
     */
    const T_INT = 0x0040;
    const T_INT_STRICT = self::T_INT | self::T_STRICT;

    /**
     * `<T_CONST>`: 8-byte IEEE floating point number.
     */
    const T_FLOAT = 0x0020;
    const T_FLOAT_STRICT = self::T_FLOAT | self::T_STRICT;

    /**
     * `<T_CONST>`: UTF-8 up to 255 bytes.
     */
    const T_STRING = 0x0008;
    const T_STRING_STRICT = self::T_STRING | self::T_STRICT;

    /**
     * `<T_CONST>`: UTF-8 up to 64KiB.
     */
    const T_TEXT = 0x0004;
    const T_TEXT_STRICT = self::T_TEXT | self::T_STRICT;

    /**
     * `<T_CONST>`: Arbitrary binary data up to 4GiB.
     */
    const T_BLOB = 0x0002;
    const T_BLOB_STRICT = self::T_BLOB | self::T_STRICT;

    /**
     * Maps native/annotated types to `T_CONST` values.
     */
    protected const PHP_TYPES = [
        'bool' => self::T_BOOL,
        'boolean' => self::T_BOOL,  // gettype()
        'double' => self::T_FLOAT,  // gettype()
        'float' => self::T_FLOAT,
        'int' => self::T_INT,
        'integer' => self::T_INT,   // gettype()
        'string' => self::T_STRING,
        'String' => self::T_TEXT,   // @var String
        'STRING' => self::T_BLOB    // @var STRING
    ];

    /**
     * Maps native/annotated types to `T_CONST` names.
     * This is used when generating migrations on the command-line.
     */
    const PHP_TYPE_NAMES = [
        'bool' => 'T_BOOL',
        'boolean' => 'T_BOOL',  // gettype()
        'double' => 'T_BLOB',   // gettype()
        'float' => 'T_FLOAT',
        'int' => 'T_INT',
        'integer' => 'T_INT',   // gettype()
        'string' => 'T_STRING',
        'String' => 'T_TEXT',   // @var String
        'STRING' => 'T_BLOB',   // @var STRING
    ];

    /**
     * Maps column types reported by the database into PHP native/annotated types.
     */
    protected const SCHEMA_TYPES = [
        // bool
        'BOOLEAN' => 'bool',
        // int
        'BIGINT' => 'int',  // mysql
        'INTEGER' => 'int', // sqlite (must be this type to allow AUTOINCREMENT)
        // float
        'DOUBLE PRECISION' => 'float',
        // string <= 255
        'VARCHAR(255)' => 'string',
        // string <= 64k
        'TEXT' => 'String',
        // string > 64k
        'BLOB' => 'STRING',     // sqlite
        'LONGBLOB' => 'STRING', // mysql
    ];

    /**
     * Driver-specific schema phrases.
     */
    protected const COLUMN_DEFINITIONS = [
        'mysql' => [
            self::I_AUTOINCREMENT => 'PRIMARY KEY AUTO_INCREMENT',
            self::I_PRIMARY => 'PRIMARY KEY',
            self::I_UNIQUE => 'UNIQUE',
            self::T_BLOB => 'LONGBLOB NULL DEFAULT NULL',
            self::T_BOOL => 'BOOLEAN NULL DEFAULT NULL',
            self::T_FLOAT => 'DOUBLE PRECISION NULL DEFAULT NULL',
            self::T_INT => 'BIGINT NULL DEFAULT NULL',
            self::T_STRING => 'VARCHAR(255) NULL DEFAULT NULL',
            self::T_TEXT => 'TEXT NULL DEFAULT NULL',
            self::T_BLOB_STRICT => 'LONGBLOB NOT NULL DEFAULT ""',
            self::T_BOOL_STRICT => 'BOOLEAN NOT NULL DEFAULT 0',
            self::T_FLOAT_STRICT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
            self::T_INT_STRICT => 'BIGINT NOT NULL DEFAULT 0',
            self::T_STRING_STRICT => 'VARCHAR(255) NOT NULL DEFAULT ""',
            self::T_TEXT_STRICT => 'TEXT NOT NULL DEFAULT ""',
        ],
        'sqlite' => [
            self::I_AUTOINCREMENT => 'PRIMARY KEY AUTOINCREMENT',
            self::I_PRIMARY => 'PRIMARY KEY',
            self::I_UNIQUE => 'UNIQUE',
            self::T_BLOB => 'BLOB DEFAULT NULL',
            self::T_BOOL => 'BOOLEAN DEFAULT NULL',
            self::T_FLOAT => 'DOUBLE PRECISION DEFAULT NULL',
            self::T_INT => 'INTEGER DEFAULT NULL',
            self::T_STRING => 'VARCHAR(255) DEFAULT NULL',
            self::T_TEXT => 'TEXT DEFAULT NULL',
            self::T_BLOB_STRICT => 'BLOB NOT NULL DEFAULT ""',
            self::T_BOOL_STRICT => 'BOOLEAN NOT NULL DEFAULT 0',
            self::T_FLOAT_STRICT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
            self::T_INT_STRICT => 'INTEGER NOT NULL DEFAULT 0',
            self::T_STRING_STRICT => 'VARCHAR(255) NOT NULL DEFAULT ""',
            self::T_TEXT_STRICT => 'TEXT NOT NULL DEFAULT ""',
        ]
    ];

    /**
     * @var int[]
     */
    protected $colDefs;

    /**
     * @var DB
     */
    protected $db;

    /**
     * @var Table[]
     */
    protected $tables = [];

    /**
     * @param DB $db
     */
    public function __construct (DB $db) {
        $this->db = $db;
        $this->colDefs ??= self::COLUMN_DEFINITIONS[$db->getDriver()];
    }

    /**
     * `ALTER TABLE $table ADD COLUMN $column ...`
     *
     * @param string $table
     * @param string $column
     * @param int $type
     * @return $this
     */
    public function addColumn (string $table, string $column, int $type = self::T_STRING) {
        $type = $this->colDefs[$type & self::T_MASK];
        $this->db->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$type}");
        unset($this->tables[$table]);
        return $this;
    }

    /**
     * `CREATE TABLE $table ...`
     *
     * At least one column must be given.
     *
     * `$constraints` is a multidimensional array of table-level constraints.
     * - `TABLE_PRIMARY => [col, col, col]`
     *      - String list of columns composing the primary key.
     *      - Not needed for single-column primary keys. Use `I_PRIMARY` or `T_AUTOINCREMENT` for that.
     * - `TABLE_UNIQUE => [ [col, col, col] , ... ]`
     *      - One or more string lists of columns, each grouping composing a unique key together.
     * - `TABLE_FOREIGN => [ col => <External Column> ]`
     *      - Associative columns that are each foreign keys to a {@link Column} instance.
     *
     * @param string $table
     * @param int[] $columns `[ name => <I_CONST> | <T_CONST> ]`
     * @param array[] $constraints `[ <TABLE_CONST> => spec ]`
     * @return $this
     */
    public function createTable (string $table, array $columns, array $constraints = []) {
        $defs = $this->toColumnDefinitions($columns);

        /** @var string[] $pk */
        if ($pk = $constraints[self::TABLE_PRIMARY] ?? []) {
            $defs[] = $this->toPrimaryKeyConstraint($table, $pk);
        }

        /** @var string[] $unique */
        foreach ($constraints[self::TABLE_UNIQUE] ?? [] as $unique) {
            $defs[] = $this->toUniqueKeyConstraint($table, $unique);
        }

        /** @var string $local */
        /** @var Column $foreign */
        foreach ($constraints[self::TABLE_FOREIGN] ?? [] as $local => $foreign) {
            $defs[] = $this->toForeignKeyConstraint($table, $local, $columns[$local], $foreign);
        }

        $sql = sprintf(
            "CREATE TABLE %s (%s)",
            $table,
            implode(', ', $defs)
        );

        $this->db->exec($sql);
        return $this;
    }

    /**
     * `ALTER TABLE $table DROP COLUMN $column`
     *
     * @param string $table
     * @param string $column
     * @return $this
     */
    public function dropColumn (string $table, string $column) {
        $this->db->exec("ALTER TABLE {$table} DROP COLUMN {$column}");
        unset($this->tables[$table]);
        return $this;
    }

    /**
     * `DROP TABLE IF EXISTS $table`
     *
     * @param string $table
     */
    public function dropTable (string $table): void {
        $this->db->exec("DROP TABLE IF EXISTS {$table}");
        unset($this->tables[$table]);
    }

    /**
     * Returns column metadata in an associative array.
     *
     * Elements are:
     * - `name`
     * - `type`: PHP native/annotated type (as a string)
     * - `nullable`: boolean
     *
     * @param string $table
     * @param string $column
     * @return array[] Keyed by name.
     */
    public function getColumnInfo (string $table): array {
        if ($this->db->isSQLite()) {
            $info = $this->db->query("PRAGMA table_info({$table})")->fetchAll();
            return array_combine(array_column($info, 'name'), array_map(fn(array $each) => [
                'name' => $each['name'],
                'type' => static::SCHEMA_TYPES[$each['type']] ?? 'string',
                'nullable' => !$each['notnull'],
            ], $info));
        }
        $info = $this->db->query("SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = \"{$table}\" ORDER BY ordinal_position")->fetchAll();
        return array_combine(array_column($info, 'column_name'), array_map(fn(array $each) => [
            'name' => $each['column_name'],
            'type' => static::SCHEMA_TYPES[$each['data_type']] ?? 'string',
            'nullable' => $each['is_nullable'] === 'YES',
        ], $info));
    }

    /**
     * @return DB
     */
    public function getDb () {
        return $this->db;
    }

    /**
     * @param string $name
     * @return null|Table
     */
    public function getTable (string $name) {
        if (!isset($this->tables[$name])) {
            if ($this->db->isSQLite()) {
                $info = $this->db->query("PRAGMA table_info({$name})")->fetchAll();
                $cols = array_column($info, 'name');
            }
            else {
                $cols = $this->db->query("SELECT column_name FROM information_schema.tables WHERE table_name = \"{$name}\"")->fetchAll(DB::FETCH_COLUMN);
            }
            if (!$cols) {

                return null;
            }
            $this->tables[$name] = Table::factory($this->db, $name, $cols);
        }
        return $this->tables[$name];
    }

    /**
     * Whether a table exists.
     *
     * @param string $table
     * @return bool
     */
    final public function offsetExists ($table): bool {
        return (bool)$this->offsetGet($table);
    }

    /**
     * Returns a table by name.
     *
     * @param string $table
     * @return null|Table
     */
    public function offsetGet ($table) {
        return $this->getTable($table);
    }

    /**
     * @param $offset
     * @param $value
     * @throws LogicException
     */
    final public function offsetSet ($offset, $value) {
        throw new LogicException('The schema cannot be altered this way.');
    }

    /**
     * @param $offset
     * @throws LogicException
     */
    final public function offsetUnset ($offset) {
        throw new LogicException('The schema cannot be altered this way.');
    }

    /**
     * `ALTER TABLE $table RENAME COLUMN $oldName TO $newName`
     *
     * @param string $table
     * @param string $oldName
     * @param string $newName
     * @return $this
     */
    public function renameColumn (string $table, string $oldName, string $newName) {
        $this->db->exec("ALTER TABLE {$table} RENAME COLUMN {$oldName} TO {$newName}");
        unset($this->tables[$table]);
        return $this;
    }

    /**
     * `ALTER TABLE $oldName RENAME TO $newName`
     *
     * @param string $oldName
     * @param string $newName
     * @return $this
     */
    public function renameTable (string $oldName, string $newName) {
        $this->db->exec("ALTER TABLE {$oldName} RENAME TO {$newName}");
        unset($this->tables[$oldName]);
        return $this;
    }

    /**
     * Sorts according to index priority, storage size/complexity, and name.
     *
     * @param int[] $types
     * @return int[]
     */
    protected function sortColumns (array $types): array {
        uksort($types, function(string $a, string $b) use ($types) {
            // descending index priority, increasing size, name
            return $types[$b] <=> $types[$a] ?: $a <=> $b;
        });
        return $types;
    }

    /**
     * @param int[] $columns `[ name => <I_CONST> | <T_CONST> ]`
     * @return string[]
     */
    protected function toColumnDefinitions (array $columns): array {
        assert(count($columns) > 0);
        $columns = $this->sortColumns($columns);
        $defs = [];

        /**
         * @var string $name
         * @var int $type
         */
        foreach ($columns as $name => $type) {
            $defs[$name] = sprintf("%s %s", $name, $this->colDefs[$type & self::T_MASK]);
            if ($indexSql = $type & self::I_MASK) {
                $defs[$name] .= " {$this->colDefs[$indexSql]}";
            }
        }

        return $defs;
    }

    /**
     * @param string $table
     * @param string $local
     * @param int $type
     * @param Column $foreign
     * @return string
     */
    protected function toForeignKeyConstraint (string $table, string $local, int $type, Column $foreign): string {
        return sprintf(
            'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s) ON UPDATE CASCADE ON DELETE %s',
            $this->toForeignKeyConstraint_name($table, $local),
            $local,
            $foreign->getQualifier(),
            $foreign->getName(),
            $type | self::T_STRICT ? 'CASCADE' : 'SET NULL'
        );
    }

    /**
     * `FK_TABLE__COLUMN__COLUMN__COLUMN`
     *
     * @param string $table
     * @param string $column
     * @return string
     */
    protected function toForeignKeyConstraint_name (string $table, string $column): string {
        return 'FK_' . $table . '__' . $column;
    }

    /**
     * @param string $table
     * @param string[] $columns
     * @return string
     */
    protected function toPrimaryKeyConstraint (string $table, array $columns): string {
        return sprintf(
            'CONSTRAINT %s PRIMARY KEY (%s)',
            $this->toPrimaryKeyConstraint_name($table, $columns),
            implode(',', $columns)
        );
    }

    /**
     * `PK_TABLE__COLUMN__COLUMN__COLUMN`
     *
     * @param string $table
     * @param string[] $columns
     * @return string
     */
    protected function toPrimaryKeyConstraint_name (string $table, array $columns): string {
        sort($columns, SORT_STRING);
        return 'PK_' . $table . '__' . implode('__', $columns);
    }

    /**
     * @param string $table
     * @param string[] $columns
     * @return string
     */
    protected function toUniqueKeyConstraint (string $table, array $columns): string {
        return sprintf(
            'CONSTRAINT %s UNIQUE (%s)',
            $this->toUniqueKeyConstraint_name($table, $columns),
            implode(',', $columns)
        );
    }

    /**
     * `UQ_TABLE__COLUMN__COLUMN__COLUMN`
     *
     * @param string $table
     * @param string[] $columns
     * @return string
     */
    protected function toUniqueKeyConstraint_name (string $table, array $columns): string {
        sort($columns, SORT_STRING);
        return 'UQ_' . $table . '__' . implode('__', $columns);
    }
}

1			<?php
2
3			namespace Helix\DB;
4
5			use ArrayAccess;
6			use Helix\DB;
7			use LogicException;
8
9			/**
10			* Schema control and metadata.
11			*
12			* The column definition constants are two bytes each, used in bitwise composition.
13			* - The high-byte (`<I_CONST>`) is used for the specific index type.
14			* - Descending index priority. For example, `I_PRIMARY` is `0x8000` (highest bit)
15			* - The low-byte (`<T_CONST>`) is used for the specific storage type.
16			* - Inverse size complexity. For example, `BOOL` is `0x0080`, and `T_BLOB` is `0x0002`
17			* - The final bit `0x0001` flags nullability.
18			* - Bit `0x0010` is reserved for future use (probably `DateTime`).
19			* - The literal values may change in the future, do not hard code them.
20			* - The values may expand to use a total of 4 or 8 bytes to accommodate more stuff.
21			*
22			* @method static static factory(DB $db)
23			*/
24			class Schema implements ArrayAccess {
25
26			use FactoryTrait;
27
28			/**
29			* `<TABLE_CONST>`: Multi-column primary key.
30			*/
31			const TABLE_PRIMARY = 0;
32
33			/**
34			* `<TABLE_CONST>`: Associative foreign keys.
35			*/
36			const TABLE_FOREIGN = 1;
37
38			/**
39			* `<TABLE_CONST>`: Groups of columns are unique together.
40			*/
41			const TABLE_UNIQUE = 2;
42
43			/**
44			* Higher byte mask (column index type).
45			*/
46			protected const I_MASK = 0xFF00;
47
48			/**
49			* `<I_CONST>`: Column is the primary key.
50			*/
51			const I_PRIMARY = 0x8000;
52
53			/**
54			* Partial definition for `T_AUTOINCREMENT`, use that in compositions instead of this.
55			*/
56			protected const I_AUTOINCREMENT = 0x4000 \| self::I_PRIMARY;
57
58			/**
59			* `<I_CONST>`: Column is unique.
60			*/
61			const I_UNIQUE = 0x2000;
62
63			/**
64			* Lower-byte mask (column storage type).
65			*/
66			protected const T_MASK = 0x00FF;
67
68			/**
69			* `<T_CONST>`: Column is the primary key and auto-increments (8-byte signed integer).
70			*/
71			const T_AUTOINCREMENT = self::I_AUTOINCREMENT \| self::T_INT_STRICT;
72
73			/**
74			* Flags whether a type is `NOT NULL`
75			*/
76			protected const T_STRICT = 0x0001;
77
78			/**
79			* `<T_CONST>`: Boolean analog (numeric).
80			*/
81			const T_BOOL = 0x0080;
82			const T_BOOL_STRICT = self::T_BOOL \| self::T_STRICT;
83
84			/**
85			* `<T_CONST>`: 8-byte signed integer.
86			*/
87			const T_INT = 0x0040;
88			const T_INT_STRICT = self::T_INT \| self::T_STRICT;
89
90			/**
91			* `<T_CONST>`: 8-byte IEEE floating point number.
92			*/
93			const T_FLOAT = 0x0020;
94			const T_FLOAT_STRICT = self::T_FLOAT \| self::T_STRICT;
95
96			/**
97			* `<T_CONST>`: UTF-8 up to 255 bytes.
98			*/
99			const T_STRING = 0x0008;
100			const T_STRING_STRICT = self::T_STRING \| self::T_STRICT;
101
102			/**
103			* `<T_CONST>`: UTF-8 up to 64KiB.
104			*/
105			const T_TEXT = 0x0004;
106			const T_TEXT_STRICT = self::T_TEXT \| self::T_STRICT;
107
108			/**
109			* `<T_CONST>`: Arbitrary binary data up to 4GiB.
110			*/
111			const T_BLOB = 0x0002;
112			const T_BLOB_STRICT = self::T_BLOB \| self::T_STRICT;
113
114			/**
115			* Maps native/annotated types to `T_CONST` values.
116			*/
117			protected const PHP_TYPES = [
118			'bool' => self::T_BOOL,
119			'boolean' => self::T_BOOL, // gettype()
120			'double' => self::T_FLOAT, // gettype()
121			'float' => self::T_FLOAT,
122			'int' => self::T_INT,
123			'integer' => self::T_INT, // gettype()
124			'string' => self::T_STRING,
125			'String' => self::T_TEXT, // @var String
126			'STRING' => self::T_BLOB // @var STRING
127			];
128
129			/**
130			* Maps native/annotated types to `T_CONST` names.
131			* This is used when generating migrations on the command-line.
132			*/
133			const PHP_TYPE_NAMES = [
134			'bool' => 'T_BOOL',
135			'boolean' => 'T_BOOL', // gettype()
136			'double' => 'T_BLOB', // gettype()
137			'float' => 'T_FLOAT',
138			'int' => 'T_INT',
139			'integer' => 'T_INT', // gettype()
140			'string' => 'T_STRING',
141			'String' => 'T_TEXT', // @var String
142			'STRING' => 'T_BLOB', // @var STRING
143			];
144
145			/**
146			* Maps column types reported by the database into PHP native/annotated types.
147			*/
148			protected const SCHEMA_TYPES = [
149			// bool
150			'BOOLEAN' => 'bool',
151			// int
152			'BIGINT' => 'int', // mysql
153			'INTEGER' => 'int', // sqlite (must be this type to allow AUTOINCREMENT)
154			// float
155			'DOUBLE PRECISION' => 'float',
156			// string <= 255
157			'VARCHAR(255)' => 'string',
158			// string <= 64k
159			'TEXT' => 'String',
160			// string > 64k
161			'BLOB' => 'STRING', // sqlite
162			'LONGBLOB' => 'STRING', // mysql
163			];
164
165			/**
166			* Driver-specific schema phrases.
167			*/
168			protected const COLUMN_DEFINITIONS = [
169			'mysql' => [
170			self::I_AUTOINCREMENT => 'PRIMARY KEY AUTO_INCREMENT',
171			self::I_PRIMARY => 'PRIMARY KEY',
172			self::I_UNIQUE => 'UNIQUE',
173			self::T_BLOB => 'LONGBLOB NULL DEFAULT NULL',
174			self::T_BOOL => 'BOOLEAN NULL DEFAULT NULL',
175			self::T_FLOAT => 'DOUBLE PRECISION NULL DEFAULT NULL',
176			self::T_INT => 'BIGINT NULL DEFAULT NULL',
177			self::T_STRING => 'VARCHAR(255) NULL DEFAULT NULL',
178			self::T_TEXT => 'TEXT NULL DEFAULT NULL',
179			self::T_BLOB_STRICT => 'LONGBLOB NOT NULL DEFAULT ""',
180			self::T_BOOL_STRICT => 'BOOLEAN NOT NULL DEFAULT 0',
181			self::T_FLOAT_STRICT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
182			self::T_INT_STRICT => 'BIGINT NOT NULL DEFAULT 0',
183			self::T_STRING_STRICT => 'VARCHAR(255) NOT NULL DEFAULT ""',
184			self::T_TEXT_STRICT => 'TEXT NOT NULL DEFAULT ""',
185			],
186			'sqlite' => [
187			self::I_AUTOINCREMENT => 'PRIMARY KEY AUTOINCREMENT',
188			self::I_PRIMARY => 'PRIMARY KEY',
189			self::I_UNIQUE => 'UNIQUE',
190			self::T_BLOB => 'BLOB DEFAULT NULL',
191			self::T_BOOL => 'BOOLEAN DEFAULT NULL',
192			self::T_FLOAT => 'DOUBLE PRECISION DEFAULT NULL',
193			self::T_INT => 'INTEGER DEFAULT NULL',
194			self::T_STRING => 'VARCHAR(255) DEFAULT NULL',
195			self::T_TEXT => 'TEXT DEFAULT NULL',
196			self::T_BLOB_STRICT => 'BLOB NOT NULL DEFAULT ""',
197			self::T_BOOL_STRICT => 'BOOLEAN NOT NULL DEFAULT 0',
198			self::T_FLOAT_STRICT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
199			self::T_INT_STRICT => 'INTEGER NOT NULL DEFAULT 0',
200			self::T_STRING_STRICT => 'VARCHAR(255) NOT NULL DEFAULT ""',
201			self::T_TEXT_STRICT => 'TEXT NOT NULL DEFAULT ""',
202			]
203			];
204
205			/**
206			* @var int[]
207			*/
208			protected $colDefs;
209
210			/**
211			* @var DB
212			*/
213			protected $db;
214
215			/**
216			* @var Table[]
217			*/
218			protected $tables = [];
219
220			/**
221			* @param DB $db
222			*/
223			public function __construct (DB $db) {
224			$this->db = $db;
225			$this->colDefs ??= self::COLUMN_DEFINITIONS[$db->getDriver()];
226			}
227
228			/**
229			* `ALTER TABLE $table ADD COLUMN $column ...`
230			*
231			* @param string $table
232			* @param string $column
233			* @param int $type
234			* @return $this
235			*/
236			public function addColumn (string $table, string $column, int $type = self::T_STRING) {
237			$type = $this->colDefs[$type & self::T_MASK];
238			$this->db->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$type}");
239			unset($this->tables[$table]);
240			return $this;
241			}
242
243			/**
244			* `CREATE TABLE $table ...`
245			*
246			* At least one column must be given.
247			*
248			* `$constraints` is a multidimensional array of table-level constraints.
249			* - `TABLE_PRIMARY => [col, col, col]`
250			* - String list of columns composing the primary key.
251			* - Not needed for single-column primary keys. Use `I_PRIMARY` or `T_AUTOINCREMENT` for that.
252			* - `TABLE_UNIQUE => [ [col, col, col] , ... ]`
253			* - One or more string lists of columns, each grouping composing a unique key together.
254			* - `TABLE_FOREIGN => [ col => <External Column> ]`
255			* - Associative columns that are each foreign keys to a {@link Column} instance.
256			*
257			* @param string $table
258			* @param int[] $columns `[ name => <I_CONST> \| <T_CONST> ]`
259			* @param array[] $constraints `[ <TABLE_CONST> => spec ]`
260			* @return $this
261			*/
262			public function createTable (string $table, array $columns, array $constraints = []) {
263			$defs = $this->toColumnDefinitions($columns);
264
265			/** @var string[] $pk */
266			if ($pk = $constraints[self::TABLE_PRIMARY] ?? []) {
267			$defs[] = $this->toPrimaryKeyConstraint($table, $pk);
268			}
269
270			/** @var string[] $unique */
271			foreach ($constraints[self::TABLE_UNIQUE] ?? [] as $unique) {
272			$defs[] = $this->toUniqueKeyConstraint($table, $unique);
273			}
274
275			/** @var string $local */
276			/** @var Column $foreign */
277			foreach ($constraints[self::TABLE_FOREIGN] ?? [] as $local => $foreign) {
278			$defs[] = $this->toForeignKeyConstraint($table, $local, $columns[$local], $foreign);
279			}
280
281			$sql = sprintf(
282			"CREATE TABLE %s (%s)",
283			$table,
284			implode(', ', $defs)
285			);
286
287			$this->db->exec($sql);
288			return $this;
289			}
290
291			/**
292			* `ALTER TABLE $table DROP COLUMN $column`
293			*
294			* @param string $table
295			* @param string $column
296			* @return $this
297			*/
298			public function dropColumn (string $table, string $column) {
299			$this->db->exec("ALTER TABLE {$table} DROP COLUMN {$column}");
300			unset($this->tables[$table]);
301			return $this;
302			}
303
304			/**
305			* `DROP TABLE IF EXISTS $table`
306			*
307			* @param string $table
308			*/
309			public function dropTable (string $table): void {
310			$this->db->exec("DROP TABLE IF EXISTS {$table}");
311			unset($this->tables[$table]);
312			}
313
314			/**
315			* Returns column metadata in an associative array.
316			*
317			* Elements are:
318			* - `name`
319			* - `type`: PHP native/annotated type (as a string)
320			* - `nullable`: boolean
321			*
322			* @param string $table
323			* @param string $column
324			* @return array[] Keyed by name.
325			*/
326			public function getColumnInfo (string $table): array {
327			if ($this->db->isSQLite()) {
328			$info = $this->db->query("PRAGMA table_info({$table})")->fetchAll();
329			return array_combine(array_column($info, 'name'), array_map(fn(array $each) => [
330			'name' => $each['name'],
331			'type' => static::SCHEMA_TYPES[$each['type']] ?? 'string',
332			'nullable' => !$each['notnull'],
333			], $info));
334			}
335			$info = $this->db->query("SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = \"{$table}\" ORDER BY ordinal_position")->fetchAll();
336			return array_combine(array_column($info, 'column_name'), array_map(fn(array $each) => [
337			'name' => $each['column_name'],
338			'type' => static::SCHEMA_TYPES[$each['data_type']] ?? 'string',
339			'nullable' => $each['is_nullable'] === 'YES',
340			], $info));
341			}
342
343			/**
344			* @return DB
345			*/
346			public function getDb () {
347			return $this->db;
348			}
349
350			/**
351			* @param string $name
352			* @return null\|Table
353			*/
354			public function getTable (string $name) {
355			if (!isset($this->tables[$name])) {
356			if ($this->db->isSQLite()) {
357			$info = $this->db->query("PRAGMA table_info({$name})")->fetchAll();
358			$cols = array_column($info, 'name');
359			}
360			else {
361			$cols = $this->db->query("SELECT column_name FROM information_schema.tables WHERE table_name = \"{$name}\"")->fetchAll(DB::FETCH_COLUMN);
362			}
363			if (!$cols) {
			0 ignored issues – show Bug Best Practice introduced 2020-11-05 23:00 UTC by Report Bug Copy Issue Report The expression `$cols` of type `array` is implicitly converted to a boolean; are you sure this is intended? If so, consider using `empty($expr)` instead to make it clear that you intend to check for an array without elements. This check marks implicit conversions of arrays to boolean values in a comparison. While in PHP an empty array is considered to be equal (but not identical) to false, this is not always apparent. Consider making the comparison explicit by using `empty(..)` or `! empty(...)` instead. Loading history...
364			return null;
365			}
366			$this->tables[$name] = Table::factory($this->db, $name, $cols);
367			}
368			return $this->tables[$name];
369			}
370
371			/**
372			* Whether a table exists.
373			*
374			* @param string $table
375			* @return bool
376			*/
377			final public function offsetExists ($table): bool {
378			return (bool)$this->offsetGet($table);
379			}
380
381			/**
382			* Returns a table by name.
383			*
384			* @param string $table
385			* @return null\|Table
386			*/
387			public function offsetGet ($table) {
388			return $this->getTable($table);
389			}
390
391			/**
392			* @param $offset
393			* @param $value
394			* @throws LogicException
395			*/
396			final public function offsetSet ($offset, $value) {
397			throw new LogicException('The schema cannot be altered this way.');
398			}
399
400			/**
401			* @param $offset
402			* @throws LogicException
403			*/
404			final public function offsetUnset ($offset) {
405			throw new LogicException('The schema cannot be altered this way.');
406			}
407
408			/**
409			* `ALTER TABLE $table RENAME COLUMN $oldName TO $newName`
410			*
411			* @param string $table
412			* @param string $oldName
413			* @param string $newName
414			* @return $this
415			*/
416			public function renameColumn (string $table, string $oldName, string $newName) {
417			$this->db->exec("ALTER TABLE {$table} RENAME COLUMN {$oldName} TO {$newName}");
418			unset($this->tables[$table]);
419			return $this;
420			}
421
422			/**
423			* `ALTER TABLE $oldName RENAME TO $newName`
424			*
425			* @param string $oldName
426			* @param string $newName
427			* @return $this
428			*/
429			public function renameTable (string $oldName, string $newName) {
430			$this->db->exec("ALTER TABLE {$oldName} RENAME TO {$newName}");
431			unset($this->tables[$oldName]);
432			return $this;
433			}
434
435			/**
436			* Sorts according to index priority, storage size/complexity, and name.
437			*
438			* @param int[] $types
439			* @return int[]
440			*/
441			protected function sortColumns (array $types): array {
442			uksort($types, function(string $a, string $b) use ($types) {
443			// descending index priority, increasing size, name
444			return $types[$b] <=> $types[$a] ?: $a <=> $b;
445			});
446			return $types;
447			}
448
449			/**
450			* @param int[] $columns `[ name => <I_CONST> \| <T_CONST> ]`
451			* @return string[]
452			*/
453			protected function toColumnDefinitions (array $columns): array {
454			assert(count($columns) > 0);
455			$columns = $this->sortColumns($columns);
456			$defs = [];
457
458			/**
459			* @var string $name
460			* @var int $type
461			*/
462			foreach ($columns as $name => $type) {
463			$defs[$name] = sprintf("%s %s", $name, $this->colDefs[$type & self::T_MASK]);
464			if ($indexSql = $type & self::I_MASK) {
465			$defs[$name] .= " {$this->colDefs[$indexSql]}";
466			}
467			}
468
469			return $defs;
470			}
471
472			/**
473			* @param string $table
474			* @param string $local
475			* @param int $type
476			* @param Column $foreign
477			* @return string
478			*/
479			protected function toForeignKeyConstraint (string $table, string $local, int $type, Column $foreign): string {
480			return sprintf(
481			'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s) ON UPDATE CASCADE ON DELETE %s',
482			$this->toForeignKeyConstraint_name($table, $local),
483			$local,
484			$foreign->getQualifier(),
485			$foreign->getName(),
486			$type \| self::T_STRICT ? 'CASCADE' : 'SET NULL'
487			);
488			}
489
490			/**
491			* `FK_TABLE__COLUMN__COLUMN__COLUMN`
492			*
493			* @param string $table
494			* @param string $column
495			* @return string
496			*/
497			protected function toForeignKeyConstraint_name (string $table, string $column): string {
498			return 'FK_' . $table . '__' . $column;
499			}
500
501			/**
502			* @param string $table
503			* @param string[] $columns
504			* @return string
505			*/
506			protected function toPrimaryKeyConstraint (string $table, array $columns): string {
507			return sprintf(
508			'CONSTRAINT %s PRIMARY KEY (%s)',
509			$this->toPrimaryKeyConstraint_name($table, $columns),
510			implode(',', $columns)
511			);
512			}
513
514			/**
515			* `PK_TABLE__COLUMN__COLUMN__COLUMN`
516			*
517			* @param string $table
518			* @param string[] $columns
519			* @return string
520			*/
521			protected function toPrimaryKeyConstraint_name (string $table, array $columns): string {
522			sort($columns, SORT_STRING);
523			return 'PK_' . $table . '__' . implode('__', $columns);
524			}
525
526			/**
527			* @param string $table
528			* @param string[] $columns
529			* @return string
530			*/
531			protected function toUniqueKeyConstraint (string $table, array $columns): string {
532			return sprintf(
533			'CONSTRAINT %s UNIQUE (%s)',
534			$this->toUniqueKeyConstraint_name($table, $columns),
535			implode(',', $columns)
536			);
537			}
538
539			/**
540			* `UQ_TABLE__COLUMN__COLUMN__COLUMN`
541			*
542			* @param string $table
543			* @param string[] $columns
544			* @return string
545			*/
546			protected function toUniqueKeyConstraint_name (string $table, array $columns): string {
547			sort($columns, SORT_STRING);
548			return 'UQ_' . $table . '__' . implode('__', $columns);
549			}
550			}

hfw / db

Push — master ( e266f8...ec8004 )

Schema::getColumnInfo() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like