Commit | Line | Data |
d442cc9f |
1 | =head1 NAME |
2 | |
3533daff |
3 | Catalyst::Manual::Tutorial::Appendices - Catalyst Tutorial - Part 10: Appendices |
d442cc9f |
4 | |
5 | |
6 | =head1 OVERVIEW |
7 | |
3533daff |
8 | This is B<Part 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 | |
16 | L<Introduction|Catalyst::Manual::Tutorial::Intro> |
17 | |
18 | =item 2 |
19 | |
20 | L<Catalyst Basics|Catalyst::Manual::Tutorial::CatalystBasics> |
21 | |
22 | =item 3 |
23 | |
3533daff |
24 | L<More Catalyst Basics|Catalyst::Manual::Tutorial::MoreCatalystBasics> |
d442cc9f |
25 | |
26 | =item 4 |
27 | |
3533daff |
28 | L<Basic CRUD|Catalyst::Manual::Tutorial::BasicCRUD> |
d442cc9f |
29 | |
30 | =item 5 |
31 | |
3533daff |
32 | L<Authentication|Catalyst::Manual::Tutorial::Authentication> |
d442cc9f |
33 | |
34 | =item 6 |
35 | |
3533daff |
36 | L<Authorization|Catalyst::Manual::Tutorial::Authorization> |
d442cc9f |
37 | |
38 | =item 7 |
39 | |
3533daff |
40 | L<Debugging|Catalyst::Manual::Tutorial::Debugging> |
d442cc9f |
41 | |
42 | =item 8 |
43 | |
3533daff |
44 | L<Testing|Catalyst::Manual::Tutorial::Testing> |
d442cc9f |
45 | |
46 | =item 9 |
47 | |
3533daff |
48 | L<Advanced CRUD|Catalyst::Manual::Tutorial::AdvancedCRUD> |
49 | |
50 | =item 10 |
51 | |
d442cc9f |
52 | B<Appendices> |
53 | |
54 | =back |
55 | |
56 | |
57 | =head1 DESCRIPTION |
58 | |
59 | This part of the tutorial provides supporting information relevant to |
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 | |
813d364c |
110 | Although there 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 |
d442cc9f |
126 | C<C-E<lt>> and C<C-E<gt>> to set the mark at the beginning and end of the |
127 | file respectively. |
128 | |
129 | |
130 | =head1 APPENDIX 2: USING MYSQL AND POSTGRESQL |
131 | |
132 | The main database used in this tutorial is the very simple yet powerful |
133 | SQLite. This section provides information that can be used to "convert" |
134 | the tutorial to use MySQL and PostgreSQL. However, note that part of |
135 | the beauty of the MVC architecture is that very little database-specific |
136 | code is spread throughout the system (at least when MVC is "done |
137 | right"). Consequently, converting from one database to another is |
138 | relatively painless with most Catalyst applications. In general, you |
139 | just need to adapt the schema definition C<.sql> file you use to |
140 | initialize your database and adjust a few configuration parameters. |
141 | |
142 | Also note that the purpose of the data definition statements for this |
143 | section are not designed to take maximum advantage of the various |
144 | features in each database for issues such as referential integrity and |
145 | field types/constraints. |
146 | |
147 | =head2 MySQL |
148 | |
813d364c |
149 | Use the following steps to adapt the tutorial to MySQL. Thanks to Jim |
d442cc9f |
150 | Howard for the help. |
151 | |
152 | =over 4 |
153 | |
154 | =item * |
155 | |
156 | Part 2: Catalyst Basics |
157 | |
158 | =over 4 |
159 | |
160 | =item * |
161 | |
162 | Install the required software: |
163 | |
164 | =over 4 |
165 | |
166 | =item * |
167 | |
168 | The MySQL database server and client utility. |
169 | |
170 | =item * |
171 | |
172 | The Perl C<DBD::MySQL> module |
173 | |
174 | =back |
175 | |
813d364c |
176 | For CentOS users (see |
d442cc9f |
177 | L<Catalyst::Manual::Installation::CentOS4|Catalyst::Manual::Installation::CentOS4>), |
178 | you can use the following commands to install the software and start the MySQL |
179 | daemon: |
180 | |
181 | yum -y install mysql mysql-server |
182 | service mysqld start |
183 | |
184 | =item * |
185 | |
186 | Create the database and set the permissions: |
187 | |
188 | $ mysql |
189 | Welcome to the MySQL monitor. Commands end with ; or \g. |
190 | Your MySQL connection id is 2 to server version: 4.1.20 |
191 | |
192 | Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
193 | |
194 | mysql> create database myapp; |
195 | Query OK, 1 row affected (0.01 sec) |
196 | |
197 | mysql> grant all on myapp.* to tutorial@'localhost'; |
198 | Query OK, 0 rows affected (0.00 sec) |
199 | |
200 | mysql> flush privileges; |
201 | Query OK, 0 rows affected (0.00 sec) |
202 | |
203 | mysql> quit |
204 | Bye |
205 | |
206 | =item * |
207 | |
208 | Create the C<.sql> file and load the data: |
209 | |
210 | =over 4 |
211 | |
212 | =item * |
213 | |
214 | Open the C<myapp01_mysql.sql> in your editor and enter: |
215 | |
216 | -- |
217 | -- Create a very simple database to hold book and author information |
218 | -- |
219 | DROP TABLE IF EXISTS books; |
220 | DROP TABLE IF EXISTS book_authors; |
221 | DROP TABLE IF EXISTS authors; |
222 | CREATE TABLE books ( |
223 | id INT(11) PRIMARY KEY AUTO_INCREMENT, |
224 | title TEXT , |
225 | rating INT(11) |
226 | ); |
227 | -- 'book_authors' is a many-to-many join table between books & authors |
228 | CREATE TABLE book_authors ( |
229 | book_id INT(11), |
230 | author_id INT(11), |
231 | PRIMARY KEY (book_id, author_id) |
232 | ); |
233 | CREATE TABLE authors ( |
234 | id INT(11) PRIMARY KEY AUTO_INCREMENT, |
235 | first_name TEXT, |
236 | last_name TEXT |
237 | ); |
238 | --- |
239 | --- Load some sample data |
240 | --- |
241 | INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5); |
242 | INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5); |
243 | INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4); |
244 | INSERT INTO books VALUES (4, 'Perl Cookbook', 5); |
245 | INSERT INTO books VALUES (5, 'Designing with Web Standards', 5); |
246 | INSERT INTO authors VALUES (1, 'Greg', 'Bastien'); |
247 | INSERT INTO authors VALUES (2, 'Sara', 'Nasseh'); |
248 | INSERT INTO authors VALUES (3, 'Christian', 'Degu'); |
249 | INSERT INTO authors VALUES (4, 'Richard', 'Stevens'); |
250 | INSERT INTO authors VALUES (5, 'Douglas', 'Comer'); |
251 | INSERT INTO authors VALUES (6, 'Tom', 'Christiansen'); |
252 | INSERT INTO authors VALUES (7, ' Nathan', 'Torkington'); |
253 | INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman'); |
254 | INSERT INTO book_authors VALUES (1, 1); |
255 | INSERT INTO book_authors VALUES (1, 2); |
256 | INSERT INTO book_authors VALUES (1, 3); |
257 | INSERT INTO book_authors VALUES (2, 4); |
258 | INSERT INTO book_authors VALUES (3, 5); |
259 | INSERT INTO book_authors VALUES (4, 6); |
260 | INSERT INTO book_authors VALUES (4, 7); |
261 | INSERT INTO book_authors VALUES (5, 8); |
262 | |
263 | =item * |
264 | |
265 | Load the data: |
266 | |
267 | mysql -ututorial myapp < myapp01_mysql.sql |
268 | |
269 | =item * |
270 | |
271 | Make sure the data loaded correctly: |
272 | |
273 | $ mysql -ututorial myapp |
274 | Reading table information for completion of table and column names |
275 | You can turn off this feature to get a quicker startup with -A |
276 | |
277 | Welcome to the MySQL monitor. Commands end with ; or \g. |
278 | Your MySQL connection id is 4 to server version: 4.1.20 |
279 | |
280 | Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
281 | |
282 | mysql> show tables; |
283 | +-----------------+ |
284 | | Tables_in_myapp | |
285 | +-----------------+ |
286 | | authors | |
287 | | book_authors | |
288 | | books | |
289 | +-----------------+ |
290 | 3 rows in set (0.00 sec) |
291 | |
292 | mysql> select * from books; |
293 | +----+------------------------------------+--------+ |
294 | | id | title | rating | |
295 | +----+------------------------------------+--------+ |
296 | | 1 | CCSP SNRS Exam Certification Guide | 5 | |
297 | | 2 | TCP/IP Illustrated, Volume 1 | 5 | |
298 | | 3 | Internetworking with TCP/IP Vol.1 | 4 | |
299 | | 4 | Perl Cookbook | 5 | |
300 | | 5 | Designing with Web Standards | 5 | |
301 | +----+------------------------------------+--------+ |
302 | 5 rows in set (0.00 sec) |
303 | |
304 | mysql> |
305 | |
306 | =back |
307 | |
308 | =item * |
309 | |
310 | Update the model: |
311 | |
312 | =over 4 |
313 | |
314 | =item * |
315 | |
316 | Delete the existing model: |
317 | |
318 | rm lib/MyApp/Model/MyAppDB.pm |
319 | |
320 | =item * |
321 | |
322 | Regenerate the model using the Catalyst "_create.pl" script: |
323 | |
1cde0fd6 |
324 | script/myapp_create.pl model MyAppDB DBIC::Schema MyApp::Schema \ |
325 | dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }' |
d442cc9f |
326 | |
327 | =back |
328 | |
329 | =back |
330 | |
331 | =item * |
332 | |
333 | Part 4: Authentication |
334 | |
335 | =over 4 |
336 | |
337 | =item * |
338 | |
339 | Create the C<.sql> file for the user/roles data: |
340 | |
341 | Open C<myapp02_mysql.sql> in your editor and enter: |
342 | |
343 | -- |
344 | -- Add users and roles tables, along with a many-to-many join table |
345 | -- |
346 | CREATE TABLE users ( |
347 | id INT(11) PRIMARY KEY, |
348 | username TEXT, |
349 | password TEXT, |
350 | email_address TEXT, |
351 | first_name TEXT, |
352 | last_name TEXT, |
353 | active INT(11) |
354 | ); |
355 | CREATE TABLE roles ( |
356 | id INTEGER PRIMARY KEY, |
357 | role TEXT |
358 | ); |
359 | CREATE TABLE user_roles ( |
360 | user_id INT(11), |
361 | role_id INT(11), |
362 | PRIMARY KEY (user_id, role_id) |
363 | ); |
364 | -- |
365 | -- Load up some initial test data |
366 | -- |
367 | INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1); |
368 | INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1); |
369 | INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0); |
370 | INSERT INTO roles VALUES (1, 'user'); |
371 | INSERT INTO roles VALUES (2, 'admin'); |
372 | INSERT INTO user_roles VALUES (1, 1); |
373 | INSERT INTO user_roles VALUES (1, 2); |
374 | INSERT INTO user_roles VALUES (2, 1); |
375 | INSERT INTO user_roles VALUES (3, 1); |
376 | |
377 | =item * |
378 | |
379 | Load the user/roles data: |
380 | |
381 | mysql -ututorial myapp < myapp02_mysql.sql |
382 | |
383 | =item * |
384 | |
385 | Create the C<.sql> file for the hashed password data: |
386 | |
387 | Open C<myapp03_mysql.sql> in your editor and enter: |
388 | |
389 | -- |
390 | -- Convert passwords to SHA-1 hashes |
391 | -- |
392 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1; |
393 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2; |
394 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3; |
395 | |
396 | =item * |
397 | |
398 | Load the user/roles data: |
399 | |
400 | mysql -ututorial myapp < myapp03_mysql.sql |
401 | |
402 | =back |
403 | |
404 | =back |
405 | |
406 | =head2 PostgreSQL |
407 | |
3533daff |
408 | Use the following steps to adapt the tutorial to PostgreSQL. Thanks to |
813d364c |
409 | Louis Moore for the help who was in turn helped by Marcello Romani and |
3533daff |
410 | Tom Lanyon. |
411 | |
412 | =over 4 |
413 | |
414 | =item * |
415 | |
416 | Part 2: Catalyst Basics |
417 | |
418 | =over 4 |
419 | |
420 | =item * |
421 | |
422 | Install the required software: |
423 | |
424 | =over 4 |
425 | |
426 | =item * |
427 | |
428 | The PostgreSQL database server and client. |
429 | |
430 | =item * |
431 | |
432 | The Perl C<DBD::Pg> module |
433 | |
434 | =back |
435 | |
436 | =item * |
437 | |
438 | Create the database and a user for the database |
439 | |
813d364c |
440 | $ createuser -P catappuser |
441 | Enter password for new role: <catalyst> |
3533daff |
442 | Enter it again: <catalyst> |
443 | Shall the new role be a superuser? (y/n) n |
444 | Shall the new role be allowed to create databases? (y/n) n |
445 | Shall the new role be allowed to create more new roles? (y/n) n |
446 | CREATE ROLE |
813d364c |
447 | $ createdb -O catappuser catappdb |
3533daff |
448 | CREATE DATABASE |
449 | |
450 | =item * |
451 | |
452 | Create the C<.sql> file and load the data: |
453 | |
454 | =over 4 |
455 | |
456 | =item * |
457 | |
458 | Open the C<myapp01_psql.sql> in your editor and enter: |
459 | |
460 | |
1390ef0e |
461 | -- |
462 | -- Create a very simple database to hold book and author information |
463 | -- |
464 | -- The sequence is how we get a unique id in PostgreSQL |
465 | -- |
466 | CREATE SEQUENCE books_seq START 5 ; |
467 | SELECT nextval ('books_seq'); |
468 | |
469 | CREATE TABLE books ( |
470 | id INTEGER PRIMARY KEY DEFAULT nextval('books_seq'), |
471 | title TEXT , |
472 | rating INTEGER |
473 | ); |
474 | |
475 | -- 'book_authors' is a many-to-many join table between books & authors |
476 | CREATE TABLE book_authors ( |
477 | book_id INTEGER, |
478 | author_id INTEGER, |
479 | PRIMARY KEY (book_id, author_id) |
480 | ); |
481 | |
482 | CREATE SEQUENCE authors_seq START 8 ; |
483 | SELECT nextval ('authors_seq'); |
484 | |
485 | CREATE TABLE authors ( |
486 | id INTEGER PRIMARY KEY DEFAULT nextval('authors_seq'), |
487 | first_name TEXT, |
488 | last_name TEXT |
489 | ); |
490 | --- |
491 | --- Load some sample data |
492 | --- |
493 | INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5); |
494 | INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5); |
495 | INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4); |
496 | INSERT INTO books VALUES (4, 'Perl Cookbook', 5); |
497 | INSERT INTO books VALUES (5, 'Designing with Web Standards', 5); |
498 | INSERT INTO authors VALUES (1, 'Greg', 'Bastien'); |
499 | INSERT INTO authors VALUES (2, 'Sara', 'Nasseh'); |
500 | INSERT INTO authors VALUES (3, 'Christian', 'Degu'); |
501 | INSERT INTO authors VALUES (4, 'Richard', 'Stevens'); |
502 | INSERT INTO authors VALUES (5, 'Douglas', 'Comer'); |
503 | INSERT INTO authors VALUES (6, 'Tom', 'Christiansen'); |
504 | INSERT INTO authors VALUES (7, 'Nathan', 'Torkington'); |
505 | INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman'); |
506 | INSERT INTO book_authors VALUES (1, 1); |
507 | INSERT INTO book_authors VALUES (1, 2); |
508 | INSERT INTO book_authors VALUES (1, 3); |
509 | INSERT INTO book_authors VALUES (2, 4); |
510 | INSERT INTO book_authors VALUES (3, 5); |
511 | INSERT INTO book_authors VALUES (4, 6); |
512 | INSERT INTO book_authors VALUES (4, 7); |
513 | INSERT INTO book_authors VALUES (5, 8); |
3533daff |
514 | |
515 | =item * |
516 | |
517 | Load the data: |
518 | |
813d364c |
519 | $ psql -U catappuser -W catappdb |
520 | Password for user catappuser: <catalyst> |
3533daff |
521 | Welcome to psql 8.1.8, the PostgreSQL interactive terminal. |
1390ef0e |
522 | |
3533daff |
523 | Type: \copyright for distribution terms |
524 | \h for help with SQL commands |
525 | \? for help with psql commands |
526 | \g or terminate with semicolon to execute query |
527 | \q to quit |
1390ef0e |
528 | |
813d364c |
529 | catappdb=> \i myapp01_psql.sql |
1390ef0e |
530 | |
3533daff |
531 | CREATE SEQUENCE |
813d364c |
532 | nextval |
3533daff |
533 | --------- |
534 | 5 |
535 | (1 row) |
1390ef0e |
536 | |
3533daff |
537 | psql:myapp01_psql.sql:11: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books" |
538 | CREATE TABLE |
813d364c |
539 | psql:myapp01_psql.sql:19: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table |
3533daff |
540 | "book_authors" |
541 | CREATE TABLE |
542 | CREATE SEQUENCE |
813d364c |
543 | nextval |
3533daff |
544 | --------- |
545 | 8 |
546 | (1 row) |
1390ef0e |
547 | |
3533daff |
548 | psql:myapp01_psql.sql:30: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" |
549 | CREATE TABLE |
550 | INSERT 0 1 |
551 | INSERT 0 1 |
552 | INSERT 0 1 |
553 | INSERT 0 1 |
554 | ... |
555 | |
556 | =item * |
557 | |
558 | Make sure the data loaded correctly: |
559 | |
813d364c |
560 | catappdb=> \dt |
3533daff |
561 | List of relations |
813d364c |
562 | Schema | Name | Type | Owner |
3533daff |
563 | --------+--------------+-------+---------- |
813d364c |
564 | public | authors | table | catappuser |
565 | public | book_authors | table | catappuser |
566 | public | books | table | catappuser |
3533daff |
567 | (3 rows) |
1390ef0e |
568 | |
813d364c |
569 | catappdb=> select * from books; |
570 | id | title | rating |
3533daff |
571 | ----+------------------------------------+-------- |
572 | 1 | CCSP SNRS Exam Certification Guide | 5 |
573 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
574 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
575 | 4 | Perl Cookbook | 5 |
576 | 5 | Designing with Web Standards | 5 |
577 | (5 rows) |
1390ef0e |
578 | |
813d364c |
579 | catappdb=> \q |
3533daff |
580 | |
581 | =back |
582 | |
583 | =item * |
584 | |
585 | After the steps where you: |
586 | |
813d364c |
587 | edit lib/MyApp.pm |
1390ef0e |
588 | |
813d364c |
589 | create lib/MyAppDB.pm |
1390ef0e |
590 | |
3533daff |
591 | create lib/MyAppDB/Book.pm |
1390ef0e |
592 | |
3533daff |
593 | create lib/MyAppDB/Author.pm |
1390ef0e |
594 | |
813d364c |
595 | create lib/MyAppDB/BookAuthor.pm |
3533daff |
596 | |
597 | |
598 | =item * |
599 | |
600 | Generate the model using the Catalyst "_create.pl" script: |
601 | |
813d364c |
602 | script/myapp_create.pl model MyAppDB DBIC::Schema MyAppDB 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' |
3533daff |
603 | |
604 | |
605 | =back |
606 | |
607 | =item * |
608 | |
609 | Part 4: Authentication |
610 | |
611 | =over 4 |
612 | |
613 | =item * |
614 | |
615 | Create the C<.sql> file for the user/roles data: |
616 | |
617 | Open C<myapp02_psql.sql> in your editor and enter: |
618 | |
619 | -- |
620 | -- Add users and roles tables, along with a many-to-many join table |
621 | -- |
1390ef0e |
622 | |
3533daff |
623 | CREATE SEQUENCE users_seq START 3 ; |
624 | SELECT nextval ('users_seq'); |
1390ef0e |
625 | |
3533daff |
626 | CREATE TABLE users ( |
627 | id INTEGER PRIMARY KEY DEFAULT nextval('users_seq'), |
628 | username TEXT, |
629 | password TEXT, |
630 | email_address TEXT, |
631 | first_name TEXT, |
632 | last_name TEXT, |
633 | active INTEGER |
634 | ); |
1390ef0e |
635 | |
3533daff |
636 | CREATE SEQUENCE roles_seq START 2 ; |
637 | SELECT nextval ('roles_seq'); |
1390ef0e |
638 | |
3533daff |
639 | CREATE TABLE roles ( |
640 | id INTEGER PRIMARY KEY DEFAULT nextval('roles_seq'), |
641 | role TEXT |
642 | ); |
1390ef0e |
643 | |
3533daff |
644 | CREATE TABLE user_roles ( |
645 | user_id INTEGER, |
646 | role_id INTEGER, |
647 | PRIMARY KEY (user_id, role_id) |
648 | ); |
1390ef0e |
649 | |
3533daff |
650 | -- |
651 | -- Load up some initial test data |
652 | -- |
653 | INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1); |
654 | INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1); |
655 | INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0); |
656 | INSERT INTO roles VALUES (1, 'user'); |
657 | INSERT INTO roles VALUES (2, 'admin'); |
658 | INSERT INTO user_roles VALUES (1, 1); |
659 | INSERT INTO user_roles VALUES (1, 2); |
660 | INSERT INTO user_roles VALUES (2, 1); |
661 | INSERT INTO user_roles VALUES (3, 1); |
662 | |
663 | =item * |
664 | |
665 | Load the data: |
666 | |
813d364c |
667 | $ psql -U catappuser -W catappdb |
668 | Password for user catappuser: catalyst |
3533daff |
669 | Welcome to psql 8.1.8, the PostgreSQL interactive terminal. |
1390ef0e |
670 | |
3533daff |
671 | Type: \copyright for distribution terms |
672 | \h for help with SQL commands |
673 | \? for help with psql commands |
674 | \g or terminate with semicolon to execute query |
675 | \q to quit |
1390ef0e |
676 | |
813d364c |
677 | catappdb=> \i myapp02_psql.sql |
1390ef0e |
678 | |
3533daff |
679 | CREATE SEQUENCE |
813d364c |
680 | nextval |
3533daff |
681 | --------- |
682 | 3 |
683 | (1 row) |
1390ef0e |
684 | |
3533daff |
685 | psql:myapp02_psql.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" |
686 | CREATE TABLE |
687 | CREATE SEQUENCE |
813d364c |
688 | nextval |
3533daff |
689 | --------- |
690 | 2 |
691 | (1 row) |
1390ef0e |
692 | |
3533daff |
693 | psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles" |
694 | CREATE TABLE |
695 | psql:myapp02_psql.sql:30: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles" |
696 | CREATE TABLE |
697 | INSERT 0 1 |
698 | INSERT 0 1 |
699 | INSERT 0 1 |
700 | INSERT 0 1 |
701 | INSERT 0 1 |
702 | INSERT 0 1 |
703 | INSERT 0 1 |
704 | INSERT 0 1 |
705 | INSERT 0 1 |
813d364c |
706 | catappdb=> |
1390ef0e |
707 | |
813d364c |
708 | catappdb=> select * from users; |
709 | id | username | password | email_address | first_name | last_name | active |
3533daff |
710 | ----+----------+----------+---------------+------------+-----------+-------- |
711 | 1 | test01 | mypass | t01@na.com | Joe | Blow | 1 |
712 | 2 | test02 | mypass | t02@na.com | Jane | Doe | 1 |
713 | 3 | test03 | mypass | t03@na.com | No | Go | 0 |
714 | (3 rows) |
715 | |
716 | |
717 | =item * |
718 | |
719 | Create the C<.sql> file for the hashed password data: |
720 | |
721 | Open C<myapp03_psql.sql> in your editor and enter: |
722 | |
723 | -- |
724 | -- Convert passwords to SHA-1 hashes |
725 | -- |
726 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1; |
727 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2; |
728 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3; |
729 | |
730 | =item * |
731 | |
732 | Load in the data |
733 | |
813d364c |
734 | $ psql -U catappuser -W catappdb |
735 | Password for user catappuser: |
3533daff |
736 | Welcome to psql 8.1.8, the PostgreSQL interactive terminal. |
1390ef0e |
737 | |
3533daff |
738 | Type: \copyright for distribution terms |
739 | \h for help with SQL commands |
740 | \? for help with psql commands |
741 | \g or terminate with semicolon to execute query |
742 | \q to quit |
1390ef0e |
743 | |
813d364c |
744 | catappdb=> \i myapp03_psql.sql |
3533daff |
745 | UPDATE 1 |
746 | UPDATE 1 |
747 | UPDATE 1 |
748 | |
749 | |
750 | |
751 | =back |
752 | |
753 | =back |
d442cc9f |
754 | |
755 | |
756 | =head1 APPENDIX 3: IMPROVED HASHING SCRIPT |
757 | |
758 | Here is an improved SHA-1 hashing script from Gavin Henry that does |
759 | not expose the passwords to "capture" on the command line. |
760 | |
761 | #!/usr/bin/perl -w |
762 | #=============================================================================== |
763 | # |
764 | # FILE: enc_pass.pl |
765 | # |
766 | # USAGE: ./enc_pass.pl |
767 | # |
768 | # DESCRIPTION: Encrypt a Password using SHA-1 |
769 | # |
770 | # OPTIONS: --- |
771 | # REQUIREMENTS: --- |
772 | # BUGS: --- |
773 | # NOTES: --- |
774 | # AUTHOR: Gavin Henry (GH), <ghenry@suretecsystems.com> |
775 | # COMPANY: Suretec Systems Ltd. |
776 | # VERSION: 1.0 |
777 | # CREATED: 26/06/2006 |
778 | # REVISION: --- |
779 | # COPYRIGHT: http://search.cpan.org/dist/perl/pod/perlgpl.pod |
780 | #=============================================================================== |
781 | |
782 | use strict; |
783 | use warnings; |
784 | use Digest::SHA1; |
785 | use Term::ReadKey; |
786 | |
787 | sub get_pass { |
788 | ReadMode 'noecho'; |
789 | chomp( my $pw = ReadLine 0 ); |
790 | ReadMode 'normal'; |
791 | return $pw; |
792 | } |
793 | |
794 | print "Enter the password to be encrypted: "; |
795 | my $pass = get_pass(); |
796 | |
797 | print "\nConfirm the password: "; |
798 | my $verify = get_pass(); |
799 | |
800 | if ( $pass eq $verify ) { |
801 | my $sha1_enc = Digest::SHA1->new; |
802 | $sha1_enc->add($pass); |
803 | |
804 | print "\nYour encrypted password is: " |
805 | . $sha1_enc->hexdigest . "\n" |
806 | . "Paste this into your SQL INSERT/COPY Data.\n"; |
807 | } |
808 | else { |
809 | print "\nPasswords do not match!\n"; |
810 | } |
811 | |
812 | |
813 | =head1 AUTHOR |
814 | |
815 | Kennedy Clark, C<hkclark@gmail.com> |
816 | |
817 | Please report any errors, issues or suggestions to the author. The |
818 | most recent version of the Catalyst Tutorial can be found at |
82ab4bbf |
819 | L<http://dev.catalyst.perl.org/repos/Catalyst/Catalyst-Manual/5.70/trunk/lib/Catalyst/Manual/Tutorial/>. |
d442cc9f |
820 | |
45c7830f |
821 | Copyright 2006-2008, Kennedy Clark, under Creative Commons License |
95674086 |
822 | (L<http://creativecommons.org/licenses/by-sa/3.0/us/>). |