NAME

DBIx::SearchBuilder::Handle - Perl extension which is a generic DBI handle

SYNOPSIS

 use DBIx::SearchBuilder::Handle;

 my $handle = DBIx::SearchBuilder::Handle->new();
 $handle->Connect( Driver => 'mysql',
                   Database => 'dbname',
                   Host => 'hostname',
                   User => 'dbuser',
                   Password => 'dbpassword');
 # now $handle isa DBIx::SearchBuilder::Handle::mysql

DESCRIPTION

This class provides a wrapper for DBI handles that can also perform a number of additional functions.

new

Generic constructor

Connect PARAMHASH: Driver, Database, Host, User, Password

Takes a paramhash and connects to your DBI datasource.

You should _always_ set

DisconnectHandleOnDestroy => 1

unless you have a legacy app like RT2 or RT 3.0.{0,1,2} that depends on the broken behaviour.

If you created the handle with DBIx::SearchBuilder::Handle->new and there is a DBIx::SearchBuilder::Handle::(Driver) subclass for the driver you have chosen, the handle will be automatically "upgraded" into that subclass.

_UpgradeHandle DRIVER

This private internal method turns a plain DBIx::SearchBuilder::Handle into one of the standard driver-specific subclasses.

BuildDSN PARAMHASH

Takes a bunch of parameters:

Required: Driver, Database, Optional: Host, Port and RequireSSL

Builds a DSN suitable for a DBI connection

DSN

Returns the DSN for this database connection.

RaiseError [MODE]

Turns on the Database Handle's RaiseError attribute.

PrintError [MODE]

Turns on the Database Handle's PrintError attribute.

LogSQLStatements BOOL

Takes a boolean argument. If the boolean is true, SearchBuilder will log all SQL statements, as well as their invocation times and execution times.

Returns whether we're currently logging or not as a boolean

_LogSQLStatement STATEMENT DURATION

Add an SQL statement to our query log

ClearSQLStatementLog

Clears out the SQL statement log.

SQLStatementLog

Returns the current SQL statement log as an array of arrays. Each entry is a triple of

(Time, Statement, Duration)

AutoCommit [MODE]

Turns on the Database Handle's AutoCommit attribute.

Disconnect

Disconnect from your DBI datasource

dbh [HANDLE]

Return the current DBI handle. If we're handed a parameter, make the database handle that.

Insert $TABLE_NAME @KEY_VALUE_PAIRS

Takes a table name and a set of key-value pairs in an array. Splits the key value pairs, constructs an INSERT statement and performs the insert.

Base class return statement handle object, while DB specific subclass should return row id.

InsertQueryString $TABLE_NAME @KEY_VALUE_PAIRS

Takes a table name and a set of key-value pairs in an array. Splits the key value pairs, constructs an INSERT statement and returns query string and set of bind values.

This method is more useful for subclassing in DB specific handles. "Insert" method is prefered for end users.

UpdateRecordValue

Takes a hash with fields: Table, Column, Value PrimaryKeys, and IsSQLFunction. Table, and Column should be obvious, Value is where you set the new value you want the column to have. The primary_keys field should be the lvalue of DBIx::SearchBuilder::Record::PrimaryKeys(). Finally IsSQLFunction is set when the Value is a SQL function. For example, you might have ('Value'=>'PASSWORD(string)'), by setting IsSQLFunction that string will be inserted into the query directly rather then as a binding.

UpdateTableValue TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL

Update column COLUMN of table TABLE where the record id = RECORD_ID. if IS_SQL is set, don\'t quote the NEW_VALUE

SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]

Execute the SQL string specified in QUERY_STRING

FetchResult QUERY, [ BIND_VALUE, ... ]

Takes a SELECT query as a string, along with an array of BIND_VALUEs If the select succeeds, returns the first row as an array. Otherwise, returns a Class::ResturnValue object with the failure loaded up.

BinarySafeBLOBs

Returns 1 if the current database supports BLOBs with embedded nulls. Returns undef if the current database doesn't support BLOBs with embedded nulls

KnowsBLOBs

Returns 1 if the current database supports inserts of BLOBs automatically. Returns undef if the current database must be informed of BLOBs for inserts.

BLOBParams FIELD_NAME FIELD_TYPE

Returns a hash ref for the bind_param call to identify BLOB types used by the current database for a particular column type.

DatabaseVersion [Short => 1]

Returns the database's version.

If argument Short is true returns short variant, in other case returns whatever database handle/driver returns. By default returns short version, e.g. '4.1.23' or '8.0-rc4'.

Returns empty string on error or if database couldn't return version.

The base implementation uses a SELECT VERSION()

CaseSensitive

Returns 1 if the current database's searches are case sensitive by default Returns undef otherwise

_MakeClauseCaseInsensitive FIELD OPERATOR VALUE

Takes a field, operator and value. performs the magic necessary to make your database treat this clause as case insensitive.

Returns a FIELD OPERATOR VALUE triple.

Transactions

DBIx::SearchBuilder::Handle emulates nested transactions, by keeping a transaction stack depth.

NOTE: In nested transactions you shouldn't mix rollbacks and commits, because only last action really do commit/rollback. For example next code would produce desired results:

$handle->BeginTransaction;
  $handle->BeginTransaction;
  ...
  $handle->Rollback;
  $handle->BeginTransaction;
  ...
  $handle->Commit;
$handle->Commit;

Only last action(Commit in example) finilize transaction in DB.

BeginTransaction

Tells DBIx::SearchBuilder to begin a new SQL transaction. This will temporarily suspend Autocommit mode.

EndTransaction [Action => 'commit'] [Force => 0]

Tells to end the current transaction. Takes Action argument that could be commit or rollback, the default value is commit.

If Force argument is true then all nested transactions would be committed or rolled back.

If there is no transaction in progress then method throw warning unless action is forced.

Method returns true on success or false if error occured.

Commit [FORCE]

Tells to commit the current SQL transaction.

Method uses EndTransaction method, read its description.

Rollback [FORCE]

Tells to abort the current SQL transaction.

Method uses EndTransaction method, read its description.

ForceRollback

Force the handle to rollback. Whether or not we're deep in nested transactions.

TransactionDepth

Returns the current depth of the nested transaction stack. Returns undef if there is no connection to database.

ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW

takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE starting with FIRST_ROW;

Join { Paramhash }

Takes a paramhash of everything Searchbuildler::Record does plus a parameter called 'SearchBuilder' that contains a ref to a SearchBuilder object'.

This performs the join.

MayBeNull

Takes a SearchBuilder and ALIAS in a hash and resturns true if restrictions of the query allow NULLs in a table joined with the ALIAS, otherwise returns false value which means that you can use normal join instead of left for the aliased table.

Works only for queries have been built with "Join" in DBIx::SearchBuilder and "Limit" in DBIx::SearchBuilder methods, for other cases return true value to avoid fault optimizations.

DistinctQuery STATEMENTREF

takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.

DistinctCount STATEMENTREF