Committed by =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <[email protected]>
Subject: [DBD::Pg 2/2] Fix foreign_key_info with unspecified schema
---
Pg.pm | 3 ++
t/03dbmethod.t | 64 +++++++++++++++++++++++++++++++++----------------------
2 files changed, 41 insertions(+), 26 deletions(-)
diff --git a/Pg.pm b/Pg.pm
index 260eaf6..c620d21 100644
--- a/Pg.pm
+++ b/Pg.pm
@@ -877,6 +877,9 @@ use 5.006001;
if (length $_->[1]) {
$SQL .= ' AND n.nspname = ' .
$dbh->quote($_->[1]);
}
+ else {
+ $SQL .= ' AND
pg_catalog.pg_table_is_visible(c.oid)'
+ }
my $info = $dbh->selectall_arrayref($SQL);
return undef if ! @$info;
$oid{$_->[2]} = $info->[0][0];
diff --git a/t/03dbmethod.t b/t/03dbmethod.t
index 976182d..c831175 100644
--- a/t/03dbmethod.t
+++ b/t/03dbmethod.t
@@ -31,7 +31,7 @@ plan tests => 535;
isnt ($dbh, undef, 'Connect to database for database handle method testing');
my ($pglibversion,$pgversion) =
($dbh->{pg_lib_version},$dbh->{pg_server_version});
-my ($schema,$schema2) = ('dbd_pg_testschema', 'dbd_pg_testschema2');
+my ($schema,$schema2,$schema3) = ('dbd_pg_testschema', 'dbd_pg_testschema2',
'dbd_pg_testschema3');
my ($table1,$table2,$table3) = ('dbd_pg_test1','dbd_pg_test2','dbd_pg_test3');
my ($sequence2,$sequence3,$sequence4) =
('dbd_pg_testsequence2','dbd_pg_testsequence3','dbd_pg_testsequence4');
@@ -800,7 +800,7 @@ is ($sth, undef, $t);
# Drop any tables that may exist
my $fktables = join ',' => map { "'dbd_pg_test$_'" } (1..3);
-$SQL = "SELECT relname FROM pg_catalog.pg_class WHERE relkind='r' AND relname
IN ($fktables)";
+$SQL = "SELECT n.nspname||'.'||r.relname FROM pg_catalog.pg_class r,
pg_catalog.pg_namespace n WHERE relkind='r' AND r.relnamespace = n.oid AND
r.relname IN ($fktables)";
{
local $SIG{__WARN__} = sub {};
for (@{$dbh->selectall_arrayref($SQL)}) {
@@ -823,12 +823,21 @@ $sth =
$dbh->foreign_key_info(undef,undef,'dbd_pg_test9',undef,undef,'dbd_pg_tes
is ($sth, undef, $t);
## Create a pk table
-{
+
+# The order of the tables returned by the OID query in foreign_key_info
+# seems to be influenced by schema creation order, so create the schemas
+# in the opposite order of the search_path, so we have at least a vague
+# chance of testing that we respect the search_path order. Also create
+# the tables in the opposite order, for good measure
+$dbh->do("CREATE SCHEMA $schema3");
+$dbh->do("CREATE SCHEMA $schema2");
+$dbh->do("SET search_path = $schema2,$schema3");
+for my $s ($schema3, $schema2) {
local $SIG{__WARN__} = sub {};
- $dbh->do('CREATE TABLE dbd_pg_test1 (a INT, b INT NOT NULL, c INT NOT
NULL, '.
+ $dbh->do("CREATE TABLE $s.dbd_pg_test1 (a INT, b INT NOT NULL, c INT
NOT NULL, ".
'CONSTRAINT dbd_pg_test1_pk PRIMARY KEY (a))');
- $dbh->do('ALTER TABLE dbd_pg_test1 ADD CONSTRAINT dbd_pg_test1_uc1
UNIQUE (b)');
- $dbh->do('CREATE UNIQUE INDEX dbd_pg_test1_index_c ON dbd_pg_test1(c)');
+ $dbh->do("ALTER TABLE $s.dbd_pg_test1 ADD CONSTRAINT dbd_pg_test1_uc1
UNIQUE (b)");
+ $dbh->do("CREATE UNIQUE INDEX dbd_pg_test1_index_c ON
$s.dbd_pg_test1(c)");
$dbh->commit();
}
@@ -838,10 +847,10 @@ $sth =
$dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
is ($sth, undef, $t);
## Create a simple foreign key table
-{
+for my $s ($schema3, $schema2) {
local $SIG{__WARN__} = sub {};
- $dbh->do('CREATE TABLE dbd_pg_test2 (f1 INT PRIMARY KEY, f2 INT NOT
NULL, f3 INT NOT NULL)');
- $dbh->do('ALTER TABLE dbd_pg_test2 ADD CONSTRAINT dbd_pg_test2_fk1
FOREIGN KEY(f2) REFERENCES dbd_pg_test1(a)');
+ $dbh->do("CREATE TABLE $s.dbd_pg_test2 (f1 INT PRIMARY KEY, f2 INT NOT
NULL, f3 INT NOT NULL)");
+ $dbh->do("ALTER TABLE $s.dbd_pg_test2 ADD CONSTRAINT dbd_pg_test2_fk1
FOREIGN KEY(f2) REFERENCES $s.dbd_pg_test1(a)");
$dbh->commit();
}
@@ -886,11 +895,11 @@ $sth =
$dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk1 = [
undef, ## Catalog
- $schema, ## Schema
+ $schema2, ## Schema
$table1, ## Table
'a', ## Column
undef, ## FK Catalog
- $schema, ## FK Schema
+ $schema2, ## FK Schema
$table2, ## FK Table
'f2', ## FK Table
1, ## Ordinal position
@@ -929,11 +938,11 @@ $sth =
$dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk2 = [
undef,
- $schema,
+ $schema2,
$table1,
'b',
undef,
- $schema,
+ $schema2,
$table2,
'f3',
'1',
@@ -960,11 +969,11 @@ $sth =
$dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk3 = [
undef,
- $schema,
+ $schema2,
$table1,
'c',
undef,
- $schema,
+ $schema2,
$table2,
'f3',
'1',
@@ -982,10 +991,10 @@ is_deeply ($result, $expected, $t);
## Create another foreign key table to point to the first (primary) table
$t='DB handle method "foreign_key_info" works for multiple fks';
-{
+for my $s ($schema3, $schema2) {
local $SIG{__WARN__} = sub {};
- $dbh->do('CREATE TABLE dbd_pg_test3 (ff1 INT NOT NULL)');
- $dbh->do('ALTER TABLE dbd_pg_test3 ADD CONSTRAINT dbd_pg_test3_fk1
FOREIGN KEY(ff1) REFERENCES dbd_pg_test1(a)');
+ $dbh->do("CREATE TABLE $s.dbd_pg_test3 (ff1 INT NOT NULL)");
+ $dbh->do("ALTER TABLE $s.dbd_pg_test3 ADD CONSTRAINT dbd_pg_test3_fk1
FOREIGN KEY(ff1) REFERENCES $s.dbd_pg_test1(a)");
$dbh->commit();
}
@@ -993,11 +1002,11 @@ $sth =
$dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk4 = [
undef,
- $schema,
+ $schema2,
$table1,
'a',
undef,
- $schema,
+ $schema2,
$table3,
'ff1',
'1',
@@ -1034,11 +1043,11 @@ $result = $sth->fetchall_arrayref();
## "dbd_pg_test2_fk4" FOREIGN KEY (f1, f3, f2) REFERENCES dbd_pg_test1(c, a, b)
my $fk5 = [
undef,
- $schema,
+ $schema2,
$table1,
'c',
undef,
- $schema,
+ $schema2,
$table2,
'f1',
'1',
@@ -1083,12 +1092,15 @@ $result = $sth->fetchrow_hashref();
ok (exists $result->{'FK_TABLE_NAME'}, $t);
# Clean everything up
-{
- $dbh->do('DROP TABLE dbd_pg_test3');
- $dbh->do('DROP TABLE dbd_pg_test2');
- $dbh->do('DROP TABLE dbd_pg_test1');
+for my $s ($schema3, $schema2) {
+ $dbh->do("DROP TABLE $s.dbd_pg_test3");
+ $dbh->do("DROP TABLE $s.dbd_pg_test2");
+ $dbh->do("DROP TABLE $s.dbd_pg_test1");
}
+$dbh->do("DROP SCHEMA $schema2");
+$dbh->do("DROP SCHEMA $schema3");
+$dbh->do("SET search_path = $schema");
#
# Test of the "tables" database handle method
#
--
1.7.1
