Sometimes you need only the first "top" row of a resultset. While this can be easily done with $rs->first, it is suboptimal, as a full blown cursor for the resultset will be created and then immediately destroyed after fetching the first row object. $rs->single is designed specifically for this case - it will grab the first returned result without even instantiating a cursor.

Before replacing all your calls to first() with single() please observe the following CAVEATS:

While single() takes a search condition just like search() does, it does _not_ accept search attributes. However one can always chain a single() to a search():

my $top_cd = $cd_rs->search({}, { order_by => 'rating' })->single;

Since single() is the engine behind find(), it is designed to fetch a single row per database query. Thus a warning will be issued when the underlying SELECT returns more than one row. Sometimes however this usage is valid: i.e. we have an arbitrary number of cd's but only one of them is at the top of the charts at any given time. If you know what you are doing, you can silence the warning by explicitly limiting the resultset size:

Sometimes you have to run arbitrary SQL because your query is too complex (e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to be optimized for your database in a special way, but you still want to get the results as a DBIx::Class::ResultSet.

This is accomplished by defining a ResultSource::View for your query, almost like you would define a regular ResultSource.

Note that you cannot have bind parameters unless is_virtual is set to true.

NOTE

If you're using the old deprecated $rsrc_instance->name(\'( SELECT ...') 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 "deploy" in DBIx::Class::Schema, add this line to your source definition, so that deploy will exclude this "table":

When you only want specific columns from a table, you can use columns to specify which ones you need. This is useful to avoid loading columns with large amounts of data that you aren't about to use anyway:

The combination of select and as can be used to return the result of a database function or stored procedure as a column value. You use select to specify the source for your column value (e.g. a column name, function, or stored procedure name). You then use as to set the column name you will use to access the returned value:

Note that the as attribute has absolutely nothing to do with the SQL syntax SELECT foo AS bar (see the documentation in "ATTRIBUTES" in DBIx::Class::ResultSet). You can control the AS part of the generated SQL via the -as field attribute as follows:

Using SQL functions on the left hand side of a comparison is generally not a 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 DBIx::Class when necessary by resorting to literal SQL:

Note: the plain_value string in the [ 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 in the case of a function it's currently treated as a dummy string (it is a good idea to use plain_value or something similar to convey intent). The value is currently only significant when handling special column types (BLOBs, arrays, etc.), but this may change in the future.

When your RDBMS does not have a working SQL limit mechanism (e.g. Sybase ASE) and GenericSubQ is either too slow or does not work at all, you can try the software_limitDBIx::Class::ResultSet attribute, which skips over records to simulate limits in the Perl layer.

You can set it as a default for your schema by placing the following in your Schema.pm:

__PACKAGE__->default_resultset_attributes({ software_limit => 1 });

WARNING: If you are dealing with large resultsets and your 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!

Note that the join attribute should only be used when you need to search or sort using columns in a related table. Joining related tables when you only need columns from the main table will make performance worse!

Now let's say you want to display a list of CDs, each with the name of the artist. The following will work fine:

There is a problem however. We have searched both the cd and artist tables in our main query, but we have only returned data from the cd table. To get the artist name for any of the CD objects returned, DBIx::Class will go back to the database:

SELECT artist.* FROM artist WHERE artist.id = ?

A statement like the one above will run for each and every CD returned by our main query. Five CDs, five extra queries. A hundred CDs, one hundred extra queries!

Thankfully, DBIx::Class has a prefetch attribute to solve this problem. This allows you to fetch results from related tables in advance:

DBIx::Class has now prefetched all matching data from the artist table, so no additional SQL statements are executed. You now have a much more efficient query.

Also note that 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!

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;

DBIx::Class classes are proxy classes, therefore some different techniques need to be employed for more than basic subclassing. In this example we have a single user table that carries a boolean bit for admin. We would like like to give the admin users objects (DBIx::Class::Row) the same methods as a regular user but also special admin only methods. It doesn't make sense to create two separate proxy-class files for this. We would be copying all the user methods into the Admin class. There is a cleaner way to accomplish this.

Overriding the inflate_result method within the User proxy-class gives us the effect we want. This method is called by DBIx::Class::ResultSet when inflating a result from storage. So we grab the object being returned, inspect the values we are looking for, bless it if it's an admin object, and then return it. See the example below:

If the 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 database with none of the convenience methods wrapped round them.

Mapping relationships across DB schemas 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...

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 connection method in your Schema class and building a renaming facility, like so:

Note: by default 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.

Here is an example of true nested transactions. In the example, we start a big task which will create several rows. Generation of data for each row is a fragile operation and might fail. If we fail creating something, depending on the type of failure, we want to abort the whole task, or only skip the failed row.

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 <txn_do>: If the try-block around txn_do fails, a rollback is issued. If the try succeeds, the transaction is committed (or the savepoint released).

While you can get more fine-grained control using svp_begin, svp_release and svp_rollback, it is strongly recommended to use txn_do with coderefs.

Note that unlike txn_do, TxnScopeGuard will only make sure the connection is alive when issuing the BEGIN statement. It will not (and really can not) retry if the server goes away mid-operations, unlike txn_do.

This will produce new database-specific .sql files for the new version of the schema, plus scripts to convert from version 0.1 to 0.2. This requires that the files for 0.1 as created above are available in the given directory to diff against.

Often you will want indexes on columns on your table to speed up searching. To do this, create a method called sqlt_deploy_hook in the relevant source class (refer to the advanced callback system if you wish to share a hook between multiple sources):

There are several ways you could deploy your schema. These are probably beyond the scope of this recipe, but might include:

Require customer to apply manually using their RDBMS.

Package along with your app, making database dump/schema update/tests all part of your install.

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 and the conversion scripts as in "Creating DDL SQL".

Deploy update to customers

Add the DBIx::Class::Schema::Versioned schema component to your Schema class. This will add a new table to your database called dbix_class_schema_vesion which will keep track of which version is installed and warn if the user tries to run a newer schema version than the database thinks it has.

Alternatively, you can send the conversion SQL scripts to your customers as above.

The first sets the quote characters. Either a pair of matching brackets, or a " or ':

$schema->storage->sql_maker->quote_char('"');

Check the documentation of your database for the correct quote characters to use. name_sep needs to be set to allow the SQL generator to put the quotes the correct place, and defaults to . if not supplied.

In conditions (e.g. \%cond in the "search" in DBIx::Class::ResultSet family of methods) you cannot directly use array references (since this is interpreted as a list of values to be ORed), but you can use the following syntax to force passing them as bind values:

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 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 will occur.

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.

SQLite version 3 and above natively use unicode internally. To correctly mark unicode strings taken from the database, the sqlite_unicode flag should be set at connect time (in versions of DBD::SQLite prior to 1.27 this attribute was named unicode).

For example, say that you have three columns, id, number, and squared. You would like to make changes to number and have squared be automagically set to the value of number squared. You can accomplish this by wrapping the number accessor with Class::Method::Modifiers:

Say you have a table "Camera" and want to associate a description with each camera. For most cameras, you'll be able to generate the description from the other columns. However, in a few special cases you may want to associate a custom description with a camera.

Solution:

In your database schema, define a description field in the "Camera" table that can contain text and null values.

In DBIC, we'll overload the column accessor to provide a sane default if no custom description is defined. The accessor will either return or generate the description, depending on whether the field is null or not.

First, in your "Camera" schema class, define the description field as follows:

several pages worth of data from the CD object's schema and result source will be dumped to the screen. Since usually one is only interested in a few column values of the object, this is not very helpful.

Luckily, it is possible to modify the data before Data::Dumper outputs it. Simply define a hook that Data::Dumper will call on the object before dumping it. For example,

If the structure of your schema is such that there is a common base class for all your table classes, simply put a method similar to _dumper_hook in the base class and set $Data::Dumper::Freezer to its name and Data::Dumper will automagically clean up your data before printing it. See "EXAMPLES" in Data::Dumper for more information.

When you enable DBIx::Class::Storage's debugging it prints the SQL executed as well as notifications of query completion and transaction begin/commit. If you'd like to profile the SQL you can subclass the DBIx::Class::Storage::Statistics class and write your own profiling mechanism:

You could then create average, high and low execution times for an SQL statement and dig down to see if certain parameters cause aberrant behavior. You might want to check out DBIx::Class::QueryLog as well.

to load the result classes. This will use Module::Find to find and load the appropriate modules. Explicitly defining the classes you wish to load will remove the overhead of Module::Find and the related directory operations:

__PACKAGE__->load_classes(qw/ CD Artist Track /);

If you are instead using the load_namespaces syntax to load the appropriate classes there is not a direct alternative avoiding Module::Find.

DBIx::Class normally caches all statements with 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 CachedKids hash: