- rename ResultSetInstance class to ResultSetProxy

[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / SchemaIntro.pod

=head1 NAME

DBIx::Class::Manual::SchemaIntro - Introduction to DBIx::Class::Schema

=head1 INTRODUCTION

This document describes how to set up DBIx::Class using the recommended
schema-based approach.

=head2 Setup

First, you should create your base schema class, which inherits from
L<DBIx::Class::Schema>:

  package My::Schema;
  use base qw/DBIx::Class::Schema/;

In this class you load your result_source ("table", "model") classes, which
we will define later, using the load_classes() method. You can specify which
classes to load manually: 

  # load My::Schema::Album and My::Schema::Artist
  __PACKAGE__->load_classes(qw/ Album Artist /);

Or load classes by namespace:

  # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes
  __PACKAGE__->load_classes(
    {
      'My::Schema' => qw/ Album Artist /,
      'My::OtherSchema' => qw/ LinerNotes /
    }
  );

Or let your schema class load all classes in its namespace automatically:

   # load My::Schema::*
  __PACKAGE__->load_classes();

Next, create each of the classes you want to load as specified above:

  package My::Schema::Album;
  use base qw/DBIx::Class/;

Load any components required by each class with the load_components() method.
This should consist of "Core" plus any additional components you want to use.
For example, if you use SQLite and want serial/auto-incrementing primary keys:

  __PACKAGE__->load_components(qw/ PK::Auto::SQLite Core /);

C<PK::Auto> classes exist for many databases; see
L<DBIx::Class::PK::Auto> for more information.

Set the table for your class:

  __PACKAGE__->table('album');

Add columns to your class:

  __PACKAGE__->add_columns(qw/ albumid artist title /);

Accessors are created for each column automatically, so My::Schema::Album will
have albumid(), artist() and title() methods.

Define a primary key for your class:

  __PACKAGE__->set_primary_key('albumid');

If you have a multi-column primary key, just pass a list instead:

  __PACKAGE__->set_primary_key( qw/ albumid artistid / );

You can define relationships for any of your classes. L<DBIx::Class> will
automatically fill in the correct namespace, so if you want to say
"a My::Schema::Album object belongs to a My::Schema::Artist object" you do not
need to include the namespace when declaring the relationship:

  __PACKAGE__->belongs_to('artist' => 'Artist');

That's all you need in terms of setup.

=head2 Usage

In your application code, you should first create a connected schema object:

  my $schema = My::Schema->connect( $dsn, $user, $password, $attrs );

You can create as many different schema instances as you need. So if you have
a second database you want to access:

  my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );

Note that L<DBIx::Class::Schema> does not cache connnections for you. If you
use multiple connections, you need to do this manually.

The simplest way to get a record is by primary key:

  my $schema = My::Schema->connect( ... );
  my $album = $schema->resultset('Album')->find(14);

This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause,
and return an instance of C<My::Schema::Album> that represents this
row. Once you have that row, you can access and update columns:

  $album->name('Physical Graffiti');
  my $title = $album->title; # holds 'Physical Graffiti'

If you prefer, you can use the C<set_column> and C<get_column>
accessors instead:

  $album->set_column('title', 'Presence');
  $title = $album->get_column('title');

You use C<update> to commit your changes to the database:

  $album->update();

If needed, you can throw away your local changes like this:

  $album->discard_changes() if $album->is_changed();

As you can see, C<is_changed> allows you to check if there are local
changes to your object.

=head2 Adding and removing rows

To create a new record in the database, you can use the C<create>
method.  It returns an instance of C<My::Schema::Album> that can be
used to access the data in the new record:

  my $new_album = $schema->resultset('Album')->create({
    title  => 'Wish You Were Here',
    artist => 'Pink Floyd'
  });

Now you can add data to the new record:

  $new_album->label('Capitol');
  $new_album->year('1975');
  $new_album->update;

Likewise, you can remove it from the database like this:

  $new_album->delete;

You can also remove records without or retrieving first.  This
operation takes the same kind of arguments as a search.

  # Delete all of Falco's albums
  $schema->resultset('Album')->delete({ artist => 'Falco' });

=head2 Finding your objects

L<DBIx::Class> provides a few different ways to retrieve data from
your database.  Here's one example:

  # Find all of Santana's albums
  my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });

In scalar context, as above, C<search> returns a
L<DBIx::Class::ResultSet> object.  It can be used to peek at the first
album returned by the database:

  my $album = $rs->first;
  print $album->title;

Or, you can loop over the albums and update each one:

  while (my $album = $rs->next) {
    print $album->artist . ' - ' . $album->title;
    $album->year(2001);
    $album->update;
  }

For more information on what you can do with a
L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>.

In list context, the C<search> method returns all of the matching
rows:

  # Fetch immediately all of Carlos Santana's albums
  my @albums = @{ $schema->resultset('Album')->search(
    { artist => 'Carlos Santana' }
  ) };
  foreach my $album (@albums) {
    print $album->artist . ' - ' . $album->title;
  }

We also provide a handy shortcut for doing a C<LIKE> search:

  # Find albums whose artist starts with 'Jimi'
  my $rs = MyApp::DB::Album->search_like({ artist => 'Jimi%' });

Or you can provide your own handmade C<WHERE> clause, like:

  # Find Peter Frampton albums from the year 1986
  my $where = 'artist = ? AND year = ?';
  my @bind  = ( 'Peter Frampton', 1986 );
  my $rs    = $schema->resultset('Album')->search_literal( $where, @bind );

The preferred way to generate complex queries is to provide a
L<SQL::Abstract> construct to C<search>:

  my $rs = $schema->resultset('Album')->search({
    artist  => { '!=', 'Janis Joplin' },
    year    => { '<' => 1980 },
    albumid => [ 1, 14, 15, 65, 43 ]
  });

This results in something like the following C<WHERE> clause:

  WHERE artist != 'Janis Joplin'
    AND year < 1980
    AND albumid IN (1, 14, 15, 65, 43)

For more examples of complex queries, see
L<DBIx::Class::Manual::Cookbook>.

The search can also be modified by passing another hash with
attributes:

  my @albums = $schema->resultset('Album')->search(
    { artist => 'Bob Marley' },
    { rows => 2, order_by => 'year DESC' }
  );

C<@albums> then holds the two most recent Bob Marley albums.

For a complete overview of the available attributes, see
L<DBIx::Class::ResultSet/ATTRIBUTES>.

=head1 SEE ALSO

=over 4

=item * L<DBIx::Class::Manual::Cookbook>

=item * L<DBIx::Class::Manual::FAQ>

=back

=cut