www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.announce - d-dbapi ALPHA-2 released

reply Myron Alexander <someone somewhere.com> writes:
Hello.

I have released the ALPHA-2 version of D-DBAPI. There is no download 
package for this release but you can checkout from the Google Code 
project using tag "alpha-2".

The D-DBAPI project is a database interface using the PEP239 python 
specification as a base.

The project link is : http://code.google.com/p/d-dbapi/

You can checkout the code using this SVN repository:

http://d-dbapi.googlecode.com/svn/tags/alpha-2

My plan is for BETA-1 to be released once the exceptions are designed 
and implemented. If there are any interface changes, then BETA-2 etc 
will be released. Once all interface changes and unit tests have been 
implemented, I will release RC-1 and so forth before releasing V1.

Regards,

Myron.

P.S., any D developers from South Africa?
Jun 09 2007
parent reply Kirk McDonald <kirklin.mcdonald gmail.com> writes:
Myron Alexander wrote:

 Hello.
 
 I have released the ALPHA-2 version of D-DBAPI. There is no download 
 package for this release but you can checkout from the Google Code 
 project using tag "alpha-2".
 
 The D-DBAPI project is a database interface using the PEP239 python 
 specification as a base.
 
 The project link is : http://code.google.com/p/d-dbapi/
 
 You can checkout the code using this SVN repository:
 
 http://d-dbapi.googlecode.com/svn/tags/alpha-2
 
 My plan is for BETA-1 to be released once the exceptions are designed 
 and implemented. If there are any interface changes, then BETA-2 etc 
 will be released. Once all interface changes and unit tests have been 
 implemented, I will release RC-1 and so forth before releasing V1.
 
 Regards,
 
 Myron.
 
 P.S., any D developers from South Africa?


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.

I would also try to find a way to ditch std.boxer. I never liked it. 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) {

}

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.

-- 
Kirk McDonald
http://kirkmcdonald.blogspot.com
Pyd: Connecting D and Python
http://pyd.dsource.org
Jun 10 2007
parent reply Myron Alexander <someone somewhere.com> writes:
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) {

 }
 
 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.
Jun 10 2007
parent reply Kirk McDonald <kirklin.mcdonald gmail.com> writes:
Myron Alexander wrote:

 Kirk McDonald wrote:


[snip]

 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.
 


Oof. That does throw a wrench into the works.


 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?
 


The tricky part is that, since D is a statically typed language, you 
must /always/ know the type of something when it comes time to use it. 
In other words, if you don't know the types involved, you can't extract 
the data and use it.

I've mused on this subject before, when I was thinking about how you'd 
write an ORM in D:

http://dsource.org/forums/viewtopic.php?t=2283

I remain convinced that something really brilliant would be possible on 
this front, but it would be a lot of work to get it right.

It turns out that my work on Pyd is highly relevant to this problem, 
since the problems are very similar: In both cases, you have some sort 
of collection of data of different types, and you can extract these 
types at runtime. Those runtime-types map fairly well onto D's native 
types. The trick is to basically turn the problem on its head: You 
define something in D, at compile time, and try to match the runtime 
data to it. If it doesn't match, it's an error.

In Pyd, you define a regular D function, and it tries to match function 
arguments from Python to the function's signature. With an ORM, you 
define a D class, and it tries to match values fetched from the database 
to the class's fields.


 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.
 


That Variant is a nifty piece of work, but it has the same fundamental 
problem as boxer: You basically lose the type and then find it again. 
This generally makes me nervous and makes me want to fix something. 
(Hence my thinking about ORMs.)


 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.
 


Your d-dbapi is smarter than DDBI when it comes to database rows. This 
was always DDBI's main problem, and it's good to see progress there.

-- 
Kirk McDonald
http://kirkmcdonald.blogspot.com
Pyd: Connecting D and Python
http://pyd.dsource.org
Jun 10 2007
parent reply Myron Alexander <someone somewhere.com> writes:
Kirk McDonald wrote:

 The tricky part is that, since D is a statically typed language, you 
 must /always/ know the type of something when it comes time to use it. 
 In other words, if you don't know the types involved, you can't extract 
 the data and use it.


For application specific programs, I agree 100% but there are a class of 
utilities where the handling of result types at runtime is required.

Since you make a very good point, I don't see why I can't look into 
changing the interface to use type-safe fetches as well as having the 
option of using Boxes.

