Question

Metadata is information about the data in the database. Metadata in SQLite contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well.

Metadata in SQLite can be obtained using the PRAGMA command. SQLite objects may have attributes, which are metadata. Finally, we can also obtain specific metatada from querying the SQLite system sqlite_master table.

Method name	Description
column_info()	Provides information about columns
table_info()	Provides information about tables
primary_key_info()	Provides information about primary keys in tables
foreign_key_info()	Provides information about foreign keys in tables

The above table lists four Perl DBI methods, which are used to retrieve metadata.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:SQLite:dbname=test.db",      
  "",
  "",   
  { RaiseError => 1 } 
) or die $DBI::errstr;

my $sth = $dbh->primary_key_info(undef, "main", "Cars");
my @ary = $sth->fetchrow_array();
    
print join(" ", @ary), "\n";

$sth->finish();
$dbh->disconnect();

In the first example, we will find out information about a primary key in the Cars table.

my $sth = $dbh->primary_key_info(undef, "main", "Cars");

The primary_key_info() returns an active statement handle that can be used to fetch information about columns that make up the primary key for a table.

my @ary = $sth->fetchrow_array();

From the statement handle, we retrieve the information.

$ ./pkinfo.pl
 main Cars Id 1 PRIMARY KEY

From the output we can see that there is a primary key in the Cars table. The primary key is the first column, named Id .

In the next example, we will find out some data about the Cars table.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:SQLite:dbname=test.db",  
  "",
  "",          
  { RaiseError => 1 }
) or die $DBI::errstr;


my $sth = $dbh->prepare( "PRAGMA table_info(Cars)" );  
$sth->execute();
    
my @row;
while (@row = $sth->fetchrow_array()) {
  print "@row\n";
}

$sth->finish();
$dbh->disconnect();

In this example, we issue the PRAGMA table_info(tableName) command, to get some metadata info about our Cars table.

my $sth = $dbh->prepare( "PRAGMA table_info(Cars)" );  
$sth->execute();

The PRAGMA table_info(Cars) command returns one row for each column in the Cars table. Columns in the result set include the column order number, column name, data type, whether or not the column can be NULL , and the default value for the column.

my @row;
while (@row = $sth->fetchrow_array()) {
  print "@row\n";
}

We print the selected data.

$ ./pragma_table.pl 
0 Id INT 0  0
1 Name TEXT 0  0
2 Price INT 0  0

Output of the example.

Next we will print all rows from the Cars table with their column names.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:SQLite:dbname=test.db", 
  "",              
  "",              
  { RaiseError => 1 },     
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 8" );  
my $headers = $sth->{NAME};

my ($id, $name, $price) = @$headers;
printf  "%s %-10s %s\n", $id, $name, $price;

$sth->execute();

my $row;
while($row = $sth->fetchrow_hashref()) {
  printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

$sth->finish();
$dbh->disconnect();

We print the contents of the Cars table to the console. Now, we include the names of the columns too. The records are aligned with the column names.

my $headers = $sth->{NAME};

We get the column names from the statement object.

my ($id, $name, $price) = @$headers;
printf "%s %-10s %s\n", $id, $name, $price;

The column names are printed to the console. We apply some formatting with the printf function.

my $row;
while($row = $sth->fetchrow_hashref()) {
  printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

The data is retrieved, formatted, and printed to the terminal.

$ ./columnheaders.pl
Id Name     Price
 1 Audi     52642
 2 Mercedes   57127
 3 Skoda    9000
 4 Volvo    29000
 5 Bentley  350000
 6 Citroen  21000
 7 Hummer   41400
 8 Volkswagen 21600

Output.

In our last example related to the metadata, we will list all tables in the test.db database.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(      
  "dbi:SQLite:dbname=test.db", 
  "",              
  "",              
  { RaiseError => 1 },     
) or die $DBI::errstr;


my @tables = $dbh->tables(); 

foreach my $table ( @tables ) {
   print "Table: $table\n"; 
}

$dbh->disconnect();

The code example prints all available tables in the current database to the terminal.

my @tables = $dbh->tables();

The table names are retrieved with the tables() method.

$ ./list_tables.pl
Table: "main"."sqlite_master"
Table: "temp"."sqlite_temp_master"
Table: "main"."Cars"
Table: "main"."Friends"
Table: "main"."Images"

These were the tables on our system.

In this part of the SQLite Perl tutorial, we have worked with database metadata.