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-E<lt>> and C<C-E<gt>> 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 5, 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 C</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 "E<lt>catalystE<gt>" 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 C<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,EncodedColumn \
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,EncodedColumn \
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 C<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 C<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 C<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 B<NOTE:> This section is out of date with the rest of the tutorial.
517 Consider using SQLite or PostgreSQL since they are current.
519 Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
526 Chapter 3: Catalyst Basics
532 Install the required software:
538 The MySQL database server and client utility.
542 The Perl C<DBD::MySQL> module
546 For CentOS users (see
547 L<Catalyst::Manual::Installation::CentOS4|Catalyst::Manual::Installation::CentOS4>),
548 you can use the following commands to install the software and start the MySQL
551 yum -y install mysql mysql-server
556 Create the database and set the permissions:
559 Welcome to the MySQL monitor. Commands end with ; or \g.
560 Your MySQL connection id is 2 to server version: 4.1.20
562 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
564 mysql> create database myapp;
565 Query OK, 1 row affected (0.01 sec)
567 mysql> grant all on myapp.* to tutorial@'localhost';
568 Query OK, 0 rows affected (0.00 sec)
570 mysql> flush privileges;
571 Query OK, 0 rows affected (0.00 sec)
578 Create the C<.sql> file and load the data:
584 Open the C<myapp01_mysql.sql> in your editor and enter:
587 -- Create a very simple database to hold book and author information
589 DROP TABLE IF EXISTS books;
590 DROP TABLE IF EXISTS book_authors;
591 DROP TABLE IF EXISTS authors;
593 id INT(11) PRIMARY KEY AUTO_INCREMENT,
597 -- 'book_authors' is a many-to-many join table between books & authors
598 CREATE TABLE book_authors (
601 PRIMARY KEY (book_id, author_id)
603 CREATE TABLE authors (
604 id INT(11) PRIMARY KEY AUTO_INCREMENT,
609 --- Load some sample data
611 INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5);
612 INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5);
613 INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4);
614 INSERT INTO books VALUES (4, 'Perl Cookbook', 5);
615 INSERT INTO books VALUES (5, 'Designing with Web Standards', 5);
616 INSERT INTO authors VALUES (1, 'Greg', 'Bastien');
617 INSERT INTO authors VALUES (2, 'Sara', 'Nasseh');
618 INSERT INTO authors VALUES (3, 'Christian', 'Degu');
619 INSERT INTO authors VALUES (4, 'Richard', 'Stevens');
620 INSERT INTO authors VALUES (5, 'Douglas', 'Comer');
621 INSERT INTO authors VALUES (6, 'Tom', 'Christiansen');
622 INSERT INTO authors VALUES (7, ' Nathan', 'Torkington');
623 INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman');
624 INSERT INTO book_authors VALUES (1, 1);
625 INSERT INTO book_authors VALUES (1, 2);
626 INSERT INTO book_authors VALUES (1, 3);
627 INSERT INTO book_authors VALUES (2, 4);
628 INSERT INTO book_authors VALUES (3, 5);
629 INSERT INTO book_authors VALUES (4, 6);
630 INSERT INTO book_authors VALUES (4, 7);
631 INSERT INTO book_authors VALUES (5, 8);
637 mysql -ututorial myapp < myapp01_mysql.sql
641 Make sure the data loaded correctly:
643 $ mysql -ututorial myapp
644 Reading table information for completion of table and column names
645 You can turn off this feature to get a quicker startup with -A
647 Welcome to the MySQL monitor. Commands end with ; or \g.
648 Your MySQL connection id is 4 to server version: 4.1.20
650 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
660 3 rows in set (0.00 sec)
662 mysql> select * from books;
663 +----+------------------------------------+--------+
664 | id | title | rating |
665 +----+------------------------------------+--------+
666 | 1 | CCSP SNRS Exam Certification Guide | 5 |
667 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
668 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
669 | 4 | Perl Cookbook | 5 |
670 | 5 | Designing with Web Standards | 5 |
671 +----+------------------------------------+--------+
672 5 rows in set (0.00 sec)
686 Delete the existing model:
688 rm lib/MyApp/Model/MyAppDB.pm
692 Regenerate the model using the Catalyst "_create.pl" script:
694 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
695 dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }'
703 Chapter 5: Authentication
709 Create the C<.sql> file for the user/roles data:
711 Open C<myapp02_mysql.sql> in your editor and enter:
714 -- Add users and roles tables, along with a many-to-many join table
717 id INT(11) PRIMARY KEY,
726 id INTEGER PRIMARY KEY,
729 CREATE TABLE user_roles (
732 PRIMARY KEY (user_id, role_id)
735 -- Load up some initial test data
737 INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
738 INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
739 INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
740 INSERT INTO roles VALUES (1, 'user');
741 INSERT INTO roles VALUES (2, 'admin');
742 INSERT INTO user_roles VALUES (1, 1);
743 INSERT INTO user_roles VALUES (1, 2);
744 INSERT INTO user_roles VALUES (2, 1);
745 INSERT INTO user_roles VALUES (3, 1);
749 Load the user/roles data:
751 mysql -ututorial myapp < myapp02_mysql.sql
755 Create the C<.sql> file for the hashed password data:
757 Open C<myapp03_mysql.sql> in your editor and enter:
760 -- Convert passwords to SHA-1 hashes
762 UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1;
763 UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2;
764 UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3;
768 Load the user/roles data:
770 mysql -ututorial myapp < myapp03_mysql.sql
779 Kennedy Clark, C<hkclark@gmail.com>
781 Please report any errors, issues or suggestions to the author. The
782 most recent version of the Catalyst Tutorial can be found at
783 L<http://dev.catalyst.perl.org/repos/Catalyst/Catalyst-Manual/5.80/trunk/lib/Catalyst/Manual/Tutorial/>.
785 Copyright 2006-2008, Kennedy Clark, under Creative Commons License
786 (L<http://creativecommons.org/licenses/by-sa/3.0/us/>).