Robert Rothenberg > SQL-QueryBuilder-Simple-0.04 > SQL::QueryBuilder::Simple

Download:
SQL-QueryBuilder-Simple-0.04.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.04   Source  

NAME ^

SQL::QueryBuilder::Simple - Generates simple SQL SELECT queries

REQUIREMENTS ^

This module is written for and tested on Perl 5.6.0.

It uses only standard modules.

Installation

Installation is pretty standard:

  perl Makefile.PL
  make
  make test
  make install

SYNOPSIS ^

  use SQL::QueryBuilder::Simple;

  my $query = SQL::QueryBuilder::Simple->new(
    table  => "MYTABLE",            # from table MYTABLE
    fields => [ qw( FLD1 FLD2 ) ],  # select fields FLD1, FLD2
    ne     => { FLD1 => -1 },       # where FLD1 not equal to -1
  );

  print $query->sql; # SELECT FLD1, FLD2 FROM MYTABLE WHERE FLD1<>?

  use DBI;

  my $dbh = DBI->connect( ... ) or die;
  my $sth = $dbh->prepare( $query->sql ) or die;

  $sth->execute( $query->bindings ) or die;

DESCRIPTION ^

This module generates simple SQL select statements and manages binding parameters, to simplify generating dynamic queries.

This module will only generate SQL code. It does not validate the code, nor does it check against any database to see if table or field names are valid.

METHODS

new
  $obj = SQL::QueryBuilder::Simple->new( %PARAMTETERS );

Creates a new object. Optional parameters (corresponding to most method names below) can be specified to configure the query.

table
  $obj->table( $TABLENAME );

  $TABLENAME = $obj->table;

Sets the table or view used in the query. Only one table may be specified. If it is called without arguments, it returns the name of the table.

fields
  $obj->fields( @FIELDS );

  @FIELDS = $obj->fields;

Sets the fields to be returned from the table. If none set set, the query will default to selecting all fields. Redefining fields will clear any defined aliases and order.

If it is called without any arguments, it returns an array of field names.

If called as a parameter within the new method, the argument must be an array reference.

clear_fields
  $obj->clear_fields;

Clear all defined fields. The query will default to selecting all fields.

aliases
  $obj->aliases( %ALIASES );

  %ALIASES = $obj->aliases;

Defines aliases for field names. For example,

  $query->aliases( FLD1 => "First", FLD2 => "Second" );

Note that aliases are cumulative (unlike other methods, where calls will redefine related values). The following is equivalent to the above example:

  $query->aliases( FLD1 => "First" );
  $query->aliases( FLD2 => "Second" );

If called as a parameter within the new method, the argument must be a hash reference.

clear_aliases
  $obj->clear_aliases;

Clear all defined aliases.

report_field_names
  %NAMES = $obj->report_field_names;

Returns a hash where the keys are the field names (or alias names instead).

eq
  $obj->eq( %EQUALS );

  %EQUALS = $obj->eq;

Sets FIELD=VALUE WHERE clauses. The hash keys are field names or aliases, and the hash values are the values are the values that the fields should be equal to. For example,

  $query->eq( FLD1 => 0 );

will require that FLD1 be equal to 0. (In SQL examples, we will use something like "WHERE FLD1=0", but in reality the object generates SQL with binding parameters: "WHERE FLD1=?"; see bindings for more information).

If the value is an array reference, it will require that the field be one of those values:

  $query->eq( FLD1 => [1, 4] );

will translate to "WHERE (FLD1=1 OR FLD1=4)" in SQL.

When the value is undefined:

  $query->eq( FLD1 => undef );

will translate to "WHERE FLD IS NULL" in SQL (requiring a NULL value).

Multiple fields may be specified:

  $query->eq( FLD1 => [1, 4], FLD2 => 0, FLD3 => undef, );

In the above example, the query will require that any of the above restrictions be true (connected by "OR"). If you want to require that all of them be true (connected by "AND"), set any_clause to false.

Note that there is no way to specify the order of the conditions in the clause, which is why only simple connectors are used.

Redefining this method will overwrite existing clauses.

If called as a parameter within the new method, the argument must be a hash reference.

ne
  $obj->ne( %NOT_EQUALS );

  %NOT_EQUALS = $obj->ne;

Specify inequalities for the query (fields not be equal to values).

Similar to eq, with some exceptions: when passed an array reference, the connector will be "AND" instead of "OR":

  $query->ne( FLD1 => [1, 4] );

will translate to "WHERE (FLD1<>1 AND FLD1<>4)" in SQL.

If called as a parameter within the new method, the argument must be a hash reference.

It is possible to use multple clauses for a query:

  $query->ne( FLD1 => 1 );
  $query->eq( FLD2 => "Bob" );
  $query->any_clause(0);       # require all clauses

returns "WHERE FLD1<>1 AND FLD2='Bob'" in SQL.

lt
  $obj->lt( %LESS_THAN );

  %LESS_THAN = $obj->lt;

Specify that fields be less than values for the query.

Similar to eq, except that array references or undefined values will produce nonsense results (read: undocumented and unsupported).

If called as a parameter within the new method, the argument must be a hash reference.

gt

Specify that fields be greater than values for the query. See lt.

le

Specify that fields be less than or equal to values for the query. See lt.

ge

Specify that fields be greater than or equal to values for the query. See lt.

any_clause
  $query->any_clause(0);

  if ($query->any_clause) { ... }

If true (default), the query will require that any clause be true. If false, the query will require all clauses to be true. For example,

  $query->any_clause(1);
  print $query->sql;     # SELECT * FROM FOO WHERE A=? OR B=?

  $query->any_clause(0);
  print $query->sql;     # SELECT * FROM FOO WHERE A=? AND B=?

This method may also be specified as a parameter in the new method.

clear_clauses
  $query->clear_clauses;

Remove all defined clauses.

order

If called as a parameter within the new method, the argument must be an array reference.

clear_order
  $query->clear_order;

Remove defined sorting orders.

sql
  $SQL_STATEMENT = $obj->sql;

Returns the SQL statement.

bindings
  @BINDING_PARAMS = $obj->bindings();

Returns the actual binding parameters used for DBI, in the same order as the corresponding SQL statement generated by sql. For example:

  use DBI;

  my $dbh = DBI->connect( ... ) or die;
  my $sth = $dbh->prepare( $query->sql ) or die;

  $sth->execute( $query->bindings ) or die;
use_bindings
  $obj->use_bindings(1);

  if ($obj->use_bindings) { ... }

When true (default>, generate SQL with binding parameters rather than with values plugged in. When false, sql will produce an error because the feature is not yet implemented.

EXPORT

None by default. The following subroutines may be specified manually:

quote_name

A utility routine which surrounds a name with quotes.

KNOWN ISSUES ^

This module implements simple SQL queries against tables and views. Joined tables, functions, and many clauses such as BETWEEN or IN are not supported.

Complex where clauses beyond any/all are not supported.

sql cannot run when use_bindings is false. This feature is not yet implemented.

See the TODO file in this distribution for future features.

SEE ALSO ^

There are similar modules available on CPAN. Many of these modules incorporate database connections and execute the queries, which in some cases is too much functionality. However, these may be more suited to your needs. Some of them are listed below:

  DBIx::SearchBuilder
  Relations::Query
  Text::Query::SQL

AUTHOR ^

Robert Rothenberg <rrwo@cpan.org>

LICENSE ^

Copyright (c) 2001 Robert Rothenberg. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

syntax highlighting: