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

> 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

> 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

Me too. Trying to port postgres JDBC driver to D. Already have somewhat working ODBC bridge. (ported libodbc++).

Regards
Mandeep

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

> 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

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