DBIx::SearchBuilder::Handle(3pm)

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
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   _UpgradeHandle DRIVER
43       This private internal method turns a plain DBIx::SearchBuilder::Handle
44       into one of the standard driver-specific subclasses.
45
46   BuildDSN PARAMHASH
47       Takes a bunch of parameters:
48
49       Required: Driver, Database, Optional: Host, Port and RequireSSL
50
51       Builds a DSN suitable for a DBI connection
52
53   DSN
54       Returns the DSN for this database connection.
55
56   RaiseError [MODE]
57       Turns on the Database Handle's RaiseError attribute.
58
59   PrintError [MODE]
60       Turns on the Database Handle's PrintError attribute.
61
62   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
67       Returns whether we're currently logging or not as a boolean
68
69   _LogSQLStatement STATEMENT DURATION
70       Add an SQL statement to our query log
71
72   ClearSQLStatementLog
73       Clears out the SQL statement log.
74
75   SQLStatementLog
76       Returns the current SQL statement log as an array of arrays. Each entry
77       is a triple of
78
79       (Time,  Statement, Duration)
80
81   AutoCommit [MODE]
82       Turns on the Database Handle's AutoCommit attribute.
83
84   Disconnect
85       Disconnect from your DBI datasource
86
87   dbh [HANDLE]
88       Return the current DBI handle. If we're handed a parameter, make the
89       database handle that.
90
91   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
96       Base class return statement handle object, while DB specific subclass
97       should return row id.
98
99   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
104       This method is more useful for subclassing in DB specific handles.
105       "Insert" method is prefered for end users.
106
107   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
111       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
115   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
125   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

SimpleUpdateFromSelect

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

DeleteFromSelect

141       Takes table name, select query and list of bind values.
142
143       Deletes from the table, but only records with IDs returned by the
144       select query, eg:
145
146           DELETE FROM $table WHERE id IN ($query)
147
148   SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]
149       Execute the SQL string specified in QUERY_STRING
150
151   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
156   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
161   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
166   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
170   DatabaseVersion [Short => 1]
171       Returns the database's version.
172
173       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
177       Returns empty string on error or if database couldn't return version.
178
179       The base implementation uses a "SELECT VERSION()"
180
181   CaseSensitive
182       Returns 1 if the current database's searches are case sensitive by
183       default Returns undef otherwise
184
185   _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
189       Returns a FIELD OPERATOR VALUE triple.
190
191   Transactions
192       DBIx::SearchBuilder::Handle emulates nested transactions, by keeping a
193       transaction stack depth.
194
195       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
199         $handle->BeginTransaction;
200           $handle->BeginTransaction;
201           ...
202           $handle->Rollback;
203           $handle->BeginTransaction;
204           ...
205           $handle->Commit;
206         $handle->Commit;
207
208       Only last action(Commit in example) finilize transaction in DB.
209
210       BeginTransaction
211
212       Tells DBIx::SearchBuilder to begin a new SQL transaction.  This will
213       temporarily suspend Autocommit mode.
214
215       EndTransaction [Action => 'commit'] [Force => 0]
216
217       Tells to end the current transaction. Takes "Action" argument that
218       could be "commit" or "rollback", the default value is "commit".
219
220       If "Force" argument is true then all nested transactions would be
221       committed or rolled back.
222
223       If there is no transaction in progress then method throw warning unless
224       action is forced.
225
226       Method returns true on success or false if error occured.
227
228       Commit [FORCE]
229
230       Tells to commit the current SQL transaction.
231
232       Method uses "EndTransaction" method, read its description.
233
234       Rollback [FORCE]
235
236       Tells to abort the current SQL transaction.
237
238       Method uses "EndTransaction" method, read its description.
239
240       ForceRollback
241
242       Force the handle to rollback.  Whether or not we're deep in nested
243       transactions.
244
245       TransactionDepth
246
247       Returns the current depth of the nested transaction stack.  Returns
248       "undef" if there is no connection to database.
249
250   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
254   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
259       This performs the join.
260
261   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
267       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
271   DistinctQuery STATEMENTREF
272       takes an incomplete SQL SELECT statement and massages it to return a
273       DISTINCT result set.
274
275   DistinctCount STATEMENTREF
276       takes an incomplete SQL SELECT statement and massages it to return a
277       DISTINCT result set.
278
279   Log MESSAGE
280       Takes a single argument, a message to log.
281
282       Currently prints that message to STDERR
283
284   SimpleDateTimeFunctions
285       See "DateTimeFunction" for details on supported functions.  This method
286       is for implementers of custom DB connectors.
287
288       Returns hash reference with (function name, sql template) pairs.
289
290   DateTimeFunction
291       Takes named arguments:
292
293       ·   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
296       ·   Type - name of the function, see supported values below.
297
298       ·   Timezone - optional hash reference with From and To values, see
299           "ConvertTimezoneFunction" for details.
300
301       Returns SQL statement. Returns NULL if function is not supported.
302
303       Supported functions
304
305       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
309       ·   date time - as is, no conversion, except applying timezone
310           conversion if it's provided.
311
312       ·   time - time only
313
314       ·   hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'
315
316       ·   hour - hour, 0 - 23
317
318       ·   date - date only
319
320       ·   daily - synonym for date
321
322       ·   day of week - 0 - 6, 0 - Sunday
323
324       ·   day - day of month, 1 - 31
325
326       ·   day of month - synonym for day
327
328       ·   day of year - 1 - 366, support is database dependent
329
330       ·   month - 1 - 12
331
332       ·   monthly - year and month prefix, e.g. '2010-11'
333
334       ·   year - e.g. '2023'
335
336       ·   annually - synonym for year
337
338       ·   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
342   ConvertTimezoneFunction
343       Generates a function applied to Field argument that converts timezone.
344       By default converts from UTC. Examples:
345
346           # UTC => Moscow
347           $handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');
348
349       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
353       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
358   DESTROY
359       When we get rid of the Searchbuilder::Handle, we need to disconnect
360       from the database
361

AUTHOR

363       Jesse Vincent, jesse@fsck.com
364

NAME

SYNOPSIS

DESCRIPTION

SimpleUpdateFromSelect

DeleteFromSelect

AUTHOR

SEE ALSO