I wrote a quick spike to test the case since I am not completely up to 
speed on variadic templates:


 import std.stdio;
 
 struct Row(T ...) {
    T              t;
    bool[T.length] nullFlag;
 }
 
 Row!(T) fetchOne(T ...) () {
    Row!(T) r;
    r.t[0] = 1;
    r.t[1] = "Hello World";
    r.t[2] = 5.0;
    //r.nullFlag = new bool[r.t.length];
    r.nullFlag[0] = false; 
    r.nullFlag[1] = true; 
    r.nullFlag[2] = false; 
    return r;
 }
 
 void main () {
    auto row = fetchOne!(int, char[], double) ();

    writefln("%s, %s, %s", typeid(typeof(row.t[0])), typeid(typeof(row.t[1])),
typeid(typeof(row.t[2])));
    writefln ("%s  --  %s", row.nullFlag.length, typeid(typeof(row.nullFlag)));
    foreach (n; row.nullFlag) {
       writefln ("%s", n);
    }
 }


It works, and it makes sense.

I need to think about how to integrate all the ideas so I can have the 
best of both worlds without it being a complex mess, and conforming to DRY.

Regards,

Myron.
Jun 10 2007
parent reply Myron Alexander <someone somewhere.com> writes:
Myron Alexander wrote:

 
 It works, and it makes sense.
 


Oops, I spoke too soon. I have hit a stumbling block. When working with 
tuples, you cannot use non-constant indexes. This is what I was trying 
to achieve (ambitious no? :))


 import std.stdio;
 import std.traits;
 
 /* Example value object.
  */
 struct ValueStruc {
    char[] name;
    int    age;
    char[] addr1;
    char[] addr2;
    char[] addr3;
    double balance;
 }
 
 /* Typesafe database row object.
  */
 struct Row(T ...) {
    T              t;
    bool[T.length] nullFlag;
 }
 
 /* Simulate a typesafe fetch from the database.
  */
 Row!(T) fetchOne(T ...) () {
    Row!(T) r;
    /+ Won't compile:
    int i = 0;
    r.t[i++] = "Myron";
    r.t[i++] = 10;
    r.t[i++] = "addr1";
    r.t[i++] = "addr2";
    r.t[i++] = "addr3";
    r.t[i++] = 100001.10;
    +/
    r.t[0] = "Myron";
    r.t[1] = 10;
    r.t[2] = "addr1";
    r.t[3] = "addr2";
    r.t[4] = "addr3";
    r.t[5] = 100001.10;
    r.nullFlag[0] = false; 
    r.nullFlag[1] = true; 
    r.nullFlag[2] = false; 
    return r;
 }
 
 /* Fetch a typesafe record from the database and populate the struct.
  */
 T fetchStruc(T) () {
    static if (is (T == struct)) {
       auto r = fetchOne!(FieldTypeTuple!(T))();
       T t;
 
       /+ Won't compile:
       for (int i = 0; i < T.tupleof.length; i++) {
          t.tupleof[i] = r.t[i];
       }
       +/
 
       // My nasty hack to get it to work:
       static if ( 0 < T.tupleof.length) t.tupleof[0] = r.t[0];
       static if ( 1 < T.tupleof.length) t.tupleof[1] = r.t[1];
       static if ( 2 < T.tupleof.length) t.tupleof[2] = r.t[2];
       static if ( 3 < T.tupleof.length) t.tupleof[3] = r.t[3];
       static if ( 4 < T.tupleof.length) t.tupleof[4] = r.t[4];
       static if ( 5 < T.tupleof.length) t.tupleof[5] = r.t[5];
 
       return t;
    } else {
       static assert (0);
    }
 }
 
 void main () {
    auto x = fetchStruc!(ValueStruc) ();
    

x.addr3, x.balance);
 }


I want to get the "Won't compile" bits working. Do you have any suggestions?

Thanks,

Myron.
Jun 11 2007
parent reply Christian Kamm <kamm.incasoftware shift-at-left-and-remove-this.de> writes:
 Oops, I spoke too soon. I have hit a stumbling block. When working with
 tuples, you cannot use non-constant indexes. This is what I was trying
 to achieve (ambitious no? :))


You can use 'static' foreach, however. If you use foreach with tuples, it is
expanded at compile time. 


 T fetchStruc(T) () {
    static if (is (T == struct)) {
       auto r = fetchOne!(FieldTypeTuple!(T))();
       T t;
 
       foreach (i, telem; t.tupleof) {
          // note that using telem doesn't work sometimes,
          // so I'm using the index
          t.tupleof[i] = r.t[i]; 
       }
 
       return t;
    } else {
       static assert (0);
    }
 }


And probably something like


 Row!(T) fetchOne(T ...) () {
    Row!(T) r;

    foreach(i, relem; r.t)
    {
       r.t[i] = getRowElement!(typeof(r.t[i]))(i);
       // do something with null flags
    }
    return r;
 }


Cheers,
Christian
Jun 11 2007
parent reply Myron Alexander <someone somewhere.com> writes:
Christian Kamm wrote:

 You can use 'static' foreach, however. If you use foreach with tuples, it is
 expanded at compile time. 



