1Catalyst::Manual::TutorUisaelr::C1o0n_tArpipbeuCntadetidaclePyses(rt3l:):DMoacnuumaeln:t:aTtuitoonrial::10_Appendices(3)
2
3
4

NAME

6       Catalyst::Manual::Tutorial::10_Appendices - Catalyst Tutorial - Chapter
7       10: Appendices
8

OVERVIEW

10       This is Chapter 10 of 10 for the Catalyst tutorial.
11
12       Tutorial Overview
13
14       1.  Introduction
15
16       2.  Catalyst Basics
17
18       3.  More Catalyst Basics
19
20       4.  Basic CRUD
21
22       5.  Authentication
23
24       6.  Authorization
25
26       7.  Debugging
27
28       8.  Testing
29
30       9.  Advanced CRUD
31
32       10. 10_Appendices
33

DESCRIPTION

35       This chapter of the tutorial provides supporting information relevant
36       to the Catalyst tutorial.
37

APPENDIX 1: CUT AND PASTE FOR POD-BASED EXAMPLES

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
43   "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
49       ·   ":0,$s/^    "
50
51           Removes four leading spaces from the entire file (from the first
52           line, 0, to the last line, "$").
53
54       ·   "%s/^    "
55
56           A shortcut for the previous item ("%" specifies the entire file; so
57           this removes four leading spaces from every line).
58
59       ·   ":.,$s/^    "
60
61           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
65       ·   ":.,44s/^    "
66
67           Removes four leading space from the current line through line 44
68           (obviously adjust the 44 to the appropriate value in your example).
69
70   "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
75           M-x replace-regexp<RET>
76           Replace regexp: ^    <RET>
77           with: <RET>
78
79       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
84       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
90       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
97       <http://www.gnu.org/software/emacs/manual/html_node/emacs/Indentation-Commands.html>
98

