X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=5690f43c8cfba8df7ee6f7cc7d5484ce6422bb07;hb=de60a93dfda2e3384c2f6a00d55b352a4b4188c0;hp=6acd286662cb77a58e93c39f14ef54561915fb9c;hpb=bade79c4db3f32db885572fc8588301dbe7762ff;p=dbsrgits%2FDBIx-Class-Historic.git

diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod
index 6acd286..5690f43 100644
--- a/lib/DBIx/Class/Manual/Cookbook.pod
+++ b/lib/DBIx/Class/Manual/Cookbook.pod
@@ -12,7 +12,7 @@ When you expect a large number of results, you can ask L<DBIx::Class> for a
 paged resultset, which will fetch only a small number of records at a time:
 
   my $rs = $schema->resultset('Artist')->search(
-    {},
+    undef,
     {
       page => 1,  # page to return (defaults to 1)
       rows => 10, # number of results per page
@@ -24,7 +24,7 @@ paged resultset, which will fetch only a small number of records at a time:
 The C<page> attribute does not have to be specified in your search:
 
   my $rs = $schema->resultset('Artist')->search(
-    {},
+    undef,
     {
       rows => 10,
     }
@@ -76,9 +76,9 @@ When you only want selected columns from a table, you can use C<cols> to
 specify which ones you need:
 
   my $rs = $schema->resultset('Artist')->search(
-    {},
+    undef,
     {
-      cols => [qw/ name /]
+      columns => [qw/ name /]
     }
   );
 
@@ -94,7 +94,7 @@ stored procedure name). You then use C<as> to set the column name you will use
 to access the returned value:
 
   my $rs = $schema->resultset('Artist')->search(
-    {},
+    undef,
     {
       select => [ 'name', { LENGTH => 'name' } ],
       as     => [qw/ name name_length /],
@@ -129,7 +129,7 @@ any of your aliases using either of these:
 =head3 SELECT DISTINCT with multiple columns
 
   my $rs = $schema->resultset('Foo')->search(
-    {},
+    undef,
     {
       select => [
         { distinct => [ $source->columns ] }
@@ -141,7 +141,7 @@ any of your aliases using either of these:
 =head3 SELECT COUNT(DISTINCT colname)
 
   my $rs = $schema->resultset('Foo')->search(
-    {},
+    undef,
     {
       select => [
         { count => { distinct => 'colname' } }
@@ -155,7 +155,7 @@ any of your aliases using either of these:
 L<DBIx::Class> supports C<GROUP BY> as follows:
 
   my $rs = $schema->resultset('Artist')->search(
-    {},
+    undef,
     {
       join     => [qw/ cds /],
       select   => [ 'name', { count => 'cds.cdid' } ],
@@ -169,6 +169,37 @@ L<DBIx::Class> supports C<GROUP BY> as follows:
   # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
   # GROUP BY name
 
+=head3 Predefined searches
+
+You can write your own DBIx::Class::ResultSet class by inheriting from it
+and define often used searches as methods:
+
+  package My::DBIC::ResultSet::CD;
+  use strict;
+  use warnings;
+  use base 'DBIx::Class::ResultSet';
+
+  sub search_cds_ordered {
+      my ($self) = @_;
+
+      return $self->search(
+          {},
+          { order_by => 'name DESC' },
+      );
+  }
+
+  1;
+
+To use your resultset, first tell DBIx::Class to create an instance of it
+for you, in your My::DBIC::Schema::CD class:
+
+  __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
+
+Then call your new method in your code:
+
+   my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
+
+
 =head2 Using joins and prefetch
 
 You can use the C<join> attribute to allow searching on, or sorting your
@@ -231,8 +262,7 @@ main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
 queries!
 
 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
-This allows you to fetch results from a related table as well as the main table
-for your class:
+This allows you to fetch results from related tables in advance:
 
   my $rs = $schema->resultset('CD')->search(
     {
@@ -325,32 +355,70 @@ notes:
   # WHERE liner_notes.notes LIKE '%some text%'
   # AND author.name = 'A. Writer'
 
+=head2 Multi-step prefetch
+
+From 0.04999_05 onwards, 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(
+    undef,
+    {
+      prefetch => {
+        cd => 'artist'
+      }
+    }
+  );
+
+  # Equivalent SQL:
+  # SELECT tag.*, cd.*, artist.* FROM tag
+  # JOIN cd ON tag.cd = cd.cdid
+  # JOIN artist ON cd.artist = artist.artistid
+
+Now accessing our C<cd> and C<artist> relationships does not need additional
+SQL statements:
+
+  my $tag = $rs->first;
+  print $tag->cd->artist->name;
+
 =head2 Transactions
 
 As of version 0.04001, there is improved transaction support in
-L<DBIx::Class::Storage::DBI>.  Here is an example of the recommended
-way to use it:
+L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>.  Here is an
+example of the recommended way to use it:
 
-  my $genus = Genus->find(12);
-  eval {
-    MyDB->txn_begin;
+  my $genus = $schema->resultset('Genus')->find(12);
+
+  my $coderef1 = sub {
+    my ($schema, $genus, $code) = @_;
     $genus->add_to_species({ name => 'troglodyte' });
     $genus->wings(2);
     $genus->update;
-    cromulate($genus); # Can have a nested transation
-    MyDB->txn_commit;
+    $schema->txn_do($code, $genus); # Can have a nested transaction
+    return $genus->species;
+  };
+
+  my $coderef2 = sub {
+    my ($genus) = @_;
+    $genus->extinct(1);
+    $genus->update;
+  };
+
+  my $rs;
+  eval {
+    $rs = $schema->txn_do($coderef1, $schema, $genus, $coderef2);
   };
-  if ($@) {
-    # Rollback might fail, too
-    eval {
-      MyDB->txn_rollback
-    };
+
+  if ($@) {                             # Transaction failed
+    die "the sky is falling!"           #
+      if ($@ =~ /Rollback failed/);     # Rollback failed
+
+    deal_with_failed_transaction();
   }
 
-Currently, a nested commit will do nothing and a nested rollback will
-die.  The code at each level must be sure to call rollback in the case
-of an error, to ensure that the rollback will propagate to the top
-level and be issued.  Support for savepoints and for true nested
+Nested transactions will work as expected. That is, only the outermost
+transaction will actually issue a commit to the $dbh, and a rollback
+at any level of any transaction will cause the entire nested
+transaction to fail. Support for savepoints and for true nested
 transactions (for databases that support them) will hopefully be added
 in the future.
 
@@ -415,7 +483,182 @@ development, you might like to put the following signal handler in
 your main database class to make sure it disconnects cleanly:
 
   $SIG{INT} = sub {
-    __PACKAGE__->storage->dbh->disconnect;
+    __PACKAGE__->storage->disconnect;
   };
 
+=head2 Schema import/export
+
+This functionality requires you to have L<SQL::Translator> (also known as
+"SQL Fairy") installed.
+
+To create a DBIx::Class schema from an existing database:
+
+ sqlt --from DBI
+      --to DBIx::Class::File
+      --prefix "MySchema" > MySchema.pm
+
+To create a MySQL database from an existing L<DBIx::Class> schema, convert the
+schema to MySQL's dialect of SQL:
+
+  sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
+  
+And import using the mysql client:
+
+  mysql -h "host" -D "database" -u "user" -p < Schema1.sql
+
+=head2 Easy migration from class-based to schema-based setup
+
+You want to start using the schema-based approach to L<DBIx::Class>
+(see L<SchemaIntro.pod>), but have an established class-based setup with lots
+of existing classes that you don't want to move by hand. Try this nifty script
+instead:
+
+  use MyDB;
+  use SQL::Translator;
+  
+  my $schema = MyDB->schema_instance;
+  
+  my $translator           =  SQL::Translator->new( 
+      debug                => $debug          ||  0,
+      trace                => $trace          ||  0,
+      no_comments          => $no_comments    ||  0,
+      show_warnings        => $show_warnings  ||  0,
+      add_drop_table       => $add_drop_table ||  0,
+      validate             => $validate       ||  0,
+      parser_args          => {
+         'DBIx::Schema'    => $schema,
+                              },
+      producer_args   => {
+          'prefix'         => 'My::Schema',
+                         },
+  );
+  
+  $translator->parser('DBIx::Class');
+  $translator->producer('DBIx::Class::File');
+  
+  my $output = $translator->translate(@args) or die
+          "Error: " . $translator->error;
+  
+  print $output;
+
+You could use L<Module::Find> to search for all subclasses in the MyDB::*
+namespace, which is currently left as an exercise for the reader.
+
+=head2 Schema versioning
+
+The following example shows simplistically how you might use DBIx::Class to
+deploy versioned schemas to your customers. The basic process is as follows:
+
+=over 4
+
+=item 1.
+
+Create a DBIx::Class schema
+
+=item 2.
+
+Save the schema
+
+=item 3.
+
+Deploy to customers
+
+=item 4.
+
+Modify schema to change functionality
+
+=item 5.
+
+Deploy update to customers
+
+=back
+
+=head3 Create a DBIx::Class schema
+
+This can either be done manually, or generated from an existing database as
+described under C<Schema import/export>.
+
+=head3 Save the schema
+
+Use C<sqlt> to transform your schema into an SQL script suitable for your
+customer's database. E.g. for MySQL:
+
+  sqlt --from DBIx::Class
+       --to MySQL
+       --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
+
+If you need to target databases from multiple vendors, just generate an SQL
+script suitable for each. To support PostgreSQL too:
+
+  sqlt --from DBIx::Class
+       --to PostgreSQL
+       --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
+
+=head3 Deploy to customers
+
+There are several ways you could deploy your schema. These are probably
+beyond the scope of this recipe, but might include:
+
+=over 4
+
+=item 1.
+
+Require customer to apply manually using their RDBMS.
+
+=item 2.
+
+Package along with your app, making database dump/schema update/tests
+all part of your install.
+
+=back
+
+=head3 Modify the schema to change functionality
+
+As your application evolves, it may be necessary to modify your schema to
+change functionality. Once the changes are made to your schema in DBIx::Class,
+export the modified schema as before, taking care not to overwrite the original:
+
+  sqlt --from DBIx::Class
+       --to MySQL
+       --DBIx::Class "Anything.pm" > Schema2.mysql.sql
+
+Next, use sqlt-diff to create an SQL script that will update the customer's
+database schema:
+
+  sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
+
+=head3 Deploy update to customers
+
+The schema update can be deployed to customers using the same method as before.
+
+=head2 Setting limit dialect for SQL::Abstract::Limit
+
+In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
+SQL-server by looking at the database-handle. This is a common problem when
+using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
+available, not which JDBC-driver the Java component has loaded.
+This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
+in SQL::Abstract::Limit -documentation.
+
+  __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
+
+The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
+that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
+
+=head2 Setting quotes for the generated SQL. 
+
+If the database contains columnames with spaces and/or reserved words, the
+SQL-query needs to be quoted. This is done using:
+
+  __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
+  __PACKAGE__->storage->sql_maker->name_sep('.');
+
+The first sets the quotesymbols. If the quote i "symmetric" as " or '
+  
+  __PACKAGE__->storage->sql_maker->quote_char('"');
+
+is enough. If the left quote differs form the right quote, the first 
+notation should be used. name_sep needs to be set to allow the 
+SQL generator to put the quotes the correct place. 
+
 =cut