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, QuoteTableNames
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 QuoteTableNames option will force all table names to be quoted if the
43 driver subclass has a method for quoting implemented. The mysql
44 subclass will detect mysql version 8 and set the flag.
45 _UpgradeHandle DRIVER
47 This private internal method turns a plain DBIx::SearchBuilder::Handle
48 into one of the standard driver-specific subclasses.
49 BuildDSN PARAMHASH
51 Takes a bunch of parameters:
52 Required: Driver, Database, Optional: Host, Port and RequireSSL
54 Builds a DSN suitable for a DBI connection
56 DSN
58 Returns the DSN for this database connection.
59 RaiseError [MODE]
61 Turns on the Database Handle's RaiseError attribute.
62 PrintError [MODE]
64 Turns on the Database Handle's PrintError attribute.
65 LogSQLStatements BOOL
67 Takes a boolean argument. If the boolean is true, SearchBuilder will
68 log all SQL statements, as well as their invocation times and execution
69 times.
70 Returns whether we're currently logging or not as a boolean
72 _LogSQLStatement STATEMENT DURATION
74 Add an SQL statement to our query log
75 ClearSQLStatementLog
77 Clears out the SQL statement log.
78 SQLStatementLog
80 Returns the current SQL statement log as an array of arrays. Each entry
81 is a triple of
82 (Time, Statement, Duration)
84 AutoCommit [MODE]
86 Turns on the Database Handle's AutoCommit attribute.
87 Disconnect
89 Disconnect from your DBI datasource
90 dbh [HANDLE]
92 Return the current DBI handle. If we're handed a parameter, make the
93 database handle that.
94 Insert $TABLE_NAME @KEY_VALUE_PAIRS
96 Takes a table name and a set of key-value pairs in an array. Splits
97 the key value pairs, constructs an INSERT statement and performs the
98 insert.
99 Base class return statement handle object, while DB specific subclass
101 should return row id.
102 InsertQueryString $TABLE_NAME @KEY_VALUE_PAIRS
104 Takes a table name and a set of key-value pairs in an array. Splits
105 the key value pairs, constructs an INSERT statement and returns query
106 string and set of bind values.
107 This method is more useful for subclassing in DB specific handles.
109 "Insert" method is preferred for end users.
110 InsertFromSelect
112 Takes table name, array reference with columns, select query and list
113 of bind values. Inserts data select by the query into the table.
114 To make sure call is portable every column in result of the query
116 should have unique name or should be aliased. See "InsertFromSelect"
117 in DBIx::SearchBuilder::Handle::Oracle for details.
118 UpdateRecordValue
120 Takes a hash with fields: Table, Column, Value PrimaryKeys, and
121 IsSQLFunction. Table, and Column should be obvious, Value is where you
122 set the new value you want the column to have. The primary_keys field
123 should be the lvalue of DBIx::SearchBuilder::Record::PrimaryKeys().
124 Finally IsSQLFunction is set when the Value is a SQL function. For
125 example, you might have ('Value'=>'PASSWORD(string)'), by setting
126 IsSQLFunction that string will be inserted into the query directly
127 rather then as a binding.
128 UpdateTableValue TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL
130 Update column COLUMN of table TABLE where the record id = RECORD_ID.
131 if IS_SQL is set, don\'t quote the NEW_VALUE
132
134 Takes table name, hash reference with (column, value) pairs, select
135 query and list of bind values.
136 Updates the table, but only records with IDs returned by the selected
138 query, eg:
139 UPDATE $table SET %values WHERE id IN ( $query )
141 It's simple as values are static and search only allowed by id.
143
145 Takes table name, select query and list of bind values.
146 Deletes from the table, but only records with IDs returned by the
148 select query, eg:
149 DELETE FROM $table WHERE id IN ($query)
151 SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]
153 Execute the SQL string specified in QUERY_STRING
154 FetchResult QUERY, [ BIND_VALUE, ... ]
156 Takes a SELECT query as a string, along with an array of BIND_VALUEs If
157 the select succeeds, returns the first row as an array. Otherwise,
158 returns a Class::ResturnValue object with the failure loaded up.
159 BinarySafeBLOBs
161 Returns 1 if the current database supports BLOBs with embedded nulls.
162 Returns undef if the current database doesn't support BLOBs with
163 embedded nulls
164 KnowsBLOBs
166 Returns 1 if the current database supports inserts of BLOBs
167 automatically. Returns undef if the current database must be informed
168 of BLOBs for inserts.
169 BLOBParams FIELD_NAME FIELD_TYPE
171 Returns a hash ref for the bind_param call to identify BLOB types used
172 by the current database for a particular column type.
173 DatabaseVersion [Short => 1]
175 Returns the database's version.
176 If argument "Short" is true returns short variant, in other case
178 returns whatever database handle/driver returns. By default returns
179 short version, e.g. '4.1.23' or '8.0-rc4'.
180 Returns empty string on error or if database couldn't return version.
182 The base implementation uses a "SELECT VERSION()"
184 CaseSensitive
186 Returns 1 if the current database's searches are case sensitive by
187 default Returns undef otherwise
188 QuoteTableNames
190 Returns 1 if table names will be quoted in queries, otherwise 0
191 _MakeClauseCaseInsensitive FIELD OPERATOR VALUE
193 Takes a field, operator and value. performs the magic necessary to make
194 your database treat this clause as case insensitive.
195 Returns a FIELD OPERATOR VALUE triple.
197 Transactions
199 DBIx::SearchBuilder::Handle emulates nested transactions, by keeping a
200 transaction stack depth.
201 NOTE: In nested transactions you shouldn't mix rollbacks and commits,
203 because only last action really do commit/rollback. For example next
204 code would produce desired results:
205 $handle->BeginTransaction;
207 $handle->BeginTransaction;
208 ...
209 $handle->Rollback;
210 $handle->BeginTransaction;
211 ...
212 $handle->Commit;
213 $handle->Commit;
214 Only last action(Commit in example) finilize transaction in DB.
216 BeginTransaction
218 Tells DBIx::SearchBuilder to begin a new SQL transaction. This will
220 temporarily suspend Autocommit mode.
221 EndTransaction [Action => 'commit'] [Force => 0]
223 Tells to end the current transaction. Takes "Action" argument that
225 could be "commit" or "rollback", the default value is "commit".
226 If "Force" argument is true then all nested transactions would be
228 committed or rolled back.
229 If there is no transaction in progress then method throw warning unless
231 action is forced.
232 Method returns true on success or false if an error occurred.
234 Commit [FORCE]
236 Tells to commit the current SQL transaction.
238 Method uses "EndTransaction" method, read its description.
240 Rollback [FORCE]
242 Tells to abort the current SQL transaction.
244 Method uses "EndTransaction" method, read its description.
246 ForceRollback
248 Force the handle to rollback. Whether or not we're deep in nested
250 transactions.
251 TransactionDepth
253 Returns the current depth of the nested transaction stack. Returns
255 "undef" if there is no connection to database.
256 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW
258 takes an SQL SELECT statement and massages it to return ROWS_PER_PAGE
259 starting with FIRST_ROW;
260 Join { Paramhash }
262 Takes a paramhash of everything Searchbuildler::Record does plus a
263 parameter called 'SearchBuilder' that contains a ref to a SearchBuilder
264 object'.
265 This performs the join.
267 MayBeNull
269 Takes a "SearchBuilder" and "ALIAS" in a hash and resturns true if
270 restrictions of the query allow NULLs in a table joined with the ALIAS,
271 otherwise returns false value which means that you can use normal join
272 instead of left for the aliased table.
273 Works only for queries have been built with "Join" in
275 DBIx::SearchBuilder and "Limit" in DBIx::SearchBuilder methods, for
276 other cases return true value to avoid fault optimizations.
277 DistinctQuery STATEMENTREF
279 takes an incomplete SQL SELECT statement and massages it to return a
280 DISTINCT result set.
281 DistinctQueryAndCount STATEMENTREF
283 takes an incomplete SQL SELECT statement and massages it to return a
284 DISTINCT result set and the total count of potential records.
285 DistinctCount STATEMENTREF
287 takes an incomplete SQL SELECT statement and massages it to return a
288 DISTINCT result set.
289 Log MESSAGE
291 Takes a single argument, a message to log.
292 Currently prints that message to STDERR
294 SimpleDateTimeFunctions
296 See "DateTimeFunction" for details on supported functions. This method
297 is for implementers of custom DB connectors.
298 Returns hash reference with (function name, sql template) pairs.
300 DateTimeFunction
302 Takes named arguments:
303 • Field - SQL expression date/time function should be applied to.
305 Note that this argument is used as is without any kind of quoting.
306 • Type - name of the function, see supported values below.
308 • Timezone - optional hash reference with From and To values, see
310 "ConvertTimezoneFunction" for details.
311 Returns SQL statement. Returns NULL if function is not supported.
313 Supported functions
315 Type value in "DateTimeFunction" is case insesitive. Spaces,
317 underscores and dashes are ignored. So 'date time', 'DateTime' and
318 'date_time' are all synonyms. The following functions are supported:
319 • date time - as is, no conversion, except applying timezone
321 conversion if it's provided.
322 • time - time only
324 • hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'
326 • hour - hour, 0 - 23
328 • date - date only
330 • daily - synonym for date
332 • day of week - 0 - 6, 0 - Sunday
334 • day - day of month, 1 - 31
336 • day of month - synonym for day
338 • day of year - 1 - 366, support is database dependent
340 • month - 1 - 12
342 • monthly - year and month prefix, e.g. '2010-11'
344 • year - e.g. '2023'
346 • annually - synonym for year
348 • week of year - 0-53, presence of zero week, 1st week meaning and
350 whether week starts on Monday or Sunday heavily depends on
351 database.
352 ConvertTimezoneFunction
354 Generates a function applied to Field argument that converts timezone.
355 By default converts from UTC. Examples:
356 # UTC => Moscow
358 $handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');
359 If there is problem with arguments or timezones are equal then Field
361 returned without any function applied. Field argument is not escaped in
362 any way, it's your job.
363 Implementation is very database specific. To be portable convert from
365 UTC or to UTC. Some databases have internal storage for information
366 about timezones that should be kept up to date. Read documentation for
367 your DB.
368 DateTimeIntervalFunction
370 Generates a function to calculate interval in seconds between two
371 dates. Takes From and To arguments which can be either scalar or a
372 hash. Hash is processed with "CombineFunctionWithField" in
373 DBIx::SearchBuilder.
374 Arguments are not quoted or escaped in any way. It's caller's job.
376 NullsOrder
378 Sets order of NULLs when sorting columns when called with mode, but
379 only if DB supports it. Modes:
380 • small
382 NULLs are smaller then anything else, so come first when order is
384 ASC and last otherwise.
385 • large
387 NULLs are larger then anything else.
389 • first
391 NULLs are always first.
393 • last
395 NULLs are always last.
397 • default
399 Return back to DB's default behaviour.
401 When called without argument returns metadata required to generate SQL.
403 HasSupportForNullsOrder
405 Returns true value if DB supports adjusting NULLs order while sorting a
406 column, for example "ORDER BY Value ASC NULLS FIRST".
407 HasSupportForCombineSearchAndCount
409 Returns true value if DB supports to combine search and count in single
410 query.
411 QuoteName
413 Quote table or column name to avoid reserved word errors.
414 Returns same value passed unless over-ridden in database-specific
416 subclass.
417 DequoteName
419 Undo the effects of QuoteName by removing quoting.
420 DESTROY
422 When we get rid of the Searchbuilder::Handle, we need to disconnect
423 from the database
424perl v5.36.3 2023-12-04 DBIx::SearchBuilder::Handle(3)