First, thanks in advance for any help offered.
I'm pretty comfortable using sqlite but just now starting to develop
with virtual tables.
I'm running into troubles and I'm pretty sure it's because my mental
model of sqlite is wimpy.
I'm trying to build a tool that interfaces to C++ objects in memory that
are basically
arranged as a tree (or graph or mesh). For example, let's suppose I
have a class called
A and objects of this class can own any number of B objects. Every A
and B object has
an ObjectID. Each object also has an owner.
In C/C++ I would do this sort of thing:
A* pA = GetOjectGivenID(12345); B* pB;
BIterator iter(pA);
while ((pB = iter.Next()) != 0) { assert(pB->pOwner == pA); }
That's simple enough. I'm trying to achieve the same effect using
virtual tables,
each of which follow this basic pattern:
create table A(OID integer primary key, Owner integer, Attr1
integer, Attr text, ...) without rowid;
That is, the first column of every table is OID, the object ID. For
most of the tables, there's also
a column called Owner and maybe other columns that act as foreign keys,
too. (I haven't actually
gone to the trouble to denote the columns are foreign keys just yet.)
Everything works when I have just a single table with SQL like this:
select * from A where A.OID == 12345;
The statement will walk through all of the rows. Behind the scenes, a
C++ iterator is
doing all of the work.
I'm struggling with joins. This statement doesn't work in my application:
select * from A join B on A.OID == B.Owner where A.OID == 12345;
The xBestIndex function is called a couple of times if this is the first
time
the table has been defined. (xBestIndex is called fewer times if the
table has
been seen before. I'm guessing sqlite is caching some info.) When the
xFilter
function is called, it's being handed the virtual table for B, not A.
Because
there's no object ID to act as the iterator's source, the xFilter
function ends in failure.
I'm struggling to find aConstraintUsage settings that will cause A, the
left-hand
table in my mental model, to be presented before B.
I'm setting the estimated number of rows correctly, I think. I think the
argvIndex values are being set correctly, too, I think. At least sqlite
isn't complaining
about malformed expressions. But, something is missing.
I spent some time looking at other examples but most of them seem to use
other tables within sqlite to hold the data vs. objects in memory. I did
stumble
across https://osquery.io/ yesterday, which looks interesting and
useful. It's also
somewhat complex and I haven't delved into its xBestIndex/xFilter
implementations
just yet.
I think I'd benefit from looking at any application that uses C/C++ objects
in memory plus iterators to traverse objects in a tree/graph/mesh/etc. Can
anyone point out projects like this?
I think I'd also benefit with documentation that shows more of what's
happening
behind the scenes with xBestIndex/xFilter. Can anyone point me to
documentation
that is more detailed? I've looked at the sqlite docs, Jay Kreibich's
book& Mike Owens
book and "Query Anything" documentation.
Thanks,
David
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
