D2 postgresql interface - Phobos2?

[Piotr Szturmaj]

> 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