[std.database]

[Jacob Carlborg]

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