From: Eric Wong <e@80x24.org>
To: meta@public-inbox.org
Subject: [PATCH 42/52] over: store xref3 data in over.sqlite3
Date: Tue, 27 Oct 2020 07:54:43 +0000 [thread overview]
Message-ID: <20201027075453.19163-43-e@80x24.org> (raw)
In-Reply-To: <20201027075453.19163-1-e@80x24.org>
We may not end up storing xref3 data in Xapian, actually.
This will make indexlevel=basic possible, and along with
--sequential-shard indexing support for slow storage.
Making oidmap a separate table seems unnecessary, too, so
fold it into the xref3 table since it's unlikely a git blob
will be responsible for multiple xref3 rows.
---
lib/PublicInbox/Over.pm | 19 +++++++++++
lib/PublicInbox/OverIdx.pm | 64 ++++++++++++++++++++++----------------
t/over.t | 27 +++++++++++-----
3 files changed, 77 insertions(+), 33 deletions(-)
diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index 08112386..f34e7fc1 100644
--- a/lib/PublicInbox/Over.pm
+++ b/lib/PublicInbox/Over.pm
@@ -260,6 +260,25 @@ SELECT num,tid,ds,ts,ddd FROM over WHERE num = ? LIMIT 1
$smsg ? load_from_row($smsg) : undef;
}
+sub get_xref3 {
+ my ($self, $num) = @_;
+ my $dbh = dbh($self);
+ my $sth = $dbh->prepare_cached(<<'', undef, 1);
+SELECT ibx_id,xnum,oidbin FROM xref3 WHERE docid = ? ORDER BY ibx_id ASC
+
+ $sth->execute($num);
+ my $rows = $sth->fetchall_arrayref;
+ my $eidx_key_sth = $dbh->prepare_cached(<<'', undef, 1);
+SELECT eidx_key FROM inboxes WHERE ibx_id = ?
+
+ [ map {
+ my $r = $_;
+ $eidx_key_sth->execute($r->[0]);
+ my $eidx_key = $eidx_key_sth->fetchrow_array;
+ "$eidx_key:$r->[1]:".unpack('H*', $r->[2]);
+ } @$rows ];
+}
+
sub next_by_mid {
my ($self, $mid, $id, $prev) = @_;
my $dbh = dbh($self);
diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm
index 09bca790..dff2780d 100644
--- a/lib/PublicInbox/OverIdx.pm
+++ b/lib/PublicInbox/OverIdx.pm
@@ -517,20 +517,27 @@ sub eidx_prep ($) {
my ($self) = @_;
$self->{-eidx_prep} //= do {
my $dbh = $self->dbh;
- $dbh->do(<<'');
-INSERT OR IGNORE INTO counter (key) VALUES ('oidmap_num')
+ $dbh->do(<<"");
+INSERT OR IGNORE INTO counter (key) VALUES ('eidx_docid')
$dbh->do(<<'');
-INSERT OR IGNORE INTO counter (key) VALUES ('eidx_docid')
+CREATE TABLE IF NOT EXISTS inboxes (
+ ibx_id INTEGER PRIMARY KEY AUTOINCREMENT,
+ eidx_key VARCHAR(255) NOT NULL, /* {newsgroup} // {inboxdir} */
+ UNIQUE (eidx_key)
+)
$dbh->do(<<'');
-CREATE TABLE IF NOT EXISTS oidmap (
- num INTEGER NOT NULL, /* NNTP article number == IMAP UID */
- oidbin VARBINARY, /* 20-byte SHA-1 or 32-byte SHA-256 */
- UNIQUE (num),
- UNIQUE (oidbin)
+CREATE TABLE IF NOT EXISTS xref3 (
+ docid INTEGER NOT NULL, /* <=> over.num */
+ ibx_id INTEGER NOT NULL, /* <=> inboxes.ibx_id */
+ xnum INTEGER NOT NULL, /* NNTP article number in ibx */
+ oidbin VARBINARY NOT NULL, /* 20-byte SHA-1 or 32-byte SHA-256 */
+ UNIQUE (docid, ibx_id, xnum, oidbin)
)
+ $dbh->do('CREATE INDEX IF NOT EXISTS idx_docid ON xref3 (docid)');
+
$dbh->do(<<'');
CREATE TABLE IF NOT EXISTS eidx_meta (
key VARCHAR(255) PRIMARY KEY,
@@ -564,28 +571,33 @@ sub eidx_max {
get_counter($self->{dbh}, 'eidx_docid');
}
-sub oid2num {
- my ($self, $oidhex) = @_;
- my $dbh = eidx_prep($self);
- my $sth = $dbh->prepare_cached(<<'', undef, 1);
-SELECT num FROM oidmap WHERE oidbin = ?
-
- $sth->bind_param(1, pack('H*', $oidhex), SQL_BLOB);
+sub add_xref3 {
+ my ($self, $docid, $xnum, $oidhex, $eidx_key) = @_;
+ begin_lazy($self);
+ my $ibx_id = id_for($self, 'inboxes', 'ibx_id', eidx_key => $eidx_key);
+ my $oidbin = pack('H*', $oidhex);
+ my $sth = $self->{dbh}->prepare_cached(<<'');
+INSERT OR IGNORE INTO xref3 (docid, ibx_id, xnum, oidbin) VALUES (?, ?, ?, ?)
+
+ $sth->bind_param(1, $docid);
+ $sth->bind_param(2, $ibx_id);
+ $sth->bind_param(3, $xnum);
+ $sth->bind_param(4, $oidbin, SQL_BLOB);
$sth->execute;
- $sth->fetchrow_array;
}
-sub oid_add {
- my ($self, $oidhex) = @_;
- my $dbh = eidx_prep($self);
- my $num = adj_counter($self, 'oidmap_num', '+');
- my $sth = $dbh->prepare_cached(<<'');
-INSERT INTO oidmap (num, oidbin) VALUES (?,?)
-
- $sth->bind_param(1, $num);
- $sth->bind_param(2, pack('H*', $oidhex), SQL_BLOB);
+sub remove_xref3 {
+ my ($self, $docid, $oidhex, $eidx_key) = @_;
+ begin_lazy($self);
+ my $ibx_id = id_for($self, 'inboxes', 'ibx_id', eidx_key => $eidx_key);
+ my $oidbin = pack('H*', $oidhex);
+ my $sth = $self->{dbh}->prepare_cached(<<'');
+DELETE FROM xref3 WHERE docid = ? AND ibx_id = ? AND oidbin = ?
+
+ $sth->bind_param(1, $docid);
+ $sth->bind_param(2, $ibx_id);
+ $sth->bind_param(3, $oidbin, SQL_BLOB);
$sth->execute;
- $num;
}
1;
diff --git a/t/over.t b/t/over.t
index 3e2860f8..56c20d01 100644
--- a/t/over.t
+++ b/t/over.t
@@ -75,14 +75,27 @@ SKIP: {
}
# ext index additions
+$over->eidx_prep;
{
- my $hex = 'deadbeefcafe';
- my $n = $over->oid_add($hex);
- ok($n > 0, 'oid_add returned number');
- is($over->oid2num($hex), $n, 'oid2num works');
- my $n2 = $over->oid_add($hex.$hex);
- ok($n2 > $n, 'oid_add increments');
- is($over->oid2num($hex.$hex), $n2, 'oid2num works again');
+ my @arg = qw(1349 2019 adeadba7cafe example.key);
+ ok($over->add_xref3(@arg), 'first add');
+ ok($over->add_xref3(@arg), 'add idempotent');
+ my $xref3 = $over->get_xref3(1349);
+ is_deeply($xref3, [ 'example.key:2019:adeadba7cafe' ], 'xref3 works');
+
+ @arg = qw(1349 2018 deadbeefcafe example.kee);
+ ok($over->add_xref3(@arg), 'add another xref3');
+ $xref3 = $over->get_xref3(1349);
+ is_deeply($xref3, [ 'example.key:2019:adeadba7cafe',
+ 'example.kee:2018:deadbeefcafe' ],
+ 'xref3 works forw two');
+
+ @arg = qw(1349 adeadba7cafe example.key);
+ ok($over->remove_xref3(@arg), 'remove first');
+ $xref3 = $over->get_xref3(1349);
+ is_deeply($xref3, [ 'example.kee:2018:deadbeefcafe' ],
+ 'confirm removal successful');
+ $over->rollback_lazy;
}
done_testing();
next prev parent reply other threads:[~2020-10-27 7:55 UTC|newest]
Thread overview: 57+ messages / expand[flat|nested] mbox.gz Atom feed top
2020-10-27 7:54 [PATCH 00/52] detached external index: mostly Eric Wong
2020-10-27 7:54 ` [PATCH 01/52] doc/standards: add RFCs for URL schemes Eric Wong
2020-11-05 7:54 ` [PATCH v2] " Eric Wong
2020-10-27 7:54 ` [PATCH 02/52] search: hoist out _xdb_sharded for v2 inboxes Eric Wong
2020-10-27 7:54 ` [PATCH 03/52] extsearch: start mocking out Eric Wong
2020-10-27 7:54 ` [PATCH 04/52] searchidx: expose INDEXLEVELS as `our' Eric Wong
2020-10-27 7:54 ` [PATCH 05/52] v2writable: add git method Eric Wong
2020-10-27 7:54 ` [PATCH 06/52] v2writable: make OO calls to last_commit-related methods Eric Wong
2020-10-27 7:54 ` [PATCH 07/52] search: xdb_sharded: make this a public method for ExtSearch Eric Wong
2020-10-27 7:54 ` [PATCH 08/52] searchidx: introduce "xref3" concept Eric Wong
2020-10-27 7:54 ` [PATCH 09/52] v2writable: prepare initialization for external indices Eric Wong
2020-10-27 7:54 ` [PATCH 10/52] v2writable: hoist out write_alternates Eric Wong
2020-10-27 7:54 ` [PATCH 11/52] searchidxshard: allow msgref to be undef Eric Wong
2020-10-27 7:54 ` [PATCH 12/52] v2writable: idx_shard: simplify callers Eric Wong
2020-10-27 7:54 ` [PATCH 13/52] v2writable: count_shards: allow working without {ibx} Eric Wong
2020-10-27 7:54 ` [PATCH 14/52] overidx: introduce changes for external index Eric Wong
2020-10-27 7:54 ` [PATCH 15/52] v2: some changes for ExtSearchIdx compatibility Eric Wong
2020-10-27 7:54 ` [PATCH 16/52] inboxwritable: eidx_key for external index Eric Wong
2020-10-27 7:54 ` [PATCH 17/52] v2writable: rename remaining "remote" terminology Eric Wong
2020-10-27 7:54 ` [PATCH 18/52] v2writable: checkpoint: account for lack of {mm} Eric Wong
2020-10-27 7:54 ` [PATCH 19/52] extsearchidx: initial implementation Eric Wong
2020-10-27 7:54 ` [PATCH 20/52] searchidx: index eidx_key as a boolean term Eric Wong
2020-10-27 7:54 ` [PATCH 21/52] searchidx: xref3 delete support Eric Wong
2020-10-27 7:54 ` [PATCH 22/52] searchidxshard: special init for eidx Eric Wong
2020-10-27 7:54 ` [PATCH 23/52] searchidx: put {ibx} into $sync state Eric Wong
2020-10-27 7:54 ` [PATCH 24/52] searchidx: log2stack: simplify callers Eric Wong
2020-10-27 7:54 ` [PATCH 25/52] v2writable: more generic sync setup code Eric Wong
2020-10-27 7:54 ` [PATCH 26/52] v2writable: allow OO method references Eric Wong
2020-10-27 7:54 ` [PATCH 27/52] v2writable: rename {v2w} field to {self} Eric Wong
2020-10-27 7:54 ` [PATCH 28/52] v2writable: make *last_commits and sync_prepare OO methods Eric Wong
2020-10-27 7:54 ` [PATCH 29/52] v2writable: move size check init to sync_prepare Eric Wong
2020-10-27 7:54 ` [PATCH 30/52] extsearchidx: more compatibility with V2Writable callers Eric Wong
2020-10-27 7:54 ` [PATCH 31/52] v2writable: reduce scope of epoch-aware code Eric Wong
2020-10-27 7:54 ` [PATCH 32/52] extsearchidx: remove {unindex_range} field Eric Wong
2020-10-27 7:54 ` [PATCH 33/52] v2writable: pass oid to uindex_oid Eric Wong
2020-10-27 7:54 ` [PATCH 34/52] extsearchidx: sync unit updates Eric Wong
2020-10-27 7:54 ` [PATCH 35/52] searchidx: export prepare_stack Eric Wong
2020-10-27 7:54 ` [PATCH 36/52] extsearchidx: sync updates Eric Wong
2020-10-27 7:54 ` [PATCH 37/52] searchidx: reduce inbox-dependency, wrap ->with_umask Eric Wong
2020-10-27 7:54 ` [PATCH 38/52] searchidx: favor $sync->{ibx} (over $self->{ibx}) Eric Wong
2020-10-27 7:54 ` [PATCH 39/52] Makefile.PL: do not build manpage if POD is missing Eric Wong
2020-10-27 7:54 ` [PATCH 40/52] script: add preliminary eindex implementation Eric Wong
2020-10-27 7:54 ` [PATCH 41/52] index: eindex wiring Eric Wong
2020-10-27 7:54 ` Eric Wong [this message]
2020-10-27 7:54 ` [PATCH 43/52] searchidx: remove xref3 support for Xapian Eric Wong
2020-10-27 7:54 ` [PATCH 44/52] t/extsearch.t: verify results and xref3 ordering Eric Wong
2020-10-27 7:54 ` [PATCH 45/52] t/v2writable: remove pointless ->barrier call Eric Wong
2020-10-27 7:54 ` [PATCH 46/52] extsearch: wire up smsg_eml Eric Wong
2020-10-27 7:54 ` [PATCH 47/52] extsearchidx: handle edits Eric Wong
2020-10-27 7:54 ` [PATCH 48/52] extsearch: wire up remaining Inbox-like methods for WWW Eric Wong
2020-10-27 7:54 ` [PATCH 49/52] searchidx: ignore exceptions from ->remove_term Eric Wong
2020-10-27 7:54 ` [PATCH 50/52] extsearchidx: set current_info in warning callbacks Eric Wong
2020-10-27 7:54 ` [PATCH 51/52] extsearchidx: support --batch-size checkpoints Eric Wong
2020-11-03 9:08 ` Eric Wong
2020-10-27 7:54 ` [PATCH 52/52] searchidxshard: make warnings with eidx_key less confusing Eric Wong
2020-10-27 12:08 ` [PATCH 00/52] detached external index: mostly Konstantin Ryabitsev
2020-11-10 18:53 ` detached external index: performance note Eric Wong
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://public-inbox.org/README
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=20201027075453.19163-43-e@80x24.org \
--to=e@80x24.org \
--cc=meta@public-inbox.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://80x24.org/public-inbox.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).