Bug #46831 for DBD-SQLite: table_info schema is incorrect and doesn't work with attached databases

Currently table_info() always returns NULL as the schema, but NULL is not a valid return value for the schema. (See SQL/CLI (ISO/IEC JTC 1/SC 32 N 0595), 6.63 Tables - part 7a & 7b) When a database is attached the attached database's tables are inaccessible through table_info(). I've attached a patch to DBD-SQLite-1.26_01 that fixes this problem, and added another test script t/34_table_info.t to test the table_info() method correctly. 2 Tests break with the applied patch: t/03_create_table.t - Assumes NULL schema (patch attached) t/27_metadata.t - calls primary_key_info() incorrectly (patch attached)

#!/usr/bin/perl use strict; BEGIN { $| = 1; $^W = 1; } use t::lib::Test; use Test::More tests => 18; use Test::NoWarnings; my @schema_info = ( [undef, 'main', undef, undef, undef], [undef, 'temp', undef, undef, undef] ); my @systable_info = ( [undef, 'main', 'sqlite_master', 'SYSTEM TABLE', undef, undef], [undef, 'temp', 'sqlite_temp_master', 'SYSTEM TABLE', undef, undef] ); # Create a database my $dbh = connect_ok(); # Check avalable schemas my $sth = $dbh->table_info('', '%', ''); ok $sth, 'We can get table/schema information'; my $info = $sth->fetchall_arrayref; is_deeply $info, \@schema_info, 'Correct table/schema information'; # Create a table ok( $dbh->do(<<'END_SQL'), 'CREATE TABLE one' ); CREATE TABLE one ( id INTEGER PRIMARY KEY NOT NULL, name CHAR (64) NOT NULL ) END_SQL my $table1_info = [undef, 'main', 'one', 'TABLE', undef, 'CREATE TABLE one ( id INTEGER PRIMARY KEY NOT NULL, name CHAR (64) NOT NULL )']; # Create a temporary table ok( $dbh->do(<<'END_SQL'), 'CREATE TEMP TABLE two' ); CREATE TEMP TABLE two ( id INTEGER NOT NULL, name CHAR (64) NOT NULL ) END_SQL my $table2_info = [undef, 'temp', 'two', 'LOCAL TEMPORARY', undef, 'CREATE TABLE two ( id INTEGER NOT NULL, name CHAR (64) NOT NULL )']; # Attach a memory database ok( $dbh->do('ATTACH DATABASE ":memory:" AS db3'), 'ATTACH DATABASE ":memory:" AS db3' ); # Create a table on the attached database ok( $dbh->do(<<'END_SQL'), 'CREATE TABLE db3.three' ); CREATE TABLE db3.three ( id INTEGER NOT NULL, name CHAR (64) NOT NULL ) END_SQL my $table3_info = [undef, 'db3', 'three', 'TABLE', undef, 'CREATE TABLE three ( id INTEGER NOT NULL, name CHAR (64) NOT NULL )']; # Get table_info for "one" $info = $dbh->table_info(undef, undef, 'one')->fetchall_arrayref; is_deeply $info, [$table1_info], 'Correct table_info for "one"'; # Get table_info for "main"."one" $info = $dbh->table_info(undef, 'main', 'one')->fetchall_arrayref; is_deeply $info, [$table1_info], 'Correct table_info for "main"."one"'; # Get table_info for "two" $info = $dbh->table_info(undef, undef, 'two')->fetchall_arrayref; is_deeply $info, [$table2_info], 'Correct table_info for "two"'; # Get table_info for "temp"."two" $info = $dbh->table_info(undef, 'temp', 'two')->fetchall_arrayref; is_deeply $info, [$table2_info], 'Correct table_info for "temp"."two"'; # Get table_info for "three" $info = $dbh->table_info(undef, undef, 'three')->fetchall_arrayref; is_deeply $info, [$table3_info], 'Correct table_info for "three"'; # Get table_info for "db3"."three" $info = $dbh->table_info(undef, 'db3', 'three')->fetchall_arrayref; is_deeply $info, [$table3_info], 'Correct table_info for "db3"."three"'; # Create another table "one" on the attached database ok( $dbh->do(<<'END_SQL'), 'CREATE TABLE db3.one' ); CREATE TABLE db3.one ( id INTEGER PRIMARY KEY NOT NULL, name CHAR (64) NOT NULL ) END_SQL my $table4_info = [undef, 'db3', 'one', 'TABLE', undef, 'CREATE TABLE one ( id INTEGER PRIMARY KEY NOT NULL, name CHAR (64) NOT NULL )']; # Get table_info for both tables named "one" $info = $dbh->table_info(undef, undef, 'one')->fetchall_arrayref; is_deeply $info, [$table4_info, $table1_info], 'Correct table_info for both tables named "one"'; # Get table_info for the system tables $info = $dbh->table_info(undef, undef, undef, 'SYSTEM TABLE')->fetchall_arrayref; is_deeply $info, \@systable_info, 'Correct table_info for the system tables'; # Get table_info for all tables $info = $dbh->table_info()->fetchall_arrayref; is_deeply $info, [$table2_info, @systable_info, $table4_info, $table3_info, $table1_info], 'Correct table_info for all tables'; #use Data::Dumper; #warn 'Catalog Names', substr Dumper($dbh->table_info('%', '', '')->fetchall_arrayref), 5; #warn 'Schema Names', substr Dumper($dbh->table_info('', '%', '')->fetchall_arrayref), 5; #warn 'Table Types', substr Dumper($dbh->table_info('', '', '', '%')->fetchall_arrayref), 5; #warn 'table_info', substr Dumper($info), 5;

