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

=head1 NAME

DBIx::Class::Manual::Intro - Introduction to DBIx::Class

=head1 INTRODUCTION

You're bored with SQL, and want a native Perl interface for your database?  Or
you've been doing this for a while with L<Class::DBI>, and think there's a
better way?  You've come to the right place.

=head1 THE DBIx::Class WAY (CLIFF NOTES)

Here are a few simple tips that will help you get your bearings with
L<DBIC|DBIx::Class>.

=head2 Tables become Result classes

L<DBIC|DBIx::Class> needs to know what your Table structure looks like.  You
do that by defining L<Result classes|DBIx::Class::Manual::ResultClass>.  Each
Result class defines one Table, which defines the Columns it has, any
L<Relationships|DBIx::Class::Relationship> it has to other tables, and much more.

The important thing to understand:

  A Result class == Table

(most of the time, but just bear with my simplification)

=head2 It's all about the ResultSet

Let's say we defined a L<Result classes|DBIx::Class::Manual::ResultClass> (called
C<MyApp::Schema::Result::Artist>) for an C<album> table with three columns:
C<albumid>, C<artist>, and C<title>.  Any time we want to query this table, we'll
be creating a L<ResultSet|DBIx::Class::ResultSet> from its
L<Schema|DBIx::Class::Schema>.  For example, the results of:

  SELECT albumid, artist, title FROM album;

Would be represented like so:

  my $rs = $schema->resultset('Album')->search(undef, {
    columns => [qw{ albumid artist title }]
  });

L<DBIC|DBIx::Class> doesn't limit you to creating only simple ResultSets -- if you
wanted to do something like:

  SELECT title FROM album GROUP BY title;

You could easily achieve it, like this:

  my $rs = $schema->resultset('Album')->search(undef, {
    columns  => ['title'],
    group_by => ['title'],
  });

The important thing to understand:

  Instead of writing SQL queries manually, you ask a ResultSet object to
  generate them.

=head2 Search results are returned as "Rows"

Rows of the search from the database are blessed into
L<Result objects|DBIx::Class::Manual::ResultClass>.  This might seem conflicting,
but this is because a Result class is supposed to be customizable "toolset" for
both result management and table definition.

The important thing to understand:

  When -defining- a Result class, the purpose of the class is to:
    * Define the table, columns, and constraints
    * Define relationships to other tables

  When -using- a Result object, the purpose of the object is to:
    * Read data from a result (like a row)
    * Perform CRUD operations, based on that data
    * "Inflate" columns

=head2 Search is like "prepare"

L<DBIC|DBIx::Class> tends to wait until it absolutely must fetch information from the
database.  If you are returning a L<ResultSet|DBIx::Class::ResultSet>, the
query won't execute until you use a method that wants to access the data, such
as C<next> or C<first>.

  # Does not run any SQL statements yet
  my $rs = $schema->resultset('Album')->search(undef, {
    columns => [qw{ albumid artist title }]
  });

  # Will trigger the SQL query once and loop through the results
  while (my $result = $rs->next) {
    my $artist = $result->artist;
    ...
  }

=head1 SETTING UP DBIx::Class

Let's look at how you can set and use your first native L<DBIC|DBIx::Class> tree.

=head2 Which deployment method to take

There are a few different ways to create your L<DBIC|DBIx::Class> tree.  Which
recommended method to take depends on how your database currently exists:

  * If you are creating a brand new database, set up the tree manually, and then
    create the tables via $schema->deploy (or another deployment tool).
  * If the database already exists, use DBIx::Class::Schema::Loader or another
    schema builder.

Since L<DBIC|DBIx::Class> first came into the scene, there have been a number of
useful deployment tools (schema builders) that ease the process (from oldest to
newest):

=over

=item *

L<DBIx::Class::Schema::Loader> (existing DB only)

=item *

L<DBIx::Class::Schema::Versioned> (new or existing DB)

=item *

L<DBIx::Class::DeploymentHandler> (new or existing DB)

=item *

L<DBIx::Class::Migration> (new or existing DB)

=back

This document only covers the manual method and L<DBICSL|/Using
DBIx::Class::Schema::Loader>.  However, users with complex schemas might want
to check out the latest deployment tech to make life easier.

=head2 Setting it up manually

