1NAME
2 Class::DBI::Sweet - Making sweet things sweeter
3
4SYNOPSIS
5 package MyApp::DBI;
6 use base 'Class::DBI::Sweet';
7 MyApp::DBI->connection('dbi:driver:dbname', 'username', 'password');
8
9 package MyApp::Article;
10 use base 'MyApp::DBI';
11
12 use DateTime;
13
14 __PACKAGE__->table('article');
15 __PACKAGE__->columns( Primary => qw[ id ] );
16 __PACKAGE__->columns( Essential => qw[ title created_on created_by ] );
17
18 __PACKAGE__->has_a(
19 created_on => 'DateTime',
20 inflate => sub { DateTime->from_epoch( epoch => shift ) },
21 deflate => sub { shift->epoch }
22 );
23
24
25 # Simple search
26
27 MyApp::Article->search( created_by => 'sri', { order_by => 'title' } );
28
29 MyApp::Article->count( created_by => 'sri' );
30
31 MyApp::Article->page( created_by => 'sri', { page => 5 } );
32
33 MyApp::Article->retrieve_all( order_by => 'created_on' );
34
35
36 # More powerful search with deflating
37
38 $criteria = {
39 created_on => {
40 -between => [
41 DateTime->new( year => 2004 ),
42 DateTime->new( year => 2005 ),
43 ]
44 },
45 created_by => [ qw(chansen draven gabb jester sri) ],
46 title => {
47 -like => [ qw( perl% catalyst% ) ]
48 }
49 };
50
51 MyApp::Article->search( $criteria, { rows => 30 } );
52
53 MyApp::Article->count($criteria);
54
55 MyApp::Article->page( $criteria, { rows => 10, page => 2 } );
56
57 MyApp::Article->retrieve_next( $criteria,
58 { order_by => 'created_on' } );
59
60 MyApp::Article->retrieve_previous( $criteria,
61 { order_by => 'created_on' } );
62
63 MyApp::Article->default_search_attributes(
64 { order_by => 'created_on' } );
65
66 # Automatic joins for search and count
67
68 MyApp::CD->has_many(tracks => 'MyApp::Track');
69 MyApp::CD->has_many(tags => 'MyApp::Tag');
70 MyApp::CD->has_a(artist => 'MyApp::Artist');
71 MyApp::CD->might_have(liner_notes
72 => 'MyApp::LinerNotes' => qw/notes/);
73
74 MyApp::Artist->search({ 'cds.year' => $cd }, # $cd->year subtituted
75 { order_by => 'artistid DESC' });
76
77 my ($tag) = $cd->tags; # Grab first tag off CD
78
79 my ($next) = $cd->retrieve_next( { 'tags.tag' => $tag },
80 { order_by => 'title' } );
81
82 MyApp::CD->search( { 'liner_notes.notes' => { "!=" => undef } } );
83
84 MyApp::CD->count(
85 { 'year' => { '>', 1998 }, 'tags.tag' => 'Cheesy',
86 'liner_notes.notes' => { 'like' => 'Buy%' } } );
87
88 # Multi-step joins
89
90 MyApp::Artist->search({ 'cds.tags.tag' => 'Shiny' });
91
92 # Retrieval with pre-loading
93
94 my ($cd) = MyApp::CD->search( { ... },
95 { prefetch => [ qw/artist liner_notes/ ] } );
96
97 $cd->artist # Pre-loaded
98
99 # Caching of resultsets (*experimental*)
100
101 __PACKAGE__->default_search_attributes( { use_resultset_cache => 1 } );
102
103DESCRIPTION
104 Class::DBI::Sweet provides convenient count, search, page, and cache
105 functions in a sweet package. It integrates these functions with
106 "Class::DBI" in a convenient and efficient way.
107
108RETRIEVING OBJECTS
109 All retrieving methods can take the same criteria and attributes.
110 Criteria is the only required parameter.
111
112 criteria
113 Can be a hash, hashref, or an arrayref. Takes the same options as the
114 SQL::Abstract "where" method. If values contain any objects, they will
115 be deflated before querying the database.
116
117 attributes
118 case, cmp, convert, and logic
119 These attributes are passed to SQL::Abstract's constuctor and alter
120 the behavior of the criteria.
121
122 { cmp => 'like' }
123
124 order_by
125 Specifies the sort order of the results.
126
127 { order_by => 'created_on DESC' }
128
129 rows
130 Specifies the maximum number of rows to return. Currently supported
131 RDBMs are Interbase, MaxDB, MySQL, PostgreSQL and SQLite. For other
132 RDBMs, it will be emulated.
133
134 { rows => 10 }
135
136 offset
137 Specifies the offset of the first row to return. Defaults to 0 if
138 unspecified.
139
140 { offset => 0 }
141
142 page
143 Specifies the current page in "page". Defaults to 1 if unspecified.
144
145 { page => 1 }
146
147 prefetch
148 Specifies a listref of relationships to prefetch. These must be
149 has_a or might_haves or Sweet will throw an error. This will cause
150 Sweet to do a join across to the related tables in order to return
151 the related object without a second trip to the database. All
152 'Essential' columns of the foreign table are retrieved.
153
154 { prefetch => [ qw/some_rel some_other_rel/ ] }
155
156 Sweet constructs the joined SQL statement by aliasing the columns in
157 each table and prefixing the column name with 'sweet__N_' where N is
158 a counter starting at 1. Note that if your database has a column
159 length limit (for example, Oracle's limit is 30) and you use long
160 column names in your application, Sweet's addition of at least 9
161 extra characters to your column name may cause database errors.
162
163 use_resultset_cache
164 Enables the resultset cache. This is a little experimental and
165 massive gotchas may rear their ugly head at some stage, but it does
166 seem to work pretty well.
167
168 For best results, the resultset cache should only be used
169 selectively on queries where you experience performance problems.
170 Enabling it for every single query in your application will most
171 likely cause a drop in performance as the cache overhead is greater
172 than simply fetching the data from the database.
173
174 profile_cache
175 Records cache hits/misses and what keys they were for in
176 ->profiling_data. Note that this is class metadata so if you don't
177 want it to be global for Sweet you need to do
178
179 __PACKAGE__->profiling_data({ });
180
181 in either your base class or your table classes to taste.
182
183 disable_sql_paging
184 Disables the use of paging in SQL statements if set, forcing Sweet
185 to emulate paging by slicing the iterator at the end of ->search
186 (which it normally only uses as a fallback mechanism). Useful for
187 testing or for causing the entire query to be retrieved initially
188 when the resultset cache is used.
189
190 This is also useful when using custom SQL via "set_sql" and setting
191 "sql_method" (see below) where a COUNT(*) may not make sense (i.e.
192 when the COUNT(*) might be as expensive as just running the full
193 query and just slicing the iterator).
194
195 sql_method
196 This sets the name of the sql fragment to use as previously set by a
197 "set_sql" call. The default name is "Join_Retrieve" and the
198 associated default sql fragment set in this class is:
199
200 __PACKAGE__->set_sql( Join_Retrieve => <<'SQL' );
201 SELECT __ESSENTIAL(me)__%s
202 FROM %s
203 WHERE %s
204 SQL
205
206 You may override this in your table or base class using the same
207 name and CDBI::Sweet will use your custom fragment, instead.
208
209 If you need to use more than one sql fragment in a given class you
210 may create a new sql fragment and then specify its name using the
211 "sql_method" attribute.
212
213 The %s strings are replaced by sql parts as described in Ima::DBI.
214 See "statement_order" for the sql part that replaces each instance
215 of %s.
216
217 In addition, the associated statment for COUNT(*) statement has
218 "_Count" appended to the sql_method name. Only "from" and "where"
219 are passed to the sprintf function.
220
221 The default sql fragment used for "Join_Retrieve" is:
222
223 __PACKAGE__->set_sql( Join_Retrieve_Count => <<'SQL' );
224 SELECT COUNT(*)
225 FROM %s
226 WHERE %s
227 SQL
228
229 If you create a custom sql method (and set the "sql_method"
230 attribute) then you will likely need to also create an associated
231 _Count fragment. If you do not have an associated _Count, and wish
232 to call the "page" method, then set "disable_sql_paging" to true and
233 your result set from the select will be spliced to return the page
234 you request.
235
236 Here's an example.
237
238 Assume a CD has_a Artist (and thus Artists have_many CDs), and you
239 wish to return a list of artists and how many CDs each have:
240
241 In package MyDB::Artist
242
243 __PACKAGE__->columns( TEMP => 'cd_count');
244
245 __PACKAGE__->set_sql( 'count_by_cd', <<'');
246 SELECT __ESSENTIAL(me)__, COUNT(cds.cdid) as cd_count
247 FROM %s -- ("from")
248 WHERE %s -- ("where")
249 GROUP BY __ESSENTIAL(me)__
250 %s %s -- ("limit" and "order_by")
251
252 Then in your application code:
253
254 my ($pager, $iterator) = MyDB::Artist->page(
255 {
256 'cds.title' => { '!=', undef },
257 },
258 {
259 sql_method => 'count_by_cd',
260 statement_order => [qw/ from where limit order_by / ],
261 disable_sql_paging => 1,
262 order_by => 'cd_count desc',
263 rows => 10,
264 page => 1,
265 } );
266
267 The above generates the following SQL:
268
269 SELECT me.artistid, me.name, COUNT(cds.cdid) as cd_count
270 FROM artist me, cd cds
271 WHERE ( cds.title IS NOT NULL ) AND me.artistid = cds.artist
272 GROUP BY me.artistid, me.name
273 ORDER BY cd_count desc
274
275 The one caveat is that Sweet cannot figure out the has_many joins
276 unless you specify them in the $criteria. In the previous example
277 that's done by asking for all cd titles that are not null (which
278 should be all).
279
280 To fetch a list like above but limited to cds that were created
281 before the year 2000, you might do:
282
283 my ($pager, $iterator) = MyDB::Artist->page(
284 {
285 'cds.year' => { '<', 2000 },
286 },
287 {
288 sql_method => 'count_by_cd',
289 statement_order => [qw/ from where limit order_by / ],
290 disable_sql_paging => 1,
291 order_by => 'cd_count desc',
292 rows => 10,
293 page => 1,
294 } );
295
296 statement_order
297 Specifies a list reference of SQL parts that are replaced in the SQL
298 fragment (which is defined with "sql_method" above). The available
299 SQL parts are:
300
301 prefetch_cols from where order_by limit sql prefetch_names
302
303 The "sql" part is shortcut notation for these three combined:
304
305 where order_by limit
306
307 Prefecch_cols are the columns selected when a prefetch is speccified
308 -- use in the SELECT. Prefetch_names are just the column names for
309 use in GROUP BY.
310
311 This is useful when statement order needs to be changed, such as
312 when using a GROUP BY:
313
314 count
315 Returns a count of the number of rows matching the criteria. "count"
316 will discard "offset", "order_by", and "rows".
317
318 $count = MyApp::Article->count(%criteria);
319
320 search
321 Returns an iterator in scalar context, or an array of objects in list
322 context.
323
324 @objects = MyApp::Article->search(%criteria);
325
326 $iterator = MyApp::Article->search(%criteria);
327
328 search_like
329 As search but adds the attribute { cmp => 'like' }.
330
331 page
332 Retuns a page object and an iterator. The page object is an instance of
333 Data::Page.
334
335 ( $page, $iterator )
336 = MyApp::Article->page( $criteria, { rows => 10, page => 2 );
337
338 printf( "Results %d - %d of %d Found\n",
339 $page->first, $page->last, $page->total_entries );
340
341 pager
342 An alias to page.
343
344 retrieve_all
345 Same as "Class::DBI" with addition that it takes "attributes" as
346 arguments, "attributes" can be a hash or a hashref.
347
348 $iterator = MyApp::Article->retrieve_all( order_by => 'created_on' );
349
350 retrieve_next
351 Returns the next record after the current one according to the order_by
352 attribute (or primary key if no order_by specified) matching the
353 criteria. Must be called as an object method.
354
355 retrieve_previous
356 As retrieve_next but retrieves the previous record.
357
358CACHING OBJECTS
359 Objects will be stored deflated in cache. Only "Primary" and "Essential"
360 columns will be cached.
361
362 cache
363 Class method: if this is set caching is enabled. Any cache object that
364 has a "get", "set", and "remove" method is supported.
365
366 __PACKAGE__->cache(
367 Cache::FastMmap->new(
368 share_file => '/tmp/cdbi',
369 expire_time => 3600
370 )
371 );
372
373 cache_key
374 Returns a cache key for an object consisting of class and primary keys.
375
376 Overloaded methods
377 _init
378 Overrides "Class::DBI"'s internal cache. On a cache hit, it will
379 return a cached object; on a cache miss it will create an new object
380 and store it in the cache.
381
382 create
383 insert
384 All caches for this table are marked stale and will be re-cached on
385 next retrieval. create is an alias kept for backwards compability.
386
387 retrieve
388 On a cache hit the object will be inflated by the "select" trigger
389 and then served.
390
391 update
392 Object is removed from the cache and will be cached on next
393 retrieval.
394
395 delete
396 Object is removed from the cache.
397
398UNIVERSALLY UNIQUE IDENTIFIERS
399 If enabled a UUID string will be generated for primary column. A
400 CHAR(36) column is suitable for storage.
401
402 __PACKAGE__->sequence('uuid');
403
404MAINTAINERS
405 Fred Moyer <fred@redhotpenguin.com>
406
407AUTHORS
408 Christian Hansen <ch@ngmedia.com>
409
410 Matt S Trout <mstrout@cpan.org>
411
412 Andy Grundman <andy@hybridized.org>
413
414THANKS TO
415 Danijel Milicevic, Jesse Sheidlower, Marcus Ramberg, Sebastian Riedel,
416 Viljo Marrandi, Bill Moseley
417
418SUPPORT
419 #catalyst on <irc://irc.perl.org>
420
421 <http://lists.rawmode.org/mailman/listinfo/catalyst>
422
423 <http://lists.rawmode.org/mailman/listinfo/catalyst-dev>
424
425LICENSE
426 This library is free software; you can redistribute it and/or modify it
427 under the same terms as Perl itself.
428
429SEE ALSO
430 Class::DBI
431
432 Data::Page
433
434 Data::UUID
435
436 SQL::Abstract
437
438 Catalyst
439
440 <http://cpan.robm.fastmail.fm/cache_perf.html> A comparison of different
441 caching modules for perl.
442
443