/ 
SQL-AnyDBD-0.02
River stage zero No dependents
/ SQL::AnyDBD

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 $sb  = SQL::AnyDBD->new($dbh);

my $rows_desired = 8;
my $start_row    = 4;

warn $sb->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

$sb->LIMIT(rows_desired => $r [ , start_row => $s ] )

REQUIRED: rows_desired
OPTIONAL: start_row

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.

$sb->IN ( values => \@v )

REQUIRED: values

It is required that the arrayref values be passed. It is not required that the arrayref have any data:

my $sql = $sb->IN(values => [12..14]);

# yields...

IN (12,13,14)

$sb->SELECT ( fields => \@f, tables => \@t, ... )

REQUIRED: fields, tables
OPTIONAL: where, group_by, having, order_by, limit

All the optional arguments take a string as an argument with the exception of limit which takes a hashref which is passed to LIMIT(). Both 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 $sb  = SQL::AnyDBD->new($dbh);
 my $sql = $sb->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

$sb->DELETE ( table => $tbl [ , ... ] )

REQUIRED: table
OPTIONAL: where, order_by, limit

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 $sb  = SQL::AnyDBD->new($dbh);
 my $sql = $sb->DELETE(%parms);

 # yields ...

 DELETE FROM 
   student 
 WHERE 
   student.classes_id = 420 
 ORDER BY 
   date_enrolled 
 LIMIT 4

$sb->UPDATE ( set => $set_expr, , table => $t [, ...] )

REQUIRED: set, table
OPTIONAL: where, limit

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 $sb  = SQL::AnyDBD->new($dbh);
 my $sql = $sb->UPDATE(%update);

 # yields...

 UPDATE student SET student_ssan = NULL WHERE student_country_id <> 1 LIMIT 12

$sb->INSERT ( table => $tbl, values => \@values ...

REQUIRED: table, values
OPTIONAL: columns
my %insert =
  (
    table     => "student",
    columns   => [qw(student_ssan3 student_ssan2 student_ssan4 
                     student_lname student_fname)],
    values    => [qw(123 45 9876 olajuwon hakeem)]
  );

my $sb  = SQL::AnyDBD->new($dbh);
my $sql = $sb->INSERT(%insert);

# yields ...

INSERT INTO student 
  (student_ssan3, student_ssan2, student_ssan4,student_lname,student_fname) 
VALUES
  (123,           45,            9876,         olajuwon,     hakeem)

EXPORT

None by default.

SEE ALSO

Multi-database Products on CPAN

SQL::Translator
Alzabo
Class::DBI
DBIx::Recordset
DBIx::AnyDBD

Supported RDBMSes

Postgresql

http://www.postgresql.org

SQLite

http://www.sqlite.org

MySQL

http://www.mysql.com

AUTHOR

Terrence Brannon, <tbone@cpan.org>

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.