H1 2015 - db access support in Phobos

[Vadim Lopatin via Digitalmars-d]

On Tuesday, 3 February 2015 at 15:20:41 UTC, Vadim Lopatin wrote:
> 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

Small addition for D-style selects:

     // now it's possible to use vars as destination
     long id;
     string name;
     foreach(e; stmt.select!()("SELECT id, name FROM user", id, 
name)) {
         writeln("id:", id, " name:", name);
     }

Result:
id:1 name:John
id:2 name:Andrei
id:3 name:Walter
id:4 name:Rikki
id:5 name:Iain
id:6 name:Robert