DBIx::SearchBuilder(3pm)

1DBIx::SearchBuilder(3)User Contributed Perl DocumentationDBIx::SearchBuilder(3)
2
3
4

NAME

6       DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple perl
7       objects
8

SYNOPSIS

10         use DBIx::SearchBuilder;
11
12         package My::Things;
13         use base qw/DBIx::SearchBuilder/;
14
15         sub _Init {
16             my $self = shift;
17             $self->Table('Things');
18             return $self->SUPER::_Init(@_);
19         }
20
21         sub NewItem {
22             my $self = shift;
23             # MyThing is a subclass of DBIx::SearchBuilder::Record
24             return(MyThing->new);
25         }
26
27         package main;
28
29         use DBIx::SearchBuilder::Handle;
30         my $handle = DBIx::SearchBuilder::Handle->new();
31         $handle->Connect( Driver => 'SQLite', Database => "my_test_db" );
32
33         my $sb = My::Things->new( Handle => $handle );
34
35         $sb->Limit( FIELD => "column_1", VALUE => "matchstring" );
36
37         while ( my $record = $sb->Next ) {
38             print $record->my_column_name();
39         }
40

DESCRIPTION

42       This module provides an object-oriented mechanism for retrieving and
43       updating data in a DBI-accesible database.
44
45       In order to use this module, you should create a subclass of
46       "DBIx::SearchBuilder" and a subclass of "DBIx::SearchBuilder::Record"
47       for each table that you wish to access.  (See the documentation of
48       "DBIx::SearchBuilder::Record" for more information on subclassing it.)
49
50       Your "DBIx::SearchBuilder" subclass must override "NewItem", and
51       probably should override at least "_Init" also; at the very least,
52       "_Init" should probably call "_Handle" and "_Table" to set the database
53       handle (a "DBIx::SearchBuilder::Handle" object) and table name for the
54       class.  You can try to override just about every other method here, as
55       long as you think you know what you are doing.
56

METHOD NAMING

58       Each method has a lower case alias; '_' is used to separate words.  For
59       example, the method "RedoSearch" has the alias "redo_search".
60

METHODS

