#!/usr/bin/perl -w

use strict;

BEGIN {
	$| = 1;
	eval 'use DBI 1.00';
	if ($@ ne '') {
		print "1..0 # SKIP No DBI module\n";
		print "DBI couldn't be loaded, aborting test\n";
		print "Error returned from eval was:\n", $@;
		exit;
	}
	print "1..47\n";
	print "DBI loaded\n";
}

END {
	print "not ok 1\n" unless $::DBIloaded;
}

### DBI->trace(2);
$::DBIloaded = 1;
print "ok 1\n";

my $dir = ( -d './t' ? 't' : '.' );

print "Connect to dbi:XBase:$dir\n";
my $dbh = DBI->connect("dbi:XBase:$dir") or do {
	print $DBI::errstr;
	print "not ok 2\n";
	exit;
};
print "ok 2\n";

my $command = "select ID, MSG from test";
print "Prepare command `$command'\n";
my $sth = $dbh->prepare($command) or do {
	print $dbh->errstr();
	print "not ok 3\n";
	exit;
};
print "ok 3\n";

print "Execute it\n";
$sth->execute() or do {
	print $sth->errstr();
	print "not ok 4\n";
	exit;
};
print "ok 4\n";

print "And get two lines\n";

my @line;

@line = $sth->fetchrow_array();
my $result = join ":", @line;
print "Got: $result\n";
print "not " if $result ne "1:Record no 1";
print "ok 5\n";

@line = $sth->fetchrow_array();
$result = join ":", @line;
print "Got: $result\n";
print "not " if $result ne "3:Message no 3";
print "ok 6\n";

@line = $sth->fetchrow_array();
print "Got empty list\n" unless @line;
print "not " if scalar(@line) != 0;
print "ok 7\n";

my $attrib;
print "Check attributes NAME, TYPE, PRECISION\n";
$attrib = "[@{$sth->{'NAME'}}] [@{$sth->{'TYPE'}}] [@{$sth->{'PRECISION'}}]";
if ($attrib ne '[ID MSG] [2 1] [5 254]') {
	print "Got $attrib\nnot ";
}
print "ok 8\n";

$sth->finish();


$command = "select * from rooms where facility = 'Audio' or roomname > 'B'";
print "Prepare command `$command'\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr();
	print "not ok 9\n";
	exit;
};
print "ok 9\n";

print "Execute it\n";
$sth->execute() or do {
	print $sth->errstr();
	print "not ok 10\n";
	exit;
};
print "ok 10\n";

print "And now get the result\n";

$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}


my $expected_result = '';

while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n$expected_result";
	print "Got:\n$result";
	print "not ";
}
print "ok 11\n";

print "Check attributes NAME, TYPE, PRECISION\n";
$attrib = "[@{$sth->{'NAME'}}] [@{$sth->{'TYPE'}}] [@{$sth->{'PRECISION'}}]";
if ($attrib ne '[ROOMNAME FACILITY] [1 1] [10 10]') {
	print "Got $attrib\nnot ";
}
print "ok 12\n";



$command = "select * from rooms where facility = ? or roomname > ?";
print "Prepare command `$command'\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr();
	print "not ok 13\n";
	exit;
};
print "ok 13\n";

print "Execute it with bind parameters ('Audio', 'B')\n";
$sth->execute('Audio', 'B') or do {
	print $sth->errstr();
	print "not ok 14\n";
	exit;
};
print "ok 14\n";

print "And now get the result\n";

$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}


if ($result ne $expected_result) {
	print "Expected:\n$expected_result";
	print "Got:\n$result";
	print "not ";
}
print "ok 15\n";

$command = "select facility,roomname from rooms where roomname > ? or facility = ? order by roomname";
print "Prepare command\t`$command'\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr();
	print "not ok 16\n";
	exit;
};
print "ok 16\n";

print "Execute it with bind parameters ('F', 'Audio')\n";
$sth->execute('F', 'Audio') or do {
	print $sth->errstr();
	print "not ok 17\n";
	exit;
};
print "ok 17\n";


