www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - std.database

reply "Erik Smith" <erik cruiserhouse.com> writes:
I'm working on a standards grade interface & implementation for 
database clients in D.  It defines a common interface (the 
implicit kind) and allows for both native and polymorphic 
drivers.  A key feature is a range interface to query results.

Here's the project page with the design highlights and examples 
of usage:

https://github.com/cruisercoder/ddb

The implementation is in the early stages and only a few things 
work, but I'm planning to include sqlite, mysql, Oracle, and ODBC 
reference implementations.

I could use some feedback on the basic approach I'm taking and to 
help ensure that I'm not doing something horribly wrong with my 
basic D skills.

erik
May 27 2015
next sibling parent Rikki Cattermole <alphaglosined gmail.com> writes:
On 28/05/2015 2:04 p.m., Erik Smith wrote:

 I'm working on a standards grade interface & implementation for database
 clients in D.  It defines a common interface (the implicit kind) and
 allows for both native and polymorphic drivers.  A key feature is a
 range interface to query results.

 Here's the project page with the design highlights and examples of usage:

 https://github.com/cruisercoder/ddb

 The implementation is in the early stages and only a few things work,
 but I'm planning to include sqlite, mysql, Oracle, and ODBC reference
 implementations.

 I could use some feedback on the basic approach I'm taking and to help
 ensure that I'm not doing something horribly wrong with my basic D skills.

 erik


Feel free to come on https://gitter.im/DNetDev/Public to chat.
I was thinking about doing something along these lines eventually. Since 
Dvorm is going away.
May 27 2015
prev sibling next sibling parent reply "Robert burner Schadek" <rburners gmail.com> writes:
I believe you're a aiming to low.

If you have a struct you could use traits and ctfe to generate 
perfect sql statements. This would make it possible to a range of 
"struct Customers".

That would be awesome
May 27 2015
parent "Erik Smith" <erik cruiserhouse.com> writes:
On Thursday, 28 May 2015 at 03:40:36 UTC, Robert burner Schadek 
wrote:

 I believe you're a aiming to low.

 If you have a struct you could use traits and ctfe to generate 
 perfect sql statements. This would make it possible to a range 
 of "struct Customers".

 That would be awesome


I agree that it would be awesome.  I see that kind of query 
generation framework being built separately on top of this layer.

erik
May 27 2015
prev sibling next sibling parent reply "Sebastiaan Koppe" <mail skoppe.eu > writes:
On Thursday, 28 May 2015 at 02:04:31 UTC, Erik Smith wrote:

Shouldn't the statement be reusable? I.e. bind variables and run 
multiple times. From the code examples creating a statement and 
binding variables seemed to be coupled.

Since one obvious use case would be running the behind a http 
server - vibe.d - using fibers and using yield would seem nice to 
have.

In any case it should do one thing, and do it well. All the other 
fancy features like orms and auto-struct bindings should be build 
ontop (maybe as separate libs)
May 27 2015
parent reply "Erik Smith" <erik cruiserhouse.com> writes:
 Shouldn't the statement be reusable?


Yes it should.  I added this use case:

auto stmt = con.statement("insert into table values(?,?)");
stmt.execute("a",1);
stmt.execute("b",2);
stmt.execute("c",3);


 Since one obvious use case would be running the behind a http 
 server - vibe.d - using fibers and using yield would seem nice 
 to have.


I think it's agnostic to fibers but I'm not sure.


 In any case it should do one thing, and do it well. All the 
 other fancy features like orms and auto-struct bindings should 
 be build ontop (maybe as separate libs)


Agree.
May 27 2015
parent reply "Robert burner Schadek" <rburners gmail.com> writes:
On Thursday, 28 May 2015 at 04:45:52 UTC, Erik Smith wrote:

 Shouldn't the statement be reusable?


 Yes it should.  I added this use case:

 auto stmt = con.statement("insert into table values(?,?)");
 stmt.execute("a",1);
 stmt.execute("b",2);
 stmt.execute("c",3);


struct Table;

Table a, b, c;

con.insert!Table(a);
...

if you use CTFE to create the statement string there is no reason 
to reuse it.
it will be string literal, that's even better! Think Big. Think D

the other code is Java not D
May 27 2015
next sibling parent reply Rikki Cattermole <alphaglosined gmail.com> writes:
On 28/05/2015 4:57 p.m., Robert burner Schadek wrote:

 On Thursday, 28 May 2015 at 04:45:52 UTC, Erik Smith wrote:

 Shouldn't the statement be reusable?


 Yes it should.  I added this use case:

 auto stmt = con.statement("insert into table values(?,?)");
 stmt.execute("a",1);
 stmt.execute("b",2);
 stmt.execute("c",3);


 struct Table;

 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no reason to
 reuse it.
 it will be string literal, that's even better! Think Big. Think D

 the other code is Java not D


