view release on metacpan or  search on metacpan

t/03dbmethod.t  view on Meta::CPAN

$t='Calling foreign_key_info does not change pg_expand_array';
is ($dbh->{pg_expand_array}, 0, $t);

## Good primary
$t='DB handle method "foreign_key_info" works for good pk';
$sth = $dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk1 = [
                     $dbh->{pg_db}, ## Catalog
                     $schema2, ## Schema
                     $table1, ## Table
                     'a', ## Column
                     $dbh->{pg_db}, ## FK Catalog
                     $schema2, ## FK Schema
                     $table2, ## FK Table
                     'f2', ## FK Table
                     1, ## Ordinal position
                     3, ## Update rule
                     3, ## Delete rule
                     'dbd_pg_test2_fk1', ## FK name
                     'dbd_pg_test1_pk',  ## UK name
                     '7', ## deferability
                     'PRIMARY', ## unique or primary
                     'int4', ## uk data type
                     'int4'  ## fk data type
                    ];
$expected = [$fk1];
is_deeply ($result, $expected, $t);

## Same with explicit table
$t='DB handle method "foreign_key_info" works for good pk / good fk';
$sth = $dbh->foreign_key_info(undef,undef,$table1,undef,undef,$table2);
$result = $sth->fetchall_arrayref();
is_deeply ($result, $expected, $t);

## Foreign table only
$t='DB handle method "foreign_key_info" works for good fk';
$sth = $dbh->foreign_key_info(undef,undef,undef,undef,undef,$table2);
$result = $sth->fetchall_arrayref();
is_deeply ($result, $expected, $t);

## Add a foreign key to an explicit unique constraint
$t='DB handle method "foreign_key_info" works for good pk / explicit fk';
{
    local $SIG{__WARN__} = sub {};
    $dbh->do('ALTER TABLE dbd_pg_test2 ADD CONSTRAINT dbd_pg_test2_fk2 FOREIGN KEY (f3) '.
                     'REFERENCES dbd_pg_test1(b) ON DELETE SET NULL ON UPDATE CASCADE');
}
$sth = $dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk2 = [
                     $dbh->{pg_db},
                     $schema2,
                     $table1,
                     'b',
                     $dbh->{pg_db},
                     $schema2,
                     $table2,
                     'f3',
                     '1',
                     '0', ## cascade
                     '2', ## set null
                     'dbd_pg_test2_fk2',
                     'dbd_pg_test1_uc1',
                     '7',
                     'UNIQUE',
                     'int4',
                     'int4'
          ];
$expected = [$fk1,$fk2];
is_deeply ($result, $expected, $t);

## Add a foreign key to an implicit unique constraint (a unique index on a column)
$t='DB handle method "foreign_key_info" works for good pk / implicit fk';
{
    local $SIG{__WARN__} = sub {};
    $dbh->do('ALTER TABLE dbd_pg_test2 ADD CONSTRAINT dbd_pg_test2_aafk3 FOREIGN KEY (f3) '.
                     'REFERENCES dbd_pg_test1(c) ON DELETE RESTRICT ON UPDATE SET DEFAULT');
}
$sth = $dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk3 = [
                     $dbh->{pg_db},
                     $schema2,
                     $table1,
                     'c',
                     $dbh->{pg_db},
                     $schema2,
                     $table2,
                     'f3',
                     '1',
                     '4', ## set default
                     '1', ## restrict
                     'dbd_pg_test2_aafk3',
                     undef, ## plain indexes have no named constraint
                     '7',
                     'UNIQUE',
                     'int4',
                     'int4'
          ];
$expected = [$fk3,$fk1,$fk2];
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 $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();
}

$sth = $dbh->foreign_key_info(undef,undef,$table1,undef,undef,undef);
$result = $sth->fetchall_arrayref();
my $fk4 = [
                     $dbh->{pg_db},
                     $schema2,
                     $table1,
                     'a',
                     $dbh->{pg_db},
                     $schema2,