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 _______________________________________________ List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
