X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=574a8e1eab1b4bd021b8a8aa94cb149b5dd96081;hb=82f6f45f40c17f9cf2c2385776f4165d4bb63b98;hp=00c28e9dba131a191fba91b6ce6eb67c82e62ce9;hpb=0a62f675479175606ba25cc28e0c28fb17425b0d;p=dbsrgits%2FDBIx-Class.git

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index 00c28e9..574a8e1 100644
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -19,19 +19,8 @@ paged resultset, which will fetch only a defined number of records at a time:
 
   return $rs->all(); # all records for page 1
 
-The C<page> attribute does not have to be specified in your search:
-
-  my $rs = $schema->resultset('Artist')->search(
-    undef,
-    {
-      rows => 10,
-    }
-  );
-
-  return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
-
-In either of the above cases, you can get a L<Data::Page> object for the
-resultset (suitable for use in e.g. a template) using the C<pager> method:
+You can get a L<Data::Page> object for the resultset (suitable for use
+in e.g. a template) using the C<pager> method:
 
   return $rs->pager();
 
@@ -116,7 +105,7 @@ reference (this is a feature of L<SQL::Abstract>).
 Say you want to run a complex custom query on your user data, here's what
 you have to add to your User class:
 
-  package My::Schema::User;
+  package My::Schema::Result::User;
   
   use base qw/DBIx::Class/;
   
@@ -160,10 +149,10 @@ files (instead of stuffing all of them into the same resultset class), you can
 achieve the same with subclassing the resultset class and defining the
 ResultSource there:
 
-  package My::Schema::UserFriendsComplex;
+  package My::Schema::Result::UserFriendsComplex;
 
-  use My::Schema::User;
-  use base qw/My::Schema::User/;
+  use My::Schema::Result::User;
+  use base qw/My::Schema::Result::User/;
 
   __PACKAGE__->table('dummy');  # currently must be called before anything else
 
@@ -248,29 +237,49 @@ any of your aliases using either of these:
 
 =head2 SELECT DISTINCT with multiple columns
 
