Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SQL::Abstract::ClassicUser Contributed Perl DocumentaSQL::Abstract::Classic(3)

NAME
       SQL::Abstract::Classic -	Generate SQL from Perl data structures

SYNOPSIS
	   use SQL::Abstract::Classic;

	   my $sql = SQL::Abstract::Classic->new;

	   my($stmt, @bind) = $sql->select($source, \@fields, \%where, $order);

	   my($stmt, @bind) = $sql->insert($table, \%fieldvals || \@values);

	   my($stmt, @bind) = $sql->update($table, \%fieldvals,	\%where);

	   my($stmt, @bind) = $sql->delete($table, \%where);

	   # Then, use these in	your DBI statements
	   my $sth = $dbh->prepare($stmt);
	   $sth->execute(@bind);

	   # Just generate the WHERE clause
	   my($stmt, @bind) = $sql->where(\%where, $order);

	   # Return values in the same order, for hashed queries
	   # See PERFORMANCE section for more details
	   my @bind = $sql->values(\%fieldvals);

Low-impact fork	of SQL::Abstract v1.81 ( 2014-10-25 )
       This module is nearly identical to SQL::Abstract	v1.81
	<https://metacpan.org/release/RIBASUSHI/SQL-Abstract-1.81>. A recent
       flurry of activity on the original SQL::Abstract	namespace risks
       leaving downstream users	without	a way to opt out of impending
       developments.  Therefore	this module exists to preserve the ability of
       users to	opt into the new way of	doing things according to their	own
       schedules.

DESCRIPTION
       This module was inspired	by the excellent DBIx::Abstract.  However, in
       using that module I found that what I really wanted to do was generate
       SQL, but	still retain complete control over my statement	handles	and
       use the DBI interface. So, I set	out to create an abstract SQL
       generation module.

       While based on the concepts used	by DBIx::Abstract, there are several
       important differences, especially when it comes to WHERE	clauses. I
       have modified the concepts used to make the SQL easier to generate from
       Perl data structures and, IMO, more intuitive.  The underlying idea is
       for this	module to do what you mean, based on the data structures you
       provide it. The big advantage is	that you don't have to modify your
       code every time your data changes, as this module figures it out.

       To begin	with, an SQL INSERT is as easy as just specifying a hash of
       "key=value" pairs:

	   my %data = (
	       name => 'Jimbo Bobson',
	       phone =>	'123-456-7890',
	       address => '42 Sister Lane',
	       city => 'St. Louis',
	       state =>	'Louisiana',
	   );

       The SQL can then	be generated with this:

	   my($stmt, @bind) = $sql->insert('people', \%data);

       Which would give	you something like this:

	   $stmt = "INSERT INTO	people
			   (address, city, name, phone,	state)
			   VALUES (?, ?, ?, ?, ?)";
	   @bind = ('42	Sister Lane', 'St. Louis', 'Jimbo Bobson',
		    '123-456-7890', 'Louisiana');

       These are then used directly in your DBI	code:

	   my $sth = $dbh->prepare($stmt);
	   $sth->execute(@bind);

   Inserting and Updating Arrays
       If your database	has array types	(like for example Postgres), activate
       the special option "array_datatypes => 1" when creating the
       "SQL::Abstract::Classic"	object.	 Then you may use an arrayref to
       insert and update database array	types:

	   my $sql = SQL::Abstract::Classic->new(array_datatypes => 1);
	   my %data = (
	       planets => [qw/Mercury Venus Earth Mars/]
	   );

	   my($stmt, @bind) = $sql->insert('solar_system', \%data);

       This results in:

	   $stmt = "INSERT INTO	solar_system (planets) VALUES (?)"

	   @bind = (['Mercury',	'Venus', 'Earth', 'Mars']);

   Inserting and Updating SQL
       In order	to apply SQL functions to elements of your %data you may
       specify a reference to an arrayref for the given	hash value. For
       example,	if you need to execute the Oracle "to_date" function on	a
       value, you can say something like this:

	   my %data = (
	       name => 'Bill',
	       date_entered => \[ "to_date(?,'MM/DD/YYYY')", "03/02/2003" ],
	   );

       The first value in the array is the actual SQL. Any other values	are
       optional	and would be included in the bind values array.	This gives
       you:

	   my($stmt, @bind) = $sql->insert('people', \%data);

	   $stmt = "INSERT INTO	people (name, date_entered)
		       VALUES (?, to_date(?,'MM/DD/YYYY'))";
	   @bind = ('Bill', '03/02/2003');

       An UPDATE is just as easy, all you change is the	name of	the function:

	   my($stmt, @bind) = $sql->update('people', \%data);

       Notice that your	%data isn't touched; the module	will generate the
       appropriately quirky SQL	for you	automatically. Usually you'll want to
       specify a WHERE clause for your UPDATE, though, which is	where handling
       %where hashes comes in handy...

   Complex where statements
       This module can generate	pretty complicated WHERE statements easily.
       For example, simple "key=value" pairs are taken to mean equality, and
       if you want to see if a field is	within a set of	values,	you can	use an
       arrayref. Let's say we wanted to	SELECT some data based on this
       criteria:

	   my %where = (
	      requestor	=> 'inna',
	      worker =>	['nwiger', 'rcwe', 'sfz'],
	      status =>	{ '!=',	'completed' }
	   );

	   my($stmt, @bind) = $sql->select('tickets', '*', \%where);

       The above would give you	something like this:

	   $stmt = "SELECT * FROM tickets WHERE
		       ( requestor = ? ) AND ( status != ? )
		       AND ( worker = ?	OR worker = ? OR worker	= ? )";
	   @bind = ('inna', 'completed', 'nwiger', 'rcwe', 'sfz');

       Which you could then use	in DBI code like so:

	   my $sth = $dbh->prepare($stmt);
	   $sth->execute(@bind);

       Easy, eh?

