From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.3.2 (2011-06-06) on dcvr.yhbt.net X-Spam-Level: X-Spam-ASN: AS28753 46.165.192.0/18 X-Spam-Status: No, score=-1.9 required=3.0 tests=AWL,BAYES_00,RCVD_IN_XBL, URIBL_BLOCKED shortcircuit=no autolearn=no version=3.3.2 X-Original-To: meta@public-inbox.org Received: from 80x24.org (tor-exit.dhalgren.org [46.165.230.5]) by dcvr.yhbt.net (Postfix) with ESMTP id 73DAE1F6D4 for ; Tue, 15 Sep 2015 01:08:46 +0000 (UTC) From: Eric Wong To: meta@public-inbox.org Subject: [PATCH 1/5] msgmap: add message mapping via SQLite Date: Tue, 15 Sep 2015 01:08:00 +0000 Message-Id: <20150915010804.20084-2-e@80x24.org> In-Reply-To: <20150915010804.20084-1-e@80x24.org> References: <20150915010804.20084-1-e@80x24.org> List-Id: This will allow us to maintain stable article numbers for an NNTP server independently of Xapian. --- lib/PublicInbox/Msgmap.pm | 137 ++++++++++++++++++++++++++++++++++++++++++++++ t/msgmap.t | 53 ++++++++++++++++++ 2 files changed, 190 insertions(+) create mode 100644 lib/PublicInbox/Msgmap.pm create mode 100644 t/msgmap.t diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm new file mode 100644 index 0000000..a1748af --- /dev/null +++ b/lib/PublicInbox/Msgmap.pm @@ -0,0 +1,137 @@ +# Copyright (C) 2015 all contributors +# License: AGPLv3 or later (https://www.gnu.org/licenses/agpl-3.0.txt) +# bidirectional Message-ID <-> Article Number mapping +package PublicInbox::Msgmap; +use strict; +use warnings; +use fields qw(dbh mid_insert mid_for num_for); +use DBI; +use DBD::SQLite; + +sub new { + my ($class, $git_dir, $writable) = @_; + my $d = "$git_dir/public-inbox"; + if ($writable && !-d $d && !mkdir $d) { + my $err = $!; + -d $d or die "$d not created: $err"; + } + my $f = "$d/msgmap.sqlite3"; + my $dbh = DBI->connect("dbi:SQLite:dbname=$f",'','', { + AutoCommit => 1, + RaiseError => 1, + PrintError => 0, + sqlite_use_immediate_transaction => 1, + }); + $dbh->do('PRAGMA case_sensitive_like = ON'); + + $writable and create_tables($dbh); + my $self = fields::new($class); + $self->{dbh} = $dbh; + $self; +} + +# accessor +sub last_commit { + my ($self, $commit) = @_; + my $dbh = $self->{dbh}; + my $prev; + use constant { + key => 'last_commit', + meta_select => 'SELECT val FROM meta WHERE key = ? LIMIT 1', + meta_update => 'UPDATE meta SET val = ? WHERE key = ? LIMIT 1', + meta_insert => 'INSERT INTO meta (key,val) VALUES (?,?)', + }; + + defined $commit or + return $dbh->selectrow_array(meta_select, undef, key); + + $dbh->begin_work; + eval { + $prev = $dbh->selectrow_array(meta_select, undef, key); + + if (defined $prev) { + $dbh->do(meta_update, undef, $commit, key); + } else { + $dbh->do(meta_insert, undef, key, $commit); + } + $dbh->commit; + }; + return $prev unless $@; + + $dbh->rollback; + die $@; +} + +sub mid_insert { + my ($self, $mid) = @_; + my $dbh = $self->{dbh}; + use constant MID_INSERT => 'INSERT INTO msgmap (mid) VALUES (?)'; + my $sth = $self->{mid_insert} ||= $dbh->prepare(MID_INSERT); + $sth->bind_param(1, $mid); + $sth->execute; + $dbh->last_insert_id(undef, undef, 'msgmap', 'num'); +} + +use constant MID_FOR => 'SELECT mid FROM msgmap WHERE num = ? LIMIT 1'; +sub mid_for { + my ($self, $num) = @_; + my $dbh = $self->{dbh}; + my $sth = $self->{mid_for} ||= $dbh->prepare(MID_FOR); + $sth->bind_param(1, $num); + $sth->execute; + $sth->fetchrow_array; +} + +sub num_for { + my ($self, $mid) = @_; + my $dbh = $self->{dbh}; + use constant NUM_FOR => 'SELECT num FROM msgmap WHERE mid = ? LIMIT 1'; + my $sth = $self->{num_for} ||= $dbh->prepare(NUM_FOR); + $sth->bind_param(1, $mid); + $sth->execute; + $sth->fetchrow_array; +} + +sub mid_prefixes { + my ($self, $pfx, $limit) = @_; + + die "No prefix given" unless (defined $pfx && $pfx ne ''); + $pfx =~ s/([%_])/\\$1/g; + $pfx .= '%'; + + $limit ||= 100; + $limit += 0; # force to integer + $limit ||= 100; + + $self->{dbh}->selectcol_arrayref('SELECT mid FROM msgmap ' . + 'WHERE mid LIKE ? ESCAPE ? ' . + "ORDER BY num DESC LIMIT $limit", + undef, $pfx, '\\'); +} + +sub mid_delete { + my ($self, $mid) = @_; + my $dbh = $self->{dbh}; + use constant MID_DELETE => 'DELETE FROM msgmap WHERE mid = ?'; + my $sth = $dbh->prepare(MID_DELETE); + $sth->bind_param(1, $mid); + $sth->execute; +} + +sub create_tables { + my ($dbh) = @_; + my $e; + + $e = eval { $dbh->selectrow_array('EXPLAIN SELECT * FROM msgmap;') }; + defined $e or $dbh->do('CREATE TABLE msgmap (' . + 'num INTEGER PRIMARY KEY AUTOINCREMENT, '. + 'mid VARCHAR(1000) NOT NULL, ' . + 'UNIQUE (mid) )'); + + $e = eval { $dbh->selectrow_array('EXPLAIN SELECT * FROM meta') }; + defined $e or $dbh->do('CREATE TABLE meta (' . + 'key VARCHAR(32) PRIMARY KEY, '. + 'val VARCHAR(255) NOT NULL)'); +} + +1; diff --git a/t/msgmap.t b/t/msgmap.t new file mode 100644 index 0000000..a34fd71 --- /dev/null +++ b/t/msgmap.t @@ -0,0 +1,53 @@ +# Copyright (C) 2015 all contributors +# License: AGPLv3 or later (https://www.gnu.org/licenses/agpl-3.0.txt) +use strict; +use warnings; +use Test::More; +use File::Temp qw/tempdir/; + +use_ok 'PublicInbox::Msgmap'; +my $tmpdir = tempdir(CLEANUP => 1); +my $d = PublicInbox::Msgmap->new($tmpdir, 1); + +my %mid2num; +my %num2mid; +my @mids = qw(a@b c@d e@f g@h aa@bb aa@cc); +foreach my $mid (@mids) { + my $n = $d->mid_insert($mid); + ok($n, "mid $mid inserted"); + $mid2num{$mid} = $n; + $num2mid{$n} = $mid; +} + +$@ = undef; +eval { $d->mid_insert('a@b') }; +ok($@, 'error raised when attempting duplicate message ID'); + +foreach my $n (keys %num2mid) { + is($d->mid_for($n), $num2mid{$n}, "num:$n maps correctly"); +} +foreach my $mid (@mids) { + is($d->num_for($mid), $mid2num{$mid}, "mid:$mid maps correctly"); +} + +is_deeply($d->mid_prefixes('a'), [qw(aa@cc aa@bb a@b)], "mid_prefixes match"); +is_deeply($d->mid_prefixes('A'), [], "mid_prefixes is case sensitive"); + +is(undef, $d->last_commit, "last commit not set"); +my $lc = 'deadbeef' x 5; +is(undef, $d->last_commit($lc), 'previous last commit (undef) returned'); +is($lc, $d->last_commit, 'last commit was set correctly'); + +my $nc = 'deaddead' x 5; +is($lc, $d->last_commit($nc), 'returned previously set commit'); +is($nc, $d->last_commit, 'new commit was set correctly'); + +is($d->mid_delete('a@b'), 1, 'deleted a@b'); +is($d->mid_delete('a@b') + 0, 0, 'delete again returns zero'); +is(undef, $d->num_for('a@b'), 'num_for fails on deleted msg'); +$d = undef; + +# idempotent +ok(PublicInbox::Msgmap->new($tmpdir, 1), 'idempotent DB creation'); + +done_testing(); -- EW