Using DbProviderFactory within NH's IDriver implementations

[Pablo Ruiz]

Hi,

By reading the details at http://216.121.112.228/browse/NH-1378 looks like this was an old request (1.2.1 days) which has been considered already. How ever (surelly due to time constraints ;)) this is still an open issue, which may be considered now for NH3.

I have a couple of classes implementing a base driver using DbProviderFactory, and I have attached them to the jira issue, so anyone can review them... This one are copied from my own project, but they are simple enought to get an starting point from which a discussion can be started.

The point behind this issue (for me) it's mainly mono compatibility, as mono does not implement 'BindingRedirect' support, and as such, the only way of making NH work it's by placing the ado.net provider's assembly at bin folder, which in some deployments it's far from perfect.

As such, if having alternate driver implementations using Reflection (current one) and DbProviderFactory (the alternate one) looks great for Fabio & co, I can provide fully-functional patches against trunk, just letme know about it.. ;)

Saludos.

Att. Pablo

[Diego Mijelshon]

This is a great patch, we currently have to copy IBM.Data.Informix.dll everywhere.

I can create the DPF-based Informix driver if this gets included.

    Diego

[Fabio Maulo]

connection.multiple_query_separator ?

How many new configuration parameters we will have ?

--
Fabio Maulo

[Diego Mijelshon]

To paraphrase what we tell to newbies about table-count: do you get charged for configuration settings? :-)
 
    Diego

[Jason Dentler]

LOL. 

This sounds good. It may help with some of the 32-bit / 64-bit dance we have to do with SQLite.dll, though I haven't studied it enough to know for sure.

[Pablo Ruiz]

Well, this was just an idea to allow runtime configuration without recompiling..

The whole DbProviderFactory idea is just not having to recompile to change provider, so it seemed something good having a 'configurable' provider.. ;)

However, there's also DbProviderDriver, which it's supposed to be used as base class on each 'fixed-and-not-configurable-drivers'..

So one flavor for each person.. heh

[Diego Mijelshon]

"Welcome to the world of options. Welcome to NHibernate 3!" ;-)
 
    Diego

[Fabio Maulo]

On Fri, Aug 13, 2010 at 1:40 PM, Diego Mijelshon <di...@mijelshon.com.ar> wrote:

"Welcome to the world of options. Welcome to NHibernate 3!" ;-)

But that does not mean that you have to configure 70 parameters instead implement a class (outside NH) and use just one configuration parameter.

The driver is injectable for some reason.

--
Fabio Maulo

[Fabio Maulo]

You don't have to.

In our tests you can see how redirect a partial assembly-name.

--
Fabio Maulo

[Pablo Ruiz]

You mean on windows/.net o also on mono?

[Diego Mijelshon]

I'm not saying it's the best option (I prefer the DbProviderFactory one), but it doesn't hurt anyone.
 
    Diego

[Fabio Maulo]

for sure... we are talking about C# and .NET... to hurt somebody we need a katana.

--
Fabio Maulo

[Diego Mijelshon]

I see... I didn't know that.

However, there's a problem with that solution: I lose version independency.

That problem is fixed by the DbProviderDriver without introducing any problems that I can think of.
 
    Diego

[Fabio Maulo]

That solution is the solution of Microsoft and give you the ability to work with multiple versions of the same assembly installed in the GAC.

The usage of DbProvider is not a problem but shouldn't be so complicated as proposed. It should be completely transparent; the NH users should have 0 changes to work with or without DbProvider.

--
Fabio Maulo

[Diego Mijelshon]

You can't have your cake and eat it too.

We can either make the existing drivers inherit from DbProviderDriver where it makes sense (must-install providers, mostly), or have a new set of drivers.

...or we can use a fallback logic that tries reflection first and, if unsuccessful, goes to DPF.

 
    Diego

[Fabio Maulo]

You can check the existence of a DLL even without use reflexion.

Then we can use the InvariantName.

Can you provide InvariantName for all RDBMS supported by NH ?

--
Fabio Maulo

[Diego Mijelshon]

We can crowdsource that...

Let's see which drivers are currently reflection-based:

Adaptive Server Anywhere

DB2

Firebird (two versions of this?)

Informix

Ingres

MySQL

PostgreSQL

Oracle

SQLite

SQL CE

Sybase

In any case, we can have and overload in ReflectionBasedDriver that takes a providerName. If that overload is not used, nothing changes. That will allow us to port the drivers one by one without breaking existing stuff.

    Diego

[Fabio Maulo]

I would see the list of InvariantName.

Have you the list of InvariantName used by your preferred RDBMS ? 

--
Fabio Maulo

[Pablo Ruiz]

Forgetting about 'ConfigurableDriver'.. I think we can simple modify 'ReflectionBasedDriver' to add two constructors overloads, one which allows passing a providerName and another one which allows passing a providerName and the same three arguments it allows now (assembly, connection class and command class).

This way, it will be a matter of each Driver using one of the three ctors to specify which methods are supported by this driver.

However, I see at least, one property to make drivers supporting both methods, favor DbProviderFactory instead of Reflection (of course for compatibility reasons, the default method would be Reflection). This way users can switch over to DbProviderFactory just by setting some property like 'adonet_dbprovider=true'..

Comments?

[Diego Mijelshon]