METHODS
       The methods are simple. There's one for every major SQL operation, and
       a constructor you use first. The	arguments are specified	in a similar
       order for each method (table, then fields, then a where clause) to try
       and simplify things.

   new(option => 'value')
       The "new()" function takes a list of options and	values,	and returns a
       new SQL::Abstract::Classic object which can then	be used	to generate
       SQL through the methods below. The options accepted are:

       case
	   If set to 'lower', then SQL will be generated in all	lowercase. By
	   default SQL is generated in "textbook" case meaning something like:

	       SELECT a_field FROM a_table WHERE some_field LIKE '%someval%'

	   Any setting other than 'lower' is ignored.

       cmp This	determines what	the default comparison operator	is. By default
	   it is "=", meaning that a hash like this:

	       %where =	(name => 'nwiger', email => 'nate@wiger.org');

	   Will	generate SQL like this:

	       WHERE name = 'nwiger' AND email = 'nate@wiger.org'

	   However, you	may want loose comparisons by default, so if you set
	   "cmp" to "like" you would get SQL such as:

	       WHERE name like 'nwiger'	AND email like 'nate@wiger.org'

	   You can also	override the comparison	on an individual basis - see
	   the huge section on "WHERE CLAUSES" at the bottom.

       sqltrue,	sqlfalse
	   Expressions for inserting boolean values within SQL statements.  By
	   default these are "1=1" and "1=0". They are used by the special
	   operators "-in" and "-not_in" for generating	correct	SQL even when
	   the argument	is an empty array (see below).

       logic
	   This	determines the default logical operator	for multiple WHERE
	   statements in arrays	or hashes. If absent, the default logic	is
	   "or"	for arrays, and	"and" for hashes. This means that a WHERE
	   array of the	form:

	       @where =	(
		   event_date => {'>=',	'2/13/99'},
		   event_date => {'<=',	'4/24/03'},
	       );

	   will	generate SQL like this:

	       WHERE event_date	>= '2/13/99' OR	event_date <= '4/24/03'

	   This	is probably not	what you want given this query,	though (look
	   at the dates). To change the	"OR" to	an "AND", simply specify:

	       my $sql = SQL::Abstract::Classic->new(logic => 'and');

	   Which will change the above "WHERE" to:

	       WHERE event_date	>= '2/13/99' AND event_date <= '4/24/03'

	   The logic can also be changed locally by inserting a	modifier in
	   front of an arrayref:

	       @where =	(-and => [event_date =>	{'>=', '2/13/99'},
				  event_date =>	{'<=', '4/24/03'} ]);

	   See the "WHERE CLAUSES" section for explanations.

       convert
	   This	will automatically convert comparisons using the specified SQL
	   function for	both column and	value. This is mostly used with	an
	   argument of "upper" or "lower", so that the SQL will	have the
	   effect of case-insensitive "searches". For example, this:

	       $sql = SQL::Abstract::Classic->new(convert => 'upper');
	       %where =	(keywords => 'MaKe iT CAse inSeNSItive');

	   Will	turn out the following SQL:

	       WHERE upper(keywords) like upper('MaKe iT CAse inSeNSItive')

	   The conversion can be "upper()", "lower()", or any other SQL
	   function that can be	applied	symmetrically to fields	(actually
	   SQL::Abstract::Classic does not validate this option; it will just
	   pass	through	what you specify verbatim).

       bindtype
	   This	is a kludge because many databases suck. For example, you
	   can't just bind values using	DBI's "execute()" for Oracle "CLOB" or
	   "BLOB" fields.  Instead, you	have to	use "bind_param()":

	       $sth->bind_param(1, 'reg	data');
	       $sth->bind_param(2, $lots, {ora_type => ORA_CLOB});

	   The problem is, SQL::Abstract::Classic will normally	just return a
	   @bind array,	which loses track of which field each slot refers to.
	   Fear	not.

	   If you specify "bindtype" in	new, you can determine how @bind is
	   returned.  Currently, you can specify either	"normal" (default) or
	   "columns". If you specify "columns",	you will get an	array that
	   looks like this:

	       my $sql = SQL::Abstract::Classic->new(bindtype => 'columns');
	       my($stmt, @bind)	= $sql->insert(...);

	       @bind = (
		   [ 'column1',	'value1' ],
		   [ 'column2',	'value2' ],
		   [ 'column3',	'value3' ],
	       );

	   You can then	iterate	through	this manually, using DBI's
	   "bind_param()".

	       $sth->prepare($stmt);
	       my $i = 1;
	       for (@bind) {
		   my($col, $data) = @$_;
		   if ($col eq 'details' || $col eq 'comments')	{
		       $sth->bind_param($i, $data, {ora_type =>	ORA_CLOB});
		   } elsif ($col eq 'image') {
		       $sth->bind_param($i, $data, {ora_type =>	ORA_BLOB});
		   } else {
		       $sth->bind_param($i, $data);
		   }
		   $i++;
	       }
	       $sth->execute;	   # execute without @bind now

	   Now,	why would you still use	SQL::Abstract::Classic if you have to
	   do this crap?  Basically, the advantage is still that you don't
	   have	to care	which fields are or are	not included. You could	wrap
	   that	above "for" loop in a simple sub called	"bind_fields()"	or
	   something and reuse it repeatedly. You still	get a layer of
	   abstraction over manual SQL specification.

	   Note	that if	you set	"bindtype" to "columns", the "\[ $sql, @bind
	   ]" construct	(see "Literal SQL with placeholders and	bind values
	   (subqueries)") will expect the bind values in this format.

       quote_char
	   This	is the character that a	table or column	name will be quoted
	   with.  By default this is an	empty string, but you could set	it to
	   the character "`", to generate SQL like this:

	     SELECT `a_field` FROM `a_table` WHERE `some_field`	LIKE '%someval%'

	   Alternatively, you can supply an array ref of two items, the	first
	   being the left hand quote character,	and the	second the right hand
	   quote character. For	example, you could supply "['[',']']" for SQL
	   Server 2000 compliant quotes	that generates SQL like	this:

	     SELECT [a_field] FROM [a_table] WHERE [some_field]	LIKE '%someval%'

	   Quoting is useful if	you have tables	or columns names that are
	   reserved words in your database's SQL dialect.

       escape_char
	   This	is the character that will be used to escape "quote_char"s
	   appearing in	an identifier before it	has been quoted.

	   The parameter default in case of a single "quote_char" character is
	   the quote character itself.

	   When	opening-closing-style quoting is used ("quote_char" is an
	   arrayref) this parameter defaults to	the closing (right)
	   "quote_char". Occurrences of	the opening (left) "quote_char"	within
	   the identifier are currently	left untouched.	The default for
	   opening-closing-style quotes	may change in future versions, thus
	   you are strongly encouraged to specify the escape character
	   explicitly.

       name_sep
	   This	is the character that separates	a table	and column name.  It
	   is necessary	to specify this	when the "quote_char" option is
	   selected, so	that tables and	column names can be individually
	   quoted like this:

	     SELECT `table`.`one_field`	FROM `table` WHERE `table`.`other_field` = 1

       injection_guard
	   A regular expression	"qr/.../" that is applied to any "-function"
	   and unquoted	column name specified in a query structure. This is a
	   safety mechanism to avoid injection attacks when mishandling	user
	   input e.g.:

	     my	%condition_as_column_value_pairs = get_values_from_user();
	     $sqla->select( ...	, \%condition_as_column_value_pairs );

	   If the expression matches an	exception is thrown. Note that literal
	   SQL supplied	via "\'...'" or	"\['...']" is not checked in any way.

	   Defaults to checking	for ";"	and the	"GO" keyword (TransactSQL)

       array_datatypes
	   When	this option is true, arrayrefs in INSERT or UPDATE are
	   interpreted as array	datatypes and are passed directly to the DBI
	   layer.  When	this option is false, arrayrefs	are interpreted	as
	   literal SQL,	just like refs to arrayrefs (but this behavior is for
	   backwards compatibility; when writing new queries, use the
	   "reference to arrayref" syntax for literal SQL).

       special_ops
	   Takes a reference to	a list of "special operators" to extend	the
	   syntax understood by	SQL::Abstract::Classic.	 See section "SPECIAL
	   OPERATORS" for details.

       unary_ops
	   Takes a reference to	a list of "unary operators" to extend the
	   syntax understood by	SQL::Abstract::Classic.	 See section "UNARY
	   OPERATORS" for details.

   insert($table, \@values || \%fieldvals, \%options)
       This is the simplest function. You simply give it a table name and
       either an arrayref of values or hashref of field/value pairs.  It
       returns an SQL INSERT statement and a list of bind values.  See the
       sections	on "Inserting and Updating Arrays" and "Inserting and Updating
       SQL" for	information on how to insert with those	data types.

       The optional "\%options"	hash reference may contain additional options
       to generate the insert SQL. Currently supported options are:

       returning
	   Takes either	a scalar of raw	SQL fields, or an array	reference of
	   field names,	and adds on an SQL "RETURNING" statement at the	end.
	   This	allows you to return data generated by the insert statement
	   (such as row	IDs) without performing	another	"SELECT" statement.
	   Note, however, this is not part of the SQL standard and may not be
	   supported by	all database engines.

   update($table, \%fieldvals, \%where)
       This takes a table, hashref of field/value pairs, and an	optional
       hashref WHERE clause. It	returns	an SQL UPDATE function and a list of
       bind values.  See the sections on "Inserting and	Updating Arrays" and
       "Inserting and Updating SQL" for	information on how to insert with
       those data types.

   select($source, $fields, $where, $order)
       This returns a SQL SELECT statement and associated list of bind values,
       as specified by the arguments:

       $source
	   Specification of the	'FROM' part of the statement.  The argument
	   can be either a plain scalar	(interpreted as	a table	name, will be
	   quoted), or an arrayref (interpreted	as a list of table names,
	   joined by commas, quoted), or a scalarref (literal SQL, not
	   quoted).

       $fields
	   Specification of the	list of	fields to retrieve from	the source.
	   The argument	can be either an arrayref (interpreted as a list of
	   field names,	will be	joined by commas and quoted), or a plain
	   scalar (literal SQL,	not quoted).  Please observe that this API is
	   not as flexible as that of the first	argument $source, for
	   backwards compatibility reasons.

       $where
	   Optional argument to	specify	the WHERE part of the query.  The
	   argument is most often a hashref, but can also be an	arrayref or
	   plain scalar	-- see section WHERE clause for	details.

       $order
	   Optional argument to	specify	the ORDER BY part of the query.	 The
	   argument can	be a scalar, a hashref or an arrayref -- see section
	   ORDER BY clause for details.

   delete($table, \%where)
       This takes a table name and optional hashref WHERE clause.  It returns
       an SQL DELETE statement and list	of bind	values.

   where(\%where, $order)
       This is used to generate	just the WHERE clause. For example, if you
       have an arbitrary data structure	and know what the rest of your SQL is
       going to	look like, but want an easy way	to produce a WHERE clause, use
       this. It	returns	an SQL WHERE clause and	list of	bind values.

   values(\%data)
       This just returns the values from the hash %data, in the	same order
       that would be returned from any of the other above queries.  Using this
       allows you to markedly speed up your queries if you are affecting lots
       of rows.	See below under	the "PERFORMANCE" section.

   generate($any, 'number', $of, \@data, $struct, \%types)
       Warning:	This is	an experimental	method and subject to change.

       This returns arbitrarily	generated SQL. It's a really basic shortcut.
       It will return two different things, depending on return	context:

	   my($stmt, @bind) = $sql->generate('create table', \$table, \@fields);
	   my $stmt_and_val = $sql->generate('create table', \$table, \@fields);

       These would return the following:

	   # First calling form
	   $stmt = "CREATE TABLE test (?, ?)";
	   @bind = (field1, field2);

	   # Second calling form
	   $stmt_and_val = "CREATE TABLE test (field1, field2)";

       Depending on what you're	trying to do, it's up to you to	choose the
       correct format. In this example,	the second form	is what	you would
       want.

       By the same token:

	   $sql->generate('alter session', { nls_date_format =>	'MM/YY'	});

       Might give you:

	   ALTER SESSION SET nls_date_format = 'MM/YY'

       You get the idea. Strings get their case	twiddled, but everything else
       remains verbatim.

