Summary of Virtual Table Changes
I'm doing research on SQLite Virtual Tables and thought I'd share this. I'm looking for recommendations on tutorials and/or books to read more about it.
2009-05-07 (3.6.14) * Enhanced the query optimizer so that virtual tables are able to make use of OR and IN operators in the WHERE clause.
2009-08-10 (3.6.17) * Remove the restriction on virtual tables and shared cache mode. Virtual tables and shared cache can now be used at the same time.
2011-09-19 (3.7.8) * Bug fix: Virtual tables now handle IS NOT NULL constraints correctly.
2013-03-18 (3.7.16) * Enhance virtual tables so that they can potentially use an index when the WHERE clause contains the IN operator.
2013-12-06 (3.8.2) * Extended the virtual table interface, and in particular the sqlite3_index_info object to allow a virtual table to report its estimate on the number of rows that will be returned by a query.
2014-02-03 (3.8.3) * Enhance the spellfix1 virtual table so that it can search efficiently by rowid.
2016-01-06 (3.10.0) * Added support for LIKE, GLOB, and REGEXP operators on virtual tables. * Added the colUsed field to sqlite3_index_info for use by the sqlite3_module.xBestIndex method.
2016-03-29 (3.12.0) * The query planner is able to optimize IN operators on virtual tables even if the xBestIndex method does not set the sqlite3_index_constraint_usage.omit flag of the virtual table column to the left of the IN operator. * The query planner now does a better job of optimizing virtual table accesses in a 3-way or higher join where constraints on the virtual table are split across two or more other tables of the join.
2016-04-18 (3.12.2) * Fix a bug in the code generator that can cause incorrect results if two or more virtual tables are joined and the virtual table used in outer loop of the join has an IN operator constraint.
2016-08-08 (3.14) * Added support for WITHOUT ROWID virtual tables. * Improved the query planner so that the OR optimization can be used on virtual tables even if one or more of the disjuncts use the LIKE, GLOB, REGEXP, MATCH operators.
2016-10-14 (3.15.0) * Enhance the fts5vocab virtual table to handle "ORDER BY term" efficiently.
2017-08-01 (3.20.0) * Do not use the flattening optimization for a sub-query on the RHS of a LEFT JOIN if that subquery reads data from a virtual table as doing so prevents the query planner from creating automatic indexes on the results of the sub-query, which can slow down the query.
2017-10-24 (3.21.0) * Pass information about !=, IS, IS NOT, NOT NULL, and IS NULL constraints into the xBestIndex method of virtual tables. Allow WITHOUT ROWID virtual tables to be writable if the PRIMARY KEY contains exactly one column.
2018-04-10 (3.23.1) * Fix the generate_series virtual table so that it correctly returns no rows if any of its constraints are NULL.
2018-11-05 (3.25.3) * Fix the behavior of typeof() and length() on virtual tables. Ticket 69d642332d25aa3b7315a6d385
2018-12-01 (3.26.0) * Allow the xBestIndex() method of virtual table implementations to return SQLITE_CONSTRAINT to indicate that the proposed query plan is unusable and should not be given further consideration.
There's a bit of a learning curve for sure.
I'd recommend studying the many vtable impls in the SQLite repo itself.
Also mine the ML archives for Gunther's posts about virtual tables.
Beside Richard and Dan, he's one of the authority on them on this forum.
Just want to retouch the prudent mention of Gunther ...to Gunter Hick... in order to quicken any search.
(4) By Gunter Hick (gunter_hick) on 2020-10-01 06:12:16 in reply to 2 [link] [source]
Thanks for the flowers :) At the end of 2007 we were looking for a replacement for a custom SQL parsing engine when we found out about SQLite virtual tables. This allowed us to integrate diverse data stores under a common query mechanism in a language understood by our GUI developers and we are still using it extensively after 13 years.
The book "Using SQLite" (O'Reilly) has a full chapter on Virtual Tables. Although the book is somewhat old (2010), the basics of Virtual Tables are still the same.
I would say that is accurate. One new (in my mind major) addition that is missing is https://sqlite.org/vtab.html#xfindfunction
(EDIT: I meant to say the fact that SQLITE_INDEX_CONSTRAINT_FUNCTION can be returned to short circuit search)