3 Catalyst::Manual::Tutorial::Appendices - Catalyst Tutorial - Chapter 10: Appendices
8 This is B<Chapter 10 of 10> for the Catalyst tutorial.
10 L<Tutorial Overview|Catalyst::Manual::Tutorial>
16 L<Introduction|Catalyst::Manual::Tutorial::Intro>
20 L<Catalyst Basics|Catalyst::Manual::Tutorial::CatalystBasics>
24 L<More Catalyst Basics|Catalyst::Manual::Tutorial::MoreCatalystBasics>
28 L<Basic CRUD|Catalyst::Manual::Tutorial::BasicCRUD>
32 L<Authentication|Catalyst::Manual::Tutorial::Authentication>
36 L<Authorization|Catalyst::Manual::Tutorial::Authorization>
40 L<Debugging|Catalyst::Manual::Tutorial::Debugging>
44 L<Testing|Catalyst::Manual::Tutorial::Testing>
48 L<Advanced CRUD|Catalyst::Manual::Tutorial::AdvancedCRUD>
59 This chapter of the tutorial provides supporting information relevant to
60 the Catalyst tutorial.
63 =head1 APPENDIX 1: CUT AND PASTE FOR POD-BASED EXAMPLES
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
69 =head2 "Un-indenting" with Vi/Vim
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>:
82 Removes four leading spaces from the entire file (from the first line,
83 C<0>, to the last line, C<$>).
89 A shortcut for the previous item (C<%> specifies the entire file; so
90 this removes four leading spaces from every line).
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.
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).
108 =head2 "Un-indenting" with Emacs
110 Although there author has not used Emacs for many years (apologies to
111 the Emacs fans out there), here is a quick hint to get you started. To
112 replace the leading spaces of every line in a file, use:
114 M-x replace-regexp<RET>
115 Replace regexp: ^ <RET>
118 All of that will occur on the single line at the bottom of your screen.
119 Note that "<RET>" represents the return key/enter. Also, there are
120 four spaces after the "^" on the "Replace regexp:" line and no spaces
121 entered on the last line.
123 You can limit the replacement operation by selecting text first (depending
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
126 C<C-E<lt>> and C<C-E<gt>> to set the mark at the beginning and end of the
130 =head1 APPENDIX 2: USING POSTGRESQL AND MYSQL
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 PostgreSQL and MySQL. 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.
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.
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
159 Chapter 3: More Catalyst Basics
165 Install the PostgreSQL server and client and DBD::Pg:
167 If you are following along in Debian 5, you can quickly install these
168 items via this command:
170 sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl
172 To configure the permissions, you can open
173 C</etc/postgresql/8.3/main/pg_hba.conf> and change this line (near the
176 # "local" is for Unix domain socket connections only
177 local all all ident sameuser
181 # "local" is for Unix domain socket connections only
184 And then restart PostgreSQL:
186 sudo /etc/init.d/postgresql-8.3 restart
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
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
202 $ sudo -u postgres createdb -O catappuser catappdb
207 Create the C<.sql> file and load the data:
213 Open the C<myapp01_psql.sql> in your editor and enter:
216 -- Drops just in case you are reloading
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;
226 -- Create a very simple database to hold book and author information
229 id SERIAL PRIMARY KEY,
232 -- Manually add these later
233 -- created TIMESTAMP NOT NULL DEFAULT now(),
237 CREATE TABLE authors (
238 id SERIAL PRIMARY KEY,
243 -- 'book_authors' is a many-to-many join table between books & authors
244 CREATE TABLE book_authors (
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)
251 --- Load some sample data
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');
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);
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"
284 psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
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"
296 Make sure the data loaded correctly:
298 $ psql -U catappuser -W catappdb
299 Password for user catappuser: <catalyst>
300 Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
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
310 Schema | Name | Type | Owner
311 --------+--------------+-------+------------
312 public | authors | table | catappuser
313 public | book_authors | table | catappuser
314 public | books | table | catappuser
317 catappdb=> select * from books;
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
333 After the steps where you:
337 create lib/MyAppDB.pm
339 create lib/MyAppDB/Book.pm
341 create lib/MyAppDB/Author.pm
343 create lib/MyAppDB/BookAuthor.pm
348 Generate the model using the Catalyst "_create.pl" script:
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 }'
359 Chapter 4: Basic CRUD
361 Add Datetime Columns to Our Existing Books Table
363 $ psql -U catappuser -W catappdb
365 catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();
367 catappdb=> ALTER TABLE books ADD updated TIMESTAMP;
371 Re-generate the model using the Catalyst "_create.pl" script:
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 }'
380 Chapter 5: Authentication
386 Create the C<.sql> file for the user/roles data:
388 Open C<myapp02_psql.sql> in your editor and enter:
391 -- Add users and roles tables, along with a many-to-many join table
395 id SERIAL PRIMARY KEY,
405 id SERIAL PRIMARY KEY,
409 CREATE TABLE user_roles (
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)
416 -- Load up some initial test data
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');
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);
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"
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"
443 psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles"
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
469 Modify C<set_hashed_passwords.pl> to match the following (the only difference
470 is the C<connect> line):
479 my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst');
481 my @users = $schema->resultset('Users')->all;
483 foreach my $user (@users) {
484 $user->password('mypass');
488 Run the C<set_hashed_passwords.pl> as per the "normal" flow of the
491 $ perl -Ilib set_hashed_passwords.pl
493 You can verify that it worked with this command:
495 $ psql -U catappuser -W catappdb -c "select * from users"
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.
508 Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
515 Chapter 3: Catalyst Basics
521 Install the required software:
527 The MySQL database server and client utility.
531 The Perl C<DBD::MySQL> module
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
540 yum -y install mysql mysql-server
545 Create the database and set the permissions:
548 Welcome to the MySQL monitor. Commands end with ; or \g.
549 Your MySQL connection id is 2 to server version: 4.1.20
551 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
553 mysql> create database myapp;
554 Query OK, 1 row affected (0.01 sec)
556 mysql> grant all on myapp.* to tutorial@'localhost';
557 Query OK, 0 rows affected (0.00 sec)
559 mysql> flush privileges;
560 Query OK, 0 rows affected (0.00 sec)
567 Create the C<.sql> file and load the data:
573 Open the C<myapp01_mysql.sql> in your editor and enter:
576 -- Create a very simple database to hold book and author information
578 DROP TABLE IF EXISTS books;
579 DROP TABLE IF EXISTS book_authors;
580 DROP TABLE IF EXISTS authors;
582 id INT(11) PRIMARY KEY AUTO_INCREMENT,
586 -- 'book_authors' is a many-to-many join table between books & authors
587 CREATE TABLE book_authors (
590 PRIMARY KEY (book_id, author_id)
592 CREATE TABLE authors (
593 id INT(11) PRIMARY KEY AUTO_INCREMENT,
598 --- Load some sample data
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');
611 INSERT INTO authors VALUES (7, ' Nathan', 'Torkington');
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);
626 mysql -ututorial myapp < myapp01_mysql.sql
630 Make sure the data loaded correctly:
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
636 Welcome to the MySQL monitor. Commands end with ; or \g.
637 Your MySQL connection id is 4 to server version: 4.1.20
639 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
649 3 rows in set (0.00 sec)
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)
675 Delete the existing model:
677 rm lib/MyApp/Model/MyAppDB.pm
681 Regenerate the model using the Catalyst "_create.pl" script:
683 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
684 dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }'
692 Chapter 5: Authentication
698 Create the C<.sql> file for the user/roles data:
700 Open C<myapp02_mysql.sql> in your editor and enter:
703 -- Add users and roles tables, along with a many-to-many join table
706 id INT(11) PRIMARY KEY,
715 id INTEGER PRIMARY KEY,
718 CREATE TABLE user_roles (
721 PRIMARY KEY (user_id, role_id)
724 -- Load up some initial test data
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);
738 Load the user/roles data:
740 mysql -ututorial myapp < myapp02_mysql.sql
744 Create the C<.sql> file for the hashed password data:
746 Open C<myapp03_mysql.sql> in your editor and enter:
749 -- Convert passwords to SHA-1 hashes
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;
757 Load the user/roles data:
759 mysql -ututorial myapp < myapp03_mysql.sql
768 Kennedy Clark, C<hkclark@gmail.com>
770 Please report any errors, issues or suggestions to the author. The
771 most recent version of the Catalyst Tutorial can be found at
772 L<http://dev.catalyst.perl.org/repos/Catalyst/Catalyst-Manual/5.70/trunk/lib/Catalyst/Manual/Tutorial/>.
774 Copyright 2006-2008, Kennedy Clark, under Creative Commons License
775 (L<http://creativecommons.org/licenses/by-sa/3.0/us/>).