WHERE CLAUSES
   Introduction
       This module uses	a variation on the idea	from DBIx::Abstract. It	is
       NOT, repeat not 100% compatible.	The main logic of this module is that
       things in arrays	are OR'ed, and things in hashes	are AND'ed.

       The easiest way to explain is to	show lots of examples. After each
       %where hash shown, it is	assumed	you used:

	   my($stmt, @bind) = $sql->where(\%where);

       However,	note that the %where hash can be used directly in any of the
       other functions as well,	as described above.

   Key-value pairs
       So, let's get started. To begin,	a simple hash:

	   my %where  =	(
	       user   => 'nwiger',
	       status => 'completed'
	   );

       Is converted to SQL "key	= val" statements:

	   $stmt = "WHERE user = ? AND status =	?";
	   @bind = ('nwiger', 'completed');

       One common thing	I end up doing is having a list	of values that a field
       can be in. To do	this, simply specify a list inside of an arrayref:

	   my %where  =	(
	       user   => 'nwiger',
	       status => ['assigned', 'in-progress', 'pending'];
	   );

       This simple code	will create the	following:

	   $stmt = "WHERE user = ? AND ( status	= ? OR status =	? OR status = ?	)";
	   @bind = ('nwiger', 'assigned', 'in-progress', 'pending');

       A field associated to an	empty arrayref will be considered a logical
       false and will generate 0=1.

   Tests for NULL values
       If the value part is "undef" then this is converted to SQL <IS NULL>

	   my %where  =	(
	       user   => 'nwiger',
	       status => undef,
	   );

       becomes:

	   $stmt = "WHERE user = ? AND status IS NULL";
	   @bind = ('nwiger');

       To test if a column IS NOT NULL:

	   my %where  =	(
	       user   => 'nwiger',
	       status => { '!=', undef },
	   );

   Specific comparison operators
       If you want to specify a	different type of operator for your
       comparison, you can use a hashref for a given column:

	   my %where  =	(
	       user   => 'nwiger',
	       status => { '!=', 'completed' }
	   );

       Which would generate:

	   $stmt = "WHERE user = ? AND status != ?";
	   @bind = ('nwiger', 'completed');

       To test against multiple	values,	just enclose the values	in an
       arrayref:

	   status => { '=', ['assigned', 'in-progress',	'pending'] };

       Which would give	you:

	   "WHERE status = ? OR	status = ? OR status = ?"

       The hashref can also contain multiple pairs, in which case it is
       expanded	into an	"AND" of its elements:

	   my %where  =	(
	       user   => 'nwiger',
	       status => { '!=', 'completed', -not_like	=> 'pending%' }
	   );

	   # Or	more dynamically, like from a form
	   $where{user}	= 'nwiger';
	   $where{status}{'!='}	= 'completed';
	   $where{status}{'-not_like'} = 'pending%';

	   # Both generate this
	   $stmt = "WHERE user = ? AND status != ? AND status NOT LIKE ?";
	   @bind = ('nwiger', 'completed', 'pending%');

       To get an OR instead, you can combine it	with the arrayref idea:

	   my %where =>	(
		user =>	'nwiger',
		priority => [ {	'=', 2 }, { '>', 5 } ]
	   );

       Which would generate:

	   $stmt = "WHERE ( priority = ? OR priority > ? ) AND user = ?";
	   @bind = ('2', '5', 'nwiger');

       If you want to include literal SQL (with	or without bind	values), just
       use a scalar reference or reference to an arrayref as the value:

	   my %where  =	(
	       date_entered => { '>' =>	\["to_date(?, 'MM/DD/YYYY')", "11/26/2008"] },
	       date_expires => { '<' =>	\"now()" }
	   );

       Which would generate:

	   $stmt = "WHERE date_entered > to_date(?, 'MM/DD/YYYY') AND date_expires < now()";
	   @bind = ('11/26/2008');

   Logic and nesting operators
       In the example above, there is a	subtle trap if you want	to say
       something like this (notice the "AND"):

	   WHERE priority != ? AND priority != ?

       Because,	in Perl	you can't do this:

	   priority => { '!=' => 2, '!=' => 1 }

       As the second "!=" key will obliterate the first. The solution is to
       use the special "-modifier" form	inside an arrayref:

	   priority => [ -and => {'!=',	2},
				 {'!=',	1} ]

       Normally, these would be	joined by "OR",	but the	modifier tells it to
       use "AND" instead. (Hint: You can use this in conjunction with the
       "logic" option to "new()" in order to change the	way your queries work
       by default.) Important: Note that the "-modifier" goes INSIDE the
       arrayref, as an extra first element. This will NOT do what you think it
       might:

	   priority => -and => [{'!=', 2}, {'!=', 1}]	# WRONG!

       Here is a quick list of equivalencies, since there is some overlap:

	   # Same
	   status => {'!=', 'completed', 'not like', 'pending%'	}
	   status => [ -and => {'!=', 'completed'}, {'not like', 'pending%'}]

	   # Same
	   status => {'=', ['assigned',	'in-progress']}
	   status => [ -or => {'=', 'assigned'}, {'=', 'in-progress'}]
	   status => [ {'=', 'assigned'}, {'=',	'in-progress'} ]

   Special operators: IN, BETWEEN, etc.
       You can also use	the hashref format to compare a	list of	fields using
       the "IN"	comparison operator, by	specifying the list as an arrayref:

	   my %where  =	(
	       status	=> 'completed',
	       reportid	=> { -in => [567, 2335,	2] }
	   );

       Which would generate:

	   $stmt = "WHERE status = ? AND reportid IN (?,?,?)";
	   @bind = ('completed', '567',	'2335',	'2');

       The reverse operator "-not_in" generates	SQL "NOT IN" and is used in
       the same	way.

       If the argument to "-in"	is an empty array, 'sqlfalse' is generated (by
       default:	"1=0").	Similarly, "-not_in => []" generates 'sqltrue' (by
       default:	"1=1").

       In addition to the array	you can	supply a chunk of literal sql or
       literal sql with	bind:

	   my %where = {
	     customer => { -in => \[
	       'SELECT cust_id FROM cust WHERE balance > ?',
	       2000,
	     ],
	     status => { -in =>	\'SELECT status_codes FROM states' },
	   };

       would generate:

	   $stmt = "WHERE (
		 customer IN ( SELECT cust_id FROM cust	WHERE balance >	? )
	     AND status	IN ( SELECT status_codes FROM states )
	   )";
	   @bind = ('2000');

       Finally,	if the argument	to "-in" is not	a reference, it	will be
       treated as a single-element array.

       Another pair of operators is "-between" and "-not_between", used	with
       an arrayref of two values:

	   my %where  =	(
	       user   => 'nwiger',
	       completion_date => {
		  -not_between => ['2002-10-01', '2003-02-06']
	       }
	   );

       Would give you:

	   WHERE user =	? AND completion_date NOT BETWEEN ( ? AND ? )

       Just like with "-in" all	plausible combinations of literal SQL are
       possible:

	   my %where = {
	     start0 => { -between => [ 1, 2 ] },
	     start1 => { -between => \["? AND ?", 1, 2]	},
	     start2 => { -between => \"lower(x)	AND upper(y)" },
	     start3 => { -between => [
	       \"lower(x)",
	       \["upper(?)", 'stuff' ],
	     ] },
	   };

       Would give you:

	   $stmt = "WHERE (
		 ( start0 BETWEEN ? AND	?		 )
	     AND ( start1 BETWEEN ? AND	?		 )
	     AND ( start2 BETWEEN lower(x) AND upper(y)	 )
	     AND ( start3 BETWEEN lower(x) AND upper(?)	 )
	   )";
	   @bind = (1, 2, 1, 2,	'stuff');

       These are the two builtin "special operators"; but the list can be
       expanded: see section "SPECIAL OPERATORS" below.

   Unary operators: bool
       If you wish to test against boolean columns or functions	within your
       database	you can	use the	"-bool"	and "-not_bool"	operators. For example
       to test the column "is_user" being true and the column "is_enabled"
       being false you would use:-

	   my %where  =	(
	       -bool	   => 'is_user',
	       -not_bool   => 'is_enabled',
	   );

       Would give you:

	   WHERE is_user AND NOT is_enabled

       If a more complex combination is	required, testing more conditions,
       then you	should use the and/or operators:-

	   my %where  =	(
	       -and	      => [
		   -bool      => 'one',
		   -not_bool  => { two=> { -rlike => 'bar' } },
		   -not_bool  => { three => [ {	'=', 2 }, { '>', 5 } ] },
	       ],
	   );

       Would give you:

	   WHERE
	     one
	       AND
	     (NOT two RLIKE ?)
	       AND
	     (NOT ( three = ? OR three > ? ))

   Nested conditions, -and/-or prefixes
       So far, we've seen how multiple conditions are joined with a top-level
       "AND".  We can change this by putting the different conditions we want
       in hashes and then putting those	hashes in an array. For	example:

	   my @where = (
	       {
		   user	  => 'nwiger',
		   status => { -like =>	['pending%', 'dispatched'] },
	       },
	       {
		   user	  => 'robot',
		   status => 'unassigned',
	       }
	   );

       This data structure would create	the following:

	   $stmt = "WHERE ( user = ? AND ( status LIKE ? OR status LIKE	? ) )
		       OR ( user = ? AND status	= ? ) )";
	   @bind = ('nwiger', 'pending', 'dispatched', 'robot',	'unassigned');

       Clauses in hashrefs or arrayrefs	can be prefixed	with an	"-and" or
       "-or" to	change the logic inside:

	   my @where = (
		-and =>	[
		   user	=> 'nwiger',
		   [
		       -and => [ workhrs => {'>', 20}, geo => 'ASIA' ],
		       -or => {	workhrs	=> {'<', 50}, geo => 'EURO' },
		   ],
	       ],
	   );

       That would yield:

	   $stmt = "WHERE ( user = ?
		      AND ( ( workhrs >	? AND geo = ? )
			 OR ( workhrs <	? OR geo = ? ) ) )";
	   @bind = ('nwiger', '20', 'ASIA', '50', 'EURO');

       Algebraic inconsistency,	for historical reasons

       "Important note": when connecting several conditions, the "-and-"|"-or"
       operator	goes "outside" of the nested structure;	whereas	when
       connecting several constraints on one column, the "-and"	operator goes
       "inside"	the arrayref. Here is an example combining both	features:

	  my @where = (
	    -and => [a => 1, b => 2],
	    -or	 => [c => 3, d => 4],
	     e	 => [-and => {-like => 'foo%'},	{-like => '%bar'} ]
	  )

       yielding

	 WHERE ( (    (	a = ? AND b = ?	)
		   OR (	c = ? OR d = ? )
		   OR (	e LIKE ? AND e LIKE ? )	) )

       This difference in syntax is unfortunate	but must be preserved for
       historical reasons. So be careful: the two examples below would seem
       algebraically equivalent, but they are not

	 { col => [ -and =>
	   { -like => 'foo%' },
	   { -like => '%bar' },
	 ] }
	 # yields: WHERE ( ( col LIKE ?	AND col	LIKE ? ) )

	 [ -and	=>
	   { col => { -like => 'foo%' }	},
	   { col => { -like => '%bar' }	},
	 ]
	 # yields: WHERE ( ( col LIKE ?	OR col LIKE ? )	)

   Literal SQL and value type operators
       The basic premise of SQL::Abstract::Classic is that in WHERE
       specifications the "left	side" is a column name and the "right side" is
       a value (normally rendered as a placeholder). This holds	true for both
       hashrefs	and arrayref pairs as you see in the "WHERE CLAUSES" examples
       above. Sometimes	it is necessary	to alter this behavior.	There are
       several ways of doing so.

       -ident

       This is a virtual operator that signals the string to its right side is
       an identifier (a	column name) and not a value. For example to compare
       two columns you would write:

	   my %where = (
	       priority	=> { '<', 2 },
	       requestor => { -ident =>	'submitter' },
	   );

       which creates:

	   $stmt = "WHERE priority < ? AND requestor = submitter";
	   @bind = ('2');

       If you are maintaining legacy code you may see a	different construct as
       described in "Deprecated	usage of Literal SQL", please use "-ident" in
       new code.

       -value

       This is a virtual operator that signals that the	construct to its right
       side is a value to be passed to DBI. This is for	example	necessary when
       you want	to write a where clause	against	an array (for RDBMS that
       support such datatypes).	For example:

	   my %where = (
	       array =>	{ -value => [1,	2, 3] }
	   );

       will result in:

	   $stmt = 'WHERE array	= ?';
	   @bind = ([1,	2, 3]);

       Note that if you	were to	simply say:

	   my %where = (
	       array =>	[1, 2, 3]
	   );

       the result would	probably not be	what you wanted:

	   $stmt = 'WHERE array	= ? OR array = ? OR array = ?';
	   @bind = (1, 2, 3);

       Literal SQL

       Finally,	sometimes only literal SQL will	do. To include a random
       snippet of SQL verbatim,	you specify it as a scalar reference. Consider
       this only as a last resort. Usually there is a better way. For example:

	   my %where = (
	       priority	=> { '<', 2 },
	       requestor => { -in => \'(SELECT name FROM hitmen)' },
	   );

       Would create:

	   $stmt = "WHERE priority < ? AND requestor IN	(SELECT	name FROM hitmen)"
	   @bind = (2);

       Note that in this example, you only get one bind	parameter back,	since
       the verbatim SQL	is passed as part of the statement.

       CAVEAT

	 Never use untrusted input as a	literal	SQL argument - this is a massive
	 security risk (there is no way	to check literal snippets for SQL
	 injections and	other nastyness). If you need to deal with untrusted input
	 use literal SQL with placeholders as described	next.

       Literal SQL with	placeholders and bind values (subqueries)

       If the literal SQL to be	inserted has placeholders and bind values, use
       a reference to an arrayref (yes this is a double	reference -- not so
       common, but perfectly legal Perl). For example, to find a date in
       Postgres	you can	use something like this:

	   my %where = (
	      date_column => \[	"= date	'2008-09-30' - ?::integer", 10 ]
	   )

       This would create:

	   $stmt = "WHERE ( date_column	= date '2008-09-30' - ?::integer )"
	   @bind = ('10');

       Note that you must pass the bind	values in the same format as they are
       returned	by where. This means that if you set "bindtype"	to "columns",
       you must	provide	the bind values	in the "[ column_meta => value ]"
       format, where "column_meta" is an opaque	scalar value; most commonly
       the column name,	but you	can use	any scalar value (including references
       and blessed references),	SQL::Abstract::Classic will simply pass	it
       through intact. So if "bindtype"	is set to "columns" the	above example
       will look like:

	   my %where = (
	      date_column => \[	"= date	'2008-09-30' - ?::integer", [ {} => 10 ] ]
	   )

       Literal SQL is especially useful	for nesting parenthesized clauses in
       the main	SQL query. Here	is a first example:

	 my ($sub_stmt,	@sub_bind) = ("SELECT c1 FROM t1 WHERE c2 < ? AND c3 LIKE ?",
				      100, "foo%");
	 my %where = (
	   foo => 1234,
	   bar => \["IN	($sub_stmt)" =>	@sub_bind],
	 );

       This yields:

	 $stmt = "WHERE	(foo = ? AND bar IN (SELECT c1 FROM t1
						    WHERE c2 < ? AND c3	LIKE ?))";
	 @bind = (1234,	100, "foo%");

       Other subquery operators, like for example "> ALL" or "NOT IN", are
       expressed in the	same way. Of course the	$sub_stmt and its associated
       bind values can be generated through a former call to "select()"	:

	 my ($sub_stmt,	@sub_bind)
	    = $sql->select("t1", "c1", {c2 => {"<" => 100},
					c3 => {-like =>	"foo%"}});
	 my %where = (
	   foo => 1234,
	   bar => \["> ALL ($sub_stmt)"	=> @sub_bind],
	 );

       In the examples above, the subquery was used as an operator on a
       column; but the same principle also applies for a clause	within the
       main %where hash, like an EXISTS	subquery:

	 my ($sub_stmt,	@sub_bind)
	    = $sql->select("t1", "*", {c1 => 1,	c2 => \"> t0.c0"});
	 my %where = ( -and => [
	   foo	 => 1234,
	   \["EXISTS ($sub_stmt)" => @sub_bind],
	 ]);

       which yields

	 $stmt = "WHERE	(foo = ? AND EXISTS (SELECT * FROM t1
					       WHERE c1	= ? AND	c2 > t0.c0))";
	 @bind = (1234,	1);

       Observe that the	condition on "c2" in the subquery refers to column
       "t0.c0" of the main query: this is not a	bind value, so we have to
       express it through a scalar ref.	 Writing "c2 =>	{">" =>	"t0.c0"}"
       would have generated "c2	> ?" with bind value "t0.c0" ... not exactly
       what we wanted here.

       Finally,	here is	an example where a subquery is used for	expressing
       unary negation:

	 my ($sub_stmt,	@sub_bind)
	    = $sql->where({age => [{"<"	=> 10},	{">" =>	20}]});
	 $sub_stmt =~ s/^ where	//i; # don't want "WHERE" in the subclause
	 my %where = (
	       lname  => {like => '%son%'},
	       \["NOT ($sub_stmt)" => @sub_bind],
	   );

       This yields

	 $stmt = "lname	LIKE ? AND NOT ( age < ? OR age	> ? )"
	 @bind = ('%son%', 10, 20)

       Deprecated usage	of Literal SQL

       Below are some examples of archaic use of literal SQL. It is shown only
       as reference for	those who deal with legacy code. Each example has a
       much better, cleaner and	safer alternative that users should opt	for in
       new code.

       o

	       my %where = ( requestor => \'IS NOT NULL' )

	       $stmt = "WHERE requestor	IS NOT NULL"

	   This	used to	be the way of generating NULL comparisons, before the
	   handling of "undef" got formalized. For new code please use the
	   superior syntax as described	in "Tests for NULL values".

       o

	       my %where = ( requestor => \'= submitter' )

	       $stmt = "WHERE requestor	= submitter"

	   This	used to	be the only way	to compare columns. Use	the superior
	   "-ident" method for all new code. For example an identifier
	   declared in such a way will be properly quoted if "quote_char" is
	   properly set, while the legacy form will remain as supplied.

       o

	       my %where = ( is_ready  => \"", completed => { '>', '2012-12-21'	} )

	       $stmt = "WHERE completed	> ? AND	is_ready"
	       @bind = ('2012-12-21')

	   Using an empty string literal used to be the	only way to express a
	   boolean.  For all new code please use the much more readable	-bool
	   operator.

   Conclusion
       These pages could go on for a while, since the nesting of the data
       structures this module can handle are pretty much unlimited (the	module
       implements the "WHERE" expansion	as a recursive function	internally).
       Your best bet is	to "play around" with the module a little to see how
       the data	structures behave, and choose the best format for your data
       based on	that.

       And of course, all the values above will	probably be replaced with
       variables gotten	from forms or the command line.	After all, if you knew
       everything ahead	of time, you wouldn't have to worry about dynamically-
       generating SQL and could	just hardwire it into your script.

