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   DistinctCount STATEMENTREF
283       takes an incomplete SQL SELECT statement and massages it to return a
284       DISTINCT result set.
285
286   Log MESSAGE
287       Takes a single argument, a message to log.
288
289       Currently prints that message to STDERR
290
291   SimpleDateTimeFunctions
292       See "DateTimeFunction" for details on supported functions.  This method
293       is for implementers of custom DB connectors.
294
295       Returns hash reference with (function name, sql template) pairs.
296
297   DateTimeFunction
298       Takes named arguments:
299
300       •   Field - SQL expression date/time function should be applied to.
301           Note that this argument is used as is without any kind of quoting.
302
303       •   Type - name of the function, see supported values below.
304
305       •   Timezone - optional hash reference with From and To values, see
306           "ConvertTimezoneFunction" for details.
307
308       Returns SQL statement. Returns NULL if function is not supported.
309
310       Supported functions
311
312       Type value in "DateTimeFunction" is case insesitive. Spaces,
313       underscores and dashes are ignored. So 'date time', 'DateTime' and
314       'date_time' are all synonyms. The following functions are supported:
315
316       •   date time - as is, no conversion, except applying timezone
317           conversion if it's provided.
318
319       •   time - time only
320
321       •   hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'
322
323       •   hour - hour, 0 - 23
324
325       •   date - date only
326
327       •   daily - synonym for date
328
329       •   day of week - 0 - 6, 0 - Sunday
330
331       •   day - day of month, 1 - 31
332
333       •   day of month - synonym for day
334
335       •   day of year - 1 - 366, support is database dependent
336
337       •   month - 1 - 12
338
339       •   monthly - year and month prefix, e.g. '2010-11'
340
341       •   year - e.g. '2023'
342
343       •   annually - synonym for year
344
345       •   week of year - 0-53, presence of zero week, 1st week meaning and
346           whether week starts on Monday or Sunday heavily depends on
347           database.
348
349   ConvertTimezoneFunction
350       Generates a function applied to Field argument that converts timezone.
351       By default converts from UTC. Examples:
352
353           # UTC => Moscow
354           $handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');
355
356       If there is problem with arguments or timezones are equal then Field
357       returned without any function applied. Field argument is not escaped in
358       any way, it's your job.
359
360       Implementation is very database specific. To be portable convert from
361       UTC or to UTC. Some databases have internal storage for information
362       about timezones that should be kept up to date.  Read documentation for
363       your DB.
364
365   DateTimeIntervalFunction
366       Generates a function to calculate interval in seconds between two
367       dates. Takes From and To arguments which can be either scalar or a
368       hash. Hash is processed with "CombineFunctionWithField" in
369       DBIx::SearchBuilder.
370
371       Arguments are not quoted or escaped in any way. It's caller's job.
372
373   NullsOrder
374       Sets order of NULLs when sorting columns when called with mode, but
375       only if DB supports it. Modes:
376
377       •   small
378
379           NULLs are smaller then anything else, so come first when order is
380           ASC and last otherwise.
381
382       •   large
383
384           NULLs are larger then anything else.
385
386       •   first
387
388           NULLs are always first.
389
390       •   last
391
392           NULLs are always last.
393
394       •   default
395
396           Return back to DB's default behaviour.
397
398       When called without argument returns metadata required to generate SQL.
399
400   HasSupportForNullsOrder
401       Returns true value if DB supports adjusting NULLs order while sorting a
402       column, for example "ORDER BY Value ASC NULLS FIRST".
403
404   QuoteName
405       Quote table or column name to avoid reserved word errors.
406
407       Returns same value passed unless over-ridden in database-specific
408       subclass.
409
410   DequoteName
411       Undo the effects of QuoteName by removing quoting.
412
413   DESTROY
414       When we get rid of the Searchbuilder::Handle, we need to disconnect
415       from the database
416

AUTHOR

418       Jesse Vincent, jesse@fsck.com
419

SEE ALSO

421       perl(1), DBIx::SearchBuilder
422
423
424
425perl v5.32.1                      2021-04-27    DBIx::SearchBuilder::Handle(3)
Impressum