Virtual Table xRowid

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Virtual Table xRowid

Kevin Martin
Hi,

My questions are basically:

1) What causes sqlite3 to call xRowid
2) If I don't want to update my virtual table, or do a select ROWID, can I just use an incremental counter, increased on every call to xNext (bearing in mind the order of my data is not guaranteed, so this won't necessarily return the same rowid for the same row on separate calls to xFilter), or even better just return SQLITE_ERROR?

The problem itself is detailed below.

Thanks,
Kevin

---------------------------------------

I am creating virtual table that unpacks the contents of another table/view.

As a simple example, consider the following:

create table t(week, performance_csv)

Where each row contains a csv file for the performance of the given week, and each csv file has two columns, person and percentage.

I want to create a virtual table as follows:

create virtual table t2 using unpack_performance(t)

This table will declare the table as follows

create table t2(week, person, percentage)

and it will iterate over each row of each csv in the table t.

Implementing xRowid is easy because I can base it off the ROWID of t and number of lines in the csv file.

However, the problem comes when t is a view, I've only just discovered that views don't have ROWIDs!

I can quite happily just increment a simple counter, but the view isn't necessarily ordered, so the rowid won't match on multiple filters.

In my actual application the view is the union of a few selects, so actually including the rowid in the view is going to be a complete pain, and is not an option I want to consider.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Virtual Table xRowid

Dan Kennedy-4
On 02/06/2014 01:22 AM, Kevin Martin wrote:
> Hi,
>
> My questions are basically:
>
> 1) What causes sqlite3 to call xRowid
> 2) If I don't want to update my virtual table, or do a select ROWID, can I just use an incremental counter, increased on every call to xNext (bearing in mind the order of my data is not guaranteed, so this won't necessarily return the same rowid for the same row on separate calls to xFilter), or even better just return SQLITE_ERROR?

One more case. If you have a query like this:

   SELECT ... FROM vtable WHERE vtable.a = ? OR vtable.b = ?;

and the implementation of vtable can handle queries for both
(vtable.a=?) and (vtable.b=?) efficiently, then SQLite might
make two separate sets of calls to xFilter/xStep - one to obtain
the set of rows for (a=?) and a second to obtain the set that
match (b=?). It then merges the two sets together using the
rowids as a unique key. If the rowids in the two sets are not
consistent, then you might end up returning duplicate rows to
the user or omitting rows from the result.

But is it the case that your virtual table doesn't handle
anything other than full scans of the entire table contents? If
so, it's probably not a problem if rowids are inconsistent.

Dan.





>
> The problem itself is detailed below.
>
> Thanks,
> Kevin
>
> ---------------------------------------
>
> I am creating virtual table that unpacks the contents of another table/view.
>
> As a simple example, consider the following:
>
> create table t(week, performance_csv)
>
> Where each row contains a csv file for the performance of the given week, and each csv file has two columns, person and percentage.
>
> I want to create a virtual table as follows:
>
> create virtual table t2 using unpack_performance(t)
>
> This table will declare the table as follows
>
> create table t2(week, person, percentage)
>
> and it will iterate over each row of each csv in the table t.
>
> Implementing xRowid is easy because I can base it off the ROWID of t and number of lines in the csv file.
>
> However, the problem comes when t is a view, I've only just discovered that views don't have ROWIDs!
>
> I can quite happily just increment a simple counter, but the view isn't necessarily ordered, so the rowid won't match on multiple filters.
>
> In my actual application the view is the union of a few selects, so actually including the rowid in the view is going to be a complete pain, and is not an option I want to consider.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Virtual Table xRowid

Kevin Martin

On 5 Feb 2014, at 18:40, Dan Kennedy <[hidden email]> wrote:

> But is it the case that your virtual table doesn't handle
> anything other than full scans of the entire table contents? If
> so, it's probably not a problem if rowids are inconsistent.

Thanks for the heads up on the 'or' case, I didn't know that. I don't use xBestIndex, so the table only supports full scans.

I think I'll try it with returning SQLITE_ERROR in xRowid, because I'd rather see the error than have strange results happen silently, I'll, watch what happens. One of the columns in the underlying table/view is unique, so one solution is to order by that column when doing the internal select, and then the auto generated row ids will be consistent, but that just feels like it adds a lot of calculation I don't want to do, and won't be necessary most of the time.

Thanks,
Kevin
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users