H1 2015 - db access support in Phobos

[Vadim Lopatin via Digitalmars-d]

On Tuesday, 3 February 2015 at 14:41:02 UTC, Vadim Lopatin wrote:
> On Tuesday, 3 February 2015 at 10:49:07 UTC, Robert burner
>> IMO writing:
>>
>> foreach(it; db.select<User>("...")) {
>> }
>>
>> is epic. you have entered std.(range|algorithm) land.
>
> Implemented in v0.2.17 for select.
>
> test:
>
>     auto ds = new ConnectionPoolDataSourceImpl(new 
> SQLITEDriver(), "ddbctest.sqlite");
>     auto conn = ds.getConnection();
>     scope(exit) conn.close();
>     Statement stmt = conn.createStatement();
>     scope(exit) stmt.close();
>     // test data preparation
>     stmt.executeUpdate("DROP TABLE IF EXISTS user");
>     stmt.executeUpdate("CREATE TABLE user (id INTEGER PRIMARY 
> KEY, name VARCHAR(255) NOT NULL, flags int null)");
>     stmt.executeUpdate(`INSERT INTO user (id, name, flags) 
> VALUES (1, "John", 5), (2, "Andrei", 2), (3, "Walter", 2), (4, 
> "Rikki", 3), (5, "Iain", 0), (6, "Robert", 1)`);
>
>     // our POD struct
>     struct User {
>         long id;
>         string name;
>         int flags;
>     }
>
>     // let's execute select and nump query result
>     foreach(user; stmt.select!User.where("id < 
> 6").orderBy("name desc")) {
>         writeln("id:", user.id, " name:", user.name, " flags:", 
> user.flags);
>     }
>
> Output is:
> ------------------
> id:3 name:Walter flags:2
> id:4 name:Rikki flags:3
> id:1 name:John flags:5
> id:5 name:Iain flags:0
> id:2 name:Andrei flags:2
> ------------------
> Where and orderBy are optional - without them always reads all 
> rows in default order.
>
> Possible improvements: ability to specify field list to read 
> only necessary fields.

Update in v0.2.19: you can specify field list for select

     writeln("reading all user table rows, but fetching only id 
and name (you will see default value 0 in flags field)");
     foreach(ref e; stmt.select!(User, "id", "name")) {
         writeln("id:", e.id, " name:", e.name, " flags:", 
e.flags);
     }

output:

reading all user table rows, but fetching only id and name (you 
will see default value 0 in flags field)
id:1 name:John flags:0
id:2 name:Andrei flags:0
id:3 name:Walter flags:0
id:4 name:Rikki flags:0
id:5 name:Iain flags:0
id:6 name:Robert flags:0