Can your programming language do this?

[Adam Ruppe]

Piotr Szturmaj wrote:
> I already have working postgresql row querying. There are typed and
> untyped rows. Untyped row is DBRow!(Variant[]), typed rows are
> encapsulated using struct or tuple, for example:

Very nice! I did something similar in two cases: one is the
byStruct in mysql.d (currently commented out - it was rather buggy
anyway) and the other is the next(T..) function in sqlite.


byStruct translated a textual mysql row into the given struct, by
translating the names:

foreach(member; __traits(allMembers, S)) {
        S.member = to!(typeof(S.member))(db_row[member]);
}
 return S;

(pseudocode just to show the idea).


My implementation was always a little buggy though, so I didn't
use it much. I still like the idea though!


The other thing is to fill in a bunch of local variables. This
was my first attempt at database in D, written before we had
immutable and half of Phobos. You can see some of it in sqlite.d:

auto db_statement = db.prepare("SELECT id, name FROM users");

string name;
int id;

db_statement.execute();

while(db_statement.next(id, name)) {
      // the variables id and name are now filled in with the row
}


It would fill them in positionally, and coerce the type from the
db to match the variables passed in. I don't remember why I abandoned
that. I think it was just a long gap between writing it and the next
time I needed a database, which was mysql, so the sqlite module didn't
get reused.

It's a little tedious to use anyway though. I prefer having all
the variables in the scope of the loop, rather than outside like
it is here.




But anyway, returning a Tuple!() is something I've never tried,
I like the idea.


> I've also written simple ORM mapping - creating and managing tables
> based on struct layout. I see your solution is in opposite -
> creating structs/classes based on SQL CREATE scripts.

Yeah, the main reason there is all my attempts to go struct > sql
ended up being pretty sucky.

I'd start with a basic struct. Getting it to a simple create table
command is pretty straightforward (hey, remember that post a month
or two ago where someone did that in Go and was like "yay Go rocks!"
That thread is actually what inspired the subject to this thread.)

But, a real database has a lot of constraints on top of that -
field lengths, foreign keys, checks, and so on.

I tried two approaches: one was magic. Add stuff based on the names
and types, so assume "int id" is a primary key, for example.

Didn't work in practice. What about a table with a primary key
that spans two columns?

So, then I tried adding a bunch of attributes and templates, but
that felt like a buggy and incomplete SQL forced into D... didn't
feel like natural SQL nor D.



What approach did you take? I wonder if I didn't get anywhere because
I'm just so set in my old ways!

> Maybe we could join our efforts and create kind of hybrid
> DB library? :)

Indeed. Is your code on the internet somewhere?