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: [email protected] [mailto:[email protected]]
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:[email protected]]
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
[email protected]
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: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
