* [PATCH 42/52] over: store xref3 data in over.sqlite3
2020-10-27 7:54 7% [PATCH 00/52] detached external index: mostly Eric Wong
@ 2020-10-27 7:54 7% ` Eric Wong
0 siblings, 0 replies; 2+ results
From: Eric Wong @ 2020-10-27 7:54 UTC (permalink / raw)
To: meta
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();
^ permalink raw reply related [relevance 7%]
* [PATCH 00/52] detached external index: mostly
@ 2020-10-27 7:54 7% Eric Wong
2020-10-27 7:54 7% ` [PATCH 42/52] over: store xref3 data in over.sqlite3 Eric Wong
0 siblings, 1 reply; 2+ results
From: Eric Wong @ 2020-10-27 7:54 UTC (permalink / raw)
To: meta
...and mostly wired up for WWW, but requires manual config
editing atm. Needs docs and tests, and IMAP support.
This will also form the basis of a mairix workalike client.
Not sure about the usability aspects, but I think this can
replace the need for per-inbox Xapian DBs and save a truckload
of disk space (and more importantly: cache space). Per-inbox
over.sqlite3 remains required for compatibility with NNTP/IMAP
and existing WWW code.
I don't know if the command-line tool is going to be called
public-inbox-eindex or public-inbox-extindex, but probably the
latter...
"xindex" could be confusing, and "eindex" rhymes with "reindex"
which could also be confusing. But I'm even more easily
confused than usual these days :x
Performance isn't great, it took 30+ hours to index my mirror of
lore on a SATA SSD, but the entire index is <200GB due to
deduplication between cross posts. -compact isn't working with
these indices, yet, but will sometime...
More changes on the way, still trying fix my brain and get
through this year...
Eric Wong (52):
doc/standards: add RFCs for URL schemes
search: hoist out _xdb_sharded for v2 inboxes
extsearch: start mocking out
searchidx: expose INDEXLEVELS as `our'
v2writable: add git method
v2writable: make OO calls to last_commit-related methods
search: xdb_sharded: make this a public method for ExtSearch
searchidx: introduce "xref3" concept
v2writable: prepare initialization for external indices
v2writable: hoist out write_alternates
searchidxshard: allow msgref to be undef
v2writable: idx_shard: simplify callers
v2writable: count_shards: allow working without {ibx}
overidx: introduce changes for external index
v2: some changes for ExtSearchIdx compatibility
inboxwritable: eidx_key for external index
v2writable: rename remaining "remote" terminology
v2writable: checkpoint: account for lack of {mm}
extsearchidx: initial implementation
searchidx: index eidx_key as a boolean term
searchidx: xref3 delete support
searchidxshard: special init for eidx
searchidx: put {ibx} into $sync state
searchidx: log2stack: simplify callers
v2writable: more generic sync setup code
v2writable: allow OO method references
v2writable: rename {v2w} field to {self}
v2writable: make *last_commits and sync_prepare OO methods
v2writable: move size check init to sync_prepare
extsearchidx: more compatibility with V2Writable callers
v2writable: reduce scope of epoch-aware code
extsearchidx: remove {unindex_range} field
v2writable: pass oid to uindex_oid
extsearchidx: sync unit updates
searchidx: export prepare_stack
extsearchidx: sync updates
searchidx: reduce inbox-dependency, wrap ->with_umask
searchidx: favor $sync->{ibx} (over $self->{ibx})
Makefile.PL: do not build manpage if POD is missing
script: add preliminary eindex implementation
index: eindex wiring
over: store xref3 data in over.sqlite3
searchidx: remove xref3 support for Xapian
t/extsearch.t: verify results and xref3 ordering
t/v2writable: remove pointless ->barrier call
extsearch: wire up smsg_eml
extsearchidx: handle edits
extsearch: wire up remaining Inbox-like methods for WWW
searchidx: ignore exceptions from ->remove_term
extsearchidx: set current_info in warning callbacks
extsearchidx: support --batch-size checkpoints
searchidxshard: make warnings with eidx_key less confusing
Documentation/standards.perl | 3 +
MANIFEST | 4 +
Makefile.PL | 16 +-
lib/PublicInbox/Config.pm | 12 +
lib/PublicInbox/ExtSearch.pm | 69 +++++
lib/PublicInbox/ExtSearchIdx.pm | 404 ++++++++++++++++++++++++++++++
lib/PublicInbox/Inbox.pm | 53 ++--
lib/PublicInbox/InboxWritable.pm | 23 ++
lib/PublicInbox/Over.pm | 19 ++
lib/PublicInbox/OverIdx.pm | 122 ++++++++-
lib/PublicInbox/Search.pm | 62 ++---
lib/PublicInbox/SearchIdx.pm | 135 +++++++---
lib/PublicInbox/SearchIdxShard.pm | 77 +++++-
lib/PublicInbox/V2Writable.pm | 310 ++++++++++++-----------
lib/PublicInbox/WWW.pm | 3 +-
lib/PublicInbox/Xapcmd.pm | 2 +-
script/public-inbox-eindex | 43 ++++
script/public-inbox-index | 3 +-
t/extsearch.t | 75 ++++++
t/over.t | 24 ++
t/search.t | 2 -
t/v2writable.t | 3 +-
22 files changed, 1204 insertions(+), 260 deletions(-)
create mode 100644 lib/PublicInbox/ExtSearch.pm
create mode 100644 lib/PublicInbox/ExtSearchIdx.pm
create mode 100644 script/public-inbox-eindex
create mode 100644 t/extsearch.t
^ permalink raw reply [relevance 7%]
Results 1-2 of 2 | reverse | options above
-- pct% links below jump to the message on this page, permalinks otherwise --
2020-10-27 7:54 7% [PATCH 00/52] detached external index: mostly Eric Wong
2020-10-27 7:54 7% ` [PATCH 42/52] over: store xref3 data in over.sqlite3 Eric Wong
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).