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

i want retrieve sqlite database schema information .schema perl dbi. first try has been retrieve table names $dbh->tables obtain table names repeated. 1 entry table , 1 entry each index have (table_a has 3 indexes , b one) 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 appreciate give hint these 3 related questions: how can obtain tables (without doing uniq) , why there 1 row each index? how can obtain indexes info? , together, how obtain equivalent info of .schema?

[upate] have seen @ dbi 1.627

@names = $dbh->tables;        # deprecated 

but not mention why.

and suggest use

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

but after reading dbi table_info, params explained, have not understood how populate them obtaining info of tables obtain table names once or if possible have all, same info .schema.

any example or link more detailed usage appreciated.

[[update]]
future reference these 2 issues prevented me find answer myself before asking here:

1- google direct me old dbd-sqlite docs page nothing in , did not pay attention link latest release @ top of page.

2- before asking here read in other threads table_info (the right answer) being several years without writing dbi code fall in 1 of newcomers traps. $dbh->tables returns array, did not pay attention table_info returns statement handler, when tried x $dbh->table_info(...) in debugger , obtained empty hash got stuck. forgot need call fetch part $sth returned.

how can obtain tables (without doing uniq)

the $dbh->tables interface simple, i'd guess, represent complexity of sql engines , various drivers. noticed, should use table_info() instead.

in particular, see dbd::sqlite documentation table_info() (v1.39 @ time of writing), dbd docs can tell features supported db , driver (e.g., sqlite has no catalogs). give want:

use feature qw(say); ...  # info on every ('%') table entity in "main" schema.  catalog blank # b/c dbd::sqlite and/or sqlite has no concept of catalogs. $sth = $dbh->table_info('', 'main', '%', 'table');  while (my $r = $sth->fetchrow_hashref) {   # outputs:  table_a   $r->{table_name};                    #           table_b } 

and why there 1 row each index?

unsure, accident of tables() simple interface. again, use table_info() enumerate tables , table-like objects.

how can obtain indexes info?

the experimental statistics_info() method might supported.

and together, how obtain equivalent info of .schema?

you're in luck. dbd::sqlite driver supplies table_info extension field 'sqlite_sql', me gives "create table ..." statements behind each table. (also .schema.) try:

my $sth = $dbh->table_info('', 'main', '%', 'table');  while (my $r = $sth->fetchrow_hashref) {   $r->{sqlite_sql}; }