-  my $rs = $schema->resultset('Foo')->search(
+  my $rs = $schema->resultset('Artist')->search(
     {},
     {
-      select => [
-        { distinct => [ $source->columns ] }
-      ],
-      as => [ $source->columns ] # remember 'as' is not the same as SQL AS :-)
+      columns => [ qw/artistid name rank/ ],
+      distinct => 1
+    } 
+  );
+
+  my $rs = $schema->resultset('Artist')->search(
+    {},
+    {
+      columns => [ qw/artistid name rank/ ],
+      group_by => [ qw/artistid name rank/ ],
     }
   );
 
+  # Equivalent SQL:
+  # SELECT me.artistid, me.name, me.rank
+  # FROM artist me
+  # GROUP BY artistid, name, rank
+
 =head2 SELECT COUNT(DISTINCT colname)
 
-  my $rs = $schema->resultset('Foo')->search(
+  my $rs = $schema->resultset('Artist')->search(
     {},
     {
-      select => [
-        { count => { distinct => 'colname' } }
-      ],
-      as => [ 'count' ]
+      columns => [ qw/name/ ],
+      distinct => 1
+    }
+  );
+
+  my $rs = $schema->resultset('Artist')->search(
+    {},
+    {
+      columns => [ qw/name/ ],
+      group_by => [ qw/name/ ],
     }
   );
 
-  my $count = $rs->next->get_column('count');
+  my $count = $rs->count;
+
+  # Equivalent SQL:
+  # SELECT COUNT( DISTINCT( me.name ) ) FROM artist me 
 
 =head2 Grouping results
 
@@ -295,7 +304,7 @@ Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you
 are in any way unsure about the use of the attributes above (C< join
 >, C< select >, C< as > and C< group_by >).
 
-=head2 Subqueries
+=head2 Subqueries (EXPERIMENTAL)
 
 You can write subqueries relatively easily in DBIC.
 
@@ -343,6 +352,10 @@ That creates the following SQL:
        WHERE artistid = me.artistid
       )
 
+=head3 EXPERIMENTAL
+
+Please note that subqueries are considered an experimental feature.
+
 =head2 Predefined searches
 
 You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
@@ -504,9 +517,6 @@ L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
 so no additional SQL statements are executed. You now have a much more
 efficient query.
 
-Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
-C<has_many> relationships.
-
 Also note that C<prefetch> should only be used when you know you will
 definitely use data from a related table. Pre-fetching related tables when you
 only need columns from the main table will make performance worse!
@@ -624,7 +634,7 @@ CD and Concert, and join CD to LinerNotes:
 
 =head2 Multi-step prefetch
 
-From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
+C<prefetch> can be nested more than one relationship
 deep using the same syntax as a multi-step join:
 
   my $rs = $schema->resultset('Tag')->search(
@@ -664,8 +674,7 @@ method.
 
 AKA getting last_insert_id
 
-If you are using PK::Auto (which is a core component as of 0.07), this is 
-straightforward:
+Thanks to the core component PK::Auto, this is straightforward:
 
   my $foo = $rs->create(\%blah);
   # do more stuff
@@ -680,7 +689,7 @@ Employ the standard stringification technique by using the C<overload>
 module.
 
 To make an object stringify itself as a single column, use something
-like this (replace C<foo> with the column/method of your choice):
+like this (replace C<name> with the column/method of your choice):
 
   use overload '""' => sub { shift->name}, fallback => 1;
 
@@ -724,6 +733,48 @@ Just use C<find_or_new> instead, then check C<in_storage>:
     # do whatever else you wanted if it was a new row
   }
 
+=head2 Static sub-classing DBIx::Class result classes 
+
+AKA adding additional relationships/methods/etc. to a model for a
+specific usage of the (shared) model.
+
+B<Schema definition> 
+ 
+    package My::App::Schema; 
+     
+    use base DBIx::Class::Schema; 
+
+    # load subclassed classes from My::App::Schema::Result/ResultSet
+    __PACKAGE__->load_namespaces;
+
+    # load classes from shared model
+    load_classes({
+        'My::Shared::Model::Result' => [qw/
+            Foo
+            Bar
+        /]});
+
+    1;
+ 
+B<Result-Subclass definition> 
+ 
+    package My::App::Schema::Result::Baz;
+     
+    use strict; 
+    use warnings; 
+    use base My::Shared::Model::Result::Baz; 
+    
+    # WARNING: Make sure you call table() again in your subclass,
+    # otherwise DBIx::Class::ResultSourceProxy::Table will not be called
+    # and the class name is not correctly registered as a source
+    __PACKAGE__->table('baz'); 
+     
+    sub additional_method { 
+        return "I'm an additional method only needed by this app"; 
+    }
+
+    1;
+     
 =head2 Dynamic Sub-classing DBIx::Class proxy classes 
 
 AKA multi-class object inflation from one table
@@ -747,16 +798,18 @@ below:
  
 B<Schema Definition> 
  
-    package DB::Schema; 
+    package My::Schema; 
      
     use base qw/DBIx::Class::Schema/; 
  
-    __PACKAGE__->load_classes(qw/User/); 
+    __PACKAGE__->load_namespaces;
+
+    1;
  
  
 B<Proxy-Class definitions> 
  
-    package DB::Schema::User; 
+    package My::Schema::Result::User; 
      
     use strict; 
     use warnings; 
@@ -789,13 +842,15 @@ B<Proxy-Class definitions>
         print "I am a regular user.\n"; 
         return ; 
     } 
+    
+    1;
+
      
-     
-    package DB::Schema::User::Admin; 
+    package My::Schema::Result::User::Admin; 
      
     use strict; 
     use warnings; 
-    use base qw/DB::Schema::User/; 
+    use base qw/My::Schema::Result::User/; 
      
     sub hello 
     { 
@@ -807,13 +862,15 @@ B<Proxy-Class definitions>
     { 
         print "I am doing admin stuff\n"; 
         return ; 
-    } 
+    }
+
+    1;
  
 B<Test File> test.pl 
  
     use warnings; 
     use strict; 
-    use DB::Schema; 
+    use My::Schema; 
      
     my $user_data = { email    => 'someguy@place.com',  
                       password => 'pass1',  
@@ -823,7 +880,7 @@ B<Test File> test.pl
                        password => 'pass2',  
                        admin    => 1 }; 
                            
