Virtual tables support

[kant2002]

I would like to discuss for possible ways how one can use virtual tables in SqlLitePCL.raw

For now I think this can be done using PInvoke + handle from Sqlite connection, but would be great if at least PInvoke API would be exposed.

Here is my sample implementation how one can create virtual tables in C#. https://github.com/kant2002/SqliteVirtualTables

I was trying to use classes/structs from SqlitePCL.raw but I face followig challenges:

• SqlitePCL uses custom marshalling, so classes like sqlite3, sqlite3_context, sqlite3_value cannot be used as is and require custom plumging
• SqlitePCL cannot use LibraryImportGenerator as is, so that's a bit annoying to work in modern C#, would be great to have some support. Not sure if it would be possible, but this is request to think about it.

Things I did not try

• Support multiple calling conventions
• Provide marshalling for sqlite3_module so it would be proper managed struct which is closer to how SqlitePCL do things.

[ericsink]

The use of LibraryImport is #528

[ericsink]

When you say "custom marshaling", are you referring to the fact that sqlite3 is a SafeHandle, for example?

[kant2002]

When you say "custom marshaling", are you referring to the fact that sqlite3 is a SafeHandle, for example?

I refere more to the fact that partially some marshalling reside in provider.tt, like working with UTF-8 strings, some hooks magic. We can say this is not "marshalling", but for me this is plumbing for the user facing API and unmanaged world, so it's ideologically same for me.

sqlite3 is actually work nice with LibraryImportGenerator, do not have issues with it.

[ericsink]

I think I understand.

There are definitely some things in there that make some cases more challenging. I'm not sure the utf8z idea was worth the trouble.

[kant2002]

I think easiest route, at least for me, would be to start with trivial functions like sqlite3_malloc/sqlite3_free/sqlite3_result_xxx/sqlite3_value_type/sqlite3_value_xxx and add them to SqlitePCL. At least that way you can show me the ropes.

https://github.com/kant2002/SqliteVirtualTables/blob/40f846a2de62252fa0e9fbb80080af56abafa073/SqliteVirtualTables/VirtualTableSupport.cs#L115-L121

Take notice that in https://github.com/kant2002/SqliteVirtualTables/blob/40f846a2de62252fa0e9fbb80080af56abafa073/SqliteVirtualTables/VirtualTableSupport.cs#L135-L145 I declare C sqlite3_context* as void* to avoid custom logic for marshalling. I think this not now SqlitePCL do things.

[ericsink]

I should clarify that I don't accept very many pull requests. And I [basically] never accept a pull request unless it addresses a specific user need.

This project is open source, but not really community-developed.

[kant2002]

That's not an issue. I already try to take a stab on this in https://github.com/ericsink/SQLitePCL.raw/pull/486 but giveup, so now I decide to try slightly different way by building out side of SqlitePCL but try to integrate later on if there is desire from you/community.

[bricelam]

@kant2002 I'm also interested in adding support for this and lighting it up in Microsoft.Data.Sqlite. If you want to work together on this in a longer-running feature branch/fork, I'd be happy to help.

[kant2002]

@bricelam now I'm interested. I was scary to work on fork/branch alone since that can be harder to use for other (probably) but if I have potential partner-in-crime then why do not try that.

My first question, are there any tutorials how I can use Microsoft.Data.Sqlite with custom built SqlitePCL ?

[bricelam]

@kant2002 Let's move the conversation over to https://github.com/bricelam/SQLitePCL.raw. I've created a project containing the big items that need to happen. We can use issues, discussions, and PRs there to collaborate.