=head1 NAME 

DBIx::Class::Tutorial::Part1

=head1 DESCRIPTION

This is part one of a DBIx::Class tutorial. In which you should learn
how to create a set of DBIx::Class files from an existing database,
and do some simple searching, inserting, updating and deleting. More
complex things come later.

=head1 GLOSSARY

See L<DBIx::Class::Manual::Glossary>.

Some terms needed for this doc:

=over 

=item Schema

The schema object is the main point of contact with the rest of
DBIx::Class. A schema is created initially using the database's
connection details, and is kept around until we are completely done
with the database. Although the schema is passed the connection
details, it does not use them until we actually ask for data via a
resultset.

=item Result class

A class whose objects represent a single result from a resultset,
usually corresponding to a single row of a table. Result classes are
defined by calls to class methods proxyied from 
L<DBIx::Class::ResultSource>. These populate an instance of a table
(Result source) object, with column and relation metadata.

=item ResultSet

A L<DBIx::Class::ResultSet>, an object which represents a database
query, including all conditions/clauses. Creating one does not run the
associated query, it is stored and used when actual data objects are
requested. The simplest form of ResultSet represents an entire
table. Actual data is returned from a ResultSet in the form of a Row
or a ResultSetColumn.

=item Row

A L<DBIx::Class::Row> represents an actual row of data resulting from
a database query. This could be an entire row, or just certain fields
as restricted by a ResultSet. Fields in the Row object are as
described by its L</Result class>. A Row object can be used to update
and delete data.

=head1 GETTING STARTED

To create a new set of DBIx::Class Result classes, you can either
write them by hand, or create from an existing database.

=head2 Create Result classes from existing database

Install the separate module L<DBIx::Class::Schema::Loader>.

Use its C<make_schema_at> to extract your schema definitions from
existing databases and create a complete L<DBIx::Class> schema for
you, example:

  perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("Breadcrumbs::Schema", { debug => 1 }, [ "dbi:mysql:dbname=Breadcrumbs","user", "passwd" ])'

This will create a file for each database table (in
F<lib/Breadcrumbs/Schema/>), plus the file F<Breadcrumbs/Schema.pm> which is
the L<DBIx::Class::Schema> file.

The table files will contain information about the columns and their
types. If the database is supports foreign keys, it will also extract
relationships based on foreign key references etc. An md5 sum of the
contents is added, so that the user can add more relations or other
methods to the file, and a later update will not overwrite them.

Look in the files that have been created. For help on what has been
created, see L<DBIx::Class::ResultSource>. For some databases, the
schema loader may not have been able to correctly determine your table
relationships, and you will have to enter your them by hand. You can
also create relationships in your ResultSource classes that don't
exist in the database. For how to set relationships, see
L<Part2|DBIx::Class::Tutorial::Part2>.

=head2 If the database layout changes

Re-run the C<make_schema_at> command shown above. 

=head2 Create Result classes from scratch

See L<DBIx::Class::Manual::Intro/SETTING UP DBIx::Class>.

=head1 Setting up relationships

If your database is mysql with myisam tables, or for some reason
C<Loader> could not determine your table relationships, you will have
to add the them yourself. These are the most useful part of
DBIx::Class, otherwise we might as well just use DBI..

There are two main/useful relationship types, for the rest and a more wordy
description, see L<DBIx::Class::Relationship>.

The B<name> of each relationship (the first argument), is important,
it is used both as an accessor and as a key to join across tables.

=head2 belongs_to (foreign keys)

Breadcrumbs's C<Name> table has a C<PathID> column which contains
an ID from the C<Path> table. To make the Path object simple to
retrieve and update, we add the following to the F<Name.pm> file,
after the md5 sum from Loader:

  __PACKAGE__->belongs_to('path', 'Breadcrumbs::Schema::Path', 'PathID');

Read as: The value in the C<PathID> column belongs_to the table
represented by C<Breadcrumbs::Schema::Path>. Since we didn't specify,
this will default to assuming that the C<PathID> field contains the
C<ID> of the C<Path> table.

(Yes, we think this relationship type should be renamed too, probably
to C<refers_to> or similar)

