.\" Automatically generated by Pod::Man 2.28 (Pod::Simple 3.28) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is turned on, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{ . if \nF \{ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). .\" Fear. Run. Save yourself. No user-serviceable parts. . \" fudge factors for nroff and troff .if n \{\ . ds #H 0 . ds #V .8m . ds #F .3m . ds #[ \f1 . ds #] \fP .\} .if t \{\ . ds #H ((1u-(\\\\n(.fu%2u))*.13m) . ds #V .6m . ds #F 0 . ds #[ \& . ds #] \& .\} . \" simple accents for nroff and troff .if n \{\ . ds ' \& . ds ` \& . ds ^ \& . ds , \& . ds ~ ~ . ds / .\} .if t \{\ . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' .\} . \" troff and (daisy-wheel) nroff accents .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' .ds 8 \h'\*(#H'\(*b\h'-\*(#H' .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] .ds ae a\h'-(\w'a'u*4/10)'e .ds Ae A\h'-(\w'A'u*4/10)'E . \" corrections for vroff .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' . \" for low resolution devices (crt and lpr) .if \n(.H>23 .if \n(.V>19 \ \{\ . ds : e . ds 8 ss . ds o a . ds d- d\h'-1'\(ga . ds D- D\h'-1'\(hy . ds th \o'bp' . ds Th \o'LP' . ds ae ae . ds Ae AE .\} .rm #[ #] #H #V #F C .\" ======================================================================== .\" .IX Title "Class::DBI::Sweet 3pm" .TH Class::DBI::Sweet 3pm "2015-06-08" "perl v5.20.2" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" .Vb 1 \& Class::DBI::Sweet \- Making sweet things sweeter .Ve .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 3 \& package MyApp::DBI; \& use base \*(AqClass::DBI::Sweet\*(Aq; \& MyApp::DBI\->connection(\*(Aqdbi:driver:dbname\*(Aq, \*(Aqusername\*(Aq, \*(Aqpassword\*(Aq); \& \& package MyApp::Article; \& use base \*(AqMyApp::DBI\*(Aq; \& \& use DateTime; \& \& _\|_PACKAGE_\|_\->table(\*(Aqarticle\*(Aq); \& _\|_PACKAGE_\|_\->columns( Primary => qw[ id ] ); \& _\|_PACKAGE_\|_\->columns( Essential => qw[ title created_on created_by ] ); \& \& _\|_PACKAGE_\|_\->has_a( \& created_on => \*(AqDateTime\*(Aq, \& inflate => sub { DateTime\->from_epoch( epoch => shift ) }, \& deflate => sub { shift\->epoch } \& ); \& \& \& # Simple search \& \& MyApp::Article\->search( created_by => \*(Aqsri\*(Aq, { order_by => \*(Aqtitle\*(Aq } ); \& \& MyApp::Article\->count( created_by => \*(Aqsri\*(Aq ); \& \& MyApp::Article\->page( created_by => \*(Aqsri\*(Aq, { page => 5 } ); \& \& MyApp::Article\->retrieve_all( order_by => \*(Aqcreated_on\*(Aq ); \& \& \& # More powerful search with deflating \& \& $criteria = { \& created_on => { \& \-between => [ \& DateTime\->new( year => 2004 ), \& DateTime\->new( year => 2005 ), \& ] \& }, \& created_by => [ qw(chansen draven gabb jester sri) ], \& title => { \& \-like => [ qw( perl% catalyst% ) ] \& } \& }; \& \& MyApp::Article\->search( $criteria, { rows => 30 } ); \& \& MyApp::Article\->count($criteria); \& \& MyApp::Article\->page( $criteria, { rows => 10, page => 2 } ); \& \& MyApp::Article\->retrieve_next( $criteria, \& { order_by => \*(Aqcreated_on\*(Aq } ); \& \& MyApp::Article\->retrieve_previous( $criteria, \& { order_by => \*(Aqcreated_on\*(Aq } ); \& \& MyApp::Article\->default_search_attributes( \& { order_by => \*(Aqcreated_on\*(Aq } ); \& \& # Automatic joins for search and count \& \& MyApp::CD\->has_many(tracks => \*(AqMyApp::Track\*(Aq); \& MyApp::CD\->has_many(tags => \*(AqMyApp::Tag\*(Aq); \& MyApp::CD\->has_a(artist => \*(AqMyApp::Artist\*(Aq); \& MyApp::CD\->might_have(liner_notes \& => \*(AqMyApp::LinerNotes\*(Aq => qw/notes/); \& \& MyApp::Artist\->search({ \*(Aqcds.year\*(Aq => $cd }, # $cd\->year subtituted \& { order_by => \*(Aqartistid DESC\*(Aq }); \& \& my ($tag) = $cd\->tags; # Grab first tag off CD \& \& my ($next) = $cd\->retrieve_next( { \*(Aqtags.tag\*(Aq => $tag }, \& { order_by => \*(Aqtitle\*(Aq } ); \& \& MyApp::CD\->search( { \*(Aqliner_notes.notes\*(Aq => { "!=" => undef } } ); \& \& MyApp::CD\->count( \& { \*(Aqyear\*(Aq => { \*(Aq>\*(Aq, 1998 }, \*(Aqtags.tag\*(Aq => \*(AqCheesy\*(Aq, \& \*(Aqliner_notes.notes\*(Aq => { \*(Aqlike\*(Aq => \*(AqBuy%\*(Aq } } ); \& \& # Multi\-step joins \& \& MyApp::Artist\->search({ \*(Aqcds.tags.tag\*(Aq => \*(AqShiny\*(Aq }); \& \& # Retrieval with pre\-loading \& \& my ($cd) = MyApp::CD\->search( { ... }, \& { prefetch => [ qw/artist liner_notes/ ] } ); \& \& $cd\->artist # Pre\-loaded \& \& # Caching of resultsets (*experimental*) \& \& _\|_PACKAGE_\|_\->default_search_attributes( { use_resultset_cache => 1 } ); .Ve .SH "DESCRIPTION" .IX Header "DESCRIPTION" Class::DBI::Sweet provides convenient count, search, page, and cache functions in a sweet package. It integrates these functions with \&\f(CW\*(C`Class::DBI\*(C'\fR in a convenient and efficient way. .SH "RETRIEVING OBJECTS" .IX Header "RETRIEVING OBJECTS" All retrieving methods can take the same criteria and attributes. Criteria is the only required parameter. .SS "criteria" .IX Subsection "criteria" Can be a hash, hashref, or an arrayref. Takes the same options as the SQL::Abstract \f(CW\*(C`where\*(C'\fR method. If values contain any objects, they will be deflated before querying the database. .SS "attributes" .IX Subsection "attributes" .IP "case, cmp, convert, and logic" 4 .IX Item "case, cmp, convert, and logic" These attributes are passed to SQL::Abstract's constuctor and alter the behavior of the criteria. .Sp .Vb 1 \& { cmp => \*(Aqlike\*(Aq } .Ve .IP "order_by" 4 .IX Item "order_by" Specifies the sort order of the results. .Sp .Vb 1 \& { order_by => \*(Aqcreated_on DESC\*(Aq } .Ve .IP "rows" 4 .IX Item "rows" Specifies the maximum number of rows to return. Currently supported RDBMs are Interbase, MaxDB, MySQL, PostgreSQL and SQLite. For other RDBMs, it will be emulated. .Sp .Vb 1 \& { rows => 10 } .Ve .IP "offset" 4 .IX Item "offset" Specifies the offset of the first row to return. Defaults to 0 if unspecified. .Sp .Vb 1 \& { offset => 0 } .Ve .IP "page" 4 .IX Item "page" Specifies the current page in \f(CW\*(C`page\*(C'\fR. Defaults to 1 if unspecified. .Sp .Vb 1 \& { page => 1 } .Ve .IP "prefetch" 4 .IX Item "prefetch" Specifies a listref of relationships to prefetch. These must be has_a or might_haves or Sweet will throw an error. This will cause Sweet to do a join across to the related tables in order to return the related object without a second trip to the database. All 'Essential' columns of the foreign table are retrieved. .Sp .Vb 1 \& { prefetch => [ qw/some_rel some_other_rel/ ] } .Ve .Sp Sweet constructs the joined \s-1SQL\s0 statement by aliasing the columns in each table and prefixing the column name with 'sweet_\|_N_' where N is a counter starting at 1. Note that if your database has a column length limit (for example, Oracle's limit is 30) and you use long column names in your application, Sweet's addition of at least 9 extra characters to your column name may cause database errors. .IP "use_resultset_cache" 4 .IX Item "use_resultset_cache" Enables the resultset cache. This is a little experimental and massive gotchas may rear their ugly head at some stage, but it does seem to work pretty well. .Sp For best results, the resultset cache should only be used selectively on queries where you experience performance problems. Enabling it for every single query in your application will most likely cause a drop in performance as the cache overhead is greater than simply fetching the data from the database. .IP "profile_cache" 4 .IX Item "profile_cache" Records cache hits/misses and what keys they were for in \->profiling_data. Note that this is class metadata so if you don't want it to be global for Sweet you need to do .Sp .Vb 1 \& _\|_PACKAGE_\|_\->profiling_data({ }); .Ve .Sp in either your base class or your table classes to taste. .IP "disable_sql_paging" 4 .IX Item "disable_sql_paging" Disables the use of paging in \s-1SQL\s0 statements if set, forcing Sweet to emulate paging by slicing the iterator at the end of \->search (which it normally only uses as a fallback mechanism). Useful for testing or for causing the entire query to be retrieved initially when the resultset cache is used. .Sp This is also useful when using custom \s-1SQL\s0 via \f(CW\*(C`set_sql\*(C'\fR and setting \&\f(CW\*(C`sql_method\*(C'\fR (see below) where a \s-1COUNT\s0(*) may not make sense (i.e. when the \s-1COUNT\s0(*) might be as expensive as just running the full query and just slicing the iterator). .IP "sql_method" 4 .IX Item "sql_method" This sets the name of the sql fragment to use as previously set by a \&\f(CW\*(C`set_sql\*(C'\fR call. The default name is \*(L"Join_Retrieve\*(R" and the associated default sql fragment set in this class is: .Sp .Vb 5 \& _\|_PACKAGE_\|_\->set_sql( Join_Retrieve => <<\*(AqSQL\*(Aq ); \& SELECT _\|_ESSENTIAL(me)_\|_%s \& FROM %s \& WHERE %s \& SQL .Ve .Sp You may override this in your table or base class using the same name and CDBI::Sweet will use your custom fragment, instead. .Sp If you need to use more than one sql fragment in a given class you may create a new sql fragment and then specify its name using the \f(CW\*(C`sql_method\*(C'\fR attribute. .Sp The \f(CW%s\fR strings are replaced by sql parts as described in Ima::DBI. See \&\*(L"statement_order\*(R" for the sql part that replaces each instance of \f(CW%s\fR. .Sp In addition, the associated statment for \s-1COUNT\s0(*) statement has \*(L"_Count\*(R" appended to the sql_method name. Only \*(L"from\*(R" and \*(L"where\*(R" are passed to the sprintf function. .Sp The default sql fragment used for \*(L"Join_Retrieve\*(R" is: .Sp .Vb 5 \& _\|_PACKAGE_\|_\->set_sql( Join_Retrieve_Count => <<\*(AqSQL\*(Aq ); \& SELECT COUNT(*) \& FROM %s \& WHERE %s \& SQL .Ve .Sp If you create a custom sql method (and set the \f(CW\*(C`sql_method\*(C'\fR attribute) then you will likely need to also create an associated _Count fragment. If you do not have an associated _Count, and wish to call the \f(CW\*(C`page\*(C'\fR method, then set \&\f(CW\*(C`disable_sql_paging\*(C'\fR to true and your result set from the select will be spliced to return the page you request. .Sp Here's an example. .Sp Assume a \s-1CD\s0 has_a Artist (and thus Artists have_many CDs), and you wish to return a list of artists and how many CDs each have: .Sp In package MyDB::Artist .Sp .Vb 1 \& _\|_PACKAGE_\|_\->columns( TEMP => \*(Aqcd_count\*(Aq); \& \& _\|_PACKAGE_\|_\->set_sql( \*(Aqcount_by_cd\*(Aq, <<\*(Aq\*(Aq); \& SELECT _\|_ESSENTIAL(me)_\|_, COUNT(cds.cdid) as cd_count \& FROM %s \-\- ("from") \& WHERE %s \-\- ("where") \& GROUP BY _\|_ESSENTIAL(me)_\|_ \& %s %s \-\- ("limit" and "order_by") .Ve .Sp Then in your application code: .Sp .Vb 12 \& my ($pager, $iterator) = MyDB::Artist\->page( \& { \& \*(Aqcds.title\*(Aq => { \*(Aq!=\*(Aq, undef }, \& }, \& { \& sql_method => \*(Aqcount_by_cd\*(Aq, \& statement_order => [qw/ from where limit order_by / ], \& disable_sql_paging => 1, \& order_by => \*(Aqcd_count desc\*(Aq, \& rows => 10, \& page => 1, \& } ); .Ve .Sp The above generates the following \s-1SQL:\s0 .Sp .Vb 5 \& SELECT me.artistid, me.name, COUNT(cds.cdid) as cd_count \& FROM artist me, cd cds \& WHERE ( cds.title IS NOT NULL ) AND me.artistid = cds.artist \& GROUP BY me.artistid, me.name \& ORDER BY cd_count desc .Ve .Sp The one caveat is that Sweet cannot figure out the has_many joins unless you specify them in the \f(CW$criteria\fR. In the previous example that's done by asking for all cd titles that are not null (which should be all). .Sp To fetch a list like above but limited to cds that were created before the year 2000, you might do: .Sp .Vb 12 \& my ($pager, $iterator) = MyDB::Artist\->page( \& { \& \*(Aqcds.year\*(Aq => { \*(Aq<\*(Aq, 2000 }, \& }, \& { \& sql_method => \*(Aqcount_by_cd\*(Aq, \& statement_order => [qw/ from where limit order_by / ], \& disable_sql_paging => 1, \& order_by => \*(Aqcd_count desc\*(Aq, \& rows => 10, \& page => 1, \& } ); .Ve .IP "statement_order" 4 .IX Item "statement_order" Specifies a list reference of \s-1SQL\s0 parts that are replaced in the \s-1SQL\s0 fragment (which is defined with \*(L"sql_method\*(R" above). The available \s-1SQL\s0 parts are: .Sp .Vb 1 \& prefetch_cols from where order_by limit sql prefetch_names .Ve .Sp The \*(L"sql\*(R" part is shortcut notation for these three combined: .Sp .Vb 1 \& where order_by limit .Ve .Sp Prefecch_cols are the columns selected when a prefetch is speccified \*(-- use in the \s-1SELECT.\s0 Prefetch_names are just the column names for use in \s-1GROUP BY.\s0 .Sp This is useful when statement order needs to be changed, such as when using a \&\s-1GROUP BY:\s0 .SS "count" .IX Subsection "count" Returns a count of the number of rows matching the criteria. \f(CW\*(C`count\*(C'\fR will discard \f(CW\*(C`offset\*(C'\fR, \f(CW\*(C`order_by\*(C'\fR, and \f(CW\*(C`rows\*(C'\fR. .PP .Vb 1 \& $count = MyApp::Article\->count(%criteria); .Ve .SS "search" .IX Subsection "search" Returns an iterator in scalar context, or an array of objects in list context. .PP .Vb 1 \& @objects = MyApp::Article\->search(%criteria); \& \& $iterator = MyApp::Article\->search(%criteria); .Ve .SS "search_like" .IX Subsection "search_like" As search but adds the attribute { cmp => 'like' }. .SS "page" .IX Subsection "page" Retuns a page object and an iterator. The page object is an instance of Data::Page. .PP .Vb 2 \& ( $page, $iterator ) \& = MyApp::Article\->page( $criteria, { rows => 10, page => 2 ); \& \& printf( "Results %d \- %d of %d Found\en", \& $page\->first, $page\->last, $page\->total_entries ); .Ve .SS "pager" .IX Subsection "pager" An alias to page. .SS "retrieve_all" .IX Subsection "retrieve_all" Same as \f(CW\*(C`Class::DBI\*(C'\fR with addition that it takes \f(CW\*(C`attributes\*(C'\fR as arguments, \&\f(CW\*(C`attributes\*(C'\fR can be a hash or a hashref. .PP .Vb 1 \& $iterator = MyApp::Article\->retrieve_all( order_by => \*(Aqcreated_on\*(Aq ); .Ve .SS "retrieve_next" .IX Subsection "retrieve_next" Returns the next record after the current one according to the order_by attribute (or primary key if no order_by specified) matching the criteria. Must be called as an object method. .SS "retrieve_previous" .IX Subsection "retrieve_previous" As retrieve_next but retrieves the previous record. .SH "CACHING OBJECTS" .IX Header "CACHING OBJECTS" Objects will be stored deflated in cache. Only \f(CW\*(C`Primary\*(C'\fR and \f(CW\*(C`Essential\*(C'\fR columns will be cached. .SS "cache" .IX Subsection "cache" Class method: if this is set caching is enabled. Any cache object that has a \&\f(CW\*(C`get\*(C'\fR, \f(CW\*(C`set\*(C'\fR, and \f(CW\*(C`remove\*(C'\fR method is supported. .PP .Vb 6 \& _\|_PACKAGE_\|_\->cache( \& Cache::FastMmap\->new( \& share_file => \*(Aq/tmp/cdbi\*(Aq, \& expire_time => 3600 \& ) \& ); .Ve .SS "cache_key" .IX Subsection "cache_key" Returns a cache key for an object consisting of class and primary keys. .SS "Overloaded methods" .IX Subsection "Overloaded methods" .IP "_init" 4 .IX Item "_init" Overrides \f(CW\*(C`Class::DBI\*(C'\fR's internal cache. On a cache hit, it will return a cached object; on a cache miss it will create an new object and store it in the cache. .IP "create" 4 .IX Item "create" .PD 0 .IP "insert" 4 .IX Item "insert" .PD All caches for this table are marked stale and will be re-cached on next retrieval. create is an alias kept for backwards compatibility. .IP "retrieve" 4 .IX Item "retrieve" On a cache hit the object will be inflated by the \f(CW\*(C`select\*(C'\fR trigger and then served. .IP "update" 4 .IX Item "update" Object is removed from the cache and will be cached on next retrieval. .IP "delete" 4 .IX Item "delete" Object is removed from the cache. .SH "UNIVERSALLY UNIQUE IDENTIFIERS" .IX Header "UNIVERSALLY UNIQUE IDENTIFIERS" If enabled a \s-1UUID\s0 string will be generated for primary column. A \s-1CHAR\s0(36) column is suitable for storage. .PP .Vb 1 \& _\|_PACKAGE_\|_\->sequence(\*(Aquuid\*(Aq); .Ve .SH "MAINTAINERS" .IX Header "MAINTAINERS" Fred Moyer .SH "AUTHORS" .IX Header "AUTHORS" Christian Hansen .PP Matt S Trout .PP Andy Grundman .SH "THANKS TO" .IX Header "THANKS TO" Danijel Milicevic, Jesse Sheidlower, Marcus Ramberg, Sebastian Riedel, Viljo Marrandi, Bill Moseley .SH "SUPPORT" .IX Header "SUPPORT" #catalyst on .PP .PP .SH "LICENSE" .IX Header "LICENSE" This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. .SH "SEE ALSO" .IX Header "SEE ALSO" Class::DBI .PP Data::Page .PP Data::UUID .PP SQL::Abstract .PP Catalyst .PP A comparison of different caching modules for perl.