Perl - Get the structure of a sqlite database using DBI

Question

I need to test the structure of my SQLite database which is composed by a unique table with let's say 2 columns (id, name). I can't figure out the SQL query to get the table schema of my database.

I am able to get all the content of the database using the DBI method selectall_arrayref(). However it only returns an array containing the values inside my database. This information is useful but I would like to have a SQL query which returns something like id, name (Basically, the table schema).

I tried the following queries : SHOW COLUMNS FROM $tablename but also SELECT * from $tablename (This one returns all the table content).

Here is my implementation so far :

# database path
my $db_path   = "/my/path/to/.database.sqlite";
my $tablename = "table_name";

sub connect_to_database {

    # Connect to the database
    my $dbh = DBI->connect ("dbi:SQLite:dbname=$db_path", "", "",
                            { RaiseError => 1, AutoCommit => 0 },
                           )
    or confess $DBI::errstr;
    return $dbh;
}

sub get_database_structure {

    # Connect to the database
    my $dbh = &connect_to_database();

    # Get the structure of the database
    my $sth = $dbh->prepare("SHOW COLUMNS FROM $tablename");
    $sth->execute();
    while (my $inphash = $sth->fetrow_hashref()) {
        print $inphash."\n";
    }

    # Disconnect from the database
    $dbh->disconnect();
}

# Call the sub to print the database structure
&get_database_structure();

I expect the output to be the structure of my table so id, name but I raise an error : DBD::SQLite::db prepare failed: near "SHOW": syntax error

I can't find the good query. Any comments or help would be greatly appreciated.

Thanks !

`SHOW` is not a sqlite-dialect SQL command. You want the `table_info` pragma: https://www.sqlite.org/pragma.html#pragma_table_info — Shawn, Mar 28 '19 at 14:06
Do you want the `CREATE` statements, or do you want the column headers? DBI has a built-in way to produce those from the statement handle. — simbabque, Mar 28 '19 at 14:30
It seems like https://metacpan.org/pod/DBI#NAME1 is not implemented by dbd::SQLite. I get `"bright_yellow" out when I use it. — simbabque, Mar 28 '19 at 14:41
I tried to use the table_info method (https://metacpan.org/pod/DBI#table_info) but it doesn't return what I expect. — AntMau, Mar 28 '19 at 15:00

score 3 · Answer 1 · answered Mar 28 '19 at 17:33

What you're looking for is really just the SQL lite query for the table and column information. This answer SQLite Schema Information Metadata has the full details if this query doesn't work for you, but under the assumption you're using whatever the 'recent' version mentioned in one of the answers is, you can do something like this:

# Get the structure of the database
my $sth = $dbh->prepare("<<END_SQL");
SELECT 
  m.name as table_name, 
  p.name as column_name
FROM sqlite_master AS m
JOIN pragma_table_info(m.name) AS p
ORDER BY m.name, p.cid
END_SQL
$sth->execute();
my $last = '';
while (my $row = $sth->fetchrow_arrayref()) {
    my ($table, $column) = @$row;
    if ($table ne $last) {
        print "=== $table ===\n";
        $last = $table;
    }
    print "$column\n";
}

score 1 · Accepted Answer · answered Mar 28 '19 at 18:55

After digging through the community answers I finally find a solution using the pragma table_info.

sub get_database_structure {

    # Connect to the database
    my $dbh = &connect_to_database ();

    # Return the structure of the table execution_host
    my $sth = $dbh->prepare('pragma table_info(execution_host)');
    $sth->execute();
    my @struct;
    while (my $row = $sth->fetchrow_arrayref()) {
        push @struct, @$row[1];
    }

    # Disconnect from the database
    $dbh->disconnect ();

    return @struct;
}

It returns a list of the columns name present in the table execution_host.

Thanks for the help !

Perl - Get the structure of a sqlite database using DBI

2 Answers2