--- DBD-SQLite-1.26_01/lib/DBD/SQLite.pm 2009-05-05 06:44:11.000000000 +0100 +++ DBD-SQLite-new/lib/DBD/SQLite.pm 2009-06-10 20:16:18.000000000 +0100 @@ -133,16 +133,28 @@ return $v; } +sub _attached_database_list { + my $dbh = shift; + my @attached; + + my $sth_databases = $dbh->prepare( 'PRAGMA database_list' ); + $sth_databases->execute; + while ( my $db_info = $sth_databases->fetchrow_hashref ) { + push @attached, $db_info->{name} if $db_info->{seq} >= 2; + } + return @attached; +} + # SQL/CLI (ISO/IEC JTC 1/SC 32 N 0595), 6.63 Tables # Based on DBD::Oracle's -# See also http://www.ch-werner.de/sqliteodbc/html/sqliteodbc_8c.html#a117 +# See also http://www.ch-werner.de/sqliteodbc/html/sqlite3odbc_8c.html#a213 sub table_info { my ($dbh, $cat_val, $sch_val, $tbl_val, $typ_val) = @_; my @where = (); my $sql; - if ( defined($cat_val) && $cat_val eq '%' - && defined($sch_val) && $sch_val eq '' + if ( defined($cat_val) && $cat_val eq '%' + && defined($sch_val) && $sch_val eq '' && defined($tbl_val) && $tbl_val eq '') { # Rule 19a $sql = <<'END_SQL'; SELECT NULL TABLE_CAT @@ -152,21 +164,28 @@ , NULL REMARKS END_SQL } - elsif ( defined($sch_val) && $sch_val eq '%' - && defined($cat_val) && $cat_val eq '' + elsif ( defined($cat_val) && $cat_val eq '' + && defined($sch_val) && $sch_val eq '%' && defined($tbl_val) && $tbl_val eq '') { # Rule 19b $sql = <<'END_SQL'; SELECT NULL TABLE_CAT - , NULL TABLE_SCHEM + , t.tn TABLE_SCHEM , NULL TABLE_NAME , NULL TABLE_TYPE , NULL REMARKS +FROM ( + SELECT 'main' tn + UNION SELECT 'temp' tn END_SQL + for my $db_name (_attached_database_list($dbh)) { + $sql .= " UNION SELECT '$db_name' tn\n"; + } + $sql .= ") t\n"; } - elsif ( defined($typ_val) && $typ_val eq '%' - && defined($cat_val) && $cat_val eq '' - && defined($sch_val) && $sch_val eq '' - && defined($tbl_val) && $tbl_val eq '') { # Rule 19c + elsif ( defined($cat_val) && $cat_val eq '' + && defined($sch_val) && $sch_val eq '' + && defined($tbl_val) && $tbl_val eq '' + && defined($typ_val) && $typ_val eq '%') { # Rule 19c $sql = <<'END_SQL'; SELECT NULL TABLE_CAT , NULL TABLE_SCHEM @@ -187,26 +206,41 @@ FROM ( SELECT NULL TABLE_CAT - , NULL TABLE_SCHEM + , TABLE_SCHEM , tbl_name TABLE_NAME , TABLE_TYPE , NULL REMARKS , sql sqlite_sql FROM ( - SELECT tbl_name, upper(type) TABLE_TYPE, sql + SELECT 'main' TABLE_SCHEM, tbl_name, upper(type) TABLE_TYPE, sql FROM sqlite_master WHERE type IN ( 'table','view') UNION ALL - SELECT tbl_name, 'LOCAL TEMPORARY' TABLE_TYPE, sql + SELECT 'temp' TABLE_SCHEM, tbl_name, 'LOCAL TEMPORARY' TABLE_TYPE, sql FROM sqlite_temp_master WHERE type IN ( 'table','view') +END_SQL + + for my $db_name (_attached_database_list($dbh)) { + $sql .= <<"END_SQL"; +UNION ALL + SELECT '$db_name' TABLE_SCHEM, tbl_name, upper(type) TABLE_TYPE, sql + FROM "$db_name".sqlite_master + WHERE type IN ('table','view') +END_SQL + } + + $sql .= <<'END_SQL'; UNION ALL - SELECT 'sqlite_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql + SELECT 'main' TABLE_SCHEM, 'sqlite_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql UNION ALL - SELECT 'sqlite_temp_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql + SELECT 'temp' TABLE_SCHEM, 'sqlite_temp_master' tbl_name, 'SYSTEM TABLE' TABLE_TYPE, NULL sql ) ) END_SQL + if ( defined $sch_val ) { + push @where, "TABLE_SCHEM LIKE '$sch_val'"; + } if ( defined $tbl_val ) { push @where, "TABLE_NAME LIKE '$tbl_val'"; }

