Re: [sqlite] Virtual Table: xRowID shortcommings
>> 2. In case the virtual table implementation needs to allocate memory in >> order to uniquely >> describe a row/item, this memory needs to be freed when no longer used. As I >> see it, there is no >> such method in the Virtual Table implementation. > >Maybe the transaction part of the virtual table API is useful in >this context (xBegin/xSync/xCommit/xRollback). SQLite will only store >values retrieved via xRowid for the duration of a transaction. So >if you need to create a mapping between the integer id's used by >SQLite and the complex identifiers, you can throw the table away >at the end of the transaction. > >A linked list and a willingness to cast pointers to 64-bit integers >seems like it would do the trick :) This willingness is certainly there, but with many thousand modifications in a single transaction, this would accumulate a couple thousant memory allocations and free them only after the transaction is committed. Very ineffective, in my opinion :( >Of course that won't help with sqlite apps that expect the rowid >field to remain persistent between queries that span multiple >transactions (i.e. MS Access style GUIs etc.). Correct. Quite a few DBMS simply do not use and support the concept of integer rowids. The current xRowID implementation would not allow to access those as virtual tables from SQLite. To support those as well, I believe that the SQLite type integer rowids could be made optional for virtual tables and another, more flexible approach could instead be made available (as proposed in my previous mailing). This would of course mean that those virtual tables would not have a rowid column and SQLite would return the usual 'No such solumn: rowid'. But I do not see any problems to this from a user's perspective.
Re: [sqlite] Virtual Table: xRowID shortcommings
> I have played with the new Virtual Table interface from CVS and found some > shortcommings for the > current implementation of the xRowID method: > > int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid); > > As far as I understand, this function is called by SQLite whenever it needs a > unique identifer > for a row/item as a signed 64 bit integer type. SQLite uses this to pass it > back to other > Virtual Table methods (most notably xUpdate) so the Virtual Table > implementation can use this > identifier to locate a particular row/item for deletes, inserts, or updates. Quite correct. It will also be accessible as the "rowid" field via SQL. > I can see two problems here: > > 1. Using a signed 64 bit integer type works fine for SQLite, but might be > insufficient for other > Virtual Table implementations. Imagine a Virtual Table which maps the files > of a disk/directoy. > In Windows, for example, a unique ITEMIDLIST can be generated for a file or > folder. However, > this ITEMIDLIST is not a signed 64 bit integer, so mapping an ITEMIDLIST to > the current > implementation is not easily possible. I can even imaginge other scenarios > where the virtual > table implementation must allocate a special memory structure larger than 64 > bit to uniquely > identify a row/item. > 2. In case the virtual table implementation needs to allocate memory in order > to uniquely > describe a row/item, this memory needs to be freed when no longer used. As I > see it, there is no > such method in the Virtual Table implementation. Maybe the transaction part of the virtual table API is useful in this context (xBegin/xSync/xCommit/xRollback). SQLite will only store values retrieved via xRowid for the duration of a transaction. So if you need to create a mapping between the integer id's used by SQLite and the complex identifiers, you can throw the table away at the end of the transaction. A linked list and a willingness to cast pointers to 64-bit integers seems like it would do the trick :) Of course that won't help with sqlite apps that expect the rowid field to remain persistent between queries that span multiple transactions (i.e. MS Access style GUIs etc.). __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Virtual Table: xRowID shortcommings
Hello All, I have played with the new Virtual Table interface from CVS and found some shortcommings for the current implementation of the xRowID method: int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid); As far as I understand, this function is called by SQLite whenever it needs a unique identifer for a row/item as a signed 64 bit integer type. SQLite uses this to pass it back to other Virtual Table methods (most notably xUpdate) so the Virtual Table implementation can use this identifier to locate a particular row/item for deletes, inserts, or updates. I can see two problems here: 1. Using a signed 64 bit integer type works fine for SQLite, but might be insufficient for other Virtual Table implementations. Imagine a Virtual Table which maps the files of a disk/directoy. In Windows, for example, a unique ITEMIDLIST can be generated for a file or folder. However, this ITEMIDLIST is not a signed 64 bit integer, so mapping an ITEMIDLIST to the current implementation is not easily possible. I can even imaginge other scenarios where the virtual table implementation must allocate a special memory structure larger than 64 bit to uniquely identify a row/item. 2. In case the virtual table implementation needs to allocate memory in order to uniquely describe a row/item, this memory needs to be freed when no longer used. As I see it, there is no such method in the Virtual Table implementation. Propsal: A. Maintain the current xRowID interface, but make sure that the sqlite_int64 * can be used by the virtual table implementation in any way it likes, meaning that it is not used internally by SQLite. However, this would not allow counts on the virtual table RowIDs. B. Add a new xFreeRowID merhod, which allows the virtual table implementation to deallocate / free memory which was previously allocated in order to properly describe a row/item. C. Optionally choose a more telling name for xRowID and xFreeRowID to imply a behaviour similar but not equal to SQLite, for example xGetBookmark and xFreeBookmark. D. Optionally offer a second set of methods as proposed in C in addition to the existing xRowID. If xRowID is NULL, there is no RowID available for the given Virtual Table and xGetBookmark and xFreeBookmark are being triggered. if xRowID is not NULL, xGetBookmark and xFreeBookmark are never being used. These are just some suggestions. However, I feel that the Virtual Table idea is currently limited by the 64 bit RowID. If this limit is relaxed, this great idea can be applied to a far wider range of usages. Best Regards, Ralf