62   new
63       Creates a new SearchBuilder object and immediately calls "_Init" with
64       the same parameters that were passed to "new".  If you haven't
65       overridden "_Init" in your subclass, this means that you should pass in
66       a "DBIx::SearchBuilder::Handle" (or one of its subclasses) like this:
67
68          my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );
69
70       However, if your subclass overrides _Init you do not need to take a
71       Handle argument, as long as your subclass returns an appropriate handle
72       object from the "_Handle" method.  This is useful if you want all of
73       your SearchBuilder objects to use a shared global handle and don't want
74       to have to explicitly pass it in each time, for example.
75
76   _Init
77       This method is called by "new" with whatever arguments were passed to
78       "new".  By default, it takes a "DBIx::SearchBuilder::Handle" object as
79       a "Handle" argument, although this is not necessary if your subclass
80       overrides "_Handle".
81
82   CleanSlate
83       This completely erases all the data in the SearchBuilder object. It's
84       useful if a subclass is doing funky stuff to keep track of a search and
85       wants to reset the SearchBuilder data without losing its own data; it's
86       probably cleaner to accomplish that in a different way, though.
87
88   Clone
89       Returns copy of the current object with all search restrictions.
90
91   _ClonedAttributes
92       Returns list of the object's fields that should be copied.
93
94       If your subclass store references in the object that should be copied
95       while clonning then you probably want override this method and add own
96       values to the list.
97
98   _Handle  [DBH]
99       Get or set this object's DBIx::SearchBuilder::Handle object.
100
101   _DoSearch
102       This internal private method actually executes the search on the
103       database; it is called automatically the first time that you actually
104       need results (such as a call to "Next").
105
106   AddRecord RECORD
107       Adds a record object to this collection.
108
109   _RecordCount
110       This private internal method returns the number of Record objects saved
111       as a result of the last query.
112
113   _DoCount
114       This internal private method actually executes a counting operation on
115       the database; it is used by "Count" and "CountAll".
116
117   _ApplyLimits STATEMENTREF
118       This routine takes a reference to a scalar containing an SQL statement.
119       It massages the statement to limit the returned rows to only
120       "$self->RowsPerPage" rows, skipping "$self->FirstRow" rows.  (That is,
121       if rows are numbered starting from 0, row number "$self->FirstRow" will
122       be the first row returned.)  Note that it probably makes no sense to
123       set these variables unless you are also enforcing an ordering on the
124       rows (with "OrderByCols", say).
125
126   _DistinctQuery STATEMENTREF
127       This routine takes a reference to a scalar containing an SQL statement.
128       It massages the statement to ensure a distinct result set is returned.
129
130   _BuildJoins
131       Build up all of the joins we need to perform this query.
132
133   _isJoined
134       Returns true if this SearchBuilder will be joining multiple tables
135       together.
136
137   _isLimited
138       If we've limited down this search, return true. Otherwise, return
139       false.
140
141   BuildSelectQuery
142       Builds a query string for a "SELECT rows from Tables" statement for
143       this SearchBuilder object
144
145   BuildSelectCountQuery
146       Builds a SELECT statement to find the number of rows this SearchBuilder
147       object would find.
148
149   Next
150       Returns the next row from the set as an object of the type defined by
151       sub NewItem.  When the complete set has been iterated through, returns
152       undef and resets the search such that the following call to Next will
153       start over with the first item retrieved from the database.
154
155   GotoFirstItem
156       Starts the recordset counter over from the first item. The next time
157       you call Next, you'll get the first item returned by the database, as
158       if you'd just started iterating through the result set.
159
160   GotoItem
161       Takes an integer N and sets the record iterator to N.  The first time
162       "Next" is called afterwards, it will return the Nth item found by the
163       search.
164
165       You should only call GotoItem after you've already fetched at least one
166       result or otherwise forced the search query to run (such as via
167       "ItemsArrayRef").  If GotoItem is called before the search query is
168       ever run, it will reset the item iterator and "Next" will return the
169       "First" item.
170
171   First
172       Returns the first item
173
174   Last
175       Returns the last item
176
177   DistinctFieldValues
178       Returns list with distinct values of field. Limits on collection are
179       accounted, so collection should be "UnLimit"ed to get values from the
180       whole table.
181
182       Takes paramhash with the following keys:
183
184       Field
185           Field name. Can be first argument without key.
186
187       Order
188           'ASC', 'DESC' or undef. Defines whether results should be sorted or
189           not. By default results are not sorted.
190
191       Max Maximum number of elements to fetch.
192
193   ItemsArrayRef
194       Return a refernece to an array containing all objects found by this
195       search.
196
197   NewItem
198       NewItem must be subclassed. It is used by DBIx::SearchBuilder to create
199       record objects for each row returned from the database.
200
201   RedoSearch
202       Takes no arguments.  Tells DBIx::SearchBuilder that the next time it's
203       asked for a record, it should requery the database
204
205   UnLimit
206       UnLimit clears all restrictions and causes this object to return all
207       rows in the primary table.
208
209   Limit
210       Limit takes a hash of parameters with the following keys:
211
212       TABLE
213           Can be set to something different than this table if a join is
214           wanted (that means we can't do recursive joins as for now).
215
216       ALIAS
217           Unless ALIAS is set, the join criterias will be taken from
218           EXT_LINKFIELD and INT_LINKFIELD and added to the criterias.  If
219           ALIAS is set, new criterias about the foreign table will be added.
220
221       LEFTJOIN
222           To apply the Limit inside the ON clause of a previously created
223           left join, pass this option along with the alias returned from
224           creating the left join. ( This is similar to using the EXPRESSION
225           option when creating a left join but this allows you to refer to
226           the join alias in the expression. )
227
228       FIELD
229           Column to be checked against.
230
231       FUNCTION
232           Function that should be checked against or applied to the FIELD
233           before check. See "CombineFunctionWithField" for rules.
234
235       VALUE
236           Should always be set and will always be quoted.
237
238       OPERATOR
239           OPERATOR is the SQL operator to use for this phrase.  Possible
240           choices include:
241
242           "="
243           "!="
244           "LIKE"
245               In the case of LIKE, the string is surrounded in % signs.  Yes.
246               this is a bug.
247
248           "NOT LIKE"
249           "STARTSWITH"
250               STARTSWITH is like LIKE, except it only appends a % at the end
251               of the string
252
253           "ENDSWITH"
254               ENDSWITH is like LIKE, except it prepends a % to the beginning
255               of the string
256
257           "MATCHES"
258               MATCHES is equivalent to the database's LIKE -- that is, it's
259               actually LIKE, but doesn't surround the string in % signs as
260               LIKE does.
261
262           "IN" and "NOT IN"
263               VALUE can be an array reference or an object inherited from
264               this class. If it's not then it's treated as any other operator
265               and in most cases SQL would be wrong. Values in array are
266               considered as constants and quoted according to QUOTEVALUE.
267
268               If object is passed as VALUE then its select statement is used.
269               If no "Column" is selected then "id" is used, if more than one
270               selected then warning is issued and first column is used.
271
272       ENTRYAGGREGATOR
273           Can be "AND" or "OR" (or anything else valid to aggregate two
274           clauses in SQL).  Special value is "none" which means that no entry
275           aggregator should be used.  The default value is "OR".
276
277       CASESENSITIVE
278           on some databases, such as postgres, setting CASESENSITIVE to 1
279           will make this search case sensitive
280
281       SUBCLAUSE
282           Subclause allows you to assign tags to Limit statements.
283           Statements with matching SUBCLAUSE tags will be grouped together in
284           the final SQL statement.
285
286           Example:
287
288           Suppose you want to create Limit statements which would produce
289           results the same as the following SQL:
290
291              SELECT * FROM Users WHERE EmailAddress OR Name OR RealName OR Email LIKE $query;
292
293           You would use the following Limit statements:
294
295               $folks->Limit( FIELD => 'EmailAddress', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
296               $folks->Limit( FIELD => 'Name', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
297               $folks->Limit( FIELD => 'RealName', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
298
299   OrderBy PARAMHASH
300       Orders the returned results by ALIAS.FIELD ORDER.
301
302       Takes a paramhash of ALIAS, FIELD and ORDER.  ALIAS defaults to "main".
303       FIELD has no default value.  ORDER defaults to ASC(ending).
304       DESC(ending) is also a valid value for OrderBy.
305
306       FIELD also accepts "FUNCTION(FIELD)" format.
307
308   OrderByCols ARRAY
309       OrderByCols takes an array of paramhashes of the form passed to
310       OrderBy.  The result set is ordered by the items in the array.
311
312   _OrderClause
313       returns the ORDER BY clause for the search.
314
315   GroupByCols ARRAY_OF_HASHES
316       Each hash contains the keys FIELD, FUNCTION and ALIAS. Hash combined
317       into SQL with "CombineFunctionWithField".
318
319   _GroupClause
320       Private function to return the "GROUP BY" clause for this query.
321
322   NewAlias
323       Takes the name of a table and paramhash with TYPE and DISTINCT.
324
325       Use TYPE equal to "LEFT" to indicate that it's LEFT JOIN. Old style way
326       to call (see below) is also supported, but should be avoided:
327
328           $records->NewAlias('aTable', 'left');
329
330       True DISTINCT value indicates that this join keeps result set distinct
331       and DB side distinct is not required. See also "Join".
332
333       Returns the string of a new Alias for that table, which can be used to
334       Join tables or to Limit what gets found by a search.
335
336   Join
337       Join instructs DBIx::SearchBuilder to join two tables.
338
339       The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2
340       and FIELD2. ALIAS1 and ALIAS2 are column aliases obtained from
341       $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are the fields in
342       ALIAS1 and ALIAS2 that should be linked, respectively.  For this type
343       of join, this method has no return value.
344
345       Supplying the parameter TYPE => 'left' causes Join to preform a left
346       join.  in this case, it takes ALIAS1, FIELD1, TABLE2 and FIELD2.
347       Because of the way that left joins work, this method needs a TABLE for
348       the second field rather than merely an alias.  For this type of join,
349       it will return the alias generated by the join.
350
351       Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
352       ALIAS2/TABLE2 on an arbitrary expression.
353
354       It is also possible to join to a pre-existing, already-limited
355       DBIx::SearchBuilder object, by passing it as COLLECTION2, instead of
356       providing an ALIAS2 or TABLE2.
357
358       By passing true value as DISTINCT argument join can be marked distinct.
359       If all joins are distinct then whole query is distinct and
360       SearchBuilder can avoid "_DistinctQuery" call that can hurt performance
361       of the query. See also "NewAlias".
362
363   Pages: size and changing
364       Use "RowsPerPage" to set size of pages. "NextPage", "PrevPage",
365       "FirstPage" or "GotoPage" to change pages. "FirstRow" to do tricky
366       stuff.
367
368       RowsPerPage
369
370       Get or set the number of rows returned by the database.
371
372       Takes an optional integer which restricts the # of rows returned in a
373       result. Zero or undef argument flush back to "return all records
374       matching current conditions".
375
376       Returns the current page size.
377
378       NextPage
379
380       Turns one page forward.
381
382       PrevPage
383
384       Turns one page backwards.
385
386       FirstPage
387
388       Jumps to the first page.
389
390       GotoPage
391
392       Takes an integer number and jumps to that page or first page if number
393       omitted. Numbering starts from zero.
394
395       FirstRow
396
397       Get or set the first row of the result set the database should return.
398       Takes an optional single integer argrument. Returns the currently set
399       integer minus one (this is historical issue).
400
401       Usually you don't need this method. Use "RowsPerPage", "NextPage" and
402       other methods to walk pages. It only may be helpful to get 10 records
403       starting from 5th.
404
405   _ItemsCounter
406       Returns the current position in the record set.
407
408   Count
409       Returns the number of records in the set.
410
411   CountAll
412       Returns the total number of potential records in the set, ignoring any
413       "RowsPerPage" settings.
414
415   IsLast
416       Returns true if the current row is the last record in the set.
417
418   Column
419       Call to specify which columns should be loaded from the table. Each
420       calls adds one column to the set.  Takes a hash with the following
421       named arguments:
422
423       FIELD
424           Column name to fetch or apply function to.
425
426       ALIAS
427           Alias of a table the field is in; defaults to "main"
428
429       FUNCTION
430           A SQL function that should be selected instead of FIELD or applied
431           to it.
432
433       AS  The column alias to use instead of the default.  The default column
434           alias is either the column's name (i.e. what is passed to FIELD) if
435           it is in this table (ALIAS is 'main') or an autogenerated alias.
436           Pass "undef" to skip column aliasing entirely.
437
438       "FIELD", "ALIAS" and "FUNCTION" are combined according to
439       "CombineFunctionWithField".
440
441       If a FIELD is provided and it is in this table (ALIAS is 'main'), then
442       the column named FIELD and can be accessed as usual by accessors:
443
444           $articles->Column(FIELD => 'id');
445           $articles->Column(FIELD => 'Subject', FUNCTION => 'SUBSTR(?, 1, 20)');
446           my $article = $articles->First;
447           my $aid = $article->id;
448           my $subject_prefix = $article->Subject;
449
450       Returns the alias used for the column. If FIELD was not provided, or
451       was from another table, then the returned column alias should be passed
452       to the "_Value" in DBIx::SearchBuilder::Record method to retrieve the
453       column's result:
454
455           my $time_alias = $articles->Column(FUNCTION => 'NOW()');
456           my $article = $articles->First;
457           my $now = $article->_Value( $time_alias );
458
459       To choose the column's alias yourself, pass a value for the AS
460       parameter (see above).  Be careful not to conflict with existing column
461       aliases.
462
463   CombineFunctionWithField
464       Takes a hash with three optional arguments: FUNCTION, FIELD and ALIAS.
465
466       Returns SQL with all three arguments combined according to the
467       following rules.
468
469       ·   FUNCTION or undef returned when FIELD is not provided
470
471       ·   'main' ALIAS is used if not provided
472
473       ·   ALIAS.FIELD returned when FUNCTION is not provided
474
475       ·   NULL returned if FUNCTION is 'NULL'
476
477       ·   If FUNCTION contains '?' (question marks) then they are replaced
478           with ALIAS.FIELD and result returned.
479
480       ·   If FUNCTION has no '(' (opening parenthesis) then ALIAS.FIELD is
481           appended in parentheses and returned.
482
483       Examples:
484
485           $obj->CombineFunctionWithField()
486            => undef
487
488           $obj->CombineFunctionWithField(FUNCTION => 'FOO')
489            => 'FOO'
490
491           $obj->CombineFunctionWithField(FIELD => 'foo')
492            => 'main.foo'
493
494           $obj->CombineFunctionWithField(ALIAS => 'bar', FIELD => 'foo')
495            => 'bar.foo'
496
497           $obj->CombineFunctionWithField(FUNCTION => 'FOO(?, ?)', FIELD => 'bar')
498            => 'FOO(main.bar, main.bar)'
499
500           $obj->CombineFunctionWithField(FUNCTION => 'FOO', ALIAS => 'bar', FIELD => 'baz')
501            => 'FOO(bar.baz)'
502
503           $obj->CombineFunctionWithField(FUNCTION => 'NULL', FIELD => 'bar')
504            => 'NULL'
505
506   Columns LIST
507       Specify that we want to load only the columns in LIST
508
509   AdditionalColumn
510       Calls "Column", but first ensures that this table's standard columns
511       are selected as well.  Thus, each call to this method results in an
512       additional column selected instead of replacing the default columns.
513
514       Takes a hash of parameters which is the same as "Column".  Returns the
515       result of calling "Column".
516
517   Fields TABLE
518       Return a list of fields in TABLE.  These fields are in the case
519       presented by the database, which may be case-sensitive.
520
521   HasField  { TABLE => undef, FIELD => undef }
522       Returns true if TABLE has field FIELD.  Return false otherwise
523
524       Note: Both TABLE and FIELD are case-sensitive (See: "Fields")
525
526   Table [TABLE]
527       If called with an argument, sets this collection's table.
528
529       Always returns this collection's table.
530
531   QueryHint [Hint]
532       If called with an argument, sets a query hint for this collection.
533
534       Always returns the query hint.
535
536       When the query hint is included in the SQL query, the "/* ... */" will
537       be included for you. Here's an example query hint for Oracle:
538
539           $sb->QueryHint("+CURSOR_SHARING_EXACT");
540
541   QueryHintFormatted
542       Returns the query hint formatted appropriately for inclusion in SQL
543       queries.
544

DEPRECATED METHODS

546   GroupBy
547       DEPRECATED. Alias for the "GroupByCols" method.
548
549   SetTable
550       DEPRECATED. Alias for the "Table" method.
551
552   ShowRestrictions
553       DEPRECATED AND DOES NOTHING.
554
555   ImportRestrictions
556       DEPRECATED AND DOES NOTHING.
557

TESTING

559       In order to test most of the features of "DBIx::SearchBuilder", you
560       need to provide "make test" with a test database.  For each DBI driver
561       that you would like to test, set the environment variables
562       "SB_TEST_FOO", "SB_TEST_FOO_USER", and "SB_TEST_FOO_PASS" to a database
563       name, database username, and database password, where "FOO" is the
564       driver name in all uppercase.  You can test as many drivers as you
565       like.  (The appropriate "DBD::" module needs to be installed in order
566       for the test to work.)  Note that the "SQLite" driver will
567       automatically be tested if "DBD::Sqlite" is installed, using a
568       temporary file as the database.  For example:
569
570         SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
571           SB_TEST_PG=test SB_TEST_PG_USER=postgres  make test
572

AUTHOR

574       Best Practical Solutions, LLC <modules@bestpractical.com>
575

BUGS

577       All bugs should be reported via email to
578
579           L<bug-DBIx-SearchBuilder@rt.cpan.org|mailto:bug-DBIx-SearchBuilder@rt.cpan.org>
580
581       or via the web at
582
583           L<rt.cpan.org|http://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-SearchBuilder>.
584

LICENSE AND COPYRIGHT

586       Copyright (C) 2001-2014, Best Practical Solutions LLC.
587
588       This library is free software; you can redistribute it and/or modify it
589       under the same terms as Perl itself.
590