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.
