www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.announce - Regal: An SQL relational algebra builder

reply "Dylan Knutson" <tcdknutson gmail.com> writes:
Hi all,

I'd like to announce the initial version of Regal, an SQL 
relational algebra builder for D. It's intended as a backbone for 
a relational database ORM, in line with how Arel works with 
Rails' ActiveRecord, but nearly any project that generates SQL 
dynamically can benefit from it. The goal of the library is to 
make programmatically generating queries simple (and more robust 
than simple string concatenation), and to be database agnostic 
(it's very easy to write database specific printers for a Regal 
AST).

There are extensive examples available in the Readme about how to 
use the library, from generating very simple queries (SELECT * 
FROM users) to multiple chained joins and nested constraints. 
Here's an example from the documentation: finding a user with ID 
1:

```
auto users = new Table(
   "users", // table name
   "id",    // the rest are columns
   "name",
   "created_at",
   "updated_at");

// SELECT * FROM users WHERE users.id = 1 LIMIT 1
users
   .where(users.id.eq(1))
   .limit(1)
   .project(new Sql("*"))
   .to_sql
```

The library can be found at: https://github.com/dymk/regal
And the dub package: http://code.dlang.org/packages/regal

Please let me know if you find any bugs via the Github tracker!

Regards,
Dylan
May 15 2014
parent reply Jacob Carlborg <doob me.com> writes:
On 16/05/14 02:29, Dylan Knutson wrote:

 Hi all,

 I'd like to announce the initial version of Regal, an SQL relational
 algebra builder for D. It's intended as a backbone for a relational
 database ORM, in line with how Arel works with Rails' ActiveRecord, but
 nearly any project that generates SQL dynamically can benefit from it.
 The goal of the library is to make programmatically generating queries
 simple (and more robust than simple string concatenation), and to be
 database agnostic (it's very easy to write database specific printers
 for a Regal AST).

 There are extensive examples available in the Readme about how to use
 the library, from generating very simple queries (SELECT * FROM users)
 to multiple chained joins and nested constraints. Here's an example from
 the documentation: finding a user with ID 1:

 ```
 auto users = new Table(
    "users", // table name
    "id",    // the rest are columns
    "name",
    "created_at",
    "updated_at");


Does this need to be a class, can it be a struct instead?


 // SELECT * FROM users WHERE users.id = 1 LIMIT 1
 users
    .where(users.id.eq(1))
    .limit(1)
    .project(new Sql("*"))
    .to_sql
 ```


Why is 'new Sql("*")' needed? If you need to have a specific type, could 
it be a struct instead?


 The library can be found at: https://github.com/dymk/regal
 And the dub package: http://code.dlang.org/packages/regal


This all looks pretty nice :). Does it work at compile time?

-- 
/Jacob Carlborg
May 15 2014
parent reply "Dylan Knutson" <tcdknutson gmail.com> writes:
 Does this need to be a class, can it be a struct instead?


Ya know, it might be able to be made into a struct; I'll fiddle 
with it tomorrow. The main reason it was made a class was so 
.join had to take a Table type as its first parameter, and 
internally Table implements a Joinable interface (which is needed 
for chaining .joins and propagating the table name during 
printing).


 Why is 'new Sql("*")' needed? If you need to have a specific 
 type, could it be a struct instead?


The Sql class is needed because it's a node in the AST (all nodes 
inherit from a basic Node class) that just prints out its content 
verbatim when to_sql is called. All the methods that take a Node 
would need to be modified/templated to also accept a specialized 
SQL struct type, which unfortunately isn't feasible. If it was 
done, it'd probably be implemented using std.variant's Algebraic 
type, but then CTFE is forfeited. If you know of another way to 
go about doing this, I'd be really interested in hearing it. It 
is unfortunate that the library has to make so many small 
allocations, for sure.

Another option is, you could try using CTFE as much as possible 
to get the allocations over with at compile time :)


 The library can be found at: https://github.com/dymk/regal
 And the dub package: http://code.dlang.org/packages/regal


 This all looks pretty nice :). Does it work at compile time?


Thank you! I haven't tested it, but it's not using any features 
that would impede CTFE. So that's a solid "probably".
May 16 2014
parent reply Jacob Carlborg <doob me.com> writes:
On 16/05/14 09:58, Dylan Knutson wrote:


 Ya know, it might be able to be made into a struct; I'll fiddle with it
 tomorrow. The main reason it was made a class was so .join had to take a
 Table type as its first parameter, and internally Table implements a
 Joinable interface (which is needed for chaining .joins and propagating
 the table name during printing).


Can you do compile time introspection, like ranges do? Basically check 
if it has a "join" method.


 The Sql class is needed because it's a node in the AST (all nodes
 inherit from a basic Node class) that just prints out its content
 verbatim when to_sql is called. All the methods that take a Node would
 need to be modified/templated to also accept a specialized SQL struct
 type, which unfortunately isn't feasible. If it was done, it'd probably
 be implemented using std.variant's Algebraic type, but then CTFE is
 forfeited. If you know of another way to go about doing this, I'd be
 really interested in hearing it. It is unfortunate that the library has
 to make so many small allocations, for sure.


I don't know, use structs and compile time introspection.


 Another option is, you could try using CTFE as much as possible to get
 the allocations over with at compile time :)




-- 
/Jacob Carlborg
May 16 2014
parent reply "Dylan Knutson" <tcdknutson gmail.com> writes:
On Friday, 16 May 2014 at 13:42:30 UTC, Jacob Carlborg wrote:

 On 16/05/14 09:58, Dylan Knutson wrote:


 Ya know, it might be able to be made into a struct; I'll 
 fiddle with it
 tomorrow. The main reason it was made a class was so .join had 
 to take a
 Table type as its first parameter, and internally Table 
 implements a
 Joinable interface (which is needed for chaining .joins and 
 propagating
 the table name during printing).


 Can you do compile time introspection, like ranges do? 
 Basically check if it has a "join" method.


I've played around with making things structs a bit more, and 
have modified regal to have Table and Sql be structs (by having 
Sql and the generic Node class wrapped in a tagged union). Making 
Table a struct was just a matter of putting some common methods 
in a template mixin, and mixin'ing that where appropriate.

New version is at ~master on github and code.dlang.org
May 19 2014
parent Jacob Carlborg <doob me.com> writes:
On 2014-05-19 16:45, Dylan Knutson wrote:


 I've played around with making things structs a bit more, and have
 modified regal to have Table and Sql be structs (by having Sql and the
 generic Node class wrapped in a tagged union). Making Table a struct was
 just a matter of putting some common methods in a template mixin, and
 mixin'ing that where appropriate.

 New version is at ~master on github and code.dlang.org


Cool.

-- 
/Jacob Carlborg
May 19 2014