3 DBIx::Class::Manual::SQLHackers::CREATE - DBIx::Class for SQL Hackers - CREATE
5 =head1 Table of Contents
9 =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction>
13 =item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT>
15 =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT>
17 =item L<UPDATE|DBIx::Class::Manual::SQLHackers::UPDATE>
19 =item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE>
21 =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions>
25 =head1 Database structure
27 To use DBIx::Class, we need to teach it about the layout of the
28 underlying database. Several methods of doing this are available. If
29 you have an existing database the most straightforward way is to use
30 the module L<DBIx::Class::Schema::Loader>, which will introspect your
31 database and generate individual classes representing every table and
32 view in your database. For new projects one usually writes these
33 classes by hand as described below. If you find the methods provided
34 by L<DBIx::Class> core overly verbose, you can try to define your
35 result classes via the more concise syntax of L<DBIx::Class::Candy>
36 (the result is fully compatible with L<DBIx::Class>).
38 Once a DBIx::Class schema (set of classes describing the database) has
39 been created, built-in methods can be used to export it as SQL DDL
40 using L<SQL::Translator>.
44 Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes.
46 Run the included L<dbicdump> script.
48 dbicdump -o dump_directory=./lib \
49 -o components='["InflateColumn::DateTime"]' \
51 MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
54 =head2 Manual Result class creation (and understanding Loader results)
56 This section covers the common and oft used CREATE DDL statements that DBIx::Class can replace with Perl classes: B<CREATE TABLE>, B<CREATE VIEW> and B<CREATE INDEX>. The classes can be used to write the actual SQL DDL to the database or disc, if required.
60 =head4 Standard basic table creation in SQL
63 id INTEGER AUTO_INCREMENT,
64 username VARCHAR(255),
66 realname VARCHAR(255),
70 We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:
72 The recommended version:
74 package MyDatabase::Schema::Result::User;
78 use base 'DBIx::Class::Core';
80 __PACKAGE__->table('users');
81 __PACKAGE__->add_columns(
83 data_type => 'integer',
84 is_auto_increment => 1,
87 data_type => 'varchar',
94 data_type => 'varchar',
98 data_type => 'varchar',
102 __PACKAGE__->set_primary_key('id');
103 __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
106 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.
108 =head4 Table creation with references
110 A relational database isn't worth much if we don't actually use references and constraints, so here is an example which constrains the B<user_id> column to only contain B<id> values from the *users* table.
113 id INTEGER AUTO_INCREMENT,
115 created_date DATETIME,
118 INDEX posts_idx_user_id (user_id),
120 CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
123 In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class:
125 package MyDatabase::Schema::Result::Post;
128 use base 'DBIx::Class::Core';
130 __PACKAGE__->table('posts');
131 __PACKAGE__->add_columns(
133 data_type => 'integer',
134 is_auto_increment => 1,
137 data_type => 'integer',
140 data_type => 'datetime',
143 data_type => 'varchar',
151 __PACKAGE__->set_primary_key('id');
152 __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
155 The B<belongs_to> relation creates a B<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.
157 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.
159 Each relationship declaration in DBIC is one-way only.
160 To allow access from the B<user> object back to the posts they have written, we need to define another relationship in the User class:
162 __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');
166 In SQL, a simple view that returns all users and their posts:
168 CREATE VIEW userposts
170 SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
172 JOIN posts ON (users.id = posts.user_id)
174 In DBIx::Class this can have a Result Class of its own:
176 package MyDatabase::Schema::Result::UserPosts;
178 use base qw/DBIx::Class::Core/;
180 # Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
181 __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
183 __PACKAGE__->table('userposts');
185 # Do not emit SQL DDL for this particular resultsource
186 __PACKAGE__->result_source_instance->is_virtual(1);
188 __PACKAGE__->result_source_instance->view_definition(
189 "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
191 JOIN posts ON (users.id = posts.user_id)"
193 __PACKAGE__->add_columns(
195 data_type => 'integer',
198 data_type => 'varchar',
205 data_type => 'varchar',
209 data_type => 'datetime',
212 data_type => 'varchar',
220 __PACKAGE__->set_primary_key('user_id', 'post_id');
224 =head4 UNIQUE indexes
226 CREATE UNIQUE INDEX username_idx ON user (username);
228 To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class.
230 __PACKAGE__->add_unique_constraint('username_idx' => ['username']);
232 =head4 NON-UNIQUE indexes
234 CREATE INDEX user_email_idx ON user (username, email);
236 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.
238 The B<sqlt_deploy_hook> method allows you to add L<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.
240 sub sqlt_deploy_hook {
241 my ($self, $sqlt_table) = @_;
243 $sqlt_table->add_index(name => 'user_email_idx', fields => ['username', 'email']);
247 =head3 Outputting SQL DDL
249 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.
251 =head4 Deploy directly to the database
253 Create a schema object with the correct database connection, then call B<deploy> on it.
255 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
256 $schema->deploy({add_drop_table => 1});
258 L<DBIx::Class::Schema/deploy> has the documentation for the deploy method.
260 =head4 Write out SQL files
262 Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
264 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
265 $schema->create_ddl_dir(['SQLite', 'MySQL']);
267 If called with no arguments, this method will create an SQL file each for MySQL, PostgreSQL and SQLite. More databases are supported by L<SQL::Translator> if necessary.
269 =head4 SQL files for upgrades (ALTER TABLE)
271 DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
273 To do this, make sure that you set a B<$VERSION> in your main Schema class, and run B<create_ddl_dir> on the initial version to provide a baseline.
275 After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>.
277 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
278 $schema->create_ddl_dir(\@databases, undef, '.', '0.1');