Index: /trunk/BookmarkApp.pm
===================================================================
--- /trunk/BookmarkApp.pm	(revision 14)
+++ /trunk/BookmarkApp.pm	(revision 15)
@@ -6,5 +6,4 @@
 use CGI::Application::Plugin::TT;
 
-use DBI;
 use Encode;
 use JSON;
@@ -18,8 +17,7 @@
 $base_uri->path($ENV{SCRIPT_NAME} . '/');
 
-my $dbname = 'new.db';
-my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "", { RaiseError => 1 });
+my $dbname = 'fk.db';
 my $bookmarks = Bookmarks->new({
-    dbh => $dbh,
+    dbname   => $dbname,
     base_uri => $base_uri->canonical,
 });
Index: /trunk/Bookmarks.pm
===================================================================
--- /trunk/Bookmarks.pm	(revision 14)
+++ /trunk/Bookmarks.pm	(revision 15)
@@ -1,10 +1,27 @@
 package Bookmarks;
 
-use Class::Accessor 'antlers';
+use Moose;
 use SQL::Interp qw{:all};
 use Bookmark;
 
-has dbh      => ( is => 'ro' );
+has dbh      => ( is => 'rw' );
 has base_uri => ( is => 'ro' );
+
+sub BUILD {
+    my $self = shift;
+    my $args = shift;
+
+    if (!$self->dbh) {
+        if ($args->{dbname}) {
+            require DBI;
+            $self->dbh(DBI->connect("dbi:SQLite:dbname=$$args{dbname}", "", "", { RaiseError => 1, PrintError => 0 }));
+            # enable foreign key support (requires DBD::SQLite 1.26_05 or above (sqlite 3.6.19 or above))
+            $self->dbh->do('pragma foreign_keys = on;');
+        } else {
+            #TODO: figure out how to make croak play nice with Moose to get the user-visible caller line
+            die "No dbh or dbname specified in the constructor";
+        }
+    }
+}
 
 sub get_bookmark {
@@ -91,5 +108,7 @@
     my $title = $bookmark->{title};
     #TODO: accept a ctime or mtime
-    my $mtime = my $ctime = $bookmark->{ctime} || time;
+    my $ctime = $bookmark->{ctime} || time;
+    my $mtime = $bookmark->{mtime} || $ctime;
+    my $id = $bookmark->{id};
 
     # create an entry for the resource
@@ -110,7 +129,10 @@
 
     # create the bookmark
-    my $sth_bookmark = $self->dbh->prepare('insert into bookmarks (uri, ctime, mtime) values (?, ?, ?)');
+    my ($sql_bookmark, @bind_bookmark) = sql_interp(
+        'insert into bookmarks', { ($id ? (id => $id) : ()), uri => $uri, ctime => $ctime, mtime => $mtime }
+    );
+    my $sth_bookmark = $self->dbh->prepare($sql_bookmark);
     eval {
-        $sth_bookmark->execute($uri, $ctime, $mtime);
+        $sth_bookmark->execute(@bind_bookmark);
     };
     if ($@) {
Index: /trunk/bkmk
===================================================================
--- /trunk/bkmk	(revision 14)
+++ /trunk/bkmk	(revision 15)
@@ -2,14 +2,16 @@
 use strict;
 
-use DBI;
 use YAML;
 use Bookmarks;
+use Getopt::Long;
 
-my $dbname = 'new.db';
+GetOptions(
+    'file|f=s' => \my $DBNAME,
+);
 
-my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "", { RaiseError => 1, PrintError => 0 });
+die "Usage: $0 --file <dbname> <command>\n" unless $DBNAME;
 
 my $bookmarks = Bookmarks->new({
-    dbh => $dbh,
+    dbname => $DBNAME,
 });
 
@@ -34,5 +36,12 @@
         # TODO: coordinate this commandline script with the CGI app
         print Dump(\@resources);
-    }
+    },
+    load => sub {
+        my ($src_file) = @_;
+        my $src_bookmarks = YAML::LoadFile($src_file);
+        for my $bookmark (@{ $src_bookmarks }) {
+            $bookmarks->add($bookmark);
+        }
+    },
 );
 
Index: /trunk/bookmarks.sql
===================================================================
--- /trunk/bookmarks.sql	(revision 14)
+++ /trunk/bookmarks.sql	(revision 15)
@@ -8,5 +8,5 @@
 create table bookmarks (
     id integer primary key,
-    uri varchar,
+    uri varchar references resources(uri) on update cascade,
     ctime integer, -- creation time of the bookmark
     mtime integer,  -- modification time of the bookmark
@@ -34,5 +34,5 @@
 -- TODO: machine-tag style tags? e.g. format:video or creator:NASA; implicit tag prefix is "subject:"
 create table tags (
-    uri varchar,
+    uri varchar references resources(uri) on update cascade,
     tag varchar,
     constraint unique_tag primary key (uri, tag)