NAME

DBIx::SQLEngine - Extends DBI with high-level operations

SYNOPSIS

my $sqldb = DBIx::SQLEngine->new( $DBIConnectionString );

$sqldb->do_insert( 
  table => 'students', 
  values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' } 
);

$hashes = $sqldb->fetch_select( 
  table => 'students', criteria => { 'status'=>'minor' } 
);

$sqldb->do_update( 
  table => 'students', criteria => 'age > 20', 
  values => { 'status'=>'adult' } 
);

$sqldb->do_delete( 
  table => 'students', criteria => { 'name'=>'Dave' } 
);

DESCRIPTION

The DBIx::SQLEngine class provides an extended interface for the DBI database interface, adding methods that support ad-hoc SQL generation and query execution in a single call.

Behind the scenes, different subclasses are instantiated depending on the type of server to which you connect, thanks to DBIx::AnyData. As a result, SQL dialect ideosyncracies can be compensated for; this release includes subclasses supporting the MySQL, Pg, AnyData, and CSV drivers.

INTERFACE

Connection Setup

Create one SQLEngine for each DBI datasource you will use.

new

DBIx::SQLEngine->new( $dsn ) : $sqldb
DBIx::SQLEngine->new( $dsn, $user, $pass ) : $sqldb
DBIx::SQLEngine->new( $dsn, $user, $pass, $args ) : $sqldb

Accepts the same arguments as the standard DBI connect method.

Retrieving Data

The following methods retrive data from the datasource using SQL select statements.

fetch_select

$sqldb->fetch_select( %sql_clauses ) : $row_hashes
$sqldb->fetch_select( %sql_clauses ) : ( $row_hashes, $column_hashes )

Retrieve rows from the datasource as an array of hashrefs. If called in a list context, also returns an array of hashrefs containing information about the columns included in the result set.

Argument Pairs:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table or tables

Required (unless explicit "sql => ..." is used). The name of the tables to select from.

columns

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to an object with a "column_names" method.

criteria

