Hi, we have extensive experience with respect to the use of virtual tables in SQLite. In fact, the only native SQLite tables we use are in a configuration checking tool.
We have "providers" from in-memory indexed tables, CTree (r) files, Oracle tables (read only), structured disk files, in-memory structures, binary records, etc. The trick is to be able to formulate your queries solely via comparison operators. This type of constraint gets passed to your xBestIndex function and can be processed there. e.g. provide 2 virtual fields _function and _frame SELECT * from VA, VB where VA._function='Intersect' and VA._frame=VB.geom; When called for VA or VB with the constraints (_function,=) and (frame,=) your xBestIndex function should return - a value proportional to the effort of locating a record via the internal index as "estimated cost" - a number that signifies "use internal index" - set the "omit" flag fort he contraints - set the "argvIndex" values for the constraints When called for VA or VB without constraints, your xBestIndex function should return - a value proportional to the effort of a full table scan as "estimated cost" - a number that signifies "full table scan" This will make SQLite read VB via full table scan, and look up VA via the internal index. For each row retrieved from VB, your xFilter function will be called with the parameter values "Intersect" and "VB.geom". SQLite will expect to retrieve exactly those rows of VA the "Intersect" with "VB.geom". Assuming that all _functions are commutative e.g. F(a,b) = F(b,a) you could provide a symmetrical solution: SELECT * from VA, VB where VA._function='Intersect' and VB._function='Intersect' and VA._frame=VB.geom and VB._frame=VA.geom; SQLite would then choose the smaller product of full table scan * lookup. I think it should be possible to have SQLite omit all the checks; if not, _frame needs to return geom (best guess...). -----Ursprüngliche Nachricht----- Von: Hugo Mercier [mailto:hugo.merc...@oslandia.com] Gesendet: Mittwoch, 05. November 2014 10:09 An: General Discussion of SQLite Database Betreff: [sqlite] SQLite as a meta database Hi all, Following a first discussion on sqlite-dev that was probably not the right place to post, I've been invited to repost here for a broader audience :) I am a developer on QGIS and I am investigating the possible use of SQLite / Spatialite to extend QGIS relational-oriented features. For now, we have what we call "data providers" that allow to open / read / modify geographic data from different data sources, more or less structured data from regular files or from local or remote databases. Some database concepts are little by little put into QGIS, but some of us feel this is not exactly the right place for that. So I am considering the use of the virtual table mechanism of SQLite to embed a powerful SQL engine in QGIS. The idea would be to expose each type of GIS layer as a virtual table in SQLite. Then the user could use them for advanced queries such as (spatial) joins. GIS layers can already be RDBMS, like Postgresql/Postgis, MSSQL, Oracle spatial, etc. There have been discussions on QGIS ML about that, and we are concerned about the performances of such an approach [1] [2] [3] The main concern is about how to "translate" a main query that must in the end be split into queries to different databases. And especially regarding the use of native indices of such databases. From previous answers on sqlite-dev, using dedicated fields estimatedCost and estimatedRows in xBestIndex could be enough to orient the planner if native indices on regular columns are present (and if the virtual table knows that) For geometry column(s) that might be more complicated if I am correct. For a query such as: SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) where VA are virtual tables of say a PostGIS table and a Shapefile respectively, there is no way to inform xBestIndex to use the native spatial indices of VA or VB during the query. Native spatial indices must be locally copied and explicitly used with spatialite like : SELECT * FROM VA, VB where Intersects(VA.geom, VB.geom) AND VA.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'VA' AND search_frame = VB.geom ) Avoiding such explicit syntax and index duplication would require something like the implementation of GIST [4] in Sqlite, and having more generic constraints passed to xBestIndex, I guess. Not very easy. Are there other possibilies that I am missing ? The other concern is about accessing the parsed SQL query before executing it. This could be used to process the query in order to : - collect information on it : table names, column names and types, especially detecting geometry columns - bypass SQLite execution if the query is actually to be made on tables of the same database - possibly do SQL->SQL transformations Apparently accessing this parse tree is often asked for here, and some said [5] it could be nice to have for sqlite 4. Is it still something considered for next versions ? [1] https://github.com/mhugo/QGIS-Enhancement-Proposals/blob/master/QEP-3-virtual-layers.rst [2] http://osgeo-org.1560.x6.nabble.com/1-many-relation-enhancements-td5168023.html#a5168822 [3] http://osgeo-org.1560.x6.nabble.com/QEP-RFC-sqlite-virtual-tables-tt5168850.html [4] http://gist.cs.berkeley.edu/ [5] https://www.mail-archive.com/sqlite-users%40sqlite.org/msg43159.html _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users