ADO.NET V2 Provider revisited

Developers
2005-02-17
2013-04-15
  • Robert Simpson
    Robert Simpson
    2005-02-17

    The latest experimental code is at:

    http://www.blackcastlesoft.com/files/system.data.sqlite.zip

    I rewrote it into C#.  Here's the rundown:

    - This code is a scratch re-write of a provider for .NET 2.0, using the 2.0 bells and whistles.  It is not a back-port of the the sourceforge project found here.

    - I have slightly modified the SQLite source distribution to support a special pragma for getting table schema.  The next version will eliminate this pragma entirely and use the stock source straight from sqlite.org -- I'm rewriting it to parse the EXPLAIN opcode output.

    - ADO.NET 2.0's Connection classes now support full schema retrieval, and so does this SQLite provider.  MetaDataCollections, DataSourceInformation, Columns, Tables, Indexes, Views, all the major schema types are supported.

    - I haven't tested everything yet, and I also haven't finished several pieces. 

    - The Connection object currently accepts but ignores things like Cache Size and Synchronization settings in the connection string.  Easy to fix, but I just forgot to do it before I posted it.

    - I haven't finished writing a SQLiteDataProvider class, and there may be a couple other base classes I might be missing.

    Robert

     
    • Robert Simpson
      Robert Simpson
      2005-02-21

      Muahaha, I'm having a ton of fun with this thing.

      I haven't reposted the latest, code, but here's what I got going on...

      - Revamped the code for better future support of the Compact Framework (*NOW* I understand what all the fuss was about! -- CF framework is a pain in the butt!)
      - DbProviderFactory support, just add the XML below at the machine.config and/or app.config level.
      - Full support for ATTACH'ed databases.  Exposed as Catalogs in the schema.  When cloning a connection, all attached databases are automatically re-attached to the new connection.
      - DbConnection.GetSchema(...) support includes the MetaDataCollections, DataSourceInformation, Columns, Tables, Views, Catalogs and Indexes keywords.
      - Enhanced DbDataReader.GetSchemaTable() functionality returns catalog, namespace and detailed schema information even for complex queries.
      - Named and unnamed parameters. Full UTF-8 and UTF-16 support.
      - Multiple simultaneous DataReaders (one DataReader per Command however).
      - Full support for user-defined scalar and aggregate functions, encapsulated into easy-to-use base classes in which only a couple of overrides are necessary to implement new SQL functions.
      - Planned support for user-defined collations.

      In order to use the SQLiteFactory and have the SQLite data provider enumerated in the DbProviderFactories methods, you must add the following segment into either your application's app.config or the system's machine.config located in the %SystemRoot%\Microsoft.Net\Framework\v2.xxxx\Config folder:

      <configuration>
        <system.data>
          <DbProviderFactories>
            <add name="SQLite Data Provider" invariant="System.Data.SQLite" support="3F" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
          </DbProviderFactories>
        </system.data>
      </configuration>

       
    • Robert Simpson
      Robert Simpson
      2005-02-21

      User-defined functions was a little bit of a pain to implement, but here's an example of how one is implemented in my code:

        class TestFunc : SQLiteScalarFunction
        {
          static TestFunc()
          {
            SQLiteScalarFunction.Register("TestFunc", 1, typeof(TestFunc));
          }

          public override void Invoke(SQLiteContext context)
          {
            int Param1 = context.GetInt32(0); // First parameter
            int Param2 = context.GetInt32(1); // Second parameter

            context.ReturnInt32(Param1 + Param2); // Set the return value
          }
        }

      I have a minor issue stemming from the fact that static constructors of classes are not invoked unless something references that class.  I may have better luck emitting some kind of attribute tag for the class that'll invoke the static constructor or somesuch thing.

       
    • Robert Simpson
      Robert Simpson
      2005-02-22

      Ok, I've posted the latest code, still at:
      http://www.blackcastlesoft/com/files/system.data.sqlite.zip

      Features:
      DbProviderFactory support, just add the XML below at the machine.config and/or app.config level.

      Full support for ATTACH'ed databases.  Exposed as Catalogs in the schema.  When cloning a connection, all attached databases are automatically re-attached to the new connection.

      DbConnection.GetSchema(...) support includes the MetaDataCollections, DataSourceInformation, Columns, Tables, Views, Catalogs and Indexes keywords.

      Enhanced DbDataReader.GetSchemaTable() functionality returns catalog, namespace and detailed schema information even for complex queries.

      Named and unnamed parameters.

      Full UTF-8 and UTF-16 support.

      Multiple simultaneous DataReaders (one DataReader per Command however).

      Full support for user-defined scalar and aggregate functions, encapsulated into an easy-to-use base class in which only a couple of overrides are necessary to implement new SQL functions.

      Planned support for user-defined collations. 

      In order to use the SQLiteFactory and have the SQLite data provider enumerated in the DbProviderFactories methods, you must add the following segment into either your application's app.config or the system's machine.config located in the %SystemRoot%\Microsoft.Net\Framework\v2.xxxx\Config folder:

      <configuration>
        <system.data>
          <DbProviderFactories>
            <add name="SQLite Data Provider" invariant="System.Data.SQLite" support="3F" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
          </DbProviderFactories>
        </system.data>
      </configuration>

      Development Notes Regarding the SQLITE3 Source Code
      *** At this time, the necessary changes to the DLL to support Windows CE have not been completed.  All function calls that return or pass a 64-bit value have been wrapped, but the necessary OS changes have not been finished.

      The version of sqlite3 that comes with this source distribution varies from the core codebase at www.sqlite.org in the following ways:

      A pragma called REAL_COLUMN_NAMES was added to the core codebase.  When active, it returns the full dotted names of table-bound columns, ignoring any aliases on a SELECT clause (pertaining to column names).   This pragma is used by the .NET code during calls to SQLiteDataReader.GetSchemaTable() to provide enhanced schema information for an arbitrary SELECT clause of varying levels of complexity.

      Most of the sqlite3_xxx API functions now have a sqlite3_xxx_interop wrapper function.  These exist because in .NET all DllImport functions are assumed to be of type __stdcall.  Although the full .NET Framework allows you to change the calling convention to CDecl, the .NET Compact Framework does not.  .NET will take steps to correct stack errors as a result of mismatched calling conventions, but it adds overhead, and it is a bad idea to intentionally take advantage of such stack corrections as there's no guarantee it will work across different versions of the framework or on the Compact Framework.

      Callbacks in the DLL have also been wrapped where utilized.  Callback interop functions in .NET MUST BE STDCALL in format, and since sqlite3 natively only does callbacks in CDecl, they are incompatible with .NET.  Although it is possible using the IL disassembler to remap calls, it is definitely not the desired norm for building modules that interop with sqlite3.

      Merging the sqlite3 codebase is pretty straightforward.  The only changes within the core that really must be made when merging are to the following files:

      sqliteint.h - adds a constant to define PRAGMA_RealColNames

      select.c - adds code to support the RealColNames pragma in the generateColumnNames() function

      pragma.c - code added to support the PRAGMA_RealColNames flag.

      The only other file that needs to be added to any new merged version of the sqlite3 source is the interop.c file, which contains all the wrappers.

       
      • Tim McDaniel
        Tim McDaniel
        2005-02-23

        Robert,

        Great effort.
        One question so far:  I read the stdcall vs cdecl explanation.  I'm a bit puzzled though.  Why did we not need to do something similar (to your xxx_interop functions) for the existing 1.1 provider?

        Tim

         
        • Robert Simpson
          Robert Simpson
          2005-02-23

          The problem is, that you DO need it.  It works right now because .NET is automatically correcting the stack frame after each and every call to the sqlite3 DLL.

          This is what I get when I run a debug test program and a debug version of both of our ado.net providers in VS2005 with unmanaged code debugging enabled:

          Managed Debugging Assistant 'PInvokeStackImbalance' has detected a problem in 'C:\src\SQLite.NET\test\bin\Debug\test.exe'.

          Additional Information: A call to PInvoke function 'System.Data.SQLite!System.Data.SQLite.SQLite3::sqlite3_create_function_interop' has unbalanced the stack. This is likely because the managed PInvoke signature does not match the unmanaged target signature. Check that the calling convention and parameters of the PInvoke signature match the target unmanaged signature.

          See sdk\bin\mdaBoilerplate.exe.config for documentation.Managed Debugging Assistant 'PInvokeStackImbalance' has detected a problem in 'C:\src\SQLite.NET\test\bin\Debug\test.exe'.

          Additional Information: A call to PInvoke function 'System.Data.SQLite!System.Data.SQLite.SQLite3::sqlite3_create_function_interop' has unbalanced the stack. This is likely because the managed PInvoke signature does not match the unmanaged target signature. Check that the calling convention and parameters of the PInvoke signature match the target unmanaged signature.

          See sdk\bin\mdaBoilerplate.exe.config for documentation.Managed Debugging Assistant 'PInvokeStackImbalance' has detected a problem in 'C:\src\SQLite.NET\test\bin\Debug\test.exe'.

          ...
          etc etc etc ... every call hit this message, which means that .NET is automatically correcting the stack after every call to sqlite3.

          Robert

           
    • Robert Simpson
      Robert Simpson
      2005-02-23

      I added support for user-defined collating sequences.  Latest code is posted.

      Hope someone finds it useful.  :)