print "And now get the result\n";

$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}

$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n$expected_result";
	print "Got:\n$result";
	print "not ";
}
print "ok 18\n";


$command = 'select * from rooms where roomname like ?';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 19\n";
	exit;
};
print "ok 19\n";

print "Execute it with parameter '%f%'\n";
$sth->execute('%f%') or do {
	print $dbh->errstr, "not ok 20\n";
	exit;
};
print "ok 20\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n"
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 21\n";


$command = 'select * from rooms where facility like ? and roomname not like ?';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 22\n";
	exit;
};
print "ok 22\n";

print "Execute it with parameters '%o', 'mi%'\n";
$sth->execute('%o', 'mi%') or do {
	print $dbh->errstr, "not ok 23\n";
	exit;
};
print "ok 23\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 24\n";


$command = 'select facility, roomname from rooms where (facility = :fac or
		facility = :fac1) and roomname not like :name';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 25\n";
	exit;
};
print "ok 25\n";

print "Bind named parameters: Film, Main, Bay%\n";
$sth->bind_param(':fac', 'Film');
$sth->bind_param(':fac1', 'Main');
$sth->bind_param(':name', 'Bay%');

$sth->execute or do {
	print $dbh->errstr, "not ok 26\n";
	exit;
};
print "ok 26\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 27\n";

print "Check attributes NAME, TYPE, PRECISION\n";
$attrib = "[@{$sth->{'NAME'}}] [@{$sth->{'TYPE'}}] [@{$sth->{'PRECISION'}}]";
if ($attrib ne '[FACILITY ROOMNAME] [1 1] [10 10]') {
	print "Got $attrib\nnot ";
}
print "ok 28\n";


$command = 'select facility, roomname from rooms where roomname like :bay
	or facility = :film';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 29\n";
	exit;
};
print "ok 29\n";

print "Bind named parameters in execute call\n";
$sth->execute('Bay  _', 'Film') or do {
	print $dbh->errstr, "not ok 30\n";
	exit;
};
print "ok 30\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 31\n";


$command = 'select (id + 9) / 3, msg message, dates as Datum from test where id > 2 + ?';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 32\n";
	exit;
};
print "ok 32\n";

print "Bind -1 (to make it into id > 1)\n";
$sth->execute(-1) or print $sth->errstr, "\nnot ";
print "ok 33\n";


print "Check the names of the fields to return\n";
$expected_result = '(ID+9)/3 message Datum';
$result = "@{$sth->{'NAME'}}";
if ($result ne $expected_result) {
	print "Expected:\n${expected_result}\nGot:\n${result}\nnot ";
}
print "ok 34\n";


print "Fetch the resulting row\n";
$expected_result = '4 Message no 3 19960102';
$result = join ' ', $sth->fetchrow_array;
if ($result ne $expected_result) {
	print "Expected:\n${expected_result}\nGot:\n${result}\nnot ";
}
print "ok 35\n";

$command = 'select * from test order by id';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 36\n";
	exit;
};
print "ok 36\n";

print "Execute it\n";
$sth->execute() or do {
	print $dbh->errstr, "not ok 37\n";
	exit;
};
print "ok 37\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 38\n";

$command = "select facility,roomname from rooms where roomname > ? or facility = ? order by facility DESC, roomname";
print "Prepare command\t`$command'\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr();
	print "not ok 39\n";
	exit;
};
print "ok 39\n";

print "Execute it with bind parameters ('F', 'Audio')\n";
$sth->execute('F', 'Audio') or do {
	print $sth->errstr();
	print "not ok 40\n";
	exit;
};
print "ok 40\n";


print "And now get the result\n";

$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}

$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n$expected_result";
	print "Got:\n$result";
	print "not ";
}
print "ok 41\n";

$command = 'select * from test where msg = ? order by id';
print "Prepare $command\n";
$sth = $dbh->prepare($command) or do {
	print $dbh->errstr, "not ok 42\n";
	exit;
};
print "ok 42\n";

