Skip to content

Commit c4f1271

Browse files
Tigrovvjik
Tigrov
and
vjik
authored
Support table view constraints (#316)
* Support table view constraints * Fix StyleCI test * Support old versions * Improve SQL queries * Add tests and fix * Fix for old versions * Add line to CHANGELOG.md * Add assertion --------- Co-authored-by: Sergei Predvoditelev <sergei@predvoditelev.ru>
1 parent c003f9c commit c4f1271

7 files changed

Lines changed: 88 additions & 5 deletions

File tree

‎CHANGELOG.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22

33
## 1.2.1 under development
44

5+
- Bug #316, #6: Support table view constraints (@Tigrov)
56
- Enh #324: Change property `Schema::$typeMap` to constant `Schema::TYPE_MAP` (@Tigrov)
67

78
## 1.2.0 November 12, 2023

‎src/Schema.php‎

Lines changed: 23 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -365,8 +365,15 @@ protected function loadTableIndexes(string $tableName): array
365365
FROM "pg_class" AS "tc"
366366
INNER JOIN "pg_namespace" AS "tcns"
367367
ON "tcns"."oid" = "tc"."relnamespace"
368+
LEFT JOIN pg_rewrite AS rw
369+
ON tc.relkind = 'v' AND rw.ev_class = tc.oid AND rw.rulename = '_RETURN'
368370
INNER JOIN "pg_index" AS "i"
369371
ON "i"."indrelid" = "tc"."oid"
372+
OR rw.ev_action IS NOT NULL
373+
AND (SELECT regexp_matches(
374+
rw.ev_action,
375+
'{TARGETENTRY .*? :resorigtbl ' || "i"."indrelid" || ' :resorigcol ' || "i"."indkey"[0] || ' '
376+
)) IS NOT NULL
370377
INNER JOIN "pg_class" AS "ic"
371378
ON "ic"."oid" = "i"."indexrelid"
372379
INNER JOIN "pg_attribute" AS "ia"
@@ -711,7 +718,7 @@ protected function findColumns(TableSchemaInterface $table): bool
711718
COALESCE(td.oid, tb.oid, a.atttypid),
712719
information_schema._pg_truetypmod(a, t)
713720
) AS size,
714-
a.attnum = any (ct.conkey) as is_pkey,
721+
ct.oid IS NOT NULL AS is_pkey,
715722
COALESCE(NULLIF(a.attndims, 0), NULLIF(t.typndims, 0), (t.typcategory='A')::int) AS dimension
716723
FROM
717724
pg_class c
@@ -722,7 +729,12 @@ protected function findColumns(TableSchemaInterface $table): bool
722729
OR t.typbasetype > 0 AND t.typbasetype = tb.oid
723730
LEFT JOIN pg_type td ON t.typndims > 0 AND t.typbasetype > 0 AND tb.typelem = td.oid
724731
LEFT JOIN pg_namespace d ON d.oid = c.relnamespace
725-
LEFT JOIN pg_constraint ct ON ct.conrelid = c.oid AND ct.contype = 'p'
732+
LEFT JOIN pg_rewrite rw ON c.relkind = 'v' AND rw.ev_class = c.oid AND rw.rulename = '_RETURN'
733+
LEFT JOIN pg_constraint ct ON ct.conrelid = c.oid AND ct.contype = 'p' AND a.attnum = ANY (ct.conkey)
734+
OR rw.ev_action IS NOT NULL AND ct.contype = 'p'
735+
AND (ARRAY(
736+
SELECT regexp_matches(rw.ev_action, '{TARGETENTRY .*? :resorigtbl (\d+) :resorigcol (\d+) ', 'g')
737+
))[a.attnum:a.attnum] <@ (ct.conrelid::text || ct.conkey::text[])
726738
WHERE
727739
a.attnum > 0 AND t.typname != '' AND NOT a.attisdropped
728740
AND c.relname = :tableName
@@ -903,10 +915,16 @@ private function loadTableConstraints(string $tableName, string $returnType): ar
903915
FROM "pg_class" AS "tc"
904916
INNER JOIN "pg_namespace" AS "tcns"
905917
ON "tcns"."oid" = "tc"."relnamespace"
906-
INNER JOIN "pg_constraint" AS "c"
907-
ON "c"."conrelid" = "tc"."oid"
908918
INNER JOIN "pg_attribute" AS "a"
909-
ON "a"."attrelid" = "c"."conrelid" AND "a"."attnum" = ANY ("c"."conkey")
919+
ON "a"."attrelid" = "tc"."oid"
920+
LEFT JOIN pg_rewrite AS rw
921+
ON "tc"."relkind" = 'v' AND "rw"."ev_class" = "tc"."oid" AND "rw"."rulename" = '_RETURN'
922+
INNER JOIN "pg_constraint" AS "c"
923+
ON "c"."conrelid" = "tc"."oid" AND "a"."attnum" = ANY ("c"."conkey")
924+
OR "rw"."ev_action" IS NOT NULL AND "c"."conrelid" != 0
925+
AND (ARRAY(
926+
SELECT regexp_matches("rw"."ev_action", '{TARGETENTRY .*? :resorigtbl (\d+) :resorigcol (\d+) ', 'g')
927+
))["a"."attnum":"a"."attnum"] <@ ("c"."conrelid"::text || "c"."conkey"::text[])
910928
LEFT JOIN "pg_class" AS "ftc"
911929
ON "ftc"."oid" = "c"."confrelid"
912930
LEFT JOIN "pg_namespace" AS "ftcns"

