Commit | Line | Data |
d442cc9f |
1 | =head1 NAME |
2 | |
4b4d3884 |
3 | Catalyst::Manual::Tutorial::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 | |
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 | |
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 | |
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 | |
e179eee0 |
130 | =head1 APPENDIX 2: USING POSTGRESQL AND MYSQL |
d442cc9f |
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" |
e179eee0 |
134 | the tutorial to use PostgreSQL and MySQL. However, note that part of |
d442cc9f |
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 | |
d442cc9f |
147 | |
e179eee0 |
148 | =head2 PostgreSQL |
149 | |
150 | Use the following steps to adapt the tutorial to PostgreSQL. Thanks |
151 | to Caelum (Rafael Kitover) for assistance with the most recent |
152 | updates, and Louis Moore, Marcello Romani and Tom Lanyon for help with |
153 | earlier versions. |
d442cc9f |
154 | |
155 | =over 4 |
156 | |
157 | =item * |
158 | |
e179eee0 |
159 | Chapter 3: More Catalyst Basics |
d442cc9f |
160 | |
161 | =over 4 |
162 | |
163 | =item * |
164 | |
e179eee0 |
165 | Install the PostgreSQL server and client and DBD::Pg: |
d442cc9f |
166 | |
e179eee0 |
167 | If you are following along in Debian 5, you can quickly install these |
168 | items via this command: |
d442cc9f |
169 | |
e179eee0 |
170 | sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl |
d442cc9f |
171 | |
e179eee0 |
172 | To configure the permissions, you can open |
173 | C</etc/postgresql/8.3/main/pg_hba.conf> and change this line (near the |
174 | bottom): |
d442cc9f |
175 | |
e179eee0 |
176 | # "local" is for Unix domain socket connections only |
177 | local all all ident sameuser |
d442cc9f |
178 | |
e179eee0 |
179 | to: |
d442cc9f |
180 | |
e179eee0 |
181 | # "local" is for Unix domain socket connections only |
182 | local all all trust |
d442cc9f |
183 | |
e179eee0 |
184 | And then restart PostgreSQL: |
185 | |
186 | sudo /etc/init.d/postgresql-8.3 restart |
d442cc9f |
187 | |
d442cc9f |
188 | |
189 | =item * |
190 | |
e179eee0 |
191 | Create the database and a user for the database (note that we are |
192 | using "E<lt>catalystE<gt>" to represent the hidden password of |
193 | "catalyst"): |
d442cc9f |
194 | |
e179eee0 |
195 | $ sudo -u postgres createuser -P catappuser |
196 | Enter password for new role: <catalyst> |
197 | Enter it again: <catalyst> |
198 | Shall the new role be a superuser? (y/n) n |
199 | Shall the new role be allowed to create databases? (y/n) n |
200 | Shall the new role be allowed to create more new roles? (y/n) n |
201 | CREATE ROLE |
202 | $ sudo -u postgres createdb -O catappuser catappdb |
203 | CREATE DATABASE |
d442cc9f |
204 | |
205 | =item * |
206 | |
207 | Create the C<.sql> file and load the data: |
208 | |
209 | =over 4 |
210 | |
211 | =item * |
212 | |
e179eee0 |
213 | Open the C<myapp01_psql.sql> in your editor and enter: |
d442cc9f |
214 | |
215 | -- |
e179eee0 |
216 | -- Drops just in case you are reloading |
217 | --- |
218 | DROP TABLE IF EXISTS books CASCADE; |
219 | DROP TABLE IF EXISTS authors CASCADE; |
220 | DROP TABLE IF EXISTS book_authors CASCADE; |
221 | DROP TABLE IF EXISTS users CASCADE; |
222 | DROP TABLE IF EXISTS roles CASCADE; |
223 | DROP TABLE IF EXISTS user_roles CASCADE; |
224 | |
225 | -- |
d442cc9f |
226 | -- Create a very simple database to hold book and author information |
227 | -- |
d442cc9f |
228 | CREATE TABLE books ( |
e179eee0 |
229 | id SERIAL PRIMARY KEY, |
230 | title TEXT , |
231 | rating INTEGER, |
232 | -- Manually add these later |
233 | -- created TIMESTAMP NOT NULL DEFAULT now(), |
234 | -- updated TIMESTAMP |
d442cc9f |
235 | ); |
e179eee0 |
236 | |
237 | CREATE TABLE authors ( |
238 | id SERIAL PRIMARY KEY, |
239 | first_name TEXT, |
240 | last_name TEXT |
241 | ); |
242 | |
d442cc9f |
243 | -- 'book_authors' is a many-to-many join table between books & authors |
244 | CREATE TABLE book_authors ( |
e179eee0 |
245 | book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, |
246 | author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE, |
247 | PRIMARY KEY (book_id, author_id) |
d442cc9f |
248 | ); |
e179eee0 |
249 | |
d442cc9f |
250 | --- |
251 | --- Load some sample data |
252 | --- |
e179eee0 |
253 | INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5); |
254 | INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5); |
255 | INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4); |
256 | INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5); |
257 | INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5); |
258 | INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien'); |
259 | INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh'); |
260 | INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu'); |
261 | INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens'); |
262 | INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer'); |
263 | INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen'); |
264 | INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington'); |
265 | INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman'); |
d442cc9f |
266 | INSERT INTO book_authors VALUES (1, 1); |
267 | INSERT INTO book_authors VALUES (1, 2); |
268 | INSERT INTO book_authors VALUES (1, 3); |
269 | INSERT INTO book_authors VALUES (2, 4); |
270 | INSERT INTO book_authors VALUES (3, 5); |
271 | INSERT INTO book_authors VALUES (4, 6); |
272 | INSERT INTO book_authors VALUES (4, 7); |
273 | INSERT INTO book_authors VALUES (5, 8); |
274 | |
275 | =item * |
276 | |
277 | Load the data: |
278 | |
e179eee0 |
279 | $ psql -U catappuser -W catappdb -f myapp01_psql.sql |
280 | Password for user catappuser: |
281 | psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id" |
282 | psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books" |
283 | CREATE TABLE |
284 | psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors" |
285 | CREATE TABLE |
286 | psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id" |
287 | psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" |
288 | CREATE TABLE |
289 | INSERT 0 1 |
290 | INSERT 0 1 |
291 | INSERT 0 1 |
292 | ... |
d442cc9f |
293 | |
294 | =item * |
295 | |
296 | Make sure the data loaded correctly: |
297 | |
e179eee0 |
298 | $ psql -U catappuser -W catappdb |
299 | Password for user catappuser: <catalyst> |
300 | Welcome to psql 8.3.7, the PostgreSQL interactive terminal. |
d442cc9f |
301 | |
e179eee0 |
302 | Type: \copyright for distribution terms |
303 | \h for help with SQL commands |
304 | \? for help with psql commands |
305 | \g or terminate with semicolon to execute query |
306 | \q to quit |
d442cc9f |
307 | |
e179eee0 |
308 | catappdb=> \dt |
309 | List of relations |
310 | Schema | Name | Type | Owner |
311 | --------+--------------+-------+------------ |
312 | public | authors | table | catappuser |
313 | public | book_authors | table | catappuser |
314 | public | books | table | catappuser |
315 | (3 rows) |
d442cc9f |
316 | |
e179eee0 |
317 | catappdb=> select * from books; |
318 | id | title | rating |
319 | ----+------------------------------------+-------- |
320 | 1 | CCSP SNRS Exam Certification Guide | 5 |
321 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
322 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
323 | 4 | Perl Cookbook | 5 |
324 | 5 | Designing with Web Standards | 5 |
325 | (5 rows) |
d442cc9f |
326 | |
e179eee0 |
327 | catappdb=> |
d442cc9f |
328 | |
329 | =back |
330 | |
331 | =item * |
332 | |
e179eee0 |
333 | After the steps where you: |
334 | |
335 | edit lib/MyApp.pm |
336 | |
337 | create lib/MyAppDB.pm |
338 | |
339 | create lib/MyAppDB/Book.pm |
340 | |
341 | create lib/MyAppDB/Author.pm |
342 | |
343 | create lib/MyAppDB/BookAuthor.pm |
d442cc9f |
344 | |
d442cc9f |
345 | |
346 | =item * |
347 | |
e179eee0 |
348 | Generate the model using the Catalyst "_create.pl" script: |
d442cc9f |
349 | |
e179eee0 |
350 | $ rm lib/MyApp/Model/DB.pm # Delete just in case already there |
351 | $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ |
352 | create=static components=TimeStamp,EncodedColumn \ |
353 | 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' |
354 | |
355 | =back |
d442cc9f |
356 | |
357 | =item * |
358 | |
e179eee0 |
359 | Chapter 4: Basic CRUD |
d442cc9f |
360 | |
e179eee0 |
361 | Add Datetime Columns to Our Existing Books Table |
d442cc9f |
362 | |
e179eee0 |
363 | $ psql -U catappuser -W catappdb |
364 | ... |
365 | catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now(); |
366 | ALTER TABLE |
367 | catappdb=> ALTER TABLE books ADD updated TIMESTAMP; |
368 | ALTER TABLE |
369 | catappdb=> \q |
370 | |
371 | Re-generate the model using the Catalyst "_create.pl" script: |
372 | |
373 | $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ |
374 | create=static components=TimeStamp,EncodedColumn \ |
375 | 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' |
d442cc9f |
376 | |
d442cc9f |
377 | |
378 | =item * |
379 | |
e179eee0 |
380 | Chapter 5: Authentication |
d442cc9f |
381 | |
382 | =over 4 |
383 | |
384 | =item * |
385 | |
386 | Create the C<.sql> file for the user/roles data: |
387 | |
e179eee0 |
388 | Open C<myapp02_psql.sql> in your editor and enter: |
d442cc9f |
389 | |
390 | -- |
391 | -- Add users and roles tables, along with a many-to-many join table |
392 | -- |
e179eee0 |
393 | |
d442cc9f |
394 | CREATE TABLE users ( |
e179eee0 |
395 | id SERIAL PRIMARY KEY, |
396 | username TEXT, |
397 | password TEXT, |
398 | email_address TEXT, |
399 | first_name TEXT, |
400 | last_name TEXT, |
401 | active INTEGER |
d442cc9f |
402 | ); |
e179eee0 |
403 | |
d442cc9f |
404 | CREATE TABLE roles ( |
e179eee0 |
405 | id SERIAL PRIMARY KEY, |
406 | role TEXT |
d442cc9f |
407 | ); |
e179eee0 |
408 | |
d442cc9f |
409 | CREATE TABLE user_roles ( |
e179eee0 |
410 | user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, |
411 | role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE, |
412 | PRIMARY KEY (user_id, role_id) |
d442cc9f |
413 | ); |
e179eee0 |
414 | |
d442cc9f |
415 | -- |
416 | -- Load up some initial test data |
417 | -- |
e179eee0 |
418 | INSERT INTO users (username, password, email_address, first_name, last_name, active) |
419 | VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1); |
420 | INSERT INTO users (username, password, email_address, first_name, last_name, active) |
421 | VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1); |
422 | INSERT INTO users (username, password, email_address, first_name, last_name, active) |
423 | VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0); |
424 | INSERT INTO roles (role) VALUES ('user'); |
425 | INSERT INTO roles (role) VALUES ('admin'); |
d442cc9f |
426 | INSERT INTO user_roles VALUES (1, 1); |
427 | INSERT INTO user_roles VALUES (1, 2); |
428 | INSERT INTO user_roles VALUES (2, 1); |
429 | INSERT INTO user_roles VALUES (3, 1); |
430 | |
431 | =item * |
432 | |
e179eee0 |
433 | Load the data: |
434 | |
435 | $ psql -U catappuser -W catappdb -f myapp02_psql.sql |
436 | Password for user catappuser: <catalyst> |
437 | psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" |
438 | psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" |
439 | CREATE TABLE |
440 | psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id" |
441 | psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles" |
442 | CREATE TABLE |
443 | psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles" |
444 | CREATE TABLE |
445 | INSERT 0 1 |
446 | INSERT 0 1 |
447 | INSERT 0 1 |
448 | INSERT 0 1 |
449 | INSERT 0 1 |
450 | INSERT 0 1 |
451 | INSERT 0 1 |
452 | INSERT 0 1 |
453 | INSERT 0 1 |
454 | |
455 | Confirm with: |
456 | |
457 | $ psql -U catappuser -W catappdb -c "select * from users" |
458 | Password for user catappuser: <catalyst> |
459 | id | username | password | email_address | first_name | last_name | active |
460 | ----+----------+----------+---------------+------------+-----------+-------- |
461 | 1 | test01 | mypass | t01@na.com | Joe | Blow | 1 |
462 | 2 | test02 | mypass | t02@na.com | Jane | Doe | 1 |
463 | 3 | test03 | mypass | t03@na.com | No | Go | 0 |
464 | (3 rows) |
d442cc9f |
465 | |
d442cc9f |
466 | |
467 | =item * |
468 | |
e179eee0 |
469 | Modify C<set_hashed_passwords.pl> to match the following (the only difference |
470 | is the C<connect> line): |
d442cc9f |
471 | |
e179eee0 |
472 | #!/usr/bin/perl |
473 | |
474 | use strict; |
475 | use warnings; |
476 | |
477 | use MyApp::Schema; |
478 | |
479 | my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst'); |
480 | |
481 | my @users = $schema->resultset('Users')->all; |
482 | |
483 | foreach my $user (@users) { |
484 | $user->password('mypass'); |
485 | $user->update; |
486 | } |
d442cc9f |
487 | |
e179eee0 |
488 | Run the C<set_hashed_passwords.pl> as per the "normal" flow of the |
489 | tutorial: |
d442cc9f |
490 | |
e179eee0 |
491 | $ perl -Ilib set_hashed_passwords.pl |
d442cc9f |
492 | |
e179eee0 |
493 | You can verify that it worked with this command: |
494 | |
495 | $ psql -U catappuser -W catappdb -c "select * from users" |
d442cc9f |
496 | |
d442cc9f |
497 | |
498 | =back |
499 | |
500 | =back |
501 | |
d442cc9f |
502 | |
e179eee0 |
503 | =head2 MySQL |
504 | |
505 | B<NOTE:> This section is out of data with the rest of the tutorial. |
506 | Consider using SQLite or PostgreSQL since they are current. |
507 | |
508 | Use the following steps to adapt the tutorial to MySQL. Thanks to Jim |
509 | Howard for the help. |
3533daff |
510 | |
511 | =over 4 |
512 | |
513 | =item * |
514 | |
e179eee0 |
515 | Chapter 3: Catalyst Basics |
3533daff |
516 | |
517 | =over 4 |
518 | |
519 | =item * |
520 | |
521 | Install the required software: |
522 | |
523 | =over 4 |
524 | |
525 | =item * |
526 | |
e179eee0 |
527 | The MySQL database server and client utility. |
3533daff |
528 | |
529 | =item * |
530 | |
e179eee0 |
531 | The Perl C<DBD::MySQL> module |
3533daff |
532 | |
533 | =back |
534 | |
e179eee0 |
535 | For CentOS users (see |
536 | L<Catalyst::Manual::Installation::CentOS4|Catalyst::Manual::Installation::CentOS4>), |
537 | you can use the following commands to install the software and start the MySQL |
538 | daemon: |
539 | |
540 | yum -y install mysql mysql-server |
541 | service mysqld start |
542 | |
3533daff |
543 | =item * |
544 | |
e179eee0 |
545 | Create the database and set the permissions: |
3533daff |
546 | |
e179eee0 |
547 | $ mysql |
548 | Welcome to the MySQL monitor. Commands end with ; or \g. |
549 | Your MySQL connection id is 2 to server version: 4.1.20 |
550 | |
551 | Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
552 | |
553 | mysql> create database myapp; |
554 | Query OK, 1 row affected (0.01 sec) |
555 | |
556 | mysql> grant all on myapp.* to tutorial@'localhost'; |
557 | Query OK, 0 rows affected (0.00 sec) |
558 | |
559 | mysql> flush privileges; |
560 | Query OK, 0 rows affected (0.00 sec) |
561 | |
562 | mysql> quit |
563 | Bye |
3533daff |
564 | |
565 | =item * |
566 | |
567 | Create the C<.sql> file and load the data: |
568 | |
569 | =over 4 |
570 | |
571 | =item * |
572 | |
e179eee0 |
573 | Open the C<myapp01_mysql.sql> in your editor and enter: |
3533daff |
574 | |
1390ef0e |
575 | -- |
576 | -- Create a very simple database to hold book and author information |
577 | -- |
e179eee0 |
578 | DROP TABLE IF EXISTS books; |
579 | DROP TABLE IF EXISTS book_authors; |
580 | DROP TABLE IF EXISTS authors; |
1390ef0e |
581 | CREATE TABLE books ( |
e179eee0 |
582 | id INT(11) PRIMARY KEY AUTO_INCREMENT, |
583 | title TEXT , |
584 | rating INT(11) |
585 | ); |
1390ef0e |
586 | -- 'book_authors' is a many-to-many join table between books & authors |
587 | CREATE TABLE book_authors ( |
e179eee0 |
588 | book_id INT(11), |
589 | author_id INT(11), |
590 | PRIMARY KEY (book_id, author_id) |
591 | ); |
1390ef0e |
592 | CREATE TABLE authors ( |
e179eee0 |
593 | id INT(11) PRIMARY KEY AUTO_INCREMENT, |
594 | first_name TEXT, |
595 | last_name TEXT |
1390ef0e |
596 | ); |
597 | --- |
598 | --- Load some sample data |
599 | --- |
600 | INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5); |
601 | INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5); |
602 | INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4); |
603 | INSERT INTO books VALUES (4, 'Perl Cookbook', 5); |
604 | INSERT INTO books VALUES (5, 'Designing with Web Standards', 5); |
605 | INSERT INTO authors VALUES (1, 'Greg', 'Bastien'); |
606 | INSERT INTO authors VALUES (2, 'Sara', 'Nasseh'); |
607 | INSERT INTO authors VALUES (3, 'Christian', 'Degu'); |
608 | INSERT INTO authors VALUES (4, 'Richard', 'Stevens'); |
609 | INSERT INTO authors VALUES (5, 'Douglas', 'Comer'); |
610 | INSERT INTO authors VALUES (6, 'Tom', 'Christiansen'); |
e179eee0 |
611 | INSERT INTO authors VALUES (7, ' Nathan', 'Torkington'); |
1390ef0e |
612 | INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman'); |
613 | INSERT INTO book_authors VALUES (1, 1); |
614 | INSERT INTO book_authors VALUES (1, 2); |
615 | INSERT INTO book_authors VALUES (1, 3); |
616 | INSERT INTO book_authors VALUES (2, 4); |
617 | INSERT INTO book_authors VALUES (3, 5); |
618 | INSERT INTO book_authors VALUES (4, 6); |
619 | INSERT INTO book_authors VALUES (4, 7); |
620 | INSERT INTO book_authors VALUES (5, 8); |
3533daff |
621 | |
622 | =item * |
623 | |
624 | Load the data: |
625 | |
e179eee0 |
626 | mysql -ututorial myapp < myapp01_mysql.sql |
3533daff |
627 | |
628 | =item * |
629 | |
630 | Make sure the data loaded correctly: |
631 | |
e179eee0 |
632 | $ mysql -ututorial myapp |
633 | Reading table information for completion of table and column names |
634 | You can turn off this feature to get a quicker startup with -A |
1390ef0e |
635 | |
e179eee0 |
636 | Welcome to the MySQL monitor. Commands end with ; or \g. |
637 | Your MySQL connection id is 4 to server version: 4.1.20 |
1390ef0e |
638 | |
e179eee0 |
639 | Type 'help;' or '\h' for help. Type '\c' to clear the buffer. |
640 | |
641 | mysql> show tables; |
642 | +-----------------+ |
643 | | Tables_in_myapp | |
644 | +-----------------+ |
645 | | authors | |
646 | | book_authors | |
647 | | books | |
648 | +-----------------+ |
649 | 3 rows in set (0.00 sec) |
650 | |
651 | mysql> select * from books; |
652 | +----+------------------------------------+--------+ |
653 | | id | title | rating | |
654 | +----+------------------------------------+--------+ |
655 | | 1 | CCSP SNRS Exam Certification Guide | 5 | |
656 | | 2 | TCP/IP Illustrated, Volume 1 | 5 | |
657 | | 3 | Internetworking with TCP/IP Vol.1 | 4 | |
658 | | 4 | Perl Cookbook | 5 | |
659 | | 5 | Designing with Web Standards | 5 | |
660 | +----+------------------------------------+--------+ |
661 | 5 rows in set (0.00 sec) |
662 | |
663 | mysql> |
3533daff |
664 | |
665 | =back |
666 | |
667 | =item * |
668 | |
e179eee0 |
669 | Update the model: |
3533daff |
670 | |
e179eee0 |
671 | =over 4 |
672 | |
673 | =item * |
674 | |
675 | Delete the existing model: |
3533daff |
676 | |
e179eee0 |
677 | rm lib/MyApp/Model/MyAppDB.pm |
3533daff |
678 | |
679 | =item * |
680 | |
e179eee0 |
681 | Regenerate the model using the Catalyst "_create.pl" script: |
3533daff |
682 | |
e179eee0 |
683 | script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ |
684 | dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }' |
3533daff |
685 | |
e179eee0 |
686 | =back |
3533daff |
687 | |
688 | =back |
689 | |
690 | =item * |
691 | |
e179eee0 |
692 | Chapter 5: Authentication |
3533daff |
693 | |
694 | =over 4 |
695 | |
696 | =item * |
697 | |
698 | Create the C<.sql> file for the user/roles data: |
699 | |
e179eee0 |
700 | Open C<myapp02_mysql.sql> in your editor and enter: |
3533daff |
701 | |
702 | -- |
703 | -- Add users and roles tables, along with a many-to-many join table |
704 | -- |
3533daff |
705 | CREATE TABLE users ( |
e179eee0 |
706 | id INT(11) PRIMARY KEY, |
3533daff |
707 | username TEXT, |
708 | password TEXT, |
709 | email_address TEXT, |
710 | first_name TEXT, |
711 | last_name TEXT, |
e179eee0 |
712 | active INT(11) |
3533daff |
713 | ); |
3533daff |
714 | CREATE TABLE roles ( |
e179eee0 |
715 | id INTEGER PRIMARY KEY, |
3533daff |
716 | role TEXT |
717 | ); |
3533daff |
718 | CREATE TABLE user_roles ( |
e179eee0 |
719 | user_id INT(11), |
720 | role_id INT(11), |
3533daff |
721 | PRIMARY KEY (user_id, role_id) |
722 | ); |
3533daff |
723 | -- |
724 | -- Load up some initial test data |
725 | -- |
726 | INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1); |
727 | INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1); |
728 | INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0); |
729 | INSERT INTO roles VALUES (1, 'user'); |
730 | INSERT INTO roles VALUES (2, 'admin'); |
731 | INSERT INTO user_roles VALUES (1, 1); |
732 | INSERT INTO user_roles VALUES (1, 2); |
733 | INSERT INTO user_roles VALUES (2, 1); |
734 | INSERT INTO user_roles VALUES (3, 1); |
735 | |
736 | =item * |
737 | |
e179eee0 |
738 | Load the user/roles data: |
3533daff |
739 | |
e179eee0 |
740 | mysql -ututorial myapp < myapp02_mysql.sql |
3533daff |
741 | |
742 | =item * |
743 | |
744 | Create the C<.sql> file for the hashed password data: |
745 | |
e179eee0 |
746 | Open C<myapp03_mysql.sql> in your editor and enter: |
3533daff |
747 | |
748 | -- |
749 | -- Convert passwords to SHA-1 hashes |
750 | -- |
751 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1; |
752 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2; |
753 | UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3; |
754 | |
755 | =item * |
756 | |
e179eee0 |
757 | Load the user/roles data: |
3533daff |
758 | |
e179eee0 |
759 | mysql -ututorial myapp < myapp03_mysql.sql |
3533daff |
760 | |
761 | =back |
762 | |
763 | =back |
d442cc9f |
764 | |
765 | |
d442cc9f |
766 | =head1 AUTHOR |
767 | |
768 | Kennedy Clark, C<hkclark@gmail.com> |
769 | |
770 | Please report any errors, issues or suggestions to the author. The |
771 | most recent version of the Catalyst Tutorial can be found at |
82ab4bbf |
772 | L<http://dev.catalyst.perl.org/repos/Catalyst/Catalyst-Manual/5.70/trunk/lib/Catalyst/Manual/Tutorial/>. |
d442cc9f |
773 | |
45c7830f |
774 | Copyright 2006-2008, Kennedy Clark, under Creative Commons License |
95674086 |
775 | (L<http://creativecommons.org/licenses/by-sa/3.0/us/>). |