#!/usr/bin/perl

use strict;

BEGIN {

	$|  = 1;

	$^W = 1;

}

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 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");

}