www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - D2 postgresql interface - Phobos2?

reply %fil <filip wuytack.net> writes:
Hi,

I was wondering if there is a postgresql db (D native) interface
available for D2?

Also, are there any plans to have a common DB interface in Phobos2?

Many thanks,

fil
Jan 06 2011
next sibling parent Piotr Szturmaj <bncrbme jadamspam.pl> writes:
 I was wondering if there is a postgresql db (D native) interface
 available for D2?

Hi, I'm currently writing one for D2 using postgresql's low level protocol directly (instead of using libpq). It supports binary formatting, so no parsing or converting to string/escaping is needed and that should give proper performance. When done, I will post source on github.
 Also, are there any plans to have a common DB interface in Phobos2?

I also have that in my mind. I designed API based on some experience with .NET and PHP, and I looked at JDBC APIs. Also I've managed to create base for ORM, example: struct City { Serial!int id; // auto increment string name; mixin PrimaryKey!(id); mixin Unique!(name); } struct Pair { int a; int b; mixin PrimaryKey!(a, b); } enum Axis { x, y, z }; struct User { Serial!int id; // auto increment char[30] user; string password; Nullable!Axis axis; Nullable!(int)[3][3] box; // PG's array elements are nullable Nullable!(int)[] numbers; Nullable!(int)[][2] twoLists; Nullable!int cityId; int a; int b; string tag; mixin PrimaryKey!(id, password); mixin Unique!(axis, box); mixin Unique!(user); mixin Unique!(password, numbers); mixin ForeignKey!(cityId, City.id, OnDelete.SetNull, OnUpdate.Cascade, Match.Simple); mixin ForeignKey!(a, b, Pair.a, Pair.b); mixin Map!(user, "login", password, "pass"); mixin Ignore!(tag); } And there is DBRow struct template: struct DBRow(T) { private T t; alias t this; int insert() { ... } int update() { ... } static T getById(...) { ... } ... } It can be used like this: DBRow!User r; r.name = "user"; r.xxx = ...; r.a = 5; r.insert(); // DBRow will automatically generate methods for relations r.getCity().getUsers(); --- Library automatically generates CREATE TABLE/TYPE strings. DBRow will be in two versions: typed and untyped. Above case is typed version. Untyped fields will be accessible by index or field name string. Regular API is similar to .NET API but it's in D's coding style: PGConnection conn = new PGConnection; conn.open([ "host" : "localhost", "database": "test", "user" : "postgres", "password" : "postgres" ]); auto cmd = new PGCommand(conn, "INSERT INTO tbl (id) VALUES ($1)"); cmd.parameters.add(1, PGType.INT8).value = -1; cmd.prepare(); cmd.bind(); // after bind we have list of field which will be returned // in case of INSERT there are no fields foreach (field; cmd.fields) writeln(field.index, " - ", field.name, ", ", field.oid); cmd.executeNonQuery(); cmd.parameters[1].value = long.max; cmd.bind(); cmd.executeNonQuery(); // there's also nice typed query function auto result = cmd.executeQuery!User(); foreach(row; result) { writeln(row.user); // row is DBRow!User } // of couse there is version for untyped DBRow auto resultUntyped = cmd.executeQuery(); foreach(row; result) { writeln(row[1]); // same as above } --- I'm still working on it, so please be patient :) Of course I will appreciate any suggestions :) regards, Piotr
Jan 06 2011
prev sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
 I was wondering if there is a postgresql db (D native) interface
 available for D2?

Hi, I'm currently writing one for D2 using postgresql's low level protocol directly (instead of using libpq). It supports binary formatting, so no parsing or converting to string/escaping is needed and that should give proper performance. When done, I will post source on github.
 Also, are there any plans to have a common DB interface in Phobos2?