--- DBD-SQLite-1.26_01/t/27_metadata.t 2009-05-05 06:44:11.000000000 +0100 +++ DBD-SQLite-new/t/27_metadata.t 2009-06-10 20:11:30.000000000 +0100 @@ -19,7 +19,7 @@ ok $dbh->do("create table meta3 (f2 char(1), f1 varchar(2) PRIMARY KEY)"); $dbh->trace(0); $DBI::neat_maxlen = 4000; -my $sth = $dbh->primary_key_info('', '', '%'); +my $sth = $dbh->primary_key_info(undef, undef, '%'); ok $sth; my $pki = $sth->fetchall_hashref('TABLE_NAME'); ok $pki; @@ -28,7 +28,7 @@ ok $_->{COLUMN_NAME} eq 'f1' for values %$pki; ok $dbh->do("create table meta4 (f1 varchar(2), f2 char(1), PRIMARY KEY (f1,f2))"); -$sth = $dbh->primary_key_info('', '', 'meta4'); +$sth = $dbh->primary_key_info(undef, undef, 'meta4'); ok $sth; $pki = $sth->fetchall_hashref('COLUMN_NAME'); ok $pki; @@ -37,7 +37,7 @@ ok $pki->{f1}->{KEY_SEQ} == 1; ok $pki->{f2}->{KEY_SEQ} == 2; -my @pk = $dbh->primary_key('','','meta4'); +my @pk = $dbh->primary_key(undef, undef, 'meta4'); ok @pk == 2; ok "@pk" eq "f1 f2";

--- DBD-SQLite-1.26_01/t/03_create_table.t 2009-05-05 06:44:12.000000000 +0100 +++ DBD-SQLite-new/t/03_create_table.t 2009-06-10 18:32:48.000000000 +0100 @@ -25,7 +25,7 @@ # Confirm fix for #34408: Primary key name wrong with newline in CREATE TABLE my $pkh = $dbh->primary_key_info( undef, undef, 'f' ); my @pk = $pkh->fetchall_arrayref(); -is_deeply( \@pk, [ [ [ undef, undef, 'f', 'f1', 1, 'PRIMARY KEY' ] ] ], '->primary_key_info ok' ); +is_deeply( \@pk, [ [ [ undef, 'main', 'f', 'f1', 1, 'PRIMARY KEY' ] ] ], '->primary_key_info ok' ); my $sth = $dbh->prepare("SELECT f.f1, f.* FROM f"); isa_ok( $sth, 'DBI::st' );