xCreate should specify "CREATE TABLE x (label TEXT);" which assumes that rowid is the implicit index
xBestIndex needs to return: 1) cost = 1, index = 0 if a single constraint -1 (rowid) and operation EQ is provided (assuming the record position can be calculated from the index) 2) cost = n/2, index = 0 if a single constraint -1 (rowid) and operation LE/GE/LT/GT is provided (as on average, half the records need to be searched) 3) cost = ld(n), index = 1 if a single constraint 0 (label) and operation EQ is provided (assuming the associative array is implemented via a binary tree) 4) cost = n/2, index = 1 if a single constraint 0 (label) and operation LE/GE/LT/GT/MATCH is provided (as on average, half the records need to be searched) 5) cost = n, index = 0 otherwise (if more than one constraint is supplied, pick your favorite) If a constraint is used, the argv index needs to be set, so ist value can be retrieved by xFilter. Minimally you need to implement 1, 3 and 5. You can implement 2 and 4 later, when the others work. I would suggest storing the operation in the idxString variable. xFilter needs to consider the cases: 1) set the current record to the one indexed by the passed index, set cursor to expire when xNext is called 3) set the current record to the one located via the passed text, set cursor to expire when xNext is called 5) set the current record to the first one, xNext needs to iterate until the last record was returned 2) set the record range according to the operation and the passed index, xNext needs to point to the record in the range in order 4) set the record range accoridng tot he operation and the passed text, xNext needs to point to the record in the range in order Note that all cases are specializations of "set range" and "set iterator function". You can add processing of the ORDER BY clause later. -----Ursprüngliche Nachricht----- Von: Mike Beddo [mailto:mike.be...@dataventures.com] Gesendet: Donnerstag, 06. November 2014 01:10 An: General Discussion of SQLite Database Betreff: Re: [sqlite] SQLite as a meta database Hi, I am intruding into this thread, because I face a similar issue. At our company we have a "proprietary" database storing customer grocery transactions which basically only a C programmer can get to. It is by no stretch of the imagination a "relational" database. Basically, everything is an integer and we rely on associative tables in our C codes to map integers back into human readable labels for output purposes. For instance, a particular UPC "0000000000000" might map to integer 123, so we work with 123 in our codes, and when we output the results we map 123 back to "0000000000000". There is intense interest in providing a SQL front-end. I thought to start with, I'd see if I could develop a virtual table for our various associative tables. Doing this would help educate me before embarking on the bigger chore of handling the database itself. Sounds simple enough, but I could use some help/advice. There are several associative tables that I would need a virtual table for: items, stores, customers. I thought something like CREATE VIRTUAL TABLE items USING aa_module('/path/to/database', 'items'); The arguments to aa_module (the thing I'm trying to write) give the path to the database to open, and which associative map to create a virtual table for. If we think of 'items' as a pointer to the items associative array, for instance, then in our code we would use expressions like: int inx = AA_inx(items, '0000000000000'); /* gives the index associated with upc 0000000000000 */ char *upc = AA_lbl(items, 123); /* gives the upc string associated with item inx 123 */ The table create string to use in xCreate/xConnect would be "CREATE TABLE x(inx INTEGER, upc VARCHAR(13));"I think that in my xBestIndex function that if the constraint involves equality then the cost is 1 lookup, but for any other type of constraint the cost is the number of items in the AA map, since this thing isn't sorted. Could someone give me an idea of what a minimal xBestIndex/xFilter skeleton might look like? I walked though "ext/misc/amatch.c" from the www.sqlite.org/src/artifact tree, but I'm a little lost. Thanks, Mike Beddo -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hick Gunter Sent: Wednesday, November 05, 2014 6:16 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] SQLite as a meta database 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 _______________________________________________ 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
