view release on metacpan or  search on metacpan

t/50_foreign_key_info.t  view on Meta::CPAN

use strict;
use warnings;
use lib "t/lib";
use SQLiteTest;
use Test::More;

BEGIN {
    use DBD::SQLite;
    unless ($DBD::SQLite::sqlite_version_number && $DBD::SQLite::sqlite_version_number >= 3006019) {
        plan skip_all => "this test requires SQLite 3.6.19 and newer";
        exit;
    }
}

use if -d ".git", "Test::FailWarnings";

# SQL below freely adapted from http://www.sqlite.org/foreignkeys.htm ...
# not the best datamodel in the world, but good enough for our tests.

my @sql_statements = split /\n\n/, <<__EOSQL__;
PRAGMA foreign_keys = ON;

CREATE TABLE artist (
  artistid    INTEGER,
  artistname  TEXT,
  UNIQUE(artistid)
);

CREATE TABLE editor (
  editorid    INTEGER PRIMARY KEY AUTOINCREMENT,
  editorname  TEXT
);

ATTACH DATABASE ':memory:' AS remote;

CREATE TABLE remote.album (
  albumartist INTEGER NOT NULL REFERENCES artist(artistid)
                                 ON DELETE RESTRICT
                                 ON UPDATE CASCADE DEFERRABLE,
  albumname TEXT,
  albumcover BINARY,
  albumeditor INTEGER NOT NULL REFERENCES editor(editorid),
  PRIMARY KEY(albumartist, albumname)
);

CREATE TABLE song(
  songid     INTEGER PRIMARY KEY AUTOINCREMENT,
  songartist INTEGER,
  songalbum  TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);
__EOSQL__

my $dbh = connect_ok( RaiseError => 1, PrintError => 0, AutoCommit => 1 );
my $sth;
my $fk_data;
my $R = \%DBD::SQLite::db::DBI_code_for_rule;

ok ($dbh->do($_), $_) foreach @sql_statements;

$sth = $dbh->foreign_key_info(undef, undef, undef,
                              undef, undef, 'album');
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');

for ($fk_data->{albumartist}) {
  is($_->{PKTABLE_NAME},  "artist"  ,   "FK albumartist, table name");
  is($_->{PKCOLUMN_NAME}, "artistid",   "FK albumartist, column name");
  is($_->{KEY_SEQ},        1,           "FK albumartist, key seq");
  is($_->{DELETE_RULE}, $R->{RESTRICT}, "FK albumartist, delete rule");
  is($_->{UPDATE_RULE}, $R->{CASCADE},  "FK albumartist, update rule");
  is($_->{DEFERRABILITY}, $R->{'INITIALLY IMMEDIATE'}, "FK albumartist, deferrability");
  is($_->{UNIQUE_OR_PRIMARY}, 'UNIQUE', "FK albumartist, unique");
}
for ($fk_data->{albumeditor}) {
  is($_->{PKTABLE_NAME},  "editor",   "FK albumeditor, table name");
  is($_->{PKCOLUMN_NAME}, "editorid", "FK albumeditor, column name");
  is($_->{KEY_SEQ},        1,         "FK albumeditor, key seq");
  # rules are 'NO ACTION' by default
  is($_->{DELETE_RULE}, $R->{'NO ACTION'}, "FK albumeditor, delete rule");
  is($_->{UPDATE_RULE}, $R->{'NO ACTION'}, "FK albumeditor, update rule");
  is($_->{DEFERRABILITY}, $R->{'NOT DEFERRABLE'}, "FK albumeditor, deferrability");
  is($_->{UNIQUE_OR_PRIMARY}, 'PRIMARY', "FK albumeditor, primary");
}

$sth = $dbh->foreign_key_info(undef, undef, 'artist',
                              undef, undef, 'album');
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
is_deeply([keys %$fk_data], ['albumartist'], "FK album with PK, only 1 result");

$sth = $dbh->foreign_key_info(undef, undef, 'foobar',
                              undef, undef, 'album');
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
is_deeply([keys %$fk_data], [], "FK album with PK foobar, 0 result");

$sth = $dbh->foreign_key_info(undef, undef, undef,
                              undef, 'remote', undef);
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
is_deeply([sort keys %$fk_data], [qw/albumartist albumeditor/], "FK remote.*, 2 results");

$sth = $dbh->foreign_key_info(undef, 'remote', undef,
                              undef, undef, undef);
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
is_deeply([sort keys %$fk_data], [qw/songalbum songartist/], "FK with PK remote.*, 2 results");

$sth = $dbh->foreign_key_info(undef, undef, undef,
                              undef, undef, 'song');