Added caveat notes to dual trick in cookbook

[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / Cookbook.pod

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index 678e173..fae1e94 100644 (file)
--- 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();
 
@@ -295,7 +284,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.
 
@@ -316,6 +305,11 @@ The following will B<not> work:
     artist_id => $inside_rs->get_column('id')->as_query,
   });
 
+=head3 Support
+
+Subqueries are supported in the where clause (first hashref), and in the
+from, select, and +select attributes.
+
 =head3 Correlated subqueries
 
   my $cdrs = $schema->resultset('CD');
@@ -338,11 +332,9 @@ That creates the following SQL:
        WHERE artistid = me.artistid
       )
 
-=head2 Where subqueries will work
+=head3 EXPERIMENTAL
 
-Currently, subqueries will B<only> work in the where-clause of a search. In
-other words, in the first hashref of a search() method. Work is being done
-to make them work as part of the second hashref (from, select, +select, etc).
+Please note that subqueries are considered an experimental feature.
 
 =head2 Predefined searches
 
@@ -1175,6 +1167,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>
  
@@ -1382,10 +1386,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]
@@ -1734,4 +1738,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