fix typo in search_like example

[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.

To execute some sql statements on every connect you can pass them to your schema after the connect:

  $schema->storage->on_connect_do(\@on_connect_sql_statments);

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 = $schema->resultset('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