Commit | Line | Data |
6c2a4396 |
1 | =head1 NAME |
2 | |
3 | DBIx::Class::Manual::SQLHackers::CREATE - DBIx::Class for SQL Hackers - CREATE |
4 | |
5 | =head1 Table of Contents |
6 | |
7 | =over |
8 | |
9 | =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction> |
10 | |
11 | =item CREATE |
12 | |
13 | =item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT> |
14 | |
15 | =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT> |
16 | |
17 | =item L<UPDATE|DBIx::Class::Manual::SQLHackers::UPDATE> |
18 | |
19 | =item L<DELETE|DBIx::Class::Manual::SQLHackers::DELETE> |
20 | |
21 | =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions> |
22 | |
23 | =head1 Database structure |
24 | |
25 | To use DBIx::Class, we need to teach it about the layout of the underlying database. Several methods are supported. The built-in method involves defining your database structure as a set of perl modules. The other oft used method is to import the definitions from an existing database using the module |
26 | L<DBIx::Class::Schema::Loader>. |
27 | |
28 | Once a DBIx::Class schema (set of classes describing the database) has been created, built-in methods can be used to export it to SQL using L<SQL::Translator>. |
29 | |
30 | =head2 Using Loader |
31 | |
32 | Install L<DBIx::Class::Schema::Loader> and decide on a name for your schema classes. |
33 | |
34 | Run the included L<dbicdump> script. |
35 | |
36 | dbicdump -Ilib -o dump_directory=./lib \ |
37 | -o components='["InflateColumn::DateTime"]' \ |
38 | -o preserve_case=1 \ |
39 | MyApp::Schema dbi:mysql:database=foo user pass '{ quote_char => "`" }' |
40 | |
41 | |
42 | =head2 Manual Result class creation (and understanding Loader results) |
43 | |
44 | =head3 CREATE TABLE |
45 | |
46 | =head4 Standard basic table creation in SQL: |
47 | |
48 | CREATE TABLE users ( |
49 | id INTEGER AUTO_INCREMENT, |
50 | username VARCHAR(255), |
51 | dob DATE, |
52 | realname VARCHAR(255), |
53 | password VARCHAR(255) |
54 | ); |
55 | |
56 | We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class: |
57 | |
58 | The recommended version: |
59 | |
60 | package MyDatabase::Schema::Result::User; |
61 | use strict; |
62 | use warnings; |
63 | |
64 | use base 'DBIx::Class::Core'; |
65 | |
66 | __PACKAGE__->table('users'); |
67 | __PACKAGE__->add_columns( |
68 | id => { |
69 | data_type => 'integer', |
70 | is_auto_increment => 1, |
71 | }, |
72 | username => { |
73 | data_type => 'varchar', |
74 | size => 255, |
75 | }, |
76 | dob => { |
77 | data_type => 'date', |
78 | }, |
79 | realname => { |
80 | data_type => 'varchar', |
81 | size => 255, |
82 | }, |
83 | password => { |
84 | data_type => 'varchar', |
85 | size => 255, |
86 | }, |
87 | ); |
88 | __PACKAGE__->set_primary_key('id'); |
89 | __PACKAGE__->add_unique_constraint('uniq_username' => ['username']); |
90 | 1; |
91 | |
92 | 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. |
93 | |
94 | The snappy version: |
95 | |
96 | package MyDatabase::Schema::Result::User; |
97 | use strict; |
98 | use warnings; |
99 | |
100 | use base 'DBIx::Class::Core'; |
101 | |
102 | __PACKAGE__->table('users'); |
103 | __PACKAGE__->add_columns( |
104 | id => { |
105 | data_type => 'integer', |
106 | is_auto_increment => 1, |
107 | }, |
108 | qw/username dob realname password/ |
109 | ); |
110 | __PACKAGE__->set_primary_key('id'); |
111 | |
112 | 1; |
113 | |
114 | This shows a minimal Result class to represent our "users" table. DBIx::Class itself does not use or care about the field types or lengths. However many external components exist on CPAN, and some of may use this information. |
115 | |
116 | =head4 Table creation with references: |
117 | |
118 | 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. |
119 | |
120 | CREATE TABLE posts ( |
121 | id INTEGER AUTO_INCREMENT, |
122 | user_id INTEGER, |
123 | created_date DATETIME, |
124 | title VARCHAR(255), |
125 | post TEXT, |
126 | INDEX posts_idx_user_id (user_id), |
127 | PRIMARY KEY (id), |
128 | CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id) |
129 | ); |
130 | |
131 | In DBIx::Class this is achieved by adding L<relationship|DBIx::Class::Relationship> definitions to the class: |
132 | |
133 | package MyDatabase::Schema::Result::Post; |
134 | use strict; |
135 | use warnings; |
136 | use base 'DBIx::Class::Core'; |
137 | |
138 | __PACKAGE__->table('posts'); |
139 | __PACKAGE__->add_columns( |
140 | id => { |
141 | data_type => 'integer', |
142 | is_auto_increment => 1, |
143 | }, |
144 | user_id => { |
145 | data_type => 'integer', |
146 | }, |
147 | created_date => { |
148 | data_type => 'datetime', |
149 | }, |
150 | title => { |
151 | data_type => 'varchar', |
152 | size => 255, |
153 | }, |
154 | post => { |
155 | data_type => 'text', |
156 | }, |
157 | ); |
158 | |
159 | __PACKAGE__->set_primary_key('id'); |
160 | __PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id'); |
161 | 1; |
162 | |
163 | The B<belongs_to> relation allows us to refer to the B<user> who authored a post as the object representing the user, rather than as just the integer tha database uses as the linking information. |
164 | |
165 | 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: |
166 | |
167 | __PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id'); |
168 | |
169 | =head3 CREATE VIEW |
170 | |
171 | In SQL, a simple view that returns all users and their posts: |
172 | |
173 | CREATE VIEW userposts |
174 | AS |
175 | SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post |
176 | FROM users |
177 | JOIN posts ON (users.id = posts.user_id) |
178 | |
179 | In DBIx::Class this can have a Result Class of its own: |
180 | |
181 | package MyDatabase::Schema::Result::UserPosts; |
182 | |
183 | use base qw/DBIx::Class::Core/; |
184 | |
185 | __PACKAGE__->table_class('DBIx::Class::ResultSource::View'); |
186 | |
187 | __PACKAGE__->table('user_posts'); |
188 | __PACKAGE__->result_source_instance->is_virtual(1); |
189 | __PACKAGE__->result_source_instance->view_definition( |
190 | "SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post |
191 | FROM users |
192 | JOIN posts ON (users.id = posts.user_id)" |
193 | ); |
194 | __PACKAGE__->add_columns( |
195 | user_id => { |
196 | data_type => 'integer', |
197 | }, |
198 | username => { |
199 | data_type => 'varchar', |
200 | size => 255, |
201 | }, |
202 | dob => { |
203 | data_type => 'date', |
204 | }, |
205 | realname => { |
206 | data_type => 'varchar', |
207 | size => 255, |
208 | }, |
209 | created_date => { |
210 | data_type => 'datetime', |
211 | }, |
212 | title => { |
213 | data_type => 'varchar', |
214 | size => 255, |
215 | }, |
216 | post => { |
217 | data_type => 'text', |
218 | }, |
219 | |
220 | ); |
221 | __PACKAGE__->set_primary_key('user_id, post_id'); |
222 | |
223 | =head3 CREATE INDEX |
224 | |
225 | =head4 UNIQUE indexes |
226 | |
227 | CREATE UNIQUE INDEX username_idx ON user (username); |
228 | |
229 | To add extra unique indexes, add the B<add_unique_constraint> call to your Result Class. |
230 | |
231 | __PACKAGE__->add_unique_constraint('username_idx' => ['username']); |
232 | |
233 | =head3 Outputting SQL |
234 | |
235 | Once the DBIC schema has been defined, you can outout the SQL needed to create the schema in the database in one of several ways. |
236 | |
237 | =head4 Deploy directly to the database |
238 | |
239 | Create a schema object with the correct database connection, then call B<deploy> on it. |
240 | |
241 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
242 | $schema->deploy({add_drop_table => 1}); |
243 | |
244 | L<DBIx::Class::Schema/deploy> has the documentation for the deploy method. |
245 | |
246 | =head4 Write out SQL files |
247 | |
248 | Create a schema object with the a database connection (any will do), and call the B<create_ddl_dir> on it. |
249 | |
250 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
251 | $schema->create_ddl_dir(['SQLite', 'MySQL']); |
252 | |
253 | By default this will create an SQL file for MySQL, PostgreSQL and SQLite. More databases are supported by L<SQL::Translator> if necessary. |
254 | |
255 | =head4 SQL files for upgrades (ALTER TABLE) |
256 | |
257 | DBIC can also make use of L<SQL::Translator::Diff> to write out ALTER TABLE statements when the schema classes are change. |
258 | |
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. |
260 | |
261 | After the schema has changed, change the B<$VERSION> value and re-run B<create_ddl_dir>. |
262 | |
263 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
264 | $schema->create_ddl_dir(\@databases, undef, '.', '0.1'); |