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

DEPRECATED METHODS

556   GroupBy
557       DEPRECATED. Alias for the "GroupByCols" method.
558
559   SetTable
560       DEPRECATED. Alias for the "Table" method.
561
562   ShowRestrictions
563       DEPRECATED AND DOES NOTHING.
564
565   ImportRestrictions
566       DEPRECATED AND DOES NOTHING.
567

TESTING

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

AUTHOR

584       Best Practical Solutions, LLC <modules@bestpractical.com>
585

BUGS

587       All bugs should be reported via email to
588
589           L<bug-DBIx-SearchBuilder@rt.cpan.org|mailto:bug-DBIx-SearchBuilder@rt.cpan.org>
590
591       or via the web at
592
593           L<rt.cpan.org|http://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-SearchBuilder>.
594

LICENSE AND COPYRIGHT

596       Copyright (C) 2001-2014, Best Practical Solutions LLC.
597
598       This library is free software; you can redistribute it and/or modify it
599       under the same terms as Perl itself.
600