[Dbix-class] [OT][ANNOUNCE] SQL Generation with SQL::DB

[Mark Lawrence]

This is the first formal release announcement of SQL::DB, yet another
Perl/SQL interface. Version 0.04 of this distribution has just been
uploaded to CPAN (should be there in an hour).

  http://search.cpan.org/~mlawren/SQL-DB-0.04/

SQL::DB is in development and this early announcement is to obtain
feedback on the design and/or usefulness.

Most Object Relational Mappers (ORMs) tend to provide an object-focused
API, in an attempt to hide or abstract away the underlying SQL. SQL::DB
takes the opposide approach. It provides an SQL-focused API with some light
support for objects.

The features/characteristics of SQL::DB are:

  * Database table structure is modelled using Perl objects
  * Queries are constructed using abstract rows based on the model
  * Expressions use Perl logic operators: ( ) == != | &
  * Query syntax is very similar to writing SQL
  * No (known) limits to the type of SQL query generated. For example
    multiple JOINs, nested queries & database functions are all easy.

Perhaps the best way to turn those statements into something real is with
the following examples.

  #
  # Create a schema object (automatically creates abstract classes)
  #
  my $schema = SQL::DB::Schema->new( <definition for Artists,CDs,Tracks> );

  #
  # Instantiate some abstract row objects. 
  #
  my $artist = Artist::Abstract->new;
  my $cd     = CD::Abstract->new;
  my $track  = Track::Abstract->new;

  #
  # ------------------------------------------------------------ #
  #
  print $schema->query(
    insert => [$track->id, $track->cd, $track->title, $track->length],
    values => [3, 2, 'Who wants to live forever?', 285]
  );

  # INSERT INTO
  #     tracks (id, cd, title, length)
  # VALUES
  #     (?, ?, ?, ?)
  # /* '3', '2', 'Who wants to live forever?', '285' */


  #
  # ------------------------------------------------------------ #
  #
  print $schema->query(
      delete => $track,
      where  => $track->id == 3,
  );

  # DELETE FROM
  #     tracks
  # WHERE
  #     id = ?
  # /* '3' */


  #
  # ------------------------------------------------------------ #
  #
  print $schema->query(
    select    => [$cd->_columns],
    from      => $cd,
    left_join => $artist,
    on        => $cd->artist == $artist->id,
    where     => $artist->name->like('%Queen%'),
  );

  # SELECT
  #     t59.id,
  #     t59.title,
  #     t59.year,
  #     t59.artist
  # FROM
  #     cds AS t59
  # LEFT OUTER JOIN
  #     artists AS t60
  # ON
  #     t60.id = t59.artist
  # WHERE
  #     t60.name LIKE ?
  # /* '%Queen%' */


  #
  # ------------------------------------------------------------ #
  #
  print $schema->query(
    select     => [$track->id->func('count'),
                   $cd->title->as('mytitle'),
                   $track->length->func('max'),
                   $track->length->func('sum')],
    from       => [$track],
    inner_join => $cd,
    on         => $track->cd == $cd->id,
    group_by   => [$cd->title],
  );

  # SELECT
  #     COUNT(t50.id) AS count_id,
  #     t51.title AS mytitle,
  #     MAX(t50.length) AS max_length,
  #     SUM(t50.length) AS sum_length
  # FROM
  #     tracks AS t50
  # INNER JOIN
  #     cds AS t51
  # ON
  #     t50.cd = t51.id
  # GROUP BY
  #     t51.title
  # /*  */


  #
  # ------------------------------------------------------------ #
  #
  my $track2 = Track::Abstract->new;
  my $cd2    = CD::Abstract->new;

  my $query2 =  $schema->query(
      select   => [ $track2->title, $cd2->year ],
      distinct => 1,
      from     => [$track2, $cd2],
      where    => ($track2->length < 248) | ($cd2->year > 1997),
  );

  print $schema->query(
      select   => [ $track->title, $cd->year],
      from     => [$track, $cd],
      distinct => 1,
      where    => ( $track->length > 248 ) & ! ($cd->year < 1997),
      union    => $query2,
  );

  # SELECT DISTINCT
  #     t50.title,
  #     t52.year
  # FROM
  #     tracks AS t50,
  #     cds AS t52
  # WHERE
  #     ((t50.length > ?) AND NOT (t52.year < ?))
  # UNION
  # SELECT DISTINCT
  #     t53.title,
  #     t54.year
  # FROM
  #     tracks AS t53,
  #     cds AS t54
  # WHERE
  #     ((t53.length < ?) OR (t54.year > ?))
  # 
  # /* 248 1997 248 1997 */


A Database table definition looks like this:

    [
        class => 'CD',
        table => 'cds',
        columns => [
            [name => 'id', type => 'INTEGER', primary => 1],
            [name => 'title', type => 'VARCHAR(255)'],
            [name => 'year', type => 'INTEGER'],
            [name => 'artist', type => 'INTEGER', references => 'artists(id)'],
        ],
        unique  => 'title,artist',
        index   => [
            columns => 'title',
        ],
        index  => [
            columns => 'artist',
        ],
    ],

Since we have the table modelled we can also easily produce the table
creation statements:

    print $schema->table('cds')->sql, "\n";
    print join("\n", $schema->table('cds')->sql_index),"\n";

    # CREATE TABLE cds (
    #     id              INTEGER        NOT NULL,
    #     title           VARCHAR(255)   NOT NULL,
    #     year            INTEGER        NOT NULL,
    #     artist          INTEGER        NOT NULL REFERENCES artists(id),
    #     PRIMARY KEY(id),
    #     UNIQUE (title, artist)
    # )
    # CREATE INDEX cds_title ON cds (title)
    # CREATE INDEX cds_artist ON cds (artist)

SQL::DB::Schema is the more interesting SQL generation part, SQL::DB
is actually the module that ties this together with DBI.

As is usually the case with early development, documentation is lacking,
to say the least. So are a whole heap of unit tests and not every SQL
expression has been implemented. However I've already successfully used
SQL::DB in one personal project, and I have to say it was
refreshing to be able to make use of the full power of the database
instead of being forced to retrieve values and work on them in Perl.

I would like to encourage any kind of discussion and/or feedback, as I
feel this approach does provide quite some power and flexibility.

Regards,
Mark.
-- 
Mark Lawrence