Then you open up table names, property serialization ext. ext.
Please no.
That is an ORM's job. I'm saying this from experience.
May 27 2015
next sibling parent reply "Vadim Lopatin" <coolreader.org gmail.com> writes:
On Thursday, 28 May 2015 at 05:00:30 UTC, Rikki Cattermole wrote:

 On 28/05/2015 4:57 p.m., Robert burner Schadek wrote:

 On Thursday, 28 May 2015 at 04:45:52 UTC, Erik Smith wrote:

 Shouldn't the statement be reusable?


 Yes it should.  I added this use case:

 auto stmt = con.statement("insert into table values(?,?)");
 stmt.execute("a",1);
 stmt.execute("b",2);
 stmt.execute("c",3);


 struct Table;

 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no 
 reason to
 reuse it.
 it will be string literal, that's even better! Think Big. 
 Think D

 the other code is Java not D


 Then you open up table names, property serialization ext. ext.
 Please no.
 That is an ORM's job. I'm saying this from experience.


Similar project: DDBC https://github.com/buggins/ddbc
Inspired by Java JDBC API.
Currently supports MySQL, PostreSQL, SQLite.
May 27 2015
parent "miazo" <miazo no.spam.please> writes:
On Thursday, 28 May 2015 at 05:12:34 UTC, Vadim Lopatin wrote:

 On Thursday, 28 May 2015 at 05:00:30 UTC, Rikki Cattermole 
 wrote:

 On 28/05/2015 4:57 p.m., Robert burner Schadek wrote:

 On Thursday, 28 May 2015 at 04:45:52 UTC, Erik Smith wrote:

 Shouldn't the statement be reusable?


 Yes it should.  I added this use case:

 auto stmt = con.statement("insert into table values(?,?)");
 stmt.execute("a",1);
 stmt.execute("b",2);
 stmt.execute("c",3);


 struct Table;

 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no 
 reason to
 reuse it.
 it will be string literal, that's even better! Think Big. 
 Think D

 the other code is Java not D


 Then you open up table names, property serialization ext. ext.
 Please no.
 That is an ORM's job. I'm saying this from experience.


 Similar project: DDBC https://github.com/buggins/ddbc
 Inspired by Java JDBC API.
 Currently supports MySQL, PostreSQL, SQLite.


Some time ago I tried a similar thing (not to build a library but 
rather to learn D a bit), however I never had time to finish it. 
A brief sample of the interface for my Result object:


1. Result's structure not known in advance

// execute simple query using connection c and assign results to r
auto r = c.execute("SELECT int_column, string_column FROM 
Table;");

// output column names
writeln(r.names[0], "\t", r.names[1]);

// iterate thru result and output values for each row
for (; !r.empty; r.popFront()) {
	writeln(r.front[0], "\t", r.front[1]);
}


2. Result's structure known in advance

// define Sample_Record struct (struct data types must conform to 
column types from query)
struct Sample_Record {
	int i;
	string s;
}

