On Mon, 25 Apr 2011 01:49:16 +0300, Robert Clipsham <robert@octarineparrot.com> wrote:

> On 24/04/2011 21:40, dsimcha wrote:
>> However, it seems others in the community are interested in a more
>> general SQL DB wrapper that can be used with a variety of backends.
>> Now that no GSoC database project has been accepted, we need to
>> consider other options for getting this done. I understand that there
>> are a lot of independent attempts, but I don't know the status of
>> them or which ones, if any, are targeting eventual inclusion in
>> Phobos.
>
> I have a general SQL db wrapper (only wrapping SQLite currently), which I'd be happy to adapt and submit for phobos, I'm currently working on a complete rewrite though, so this may not be a great idea right now.
>
> Example usage:
> ----
> struct Post
> {
>      int id;
>      DateTime time;
>      string title;
>      string content;
> }
>
> auto getPosts(long lim, long offs=0)
> {
>      with (new SqlQuery)
>      {
>          select("*").from("blog")
>                     .limit(lim)
>                     .offset(offs);
>          return execute!(Post)();
>      }
> }
> foreach(post; getPosts(10))
> {
>      // Operate on posts
> }
> ----
>
> The new interface makes the above even simpler, it will look something like (rather rough, I'm in the early stages of implementing it):
> ----
> struct Post
> {
>      int id;
>      DateTime time;
>      string title;
>      string content;
> }
>
> auto posts = new SqlitePersister!Post(new SqliteDb("my.db"));
> foreach(post; posts[0..10])
> {
>      // Operate on posts
> }
> ----
>
> Of course, both of these provide (or will provide) an interface to allow for SQL statements to be executed directly.
>

Is it possible to see code somewhere? It would be interesting to get rid of
raw SQL, but retain access to it's parts - but I'm not sure how many constructs
you can handle in this way - for example, what about complex queries with joins?
What about subqueries?

On 25/04/2011 00:19, Alex Khmara wrote:
> Is it possible to see code somewhere?

https://github.com/mrmonday/serenity/blob/master/serenity/SqlQuery.d - The code isn't great, it's adapted from D1, and wasn't complete before the port.

> It would be interesting to get rid of
> raw SQL, but retain access to it's parts - but I'm not sure how many
> constructs
> you can handle in this way - for example, what about complex queries
> with joins?
> What about subqueries?

I've implemented this kind of thing before with another developer, and it's perfectly possible to deal with complex joins and subqueries (he implemented that previously). It isn't implemented in the code above however, I only did the basics to begin with, and I'm undecided whether I'm going to keep the former once the new interface is implemented, I haven't thought that far ahead.

-- 
Robert
http://octarineparrot.com/

On Mon, 25 Apr 2011 02:33:44 +0300, Robert Clipsham <robert@octarineparrot.com> wrote:

> On 25/04/2011 00:19, Alex Khmara wrote:
>> Is it possible to see code somewhere?
>
> https://github.com/mrmonday/serenity/blob/master/serenity/SqlQuery.d - The code isn't great, it's adapted from D1, and wasn't complete before the port.
>
>> It would be interesting to get rid of
>> raw SQL, but retain access to it's parts - but I'm not sure how many
>> constructs
>> you can handle in this way - for example, what about complex queries
>> with joins?
>> What about subqueries?
>
> I've implemented this kind of thing before with another developer, and it's perfectly possible to deal with complex joins and subqueries (he implemented that previously). It isn't implemented in the code above however, I only did the basics to begin with, and I'm undecided whether I'm going to keep the former once the new interface is implemented, I haven't thought that far ahead.
>

Second variant is a kind of ORM, and I think it will be too highlevel for
many cases. But I understand that for web framework it's interesting
direction. As for standard D library, I would like to have something more
generic and traditional. Probably, something modeled from Perl's DBI, but
more D-like.

Alex Khmara wrote:
> Second variant is a kind of ORM, and I think it will be too highlevel for
> many cases. But I understand that for web framework it's interesting
> direction. As for standard D library, I would like to have something more
> generic and traditional. Probably, something modeled from Perl's DBI, but
> more D-like.

Hi,

Please take a look at https://github.com/pszturmaj/ddb

I also plan to support SQLite3 and MySQL.

On Mon, 25 Apr 2011 12:16:19 +0300, Piotr Szturmaj <bncrbme@jadamspam.pl> wrote:

> Alex Khmara wrote:
>> Second variant is a kind of ORM, and I think it will be too highlevel for
>> many cases. But I understand that for web framework it's interesting
>> direction. As for standard D library, I would like to have something more
>> generic and traditional. Probably, something modeled from Perl's DBI, but
>> more D-like.
>
> Hi,
>
> Please take a look at https://github.com/pszturmaj/ddb
>
> I also plan to support SQLite3 and MySQL.

Thank you for posting link to your library.

I think we need to collect different ideas about useful and handy API,
and then try to work out D-style DB interface.

And it's very good that there is somebody who work with Postgres - but
for generic interface we will need to use minimal possibilities set,
present on all databases - or make some "layered" structure, like ranges
(by the way, SELECT result is a forward range by it's nature, so it would
be reasonable to make it conforming to Forward Range specification.

I am sure that there are many good design possibilities, and the more code and
ideas we have from different developers, the more chances to work out great
"idiomatic D-style" DB layer. So if anyone else have DB related library in D
(apart from those that listed in Wiki and posted in thet thread) - please share
your vision.

Alex Khmara wrote:
> On Mon, 25 Apr 2011 12:16:19 +0300, Piotr Szturmaj
> <bncrbme@jadamspam.pl> wrote:
>
>> Alex Khmara wrote:
>>> Second variant is a kind of ORM, and I think it will be too highlevel
>>> for
>>> many cases. But I understand that for web framework it's interesting
>>> direction. As for standard D library, I would like to have something
>>> more
>>> generic and traditional. Probably, something modeled from Perl's DBI,
>>> but
>>> more D-like.
>>
>> Hi,
>>
>> Please take a look at https://github.com/pszturmaj/ddb
>>
>> I also plan to support SQLite3 and MySQL.
>
> Thank you for posting link to your library.
>
> I think we need to collect different ideas about useful and handy API,
> and then try to work out D-style DB interface.
>
> And it's very good that there is somebody who work with Postgres - but
> for generic interface we will need to use minimal possibilities set,
> present on all databases - or make some "layered" structure, like ranges
> (by the way, SELECT result is a forward range by it's nature, so it would
> be reasonable to make it conforming to Forward Range specification.

In my Postgres implementation SELECT result is an Input Range, since Forward Range implies position saving and that's possible only with cursors.

I think we could loosely follow .NET approach where each client has separate class with its own capabilities. On top of that there's a wrapper which abstracts all clients to one minimal api set. Actually strictly speaking it's under that rather than on top, I mean polymorphic DBConnection and its descendands. I think such an approach may be good to consider.

Class hierarchy could then look like this:

DBConnection
|
 \
  SqliteConnection
  MySqlConnection
  PostgresConnection

On Mon, 25 Apr 2011 15:26:44 +0300, Piotr Szturmaj <bncrbme@jadamspam.pl> wrote:

> In my Postgres implementation SELECT result is an Input Range, since Forward Range implies position saving and that's possible only with cursors.
>
> I think we could loosely follow .NET approach where each client has separate class with its own capabilities. On top of that there's a wrapper which abstracts all clients to one minimal api set. Actually strictly speaking it's under that rather than on top, I mean polymorphic DBConnection and its descendands. I think such an approach may be good to consider.
>
> Class hierarchy could then look like this:
>
> DBConnection
> |
>   \
>    SqliteConnection
>    MySqlConnection
>    PostgresConnection

Sorry. Of course, I meant Input Range. My error.
As for hierarchy - I don't know, may be DBConnection will become just
interface - it seems that there is little common code between different database
clients hat can be moved into common ancestor.