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

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

> 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".

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

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

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