Re: [sqlite] Virtual Table: xRowID shortcommings

[Ralf Junker]

>> 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

[Dan Kennedy]

> 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