APPENDIX 2: USING POSTGRESQL AND MYSQL

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
112       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
117   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
123       ·   Chapter 3: More Catalyst Basics
124
125           ·   Install the PostgreSQL server and client and DBD::Pg:
126
127               If you are following along in Debian 6, you can quickly install
128               these items via this command:
129
130                   sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl
131
132               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
136                   # "local" is for Unix domain socket connections only
137                   local   all         all                               ident sameuser
138
139               to:
140
141                   # "local" is for Unix domain socket connections only
142                   local   all         all                               trust
143
144               And then restart PostgreSQL:
145
146                   sudo /etc/init.d/postgresql-8.3 restart
147
148           ·   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
152                   $ 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
162           ·   Create the ".sql" file and load the data:
163
164               ·   Open the "myapp01_psql.sql" in your editor and enter:
165
166                       --
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
176                       --
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
188                       CREATE TABLE authors (
189                           id          SERIAL PRIMARY KEY,
190                           first_name  TEXT,
191                           last_name   TEXT
192                       );
193
194                       -- '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
201                       ---
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
226               ·   Load the data:
227
228                       $ 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
243               ·   Make sure the data loaded correctly:
244
245                       $ psql -U catappuser -W catappdb
246                       Password for user catappuser: <catalyst>
247                       Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
248
249                       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
255                       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
264                       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
274                       catappdb=>
275
276           ·   After the steps where you:
277
278                   edit lib/MyApp.pm
279
280                   create lib/MyAppDB.pm
281
282                   create lib/MyAppDB/Book.pm
283
284                   create lib/MyAppDB/Author.pm
285
286                   create lib/MyAppDB/BookAuthor.pm
287
288           ·   Generate the model using the Catalyst "_create.pl" script:
289
290                   $ 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
295       ·   Chapter 4: Basic CRUD
296
297           Add Datetime Columns to Our Existing Books Table
298
299               $ 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
307           Re-generate the model using the Catalyst "_create.pl" script:
308
309               $ 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
313       ·   Chapter 5: Authentication
314
315           ·   Create the ".sql" file for the user/roles data:
316
317               Open "myapp02_psql.sql" in your editor and enter:
318
319                   --
320                   -- Add users and roles tables, along with a many-to-many join table
321                   --
322
323                   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
333                   CREATE TABLE roles (
334                       id   SERIAL PRIMARY KEY,
335                       role TEXT
336                   );
337
338                   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
344                   --
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
360           ·   Load the data:
361
362                   $ 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
382               Confirm with:
383
384                   $ 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
393           ·   Modify "set_hashed_passwords.pl" to match the following (the
394               only difference is the "connect" line):
395
396                   #!/usr/bin/perl
397
398                   use strict;
399                   use warnings;
400
401                   use MyApp::Schema;
402
403                   my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst');
404
405                   my @users = $schema->resultset('Users')->all;
406
407                   foreach my $user (@users) {
408                       $user->password('mypass');
409                       $user->update;
410                   }
411
412               Run the "set_hashed_passwords.pl" as per the "normal" flow of
413               the tutorial:
414
415                   $ perl -Ilib set_hashed_passwords.pl
416
417               You can verify that it worked with this command:
418
419                   $ psql -U catappuser -W catappdb -c "select * from users"
420
421   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
425       ·   Chapter 3: Catalyst Basics
426
427           ·   Install the required software:
428
429               ·   The MySQL database server and client utility.
430
431               ·   The Perl "DBD::MySQL" module
432
433               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
437                   yum -y install mysql mysql-server
438                   service mysqld start
439
440               For Debian users you can use the following commands to install
441               the software and start the MySQL daemon:
442
443                   apt-get install mysql-client mysql-server
444                   /etc/init.d/mysql start
445
446               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
452                   # mysql -u root -p
453                   Enter password:
454                   Welcome to the MySQL monitor.  Commands end with ; or \g.
455
456                   Type 'help;' or '\h' for help. Type '\c' to clear the current input
457                   statement.
458
459                   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
467                   mysql> exit
468                   Bye
469
470               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
475           ·   Create the database and set the permissions:
476
477                   # mysql -u root -p
478                   Enter password:
479                   Welcome to the MySQL monitor.  Commands end with ; or \g.
480
481                   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
482
483                   mysql> CREATE DATABASE `myapp`;
484                   Query OK, 1 row affected (0.01 sec)
485
486                   mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword';
487                   Query OK, 0 rows affected (0.00 sec)
488
489                   mysql> FLUSH PRIVILEGES;
490                   Query OK, 0 rows affected (0.00 sec)
491
492                   mysql> exit
493                   Bye
494
495           ·   Create the ".sql" file and load the data:
496
497               ·   Open the "myapp01_mysql.sql" in your editor and enter:
498
499                       --
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
531                       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
541                       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
551                       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
555               ·   Load the data:
556
557                       mysql -u tutorial -p myapp < myapp01_mysql.sql
558
559               ·   Make sure the data loaded correctly:
560
561                       $ 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
565                       Welcome to the MySQL monitor.  Commands end with ; or \g.
566
567                       Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
568
569                       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
579                       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
591                       mysql>
592
593           ·   Update the model:
594
595               ·   Delete the existing model:
596
597                       rm lib/MyApp/Model/MyAppDB.pm
598
599               ·   Regenerate the model using the Catalyst "_create.pl"
600                   script:
601
602                       script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
603                           dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
604
605       ·   Chapter 5: Authentication
606
607           ·   Create the ".sql" file for the user/roles data:
608
609               Open "myapp02_mysql.sql" in your editor and enter:
610
611                   --
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
642                   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
647                   INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
648                   (1, 1),
649                   (2, 1),
650                   (3, 1),
651                   (1, 2);
652
653                   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
657           ·   Load the user/roles data:
658
659                   mysql -u tutorial -p myapp < myapp02_mysql.sql
660
661           ·   Update the model:
662
663               ·   Regenerate the model using the Catalyst "_create.pl"
664                   script:
665
666                       script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
667                           components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
668
669           ·   Create the ".sql" file for the hashed password data:
670
671               Open "myapp03_mysql.sql" in your editor and enter:
672
673                   --
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
680           ·   Load the user/roles data:
681
682                   mysql -u tutorial -p myapp < myapp03_mysql.sql
683

AUTHOR

685       Kennedy Clark, "hkclark@gmail.com"
686
687       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
691       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/>).
694
695
696
697perl v5.28.1                      2C0a1t3a-l0y9s-t1:6:Manual::Tutorial::10_Appendices(3)
Impressum