I'll summarize what I've done; note that I don't consider myself an 'expert' in
this area. What I have works, but others may have suggestions on improvements.
(I'd actually be happy to hear any suggestions).
For my virtual tables I generate an additional 'special' column called
'_MatchFilter' (the leading underscore is my convention identifying it as
'special'). It's a string column that returns either empty string or the
specified 'filter' from the where clause if specified. This column is a
placeholder and not useful, except when used in a where clause with 'match' or
'='. In these cases, my BestIndex prefers queries including _MatchFilter with
'match' or '=' over all others and Filter takes the match/= compare value and
passes it as a custom query to my 'table' so it can do things more efficiently.
Example:
select * from MyTable where _MatchFilter match "my custom query here"
select * from MyTable where _MatchFilter = "my custom query here"
You still have to handle cases where _MatchFilter gets 'and'ed with additional
clauses of course in your BestIndex and Filter, though at worst you should be
able to run your custom query and then in memory filter the results on the
other clause (or vice versa). But you can get really crazy with what you allow
for the filter in this manner, passing it directly to your virtual table
implementation.
What you want to do with Average is somewhat different; you really don't need a
custom query, but a way to avoid having SQLite do the average for your table.
One trick I can think of is adding your own special column to the table
'_Average', that returns the calculated average. Its somewhat strange in that
ever 'row' would contain the average of course. If it's still expensive to
calculate, you might require something similar to my _MatchFilter' arrangement
to cause '_Average' to generate and return '0' for it if not present in the
where clause.
This is all somewhat hacky of course and non-standard if you're exposing SQL to
users.
MikeN
-----Original Message-----
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jerry
Sent: Monday, June 22, 2015 2:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What's the best way to pass function information to
virtual table?
Hi, MikeN.
Assuming I want to compute the average value of all keys, how to write the
query for this using match?
For example, if I write SQL in this way
> SELECT avg(key) From table WHERE key MATCH('avg');
with MATCH info, the virtual table is able to know the query is looking for
average value for the keys.
Now, assume the virtual table is able to compute the average faster than
SQLite, after I get the average value, how can I return the result immediately
without SQLite going through the aggregator avg()?
What are your queries like when you "pass arbitrary specialized queries
directly to my virtual table"?
Thanks,
-C.Lin
2015-06-17 8:48 GMT-07:00 Mike Nicolino <mike.nicolino at centrify.com>:
> The override of match() trick works pretty well for cases like this.
> I've overridden match in my virtual table implementation to allow me
> to pass arbitrary specialized queries directly to my virtual table
> modules for cases that I know the virtual table can do a better job
> that SQLite on that query. Downside is if you're exposing the SQL to
> users of course as using match in such a manner is non-standard.
>
> MikeN
>
>
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Clemens
> Ladisch
> Sent: Wednesday, June 17, 2015 1:23 AM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] What's the best way to pass function information
> to virtual table?
>
> Jerry wrote:
> > With xBestIndex and xFilter, we can pass the constraint information
> > (e.g., those from WHERE clause) to virtual table (through struct
> > sqlite3_index_info), so that we can locate the cursor to narrow the
> > search space.
> > However, it does not provide information about functions used in SQL
> > queries.
> >
> > For example,
> >
> >> SELECT MAX(key) from Table tab;
> >
> > The virtual table has no way to know the function MAX is called
>
> SQLite has a special optimization for this particular query, and
> rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1".
>
> > The virtual table provides xFindFunction to override functions
> > (maybe this can be used to pass some information).
>
> For example, the full-text search module overrides the match()
> function; you could do something similar:
> SELECT key FROM tab WHERE tab MATCH 'max(key)'
>
> > But it seems only general functions can be override -- it has not
> > effect on aggregate functions.
>
> The virtual table interface does not allow access to all the internals
> of the query optimizer.
>
> When there is an aggregate function, you can filter the rows that will
> be given to it, but the actual aggregation is still done by SQLite.
>
> If you can compute aggregates more efficiently than SQLite, you could
> create a separate virtual table:
> SELECT max_key FROM tab_agg
> but this would not work for more complex queries.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
