1 # Copyright (C) all contributors <meta@public-inbox.org>
2 # License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>
4 # represents an POP3D (currently a singleton)
5 package PublicInbox::POP3D;
7 use parent qw(PublicInbox::Lock);
8 use DBI qw(:sql_types); # SQL_BLOB
10 use File::Temp 0.19 (); # 0.19 for ->newdir
11 use PublicInbox::Config;
12 use PublicInbox::POP3;
13 use PublicInbox::Syscall;
14 use File::Temp 0.19 (); # 0.19 for ->newdir
16 use Fcntl qw(F_SETLK F_UNLCK F_WRLCK SEEK_SET);
19 my ($cls, $pi_cfg) = @_;
20 $pi_cfg //= PublicInbox::Config->new;
21 my $d = $pi_cfg->{'publicinbox.pop3state'} //
22 die "publicinbox.pop3state undefined\n";
25 File::Path::make_path($d, { mode => 0700 });
26 PublicInbox::Syscall::nodatacow_dir($d);
32 lock_path => "$d/db.lock", # PublicInbox::Lock to protect SQLite
33 # interprocess lock is the $pop3state/txn.locks file
34 # txn_locks => {}, # intraworker locks
35 # accept_tls => { SSL_server => 1, ..., SSL_reuse_ctx => ... }
39 sub refresh_groups { # PublicInbox::Daemon callback
40 my ($self, $sig) = @_;
41 # TODO share pi_cfg with nntpd/imapd inside -netd
42 my $new = PublicInbox::Config->new;
43 my $old = $self->{pi_cfg};
44 my $s = 'publicinbox.pop3state';
45 $new->{$s} //= $old->{$s};
46 if ($new->{$s} ne $old->{$s}) {
48 $s changed: `$old->{$s}' => `$new->{$s}', config reload ignored
51 $self->{pi_cfg} = $new;
56 sub create_state_tables ($$) {
57 my ($self, $dbh) = @_;
59 $dbh->do(<<''); # map publicinbox.<name>.newsgroup to integers
60 CREATE TABLE IF NOT EXISTS newsgroups (
61 newsgroup_id INTEGER PRIMARY KEY NOT NULL,
62 newsgroup VARBINARY NOT NULL,
65 # the $NEWSGROUP_NAME.$SLICE_INDEX is part of the POP3 username;
66 # POP3 has no concept of folders/mailboxes like IMAP/JMAP
68 CREATE TABLE IF NOT EXISTS mailboxes (
69 mailbox_id INTEGER PRIMARY KEY NOT NULL,
70 newsgroup_id INTEGER NOT NULL REFERENCES newsgroups,
71 slice INTEGER NOT NULL, /* -1 for most recent slice */
72 UNIQUE (newsgroup_id, slice) )
74 $dbh->do(<<''); # actual users are differentiated by their UUID
75 CREATE TABLE IF NOT EXISTS users (
76 user_id INTEGER PRIMARY KEY NOT NULL,
77 uuid VARBINARY NOT NULL,
78 last_seen INTEGER NOT NULL, /* to expire idle accounts */
81 # we only track the highest-numbered deleted message per-UUID@mailbox
83 CREATE TABLE IF NOT EXISTS deletes (
84 txn_id INTEGER PRIMARY KEY NOT NULL, /* -1 == txn lock offset */
85 user_id INTEGER NOT NULL REFERENCES users,
86 mailbox_id INTEGER NOT NULL REFERENCES mailboxes,
87 uid_dele INTEGER NOT NULL DEFAULT -1, /* IMAP UID, NNTP article */
88 UNIQUE(user_id, mailbox_id) )
94 my $f = "$self->{pi_cfg}->{'publicinbox.pop3state'}/db.sqlite3";
97 open my $fh, '+>>', $f or Carp::croak "open($f): $!";
98 PublicInbox::Syscall::nodatacow_fh($fh);
101 my $dbh = DBI->connect("dbi:SQLite:dbname=$f",'','', {
105 sqlite_use_immediate_transaction => 1,
106 sqlite_see_if_its_a_number => 1,
108 $dbh->do('PRAGMA journal_mode = WAL') if $creat;
109 $dbh->do('PRAGMA foreign_keys = ON'); # don't forget this
111 # ensure the interprocess fcntl lock file exists
112 $f = "$self->{pi_cfg}->{'publicinbox.pop3state'}/txn.locks";
113 open my $fh, '+>>', $f or Carp::croak("open($f): $!");
114 $self->{txn_fh} = $fh;
116 create_state_tables($self, $dbh);
121 my ($self, $pop3) = @_; # pop3 - PublicInbox::POP3 client object
122 my $lk = $self->lock_for_scope; # lock the SQLite DB, only
123 my $dbh = $self->{-state_dbh} //= state_dbh_new($self);
124 my ($user_id, $ngid, $mbid, $txn_id);
125 my $uuid = delete $pop3->{uuid};
128 # 1. make sure the user exists, update `last_seen'
129 my $sth = $dbh->prepare_cached(<<'');
130 INSERT OR IGNORE INTO users (uuid, last_seen) VALUES (?,?)
132 $sth->bind_param(1, $uuid, SQL_BLOB);
133 $sth->bind_param(2, time);
134 if ($sth->execute == 0) { # existing user
135 $sth = $dbh->prepare_cached(<<'', undef, 1);
136 SELECT user_id FROM users WHERE uuid = ?
138 $sth->bind_param(1, $uuid, SQL_BLOB);
140 $user_id = $sth->fetchrow_array //
141 die 'BUG: user '.unpack('H*', $uuid).' not found';
142 $sth = $dbh->prepare_cached(<<'');
143 UPDATE users SET last_seen = ? WHERE user_id = ?
145 $sth->execute(time, $user_id);
147 $user_id = $dbh->last_insert_id(undef, undef,
151 # 2. make sure the newsgroup has an integer ID
152 $sth = $dbh->prepare_cached(<<'');
153 INSERT OR IGNORE INTO newsgroups (newsgroup) VALUES (?)
155 my $ng = $pop3->{ibx}->{newsgroup};
156 $sth->bind_param(1, $ng, SQL_BLOB);
157 if ($sth->execute == 0) {
158 $sth = $dbh->prepare_cached(<<'', undef, 1);
159 SELECT newsgroup_id FROM newsgroups WHERE newsgroup = ?
161 $sth->bind_param(1, $ng, SQL_BLOB);
163 $ngid = $sth->fetchrow_array // die "BUG: `$ng' not found";
165 $ngid = $dbh->last_insert_id(undef, undef,
166 'newsgroups', 'newsgroup_id');
169 # 3. ensure the mailbox exists
170 $sth = $dbh->prepare_cached(<<'');
171 INSERT OR IGNORE INTO mailboxes (newsgroup_id, slice) VALUES (?,?)
173 if ($sth->execute($ngid, $pop3->{slice}) == 0) {
174 $sth = $dbh->prepare_cached(<<'', undef, 1);
175 SELECT mailbox_id FROM mailboxes WHERE newsgroup_id = ? AND slice = ?
177 $sth->execute($ngid, $pop3->{slice});
178 $mbid = $sth->fetchrow_array //
179 die "BUG: mailbox_id for $ng.$pop3->{slice} not found";
181 $mbid = $dbh->last_insert_id(undef, undef,
182 'mailboxes', 'mailbox_id');
185 # 4. ensure the (max) deletes row exists for locking
186 $sth = $dbh->prepare_cached(<<'');
187 INSERT OR IGNORE INTO deletes (user_id,mailbox_id) VALUES (?,?)
189 if ($sth->execute($user_id, $mbid) == 0) {
190 $sth = $dbh->prepare_cached(<<'', undef, 1);
191 SELECT txn_id,uid_dele FROM deletes WHERE user_id = ? AND mailbox_id = ?
193 $sth->execute($user_id, $mbid);
194 ($txn_id, $pop3->{uid_dele}) = $sth->fetchrow_array;
196 $txn_id = $dbh->last_insert_id(undef, undef,
197 'deletes', 'txn_id');
201 # see if it's locked by the same worker:
202 return if $self->{txn_locks}->{$txn_id};
204 # see if it's locked by another worker:
205 my $fs = File::FcntlLock->new;
206 $fs->l_type(F_WRLCK);
207 $fs->l_whence(SEEK_SET);
208 $fs->l_start($txn_id - 1);
210 $fs->lock($self->{txn_fh}, F_SETLK) or return;
212 $pop3->{user_id} = $user_id;
213 $pop3->{txn_id} = $txn_id;
214 $self->{txn_locks}->{$txn_id} = 1;
218 my ($self, $pop3) = @_;
219 my $txn_id = delete($pop3->{txn_id}) // return;
220 delete $self->{txn_locks}->{$txn_id}; # same worker
223 my $fs = File::FcntlLock->new;
224 $fs->l_type(F_UNLCK);
225 $fs->l_whence(SEEK_SET);
226 $fs->l_start($txn_id - 1);
228 $fs->lock($self->{txn_fh}, F_SETLK) or die "F_UNLCK: $!";