1DBIx::SearchBuilder::HaUnsdelre(C3o)ntributed Perl DocumDeBnItxa:t:iSoenarchBuilder::Handle(3)
2
3
4

NAME

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

SYNOPSIS

10         use DBIx::SearchBuilder::Handle;
11
12         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

DESCRIPTION

21       This class provides a wrapper for DBI handles that can also perform a
22       number of additional functions.
23
24   new
25       Generic constructor
26
27   Connect PARAMHASH: Driver, Database, Host, User, Password, QuoteTableNames
28       Takes a paramhash and connects to your DBI datasource.
29
30       You should _always_ set
31
32            DisconnectHandleOnDestroy => 1
33
34       unless you have a legacy app like RT2 or RT 3.0.{0,1,2} that depends on
35       the broken behaviour.
36
37       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
42       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
46   _UpgradeHandle DRIVER
47       This private internal method turns a plain DBIx::SearchBuilder::Handle
48       into one of the standard driver-specific subclasses.
49
50   BuildDSN PARAMHASH
51       Takes a bunch of parameters:
52
53       Required: Driver, Database, Optional: Host, Port and RequireSSL
54
55       Builds a DSN suitable for a DBI connection
56
57   DSN
58       Returns the DSN for this database connection.
59
60   RaiseError [MODE]
61       Turns on the Database Handle's RaiseError attribute.
62
63   PrintError [MODE]
64       Turns on the Database Handle's PrintError attribute.
65
66   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
71       Returns whether we're currently logging or not as a boolean
72
73   _LogSQLStatement STATEMENT DURATION
74       Add an SQL statement to our query log
75
76   ClearSQLStatementLog
77       Clears out the SQL statement log.
78
79   SQLStatementLog
80       Returns the current SQL statement log as an array of arrays. Each entry
81       is a triple of
82
83       (Time,  Statement, Duration)
84
85   AutoCommit [MODE]
86       Turns on the Database Handle's AutoCommit attribute.
87
88   Disconnect
89       Disconnect from your DBI datasource
90
91   dbh [HANDLE]
92       Return the current DBI handle. If we're handed a parameter, make the
93       database handle that.
94
95   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
100       Base class return statement handle object, while DB specific subclass
101       should return row id.
102
103   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
108       This method is more useful for subclassing in DB specific handles.
109       "Insert" method is preferred for end users.
110
111   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
115       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
119   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
129   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

SimpleUpdateFromSelect

134       Takes table name, hash reference with (column, value) pairs, select
135       query and list of bind values.
136
137       Updates the table, but only records with IDs returned by the selected
138       query, eg:
139
140           UPDATE $table SET %values WHERE id IN ( $query )
141
142       It's simple as values are static and search only allowed by id.
143

DeleteFromSelect