-    my $schema = DB::Schema->connection('dbi:Pg:dbname=test'); 
+    my $schema = My::Schema->connection('dbi:Pg:dbname=test'); 
      
     $schema->resultset('User')->create( $user_data ); 
     $schema->resultset('User')->create( $admin_data ); 
@@ -861,11 +918,16 @@ To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>.
 
 Wasn't that easy?
 
+Beware, changing the Result class using
+L<DBIx::Class::ResultSet/result_class> will replace any existing class
+completely including any special components loaded using
+load_components, eg L<DBIx::Class::InflateColumn::DateTime>.
+
 =head2 Get raw data for blindingly fast results
 
 If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
 above is not fast enough for you, you can use a DBIx::Class to return values
-exactly as they come out of the data base with none of the convenience methods
+exactly as they come out of the database with none of the convenience methods
 wrapped round them.
 
 This is used like so:
@@ -876,13 +938,13 @@ This is used like so:
   }
 
 You will need to map the array offsets to particular columns (you can
-use the I<select> attribute of C<search()> to force ordering).
+use the L<DBIx::Class::ResultSet/select> attribute of L<DBIx::Class::ResultSet/search> to force ordering).
 
 =head1 RESULTSET OPERATIONS
 
 =head2 Getting Schema from a ResultSet
 
-To get the schema object from a result set, do the following:
+To get the L<DBIx::Class::Schema> object from a ResultSet, do the following:
 
  $rs->result_source->schema
 
@@ -1022,6 +1084,98 @@ This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_man
   $rs = $user->addresses(); # get all addresses for a user
   $rs = $address->users(); # get all users for an address
 
+=head2 Relationships across DB schemas
+
+Mapping relationships across L<DB schemas|DBIx::Class::Manual::Glossary/DB schema>
+is easy as long as the schemas themselves are all accessible via the same DBI
+connection. In most cases, this means that they are on the same database host
+as each other and your connecting database user has the proper permissions to them.
+
+To accomplish this one only needs to specify the DB schema name in the table
+declaration, like so...
+
+  package MyDatabase::Main::Artist;
+  use base qw/DBIx::Class/;
+  __PACKAGE__->load_components(qw/PK::Auto Core/);
+  
+  __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause
+  
+  __PACKAGE__->add_columns(qw/ artistid name /);
+  __PACKAGE__->set_primary_key('artistid');
+  __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
+
+  1;
+
+Whatever string you specify there will be used to build the "FROM" clause in SQL
+queries.
+
+The big drawback to this is you now have DB schema names hardcoded in your
+class files. This becomes especially troublesome if you have multiple instances
+of your application to support a change lifecycle (e.g. DEV, TEST, PROD) and
+the DB schemas are named based on the environment (e.g. database1_dev).
+
+However, one can dynamically "map" to the proper DB schema by overriding the
+L<connection|DBIx::Class::Schama/connection> method in your Schema class and
+building a renaming facility, like so:
+
+  package MyDatabase::Schema;
+  use Moose;
+  
+  extends 'DBIx::Class::Schema';
+  
+  around connection => sub {
+    my ( $inner, $self, $dsn, $username, $pass, $attr ) = ( shift, @_ );
+   
+    my $postfix = delete $attr->{schema_name_postfix};
+    
+    $inner->(@_);
+    
+    if ( $postfix ) {
+        $self->append_db_name($postfix);
+    }
+  };
+
+  sub append_db_name {
+    my ( $self, $postfix ) = @_;
+    
+    my @sources_with_db 
+        = grep 
+            { $_->name =~ /^\w+\./mx } 
+            map 
+                { $self->source($_) } 
+                $self->sources;
+    
+    foreach my $source (@sources_with_db) {
+        my $name = $source->name;
+        $name =~ s{^(\w+)\.}{${1}${postfix}\.}mx;
+        
+        $source->name($name);
+    }
+  }
+
+  1;
+
+By overridding the L<connection|DBIx::Class::Schama/connection>
+method and extracting a custom option from the provided \%attr hashref one can
+then simply iterate over all the Schema's ResultSources, renaming them as
+needed.
+
+To use this facility, simply add or modify the \%attr hashref that is passed to 
+L<connection|DBIx::Class::Schama/connect>, as follows:
+
+  my $schema 
+    = MyDatabase::Schema->connect(
+      $dsn, 
+      $user, 
+      $pass,
+      {
+        schema_name_postfix => '_dev'
+        # ... Other options as desired ... 
+      })
+
+Obviously, one could accomplish even more advanced mapping via a hash map or a
+callback routine.
+
 =head1 TRANSACTIONS
 
 As of version 0.04001, there is improved transaction support in