ORDER BY CLAUSES
       Some functions take an order by clause. This can	either be a scalar
       (just a column name), a hashref of "{ -desc => 'col' }" or "{ -asc =>
       'col' }", a scalarref, an arrayref-ref, or an arrayref of any of	the
       previous	forms. Examples:

		      Given		 |	   Will	Generate
	   ---------------------------------------------------------------
					 |
	   'colA'			 | ORDER BY colA
					 |
	   [qw/colA colB/]		 | ORDER BY colA, colB
					 |
	   {-asc  => 'colA'}		 | ORDER BY colA ASC
					 |
	   {-desc => 'colB'}		 | ORDER BY colB DESC
					 |
	   ['colA', {-asc => 'colB'}]	 | ORDER BY colA, colB ASC
					 |
	   { -asc => [qw/colA colB/] }	 | ORDER BY colA ASC, colB ASC
					 |
	   \'colA DESC'			 | ORDER BY colA DESC
					 |
	   \[ 'FUNC(colA, ?)', $x ]	 | ORDER BY FUNC(colA, ?)
					 |   /*	...with	$x bound to ? */
					 |
	   [				 | ORDER BY
	     { -asc => 'colA' },	 |     colA ASC,
	     { -desc =>	[qw/colB/] },	 |     colB DESC,
	     { -asc => [qw/colC	colD/] },|     colC ASC, colD ASC,
	     \'colE DESC',		 |     colE DESC,
	     \[	'FUNC(colF, ?)', $x ],	 |     FUNC(colF, ?)
	   ]				 |   /*	...with	$x bound to ? */
	   ===============================================================