// execute simple query using connection c and assign results to r
auto r = c.execute("SELECT int_column, string_column FROM 
Table;");

// assign results to sample_record struct
auto sample_record = r.getRecord!(Sample_Record);

// output sample_record struct values
writeln(sample_record.i, "\t", sample_record.s);
May 28 2015
prev sibling parent "lobo" <swamplobo gmail.com> writes:
On Thursday, 28 May 2015 at 05:00:30 UTC, Rikki Cattermole wrote:

 On 28/05/2015 4:57 p.m., Robert burner Schadek wrote:

 On Thursday, 28 May 2015 at 04:45:52 UTC, Erik Smith wrote:

 Shouldn't the statement be reusable?


 Yes it should.  I added this use case:

 auto stmt = con.statement("insert into table values(?,?)");
 stmt.execute("a",1);
 stmt.execute("b",2);
 stmt.execute("c",3);


 struct Table;

 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no 
 reason to
 reuse it.
 it will be string literal, that's even better! Think Big. 
 Think D

 the other code is Java not D


 Then you open up table names, property serialization ext. ext.
 Please no.
 That is an ORM's job. I'm saying this from experience.


+1 for decoupling ORMish stuff from the DB driver layer but I 
agree with Robert,  std.database should have an ORM layer on top 
of its DB drivers. It's powered by D and writing an ORM layer in 
D is easy.

The hard part, as is always the case, is agreeing on the API for 
Phobos inclusion.

bye,
lobo
May 27 2015
prev sibling next sibling parent "Erik Smith" <erik cruiserhouse.com> writes:
 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no 
 reason to reuse it.
 it will be string literal, that's even better! Think Big. Think 
 D

 the other code is Java not D


The statement reuse with binding is primarily for performance and 
is important for many use cases.

I get that it looks like Java, but that's because it's wrapping 
around the familiar constructs of a database client API that 
similar across languages.   Also, I think has the potential to be 
much better than JDBC with the deterministic resource management, 
variadic functions, and higher performance.

An independent query generation / ORM layer would be a nice 
complement though.

erik
May 27 2015
prev sibling next sibling parent Jacob Carlborg <doob me.com> writes:
On 2015-05-28 06:57, Robert burner Schadek wrote:


 struct Table;

 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no reason to
 reuse it.
 it will be string literal, that's even better! Think Big. Think D

 the other code is Java not D


It still needs to be possible to execute raw SQL.

-- 
/Jacob Carlborg
May 27 2015
prev sibling parent reply "Sebastiaan Koppe" <mail skoppe.eu> writes:
On Thursday, 28 May 2015 at 04:57:55 UTC, Robert burner Schadek 
wrote:

 On Thursday, 28 May 2015 at 04:45:52 UTC, Erik Smith wrote:

 Shouldn't the statement be reusable?


 Yes it should.  I added this use case:

 auto stmt = con.statement("insert into table values(?,?)");
 stmt.execute("a",1);
 stmt.execute("b",2);
 stmt.execute("c",3);


 struct Table;

 Table a, b, c;

 con.insert!Table(a);
 ...

 if you use CTFE to create the statement string there is no 
 reason to reuse it.
 it will be string literal, that's even better! Think Big. Think 
 D

 the other code is Java not D


That might be, but doing struct-table mappings will open a big 
can of worms, not to mention all the syntax opinions (optional 
and default values, table relationships, etc). Beter build layer 
by layer.

Besides, some people dont believe the orm hype, and would be 
happy to build their own query generation layer on top of a 
low-level sql library, not one tainted with ctfe and template 
mixins.
May 28 2015
parent "Kagamin" <spam here.lot> writes:
On Thursday, 28 May 2015 at 09:18:53 UTC, Sebastiaan Koppe wrote:

 That might be, but doing struct-table mappings will open a big 
 can of worms, not to mention all the syntax opinions (optional 
 and default values, table relationships, etc). Beter build 
 layer by layer.


In fact, Robert did layered abstraction+frontend design for 
std.log :)
May 28 2015
prev sibling next sibling parent reply "Kagamin" <spam here.lot> writes:
On Thursday, 28 May 2015 at 02:04:31 UTC, Erik Smith wrote:

 https://github.com/cruisercoder/ddb


Maybe make the database providers interfaces instead of 
data+dispatch? You're allocating the stuff anyway.
May 28 2015
parent "Erik Smith" <erik cruiserhouse.com> writes:
 Maybe make the database providers interfaces instead of 
 data+dispatch? You're allocating the stuff anyway.


Do you mean inheriting from interfaces like this?

class MysqlStatement : Statement {...}

I need deterministic resource management and I don't think 
classes provide that.  Using structs with RefCounted internals 
seems ideal for this and it appears to be working well.

erik
May 28 2015
prev sibling parent reply "Andrea Fontana" <nospam example.com> writes:
It seems std.database.sql not std.database. You can't build, for 
example, a mongodb driver over this.



On Thursday, 28 May 2015 at 02:04:31 UTC, Erik Smith wrote:

 I'm working on a standards grade interface & implementation for 
 database clients in D.  It defines a common interface (the 
 implicit kind) and allows for both native and polymorphic 
 drivers.  A key feature is a range interface to query results.

 Here's the project page with the design highlights and examples 
 of usage:

 https://github.com/cruisercoder/ddb

 The implementation is in the early stages and only a few things 
 work, but I'm planning to include sqlite, mysql, Oracle, and 
 ODBC reference implementations.

 I could use some feedback on the basic approach I'm taking and 
 to help ensure that I'm not doing something horribly wrong with 
 my basic D skills.

 erik
May 28 2015
parent "Erik Smith" <erik cruiserhouse.com> writes:
 It seems std.database.sql not std.database. You can't build, 
 for example, a mongodb driver over this.


Maybe, but mongodb is a proprietary nosql interface rather than a 
standard one so I'm not sure that it should be in std.

erik
May 28 2015