Xapian discuss - Mar 2021 - SQL-like JOINs on separate DBs?

Hi all, I'm dealing with two classes of Xapian DBs.

One class of Xapian DBs is large, well-established, and treated
as read-only for this application.

The other class is new, and in-development for storing keywords
(with "kw:" prefix).  It could potentially have as many
documents as the large DBs (or even all the large DBs combined
into one multi-shard monster)), but few terms and no positional
data, just keywords (e.g. "seen", "flagged").

I want to be able to run queries like:

	something in a giant databases AND kw:seen

	something in a giant databases AND NOT kw:seen

Where "kw:" would only be stored in the small DB and keyed
against docs in the giant DB by a SHA-<1|256>, or
$PATHNAME:$DOCID, even.

It's entirely possible I missed something, but there's currently
no way in Xapian to combine DBs in a way similar to RDBMS JOINs,
is there?

What I may resort to doing is:

1) parse the query myself for each class of DB
   (not sure if QueryParser can help, here)

2) run separate queries and output to text files (in parallel, of course :)

3) run the standard join(1) command on resulting text files

Or what could be done instead of 2)+3) is:

2a) run query on large DB

3a) iterate through results from large DB; include or exclude only
    the ones matching the requested kw: field.

Any other ideas?

I'm using Perl Search::Xapian from Debian stable (buster).

Thanks.

On Fri, Feb 26, 2021 at 11:54:44AM +0000, Eric Wong
wrote:
> I want to be able to run queries like:
> 
> 	something in a giant databases AND kw:seen
> 
> 	something in a giant databases AND NOT kw:seen
> 
> Where "kw:" would only be stored in the small DB and keyed
> against docs in the giant DB by a SHA-<1|256>, or
> $PATHNAME:$DOCID, even.
> 
> It's entirely possible I missed something, but there's currently
> no way in Xapian to combine DBs in a way similar to RDBMS JOINs,
> is there?
There's not a "JOIN" feature as such.  It's been suggested
before, but
we don't have a clear plan for how it would work (either from the
outside or internally.)

And it really needs to be efficient (at least for typical use cases) or
it's just an annoying trap for developers - they'd build a system with
"JOIN" as part of the design only to find performance is
disappointing.

I'm not clear exactly you want to join on (SHA of what or pathname of
what?), but if you can efficiently map the Xapian::docid in the main
database to a Xapian::docid or unique ID in the auxiliary database then
a custom PostingSource subclass would work here (and the auxiliary
database wouldn't even need to be a Xapian database, or indeed a
database at all.)  The `kw:seen` subquery would be a PostingSource.
> I'm using Perl Search::Xapian from Debian stable (buster).
Unfortunately that doesn't wrap PostingSource.

But note that since 1.2.7 the case of adding and/or removing boolean
terms from an existing document is optimised - only the posting lists
of the added/removed boolean terms and the termlists of the affected
documents are rewritten, not the posting lists for all terms in the
documents.  This kicks in for code like:

    Xapian::Document doc = db.get_document(docid);
    doc.add_boolean_term("Kseen");
    db.replace_document(did, doc);

It wouldn't be as fast to update as an entirely separate database, but
if you've rejected this approach based on trying it with a much older
Xapian version then it may be worth reconsidering.

Cheers,
    Olly