On 2011-10-11 05:08, Johann MacDonagh 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));
> }
>
> 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 think that the use of opDispatch and selecting with a struct should be available at the higher level interfaces and not in the low level interface.

-- 
/Jacob Carlborg

On 10/10/11 11:02 PM, Robert Jacques wrote:
> 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)

Is it ready for review?

Andrei

On 10/10/11 9:07 AM, Steve Teale wrote:
> Here's a sketch of an interface. This is based on my experiments with
> MySQL, and as such it is probably mid-level, and not a top level covers-
> all interface.
>
> Hopefully it will create a number of discussion points.
>
> // Can interfaces include template functions???
> interface SQLDBConnection
> {
>     @property Handle handle();
>     Handle connect(string host, string user, string password,
>                    string database = null);
>     T getProperty(T)(string name);
>     T getProperty(T)(int id);
>     void setProperty(T)(T property, string name);
>     void setProperty(T)(T property, int id);
>     Handle disconnect();
> }
[snip]

This makes sense from the perspective of a database implementor who needs to provide the appropriate interfaces, but I think a better angle would be to focus on use cases and work the API backwards from there.

Here's what I think should be a complete program:

import std.sql, std.stdio;

void main() {
    auto db = connect("
        engine = mysql;
        user = john;
        password = doe;
        port = 6900;
    ");
    auto rows = db.execute("SELECT score, a.user FROM users a
      JOIN scores b on a.user = b.user");
    foreach (row; rows) {
        writeln("%10s\t%s", row[0], row[1]);
        // or
        // writeln("%10s\t%s", row["score"], row["user"]);
    }
}

This should cleanly close everything upon exiting from main, provide proper error reporting if anything bad happens (and a lot could go wrong: malformed conn string, missing driver, wrong username/password, connection errors, malformed query, wrong column name...).

Using the interfaces you propose directly would allow one to implement the same program, but with significant extra chaff. That's why suggest we focus on user-level APIs first because in many ways those are the most important thing. Then we will distill the proper interfaces.

So the question is - what should a typical database task look like? That includes prepared statements, binding to tuples and structs, and a lot more.


Andrei

On 2011-10-11 19:40, Andrei Alexandrescu wrote:
> This makes sense from the perspective of a database implementor who
> needs to provide the appropriate interfaces, but I think a better angle
> would be to focus on use cases and work the API backwards from there.
>
> Here's what I think should be a complete program:
>
> import std.sql, std.stdio;
>
> void main() {
> auto db = connect("
> engine = mysql;
> user = john;
> password = doe;
> port = 6900;
> ");
> auto rows = db.execute("SELECT score, a.user FROM users a
> JOIN scores b on a.user = b.user");
> foreach (row; rows) {
> writeln("%10s\t%s", row[0], row[1]);
> // or
> // writeln("%10s\t%s", row["score"], row["user"]);
> }
> }
>
> This should cleanly close everything upon exiting from main, provide
> proper error reporting if anything bad happens (and a lot could go
> wrong: malformed conn string, missing driver, wrong username/password,
> connection errors, malformed query, wrong column name...).
>
> Using the interfaces you propose directly would allow one to implement
> the same program, but with significant extra chaff. That's why suggest
> we focus on user-level APIs first because in many ways those are the
> most important thing. Then we will distill the proper interfaces.
>
> So the question is - what should a typical database task look like? That
> includes prepared statements, binding to tuples and structs, and a lot
> more.
>
>
> Andrei

If we're talking use cases and high level interfaces I would go with something like:

class Post : Model
{
    mixin hasMany!("comments");
}

class Comment : Model
{
    mixin belongsTo!("post");
}

The above mixins are very ugly. This would be perfect for user definable annotations/attributes

void main ()
{
    Connection connection = new MySqlConnection("localhost", "john", "doe", "6900");

    // or using field syntax
    connection.host = "localhost";
    connection.username = "john";
    connection.password = "doe";
    connection.port = "6900";

    auto posts = Post.all; // lazily get all rows from the table "posts"
    posts = posts.where("title = ?", "foobar"); // add where condition

     // or if this is possible, using new-style lambda syntax
    posts = posts.where(post => post.title == "foobar");

    foreach (post ; posts) // execute SQL here
    {
        // get the title and content via opDispatch
        writeln("title: %s\n\n%s", post.title, post.content);
        writeln("Comments:\n");

        foreach (comment ; post.comments) // lazily loaded comments
            writeln(comments.content);
    }

    auto post = new Post;
    post.title = "foobar"; // this is not currently possible with opDispatch
    post.content = "content of post";

    // create a new row in the "posts" table
    post.save;
}

I haven't figured out yet how to connect a connection to the models. Perhaps assigning a connection object to the base class "Model":

// using field syntax
Model.connection.host = "localhost";
Model.connection.username = "john";
Model.connection.password = "doe";
Model.connection.port = "6900";

// or by assigning a new connection object
Model.connection = new MySqlConnection("localhost", "john", "doe", "6900");

Anyway, this is what I think the highest level of the interfaces could look like.

I recommend that everyone take a good look at ActiveRecord in Ruby on Rails:

http://guides.rubyonrails.org/active_record_querying.html
http://guides.rubyonrails.org/association_basics.html
http://guides.rubyonrails.org/active_record_validations_callbacks.html

-- 
/Jacob Carlborg

On 10/11/2011 1:57 AM, Piotr Szturmaj wrote:
> 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.

No, I agree. The base type should be DbConnection (I dunno, SqlConnection is a little too specific, I like DbConnection better), extended by PGConnection, MySqlConnection, SqliteConnection, etc...

On 10/11/2011 4:07 AM, Jacob Carlborg wrote:
>
> I think that the use of opDispatch and selecting with a struct should be
> available at the higher level interfaces and not in the low level
> interface.
>

What do you mean by this? Do you mean instead of having the opDispatch in the Connection class, we'd have it in a separate struct / template / other magic? This would allow us to utilize this method for other sources of data, such as XML.

On the other hand though, this doesn't add a whole lot. Let's say we had this code:

auto rows = db.execute("SELECT score, a.user FROM users a JOIN scores b on a.user = b.user");
foreach (row; rows) {
  writeln(to!int(row["score"]));
}

We could also add in a generic range operation that converts a range of Variant[string] (a row) to a struct specified by the user. The code would become:


auto rows = extractStructs!(MyStruct, db.execute("SELECT score, a.user FROM users a JOIN scores b on a.user = b.user"));
foreach (row; rows) {
  writeln(row.score);
}

I suppose I'm just remembering how much plumbing code had to be done when accessing databases in C#. It sounds like doing this from D will be much easier, even without my silly "LINQ" concept.

On 10/11/11 3:05 PM, Jacob Carlborg wrote:
> If we're talking use cases and high level interfaces I would go with
> something like:
[snip]
> I recommend that everyone take a good look at ActiveRecord in Ruby on
> Rails:
>
> http://guides.rubyonrails.org/active_record_querying.html
> http://guides.rubyonrails.org/association_basics.html
> http://guides.rubyonrails.org/active_record_validations_callbacks.html

I confess the example you gave looks very foreign to me. From consulting http://guides.rubyonrails.org/active_record_querying.html, I see Ruby's active records esentially recode relational algebra in Ruby (as for the constructs the equivalent SQL is shown).

For a variety of reasons, this would be tenuous in D. One simple reason is that e.g. lambdas don't offer access to textual representation, which would be necessary to translate lambda-based conditions into SQL text.

I might be narrow-minded, but I thought we're still looking at writing and executing good old SQL code.


Andrei

On 10/11/11 4:00 PM, Johann MacDonagh wrote:
> On 10/11/2011 1:57 AM, Piotr Szturmaj wrote:
>> 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.
>
> No, I agree. The base type should be DbConnection (I dunno,
> SqlConnection is a little too specific, I like DbConnection better),
> extended by PGConnection, MySqlConnection, SqliteConnection, etc...

The database engine should be codified in the connection string, not in the type name.

Andrei

Andrei Alexandrescu wrote:
> The database engine should be codified in the connection string, not in the type name.

Why?

If it's in the type, you can trivially specialize for different engines and get static checking for functions not supported across them all, or runtime checking if you prefer.

You can also change the constructors to make it clear what is being created.

On 10/11/2011 5:46 PM, Adam Ruppe wrote:
> Andrei Alexandrescu wrote:
>> The database engine should be codified in the connection string,
>> not in the type name.
>
> Why?
>
> If it's in the type, you can trivially specialize for different
> engines and get static checking for functions not supported
> across them all, or runtime checking if you prefer.
>
> You can also change the constructors to make it clear what
> is being created.

Agreed. In addition, if DbConnection handled all connections based on the connection string (of course, we're assuming connection strings are all unique here), then DbConnection would have to be modified to support a new driver.

DbConnection should have the lowest common denominator implementation / abstract routines, and the specialized Connection classes would work from that. That's how ADO.net works at least.