Sql::Simple

An Sql statement generation and execution library (for simple tasks)

SYNOPSIS:

Sql::Simple->setdbh($dbh);
# select price, weight from fruit where onSale = 'true'
my $result = Sql::Simple->query($dbh, [ qw(price, weight) ], 'fruit', { 'onSale' => 'true' });

DESCRIPTION:

This module is a re-hash of like fifty different modules already on CPAN. TWMTODI is a good thing, but sadly this is more of the case of being tired of seeing patch submissions being rejected into similar modules. The upside to this module is that it handles table joins rather elegantly (ie: no creation of seperate classes, config files, xml, GPS coordinates, or cat scans). The downside is probably the fact that I wrote it, as there are a few inefficient quirks to my coding style (comments appreciated).

Of course, this module covers the basics of sql... insert, update, delete, and select's. The ability to do things in mass is available as well, to assist you with large data maniuplations. (taking advantage of a database that takes advantage of placeholders, and setting AutoCommit to 0).

IMHO, this module is almost mostly DWIM. A nice OO approach is in the works, just needed some pointers from some friends before I could break ground on it. (I do not do a lot of web programming with perl anymore, more data transformation stuff, so this module suits me for what I do).

This module currently ONLY SUPPORTS ANSI SQL-92, there has been suggestions to make this more modular, but I think the db's will catch up before I do.

This module will only work with the following database servers (that I have personally tested so far)

Microsoft SQL Server 7, 2000 
  (tested via DBD::ODBC, supports full outer join)

Sybase 12 (11 does not support ANSI SQL 92) 
  Does not support FULL OUTER JOIN

PostgreSQL 7.3.X and above supports 
  (no FULL OUTER support)

MySQL 3.23 and [4.1 required if you intend on using subqueries]).  
  * Notes on MySQL
    LEFT JOIN and RIGHT JOIN are not ANSI compliant.  Sql::Simple 
    isn't going to stop you from using that syntax, however, this 
    document will refer to joins in ANSI syntax only.  MySQL 4 
    treats LEFT JOIN and LEFT OUTER JOIN synonmously.  In my 
    research, it appears that certain versions of 3.23 act this 
    way as well, but do not support FULL OUTER joins as a part 
    of their syntax.  Sql::Simple does not support Union (yet). 

Oracle 9i 
  (supports full outer join)

If there is some weird incompatability, you'll see it, as I cluck out the errors back to you by die'ing...

Here is a simple example...

Sql::Simple->delete($dbh, 'tablename', { 'whereColumn' => 'whereValue' });

Instead of...

eval {
  my $sql = 'delete from tablename where whereColumn = ?';
  my $sth = $dbh->prepare($sql);
  $sth->execute('whereValue');
  $sth->finish();
  $dbh->commit() unless ( $dbh->{'AutoCommit'} );
};
if ( $@ ) {
  $dbh->rollback();
  # do something to handle the exception...
}

