Hi, Le 05/11/2014 14:16, Hick Gunter a écrit : > 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...). >
This 'virtual field' trick is very clever, thanks ! I still have to figure out the details, but it could allow us to use internal spatial indices, without copying them locally. It confirms however the query would not be very natural to write for the end user and that we would have to assist him or automate the query generation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
