Making a list, checking it twice...
Christmas is coming. There aren't many shopping days left, and it's time to get organized when it comes to gift buying!
This year I decided I wanted to create an on-disk database of all the present ideas that I had for people. It's nothing more than a single table database schema that looks like this:
Turning this SQL into a working database is a one liner involving the sqlite3 command line utility that
sqlite3 that ships with OS X.
bash$ sqlite3 xmas.db < xmas.sql
This creates a simple file based sqlite3 database which has more that sufficient performance for my usage, doesn't require installing any long running daemons (like say PostgreSQL or MySQL do), and can be talked to easily to Perl from DBI via the DBD::SQLite module.
Building the Perl ORM
Okay, now we need to be able to talk to it from Perl. While talking via DBI is easy, using a object relational mapper like DBIx::Class allows us to skip the SQL writing entirely.
First we need to write a bunch of Perl classes that handle the mapping. Or rather, we can use the
dbicdump command line utility that ships with DBIx::Class::Schema::Loader to do it all for us:
bash$ dbicdump -o dump_directory=lib XmasList dbi:SQLite:xmas.db
dbicdump command inspects the database and writes out a bunch of files for us:
bash$ find lib lib lib/XmasList lib/XmasList/Result lib/XmasList/Result/Gift.pm lib/XmasList.pm
Now we can easily write a script to put things in the database without having to write any SQL:
Populating Directly From Safari
Of course, writing Perl code to insert stuff into my database is still too much work for the lazy programmer that I am. What I really want is the ability to take what I'm directly looking at in my web browser and put it into the database at the click of a button.
While I can trigger the above script by executing it in the terminal, the really really lazy programmer in me just uses a third party application like KeyboardMaestro to trigger the script from a keyboard shortcut.
Okay, enough work. Let's go shopping!