Commit | Line | Data |
d442cc9f |
1 | =head1 NAME |
2 | |
3ab6187c |
3 | Catalyst::Manual::Tutorial::10_Appendices - Catalyst Tutorial - Chapter 10: Appendices |
d442cc9f |
4 | |
5 | |
6 | =head1 OVERVIEW |
7 | |
4b4d3884 |
8 | This is B<Chapter 10 of 10> for the Catalyst tutorial. |
d442cc9f |
9 | |
10 | L<Tutorial Overview|Catalyst::Manual::Tutorial> |
11 | |
12 | =over 4 |
13 | |
14 | =item 1 |
15 | |
3ab6187c |
16 | L<Introduction|Catalyst::Manual::Tutorial::01_Intro> |
d442cc9f |
17 | |
18 | =item 2 |
19 | |
3ab6187c |
20 | L<Catalyst Basics|Catalyst::Manual::Tutorial::02_CatalystBasics> |
d442cc9f |
21 | |
22 | =item 3 |
23 | |
3ab6187c |
24 | L<More Catalyst Basics|Catalyst::Manual::Tutorial::03_MoreCatalystBasics> |
d442cc9f |
25 | |
26 | =item 4 |
27 | |
3ab6187c |
28 | L<Basic CRUD|Catalyst::Manual::Tutorial::04_BasicCRUD> |
d442cc9f |
29 | |
30 | =item 5 |
31 | |
3ab6187c |
32 | L<Authentication|Catalyst::Manual::Tutorial::05_Authentication> |
d442cc9f |
33 | |
34 | =item 6 |
35 | |
3ab6187c |
36 | L<Authorization|Catalyst::Manual::Tutorial::06_Authorization> |
d442cc9f |
37 | |
38 | =item 7 |
39 | |
3ab6187c |
40 | L<Debugging|Catalyst::Manual::Tutorial::07_Debugging> |
d442cc9f |
41 | |
42 | =item 8 |
43 | |
3ab6187c |
44 | L<Testing|Catalyst::Manual::Tutorial::08_Testing> |
d442cc9f |
45 | |
46 | =item 9 |
47 | |
3ab6187c |
48 | L<Advanced CRUD|Catalyst::Manual::Tutorial::09_AdvancedCRUD> |
3533daff |
49 | |
50 | =item 10 |
51 | |
3ab6187c |
52 | B<10_Appendices> |
d442cc9f |
53 | |
54 | =back |
55 | |
56 | |
57 | =head1 DESCRIPTION |
58 | |
4b4d3884 |
59 | This chapter of the tutorial provides supporting information relevant to |
d442cc9f |
60 | the Catalyst tutorial. |
61 | |
62 | |
63 | =head1 APPENDIX 1: CUT AND PASTE FOR POD-BASED EXAMPLES |
64 | |
65 | You may notice that Pod indents example code with four spaces. This |
66 | section provides some quick advice to "un-indent" this text in common |
67 | editors. |
68 | |
69 | =head2 "Un-indenting" with Vi/Vim |
70 | |
71 | When cutting and pasting multi-line text from Pod-based documents, the |
72 | following vi/vim regexs can be helpful to "un-indent" the inserted text |
73 | (do NOT type the quotes, they are only included to show spaces in the |
74 | regex patterns). I<Note that all 3 of the regexs end in 4 spaces>: |
75 | |
76 | =over 4 |
77 | |
813d364c |
78 | =item * |
d442cc9f |
79 | |
80 | ":0,$s/^ " |
81 | |
82 | Removes four leading spaces from the entire file (from the first line, |
83 | C<0>, to the last line, C<$>). |
84 | |
813d364c |
85 | =item * |
d442cc9f |
86 | |
87 | "%s/^ " |
88 | |
89 | A shortcut for the previous item (C<%> specifies the entire file; so |
90 | this removes four leading spaces from every line). |
91 | |
813d364c |
92 | =item * |
d442cc9f |
93 | |
94 | ":.,$s/^ " |
95 | |
96 | Removes the first four spaces from the line the cursor is on at the time |
97 | the regex command is executed (".") to the last line of the file. |
98 | |
813d364c |
99 | =item * |
d442cc9f |
100 | |
101 | ":.,44s/^ " |
102 | |
103 | Removes four leading space from the current line through line 44 |
104 | (obviously adjust the C<44> to the appropriate value in your example). |
105 | |
106 | =back |
107 | |
108 | =head2 "Un-indenting" with Emacs |
109 | |
325bc0fd |
110 | Although the author has not used Emacs for many years (apologies to |
191dee29 |
111 | the Emacs fans out there), here is a quick hint to get you started. To |
d442cc9f |
112 | replace the leading spaces of every line in a file, use: |
113 | |
114 | M-x replace-regexp<RET> |
115 | Replace regexp: ^ <RET> |
116 | with: <RET> |
117 | |
118 | All of that will occur on the single line at the bottom of your screen. |
813d364c |
119 | Note that "<RET>" represents the return key/enter. Also, there are |
d442cc9f |
120 | four spaces after the "^" on the "Replace regexp:" line and no spaces |
121 | entered on the last line. |
122 | |
123 | You can limit the replacement operation by selecting text first (depending |
813d364c |
124 | on your version of Emacs, you can either use the mouse or experiment with |
125 | commands such as C<C-SPC> to set the mark at the cursor location and |
429d1caf |
126 | C<< C-< >> and C<< C-> >> to set the mark at the beginning and end of the |
d442cc9f |
127 | file respectively. |
128 | |
7ce05098 |
129 | Also, Stefan Kangas sent in the following tip about an alternate |
130 | approach using the command C<indent-region> to redo the indentation |
131 | for the currently selected region (adhering to indent rules in the |
132 | current major mode). You can run the command by typing M-x |
133 | indent-region or pressing the default keybinding C-M-\ in cperl-mode. |
325bc0fd |
134 | Additional details can be found here: |
135 | |
d672dfd7 |
136 | L<http://www.gnu.org/software/emacs/manual/html_node/emacs/Indentation-Commands.html> |
325bc0fd |
137 | |
d442cc9f |
138 | |
e179eee0 |
139 | =head1 APPENDIX 2: USING POSTGRESQL AND MYSQL |
d442cc9f |
140 | |
141 | The main database used in this tutorial is the very simple yet powerful |
7c63f9e7 |
142 | L<SQLite|http://www.sqlite.org>. This section provides information |
1eaebcbf |
143 | that can be used to "convert" the tutorial to use |
7c63f9e7 |
144 | L<PostgreSQL|http://www.postgresql.org> and |
145 | L<MySQL|http://dev.mysql.com>. However, note that part of |
d442cc9f |
146 | the beauty of the MVC architecture is that very little database-specific |
147 | code is spread throughout the system (at least when MVC is "done |
148 | right"). Consequently, converting from one database to another is |
149 | relatively painless with most Catalyst applications. In general, you |
150 | just need to adapt the schema definition C<.sql> file you use to |
151 | initialize your database and adjust a few configuration parameters. |
152 | |
153 | Also note that the purpose of the data definition statements for this |
154 | section are not designed to take maximum advantage of the various |
155 | features in each database for issues such as referential integrity and |
156 | field types/constraints. |
157 | |
d442cc9f |
158 | |
e179eee0 |
159 | =head2 PostgreSQL |
160 | |
7ce05098 |
161 | Use the following steps to adapt the tutorial to PostgreSQL. Thanks |
162 | to Caelum (Rafael Kitover) for assistance with the most recent |
163 | updates, and Louis Moore, Marcello Romani and Tom Lanyon for help with |
e179eee0 |
164 | earlier versions. |
d442cc9f |
165 | |
166 | =over 4 |
167 | |
168 | =item * |
169 | |
e179eee0 |
170 | Chapter 3: More Catalyst Basics |
d442cc9f |
171 | |
172 | =over 4 |
173 | |
174 | =item * |
175 | |
e179eee0 |
176 | Install the PostgreSQL server and client and DBD::Pg: |
d442cc9f |
177 | |
b9e431e3 |
178 | If you are following along in Debian 6, you can quickly install these |
e179eee0 |
179 | items via this command: |
d442cc9f |
180 | |
e179eee0 |
181 | sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl |
d442cc9f |
182 | |
7ce05098 |
183 | To configure the permissions, you can open |
f4e9de4a |
184 | F</etc/postgresql/8.3/main/pg_hba.conf> and change this line (near the |
e179eee0 |
185 | bottom): |
d442cc9f |
186 | |
e179eee0 |
187 | # "local" is for Unix domain socket connections only |
188 | local all all ident sameuser |
d442cc9f |
189 | |
e179eee0 |
190 | to: |
d442cc9f |
191 | |
e179eee0 |
192 | # "local" is for Unix domain socket connections only |
193 | local all all trust |
d442cc9f |
194 | |
e179eee0 |
195 | And then restart PostgreSQL: |
196 | |
197 | sudo /etc/init.d/postgresql-8.3 restart |
d442cc9f |
198 | |
d442cc9f |
199 | |
200 | =item * |
201 | |
7ce05098 |
202 | Create the database and a user for the database (note that we are |
429d1caf |
203 | using "<catalyst>" to represent the hidden password of |
e179eee0 |
204 | "catalyst"): |
d442cc9f |
205 | |
e179eee0 |
206 | $ sudo -u postgres createuser -P catappuser |
207 | Enter password for new role: <catalyst> |
208 | Enter it again: <catalyst> |
209 | Shall the new role be a superuser? (y/n) n |
210 | Shall the new role be allowed to create databases? (y/n) n |
211 | Shall the new role be allowed to create more new roles? (y/n) n |
212 | CREATE ROLE |
213 | $ sudo -u postgres createdb -O catappuser catappdb |
214 | CREATE DATABASE |
d442cc9f |
215 | |
216 | =item * |
217 | |
218 | Create the C<.sql> file and load the data: |
219 | |
220 | =over 4 |
221 | |
222 | =item * |
223 | |
f4e9de4a |
224 | Open the F<myapp01_psql.sql> in your editor and enter: |
d442cc9f |
225 | |
226 | -- |
e179eee0 |
227 | -- Drops just in case you are reloading |
228 | --- |
229 | DROP TABLE IF EXISTS books CASCADE; |
230 | DROP TABLE IF EXISTS authors CASCADE; |
231 | DROP TABLE IF EXISTS book_authors CASCADE; |
232 | DROP TABLE IF EXISTS users CASCADE; |
233 | DROP TABLE IF EXISTS roles CASCADE; |
234 | DROP TABLE IF EXISTS user_roles CASCADE; |
7ce05098 |
235 | |
e179eee0 |
236 | -- |
d442cc9f |
237 | -- Create a very simple database to hold book and author information |
238 | -- |
d442cc9f |
239 | CREATE TABLE books ( |
e179eee0 |
240 | id SERIAL PRIMARY KEY, |
241 | title TEXT , |
242 | rating INTEGER, |
243 | -- Manually add these later |
244 | -- created TIMESTAMP NOT NULL DEFAULT now(), |
245 | -- updated TIMESTAMP |
d442cc9f |
246 | ); |
7ce05098 |
247 | |
e179eee0 |
248 | CREATE TABLE authors ( |
249 | id SERIAL PRIMARY KEY, |
250 | first_name TEXT, |
251 | last_name TEXT |
252 | ); |
7ce05098 |
253 | |
d442cc9f |
254 | -- 'book_authors' is a many-to-many join table between books & authors |
255 | CREATE TABLE book_authors ( |
e179eee0 |
256 | book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, |
257 | author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE, |
258 | PRIMARY KEY (book_id, author_id) |
d442cc9f |
259 | ); |
7ce05098 |
260 | |
d442cc9f |
261 | --- |
262 | --- Load some sample data |
263 | --- |
e179eee0 |
264 | INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5); |
265 | INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5); |
266 | INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4); |
267 | INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5); |
268 | INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5); |
269 | INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien'); |
270 | INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh'); |
271 | INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu'); |
272 | INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens'); |
273 | INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer'); |
274 | INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen'); |
275 | INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington'); |
276 | INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman'); |
d442cc9f |
277 | INSERT INTO book_authors VALUES (1, 1); |
278 | INSERT INTO book_authors VALUES (1, 2); |
279 | INSERT INTO book_authors VALUES (1, 3); |
280 | INSERT INTO book_authors VALUES (2, 4); |
281 | INSERT INTO book_authors VALUES (3, 5); |
282 | INSERT INTO book_authors VALUES (4, 6); |
283 | INSERT INTO book_authors VALUES (4, 7); |
284 | INSERT INTO book_authors VALUES (5, 8); |
285 | |
286 | =item * |
287 | |
288 | Load the data: |
289 | |
e179eee0 |
290 | $ psql -U catappuser -W catappdb -f myapp01_psql.sql |
7ce05098 |
291 | Password for user catappuser: |
e179eee0 |
292 | psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id" |
293 | psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books" |
294 | CREATE TABLE |
295 | psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors" |
296 | CREATE TABLE |
297 | psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id" |
298 | psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" |
299 | CREATE TABLE |
300 | INSERT 0 1 |
301 | INSERT 0 1 |
302 | INSERT 0 1 |
303 | ... |
d442cc9f |
304 | |
305 | =item * |
306 | |
307 | Make sure the data loaded correctly: |
308 | |
e179eee0 |
309 | $ psql -U catappuser -W catappdb |
310 | Password for user catappuser: <catalyst> |
311 | Welcome to psql 8.3.7, the PostgreSQL interactive terminal. |
7ce05098 |
312 | |
e179eee0 |
313 | Type: \copyright for distribution terms |
314 | \h for help with SQL commands |
315 | \? for help with psql commands |
316 | \g or terminate with semicolon to execute query |
317 | \q to quit |
7ce05098 |
318 | |
e179eee0 |
319 | catappdb=> \dt |
320 | List of relations |
7ce05098 |
321 | Schema | Name | Type | Owner |
e179eee0 |
322 | --------+--------------+-------+------------ |
323 | public | authors | table | catappuser |
324 | public | book_authors | table | catappuser |
325 | public | books | table | catappuser |
326 | (3 rows) |
7ce05098 |
327 | |
e179eee0 |
328 | catappdb=> select * from books; |
7ce05098 |
329 | id | title | rating |
e179eee0 |
330 | ----+------------------------------------+-------- |
331 | 1 | CCSP SNRS Exam Certification Guide | 5 |
332 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
333 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
334 | 4 | Perl Cookbook | 5 |
335 | 5 | Designing with Web Standards | 5 |
336 | (5 rows) |
7ce05098 |
337 | |
338 | catappdb=> |
d442cc9f |
339 | |
340 | =back |
341 | |
342 | =item * |
343 | |
e179eee0 |
344 | After the steps where you: |
345 | |
346 | edit lib/MyApp.pm |
7ce05098 |
347 | |
e179eee0 |
348 | create lib/MyAppDB.pm |
7ce05098 |
349 | |
e179eee0 |
350 | create lib/MyAppDB/Book.pm |
7ce05098 |
351 | |
e179eee0 |
352 | create lib/MyAppDB/Author.pm |
7ce05098 |
353 | |
e179eee0 |
354 | create lib/MyAppDB/BookAuthor.pm |
d442cc9f |
355 | |
d442cc9f |
356 | |
357 | =item * |
358 | |
e179eee0 |
359 | Generate the model using the Catalyst "_create.pl" script: |
d442cc9f |
360 | |
e179eee0 |
361 | $ rm lib/MyApp/Model/DB.pm # Delete just in case already there |
362 | $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ |
b6e53c1c |
363 | create=static components=TimeStamp,PassphraseColumn \ |
e179eee0 |
364 | 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' |
365 | |
366 | =back |
d442cc9f |
367 | |
368 | =item * |
369 | |
e179eee0 |
370 | Chapter 4: Basic CRUD |
d442cc9f |
371 | |
e179eee0 |
372 | Add Datetime Columns to Our Existing Books Table |
d442cc9f |
373 | |
e179eee0 |
374 | $ psql -U catappuser -W catappdb |
375 | ... |
376 | catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now(); |
377 | ALTER TABLE |
378 | catappdb=> ALTER TABLE books ADD updated TIMESTAMP; |
379 | ALTER TABLE |
380 | catappdb=> \q |
381 | |
382 | Re-generate the model using the Catalyst "_create.pl" script: |
383 | |
384 | $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ |
b6e53c1c |
385 | create=static components=TimeStamp,PassphraseColumn \ |
e179eee0 |
386 | 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' |
d442cc9f |
387 | |
d442cc9f |
388 | |
389 | =item * |
390 | |
e179eee0 |
391 | Chapter 5: Authentication |
d442cc9f |
392 | |
393 | =over 4 |
394 | |
395 | =item * |
396 | |
397 | Create the C<.sql> file for the user/roles data: |
398 | |
f4e9de4a |
399 | Open F<myapp02_psql.sql> in your editor and enter: |
d442cc9f |
400 | |
401 | -- |
402 | -- Add users and roles tables, along with a many-to-many join table |
403 | -- |
7ce05098 |
404 | |
d442cc9f |
405 | CREATE TABLE users ( |
e179eee0 |
406 | id SERIAL PRIMARY KEY, |
407 | username TEXT, |
408 | password TEXT, |
409 | email_address TEXT, |
410 | first_name TEXT, |
411 | last_name TEXT, |
412 | active INTEGER |
d442cc9f |
413 | ); |
7ce05098 |
414 | |
d442cc9f |
415 | CREATE TABLE roles ( |
e179eee0 |
416 | id SERIAL PRIMARY KEY, |
417 | role TEXT |
d442cc9f |
418 | ); |
7ce05098 |
419 | |
d442cc9f |
420 | CREATE TABLE user_roles ( |
e179eee0 |
421 | user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, |
422 | role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE, |
423 | PRIMARY KEY (user_id, role_id) |
d442cc9f |
424 | ); |
7ce05098 |
425 | |
d442cc9f |
426 | -- |
427 | -- Load up some initial test data |
428 | -- |
7ce05098 |
429 | INSERT INTO users (username, password, email_address, first_name, last_name, active) |
e179eee0 |
430 | VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1); |
7ce05098 |
431 | INSERT INTO users (username, password, email_address, first_name, last_name, active) |
e179eee0 |
432 | VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1); |
433 | INSERT INTO users (username, password, email_address, first_name, last_name, active) |
434 | VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0); |
435 | INSERT INTO roles (role) VALUES ('user'); |
436 | INSERT INTO roles (role) VALUES ('admin'); |
d442cc9f |
437 | INSERT INTO user_roles VALUES (1, 1); |
438 | INSERT INTO user_roles VALUES (1, 2); |
439 | INSERT INTO user_roles VALUES (2, 1); |
440 | INSERT INTO user_roles VALUES (3, 1); |
441 | |
442 | =item * |
443 | |
e179eee0 |
444 | Load the data: |
445 | |
446 | $ psql -U catappuser -W catappdb -f myapp02_psql.sql |
447 | Password for user catappuser: <catalyst> |
448 | psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" |
449 | psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" |
450 | CREATE TABLE |
451 | psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id" |
452 | psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles" |
453 | CREATE TABLE |
454 | psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles" |
455 | CREATE TABLE |
456 | INSERT 0 1 |
457 | INSERT 0 1 |
458 | INSERT 0 1 |
459 | INSERT 0 1 |
460 | INSERT 0 1 |
461 | INSERT 0 1 |
462 | INSERT 0 1 |
463 | INSERT 0 1 |
464 | INSERT 0 1 |
465 | |
466 | Confirm with: |
467 | |
468 | $ psql -U catappuser -W catappdb -c "select * from users" |
469 | Password for user catappuser: <catalyst> |
7ce05098 |
470 | id | username | password | email_address | first_name | last_name | active |
e179eee0 |
471 | ----+----------+----------+---------------+------------+-----------+-------- |
472 | 1 | test01 | mypass | t01@na.com | Joe | Blow | 1 |
473 | 2 | test02 | mypass | t02@na.com | Jane | Doe | 1 |
474 | 3 | test03 | mypass | t03@na.com | No | Go | 0 |
475 | (3 rows) |
d442cc9f |
476 | |
d442cc9f |
477 | |
478 | =item * |
479 | |
f4e9de4a |
480 | Modify F<set_hashed_passwords.pl> to match the following (the only difference |
e179eee0 |
481 | is the C<connect> line): |
d442cc9f |
482 | |
e179eee0 |
483 | #!/usr/bin/perl |
7ce05098 |
484 | |
e179eee0 |
485 | use strict; |
486 | use warnings; |
7ce05098 |
487 | |
e179eee0 |
488 | use MyApp::Schema; |
7ce05098 |
489 | |
e179eee0 |
490 | my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst'); |
7ce05098 |
491 | |
e179eee0 |
492 | my @users = $schema->resultset('Users')->all; |
7ce05098 |
493 | |
e179eee0 |
494 | foreach my $user (@users) { |
495 | $user->password('mypass'); |
496 | $user->update; |
497 | } |
d442cc9f |
498 | |
f4e9de4a |
499 | Run the F<set_hashed_passwords.pl> as per the "normal" flow of the |
e179eee0 |
500 | tutorial: |
d442cc9f |
501 | |
e179eee0 |
502 | $ perl -Ilib set_hashed_passwords.pl |
d442cc9f |
503 | |
e179eee0 |
504 | You can verify that it worked with this command: |
505 | |
506 | $ psql -U catappuser -W catappdb -c "select * from users" |
d442cc9f |
507 | |
d442cc9f |
508 | |
509 | =back |
510 | |
511 | =back |
512 | |
d442cc9f |
513 | |
e179eee0 |
514 | =head2 MySQL |
515 | |
e179eee0 |
516 | Use the following steps to adapt the tutorial to MySQL. Thanks to Jim |
583938b3 |
517 | Howard for the help and Zsolt Zemancsik for the up to date fixes. |
3533daff |
518 | |
519 | =over 4 |
520 | |
521 | =item * |
522 | |
e179eee0 |
523 | Chapter 3: Catalyst Basics |
3533daff |
524 | |
525 | =over 4 |
526 | |
527 | =item * |
528 | |
529 | Install the required software: |
530 | |
531 | =over 4 |
532 | |
533 | =item * |
534 | |
e179eee0 |
535 | The MySQL database server and client utility. |
3533daff |
536 | |
537 | =item * |
538 | |
e179eee0 |
539 | The Perl C<DBD::MySQL> module |
3533daff |
540 | |
541 | =back |
542 | |
e179eee0 |
543 | For CentOS users (see |
2217b252 |
544 | L<Catalyst::Manual::Installation::CentOS4>), |
e179eee0 |
545 | you can use the following commands to install the software and start the MySQL |
546 | daemon: |
547 | |
548 | yum -y install mysql mysql-server |
549 | service mysqld start |
550 | |
583938b3 |
551 | For Debian users you can use the following commands to install the software and start the MySQL |
552 | daemon: |
553 | |
554 | apt-get install mysql-client mysql-server |
555 | /etc/init.d/mysql start |
556 | |
557 | B<NOTE:> The tutorial is based on Foreign Keys in database which is supported by InnoDB. |
558 | Only MySQL 5.0 and above supports InnoDB storage Engine so you need to have InnoDB support |
559 | in you MySQL. You can simply figure out that your install supports it or not: |
560 | |
561 | # mysql -u root -p |
562 | Enter password: |
563 | Welcome to the MySQL monitor. Commands end with ; or \g. |
7ce05098 |
564 | |
565 | Type 'help;' or '\h' for help. Type '\c' to clear the current input |
583938b3 |
566 | statement. |
7ce05098 |
567 | |
583938b3 |
568 | mysql> SHOW VARIABLES LIKE 'have_innodb'; |
569 | +---------------+-------+ |
570 | | Variable_name | Value | |
571 | +---------------+-------+ |
572 | | have_innodb | YES | |
573 | +---------------+-------+ |
574 | 1 row in set (0.01 sec) |
7ce05098 |
575 | |
583938b3 |
576 | mysql> exit |
577 | Bye |
578 | |
579 | If the Value is "YES" you can use your setup (Debian based mysql supports it by default). |
580 | Else, you need to configure your my.cnf or start your MySQL daemon without --skip-innodb option. |
581 | |
3533daff |
582 | =item * |
583 | |
e179eee0 |
584 | Create the database and set the permissions: |
3533daff |
585 | |
583938b3 |
586 | # mysql -u root -p |
587 | Enter password: |
e179eee0 |
588 | Welcome to the MySQL monitor. Commands end with ; or \g. |
7ce05098 |
589 | |
583938b3 |
590 | Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
7ce05098 |
591 | |
583938b3 |
592 | mysql> CREATE DATABASE `myapp`; |
e179eee0 |
593 | Query OK, 1 row affected (0.01 sec) |
7ce05098 |
594 | |
583938b3 |
595 | mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword'; |
e179eee0 |
596 | Query OK, 0 rows affected (0.00 sec) |
7ce05098 |
597 | |
583938b3 |
598 | mysql> FLUSH PRIVILEGES; |
e179eee0 |
599 | Query OK, 0 rows affected (0.00 sec) |
7ce05098 |
600 | |
583938b3 |
601 | mysql> exit |
e179eee0 |
602 | Bye |
3d5fc70d |
603 | |
3533daff |
604 | |
605 | =item * |
606 | |
607 | Create the C<.sql> file and load the data: |
608 | |
609 | =over 4 |
610 | |
611 | =item * |
612 | |
f4e9de4a |
613 | Open the F<myapp01_mysql.sql> in your editor and enter: |
3533daff |
614 | |
1390ef0e |
615 | -- |
616 | -- Create a very simple database to hold book and author information |
617 | -- |
3d5fc70d |
618 | CREATE TABLE IF NOT EXISTS `books` ( |
583938b3 |
619 | `id` int(11) NOT NULL AUTO_INCREMENT, |
620 | `title` text CHARACTER SET utf8, |
621 | `rating` int(11) DEFAULT NULL, |
622 | PRIMARY KEY (`id`) |
623 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
1390ef0e |
624 | -- 'book_authors' is a many-to-many join table between books & authors |
3d5fc70d |
625 | CREATE TABLE IF NOT EXISTS `book_authors` ( |
583938b3 |
626 | `book_id` int(11) NOT NULL DEFAULT '0', |
627 | `author_id` int(11) NOT NULL DEFAULT '0', |
628 | PRIMARY KEY (`book_id`,`author_id`), |
629 | KEY `author_id` (`author_id`) |
630 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3d5fc70d |
631 | CREATE TABLE IF NOT EXISTS `authors` ( |
583938b3 |
632 | `id` int(11) NOT NULL AUTO_INCREMENT, |
633 | `first_name` text CHARACTER SET utf8, |
634 | `last_name` text CHARACTER SET utf8, |
635 | PRIMARY KEY (`id`) |
636 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
1390ef0e |
637 | --- |
638 | --- Load some sample data |
639 | --- |
3d5fc70d |
640 | INSERT INTO `books` (`id`, `title`, `rating`) VALUES |
583938b3 |
641 | (1, 'CCSP SNRS Exam Certification Guide', 5), |
642 | (2, 'TCP/IP Illustrated, Volume 1', 5), |
643 | (3, 'Internetworking with TCP/IP Vol.1', 4), |
644 | (4, 'Perl Cookbook', 5), |
645 | (5, 'Designing with Web Standards', 5); |
7ce05098 |
646 | |
3d5fc70d |
647 | INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES |
583938b3 |
648 | (1, 1), |
649 | (1, 2), |
650 | (1, 3), |
651 | (2, 4), |
652 | (3, 5), |
653 | (4, 6), |
654 | (4, 7), |
655 | (5, 8); |
7ce05098 |
656 | |
3d5fc70d |
657 | INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES |
583938b3 |
658 | (1, 'Greg', 'Bastien'), |
659 | (2, 'Sara', 'Nasseh'), |
660 | (3, 'Christian', 'Degu'), |
661 | (4, 'Richard', 'Stevens'), |
662 | (5, 'Douglas', 'Comer'), |
663 | (6, 'Tom', 'Christiansen'), |
664 | (7, 'Nathan', 'Torkington'), |
665 | (8, 'Jeffrey', 'Zeldman'); |
7ce05098 |
666 | |
3d5fc70d |
667 | ALTER TABLE `book_authors` |
668 | ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
669 | ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; |
3533daff |
670 | |
671 | =item * |
672 | |
673 | Load the data: |
674 | |
583938b3 |
675 | mysql -u tutorial -p myapp < myapp01_mysql.sql |
3533daff |
676 | |
677 | =item * |
678 | |
679 | Make sure the data loaded correctly: |
680 | |
583938b3 |
681 | $ mysql -u tutorial -p myapp |
e179eee0 |
682 | Reading table information for completion of table and column names |
683 | You can turn off this feature to get a quicker startup with -A |
7ce05098 |
684 | |
e179eee0 |
685 | Welcome to the MySQL monitor. Commands end with ; or \g. |
7ce05098 |
686 | |
e179eee0 |
687 | Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
7ce05098 |
688 | |
e179eee0 |
689 | mysql> show tables; |
690 | +-----------------+ |
691 | | Tables_in_myapp | |
692 | +-----------------+ |
693 | | authors | |
694 | | book_authors | |
695 | | books | |
696 | +-----------------+ |
697 | 3 rows in set (0.00 sec) |
7ce05098 |
698 | |
e179eee0 |
699 | mysql> select * from books; |
700 | +----+------------------------------------+--------+ |
701 | | id | title | rating | |
702 | +----+------------------------------------+--------+ |
703 | | 1 | CCSP SNRS Exam Certification Guide | 5 | |
704 | | 2 | TCP/IP Illustrated, Volume 1 | 5 | |
705 | | 3 | Internetworking with TCP/IP Vol.1 | 4 | |
706 | | 4 | Perl Cookbook | 5 | |
707 | | 5 | Designing with Web Standards | 5 | |
708 | +----+------------------------------------+--------+ |
709 | 5 rows in set (0.00 sec) |
7ce05098 |
710 | |
e179eee0 |
711 | mysql> |
3533daff |
712 | |
713 | =back |
714 | |
715 | =item * |
716 | |
e179eee0 |
717 | Update the model: |
3533daff |
718 | |
e179eee0 |
719 | =over 4 |
720 | |
721 | =item * |
722 | |
723 | Delete the existing model: |
3533daff |
724 | |
e179eee0 |
725 | rm lib/MyApp/Model/MyAppDB.pm |
3533daff |
726 | |
727 | =item * |
728 | |
e179eee0 |
729 | Regenerate the model using the Catalyst "_create.pl" script: |
3533daff |
730 | |
583938b3 |
731 | script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \ |
f9ce2976 |
732 | dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }' |
3533daff |
733 | |
e179eee0 |
734 | =back |
3533daff |
735 | |
736 | =back |
737 | |
738 | =item * |
739 | |
e179eee0 |
740 | Chapter 5: Authentication |
3533daff |
741 | |
742 | =over 4 |
743 | |
744 | =item * |
745 | |
746 | Create the C<.sql> file for the user/roles data: |
747 | |
f4e9de4a |
748 | Open F<myapp02_mysql.sql> in your editor and enter: |
3533daff |
749 | |
750 | -- |
751 | -- Add users and roles tables, along with a many-to-many join table |
752 | -- |
3d5fc70d |
753 | CREATE TABLE IF NOT EXISTS `roles` ( |
583938b3 |
754 | `id` int(11) NOT NULL, |
755 | `role` text CHARACTER SET utf8, |
756 | PRIMARY KEY (`id`) |
757 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3d5fc70d |
758 | CREATE TABLE IF NOT EXISTS `users` ( |
583938b3 |
759 | `id` int(11) NOT NULL, |
760 | `username` text CHARACTER SET utf8, |
761 | `password` text CHARACTER SET utf8, |
762 | `email_address` text CHARACTER SET utf8, |
763 | `first_name` text CHARACTER SET utf8, |
764 | `last_name` text CHARACTER SET utf8, |
765 | `active` int(11) DEFAULT NULL, |
766 | PRIMARY KEY (`id`) |
767 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3d5fc70d |
768 | CREATE TABLE IF NOT EXISTS `user_roles` ( |
583938b3 |
769 | `user_id` int(11) NOT NULL DEFAULT '0', |
770 | `role_id` int(11) NOT NULL DEFAULT '0', |
771 | PRIMARY KEY (`user_id`,`role_id`), |
772 | KEY `role_id` (`role_id`) |
773 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
3533daff |
774 | -- |
775 | -- Load up some initial test data |
776 | -- |
3d5fc70d |
777 | INSERT INTO `roles` (`id`, `role`) VALUES |
583938b3 |
778 | (1, 'user'), |
779 | (2, 'admin'); |
7ce05098 |
780 | |
3d5fc70d |
781 | INSERT INTO `users` (`id`, `username`, `password`, `email_address`, `first_name`, `last_name`, `active`) VALUES |
583938b3 |
782 | (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1), |
783 | (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1), |
784 | (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0); |
7ce05098 |
785 | |
3d5fc70d |
786 | INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES |
583938b3 |
787 | (1, 1), |
788 | (2, 1), |
789 | (3, 1), |
790 | (1, 2); |
7ce05098 |
791 | |
3d5fc70d |
792 | ALTER TABLE `user_roles |
793 | ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
794 | ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; |
3533daff |
795 | |
796 | =item * |
797 | |
e179eee0 |
798 | Load the user/roles data: |
3533daff |
799 | |
583938b3 |
800 | mysql -u tutorial -p myapp < myapp02_mysql.sql |
801 | |
802 | =item * |
803 | |
804 | Update the model: |
805 | |
806 | =over 4 |
807 | |
808 | =item * |
809 | |
810 | Regenerate the model using the Catalyst "_create.pl" script: |
811 | |
812 | script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \ |
f9ce2976 |
813 | components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }' |
583938b3 |
814 | |
815 | =back |
3533daff |
816 | |
817 | =item * |
818 | |
819 | Create the C<.sql> file for the hashed password data: |
820 | |
f4e9de4a |
821 | Open F<myapp03_mysql.sql> in your editor and enter: |
3533daff |
822 | |
823 | -- |
824 | -- Convert passwords to SHA-1 hashes |
825 | -- |
436f45da |
826 | UPDATE users SET password = '{SSHA}esgz64CpHMo8pMfgIIszP13ft23z/zio04aCwNdm0wc6MDeloMUH4g==' WHERE id = 1; |
827 | UPDATE users SET password = '{SSHA}FpGhpCJus+Ea9ne4ww8404HH+hJKW/fW+bAv1v6FuRUy2G7I2aoTRQ==' WHERE id = 2; |
828 | UPDATE users SET password = '{SSHA}ZyGlpiHls8qFBSbHr3r5t/iqcZE602XLMbkSVRRNl6rF8imv1abQVg==' WHERE id = 3; |
3533daff |
829 | |
830 | =item * |
831 | |
e179eee0 |
832 | Load the user/roles data: |
3533daff |
833 | |
583938b3 |
834 | mysql -u tutorial -p myapp < myapp03_mysql.sql |
3533daff |
835 | |
836 | =back |
837 | |
838 | =back |
d442cc9f |
839 | |
840 | |
d442cc9f |
841 | =head1 AUTHOR |
842 | |
843 | Kennedy Clark, C<hkclark@gmail.com> |
844 | |
bb0999d3 |
845 | Feel free to contact the author for any errors or suggestions, but the |
846 | best way to report issues is via the CPAN RT Bug system at |
847 | L<https://rt.cpan.org/Public/Dist/Display.html?Name=Catalyst-Manual>. |
d442cc9f |
848 | |
bb0999d3 |
849 | Copyright 2006-2011, Kennedy Clark, under the |
ec3ef4ad |
850 | Creative Commons Attribution Share-Alike License Version 3.0 |
95674086 |
851 | (L<http://creativecommons.org/licenses/by-sa/3.0/us/>). |