virtual tables, xBestIndex/xFilter question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

virtual tables, xBestIndex/xFilter question

David Fletcher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] virtual tables, xBestIndex/xFilter question

Hick Gunter
For your JOIN case, I would expect the following "conversation" (simplified and based on the original cost based QP, the NGQP may be doing something else)

xBestIndex( table A, no constraints) -> Full table scan FTS(A), cost = CARD(A), count=CARD(A)
xBestIndex( table A, OID) -> primary key lookup PK(A), cost = LOG(CARD(A)), count=1, unique flag set
xBestIndex( table B, no constraints) -> FTS B
xBestIndex( table B, Owner) -> partial table scan PTS(B, Owner); since this seems to be unsupported, this is also FTS(B)

Now it considers the possible query plans

Lookup in table A, followed by a full table scan of table B: LOG(CARD(A)) * CARD(B) (because there is no index on B.Owner)
Full table scan of table B, followed by lookup in table A: CARD(B) * LOG(CARD(A))

Since the cost estimate is the same, it is free to choose either plan, apparently the second one. In both cases, a full table scan of table B is performed. Why does this cause a problem in the second query plan?

Note: you can use the keyword "cross" to force the desired order. Does your query work then? What is the difference in the way that table B is handled?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Fletcher
Gesendet: Sonntag, 29. Juli 2018 18:42
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] virtual tables, xBestIndex/xFilter question

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users