Add Column / Index “If Not Exists” with Perl and SQLite

I recently updated a script that checks Apache httpd process sizes and saves the information to an SQLite database file. As part of some new functionality in the script, I needed to modify the SQLite database to add an additional column and some indexes. When creating new tables, you can use create table if not exists $table (); for example, but the same “if not exists” condition is not available when adding columns or creating indexes.

There are several solutions available to create a similar “if not exists” test for columns and indexes, but all of them (or at least the ones I found) are based on long SQL statements and/or stored procedures. I wanted something more flexible and perl-based, so wrote the following to set hash elements (%dbcol_exists and %dbidx_exists) using column and index names as references. The section that retrieves table / index names from the database and sets the hash elements, has been highlighted in the following snippet of code.

After setting the hash elements, perl can quickly loop through the column and index definition arrays (@dbcol and @dbidx) to create the missing columns and indexes. This snippet of code was written for SQLite, and assumes you’ve already connected to the database using $dbh = DBI->connect();.

# Use an array instead of a hash to keep the column order. Always add new
# columns to the end of the array.

my @dbcol = (
	{ 'name' => 'DateTimeAdded',  'definition' => 'DATE', },
	{ 'name' => 'HttpdRealAvg',   'definition' => 'INTEGER', },
	{ 'name' => 'HttpdSharedAvg', 'definition' => 'INTEGER', },
	{ 'name' => 'HttpdRealTot',   'definition' => 'INTEGER', },
	{ 'name' => 'HttpdRunning',   'definition' => 'INTEGER', },
);

my @dbidx = (
	{ 'name' => 'HttpdRealAvgIdx', 'table' => 'HttpdRealAvg', },
	{ 'name' => 'HttpdRunningIdx', 'table' => 'HttpdRunning', },
); 

# Use hashes to quickly define (and lookup) which tables/indexes already exist.

my %dbcol_exists = ();
my %dbidx_exists = ();

# Get the table and index information as a two-dimensional array, loop through
# each first-dimension array (the array elements are another array describing a
# table or index), and set a hash string using the table / index name (second
# element of second dimension).

for ( @{ $dbh->selectall_arrayref( "PRAGMA TABLE_INFO($dbtable)") } ) { 
	$dbcol_exists{$_->[1]} = 1; };
for ( @{ $dbh->selectall_arrayref( "PRAGMA INDEX_LIST($dbtable)") } ) { 
	$dbidx_exists{$_->[1]} = 1; };

# Create missing columns by looping through the @dbcol array and checking for
# missing %dbcol_exists hash elements (using the column name as the hash
# reference).

for my $col ( @dbcol ) {
	$dbh->do("ALTER TABLE $dbtable ADD COLUMN $col->{'name'} $col->{'definition'}")
		unless ( $dbcol_exists{$col->{'name'}} );
}

# Create missing indexes by looping through the @dbidx array and checking for
# missing %dbidx_exists hash elements (using the index name as the hash
# reference).

for my $idx ( @dbidx ) {
	$dbh->do("CREATE INDEX $idx->{'name'} ON $dbtable ($idx->{'table'})")
		unless ( $dbidx_exists{$idx->{'name'}} );
}

# Use an array instead of a hash to keep the column order. Always add new

# columns to the end of the array.

my @dbcol = (

{ 'name' => 'DateTimeAdded', 'definition' => 'DATE', },

{ 'name' => 'HttpdRealAvg', 'definition' => 'INTEGER', },

{ 'name' => 'HttpdSharedAvg', 'definition' => 'INTEGER', },

{ 'name' => 'HttpdRealTot', 'definition' => 'INTEGER', },

{ 'name' => 'HttpdRunning', 'definition' => 'INTEGER', },

);

my @dbidx = (

{ 'name' => 'HttpdRealAvgIdx', 'table' => 'HttpdRealAvg', },

{ 'name' => 'HttpdRunningIdx', 'table' => 'HttpdRunning', },

);

# Use hashes to quickly define (and lookup) which tables/indexes already exist.

my %dbcol_exists = ();

my %dbidx_exists = ();

# Get the table and index information as a two-dimensional array, loop through

# each first-dimension array (the array elements are another array describing a

# table or index), and set a hash string using the table / index name (second

# element of second dimension).

for ( @{ $dbh->selectall_arrayref( "PRAGMA TABLE_INFO($dbtable)") } ) {

$dbcol_exists{$_->[1]} = 1; };

for ( @{ $dbh->selectall_arrayref( "PRAGMA INDEX_LIST($dbtable)") } ) {

$dbidx_exists{$_->[1]} = 1; };

# Create missing columns by looping through the @dbcol array and checking for

# missing %dbcol_exists hash elements (using the column name as the hash

# reference).

for my $col ( @dbcol ) {

$dbh->do("ALTER TABLE $dbtable ADD COLUMN $col->{'name'} $col->{'definition'}")

unless ( $dbcol_exists{$col->{'name'}} );

}

# Create missing indexes by looping through the @dbidx array and checking for

# missing %dbidx_exists hash elements (using the index name as the hash

# reference).

for my $idx ( @dbidx ) {

$dbh->do("CREATE INDEX $idx->{'name'} ON $dbtable ($idx->{'table'})")

unless ( $dbidx_exists{$idx->{'name'}} );

}

Did you find this post useful? Share it with your circles / friends, or leave a quick note bellow.

Thank you,

js.

Thomas Erskine says:

Saturday, December 15th, 2012 at 7:37 am EDT

Hi. Unfortunately, this (“PRAGMA TABLE_INFO”) is only valid for sqlite. In theory, you could use the DBI method table_info(), but my experience is that it is not widely supported and the support isn’t consistent. If you were use DBIx::Class, you could ask it which columns exist. If there were a consistent set of error codes between drivers, you could try to create the column, and check the error code to decide. I think the best you’ll get at the moment would be to “select * from table” and look at the column names in the result.

Jean-Sebastien Morisset says:

Sunday, December 16th, 2012 at 11:35 am EDT
Thanks for your comment Thomas! I think SHOW COLUMNS FROM {table} with MySQL might return a very similar array. And if you need additional information on the table, besides just the column names, MySQL::TableInfo on CPAN could be a good alternative. js.

Surnia Ulula