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
100 The main database used in this tutorial is the very simple yet powerful
101 SQLite <http://www.sqlite.org>. This section provides information that
102 can be used to "convert" the tutorial to use PostgreSQL
103 <http://www.postgresql.org> and MySQL <http://dev.mysql.com>. However,
104 note that part of the beauty of the MVC architecture is that very
105 little database-specific code is spread throughout the system (at least
106 when MVC is "done right"). Consequently, converting from one database
107 to another is relatively painless with most Catalyst applications. In
108 general, you just need to adapt the schema definition ".sql" file you
109 use to initialize your database and adjust a few configuration
110 parameters.
111 Also note that the purpose of the data definition statements for this
113 section are not designed to take maximum advantage of the various
114 features in each database for issues such as referential integrity and
115 field types/constraints.
116 PostgreSQL
118 Use the following steps to adapt the tutorial to PostgreSQL. Thanks to
119 Caelum (Rafael Kitover) for assistance with the most recent updates,
120 and Louis Moore, Marcello Romani and Tom Lanyon for help with earlier
121 versions.
122 · Chapter 3: More Catalyst Basics
124 · Install the PostgreSQL server and client and DBD::Pg:
126 If you are following along in Debian 6, you can quickly install
128 these items via this command:
129 sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl
131 To configure the permissions, you can open
133 "/etc/postgresql/8.3/main/pg_hba.conf" and change this line
134 (near the bottom):
135 # "local" is for Unix domain socket connections only
137 local all all ident sameuser
138 to:
140 # "local" is for Unix domain socket connections only
142 local all all trust
143 And then restart PostgreSQL:
145 sudo /etc/init.d/postgresql-8.3 restart
147 · Create the database and a user for the database (note that we
149 are using "<catalyst>" to represent the hidden password of
150 "catalyst"):
151 $ sudo -u postgres createuser -P catappuser
153 Enter password for new role: <catalyst>
154 Enter it again: <catalyst>
155 Shall the new role be a superuser? (y/n) n
156 Shall the new role be allowed to create databases? (y/n) n
157 Shall the new role be allowed to create more new roles? (y/n) n
158 CREATE ROLE
159 $ sudo -u postgres createdb -O catappuser catappdb
160 CREATE DATABASE
161 · Create the ".sql" file and load the data:
163 · Open the "myapp01_psql.sql" in your editor and enter:
165 --
167 -- Drops just in case you are reloading
168 ---
169 DROP TABLE IF EXISTS books CASCADE;
170 DROP TABLE IF EXISTS authors CASCADE;
171 DROP TABLE IF EXISTS book_authors CASCADE;
172 DROP TABLE IF EXISTS users CASCADE;
173 DROP TABLE IF EXISTS roles CASCADE;
174 DROP TABLE IF EXISTS user_roles CASCADE;
175 --
177 -- Create a very simple database to hold book and author information
178 --
179 CREATE TABLE books (
180 id SERIAL PRIMARY KEY,
181 title TEXT ,
182 rating INTEGER,
183 -- Manually add these later
184 -- created TIMESTAMP NOT NULL DEFAULT now(),
185 -- updated TIMESTAMP
186 );
187 CREATE TABLE authors (
189 id SERIAL PRIMARY KEY,
190 first_name TEXT,
191 last_name TEXT
192 );
193 -- 'book_authors' is a many-to-many join table between books & authors
195 CREATE TABLE book_authors (
196 book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE,
197 author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE,
198 PRIMARY KEY (book_id, author_id)
199 );
200 ---
202 --- Load some sample data
203 ---
204 INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5);
205 INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5);
206 INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4);
207 INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5);
208 INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5);
209 INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien');
210 INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh');
211 INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu');
212 INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens');
213 INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer');
214 INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen');
215 INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington');
216 INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman');
217 INSERT INTO book_authors VALUES (1, 1);
218 INSERT INTO book_authors VALUES (1, 2);
219 INSERT INTO book_authors VALUES (1, 3);
220 INSERT INTO book_authors VALUES (2, 4);
221 INSERT INTO book_authors VALUES (3, 5);
222 INSERT INTO book_authors VALUES (4, 6);
223 INSERT INTO book_authors VALUES (4, 7);
224 INSERT INTO book_authors VALUES (5, 8);
225 · Load the data:
227 $ psql -U catappuser -W catappdb -f myapp01_psql.sql
229 Password for user catappuser:
230 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id"
231 psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books"
232 CREATE TABLE
233 psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
234 CREATE TABLE
235 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id"
236 psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
237 CREATE TABLE
238 INSERT 0 1
239 INSERT 0 1
240 INSERT 0 1
241 ...
242 · Make sure the data loaded correctly:
244 $ psql -U catappuser -W catappdb
246 Password for user catappuser: <catalyst>
247 Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
248 Type: \copyright for distribution terms
250 \h for help with SQL commands
251 \? for help with psql commands
252 \g or terminate with semicolon to execute query
253 \q to quit
254 catappdb=> \dt
256 List of relations
257 Schema | Name | Type | Owner
258 --------+--------------+-------+------------
259 public | authors | table | catappuser
260 public | book_authors | table | catappuser
261 public | books | table | catappuser
262 (3 rows)
263 catappdb=> select * from books;
265 id | title | rating
266 ----+------------------------------------+--------
267 1 | CCSP SNRS Exam Certification Guide | 5
268 2 | TCP/IP Illustrated, Volume 1 | 5
269 3 | Internetworking with TCP/IP Vol.1 | 4
270 4 | Perl Cookbook | 5
271 5 | Designing with Web Standards | 5
272 (5 rows)
273 catappdb=>
275 · After the steps where you:
277 edit lib/MyApp.pm
279 create lib/MyAppDB.pm
281 create lib/MyAppDB/Book.pm
283 create lib/MyAppDB/Author.pm
285 create lib/MyAppDB/BookAuthor.pm
287 · Generate the model using the Catalyst "_create.pl" script:
289 $ rm lib/MyApp/Model/DB.pm # Delete just in case already there
291 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
292 create=static components=TimeStamp,PassphraseColumn \
293 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
294 · Chapter 4: Basic CRUD
296 Add Datetime Columns to Our Existing Books Table
298 $ psql -U catappuser -W catappdb
300 ...
301 catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();
302 ALTER TABLE
303 catappdb=> ALTER TABLE books ADD updated TIMESTAMP;
304 ALTER TABLE
305 catappdb=> \q
306 Re-generate the model using the Catalyst "_create.pl" script:
308 $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
310 create=static components=TimeStamp,PassphraseColumn \
311 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'
312 · Chapter 5: Authentication
314 · Create the ".sql" file for the user/roles data:
316 Open "myapp02_psql.sql" in your editor and enter:
318 --
320 -- Add users and roles tables, along with a many-to-many join table
321 --
322 CREATE TABLE users (
324 id SERIAL PRIMARY KEY,
325 username TEXT,
326 password TEXT,
327 email_address TEXT,
328 first_name TEXT,
329 last_name TEXT,
330 active INTEGER
331 );
332 CREATE TABLE roles (
334 id SERIAL PRIMARY KEY,
335 role TEXT
336 );
337 CREATE TABLE user_roles (
339 user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
340 role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
341 PRIMARY KEY (user_id, role_id)
342 );
343 --
345 -- Load up some initial test data
346 --
347 INSERT INTO users (username, password, email_address, first_name, last_name, active)
348 VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
349 INSERT INTO users (username, password, email_address, first_name, last_name, active)
350 VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
351 INSERT INTO users (username, password, email_address, first_name, last_name, active)
352 VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
353 INSERT INTO roles (role) VALUES ('user');
354 INSERT INTO roles (role) VALUES ('admin');
355 INSERT INTO user_roles VALUES (1, 1);
356 INSERT INTO user_roles VALUES (1, 2);
357 INSERT INTO user_roles VALUES (2, 1);
358 INSERT INTO user_roles VALUES (3, 1);
359 · Load the data:
361 $ psql -U catappuser -W catappdb -f myapp02_psql.sql
363 Password for user catappuser: <catalyst>
364 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
365 psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
366 CREATE TABLE
367 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
368 psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
369 CREATE TABLE
370 psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles"
371 CREATE TABLE
372 INSERT 0 1
373 INSERT 0 1
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 Confirm with:
383 $ psql -U catappuser -W catappdb -c "select * from users"
385 Password for user catappuser: <catalyst>
386 id | username | password | email_address | first_name | last_name | active
387 ----+----------+----------+---------------+------------+-----------+--------
388 1 | test01 | mypass | t01@na.com | Joe | Blow | 1
389 2 | test02 | mypass | t02@na.com | Jane | Doe | 1
390 3 | test03 | mypass | t03@na.com | No | Go | 0
391 (3 rows)
392 · Modify "set_hashed_passwords.pl" to match the following (the
394 only difference is the "connect" line):
395 #!/usr/bin/perl
397 use strict;
399 use warnings;
400 use MyApp::Schema;
402 my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst');
404 my @users = $schema->resultset('Users')->all;
406 foreach my $user (@users) {
408 $user->password('mypass');
409 $user->update;
410 }
411 Run the "set_hashed_passwords.pl" as per the "normal" flow of
413 the tutorial:
414 $ perl -Ilib set_hashed_passwords.pl
416 You can verify that it worked with this command:
418 $ psql -U catappuser -W catappdb -c "select * from users"
420 MySQL
422 Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
423 Howard for the help and Zsolt Zemancsik for the up to date fixes.
424 · Chapter 3: Catalyst Basics
426 · Install the required software:
428 · The MySQL database server and client utility.
430 · The Perl "DBD::MySQL" module
432 For CentOS users (see Catalyst::Manual::Installation::CentOS4),
434 you can use the following commands to install the software and
435 start the MySQL daemon:
436 yum -y install mysql mysql-server
438 service mysqld start
439 For Debian users you can use the following commands to install
441 the software and start the MySQL daemon:
442 apt-get install mysql-client mysql-server
444 /etc/init.d/mysql start
445 NOTE: The tutorial is based on Foreign Keys in database which
447 is supported by InnoDB. Only MySQL 5.0 and above supports
448 InnoDB storage Engine so you need to have InnoDB support in you
449 MySQL. You can simply figure out that your install supports it
450 or not:
451 # mysql -u root -p
453 Enter password:
454 Welcome to the MySQL monitor. Commands end with ; or \g.
455 Type 'help;' or '\h' for help. Type '\c' to clear the current input
457 statement.
458 mysql> SHOW VARIABLES LIKE 'have_innodb';
460 +---------------+-------+
461 | Variable_name | Value |
462 +---------------+-------+
463 | have_innodb | YES |
464 +---------------+-------+
465 1 row in set (0.01 sec)
466 mysql> exit
468 Bye
469 If the Value is "YES" you can use your setup (Debian based
471 mysql supports it by default). Else, you need to configure
472 your my.cnf or start your MySQL daemon without --skip-innodb
473 option.
474 · Create the database and set the permissions:
476 # mysql -u root -p
478 Enter password:
479 Welcome to the MySQL monitor. Commands end with ; or \g.
480 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
482 mysql> CREATE DATABASE `myapp`;
484 Query OK, 1 row affected (0.01 sec)
485 mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword';
487 Query OK, 0 rows affected (0.00 sec)
488 mysql> FLUSH PRIVILEGES;
490 Query OK, 0 rows affected (0.00 sec)
491 mysql> exit
493 Bye
494 · Create the ".sql" file and load the data:
496 · Open the "myapp01_mysql.sql" in your editor and enter:
498 --
500 -- Create a very simple database to hold book and author information
501 --
502 CREATE TABLE IF NOT EXISTS `books` (
503 `id` int(11) NOT NULL AUTO_INCREMENT,
504 `title` text CHARACTER SET utf8,
505 `rating` int(11) DEFAULT NULL,
506 PRIMARY KEY (`id`)
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
508 -- 'book_authors' is a many-to-many join table between books & authors
509 CREATE TABLE IF NOT EXISTS `book_authors` (
510 `book_id` int(11) NOT NULL DEFAULT '0',
511 `author_id` int(11) NOT NULL DEFAULT '0',
512 PRIMARY KEY (`book_id`,`author_id`),
513 KEY `author_id` (`author_id`)
514 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
515 CREATE TABLE IF NOT EXISTS `authors` (
516 `id` int(11) NOT NULL AUTO_INCREMENT,
517 `first_name` text CHARACTER SET utf8,
518 `last_name` text CHARACTER SET utf8,
519 PRIMARY KEY (`id`)
520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
521 ---
522 --- Load some sample data
523 ---
524 INSERT INTO `books` (`id`, `title`, `rating`) VALUES
525 (1, 'CCSP SNRS Exam Certification Guide', 5),
526 (2, 'TCP/IP Illustrated, Volume 1', 5),
527 (3, 'Internetworking with TCP/IP Vol.1', 4),
528 (4, 'Perl Cookbook', 5),
529 (5, 'Designing with Web Standards', 5);
530 INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES
532 (1, 1),
533 (1, 2),
534 (1, 3),
535 (2, 4),
536 (3, 5),
537 (4, 6),
538 (4, 7),
539 (5, 8);
540 INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES
542 (1, 'Greg', 'Bastien'),
543 (2, 'Sara', 'Nasseh'),
544 (3, 'Christian', 'Degu'),
545 (4, 'Richard', 'Stevens'),
546 (5, 'Douglas', 'Comer'),
547 (6, 'Tom', 'Christiansen'),
548 (7, 'Nathan', 'Torkington'),
549 (8, 'Jeffrey', 'Zeldman');
550 ALTER TABLE `book_authors`
552 ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
553 ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
554 · Load the data:
556 mysql -u tutorial -p myapp < myapp01_mysql.sql
558 · Make sure the data loaded correctly:
560 $ mysql -u tutorial -p myapp
562 Reading table information for completion of table and column names
563 You can turn off this feature to get a quicker startup with -A
564 Welcome to the MySQL monitor. Commands end with ; or \g.
566 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
568 mysql> show tables;
570 +-----------------+
571 | Tables_in_myapp |
572 +-----------------+
573 | authors |
574 | book_authors |
575 | books |
576 +-----------------+
577 3 rows in set (0.00 sec)
578 mysql> select * from books;
580 +----+------------------------------------+--------+
581 | id | title | rating |
582 +----+------------------------------------+--------+
583 | 1 | CCSP SNRS Exam Certification Guide | 5 |
584 | 2 | TCP/IP Illustrated, Volume 1 | 5 |
585 | 3 | Internetworking with TCP/IP Vol.1 | 4 |
586 | 4 | Perl Cookbook | 5 |
587 | 5 | Designing with Web Standards | 5 |
588 +----+------------------------------------+--------+
589 5 rows in set (0.00 sec)
590 mysql>
592 · Update the model:
594 · Delete the existing model:
596 rm lib/MyApp/Model/MyAppDB.pm
598 · Regenerate the model using the Catalyst "_create.pl"
600 script:
601 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
603 dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
604 · Chapter 5: Authentication
606 · Create the ".sql" file for the user/roles data:
608 Open "myapp02_mysql.sql" in your editor and enter:
610 --
612 -- Add users and roles tables, along with a many-to-many join table
613 --
614 CREATE TABLE IF NOT EXISTS `roles` (
615 `id` int(11) NOT NULL,
616 `role` text CHARACTER SET utf8,
617 PRIMARY KEY (`id`)
618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
619 CREATE TABLE IF NOT EXISTS `users` (
620 `id` int(11) NOT NULL,
621 `username` text CHARACTER SET utf8,
622 `password` text CHARACTER SET utf8,
623 `email_address` text CHARACTER SET utf8,
624 `first_name` text CHARACTER SET utf8,
625 `last_name` text CHARACTER SET utf8,
626 `active` int(11) DEFAULT NULL,
627 PRIMARY KEY (`id`)
628 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
629 CREATE TABLE IF NOT EXISTS `user_roles` (
630 `user_id` int(11) NOT NULL DEFAULT '0',
631 `role_id` int(11) NOT NULL DEFAULT '0',
632 PRIMARY KEY (`user_id`,`role_id`),
633 KEY `role_id` (`role_id`)
634 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
635 --
636 -- Load up some initial test data
637 --
638 INSERT INTO `roles` (`id`, `role`) VALUES
639 (1, 'user'),
640 (2, 'admin');
641 INSERT INTO `users` (`id`, `username`, `password`, `email_address`, `first_name`, `last_name`, `active`) VALUES
643 (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1),
644 (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1),
645 (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
646 INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
648 (1, 1),
649 (2, 1),
650 (3, 1),
651 (1, 2);
652 ALTER TABLE `user_roles
654 ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
655 ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
656 · Load the user/roles data:
658 mysql -u tutorial -p myapp < myapp02_mysql.sql
660 · Update the model:
662 · Regenerate the model using the Catalyst "_create.pl"
664 script:
665 script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
667 components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
668 · Create the ".sql" file for the hashed password data:
670 Open "myapp03_mysql.sql" in your editor and enter:
672 --
674 -- Convert passwords to SHA-1 hashes
675 --
676 UPDATE users SET password = '{SSHA}esgz64CpHMo8pMfgIIszP13ft23z/zio04aCwNdm0wc6MDeloMUH4g==' WHERE id = 1;
677 UPDATE users SET password = '{SSHA}FpGhpCJus+Ea9ne4ww8404HH+hJKW/fW+bAv1v6FuRUy2G7I2aoTRQ==' WHERE id = 2;
678 UPDATE users SET password = '{SSHA}ZyGlpiHls8qFBSbHr3r5t/iqcZE602XLMbkSVRRNl6rF8imv1abQVg==' WHERE id = 3;
679 · Load the user/roles data:
681 mysql -u tutorial -p myapp < myapp03_mysql.sql
683
685 Kennedy Clark, "hkclark@gmail.com"
686 Feel free to contact the author for any errors or suggestions, but the
688 best way to report issues is via the CPAN RT Bug system at
689 <https://rt.cpan.org/Public/Dist/Display.html?Name=Catalyst-Manual>.
690 Copyright 2006-2011, Kennedy Clark, under the Creative Commons
692 Attribution Share-Alike License Version 3.0
693 (<http://creativecommons.org/licenses/by-sa/3.0/us/>).
694perl v5.28.0 2C0a1t3a-l0y9s-t1:6:Manual::Tutorial::10_Appendices(3)