I also have that in my mind. I designed API based on some experience with .NET and PHP, and I looked at JDBC APIs. Also I've managed to create base for ORM, example: struct City { Serial!int id; // auto increment string name; mixin PrimaryKey!(id); mixin Unique!(name); } struct Pair { int a; int b; mixin PrimaryKey!(a, b); } enum Axis { x, y, z }; struct User { Serial!int id; // auto increment char[30] user; string password; Nullable!Axis axis; Nullable!(int)[3][3] box; // PG's array elements are nullable Nullable!(int)[] numbers; Nullable!(int)[][2] twoLists; Nullable!int cityId; int a; int b; string tag; mixin PrimaryKey!(id, password); mixin Unique!(axis, box); mixin Unique!(user); mixin Unique!(password, numbers); mixin ForeignKey!(cityId, City.id, OnDelete.SetNull, OnUpdate.Cascade, Match.Simple); mixin ForeignKey!(a, b, Pair.a, Pair.b); mixin Map!(user, "login", password, "pass"); mixin Ignore!(tag); } And there is DBRow struct template: struct DBRow(T) { private T t; alias t this; int insert() { ... } int update() { ... } static T getById(...) { ... } ... } It can be used like this: DBRow!User r; r.name = "user"; r.xxx = ...; r.a = 5; r.insert(); // DBRow will automatically generate methods for relations r.getCity().getUsers(); --- Library automatically generates CREATE TABLE/TYPE strings. DBRow will be in two versions: typed and untyped. Above case is typed version. Untyped fields will be accessible by index or field name string. Regular API is similar to .NET API but it's in D's coding style: PGConnection conn = new PGConnection; conn.open([ "host" : "localhost", "database": "test", "user" : "postgres", "password" : "postgres" ]); auto cmd = new PGCommand(conn, "INSERT INTO tbl (id) VALUES ($1)"); cmd.parameters.add(1, PGType.INT8).value = -1; cmd.prepare(); cmd.bind(); // after bind we have list of field which will be returned // in case of INSERT there are no fields foreach (field; cmd.fields) writeln(field.index, " - ", field.name, ", ", field.oid); cmd.executeNonQuery(); cmd.parameters[1].value = long.max; cmd.bind(); cmd.executeNonQuery(); // there's also nice typed query function auto result = cmd.executeQuery!User(); foreach(row; result) { writeln(row.user); // row is DBRow!User } // of couse there is version for untyped DBRow auto resultUntyped = cmd.executeQuery(); foreach(row; result) { writeln(row[1]); // same as above } --- I'm still working on it, so please be patient :) Of course I will appreciate any suggestions :) regards, Piotr
Jan 06 2011
next sibling parent reply Mandeep Singh Brar <mandeep brars.co.in> writes:
Me too. Trying to port postgres JDBC driver to D. Already have
somewhat working ODBC bridge. (ported libodbc++).

Regards
Mandeep
Jan 06 2011
parent Mandeep Singh Brar <mandeep brars.co.in> writes:
Hi,

Further to discussion on the above topic around Jan, I have
uploaded a D2/Phobos based port of postgres and sqlite jdbc drivers
to

http://dsource.org/projects/ddbc

This also contains a port of libodbcxx.

A sample program working with the library has also been put up. Its
just an initial port and functionality other than put in the sample
may not be working.

Thanks
Mandeep
May 22 2011
prev sibling parent reply %fil <fil digitalmars.com> writes:
Hi Piotr,

How cool. Very glad you're going native D. I've used Npgsql a lot
and also the more standard data.sqlclient interface from c# so I'm
happy you're modeling after that API. In your "general" API, will
you support the more advanced features like creating functions,
refcursors, preplanning queries, etc?

Also, your base db object looks very usefull. Do you have any
sense when you would have code ready for testing purposes (don't
take this as pressure, just curious)? Or for others to review?
Maybe people like Mandeep or myself could help on the coding
front? As I guess a DB interface will be used a lot.

Andrei (or Phobos team?), would you consider a DB interface in
Phobos or are you already planning something yourselves for Phobos
or feel this does not belong in Phobos and should stay outside?


Many thanks,

fil
Jan 07 2011
parent Piotr Szturmaj <bncrbme jadamspam.pl> writes:
 How cool. Very glad you're going native D. I've used Npgsql a lot
 and also the more standard data.sqlclient interface from c# so I'm
 happy you're modeling after that API. In your "general" API, will
 you support the more advanced features like creating functions,
 refcursors, preplanning queries, etc?

I plan to support most of postgres features. Preplanning or preparing queries is already done using prepare() method of PGCommand just like in Npgsql. Cursors also should be available to users. In case of functions I assume native D functions linked to postgres. These must be compiled to shared library and loaded within server but AFAIK shared library support is not complete in D2 (maybe I'm misinformed?). Also there will be support for other advanced features like asynchronous notifications (using NOTIFY channel, payload; syntax).
 Also, your base db object looks very usefull. Do you have any
 sense when you would have code ready for testing purposes (don't
 take this as pressure, just curious)? Or for others to review?
 Maybe people like Mandeep or myself could help on the coding
 front? As I guess a DB interface will be used a lot.

Non query prepared statements are already working. For general API, I need to finish query result handling and binary formatting of compound types and arrays. Then I will be working on ORM API. I will post source code when result handling is done :) regards, Piotr
Jan 07 2011