This creates an accessor C<path>, which we can call on a Name
L<object|DBIx::Class::Row> which will retrieve the related
L<object|DBIx::Class::Row> in the C<Path> table.

  ## Print the path of the current name entry
  print $name->path->path;

We can also set a PathID for a new Name row by calling:

  $name->path($pathobj);
  $name->update;

=head2 has_many (reverse foreign key)

Going in the opposite direction, each C<Path> row has 0 to many
C<Name> entries associated with it, indicated by the C<PathID> column
in the C<Name> table. We can make it simple to retrieve all the name
values for a particular C<Path> row:

  __PACKAGE__->has_many('names', 'Breadcrumbs::Schema::Name', 'PathID');

Read as: This class has many C<names> in C<Breadcrumbs::Schema::Name>
linked via the C<PathID> column.

This creates an accessor C<names> which can return a
L<DBIx::Class::ResultSet> (of which more later), or an array of
C<Name> objects.

  ## find all names for current path entry
  foreach my $name ($path->names->all)
  {
     print $name->name;
  }

Add a new name for the current path, assuming we have a language
object as well:

  $path->create_related('names', 
                        { name => 'Products',
                          lang => $lang }
                       );

=head1 Creating a Schema object

To use a L<DBIx::Class> schema, you must first create a C<$schema>
object. This object must not go out of scope while you are using any
objects created from it.

For example, to connect to a mysql database named C<breadcrumbs>:

  my $schema = Breadcrumbs::Schema::Path->connect('dbi:mysql:dbname=breadcrumbs', 'user', 'pass');

For more information on the arguments to connect, see
L<DBIx::Class::Schema/connect_info>

To make use of an existing mechanism to store credentials for
databases, we can do something like:

  my $schema = Breadcrumbs::Schema->connect(sub { get_dbh('Breadcrumbs') } );

=head1 Getting data

Whether fetching complete rows, searching for rows, or fetching data
from multiple tables, the methods are much the same, all return
L<DBIx::Class::Row> objects eventually.

Remember L</ResultSet>s? To start any query we need to start from a
resultset representing the main table accessed in the query. To fetch
a resultset, we call C<resultset> on our L</Schema> object, and pass
it the B<name> of a C</Result class>.

  ## ResultSet for the Breadcrumbs::Schema::Name class
  my $name_rs = $schema->resultset('Name')

=head2 find (single row by unique key)

To fetch a full row using its Primary Key (they all have PKs,
right?), we can retrieve a Row object directly from a ResultSet
representing the table:

  ## Just PK:
  my $name1 = $schema->resultset('Name')->find(1);

  ## More meaningful:
  my $name1 = $schema->resultset('Name')->find({ id => 1 });

The Row object contains all the values of all the fields defined in
the Result class. Not necessarily in the table. Each column has an
accessor which is by default the name of the column lower-cased (When
using Loader).

  my $namename = $name1->name;   ## Japan
  my $langid   = $name1->langid; ## 1

=head2 search (multiple objects)

