Index: t/16_column_info.t
===================================================================
--- t/16_column_info.t (revision 9042)
+++ t/16_column_info.t (working copy)
@@ -7,11 +7,12 @@
}
use t::lib::Test;
-use Test::More tests => 7;
+use Test::More tests => 12;
use Test::NoWarnings;
my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:',undef,undef,{RaiseError => 1});
+# 1. Create a table
ok( $dbh->do(<<'END_SQL'), 'Created test table' );
CREATE TABLE test (
id INTEGER PRIMARY KEY NOT NULL,
@@ -19,27 +20,63 @@
);
END_SQL
-my $sth = $dbh->column_info(undef,undef,'test',undef);
+# 2. Create a temporary table
+ok( $dbh->do(<<'END_SQL'), 'Created temp test table' );
+ CREATE TEMP TABLE test2 (
+ id INTEGER PRIMARY KEY NOT NULL,
+ flag INTEGER
+ );
+END_SQL
+
+# 3. Attach a memory database
+ok( $dbh->do('ATTACH DATABASE ":memory:" AS db3'), 'ATTACH DATABASE ":memory:" AS db3' );
+
+# 4. 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
+
+# 5. No errors from column_info()
+my $sth = $dbh->column_info(undef, undef, 'test', undef);
is $@, '', 'No error creating the table';
+# 6. Get column information
ok $sth, 'We can get column information';
my %expected = (
- TYPE_NAME => [qw[ INTEGER VARCHAR ]],
- COLUMN_NAME => [qw[ ID NAME ]],
+ TYPE_NAME => [qw( INTEGER VARCHAR )],
+ COLUMN_NAME => [qw( id name )],
);
SKIP: {
- if ($sth) {
- my $info = $sth->fetchall_arrayref({});
+ skip( "The table didn't get created correctly or we can't get column information.", 5 ) unless $sth;
- is( scalar @$info, 2, 'We got information on two columns' );
+ my $info = $sth->fetchall_arrayref({});
+
+ # 7. Found 2 columns
+ is( scalar @$info, 2, 'We got information on two columns' );
- foreach my $item (qw( TYPE_NAME COLUMN_NAME )) {
- my @info = map { uc $_->{$item} } (@$info);
- is_deeply( \@info, $expected{$item}, "We got the right info in $item" );
- };
- } else {
- skip( "The table didn't get created correctly or we can't get column information.", 3 );
+ foreach my $item (qw( TYPE_NAME COLUMN_NAME )) {
+ my @info = map { $_->{$item} } (@$info);
+ is_deeply( \@info, $expected{$item}, "We got the right info in $item" );
}
-};
+
+ $info = $dbh->column_info(undef, undef, 't%', '%a%')->fetchall_arrayref({});
+
+ # 10. Found 3 columns
+ is( scalar @$info, 3, 'We matched information from multiple databases' );
+
+ my @fields = qw( TABLE_SCHEM TYPE_NAME COLUMN_NAME COLUMN_SIZE NULLABLE );
+ my @info = map [ @$_{@fields} ], @$info;
+ my $expected = [
+ [ 'db3', 'CHAR', 'name', 64, 0 ],
+ [ 'main', 'VARCHAR', 'name', 255, 1 ],
+ [ 'temp', 'INTEGER', 'flag', undef, 1 ] # TODO: column_info should always return a valid COLUMN_SIZE
+ ];
+
+ # 11. Correct info retrieved
+ is_deeply( \@info, $expected, 'We got the right info from multiple databases' );
+}
Index: lib/DBD/SQLite.pm
===================================================================
--- lib/DBD/SQLite.pm (revision 9042)
+++ lib/DBD/SQLite.pm (working copy)
@@ -267,11 +267,11 @@
FROM (
SELECT 'main' TABLE_SCHEM, tbl_name, upper(type) TABLE_TYPE, sql
FROM sqlite_master
- WHERE type IN ( 'table','view')
+ WHERE type IN ('table','view')
UNION ALL
SELECT 'temp' TABLE_SCHEM, tbl_name, 'LOCAL TEMPORARY' TABLE_TYPE, sql
FROM sqlite_temp_master
- WHERE type IN ( 'table','view')
+ WHERE type IN ('table','view')
END_SQL
for my $db_name (_attached_database_list($dbh)) {
@@ -306,8 +306,8 @@
if ($table_type !~ /^'.*'$/) {
$table_type = "'" . $table_type . "'";
}
- $table_type_list = join(", ", @ttype_list);
}
+ $table_type_list = join(', ', @ttype_list);
push @where, "TABLE_TYPE IN (\U$table_type_list)" if $table_type_list;
}
$sql .= ' WHERE ' . join("\n AND ", @where ) . "\n" if @where;
@@ -324,7 +324,7 @@
# This is a hack but much simpler than using pragma index_list etc
# also the pragma doesn't list 'INTEGER PRIMARY KEY' autoinc PKs!
my @pk_info;
- my $sth_tables = $dbh->table_info($catalog, $schema, $table, '');
+ my $sth_tables = $dbh->table_info($catalog, $schema, $table, undef);
while ( my $row = $sth_tables->fetchrow_hashref ) {
my $sql = $row->{sqlite_sql} or next;
next unless $sql =~ /(.*?)\s*PRIMARY\s+KEY\s*(?:\(\s*(.*?)\s*\))?/si;
@@ -423,60 +423,106 @@
IS_NULLABLE
);
-# Taken from Fey::Loader::SQLite
sub column_info {
- my($dbh, $catalog, $schema, $table, $column) = @_;
+ my ($dbh, $cat_val, $sch_val, $tbl_val, $col_val) = @_;
- if ( defined $column and $column eq '%' ) {
- $column = undef;
+ if ( defined $col_val and $col_val eq '%' ) {
+ $col_val = undef;
}
- my @cols = ();
- my $position = 0;
- my $sth_columns = $dbh->prepare("PRAGMA table_info('$table')");
- $sth_columns->execute;
- while ( my $col_info = $sth_columns->fetchrow_hashref ) {
- $position++;
- next if defined $column && $column ne $col_info->{name};
+ # Get a list of all tables ordered by TABLE_SCHEM, TABLE_NAME
+ my $sql = <<'END_SQL';
+SELECT TABLE_SCHEM, tbl_name TABLE_NAME
+FROM (
+ SELECT 'main' TABLE_SCHEM, tbl_name
+ FROM sqlite_master
+ WHERE type IN ('table','view')
+UNION ALL
+ SELECT 'temp' TABLE_SCHEM, tbl_name
+ FROM sqlite_temp_master
+ WHERE type IN ('table','view')
+END_SQL
- my %col = (
- TABLE_NAME => $table,
- COLUMN_NAME => $col_info->{name},
- ORDINAL_POSITION => $position,
- );
+ for my $db_name (_attached_database_list($dbh)) {
+ $sql .= <<"END_SQL";
+UNION ALL
+ SELECT '$db_name' TABLE_SCHEM, tbl_name
+ FROM "$db_name".sqlite_master
+ WHERE type IN ('table','view')
+END_SQL
+ }
- my $type = $col_info->{type};
- if ( $type =~ s/(\w+)\((\d+)(?:,(\d+))?\)/$1/ ) {
- $col{COLUMN_SIZE} = $2;
- $col{DECIMAL_DIGITS} = $3;
- }
+ $sql .= <<'END_SQL';
+UNION ALL
+ SELECT 'main' TABLE_SCHEM, 'sqlite_master' tbl_name
+UNION ALL
+ SELECT 'temp' TABLE_SCHEM, 'sqlite_temp_master' tbl_name
+)
+END_SQL
- $col{TYPE_NAME} = $type;
+ my @where;
+ if ( defined $sch_val ) {
+ push @where, "TABLE_SCHEM LIKE '$sch_val'";
+ }
+ if ( defined $tbl_val ) {
+ push @where, "TABLE_NAME LIKE '$tbl_val'";
+ }
+ $sql .= ' WHERE ' . join("\n AND ", @where ) . "\n" if @where;
+ $sql .= " ORDER BY TABLE_SCHEM, TABLE_NAME\n";
+ my $sth_tables = $dbh->prepare($sql) or return undef;
+ $sth_tables->execute or return undef;
- if ( defined $col_info->{dflt_value} ) {
- $col{COLUMN_DEF} = $col_info->{dflt_value}
- }
+ # Taken from Fey::Loader::SQLite
+ my @cols;
+ while ( my ($schema, $table) = $sth_tables->fetchrow_array ) {
+ my $sth_columns = $dbh->prepare(qq{PRAGMA "$schema".table_info("$table")});
+ $sth_columns->execute;
- if ( $col_info->{notnull} ) {
- $col{NULLABLE} = 0;
- $col{IS_NULLABLE} = 'NO';
- } else {
- $col{NULLABLE} = 1;
- $col{IS_NULLABLE} = 'YES';
- }
+ for ( my $position = 0; my $col_info = $sth_columns->fetchrow_hashref; $position++ ) {
+ if ( defined $col_val ) {
+ # This must do a LIKE comparison
+ my $sth = $dbh->prepare("SELECT '$col_info->{name}' LIKE '$col_val'") or return undef;
+ $sth->execute or return undef;
+ # Skip columns that don't match $col_val
+ next unless ($sth->fetchrow_array)[0];
+ }
- foreach my $key ( @COLUMN_INFO ) {
- next if exists $col{$key};
- $col{$key} = undef;
+ my %col = (
+ TABLE_SCHEM => $schema,
+ TABLE_NAME => $table,
+ COLUMN_NAME => $col_info->{name},
+ ORDINAL_POSITION => $position,
+ );
+
+ my $type = $col_info->{type};
+ if ( $type =~ s/(\w+) ?\((\d+)(?:,(\d+))?\)/$1/ ) {
+ $col{COLUMN_SIZE} = $2;
+ $col{DECIMAL_DIGITS} = $3;
+ }
+
+ $col{TYPE_NAME} = $type;
+
+ if ( defined $col_info->{dflt_value} ) {
+ $col{COLUMN_DEF} = $col_info->{dflt_value}
+ }
+
+ if ( $col_info->{notnull} ) {
+ $col{NULLABLE} = 0;
+ $col{IS_NULLABLE} = 'NO';
+ } else {
+ $col{NULLABLE} = 1;
+ $col{IS_NULLABLE} = 'YES';
+ }
+
+ push @cols, \%col;
}
-
- push @cols, \%col;
+ $sth_columns->finish;
}
- $sth_columns->finish;
+ $sth_tables->finish;
my $sponge = DBI->connect("DBI:Sponge:", '','')
or return $dbh->DBI::set_err($DBI::err, "DBI::Sponge: $DBI::errstr");
- my $sth = $sponge->prepare( "column_info $table", {
+ $sponge->prepare( "column_info", {
rows => [ map { [ @{$_}{@COLUMN_INFO} ] } @cols ],
NUM_OF_FIELDS => scalar @COLUMN_INFO,
NAME => [ @COLUMN_INFO ],
@@ -484,8 +530,6 @@
$sponge->err,
$sponge->errstr,
);
-
- return $sth;
}
Index: Changes
===================================================================
--- Changes (revision 9042)
+++ Changes (working copy)
@@ -9,6 +9,8 @@
under a very, very slow (virtual) machine. (ISHIGAKI)
- Added a code to look for a compiler from Module::Install::Can.
(ISHIGAKI)
+ - Fixed $dbh->column_info to work according to the spec in DBI and
+ added support for attached daatabases. (VLYON)
1.26_03 Wed 12 Aug 2009
- Updated to SQLite 3.6.17 (ISHIGAKI)