‎tests/ColumnSchemaTest.php‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -197,4 +197,18 @@ public function testDbTypeCastBit()
197197

198198
$this->assertSame('01100100', $tableSchema->getColumn('bit_col')->dbTypecast('01100100'));
199199
}
200+
201+
public function testPrimaryKeyOfView()
202+
{
203+
$db = $this->getConnection(true);
204+
$schema = $db->getSchema();
205+
$tableSchema = $schema->getTableSchema('T_constraints_2_view');
206+
207+
$this->assertSame(['C_id_1', 'C_id_2'], $tableSchema->getPrimaryKey());
208+
$this->assertTrue($tableSchema->getColumn('C_id_1')->isPrimaryKey());
209+
$this->assertTrue($tableSchema->getColumn('C_id_2')->isPrimaryKey());
210+
$this->assertFalse($tableSchema->getColumn('C_index_1')->isPrimaryKey());
211+
$this->assertFalse($tableSchema->getColumn('C_index_2_1')->isPrimaryKey());
212+
$this->assertFalse($tableSchema->getColumn('C_index_2_2')->isPrimaryKey());
213+
}
200214
}

‎tests/Provider/QueryBuilderProvider.php‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -447,6 +447,14 @@ public static function upsert(): array
447447
$upsert[$testName] = array_replace($upsert[$testName], $data);
448448
}
449449

450+
$upsert['table view'] = [
451+
'animal_view',
452+
['id' => 3, 'type' => 'yiiunit\data\ar\Mouse'],
453+
true,
454+
'INSERT INTO "animal_view" ("id", "type") VALUES (:qp0, :qp1) ON CONFLICT ("id") DO UPDATE SET "type"=EXCLUDED."type"',
455+
[':qp0' => 3, ':qp1' => 'yiiunit\data\ar\Mouse'],
456+
];
457+
450458
return $upsert;
451459
}
452460
}

‎tests/Provider/SchemaProvider.php‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -447,6 +447,17 @@ public static function constraints(): array
447447
return $constraints;
448448
}
449449

450+
public static function constraintsOfView(): array
451+
{
452+
$constraints = self::constraints();
453+
454+
foreach ($constraints as $key => $constraint) {
455+
$constraints[$key][0] = $constraint[0] . '_view';
456+
}
457+
458+
return $constraints;
459+
}
460+
450461
public static function tableSchemaCacheWithTablePrefixes(): array
451462
{
452463
$configs = [

‎tests/SchemaTest.php‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -361,6 +361,7 @@ public function testTableSchemaCacheWithTablePrefixes(
361361

362362
/**
363363
* @dataProvider \Yiisoft\Db\Pgsql\Tests\Provider\SchemaProvider::constraints
364+
* @dataProvider \Yiisoft\Db\Pgsql\Tests\Provider\SchemaProvider::constraintsOfView
364365
*
365366
* @throws Exception
366367
* @throws JsonException
@@ -557,4 +558,25 @@ public function testDomainType(): void
557558

558559
$db->close();
559560
}
561+
562+
public function testGetViewNames(): void
563+
{
564+
$db = $this->getConnection(true);
565+
566+
$schema = $db->getSchema();
567+
$views = $schema->getViewNames();
568+
569+
$this->assertSame(
570+
[
571+
'T_constraints_1_view',
572+
'T_constraints_2_view',
573+
'T_constraints_3_view',
574+
'T_constraints_4_view',
575+
'animal_view',
576+
],
577+
$views,
578+
);
579+
580+
$db->close();
581+
}
560582
}

‎tests/Support/Fixture/pgsql.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,10 @@ DROP TABLE IF EXISTS "department";
2525
DROP TABLE IF EXISTS "alpha";
2626
DROP TABLE IF EXISTS "beta";
2727
DROP VIEW IF EXISTS "animal_view";
28+
DROP VIEW IF EXISTS "T_constraints_4_view";
29+
DROP VIEW IF EXISTS "T_constraints_3_view";
30+
DROP VIEW IF EXISTS "T_constraints_2_view";
31+
DROP VIEW IF EXISTS "T_constraints_1_view";
2832
DROP TABLE IF EXISTS "T_constraints_6";
2933
DROP TABLE IF EXISTS "T_constraints_5";
3034
DROP TABLE IF EXISTS "T_constraints_4";
@@ -413,6 +417,11 @@ CREATE TABLE "T_constraints_6"
413417
CONSTRAINT "CN_constraints_6" FOREIGN KEY ("C_fk_id_1", "C_fk_id_2") REFERENCES "schema1"."T_constraints_5" ("C_id_1", "C_id_2") ON DELETE CASCADE ON UPDATE CASCADE
414418
);
415419

420+
CREATE VIEW "T_constraints_1_view" AS SELECT 'first_value', * FROM "T_constraints_1";
421+
CREATE VIEW "T_constraints_2_view" AS SELECT 'first_value', * FROM "T_constraints_2";
422+
CREATE VIEW "T_constraints_3_view" AS SELECT 'first_value', * FROM "T_constraints_3";
423+
CREATE VIEW "T_constraints_4_view" AS SELECT 'first_value', * FROM "T_constraints_4";
424+
416425
CREATE TABLE "T_upsert"
417426
(
418427
"id" SERIAL NOT NULL PRIMARY KEY,

0 commit comments

Comments
 (0)