Ok, am I gaining you anything by using this module? I think so.. 1. No declaration of any of those variables into your scope 2. Code reduction 3. No SQL will sit in your perl code (perl is good at formatting text, why get in it's way?) 4. Results from other routines can easily map into the argument stream, avoiding temporary placeholders (which for me tends to gain some performance) 5. I find that Sql abstraction layers, if done properly, can make simple tasks such as these short concise statements, instead of slightly larger blocks of code that are prone to irritating syntax and scope related issues. 6. I find writing sql tedious. 7. I find writing the code to execute sql tedious. 8. It's nice to be able to generate an sql statement. even if you intend on using your own execution methods..

"WHERE" clause structure

the generic where clause "structure" defining a constraint within a query

There is a unified function that generates clauses for all of the functions within Sql::Simple. I figure explaining it once will make update, delete, and query a bit easier to digest. (as it is used in insert, update, delete and subqueries within query). It is also used in the "FROM" clause within a query (specifically in the "ON" element structure).

  [
    {
      'column1' => 'value1',
      'column2' => \'is null',
      'column3' => [ 'val3-1', 'val3-2', 'val3-3' ],
    },
    {
      'column4' => {
		     'op' => 'like',
		     'val' => '%value4%'
		   },
      'column5' => {
                     'columns' => 'value5column',
		     'table' => 'value5table',
		     'where' => {
		                  'column5sub' => 'value5sub'
			        }
		   }
    }
  ]
      

This statment will generate the following clause. (or close to it, as the formatting might be off)

(
  column1 = ? 
  AND column2 is null 
  AND column3 in (?,?,?) 
) OR (
  column4 like ? 
  AND column5 in ( 
    SELECT value5column FROM value5table WHERE column5sub = ? 
  )
)

column1 is a simple "=" operator between the column name and the placeholder. column2 is a scalar reference forcing what you want verbatim (abuse to this function is kind of expected) column3 creates a "where in" clause which is interpreted as an OR statement

OR statement kicks in, creating another level of nesting for the next hashref

column4 creates a specific relationship, such as "greater than" or the "like" operator shown above. column5 creates a subquery with a where clause that can any level of depth of complexity as shown above.. (it is fully explained in the query function documentation)

Of course, if you don't have an or clause, just pass in the hash..

{
  'column1' => 'value1'
  'column2' => 'value2'
}

Which would generate ...

column1 = ? AND column2 = ?

This module will "auto-magically" figure out if your argument stream contains multiple objects to execute the statement with, or if it's just one really big statement.

(hmm, maybe I should have named this thing Sql::KindaSimple)

Logical operators

To retouch on the above, if you needed to do something like the following..

SELECT fruit FROM produce WHERE price > 10

The "logical operator" syntax (anything besides the basic "=" operator is executed by the following)..

Sql::Simple->query('fruit', 'produce', { 
  'price' => { 
    'op' => '>', 
    'val' => 10 
   } 
} );

To do a "between" query simply execute the follwing.

Sql::Simple->query('fruit', 'produce', { 
  'price' => { 
    'op' => 'between', 
    'val' => 10,
    'val2' => 20
   } 
} );

UPDATE (0.7): If you wanted to generate a clause that allowed a value, or a couple of values, OR allow null, you can now "allowNull"

'price' => { 
  'op' => 'between', 
  'val' => 10,
  'val2' => 20
  'allowNull' => 'true' # anything boolean true will work here
 } 

 # This generates...

 ( price between ? AND ?  OR price is null )

Variables

$DBH - (the database handle to use) You can modify this directly, or for your convenience you may call this helper method

Sql::Simple->setdbh($dbh);

$RETURNSTH - (if "true" it returns the statement handle, and the map)

Sql::Simple->returnsth(1); # returns a statement handle for the generated sql statement
Sql::Simple->returnsth(0); # default (execute)

$RETURNSQL - (if "true" just return the SQL statement generated, don't actually execute it) Or use the following.

Sql::Simple->setreturn(1); # for "return"
Sql::Simple->setreturn(0); # for "execute" [default]

$DEBUGSQL - (if "true" warn out the SQL being executed)

Sql::Simple->setdebug(1); # for "warn sql before execution"
Sql::Simple->setdebug(0); # for "no warn" [default]

If you do not set this, this module will expect the first argument to each function call to be the database handle

Sql::Simple->delete

provide an easy interface to delete row(s) from a table.

Two methods of invocation.

1. as a list of arguments

Sql::Simple->delete(
  databaseHandle, 
  'scalar tablename', 
  WHERE_CLAUSE_STRUCTURE (see documentation above),
);

2. or as a hash

Sql::Simple->delete(
  databaseHandle, 
  'table' => $tablename, 
  WHERE_CLAUSE_STRUCTURE (see documentation above),
);

Examples

create table books (
  title varchar(20)
  author varchar(20)
)

# set our dbh
Sql::Simple->setdbh($dbh);
# delete from books where title = 'Java Programming'
Sql::Simple->delete('books', { 'title' => 'Java Programming' });

Sql::Simple->update

provide an easy interface to update row(s) in a table

The "set" structure and "where" structure can also be arrayRef's of hashRefs. This allows you to perform multiple executions on a single prepared statement handle. This is shown in better detail in the examples

1. as a list of arguments

Sql::Simple->update(
  databaseHandle, 
  'scalar tablename', 
  [ { 'setColumn' => 'setValue' }, { 'setColumn' => 'setValue' } ],
  [ WHERE CLAUSE STRUCTURE (see above) ]
);

2. or as a hash

Sql::Simple->update(
  databaseHandle, 
  'table'  => $tablename, 
  'set'    => { 'setColumn' => 'setValue' }, 
  WHERE CLAUSE STRUCTURE (see above)
);

Examples

create table produce (
  price float,
  name varchar(20),
  color varchar(10)
)

# set the database handle for these transactions
Sql::Simple->setdbh($dbh);

# set green tomatoes to 75 cents
# update produce set price = ? where name = ? and color = ?
Sql::Simple->update('produce', { 'price' => .75 }, 
  { 'name' => 'tomatoe', 'color' => { 'green' });

# set olives to 1.35 and pickles to 1.50
# update produce set price = ? where name = ?
Sql::Simple->update('produce', 
  [ { 'price' => 1.35 },   { 'price' => 1.50 } ],
  [ { 'name' => 'olive' }, { 'name' => 'pickles' } ]
);

# if you have a reason (and I can't think of one) to execute 
# update multiple times with one set value. (a where "in" is advisable)
Sql::Simple->update('produce', { 'price' = .50 }, 
  [ { 'name' => 'lettuce' }, { 'name' => 'onions' } );

Sql::Simple->insert

provide an easy interface to insert row(s) into a table

I use this routine quite a bit, so I tried to keep it any superfluous features far away from creeping in. Since there are so many ways to pass things into this module, I'm just going to explain things in the examples.

Examples

  create table users (
    id int,
    name varchar(20),
  )

  create table visitors (
    id int,
    name varchar(20),
    specialty varchar(10)
  )

  # insert into users ( ?, ? ) 
  # Executed with: 1, 'john'
  Sql::Simple->insert($dbh, 'users', [ 'id', 'name' ], [ 1, 'john' ]);

  # insert into users ( ?, ? ) 
  # Executed with: 2, 'jack'
  # Executed with: 3, 'jim'
  Sql::Simple->insert($dbh, 'users', [ 'id', 'name' ], [
							 [ 2, 'jack' ],
							 [ 3, 'jim' ],
						       ]);
Or, by using a hash directly.

  # insert into users ( ?, ? ) 
  # Executed with: 1, 'john'
  Sql::Simple->insert($dbh, 'users', { 'id' => 1, 'name' => 'john' });

  # insert into users ( ?, ? ) 
  # Executed with: 2, 'jack'
  # Executed with: 3, 'jim'
  Sql::Simple->insert($dbh, 'users', [
				       { 'id' => 2, 'name' => 'jack' },
				       { 'id' => 3, 'name' => 'jim' },
				     ]);

Lastly, a hash, but using a subquery

# insert into users ( id, name ) 
# ( select id, name from visitors where specialty = ? )

# Executed with: 'basketweaving'
Sql::Simple->insert($dbh, 'users', 
  [ qw(id name) ], 
  { 
    'columns' => [ qw(id name) ], 
    'table' => 'visitors', 
    'where' => { 'specialty' => 'basketweaving' } 
  }
);

UPDATE 0.7: If you want to call a sql function, simply pass a scalar reference as a value for a column. (Example, to execute the "now()" function for a date..

Sql::Simple->insert( $dbh, 'myTable', { 'columnA' => \'valueB' } );

Sql::Simple->query

Retrieve information from a table

Method invocation description.

1. database handle (not required if "setdbh" used)
2. A datastructure depicting what columns you wish to query for. 
  The following formats are supported:
  A. scalar of column (or columns, as it will simply interpolate 
     into the SQL to be executed)
  B. arrayRef of column or columns (or computations, such as 2 + 2 
     as 'foo')
  C. hashRef of columns (with the appropriate alias's as their values
  D. hashRef of columns, with it's value an arrayRef 
     (the keys acting as a table prefix)

  Here's some examples of the "column" data structure

  'mycolumn'
  # OR
  [ qw(mycolumn mycolumn2 mycolumn3) ],
  # OR
  {
    'mycolumn' => 'mc',
    'mycolumn2' => 'm2',
    'mycolumn3' => 'm3'
  }
  # OR
  {
    'mytable' => [ qw(mycolumn mycolumn2 mycolumn3) ]
  }

3. A datastructure depicting what tables you wish to query against
  table => (scalar at the least is required)
    A. scalar of the table you wish to query from.
    B. hashRef of the relationships you are defining for this query.. 
       ie: table1.column1 => table2.column1 ...
    C. Array Reference of multiple tables

4. A data structure depicting constraints in the "where" clause 
   (see complete documentation above)

5. Options 
  order => (optional)
    A. a scalar value with a single column to order by
    B. an arrayRef of columns to order by, in the same alignment as given
  col =>
    A. a scalar value requesting that the result be handed as a complete 
       hash, courtesy of fetchall_hashref($col)

Examples: (again, tables first)

create table fruit (
  id int,
  name varchar(20),
  cost float,
  color varchar(20)
)

create table plant (
  species varchar(20),
  produces int, # foreign key to fruit
)

create table producer (
  title varchar(20),
  top_product int # foreign key to fruit
)
 
# set the dbh for these transactions
Sql::Simple->setdbh($dbh);

# select fruit_name, fruit_cost from fruit where fruit where fruit_color = ?
# Executed with: "red"
Sql::Simple->query(
  [ 'name', 'cost' ], 
  'fruit', 
  { 'color' => 'red' }
);

Simple table joins are fairly simple.

# select fruit_name, plant_name 
# from fruit inner join plant on fruit.fruit_id = plant.produces
Sql::Simple->query(
  [ 'name', 'species' ], 
  { 'fruit.id' => 'plant.produces' }
);

Complicated Table joins are only mildly more difficult (thanks to the standardization of ANSI-SQL 92)

  # select 
  #   name, species, title 
  # from 
  #   fruit 
  #   inner join plant on fruit_id = plant.produces 
  #   left outer join prodcuer on fruit.id = producer.top_product
  #     and producer.title ne 'Bad Fruit Company'
  Sql::Simple->query(
    [ 'name', 'species', 'title' ],
    [ 
      'fruit', 
      {
        'table' => 'plant'
	'on' => {
          'fruit.id' => 'plant.produces' 
        }
      },
      {
        'table' => 'producer',
	'join' => 'left outer',
	'on' => {
	  'fruit.id' => 'producer.top_product',
	  'producer.title' => {
	    'op' => 'ne',
	    'val' => 'Bad Fruit Company'
	  }
	}
      }
    ]
  );  

Ambiguity within table joins must be handled .. well, somewhat on your own. YMMV depending on your approach. This module doesn't have your schema, so it's hard to figure out relationships. (next version might support something wacky like this). If you do not use a table prefix in your join, Sql::Simple will interpret it as a bind variable. The only way to get around this is by using a scalar reference. (example below)

[
  'produce',
  {
    'table' => 'shelf_life',
    'on' => {
      'produce_id' => 'sl_produce_id'
    }
  }
]
# generates " FROM produce INNER JOIN shelf_life on produce_id = ? " 
# intead of...
#	      " FROM produce INNER JOIN shelf_life on produce_id = sl_produce_id "

Simple enough to get around..

[
  'produce',
  {
    'table' => 'shelf_life',
    'on' => {
      'produce_id' => \'sl_produce_id'
    }
  }
]
# generates " FROM produce INNER JOIN shelf_life on produce_id = sl_produce_id "

Return structure Format

UPDATE 0.7: You can specify the type of structure you want returned from a query. The default is an array of hashref's, keyed by the column names. (that you may have chosen with aliases). You can get an array of arrays by calling the static method "asarray" and passing it a true or a false value. If you want the result set executed with using "fetchall_hashref()" simply pass the column you want after the where clause. Eventually, I'll probably move these other fetch styles as static methods like "asarray".

state

The state function controls how the stateful subroutines act in a OO environment. As class based functions, you are returned the entire result set as one large data structure. With larger data sets, you may wish to iterate through them one at a time, to avoid massive memory consumption. If this is set to "true", then any direct query will save the statement handle to the object. You can call all of the various DBI routines from here, or simply call the other helper methods.

Here is a quick example of how you would deal with Sql::Simple through the OO interface.

my $obj = new Sql::Simple( DBI->connect('dbi:ODBC:instance', 'username', 'password') );
my $result = $s->query(qw(name density), 'minerals')->fetchall_arrayref();

my $sth = $obj->query(qw(color taste durability), 'fruit');
while ( my @row = $sth->fetchrow() ) {
  # do something...
}
$sth->finish();

BUGS:

I sure hope there are no bugs, feel free to drop me a line if you run into anything I need to be concerned with.

Acknowledgements:

The author of XML::Simple (use it all the time). Paul Lindner, Garth Webb, Kevin Moffatt, Chuck McLean, Intelligent Software Solutions (www.iswsolutions.com)

TODO:

1. Figure out a good way of handling prefix's for columns.. (ugh)
2A. store pre-computed sql and map (in object or possibly global via mod_perl or serialized in mldbm or whatever)
2B. Be able to pass in the precomputed information as arguments to functions.. (partially done, with the execute method)

See also:

DBI (manpage)
Sql::* (lots of similar modules to this)

Specifically, take a look at DBIx::Abstract and Sql::Abstract. I was rather astonished when I released this module today to find out there was another module that had such similar capabilities. Great minds must think alike ;-). After reviewing the modules, I can say that DBIx::Abstract and Sql::Simple have very little in common, but it does fill a niche that Sql::Simple does not. Sql::Abstract however, does have nearly identical syntax on a few of the method calls, and has support for some of the features that I tout in Sql::Simple. (I'm not apologizing for writing this module, I like what it has done for me,

I'm not going to write a bullet background paper iterating over every feature this module has and doesn't have in comparison but I will cover the major differences.

ANSI SQL 92 join support

This feature, combined with the fact that the "clause" for the join is directly tied to the same code that generates a where clause is probably the biggest difference. This feature is available in all aspects of Sql::Simple, not just the query method (as any sub query made in insert, update, or delete simply recursively call the query method to build it's data set).

Execution

Sql::Abstract right now is better suited for a web environment where you would want to write your own custom handlers to handle errors. Once an OO interface is added to Sql::Simple, that may be reason enough to switch. Right now, Sql::Simple is capable of returning the completed Sql statement back to the user, not really all that different from Sql::Abstract.. ie:

$Sql::Simple::RETURNSQL = 1;
my $sth = $dbh->prepare(Sql::Simple->query('id', 'fruit', { 'name' => 'apple' }));

Similar to.

my $sql = SQL::Abstract->new;
my ( $sth, @bind ) = $sql->select('fruit', ['id'], { 'name' => 'apple' });

Mass Execution

The main reason I wrote this module was to simplify the "I need to insert 10,000 records, but not use BCP, because I need it to hit the rules etc.". With that said, the ability to pass in an array ref of hash refs into the insert routine, is fairly nice (or an array ref of columns, and an arrayref of arrayrefs of values). Or be able to mass update quickly.

Summary

Umm, TMTOWTDI, or whatever. Use what suits you, the only real personal preference issue I have is that the variables are out of order in Sql::Abstract. I'd rather see it line up with an actual SQL query. IE: select COLUMNS from TABLE where CLAUSE, instead of TABLE, COLUMNS, WHERE

COPYRIGHT:

The Sql::Simple module is Copyright (c) 2004 Ryan Alan Dietrich. The Sql::Simple module is free software; you can redistribute it and/or modify it under the same terms as Perl itself with the exception that it cannot be placed on a CD-ROM or similar media for commercial distribution without the prior approval of the author.

AUTHOR:

Sql::Simple by Ryan Alan Dietrich <ryan@dietrich.net>

Contributions (thanks!) from the following.

Mark Stosberg, Miguel Manso, Tiago Almeida

cpanm Sql::Simple

perl -MCPAN -e shell
install Sql::Simple