DBIx::Class::Tutorial::Part1
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.
See DBIx::Class::Manual::Glossary.
Some terms needed for this doc:
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.
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.
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.
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.
To create a new set of DBIx::Class Result classes, you can either write them by hand, or create from an 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:
make_schema_at
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.
Re-run the make_schema_at command shown above.
See "SETTING UP DBIx::Class" in DBIx::Class::Manual::Intro.
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..
Loader
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.
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:
Name
PathID
Path
__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.
Breadcrumbs::Schema::Path
ID
(Yes, we think this relationship type should be renamed too, probably to refers_to or similar)
refers_to
This creates an accessor path, which we can call on a Name object which will retrieve the related object in the Path table.
path
## 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;
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.
names
Breadcrumbs::Schema::Name
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 } );
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.
$schema
For example, to connect to a mysql database named breadcrumbs:
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') } );
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
/Result class
## ResultSet for the Breadcrumbs::Schema::Name class my $name_rs = $schema->resultset('Name')
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
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.
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.
as
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.
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.
create
insert
## 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.
in_storage
new just creates a local Row object, it doesn't exist in the database until we call insert.
new
my $newpath = $schema->resultset('Path')->new( { path => 'support', }); print $newpath->in_storage(); ## 0 (FALSE) $newpath->insert(); print $newpath->in_storage(); ## 1 (TRUE)
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
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.
update
UPDATE
## All name rows with value 'Evaluation' my $name_rs = $schema->resultset('Name')->search( { 'me.name' => 'Evaluation', }); $name_rs->update({ name => 'Free Trial' }); ## UPDATE .. WHERE
Just call delete on a Row object.
delete
Row
## 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.
Call delete on a ResultSet object instead. This will run a DELETE statement with a WHERE clause, and will not cascade the deletes.
DELETE
WHERE
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.
delete_all
$path_rs->delete_all; ## many DELETE statements
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.
Part 2 of the tutorial
Loader documentation
More about searching
Add some sorta table descriptions to this doc.
Patches and suggestions welcome.
Jess Robinson <castaway@desert-island.me.uk>
1 POD Error
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head1'
To install DBIx::Class::Tutorial::Part1, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class::Tutorial::Part1
CPAN shell
perl -MCPAN -e shell install DBIx::Class::Tutorial::Part1
For more information on module installation, please visit the detailed CPAN module installation guide.