Thanks Christian. Works like a charm. Here is the final spike. I am 
passing in args to make it more "dynamic" and to factor out the compile 
time function effect. I have not looked at CTFs yet so I don't know if 
my method does actually simulate a database lookup. I guess I will only 
find out once I have implemented the real code.

Thanks again,

Myron.


 import std.stdio;
 import std.traits;
 import std.conv;
 
 /* Example value object.
  */
 struct ValueStruc {
    char[] name;
    int    age;
    char[] addr1;
    char[] addr2;
    char[] addr3;
    double balance;
 }
 
 struct OtherValue {
    int     type;
    char[]  dirname;
    char[]  filename;
 }
 
 struct ValueStrucWNull {
    char[] name;
    int    age;
    char[] addr1;
    char[] addr2;
    char[] addr3;
    double balance;
 
    bool[6] nullFlag;
 }
 
 struct OtherValueWNull {
    int     type;
    char[]  dirname;
    char[]  filename;
    
    bool[3] nullFlag;
 }
 
 /* Typesafe database row object.
  */
 struct Row(T ...) {
    T              t;
    bool[T.length] nullFlag;
 }
 
 char[][] args;
 
 int getInt (int i) {
    return toInt (args[i+1]);
 }
 
 char[] getString (int i) {
    return args[i+1];
 }
 
 double getDouble (int i) {
    return toDouble(args[i+1]);
 }
 
 // This works
 T getRowElement(T) (uint i) {
    static if (is (T == int)) {
       return getInt (i);
    } else static if (is (T == char[])) {
       return getString (i);
    } else static if (is (T == double)) {
       return getDouble (i);
    } else {
       static assert (0);
    }
 }
 
 // So do these when the index is uint. If you use void getElem (int i, .....
 // then it fails as it does not know how to differentiate between "int value"
 // and "double value" signatures.
 void getElem (uint i, ref int value) {
    value = getInt (i);
 }
 
 void getElem (uint i, ref char[] value) {
    value = getString (i);
 }
 
 void getElem (uint i, ref double value) {
    value = getDouble (i);
 }
 
 /* Simulate a typesafe fetch from the database.
  *
  * With thanks to Chris Nicholson-Sauls, Lutger Blijdestijn, and Christian Kam
  * who provided code and information on how to make this possible.
  *
  * Thanks to Kirk McDonald for for the type-safe fetch idea.
  */
 Row!(T) fetchone(T ...) () {
    Row!(T) r;
 
    foreach (i, val; r.t) {
       r.t[i] = getRowElement!(typeof(r.t[i]))(i);
 
       // Works too
       //getElem (i, r.t[i]);
    }
 
    static if (T.length == 6) {
       //~ r.t[0] = "Myron";
       //~ r.t[1] = 10;
       //~ r.t[2] = "addr1";
       //~ r.t[3] = "addr2";
       //~ r.t[4] = "addr3";
       //~ r.t[5] = 100001.10;
       r.nullFlag[0] = true; 
       r.nullFlag[1] = false; 
       r.nullFlag[2] = false; 
       r.nullFlag[3] = true; 
       r.nullFlag[4] = false; 
       r.nullFlag[5] = false; 
    }
 
    static if (T.length == 3) {
       //~ r.t [0] = 34;
       //~ r.t [1] = r"c:\dir1\dir2\dir2\";
       //~ r.t [2] = "filename";
       r.nullFlag[0] = false; 
       r.nullFlag[1] = true; 
       r.nullFlag[2] = false; 
    }
 
    return r;
 }
 
 /* Fetch a typesafe record from the database and populate the struct.
  *
  * With thanks to Chris Nicholson-Sauls, Lutger Blijdestijn, and Christian Kam.
  */
 T fetchstruc(T) () {
    
    static if (is (T == struct)) {
       auto r = fetchone!(FieldTypeTuple!(T))();
       T t;
       foreach (i, v; r.t) {
          // According to Christian, using v may not work every time.
          t.tupleof[i] = r.t[i];
       }
       return t;
 
    } else {
       static assert (0);
    }
 }
 
 /* Fetch a typesafe record from the database, populate the struct and set
  * null field flags.
  */
 T fetchnullstruc(T) () {
    
    static if (is (T == struct)) {
       auto r = fetchone!(FieldTypeTuple!(T)[0..length-1])();
       T t;
       foreach (i, v; r.t) {
          // According to Christian, using v may not work every time.
          t.tupleof[i] = r.t[i];
       }
       foreach (i, nullFlagValue; r.nullFlag) {
          t.nullFlag[i] = nullFlagValue;
       }
       return t;
 
    } else {
       static assert (0);
    }
 }
 
 
 void main (char[][] args) {
 
    .args = args;
 
    auto x = fetchstruc!(ValueStruc) ();

x.addr3, x.balance);
 
    auto y = fetchstruc!(OtherValue) ();
    writefln ("%s, %s, %s", y.type, y.dirname, y.filename);
    
    auto a = fetchnullstruc!(ValueStrucWNull) ();

