www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - Can your programming language do this?

reply Adam D. Ruppe <destructionator gmail.com> writes:
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
next sibling parent reply "Simen kjaeraas" <simen.kjaras gmail.com> writes:
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
parent Adam D. Ruppe <destructionator gmail.com> writes:
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
prev sibling next sibling parent "Nick Sabalausky" <a a.a> writes:
"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
prev sibling parent reply "Nick Sabalausky" <a a.a> writes:
"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
next sibling parent reply Adam Ruppe <destructionator gmail.com> writes:
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
parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
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
parent reply Adam Ruppe <destructionator gmail.com> writes:
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
next sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
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 :)
 Maybe we could join our efforts and create kind of hybrid
 DB library? :)

Indeed. Is your code on the internet somewhere?

Not yet. First, I must clean it from some messed up code :) Then I plan to publish it on github under Boost license.
Jan 25 2011
parent Adam Ruppe <destructionator gmail.com> writes:
 Please read my messages in "D2 postgresql interface - Phobos2?" thread

Wow, that looks outstanding! I look forward to the release.
Jan 25 2011
prev sibling next sibling parent reply Piotr Szturmaj <bncrbme jadamspam.pl> writes:
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
parent Jacob Carlborg <doob me.com> writes:
On 2011-01-25 17:41, Piotr Szturmaj wrote:
 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.

That would be really nice to have. -- /Jacob Carlborg
Jan 25 2011
prev sibling parent Adam Ruppe <destructionator gmail.com> writes:
 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
prev sibling parent Andrew Wiley <debio264 gmail.com> writes:
--001636c5b43c33f51c049aae27dc
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Jan 25, 2011 at 9:48 AM, Adam Ruppe <destructionator gmail.com>wrote:

 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?

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. --001636c5b43c33f51c049aae27dc Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable <div class=3D"gmail_quote">On Tue, Jan 25, 2011 at 9:48 AM, Adam Ruppe <spa= n dir=3D"ltr">&lt;<a href=3D"mailto:destructionator gmail.com">destructiona= tor gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" st= yle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> <div class=3D"im">Piotr Szturmaj wrote:<br> &gt; I already have working postgresql row querying. There are typed and<br=

&gt; encapsulated using struct or tuple, for example:<br> <br> </div>Very nice! I did something similar in two cases: one is the<br> byStruct in mysql.d (currently commented out - it was rather buggy<br> anyway) and the other is the next(T..) function in sqlite.<br> <br> <br> byStruct translated a textual mysql row into the given struct, by<br> translating the names:<br> <br> foreach(member; __traits(allMembers, S)) {<br> =A0 =A0 =A0 =A0S.member =3D to!(typeof(S.member))(db_row[member]);<br> }<br> =A0return S;<br> <br> (pseudocode just to show the idea).<br> <br> <br> My implementation was always a little buggy though, so I didn&#39;t<br> use it much. I still like the idea though!<br> <br> <br> The other thing is to fill in a bunch of local variables. This<br> was my first attempt at database in D, written before we had<br> immutable and half of Phobos. You can see some of it in sqlite.d:<br> <br> auto db_statement =3D db.prepare(&quot;SELECT id, name FROM users&quot;);<b= r> <br> string name;<br> int id;<br> <br> db_statement.execute();<br> <br> while(db_statement.next(id, name)) {<br> =A0 =A0 =A0// the variables id and name are now filled in with the row<br> }<br> <br> <br> It would fill them in positionally, and coerce the type from the<br> db to match the variables passed in. I don&#39;t remember why I abandoned<b= r> that. I think it was just a long gap between writing it and the next<br> time I needed a database, which was mysql, so the sqlite module didn&#39;t<= br> get reused.<br> <br> It&#39;s a little tedious to use anyway though. I prefer having all<br> the variables in the scope of the loop, rather than outside like<br> it is here.<br> <br> <br> <br> <br> But anyway, returning a Tuple!() is something I&#39;ve never tried,<br> I like the idea.<br> <div class=3D"im"><br> <br> &gt; I&#39;ve also written simple ORM mapping - creating and managing table= s<br> &gt; based on struct layout. I see your solution is in opposite -<br> &gt; creating structs/classes based on SQL CREATE scripts.<br> <br> </div>Yeah, the main reason there is all my attempts to go struct &gt; sql<= br> ended up being pretty sucky.<br> <br> I&#39;d start with a basic struct. Getting it to a simple create table<br> command is pretty straightforward (hey, remember that post a month<br> or two ago where someone did that in Go and was like &quot;yay Go rocks!&qu= ot;<br> That thread is actually what inspired the subject to this thread.)<br> <br> But, a real database has a lot of constraints on top of that -<br> field lengths, foreign keys, checks, and so on.<br> <br> I tried two approaches: one was magic. Add stuff based on the names<br> and types, so assume &quot;int id&quot; is a primary key, for example.<br> <br> Didn&#39;t work in practice. What about a table with a primary key<br> that spans two columns?<br></blockquote><div><br></div><div>I know I keep b= eating 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 b= e examined at compile time, can be immensely useful.</div> </div> --001636c5b43c33f51c049aae27dc--
Jan 25 2011