SPECIAL	OPERATORS
	 my $sqlmaker =	SQL::Abstract::Classic->new(special_ops	=> [
	    {
	     regex => qr/.../,
	     handler =>	sub {
	       my ($self, $field, $op, $arg) = @_;
	       ...
	     },
	    },
	    {
	     regex => qr/.../,
	     handler =>	'method_name',
	    },
	  ]);

       A "special operator" is a SQL syntactic clause that can be applied to a
       field, instead of a usual binary	operator.  For example:

	  WHERE	field IN (?, ?,	?)
	  WHERE	field BETWEEN ?	AND ?
	  WHERE	MATCH(field) AGAINST (?, ?)

       Special operators IN and	BETWEEN	are fairly standard and	therefore are
       builtin within "SQL::Abstract::Classic" (as the overridable methods
       "_where_field_IN" and "_where_field_BETWEEN"). For other	operators,
       like the	MATCH .. AGAINST example above which is	specific to MySQL, you
       can write your own operator handlers - supply a "special_ops" argument
       to the "new" method. That argument takes	an arrayref of operator
       definitions; each operator definition is	a hashref with two entries:

       regex
	   the regular expression to match the operator

       handler
	   Either a coderef or a plain scalar method name. In both cases the
	   expected return is "($sql, @bind)".

	   When	supplied with a	method name, it	is simply called on the
	   SQL::Abstract::Classic object as:

	    $self->$method_name	($field, $op, $arg)

	    Where:

	     $field is the LHS of the operator
	     $op is the	part that matched the handler regex
	     $arg is the RHS

	   When	supplied with a	coderef, it is called as:

	    $coderef->($self, $field, $op, $arg)

       For example, here is an implementation of the MATCH .. AGAINST syntax
       for MySQL

	 my $sqlmaker =	SQL::Abstract::Classic->new(special_ops	=> [

	   # special op	for MySql MATCH	(field)	AGAINST(word1, word2, ...)
	   {regex => qr/^match$/i,
	    handler => sub {
	      my ($self, $field, $op, $arg) = @_;
	      $arg = [$arg] if not ref $arg;
	      my $label		= $self->_quote($field);
	      my ($placeholder)	= $self->_convert('?');
	      my $placeholders	= join ", ", (($placeholder) x @$arg);
	      my $sql		= $self->_sqlcase('match') . " ($label)	"
				. $self->_sqlcase('against') . " ($placeholders) ";
	      my @bind = $self->_bindtype($field, @$arg);
	      return ($sql, @bind);
	      }
	    },

	 ]);

