|
Packit |
723767 |
#!/usr/bin/perl
|
|
Packit |
723767 |
|
|
Packit |
723767 |
use strict;
|
|
Packit |
723767 |
BEGIN {
|
|
Packit |
723767 |
$| = 1;
|
|
Packit |
723767 |
$^W = 1;
|
|
Packit |
723767 |
}
|
|
Packit |
723767 |
|
|
Packit |
723767 |
use lib "t/lib";
|
|
Packit |
723767 |
use SQLiteTest;
|
|
Packit |
723767 |
use Test::More;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
BEGIN {
|
|
Packit |
723767 |
use DBD::SQLite;
|
|
Packit |
723767 |
unless ($DBD::SQLite::sqlite_version_number && $DBD::SQLite::sqlite_version_number >= 3006019) {
|
|
Packit |
723767 |
plan skip_all => "this test requires SQLite 3.6.19 and newer";
|
|
Packit |
723767 |
exit;
|
|
Packit |
723767 |
}
|
|
Packit |
723767 |
}
|
|
Packit |
723767 |
|
|
Packit |
723767 |
use Test::NoWarnings;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
# SQL below freely adapted from http://www.sqlite.org/foreignkeys.htm ...
|
|
Packit |
723767 |
# not the best datamodel in the world, but good enough for our tests.
|
|
Packit |
723767 |
|
|
Packit |
723767 |
my @sql_statements = split /\n\n/, <<__EOSQL__;
|
|
Packit |
723767 |
PRAGMA foreign_keys = ON;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
CREATE TABLE artist (
|
|
Packit |
723767 |
artistid INTEGER,
|
|
Packit |
723767 |
artistname TEXT,
|
|
Packit |
723767 |
UNIQUE(artistid)
|
|
Packit |
723767 |
);
|
|
Packit |
723767 |
|
|
Packit |
723767 |
CREATE TABLE editor (
|
|
Packit |
723767 |
editorid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
Packit |
723767 |
editorname TEXT
|
|
Packit |
723767 |
);
|
|
Packit |
723767 |
|
|
Packit |
723767 |
ATTACH DATABASE ':memory:' AS remote;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
CREATE TABLE remote.album (
|
|
Packit |
723767 |
albumartist INTEGER NOT NULL REFERENCES artist(artistid)
|
|
Packit |
723767 |
ON DELETE RESTRICT
|
|
Packit |
723767 |
ON UPDATE CASCADE,
|
|
Packit |
723767 |
albumname TEXT,
|
|
Packit |
723767 |
albumcover BINARY,
|
|
Packit |
723767 |
albumeditor INTEGER NOT NULL REFERENCES editor(editorid),
|
|
Packit |
723767 |
PRIMARY KEY(albumartist, albumname)
|
|
Packit |
723767 |
);
|
|
Packit |
723767 |
|
|
Packit |
723767 |
CREATE TABLE song(
|
|
Packit |
723767 |
songid INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
Packit |
723767 |
songartist INTEGER,
|
|
Packit |
723767 |
songalbum TEXT,
|
|
Packit |
723767 |
songname TEXT,
|
|
Packit |
723767 |
FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
|
|
Packit |
723767 |
);
|
|
Packit |
723767 |
__EOSQL__
|
|
Packit |
723767 |
|
|
Packit |
723767 |
|
|
Packit |
723767 |
plan tests => @sql_statements + 20;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
my $dbh = connect_ok( RaiseError => 1, PrintError => 0, AutoCommit => 1 );
|
|
Packit |
723767 |
my $sth;
|
|
Packit |
723767 |
my $fk_data;
|
|
Packit |
723767 |
my $R = \%DBD::SQLite::db::DBI_code_for_rule;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
ok ($dbh->do($_), $_) foreach @sql_statements;
|
|
Packit |
723767 |
|
|
Packit |
723767 |
$sth = $dbh->foreign_key_info(undef, undef, undef,
|
|
Packit |
723767 |
undef, undef, 'album');
|
|
Packit |
723767 |
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
|
|
Packit |
723767 |
|
|
Packit |
723767 |
for ($fk_data->{albumartist}) {
|
|
Packit |
723767 |
is($_->{PKTABLE_NAME}, "artist" , "FK albumartist, table name");
|
|
Packit |
723767 |
is($_->{PKCOLUMN_NAME}, "artistid", "FK albumartist, column name");
|
|
Packit |
723767 |
is($_->{KEY_SEQ}, 1, "FK albumartist, key seq");
|
|
Packit |
723767 |
is($_->{DELETE_RULE}, $R->{RESTRICT}, "FK albumartist, delete rule");
|
|
Packit |
723767 |
is($_->{UPDATE_RULE}, $R->{CASCADE}, "FK albumartist, update rule");
|
|
Packit |
723767 |
is($_->{UNIQUE_OR_PRIMARY}, 'UNIQUE', "FK albumartist, unique");
|
|
Packit |
723767 |
}
|
|
Packit |
723767 |
for ($fk_data->{albumeditor}) {
|
|
Packit |
723767 |
is($_->{PKTABLE_NAME}, "editor", "FK albumeditor, table name");
|
|
Packit |
723767 |
is($_->{PKCOLUMN_NAME}, "editorid", "FK albumeditor, column name");
|
|
Packit |
723767 |
is($_->{KEY_SEQ}, 1, "FK albumeditor, key seq");
|
|
Packit |
723767 |
# rules are 'NO ACTION' by default
|
|
Packit |
723767 |
is($_->{DELETE_RULE}, $R->{'NO ACTION'}, "FK albumeditor, delete rule");
|
|
Packit |
723767 |
is($_->{UPDATE_RULE}, $R->{'NO ACTION'}, "FK albumeditor, update rule");
|
|
Packit |
723767 |
is($_->{UNIQUE_OR_PRIMARY}, 'PRIMARY', "FK albumeditor, primary");
|
|
Packit |
723767 |
}
|
|
Packit |
723767 |
|
|
Packit |
723767 |
|
|
Packit |
723767 |
$sth = $dbh->foreign_key_info(undef, undef, 'artist',
|
|
Packit |
723767 |
undef, undef, 'album');
|
|
Packit |
723767 |
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
|
|
Packit |
723767 |
is_deeply([keys %$fk_data], ['albumartist'], "FK album with PK, only 1 result");
|
|
Packit |
723767 |
|
|
Packit |
723767 |
|
|
Packit |
723767 |
$sth = $dbh->foreign_key_info(undef, undef, 'foobar',
|
|
Packit |
723767 |
undef, undef, 'album');
|
|
Packit |
723767 |
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
|
|
Packit |
723767 |
is_deeply([keys %$fk_data], [], "FK album with PK foobar, 0 result");
|
|
Packit |
723767 |
|
|
Packit |
723767 |
|
|
Packit |
723767 |
$sth = $dbh->foreign_key_info(undef, undef, undef,
|
|
Packit |
723767 |
undef, 'remote', undef);
|
|
Packit |
723767 |
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
|
|
Packit |
723767 |
is_deeply([sort keys %$fk_data], [qw/albumartist albumeditor/], "FK remote.*, 2 results");
|
|
Packit |
723767 |
|
|
Packit |
723767 |
|
|
Packit |
723767 |
$sth = $dbh->foreign_key_info(undef, 'remote', undef,
|
|
Packit |
723767 |
undef, undef, undef);
|
|
Packit |
723767 |
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
|
|
Packit |
723767 |
is_deeply([sort keys %$fk_data], [qw/songalbum songartist/], "FK with PK remote.*, 2 results");
|
|
Packit |
723767 |
|
|
Packit |
723767 |
|
|
Packit |
723767 |
$sth = $dbh->foreign_key_info(undef, undef, undef,
|
|
Packit |
723767 |
undef, undef, 'song');
|
|
Packit |
723767 |
$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME');
|
|
Packit |
723767 |
for ($fk_data->{songartist}) {
|
|
Packit |
723767 |
is($_->{KEY_SEQ}, 1, "FK song, key seq 1");
|
|
Packit |
723767 |
}
|
|
Packit |
723767 |
for ($fk_data->{songalbum}) {
|
|
Packit |
723767 |
is($_->{KEY_SEQ}, 2, "FK song, key seq 2");
|
|
Packit |
723767 |
}
|