NAME
DBIx::Class::Manual::SQLHackers::CREATE - DBIx::Class for SQL Hackers - CREATE
Table of Contents
Database structure
To use DBIx::Class, we need to teach it about the layout of the underlying database. Several methods of doing this are available. If you have an existing database the most straightforward way is to use the module DBIx::Class::Schema::Loader, which will introspect your database and generate individual classes representing every table and view in your database. For new projects one usually writes these classes by hand as described below. If you find the methods provided by DBIx::Class core overly verbose, you can try to define your result classes via the more concise syntax of DBIx::Class::Candy (the result is fully compatible with DBIx::Class).
Once a DBIx::Class schema (set of classes describing the database) has been created, built-in methods can be used to export it as SQL DDL using SQL::Translator.
Using Loader
Install DBIx::Class::Schema::Loader and decide on a name for your schema classes.
Run the included dbicdump script.
dbicdump -o dump_directory=./lib \
-o components='["InflateColumn::DateTime"]' \
-o preserve_case=1 \
MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'Manual Result class creation (and understanding Loader results)
This section covers the common and oft used CREATE DDL statements that DBIx::Class can replaces with Perl classes: CREATE TABLE, CREATE VIEW and CREATE INDEX. The classes can be used to write the actual SQL DDL to the database or disc, if required.
CREATE TABLE
Standard basic table creation in SQL:
CREATE TABLE users (
id INTEGER AUTO_INCREMENT,
username VARCHAR(255),
dob DATE,
realname VARCHAR(255),
password VARCHAR(255)
);We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:
The recommended version:
package MyDatabase::Schema::Result::User;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('users');
__PACKAGE__->add_columns(
id => {
data_type => 'integer',
is_auto_increment => 1,
},
username => {
data_type => 'varchar',
size => 255,
},
dob => {
data_type => 'date',
},
realname => {
data_type => 'varchar',
size => 255,
},
password => {
data_type => 'varchar',
size => 255,
},
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
1;The fully descriptive version is required if you want to have DBIx::Class create your CREATE TABLE sql for you later. Many DBIC components also use settings in the column info hashrefs to decide how to treat the data for those columns.
Table creation with references:
A relational database isn't worth much if we don't actually use references and constraints, so here is an example which constrains the user_id column to only contain id values from the *users* table.
CREATE TABLE posts (
id INTEGER AUTO_INCREMENT,
user_id INTEGER,
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)
);In DBIx::Class this is achieved by adding relationship definitions to the class:
package MyDatabase::Schema::Result::Post;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('posts');
__PACKAGE__->add_columns(
id => {
data_type => 'integer',
is_auto_increment => 1,
},
user_id => {
data_type => 'integer',
},
created_date => {
data_type => 'datetime',
},
title => {
data_type => 'varchar',
size => 255,
},
post => {
data_type => 'text',
},
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
1;The belongs_to relation creates a user method which returns the user object, as well as storing JOIN information to be used when querying with JOINs. When not explicitly specified (as in this example), the columns for the JOIN clause default to the remote PRIMARY KEY column set.
Relationships may also be specified with completely custom JOIN conditions, using any columns, whether the database has them defined as constraints or not, or literal values.
Each relationship declaration in DBIC is one-way only. To allow access from the user object back to the posts they have written, we need to define another relationship in the User class:
__PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');CREATE VIEW
In SQL, a simple view that returns all users and their posts:
CREATE VIEW userposts
AS
SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
FROM users
JOIN posts ON (users.id = posts.user_id)In DBIx::Class this can have a Result Class of its own:
package MyDatabase::Schema::Result::UserPosts;
use base qw/DBIx::Class::Core/;
# Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
__PACKAGE__->table('user_posts');
# Do not emit SQL DDL for this particular resultsource
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition(
"SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
FROM users
JOIN posts ON (users.id = posts.user_id)"
);
__PACKAGE__->add_columns(
user_id => {
data_type => 'integer',
},
username => {
data_type => 'varchar',
size => 255,
},
dob => {
data_type => 'date',
},
realname => {
data_type => 'varchar',
size => 255,
},
created_date => {
data_type => 'datetime',
},
title => {
data_type => 'varchar',
size => 255,
},
post => {
data_type => 'text',
},
);
__PACKAGE__->set_primary_key('user_id, post_id');CREATE INDEX
UNIQUE indexes
CREATE UNIQUE INDEX username_idx ON user (username);To add extra unique indexes, add the add_unique_constraint call to your Result Class.
__PACKAGE__->add_unique_constraint('username_idx' => ['username']);NON-UNIQUE indexes
CREATE INDEX user_email_idx ON user (username, email);These are not created or used by DBIx::Class itself, but can be added so that deploying (creating DDL SQL from your Schema) can include them.
The sqlt_deply_hook method allows you to add SQL::Translator code to your Result class. It is called with the SQL::Translator::Schema::Table object, and allows you to amend the Table before it is converted into SQL.
sub sqlt_deploy_hook {
my ($self, $sqlt_table) = @_;
$sqlt_table->add_index(name => 'user_email_idx', fields => ['username', 'email']);
}Outputting SQL DDL
Once the DBIC schema has been defined, you can outout the SQL DDL needed to create the schema in your database (using the RDBMS-specific flavor of SQL DDL) in one of several ways.
Deploy directly to the database
Create a schema object with the correct database connection, then call deploy on it.
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
$schema->deploy({add_drop_table => 1});"deploy" in DBIx::Class::Schema has the documentation for the deploy method.
Write out SQL files
Create a schema object with the a database connection (any will do), and call the create_ddl_dir method on it.
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
$schema->create_ddl_dir(['SQLite', 'MySQL']);If called with no arguments, this method will create an SQL file each for MySQL, PostgreSQL and SQLite. More databases are supported by SQL::Translator if necessary.
SQL files for upgrades (ALTER TABLE)
DBIC can also make use of SQL::Translator::Diff to write out ALTER TABLE statements when the schema classes are changed.
To do this, make sure that you set a $VERSION in your main Schema class, and run create_ddl_dir on the initial version to provide a baseline.
After the schema has changed, change the $VERSION value and re-run create_ddl_dir.
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
$schema->create_ddl_dir(\@databases, undef, '.', '0.1');