@@ -1174,6 +1328,18 @@ Or use C<cursor>
   while (my @vals = $cursor->next) {
     print $vals[0]."\n";
   }
+
+In case you're going to use this "trick" together with L<DBIx::Class::Schema/deploy> or
+L<DBIx::Class::Schema/create_ddl_dir> a table called "dual" will be created in your
+current schema. This would overlap "sys.dual" and you could not fetch "sysdate" or
+"sequence.nextval" anymore from dual. To avoid this problem, just tell
+L<SQL::Translator> to not create table dual:
+
+    my $sqlt_args = {
+        add_drop_table => 1,
+        parser_args    => { sources => [ grep $_ ne 'Dual', schema->sources ] },
+    };
+    $schema->create_ddl_dir( [qw/Oracle/], undef, './sql', undef, $sqlt_args );
  
 Or use L<DBIx::Class::ResultClass::HashRefInflator>
  
@@ -1219,7 +1385,7 @@ class (refer to the advanced
 L<callback system|DBIx::Class::ResultSource/sqlt_deploy_callback> if you wish
 to share a hook between multiple sources):
 
- package My::Schema::Artist;
+ package My::Schema::Result::Artist;
 
  __PACKAGE__->table('artist');
  __PACKAGE__->add_columns(id => { ... }, name => { ... })
@@ -1381,10 +1547,10 @@ C<limit_dialect> key in the final hash as shown above.
 
 =head2 Working with PostgreSQL array types
 
-If your SQL::Abstract version (>= 1.50) supports it, you can assign to
-PostgreSQL array values by passing array references in the C<\%columns>
-(C<\%vals>) hashref of the L<DBIx::Class::ResultSet/create> and
-L<DBIx::Class::Row/update> family of methods:
+You can also assign values to PostgreSQL array columns by passing array
+references in the C<\%columns> (C<\%vals>) hashref of the
+L<DBIx::Class::ResultSet/create> and L<DBIx::Class::Row/update> family of
+methods:
 
   $resultset->create({
     numbers => [1, 2, 3]
@@ -1733,4 +1899,33 @@ If you are instead using the L<load_namespaces|DBIx::Class::Schema/load_namespac
 syntax to load the appropriate classes there is not a direct alternative
 avoiding L<Module::Find|Module::Find>.
 
+=head1 MEMORY USAGE
+
+=head2 Cached statements
+
+L<DBIx::Class> normally caches all statements with L<< prepare_cached()|DBI/prepare_cached >>.
+This is normally a good idea, but if too many statements are cached, the database may use too much
+memory and may eventually run out and fail entirely.  If you suspect this may be the case, you may want
+to examine DBI's L<< CachedKids|DBI/CachedKidsCachedKids_(hash_ref) >> hash:
+
+    # print all currently cached prepared statements
+    print for keys %{$schema->storage->dbh->{CachedKids}};
+    # get a count of currently cached prepared statements
+    my $count = scalar keys %{$schema->storage->dbh->{CachedKids}};
+
+If it's appropriate, you can simply clear these statements, automatically deallocating them in the
+database:
+
+    my $kids = $schema->storage->dbh->{CachedKids};
+    delete @{$kids}{keys %$kids} if scalar keys %$kids > 100;
+
+But what you probably want is to expire unused statements and not those that are used frequently.
+You can accomplish this with L<Tie::Cache> or L<Tie::Cache::LRU>:
+
+    use Tie::Cache;
+    use DB::Main;
+    my $schema = DB::Main->connect($dbi_dsn, $user, $pass, {
+        on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 },
+    });
+
 =cut