Creating an SQLite database with the OrLite module

[d5e5]

Someone mentioned in another thread they had found and downloaded OrLite, presumably to consider using it to access data in an SQLite database. Since, to use OrLite you need to have the DBI and DBD::SQLite modules installed on your computer as well, I think you could just use DBI to accomplish whatever you want to do with SQLite without bothering with OrLite. Nevertheless, some people may prefer the OrLite way of doing it. Unfortunately when you search for examples of how to use OrLite you may not find nearly as many as when looking for examples of using DBI, so if you like to have lots of examples available you may prefer sticking with DBI.

The following example creates an SQLite database in a folder, creates a table, populates it with data, then does a simple select of all the data. If the folder, database and table already exist, more data is inserted into the existing table.

#!/usr/bin/perl
use strict;
use warnings;
use ORLite {
     package      => 'Foo::Bar',
     file         => 'data/OrLiteDemo.db',
     user_version => 0,
     create       => sub {
         my $dbh = shift;
         $dbh->do('CREATE TABLE inventory ( item TEXT NOT NULL, quantity INT );')},
     cleanup      => 'VACUUM',
};

while (<DATA>){
    my @fields = split ',';
    my ($itm, $qty) = ($fields[0], $fields[1]);
    Foo::Bar->do(
      'insert into inventory (item, quantity) values (?, ?)',
      {},
      $itm, $qty,
  );
}

my @stock = Foo::Bar::Inventory->select;

foreach (@stock){
    printf("%s\t%d\n", ($_->item, $_->quantity));
}
__DATA__
hammers,1
nails,153
screwdrivers,7
saws,45
bolts,23
nuts,23

This gives the following output (twice as many records in database because I ran the script twice):

hammers	1
nails	153
screwdrivers	7
saws	45
bolts	23
nuts	23
hammers	1
nails	153
screwdrivers	7
saws	45
bolts	23
nuts	23

[d5e5]

If you replace the cleanup => 'VACUUM', option in the above script with the prune => 1, option a neat thing happens. The folder, database and table are created if they don't already exist, then before the script terminates the folder and database are deleted. That might come in handy if you wanted to load a large amount of data in order to take advantage of various database query operations but did not want to save the data as a database. (The 'VACUUM' option condenses the database by reclaiming unused space, which you don't need to do if you prune it.)

In order for the prune option to work properly (i.e. delete folder as well as file), you need to have the File::Remove module installed. (Search for libfile-remove-perl in Synaptic Package Manager.)