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 ### complete paragraph rewording suggestion
29 To use DBIx::Class, we need to teach it about the layout of the underlying database. Several methods of doing this are available.
30 If you have an existing database the most straightforward way is to use the module L<DBIx::Class::Schema::Loader>, which
31 will introspect your database and generate individual classes representing every table and view in your database.
32 For new projects one usually writes these classes by hand as described below. If you find the methods provided by
33 L<DBIx::Class> core overly verbose, you can try to define your result classes via the more concise syntax of
34 L<DBIx::Class::Candy> (the result is fully compatible with L<DBIx::Class>).
36 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 L<SQL::Translator>.
40 Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes.
42 Run the included L<dbicdump> script.
44 dbicdump -o dump_directory=./lib \
45 -o components='["InflateColumn::DateTime"]' \
47 MyApp::Schema dbi:mysql:database=foo user pass '{ quote_names => 1 }'
50 =head2 Manual Result class creation (and understanding Loader results)
52 # note - CREATE INDEX is a bitch these days (requires a deploy hook) - perhaps not mentioning it at all is wise-ish?
53 This section covers the common and oft used CREATE DDL statements that DBIx::Class can replaces 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.
57 =head4 Standard basic table creation in SQL:
60 id INTEGER AUTO_INCREMENT,
61 username VARCHAR(255),
63 realname VARCHAR(255),
67 We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:
69 The recommended version:
71 package MyDatabase::Schema::Result::User;
75 use base 'DBIx::Class::Core';
77 __PACKAGE__->table('users');
78 __PACKAGE__->add_columns(
80 data_type => 'integer',
81 is_auto_increment => 1,
84 data_type => 'varchar',
91 data_type => 'varchar',
95 data_type => 'varchar',
99 __PACKAGE__->set_primary_key('id');
100 __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
103 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.
105 # perhaps "... with declared relations / Declaring relationships" ? "references" doesn't sound right in the context imho
106 =head4 Table creation with references:
108 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.
111 id INTEGER AUTO_INCREMENT,
113 created_date DATETIME,
116 INDEX posts_idx_user_id (user_id),
118 CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
121 In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class:
123 package MyDatabase::Schema::Result::Post;
126 use base 'DBIx::Class::Core';
128 __PACKAGE__->table('posts');
129 __PACKAGE__->add_columns(
131 data_type => 'integer',
132 is_auto_increment => 1,
135 data_type => 'integer',
138 data_type => 'datetime',
141 data_type => 'varchar',
149 __PACKAGE__->set_primary_key('id');
150 __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
153 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.
155 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.
157 Each relationship declaration in DBIC is one-way only.
158 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:
160 __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');
164 In SQL, a simple view that returns all users and their posts:
166 CREATE VIEW userposts
168 SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
170 JOIN posts ON (users.id = posts.user_id)
172 In DBIx::Class this can have a Result Class of its own:
174 package MyDatabase::Schema::Result::UserPosts;
176 use base qw/DBIx::Class::Core/;
178 # Defaults to 'DBIx::Class::ResultSource::Table' unless specified like this
179 __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
181 __PACKAGE__->table('user_posts');
183 # Do not emit SQL DDL for this particular resultsource
184 __PACKAGE__->result_source_instance->is_virtual(1);
186 __PACKAGE__->result_source_instance->view_definition(
187 "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
189 JOIN posts ON (users.id = posts.user_id)"
191 __PACKAGE__->add_columns(
193 data_type => 'integer',
196 data_type => 'varchar',
203 data_type => 'varchar',
207 data_type => 'datetime',
210 data_type => 'varchar',
218 __PACKAGE__->set_primary_key('user_id, post_id');
222 =head4 UNIQUE indexes
224 CREATE UNIQUE INDEX username_idx ON user (username);
226 To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class.
228 __PACKAGE__->add_unique_constraint('username_idx' => ['username']);
230 =head3 Outputting SQL DDL
232 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.
234 =head4 Deploy directly to the database
236 Create a schema object with the correct database connection, then call B<deploy> on it.
238 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
239 $schema->deploy({add_drop_table => 1});
241 L<DBIx::Class::Schema/deploy> has the documentation for the deploy method.
243 =head4 Write out SQL files
245 Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> method on it.
247 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
248 $schema->create_ddl_dir(['SQLite', 'MySQL']);
250 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.
252 ### IIRC this is not true - one can not do diffs without Schema::Versioned
253 ### which is not loaded by default (and will soon be deprecated anyway, given how far frew and jnap have gone)
255 =head4 SQL files for upgrades (ALTER TABLE)
257 DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are changed.
259 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.
261 After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>.
263 my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
264 $schema->create_ddl_dir(\@databases, undef, '.', '0.1');