NAME

SQLib - A simple module to manage and store SQL queries in separate files.

VERSION

Version 0.05

AUTHOR

Mateusz Szczyrzyca, mateusz at szczyrzyca.pl

SYNOPSIS

The module allows to store SQL queries in separate files and provides easy access to them. This functionality is helpful if you want to separate a Perl code from a SQL code.

A file with list of queries has the following syntax:

[NAME_OF_QUERY1]
-- A SQL query with {vars} to replace 
[/NAME_OF_QUERY1]

[NAME_OF_QUERY2]
-- A SQL query with {vars} to replace
[/NAME_OF_QUERY2]

...

[NAME_OF_QUERY_N]
-- A SQL query with {vars} to replace
[/NAME_OF_QUERY_N]

[     QUERIES_WITH_SPACES_IN_NAME_ARE_POSSIBLE  ]
               -- A SQL query with spaces
[     /   QUERIES_WITH_SPACES_IN_NAME_ARE_POSSIBLE       ]

First parenthesis "[" always starts from a new line (don't use whitespaces).

Empty lines between queries are ignored. If there are two or more SQL queries with same [NAME], then only one (first) will be used.

If a query with a specified name doesn't exist then undef is returned as soon as if a file or query has an invalid syntax.

[QUERY_NAME]A sql code[/QUERY_NAME] isn't a valid syntax as well.

Simple example (file_with_queries.sql):

[CHECK_PASSWORD]
-- Comments for SQL debug
-- Some::Program @ CHECK_PASSWORD
-- Check a user password
SELECT
 login,password
FROM
 {table}
WHERE
(
  login = '{login}',
 AND
  password = '{password}'
);
[/CHECK_PASSWORD]

And how to use it in a perl code:

use SQLib;
my $SQLib = SQLib->new( './file_with_queries.sql' );

my %sql_params =
(
 table    => 'cms_users',
 login    => 'someuser',
 password => 'somepass'
);

my $check_auth_query = $SQLib->get_query( 'CHECK_PASSWORD', \%sql_params );

In the above example $check_auth_query contains:

-- Comments for SQL debug
-- Some::Program @ CHECK_PASSWORD
-- Check a user password
SELECT
 login,password
FROM
 cms_users
WHERE
(
  login = 'someuser',
 AND
  password = 'somepass'
);