[storm] using odbc as a backend

[James Henstridge]

On Fri, Mar 6, 2009 at 5:06 PM, Vernon Cole <vernondcole at gmail.com> wrote:
> Indeed, this is, IMHO, the biggest sticky problem with ADO and/or odbc
> integration.  Taking care of the database specific stuff is exactly what
> odbc DOES NOT do. The code we are working on now is specific to using odbc
> or ADO to access a Microsoft SQL database.  If some poor unfortunate user
> were to try using the same code to get to (for example) an ACCESS database,
> things would mysteriously start to break.

No argument there.  Sounds like a reason to clearly badge the backend
as MS SQL server support rather than ODBC or ADO support.

>   And what happens if we DO open a postgress or MySQL database using ADO?
> The code which already knows how to handle those database will not be
> called!
>   The storm.database.register_scheme(scheme, factory) design is simply not
> capable of handeling the complexities involved in a user (or programmer)
> friendly way.  The idea that each scheme uses a single specific api
> implementation to get to a single specific database server engine will not
> hold up in the real world. For example, if I access a MS-SQL database using
> adodbapi, I use qmark parameter substitution. To get to the same database
> using the Linux odbc driver, I must switch to the %s variety of parameters.
> Storm would have to know this.
>   Am I supposed to (in my python application program) ask which operating
> system I am running on in order to use a different scheme? I think we can do
> better, but I am not sure how. Perhaps we will need to implement some sort
> of environment layer -- sort of like sys.path() -- in order to smooth this
> whole mess out.

While supporting every adapter under the sun for each supported
database might seem nice, it has a cost.  If there is one Python
adapter that is clearly the best for a database, does it actually make
sense not to depend on it for access to the DB?

In the case of proprietary adapters, or if the primary adapter for the
database isn't portable, it might make sense to support multiple
adapters but I wouldn't say that should be the default.  What concrete
benefit do you get from not using psycopg2 or MySQLdb or pysqlite?

James.