Nothing to see here. Move along please

Using SQLite triggers to increment a count

Posted on October 6, 2011. Filed under: Uncategorized | Tags: author:briang, perl, sql, triggers |

I want to apply tags to urls and keep a count of how many times each tag has been used. Here’s some code that does just that:

use DBI;
use Text::Table;

my $FILENAME = 'play.sqlite';

unlink $FILENAME;

my $dsn = "dbi:SQLite:database=$FILENAME";
my $dbh = DBI->connect($dsn) or die;
$dbh->do($_) or die "$DBI::errstr for $_"
  for split /^=+$/m, join '', ;

my $sql = "insert into tags (tag) values(?)";
my $sth = $dbh->prepare($sql) or die;
$sth->execute($_) or die for qw/one two three/;

$sql = "insert into urls (url) values(?)";
$sth = $dbh->prepare($sql) or die;
$sth->execute("site $_") or die for qw/a b c d e f/;

$sql = "insert into tagged (url_id, tag_id) values(?, ?)";
$sth = $dbh->prepare($sql) or die;
$sth->execute(@$_) or die for [2,1], [3,1], [1,2];

for my $t (qw/urls tags tagged/) {
    say "\n=== \U$t ===";
    $sth = $dbh->column_info(undef, undef, $t, undef);
    my @cols;
    while (my $c = $sth->fetchrow_hashref) {
        push @cols, $c->{COLUMN_NAME};
    }

    my $tab = Text::Table->new(@cols);

    $sth = $dbh->prepare("select * from $t");
    $sth->execute;
    while (my $c = $sth->fetchrow_arrayref) {
        $tab->add(@$c)
    }

    print $tab;
}

__DATA__

And, here is the SQL that goes in the __DATA__ section:

CREATE TABLE tags (
  id          INTEGER PRIMARY KEY,
  tag         TEXT    NOT NULL UNIQUE,
  count       INTEGER DEFAULT 0
);
===
CREATE TABLE urls (
  id    INTEGER PRIMARY KEY,
  url   TEXT    NOT NULL UNIQUE
);
===
CREATE TABLE tagged (
  id     INTEGER PRIMARY KEY,
  tag_id INTEGER REFERENCES tags(id),
  url_id INTEGER REFERENCES urls(id),
  unique( tag_id, url_id )
);
===
CREATE TRIGGER inc_tag_count
AFTER INSERT ON tagged
BEGIN
  UPDATE tags
  SET    count = count + 1
  WHERE  id = new.tag_id;
END;
===
CREATE TRIGGER dec_tag_count
AFTER DELETE ON tagged
BEGIN
  UPDATE tags
  SET    count = count - 1
  WHERE  id = new.tag_id;
END;

This results in this output

=== URLS ===
id url
1  site a
2  site b
3  site c
4  site d
5  site e
6  site f

=== TAGS ===
id tag   count
1  one   2
2  two   1
3  three 0

=== TAGGED ===
id tag_id url_id
1  1      2
2  1      3
3  2      1