The trouble with relational databases is that that they introduce complications to your project. Suddenly the requirements for your script to function properly change from just requiring a working copy of Perl to requiring a database be set up for your application.

This is quite a sudden increase in requirements; It might require a user to go off and fiddle with some permissions, create tables, or otherwise configure their database, ensuring that they don't break anything else that runs on that database server. It requires the user to somehow configure your script with the usernames and passwords that are needed to access the database. In the worst case scenario, the user has to install a database from scratch just to run your application.

All this seems a bit much to ask if all your programs wants is a place to store some data during your quick log progressing run.

This is where DBD::SQLite shines. It's a relational database driver like many of the others, but it ships with the database itself. Installing the module (which isn't too big, the whole distribution is 277KB at the time of writing) installs the database program.

SQLite databases are simply files created in the filesystem. This may come to a shock to traditional database users who are used to their database server hiding their data away somewhere out of sight. This just having the databases in the filesystem can encourage you to quickly create databases as and when you need them and simple delete them (using the unlink command) when you're done with them.

   # use the standard DBI (database interface)
   use DBI;

   # get a handle on our database, which is simply the file
   # "foo.db" in our current directory
   my $dbh = DBI->connect('dbi:SQLite:foo.db');

Since you're often need a database for each run of your script, and you don't want concurrent runs of the program interfering with each other's database it often makes sense to create the database in a separate temp file for each run.

   use File::Temp qw( :POSIX );
   use DBI;
   my $dbh = DBI->connect('dbi:SQLite:' . tmpnam());

Anyway, once you have a database handle you can treat it exactly like you would a normal database. First up you create a table:

  # just do the SQL needed to create a table
  $dbh->do(q{
    CREATE TABLE modules (day        INTEGER PRIMARY KEY,
                          name       TEXT,
                          maintainer TEXT)
  });

Note that the type system with SQLite is a little odd compared to other databases. SQLite doesn't really have types so specifying something as TEXT or INT(10) really won't make much difference, SQLite will accept anything. This is considered a feature rather than a bug - the view being that your database shouldn't really care about the type of data being stored into it.

The type is important when doing comparisons though with SELECT conditions and their like. If you've defined something as TEXT then it is compared with a string comparison (like Perl's eq operator) and if it's created with a number data type it's compared like a number (like Perl's == operator.)

Finally you might notice the INTEGER PRIMARY KEY field. This is an autoinc field. Every time data is added to the database this column will automatically get a new number, starting at 1.

Enough, let's enter some data:

  my $sth = $dbh->prepare(q{
    INSERT INTO modules (name, maintainer) VALUES (?,?)
  });

  # run that insert, with the text to replace the ? with
  $sth->execute("URI::Find",     "ROSCH");
  $sth->execute("IO::AtomicFile","ERYQ");
  $sth->execute("DBD::SQLite",   "MSERGEANT");

Note we don't assign any value to the day column, so it gets assigned NULL. The database will automatically fill that in for us. If we want the value that the database filled in for us we can get at it like so:

  print $dbh->func('last_insert_rowid');

Right, so we've got some data in, now we're ready to search it, right? Well, yes. However, note that if we'd turned autocommit off (enabling the rollback - the undo - feature of the database and making it transactional) we'd want to commit the data at this point.

   # connect to the database with no username or password but
   # set autocommit off so we can use transactions
   my $dbh = DBI->connect('dbi:SQLite:' . tmpnam(), "", "",
                          { AutoCommit => 0 });
	
   # do all the inserts and stuff
   ...

   # finally commit it
   $dbh->commit();

If we'd wanted to undo it instead of commiting it we could have said

   $dbh->rollback();

So, onto the searching:

  # load the module for the ordinate function
  # to make '1' '1st' and '2' '2nd' etc.
  use Lingua::EN::Numbers::Ordinate;

  # find which day someone's modules are shown on
  my $sth2 = $dbh->prepare(q{
    SELECT day, name FROM modules WHERE maintainer = ?
  });

  # look up baud's modules
  $sth2->execute("MSERGEANT");

  # print out each result
  while(my ($day, $name) = $sth2->fetchrow)
  {
    print "$name will be shown on the ".ordinate($day)."\n";
  }