3 ms·

I'm curious what issues you had with P/Invoke. I use SQLite's C API from C# using P/Invoke rather than using a high-level .NET library. I've implemented virtual

I'm curious what issues you had with P/Invoke. I use SQLite's C API from C# using P/Invoke rather than using a high-level .NET library. I've implemented virtual table modules, VFSes, application-defined SQL functions, you name it. I even reach into SQLite and call their internal tokenizer directly. The Visual Studio debugger was key to getting this done productively. With C#'s high level of interoperability with C, I can't imagine a similar "SQLite transpiled to C#" project gaining traction in our ecosystem. I think C# stands out from the crowd on interop with C libraries.

ncruces 3y ago
Issue one: someone else was employed professionally to develope those bindings for you. :) Once that's done correctly and at a high quality, your life is easier. Developing those bindings is another story. But PInvoke is one of the best development stories, I'm not dinging on it.
- electroly 3y ago
  No, I wrote my own bindings to the C API with P/Invoke. I _don't_ use any other high-level library in .NET. Previously I used C++/CLI, but I migrated it all to vanilla C# with P/Invoke. I have significant experience with the SQLite C API (which is why I don't bother with other people's bindings). With P/Invoke it was a pretty straightforward experience, and specifically the debugger worked great and was crucial to getting it working. I really think that C# with P/Invoke and the Visual Studio debugger is a cut above the rest.
  - ncruces 3y ago
    Oh, great! I assumed you meant the officially supported System.Data.SQLite bindings. Do you have a link or is this internal?
    - electroly 3y ago
      The meat of the P/Invoke code is in here: https://github.com/electroly/sqlnotebook/tree/master/src/SqlNotebookScript/Core/SqliteInterop https://github.com/electroly/sqlnotebook/tree/master/src/Sql... The parent directory includes code that uses it. I'm most proud of this SQLite virtual table module that proxies queries to remote ADO.NET connections, allowing you to write joins directly between local SQLite tables and remote SQL Server tables. https://github.com/electroly/sqlnotebook/blob/master/src/SqlNotebookScript/Core/AdoModules/AdoModuleProvider.cs https://github.com/electroly/sqlnotebook/blob/master/src/Sql... This is exposed to the user as a "LINK" option on the "IMPORT DATABASE" statement: https://sqlnotebook.com/import-database-stmt.html https://sqlnotebook.com/import-database-stmt.html I've also got a generic virtual table module that lets me easily write table-valued functions in C#: https://github.com/electroly/sqlnotebook/blob/master/src/SqlNotebookScript/Core/GenericModules/GenericModuleProvider.cs https://github.com/electroly/sqlnotebook/blob/master/src/Sql... I don't have too many table-valued functions yet but one example is the "LIST_XLS_WORKSHEETS" function: https://sqlnotebook.com/list-xls-worksheets-func.html https://sqlnotebook.com/list-xls-worksheets-func.html I have my own implementation of SQLite's grammar so I can embed it inside my larger grammar which adds things like DECLARE, IF, and WHILE: https://github.com/electroly/sqlnotebook/blob/master/src/SqlNotebookScript/Interpreter/SqliteGrammar.cs https://github.com/electroly/sqlnotebook/blob/master/src/Sql... The goal is to provide various "supercharged" features to base SQLite by taking advantage of all the extension points I can. I wish some went further; in particular the virtual table API doesn't "push down" enough of the original query to allow the module to avoid doing N+1 queries in some cases. Always happy to see people using SQLite in unique ways!
      - ncruces 3y ago
        Oh I wanted to implement a virtual table to do the same thing in Go (access a remote DB, with where clause push down). Will have a look at your code!
  - pjmlp 3y ago
    P/Invoke is similar to how many compiled languages used to bind to other compiled languages for many years, e.g. Modula-2, BASIC and Pascal dialects. Somehow it became common to do it in more complex ways, and the return to AOT compilation is rediscovering it. The way VS debugging allows for mixed language debugging is only comparable with Netbeans/Eclipse, but neither JNI nor Panama are as good as P/Invoke.

ncruces 3y ago

Issue one: someone else was employed professionally to develope those bindings for you. :) Once that's done correctly and at a high quality, your life is easier. Developing those bindings is another story. But PInvoke is one of the best development stories, I'm not dinging on it.

electroly 3y ago
No, I wrote my own bindings to the C API with P/Invoke. I _don't_ use any other high-level library in .NET. Previously I used C++/CLI, but I migrated it all to vanilla C# with P/Invoke. I have significant experience with the SQLite C API (which is why I don't bother with other people's bindings). With P/Invoke it was a pretty straightforward experience, and specifically the debugger worked great and was crucial to getting it working. I really think that C# with P/Invoke and the Visual Studio debugger is a cut above the rest.
- ncruces 3y ago
  Oh, great! I assumed you meant the officially supported System.Data.SQLite bindings. Do you have a link or is this internal?
  - electroly 3y ago
    The meat of the P/Invoke code is in here: https://github.com/electroly/sqlnotebook/tree/master/src/SqlNotebookScript/Core/SqliteInterop https://github.com/electroly/sqlnotebook/tree/master/src/Sql... The parent directory includes code that uses it. I'm most proud of this SQLite virtual table module that proxies queries to remote ADO.NET connections, allowing you to write joins directly between local SQLite tables and remote SQL Server tables. https://github.com/electroly/sqlnotebook/blob/master/src/SqlNotebookScript/Core/AdoModules/AdoModuleProvider.cs https://github.com/electroly/sqlnotebook/blob/master/src/Sql... This is exposed to the user as a "LINK" option on the "IMPORT DATABASE" statement: https://sqlnotebook.com/import-database-stmt.html https://sqlnotebook.com/import-database-stmt.html I've also got a generic virtual table module that lets me easily write table-valued functions in C#: https://github.com/electroly/sqlnotebook/blob/master/src/SqlNotebookScript/Core/GenericModules/GenericModuleProvider.cs https://github.com/electroly/sqlnotebook/blob/master/src/Sql... I don't have too many table-valued functions yet but one example is the "LIST_XLS_WORKSHEETS" function: https://sqlnotebook.com/list-xls-worksheets-func.html https://sqlnotebook.com/list-xls-worksheets-func.html I have my own implementation of SQLite's grammar so I can embed it inside my larger grammar which adds things like DECLARE, IF, and WHILE: https://github.com/electroly/sqlnotebook/blob/master/src/SqlNotebookScript/Interpreter/SqliteGrammar.cs https://github.com/electroly/sqlnotebook/blob/master/src/Sql... The goal is to provide various "supercharged" features to base SQLite by taking advantage of all the extension points I can. I wish some went further; in particular the virtual table API doesn't "push down" enough of the original query to allow the module to avoid doing N+1 queries in some cases. Always happy to see people using SQLite in unique ways!
    - ncruces 3y ago
      Oh I wanted to implement a virtual table to do the same thing in Go (access a remote DB, with where clause push down). Will have a look at your code!
- pjmlp 3y ago
  P/Invoke is similar to how many compiled languages used to bind to other compiled languages for many years, e.g. Modula-2, BASIC and Pascal dialects. Somehow it became common to do it in more complex ways, and the return to AOT compilation is rediscovering it. The way VS debugging allows for mixed language debugging is only comparable with Netbeans/Eclipse, but neither JNI nor Panama are as good as P/Invoke.