1DBIx::SearchBuilder(3)User Contributed Perl DocumentationDBIx::SearchBuilder(3)
2
6 DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple perl
7 objects
8
10 use DBIx::SearchBuilder;
11 package My::Things;
13 use base qw/DBIx::SearchBuilder/;
14 sub _Init {
16 my $self = shift;
17 $self->Table('Things');
18 return $self->SUPER::_Init(@_);
19 }
20 sub NewItem {
22 my $self = shift;
23 # MyThing is a subclass of DBIx::SearchBuilder::Record
24 return(MyThing->new);
25 }
26 package main;
28 use DBIx::SearchBuilder::Handle;
30 my $handle = DBIx::SearchBuilder::Handle->new();
31 $handle->Connect( Driver => 'SQLite', Database => "my_test_db" );
32 my $sb = My::Things->new( Handle => $handle );
34 $sb->Limit( FIELD => "column_1", VALUE => "matchstring" );
36 while ( my $record = $sb->Next ) {
38 print $record->my_column_name();
39 }
40
42 This module provides an object-oriented mechanism for retrieving and
43 updating data in a DBI-accesible database.
44 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 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
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
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 my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );
69 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 _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 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 Clone
89 Returns copy of the current object with all search restrictions.
90 _ClonedAttributes
92 Returns list of the object's fields that should be copied.
93 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 _Handle [DBH]
99 Get or set this object's DBIx::SearchBuilder::Handle object.
100 _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 AddRecord RECORD
107 Adds a record object to this collection.
108 _RecordCount
110 This private internal method returns the number of Record objects saved
111 as a result of the last query.
112 _DoCount
114 This internal private method actually executes a counting operation on
115 the database; it is used by "Count" and "CountAll".
116 _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 _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 _BuildJoins
131 Build up all of the joins we need to perform this query.
132 _isJoined
134 Returns true if this SearchBuilder will be joining multiple tables
135 together.
136 _isLimited
138 If we've limited down this search, return true. Otherwise, return
139 false.
140 BuildSelectQuery
142 Builds a query string for a "SELECT rows from Tables" statement for
143 this SearchBuilder object
144 BuildSelectCountQuery
146 Builds a SELECT statement to find the number of rows this SearchBuilder
147 object would find.
148 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 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 GotoItem
161 Takes an integer, n. Sets the record counter to n. the next time you
162 call Next, you'll get the nth item.
163 First
165 Returns the first item
166 Last
168 Returns the last item
169 DistinctFieldValues
171 Returns list with distinct values of field. Limits on collection are
172 accounted, so collection should be "UnLimit"ed to get values from the
173 whole table.
174 Takes paramhash with the following keys:
176 Field
178 Field name. Can be first argument without key.
179 Order
181 'ASC', 'DESC' or undef. Defines whether results should be sorted or
182 not. By default results are not sorted.
183 Max Maximum number of elements to fetch.
185 ItemsArrayRef
187 Return a refernece to an array containing all objects found by this
188 search.
189 NewItem
191 NewItem must be subclassed. It is used by DBIx::SearchBuilder to create
192 record objects for each row returned from the database.
193 RedoSearch
195 Takes no arguments. Tells DBIx::SearchBuilder that the next time it's
196 asked for a record, it should requery the database
197 UnLimit
199 UnLimit clears all restrictions and causes this object to return all
200 rows in the primary table.
201 Limit
203 Limit takes a hash of parameters with the following keys:
204 TABLE
206 Can be set to something different than this table if a join is
207 wanted (that means we can't do recursive joins as for now).
208 ALIAS
210 Unless ALIAS is set, the join criterias will be taken from
211 EXT_LINKFIELD and INT_LINKFIELD and added to the criterias. If
212 ALIAS is set, new criterias about the foreign table will be added.
213 LEFTJOIN
215 To apply the Limit inside the ON clause of a previously created
216 left join, pass this option along with the alias returned from
217 creating the left join. ( This is similar to using the EXPRESSION
218 option when creating a left join but this allows you to refer to
219 the join alias in the expression. )
220 FIELD
222 Column to be checked against.
223 VALUE
225 Should always be set and will always be quoted.
226 OPERATOR
228 OPERATOR is the SQL operator to use for this phrase. Possible
229 choices include:
230 "="
232 "!="
233 "LIKE"
234 In the case of LIKE, the string is surrounded in % signs. Yes.
235 this is a bug.
236 "NOT LIKE"
238 "STARTSWITH"
239 STARTSWITH is like LIKE, except it only appends a % at the end
240 of the string
241 "ENDSWITH"
243 ENDSWITH is like LIKE, except it prepends a % to the beginning
244 of the string
245 "MATCHES"
247 MATCHES is equivalent to the database's LIKE -- that is, it's
248 actually LIKE, but doesn't surround the string in % signs as
249 LIKE does.
250 ENTRYAGGREGATOR
252 Can be "AND" or "OR" (or anything else valid to aggregate two
253 clauses in SQL). Special value is "none" which means that no entry
254 aggregator should be used. The default value is "OR".
255 CASESENSITIVE
257 on some databases, such as postgres, setting CASESENSITIVE to 1
258 will make this search case sensitive
259 SUBCLAUSE
261 Subclause allows you to assign tags to Limit statements.
262 Statements with matching SUBCLAUSE tags will be grouped together in
263 the final SQL statement.
264 Example:
266 Suppose you want to create Limit statments which would produce
268 results the same as the following SQL:
269 SELECT * FROM Users WHERE EmailAddress OR Name OR RealName OR Email LIKE $query;
271 You would use the following Limit statements:
273 $folks->Limit( FIELD => 'EmailAddress', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
275 $folks->Limit( FIELD => 'Name', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
276 $folks->Limit( FIELD => 'RealName', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
277 OrderBy PARAMHASH
279 Orders the returned results by ALIAS.FIELD ORDER.
280 Takes a paramhash of ALIAS, FIELD and ORDER. ALIAS defaults to "main".
282 FIELD has no default value. ORDER defaults to ASC(ending).
283 DESC(ending) is also a valid value for OrderBy.
284 FIELD also accepts "FUNCTION(FIELD)" format.
286 OrderByCols ARRAY
288 OrderByCols takes an array of paramhashes of the form passed to
289 OrderBy. The result set is ordered by the items in the array.
290 _OrderClause
292 returns the ORDER BY clause for the search.
293 GroupByCols ARRAY_OF_HASHES
295 Each hash contains the keys ALIAS and FIELD. ALIAS defaults to 'main'
296 if ignored.
297 _GroupClause
299 Private function to return the "GROUP BY" clause for this query.
300 NewAlias
302 Takes the name of a table. Returns the string of a new Alias for that
303 table, which can be used to Join tables or to Limit what gets found by
304 a search.
305 Join
307 Join instructs DBIx::SearchBuilder to join two tables.
308 The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2
310 and FIELD2. ALIAS1 and ALIAS2 are column aliases obtained from
311 $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are the fields in
312 ALIAS1 and ALIAS2 that should be linked, respectively. For this type
313 of join, this method has no return value.
314 Supplying the parameter TYPE => 'left' causes Join to preform a left
316 join. in this case, it takes ALIAS1, FIELD1, TABLE2 and FIELD2.
317 Because of the way that left joins work, this method needs a TABLE for
318 the second field rather than merely an alias. For this type of join,
319 it will return the alias generated by the join.
320 Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
322 ALIAS2/TABLE2 on an arbitrary expression.
323 It is also possible to join to a pre-existing, already-limited
325 DBIx::SearchBuilder object, by passing it as COLLECTION2, instead of
326 providing an ALIAS2 or TABLE2.
327 Pages: size and changing
329 Use "RowsPerPage" to set size of pages. "NextPage", "PrevPage",
330 "FirstPage" or "GotoPage" to change pages. "FirstRow" to do tricky
331 stuff.
332 RowsPerPage
334 Get or set the number of rows returned by the database.
336 Takes an optional integer which restricts the # of rows returned in a
338 result. Zero or undef argument flush back to "return all records
339 matching current conditions".
340 Returns the current page size.
342 NextPage
344 Turns one page forward.
346 PrevPage
348 Turns one page backwards.
350 FirstPage
352 Jumps to the first page.
354 GotoPage
356 Takes an integer number and jumps to that page or first page if number
358 ommitted. Numbering starts from zero.
359 FirstRow
361 Get or set the first row of the result set the database should return.
363 Takes an optional single integer argrument. Returns the currently set
364 integer minus one (this is historical issue).
365 Usually you don't need this method. Use "RowsPerPage", "NextPage" and
367 other methods to walk pages. It only may be helpful to get 10 records
368 starting from 5th.
369 _ItemsCounter
371 Returns the current position in the record set.
372 Count
374 Returns the number of records in the set.
375 CountAll
377 Returns the total number of potential records in the set, ignoring any
378 "RowsPerPage" settings.
379 IsLast
381 Returns true if the current row is the last record in the set.
382 Column
384 Call to specify which columns should be loaded from the table. Each
385 calls adds one column to the set. Takes a hash with the following
386 named arguments:
387 FIELD
389 Column name to fetch or apply function to. This can be omitted if
390 a FUNCTION is given which is not a function of a field.
391 ALIAS
393 Alias of a table the field is in; defaults to "main"
394 FUNCTION
396 A SQL function that should be selected as the result. If a FIELD
397 is provided, then it is inserted into the function according to the
398 following rules:
399 · If the text of the function contains a '?' (question mark),
401 then it is replaced with qualified FIELD.
402 · If the text of the function has no '(' (opening parenthesis),
404 then the qualified FIELD is appended in parentheses to the
405 text.
406 · Otherwise, the function is inserted verbatim, with no
408 substitution.
409 If a FIELD is provided and it is in this table (ALIAS is 'main'), then
411 the column named FIELD and can be accessed as usual by accessors:
412 $articles->Column(FIELD => 'id');
414 $articles->Column(FIELD => 'Subject', FUNCTION => 'SUBSTR(?, 1, 20)');
415 my $article = $articles->First;
416 my $aid = $article->id;
417 my $subject_prefix = $article->Subject;
418 Returns the alias used for the column. If FIELD was not provided, or
420 was from another table, then the returned column alias should be passed
421 to the "_Value" in DBIx::SearchBuilder::Record method to retrieve the
422 column's result:
423 my $time_alias = $articles->Column(FUNCTION => 'NOW()');
425 my $article = $articles->First;
426 my $now = $article->_Value( $time_alias );
427 Columns LIST
429 Specify that we want to load only the columns in LIST
430 Fields TABLE
432 Return a list of fields in TABLE, lowercased.
433 TODO: Why are they lowercased?
435 HasField { TABLE => undef, FIELD => undef }
437 Returns true if TABLE has field FIELD. Return false otherwise
438 Table [TABLE]
440 If called with an argument, sets this collection's table.
441 Always returns this collection's table.
443
445 GroupBy
446 DEPRECATED. Alias for the "GroupByCols" method.
447 SetTable
449 DEPRECATED. Alias for the "Table" method.
450 ShowRestrictions
452 DEPRECATED AND DOES NOTHING.
453 ImportRestrictions
455 DEPRECATED AND DOES NOTHING.
456
458 In order to test most of the features of "DBIx::SearchBuilder", you
459 need to provide "make test" with a test database. For each DBI driver
460 that you would like to test, set the environment variables
461 "SB_TEST_FOO", "SB_TEST_FOO_USER", and "SB_TEST_FOO_PASS" to a database
462 name, database username, and database password, where "FOO" is the
463 driver name in all uppercase. You can test as many drivers as you
464 like. (The appropriate "DBD::" module needs to be installed in order
465 for the test to work.) Note that the "SQLite" driver will
466 automatically be tested if "DBD::Sqlite" is installed, using a
467 temporary file as the database. For example:
468 SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
470 SB_TEST_PG=test SB_TEST_PG_USER=postgres make test
471
473 Copyright (c) 2001-2006 Jesse Vincent, jesse@bestpractical.com.
474 All rights reserved.
476 This library is free software; you can redistribute it and/or modify it
478 under the same terms as Perl itself.
479
481 DBIx::SearchBuilder::Handle, DBIx::SearchBuilder::Record.
482perl v5.12.4 2011-09-16 DBIx::SearchBuilder(3)