=head3 Schema

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 C<result_source> ("table", "model") classes, which
we will define later, using the L<load_namespaces|DBIx::Class::Schema/load_namespaces>
method:

  # load My::Schema::Result::* and their resultset classes
  __PACKAGE__->load_namespaces();

By default, this loads all the L<Result classes|DBIx::Class::Manual::ResultClass>
in the C<My::Schema::Result::> namespace, and also any ResultSet classes in the
C<My::Schema::ResultSet::> namespace.  (If missing, the ResultSets are
defaulted to be L<DBIx::Class::ResultSet> objects.)  You can change the Result
and ResultSet namespaces by using options to the
L<load_namespaces|DBIx::Class::Schema/load_namespaces> call.

It is also possible to do the same things manually by calling
L<load_classes|DBIx::Class::Schema/load_classes> for the
L<Result classes|DBIx::Class::Manual::ResultClass> and
defining in those classes any required ResultSet classes.

=head3 Result class

=head4 Header

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

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

Load any additional components you may need with the
L<load_components|Class::C3::Componentised/load_components( @comps )> method,
and provide component configuration if required.  For example, if you want
automatic row ordering:

  __PACKAGE__->load_components(qw/ Ordered /);
  __PACKAGE__->position_column('rank');

(See L<DBIx::Class::Ordered> for more information.)

Set the table for your class:

  __PACKAGE__->table('album');

=head4 Columns

Add columns to your class:

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

Each column can also be set up with its own accessor, data_type and other pieces
of information that it may be useful to have -- just pass C<add_columns> a hash:

  __PACKAGE__->add_columns(
    albumid => {
      accessor    => 'album',
      data_type   => 'integer',
      size        => 16,
      is_nullable => 0,
      is_auto_increment => 1,
    },
    artist  => {
      data_type   => 'integer',
      size        => 16,
      is_nullable => 0,
    },
    title   => {
      data_type   => 'varchar',
      size        => 256,
      is_nullable => 0,
    },
    rank    => {
      data_type   => 'integer',
      size        => 16,
      is_nullable => 0,
      default_value => 0,
    },
  );

L<DBIC|DBIx::Class> doesn't directly use most of this data yet, but various
related modules, such as L<HTML::FormHandler::Model::DBIC>, make use of it.  Also,
it allows you to create your database tables from your Schema, instead of the
other way around.  See L<DBIx::Class::Schema/deploy> for details.

See L<DBIx::Class::ResultSource/add_columns> for more details of the possible column
attributes.

Accessors are created for each column automatically, so C<My::Schema::Result::Album>
will have C<albumid> (or C<album>, when using the accessor shown above), C<artist>
and C<title> methods.

=head4 Primary Key

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 / );

=head4 Relationships

Define this class' relationships with other classes using either
L<belongs_to|DBIx::Class::Relationship/belongs_to> to describe a column which
contains an ID of another Table, or
L<has_many|DBIx::Class::Relationship/has_many> to make a predefined accessor for
fetching objects that contain this Table's foreign key:

  # in My::Schema::Result::Artist
  __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');

See L<DBIx::Class::Relationship> for more information about the various types of
available relationships and how you can design your own.

=head2 Using DBIx::Class::Schema::Loader

L<DBICSL|DBIx::Class::Schema::Loader> is an external module, and not part
of the L<DBIC|DBIx::Class> distribution.  It inspects your database, and
automatically creates classes for all the tables in your schema.

The simplest way to use it is via the L<dbicdump> script from the
L<DBIx::Class::Schema::Loader> distribution. For example:

    $ dbicdump -o dump_directory=./lib \
        -o components='["InflateColumn::DateTime"]' \
        MyApp::Schema dbi:mysql:mydb user pass

If you have a mixed-case database, use the C<preserve_case> option, e.g.:

    $ dbicdump -o dump_directory=./lib -o preserve_case=1 \
        -o components='["InflateColumn::DateTime"]' \
        MyApp::Schema dbi:mysql:mydb user pass

If you are using L<Catalyst>, then you can use the helper that comes with
L<Catalyst::Model::DBIC::Schema>:

    $ script/myapp_create.pl model MyDB DBIC::Schema MyDB::Schema \
        create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \
        on_connect_do='PRAGMA foreign_keys=ON' quote_char='"'

See L<Catalyst::Helper::Model::DBIC::Schema> for more information on this
helper.

See the L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base>
documentation for more information on the many loader options.

=head2 Connecting

To connect to your Schema, you need to provide the connection details or a
database handle.