My idea was actually to have an overload with the providerName IN ADDITION TO driver/connType/cmdType. That way, if the assembly is found, we use it. Otherwise, we fall back to the providerName.

The advantage is that users relying on the current resolution logic won't notice any changes.
 
    Diego

[Fabio Maulo]

Congratulation to both... you are closer...

Now I would see the list of InvariantName... a little list nothing more

--
Fabio Maulo

[Frans Bouma]

> I see... I didn't know that.
> However, there's a problem with that solution: I lose version
independency.
> That problem is fixed by the DbProviderDriver without introducing any
> problems that I can think of.

DbProviderFactory has some problems with ODP.NET: if you want to
have named parameters, you have to set the BindByName property on the
OracleConnection. This is a property on the class provider by ODP.NET, not
in DbConnection. I've to check, but if memory serves me correctly, there's
also a problem with Sybase ASA.

So in short, even if you use DbProviderFactory, you need some
reflection to use it.

FB

[Fabio Maulo]

ok

--
Fabio Maulo

[Diego Mijelshon]

Just posted here: http://stackoverflow.com/questions/3481050/invariant-names-for-different-ado-net-providers

We'll probably have most of them by tomorrow.
 

Informix is "IBM.Data.Informix", PostgreSQL is "Npgsql"

    Diego

[Diego Mijelshon]

Frans,

Reflection is fine; the idea is not to eliminate reflection, but to ease deployment.
 
    Diego

[Frans Bouma]

> Reflection is fine; the idea is not to eliminate reflection, but to ease
> deployment.

I know reflection is ok, you already have it ;). I just pointed to a
problem you might run into when going for dbproviderfactory. Easy
deployment: dbproviderfactory is supported by all ADO.NET 2.0+ providers, so
if you support dbproviderfactory, it's always going to work. There's 1
caveat: not all providers install a line in machine.config for their
factory, e.g. firebird client and npgsql don't, so you have to manually add
that factory definition to your config file but that's about it.

[Frans Bouma]

> Just posted here: http://stackoverflow.com/questions/3481050/invariant-
> names-for-different-ado-net-providers
> <http://stackoverflow.com/questions/3481050/invariant-names-for-different-
> ado-net-providers> We'll probably have most of them by tomorrow.

>
> Informix is "IBM.Data.Informix", PostgreSQL is "Npgsql"

* Adaptive Server Anywhere: "iAnywhere.Data.SQLAnywhere"
* DB2: "IBM.Data.DB2"
* Firebird: "FirebirdSql.Data.FirebirdClient"
* Ingres: (don't know)
* MySQL: "MySql.Data.MySqlClient"
* Oracle: ODP.NET: "Oracle.DataAccess.Client"
* Oracle: MS Oracle: "System.Data.OracleClient"
* SQLite: (don't know)
* SQL CE: v3.0: "System.Data.SqlServerCe".
* SQL CE: v3.5: "System.Data.SqlServerCe.3.5".
* Sybase ASE: "Sybase.Data.AseClient"

FB

[Jason Dentler]

According to this thread, SQLite is "System.Data.SQLite"

http://sqlite.phxsoftware.com/forums/t/1886.aspx

I haven't tested it myself. FYI - It's installed, but didn't create an entry in machine.config.

[Jason Dentler]

Yep. The readme for the SQLite provider confirms it.

http://sqlite.phxsoftware.com/readme.htm

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

[Jason Dentler]

Ingres is "Ingres.Client"

http://code.ingres.com/ingres/main/src/common/dotnet2_win/ingres/factory.cs

[Fabio Maulo]

Ok now we have enough material to implements the solution.

1) new interface IDriveConnectionCommandProvider (CreateConnection, CreateCommand)

2) 2 impls of IDriveConnectionCommandProvider; one with creation through Bytecode the other through DbProviderFactory

3)The ReflectionBasedDriver needs 4 parameters; the newone is the InvariantName.

if (connectionType == null || commandType == null)

{

then first try to retrieve info from DbProviderFactories: where available then create appropriate IDriveConnectionCommandProvider 

else throws

}

else

{

create appropriate IDriveConnectionCommandProvider (creation through Bytecode)

}

4) The method CreateConnection() and CreateCommand() will delegate the responsibility to IDriveConnectionCommandProvider 

--
Fabio Maulo

[Fabio Maulo]

LOL!!!...

The only one not following the rule was Microsoft

   * SQL CE: v3.0: "System.Data.SqlServerCe".
   * SQL CE: v3.5: "System.Data.SqlServerCe.3.5".

InvarianName now is VariableName

--
Fabio Maulo

[Frans Bouma]

> LOL!!!...
> The only one not following the rule was Microsoft
> * SQL CE: v3.0: "System.Data.SqlServerCe".
> * SQL CE: v3.5: "System.Data.SqlServerCe.3.5".
>
>
> InvarianName now is VariableName

Actually, it's not really that weird considering that the v3.5
assembly is not backwards compatible with v3.0's so you need both registered
(for apps using 3.0) in machine.config :). Of course overall, the design of
this sucks, but as it's CE, that's no surprise.

FB

[Diego Mijelshon]

I have added http://216.121.112.228/secure/attachment/13279/ReflectionBasedDriver.patch

I was working on that this morning, but my kids wouldn't let me test it :-)
 
    Diego