Wow! Please keep this discussion up-to-date because it is absolutely fascinating what all of you are doing. Thanks, dvn
On Wed, Nov 5, 2014 at 8:36 AM, Hugo Mercier <hugo.merc...@oslandia.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
