Schema::getPrimaryKeyConstraintName() - Code Metrics - Inspection of "Schema :: createTable() -- simplified primary/fore..." - hfw/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 97aa2a...b1b817 )

by y

created 2021-07-15 17:28 UTC

Schema::getPrimaryKeyConstraintName() A

↳ Parent: Schema

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	2
c	0
b	0
f	0
nc	1
nop	2
dl	0
loc	4
rs	10

<?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 primary index type.
 * - The low-byte (`<T_CONST>`) is used for the specific storage type.
 *      - The final bit `0x01` flags `NOT NULL`
 * - 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.
 *
 * Definition constants are never returned by this class' methods. The methods can only receive them.
 *
 * @method static static factory(DB $db)
 */
class Schema implements ArrayAccess
{

    use FactoryTrait;

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

    /**
     * Partial definition for `T_AUTOINCREMENT`, use that instead.
     */
    protected const I_AUTOINCREMENT = self::I_PRIMARY | 0x0100; // 0xff00

    /**
     * `<I_CONST>`: One or more columns compose the primary key.
     */
    const I_PRIMARY = 0xfe00;

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

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

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

    /**
     * `<T_CONST>`: Boolean analog (numeric).
     */
    const T_BOOL = 0xff;
    const T_BOOL_NULL = 0xfe;

    /**
     * `<T_CONST>`: 8-byte signed integer.
     */
    const T_INT = 0xfd;
    const T_INT_NULL = 0xfc;

    /**
     * `<T_CONST>`: 8-byte IEEE floating point number.
     */
    const T_FLOAT = 0xfb;
    const T_FLOAT_NULL = 0xfa;

    /**
     * `<T_CONST>`: Native `DATETIME` type, stored as `YYYY-MM-DD hh:mm:ss` UTC.
     */
    const T_DATETIME = 0xf9;
    const T_DATETIME_NULL = 0xf8;
    const DATETIME_FORMAT = 'Y-m-d H:i:s';

    /**
     * `<T_CONST>`: UTF-8 up to 255 bytes.
     */
    const T_STRING = 0xf7;
    const T_STRING_NULL = 0xf6;

    /**
     * `<T_CONST>`: UTF-8 up to 64KiB.
     */
    const T_TEXT = 0x05;
    const T_TEXT_NULL = 0x04;

    /**
     * `<T_CONST>`: Arbitrary binary data up to 4GiB.
     */
    const T_BLOB = 0x03;
    const T_BLOB_NULL = 0x02;

    /**
     * Maps storage types to `T_CONST` names.
     *
     * Resolved storage types in {@link Record} are keys here.
     *
     * This is also used when generating migrations on the command-line.
     */
    const T_CONST_NAMES = [
        'bool' => 'T_BOOL',
        'DateTime' => 'T_DATETIME',
        'float' => 'T_FLOAT',
        'int' => 'T_INT',
        'string' => 'T_STRING',
        'String' => 'T_TEXT',
        'STRING' => 'T_BLOB',
    ];