145       Takes table name, select query and list of bind values.
146
147       Deletes from the table, but only records with IDs returned by the
148       select query, eg:
149
150           DELETE FROM $table WHERE id IN ($query)
151
152   SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]
153       Execute the SQL string specified in QUERY_STRING
154
155   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
160   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
165   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
170   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
174   DatabaseVersion [Short => 1]
175       Returns the database's version.
176
177       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
181       Returns empty string on error or if database couldn't return version.
182
183       The base implementation uses a "SELECT VERSION()"
184
185   CaseSensitive
186       Returns 1 if the current database's searches are case sensitive by
187       default Returns undef otherwise
188
189   QuoteTableNames
190       Returns 1 if table names will be quoted in queries, otherwise 0
191
192   _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
196       Returns a FIELD OPERATOR VALUE triple.
197
198   Transactions
199       DBIx::SearchBuilder::Handle emulates nested transactions, by keeping a
200       transaction stack depth.
201
202       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
206         $handle->BeginTransaction;
207           $handle->BeginTransaction;
208           ...
209           $handle->Rollback;
210           $handle->BeginTransaction;
211           ...
212           $handle->Commit;
213         $handle->Commit;
214
215       Only last action(Commit in example) finilize transaction in DB.
216
217       BeginTransaction
218
219       Tells DBIx::SearchBuilder to begin a new SQL transaction.  This will
220       temporarily suspend Autocommit mode.
221
222       EndTransaction [Action => 'commit'] [Force => 0]
223
224       Tells to end the current transaction. Takes "Action" argument that
225       could be "commit" or "rollback", the default value is "commit".
226
227       If "Force" argument is true then all nested transactions would be
228       committed or rolled back.
229
230       If there is no transaction in progress then method throw warning unless
231       action is forced.
232
233       Method returns true on success or false if an error occurred.
234
235       Commit [FORCE]
236
237       Tells to commit the current SQL transaction.
238
239       Method uses "EndTransaction" method, read its description.
240
241       Rollback [FORCE]
242
243       Tells to abort the current SQL transaction.
244
245       Method uses "EndTransaction" method, read its description.
246
247       ForceRollback
248
249       Force the handle to rollback.  Whether or not we're deep in nested
250       transactions.
251
252       TransactionDepth
253
254       Returns the current depth of the nested transaction stack.  Returns
255       "undef" if there is no connection to database.
256
257   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
261   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
266       This performs the join.
267
268   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
274       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
278   DistinctQuery STATEMENTREF
279       takes an incomplete SQL SELECT statement and massages it to return a
280       DISTINCT result set.
281
282   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
286   DistinctCount STATEMENTREF
287       takes an incomplete SQL SELECT statement and massages it to return a
288       DISTINCT result set.
289
290   Log MESSAGE
291       Takes a single argument, a message to log.
292
293       Currently prints that message to STDERR
294
295   SimpleDateTimeFunctions
296       See "DateTimeFunction" for details on supported functions.  This method
297       is for implementers of custom DB connectors.
298
299       Returns hash reference with (function name, sql template) pairs.
300
301   DateTimeFunction
302       Takes named arguments:
303
304       •   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
307       •   Type - name of the function, see supported values below.
308
309       •   Timezone - optional hash reference with From and To values, see
310           "ConvertTimezoneFunction" for details.
311
312       Returns SQL statement. Returns NULL if function is not supported.
313
314       Supported functions
315
316       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
320       •   date time - as is, no conversion, except applying timezone
321           conversion if it's provided.
322
323       •   time - time only
324
325       •   hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'
326
327       •   hour - hour, 0 - 23
328
329       •   date - date only
330
331       •   daily - synonym for date
332
333       •   day of week - 0 - 6, 0 - Sunday
334
335       •   day - day of month, 1 - 31
336
337       •   day of month - synonym for day
338
339       •   day of year - 1 - 366, support is database dependent
340
341       •   month - 1 - 12
342
343       •   monthly - year and month prefix, e.g. '2010-11'
344
345       •   year - e.g. '2023'
346
347       •   annually - synonym for year
348
349       •   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
353   ConvertTimezoneFunction
354       Generates a function applied to Field argument that converts timezone.
355       By default converts from UTC. Examples:
356
357           # UTC => Moscow
358           $handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');
359
360       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
364       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
369   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
375       Arguments are not quoted or escaped in any way. It's caller's job.
376
377   NullsOrder
378       Sets order of NULLs when sorting columns when called with mode, but
379       only if DB supports it. Modes:
380
381       •   small
382
383           NULLs are smaller then anything else, so come first when order is
384           ASC and last otherwise.
385
386       •   large
387
388           NULLs are larger then anything else.
389
390       •   first
391
392           NULLs are always first.
393
394       •   last
395
396           NULLs are always last.
397
398       •   default
399
400           Return back to DB's default behaviour.
401
402       When called without argument returns metadata required to generate SQL.
403
404   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
408   HasSupportForCombineSearchAndCount
409       Returns true value if DB supports to combine search and count in single
410       query.
411
412   QuoteName
413       Quote table or column name to avoid reserved word errors.
414
415       Returns same value passed unless over-ridden in database-specific
416       subclass.
417
418   DequoteName
419       Undo the effects of QuoteName by removing quoting.
420
421   DESTROY
422       When we get rid of the Searchbuilder::Handle, we need to disconnect
423       from the database
424
425
426
427perl v5.36.3                      2023-12-04    DBIx::SearchBuilder::Handle(3)
Impressum