3 Catalyst::Manual::Tutorial::10_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::01_Intro>
20 L<Catalyst Basics|Catalyst::Manual::Tutorial::02_CatalystBasics>
24 L<More Catalyst Basics|Catalyst::Manual::Tutorial::03_MoreCatalystBasics>
28 L<Basic CRUD|Catalyst::Manual::Tutorial::04_BasicCRUD>
32 L<Authentication|Catalyst::Manual::Tutorial::05_Authentication>
36 L<Authorization|Catalyst::Manual::Tutorial::06_Authorization>
40 L<Debugging|Catalyst::Manual::Tutorial::07_Debugging>
44 L<Testing|Catalyst::Manual::Tutorial::08_Testing>
48 L<Advanced CRUD|Catalyst::Manual::Tutorial::09_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 the 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-< >> and C<< C-> >> to set the mark at the beginning and end of the
129 Also, Stefan Kangas sent in the following tip about an alternate
130 approach using the command C<indent-region> to redo the indentation
131 for the currently selected region (adhering to indent rules in the
132 current major mode). You can run the command by typing M-x
133 indent-region or pressing the default keybinding C-M-\ in cperl-mode.
134 Additional details can be found here:
136 L<http://www.gnu.org/software/emacs/manual/html_node/emacs/Indentation-Commands.html>
139 =head1 APPENDIX 2: USING POSTGRESQL AND MYSQL
141 The main database used in this tutorial is the very simple yet powerful
142 L<SQLite|http://www.sqlite.org>. This section provides information
143 that can be used to "convert" the tutorial to use
144 L<PostgreSQL|http://www.postgresql.org> and
145 L<MySQL|http://dev.mysql.com>. However, note that part of
146 the beauty of the MVC architecture is that very little database-specific
147 code is spread throughout the system (at least when MVC is "done
148 right"). Consequently, converting from one database to another is
149 relatively painless with most Catalyst applications. In general, you
150 just need to adapt the schema definition C<.sql> file you use to
151 initialize your database and adjust a few configuration parameters.
153 Also note that the purpose of the data definition statements for this
154 section are not designed to take maximum advantage of the various
155 features in each database for issues such as referential integrity and
156 field types/constraints.
161 Use the following steps to adapt the tutorial to PostgreSQL. Thanks
162 to Caelum (Rafael Kitover) for assistance with the most recent
163 updates, and Louis Moore, Marcello Romani and Tom Lanyon for help with
170 Chapter 3: More Catalyst Basics
176 Install the PostgreSQL server and client and DBD::Pg:
178 If you are following along in Debian 6, you can quickly install these
179 items via this command:
181 sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl
183 To configure the permissions, you can open
184 F</etc/postgresql/8.3/main/pg_hba.conf> and change this line (near the
187 # "local" is for Unix domain socket connections only
188 local all all ident sameuser
192 # "local" is for Unix domain socket connections only
195 And then restart PostgreSQL:
197 sudo /etc/init.d/postgresql-8.3 restart
202 Create the database and a user for the database (note that we are
203 using "<catalyst>" to represent the hidden password of
206 $ sudo -u postgres createuser -P catappuser
207 Enter password for new role: <catalyst>
208 Enter it again: <catalyst>
209 Shall the new role be a superuser? (y/n) n
210 Shall the new role be allowed to create databases? (y/n) n
211 Shall the new role be allowed to create more new roles? (y/n) n
213 $ sudo -u postgres createdb -O catappuser catappdb
218 Create the C<.sql> file and load the data:
224 Open the F<myapp01_psql.sql> in your editor and enter:
227 -- Drops just in case you are reloading
229 DROP TABLE IF EXISTS books CASCADE;
230 DROP TABLE IF EXISTS authors CASCADE;
231 DROP TABLE IF EXISTS book_authors CASCADE;
232 DROP TABLE IF EXISTS users CASCADE;
233 DROP TABLE IF EXISTS roles CASCADE;
234 DROP TABLE IF EXISTS user_roles CASCADE;
237 -- Create a very simple database to hold book and author information
240 id SERIAL PRIMARY KEY,
243 -- Manually add these later
244 -- created TIMESTAMP NOT NULL DEFAULT now(),
248 CREATE TABLE authors (
249 id SERIAL PRIMARY KEY,
254 -- 'book_authors' is a many-to-many join table between books & authors
255 CREATE TABLE book_authors (
256 book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE,
257 author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE,
258 PRIMARY KEY (book_id, author_id)
262 --- Load some sample data
264 INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5);
265 INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5);
266 INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4);
267 INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5);
268 INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5);
269 INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien');
270 INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh');
271 INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu');
272 INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens');
273 INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer');
274 INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen');
275 INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington');
276 INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman');
277 INSERT INTO book_authors VALUES (1, 1);
278 INSERT INTO book_authors VALUES (1, 2);
279 INSERT INTO book_authors VALUES (1, 3);
280 INSERT INTO book_authors VALUES (2, 4);
281 INSERT INTO book_authors VALUES (3, 5);
282 INSERT INTO book_authors VALUES (4, 6);
283 INSERT INTO book_authors VALUES (4, 7);
284 INSERT INTO book_authors VALUES (5, 8);
290 $ psql -U catappuser -W catappdb -f myapp01_psql.sql
291 Password for user catappuser:
292 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id"
293 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books"
295 psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
297 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id"
298 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
307 Make sure the data loaded correctly:
309 $ psql -U catappuser -W catappdb
310 Password for user catappuser: <catalyst>
311 Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
313 Type: \copyright for distribution terms
314 \h for help with SQL commands
315 \? for help with psql commands
316 \g or terminate with semicolon to execute query
321 Schema | Name | Type | Owner
322 --------+--------------+-------+------------
323 public | authors | table | catappuser
324 public | book_authors | table | catappuser
325 public | books | table | catappuser
328 catappdb=> select * from books;
330 ----+------------------------------------+--------
331 1 | CCSP SNRS Exam Certification Guide | 5
332 2 | TCP/IP Illustrated, Volume 1 | 5
333 3 | Internetworking with TCP/IP Vol.1 | 4
334 4 | Perl Cookbook | 5
335 5 | Designing with Web Standards | 5
344 After the steps where you:
348 create lib/MyAppDB.pm
350 create lib/MyAppDB/Book.pm
352 create lib/MyAppDB/Author.pm
354 create lib/MyAppDB/BookAuthor.pm
359 Generate the model using the Catalyst "_create.pl" script:
361 $ rm lib/MyApp/Model/DB.pm # Delete just in case already there
362 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
363 create=static components=TimeStamp,PassphraseColumn \
364 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
370 Chapter 4: Basic CRUD
372 Add Datetime Columns to Our Existing Books Table
374 $ psql -U catappuser -W catappdb
376 catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();
378 catappdb=> ALTER TABLE books ADD updated TIMESTAMP;
382 Re-generate the model using the Catalyst "_create.pl" script:
384 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
385 create=static components=TimeStamp,PassphraseColumn \
386 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
391 Chapter 5: Authentication
397 Create the C<.sql> file for the user/roles data:
399 Open F<myapp02_psql.sql> in your editor and enter:
402 -- Add users and roles tables, along with a many-to-many join table
406 id SERIAL PRIMARY KEY,
416 id SERIAL PRIMARY KEY,
420 CREATE TABLE user_roles (
421 user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
422 role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
423 PRIMARY KEY (user_id, role_id)
427 -- Load up some initial test data
429 INSERT INTO users (username, password, email_address, first_name, last_name, active)
430 VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
431 INSERT INTO users (username, password, email_address, first_name, last_name, active)
432 VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
433 INSERT INTO users (username, password, email_address, first_name, last_name, active)
434 VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
435 INSERT INTO roles (role) VALUES ('user');
436 INSERT INTO roles (role) VALUES ('admin');
437 INSERT INTO user_roles VALUES (1, 1);
438 INSERT INTO user_roles VALUES (1, 2);
439 INSERT INTO user_roles VALUES (2, 1);
440 INSERT INTO user_roles VALUES (3, 1);
446 $ psql -U catappuser -W catappdb -f myapp02_psql.sql
447 Password for user catappuser: <catalyst>
448 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
449 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
451 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
452 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
454 psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles"
468 $ psql -U catappuser -W catappdb -c "select * from users"
469 Password for user catappuser: <catalyst>
470 id | username | password | email_address | first_name | last_name | active
471 ----+----------+----------+---------------+------------+-----------+--------
472 1 | test01 | mypass | t01@na.com | Joe | Blow | 1
473 2 | test02 | mypass | t02@na.com | Jane | Doe | 1
474 3 | test03 | mypass | t03@na.com | No | Go | 0
480 Modify F<set_hashed_passwords.pl> to match the following (the only difference
481 is the C<connect> line):
490 my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst');
492 my @users = $schema->resultset('Users')->all;
494 foreach my $user (@users) {
495 $user->password('mypass');
499 Run the F<set_hashed_passwords.pl> as per the "normal" flow of the
502 $ perl -Ilib set_hashed_passwords.pl
504 You can verify that it worked with this command:
506 $ psql -U catappuser -W catappdb -c "select * from users"
516 Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
517 Howard for the help and Zsolt Zemancsik for the up to date fixes.
523 Chapter 3: Catalyst Basics
529 Install the required software:
535 The MySQL database server and client utility.
539 The Perl L<DBD::MySQL> module
543 For CentOS users (see
544 L<Catalyst::Manual::Installation::CentOS4>),
545 you can use the following commands to install the software and start the MySQL
548 yum -y install mysql mysql-server
551 For Debian users you can use the following commands to install the software and start the MySQL
554 apt-get install mysql-client mysql-server
555 /etc/init.d/mysql start
557 B<NOTE:> The tutorial is based on Foreign Keys in database which is supported by InnoDB.
558 Only MySQL 5.0 and above supports InnoDB storage Engine so you need to have InnoDB support
559 in you MySQL. You can simply figure out that your install supports it or not:
563 Welcome to the MySQL monitor. Commands end with ; or \g.
565 Type 'help;' or '\h' for help. Type '\c' to clear the current input
568 mysql> SHOW VARIABLES LIKE 'have_innodb';
569 +---------------+-------+
570 | Variable_name | Value |
571 +---------------+-------+
572 | have_innodb | YES |
573 +---------------+-------+
574 1 row in set (0.01 sec)
579 If the Value is "YES" you can use your setup (Debian based mysql supports it by default).
580 Else, you need to configure your my.cnf or start your MySQL daemon without --skip-innodb option.
584 Create the database and set the permissions:
588 Welcome to the MySQL monitor. Commands end with ; or \g.
590 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
592 mysql> CREATE DATABASE `myapp`;
593 Query OK, 1 row affected (0.01 sec)
595 mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword';
596 Query OK, 0 rows affected (0.00 sec)
598 mysql> FLUSH PRIVILEGES;
599 Query OK, 0 rows affected (0.00 sec)
607 Create the C<.sql> file and load the data:
613 Open the F<myapp01_mysql.sql> in your editor and enter:
616 -- Create a very simple database to hold book and author information
618 CREATE TABLE IF NOT EXISTS `books` (
619 `id` int(11) NOT NULL AUTO_INCREMENT,
620 `title` text CHARACTER SET utf8,
621 `rating` int(11) DEFAULT NULL,
623 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
624 -- 'book_authors' is a many-to-many join table between books & authors
625 CREATE TABLE IF NOT EXISTS `book_authors` (
626 `book_id` int(11) NOT NULL DEFAULT '0',
627 `author_id` int(11) NOT NULL DEFAULT '0',
628 PRIMARY KEY (`book_id`,`author_id`),
629 KEY `author_id` (`author_id`)
630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
631 CREATE TABLE IF NOT EXISTS `authors` (
632 `id` int(11) NOT NULL AUTO_INCREMENT,
633 `first_name` text CHARACTER SET utf8,
634 `last_name` text CHARACTER SET utf8,
636 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
638 --- Load some sample data
640 INSERT INTO `books` (`id`, `title`, `rating`) VALUES
641 (1, 'CCSP SNRS Exam Certification Guide', 5),
642 (2, 'TCP/IP Illustrated, Volume 1', 5),
643 (3, 'Internetworking with TCP/IP Vol.1', 4),
644 (4, 'Perl Cookbook', 5),
645 (5, 'Designing with Web Standards', 5);
647 INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES
657 INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES
658 (1, 'Greg', 'Bastien'),
659 (2, 'Sara', 'Nasseh'),
660 (3, 'Christian', 'Degu'),
661 (4, 'Richard', 'Stevens'),
662 (5, 'Douglas', 'Comer'),
663 (6, 'Tom', 'Christiansen'),
664 (7, 'Nathan', 'Torkington'),
665 (8, 'Jeffrey', 'Zeldman');
667 ALTER TABLE `book_authors`
668 ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
669 ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
675 mysql -u tutorial -p myapp < myapp01_mysql.sql
679 Make sure the data loaded correctly:
681 $ mysql -u tutorial -p myapp
682 Reading table information for completion of table and column names
683 You can turn off this feature to get a quicker startup with -A
685 Welcome to the MySQL monitor. Commands end with ; or \g.
687 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
697 3 rows in set (0.00 sec)
699 mysql> select * from books;
700 +----+------------------------------------+--------+
701 | id | title | rating |
702 +----+------------------------------------+--------+
703 | 1 | CCSP SNRS Exam Certification Guide | 5 |
704 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
705 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
706 | 4 | Perl Cookbook | 5 |
707 | 5 | Designing with Web Standards | 5 |
708 +----+------------------------------------+--------+
709 5 rows in set (0.00 sec)
723 Delete the existing model:
725 rm lib/MyApp/Model/MyAppDB.pm
729 Regenerate the model using the Catalyst "_create.pl" script:
731 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
732 dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
740 Chapter 5: Authentication
746 Create the C<.sql> file for the user/roles data:
748 Open F<myapp02_mysql.sql> in your editor and enter:
751 -- Add users and roles tables, along with a many-to-many join table
753 CREATE TABLE IF NOT EXISTS `roles` (
754 `id` int(11) NOT NULL,
755 `role` text CHARACTER SET utf8,
757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
758 CREATE TABLE IF NOT EXISTS `users` (
759 `id` int(11) NOT NULL,
760 `username` text CHARACTER SET utf8,
761 `password` text CHARACTER SET utf8,
762 `email_address` text CHARACTER SET utf8,
763 `first_name` text CHARACTER SET utf8,
764 `last_name` text CHARACTER SET utf8,
765 `active` int(11) DEFAULT NULL,
767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
768 CREATE TABLE IF NOT EXISTS `user_roles` (
769 `user_id` int(11) NOT NULL DEFAULT '0',
770 `role_id` int(11) NOT NULL DEFAULT '0',
771 PRIMARY KEY (`user_id`,`role_id`),
772 KEY `role_id` (`role_id`)
773 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
775 -- Load up some initial test data
777 INSERT INTO `roles` (`id`, `role`) VALUES
781 INSERT INTO `users` (`id`, `username`, `password`, `email_address`, `first_name`, `last_name`, `active`) VALUES
782 (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1),
783 (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1),
784 (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
786 INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
792 ALTER TABLE `user_roles
793 ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
794 ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
798 Load the user/roles data:
800 mysql -u tutorial -p myapp < myapp02_mysql.sql
810 Regenerate the model using the Catalyst "_create.pl" script:
812 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
813 components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
819 Create the C<.sql> file for the hashed password data:
821 Open F<myapp03_mysql.sql> in your editor and enter:
824 -- Convert passwords to SHA-1 hashes
826 UPDATE users SET password = '{SSHA}esgz64CpHMo8pMfgIIszP13ft23z/zio04aCwNdm0wc6MDeloMUH4g==' WHERE id = 1;
827 UPDATE users SET password = '{SSHA}FpGhpCJus+Ea9ne4ww8404HH+hJKW/fW+bAv1v6FuRUy2G7I2aoTRQ==' WHERE id = 2;
828 UPDATE users SET password = '{SSHA}ZyGlpiHls8qFBSbHr3r5t/iqcZE602XLMbkSVRRNl6rF8imv1abQVg==' WHERE id = 3;
832 Load the user/roles data:
834 mysql -u tutorial -p myapp < myapp03_mysql.sql
843 Kennedy Clark, C<hkclark@gmail.com>
845 Feel free to contact the author for any errors or suggestions, but the
846 best way to report issues is via the CPAN RT Bug system at
847 L<https://rt.cpan.org/Public/Dist/Display.html?Name=Catalyst-Manual>.
849 Copyright 2006-2011, Kennedy Clark, under the
850 Creative Commons Attribution Share-Alike License Version 3.0
851 (L<http://creativecommons.org/licenses/by-sa/3.0/us/>).