[yunqa.de] Re: Using the virtual table module interface in SQLite
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx,yunqa@xxxxxxxxxxxxx
- Date: Fri, 25 Sep 2009 15:35:27 +0200
At 10:50 25.09.2009, Tobias Rapp wrote:
>I'd like to create a virtual table module that presents all files within a
>specific directory as a read-only table in SQLite (implemented using
>FindFirst/FindNext). For this I had a look at the SQLite documentation and
>the DISQLite3_VirtualTable_StringList demo that is included in the DISQLite
>sources but some questions remain:
Here is a C implementation of a file system virtual table:
http://www.ddj.com/database/202802959?pgno=1
Full source code:
ftp://66.77.27.238/sourcecode/ddj/2008/0812.zip
>1) xBestIndex: In the demo this method just returns the StringList count. I
>do not know the amount of files in a directory until I fully iterate it. Does
>it make sense to return a constant (0.0) in this case?
From the help:
"The estimatedCost field should be set to the estimated number of disk access
operations required to execute this query against the virtual table."
In your case, the number of disk acccss operations is not known in advance.
SQLite uses an internal cost of 0.0 for a direct rowid lookup. FTS modules
assign a cost of 1.0. So my guess is that you can not really return a "wrong"
cost here, since FindFirst / FindNext will do not allow for indexes but force a
full table scan anyway.
>2) xRowId: Not sure what should be returned here in my case. Maybe I can just
>return an Index counter which is incremented each time xNext is called? Or
>maybe a 64bit hash value of the filename? Or just NULL?
The example code above uses the inode of a file object (Linux only, see
http://en.wikipedia.org/wiki/Inode). There is no equivalent for Windows
FindFirst / FindNext. Maybe you should use the Shell Namespace instead?
http://msdn.microsoft.com/en-us/library/bb776813%28VS.85%29.aspx
However, file PIDLs do not fit into the 64 bit rowid. Hence you must allocate
additional memory and obviously free it later on. Unfortunately, the virtual
table inteterface does not provide a method to do so.
However, my impression is that virtual tables use xRowID only for insert,
update, and delete operations or if you filter by rowid.
Since your virtual file table is readonly, the rowid should not matter.
Returning an index counter incremented each time xNext is called should be fine
IMO.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
