CODEWITHSUNDEEP
perlsqlitedbi
Pablo Marin-Garcia
Pablo Marin-Garcia

Reputation: 4251

how to obtain a single row per table with Perl $dbh->tables?

I want to retrieve sqlite database schema information as .schema does but with Perl DBI. The first try has been to retrieve table names with $dbh->tables but I obtain the table names repeated. They are one entry for the table itself and one entry for each index that I have (table_a has 3 indexes and b one) what is the rationale behind this?. 

DB<7> x $dbh->tables;
0  '"main"."table_a"'
1  '"main"."table_a"'
2  '"main"."table_a"'
3  '"main"."table_b"'
4  '"main"."sqlite_master"'
5  '"temp"."sqlite_temp_master"'
6  '"main"."table_a"'
7  '"main"."table_b"'

I would appreciate is someone could give a hint for these three related questions: How can obtain only the tables (without doing a uniq) and why there is one row for each index? How can I obtain the indexes info? and all together, how to obtain the equivalent info of .schema?

[upate] I have seen that at DBI 1.627

@names = $dbh->tables;        # deprecated

But it does not mention why.

and they suggest to use 

@names = $dbh->tables( $catalog, $schema, $table, $type );

but after reading the DBI table_info, where this params are explained, I have not fully understood how to populate them for obtaining the info of the tables to obtain the table names only once or if it is possible to have it all, the same info that with .schema.

Any example or link to more detailed usage would be appreciated.

[[UPDATE]]
just for future reference these are the two issues that prevented me to find the answer by myself before asking here:

1- google direct me to a very old DBD-sqlite docs page with almost nothing in it and I did not pay attention to the link to the latest release at the top of the page.

2- Before asking here I read in other threads about table_info (the right answer) but being several years without writing DBI code I fall in one of the newcomers traps. As $dbh->tables returns an array, I did not pay attention that table_info returns a statement handler, so when tried x $dbh->table_info(...) in the debugger and obtained an empty hash I got stuck. I forgot the need to call the fetch part to the $sth returned.

Upvotes: 4

Views: 684

Answers (2)

Pablo Marin-Garcia
Pablo Marin-Garcia

Reputation: 4251

How to list the table names:

my $sth=$dbh->table_info('','main','%', 'TABLE')
my $tables = [map{$_->[2]} @{$sth->fetchall_arrayref()}];
say join("\n", @$tables);

Upvotes: 0

pilcrow
pilcrow

Reputation: 58589

How can obtain only the tables (without doing a uniq)

The $dbh->tables interface is too simple, I'd guess, to represent the complexity of SQL engines and the various drivers. As you noticed, you should use table_info() instead.

In particular, see the DBD::SQLite documentation for table_info() (v1.39 at the time of writing), as DBD docs can tell you what features are supported by the db and driver (e.g., SQLite has no catalogs). Something like this will give you what you want:

use feature qw(say);
...

# Get info on every ('%') TABLE entity in the "main" schema.  Catalog is blank
# b/c DBD::SQLite and/or SQLite itself has no concept of catalogs.
my $sth = $dbh->table_info('', 'main', '%', 'TABLE');

while (my $r = $sth->fetchrow_hashref) {   # outputs:  table_a
  say $r->{TABLE_NAME};                    #           table_b
}

and why there is one row for each index?

Unsure, but probably an accident of tables() simple interface. Again, use table_info() to enumerate tables and table-like objects.

How can I obtain the indexes info?

The experimental statistics_info() method might be supported.

and all together, how to obtain the equivalent info of .schema?

You're in luck. The DBD::SQLite driver supplies a table_info extension field 'sqlite_sql', which for me gives the "CREATE TABLE ..." statements behind each table. (Also what I get from .schema.) Try:

my $sth = $dbh->table_info('', 'main', '%', 'TABLE');

while (my $r = $sth->fetchrow_hashref) {
  say $r->{sqlite_sql};
}

Upvotes: 3

Related Questions