NAME
SQL::AnyDBD - Perl extension to generate SQL for RDBMS variants
SYNOPSIS
use SQL::AnyDBD;
my $dbh = DBI->connect($dsn, $user, $pass, $attr) or die $!;
my $sa = SQL::AnyDBD->new($dbh);
my $rows_desired = 8;
my $start_row = 4;
warn $sa->LIMIT(rows_desired => $rows_desired, start_row => $start_row);
# yields ...
LIMIT 8 OFFSET 4 -- for Pg and SQLite
4,8 -- for Mysql
ABSTRACT
SQL::AnyDBD is module which generates SQL for different RDBMSes from a uniform API. It uses DBIx::AnyDBD to determine which SQL variant to generate. In this documentation, "the big 3" is used to refer to the 3 free popular databases: Postgresql, SQLite, and MySQL (There is a reason I listed them in that order, but now is no time to get into a holy war. :)
METHODS
$sa->LIMIT(rows_desired => $r [ , start_row => $s ] )
A limit clause is used to limit the result set from an SQL select. Each of the big 3 supports this concept. All 3 also accept integers for both arguments. However, Pg can also accept the term ALL
for rows_desired
.
$sa->IN ( values => \@v )
It is required that the arrayref values
be passed. It is not required that the arrayref have any data:
my $sql = $sa->IN(values => [12..14]);
# yields...
IN (12,13,14)
$sa->AS ( $real, $alias)
my $sql = $sa->AS('userprefs', 'up');
# yields...
userprefs AS up
$sa->SELECT ( fields => \@f, tables => \@t, ... )
All the optional arguments take a string as an argument with the exception of limit
which takes a hashref which is passed to LIMIT()
. 'tables' and the required arguments take an arrayref even if they only have one argument:
my %select =
(
fields => [qw(student_ssan)],
tables => ["student INNER JOIN classes"],
where => "student.classes_id = classes.classes_id",
group_by => "classes_year",
having => "student_age > 30",
order_by => 'student_id',
limit => { rows_desired => 5, start_row => 77 },
);
my $sa = SQL::AnyDBD->new($dbh);
my $sql = $sa->SELECT(%select);
# yields...
SELECT
student_ssan
FROM
student INNER JOIN classes
WHERE
student.classes_id = classes.classes_id
GROUP BY
classes_year
HAVING
student_age > 30
ORDER BY
student_id
LIMIT
5
OFFSET
77
As of 0.03 'tables' is optional so that you can do queries liek this:
my $sql = $sa->SELECT( 'fields' => [ $sa->function('NOW', time) ]);
#yields:
SELECT NOW(1170616724)
$sa->DELETE ( table => $tbl [ , ... ] )
table
is the name of the table from which to delete records. where
is a string specifying the filtering of rows. order_by
is useful in conjunction with limit
in order to delete rows based on order:
my %parms =
(
table => 'student',
where => "student.classes_id = 420",
order_by => 'date_enrolled',
limit => 4
);
my $sa = SQL::AnyDBD->new($dbh);
my $sql = $sa->DELETE(%parms);
# yields ...
DELETE FROM
student
WHERE
student.classes_id = 420
ORDER BY
date_enrolled
LIMIT 4
$sa->UPDATE ( set => $set_expr, , table => $t [, ...] )
The required argument set is a string consisting of a series of
col_name1=expr1 [, col_name2=expr2, ...]
expressions. The required argument table
is a table name.
All the optional arguments take a scalar as an argument. limit
which takes a hashref which is passed to LIMIT()
. Both required arguments take an arrayref even if they only have one argument:
my %update =
(
table => "student",
set => "student_ssan = NULL",
where => "student_country_id <> 1",
limit => 12
);
my $sa = SQL::AnyDBD->new($dbh);
my $sql = $sa->UPDATE(%update);
# yields...
UPDATE student SET student_ssan = NULL WHERE student_country_id <> 1 LIMIT 12
$sa->INSERT ( table => $tbl, values => \@values ...
my %insert =
(
table => "student",
columns => [qw(student_ssan3 student_ssan2 student_ssan4
student_lname student_fname)],
values => [qw(123 45 9876 olajuwon hakeem)]
);
my $sa = SQL::AnyDBD->new($dbh);
my $sql = $sa->INSERT(%insert);
# yields ...
INSERT INTO student
(student_ssan3, student_ssan2, student_ssan4,student_lname,student_fname)
VALUES
(123, 45, 9876, olajuwon, hakeem)
Utility methods
$sa->function($function_name, $argument_string)
This utility method helps us to consistently render funtion call SQL.
$sa->function('foo') # FOO()
$sa->function('bar', q{'baz = 1'}) # BAR('baz = 1')
A subclass might have a lookup hash in its function() where it translates common functions to its specific type and set default arguments:
$sa->function('NOW') # SYSTEM_TIME( GMTOFFSET -4 )
$sa->get_placeholder_string()
Pass a single array ref or array and you get back a string suitable for placeholding ('?' joined with commas):
my @people = (
['Larry', 'Wall'],
['Homer', 'Simpson'],
['Joe', 'Mama'],
);
my $sth = $dbh->prepare(
$sa->INSERT(
'table' => 'people',
'values' => [ 'NULL', $sa->get_placeholder_string( $people[0] ) ], # ?,?
)
);
# INSERT INTO people VALUES(NULL,?,?)
for my $person ( @people ) {
$sth->execute( @{ $person } );
}
$sa->get_placeholder_array()
Same as get_placeholder_string() except it returns an array of '?' placeholder (or an array ref of the same in scalar context)
$sa->get_sql_from_files()
Takes a list of files and concatenates their contents together.
If a given argument exists with a dot-normalized_sql_driver_name that is used instead.
If you are using SQLite and do this:
my $sql = $sa->get_sql_from_files('./sql/schema');
Then is uses ./sql/schema.sqlite if it exists, and ./sql/schema if it does not.
If the last argument is a code ref its return value is what gets concatenated instead of the raw file. It gets called with these arguments: SQL::AnyDBD-object, SQL, FILE_PASSED, FILE_OPENED $sa, -contents-, './sql/schema', './sql/schema.sqlite'
$sa->normalized_sql_driver_name()
Normalized version of $sa->sql_driver_name()
$sa->sql_driver_name()
SQL::AnyDBD driver name
$sa->sql_driver_version()
SQL::AnyDBD driver version number.
$sa->get_dbh()
Returns the $dbh passed to new(). provided by DBIx::AnyDBD
SEE ALSO
Multi-database Products on CPAN
Supported RDBMSes
- Postgresql
-
http://www.postgresql.org
- SQLite
-
http://www.sqlite.org
- MySQL
-
http://www.mysql.com
AUTHOR
Terrence Brannon, <tbone@cpan.org>
v0.03 Co-maintainer Daniel Muey, http://drmuey.com/cpan_contact.pl
COPYRIGHT AND LICENSE
Copyright 2003 by Terrence Brannon
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.