1DBIx::SearchBuilder::HaUnsdelre(C3o)ntributed Perl DocumDeBnItxa:t:iSoenarchBuilder::Handle(3)
2
6 DBIx::SearchBuilder::Handle - Perl extension which is a generic DBI
7 handle
8
10 use DBIx::SearchBuilder::Handle;
11 my $handle = DBIx::SearchBuilder::Handle->new();
13 $handle->Connect( Driver => 'mysql',
14 Database => 'dbname',
15 Host => 'hostname',
16 User => 'dbuser',
17 Password => 'dbpassword');
18 # now $handle isa DBIx::SearchBuilder::Handle::mysql
19
21 This class provides a wrapper for DBI handles that can also perform a
22 number of additional functions.
23 new
25 Generic constructor
26 Connect PARAMHASH: Driver, Database, Host, User, Password
28 Takes a paramhash and connects to your DBI datasource.
29 You should _always_ set
31 DisconnectHandleOnDestroy => 1
33 unless you have a legacy app like RT2 or RT 3.0.{0,1,2} that depends on
35 the broken behaviour.
36 If you created the handle with
38 DBIx::SearchBuilder::Handle->new and there is a
39 DBIx::SearchBuilder::Handle::(Driver) subclass for the driver you have
40 chosen, the handle will be automatically "upgraded" into that subclass.
41 _UpgradeHandle DRIVER
43 This private internal method turns a plain DBIx::SearchBuilder::Handle
44 into one of the standard driver-specific subclasses.
45 BuildDSN PARAMHASH
47 Takes a bunch of parameters:
48 Required: Driver, Database, Optional: Host, Port and RequireSSL
50 Builds a DSN suitable for a DBI connection
52 DSN
54 Returns the DSN for this database connection.
55 RaiseError [MODE]
57 Turns on the Database Handle's RaiseError attribute.
58 PrintError [MODE]
60 Turns on the Database Handle's PrintError attribute.
61 LogSQLStatements BOOL
63 Takes a boolean argument. If the boolean is true, SearchBuilder will
64 log all SQL statements, as well as their invocation times and execution
65 times.
66 Returns whether we're currently logging or not as a boolean
68 _LogSQLStatement STATEMENT DURATION
70 Add an SQL statement to our query log
71 ClearSQLStatementLog
73 Clears out the SQL statement log.
74 SQLStatementLog
76 Returns the current SQL statement log as an array of arrays. Each entry
77 is a triple of
78 (Time, Statement, Duration)
80 AutoCommit [MODE]
82 Turns on the Database Handle's AutoCommit attribute.
83 Disconnect
85 Disconnect from your DBI datasource
86 dbh [HANDLE]
88 Return the current DBI handle. If we're handed a parameter, make the
89 database handle that.
90 Insert $TABLE_NAME @KEY_VALUE_PAIRS
92 Takes a table name and a set of key-value pairs in an array. Splits
93 the key value pairs, constructs an INSERT statement and performs the
94 insert.
95 Base class return statement handle object, while DB specific subclass
97 should return row id.
98 InsertQueryString $TABLE_NAME @KEY_VALUE_PAIRS
100 Takes a table name and a set of key-value pairs in an array. Splits
101 the key value pairs, constructs an INSERT statement and returns query
102 string and set of bind values.
103 This method is more useful for subclassing in DB specific handles.
105 "Insert" method is preferred for end users.
106 InsertFromSelect
108 Takes table name, array reference with columns, select query and list
109 of bind values. Inserts data select by the query into the table.
110 To make sure call is portable every column in result of the query
112 should have unique name or should be aliased. See "InsertFromSelect"
113 in DBIx::SearchBuilder::Handle::Oracle for details.
114 UpdateRecordValue
116 Takes a hash with fields: Table, Column, Value PrimaryKeys, and
117 IsSQLFunction. Table, and Column should be obvious, Value is where you
118 set the new value you want the column to have. The primary_keys field
119 should be the lvalue of DBIx::SearchBuilder::Record::PrimaryKeys().
120 Finally IsSQLFunction is set when the Value is a SQL function. For
121 example, you might have ('Value'=>'PASSWORD(string)'), by setting
122 IsSQLFunction that string will be inserted into the query directly
123 rather then as a binding.
124 UpdateTableValue TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL
126 Update column COLUMN of table TABLE where the record id = RECORD_ID.
127 if IS_SQL is set, don\'t quote the NEW_VALUE
128
130 Takes table name, hash reference with (column, value) pairs, select
131 query and list of bind values.
132 Updates the table, but only records with IDs returned by the selected
134 query, eg:
135 UPDATE $table SET %values WHERE id IN ( $query )
137 It's simple as values are static and search only allowed by id.
139
141 Takes table name, select query and list of bind values.
142 Deletes from the table, but only records with IDs returned by the
144 select query, eg:
145 DELETE FROM $table WHERE id IN ($query)
147 SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]
149 Execute the SQL string specified in QUERY_STRING
150 FetchResult QUERY, [ BIND_VALUE, ... ]
152 Takes a SELECT query as a string, along with an array of BIND_VALUEs If
153 the select succeeds, returns the first row as an array. Otherwise,
154 returns a Class::ResturnValue object with the failure loaded up.
155 BinarySafeBLOBs
157 Returns 1 if the current database supports BLOBs with embedded nulls.
158 Returns undef if the current database doesn't support BLOBs with
159 embedded nulls
160 KnowsBLOBs
162 Returns 1 if the current database supports inserts of BLOBs
163 automatically. Returns undef if the current database must be informed
164 of BLOBs for inserts.
165 BLOBParams FIELD_NAME FIELD_TYPE
167 Returns a hash ref for the bind_param call to identify BLOB types used
168 by the current database for a particular column type.
169 DatabaseVersion [Short => 1]
171 Returns the database's version.
172 If argument "Short" is true returns short variant, in other case
174 returns whatever database handle/driver returns. By default returns
175 short version, e.g. '4.1.23' or '8.0-rc4'.
176 Returns empty string on error or if database couldn't return version.
178 The base implementation uses a "SELECT VERSION()"
180 CaseSensitive
182 Returns 1 if the current database's searches are case sensitive by
183 default Returns undef otherwise
184 _MakeClauseCaseInsensitive FIELD OPERATOR VALUE
186 Takes a field, operator and value. performs the magic necessary to make
187 your database treat this clause as case insensitive.
188 Returns a FIELD OPERATOR VALUE triple.
190 Transactions
192 DBIx::SearchBuilder::Handle emulates nested transactions, by keeping a
193 transaction stack depth.
194 NOTE: In nested transactions you shouldn't mix rollbacks and commits,
196 because only last action really do commit/rollback. For example next
197 code would produce desired results:
198 $handle->BeginTransaction;
200 $handle->BeginTransaction;
201 ...
202 $handle->Rollback;
203 $handle->BeginTransaction;
204 ...
205 $handle->Commit;
206 $handle->Commit;
207 Only last action(Commit in example) finilize transaction in DB.
209 BeginTransaction
211 Tells DBIx::SearchBuilder to begin a new SQL transaction. This will
213 temporarily suspend Autocommit mode.
214 EndTransaction [Action => 'commit'] [Force => 0]
216 Tells to end the current transaction. Takes "Action" argument that
218 could be "commit" or "rollback", the default value is "commit".
219 If "Force" argument is true then all nested transactions would be
221 committed or rolled back.
222 If there is no transaction in progress then method throw warning unless
224 action is forced.
225 Method returns true on success or false if error occured.
227 Commit [FORCE]
229 Tells to commit the current SQL transaction.
231 Method uses "EndTransaction" method, read its description.
233 Rollback [FORCE]
235 Tells to abort the current SQL transaction.
237 Method uses "EndTransaction" method, read its description.
239 ForceRollback
241 Force the handle to rollback. Whether or not we're deep in nested
243 transactions.
244 TransactionDepth
246 Returns the current depth of the nested transaction stack. Returns
248 "undef" if there is no connection to database.
249 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
251 takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE
252 starting with FIRST_ROW;
253 Join { Paramhash }
255 Takes a paramhash of everything Searchbuildler::Record does plus a
256 parameter called 'SearchBuilder' that contains a ref to a SearchBuilder
257 object'.
258 This performs the join.
260 MayBeNull
262 Takes a "SearchBuilder" and "ALIAS" in a hash and resturns true if
263 restrictions of the query allow NULLs in a table joined with the ALIAS,
264 otherwise returns false value which means that you can use normal join
265 instead of left for the aliased table.
266 Works only for queries have been built with "Join" in
268 DBIx::SearchBuilder and "Limit" in DBIx::SearchBuilder methods, for
269 other cases return true value to avoid fault optimizations.
270 DistinctQuery STATEMENTREF
272 takes an incomplete SQL SELECT statement and massages it to return a
273 DISTINCT result set.
274 DistinctCount STATEMENTREF
276 takes an incomplete SQL SELECT statement and massages it to return a
277 DISTINCT result set.
278 Log MESSAGE
280 Takes a single argument, a message to log.
281 Currently prints that message to STDERR
283 SimpleDateTimeFunctions
285 See "DateTimeFunction" for details on supported functions. This method
286 is for implementers of custom DB connectors.
287 Returns hash reference with (function name, sql template) pairs.
289 DateTimeFunction
291 Takes named arguments:
292 · Field - SQL expression date/time function should be applied to.
294 Note that this argument is used as is without any kind of quoting.
295 · Type - name of the function, see supported values below.
297 · Timezone - optional hash reference with From and To values, see
299 "ConvertTimezoneFunction" for details.
300 Returns SQL statement. Returns NULL if function is not supported.
302 Supported functions
304 Type value in "DateTimeFunction" is case insesitive. Spaces,
306 underscores and dashes are ignored. So 'date time', 'DateTime' and
307 'date_time' are all synonyms. The following functions are supported:
308 · date time - as is, no conversion, except applying timezone
310 conversion if it's provided.
311 · time - time only
313 · hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'
315 · hour - hour, 0 - 23
317 · date - date only
319 · daily - synonym for date
321 · day of week - 0 - 6, 0 - Sunday
323 · day - day of month, 1 - 31
325 · day of month - synonym for day
327 · day of year - 1 - 366, support is database dependent
329 · month - 1 - 12
331 · monthly - year and month prefix, e.g. '2010-11'
333 · year - e.g. '2023'
335 · annually - synonym for year
337 · week of year - 0-53, presence of zero week, 1st week meaning and
339 whether week starts on Monday or Sunday heavily depends on
340 database.
341 ConvertTimezoneFunction
343 Generates a function applied to Field argument that converts timezone.
344 By default converts from UTC. Examples:
345 # UTC => Moscow
347 $handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');
348 If there is problem with arguments or timezones are equal then Field
350 returned without any function applied. Field argument is not escaped in
351 any way, it's your job.
352 Implementation is very database specific. To be portable convert from
354 UTC or to UTC. Some databases have internal storage for information
355 about timezones that should be kept up to date. Read documentation for
356 your DB.
357 DateTimeIntervalFunction
359 Generates a function to calculate interval in seconds between two
360 dates. Takes From and To arguments which can be either scalar or a
361 hash. Hash is processed with "CombineFunctionWithField" in
362 DBIx::SearchBuilder.
363 Arguments are not quoted or escaped in any way. It's caller's job.
365 NullsOrder
367 Sets order of NULLs when sorting columns when called with mode, but
368 only if DB supports it. Modes:
369 · small
371 NULLs are smaller then anything else, so come first when order is
373 ASC and last otherwise.
374 · large
376 NULLs are larger then anything else.
378 · first
380 NULLs are always first.
382 · last
384 NULLs are always last.
386 · default
388 Return back to DB's default behaviour.
390 When called without argument returns metadata required to generate SQL.
392 HasSupportForNullsOrder
394 Returns true value if DB supports adjusting NULLs order while sorting a
395 column, for example "ORDER BY Value ASC NULLS FIRST".
396 DESTROY
398 When we get rid of the Searchbuilder::Handle, we need to disconnect
399 from the database
400
402 Jesse Vincent, jesse@fsck.com
403
405 perl(1), DBIx::SearchBuilder
406perl v5.30.0 2019-07-26 DBIx::SearchBuilder::Handle(3)