3 Catalyst::Manual::Tutorial::10_Appendices - Catalyst Tutorial - Chapter 10: Appendices
7 This is B<Chapter 10 of 10> for the Catalyst tutorial.
9 L<Tutorial Overview|Catalyst::Manual::Tutorial>
15 L<Introduction|Catalyst::Manual::Tutorial::01_Intro>
19 L<Catalyst Basics|Catalyst::Manual::Tutorial::02_CatalystBasics>
23 L<More Catalyst Basics|Catalyst::Manual::Tutorial::03_MoreCatalystBasics>
27 L<Basic CRUD|Catalyst::Manual::Tutorial::04_BasicCRUD>
31 L<Authentication|Catalyst::Manual::Tutorial::05_Authentication>
35 L<Authorization|Catalyst::Manual::Tutorial::06_Authorization>
39 L<Debugging|Catalyst::Manual::Tutorial::07_Debugging>
43 L<Testing|Catalyst::Manual::Tutorial::08_Testing>
47 L<Advanced CRUD|Catalyst::Manual::Tutorial::09_AdvancedCRUD>
58 This chapter of the tutorial provides supporting information relevant to
59 the Catalyst tutorial.
62 =head1 APPENDIX 1: CUT AND PASTE FOR POD-BASED EXAMPLES
64 You may notice that Pod indents example code with four spaces. This
65 section provides some quick advice to "un-indent" this text in common
68 =head2 "Un-indenting" with Vi/Vim
70 When cutting and pasting multi-line text from Pod-based documents, the
71 following vi/vim regexs can be helpful to "un-indent" the inserted text
72 (do NOT type the quotes, they are only included to show spaces in the
73 regex patterns). I<Note that all 3 of the regexs end in 4 spaces>:
81 Removes four leading spaces from the entire file (from the first line,
82 C<0>, to the last line, C<$>).
88 A shortcut for the previous item (C<%> specifies the entire file; so
89 this removes four leading spaces from every line).
95 Removes the first four spaces from the line the cursor is on at the time
96 the regex command is executed (".") to the last line of the file.
102 Removes four leading space from the current line through line 44
103 (obviously adjust the C<44> to the appropriate value in your example).
107 =head2 "Un-indenting" with Emacs
109 Although the author has not used Emacs for many years (apologies to
110 the Emacs fans out there), here is a quick hint to get you started. To
111 replace the leading spaces of every line in a file, use:
113 M-x replace-regexp<RET>
114 Replace regexp: ^ <RET>
117 All of that will occur on the single line at the bottom of your screen.
118 Note that "<RET>" represents the return key/enter. Also, there are
119 four spaces after the "^" on the "Replace regexp:" line and no spaces
120 entered on the last line.
122 You can limit the replacement operation by selecting text first (depending
123 on your version of Emacs, you can either use the mouse or experiment with
124 commands such as C<C-SPC> to set the mark at the cursor location and
125 C<< C-< >> and C<< C-> >> to set the mark at the beginning and end of the
128 Also, Stefan Kangas sent in the following tip about an alternate
129 approach using the command C<indent-region> to redo the indentation
130 for the currently selected region (adhering to indent rules in the
131 current major mode). You can run the command by typing M-x
132 indent-region or pressing the default keybinding C-M-\ in cperl-mode.
133 Additional details can be found here:
135 L<https://www.gnu.org/software/emacs/manual/html_node/emacs/Indentation-Commands.html>
138 =head1 APPENDIX 2: USING POSTGRESQL AND MYSQL
140 The main database used in this tutorial is the very simple yet powerful
141 L<SQLite|https://www.sqlite.org>. This section provides information
142 that can be used to "convert" the tutorial to use
143 L<PostgreSQL|https://www.postgresql.org> and
144 L<MySQL|https://dev.mysql.com>. However, note that part of
145 the beauty of the MVC architecture is that very little database-specific
146 code is spread throughout the system (at least when MVC is "done
147 right"). Consequently, converting from one database to another is
148 relatively painless with most Catalyst applications. In general, you
149 just need to adapt the schema definition C<.sql> file you use to
150 initialize your database and adjust a few configuration parameters.
152 Also note that the purpose of the data definition statements for this
153 section are not designed to take maximum advantage of the various
154 features in each database for issues such as referential integrity and
155 field types/constraints.
160 Use the following steps to adapt the tutorial to PostgreSQL. Thanks
161 to Caelum (Rafael Kitover) for assistance with the most recent
162 updates, and Louis Moore, Marcello Romani and Tom Lanyon for help with
169 Chapter 3: More Catalyst Basics
175 Install the PostgreSQL server and client and DBD::Pg:
177 If you are following along in Debian 6, you can quickly install these
178 items via this command:
180 sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl
182 To configure the permissions, you can open
183 F</etc/postgresql/8.3/main/pg_hba.conf> and change this line (near the
186 # "local" is for Unix domain socket connections only
187 local all all ident sameuser
191 # "local" is for Unix domain socket connections only
194 And then restart PostgreSQL:
196 sudo /etc/init.d/postgresql-8.3 restart
201 Create the database and a user for the database (note that we are
202 using "<catalyst>" to represent the hidden password of
205 $ sudo -u postgres createuser -P catappuser
206 Enter password for new role: <catalyst>
207 Enter it again: <catalyst>
208 Shall the new role be a superuser? (y/n) n
209 Shall the new role be allowed to create databases? (y/n) n
210 Shall the new role be allowed to create more new roles? (y/n) n
212 $ sudo -u postgres createdb -O catappuser catappdb
217 Create the C<.sql> file and load the data:
223 Open the F<myapp01_psql.sql> in your editor and enter:
226 -- Drops just in case you are reloading
228 DROP TABLE IF EXISTS books CASCADE;
229 DROP TABLE IF EXISTS authors CASCADE;
230 DROP TABLE IF EXISTS book_authors CASCADE;
231 DROP TABLE IF EXISTS users CASCADE;
232 DROP TABLE IF EXISTS roles CASCADE;
233 DROP TABLE IF EXISTS user_roles CASCADE;
236 -- Create a very simple database to hold book and author information
239 id SERIAL PRIMARY KEY,
242 -- Manually add these later
243 -- created TIMESTAMP NOT NULL DEFAULT now(),
247 CREATE TABLE authors (
248 id SERIAL PRIMARY KEY,
253 -- 'book_authors' is a many-to-many join table between books & authors
254 CREATE TABLE book_authors (
255 book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE,
256 author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE,
257 PRIMARY KEY (book_id, author_id)
261 --- Load some sample data
263 INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5);
264 INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5);
265 INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4);
266 INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5);
267 INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5);
268 INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien');
269 INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh');
270 INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu');
271 INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens');
272 INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer');
273 INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen');
274 INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington');
275 INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman');
276 INSERT INTO book_authors VALUES (1, 1);
277 INSERT INTO book_authors VALUES (1, 2);
278 INSERT INTO book_authors VALUES (1, 3);
279 INSERT INTO book_authors VALUES (2, 4);
280 INSERT INTO book_authors VALUES (3, 5);
281 INSERT INTO book_authors VALUES (4, 6);
282 INSERT INTO book_authors VALUES (4, 7);
283 INSERT INTO book_authors VALUES (5, 8);
289 $ psql -U catappuser -W catappdb -f myapp01_psql.sql
290 Password for user catappuser:
291 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id"
292 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books"
294 psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
296 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id"
297 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
306 Make sure the data loaded correctly:
308 $ psql -U catappuser -W catappdb
309 Password for user catappuser: <catalyst>
310 Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
312 Type: \copyright for distribution terms
313 \h for help with SQL commands
314 \? for help with psql commands
315 \g or terminate with semicolon to execute query
320 Schema | Name | Type | Owner
321 --------+--------------+-------+------------
322 public | authors | table | catappuser
323 public | book_authors | table | catappuser
324 public | books | table | catappuser
327 catappdb=> select * from books;
329 ----+------------------------------------+--------
330 1 | CCSP SNRS Exam Certification Guide | 5
331 2 | TCP/IP Illustrated, Volume 1 | 5
332 3 | Internetworking with TCP/IP Vol.1 | 4
333 4 | Perl Cookbook | 5
334 5 | Designing with Web Standards | 5
343 After the steps where you:
347 create lib/MyAppDB.pm
349 create lib/MyAppDB/Book.pm
351 create lib/MyAppDB/Author.pm
353 create lib/MyAppDB/BookAuthor.pm
358 Generate the model using the Catalyst "_create.pl" script:
360 $ rm lib/MyApp/Model/DB.pm # Delete just in case already there
361 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
362 create=static components=TimeStamp,PassphraseColumn \
363 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
369 Chapter 4: Basic CRUD
371 Add Datetime Columns to Our Existing Books Table
373 $ psql -U catappuser -W catappdb
375 catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();
377 catappdb=> ALTER TABLE books ADD updated TIMESTAMP;
381 Re-generate the model using the Catalyst "_create.pl" script:
383 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
384 create=static components=TimeStamp,PassphraseColumn \
385 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
390 Chapter 5: Authentication
396 Create the C<.sql> file for the user/roles data:
398 Open F<myapp02_psql.sql> in your editor and enter:
401 -- Add users and roles tables, along with a many-to-many join table
405 id SERIAL PRIMARY KEY,
415 id SERIAL PRIMARY KEY,
419 CREATE TABLE user_roles (
420 user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
421 role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
422 PRIMARY KEY (user_id, role_id)
426 -- Load up some initial test data
428 INSERT INTO users (username, password, email_address, first_name, last_name, active)
429 VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
430 INSERT INTO users (username, password, email_address, first_name, last_name, active)
431 VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
432 INSERT INTO users (username, password, email_address, first_name, last_name, active)
433 VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
434 INSERT INTO roles (role) VALUES ('user');
435 INSERT INTO roles (role) VALUES ('admin');
436 INSERT INTO user_roles VALUES (1, 1);
437 INSERT INTO user_roles VALUES (1, 2);
438 INSERT INTO user_roles VALUES (2, 1);
439 INSERT INTO user_roles VALUES (3, 1);
445 $ psql -U catappuser -W catappdb -f myapp02_psql.sql
446 Password for user catappuser: <catalyst>
447 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
448 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
450 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
451 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
453 psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles"
467 $ psql -U catappuser -W catappdb -c "select * from users"
468 Password for user catappuser: <catalyst>
469 id | username | password | email_address | first_name | last_name | active
470 ----+----------+----------+---------------+------------+-----------+--------
471 1 | test01 | mypass | t01@na.com | Joe | Blow | 1
472 2 | test02 | mypass | t02@na.com | Jane | Doe | 1
473 3 | test03 | mypass | t03@na.com | No | Go | 0
479 Modify F<set_hashed_passwords.pl> to match the following (the only difference
480 is the C<connect> line):
489 my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst');
491 my @users = $schema->resultset('Users')->all;
493 foreach my $user (@users) {
494 $user->password('mypass');
498 Run the F<set_hashed_passwords.pl> as per the "normal" flow of the
501 $ perl -Ilib set_hashed_passwords.pl
503 You can verify that it worked with this command:
505 $ psql -U catappuser -W catappdb -c "select * from users"
515 Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
516 Howard for the help and Zsolt Zemancsik for the up to date fixes.
522 Chapter 3: Catalyst Basics
528 Install the required software:
534 The MySQL database server and client utility.
538 The Perl L<DBD::MySQL> module
542 For CentOS users (see
543 L<Catalyst::Manual::Installation::CentOS4>),
544 you can use the following commands to install the software and start the MySQL
547 yum -y install mysql mysql-server
550 For Debian users you can use the following commands to install the software and start the MySQL
553 apt-get install mysql-client mysql-server
554 /etc/init.d/mysql start
556 B<NOTE:> The tutorial is based on Foreign Keys in database which is supported by InnoDB.
557 Only MySQL 5.0 and above supports InnoDB storage Engine so you need to have InnoDB support
558 in you MySQL. You can simply figure out that your install supports it or not:
562 Welcome to the MySQL monitor. Commands end with ; or \g.
564 Type 'help;' or '\h' for help. Type '\c' to clear the current input
567 mysql> SHOW VARIABLES LIKE 'have_innodb';
568 +---------------+-------+
569 | Variable_name | Value |
570 +---------------+-------+
571 | have_innodb | YES |
572 +---------------+-------+
573 1 row in set (0.01 sec)
578 If the Value is "YES" you can use your setup (Debian based mysql supports it by default).
579 Else, you need to configure your my.cnf or start your MySQL daemon without --skip-innodb option.
583 Create the database and set the permissions:
587 Welcome to the MySQL monitor. Commands end with ; or \g.
589 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
591 mysql> CREATE DATABASE `myapp`;
592 Query OK, 1 row affected (0.01 sec)
594 mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword';
595 Query OK, 0 rows affected (0.00 sec)
597 mysql> FLUSH PRIVILEGES;
598 Query OK, 0 rows affected (0.00 sec)
606 Create the C<.sql> file and load the data:
612 Open the F<myapp01_mysql.sql> in your editor and enter:
615 -- Create a very simple database to hold book and author information
617 CREATE TABLE IF NOT EXISTS `books` (
618 `id` int(11) NOT NULL AUTO_INCREMENT,
619 `title` text CHARACTER SET utf8,
620 `rating` int(11) DEFAULT NULL,
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
623 -- 'book_authors' is a many-to-many join table between books & authors
624 CREATE TABLE IF NOT EXISTS `book_authors` (
625 `book_id` int(11) NOT NULL DEFAULT '0',
626 `author_id` int(11) NOT NULL DEFAULT '0',
627 PRIMARY KEY (`book_id`,`author_id`),
628 KEY `author_id` (`author_id`)
629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
630 CREATE TABLE IF NOT EXISTS `authors` (
631 `id` int(11) NOT NULL AUTO_INCREMENT,
632 `first_name` text CHARACTER SET utf8,
633 `last_name` text CHARACTER SET utf8,
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
637 --- Load some sample data
639 INSERT INTO `books` (`id`, `title`, `rating`) VALUES
640 (1, 'CCSP SNRS Exam Certification Guide', 5),
641 (2, 'TCP/IP Illustrated, Volume 1', 5),
642 (3, 'Internetworking with TCP/IP Vol.1', 4),
643 (4, 'Perl Cookbook', 5),
644 (5, 'Designing with Web Standards', 5);
646 INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES
656 INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES
657 (1, 'Greg', 'Bastien'),
658 (2, 'Sara', 'Nasseh'),
659 (3, 'Christian', 'Degu'),
660 (4, 'Richard', 'Stevens'),
661 (5, 'Douglas', 'Comer'),
662 (6, 'Tom', 'Christiansen'),
663 (7, 'Nathan', 'Torkington'),
664 (8, 'Jeffrey', 'Zeldman');
666 ALTER TABLE `book_authors`
667 ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
668 ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
674 mysql -u tutorial -p myapp < myapp01_mysql.sql
678 Make sure the data loaded correctly:
680 $ mysql -u tutorial -p myapp
681 Reading table information for completion of table and column names
682 You can turn off this feature to get a quicker startup with -A
684 Welcome to the MySQL monitor. Commands end with ; or \g.
686 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
696 3 rows in set (0.00 sec)
698 mysql> select * from books;
699 +----+------------------------------------+--------+
700 | id | title | rating |
701 +----+------------------------------------+--------+
702 | 1 | CCSP SNRS Exam Certification Guide | 5 |
703 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
704 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
705 | 4 | Perl Cookbook | 5 |
706 | 5 | Designing with Web Standards | 5 |
707 +----+------------------------------------+--------+
708 5 rows in set (0.00 sec)
722 Delete the existing model:
724 rm lib/MyApp/Model/MyAppDB.pm
728 Regenerate the model using the Catalyst "_create.pl" script:
730 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
731 dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
739 Chapter 5: Authentication
745 Create the C<.sql> file for the user/roles data:
747 Open F<myapp02_mysql.sql> in your editor and enter:
750 -- Add users and roles tables, along with a many-to-many join table
752 CREATE TABLE IF NOT EXISTS `roles` (
753 `id` int(11) NOT NULL,
754 `role` text CHARACTER SET utf8,
756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
757 CREATE TABLE IF NOT EXISTS `users` (
758 `id` int(11) NOT NULL,
759 `username` text CHARACTER SET utf8,
760 `password` text CHARACTER SET utf8,
761 `email_address` text CHARACTER SET utf8,
762 `first_name` text CHARACTER SET utf8,
763 `last_name` text CHARACTER SET utf8,
764 `active` int(11) DEFAULT NULL,
766 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
767 CREATE TABLE IF NOT EXISTS `user_roles` (
768 `user_id` int(11) NOT NULL DEFAULT '0',
769 `role_id` int(11) NOT NULL DEFAULT '0',
770 PRIMARY KEY (`user_id`,`role_id`),
771 KEY `role_id` (`role_id`)
772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
774 -- Load up some initial test data
776 INSERT INTO `roles` (`id`, `role`) VALUES
780 INSERT INTO `users` (`id`, `username`, `password`, `email_address`, `first_name`, `last_name`, `active`) VALUES
781 (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1),
782 (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1),
783 (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
785 INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
791 ALTER TABLE `user_roles
792 ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
793 ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
797 Load the user/roles data:
799 mysql -u tutorial -p myapp < myapp02_mysql.sql
809 Regenerate the model using the Catalyst "_create.pl" script:
811 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
812 components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
818 Create the C<.sql> file for the hashed password data:
820 Open F<myapp03_mysql.sql> in your editor and enter:
823 -- Convert passwords to SHA-1 hashes
825 UPDATE users SET password = '{SSHA}esgz64CpHMo8pMfgIIszP13ft23z/zio04aCwNdm0wc6MDeloMUH4g==' WHERE id = 1;
826 UPDATE users SET password = '{SSHA}FpGhpCJus+Ea9ne4ww8404HH+hJKW/fW+bAv1v6FuRUy2G7I2aoTRQ==' WHERE id = 2;
827 UPDATE users SET password = '{SSHA}ZyGlpiHls8qFBSbHr3r5t/iqcZE602XLMbkSVRRNl6rF8imv1abQVg==' WHERE id = 3;
831 Load the user/roles data:
833 mysql -u tutorial -p myapp < myapp03_mysql.sql
842 Kennedy Clark, C<hkclark@gmail.com>
844 Feel free to contact the author for any errors or suggestions, but the
845 best way to report issues is via the CPAN RT Bug system at
846 L<https://rt.cpan.org/Public/Dist/Display.html?Name=Catalyst-Manual>.
848 Copyright 2006-2011, Kennedy Clark, under the
849 Creative Commons Attribution Share-Alike License Version 3.0
850 (L<https://creativecommons.org/licenses/by-sa/3.0/us/>).