Skip to content

Commit f07e26e

Browse files
terabytesoftwterabytesoftw
authored
Fix #74: Fix getTableIndexes() (#75)
1 parent b1a1afd commit f07e26e

3 files changed

Lines changed: 66 additions & 53 deletions

File tree

‎composer.json‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
"ext-mbstring": "*",
2323
"ext-pdo": "*",
2424
"yiisoft/arrays": "^1.0",
25-
"yiisoft/db": "^3.0",
25+
"yiisoft/db": "^3.0@dev",
2626
"yiisoft/strings": "^2.0"
2727
},
2828
"require-dev": {

‎src/Schema.php‎

Lines changed: 26 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -21,13 +21,11 @@
2121
use Yiisoft\Db\Schema\ColumnSchema;
2222
use Yiisoft\Db\Schema\Schema as AbstractSchema;
2323
use Yiisoft\Db\Transaction\Transaction;
24-
use Yiisoft\Db\Transaction\TransactionInterface;
2524

2625
use function count;
2726
use function explode;
2827
use function preg_match;
2928
use function strncasecmp;
30-
use function strncmp;
3129
use function strpos;
3230
use function strtolower;
3331
use function trim;
@@ -36,7 +34,7 @@
3634
* Schema is the class for retrieving metadata from a SQLite (2/3) database.
3735
*
3836
* @property string $transactionIsolationLevel The transaction isolation level to use for this transaction. This can be
39-
* either {@see TransactionInterface::READ_UNCOMMITTED} or {@see TransactionInterface::SERIALIZABLE}.
37+
* either {@see Transaction::READ_UNCOMMITTED} or {@see Transaction::SERIALIZABLE}.
4038
*/
4139
final class Schema extends AbstractSchema implements ConstraintFinderInterface
4240
{
@@ -112,7 +110,7 @@ protected function findTableNames(string $schema = ''): array
112110
*
113111
* @param string $name table name.
114112
*
115-
* @throws Exception|InvalidArgumentException|InvalidConfigException
113+
* @throws Exception|InvalidArgumentException|InvalidConfigException|Throwable
116114
*
117115
* @return TableSchema|null DBMS-dependent table metadata, `null` if the table does not exist.
118116
*/
@@ -137,7 +135,7 @@ protected function loadTableSchema(string $name): ?TableSchema
137135
*
138136
* @param string $tableName table name.
139137
*
140-
* @throws Exception|InvalidArgumentException|InvalidConfigException
138+
* @throws Exception|InvalidArgumentException|InvalidConfigException|Throwable
141139
*
142140
* @return Constraint|null primary key for the given table, `null` if the table has no primary key.
143141
*/
@@ -188,7 +186,7 @@ protected function loadTableForeignKeys(string $tableName): array
188186
*
189187
* @param string $tableName table name.
190188
*
191-
* @throws Exception|InvalidArgumentException|InvalidConfigException
189+
* @throws Exception|InvalidArgumentException|InvalidConfigException|Throwable
192190
*
193191
* @return IndexConstraint[] indexes for the given table.
194192
*/
@@ -202,7 +200,7 @@ protected function loadTableIndexes(string $tableName): array
202200
*
203201
* @param string $tableName table name.
204202
*
205-
* @throws Exception|InvalidArgumentException|InvalidConfigException
203+
* @throws Exception|InvalidArgumentException|InvalidConfigException|Throwable
206204
*
207205
* @return Constraint[] unique constraints for the given table.
208206
*/
@@ -471,7 +469,7 @@ protected function loadColumnSchema(array $info): ColumnSchema
471469
* Sets the isolation level of the current transaction.
472470
*
473471
* @param string $level The transaction isolation level to use for this transaction. This can be either
474-
* {@see TransactionInterface::READ_UNCOMMITTED} or {@see TransactionInterface::SERIALIZABLE}.
472+
* {@see Transaction::READ_UNCOMMITTED} or {@see Transaction::SERIALIZABLE}.
475473
*
476474
* @throws Exception|InvalidConfigException|NotSupportedException|Throwable when unsupported isolation levels are
477475
* used. SQLite only supports SERIALIZABLE and READ UNCOMMITTED.
@@ -527,24 +525,10 @@ private function loadTableColumnsInfo(string $tableName): array
527525
private function loadTableConstraints(string $tableName, string $returnType)
528526
{
529527
$tableColumns = null;
530-
531-
$index = $this->getDb()->createCommand(
528+
$indexList = $this->getDb()->createCommand(
532529
'PRAGMA INDEX_LIST (' . $this->quoteValue($tableName) . ')'
533530
)->queryAll();
534-
535-
$unique = $this->getDb()->createCommand(
536-
"SELECT
537-
'0' as 'seq',
538-
name,
539-
'1' as 'unique',
540-
'u' as 'origin',
541-
'0' as 'partial'
542-
FROM sqlite_master
543-
WHERE type='index' AND sql LIKE 'CREATE UNIQUE INDEX%' AND tbl_name='$tableName'"
544-
)->queryAll();
545-
546-
$indexes = array_merge($index, $unique);
547-
$indexes = $this->normalizePdoRowKeyCase($indexes, true);
531+
$indexes = $this->normalizePdoRowKeyCase($indexList, true);
548532

549533
if (!empty($indexes) && !isset($indexes[0]['origin'])) {
550534
/**
@@ -562,22 +546,14 @@ private function loadTableConstraints(string $tableName, string $returnType)
562546
];
563547

564548
foreach ($indexes as $index) {
565-
$columns = $this->getDb()->createCommand(
566-
'PRAGMA INDEX_INFO (' . $this->quoteValue($index['name']) . ')'
567-
)->queryAll();
568-
569-
$columns = $this->normalizePdoRowKeyCase($columns, true);
570-
571-
ArraySorter::multisort($columns, 'seqno', SORT_ASC, SORT_NUMERIC);
549+
$columns = $this->getPragmaIndexInfo($index['name']);
572550

573551
if ($tableColumns !== null) {
574552
/** SQLite may not have an "origin" column in INDEX_LIST */
575553
$index['origin'] = 'c';
576554

577555
if (!empty($columns) && $tableColumns[$columns[0]['cid']]['pk'] > 0) {
578556
$index['origin'] = 'pk';
579-
} elseif ($index['unique'] && $this->isSystemIdentifier($index['name'])) {
580-
$index['origin'] = 'u';
581557
}
582558
}
583559

@@ -589,17 +565,17 @@ private function loadTableConstraints(string $tableName, string $returnType)
589565

590566
$result['indexes'][] = $ic;
591567

592-
if ($index['origin'] === 'u') {
568+
if ($index['origin'] === 'pk') {
593569
$ct = (new Constraint())
594-
->name($index['name'])
595570
->columnNames(ArrayHelper::getColumn($columns, 'name'));
596571

597-
$result['uniques'][] = $ct;
598-
} elseif ($index['origin'] === 'pk') {
572+
$result['primaryKey'] = $ct;
573+
} elseif ($index['unique']) {
599574
$ct = (new Constraint())
575+
->name($index['name'])
600576
->columnNames(ArrayHelper::getColumn($columns, 'name'));
601577

602-
$result['primaryKey'] = $ct;
578+
$result['uniques'][] = $ct;
603579
}
604580
}
605581

@@ -632,20 +608,6 @@ private function loadTableConstraints(string $tableName, string $returnType)
632608
return $result[$returnType];
633609
}
634610

635-
/**
636-
* Return whether the specified identifier is a SQLite system identifier.
637-
*
638-
* @param string $identifier
639-
*
640-
* @return bool
641-
*
642-
* {@see https://www.sqlite.org/src/artifact/74108007d286232f}
643-
*/
644-
private function isSystemIdentifier(string $identifier): bool
645-
{
646-
return strncmp($identifier, 'sqlite_', 7) === 0;
647-
}
648-
649611
/**
650612
* Creates a column schema for the database.
651613
*
@@ -657,4 +619,16 @@ private function createColumnSchema(): ColumnSchema
657619
{
658620
return new ColumnSchema();
659621
}
622+
623+
/**
624+
* @throws Exception|InvalidConfigException|Throwable
625+
*/
626+
private function getPragmaIndexInfo(string $name): array
627+
{
628+
$column = $this->getDb()->createCommand('PRAGMA INDEX_INFO (' . $this->quoteValue($name) . ')')->queryAll();
629+
$columns = $this->normalizePdoRowKeyCase($column, true);
630+
ArraySorter::multisort($columns, 'seqno', SORT_ASC, SORT_NUMERIC);
631+
632+
return $columns;
633+
}
660634
}

‎tests/SchemaTest.php‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -456,4 +456,43 @@ public function testTableSchemaCacheWithTablePrefixes(
456456
$this->assertEquals($refreshedTable, $testRefreshedTable);
457457
$this->assertNotSame($testNoCacheTable, $testRefreshedTable);
458458
}
459+
460+
public function testsCountIndexUnique(): void
461+
{
462+
$db = $this->getConnection();
463+
464+
$tableName = 'test_uq';
465+
$name = 'test_uq_constraint';
466+
467+
$schema = $db->getSchema();
468+
469+
if ($schema->getTableSchema($tableName) !== null) {
470+
$db->createCommand()->dropTable($tableName)->execute();
471+
}
472+
473+
$db->createCommand()->createTable($tableName, [
474+
'int1' => 'integer not null',
475+
'int2' => 'integer not null',
476+
])->execute();
477+
478+
$this->assertEmpty($schema->getTableIndexes($tableName, true));
479+
$this->assertEmpty($schema->getTableUniques($tableName, true));
480+
481+
$db->createCommand()->addUnique($name, $tableName, ['int1'])->execute();
482+
483+
$this->assertCount(1, $schema->getTableIndexes($tableName, true));
484+
$this->assertCount(1, $schema->getTableUniques($tableName, true));
485+
$this->assertEquals(['int1'], $schema->getTableUniques($tableName, true)[0]->getColumnNames());
486+
487+
$db->createCommand()->dropUnique($name, $tableName)->execute();
488+
489+
$this->assertEmpty($schema->getTableIndexes($tableName, true));
490+
$this->assertEmpty($schema->getTableUniques($tableName, true));
491+
492+
$db->createCommand()->addUnique($name, $tableName, ['int1', 'int2'])->execute();
493+
494+
$this->assertCount(1, $schema->getTableIndexes($tableName, true));
495+
$this->assertCount(1, $schema->getTableUniques($tableName, true));
496+
$this->assertEquals(['int1', 'int2'], $schema->getTableUniques($tableName, true)[0]->getColumnNames());
497+
}
459498
}

0 commit comments

Comments
 (0)