NAME

DBIx::Class::Tutorial::Part1

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.

GLOSSARY

See DBIx::Class::Manual::Glossary.

Some terms needed for this doc:

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.

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 DBIx::Class::ResultSource. These populate an instance of a table (Result source) object, with column and relation metadata.

ResultSet

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

Row

A 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 "Result class". A Row object can be used to update and delete data.

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.

Create Result classes from existing database

Install the separate module DBIx::Class::Schema::Loader.

Use its make_schema_at to extract your schema definitions from existing databases and create a complete 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 lib/Breadcrumbs/Schema/), plus the file Breadcrumbs/Schema.pm which is the 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 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 Part2.

If the database layout changes

Re-run the make_schema_at command shown above.

Create Result classes from scratch

See "SETTING UP DBIx::Class" in DBIx::Class::Manual::Intro.

Setting up relationships

If your database is mysql with myisam tables, or for some reason 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 DBIx::Class::Relationship.

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

belongs_to (foreign keys)

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

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

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

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

This creates an accessor path, which we can call on a Name object which will retrieve the related object in the 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;

has_many (reverse foreign key)

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

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

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

This creates an accessor names which can return a DBIx::Class::ResultSet (of which more later), or an array of 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 }
                       );

Creating a Schema object

To use a DBIx::Class schema, you must first create a $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 breadcrumbs:

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

For more information on the arguments to connect, see "connect_info" in DBIx::Class::Schema

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

Getting data

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

Remember "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 resultset on our "Schema" object, and pass it the name of a /Result class.

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

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

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 DBIx::Class::ResultSet, to retrieve the actual Row object, can can iterate over the set with 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'

"prefetch" in DBIx::Class::ResultSet takes the name of the relationship for the table we want to join to. In this case, it is the name of the "belongs_to" relationship we defined in the 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 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 SQL::Abstract docs.

Inserting data

To insert new rows, call create on a ResultSet object. This will first instantiate a new Row object, then call 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)

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.

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

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

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

Changing data

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 

Updating many rows with a resultset

Create a resultset containing the condition to select all the rows to update. Calling update on the resultset will run the 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
  

Deleting a row

Just call delete on a 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.

Delete multiple rows

Call delete on a ResultSet object instead. This will run a DELETE statement with a 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 delete_all on the ResultSet, which will delete each row and its related rows individually.

  $path_rs->delete_all;    ## many DELETE statements

CONCLUSIONS

You should now be able to:

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

• Add and understand simple relationships between tables.

• Connect to the database and create a schema object.

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

• Create new rows in the database.

• Update or delete rows in the database.

EXERCISES

WHERE TO GO NEXT

Part 2 of the tutorial

Loader documentation

More about searching

TODO

Add some sorta table descriptions to this doc.

Patches and suggestions welcome.

AUTHOR

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

cpanm DBIx::Class::Tutorial::Part1

perl -MCPAN -e shell
install DBIx::Class::Tutorial::Part1