Provided by: libdbix-searchbuilder-perl_1.66-1_all 
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
preferred for end users.
InsertFromSelect
Takes table name, array reference with columns, select query and list of bind values.
Inserts data select by the query into the table.
To make sure call is portable every column in result of the query should have unique name
or should be aliased. See "InsertFromSelect" in DBIx::SearchBuilder::Handle::Oracle for
details.
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
SimpleUpdateFromSelect
Takes table name, hash reference with (column, value) pairs, select query and list of bind
values.
Updates the table, but only records with IDs returned by the selected query, eg:
UPDATE $table SET %values WHERE id IN ( $query )
It's simple as values are static and search only allowed by id.
DeleteFromSelect
Takes table name, select query and list of bind values.
Deletes from the table, but only records with IDs returned by the select query, eg:
DELETE FROM $table WHERE id IN ($query)
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
takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.
Log MESSAGE
Takes a single argument, a message to log.
Currently prints that message to STDERR
SimpleDateTimeFunctions
See "DateTimeFunction" for details on supported functions. This method is for
implementers of custom DB connectors.
Returns hash reference with (function name, sql template) pairs.
DateTimeFunction
Takes named arguments:
• Field - SQL expression date/time function should be applied to. Note that this
argument is used as is without any kind of quoting.
• Type - name of the function, see supported values below.
• Timezone - optional hash reference with From and To values, see
"ConvertTimezoneFunction" for details.
Returns SQL statement. Returns NULL if function is not supported.
Supported functions
Type value in "DateTimeFunction" is case insesitive. Spaces, underscores and dashes are
ignored. So 'date time', 'DateTime' and 'date_time' are all synonyms. The following
functions are supported:
• date time - as is, no conversion, except applying timezone conversion if it's
provided.
• time - time only
• hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'
• hour - hour, 0 - 23
• date - date only
• daily - synonym for date
• day of week - 0 - 6, 0 - Sunday
• day - day of month, 1 - 31
• day of month - synonym for day
• day of year - 1 - 366, support is database dependent
• month - 1 - 12
• monthly - year and month prefix, e.g. '2010-11'
• year - e.g. '2023'
• annually - synonym for year
• week of year - 0-53, presence of zero week, 1st week meaning and whether week starts
on Monday or Sunday heavily depends on database.
ConvertTimezoneFunction
Generates a function applied to Field argument that converts timezone. By default
converts from UTC. Examples:
# UTC => Moscow
$handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');
If there is problem with arguments or timezones are equal then Field returned without any
function applied. Field argument is not escaped in any way, it's your job.
Implementation is very database specific. To be portable convert from UTC or to UTC. Some
databases have internal storage for information about timezones that should be kept up to
date. Read documentation for your DB.
DateTimeIntervalFunction
Generates a function to calculate interval in seconds between two dates. Takes From and To
arguments which can be either scalar or a hash. Hash is processed with
"CombineFunctionWithField" in DBIx::SearchBuilder.
Arguments are not quoted or escaped in any way. It's caller's job.
NullsOrder
Sets order of NULLs when sorting columns when called with mode, but only if DB supports
it. Modes:
• small
NULLs are smaller then anything else, so come first when order is ASC and last
otherwise.
• large
NULLs are larger then anything else.
• first
NULLs are always first.
• last
NULLs are always last.
• default
Return back to DB's default behaviour.
When called without argument returns metadata required to generate SQL.
HasSupportForNullsOrder
Returns true value if DB supports adjusting NULLs order while sorting a column, for
example "ORDER BY Value ASC NULLS FIRST".
DESTROY
When we get rid of the Searchbuilder::Handle, we need to disconnect from the database
AUTHOR
Jesse Vincent, jesse@fsck.com
SEE ALSO
perl(1), DBIx::SearchBuilder