Same guys.  It's great to see this moving from theory to application so quickly.

On Oct 10, 2011, at 7:30 AM, Sean Kelly wrote:

> Surprising. I read a research paper about a proposed language just a few months ago. I wonder if this is by the same guys.
> 
> Sent from my iPhone
> 
> On Oct 10, 2011, at 12:05 AM, "Roald Ribe" <rr@pogostick.net> wrote:
> 
>> On Sun, 09 Oct 2011 20:31:35 -0300, Sean Kelly <sean@invisibleduck.org> wrote:
>> 
>>> On Oct 9, 2011, at 3:56 PM, Andrei Alexandrescu wrote:
>>> 
>>>> On 10/9/11 5:31 PM, Walter Bright wrote:
>>>>> On 10/9/2011 5:28 AM, Piotr Szturmaj wrote:
>>>>>> 1. I think that we should not design this API using the least common
>>>>>> denominator
>>>>>> approach. This is to not limit some databases. For example PostgreSQL
>>>>>> has many
>>>>>> great features not available in MySQL. That's why I started with
>>>>>> postgres in my
>>>>>> ddb project. I think DB API should be designed to support the most
>>>>>> featureful
>>>>>> databases and those that have less features may be easily adapted to
>>>>>> that API.
>>>>> 
>>>>> 
>>>>> Haven't common denominator designs been more or less failures in at least one category - gui libraries?
>>>> 
>>>> A common database interface is not a common denominator API; more like the opposite. This is not difficult because most differences across database systems lie in their SQL, which is strings from D's perspective.
>>> 
>>> Assuming that by "database" you mean SQL.  Pretty fair assumption, though NoSQL databases (which cover a broad range of designs since there's no standard language yet for key-value DBs, etc) are rapidly gaining popularity.  I almost wonder if the base type should be named SqlDatabase instead of Database.
>> 
>> There is a standard language defined for NoSQL, namely UnQL: http://wwww.unqlspec.org/display/UnQL/Home
>> 
>> Roald

bls wrote:
> Am 10.10.2011 16:07, schrieb Steve Teale:
>
>> interface SQLDBConnection
>
> How do you support different database connection requirements. f.i.
> a non default port number
> What about special parameters only available on db system xxx ?

Since D support associative arrays, I vote to use them for passing connection properties instead of connection string. See PGConnection.open in pszturmaj.github.com/ddb/postgres.html.

Steve Teale wrote:
> I've just been looking at the documentation for the PostgreSQL C api. Wow!
>
> It is so different from MySQL, and so clean. No out parameters from
> queries. That one is not going to be a problem.
>
> Steve

PostgreSQL's C lib is not needed if we handle postgres protocol directly (already done).

On 10/9/2011 2:22 PM, Andrei Alexandrescu wrote:
> On 10/9/11 11:54 AM, Adam Ruppe wrote:
>> The way I'd do it is:
>>
>> interface Database {
>> // support shared functions here, and other stuff useful enough to
>> // warrant emulation
>> }
>>
>> class Postgres : Database {
>> // implement the interface, of course, but also all other postgres
>> // specific stuff
>> }
>>
>>
>> When you go to use it, if you're happy with the basics, declare
>> Databases.
>>
>> If you need something special, use Postgres objects.
>
> Makes sense. JDBC does that, too.
>
> Andrei

Maybe "Database" should be an abstract class rather than an interface? That's how ADO.net does it:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

This is architecturally a little "cleaner", because whatever Postgres database class we have is a "database" class at heart, not a class that implements a database interface.

Also, perhaps some actual code that would exist in the abstract class would be a call to Close() in the destructor. That way you can declare a database connection on the stack, open it, do some stuff, allow it to leave scope, and clean up that resource without a timeout on the server end.

On 10/8/2011 2:43 AM, Steve Teale wrote:
> I use this title at Andrei's suggestion, and repeat his idea that it be used
> as a prefix for discussions as we navigate toward a design. Unless there is
> resistance to the idea, I will on the job of implementing whatever we decide
> is appropriate. I am retired, and have the time to do it.
>
> It seems that every man, and possibly his dog, has a private implementation
> for at least a favorite DB, so we should have plenty of material to build on.
>
> At this point I would like to get responses from those who feel they are
> likely to contribute to the design through to completion.
>
> I'd also like to get a feel for the magnitude of the task, so I'd like to ask
> what database systems you think should be supported.
>
> I have started a github account, and will put my mysqld stuff there shortly,
> then you can kick me out if you don't like what you see.
>
> Steve

I've written up a prototype for a "LINQ" style database querying mechanism in D (read about "LINQ to SQL" if you've never heard of it). Legally speaking, it has very little to do with LINQ, but the concept is similar.