print "Execute it with parameter ('Message no 3')\n";
$sth->execute('Message no 3') or do {
	print $dbh->errstr, "not ok 43\n";
	exit;
};
print "ok 43\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 44\n";

print "Execute it with parameter ('Record no 1')\n";
$sth->execute('Record no 1') or do {
	print $dbh->errstr, "not ok 45\n";
	exit;
};
print "ok 45\n";

print "And now get the result\n";
$result = '';
while (@line = $sth->fetchrow_array()) {
	$result .= "@line\n";
}
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}

if ($result ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n${result}not ";
}
print "ok 46\n";

$command = 'select test.msg from test where test.id = ?';
print "selectrow_array $command with 3\n";
my @data = $dbh->selectrow_array(q!
	select test.msg from test where test.id = ?
	!, {}, 3);
$expected_result = '';
while (<DATA>) {
	last if /^__END_DATA__$/;
	$expected_result .= $_;
}


if ("@data\n" ne $expected_result) {
	print "Expected:\n${expected_result}Got:\n@{data}\nnot ";
}
print "ok 47\n";

$sth->finish();
$dbh->disconnect();

1;

__DATA__
Bay  1 Main
Bay 14 Main
Bay  2 Main
Bay  5 Main
Bay 11 Main
Bay  6 Main
Bay  3 Main
Bay  4 Main
Bay 10 Main
Bay  8 Main
Gigapix Main
Bay 12 Main
Bay 15 Main
Bay 16 Main
Bay 17 Main
Bay 18 Main
Mix A Audio
Mix B Audio
Mix C Audio
Mix D Audio
Mix E Audio
ADR-Foley Audio
Mach Rm Audio
Transfer Audio
Bay 19 Main
Dub Main
Flambe Audio
FILM 1 Film
FILM 2 Film
FILM 3 Film
SCANNING Film
Mix F Audio
Mix G Audio
Mix H Audio
BullPen Film
Celco Film
MacGrfx Main
Mix J Audio
BAY 7 Main
__END_DATA__
Audio ADR-Foley
Film FILM 1
Film FILM 2
Film FILM 3
Audio Flambe
Main Gigapix
Main MacGrfx
Audio Mach Rm
Audio Mix A
Audio Mix B
Audio Mix C
Audio Mix D
Audio Mix E
Audio Mix F
Audio Mix G
Audio Mix H
Audio Mix J
Film SCANNING
Audio Transfer
__END_DATA__
ADR-Foley Audio
Transfer Audio
Flambe Audio
FILM 1 Film
FILM 2 Film
FILM 3 Film
Mix F Audio
MacGrfx Main
__END_DATA__
ADR-Foley Audio
Mach Rm Audio
Transfer Audio
Flambe Audio
__END_DATA__
Main Gigapix
Main Dub
Film FILM 1
Film FILM 2
Film FILM 3
Film SCANNING
Film BullPen
Film Celco
Main MacGrfx
Main AVID
__END_DATA__
Main Bay  1
Main Bay  2
Main Bay  5
Main Bay  6
Main Bay  3
Main Bay  4
Main Bay  8
Film FILM 1
Film FILM 2
Film FILM 3
Film SCANNING
Film BullPen
Film Celco
__END_DATA__
1 Record no 1 This is a memo for record no one  19960813
3 Message no 3 This is a memo for record 3 0 19960102
__END_DATA__
Main Gigapix
Main MacGrfx
Film FILM 1
Film FILM 2
Film FILM 3
Film SCANNING
Audio ADR-Foley
Audio Flambe
Audio Mach Rm
Audio Mix A
Audio Mix B
Audio Mix C
Audio Mix D
Audio Mix E
Audio Mix F
Audio Mix G
Audio Mix H
Audio Mix J
Audio Transfer
__END_DATA__
3 Message no 3 This is a memo for record 3 0 19960102
__END_DATA__
1 Record no 1 This is a memo for record no one  19960813
__END_DATA__
Message no 3
__END_DATA__