.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBIx::SearchBuilder::Handle 3pm"
.TH DBIx::SearchBuilder::Handle 3pm "2021-01-24" "perl v5.32.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
DBIx::SearchBuilder::Handle \- Perl extension which is a generic DBI handle
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 1
\&  use DBIx::SearchBuilder::Handle;
\&
\&  my $handle = DBIx::SearchBuilder::Handle\->new();
\&  $handle\->Connect( Driver => \*(Aqmysql\*(Aq,
\&                    Database => \*(Aqdbname\*(Aq,
\&                    Host => \*(Aqhostname\*(Aq,
\&                    User => \*(Aqdbuser\*(Aq,
\&                    Password => \*(Aqdbpassword\*(Aq);
\&  # now $handle isa DBIx::SearchBuilder::Handle::mysql
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This class provides a wrapper for \s-1DBI\s0 handles that can also perform a number of additional functions.
.SS "new"
.IX Subsection "new"
Generic constructor
.SS "Connect \s-1PARAMHASH:\s0 Driver, Database, Host, User, Password, QuoteTableNames"
.IX Subsection "Connect PARAMHASH: Driver, Database, Host, User, Password, QuoteTableNames"
Takes a paramhash and connects to your \s-1DBI\s0 datasource.
.PP
You should _always_ set
.PP
.Vb 1
\&     DisconnectHandleOnDestroy => 1
.Ve
.PP
unless you have a legacy app like \s-1RT2\s0 or \s-1RT 3.0\s0.{0,1,2} that depends on the broken behaviour.
.PP
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 \*(L"upgraded\*(R" into that subclass.
.PP
QuoteTableNames option will force all table names to be quoted if the driver subclass has a method
for quoting implemented. The mysql subclass will detect mysql version 8 and set the flag.
.SS "_UpgradeHandle \s-1DRIVER\s0"
.IX Subsection "_UpgradeHandle DRIVER"
This private internal method turns a plain DBIx::SearchBuilder::Handle into one
of the standard driver-specific subclasses.
.SS "BuildDSN \s-1PARAMHASH\s0"
.IX Subsection "BuildDSN PARAMHASH"
Takes a bunch of parameters:
.PP
Required: Driver, Database,
Optional: Host, Port and RequireSSL
.PP
Builds a \s-1DSN\s0 suitable for a \s-1DBI\s0 connection
.SS "\s-1DSN\s0"
.IX Subsection "DSN"
Returns the \s-1DSN\s0 for this database connection.
.SS "RaiseError [\s-1MODE\s0]"
.IX Subsection "RaiseError [MODE]"
Turns on the Database Handle's RaiseError attribute.
.SS "PrintError [\s-1MODE\s0]"
.IX Subsection "PrintError [MODE]"
Turns on the Database Handle's PrintError attribute.
.SS "LogSQLStatements \s-1BOOL\s0"
.IX Subsection "LogSQLStatements BOOL"
Takes a boolean argument. If the boolean is true, SearchBuilder will log all \s-1SQL\s0
statements, as well as their invocation times and execution times.
.PP
Returns whether we're currently logging or not as a boolean
.SS "_LogSQLStatement \s-1STATEMENT DURATION\s0"
.IX Subsection "_LogSQLStatement STATEMENT DURATION"
Add an \s-1SQL\s0 statement to our query log
.SS "ClearSQLStatementLog"
.IX Subsection "ClearSQLStatementLog"
Clears out the \s-1SQL\s0 statement log.
.SS "SQLStatementLog"
.IX Subsection "SQLStatementLog"
Returns the current \s-1SQL\s0 statement log as an array of arrays. Each entry is a triple of
.PP
(Time,  Statement, Duration)
.SS "AutoCommit [\s-1MODE\s0]"
.IX Subsection "AutoCommit [MODE]"
Turns on the Database Handle's AutoCommit attribute.
.SS "Disconnect"
.IX Subsection "Disconnect"
Disconnect from your \s-1DBI\s0 datasource
.SS "dbh [\s-1HANDLE\s0]"
.IX Subsection "dbh [HANDLE]"
Return the current \s-1DBI\s0 handle. If we're handed a parameter, make the database handle that.
.ie n .SS "Insert $TABLE_NAME @KEY_VALUE_PAIRS"
.el .SS "Insert \f(CW$TABLE_NAME\fP \f(CW@KEY_VALUE_PAIRS\fP"
.IX Subsection "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 \s-1INSERT\s0 statement
and performs the insert.
.PP
Base class return statement handle object, while \s-1DB\s0 specific
subclass should return row id.
.ie n .SS "InsertQueryString $TABLE_NAME @KEY_VALUE_PAIRS"
.el .SS "InsertQueryString \f(CW$TABLE_NAME\fP \f(CW@KEY_VALUE_PAIRS\fP"
.IX Subsection "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 \s-1INSERT\s0 statement
and returns query string and set of bind values.
.PP
This method is more useful for subclassing in \s-1DB\s0 specific
handles. \*(L"Insert\*(R" method is preferred for end users.
.SS "InsertFromSelect"
.IX Subsection "InsertFromSelect"
Takes table name, array reference with columns, select query
and list of bind values. Inserts data select by the query
into the table.
.PP
To make sure call is portable every column in result of
the query should have unique name or should be aliased.
See \*(L"InsertFromSelect\*(R" in DBIx::SearchBuilder::Handle::Oracle for
details.
.SS "UpdateRecordValue"
.IX Subsection "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 \fBDBIx::SearchBuilder::Record::PrimaryKeys()\fR.  Finally 
IsSQLFunction is set when the Value is a \s-1SQL\s0 function.  For example, you 
might have ('Value'=>'\s-1PASSWORD\s0(string)'), by setting IsSQLFunction that 
string will be inserted into the query directly rather then as a binding.
.SS "UpdateTableValue \s-1TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL\s0"
.IX Subsection "UpdateTableValue TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL"
Update column \s-1COLUMN\s0 of table \s-1TABLE\s0 where the record id = \s-1RECORD_ID.\s0  if \s-1IS_SQL\s0 is set,
don\e't quote the \s-1NEW_VALUE\s0
.SH "SimpleUpdateFromSelect"
.IX Header "SimpleUpdateFromSelect"
Takes table name, hash reference with (column, value) pairs,
select query and list of bind values.
.PP
Updates the table, but only records with IDs returned by the
selected query, eg:
.PP
.Vb 1
\&    UPDATE $table SET %values WHERE id IN ( $query )
.Ve
.PP
It's simple as values are static and search only allowed
by id.
.SH "DeleteFromSelect"
.IX Header "DeleteFromSelect"
Takes table name, select query and list of bind values.
.PP
Deletes from the table, but only records with IDs returned by the
select query, eg:
.PP
.Vb 1
\&    DELETE FROM $table WHERE id IN ($query)
.Ve
.SS "SimpleQuery \s-1QUERY_STRING,\s0 [ \s-1BIND_VALUE, ...\s0 ]"
.IX Subsection "SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]"
Execute the \s-1SQL\s0 string specified in \s-1QUERY_STRING\s0
.SS "FetchResult \s-1QUERY,\s0 [ \s-1BIND_VALUE, ...\s0 ]"
.IX Subsection "FetchResult QUERY, [ BIND_VALUE, ... ]"
Takes a \s-1SELECT\s0 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.
.SS "BinarySafeBLOBs"
.IX Subsection "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
.SS "KnowsBLOBs"
.IX Subsection "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.
.SS "BLOBParams \s-1FIELD_NAME FIELD_TYPE\s0"
.IX Subsection "BLOBParams FIELD_NAME FIELD_TYPE"
Returns a hash ref for the bind_param call to identify \s-1BLOB\s0 types used by 
the current database for a particular column type.
.SS "DatabaseVersion [Short => 1]"
.IX Subsection "DatabaseVersion [Short => 1]"
Returns the database's version.
.PP
If argument \f(CW\*(C`Short\*(C'\fR 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'.
.PP
Returns empty string on error or if database couldn't return version.
.PP
The base implementation uses a \f(CW\*(C`SELECT VERSION()\*(C'\fR
.SS "CaseSensitive"
.IX Subsection "CaseSensitive"
Returns 1 if the current database's searches are case sensitive by default
Returns undef otherwise
.SS "QuoteTableNames"
.IX Subsection "QuoteTableNames"
Returns 1 if table names will be quoted in queries, otherwise 0
.SS "_MakeClauseCaseInsensitive \s-1FIELD OPERATOR VALUE\s0"
.IX Subsection "_MakeClauseCaseInsensitive FIELD OPERATOR VALUE"
Takes a field, operator and value. performs the magic necessary to make
your database treat this clause as case insensitive.
.PP
Returns a \s-1FIELD OPERATOR VALUE\s0 triple.
.SS "Transactions"
.IX Subsection "Transactions"
DBIx::SearchBuilder::Handle emulates nested transactions,
by keeping a transaction stack depth.
.PP
\&\fB\s-1NOTE:\s0\fR 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:
.PP
.Vb 8
\&  $handle\->BeginTransaction;
\&    $handle\->BeginTransaction;
\&    ...
\&    $handle\->Rollback;
\&    $handle\->BeginTransaction;
\&    ...
\&    $handle\->Commit;
\&  $handle\->Commit;
.Ve
.PP
Only last action(Commit in example) finilize transaction in \s-1DB.\s0
.PP
\fIBeginTransaction\fR
.IX Subsection "BeginTransaction"
.PP
Tells DBIx::SearchBuilder to begin a new \s-1SQL\s0 transaction.
This will temporarily suspend Autocommit mode.
.PP
\fIEndTransaction [Action => 'commit'] [Force => 0]\fR
.IX Subsection "EndTransaction [Action => 'commit'] [Force => 0]"
.PP
Tells to end the current transaction. Takes \f(CW\*(C`Action\*(C'\fR argument
that could be \f(CW\*(C`commit\*(C'\fR or \f(CW\*(C`rollback\*(C'\fR, the default value
is \f(CW\*(C`commit\*(C'\fR.
.PP
If \f(CW\*(C`Force\*(C'\fR argument is true then all nested transactions
would be committed or rolled back.
.PP
If there is no transaction in progress then method throw
warning unless action is forced.
.PP
Method returns true on success or false if an error occurred.
.PP
\fICommit [\s-1FORCE\s0]\fR
.IX Subsection "Commit [FORCE]"
.PP
Tells to commit the current \s-1SQL\s0 transaction.
.PP
Method uses \f(CW\*(C`EndTransaction\*(C'\fR method, read its
description.
.PP
\fIRollback [\s-1FORCE\s0]\fR
.IX Subsection "Rollback [FORCE]"
.PP
Tells to abort the current \s-1SQL\s0 transaction.
.PP
Method uses \f(CW\*(C`EndTransaction\*(C'\fR method, read its
description.
.PP
\fIForceRollback\fR
.IX Subsection "ForceRollback"
.PP
Force the handle to rollback.
Whether or not we're deep in nested transactions.
.PP
\fITransactionDepth\fR
.IX Subsection "TransactionDepth"
.PP
Returns the current depth of the nested transaction stack.
Returns \f(CW\*(C`undef\*(C'\fR if there is no connection to database.
.SS "ApplyLimits \s-1STATEMENTREF ROWS_PER_PAGE FIRST_ROW\s0"
.IX Subsection "ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW"
takes an \s-1SQL SELECT\s0 statement and massages it to return \s-1ROWS_PER_PAGE\s0 starting with \s-1FIRST_ROW\s0;
.SS "Join { Paramhash }"
.IX Subsection "Join { Paramhash }"
Takes a paramhash of everything Searchbuildler::Record does 
plus a parameter called 'SearchBuilder' that contains a ref 
to a SearchBuilder object'.
.PP
This performs the join.
.SS "MayBeNull"
.IX Subsection "MayBeNull"
Takes a \f(CW\*(C`SearchBuilder\*(C'\fR and \f(CW\*(C`ALIAS\*(C'\fR in a hash and resturns
true if restrictions of the query allow NULLs in a table joined with
the \s-1ALIAS,\s0 otherwise returns false value which means that you can
use normal join instead of left for the aliased table.
.PP
Works only for queries have been built with \*(L"Join\*(R" in DBIx::SearchBuilder and
\&\*(L"Limit\*(R" in DBIx::SearchBuilder methods, for other cases return true value to
avoid fault optimizations.
.SS "DistinctQuery \s-1STATEMENTREF\s0"
.IX Subsection "DistinctQuery STATEMENTREF"
takes an incomplete \s-1SQL SELECT\s0 statement and massages it to return a \s-1DISTINCT\s0 result set.
.SS "DistinctCount \s-1STATEMENTREF\s0"
.IX Subsection "DistinctCount STATEMENTREF"
takes an incomplete \s-1SQL SELECT\s0 statement and massages it to return a \s-1DISTINCT\s0 result set.
.SS "Log \s-1MESSAGE\s0"
.IX Subsection "Log MESSAGE"
Takes a single argument, a message to log.
.PP
Currently prints that message to \s-1STDERR\s0
.SS "SimpleDateTimeFunctions"
.IX Subsection "SimpleDateTimeFunctions"
See \*(L"DateTimeFunction\*(R" for details on supported functions.
This method is for implementers of custom \s-1DB\s0 connectors.
.PP
Returns hash reference with (function name, sql template) pairs.
.SS "DateTimeFunction"
.IX Subsection "DateTimeFunction"
Takes named arguments:
.IP "\(bu" 4
Field \- \s-1SQL\s0 expression date/time function should be applied
to. Note that this argument is used as is without any kind of quoting.
.IP "\(bu" 4
Type \- name of the function, see supported values below.
.IP "\(bu" 4
Timezone \- optional hash reference with From and To values,
see \*(L"ConvertTimezoneFunction\*(R" for details.
.PP
Returns \s-1SQL\s0 statement. Returns \s-1NULL\s0 if function is not supported.
.PP
\fISupported functions\fR
.IX Subsection "Supported functions"
.PP
Type value in \*(L"DateTimeFunction\*(R" is case insesitive. Spaces,
underscores and dashes are ignored. So 'date time', 'DateTime'
and 'date_time' are all synonyms. The following functions are
supported:
.IP "\(bu" 4
date time \- as is, no conversion, except applying timezone
conversion if it's provided.
.IP "\(bu" 4
time \- time only
.IP "\(bu" 4
hourly \- datetime prefix up to the hours, e.g. '2010\-03\-25 16'
.IP "\(bu" 4
hour \- hour, 0 \- 23
.IP "\(bu" 4
date \- date only
.IP "\(bu" 4
daily \- synonym for date
.IP "\(bu" 4
day of week \- 0 \- 6, 0 \- Sunday
.IP "\(bu" 4
day \- day of month, 1 \- 31
.IP "\(bu" 4
day of month \- synonym for day
.IP "\(bu" 4
day of year \- 1 \- 366, support is database dependent
.IP "\(bu" 4
month \- 1 \- 12
.IP "\(bu" 4
monthly \- year and month prefix, e.g. '2010\-11'
.IP "\(bu" 4
year \- e.g. '2023'
.IP "\(bu" 4
annually \- synonym for year
.IP "\(bu" 4
week of year \- 0\-53, presence of zero week, 1st week meaning
and whether week starts on Monday or Sunday heavily depends on database.
.SS "ConvertTimezoneFunction"
.IX Subsection "ConvertTimezoneFunction"
Generates a function applied to Field argument that converts timezone.
By default converts from \s-1UTC.\s0 Examples:
.PP
.Vb 2
\&    # UTC => Moscow
\&    $handle\->ConvertTimezoneFunction( Field => \*(Aq?\*(Aq, To => \*(AqEurope/Moscow\*(Aq);
.Ve
.PP
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.
.PP
Implementation is very database specific. To be portable convert
from \s-1UTC\s0 or to \s-1UTC.\s0 Some databases have internal storage for
information about timezones that should be kept up to date.
Read documentation for your \s-1DB.\s0
.SS "DateTimeIntervalFunction"
.IX Subsection "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 \*(L"CombineFunctionWithField\*(R" in DBIx::SearchBuilder.
.PP
Arguments are not quoted or escaped in any way. It's caller's job.
.SS "NullsOrder"
.IX Subsection "NullsOrder"
Sets order of NULLs when sorting columns when called with mode,
but only if \s-1DB\s0 supports it. Modes:
.IP "\(bu" 4
small
.Sp
NULLs are smaller then anything else, so come first when order
is \s-1ASC\s0 and last otherwise.
.IP "\(bu" 4
large
.Sp
NULLs are larger then anything else.
.IP "\(bu" 4
first
.Sp
NULLs are always first.
.IP "\(bu" 4
last
.Sp
NULLs are always last.
.IP "\(bu" 4
default
.Sp
Return back to \s-1DB\s0's default behaviour.
.PP
When called without argument returns metadata required to generate
\&\s-1SQL.\s0
.SS "HasSupportForNullsOrder"
.IX Subsection "HasSupportForNullsOrder"
Returns true value if \s-1DB\s0 supports adjusting NULLs order while sorting
a column, for example \f(CW\*(C`ORDER BY Value ASC NULLS FIRST\*(C'\fR.
.SS "QuoteName"
.IX Subsection "QuoteName"
Quote table or column name to avoid reserved word errors.
.PP
Returns same value passed unless over-ridden in database-specific subclass.
.SS "DequoteName"
.IX Subsection "DequoteName"
Undo the effects of QuoteName by removing quoting.
.SS "\s-1DESTROY\s0"
.IX Subsection "DESTROY"
When we get rid of the Searchbuilder::Handle, we need to disconnect from the database
.SH "AUTHOR"
.IX Header "AUTHOR"
Jesse Vincent, jesse@fsck.com
.SH "SEE ALSO"
.IX Header "SEE ALSO"
\&\fBperl\fR\|(1), DBIx::SearchBuilder