a.addr3, a.balance, a.nullFlag);
 
    auto b = fetchnullstruc!(OtherValueWNull) ();
    writefln ("%s, %s, %s, %s", b.type, b.dirname, b.filename, b.nullFlag);
 }
Jun 11 2007
parent reply Christian Kamm <kamm.incasoftware shift-at-left-and-remove-this.de> writes:
Myron Alexander wrote:

 code


If there are no other reasons for it, you could avoid the intervening step
with the Row!(...) temporary struct:

T fetchstruc(T) () {   
  static if (is (T == struct)) {
    T t;
        
    // check if last struct field is bool array 
    // of correct size to store nullflags
    static if(is(typeof(t.tupleof[$-1]) == bool[t.tupleof.length - 1]))
    {
      foreach (i, v; t.tupleof[0..$-1]) {
        t.tupleof[i] = getRowElement!(typeof(t.tupleof[i]))(i);
        t.tupleof[$-1][i] = false; // get correct nullflag
      }                         
    }
    else  // ignore nullflag
    {
      foreach (i, v; t.tupleof) {
        t.tupleof[i] = getRowElement!(typeof(t.tupleof[i]))(i);
      }
    }

    return t;
  } else {
    static assert (0);
  }
}
Jun 11 2007
next sibling parent Myron Alexander <someone somewhere.com> writes:
Christian Kamm wrote:

 Myron Alexander wrote:

 code


 
 If there are no other reasons for it, you could avoid the intervening step
 with the Row!(...) temporary struct:
 
 T fetchstruc(T) () {   
   static if (is (T == struct)) {
     T t;
         
     // check if last struct field is bool array 
     // of correct size to store nullflags
     static if(is(typeof(t.tupleof[$-1]) == bool[t.tupleof.length - 1]))
     {
       foreach (i, v; t.tupleof[0..$-1]) {
         t.tupleof[i] = getRowElement!(typeof(t.tupleof[i]))(i);
         t.tupleof[$-1][i] = false; // get correct nullflag
       }                         
     }
     else  // ignore nullflag
     {
       foreach (i, v; t.tupleof) {
         t.tupleof[i] = getRowElement!(typeof(t.tupleof[i]))(i);
       }
     }
 
     return t;
   } else {
     static assert (0);
   }
 }


Thanks Christian.

I prefer to use the fetchone and Row struct as I plan to offer that 
method on the public interface and I don't want to duplicate the code.

The fetchone method is an alternative to the Box[] fetchone method of 
the current library. Thus I will have three ways to extract data: using 
Box[], using Row struct and passing in a value structure.

The first method is for applications that need to handle data types at 
runtime. The second method is for those cases when the developer does 
not want to create a structure for the query; and the last is for the 
developer that just wants to populate a structure from the database.

Any thoughts?

Best Regards,

Myron.
Jun 11 2007
prev sibling parent Myron Alexander <someone somewhere.com> writes:
Christian,

I have merged the two functions together as per your suggestion.

This is the final version:


 /* Fetch a type aware record from the database and populate the struct.
  *
  * With thanks to Chris Nicholson-Sauls, Lutger Blijdestijn, and Christian Kam.
  */
 T fetchstruct(T) () {
 
    static if (is (T == struct)) {
 
       T t;
 
       /* Check if last struct field is a bool array of correct size to store
        * null flags.
        */ 
       static if (is (typeof (t.tupleof[$-1]) == bool[t.tupleof.length-1])) {
          const bool hasNullFlags = true;
       } else {
          const bool hasNullFlags = false;
       }
 
       /* When the last field is a bool array of the correct size to store
        * null flags, exclude from the type tuple.
        */
       static if (hasNullFlags) {
          auto r = fetchone!(FieldTypeTuple!(T)[0..length-1])();
 
       } else {
          auto r = fetchone!(FieldTypeTuple!(T))();
       }
 
       /* Populate the structure from the fetched data.
        */
       foreach (i, v; r.t) {
          // According to Christian, using v may not work every time.
          t.tupleof[i] = r.t[i];
       }
 
       /* If the structure contains nullFlags, populate it.
        */
       static if (hasNullFlags) {
 
          foreach (i, nullFlagValue; r.nullFlag) {
             /* I use the name of the field instead of tupleof to guarantee
              * the name so helper functions can be written.
              */
             t.nullFlag[i] = nullFlagValue;
          }
       }
  
       return t;
 
    } else {
       static assert (0, "Invalid type, must be a struct.");
    }
 }



Thanks,

Myron.
Jun 11 2007