Optional. May contain a literal SQL where clause (everything after there word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

If the criteria argument is a reference to hash, it is treated as a set of field-name => value pairs, and a SQL expression is created that requires each one of the named fields to exactly match the value provided for it, or if the value is an array reference to match any one of the array's contents; see DBIx::SQLEngine::Criteria::HashGroup for details. Criteria values may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

Alternately, if the criteria is an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

order

Optional. May contain a comma-separated string of column names or experessions, optionally followed by "DESC", or an reference to an array of the same.

group

Optional. May contain a comma-separated string of column names or experessions, or an reference to an array of the same.

Examples:

Literal SQL:

$hashes = $sqldb->fetch_select( 
  sql => 'select * from students where id > 200'
);

Literal SQL with placeholders:

$hashes = $sqldb->fetch_select( 
  sql => [ 'select * from students where id > ?', 200 ]
);

Generation of basic SQL statements:

$hashes = $sqldb->fetch_select( 
  table => 'students', criteria => { 'status'=>'minor' } 
);

Limiting the columns returned, and specifying an order:

$hashes = $sqldb->fetch_select( 
  table => 'students', columns => 'name, age', order => 'name'
);

Here's a criteria clause that uses a function to find the youngest people; note the use of a backslash to indicate that "min(age)" is an expression to be evaluated by the database server, rather than a literal value:
```
$hashes = $sqldb->fetch_select( 
  table => 'students', criteria => { 'age' => \"min(age)" } 
);
```

Here's a join of two tables:

$hashes = $sqldb->fetch_select( 
  tables => 'students, grades', 
  criteria => 'students.id = grades.student_id',
  order => 'students.name'
);

visit_select

$sqldb->visit_select( $code_ref, %sql_clauses ) : @results

Takes the same arguments as fetch_select, with the addition of a leading subroutine reference. Retrieve rows from the datasource as a series of hashrefs, and call the user provided subroutine for each one. Returns the results returned by each of those calls. This can allow for more efficient processing if you are processing a large number of rows and do not need to keep them all in memory.

Examples:

A basic traversal of all rows:

$sqldb->visit_select( 
  sub {
    my $student = shift;
    ... do something with %$student ...
  }, 
  table => 'student'
);

You can use any combination of the other clauses supported by fetch_select:

 $sqldb->visit_select( 
  sub {
    my $student = shift;
    ... do something with $student->{id} and $student->{name} ...
  }, 
  table => 'student', 
  columns => 'id, name', 
  order => 'name, id desc',
  criteria => 'age < 22',
);

fetch_one_row

$sqldb->fetch_one_row( %sql_clauses ) : $row_hash

Calls fetch_select, then returns only the first row of results.

Examples:

$joe = $sqldb->fetch_one_row( 
  table => 'student', criteria => { 'id' => 201 }
);

$joe = $sqldb->fetch_one_row( 
  sql => [ 'select * from students where id = ?', 201 ]
);

fetch_one_value

$sqldb->fetch_one_value( %sql_clauses ) : $scalar

Calls fetch_select, then returns a single value from the first row of results.

Examples:

$maxid = $sqldb->fetch_one_value( 
  sql => 'select max(id) from students where status = ?'
);

$count = $sqldb->fetch_one_value( 
  table => 'student', columns => 'count(*)'
);

Modifying Data

do_insert( %sql_clauses )

$sqldb->do_insert( %sql_clauses )

Insert a single row into a table in the datasource.

Argument Pairs:

sql: Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.
table: Required (unless explicit "sql => ..." is used). The name of the table to insert into.
columns: Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash whose keys contain the column names, or a reference to an object with a "column_names" method.
values: Required. May contain a string with one or more comma-separated quoted values or expressions in SQL format, or a reference to an array of values to insert in order, or a reference to a hash whose values are to be inserted. If an array or hash reference is used, each value may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

Examples:

$sqldb->do_insert( 
  table => 'students', 
  values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' } 
);

$sqldb->do_insert( 
  table => 'students', 
  columns => [ 'name', 'age', 'status' ], 
  values => [ 'Dave', '19', 'minor' ]
);

$sqldb->fetch_one_row( 
  sql => [ 'insert into students (id, name) values (?, ?)', 201, 'Dave' ]
);

do_update ( %sql_clauses )

Modify one or more rows in a table in the datasource.

Argument Pairs:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table

Required (unless explicit "sql => ..." is used). The name of the table to insert into.

columns

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash whose keys contain the column names, or a reference to an object with a "column_names" method.

values

Required. May contain a string with one or more comma-separated quoted values or expressions in SQL format, or a reference to an array of values to insert in order, or a reference to a hash whose values are to be inserted. If an array or hash reference is used, each value may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

criteria

Optional, but remember that ommitting this will cause all of your rows to be updated! May contain a literal SQL where clause (everything after there word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

Alternately, if the criteria is an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

Examples:

$sqldb->do_update( 
  table => 'students', 
  criteria => 'age > 20', 
  values => { 'status'=>'adult' } 
);

$sqldb->do_update( 
  table => 'students', 
  criteria => 'age > 20', 
  columns => [ 'status' ], 
  values => [ 'adult' ]
);

$sqldb->fetch_one_row( 
  sql => [ 'update students set status = ? where age > ?', 'adult', 20 ]
);

do_delete ( %sql_clauses )

Delete one or more rows in a table in the datasource.

Argument Pairs:

sql

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

table

Required (unless explicit "sql => ..." is used). The name of the table to delete from.

criteria

Optional, but remember that ommitting this will cause all of your rows to be deleted! May contain a literal SQL where clause (everything after there word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

Alternately, if the criteria is an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

Examples:

$sqldb->do_delete( 
  table => 'students', criteria => { 'name'=>'Dave' } 
);

$sqldb->fetch_one_row( 
  sql => [ 'delete from students where name = ?', 'Dave' ]
);

Checking For Existence

To determine if the connection is working and whether a table exists.

detect_any

$sqldb->detect_any () : $boolean

Attempts to confirm that values can be retreived from the database, using a server-specific "trivial" or "guaranteed" query provided by the subclass. Catches any exceptions; if the query fails for any reason we return nothing.

detect_table

$sqldb->detect_table ( $tablename ) : @columns_or_empty

Attempts to query the given table without retrieving many (or any) rows. Uses a server-specific "trivial" or "guaranteed" query provided by the subclass. Catches any exceptions; if the query fails for any reason we return nothing.

Create and Drop Tables

do_create_table

$sqldb->do_create_table( $tablename, $column_hash_ary )

Create a table.

do_drop_table

$sqldb->do_drop_table( $tablename )

Delete a table.

Logging

DBILogging

$sqldb->DBILogging : $value
$sqldb->DBILogging( $value )

Set this to a true value to turn on logging. Can be called on the class to set a shared default for all instances, or on any instance to set the value for it alone.

SQLLogging

$sqldb->SQLLogging () : $value 
$sqldb->SQLLogging( $value )

Set this to a true value to turn on logging of internally-generated SQL statements (all queries except for those with complete SQL statements explicitly passed in by the caller). Can be called on the class to set a shared default for all instances, or on any instance to set the value for it alone.

CREDITS AND COPYRIGHT

Developed By

M. Simon Cavalletto, simonm@cavalletto.org
Evolution Softworks, www.evoscript.org

Contributors

Eric Schneider, roark@evolution.com
E. J. Evans, piglet@piglet.org

Copyright

License

You may use, modify, and distribute this software under the same terms as Perl.

To install DBIx::SQLEngine, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::SQLEngine

CPAN shell

perl -MCPAN -e shell
install DBIx::SQLEngine

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

INTERFACE

Connection Setup

Retrieving Data

Modifying Data

Checking For Existence

Create and Drop Tables

Logging

SEE ALSO

CREDITS AND COPYRIGHT

Developed By

Contributors

Copyright

License

Module Install Instructions