=head3 Via connection details

The arguments are the same as for L<DBI/connect>:

  my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');

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 connections for you. If you use
multiple connections, you need to do this manually.

To execute some SQL statements on every connect you can add them as an option in
a special fifth argument to connect:

  my $another_schema = My::Schema->connect(
      $dsn,
      $user,
      $password,
      $attrs,
      { on_connect_do => \@on_connect_sql_statments }
  );

See L<DBIx::Class::Storage::DBI/connect_info> for more information about
this and other special C<connect>-time options.

=head3 Via a database handle

The supplied coderef is expected to return a single connected database handle
(e.g. a L<DBI> C<$dbh>)

  my $schema = My::Schema->connect (
    sub { Some::DBH::Factory->connect },
    \%extra_attrs,
  );

=head2 Basic usage

Once you've defined the basic classes, either manually or using
L<DBIx::Class::Schema::Loader>, you can start interacting with your database.

To access your database using your $schema object, you can fetch a
L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
calling the C<resultset> method.

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

  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::Result::Album> that represents this row.  Once you
have that row, you can access and update columns:

  $album->title('Physical Graffiti');
  my $title = $album->title; # $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');

Just like with L<Class::DBI>, you call C<update> to save your changes to the
database (by executing the actual C<UPDATE> statement):

  $album->update;

If needed, you can throw away your local changes:

  $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::Result::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:

  $new_album->delete;

You can also remove records without retrieving them first, by calling delete
directly on a ResultSet object.

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

=head2 Finding your objects

L<DBIC|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;

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;
  }

Or, you can update them all at once:

  $rs->update({ year => 2001 });

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 C<WHERE> clause:

  # 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 => { '-in' => [ 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 = My::Schema->resultset('Album')->search(
    { artist => 'Bob Marley' },
    { rows => 2, order_by => { -desc => 'year' } }
  );

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

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

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

=head1 NOTES

=head2 The Significance and Importance of Primary Keys

The concept of a L<primary key|DBIx::Class::ResultSource/set_primary_key> in
L<DBIC|DBIx::Class> warrants special discussion.  The formal definition (which somewhat
resembles that of a classic RDBMS) is I<a unique constraint that is least
likely to change after initial row creation>.  However, this is where the
similarity ends.  Any time you call a CRUD operation on a row (e.g.
L<delete|DBIx::Class::Row/delete>,
L<update|DBIx::Class::Row/update>,
L<discard_changes|DBIx::Class::Row/discard_changes>,
etc.), L<DBIC|DBIx::Class> will use the values of of the
L<primary key|DBIx::Class::ResultSource/set_primary_key> columns to populate
the C<WHERE> clause necessary to accomplish the operation.  This is why it is
important to declare a L<primary key|DBIx::Class::ResultSource/set_primary_key>
on all your result sources B<even if the underlying RDBMS does not have one>.
In a pinch, one can always declare each row identifiable by all its columns:

 __PACKAGE__->set_primary_key(__PACKAGE__->columns);

Note that L<DBIC|DBIx::Class> is smart enough to store a copy of the PK values before
any row-object changes take place, so even if you change the values of PK
columns, the C<WHERE> clause will remain correct.

If you elect not to declare a C<primary key>, L<DBIC|DBIx::Class> will behave correctly
by throwing exceptions on any row operation that relies on unique identifiable
rows.  If you inherited datasets with multiple identical rows in them, you can
still operate with such sets provided you only utilize
L<DBIx::Class::ResultSet> CRUD methods:
L<search|DBIx::Class::ResultSet/search>,
L<update|DBIx::Class::ResultSet/update>,
L<delete|DBIx::Class::ResultSet/delete>

For example, the following would not work (assuming C<People> does not have
a declared PK):

 my $row = $schema->resultset('People')
                   ->search({ last_name => 'Dantes' })
                    ->next;
 $row->update({ children => 2 }); # <-- exception thrown because $row isn't
                                  # necessarily unique

So, instead the following should be done:

 $schema->resultset('People')
         ->search({ last_name => 'Dantes' })
          ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2

=head2 Problems on RHEL5/CentOS5

There used to be an issue with the system perl on Red Hat Enterprise
Linux 5, some versions of Fedora and derived systems. Further
information on this can be found in L<DBIx::Class::Manual::Troubleshooting>

=head1 SEE ALSO

=over 4

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

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

=back

=cut