To fetch a particular Name row and its Path at the
same time, using only one database query:

  my $name_rs = $schema->resultset('Name')->search(
    { 'me.id' => 1 },             ## WHERE
    { prefetch => [ 'path' ] }    ## JOIN/SELECT

This creates a L<DBIx::Class::ResultSet>, to retrieve the actual Row
object, can can iterate over the set with C<next>.

  ## Fetch first entry using iteration:
  my $namerow = $name_rs->next;

The following SQL is executed:

  SELECT me.ID, me.Name, me.LangID, me.PathID, path.ID, path.Path, path.Parent, path.Position, path.MenuID FROM Name me  JOIN Path path ON ( path.ID = me.PathID ) WHERE ( me.id = ? ): '1'

L<DBIx::Class::ResultSet/prefetch> takes the B<name> of the
relationship for the table we want to join to. In this case, it is the
B<name> of the L</belongs_to> relationship we defined in the C<Name>
class earlier.

As with the find, the data is retrieved using the column-name
accessors. We can also retrieve a Row object representing the path:

  my $pathrow = $namerow->path;

  my $actualpath = $pathrow->path;   ## companyinfo/careers/jp
  my $pathparent = $pathrow->parent; ## 36

To fetch just a few columns:

  my $name_rs = $schema->resultset('Name')->search(
    { 'me.id' => 1 },                                ## WHERE
    { 
      select   => [ qw/id name/ ],                   ## SELECT
      as       => [ qw/id name/ ],
    }
  );

This will only select the ID and Name columns. The C<as> attribute
names the columns.

To add more complex conditions:

  ## All names where the path matches '/support/%'
  my $name_search = $schema->resultset('Name')->search(
    {
      'path.path' => { 'like' => '/support/%' },  ## WHERE .. LIKE ..
    },
    { 
      'join' => [ 'path' ],                       ## JOIN
    }
  );

For more conditions, functions etc, see the L<SQL::Abstract> docs.

=head1 Inserting data

To insert new rows, call C<create> on a ResultSet object. This will
first instantiate a new Row object, then call C<insert> on it. This
can be done individually if needed.

  ## INSERT INTO .. 
  my $newname = $schema->resultset('Name')->create(
  {
     name   => 'Support',
     pathid => $pathid,
     langid => $langid,
  });
  print $newname->in_storage();  ## 1 (TRUE)

C<in_storage> tells us whether the Row object is in the database or
not. This is set when fetching data, and when we insert a new row.

C<new> just creates a local Row object, it doesn't exist in the
database until we call C<insert>.

  my $newpath = $schema->resultset('Path')->new(
  {
     path => 'support',
  });
  print $newpath->in_storage();  ## 0 (FALSE)

  $newpath->insert();
  print $newpath->in_storage();  ## 1 (TRUE)

=head1 Changing data

=head2 Updating one row

The column-name accessors used to fetch data from a row can also be
used to set new values, eg:

  ## Nonsensically change the language of a given Name row
  my $namerow = $schema->resultset('Name')->find({ name => 'Japan' });
  $namerow->langid(2);
  $namerow->update;    ## UPDATE 

=head2 Updating many rows with a resultset

Create a resultset containing the condition to select all the rows to
update. Calling C<update> on the resultset will run the C<UPDATE>
command using the condition in the resultset.

  ## All name rows with value 'Evaluation'
  my $name_rs = $schema->resultset('Name')->search(
  {
    'me.name' => 'Evaluation',
  });
  $name_rs->update({ name => 'Free Trial' });  ## UPDATE .. WHERE
  
=head3 Deleting a row

Just call C<delete> on a C<Row> object.

  ## No more products/es !
  my $pathrow = $schema->resultset('Path')->find({ path => 'products/es' });
  $pathrow->delete;

This will also delete related rows that would otherwise be
inconsistent. It will remove them *after* the main row. This cascading
can be turned off on a relationship if necessary.

=head3 Delete multiple rows

Call C<delete> on a ResultSet object instead. This will run a
C<DELETE> statement with a C<WHERE> clause, and will not cascade the
deletes.

  my $path_rs = $schema->resultset('Path')->search(
  {
    'me.path' => 'products/es',
  });
  $path_rs->delete;     ## DELETE .. WHERE 

To delete multiple rows with cascading, call C<delete_all> on the
ResultSet, which will delete each row and its related rows
individually.

  $path_rs->delete_all;    ## many DELETE statements

=head1 CONCLUSIONS

You should now be able to:

=over

=item *

Create a set of new DBIx::Class classes from an existing database.

=item *

Add and understand simple relationships between tables.

=item *

Connect to the database and create a schema object.

=item *

Fetch rows from the database using primary keys or simple search conditions.

=item *

Create new rows in the database.

=item *

Update or delete rows in the database.

=back

=head1 EXERCISES

=head1 WHERE TO GO NEXT

L<Part 2 of the tutorial|DBIx::Class::Tutorial::Part2>

L<Loader documentation|DBIx::Class::Schema::Loader>

L<More about searching|DBIx::Class::ResultSet/search>


=head1 TODO

Add some sorta table descriptions to this doc. 

Patches and suggestions welcome.

=head1 AUTHOR

Jess Robinson <castaway@desert-island.me.uk>