Virtual tables

[vrischmann]

Initial work on virtual tables. The goal is to be able to easily create virtual tables using Zig and expose as little as possible of the C-ism of the raw sqlite interface.

Currently implementing a virtual table is done by providing a Table type which must implement the following:

const Cursor = <cursor type ...>

const InitError = error{...};
const BuildBestIndexErrror = error{...};

fn init(allocator: mem.Allocator, diags: *sqlite.vtab.VTabDiagnostics) InitError!*Table
fn deinit(table: *Table, allocator: mem.Allocator) void
fn buildBestIndex(table: *Table, diags: *sqlite.vtab.VTabDiagnostics, builder: *sqlite.vtab.BestIndexBuilder) BuildBestIndexError!void

The cursor type must implement the following:

const InitError = error{...};
const NextError = error{...};
const HasNextError = error{...};
const FilterError = error{...};
const ColumnError = error{...};
const RowIDError = error{...};

fn init(allocator: mem.Allocator, parent: *Table) InitError!*Cursor
fn deinit(cursor: *Cursor) void
fn next(cursor: *Cursor, diags: *sqlite.vtab.VTabDiagnostics) NextError!void
fn hasNext(cursor: *Cursor, diags: *sqlite.vtab.VTabDiagnostics) HasNextError!bool
fn filter(cursor: *Cursor, diags: *sqlite.vtab.VTabDiagnostics, sqlite.vtab.IndexIdentifier) FilterError!bool
fn column(cursor: *Cursor, diags: *sqlite.vtab.VTabDiagnostics, column_number: i32) ColumnError!Column
fn rowId(cursor: *Cursor, diags: *sqlite.vtab.VTabDiagnostics) RowIDError!i64

It's relatively complete and should largely abstract the raw sqlite interface but one thing is missing: passing the arguments to the filter function. SQLite gives us an array of sqlite3_value and I'll have to think longer on how I want to convert this to idiomatic Zig types.

I'm building a demo here (not completely ready yet)

[vrischmann]

I've ended up with an API for filter that's not too bad:

pub const FilterArg = struct {
    value: ?*c.sqlite3_value,

    pub fn as(self: FilterArg, comptime Type: type) Type {
        ...
    }
};

pub fn filter(cursor: *Cursor, diags: *sqlite.vtab.VTabDiagnostics, index: sqlite.vtab.IndexIdentifier, args: []sqlite.vtab.FilterArg) FilterError!void {
    ...
}

you would use the arg like this:

const arg1 = filter_arg1.as([]const u8);
const arg2 = filter_arg2.as(usize);
...

as uses helpers.setTypeFromValue which is also used for the arguments of a user function.

[vrischmann]

Still to do:

• improve the tests
• implement xCreate and xDestroy
• add connect, disconnect called by xConnect and xDisconnect respectively
• add an argument parser for args received on xCreate

I'm not going to implement all the "write" functions (xUpdate etc); this will come at a later point.

For tests I'm thinking I could pass a bunch of random parameters as arguments to the virtual table creation, then since I know these parameters I can check that the rows I fetched match the rows generated.

[jiacai2050]

https://github.com/vrischmann/zig-sqlite-vtab-demo/blob/09f5fac452f3acdf52f35bef7340809b089e82ab/src/vtab_apida_ext.zig#L14

Yes, I think we need some C stuff to glue zig code, there is a go library use similar tricks.

• https://github.com/riyaz-ali/sqlite/blob/master/extension.c In extension.c, it will call a go func go_sqlite3_extension_init, and go_sqlite3_extension_init is an exported FFI function which used to bridge C and Go code.

• https://github.com/riyaz-ali/sqlite/blob/master/_examples/series/series.go This is a demo to to write gen_series module in Go

Hope this help you.

[vrischmann]

Thanks ! I'll take a look at your links once I get back to working on building an extension.

[vrischmann]

The abiilty to build loadable extensions is part of #112 now. This PR will focus only on virtual tables.

[vrischmann]

I'm still not 100% sure when/how to use xCreate vs xConnect and xDestroy vs xDisconnect, I think I'll implement in another PR. The arguments are now parsed into a more usable type.

I'll focus next on improving tests and that will be it for this PR I think.