DBIx::Class::Tutorial::FromScratch - Describing your database
See "CONTENTS" in DBIx::Class::Tutorial
This chapter describes how to create a set of Perl modules using DBIx::Class that describe your database tables, indexes and relationships.
You should already have a good idea of what your database layout is, and how you want to be able to access it, before embarking on this tutorial. If you need help designing your database, have a look in Appendix3, or buy a good book on the subject.
If you already have a database that you are using you can still create these files by hand following this chapter. You can also use DBIx::Class::Schema::Loader to create them automatically. Read the documentation of that manual, or look in Appendix2 on how to do that.
Some terms needed for this chapter:
"Schema" in DBIx::Class::Manual::Glossary
"Result class" in DBIx::Class::Manual::Glossary
"Component" in DBIx::Class::Manual::Glossary
"ResultSet" in DBIx::Class::Manual::Glossary
"Primary key" in DBIx::Class::Manual::Glossary
To access your database in your Perl code via DBIx::Class you need to create Perl classes that define the layout of your database structure.
There are three types of source files available. A Schema class defines the top layer object that all other data is accessed via. The schema object is created with connection information for the particular database it will be talking to. You can create a second schema object to a different database (with the same table layout) using the same class, if you wish.
A Result class should be defined for each table or view to be accessed, which is used by DBIx::Class to represent a row of results for a query done via that particular table or view. Methods acting on a row of data can be added here.
ResultSet classes are optional and used to store commonly used methods on sets of data.
Current best practice suggests that you name your DBIx::Class files in the following way:
## The Schema class
<Databasename|appname>::Schema
## The result classes
<Databasename|appname>::Schema::Result::<tablename>
## The resultset classes
<Databasename|appname>::Schema::ResultSet::<tablename>Here, Databasename|appname refers to the top-level namespace for your application. If the set of modules are to be used as a standalone re-usable set for just this database, use the name of the database or something that identifies it. If your modules are part of an entire application, then the application top-level namespace goes here.
# Examples:
MyBlog::Schema
MyBlog::Schema::Result::Users
MyBlog::Schema::ResulSet::UsersThe basic Schema class is fairly simple, it needs to inherit from DBIx::Class::Schema and call a class method to load all the associated Result and ResultSet classes.
package MyBlog::Schema;
use warnings;
use strict;
use base 'DBIx::Class::Schema';
MyBlog::Schema->load_namespaces();
1;load_namespaces does the actual work here, it loads all the files found in the Result and ResultSet subnamespaces of your schema, see "A word about namespaces" above. It can be configured to use other namespaces, or load only a subset of the available classes. See "load_namespaces" in DBIx::Class::Schema for documentation.
The loaded files are assumed to be actual "Result classes" and "ResultSet classes" if anything else is found in the subnamespaces, the load will die.
For discussions of alternative styles and methods of writing Schema classes, see "Alternative Schema classes" below.
Row objects of database query results are based on your result classes and DBIx::Class::Row. For each table in your database that you wish to use with DBIx::Class, you need to create a result class file. It is not compulsory to create one for every table, and not reccommended if you have a large/complex database (hundreds of tables) and only need to use a few of them via DBIx::Class.
Result classes can also represent database views, and even select statements.
The Result class is also used to describe the table layout, its columns, indexes and so on. A set of class methods are provided which proxy to calls in DBIx::Class::ResultSource. An instance of ResultSource is created for you and updated using these methods. The actual ResultSource object itself is used internally by DBIx::Class, the user will rarely need it. It can be queried for example, to retrieve the list of columns that were defined for a particular Result class.
Result classes inherit from DBIx::Class directly. This gives them the ability to load components containing further methods which will then be available in the result objects and classes. For a standard database table class, at least the component DBIx::Class::Core should be loaded.
ResultSet classes can be added optionally, overriding the default DBIx::Class::ResultSet. These are useful for adding oft-used searches as methods to a set of data, to keep them in the model layer rather than in the calling code. One ResultSet class can be created for each Result class in the Schema.
Our user table looks like this (in mysql):
CREATE TABLE users (
id INTEGER AUTOINCREMENT PRIMARY KEY,
realname VARCHAR(255),
username VARCHAR(255),
password VARCHAR(255),
email VARCHAR (255)
);First we setup a result class for use.
1. package MyBlog::Schema::Result::User;
2. use strict;
3. use warnings;
4. use base 'DBIx::Class';
5.
6. MyBlog::Schema::Result::User->load_components('Core');
7. __PACKAGE__->table('users');Lines 1-4 are standard Perl code:
The package statement tells Perl which module the following code is defining.
The use strict and use warnings lines turn on useful error reporting in Perl.
use base tells Perl to make this module inherit from the given module.
A note about notation: __PACKAGE__ is the same as the current package name in Perl, and it's shorter to write than the package name itself, so you will see it all over the example code. It is documented in perldata.
Then we get to the DBIx::Class specific bits:
load_components comes from DBIx::Class::Componentised and is used to load a series of modules whose methods can delegate to each other. Thus components need to be loaded in a specific order. The DBIx::Class::Core component should always be loaded last so that its methods are called after those of other components.
For a some examples of other useful components, see DBIx::Class::Tutorial::??.
table is used to set the name of the database table this class is using. It is a method in DBIx::Class::ResultSourceProxy::Table which is loaded as a component by DBIx::Class::Core.
That's a long way of saying: You must call it after load_components.
Calling the table method sets up the DBIx::Class::ResultSource instance ready for adding columns to, so this method must also be called before add_columns (see below).
NB: It is sensible to name your Result classes in the singular form; User not Users; since later these will represent a single row, or user, object. Since tables contain multiple rows of users, they are generally named in the plural.
Now you can add lines describing the columns in your table.
8. __PACKAGE__->add_columns(
9. id => {
10. data_type => 'integer',
11. is_auto_increment => 1,
12. },
13. realname => {
14. data_type => 'varchar',
15. size => 255,
16. },
17. username => {
18. data_type => 'varchar',
19. size => 255,
20. },
21. password => {
22. data_type => 'varchar',
23. size => 255,
24. },
25. email => {
26. data_type => 'varchar',
27. size => 255,
28. },
29. );
30. __PACKAGE__->set_primary_key('id');
31. __PACKAGE__->add_unique_constraint('username_idx' => ['username']);add_columns is called to define all the columns in your table that you wish to tell DBIx::Class about (you may leave out some of the table's columns if you wish).
add_columns can provide as much or little description of the columns as it likes, in its simplest form, it can contain just a list of column names:
__PACKAGE__->add_columns(qw/id realname username password email/);This will work quite happily.
The longer version, used above, has several advantages. It can be used to produce actual database tables from the schema, which will contain proper types and sizes of columns in the database. It also serves as a useful reminder to the developer of the columns available.
For the full documentation, read "add_columns" in DBIx::Class::ResultSource.
Fully describing the columns including data types and sizes allows you to create the tables in your database from your schema definition. This is done using "deploy" in DBIx::Class::Schema.
We add a column called id to store the primary key of the table. This will store a unique integer for each row in the table. The primary key will use a self-incrementing field which most databases supply, so we set is_auto_increment to 1.
The varchar column type requires a size parameter to tell the database the maximum length data in the column can be.
Tell DBIx::Class which column or columns contain your primary key by calling set_primary_key and passing it a list of column names.
The primary key columns are used by DBIx::Class to determine which values it should add to the row object after it has been inserted into the database. They are also used when automatically joining two tables.
This and other methods dealing with primary keys are described in DBIx::Class::PK.
add_unique_constraint is called to let DBIx::Class know when your table has other columns which hold unique values across rows (other than the primary key, which must also be unique). The first argument is a name for the constraint which can be anything, the second is a arrayref of column names.
The table structure information alone will allow you to query individual tables using DBIx::Class. To do queries involving multiple joined tables, you need to also describe the relationships between them.
32. __PACKAGE__->has_many('posts', 'MyBlog::Schema::Result::Post', 'user_id');To describe a one to many relationship we call the has_many method. For this one, the posts table has a column named user_id that contains the id of the users table.
The first argument, posts, is a name for the relationship, this is used as an accessor to retrieve the related items. It is also used when creating queries to link tables.
The second argument, MyBlog::Schema::Result::Post, is the class name for the related Result class file.
The third argument for has_many, user_id, describes the column in the related table that contains the primary key of the table we are writing the relationship on.
For more on relationships see DBIx::Class::Relationship.
The data_type field for each column in the add_columns is a free text field, it is only used by DBIx::Class when deploying (creating tables) the schema to a database. At that point data_type values are converted to the appropriate type for your database by SQL::Translator.
"belongs_to" in DBIx::Class::Relationship
The posts table looks like this in mysql:
CREATE TABLE posts (
id INT AUTO_INCREMENT,
user_id INT,
created_date DATETIME,
title VARCHAR(255),
post TEXT,
INDEX posts_idx_user_id (user_id),
PRIMARY KEY (id),
CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);Create the Result class for it.
You will need another type of relationship for this class, belongs_to. Use it like this:
__PACKAGE__->belongs_to('user', 'MyBlog::Schema::Result::User', 'user_id');As before, the first argument, user, is the name of the relationship, used as an accessor to get the related User object. It is also used in searching to join across the tables.
The second argument is the related class, the User class we created before.
The third argument is the column in the current class that contains the primary key of the related class.
We will be meeting some more Result classes along the way, I will describe them when we need them.
If you're starting from scratch and don't actually have a database yet, run the following now to create one:
perl -MMyBlog::Schema -le'my $schema = MyBlog::Schema->connect("dbi:SQLite:test.db"); $schema->deploy();'Installing DBIx::Class will have also installed DBD::SQLite, a small one-file database which is useful for testing and portable databases for applications.
We will discuss deployment more at length later when talking about how to change your schema without having to destroy your existing data.
You now have a couple of well-defined Result classes we can use to actually create and query some data from your database.
Patches and suggestions welcome.
Jess Robinson <castaway@desert-island.me.uk>
Last modified: