SQL::Catalog 2.5

SQL::Catalog is a Perl module to query queries, label queries, db independent SQL, separate Perl and SQL.

SYNOPSIS

shell% cd sql_repository/city,date/weather/1/

shell% cat concrete.sql
select city, date from weather where temp_lo < 20 and temp_hi > 40 LIMIT 10
shell% sql_test concrete.sql
# see results of prepare, execute on this
shell% cat concrete.sql._test

shell% cat abstract.sql
select city, date from weather where temp_lo < ? and temp_hi > ?
# send in placeholder value
shell% sql_test abstract.sql 55
# let's see results... looks good
shell% cat abstract.sql._test

shell% sql_register abstract.sql basic_weather "basic weather query"
shell% cat abstract.sql._register
[basic_weather] inserted as
[select city from weather where temp_lo < ? and temp_hi > ?]

... then in a Perl program (e.g. test.pl in this distribution)

# Cache all queries to a Cache::Cache instead of runtime db-lookup
% shell perl -MSQL::Catalog -e 'SQL::Catalog->spider'

my $dbh = get_the_handle_as_you_please;
my $sql = memory and in a large system memory is precious.
my $sth = $dbh->prepare($sql);
$sth->execute(55);

my $rows = $sth->rows;

Over time, it has become obvious that a few things about SQL queries are necessary. And before this module, time-consuming:

database independence

You may at some time to be forced to deploy an application which has to work on more than one database. Prior to SQL::Catalog, there were two choices - DBIx::AnyDBD and DBIx::Recordset. SQL::Catalog will work well alongside the latter. And in fact, SQL::Catalog itself uses DBIx::AnyDBD.
Note though that because some databases can do in one query what takes 4 in another (ie, Postgres has SELECT * FROM X INTO ...), you may have to create subclasses of your database layer classes to actually handle each needed function. This is what DBIx::AnyDBD handles for you.

labelled queries

A large, well-scaled business database application has several layers with simple well-defined tasks. The layer just above the database does database things. It inserts. It retrieves. It updates. etc, etc. Call this the database application layer. Just above the database application layer is the business object layer. These are conceptual entities whose data structures are program data structures. For permanent stores, they make simple, technology-agnostic requests of the database application layer, which then takes the business data and stores it as database data. Then above this we have the application layer. And this layer makes use of business objects, ldap objects, web objects, what have you, to string together a complete application.

queryable queries

That's right, you want to be able to query on the queries themselves. It makes it easy to do a study on just what queries are doing what.

separation of concerns

By now, everyone has heard that phrase: "my templating module is the best because it allows the HTML designer to work separately from the Perl programmer." Well, given that databases are another foreign technology to Perl proper, it only makes sense that the same ability that is afforded to HTML designers be afforded to SQL programmers.

centralization of queries

This makes it easy for someone to see how you did something so they can imitate.

memory preservation

You may be sitting there thinking "this is no better than a Perl hashref". And if you are, then I congratulate you on making it to the 6th bulleted item instead of impatiently finding something else to do.

Anyway, the problem with using a Perl hashref is that it will consume memory and in a large system memory is precious.

Now you could go the way of tying hashrefs to disk, but then you don't get the querying capabilities with a Perl hashref that you get with logging your SQL in a database, so THERE... heheh.

SQL::Catalog addresses all of these issues.

Furthermore, you don't get the querying capabilities with a Perl hashref.
SQL::Catalog addresses all of these issues.

Product's homepage

Requirements:

· Perl