Microsoft.Data.Sqlite: Virtual table modules

[bricelam]

SQLite supports creating virtual tables that are implemented by the host application. We should create an API for doing this. See Virtual Table Object.

[bricelam]

Apparently these things can be used as TVFs. See the table-valued functions section.

(cc @divega)

[AlexanderTaeschner]

We would need to have access to sqlite3_create_module or sqlite3_create_module_v2 in SQLitePCL.raw first.

[ericsink]

Yeah, that's an area of the sqlite API that I never implemented.

But I could...

:-)

[bricelam]

(Depends on ericsink/SQLitePCL.raw#191)

[fdcastel]

This is a fantastic feature of SQLite! It's really sad it isn't available in netcore.

@ericsink any chance of unleash ericsink/SQLitePCL.raw#191 for us, mere mortals? 😉

I'm really interested to work on this (with an API similar to System.Data.SQLite, with room for improvements). But for now I'm yet trying to build https://github.com/ericsink/SQLitePCL.raw (and failing miserably 😄).

[ericsink]

@fdcastel Nobody needs my permission to work on a feature of SQLitePCL.raw. It's open source, so if you want to proceed, feel free.

Admittedly, getting your changes merged into my repo is another story. :-)

The real problem here is that SQLitePCL.raw is too hostile to work on. I'm planning some improvements in that area, to make things easier for other developers who want to collaborate. But I haven't got much done in that area yet.

[bricelam]

(I've considered starting a PR, but my TODO list is pretty big...)

[fdcastel]

@bricelam if you already made any work on this subject (even if largely incomplete/unusable) and could share it, it would be great.

I'm just starting to grasp some concepts of SQLitePCL.raw.

The good thing is: It's building now! 😄

[bricelam]

I remember starting on a P/Invoke version of sqlite3_module similar to our or old sqlite3_vfs implementation, but decided I wasn't ready to see it through and threw it away.

[bricelam]

It would be nice to create sugar over this for TFVs. Something like...

connection.CreateTVF("generate_series", GenerateSeries);

record Row(int Value);

static IEnumerable<Row> GenerateSeries(int start, int stop, int step)
{
    for (var i = start; i <= stop; i += step)
    {
        yield return new (Value: i);
    }
}

SELECT value FROM generate_series(1, 10, 3);

Unfortunately, I don't see how we'd synthesize rowid (primary key) values with this approach. ☹