UNARY OPERATORS
	 my $sqlmaker =	SQL::Abstract::Classic->new(unary_ops => [
	    {
	     regex => qr/.../,
	     handler =>	sub {
	       my ($self, $op, $arg) = @_;
	       ...
	     },
	    },
	    {
	     regex => qr/.../,
	     handler =>	'method_name',
	    },
	  ]);

       A "unary	operator" is a SQL syntactic clause that can be	applied	to a
       field - the operator goes before	the field

       You can write your own operator handlers	- supply a "unary_ops"
       argument	to the "new" method. That argument takes an arrayref of
       operator	definitions; each operator definition is a hashref with	two
       entries:

       regex
	   the regular expression to match the operator

       handler
	   Either a coderef or a plain scalar method name. In both cases the
	   expected return is $sql.

	   When	supplied with a	method name, it	is simply called on the
	   SQL::Abstract::Classic object as:

	    $self->$method_name	($op, $arg)

	    Where:

	     $op is the	part that matched the handler regex
	     $arg is the RHS or	argument of the	operator

	   When	supplied with a	coderef, it is called as:

	    $coderef->($self, $op, $arg)

PERFORMANCE
       Thanks to some benchmarking by Mark Stosberg, it	turns out that this
       module is many orders of	magnitude faster than using "DBIx::Abstract".
       I must admit this wasn't	an intentional design issue, but it's a
       byproduct of the	fact that you get to control your "DBI"	handles
       yourself.

       To maximize performance,	use a code snippet like	the following:

	   # prepare a statement handle	using the first	row
	   # and then reuse it for the rest of the rows
	   my($sth, $stmt);
	   for my $href	(@array_of_hashrefs) {
	       $stmt ||= $sql->insert('table', $href);
	       $sth  ||= $dbh->prepare($stmt);
	       $sth->execute($sql->values($href));
	   }

       The reason this works is	because	the keys in your $href are sorted
       internally by SQL::Abstract::Classic. Thus, as long as your data
       retains the same	structure, you only have to generate the SQL the first
       time around. On subsequent queries, simply use the "values" function
       provided	by this	module to return your values in	the correct order.

       However this depends on the values having the same type - if, for
       example,	the values of a	where clause may either	have values (resulting
       in sql of the form "column = ?" with a single bind value), or
       alternatively the values	might be "undef" (resulting in sql of the form
       "column IS NULL"	with no	bind value) then the caching technique
       suggested will not work.

