__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
- # ->table, ->add_columns, etc.
+ # For the time being this is necessary even for virtual views
+ __PACKAGE__->table($view_name);
+
+ #
+ # ->add_columns, etc.
+ #
# do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);
=item * NOTE
If you're using the old deprecated C<< $rsrc_instance->name(\'( SELECT ...') >>
-method for custom SQL execution, you are highly encouraged to update your code
+method for custom SQL execution, you are highly encouraged to update your code
to use a virtual view as above. If you do not want to change your code, and just
want to suppress the deprecation warning when you call
L<DBIx::Class::Schema/deploy>, add this line to your source definition, so that
# FROM artist
Note that the C<as> attribute B<has absolutely nothing to do> with the SQL
-syntax C< SELECT foo AS bar > (see the documentation in
+syntax C< SELECT foo AS bar > (see the documentation in
L<DBIx::Class::ResultSet/ATTRIBUTES>). You can control the C<AS> part of the
generated SQL via the C<-as> field attribute as follows:
);
# Equivalent SQL
- # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
- # FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
- # GROUP BY me.artistid, me.name, me.rank, me.charfield
- # ORDER BY amount_of_cds DESC
+ # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
+ # FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
+ # GROUP BY me.artistid, me.name, me.rank, me.charfield
+ # ORDER BY amount_of_cds DESC
If your alias exists as a column in your base class (i.e. it was added with
my $count = $rs->count;
# Equivalent SQL:
- # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) count_subq:
+ # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) me:
=head2 Grouping results
});
my $rs = $schema->resultset('CD')->search({
- artist_id => { 'IN' => $inside_rs->get_column('id')->as_query },
+ artist_id => { -in => $inside_rs->get_column('id')->as_query },
});
-The usual operators ( =, !=, IN, NOT IN, etc.) are supported.
+The usual operators ( '=', '!=', -in, -not_in, etc.) are supported.
B<NOTE>: You have to explicitly use '=' when doing an equality comparison.
The following will B<not> work:
my $rs = $cdrs->search({
year => {
'=' => $cdrs->search(
- { artist_id => { '=' => \'me.artist_id' } },
- { alias => 'inner' }
+ { artist_id => { -ident => 'me.artist_id' } },
+ { alias => 'sub_query' }
)->get_column('year')->max_rs->as_query,
},
});
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
FROM cd me
- WHERE year = (
- SELECT MAX(inner.year)
- FROM cd inner
- WHERE artist_id = me.artist_id
- )
+ WHERE year = (
+ SELECT MAX(sub_query.year)
+ FROM cd sub_query
+ WHERE artist_id = me.artist_id
+ )
=head2 Predefined searches
-You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
-and defining often used searches as methods:
+You can define frequently used searches as methods by subclassing
+L<DBIx::Class::ResultSet>:
package My::DBIC::ResultSet::CD;
use strict;
good idea since it requires a scan of the entire table. (Unless your RDBMS
supports indexes on expressions - including return values of functions - and
you create an index on the return value of the function in question.) However,
-it can be accomplished with C<DBIx::Class> when necessary.
-
-If you do not have quoting on, simply include the function in your search
-specification as you would any column:
-
- $rs->search({ 'YEAR(date_of_birth)' => 1979 });
+it can be accomplished with C<DBIx::Class> when necessary by resorting to
+literal SQL:
-With quoting on, or for a more portable solution, use literal SQL values with
-placeholders:
-
- $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]);
+ $rs->search(
+ \[ 'YEAR(date_of_birth) = ?', 1979 ]
+ );
# Equivalent SQL:
# SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
- $rs->search({
+To include the function as part of a larger search, use the '-and' keyword
+to collect the search conditions:
+
+ $rs->search({ -and => [
name => 'Bob',
- -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
- });
+ \[ 'YEAR(date_of_birth) = ?', 1979 ]
+ ]});
# Equivalent SQL:
# SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
-Note: the C<plain_value> string in the C<< [ plain_value => 1979 ] >> part
-should be either the same as the name of the column (do this if the type of the
-return value of the function is the same as the type of the column) or
-otherwise it's essentially a dummy string currently (use C<plain_value> as a
-habit). It is used by L<DBIx::Class> to handle special column types.
+Note: the syntax for specifying the bind value's datatype and value is
+explained in L<DBIx::Class::ResultSet/DBIC BIND VALUES>.
See also L<SQL::Abstract/Literal SQL with placeholders and bind values
(subqueries)>.
+=head2 Software Limits
+
+When your RDBMS does not have a working SQL limit mechanism (e.g. Sybase ASE)
+and L<GenericSubQ|SQL::Abstract::Limit/GenericSubQ> is either too slow or does
+not work at all, you can try the
+L<software_limit|DBIx::Class::ResultSet/software_limit>
+L<DBIx::Class::ResultSet> attribute, which skips over records to simulate limits
+in the Perl layer.
+
+For example:
+
+ my $paged_rs = $rs->search({}, {
+ rows => 25,
+ page => 3,
+ order_by => [ 'me.last_name' ],
+ software_limit => 1,
+ });
+
+You can set it as a default for your schema by placing the following in your
+C<Schema.pm>:
+
+ __PACKAGE__->default_resultset_attributes({ software_limit => 1 });
+
+B<WARNING:> If you are dealing with large resultsets and your L<DBI> or
+ODBC/ADO driver does not have proper cursor support (i.e. it loads the whole
+resultset into memory) then this feature will be extremely slow and use huge
+amounts of memory at best, and may cause your process to run out of memory and
+cause instability on your server at worst, beware!
+
=head1 JOINS AND PREFETCHING
=head2 Using joins and prefetch
# SELECT cd.*, artist.*, liner_notes.* FROM cd
# JOIN artist ON cd.artist = artist.id
# JOIN liner_notes ON cd.id = liner_notes.cd
- # WHERE artist.name = 'Bob Marley'
+ # WHERE artist.name = 'Bob Marley' AND liner_notes.notes LIKE '%some text%'
# ORDER BY artist.name
=head2 Multi-step joins
=head1 ROW-LEVEL OPERATIONS
-=head2 Retrieving a row object's Schema
+=head2 Retrieving a result object's Schema
-It is possible to get a Schema object from a row object like so:
+It is possible to get a Schema object from a result object like so:
my $schema = $cd->result_source->schema;
# use the schema as normal:
Alternatively you can use L<DBIx::Class::DynamicSubclass> that implements
exactly the above functionality.
-=head2 Skip row object creation for faster results
+=head2 Skip result object creation for faster results
DBIx::Class is not built for speed, it's built for convenience and
ease of use, but sometimes you just need to get the data, and skip the
=head2 Creating a result set from a set of rows
-Sometimes you have a (set of) row objects that you want to put into a
+Sometimes you have a (set of) result objects that you want to put into a
resultset without the need to hit the DB again. You can do that by using the
L<set_cache|DBIx::Class::Resultset/set_cache> method:
__PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } );
-=head2 Many-to-many relationships
+=head2 Many-to-many relationship bridges
This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
=head1 TRANSACTIONS
+=head2 Transactions with txn_do
+
As of version 0.04001, there is improved transaction support in
L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an
example of the recommended way to use it:
return $genus->species;
};
+ use Try::Tiny;
my $rs;
- eval {
+ try {
$rs = $schema->txn_do($coderef1);
- };
-
- if ($@) { # Transaction failed
+ } catch {
+ # Transaction failed
die "the sky is falling!" #
- if ($@ =~ /Rollback failed/); # Rollback failed
+ if ($_ =~ /Rollback failed/); # Rollback failed
deal_with_failed_transaction();
- }
+ };
+
+Note: by default C<txn_do> will re-run the coderef one more time if an
+error occurs due to client disconnection (e.g. the server is bounced).
+You need to make sure that your coderef can be invoked multiple times
+without terrible side effects.
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.
-
+
=head2 Nested transactions and auto-savepoints
If savepoints are supported by your RDBMS, it is possible to achieve true
my $schema = MySchema->connect("dbi:Pg:dbname=my_db");
- # Start a transaction. Every database change from here on will only be
- # committed into the database if the eval block succeeds.
- eval {
+ # Start a transaction. Every database change from here on will only be
+ # committed into the database if the try block succeeds.
+ use Try::Tiny;
+ my $exception;
+ try {
$schema->txn_do(sub {
# SQL: BEGIN WORK;
for (1..10) {
# Start a nested transaction, which in fact sets a savepoint.
- eval {
+ try {
$schema->txn_do(sub {
# SQL: SAVEPOINT savepoint_0;
# WHERE ( id = 42 );
}
});
- };
- if ($@) {
+ } catch {
# SQL: ROLLBACK TO SAVEPOINT savepoint_0;
# There was an error while creating a $thing. Depending on the error
# changes related to the creation of this $thing
# Abort the whole job
- if ($@ =~ /horrible_problem/) {
+ if ($_ =~ /horrible_problem/) {
print "something horrible happend, aborting job!";
- die $@; # rethrow error
+ die $_; # rethrow error
}
# Ignore this $thing, report the error, and continue with the
# next $thing
- print "Cannot create thing: $@";
+ print "Cannot create thing: $_";
}
- # There was no error, so save all changes since the last
+ # There was no error, so save all changes since the last
# savepoint.
# SQL: RELEASE SAVEPOINT savepoint_0;
}
});
+ } catch {
+ $exception = $_;
};
- if ($@) {
+
+ if ($exception) {
# There was an error while handling the $job. Rollback all changes
# since the transaction started, including the already committed
# ('released') savepoints. There will be neither a new $job nor any
# SQL: ROLLBACK;
- print "ERROR: $@\n";
+ print "ERROR: $exception\n";
}
else {
# There was no error while handling the $job. Commit all changes.
In this example it might be hard to see where the rollbacks, releases and
commits are happening, but it works just the same as for plain L<<txn_do>>: If
-the C<eval>-block around C<txn_do> fails, a rollback is issued. If the C<eval>
+the C<try>-block around C<txn_do> fails, a rollback is issued. If the C<try>
succeeds, the transaction is committed (or the savepoint released).
-While you can get more fine-grained controll using C<svp_begin>, C<svp_release>
+While you can get more fine-grained control using C<svp_begin>, C<svp_release>
and C<svp_rollback>, it is strongly recommended to use C<txn_do> with coderefs.
+=head2 Simple Transactions with DBIx::Class::Storage::TxnScopeGuard
+
+An easy way to use transactions is with
+L<DBIx::Class::Storage::TxnScopeGuard>. See L</Automatically creating
+related objects> for an example.
+
+Note that unlike txn_do, TxnScopeGuard will only make sure the connection is
+alive when issuing the C<BEGIN> statement. It will not (and really can not)
+retry if the server goes away mid-operations, unlike C<txn_do>.
+
=head1 SQL
=head2 Creating Schemas From An Existing Database
L<DBIx::Class::Schema::Loader> will connect to a database and create a
L<DBIx::Class::Schema> and associated sources by examining the database.
-The recommend way of achieving this is to use the
+The recommend way of achieving this is to use the L<dbicdump> utility or the
+L<Catalyst> helper, as described in
+L<Manual::Intro|DBIx::Class::Manual::Intro/Using DBIx::Class::Schema::Loader>.
+
+Alternatively, use the
L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> method:
perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
- -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])'
+ -e 'make_schema_at("My::Schema", \
+ { db_schema => 'myschema', components => ["InflateColumn::DateTime"] }, \
+ [ "dbi:Pg:dbname=foo", "username", "password" ])'
-This will create a tree of files rooted at C<./lib/My/Schema/> containing
-source definitions for all the tables found in the C<foo> database.
+This will create a tree of files rooted at C<./lib/My/Schema/> containing source
+definitions for all the tables found in the C<myschema> schema in the C<foo>
+database.
=head2 Creating DDL SQL
Check the documentation of your database for the correct quote
characters to use. C<name_sep> needs to be set to allow the SQL
-generator to put the quotes the correct place.
+generator to put the quotes the correct place, and defaults to
+C<.> if not supplied.
In most cases you should set these as part of the arguments passed to
L<DBIx::Class::Schema/connect>:
return $rv;
}
-=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 (See 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)
-
-The limit dialect can also be set at connect time by specifying a
-C<limit_dialect> key in the final hash as shown above.
-
=head2 Working with PostgreSQL array types
You can also assign values to PostgreSQL array columns by passing array
numbers => [1, 2, 3]
});
- $row->update(
+ $result->update(
{
numbers => [1, 2, 3]
},
placeholders and bind values (subqueries)> for more explanation. Note that
L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass
the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in
-arrayrefs together with the column name, like this: C<< [column_name => value]
->>.
+arrayrefs together with the column name, like this:
+C<< [column_name => value] >>.
+
+=head2 Formatting DateTime objects in queries
+
+To ensure C<WHERE> conditions containing L<DateTime> arguments are properly
+formatted to be understood by your RDBMS, you must use the C<DateTime>
+formatter returned by L<DBIx::Class::Storage::DBI/datetime_parser> to format
+any L<DateTime> objects you pass to L<search|DBIx::Class::ResultSet/search>
+conditions. Any L<Storage|DBIx::Class::Storage> object attached to your
+L<Schema|DBIx::Class::Schema> provides a correct C<DateTime> formatter, so
+all you have to do is:
+
+ my $dtf = $schema->storage->datetime_parser;
+ my $rs = $schema->resultset('users')->search(
+ {
+ signup_date => {
+ -between => [
+ $dtf->format_datetime($dt_start),
+ $dtf->format_datetime($dt_end),
+ ],
+ }
+ },
+ );
+
+Without doing this the query will contain the simple stringification of the
+C<DateTime> object, which almost never matches the RDBMS expectations.
+
+This kludge is necessary only for conditions passed to
+L<DBIx::Class::ResultSet/search>, whereas
+L<create|DBIx::Class::ResultSet/create>,
+L<find|DBIx::Class::ResultSet/find>,
+L<DBIx::Class::Row/update> (but not L<DBIx::Class::ResultSet/update>) are all
+L<DBIx::Class::InflateColumn>-aware and will do the right thing when supplied
+an inflated C<DateTime> object.
+
+=head2 Using Unicode
+
+When using unicode character data there are two alternatives -
+either your database supports unicode characters (including setting
+the utf8 flag on the returned string), or you need to encode/decode
+data appropriately each time a string field is inserted into or
+retrieved from the database. It is better to avoid
+encoding/decoding data and to use your database's own unicode
+capabilities if at all possible.
+
+The L<DBIx::Class::UTF8Columns> component handles storing selected
+unicode columns in a database that does not directly support
+unicode. If used with a database that does correctly handle unicode
+then strange and unexpected data corrupt B<will> occur.
+
+The Catalyst Wiki Unicode page at
+L<http://wiki.catalystframework.org/wiki/tutorialsandhowtos/using_unicode>
+has additional information on the use of Unicode with Catalyst and
+DBIx::Class.
+
+The following databases do correctly handle unicode data:-
+
+=head3 MySQL
+
+MySQL supports unicode, and will correctly flag utf8 data from the
+database if the C<mysql_enable_utf8> is set in the connect options.
+
+ my $schema = My::Schema->connection('dbi:mysql:dbname=test',
+ $user, $pass,
+ { mysql_enable_utf8 => 1} );
+
+
+When set, a data retrieved from a textual column type (char,
+varchar, etc) will have the UTF-8 flag turned on if necessary. This
+enables character semantics on that string. You will also need to
+ensure that your database / table / column is configured to use
+UTF8. See Chapter 10 of the mysql manual for details.
+
+See L<DBD::mysql> for further details.
+
+=head3 Oracle
+
+Information about Oracle support for unicode can be found in
+L<DBD::Oracle/Unicode>.
+
+=head3 PostgreSQL
+
+PostgreSQL supports unicode if the character set is correctly set
+at database creation time. Additionally the C<pg_enable_utf8>
+should be set to ensure unicode data is correctly marked.
+
+ my $schema = My::Schema->connection('dbi:Pg:dbname=test',
+ $user, $pass,
+ { pg_enable_utf8 => 1} );
+
+Further information can be found in L<DBD::Pg>.
+
+=head3 SQLite
+
+SQLite version 3 and above natively use unicode internally. To
+correctly mark unicode strings taken from the database, the
+C<sqlite_unicode> flag should be set at connect time (in versions
+of L<DBD::SQLite> prior to 1.27 this attribute was named
+C<unicode>).
+
+ my $schema = My::Schema->connection('dbi:SQLite:/tmp/test.db',
+ '', '',
+ { sqlite_unicode => 1} );
=head1 BOOTSTRAPPING/MIGRATING
=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:
+(see L<DBIx::Class::Manual::Intro/Setting it up manually>), 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;
For example, say that you have three columns, C<id>, C<number>, and
C<squared>. You would like to make changes to C<number> and have
C<squared> be automagically set to the value of C<number> squared.
-You can accomplish this by overriding C<store_column>:
+You can accomplish this by wrapping the C<number> accessor with the C<around>
+method modifier, available through either L<Class::Method::Modifiers>,
+L<Moose|Moose::Manual::MethodModifiers> or L<Moose-like|Moo> modules):
+
+ around number => sub {
+ my ($orig, $self) = (shift, shift);
- sub store_column {
- my ( $self, $name, $value ) = @_;
- if ($name eq 'number') {
- $self->squared($value * $value);
+ if (@_) {
+ my $value = $_[0];
+ $self->squared( $value * $value );
}
- $self->next::method($name, $value);
- }
-Note that the hard work is done by the call to C<next::method>, which
+ $self->$orig(@_);
+ };
+
+Note that the hard work is done by the call to C<< $self->$orig >>, which
redispatches your call to store_column in the superclass(es).
+Generally, if this is a calculation your database can easily do, try
+and avoid storing the calculated value, it is safer to calculate when
+needed, than rely on the data being in sync.
+
=head2 Automatically creating related objects
You might have a class C<Artist> which has many C<CD>s. Further, you
sub query_start {
my $self = shift();
my $sql = shift();
- my $params = @_;
+ my @params = @_;
$self->print("Executing $sql: ".join(', ', @params)."\n");
$start = time();
statement and dig down to see if certain parameters cause aberrant behavior.
You might want to check out L<DBIx::Class::QueryLog> as well.
+=head1 IMPROVING PERFORMANCE
+
+=over
+
+=item *
+
+Install L<Class::XSAccessor> to speed up L<Class::Accessor::Grouped>.
+
+=item *
+
+On Perl 5.8 install L<Class::C3::XS>.
+
+=item *
+
+L<prefetch|DBIx::Class::ResultSet/prefetch> relationships, where possible. See
+L</Using joins and prefetch>.
+
+=item *
+
+Use L<populate|DBIx::Class::ResultSet/populate> in void context to insert data
+when you don't need the resulting L<result|DBIx::Class::Manual::ResultClass> objects,
+if possible, but see the caveats.
+
+When inserting many rows, for best results, populate a large number of rows at a
+time, but not so large that the table is locked for an unacceptably long time.
+
+If using L<create|DBIx::Class::ResultSet/create> instead, use a transaction and
+commit every C<X> rows; where C<X> gives you the best performance without
+locking the table for too long.
+
+=item *
+
+When selecting many rows, if you don't need full-blown L<DBIx::Class::Row>
+objects, consider using L<DBIx::Class::ResultClass::HashRefInflator>.
+
+=item *
+
+See also L</STARTUP SPEED> and L</MEMORY USAGE> in this document.
+
+=back
+
=head1 STARTUP SPEED
L<DBIx::Class|DBIx::Class> programs can have a significant startup delay
projects / dbsrgits/DBIx-Class.git / blobdiff