Basically, it allows you to write code like this:

auto x = new SqliteConnection("mydata.db");

foreach(y; x.MyTable.where("someField > 10"))
{
  // y is a wrapper around Variant[string] with some opDispatch magic
  writeln(to!string(y.MyField));
  writeln(to!int(y.SomeOtherField));
}

Of course, "MyTable" is handled via opDispatch. The SqliteConnection doesn't care what tables are available in "mydata.db". You can also do much more. Such as:

x.MyTable.startAt(20).limit(10).where("blah").select("somefield",
"sometingElse");

In addition, you should be able to do something like this (don't think I've implemented this yet):

x.MyTable.select!MyStruct();

Doing that would return a range of MyStruct structs, rather than the wrapper around Variant[string] like above. This would allow you to do:

auto x = new SqliteConnection("mydata.db");

foreach(y; x.MyTable.where("someField > 10").select!MyStruct())
{
  // y is a wrapper around Variant[string] with some opDispatch magic
  writeln(y.MyField); // No more needing the to! template
  writeln(y.SomeOtherField);
}

Of course, this would allow you to find typos in field names at compile time (provided your struct is kept in sync with the database), and means you don't have to go through the Variant[string] for all your database accesses.

To implement this, a database "driver" would have to have a shared opDispatch implementation (perhaps done with a mixin or maybe with an abstract class), and it would have to be able to translate the "query" into a SQL query that works with their underlying database system.

I have a working prototype somewhere that works with Sqlite, and it seems to work very nicely. Clearly a system like this shows off what D can do out of the box (opDispatch), and makes writing scripts very easy.

Let me know if this is something you think should be part of std.database (or whatever we end up calling it).