    /**
     * Maps column types reported by the database into PHP native/annotated types.
     * This is used by {@link Schema::getColumnInfo()}
     */
    protected const PHP_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',     // @var String
        // string > 64k
        'BLOB' => 'STRING',     // @var STRING (sqlite)
        'LONGBLOB' => 'STRING', // @var STRING (mysql)
        // DateTime
        'DATETIME' => 'DateTime',
    ];

    /**
     * Driver-specific schema phrases.
     */
    protected const COLUMN_DEFINITIONS = [
        'mysql' => [
            self::I_AUTOINCREMENT => 'PRIMARY KEY AUTO_INCREMENT',
            self::I_PRIMARY => 'PRIMARY KEY',
            self::T_BLOB => 'LONGBLOB NOT NULL DEFAULT ""',
            self::T_BOOL => 'BOOLEAN NOT NULL DEFAULT 0',
            self::T_FLOAT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
            self::T_INT => 'BIGINT NOT NULL DEFAULT 0',
            self::T_STRING => 'VARCHAR(255) NOT NULL DEFAULT ""',
            self::T_TEXT => 'TEXT NOT NULL DEFAULT ""',
            self::T_DATETIME => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP',
            self::T_BLOB_NULL => 'LONGBLOB NULL DEFAULT NULL',
            self::T_BOOL_NULL => 'BOOLEAN NULL DEFAULT NULL',
            self::T_FLOAT_NULL => 'DOUBLE PRECISION NULL DEFAULT NULL',
            self::T_INT_NULL => 'BIGINT NULL DEFAULT NULL',
            self::T_STRING_NULL => 'VARCHAR(255) NULL DEFAULT NULL',
            self::T_TEXT_NULL => 'TEXT NULL DEFAULT NULL',
            self::T_DATETIME_NULL => 'DATETIME NULL DEFAULT NULL',
        ],
        'sqlite' => [
            self::I_AUTOINCREMENT => 'PRIMARY KEY AUTOINCREMENT',
            self::I_PRIMARY => 'PRIMARY KEY',
            self::T_BLOB => 'BLOB NOT NULL DEFAULT ""',
            self::T_BOOL => 'BOOLEAN NOT NULL DEFAULT 0',
            self::T_FLOAT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
            self::T_INT => 'INTEGER NOT NULL DEFAULT 0',
            self::T_STRING => 'VARCHAR(255) NOT NULL DEFAULT ""',
            self::T_TEXT => 'TEXT NOT NULL DEFAULT ""',
            self::T_DATETIME => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP',
            self::T_BLOB_NULL => 'BLOB DEFAULT NULL',
            self::T_BOOL_NULL => 'BOOLEAN DEFAULT NULL',
            self::T_FLOAT_NULL => 'DOUBLE PRECISION DEFAULT NULL',
            self::T_INT_NULL => 'INTEGER DEFAULT NULL',
            self::T_STRING_NULL => 'VARCHAR(255) DEFAULT NULL',
            self::T_TEXT_NULL => 'TEXT DEFAULT NULL',
            self::T_DATETIME_NULL => 'DATETIME NULL DEFAULT NULL',
        ]
    ];

    /**
     * @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_NULL)
    {
        $type = $this->colDefs[$type & self::T_MASK];
        $this->db->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$type}");
        unset($this->tables[$table]);
        return $this;
    }

    /**
     * Driver-appropriate constraint creation.
     *
     * @param string $table
     * @param string[] $columns
     * @return $this
     */
    public function addUniqueKeyConstraint(string $table, array $columns)
    {
        $name = $this->getUniqueKeyConstraintName($table, $columns);
        $columns = implode(',', $columns);
        if ($this->db->isSQLite()) {
            $this->db->exec("CREATE UNIQUE INDEX {$name} ON {$table} ({$columns})");
        } else {
            $this->db->exec("ALTER TABLE {$table} ADD CONSTRAINT {$name} UNIQUE ({$columns})");
        }
        return $this;
    }

    /**
     * `CREATE TABLE $table ...`
     *
     * At least one column must be given.
     *
     * @param string $table
     * @param int[] $columns `[ name => <I_CONST> | <T_CONST> ]`
     * @param Column[] $foreign `[ column name => <External Column> ]`
     * @return $this
     */
    public function createTable(string $table, array $columns, array $foreign = [])
    {
        assert(count($columns) > 0);
        $columns = $this->sortColumns($columns);
        $colDefs = [];
        $primaryKey = [];

        // column list
        foreach ($columns as $name => $type) {
            $colDefs[$name] = sprintf("%s %s", $name, $this->colDefs[$type & self::T_MASK]);
            if (self::I_AUTOINCREMENT === $type & self::I_MASK) {
// Returns always int(0).
return 0 === $foo & 4;
return (0 === $foo) & 4;

// More likely intended return: true/false
return 0 === ($foo & 4);
                $colDefs[$name] .= " " . $this->colDefs[self::I_AUTOINCREMENT];
            } elseif ($type & self::I_PRIMARY) {
                $primaryKey[] = $name;
            }
        }

        // non auto-increment primary key
        if ($primaryKey) {
            $colDefs[] = sprintf(
                'CONSTRAINT %s PRIMARY KEY (%s)',
                $this->getPrimaryKeyConstraintName($table, $primaryKey),
                implode(',', $primaryKey)
            );
        }

        // foreign keys
        foreach ($foreign as $local => $external) {
            $colDefs[] = sprintf(
                'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s) ON UPDATE CASCADE ON DELETE %s',
                $this->getForeignKeyConstraintName($table, $local),
                $local,
                $external->getQualifier(),
                $external->getName(),
                $columns[$local] | self::T_STRICT ? 'CASCADE' : 'SET NULL'
            );
        }

        $this->db->exec(sprintf(
            "CREATE TABLE %s (%s)",
            $table,
            implode(', ', $colDefs)
        ));

        return $this;
    }

    /**
     * `ALTER TABLE $table DROP COLUMN $column`
     *
     * SQLite does not support this, so it's skipped.
     * It's beyond the scope of this method (for now) to do table recreation for SQLite.
     *
     * @param string $table
     * @param string $column
     * @return $this
     */
    public function dropColumn(string $table, string $column)
    {
        if (!$this->db->isSQLite()) {
            $this->db->exec("ALTER TABLE {$table} DROP COLUMN {$column}");
        }
        unset($this->tables[$table]);
        return $this;
    }

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

    /**
     * Driver-appropriate constraint deletion.
     *
     * @param string $table
     * @param string[] $columns
     * @return $this
     */
    public function dropUniqueKeyConstraint(string $table, array $columns)
    {
        $name = $this->getUniqueKeyConstraintName($table, $columns);
        if ($this->db->isSQLite()) {
            $this->db->exec("DROP INDEX {$name}");
        } else {
            $this->db->exec("DROP INDEX {$name} ON {$table}");
        }
        return $this;
    }

    /**
     * Returns column metadata in an associative array.
     *
     * Elements are:
     * - `name`
     * - `type`: PHP native/annotated type (as a string)
     * - `nullable`: boolean
     *
     * The returned `type` can be used to get a `T_CONST` name from {@link Schema::T_CONST_NAMES}
     *
     * @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::PHP_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::PHP_TYPES[$each['data_type']] ?? 'string',
            'nullable' => $each['is_nullable'] === 'YES',
        ], $info));
    }

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

    /**
     * `FK_TABLE__COLUMN`
     *
     * @param string $table
     * @param string $column
     * @return string
     */
    final public function getForeignKeyConstraintName(string $table, string $column): string
    {
        return 'FK_' . $table . '__' . $column;
    }

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

    /**
     * @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];
    }

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

    /**
     * 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 type constant, ascending name
            return $types[$b] <=> $types[$a] ?: $a <=> $b;
        });
        return $types;
    }

}


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 primary index type.
14			* - The low-byte (`<T_CONST>`) is used for the specific storage type.
15			* - The final bit `0x01` flags `NOT NULL`
16			* - The literal values may change in the future, do not hard code them.
17			* - The values may expand to use a total of 4 or 8 bytes to accommodate more stuff.
18			*
19			* Definition constants are never returned by this class' methods. The methods can only receive them.
20			*
21			* @method static static factory(DB $db)
22			*/
23			class Schema implements ArrayAccess
24			{
25
26			use FactoryTrait;
27
28			/**
29			* Higher byte mask (column index type).
30			*/
31			protected const I_MASK = 0xff00;
32
33			/**
34			* Partial definition for `T_AUTOINCREMENT`, use that instead.
35			*/
36			protected const I_AUTOINCREMENT = self::I_PRIMARY \| 0x0100; // 0xff00
37
38			/**
39			* `<I_CONST>`: One or more columns compose the primary key.
40			*/
41			const I_PRIMARY = 0xfe00;
42
43			/**
44			* Lower-byte mask (column storage type).
45			*/
46			protected const T_MASK = 0xff;
47
48			/**
49			* `<T_CONST>`: Column is the primary key and auto-increments (8-byte signed integer).
50			*/
51			const T_AUTOINCREMENT = self::I_AUTOINCREMENT \| self::T_INT;
52
53			/**
54			* Flags whether a type is `NOT NULL`
55			*/
56			protected const T_STRICT = 0x01;
57
58			/**
59			* `<T_CONST>`: Boolean analog (numeric).
60			*/
61			const T_BOOL = 0xff;
62			const T_BOOL_NULL = 0xfe;
63
64			/**
65			* `<T_CONST>`: 8-byte signed integer.
66			*/
67			const T_INT = 0xfd;
68			const T_INT_NULL = 0xfc;
69
70			/**
71			* `<T_CONST>`: 8-byte IEEE floating point number.
72			*/
73			const T_FLOAT = 0xfb;
74			const T_FLOAT_NULL = 0xfa;
75
76			/**
77			* `<T_CONST>`: Native `DATETIME` type, stored as `YYYY-MM-DD hh:mm:ss` UTC.
78			*/
79			const T_DATETIME = 0xf9;
80			const T_DATETIME_NULL = 0xf8;
81			const DATETIME_FORMAT = 'Y-m-d H:i:s';
82
83			/**
84			* `<T_CONST>`: UTF-8 up to 255 bytes.
85			*/
86			const T_STRING = 0xf7;
87			const T_STRING_NULL = 0xf6;
88
89			/**
90			* `<T_CONST>`: UTF-8 up to 64KiB.
91			*/
92			const T_TEXT = 0x05;
93			const T_TEXT_NULL = 0x04;
94
95			/**
96			* `<T_CONST>`: Arbitrary binary data up to 4GiB.
97			*/
98			const T_BLOB = 0x03;
99			const T_BLOB_NULL = 0x02;
100
101			/**
102			* Maps storage types to `T_CONST` names.
103			*
104			* Resolved storage types in {@link Record} are keys here.
105			*
106			* This is also used when generating migrations on the command-line.
107			*/
108			const T_CONST_NAMES = [
109			'bool' => 'T_BOOL',
110			'DateTime' => 'T_DATETIME',
111			'float' => 'T_FLOAT',
112			'int' => 'T_INT',
113			'string' => 'T_STRING',
114			'String' => 'T_TEXT',
115			'STRING' => 'T_BLOB',
116			];
117
118			/**
119			* Maps column types reported by the database into PHP native/annotated types.
120			* This is used by {@link Schema::getColumnInfo()}
121			*/
122			protected const PHP_TYPES = [
123			// bool
124			'BOOLEAN' => 'bool',
125			// int
126			'BIGINT' => 'int', // mysql
127			'INTEGER' => 'int', // sqlite (must be this type to allow AUTOINCREMENT)
128			// float
129			'DOUBLE PRECISION' => 'float',
130			// string <= 255
131			'VARCHAR(255)' => 'string',
132			// string <= 64k
133			'TEXT' => 'String', // @var String
134			// string > 64k
135			'BLOB' => 'STRING', // @var STRING (sqlite)
136			'LONGBLOB' => 'STRING', // @var STRING (mysql)
137			// DateTime
138			'DATETIME' => 'DateTime',
139			];
140
141			/**
142			* Driver-specific schema phrases.
143			*/
144			protected const COLUMN_DEFINITIONS = [
145			'mysql' => [
146			self::I_AUTOINCREMENT => 'PRIMARY KEY AUTO_INCREMENT',
147			self::I_PRIMARY => 'PRIMARY KEY',
148			self::T_BLOB => 'LONGBLOB NOT NULL DEFAULT ""',
149			self::T_BOOL => 'BOOLEAN NOT NULL DEFAULT 0',
150			self::T_FLOAT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
151			self::T_INT => 'BIGINT NOT NULL DEFAULT 0',
152			self::T_STRING => 'VARCHAR(255) NOT NULL DEFAULT ""',
153			self::T_TEXT => 'TEXT NOT NULL DEFAULT ""',
154			self::T_DATETIME => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP',
155			self::T_BLOB_NULL => 'LONGBLOB NULL DEFAULT NULL',
156			self::T_BOOL_NULL => 'BOOLEAN NULL DEFAULT NULL',
157			self::T_FLOAT_NULL => 'DOUBLE PRECISION NULL DEFAULT NULL',
158			self::T_INT_NULL => 'BIGINT NULL DEFAULT NULL',
159			self::T_STRING_NULL => 'VARCHAR(255) NULL DEFAULT NULL',
160			self::T_TEXT_NULL => 'TEXT NULL DEFAULT NULL',
161			self::T_DATETIME_NULL => 'DATETIME NULL DEFAULT NULL',
162			],
163			'sqlite' => [
164			self::I_AUTOINCREMENT => 'PRIMARY KEY AUTOINCREMENT',
165			self::I_PRIMARY => 'PRIMARY KEY',
166			self::T_BLOB => 'BLOB NOT NULL DEFAULT ""',
167			self::T_BOOL => 'BOOLEAN NOT NULL DEFAULT 0',
168			self::T_FLOAT => 'DOUBLE PRECISION NOT NULL DEFAULT 0',
169			self::T_INT => 'INTEGER NOT NULL DEFAULT 0',
170			self::T_STRING => 'VARCHAR(255) NOT NULL DEFAULT ""',
171			self::T_TEXT => 'TEXT NOT NULL DEFAULT ""',
172			self::T_DATETIME => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP',
173			self::T_BLOB_NULL => 'BLOB DEFAULT NULL',
174			self::T_BOOL_NULL => 'BOOLEAN DEFAULT NULL',
175			self::T_FLOAT_NULL => 'DOUBLE PRECISION DEFAULT NULL',
176			self::T_INT_NULL => 'INTEGER DEFAULT NULL',
177			self::T_STRING_NULL => 'VARCHAR(255) DEFAULT NULL',
178			self::T_TEXT_NULL => 'TEXT DEFAULT NULL',
179			self::T_DATETIME_NULL => 'DATETIME NULL DEFAULT NULL',
180			]
181			];
182
183			/**
184			* @var int[]
185			*/
186			protected $colDefs;
187
188			/**
189			* @var DB
190			*/
191			protected $db;
192
193			/**
194			* @var Table[]
195			*/
196			protected $tables = [];
197
198			/**
199			* @param DB $db
200			*/
201			public function __construct(DB $db)
202			{
203			$this->db = $db;
204			$this->colDefs ??= self::COLUMN_DEFINITIONS[$db->getDriver()];
205			}
206
207			/**
208			* `ALTER TABLE $table ADD COLUMN $column ...`
209			*
210			* @param string $table
211			* @param string $column
212			* @param int $type
213			* @return $this
214			*/
215			public function addColumn(string $table, string $column, int $type = self::T_STRING_NULL)
216			{
217			$type = $this->colDefs[$type & self::T_MASK];
218			$this->db->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$type}");
219			unset($this->tables[$table]);
220			return $this;
221			}
222
223			/**
224			* Driver-appropriate constraint creation.
225			*
226			* @param string $table
227			* @param string[] $columns
228			* @return $this
229			*/
230			public function addUniqueKeyConstraint(string $table, array $columns)
231			{
232			$name = $this->getUniqueKeyConstraintName($table, $columns);
233			$columns = implode(',', $columns);
234			if ($this->db->isSQLite()) {
235			$this->db->exec("CREATE UNIQUE INDEX {$name} ON {$table} ({$columns})");
236			} else {
237			$this->db->exec("ALTER TABLE {$table} ADD CONSTRAINT {$name} UNIQUE ({$columns})");
238			}
239			return $this;
240			}
241
242			/**
243			* `CREATE TABLE $table ...`
244			*
245			* At least one column must be given.
246			*
247			* @param string $table
248			* @param int[] $columns `[ name => <I_CONST> \| <T_CONST> ]`
249			* @param Column[] $foreign `[ column name => <External Column> ]`
250			* @return $this
251			*/
252			public function createTable(string $table, array $columns, array $foreign = [])
253			{
254			assert(count($columns) > 0);
255			$columns = $this->sortColumns($columns);
256			$colDefs = [];
257			$primaryKey = [];
258
259			// column list
260			foreach ($columns as $name => $type) {
261			$colDefs[$name] = sprintf("%s %s", $name, $this->colDefs[$type & self::T_MASK]);
262			if (self::I_AUTOINCREMENT === $type & self::I_MASK) {
			0 ignored issues – show Comprehensibility introduced 2021-07-15 17:34 UTC by Report Bug Copy Issue Report Consider adding parentheses for clarity. Current Interpretation: `(self::I_AUTOINCREMENT === $type) & self::I_MASK`, Probably Intended Meaning: `self::I_AUTOINCREMENT === ($type & self::I_MASK)` When comparing the result of a bit operation, we suggest to add explicit parenthesis and not to rely on PHP?s built-in operator precedence to ensure the code behaves as intended and to make it more readable. Let?s take a look at these examples: // Returns always int(0). return 0 === $foo & 4; return (0 === $foo) & 4; // More likely intended return: true/false return 0 === ($foo & 4); Loading history...
263			$colDefs[$name] .= " " . $this->colDefs[self::I_AUTOINCREMENT];
264			} elseif ($type & self::I_PRIMARY) {
265			$primaryKey[] = $name;
266			}
267			}
268
269			// non auto-increment primary key
270			if ($primaryKey) {
271			$colDefs[] = sprintf(
272			'CONSTRAINT %s PRIMARY KEY (%s)',
273			$this->getPrimaryKeyConstraintName($table, $primaryKey),
274			implode(',', $primaryKey)
275			);
276			}
277
278			// foreign keys
279			foreach ($foreign as $local => $external) {
280			$colDefs[] = sprintf(
281			'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s) ON UPDATE CASCADE ON DELETE %s',
282			$this->getForeignKeyConstraintName($table, $local),
283			$local,
284			$external->getQualifier(),
285			$external->getName(),
286			$columns[$local] \| self::T_STRICT ? 'CASCADE' : 'SET NULL'
287			);
288			}
289
290			$this->db->exec(sprintf(
291			"CREATE TABLE %s (%s)",
292			$table,
293			implode(', ', $colDefs)
294			));
295
296			return $this;
297			}
298
299			/**
300			* `ALTER TABLE $table DROP COLUMN $column`
301			*
302			* SQLite does not support this, so it's skipped.
303			* It's beyond the scope of this method (for now) to do table recreation for SQLite.
304			*
305			* @param string $table
306			* @param string $column
307			* @return $this
308			*/
309			public function dropColumn(string $table, string $column)
310			{
311			if (!$this->db->isSQLite()) {
312			$this->db->exec("ALTER TABLE {$table} DROP COLUMN {$column}");
313			}
314			unset($this->tables[$table]);
315			return $this;
316			}
317
318			/**
319			* `DROP TABLE IF EXISTS $table`
320			*
321			* @param string $table
322			* @return $this
323			*/
324			public function dropTable(string $table)
325			{
326			$this->db->exec("DROP TABLE IF EXISTS {$table}");
327			unset($this->tables[$table]);
328			return $this;
329			}
330
331			/**
332			* Driver-appropriate constraint deletion.
333			*
334			* @param string $table
335			* @param string[] $columns
336			* @return $this
337			*/
338			public function dropUniqueKeyConstraint(string $table, array $columns)
339			{
340			$name = $this->getUniqueKeyConstraintName($table, $columns);
341			if ($this->db->isSQLite()) {
342			$this->db->exec("DROP INDEX {$name}");
343			} else {
344			$this->db->exec("DROP INDEX {$name} ON {$table}");
345			}
346			return $this;
347			}
348
349			/**
350			* Returns column metadata in an associative array.
351			*
352			* Elements are:
353			* - `name`
354			* - `type`: PHP native/annotated type (as a string)
355			* - `nullable`: boolean
356			*
357			* The returned `type` can be used to get a `T_CONST` name from {@link Schema::T_CONST_NAMES}
358			*
359			* @param string $table
360			* @param string $column
361			* @return array[] Keyed by name.
362			*/
363			public function getColumnInfo(string $table): array
364			{
365			if ($this->db->isSQLite()) {
366			$info = $this->db->query("PRAGMA table_info({$table})")->fetchAll();
367			return array_combine(array_column($info, 'name'), array_map(fn(array $each) => [
368			'name' => $each['name'],
369			'type' => static::PHP_TYPES[$each['type']] ?? 'string',
370			'nullable' => !$each['notnull'],
371			], $info));
372			}
373			$info = $this->db->query("SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = \"{$table}\" ORDER BY ordinal_position")->fetchAll();
374			return array_combine(array_column($info, 'column_name'), array_map(fn(array $each) => [
375			'name' => $each['column_name'],
376			'type' => static::PHP_TYPES[$each['data_type']] ?? 'string',
377			'nullable' => $each['is_nullable'] === 'YES',
378			], $info));
379			}
380
381			/**
382			* @return DB
383			*/
384			public function getDb()
385			{
386			return $this->db;
387			}
388
389			/**
390			* `FK_TABLE__COLUMN`
391			*
392			* @param string $table
393			* @param string $column
394			* @return string
395			*/
396			final public function getForeignKeyConstraintName(string $table, string $column): string
397			{
398			return 'FK_' . $table . '__' . $column;
399			}
400
401			/**
402			* `PK_TABLE__COLUMN__COLUMN__COLUMN`
403			*
404			* @param string $table
405			* @param string[] $columns
406			* @return string
407			*/
408			final public function getPrimaryKeyConstraintName(string $table, array $columns): string
409			{
410			sort($columns, SORT_STRING);
411			return 'PK_' . $table . '__' . implode('__', $columns);
412			}
413
414			/**
415			* @param string $name
416			* @return null\|Table
417			*/
418			public function getTable(string $name)
419			{
420			if (!isset($this->tables[$name])) {
421			if ($this->db->isSQLite()) {
422			$info = $this->db->query("PRAGMA table_info({$name})")->fetchAll();
423			$cols = array_column($info, 'name');
424			} else {
425			$cols = $this->db->query("SELECT column_name FROM information_schema.tables WHERE table_name = \"{$name}\"")->fetchAll(DB::FETCH_COLUMN);
426			}
427			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...
428			return null;
429			}
430			$this->tables[$name] = Table::factory($this->db, $name, $cols);
431			}
432			return $this->tables[$name];
433			}
434
435			/**
436			* `UQ_TABLE__COLUMN__COLUMN__COLUMN`
437			*
438			* @param string $table
439			* @param string[] $columns
440			* @return string
441			*/
442			final public function getUniqueKeyConstraintName(string $table, array $columns): string
443			{
444			sort($columns, SORT_STRING);
445			return 'UQ_' . $table . '__' . implode('__', $columns);
446			}
447
448			/**
449			* Whether a table exists.
450			*
451			* @param string $table
452			* @return bool
453			*/
454			final public function offsetExists($table): bool
455			{
456			return (bool)$this->offsetGet($table);
457			}
458
459			/**
460			* Returns a table by name.
461			*
462			* @param string $table
463			* @return null\|Table
464			*/
465			public function offsetGet($table)
466			{
467			return $this->getTable($table);
468			}
469
470			/**
471			* @param $offset
472			* @param $value
473			* @throws LogicException
474			*/
475			final public function offsetSet($offset, $value)
476			{
477			throw new LogicException('The schema cannot be altered this way.');
478			}
479
480			/**
481			* @param $offset
482			* @throws LogicException
483			*/
484			final public function offsetUnset($offset)
485			{
486			throw new LogicException('The schema cannot be altered this way.');
487			}
488
489			/**
490			* `ALTER TABLE $table RENAME COLUMN $oldName TO $newName`
491			*
492			* @param string $table
493			* @param string $oldName
494			* @param string $newName
495			* @return $this
496			*/
497			public function renameColumn(string $table, string $oldName, string $newName)
498			{
499			$this->db->exec("ALTER TABLE {$table} RENAME COLUMN {$oldName} TO {$newName}");
500			unset($this->tables[$table]);
501			return $this;
502			}
503
504			/**
505			* `ALTER TABLE $oldName RENAME TO $newName`
506			*
507			* @param string $oldName
508			* @param string $newName
509			* @return $this
510			*/
511			public function renameTable(string $oldName, string $newName)
512			{
513			$this->db->exec("ALTER TABLE {$oldName} RENAME TO {$newName}");
514			unset($this->tables[$oldName]);
515			return $this;
516			}
517
518			/**
519			* Sorts according to index priority, storage size/complexity, and name.
520			*
521			* @param int[] $types
522			* @return int[]
523			*/
524			protected function sortColumns(array $types): array
525			{
526			uksort($types, function (string $a, string $b) use ($types) {
527			// descending type constant, ascending name
528			return $types[$b] <=> $types[$a] ?: $a <=> $b;
529			});
530			return $types;
531			}
532
533			}
534

hfw / db

Push — master ( 97aa2a...b1b817 )

Schema::getPrimaryKeyConstraintName() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like