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
The C<page> attribute does not have to be specified in your search:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
rows => 10,
}
specify which ones you need:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
- cols => [qw/ name /]
+ columns => [qw/ name /]
}
);
to access the returned value:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
select => [ 'name', { LENGTH => 'name' } ],
as => [qw/ name name_length /],
=head3 SELECT DISTINCT with multiple columns
my $rs = $schema->resultset('Foo')->search(
- {},
+ undef,
{
select => [
{ distinct => [ $source->columns ] }
=head3 SELECT COUNT(DISTINCT colname)
my $rs = $schema->resultset('Foo')->search(
- {},
+ undef,
{
select => [
{ count => { distinct => 'colname' } }
L<DBIx::Class> supports C<GROUP BY> as follows:
my $rs = $schema->resultset('Artist')->search(
- {},
+ undef,
{
join => [qw/ cds /],
select => [ 'name', { count => 'cds.cdid' } ],
# 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
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(
{
# 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.
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