Debian Codegraph @

README

raw
1 NAME
2 DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple perl
3 objects
4
5 SYNOPSIS
6 use DBIx::SearchBuilder;
7
8 package My::Things;
9 use base qw/DBIx::SearchBuilder/;
10
11 sub _Init {
12 my $self = shift;
13 $self->Table('Things');
14 return $self->SUPER::_Init(@_);
15 }
16
17 sub NewItem {
18 my $self = shift;
19 # MyThing is a subclass of DBIx::SearchBuilder::Record
20 return(MyThing->new);
21 }
22
23 package main;
24
25 use DBIx::SearchBuilder::Handle;
26 my $handle = DBIx::SearchBuilder::Handle->new();
27 $handle->Connect( Driver => 'SQLite', Database => "my_test_db" );
28
29 my $sb = My::Things->new( Handle => $handle );
30
31 $sb->Limit( FIELD => "column_1", VALUE => "matchstring" );
32
33 while ( my $record = $sb->Next ) {
34
35 # SearchBuilder returns the vanilla value fetched from database drivers. Note
36 # that different drivers handle the encoding differently. Check your
37 # driver's documentation to get more details.
38
39 print $record->my_column_name();
40 }
41
42 # Show the generated SELECT query, possibly for debugging
43 my $QueryString = $sb->BuildSelectQuery();
44 print "Select query is: $QueryString";
45
46 DESCRIPTION
47 This module provides an object-oriented mechanism for retrieving and
48 updating data in a DBI-accessible database.
49
50 In order to use this module, you should create a subclass of
51 "DBIx::SearchBuilder" and a subclass of "DBIx::SearchBuilder::Record"
52 for each table that you wish to access. (See the documentation of
53 "DBIx::SearchBuilder::Record" for more information on subclassing it.)
54
55 Your "DBIx::SearchBuilder" subclass must override "NewItem", and
56 probably should override at least "_Init" also; at the very least,
57 "_Init" should probably call "_Handle" and "_Table" to set the database
58 handle (a "DBIx::SearchBuilder::Handle" object) and table name for the
59 class. You can try to override just about every other method here, as
60 long as you think you know what you are doing.
61
62 METHOD NAMING
63 Each method has a lower case alias; '_' is used to separate words. For
64 example, the method "RedoSearch" has the alias "redo_search".
65
66 METHODS
67 new
68 Creates a new SearchBuilder object and immediately calls "_Init" with
69 the same parameters that were passed to "new". If you haven't overridden
70 "_Init" in your subclass, this means that you should pass in a
71 "DBIx::SearchBuilder::Handle" (or one of its subclasses) like this:
72
73 my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );
74
75 However, if your subclass overrides _Init you do not need to take a
76 Handle argument, as long as your subclass returns an appropriate handle
77 object from the "_Handle" method. This is useful if you want all of your
78 SearchBuilder objects to use a shared global handle and don't want to
79 have to explicitly pass it in each time, for example.
80
81 _Init
82 This method is called by "new" with whatever arguments were passed to
83 "new". By default, it takes a "DBIx::SearchBuilder::Handle" object as a
84 "Handle" argument, although this is not necessary if your subclass
85 overrides "_Handle".
86
87 CleanSlate
88 This completely erases all the data in the SearchBuilder object. It's
89 useful if a subclass is doing funky stuff to keep track of a search and
90 wants to reset the SearchBuilder data without losing its own data; it's
91 probably cleaner to accomplish that in a different way, though.
92
93 Clone
94 Returns copy of the current object with all search restrictions.
95
96 _ClonedAttributes
97 Returns list of the object's fields that should be copied.
98
99 If your subclass store references in the object that should be copied
100 while clonning then you probably want override this method and add own
101 values to the list.
102
103 _Handle [DBH]
104 Get or set this object's DBIx::SearchBuilder::Handle object.
105
106 _DoSearch
107 This internal private method actually executes the search on the
108 database; it is called automatically the first time that you actually
109 need results (such as a call to "Next").
110
111 AddRecord RECORD
112 Adds a record object to this collection.
113
114 _RecordCount
115 This private internal method returns the number of Record objects saved
116 as a result of the last query.
117
118 _DoCount
119 This internal private method actually executes a counting operation on
120 the database; it is used by "Count" and "CountAll".
121
122 _DoSearchAndCount
123 This internal private method actually executes the search and also
124 counting on the database;
125
126 _ApplyLimits STATEMENTREF
127 This routine takes a reference to a scalar containing an SQL statement.
128 It massages the statement to limit the returned rows to only
129 "$self->RowsPerPage" rows, skipping "$self->FirstRow" rows. (That is, if
130 rows are numbered starting from 0, row number "$self->FirstRow" will be
131 the first row returned.) Note that it probably makes no sense to set
132 these variables unless you are also enforcing an ordering on the rows
133 (with "OrderByCols", say).
134
135 _DistinctQuery STATEMENTREF
136 This routine takes a reference to a scalar containing an SQL statement.
137 It massages the statement to ensure a distinct result set is returned.
138
139 _DistinctQueryAndCount STATEMENTREF
140 This routine takes a reference to a scalar containing an SQL statement.
141 It massages the statement to ensure a distinct result set and total
142 number of potential records are returned.
143
144 _BuildJoins
145 Build up all of the joins we need to perform this query.
146
147 _isJoined
148 Returns true if this SearchBuilder will be joining multiple tables
149 together.
150
151 _isLimited
152 If we've limited down this search, return true. Otherwise, return false.
153
154 _ColumnsForQuery
155 Returns the column selection string for use in SELECT statements.
156
157 If explicit columns have been added via "Column", those are returned
158 as-is. If "_select_all_columns" is set on the object, or if the package
159 variable $DBIx::SearchBuilder::PREFER_LAZY_LOAD (controlled by the
160 "SB_PREFER_LAZY_LOAD" environment variable) is false, returns "main.*".
161 Otherwise introspects the record class's "_ClassAccessible" metadata and
162 excludes columns marked as "lazy_load", "foreign-collection", or
163 "record-read". Falls back to "main.*" if no metadata is available.
164
165 SelectAllColumns 1|0
166 When set to true, forces selection of all columns ("main.*"), bypassing
167 any "lazy_load" filtering that would otherwise apply when
168 $DBIx::SearchBuilder::PREFER_LAZY_LOAD is enabled. When set to false,
169 restores normal behaviour ("main.*" by default, or "lazy_load" filtering
170 if "PREFER_LAZY_LOAD" is enabled).
171
172 BuildSelectQuery PreferBind => 1|0
173 Builds a query string for a "SELECT rows from Tables" statement for this
174 SearchBuilder object. Used internally and can also be handy for
175 debugging. Returns the query as a string.
176
177 my $QueryString = $self->BuildSelectQuery();
178
179 If "PreferBind" is true, the generated query will use bind variables
180 where possible. If "PreferBind" is not passed, it defaults to package
181 variable $DBIx::SearchBuilder::PREFER_BIND, which defaults to
182 $ENV{SB_PREFER_BIND}.
183
184 To override global $DBIx::SearchBuilder::PREFER_BIND for current object
185 only, you can also set "_prefer_bind" accordingly, e.g.
186
187 $sb->{_prefer_bind} = 1;
188
189 BuildSelectCountQuery PreferBind => 1|0
190 Builds a SELECT statement to find the number of rows this SearchBuilder
191 object would find.
192
193 BuildSelectAndCountQuery PreferBind => 1|0
194 Builds a query string that is a combination of BuildSelectQuery and
195 BuildSelectCountQuery.
196
197 Next
198 Returns the next row from the set as an object of the type defined by
199 sub NewItem. When the complete set has been iterated through, returns
200 undef and resets the search such that the following call to Next will
201 start over with the first item retrieved from the database.
202
203 GotoFirstItem
204 Starts the recordset counter over from the first item. The next time you
205 call Next, you'll get the first item returned by the database, as if
206 you'd just started iterating through the result set.
207
208 GotoItem
209 Takes an integer N and sets the record iterator to N. The first time
210 "Next" is called afterwards, it will return the Nth item found by the
211 search.
212
213 You should only call GotoItem after you've already fetched at least one
214 result or otherwise forced the search query to run (such as via
215 "ItemsArrayRef"). If GotoItem is called before the search query is ever
216 run, it will reset the item iterator and "Next" will return the "First"
217 item.
218
219 First
220 Returns the first item
221
222 Last
223 Returns the last item
224
225 DistinctFieldValues
226 Returns list with distinct values of field. Limits on collection are
227 accounted, so collection should be "UnLimit"ed to get values from the
228 whole table.
229
230 Takes paramhash with the following keys:
231
232 Field
233 Field name. Can be first argument without key.
234
235 Order
236 'ASC', 'DESC' or undef. Defines whether results should be sorted or
237 not. By default results are not sorted.
238
239 Max Maximum number of elements to fetch.
240
241 ItemsArrayRef
242 Return a reference to an array containing all objects found by this
243 search.
244
245 NewItem
246 NewItem must be subclassed. It is used by DBIx::SearchBuilder to create
247 record objects for each row returned from the database.
248
249 RedoSearch
250 Takes no arguments. Tells DBIx::SearchBuilder that the next time it's
251 asked for a record, it should requery the database
252
253 CombineSearchAndCount 1|0
254 Tells DBIx::SearchBuilder if it shall search both records and the total
255 count in a single query.
256
257 UnLimit
258 UnLimit clears all restrictions and causes this object to return all
259 rows in the primary table.
260
261 Limit
262 Limit takes a hash of parameters with the following keys:
263
264 TABLE
265 Can be set to something different than this table if a join is
266 wanted (that means we can't do recursive joins as for now).
267
268 ALIAS
269 Unless ALIAS is set, the join criterias will be taken from
270 EXT_LINKFIELD and INT_LINKFIELD and added to the criterias. If ALIAS
271 is set, new criterias about the foreign table will be added.
272
273 LEFTJOIN
274 To apply the Limit inside the ON clause of a previously created left
275 join, pass this option along with the alias returned from creating
276 the left join. ( This is similar to using the EXPRESSION option when
277 creating a left join but this allows you to refer to the join alias
278 in the expression. )
279
280 FIELD
281 Column to be checked against.
282
283 FUNCTION
284 Function that should be checked against or applied to the FIELD
285 before check. See "CombineFunctionWithField" for rules.
286
287 VALUE
288 Should always be set and will always be quoted.
289
290 OPERATOR
291 OPERATOR is the SQL operator to use for this phrase. Possible
292 choices include:
293
294 "="
295 "!="
296 "LIKE"
297 In the case of LIKE, the string is surrounded in % signs. Yes.
298 this is a bug.
299
300 "NOT LIKE"
301 "STARTSWITH"
302 STARTSWITH is like LIKE, except it only appends a % at the end
303 of the string
304
305 "ENDSWITH"
306 ENDSWITH is like LIKE, except it prepends a % to the beginning
307 of the string
308
309 "MATCHES"
310 MATCHES is equivalent to the database's LIKE -- that is, it's
311 actually LIKE, but doesn't surround the string in % signs as
312 LIKE does.
313
314 "IN" and "NOT IN"
315 VALUE can be an array reference or an object inherited from this
316 class. If it's not then it's treated as any other operator and
317 in most cases SQL would be wrong. Values in array are considered
318 as constants and quoted according to QUOTEVALUE.
319
320 If object is passed as VALUE then its select statement is used.
321 If no "Column" is selected then "id" is used, if more than one
322 selected then warning is issued and first column is used.
323
324 ENTRYAGGREGATOR
325 Can be "AND" or "OR" (or anything else valid to aggregate two
326 clauses in SQL). Special value is "none" which means that no entry
327 aggregator should be used. The default value is "OR".
328
329 CASESENSITIVE
330 on some databases, such as postgres, setting CASESENSITIVE to 1 will
331 make this search case sensitive
332
333 SUBCLAUSE
334 Subclause allows you to assign tags to Limit statements. Statements
335 with matching SUBCLAUSE tags will be grouped together in the final
336 SQL statement.
337
338 Example:
339
340 Suppose you want to create Limit statements which would produce
341 results the same as the following SQL:
342
343 SELECT * FROM Users WHERE EmailAddress OR Name OR RealName OR Email LIKE $query;
344
345 You would use the following Limit statements:
346
347 $folks->Limit( FIELD => 'EmailAddress', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
348 $folks->Limit( FIELD => 'Name', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
349 $folks->Limit( FIELD => 'RealName', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
350
351 OrderBy PARAMHASH
352 Orders the returned results by ALIAS.FIELD ORDER.
353
354 Takes a paramhash of ALIAS, FIELD and ORDER. ALIAS defaults to "main".
355 FIELD has no default value. ORDER defaults to ASC(ending). DESC(ending)
356 is also a valid value for OrderBy.
357
358 FIELD also accepts FUNCTION(FIELD) format.
359
360 OrderByCols ARRAY
361 OrderByCols takes an array of paramhashes of the form passed to OrderBy.
362 The result set is ordered by the items in the array.
363
364 _OrderClause
365 returns the ORDER BY clause for the search.
366
367 GroupByCols ARRAY_OF_HASHES
368 Each hash contains the keys FIELD, FUNCTION and ALIAS. Hash combined
369 into SQL with "CombineFunctionWithField".
370
371 _GroupClause
372 Private function to return the "GROUP BY" clause for this query.
373
374 NewAlias
375 Takes the name of a table and paramhash with TYPE and DISTINCT.
376
377 Use TYPE equal to "LEFT" to indicate that it's LEFT JOIN. Old style way
378 to call (see below) is also supported, but should be avoided:
379
380 $records->NewAlias('aTable', 'left');
381
382 True DISTINCT value indicates that this join keeps result set distinct
383 and DB side distinct is not required. See also "Join".
384
385 Returns the string of a new Alias for that table, which can be used to
386 Join tables or to Limit what gets found by a search.
387
388 Join
389 Join instructs DBIx::SearchBuilder to join two tables.
390
391 The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2
392 and FIELD2. ALIAS1 and ALIAS2 are column aliases obtained from
393 $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are the fields in
394 ALIAS1 and ALIAS2 that should be linked, respectively. For this type of
395 join, this method has no return value.
396
397 Supplying the parameter TYPE => 'left' causes Join to preform a left
398 join. in this case, it takes ALIAS1, FIELD1, TABLE2 and FIELD2. Because
399 of the way that left joins work, this method needs a TABLE for the
400 second field rather than merely an alias. For this type of join, it will
401 return the alias generated by the join.
402
403 Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
404 ALIAS2/TABLE2 on an arbitrary expression.
405
406 It is also possible to join to a pre-existing, already-limited
407 DBIx::SearchBuilder object, by passing it as COLLECTION2, instead of
408 providing an ALIAS2 or TABLE2.
409
410 By passing true value as DISTINCT argument join can be marked distinct.
411 If all joins are distinct then whole query is distinct and SearchBuilder
412 can avoid "_DistinctQuery" call that can hurt performance of the query.
413 See also "NewAlias".
414
415 Pages: size and changing
416 Use "RowsPerPage" to set size of pages. "NextPage", "PrevPage",
417 "FirstPage" or "GotoPage" to change pages. "FirstRow" to do tricky
418 stuff.
419
420 RowsPerPage
421 Get or set the number of rows returned by the database.
422
423 Takes an optional integer which restricts the # of rows returned in a
424 result. Zero or undef argument flush back to "return all records
425 matching current conditions".
426
427 Returns the current page size.
428
429 NextPage
430 Turns one page forward.
431
432 PrevPage
433 Turns one page backwards.
434
435 FirstPage
436 Jumps to the first page.
437
438 GotoPage
439 Takes an integer number and jumps to that page or first page if number
440 omitted. Numbering starts from zero.
441
442 FirstRow
443 Get or set the first row of the result set the database should return.
444 Takes an optional single integer argrument. Returns the currently set
445 integer minus one (this is historical issue).
446
447 Usually you don't need this method. Use "RowsPerPage", "NextPage" and
448 other methods to walk pages. It only may be helpful to get 10 records
449 starting from 5th.
450
451 _ItemsCounter
452 Returns the current position in the record set.
453
454 Count
455 Returns the number of records in the set. When "RowsPerPage" is set,
456 returns number of records in the page only, otherwise the same as
457 "CountAll".
458
459 CountAll
460 Returns the total number of potential records in the set, ignoring any
461 "RowsPerPage" settings.
462
463 IsLast
464 Returns true if the current row is the last record in the set.
465
466 Column
467 Call to specify which columns should be loaded from the table. Each
468 calls adds one column to the set. Takes a hash with the following named
469 arguments:
470
471 FIELD
472 Column name to fetch or apply function to.
473
474 ALIAS
475 Alias of a table the field is in; defaults to "main"
476
477 FUNCTION
478 A SQL function that should be selected instead of FIELD or applied
479 to it.
480
481 AS The column alias to use instead of the default. The default column
482 alias is either the column's name (i.e. what is passed to FIELD) if
483 it is in this table (ALIAS is 'main') or an autogenerated alias.
484 Pass "undef" to skip column aliasing entirely.
485
486 "FIELD", "ALIAS" and "FUNCTION" are combined according to
487 "CombineFunctionWithField".
488
489 If a FIELD is provided and it is in this table (ALIAS is 'main'), then
490 the column named FIELD and can be accessed as usual by accessors:
491
492 $articles->Column(FIELD => 'id');
493 $articles->Column(FIELD => 'Subject', FUNCTION => 'SUBSTR(?, 1, 20)');
494 my $article = $articles->First;
495 my $aid = $article->id;
496 my $subject_prefix = $article->Subject;
497
498 Returns the alias used for the column. If FIELD was not provided, or was
499 from another table, then the returned column alias should be passed to
500 the "_Value" in DBIx::SearchBuilder::Record method to retrieve the
501 column's result:
502
503 my $time_alias = $articles->Column(FUNCTION => 'NOW()');
504 my $article = $articles->First;
505 my $now = $article->_Value( $time_alias );
506
507 To choose the column's alias yourself, pass a value for the AS parameter
508 (see above). Be careful not to conflict with existing column aliases.
509
510 CombineFunctionWithField
511 Takes a hash with three optional arguments: FUNCTION, FIELD and ALIAS.
512
513 Returns SQL with all three arguments combined according to the following
514 rules.
515
516 * FUNCTION or undef returned when FIELD is not provided
517
518 * 'main' ALIAS is used if not provided
519
520 * ALIAS.FIELD returned when FUNCTION is not provided
521
522 * NULL returned if FUNCTION is 'NULL'
523
524 * If FUNCTION contains '?' (question marks) then they are replaced
525 with ALIAS.FIELD and result returned.
526
527 * If FUNCTION has no '(' (opening parenthesis) then ALIAS.FIELD is
528 appended in parentheses and returned.
529
530 Examples:
531
532 $obj->CombineFunctionWithField()
533 => undef
534
535 $obj->CombineFunctionWithField(FUNCTION => 'FOO')
536 => 'FOO'
537
538 $obj->CombineFunctionWithField(FIELD => 'foo')
539 => 'main.foo'
540
541 $obj->CombineFunctionWithField(ALIAS => 'bar', FIELD => 'foo')
542 => 'bar.foo'
543
544 $obj->CombineFunctionWithField(FUNCTION => 'FOO(?, ?)', FIELD => 'bar')
545 => 'FOO(main.bar, main.bar)'
546
547 $obj->CombineFunctionWithField(FUNCTION => 'FOO', ALIAS => 'bar', FIELD => 'baz')
548 => 'FOO(bar.baz)'
549
550 $obj->CombineFunctionWithField(FUNCTION => 'NULL', FIELD => 'bar')
551 => 'NULL'
552
553 Columns LIST
554 Specify that we want to load only the columns in LIST
555
556 AdditionalColumn
557 Calls "Column", but first ensures that this table's standard columns are
558 selected as well. Thus, each call to this method results in an
559 additional column selected instead of replacing the default columns.
560
561 Takes a hash of parameters which is the same as "Column". Returns the
562 result of calling "Column".
563
564 Fields TABLE
565 Return a list of fields in TABLE. These fields are in the case presented
566 by the database, which may be case-sensitive.
567
568 HasField { TABLE => undef, FIELD => undef }
569 Returns true if TABLE has field FIELD. Return false otherwise
570
571 Note: Both TABLE and FIELD are case-sensitive (See: "Fields")
572
573 Table [TABLE]
574 If called with an argument, sets this collection's table.
575
576 Always returns this collection's table.
577
578 QueryHint [Hint]
579 If called with an argument, sets a query hint for this collection. Call
580 this method before performing additional operations on a collection,
581 such as Count(), Next(), etc.
582
583 Always returns the query hint.
584
585 When the query hint is included in the SQL query, the "/* ... */" will
586 be included for you. Here's an example query hint for Oracle:
587
588 $sb->QueryHint("+CURSOR_SHARING_EXACT");
589
590 QueryHintFormatted
591 Returns the query hint formatted appropriately for inclusion in SQL
592 queries.
593
594 DEPRECATED METHODS
595 GroupBy
596 DEPRECATED. Alias for the "GroupByCols" method.
597
598 SetTable
599 DEPRECATED. Alias for the "Table" method.
600
601 ShowRestrictions
602 DEPRECATED AND DOES NOTHING.
603
604 ImportRestrictions
605 DEPRECATED AND DOES NOTHING.
606
607 TESTING
608 In order to test most of the features of "DBIx::SearchBuilder", you need
609 to provide "make test" with a test database. For each DBI driver that
610 you would like to test, set the environment variables "SB_TEST_FOO",
611 "SB_TEST_FOO_USER", and "SB_TEST_FOO_PASS" to a database name, database
612 username, and database password, where "FOO" is the driver name in all
613 uppercase. You can test as many drivers as you like. (The appropriate
614 "DBD::" module needs to be installed in order for the test to work.)
615 Note that the "SQLite" driver will automatically be tested if
616 "DBD::Sqlite" is installed, using a temporary file as the database. For
617 example:
618
619 SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
620 SB_TEST_PG=test SB_TEST_PG_USER=postgres make test
621
622 AUTHOR
623 Best Practical Solutions, LLC <[email protected]>
624
625 CONTRIBUTORS
626 Ansgar Burchardt <[email protected]>
627 Audrey Tang <[email protected]>
628 Ivan Kohler <[email protected]>
629 Martin King <[email protected]>
630 Mathieu Arnold <[email protected]>
631 Matt Knopp <[email protected]>
632 Matthew Simon Cavalletto <[email protected]>
633 Nick Morrott <[email protected]>
634 Oliver Tappe <[email protected]>
635 Simon Cozens <[email protected]>
636
637 BUGS
638 All bugs should be reported via email to
639
640 L<[email protected]|mailto:[email protected]>
641
642 or via the web at
643
644 L<rt.cpan.org|http://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-SearchBuilder>.
645
646 LICENSE AND COPYRIGHT
647 Copyright (C) 2001-2026, Best Practical Solutions LLC.
648
649 This library is free software; you can redistribute it and/or modify it
650 under the same terms as Perl itself.
651
652 SEE ALSO
653 DBIx::SearchBuilder::Handle, DBIx::SearchBuilder::Record.
654