On Mon, 10 Oct 2011 23:08:30 -0400, Johann MacDonagh <johann.macdonagh.no@spam.gmail.com> wrote:
> On 10/8/2011 2:43 AM, Steve Teale wrote:
>> I use this title at Andrei's suggestion, and repeat his idea that it be used
>> as a prefix for discussions as we navigate toward a design. Unless there is
>> resistance to the idea, I will on the job of implementing whatever we decide
>> is appropriate. I am retired, and have the time to do it.
>>
>> It seems that every man, and possibly his dog, has a private implementation
>> for at least a favorite DB, so we should have plenty of material to build on.
>>
>> At this point I would like to get responses from those who feel they are
>> likely to contribute to the design through to completion.
>>
>> I'd also like to get a feel for the magnitude of the task, so I'd like to ask
>> what database systems you think should be supported.
>>
>> I have started a github account, and will put my mysqld stuff there shortly,
>> then you can kick me out if you don't like what you see.
>>
>> Steve
>
> I've written up a prototype for a "LINQ" style database querying
> mechanism in D (read about "LINQ to SQL" if you've never heard of it).
> Legally speaking, it has very little to do with LINQ, but the concept is
> similar.
>
> Basically, it allows you to write code like this:
>
> auto x = new SqliteConnection("mydata.db");
>
> foreach(y; x.MyTable.where("someField > 10"))
> {
>    // y is a wrapper around Variant[string] with some opDispatch magic
>    writeln(to!string(y.MyField));
>    writeln(to!int(y.SomeOtherField));
> }

For what it's worth, my Variant proposal has this kind of opDispatch magic built-in.

On Mon, 10 Oct 2011 11:09:34 -0400, Andrei Alexandrescu <SeeWebsiteForEmail@erdani.org> wrote:

> On 10/10/11 7:01 AM, Steve Teale wrote:

[snip]

> That's a bug in std.variant. Large structs should be supported
> automatically by using an indirection and dynamic allocation.

For what it's worth, my improved Variant proposal, does do that. (i.e. it fixes the bug)

On Mon, 10 Oct 2011 23:08:30 -0400, Johann MacDonagh wrote:

> I've written up a prototype for a "LINQ" style database querying mechanism in D (read about "LINQ to SQL" if you've never heard of it). Legally speaking, it has very little to do with LINQ, but the concept is similar.
> 
> Basically, it allows you to write code like this:
> 
> auto x = new SqliteConnection("mydata.db");
> 
> foreach(y; x.MyTable.where("someField > 10")) {
>    // y is a wrapper around Variant[string] with some opDispatch magic
>    writeln(to!string(y.MyField));
>    writeln(to!int(y.SomeOtherField));
> }
> 
> Of course, "MyTable" is handled via opDispatch. The SqliteConnection doesn't care what tables are available in "mydata.db". You can also do much more. Such as:
> 
> x.MyTable.startAt(20).limit(10).where("blah").select("somefield",
> "sometingElse");
> 
> In addition, you should be able to do something like this (don't think
> I've implemented this yet):
> 
> x.MyTable.select!MyStruct();
> 
> Doing that would return a range of MyStruct structs, rather than the wrapper around Variant[string] like above. This would allow you to do:
> 
> auto x = new SqliteConnection("mydata.db");
> 
> foreach(y; x.MyTable.where("someField > 10").select!MyStruct()) {
>    // y is a wrapper around Variant[string] with some opDispatch magic
>    writeln(y.MyField); // No more needing the to! template
>    writeln(y.SomeOtherField);
> }
> 
> Of course, this would allow you to find typos in field names at compile time (provided your struct is kept in sync with the database), and means you don't have to go through the Variant[string] for all your database accesses.
> 
> To implement this, a database "driver" would have to have a shared opDispatch implementation (perhaps done with a mixin or maybe with an abstract class), and it would have to be able to translate the "query" into a SQL query that works with their underlying database system.
> 
> I have a working prototype somewhere that works with Sqlite, and it seems to work very nicely. Clearly a system like this shows off what D can do out of the box (opDispatch), and makes writing scripts very easy.
> 
> Let me know if this is something you think should be part of std.database (or whatever we end up calling it).

I was lying in bed last night and realized that Variant[string] was attractive for various purposes. It's kind of like a Javascript Object.

That and the possibilities with strictly name structs creates some interesting possibilities.

Another dumb idea.

I was thinking about Johathan's idea about std.sql. What if we did something like the following - let the compiler do the work.

enum Disposition { ... }


Disposition Insert(string database, S)(out ulong ra, S s, string table)
   if (is(S == struct))
{ ... }
Disposition Insert(string database)
   (out ulong ra, Variant[string] vaa, string table) { ... }
Disposition PrepareInsert(string database, S)(S s) if (is(S == struct))
{ ... }
Disposition PrepareInsert(string database)(Variant[string] vaa)
{ ... }
Disposition ExecInsert(string database, S)
   (out ulong ra, S s) if (is(S == struct))
Disposition ExecInsert(string database)
   (out ulong ra, Variant[string] vaa)
{ ... }

Disposition Update(string database, string whereClause, S, T...)
  (out ulong ra, S s, string table, T args) if (is(S == struct)) { ... }
Disposition Update(string database, string whereClause, T...)
  (out ulong ra, Variant[string] vaa, string table, T args)
{ ... }
...

Disposition Delete(string database, string whereClause, S)
                  (out ulong ra, S s) if (is(S == struct)) { ... }
Disposition Delete(string database, string whereClause)
                  (Variant[string] vaa) { ... }
...

Disposition SelectSequence(string database, SIN, SOUT)
   (SIN sin, SOUT sout, string table)
   if (is(SIN == struct) && is(SOUT == struct)) { ... }
Disposition SelectSequence(string database)
   (Variant[string] vaain, Variant[string] vaaout, string table)
{ ... }
Disposition SelectResultSet(string database, SIN, SOUT)
   (out ulong rc, SIN sin, SOUT sout, string table)
   if (is(SIN == struct) && is(SOUT == struct))
{ ... }
Disposition SelectResultSet(string database)
   (out ulong rc, Variant[string] vaain, Variant[string] vaaout,
               string table)
{ ... }
...
...

This would sidestep the need for a lowest common denominator approach.

The compiler would generate code for a specified database, and could generate SQL with appropriate parameter markers and escaping. and the code to execute it.

If some operation wasn't supported it could do a static assert.

This would be on top of a set of modules that provided nitty-gritty operations for each database - database oriented modules as opposed to SQL oriented. If the compile time SQL option didn't work for your app, you'd fall back for specifics on these.

Could it be done?

Steve

Johann MacDonagh wrote:
> Maybe "Database" should be an abstract class rather than an interface?
> That's how ADO.net does it:
>
> http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

Why "Database" instead of PGConnection, MySqlConnection, SQLiteConnection, etc. ? And of course base SqlConnection.