H1 2015 - db access support in Phobos

[Daniel Kozák via Digitalmars-d]

V Tue, 03 Feb 2015 15:20:40 +0000
Vadim Lopatin via Digitalmars-d <digitalmars-d at puremagic.com> napsáno:

> 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
> 
This is dangerous zone, you shoud not do this ;-). In my work I have
writen our ORM-like db layer which has this funcionality (mainly
performance reasons) but if I can go back in time. I will be against
such a misfeature.