3
declare(strict_types=1);
6
* This file is part of CodeIgniter 4 framework.
8
* (c) CodeIgniter Foundation <admin@codeigniter.com>
10
* For the full copyright and license information, please view
11
* the LICENSE file that was distributed with this source code.
14
namespace CodeIgniter\Database;
16
use CodeIgniter\Database\Exceptions\DatabaseException;
17
use InvalidArgumentException;
22
* The Forge class transforms migrations to executable
28
* The active database connection.
37
* @var array<string, array|string> [name => attributes]
39
protected $fields = [];
44
* @var list<array{fields?: list<string>, keyName?: string}>
49
* List of unique keys.
53
protected $uniqueKeys = [];
58
* @var array{fields?: list<string>, keyName?: string}
60
protected $primaryKeys = [];
63
* List of foreign keys.
67
protected $foreignKeys = [];
74
protected $charset = '';
77
* CREATE DATABASE statement
81
protected $createDatabaseStr = 'CREATE DATABASE %s';
84
* CREATE DATABASE IF statement
88
protected $createDatabaseIfStr;
91
* CHECK DATABASE EXIST statement
95
protected $checkDatabaseExistStr;
98
* DROP DATABASE statement
102
protected $dropDatabaseStr = 'DROP DATABASE %s';
105
* CREATE TABLE statement
109
protected $createTableStr = "%s %s (%s\n)";
112
* CREATE TABLE IF statement
116
* @deprecated This is no longer used.
118
protected $createTableIfStr = 'CREATE TABLE IF NOT EXISTS';
121
* CREATE TABLE keys flag
123
* Whether table keys are created from within the
124
* CREATE TABLE statement.
128
protected $createTableKeys = false;
131
* DROP TABLE IF EXISTS statement
135
protected $dropTableIfStr = 'DROP TABLE IF EXISTS';
138
* RENAME TABLE statement
142
protected $renameTableStr = 'ALTER TABLE %s RENAME TO %s';
149
protected $unsigned = true;
152
* NULL value representation in CREATE/ALTER TABLE statements
156
* @internal Used for marking nullable fields. Not covered by BC promise.
158
protected $null = 'NULL';
161
* DEFAULT value representation in CREATE/ALTER TABLE statements
165
protected $default = ' DEFAULT ';
168
* DROP CONSTRAINT statement
172
protected $dropConstraintStr;
175
* DROP INDEX statement
179
protected $dropIndexStr = 'DROP INDEX %s ON %s';
182
* Foreign Key Allowed Actions
186
protected $fkAllowActions = ['CASCADE', 'SET NULL', 'NO ACTION', 'RESTRICT', 'SET DEFAULT'];
191
public function __construct(BaseConnection $db)
197
* Provides access to the forge's current database connection.
199
* @return ConnectionInterface
201
public function getConnection()
209
* @param bool $ifNotExists Whether to add IF NOT EXISTS condition
211
* @throws DatabaseException
213
public function createDatabase(string $dbName, bool $ifNotExists = false): bool
215
if ($ifNotExists && $this->createDatabaseIfStr === null) {
216
if ($this->databaseExists($dbName)) {
220
$ifNotExists = false;
223
if ($this->createDatabaseStr === false) {
224
if ($this->db->DBDebug) {
225
throw new DatabaseException('This feature is not available for the database you are using.');
228
return false; // @codeCoverageIgnore
232
if (! $this->db->query(
234
$ifNotExists ? $this->createDatabaseIfStr : $this->createDatabaseStr,
235
$this->db->escapeIdentifier($dbName),
240
// @codeCoverageIgnoreStart
241
if ($this->db->DBDebug) {
242
throw new DatabaseException('Unable to create the specified database.');
246
// @codeCoverageIgnoreEnd
249
if (! empty($this->db->dataCache['db_names'])) {
250
$this->db->dataCache['db_names'][] = $dbName;
254
} catch (Throwable $e) {
255
if ($this->db->DBDebug) {
256
throw new DatabaseException('Unable to create the specified database.', 0, $e);
259
return false; // @codeCoverageIgnore
264
* Determine if a database exists
266
* @throws DatabaseException
268
private function databaseExists(string $dbName): bool
270
if ($this->checkDatabaseExistStr === null) {
271
if ($this->db->DBDebug) {
272
throw new DatabaseException('This feature is not available for the database you are using.');
278
return $this->db->query($this->checkDatabaseExistStr, $dbName)->getRow() !== null;
284
* @throws DatabaseException
286
public function dropDatabase(string $dbName): bool
288
if ($this->dropDatabaseStr === false) {
289
if ($this->db->DBDebug) {
290
throw new DatabaseException('This feature is not available for the database you are using.');
296
if (! $this->db->query(
297
sprintf($this->dropDatabaseStr, $this->db->escapeIdentifier($dbName))
299
if ($this->db->DBDebug) {
300
throw new DatabaseException('Unable to drop the specified database.');
306
if (! empty($this->db->dataCache['db_names'])) {
309
array_map(strtolower(...), $this->db->dataCache['db_names']),
312
if ($key !== false) {
313
unset($this->db->dataCache['db_names'][$key]);
323
* @param array|string $key
327
public function addKey($key, bool $primary = false, bool $unique = false, string $keyName = '')
330
$this->primaryKeys = ['fields' => (array) $key, 'keyName' => $keyName];
332
$this->keys[] = ['fields' => (array) $key, 'keyName' => $keyName];
335
$this->uniqueKeys[] = count($this->keys) - 1;
345
* @param array|string $key
349
public function addPrimaryKey($key, string $keyName = '')
351
return $this->addKey($key, true, false, $keyName);
357
* @param array|string $key
361
public function addUniqueKey($key, string $keyName = '')
363
return $this->addKey($key, false, true, $keyName);
369
* @param array<string, array|string>|string $fields Field array or Field string
373
public function addField($fields)
375
if (is_string($fields)) {
376
if ($fields === 'id') {
381
'auto_increment' => true,
384
$this->addKey('id', true);
386
if (! str_contains($fields, ' ')) {
387
throw new InvalidArgumentException('Field information is required for that operation.');
390
$fieldName = explode(' ', $fields, 2)[0];
391
$fieldName = trim($fieldName, '`\'"');
393
$this->fields[$fieldName] = $fields;
397
if (is_array($fields)) {
398
foreach ($fields as $name => $attributes) {
399
if (is_string($attributes)) {
400
$this->addField($attributes);
405
if (is_array($attributes)) {
406
$this->fields = array_merge($this->fields, [$name => $attributes]);
417
* @param list<string>|string $fieldName
418
* @param list<string>|string $tableField
420
* @throws DatabaseException
422
public function addForeignKey(
424
string $tableName = '',
426
string $onUpdate = '',
427
string $onDelete = '',
430
$fieldName = (array) $fieldName;
431
$tableField = (array) $tableField;
433
$this->foreignKeys[] = [
434
'field' => $fieldName,
435
'referenceTable' => $tableName,
436
'referenceField' => $tableField,
437
'onDelete' => strtoupper($onDelete),
438
'onUpdate' => strtoupper($onUpdate),
448
* @throws DatabaseException
450
public function dropKey(string $table, string $keyName, bool $prefixKeyName = true): bool
452
$keyName = $this->db->escapeIdentifiers(($prefixKeyName === true ? $this->db->DBPrefix : '') . $keyName);
453
$table = $this->db->escapeIdentifiers($this->db->DBPrefix . $table);
455
$dropKeyAsConstraint = $this->dropKeyAsConstraint($table, $keyName);
457
if ($dropKeyAsConstraint === true) {
459
$this->dropConstraintStr,
472
if ($this->db->DBDebug) {
473
throw new DatabaseException('This feature is not available for the database you are using.');
479
return $this->db->query($sql);
483
* Checks if key needs to be dropped as a constraint.
485
protected function dropKeyAsConstraint(string $table, string $constraintName): bool
487
$sql = $this->_dropKeyAsConstraint($table, $constraintName);
493
return $this->db->query($sql)->getResultArray() !== [];
497
* Constructs sql to check if key is a constraint.
499
protected function _dropKeyAsConstraint(string $table, string $constraintName): string
507
public function dropPrimaryKey(string $table, string $keyName = ''): bool
510
'ALTER TABLE %s DROP CONSTRAINT %s',
511
$this->db->escapeIdentifiers($this->db->DBPrefix . $table),
512
($keyName === '') ? $this->db->escapeIdentifiers('pk_' . $this->db->DBPrefix . $table) : $this->db->escapeIdentifiers($keyName),
515
return $this->db->query($sql);
521
* @throws DatabaseException
523
public function dropForeignKey(string $table, string $foreignName)
526
(string) $this->dropConstraintStr,
527
$this->db->escapeIdentifiers($this->db->DBPrefix . $table),
528
$this->db->escapeIdentifiers($foreignName)
532
if ($this->db->DBDebug) {
533
throw new DatabaseException('This feature is not available for the database you are using.');
539
return $this->db->query($sql);
543
* @param array $attributes Table attributes
547
* @throws DatabaseException
549
public function createTable(string $table, bool $ifNotExists = false, array $attributes = [])
552
throw new InvalidArgumentException('A table name is required for that operation.');
555
$table = $this->db->DBPrefix . $table;
557
if ($this->fields === []) {
558
throw new RuntimeException('Field information is required.');
561
// If table exists lets stop here
562
if ($ifNotExists === true && $this->db->tableExists($table, false)) {
568
$sql = $this->_createTable($table, false, $attributes);
570
if (($result = $this->db->query($sql)) !== false) {
571
if (isset($this->db->dataCache['table_names']) && ! in_array($table, $this->db->dataCache['table_names'], true)) {
572
$this->db->dataCache['table_names'][] = $table;
575
// Most databases don't support creating indexes from within the CREATE TABLE statement
576
if (! empty($this->keys)) {
577
for ($i = 0, $sqls = $this->_processIndexes($table), $c = count($sqls); $i < $c; $i++) {
578
$this->db->query($sqls[$i]);
589
* @param array $attributes Table attributes
591
* @return string SQL string
593
* @deprecated $ifNotExists is no longer used, and will be removed.
595
protected function _createTable(string $table, bool $ifNotExists, array $attributes)
597
$processedFields = $this->_processFields(true);
599
for ($i = 0, $c = count($processedFields); $i < $c; $i++) {
600
$processedFields[$i] = ($processedFields[$i]['_literal'] !== false) ? "\n\t" . $processedFields[$i]['_literal']
601
: "\n\t" . $this->_processColumn($processedFields[$i]);
604
$processedFields = implode(',', $processedFields);
606
$processedFields .= $this->_processPrimaryKeys($table);
607
$processedFields .= current($this->_processForeignKeys($table));
609
if ($this->createTableKeys === true) {
610
$indexes = current($this->_processIndexes($table));
611
if (is_string($indexes)) {
612
$processedFields .= $indexes;
617
$this->createTableStr . '%s',
619
$this->db->escapeIdentifiers($table),
621
$this->_createTableAttributes($attributes)
625
protected function _createTableAttributes(array $attributes): string
629
foreach (array_keys($attributes) as $key) {
630
if (is_string($key)) {
631
$sql .= ' ' . strtoupper($key) . ' ' . $this->db->escape($attributes[$key]);
641
* @throws DatabaseException
643
public function dropTable(string $tableName, bool $ifExists = false, bool $cascade = false)
645
if ($tableName === '') {
646
if ($this->db->DBDebug) {
647
throw new DatabaseException('A table name is required for that operation.');
653
if ($this->db->DBPrefix && str_starts_with($tableName, $this->db->DBPrefix)) {
654
$tableName = substr($tableName, strlen($this->db->DBPrefix));
657
if (($query = $this->_dropTable($this->db->DBPrefix . $tableName, $ifExists, $cascade)) === true) {
661
$this->db->disableForeignKeyChecks();
663
$query = $this->db->query($query);
665
$this->db->enableForeignKeyChecks();
667
if ($query && ! empty($this->db->dataCache['table_names'])) {
669
strtolower($this->db->DBPrefix . $tableName),
670
array_map(strtolower(...), $this->db->dataCache['table_names']),
674
if ($key !== false) {
675
unset($this->db->dataCache['table_names'][$key]);
683
* Generates a platform-specific DROP TABLE string
685
* @return bool|string
687
protected function _dropTable(string $table, bool $ifExists, bool $cascade)
692
if ($this->dropTableIfStr === false) {
693
if (! $this->db->tableExists($table)) {
697
$sql = sprintf($this->dropTableIfStr, $this->db->escapeIdentifiers($table));
701
return $sql . ' ' . $this->db->escapeIdentifiers($table);
707
* @throws DatabaseException
709
public function renameTable(string $tableName, string $newTableName)
711
if ($tableName === '' || $newTableName === '') {
712
throw new InvalidArgumentException('A table name is required for that operation.');
715
if ($this->renameTableStr === false) {
716
if ($this->db->DBDebug) {
717
throw new DatabaseException('This feature is not available for the database you are using.');
723
$result = $this->db->query(sprintf(
724
$this->renameTableStr,
725
$this->db->escapeIdentifiers($this->db->DBPrefix . $tableName),
726
$this->db->escapeIdentifiers($this->db->DBPrefix . $newTableName)
729
if ($result && ! empty($this->db->dataCache['table_names'])) {
731
strtolower($this->db->DBPrefix . $tableName),
732
array_map(strtolower(...), $this->db->dataCache['table_names']),
736
if ($key !== false) {
737
$this->db->dataCache['table_names'][$key] = $this->db->DBPrefix . $newTableName;
745
* @param array<string, array|string>|string $fields Field array or Field string
747
* @throws DatabaseException
749
public function addColumn(string $table, $fields): bool
751
// Work-around for literal column definitions
752
if (is_string($fields)) {
756
foreach (array_keys($fields) as $name) {
757
$this->addField([$name => $fields[$name]]);
760
$sqls = $this->_alterTable('ADD', $this->db->DBPrefix . $table, $this->_processFields());
763
if ($sqls === false) {
764
if ($this->db->DBDebug) {
765
throw new DatabaseException('This feature is not available for the database you are using.');
771
foreach ($sqls as $sql) {
772
if ($this->db->query($sql) === false) {
781
* @param array|string $columnNames column names to DROP
785
* @throws DatabaseException
787
public function dropColumn(string $table, $columnNames)
789
$sql = $this->_alterTable('DROP', $this->db->DBPrefix . $table, $columnNames);
791
if ($sql === false) {
792
if ($this->db->DBDebug) {
793
throw new DatabaseException('This feature is not available for the database you are using.');
799
return $this->db->query($sql);
803
* @param array<string, array|string>|string $fields Field array or Field string
805
* @throws DatabaseException
807
public function modifyColumn(string $table, $fields): bool
809
// Work-around for literal column definitions
810
if (is_string($fields)) {
814
foreach (array_keys($fields) as $name) {
815
$this->addField([$name => $fields[$name]]);
818
if ($this->fields === []) {
819
throw new RuntimeException('Field information is required');
822
$sqls = $this->_alterTable('CHANGE', $this->db->DBPrefix . $table, $this->_processFields());
825
if ($sqls === false) {
826
if ($this->db->DBDebug) {
827
throw new DatabaseException('This feature is not available for the database you are using.');
833
if (is_array($sqls)) {
834
foreach ($sqls as $sql) {
835
if ($this->db->query($sql) === false) {
845
* @param 'ADD'|'CHANGE'|'DROP' $alterType
846
* @param array|string $processedFields Processed column definitions
847
* or column names to DROP
849
* @return false|list<string>|string|null SQL string
850
* @phpstan-return ($alterType is 'DROP' ? string : list<string>|false|null)
852
protected function _alterTable(string $alterType, string $table, $processedFields)
854
$sql = 'ALTER TABLE ' . $this->db->escapeIdentifiers($table) . ' ';
856
// DROP has everything it needs now.
857
if ($alterType === 'DROP') {
858
$columnNamesToDrop = $processedFields;
860
if (is_string($columnNamesToDrop)) {
861
$columnNamesToDrop = explode(',', $columnNamesToDrop);
864
$columnNamesToDrop = array_map(fn ($field) => 'DROP COLUMN ' . $this->db->escapeIdentifiers(trim($field)), $columnNamesToDrop);
866
return $sql . implode(', ', $columnNamesToDrop);
869
$sql .= ($alterType === 'ADD') ? 'ADD ' : $alterType . ' COLUMN ';
873
foreach ($processedFields as $field) {
874
$sqls[] = $sql . ($field['_literal'] !== false
876
: $this->_processColumn($field));
883
* Returns $processedFields array from $this->fields data.
885
protected function _processFields(bool $createTable = false): array
887
$processedFields = [];
889
foreach ($this->fields as $name => $attributes) {
890
if (! is_array($attributes)) {
891
$processedFields[] = ['_literal' => $attributes];
896
$attributes = array_change_key_case($attributes, CASE_UPPER);
898
if ($createTable === true && empty($attributes['TYPE'])) {
902
if (isset($attributes['TYPE'])) {
903
$this->_attributeType($attributes);
908
'new_name' => $attributes['NAME'] ?? null,
909
'type' => $attributes['TYPE'] ?? null,
915
'auto_increment' => '',
919
if (isset($attributes['TYPE'])) {
920
$this->_attributeUnsigned($attributes, $field);
923
if ($createTable === false) {
924
if (isset($attributes['AFTER'])) {
925
$field['after'] = $attributes['AFTER'];
926
} elseif (isset($attributes['FIRST'])) {
927
$field['first'] = (bool) $attributes['FIRST'];
931
$this->_attributeDefault($attributes, $field);
933
if (isset($attributes['NULL'])) {
934
$nullString = ' ' . $this->null;
936
if ($attributes['NULL'] === true) {
937
$field['null'] = empty($this->null) ? '' : $nullString;
938
} elseif ($attributes['NULL'] === $nullString) {
939
$field['null'] = $nullString;
940
} elseif ($attributes['NULL'] === '') {
943
$field['null'] = ' NOT ' . $this->null;
945
} elseif ($createTable === true) {
946
$field['null'] = ' NOT ' . $this->null;
949
$this->_attributeAutoIncrement($attributes, $field);
950
$this->_attributeUnique($attributes, $field);
952
if (isset($attributes['COMMENT'])) {
953
$field['comment'] = $this->db->escape($attributes['COMMENT']);
956
if (isset($attributes['TYPE']) && ! empty($attributes['CONSTRAINT'])) {
957
if (is_array($attributes['CONSTRAINT'])) {
958
$attributes['CONSTRAINT'] = $this->db->escape($attributes['CONSTRAINT']);
959
$attributes['CONSTRAINT'] = implode(',', $attributes['CONSTRAINT']);
962
$field['length'] = '(' . $attributes['CONSTRAINT'] . ')';
965
$processedFields[] = $field;
968
return $processedFields;
972
* Converts $processedField array to field definition string.
974
protected function _processColumn(array $processedField): string
976
return $this->db->escapeIdentifiers($processedField['name'])
977
. ' ' . $processedField['type'] . $processedField['length']
978
. $processedField['unsigned']
979
. $processedField['default']
980
. $processedField['null']
981
. $processedField['auto_increment']
982
. $processedField['unique'];
986
* Performs a data type mapping between different databases.
988
protected function _attributeType(array &$attributes)
990
// Usually overridden by drivers
994
* Depending on the unsigned property value:
996
* - TRUE will always set $field['unsigned'] to 'UNSIGNED'
997
* - FALSE will always set $field['unsigned'] to ''
998
* - array(TYPE) will set $field['unsigned'] to 'UNSIGNED',
999
* if $attributes['TYPE'] is found in the array
1000
* - array(TYPE => UTYPE) will change $field['type'],
1001
* from TYPE to UTYPE in case of a match
1003
protected function _attributeUnsigned(array &$attributes, array &$field)
1005
if (empty($attributes['UNSIGNED']) || $attributes['UNSIGNED'] !== true) {
1009
// Reset the attribute in order to avoid issues if we do type conversion
1010
$attributes['UNSIGNED'] = false;
1012
if (is_array($this->unsigned)) {
1013
foreach (array_keys($this->unsigned) as $key) {
1014
if (is_int($key) && strcasecmp($attributes['TYPE'], $this->unsigned[$key]) === 0) {
1015
$field['unsigned'] = ' UNSIGNED';
1020
if (is_string($key) && strcasecmp($attributes['TYPE'], $key) === 0) {
1021
$field['type'] = $key;
1030
$field['unsigned'] = ($this->unsigned === true) ? ' UNSIGNED' : '';
1033
protected function _attributeDefault(array &$attributes, array &$field)
1035
if ($this->default === false) {
1039
if (array_key_exists('DEFAULT', $attributes)) {
1040
if ($attributes['DEFAULT'] === null) {
1041
$field['default'] = empty($this->null) ? '' : $this->default . $this->null;
1043
// Override the NULL attribute if that's our default
1044
$attributes['NULL'] = true;
1045
$field['null'] = empty($this->null) ? '' : ' ' . $this->null;
1046
} elseif ($attributes['DEFAULT'] instanceof RawSql) {
1047
$field['default'] = $this->default . $attributes['DEFAULT'];
1049
$field['default'] = $this->default . $this->db->escape($attributes['DEFAULT']);
1054
protected function _attributeUnique(array &$attributes, array &$field)
1056
if (! empty($attributes['UNIQUE']) && $attributes['UNIQUE'] === true) {
1057
$field['unique'] = ' UNIQUE';
1061
protected function _attributeAutoIncrement(array &$attributes, array &$field)
1063
if (! empty($attributes['AUTO_INCREMENT']) && $attributes['AUTO_INCREMENT'] === true
1064
&& stripos($field['type'], 'int') !== false
1066
$field['auto_increment'] = ' AUTO_INCREMENT';
1071
* Generates SQL to add primary key
1073
* @param bool $asQuery When true returns stand alone SQL, else partial SQL used with CREATE TABLE
1075
protected function _processPrimaryKeys(string $table, bool $asQuery = false): string
1079
if (isset($this->primaryKeys['fields'])) {
1080
for ($i = 0, $c = count($this->primaryKeys['fields']); $i < $c; $i++) {
1081
if (! isset($this->fields[$this->primaryKeys['fields'][$i]])) {
1082
unset($this->primaryKeys['fields'][$i]);
1087
if (isset($this->primaryKeys['fields']) && $this->primaryKeys['fields'] !== []) {
1088
if ($asQuery === true) {
1089
$sql .= 'ALTER TABLE ' . $this->db->escapeIdentifiers($this->db->DBPrefix . $table) . ' ADD ';
1093
$sql .= 'CONSTRAINT ' . $this->db->escapeIdentifiers(($this->primaryKeys['keyName'] === '' ?
1095
$this->primaryKeys['keyName']))
1096
. ' PRIMARY KEY(' . implode(', ', $this->db->escapeIdentifiers($this->primaryKeys['fields'])) . ')';
1103
* Executes Sql to add indexes without createTable
1105
public function processIndexes(string $table): bool
1108
$fk = $this->foreignKeys;
1110
if ($this->fields === []) {
1111
$this->fields = array_flip(array_map(
1112
static fn ($columnName) => $columnName->name,
1113
$this->db->getFieldData($this->db->DBPrefix . $table)
1117
$fields = $this->fields;
1119
if ($this->keys !== []) {
1120
$sqls = $this->_processIndexes($this->db->DBPrefix . $table, true);
1123
if ($this->primaryKeys !== []) {
1124
$sqls[] = $this->_processPrimaryKeys($table, true);
1127
$this->foreignKeys = $fk;
1128
$this->fields = $fields;
1130
if ($this->foreignKeys !== []) {
1131
$sqls = array_merge($sqls, $this->_processForeignKeys($table, true));
1134
foreach ($sqls as $sql) {
1135
if ($this->db->query($sql) === false) {
1146
* Generates SQL to add indexes
1148
* @param bool $asQuery When true returns stand alone SQL, else partial SQL used with CREATE TABLE
1150
protected function _processIndexes(string $table, bool $asQuery = false): array
1154
for ($i = 0, $c = count($this->keys); $i < $c; $i++) {
1155
for ($i2 = 0, $c2 = count($this->keys[$i]['fields']); $i2 < $c2; $i2++) {
1156
if (! isset($this->fields[$this->keys[$i]['fields'][$i2]])) {
1157
unset($this->keys[$i]['fields'][$i2]);
1161
if (count($this->keys[$i]['fields']) <= 0) {
1165
$keyName = $this->db->escapeIdentifiers(($this->keys[$i]['keyName'] === '') ?
1166
$table . '_' . implode('_', $this->keys[$i]['fields']) :
1167
$this->keys[$i]['keyName']);
1169
if (in_array($i, $this->uniqueKeys, true)) {
1170
if ($this->db->DBDriver === 'SQLite3') {
1171
$sqls[] = 'CREATE UNIQUE INDEX ' . $keyName
1172
. ' ON ' . $this->db->escapeIdentifiers($table)
1173
. ' (' . implode(', ', $this->db->escapeIdentifiers($this->keys[$i]['fields'])) . ')';
1175
$sqls[] = 'ALTER TABLE ' . $this->db->escapeIdentifiers($table)
1176
. ' ADD CONSTRAINT ' . $keyName
1177
. ' UNIQUE (' . implode(', ', $this->db->escapeIdentifiers($this->keys[$i]['fields'])) . ')';
1183
$sqls[] = 'CREATE INDEX ' . $keyName
1184
. ' ON ' . $this->db->escapeIdentifiers($table)
1185
. ' (' . implode(', ', $this->db->escapeIdentifiers($this->keys[$i]['fields'])) . ')';
1192
* Generates SQL to add foreign keys
1194
* @param bool $asQuery When true returns stand alone SQL, else partial SQL used with CREATE TABLE
1196
protected function _processForeignKeys(string $table, bool $asQuery = false): array
1200
foreach ($this->foreignKeys as $fkeyInfo) {
1201
foreach ($fkeyInfo['field'] as $fieldName) {
1202
if (! isset($this->fields[$fieldName])) {
1203
$errorNames[] = $fieldName;
1208
if ($errorNames !== []) {
1209
$errorNames = [implode(', ', $errorNames)];
1211
throw new DatabaseException(lang('Database.fieldNotExists', $errorNames));
1216
foreach ($this->foreignKeys as $index => $fkey) {
1217
if ($asQuery === false) {
1223
$nameIndex = $fkey['fkName'] !== '' ?
1225
$table . '_' . implode('_', $fkey['field']) . ($this->db->DBDriver === 'OCI8' ? '_fk' : '_foreign');
1227
$nameIndexFilled = $this->db->escapeIdentifiers($nameIndex);
1228
$foreignKeyFilled = implode(', ', $this->db->escapeIdentifiers($fkey['field']));
1229
$referenceTableFilled = $this->db->escapeIdentifiers($this->db->DBPrefix . $fkey['referenceTable']);
1230
$referenceFieldFilled = implode(', ', $this->db->escapeIdentifiers($fkey['referenceField']));
1232
if ($asQuery === true) {
1233
$sqls[$index] .= 'ALTER TABLE ' . $this->db->escapeIdentifiers($this->db->DBPrefix . $table) . ' ADD ';
1235
$sqls[$index] .= ",\n\t";
1238
$formatSql = 'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s)';
1239
$sqls[$index] .= sprintf($formatSql, $nameIndexFilled, $foreignKeyFilled, $referenceTableFilled, $referenceFieldFilled);
1241
if ($fkey['onDelete'] !== false && in_array($fkey['onDelete'], $this->fkAllowActions, true)) {
1242
$sqls[$index] .= ' ON DELETE ' . $fkey['onDelete'];
1245
if ($this->db->DBDriver !== 'OCI8' && $fkey['onUpdate'] !== false && in_array($fkey['onUpdate'], $this->fkAllowActions, true)) {
1246
$sqls[$index] .= ' ON UPDATE ' . $fkey['onUpdate'];
1254
* Resets table creation vars
1256
public function reset()
1258
$this->fields = $this->keys = $this->uniqueKeys = $this->primaryKeys = $this->foreignKeys = [];