From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on dcvr.yhbt.net X-Spam-Level: X-Spam-Status: No, score=-3.6 required=3.0 tests=AWL,BAYES_00, RCVD_IN_DNSWL_LOW,RCVD_IN_MSPIKE_BL,RCVD_IN_MSPIKE_L5,SPF_HELO_NONE, SPF_PASS shortcircuit=no autolearn=ham autolearn_force=no version=3.4.2 Received: from out03.mta.xmission.com (out03.mta.xmission.com [166.70.13.233]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dcvr.yhbt.net (Postfix) with ESMTPS id E5C321F55B; Fri, 15 May 2020 21:04:28 +0000 (UTC) Received: from in02.mta.xmission.com ([166.70.13.52]) by out03.mta.xmission.com with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1jZhVL-0004Zs-4g; Fri, 15 May 2020 15:04:27 -0600 Received: from ip68-227-160-95.om.om.cox.net ([68.227.160.95] helo=x220.xmission.com) by in02.mta.xmission.com with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.87) (envelope-from ) id 1jZhVG-0002kx-2d; Fri, 15 May 2020 15:04:26 -0600 From: ebiederm@xmission.com (Eric W. Biederman) To: Eric Wong Cc: meta@public-inbox.org References: <87eeyvmx74.fsf@x220.int.ebiederm.org> <20200513193144.GA9299@dcvr> <87ftc3mrq6.fsf@x220.int.ebiederm.org> <20200513221715.GA11718@dcvr> <877dxelmsr.fsf@x220.int.ebiederm.org> Date: Fri, 15 May 2020 16:00:47 -0500 In-Reply-To: <877dxelmsr.fsf@x220.int.ebiederm.org> (Eric W. Biederman's message of "Thu, 14 May 2020 07:32:20 -0500") Message-ID: <87ftc0c3r4.fsf_-_@x220.int.ebiederm.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-XM-SPF: eid=1jZhVG-0002kx-2d;;;mid=<87ftc0c3r4.fsf_-_@x220.int.ebiederm.org>;;;hst=in02.mta.xmission.com;;;ip=68.227.160.95;;;frm=ebiederm@xmission.com;;;spf=neutral X-XM-AID: U2FsdGVkX1975z51Pt5vap+Pbj9VyKzUreFVY1ZJtlU= X-SA-Exim-Connect-IP: 68.227.160.95 X-SA-Exim-Mail-From: ebiederm@xmission.com Subject: [PATCH 1/2] IMAPTracker: Add a helper to track our place in reading imap mailboxes X-SA-Exim-Version: 4.2.1 (built Thu, 05 May 2016 13:38:54 -0600) X-SA-Exim-Scanned: Yes (on in02.mta.xmission.com) List-Id: By tracking which messages in an imap mailbox have been already downloaded they imap messages do not need to be deleted immediately. It is recommended to implement one connection per IMAP mailbox, so this tracker caches the entire url for a mailbox at creation time. If you need to process multiple IMAP mailboxes you will need multiple trackers. Signed-off-by: "Eric W. Biederman" --- lib/PublicInbox/IMAPTracker.pm | 73 ++++++++++++++++++++++++++++++++++ 1 file changed, 73 insertions(+) create mode 100644 lib/PublicInbox/IMAPTracker.pm diff --git a/lib/PublicInbox/IMAPTracker.pm b/lib/PublicInbox/IMAPTracker.pm new file mode 100644 index 000000000000..d445555c0fd9 --- /dev/null +++ b/lib/PublicInbox/IMAPTracker.pm @@ -0,0 +1,73 @@ +# Copyright (C) 2018 all contributors +# License: AGPL-3.0+ + +package PublicInbox::IMAPTracker; +use strict; +use warnings; +use DBI; +use DBD::SQLite; +use PublicInbox::Config; + +sub create_tables ($) +{ + my ($dbh) = @_; + + $dbh->do(<<''); +CREATE TABLE IF NOT EXISTS imap_last ( + url VARCHAR PRIMARY KEY NOT NULL, + uid_validity INTEGER NOT NULL, + uid INTEGER NOT NULL, + UNIQUE (url) +) + +} + + +sub dbh_new ($) +{ + my ($dbname) = @_; + my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", '', '', { + AutoCommit => 1, + RaiseError => 1, + PrintError => 0, + ReadOnly => 0, + sqlite_use_immediate_transaction => 1, + }); + $dbh->{sqlite_unicode} = 1; + $dbh->do('PRAGMA journal_mode = TRUNCATE'); + $dbh->do('PRAGMA cache_size = 80000'); + create_tables($dbh); + $dbh; +} + +sub get_last($) +{ + my ($self) = @_; + my $dbh = $self->{dbh}; + + my $sth = $dbh->prepare_cached(<<'', undef, 1); +SELECT uid_validity, uid FROM imap_last WHERE url = ? + + $sth->execute($self->{url}); + $sth->fetchrow_array; +} + +sub update_last($$$) +{ + my ($self, $validity, $last) = @_; + my $dbh = $self->{dbh}; + my $sth = $dbh->prepare_cached(<<''); +INSERT OR REPLACE INTO imap_last (url, uid_validity, uid) +VALUES (?, ?, ?) + + $sth->execute($self->{url}, $validity, $last); +} + +sub new ($) { + my ($class, $url) = @_; + my $dbname = PublicInbox::Config->config_dir() . "/imap.sqlite3"; + my $dbh = dbh_new($dbname); + bless { dbname => $dbname, dbh => $dbh, url => $url }, $class; +} + +1; -- 2.20.1