SQL::Catalog - query queries, label queries, db independant SQL, separate Perl and SQL

NAME

SQL::Catalog - query queries, label queries, db independant 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;

DESCRIPTION

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

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.

COMMON STEPS TO USAGE

Develop your concrete query in a db shell

The first step to developing a database query is to play around at the db shell. In this case, you normally don't have any placeheld values. You just keep mucking with the query until it gives you what you want.

When you finally get what you want, save it in a file, say concrete.sql for example. Here is a concrete query:

 select city, date from weather where temp_hi > 20

Abstract your query with placeholders

Now it's time to make your query more abstract. So we do the following:

 select city, date from weather where temp_hi > ?

and save in a different file, say abstract.sql.

Now let's test this query also, being sure to pass in data for the placeholder fields:

 sql_test abstract.sql 34

Certain drivers are not very good with their error messages in response to queries sent in without placeholder bindings, so take care here.

And let's cat testexec.out to see the results.

Register your query (store in the sql_category table)

 sql_register abstract.sql city_date_via_temp_hi

and the system tells you

 [city_date_via_temp_hi] saved as
 [select city, date from weather where temp_hi > ?]

Use your query from DBI:

 use SQL::Catalog;
 my $dbh = SQL::Catalog->db_handle; # or however you get your DBI handles
 my $SQL = sql_lookup('city_date_via_temp_hi') or die "not found";
 my $sth = $dbh->prepare($SQL, $cgi->param('degrees'));
  .... etc

INSTALLATION

See the README in the home directory of the distribution.

What SQL::Catalog does

It stores each query in a database table with the label as key and the SQL query as the one value for that key. Then there is a foreign table with a number of useful query attributes such as type of query, tables and columns used and number of placeholders.

Right now we have schema creation and SQL code which works for MySQL (thanks to Jason W. May), Informix (thanks to Jonathan Leffler) and Postgresql (thanks to me, although I did use Marcel Grunaer's DBIx::Renderer to make it) and welcome more.

The queries are stored in these tables (this file is db-creation/postgresql.sql):

 CREATE TABLE sql_catalog (
        label varchar(80) ,
        cmd varchar(40) ,
        phold int4 ,
        author varchar(40) ,
        query varchar(65536) ,
        comments varchar(1600) ,
        PRIMARY KEY (label)
                          );
CREATE TABLE sql_catalog_ft (
        label_ft varchar(80) ,
        tbl varchar(255) ,
        col varchar(255) ,
        PRIMARY KEY (label_ft)
                             );

And here is the result of ONE sql_register:

 mydb=# select * from sql_catalog_ft;
 label_ft |   tbl   |   col   
 ----------+---------+---------
 basic_weather     | weather | city
 basic_weather     | weather | date
 basic_weather     | weather | temp_lo
 basic_weather     | weather | temp_hi
 (4 rows)
 mydb=# select * from sql_catalog;
 label |  cmd   | phold |  author  |                                    query                                     | comments 
 -------+--------+-------+----------+------------------------------------------------------------------------------+----------
 basic_weather  | SELECT |     1 | metaperl | select city, date, temp_lo, temp_hi from weather where temp_lo < ? LIMIT 40
 | ahah
 (1 row)

Queries are only *stored* in the database, by calling SQL::Catalog-spider>, you can move them into main memory or a file cache or whatever other kind of cache that Cache::Cache supports.

IMPORTANT NOTES

AUTHOR

T. M. Brannon, <tbone@cpan.org>

Substantial contribution (and ass-kicking) by Jonathan Leffler. MySQL table creation code contribution by Jason W. May.

SEE ALSO

There are several related modules on CPAN. Each do some of what SQL::Catalog does.

 SQL::Catalog - query queries, label queries, db independant SQL, separate Perl and SQL