Add Me!Close Menu Navigation

My technical corner about Linux, Perl, programming, computer networks and network security

Add Me!Open Categories Menu

SQLib – easy store your SQL queries in a separate file

In big commercial projects a separate some layers from a raw code is important. This is the typical advantages of almost each framework. Effects of this separation are templates, languages files, modules, etc.

But not every framework supports separating SQL queries. I didn’t find any good (for me) perl module to this task, but of course there are some in CPAN (eg: SQL Library). I decided to write my own simple module. I called this SQLib. It is written with Mouse/Moose so you need to the Mouse/Moose module.

 

DOWNLOAD: SQLib 

To install this module use the CPAN:

$ cpan SQLib

Usage

A file with queries:

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

[ANOTHER_QUERY1]
 [...]
[/ANOTHER_QUERY1]

[ANOTHER_QUERY2]
 [...]
[/ANOTHER_QUERY2]

 

And how to usage it in your code:

use SQLib;

my $SQL_Query = SQLib->new( { file_sql => './some_file_with_queries.sql' } );

### We are setting up the parameters
my %sql_params =
(
 table        => 'cms_users',
 login        => 'login',
 password     => 'password123'
);

$SQL_Query->query_name( 'CHECK_PASSWORD' );
$SQL_Query->query_params( \%sql_params );

### Display the parsed query
$SQL_Query->get_query;

 

In the example “$SQL_Query->get_query” contains:

-- Comments for SQL debug
-- Some::Program @ CHECK_PASSWORD
-- Change a user password
SELECT
 cms_users
WHERE
(
 login = ‘login’,
AND
 password = ‘password123′
);

 

Leave a Reply

You must be logged in to post a comment.