digitalmars.D - Can your programming language do this?
- Adam D. Ruppe (29/29) Jan 23 2011 alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") User;
- Simen kjaeraas (7/25) Jan 23 2011 This is awesome. I've known that D could do it, and I've occasionally
- Adam D. Ruppe (4/7) Jan 23 2011 Yeah, that's the way I feel about it mostly. What finally pushed
- Nick Sabalausky (4/34) Jan 23 2011 Sweet! I'd thought about doing something like that before, but frankly I...
- Nick Sabalausky (5/35) Jan 24 2011 You should stick this in the announcements group. This sounds like a
- Adam Ruppe (28/29) Jan 24 2011 Yeah, I'm still working some kinks out though. (Trial by fire - I
- Piotr Szturmaj (30/30) Jan 24 2011 Hi, I'm also working on postgresql client implementation and common DB
- Adam Ruppe (51/59) Jan 25 2011 Very nice! I did something similar in two cases: one is the
- Andrew Wiley (4/55) Jan 25 2011 I know I keep beating this horse, but in other languages, this is the
- Piotr Szturmaj (10/14) Jan 25 2011 I fully agree. However, I did create some substitute:
- Jacob Carlborg (4/18) Jan 25 2011 That would be really nice to have.
- Adam Ruppe (4/8) Jan 25 2011 I agree, I think the @attrs should have been shorthand for
- Piotr Szturmaj (8/28) Jan 25 2011 Please read my messages in "D2 postgresql interface - Phobos2?" thread
- Adam Ruppe (3/4) Jan 25 2011 (D.learn).
alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") User;
// sql source code , table to fetch
void main() {
auto obj = new User(null); // that null should actually be a db handle if
you want to be able to commit changes
// read/write access to the user table, with compile time
// type and name checks
obj.first = "Changing my name!";
obj.date_last_edited = getUTCtime();
obj.commitChanges(); // save your writes back to the database,
(reasonably) efficiently
}
D can! It can dynamically make those DataObjects too, with the
same syntax, but you lose compile time checks for obvious reasons.
I just couldn't help but laugh a bit when I ran this thing through
CTFE and found it actually worked, so figured I'd share a positive
message with you guys :-)
See my code here:
http://arsdnet.net/dcode/database.d
You might want a database implementation to play with too:
http://arsdnet.net/dcode/mysql.d (best for this purpose so far)
http://arsdnet.net/dcode/sqlite.d (but the others work too)
http://arsdnet.net/dcode/postgres.d
A simple db.sql file is here:
http://arsdnet.net/dcode/db.sql
It's just a list of CREATE TABLE commands. I ran the program over
a far more complex file too and it worked, but it isn't a super
smart parser so you can probably break it pretty easily. (for one,
it is case sensitive where as standard SQL is not)
Jan 23 2011
Adam D. Ruppe <destructionator gmail.com> wrote:
alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") User;
// sql source code , table to fetch
void main() {
auto obj = new User(null); // that null should actually be a db
handle if
you want to be able to commit changes
// read/write access to the user table, with compile time
// type and name checks
obj.first = "Changing my name!";
obj.date_last_edited = getUTCtime();
obj.commitChanges(); // save your writes back to the database,
(reasonably) efficiently
}
D can! It can dynamically make those DataObjects too, with the
same syntax, but you lose compile time checks for obvious reasons.
I just couldn't help but laugh a bit when I ran this thing through
CTFE and found it actually worked, so figured I'd share a positive
message with you guys :-)
This is awesome. I've known that D could do it, and I've occasionally
poked at it myself, but always felt it was too much work for
something that isn't all that hard to do by hand. Now, getting
someone else to do it for me...
--
Simen
Jan 23 2011
Adam D. Ruppe <destructionator gmail.com> Simen kjaeraas wrote:I've known that D could do it, and I've occasionally poked at it myself, but always felt it was too much work for something that isn't all that hard to do by hand.Yeah, that's the way I feel about it mostly. What finally pushed me over the edge here was writing yet /another/ duplicated chunk of code for UPDATE and INSERT. Easy to do, but, ugh, so tedious!
Jan 23 2011
"Adam D. Ruppe" <destructionator gmail.com> wrote in message
news:ihhsv0$1fgf$1 digitalmars.com...
alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") User;
// sql source code , table to fetch
void main() {
auto obj = new User(null); // that null should actually be a db handle
if
you want to be able to commit changes
// read/write access to the user table, with compile time
// type and name checks
obj.first = "Changing my name!";
obj.date_last_edited = getUTCtime();
obj.commitChanges(); // save your writes back to the database,
(reasonably) efficiently
}
D can! It can dynamically make those DataObjects too, with the
same syntax, but you lose compile time checks for obvious reasons.
I just couldn't help but laugh a bit when I ran this thing through
CTFE and found it actually worked, so figured I'd share a positive
message with you guys :-)
See my code here:
http://arsdnet.net/dcode/database.d
You might want a database implementation to play with too:
http://arsdnet.net/dcode/mysql.d (best for this purpose so far)
http://arsdnet.net/dcode/sqlite.d (but the others work too)
http://arsdnet.net/dcode/postgres.d
A simple db.sql file is here:
http://arsdnet.net/dcode/db.sql
It's just a list of CREATE TABLE commands. I ran the program over
a far more complex file too and it worked, but it isn't a super
smart parser so you can probably break it pretty easily. (for one,
it is case sensitive where as standard SQL is not)
Sweet! I'd thought about doing something like that before, but frankly I'm
glad someone beat me to it :)
Jan 23 2011
"Adam D. Ruppe" <destructionator gmail.com> wrote in message
news:ihhsv0$1fgf$1 digitalmars.com...
alias DataObjectFromSqlCreateTable!(import("db.sql"), "users") User;
// sql source code , table to fetch
void main() {
auto obj = new User(null); // that null should actually be a db handle
if
you want to be able to commit changes
// read/write access to the user table, with compile time
// type and name checks
obj.first = "Changing my name!";
obj.date_last_edited = getUTCtime();
obj.commitChanges(); // save your writes back to the database,
(reasonably) efficiently
}
D can! It can dynamically make those DataObjects too, with the
same syntax, but you lose compile time checks for obvious reasons.
I just couldn't help but laugh a bit when I ran this thing through
CTFE and found it actually worked, so figured I'd share a positive
message with you guys :-)
See my code here:
http://arsdnet.net/dcode/database.d
You might want a database implementation to play with too:
http://arsdnet.net/dcode/mysql.d (best for this purpose so far)
http://arsdnet.net/dcode/sqlite.d (but the others work too)
http://arsdnet.net/dcode/postgres.d
A simple db.sql file is here:
http://arsdnet.net/dcode/db.sql
It's just a list of CREATE TABLE commands. I ran the program over
a far more complex file too and it worked, but it isn't a super
smart parser so you can probably break it pretty easily. (for one,
it is case sensitive where as standard SQL is not)
You should stick this in the announcements group. This sounds like a
fantasically useful tool, but with only a post in this "misc bin" NG named
"Can your programming language do this?" it's likely to just get lost.
Jan 24 2011
Nick wrote:You should stick this in the announcements group.Yeah, I'm still working some kinks out though. (Trial by fire - I ended up pushing the *new* mysql module to the live work server in an unrelated update yesterday! So now frantically fixing things for tonight's launches... The breakage is it used to have query(T...)(). Now it is query(...). The reason is templates aren't allowed in interfaces, and I wanted query in the interface, so yeah. (Before, I just used the class everywhere and didn't have an interface at all.) Now, the template would handle all types or fail to compile. The runtime (...) only does a specific list, and fails at runtime. (I also tried Variant[]..., which is in my interface for queryImpl, the actual virtual function, but it wasn't convenient to use and broke virtually *all* of my existing code. So I went with plain ...) So I put in the common types I know I use: int, long, string. The basics. And now I'm getting exceptions on such ridiculous things like const(long), immutable(long), immutable(string) and the sort! I didn't expect const and immutable to have so many different typeid, especially on the primitives. Really, what's the difference between const(int) and immutable(int)? But it's easy enough to add them at least. I really wish we had the ability to have final templates in interfaces though. Oh well, it works anyway, just needs more experience in it. But yeah, next weekend I'll post it to the announcement group. These kinks should be worked out by then. I just posted it yesterday because I think this newsgroup gets too little positive success stories about working with D!
Jan 24 2011
Hi, I'm also working on postgresql client implementation and common DB
interface (in my spare time between other projects). It's using native
postgres protocol without using libpq. Tt also supports binary
formatting of row fields - that should outperform textual libraries (no
parsing/toString).
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:
PGConnection conn = new PGConnection;
conn.open([
"host" : "localhost",
"database": "test",
"user" : "postgres",
"password" : "postgres"
]);
auto cmd = new PGCommand(conn, "SELECT typname, typlen FROM pg_type");
struct S { string s; short l; }
auto result = cmd.executeQuery!(Tuple!(string, "s", short, "l"))();
auto result = cmd.executeQuery!S();
// those above two lines are equivalent
foreach (i, row; result)
{
// row is DBRow!Tuple or DBRow!S
writeln(i, " - ", row.s, ", ", row.l);
}
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.
Maybe we could join our efforts and create kind of hybrid DB library? :)
Jan 24 2011
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?
Jan 25 2011
On Tue, Jan 25, 2011 at 9:48 AM, Adam Ruppe <destructionator gmail.com>wrote:Piotr Szturmaj wrote:I know I keep beating this horse, but in other languages, this is the textbook example for why annotations are useful. Adding metadata to code, even if it can only be examined at compile time, can be immensely useful.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?
Jan 25 2011
Andrew Wiley wrote:I know I keep beating this horse, but in other languages, this is the textbook example for why annotations are useful. Adding metadata to code, even if it can only be examined at compile time, can be immensely useful.I fully agree. However, I did create some substitute: struct City { Serial!int id; // auto increment string name; mixin PrimaryKey!(id); mixin Unique!(name); } but still, legal attributes such as .NET ones would be more appreciated.
Jan 25 2011
On 2011-01-25 17:41, Piotr Szturmaj wrote:Andrew Wiley wrote:That would be really nice to have. -- /Jacob CarlborgI know I keep beating this horse, but in other languages, this is the textbook example for why annotations are useful. Adding metadata to code, even if it can only be examined at compile time, can be immensely useful.I fully agree. However, I did create some substitute: struct City { Serial!int id; // auto increment string name; mixin PrimaryKey!(id); mixin Unique!(name); } but still, legal attributes such as .NET ones would be more appreciated.
Jan 25 2011
I know I keep beating this horse, but in other languages, this is the textbook example for why annotations are useful. Adding metadata to code, even if it can only be examined at compile time, can be immensely useful.I agree, I think the attrs should have been shorthand for user defined templates, or at least available as a string list through __traits. (this is one place where I disagree with the status quo quite a bit) But, we can emulate it through mixins and strings, at least.
Jan 25 2011
Adam Ruppe wrote: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 }That's nice too!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!Please read my messages in "D2 postgresql interface - Phobos2?" thread (D.learn). There are examples of structs with arrays (postgres supports them); primary, unique and foreign keys. I've also managed to generate D's enums using CREATE TYPE in postgres :)Not yet. First, I must clean it from some messed up code :) Then I plan to publish it on github under Boost license.Maybe we could join our efforts and create kind of hybrid DB library? :)Indeed. Is your code on the internet somewhere?
Jan 25 2011
Please read my messages in "D2 postgresql interface - Phobos2?" thread(D.learn). Wow, that looks outstanding! I look forward to the release.
Jan 25 2011