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