Virtual table function calls

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

Virtual table function calls

David Jones
I’ve written a virtual table to view spreadsheet data inside Excel workbook (.xlsx) files as SQlite tables. I want to implement an SQL function, attr(), which the virtual table overrides to give access to the raw attributes of the cell being returned. I’m looking for a robust way to find the actual column refered to by an overriden function, not just the value returned by the xColumn call.

Example:
   sqlite> .load xlsxvtab.so                                         # creates xlsxvtab module and attr() function
   sqlite> create virtual table summary using xlsxvtab(‘expenses.xlsx’,’sheet1’,’F20’, ‘H32’);
   sqlite> select F,G,H,attr(H,3) from summary;           # show formula used to calculate column H.

The issue is that attr gets called with 2 values and I need to divine that the first value came from column H of the current row of the cursor opened by this select statement. The hack I’m using now is to give the values returned by the xColumn method a subtype equal to the column number. In the attr() function I retrieve the subtype and re-fetch that column from the current row of the last cursor open on that table.

Various pitfalls with this technique:
    - Subtype numbers are limited to the range 0-255.
    - Assumes all functions called before xNext() method called again.
    - Complex queries with multiple cursors?

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

Re: Virtual table function calls

Jens Alfke-2


> On Feb 13, 2020, at 12:52 PM, David Jones <[hidden email]> wrote:
>
>   sqlite> select F,G,H,attr(H,3) from summary;           # show formula used to calculate column H.

Maybe pass the column name as a string, i.e. `attr('H',3)`? It sounds like your `attr` function needs to know the _identity_ of the column, not its contents, and the name is basically the identity.

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

Re: Virtual table function calls

David Jones
In reply to this post by David Jones
Jens Alfke asks:
>Maybe pass the column name as a string, i.e. `attr('H',3)`

2 problems with that idea. First, the first argument has to refer to a value in the virtual table in order to invoke the overridden version (overrides are per table, so I use the ppArg to bind function invocation to associated virtual table). Second, if I created a view that gives column H a name of Total, I’d want to the function call to be attr(Total,3). I don’t know how to make the function figure out that attr(‘Total’,3) really means column H in some table.
>>Dave
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users