Ovid

Sitting here in the Italian Perl Workshop and have been enjoying the talks and the excellent food. I was thinking about Matt Trout's Data::Query talk from yesterday. In a nutshell, Data::Query lets you write things like this:

SELECT { $_->cd->name }
  FROM { $_->cds, AS 'cd' }
  JOIN { $_->artists, AS 'artist' }
    ON { $_->cd->artistid eq $_->artist->id }
 WHERE { $_->artist->age > 25 }

This is very exciting, though it might not be immediately evident why.

The code shown should generate a data structure similar to this:

{
    'from' => {
        'from' => {
            'left' => {
                'from' => {
                    'elements' => [ 'cds' ],
                    'type'     => 'Identifier'
                },
                'to'   => 'cd',
                'type' => 'Alias'
            },
            'on' => {
                'args' => [
                    {   'elements' => [
                            'cd',
                            'artistid'
                        ],
                        'type' => 'Identifier'
                    },
                    {   'elements' => [
                            'artist',
                            'id'
                        ],
                        'type' => 'Identifier'
                    }
                ],
                'operator' => { 'Perl' => 'eq' },
                'type'     => 'Operator'
            },
            # snip

I've omitted most of the output for brevity.

You can then use a renderer to render that as SQL. What does this gain us over SQL::Abstract? Plenty! SQL::Abstract renders Perl code directly to SQL with no abstract syntax tree (AST) presentation (though it has a module for playing around with trees). What Data::Query offers is the phenomenal ability to play around with that tree before you convert it to SQL (or some other query language!). Why would you want to do this? Take a look at this bit from the above:

{   'elements' => [
        'artist',
        'id'
    ],
    'type' => 'Identifier'
}

Note that we have both a name and a primitive type (expanding type declarations would be good here). Those give us some interesting possibilities we didn't have before.

But first, let me daydream about some things I would like to see in SQL. Consider the following:

SELECT name FROM customer WHERE age > id;

This is completely non-sensical SQL that neither databases nor ORMs forbid. It's a type error. Just because the customer age and customer ID are both integers does not mean they should have an operator to compare them. In fact, we might argue that the only allowed SQL operators for an ID should be = or IN, but only compared against their own type (for example, a foreign key in another table).

Here's another interesting example:

SELECT c.name
  FROM customer c
  JOIN orders o ON o.id = c.id
 WHERE c.id = 7

Here if we declared that = can only compare against its own type, that should fail on the o.id = c.id. In other words, logic errors could (in theory) throw exceptions at compile time, except that SQL doesn't enforce this. However, what about the c.id = 7 case? That might take some work. If we claimed that 'ID' is a subtype of Integer, perhaps we could directly allow comparisons against constants (or bind params) whose type matches the type or subtype. Or perhaps we could have a casting syntax (something that Postgres allows)?

By defining types and the operators for them (conceptually similar to declaring a class and its methods), we could catch all sorts of errors in our SQL, but you poor MySQL sufferers will never get it.

... unless it's pushed up into a middleware layer between something like Data::Query and a renderer. The AST could be walked by a validator to ensure that, for example, a Fahrenheit subtype of Float can never be compared directly against a Celsius subtype of Float. You could cast them both as their parent type of Float and generate a logic error that way, but the programmer would have to try to subvert the type system but the type system itself is strict by default.

All sorts of delightful type safety are now in our reach when Data::Query is released. Matt Trout already has a branch of DBIx::Class running on top of Data::Query and this is tremendously exciting to me. You could potentially derive much of your type data from DBIx::Class metadata and if you've been careful, have "type safety" (cough) be a side effect of using an ORM!

Also note that Data::Query is not tied to SQL. Thus, we could create composable ORM queries which would allow you to fetch a bunch of data from the database but still filter on data that doesn't exist in the db.

This code is not yet on the CPAN. To play around with it, you'll want to run this:

git clone git://git.shadowcat.co.uk/dbsrgits/Data-Query.git

Running the tests requires this (the current directory is required in @INC):

prove -l -I. t

You'll have to go through and install various modules you might be missing.