DBIx::SearchBuilder(3pm)

1SearchBuilder(3)      User Contributed Perl Documentation     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 proba‐
51       bly should override at least "_Init" also; at the very least, "_Init"
52       should probably call "_Handle" and "_Table" to set the database handle
53       (a "DBIx::SearchBuilder::Handle" object) and table name for the class.
54       You can try to override just about every other method here, as long as
55       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
64       Creates a new SearchBuilder object and immediately calls "_Init" with
65       the same parameters that were passed to "new".  If you haven't overrid‐
66       den "_Init" in your subclass, this means that you should pass in a
67       "DBIx::SearchBuilder::Handle" (or one of its subclasses) like this:
68
69          my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );
70
71       However, if your subclass overrides _Init you do not need to take a
72       Handle argument, as long as your subclass returns an appropriate handle
73       object from the "_Handle" method.  This is useful if you want all of
74       your SearchBuilder objects to use a shared global handle and don't want
75       to have to explicitly pass it in each time, for example.
76
77       _Init
78
79       This method is called by "new" with whatever arguments were passed to
80       "new".  By default, it takes a "DBIx::SearchBuilder::Handle" object as
81       a "Handle" argument, although this is not necessary if your subclass
82       overrides "_Handle".
83
84       CleanSlate
85
86       This completely erases all the data in the SearchBuilder object. It's
87       useful if a subclass is doing funky stuff to keep track of a search and
88       wants to reset the SearchBuilder data without losing its own data; it's
89       probably cleaner to accomplish that in a different way, though.
90
91       Clone
92
93       Returns copy of the current object with all search restrictions.
94
95       _ClonedAttributes
96
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
105       Get or set this object's DBIx::SearchBuilder::Handle object.
106
107       _DoSearch
108
109       This internal private method actually executes the search on the data‐
110       base; it is called automatically the first time that you actually need
111       results (such as a call to "Next").
112
113       AddRecord RECORD
114
115       Adds a record object to this collection.
116
117       _RecordCount
118
119       This private internal method returns the number of Record objects saved
120       as a result of the last query.
121
122       _DoCount
123
124       This internal private method actually executes a counting operation on
125       the database; it is used by "Count" and "CountAll".
126
127       _ApplyLimits STATEMENTREF
128
129       This routine takes a reference to a scalar containing an SQL statement.
130       It massages the statement to limit the returned rows to only
131       "$self->RowsPerPage" rows, skipping "$self->FirstRow" rows.  (That is,
132       if rows are numbered starting from 0, row number "$self->FirstRow" will
133       be the first row returned.)  Note that it probably makes no sense to
134       set these variables unless you are also enforcing an ordering on the
135       rows (with "OrderByCols", say).
136
137       _DistinctQuery STATEMENTREF
138
139       This routine takes a reference to a scalar containing an SQL statement.
140       It massages the statement to ensure a distinct result set is returned.
141
142       _BuildJoins
143
144       Build up all of the joins we need to perform this query.
145
146       _isJoined
147
148       Returns true if this SearchBuilder will be joining multiple tables
149       together.
150
151       _isLimited
152
153       If we've limited down this search, return true. Otherwise, return
154       false.
155
156       BuildSelectQuery
157
158       Builds a query string for a "SELECT rows from Tables" statement for
159       this SearchBuilder object
160
161       BuildSelectCountQuery
162
163       Builds a SELECT statement to find the number of rows this SearchBuilder
164       object would find.
165
166       Next
167
168       Returns the next row from the set as an object of the type defined by
169       sub NewItem.  When the complete set has been iterated through, returns
170       undef and resets the search such that the following call to Next will
171       start over with the first item retrieved from the database.
172
173       GotoFirstItem
174
175       Starts the recordset counter over from the first item. The next time
176       you call Next, you'll get the first item returned by the database, as
177       if you'd just started iterating through the result set.
178
179       GotoItem
180
181       Takes an integer, n.  Sets the record counter to n. the next time you
182       call Next, you'll get the nth item.
183
184       First
185
186       Returns the first item
187
188       Last
189
190       Returns the last item
191
192       ItemsArrayRef
193
194       Return a refernece to an array containing all objects found by this
195       search.
196
197       NewItem
198
199       NewItem must be subclassed. It is used by DBIx::SearchBuilder to create
200       record objects for each row returned from the database.
201
202       RedoSearch
203
204       Takes no arguments.  Tells DBIx::SearchBuilder that the next time it's
205       asked for a record, it should requery the database
206
207       UnLimit
208
209       UnLimit clears all restrictions and causes this object to return all
210       rows in the primary table.
211
212       Limit
213
214       Limit takes a hash of parameters with the following keys:
215
216       TABLE
217           Can be set to something different than this table if a join is
218           wanted (that means we can't do recursive joins as for now).
219
220       ALIAS
221           Unless ALIAS is set, the join criterias will be taken from
222           EXT_LINKFIELD and INT_LINKFIELD and added to the criterias.  If
223           ALIAS is set, new criterias about the foreign table will be added.
224
225       LEFTJOIN
226           To apply the Limit inside the ON clause of a previously created
227           left join, pass this option along with the alias returned from cre‐
228           ating the left join. ( This is similar to using the EXPRESSION
229           option when creating a left join but this allows you to refer to
230           the join alias in the expression. )
231
232       FIELD
233           Column to be checked against.
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       ENTRYAGGREGATOR
263           Can be "AND" or "OR" (or anything else valid to aggregate two
264           clauses in SQL).  Special value is "none" which means that no entry
265           aggregator should be used.  The default value is "OR".
266
267       CASESENSITIVE
268           on some databases, such as postgres, setting CASESENSITIVE to 1
269           will make this search case sensitive
270
271       SUBCLAUSE
272           Subclause allows you to assign tags to Limit statements.  State‐
273           ments with matching SUBCLAUSE tags will be grouped together in the
274           final SQL statement.
275
276           Example:
277
278           Suppose you want to create Limit statments which would produce
279           results the same as the following SQL:
280
281              SELECT * FROM Users WHERE EmailAddress OR Name OR RealName OR Email LIKE $query;
282
283           You would use the following Limit statements:
284
285               $folks->Limit( FIELD => 'EmailAddress', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
286               $folks->Limit( FIELD => 'Name', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
287               $folks->Limit( FIELD => 'RealName', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
288
289       ShowRestrictions
290
291       Returns the current object's proposed WHERE clause.
292
293       Deprecated.
294
295       ImportRestrictions
296
297       Replaces the current object's WHERE clause with the string passed as
298       its argument.
299
300       Deprecated
301
302       OrderBy PARAMHASH
303
304       Orders the returned results by ALIAS.FIELD ORDER.
305
306       Takes a paramhash of ALIAS, FIELD and ORDER.  ALIAS defaults to "main".
307       FIELD has no default value.  ORDER defaults to ASC(ending). DESC(end‐
308       ing) is also a valid value for OrderBy.
309
310       FIELD also accepts "FUNCTION(FIELD)" format.
311
312       OrderByCols ARRAY
313
314       OrderByCols takes an array of paramhashes of the form passed to
315       OrderBy.  The result set is ordered by the items in the array.
316
317       _OrderClause
318
319       returns the ORDER BY clause for the search.
320
321       GroupBy  (DEPRECATED)
322
323       Alias for the GroupByCols method.
324
325       GroupByCols ARRAY_OF_HASHES
326
327       Each hash contains the keys ALIAS and FIELD. ALIAS defaults to 'main'
328       if ignored.
329
330       _GroupClause
331
332       Private function to return the "GROUP BY" clause for this query.
333
334       NewAlias
335
336       Takes the name of a table.  Returns the string of a new Alias for that
337       table, which can be used to Join tables or to Limit what gets found by
338       a search.
339
340       Join
341
342       Join instructs DBIx::SearchBuilder to join two tables.
343
344       The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2
345       and FIELD2. ALIAS1 and ALIAS2 are column aliases obtained from
346       $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are the fields in
347       ALIAS1 and ALIAS2 that should be linked, respectively.  For this type
348       of join, this method has no return value.
349
350       Supplying the parameter TYPE => 'left' causes Join to preform a left
351       join.  in this case, it takes ALIAS1, FIELD1, TABLE2 and FIELD2.
352       Because of the way that left joins work, this method needs a TABLE for
353       the second field rather than merely an alias.  For this type of join,
354       it will return the alias generated by the join.
355
356       Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
357       ALIAS2/TABLE2 on an arbitrary expression.
358
359       RowsPerPage
360
361       Limits the number of rows returned by the database.  Optionally, takes
362       an integer which restricts the # of rows returned in a result Returns
363       the number of rows the database should display.
364
365       FirstRow
366
367       Get or set the first row of the result set the database should return.
368       Takes an optional single integer argrument. Returns the currently set
369       integer first row that the database should return.
370
371       _ItemsCounter
372
373       Returns the current position in the record set.
374
375       Count
376
377       Returns the number of records in the set.
378
379       CountAll
380
381       Returns the total number of potential records in the set, ignoring any
382       LimitClause.
383
384       IsLast
385
386       Returns true if the current row is the last record in the set.
387
388       Column { FIELD => undef }
389
390       Specify that we want to load the column  FIELD.
391
392       Other parameters are TABLE ALIAS AND FUNCTION.
393
394       Autrijus and Ruslan owe docs.
395
396       Columns LIST
397
398       Specify that we want to load only the columns in LIST
399
400       Fields TABLE
401
402       Return a list of fields in TABLE, lowercased.
403
404       TODO: Why are they lowercased?
405
406       HasField  { TABLE => undef, FIELD => undef }
407
408       Returns true if TABLE has field FIELD.  Return false otherwise
409
410       Table [TABLE]
411
412       If called with an argument, sets this collection's table.
413
414       Always returns this collection's table.
415

TESTING

417       In order to test most of the features of "DBIx::SearchBuilder", you
418       need to provide "make test" with a test database.  For each DBI driver
419       that you would like to test, set the environment variables
420       "SB_TEST_FOO", "SB_TEST_FOO_USER", and "SB_TEST_FOO_PASS" to a database
421       name, database username, and database password, where "FOO" is the
422       driver name in all uppercase.  You can test as many drivers as you
423       like.  (The appropriate "DBD::" module needs to be installed in order
424       for the test to work.)  Note that the "SQLite" driver will automati‐
425       cally be tested if "DBD::Sqlite" is installed, using a temporary file
426       as the database.  For example:
427
428         SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
429           SB_TEST_PG=test SB_TEST_PG_USER=postgres  make test
430

AUTHOR

432       Copyright (c) 2001-2006 Jesse Vincent, jesse@bestpractical.com.
433
434       All rights reserved.
435
436       This library is free software; you can redistribute it and/or modify it
437       under the same terms as Perl itself.
438