1Catalyst::Manual::TutorUisaelr::C1o0n_tArpipbeuCntadetidaclePyses(rt3l:):DMoacnuumaeln:t:aTtuitoonrial::10_Appendices(3)
2
6 Catalyst::Manual::Tutorial::10_Appendices - Catalyst Tutorial - Chapter
7 10: Appendices
8
10 This is Chapter 10 of 10 for the Catalyst tutorial.
11 Tutorial Overview
13 1. Introduction
15 2. Catalyst Basics
17 3. More Catalyst Basics
19 4. Basic CRUD
21 5. Authentication
23 6. Authorization
25 7. Debugging
27 8. Testing
29 9. Advanced CRUD
31 10. 10_Appendices
33
35 This chapter of the tutorial provides supporting information relevant
36 to the Catalyst tutorial.
37
39 You may notice that Pod indents example code with four spaces. This
40 section provides some quick advice to "un-indent" this text in common
41 editors.
42 "Un-indenting" with Vi/Vim
44 When cutting and pasting multi-line text from Pod-based documents, the
45 following vi/vim regexs can be helpful to "un-indent" the inserted text
46 (do NOT type the quotes, they are only included to show spaces in the
47 regex patterns). Note that all 3 of the regexs end in 4 spaces:
48 · ":0,$s/^ "
50 Removes four leading spaces from the entire file (from the first
52 line, 0, to the last line, "$").
53 · "%s/^ "
55 A shortcut for the previous item ("%" specifies the entire file; so
57 this removes four leading spaces from every line).
58 · ":.,$s/^ "
60 Removes the first four spaces from the line the cursor is on at the
62 time the regex command is executed (".") to the last line of the
63 file.
64 · ":.,44s/^ "
66 Removes four leading space from the current line through line 44
68 (obviously adjust the 44 to the appropriate value in your example).
69 "Un-indenting" with Emacs
71 Although the author has not used Emacs for many years (apologies to the
72 Emacs fans out there), here is a quick hint to get you started. To
73 replace the leading spaces of every line in a file, use:
74 M-x replace-regexp<RET>
76 Replace regexp: ^ <RET>
77 with: <RET>
78 All of that will occur on the single line at the bottom of your screen.
80 Note that "<RET>" represents the return key/enter. Also, there are
81 four spaces after the "^" on the "Replace regexp:" line and no spaces
82 entered on the last line.
83 You can limit the replacement operation by selecting text first
85 (depending on your version of Emacs, you can either use the mouse or
86 experiment with commands such as "C-SPC" to set the mark at the cursor
87 location and "C-<" and "C->" to set the mark at the beginning and end
88 of the file respectively.
89 Also, Stefan Kangas sent in the following tip about an alternate
91 approach using the command "indent-region" to redo the indentation for
92 the currently selected region (adhering to indent rules in the current
93 major mode). You can run the command by typing M-x indent-region or
94 pressing the default keybinding C-M-\ in cperl-mode. Additional
95 details can be found here:
96 http://www.gnu.org/software/emacs/manual/html_node/emacs/Indentation-Commands.html
98 <http://www.gnu.org/software/emacs/manual/html_node/emacs/Indentation-
99 Commands.html>
100
102 The main database used in this tutorial is the very simple yet powerful
103 SQLite <http://www.sqlite.org>. This section provides information that
104 can be used to "convert" the tutorial to use PostgreSQL
105 <http://www.postgresql.org> and MySQL <http://dev.mysql.com>. However,
106 note that part of the beauty of the MVC architecture is that very
107 little database-specific code is spread throughout the system (at least
108 when MVC is "done right"). Consequently, converting from one database
109 to another is relatively painless with most Catalyst applications. In
110 general, you just need to adapt the schema definition ".sql" file you
111 use to initialize your database and adjust a few configuration
112 parameters.
113 Also note that the purpose of the data definition statements for this
115 section are not designed to take maximum advantage of the various
116 features in each database for issues such as referential integrity and
117 field types/constraints.
118 PostgreSQL
120 Use the following steps to adapt the tutorial to PostgreSQL. Thanks to
121 Caelum (Rafael Kitover) for assistance with the most recent updates,
122 and Louis Moore, Marcello Romani and Tom Lanyon for help with earlier
123 versions.
124 · Chapter 3: More Catalyst Basics
126 · Install the PostgreSQL server and client and DBD::Pg:
128 If you are following along in Debian 5, you can quickly install
130 these items via this command:
131 sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl
133 To configure the permissions, you can open
135 "/etc/postgresql/8.3/main/pg_hba.conf" and change this line
136 (near the bottom):
137 # "local" is for Unix domain socket connections only
139 local all all ident sameuser
140 to:
142 # "local" is for Unix domain socket connections only
144 local all all trust
145 And then restart PostgreSQL:
147 sudo /etc/init.d/postgresql-8.3 restart
149 · Create the database and a user for the database (note that we
151 are using "<catalyst>" to represent the hidden password of
152 "catalyst"):
153 $ sudo -u postgres createuser -P catappuser
155 Enter password for new role: <catalyst>
156 Enter it again: <catalyst>
157 Shall the new role be a superuser? (y/n) n
158 Shall the new role be allowed to create databases? (y/n) n
159 Shall the new role be allowed to create more new roles? (y/n) n
160 CREATE ROLE
161 $ sudo -u postgres createdb -O catappuser catappdb
162 CREATE DATABASE
163 · Create the ".sql" file and load the data:
165 · Open the "myapp01_psql.sql" in your editor and enter:
167 --
169 -- Drops just in case you are reloading
170 ---
171 DROP TABLE IF EXISTS books CASCADE;
172 DROP TABLE IF EXISTS authors CASCADE;
173 DROP TABLE IF EXISTS book_authors CASCADE;
174 DROP TABLE IF EXISTS users CASCADE;
175 DROP TABLE IF EXISTS roles CASCADE;
176 DROP TABLE IF EXISTS user_roles CASCADE;
177 --
179 -- Create a very simple database to hold book and author information
180 --
181 CREATE TABLE books (
182 id SERIAL PRIMARY KEY,
183 title TEXT ,
184 rating INTEGER,
185 -- Manually add these later
186 -- created TIMESTAMP NOT NULL DEFAULT now(),
187 -- updated TIMESTAMP
188 );
189 CREATE TABLE authors (
191 id SERIAL PRIMARY KEY,
192 first_name TEXT,
193 last_name TEXT
194 );
195 -- 'book_authors' is a many-to-many join table between books & authors
197 CREATE TABLE book_authors (
198 book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE,
199 author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE,
200 PRIMARY KEY (book_id, author_id)
201 );
202 ---
204 --- Load some sample data
205 ---
206 INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5);
207 INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5);
208 INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4);
209 INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5);
210 INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5);
211 INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien');
212 INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh');
213 INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu');
214 INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens');
215 INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer');
216 INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen');
217 INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington');
218 INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman');
219 INSERT INTO book_authors VALUES (1, 1);
220 INSERT INTO book_authors VALUES (1, 2);
221 INSERT INTO book_authors VALUES (1, 3);
222 INSERT INTO book_authors VALUES (2, 4);
223 INSERT INTO book_authors VALUES (3, 5);
224 INSERT INTO book_authors VALUES (4, 6);
225 INSERT INTO book_authors VALUES (4, 7);
226 INSERT INTO book_authors VALUES (5, 8);
227 · Load the data:
229 $ psql -U catappuser -W catappdb -f myapp01_psql.sql
231 Password for user catappuser:
232 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id"
233 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books"
234 CREATE TABLE
235 psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
236 CREATE TABLE
237 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id"
238 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
239 CREATE TABLE
240 INSERT 0 1
241 INSERT 0 1
242 INSERT 0 1
243 ...
244 · Make sure the data loaded correctly:
246 $ psql -U catappuser -W catappdb
248 Password for user catappuser: <catalyst>
249 Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
250 Type: \copyright for distribution terms
252 \h for help with SQL commands
253 \? for help with psql commands
254 \g or terminate with semicolon to execute query
255 \q to quit
256 catappdb=> \dt
258 List of relations
259 Schema | Name | Type | Owner
260 --------+--------------+-------+------------
261 public | authors | table | catappuser
262 public | book_authors | table | catappuser
263 public | books | table | catappuser
264 (3 rows)
265 catappdb=> select * from books;
267 id | title | rating
268 ----+------------------------------------+--------
269 1 | CCSP SNRS Exam Certification Guide | 5
270 2 | TCP/IP Illustrated, Volume 1 | 5
271 3 | Internetworking with TCP/IP Vol.1 | 4
272 4 | Perl Cookbook | 5
273 5 | Designing with Web Standards | 5
274 (5 rows)
275 catappdb=>
277 · After the steps where you:
279 edit lib/MyApp.pm
281 create lib/MyAppDB.pm
283 create lib/MyAppDB/Book.pm
285 create lib/MyAppDB/Author.pm
287 create lib/MyAppDB/BookAuthor.pm
289 · Generate the model using the Catalyst "_create.pl" script:
291 $ rm lib/MyApp/Model/DB.pm # Delete just in case already there
293 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
294 create=static components=TimeStamp,EncodedColumn \
295 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
296 · Chapter 4: Basic CRUD
298 Add Datetime Columns to Our Existing Books Table
300 $ psql -U catappuser -W catappdb
302 ...
303 catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();
304 ALTER TABLE
305 catappdb=> ALTER TABLE books ADD updated TIMESTAMP;
306 ALTER TABLE
307 catappdb=> \q
308 Re-generate the model using the Catalyst "_create.pl" script:
310 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
312 create=static components=TimeStamp,EncodedColumn \
313 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
314 · Chapter 5: Authentication
316 · Create the ".sql" file for the user/roles data:
318 Open "myapp02_psql.sql" in your editor and enter:
320 --
322 -- Add users and roles tables, along with a many-to-many join table
323 --
324 CREATE TABLE users (
326 id SERIAL PRIMARY KEY,
327 username TEXT,
328 password TEXT,
329 email_address TEXT,
330 first_name TEXT,
331 last_name TEXT,
332 active INTEGER
333 );
334 CREATE TABLE roles (
336 id SERIAL PRIMARY KEY,
337 role TEXT
338 );
339 CREATE TABLE user_roles (
341 user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
342 role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
343 PRIMARY KEY (user_id, role_id)
344 );
345 --
347 -- Load up some initial test data
348 --
349 INSERT INTO users (username, password, email_address, first_name, last_name, active)
350 VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
351 INSERT INTO users (username, password, email_address, first_name, last_name, active)
352 VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
353 INSERT INTO users (username, password, email_address, first_name, last_name, active)
354 VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
355 INSERT INTO roles (role) VALUES ('user');
356 INSERT INTO roles (role) VALUES ('admin');
357 INSERT INTO user_roles VALUES (1, 1);
358 INSERT INTO user_roles VALUES (1, 2);
359 INSERT INTO user_roles VALUES (2, 1);
360 INSERT INTO user_roles VALUES (3, 1);
361 · Load the data:
363 $ psql -U catappuser -W catappdb -f myapp02_psql.sql
365 Password for user catappuser: <catalyst>
366 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
367 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
368 CREATE TABLE
369 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
370 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
371 CREATE TABLE
372 psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles"
373 CREATE TABLE
374 INSERT 0 1
375 INSERT 0 1
376 INSERT 0 1
377 INSERT 0 1
378 INSERT 0 1
379 INSERT 0 1
380 INSERT 0 1
381 INSERT 0 1
382 INSERT 0 1
383 Confirm with:
385 $ psql -U catappuser -W catappdb -c "select * from users"
387 Password for user catappuser: <catalyst>
388 id | username | password | email_address | first_name | last_name | active
389 ----+----------+----------+---------------+------------+-----------+--------
390 1 | test01 | mypass | t01@na.com | Joe | Blow | 1
391 2 | test02 | mypass | t02@na.com | Jane | Doe | 1
392 3 | test03 | mypass | t03@na.com | No | Go | 0
393 (3 rows)
394 · Modify "set_hashed_passwords.pl" to match the following (the
396 only difference is the "connect" line):
397 #!/usr/bin/perl
399 use strict;
401 use warnings;
402 use MyApp::Schema;
404 my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst');
406 my @users = $schema->resultset('Users')->all;
408 foreach my $user (@users) {
410 $user->password('mypass');
411 $user->update;
412 }
413 Run the "set_hashed_passwords.pl" as per the "normal" flow of
415 the tutorial:
416 $ perl -Ilib set_hashed_passwords.pl
418 You can verify that it worked with this command:
420 $ psql -U catappuser -W catappdb -c "select * from users"
422 MySQL
424 NOTE: This section is out of date with the rest of the tutorial.
425 Consider using SQLite or PostgreSQL since they are current.
426 Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
428 Howard for the help.
429 · Chapter 3: Catalyst Basics
431 · Install the required software:
433 · The MySQL database server and client utility.
435 · The Perl "DBD::MySQL" module
437 For CentOS users (see Catalyst::Manual::Installation::CentOS4),
439 you can use the following commands to install the software and
440 start the MySQL daemon:
441 yum -y install mysql mysql-server
443 service mysqld start
444 · Create the database and set the permissions:
446 $ mysql
448 Welcome to the MySQL monitor. Commands end with ; or \g.
449 Your MySQL connection id is 2 to server version: 4.1.20
450 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
452 mysql> create database myapp;
454 Query OK, 1 row affected (0.01 sec)
455 mysql> grant all on myapp.* to tutorial@'localhost';
457 Query OK, 0 rows affected (0.00 sec)
458 mysql> flush privileges;
460 Query OK, 0 rows affected (0.00 sec)
461 mysql> quit
463 Bye
464 · Create the ".sql" file and load the data:
466 · Open the "myapp01_mysql.sql" in your editor and enter:
468 --
470 -- Create a very simple database to hold book and author information
471 --
472 DROP TABLE IF EXISTS books;
473 DROP TABLE IF EXISTS book_authors;
474 DROP TABLE IF EXISTS authors;
475 CREATE TABLE books (
476 id INT(11) PRIMARY KEY AUTO_INCREMENT,
477 title TEXT ,
478 rating INT(11)
479 );
480 -- 'book_authors' is a many-to-many join table between books & authors
481 CREATE TABLE book_authors (
482 book_id INT(11),
483 author_id INT(11),
484 PRIMARY KEY (book_id, author_id)
485 );
486 CREATE TABLE authors (
487 id INT(11) PRIMARY KEY AUTO_INCREMENT,
488 first_name TEXT,
489 last_name TEXT
490 );
491 ---
492 --- Load some sample data
493 ---
494 INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5);
495 INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5);
496 INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4);
497 INSERT INTO books VALUES (4, 'Perl Cookbook', 5);
498 INSERT INTO books VALUES (5, 'Designing with Web Standards', 5);
499 INSERT INTO authors VALUES (1, 'Greg', 'Bastien');
500 INSERT INTO authors VALUES (2, 'Sara', 'Nasseh');
501 INSERT INTO authors VALUES (3, 'Christian', 'Degu');
502 INSERT INTO authors VALUES (4, 'Richard', 'Stevens');
503 INSERT INTO authors VALUES (5, 'Douglas', 'Comer');
504 INSERT INTO authors VALUES (6, 'Tom', 'Christiansen');
505 INSERT INTO authors VALUES (7, ' Nathan', 'Torkington');
506 INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman');
507 INSERT INTO book_authors VALUES (1, 1);
508 INSERT INTO book_authors VALUES (1, 2);
509 INSERT INTO book_authors VALUES (1, 3);
510 INSERT INTO book_authors VALUES (2, 4);
511 INSERT INTO book_authors VALUES (3, 5);
512 INSERT INTO book_authors VALUES (4, 6);
513 INSERT INTO book_authors VALUES (4, 7);
514 INSERT INTO book_authors VALUES (5, 8);
515 · Load the data:
517 mysql -ututorial myapp < myapp01_mysql.sql
519 · Make sure the data loaded correctly:
521 $ mysql -ututorial myapp
523 Reading table information for completion of table and column names
524 You can turn off this feature to get a quicker startup with -A
525 Welcome to the MySQL monitor. Commands end with ; or \g.
527 Your MySQL connection id is 4 to server version: 4.1.20
528 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
530 mysql> show tables;
532 +-----------------+
533 | Tables_in_myapp |
534 +-----------------+
535 | authors |
536 | book_authors |
537 | books |
538 +-----------------+
539 3 rows in set (0.00 sec)
540 mysql> select * from books;
542 +----+------------------------------------+--------+
543 | id | title | rating |
544 +----+------------------------------------+--------+
545 | 1 | CCSP SNRS Exam Certification Guide | 5 |
546 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
547 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
548 | 4 | Perl Cookbook | 5 |
549 | 5 | Designing with Web Standards | 5 |
550 +----+------------------------------------+--------+
551 5 rows in set (0.00 sec)
552 mysql>
554 · Update the model:
556 · Delete the existing model:
558 rm lib/MyApp/Model/MyAppDB.pm
560 · Regenerate the model using the Catalyst "_create.pl"
562 script:
563 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
565 dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }'
566 · Chapter 5: Authentication
568 · Create the ".sql" file for the user/roles data:
570 Open "myapp02_mysql.sql" in your editor and enter:
572 --
574 -- Add users and roles tables, along with a many-to-many join table
575 --
576 CREATE TABLE users (
577 id INT(11) PRIMARY KEY,
578 username TEXT,
579 password TEXT,
580 email_address TEXT,
581 first_name TEXT,
582 last_name TEXT,
583 active INT(11)
584 );
585 CREATE TABLE roles (
586 id INTEGER PRIMARY KEY,
587 role TEXT
588 );
589 CREATE TABLE user_roles (
590 user_id INT(11),
591 role_id INT(11),
592 PRIMARY KEY (user_id, role_id)
593 );
594 --
595 -- Load up some initial test data
596 --
597 INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
598 INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
599 INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
600 INSERT INTO roles VALUES (1, 'user');
601 INSERT INTO roles VALUES (2, 'admin');
602 INSERT INTO user_roles VALUES (1, 1);
603 INSERT INTO user_roles VALUES (1, 2);
604 INSERT INTO user_roles VALUES (2, 1);
605 INSERT INTO user_roles VALUES (3, 1);
606 · Load the user/roles data:
608 mysql -ututorial myapp < myapp02_mysql.sql
610 · Create the ".sql" file for the hashed password data:
612 Open "myapp03_mysql.sql" in your editor and enter:
614 --
616 -- Convert passwords to SHA-1 hashes
617 --
618 UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1;
619 UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2;
620 UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3;
621 · Load the user/roles data:
623 mysql -ututorial myapp < myapp03_mysql.sql
625
627 Kennedy Clark, "hkclark@gmail.com"
628 Please report any errors, issues or suggestions to the author. The
630 most recent version of the Catalyst Tutorial can be found at
631 http://dev.catalyst.perl.org/repos/Catalyst/Catalyst-Manual/5.80/trunk/lib/Catalyst/Manual/Tutorial/
632 <http://dev.catalyst.perl.org/repos/Catalyst/Catalyst-
633 Manual/5.80/trunk/lib/Catalyst/Manual/Tutorial/>.
634 Copyright 2006-2008, Kennedy Clark, under Creative Commons License
636 (http://creativecommons.org/licenses/by-sa/3.0/us/
637 <http://creativecommons.org/licenses/by-sa/3.0/us/>).
638perl v5.12.0 2C0a1t0a-l0y2s-t1:7:Manual::Tutorial::10_Appendices(3)