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

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

 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