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 => $rows_desired, start_row => $start_row )

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 => [qw(fee fi fo fum)]);

 # yields...

 IN (fee,fi,fo,fum)

$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);

$sb->UPDATE ( set => $set_expr, ...

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.

cpanm SQL::AnyDBD

perl -MCPAN -e shell
install SQL::AnyDBD