Using SQLite triggers to increment a count

Posted on October 6, 2011. Filed under: Uncategorized | Tags: , , , |

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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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 '', <data>;
 
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__
</data>

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
=== 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

Liked it here?
Why not try sites on the blogroll...