Re: MSSQL PyADO2 driver

On Fri, May 27, 2005 at 05:09:56PM +0100, Tim Golden wrote:
> I've knocked up a first-pass MSSQL driver for PyDO2 using
> the adodbapi module. It's a bit rough-and-ready, but it
> does manage to guess columns and to select. 

Very exciting!

> It's given
> rise to a couple of questions:
> 
> 1) Unicode: the PyDO2 code does a certain mixture of
>    checking against str & basestring:
> 
> base.py:            if isinstance(f, str):
> base.py:            elif isinstance(f, str):
> base.py:            if not isinstance(qualifier, basestring):
> base.py:            if isinstance(unique, (unicode,str)):
> base.py:        if args and isinstance(args[0], str):
> dbi.py:            if isinstance(o, str):
> dbi.py:        elif not isinstance(order, str):
> dbi.py:    if isinstance(driver, str):
> joins.py:        if isinstance(on, basestring):
> joins.py:            if isinstance(on[0], basestring):
> operators.py:        if not isinstance(name, basestring):
> 
>    All the table names and data come back from adodbapi
>    as Unicode objects
>    and I'm not sure whether to change all the checks to
>    consider unicode (ie to use basestring) or whether to
>    determine the encoding -- however possible that is --
>    and to decode from that, or what.

Good catch.  I'm going to switch all these to test against basestring.
It seems best to make as few assumptions as possible about what will
come back from drivers.  

> 2) Sequences: MSSQL implements an IDENTITY
>    column type. But... this needn't be a primary key
>    (although, for obvious reasons they usually are).
>    It needn't even be unique, since you can
>    temporarily override the identity and insert a
>    value directly. So, what does this mean in terms
>    of PyDO2? Should we only consider IDENTITY to be
>    sequence if they are also unique/pk? Or not?

I'd be inclined to require that it be unique.  If you don't declare it
as a primary key, it is just another column which happens to have a
default value.  "Sequence" in PyDO2 really means
UniqueNotNullableSequenceThatGetUsedForPKs; any other kind of sequence
is irrelevant.  (Similarly, "Unique" means "UniqueAndNotNullable".)

> 3) Getting unique indexes is ridiculously hard, and
>    I think I've got a working version, altho' I'm
>    having to work around a quirk in the adodbapi code,
>    which might or might not be considered a bug.
> 
>    In essense, in addition to picking up unique
>    constraints (which is easy, but doesn't include
>    simple unique indexes!) I'm checking sysindexes to see if
>    there are any indexes at all. If there are, I'm
>    calling sp_helpindex and parsing the fields. The
>    alternative seems to be to mess around with sysindexes
>    and spt_values. Have I missed something?

I have no clue, I'm sorry to say, and have no access to MSSQL at the
moment to test this.  But you are testing it, and that's much better!

> I'm quite willing to post a Subversion patch for anyone
> who wants to try this out, but the whole thing's by no means
> tested yet. I'm not sure what to do with conversions, for example.

As for conversions, I have formalized an standard for what to do yet.
My general strategy is to leave things type-related to the DBAPI
driver as much as possible.  But some drivers, in some versions,
seemed not grown up enough, and it seemed necessary to help them out.
If adodbapi is behaving reasonably with date types, for instance (so
you can pass an mx.DateTime and/or datetime.datetime in as a bind
variable), then it will be behaving the same as the other drivers.

Please do send your module/patch, and I'll check it in.

Thanks,

j

-- 
Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFCl1G2uqamFyFXXLIRAmoMAKC5QGpjujkyI5FsMmduXvHieps8xgCeLsWn
pq3UQOBlMxEBrzeIayUtvSU=
=RJbo
-----END PGP SIGNATURE-----