FORMBUILDER
       If you use my "CGI::FormBuilder"	module at all, you'll hopefully	really
       like this part (I do, at	least).	Building up a complex query can	be as
       simple as the following:

	   #!/usr/bin/perl

	   use warnings;
	   use strict;

	   use CGI::FormBuilder;
	   use SQL::Abstract::Classic;

	   my $form = CGI::FormBuilder->new(...);
	   my $sql  = SQL::Abstract::Classic->new;

	   if ($form->submitted) {
	       my $field = $form->field;
	       my $id =	delete $field->{id};
	       my($stmt, @bind)	= $sql->update('table',	$field,	{id => $id});
	   }

       Of course, you would still have to connect using	"DBI" to run the
       query, but the point is that if you make	your form look like your
       table, the actual query script can be extremely simplistic.

       If you're REALLY	lazy (I	am), check out "HTML::QuickTable" for a	fast
       interface to returning and formatting data. I frequently	use these
       three modules together to write complex database	query apps in under 50
       lines.

ACKNOWLEDGEMENTS
       There are a number of individuals that have really helped out with this
       module. Unfortunately, most of them submitted bugs via CPAN so I	have
       no idea who they	are! But the people I do know are:

	   Ash Berlin (order_by	hash term support)
	   Matt	Trout (DBIx::Class support)
	   Mark	Stosberg (benchmarking)
	   Chas	Owens (initial "IN" operator support)
	   Philip Collins (per-field SQL functions)
	   Eric	Kolve (hashref "AND" support)
	   Mike	Fragassi (enhancements to "BETWEEN" and	"LIKE")
	   Dan Kubb (support for "quote_char" and "name_sep")
	   Guillermo Roditi (patch to cleanup "IN" and "BETWEEN", fix and tests	for _order_by)
	   Laurent Dami	(internal refactoring, extensible list of special operators, literal SQL)
	   Norbert Buchmuller (support for literal SQL in hashpair, misc. fixes	& tests)
	   Peter Rabbitson (rewrite of SQLA::Test, misc. fixes & tests)
	   Oliver Charles (support for "RETURNING" after "INSERT")

       Thanks!

SEE ALSO
       SQL::Abstract, DBIx::Class, DBIx::Abstract, CGI::FormBuilder,
       HTML::QuickTable.

AUTHOR
       Copyright (c) 2001-2007 Nathan Wiger <nwiger@cpan.org>. All Rights
       Reserved.

LICENSE
       This module is free software; you may copy this under the same terms as
       perl itself (either the GNU General Public License or the Artistic
       License)

perl v5.32.1			  2019-10-17	     SQL::Abstract::Classic(3)

NAME | SYNOPSIS | Low-impact fork of SQL::Abstract v1.81 ( 2014-10-25 ) | DESCRIPTION | METHODS | WHERE CLAUSES | ORDER BY CLAUSES | SPECIAL OPERATORS | UNARY OPERATORS | PERFORMANCE | FORMBUILDER | ACKNOWLEDGEMENTS | SEE ALSO | AUTHOR | LICENSE

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=SQL::Abstract::Classic&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help