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

 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("%d, %s, %#.2f", row.t[0], row.t[1], row.t[2]);
    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) ();
    
    writefln ("%s, %s, %s, %s, %s, %#.2f", x.name, x.age, x.addr1, x.addr2,
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) ();
    writefln ("%s, %s, %s, %s, %s, %#.2f", x.name, x.age, x.addr1, x.addr2,
x.addr3, x.balance);
 
    auto y = fetchstruc!(OtherValue) ();
    writefln ("%s, %s, %s", y.type, y.dirname, y.filename);
    
    auto a = fetchnullstruc!(ValueStrucWNull) ();
    writefln ("%s, %s, %s, %s, %s, %#.2f, %s", a.name, a.age, a.addr1, a.addr2,
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