Preparation¶

First, install DBIx::Class like you do with any other CPAN distribution. See <http://www.cpan.org/modules/INSTALL.html> and perlmodinstall.

Then open the distribution in your shell and change to the subdirectory mentioned earlier, the next command will download and unpack it:

    $ perl -mCPAN -e'CPAN::Shell->look("DBIx::Class")'
    DBIx-Class$ cd examples/Schema

Inspect the database:

    DBIx-Class/examples/Schema$ sqlite3 db/example.db .dump

You can also use a GUI database browser such as SQLite Manager <https://addons.mozilla.org/firefox/addon/sqlite-manager>.

Have a look at the schema classes files in the subdirectory MyApp. The "MyApp::Schema" class is the entry point for loading the other classes and interacting with the database through DBIC and the "Result" classes correspond to the tables in the database. DBIx::Class::Manual::Example shows how to write all that Perl code. That is almost never necessary, though. Instead use dbicdump (part of the distribution DBIx::Class::Schema::Loader) to automatically create schema classes files from an existing database. The chapter "Resetting the database" below shows an example invocation.

Connecting to the database¶

A schema object represents the database.

    use MyApp::Schema qw();
    my $schema = MyApp::Schema->connect('dbi:SQLite:db/example.db');

The first four arguments are the same as for "connect" in DBI.

Working with data¶

Almost all actions go through a resultset object.

Adding data

Via intermediate result objects:

    my $artist_ma = $schema->resultset('Artist')->create({
        name => 'Massive Attack',
    });
    my $cd_mezz = $artist_ma->create_related(cds => {
        title => 'Mezzanine',
    });
    for ('Angel', 'Teardrop') {
        $cd_mezz->create_related(tracks => {
            title => $_
        });
    }

Via relation accessors:

    $schema->resultset('Artist')->create({
        name => 'Metallica',
        cds => [
            {
                title => q{Kill 'Em All},
                tracks => [
                    { title => 'Jump in the Fire' },
                    { title => 'Whiplash' },
                ],
            },
            {
                title => 'ReLoad',
                tracks => [
                    { title => 'The Memory Remains' },
                    { title => 'The Unforgiven II' },
                    { title => 'Fuel' },
                ],
            },
        ],
    });

Columns that are not named are filled with default values. The value "undef" acts as a "NULL" in the database.

See the chapter "Introspecting the schema classes" below to find out where the non-obvious source name strings such as "Artist" and accessors such as "cds" and "tracks" come from.

Set the environment variable "DBI_TRACE='1|SQL'" to see the generated queries.

Retrieving data

Set up a condition.

    my $artists_starting_with_m = $schema->resultset('Artist')->search(
        {
            name => { like => 'M%' }
        }
    );

Iterate over result objects of class "MyApp::Schema::Result::Artist". Result objects represent a row and automatically get accessors for their column names.

    for my $artist ($artists_starting_with_m->all) {
        say $artist->name;
    }

Changing data

Change the release year of all CDs titled ReLoad.

    $schema->resultset('Cd')->search(
        {
            title => 'ReLoad',
        }
    )->update_all(
        {
            year => 1997,
        }
    );

Removing data

Removes all tracks titled Fuel regardless of which CD the belong to.

    $schema->resultset('Track')->search(
        {
            title => 'Fuel',
        }
    )->delete_all;

Introspecting the schema classes¶

This is useful for getting a feel for the naming of things in a REPL or during explorative programming.

From the root to the details:

    $schema->sources;                       # returns qw(Cd Track Artist)
    $schema->source('Cd')->columns;         # returns qw(cdid artist title year)
    $schema->source('Cd')->relationships;   # returns qw(artist tracks)

From a detail to the root:

    $some_result->result_source;            # returns appropriate source
    $some_resultset->result_source;
    $some_resultsource->schema;             # returns appropriate schema

Resetting the database¶

    # delete database file
    DBIx-Class/examples/Schema$ rm -f db/example.db
    # create database and set up tables from definition
    DBIx-Class/examples/Schema$ sqlite3 db/example.db < db/example.sql
    # fill them with data
    DBIx-Class/examples/Schema$ perl ./insertdb.pl
    # delete the schema classes files
    DBIx-Class/examples/Schema$ rm -rf MyApp
    # recreate schema classes files from database file
    DBIx-Class/examples/Schema$ dbicdump \
        -o dump_directory=. MyApp::Schema dbi:SQLite:db/example.db

Where to go next¶

If you want to exercise what you learned with a more complicated schema, load Northwind <http://code.google.com/p/northwindextended/> into your database.

If you want to transfer your existing SQL knowledge, read DBIx::Class::Manual::SQLHackers.

Continue with DBIx::Class::Tutorial and "WHERE TO START READING" in DBIx::Class.