diff options
Diffstat (limited to 't/50_foreign_key_info.t')
| -rw-r--r-- | t/50_foreign_key_info.t | 125 |
1 files changed, 125 insertions, 0 deletions
diff --git a/t/50_foreign_key_info.t b/t/50_foreign_key_info.t new file mode 100644 index 0000000..fffa3d7 --- /dev/null +++ b/t/50_foreign_key_info.t @@ -0,0 +1,125 @@ +#!/usr/bin/perl + +use strict; +BEGIN { + $| = 1; + $^W = 1; +} + +use t::lib::Test; +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 Test::NoWarnings; + +# 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, + 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__ + + +plan tests => @sql_statements + 20; + +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($_->{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($_->{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'); +$fk_data = $sth->fetchall_hashref('FKCOLUMN_NAME'); +for ($fk_data->{songartist}) { + is($_->{KEY_SEQ}, 1, "FK song, key seq 1"); +} +for ($fk_data->{songalbum}) { + is($_->{KEY_SEQ}, 2, "FK song, key seq 2"); +} |