d-dbapi ALPHA-2 released

[Myron Alexander]

Kirk McDonald wrote:
> I always liked Python's DBAPI. One of the benefits of that API is that 
> it is, if not always simple, at least reasonably /possible/ to swap out 
> one DBMS for another. To that end, you might want the various classes 
> involved in your API to all inherit from various interfaces. If you bind 
> any DBMSes other than SQLite, they should also inherit from those 
> interfaces.
> 

The way Python's DBAPI works is design by convention. I thought about 
making interfaces for the api but decided to keep the convention theme 
since D can handle it via auto & templates.

So far PEP249 has worked for Python and I do multi-db development 
(Oracle9i, SQLServer 2000 and SQLite) using it. I'm also a senior Java 
business/corporate developer working with Oracle9i and SQLServer 2000 so 
I am aware of JDBC and the benefits of interface abstraction.

This is one of those early/late optimization issues (for design) where I 
am not sure I will create the correct design until other databases are 
added. I also want to keep things as simple as possible. So I am not 
saying that interfaces are not the right way to go, just that I have not 
thought of a comprehensive and clean interface design as yet.

> I would also try to find a way to ditch std.boxer. I never liked it. 

I'm not a big fan of the Box solution either but it was the only one 
that suited all the conditions. My primary condition is to mimic the 
simplicity of the PEP249 array mechanism and specifying type up-front 
causes a headache when working with NULL. I chose to represent NULL as 
box(null) (mirroring the Python None mechanism) but you can't put a null 
into an int.

> My  first suggestion, off the top of my head, is something like this:
> 
> struct Result(T...) {
>     T t;
> }
> 
> // ... and in e.g. Cursor.fetchall ...
> Result!(T)[] fetchall(T ...)() {
>     Result!(T)[] r;
>     // ... fill r ...
>     return r;
> }
> 
> // And use:
> auto values = cu.fetchall!(int, char[] double)();
> foreach (row; values) {
>     writefln("%d, %s, %#.2f", row.t[0], row.t[1], row.t[2]);
> }
> 
> The ".t"s are sort of ugly (native tuple return types would sure be 
> nice...), but it does get rid of all of those calls to unboxD.
> 
> Just some food for thought.
> 

Following on from above, I can represent a NULL for char[] as null 
reference, the double NULL can be NAN but how do I show NULL for an int?

I could have a boolean field in Result that indicates NULL. The saving 
in typing, vs helper function, is 3 characters, not much better than the 
helper functions: (row is a Box[])

row.xint (0), row.xstr (1), row.xdbl (2)

or, with developer specified defaults:

row.xint (0), row.xstr (1, "<NULL>"), row.xdbl (2, 0.00)

and to test if a result column is NULL (for example, the int):
isNull (row[0])

The other consideration, how to handle queries where the type is not 
known upfront? One such case is the generic database viewer program. I'm 
still learning D so I do not know all the template features. How would I 
return a templated struct without supplying the types upfront?

I did come across a variant type yesterday 
(http://www.prowiki.org/wiki4d/wiki.cgi?DanielKeep/Variant) that could 
be better than the Box, especially if I specialize it for D-DBAPI but I 
would need a use-case to see how much benefit over the Box solution it 
would bring.

Kirk, thanks for taking the time to provide suggestions. Your comments 
are valid and this is something I am struggling to solve. I'm a firm 
believer that no matter how smart a person is, there is always someone 
else that can see the problem in another light and can come up with a 
better solution. At this time, I cannot see a better solution so all 
suggestion are helpful.

Thanks,

Myron.