[catagits/Catalyst-Manual.git] / lib / Catalyst / Manual / Tutorial / Tutorial / Appendices.pod

=head1 NAME

Catalyst::Manual::Tutorial::Appendices - Catalyst Tutorial - Part 9: Appendices


=head1 OVERVIEW

This is B<Part 9 of 9> of the Catalyst tutorial.

L<Tutorial Overview|Catalyst::Manual::Tutorial>

=over 4

=item 1

L<Introduction|Catalyst::Manual::Tutorial::Intro>

=item 2

L<Catalyst Basics|Catalyst::Manual::Tutorial::CatalystBasics>

=item 3

L<Basic CRUD|Catalyst::Manual::Tutorial_BasicCRUD>

=item 4

L<Authentication|Catalyst::Manual::Tutorial::Authentication>

=item 5

L<Authorization|Catalyst::Manual::Tutorial::Authorization>

=item 6

L<Debugging|Catalyst::Manual::Tutorial::Debugging>

=item 7

L<Testing|Catalyst::Manual::Tutorial::Testing>

=item 8

L<AdvancedCRUD|Catalyst::Manual::Tutorial::AdvancedCRUD>

=item 9

B<Appendices>

=back


=head1 DESCRIPTION

This part of the tutorial provides supporting information relevant to
the Catalyst tutorial.


=head1 APPENDIX 1: CUT AND PASTE FOR POD-BASED EXAMPLES

You may notice that Pod indents example code with four spaces.  This
section provides some quick advice to "un-indent" this text in common
editors.

=head2 "Un-indenting" with Vi/Vim

When cutting and pasting multi-line text from Pod-based documents, the
following vi/vim regexs can be helpful to "un-indent" the inserted text
(do NOT type the quotes, they are only included to show spaces in the
regex patterns).  I<Note that all 3 of the regexs end in 4 spaces>:

=over 4

=item * 

":0,$s/^    "

Removes four leading spaces from the entire file (from the first line,
C<0>, to the last line, C<$>).

=item * 

"%s/^    "

A shortcut for the previous item (C<%> specifies the entire file; so
this removes four leading spaces from every line).

=item * 

":.,$s/^    "

Removes the first four spaces from the line the cursor is on at the time
the regex command is executed (".") to the last line of the file.

=item * 

":.,44s/^    "

Removes four leading space from the current line through line 44
(obviously adjust the C<44> to the appropriate value in your example).

=back

=head2 "Un-indenting" with Emacs

Although there author has not used emacs for many years (apologies to 
the emacs fans out there), here is a quick hint to get you started.  To
replace the leading spaces of every line in a file, use:

    M-x replace-regexp<RET>
    Replace regexp: ^    <RET>
    with: <RET>

All of that will occur on the single line at the bottom of your screen.
Note that "<RET>" represents the return key/enter.  Also, there are 
four spaces after the "^" on the "Replace regexp:" line and no spaces
entered on the last line.

You can limit the replacement operation by selecting text first (depending
on your version of emacs, you can either use the mouse or experiment with 
commands such as C<C-SPC> to set the mark at the cursor location and 
C<C-E<lt>> and C<C-E<gt>> to set the mark at the beginning and end of the
file respectively.


=head1 APPENDIX 2: USING MYSQL AND POSTGRESQL

The main database used in this tutorial is the very simple yet powerful
SQLite.  This section provides information that can be used to "convert"
the tutorial to use MySQL and PostgreSQL.  However, note that part of
the beauty of the MVC architecture is that very little database-specific
code is spread throughout the system (at least when MVC is "done
right").  Consequently, converting from one database to another is
relatively painless with most Catalyst applications.  In general, you
just need to adapt the schema definition C<.sql> file you use to
initialize your database and adjust a few configuration parameters.

Also note that the purpose of the data definition statements for this
section are not designed to take maximum advantage of the various
features in each database for issues such as referential integrity and
field types/constraints.

=head2 MySQL

Use the following steps to adapt the tutorial to MySQL.  Thanks to Jim 
Howard for the help.

=over 4

=item *

Part 2: Catalyst Basics

=over 4

=item *

Install the required software:

=over 4

=item *

The MySQL database server and client utility.

=item *

The Perl C<DBD::MySQL> module

=back

For CentOS users (see 
L<Catalyst::Manual::Installation::CentOS4|Catalyst::Manual::Installation::CentOS4>),
you can use the following commands to install the software and start the MySQL
daemon:

    yum -y install mysql mysql-server
    service mysqld start

=item *

Create the database and set the permissions:

    $ mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2 to server version: 4.1.20
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> create database myapp;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> grant all on myapp.* to tutorial@'localhost';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> quit
    Bye

=item *

Create the C<.sql> file and load the data:

=over 4

=item *

Open the C<myapp01_mysql.sql> in your editor and enter:

    --
    -- Create a very simple database to hold book and author information
    --
    DROP TABLE IF EXISTS books;
    DROP TABLE IF EXISTS book_authors;
    DROP TABLE IF EXISTS authors;
    CREATE TABLE books (
           id          INT(11) PRIMARY KEY AUTO_INCREMENT,
           title       TEXT ,
           rating      INT(11)
    );
    -- 'book_authors' is a many-to-many join table between books & authors
    CREATE TABLE book_authors (
           book_id     INT(11),
           author_id   INT(11),
           PRIMARY KEY (book_id, author_id)
    );
    CREATE TABLE authors (
           id          INT(11) PRIMARY KEY AUTO_INCREMENT,
           first_name  TEXT,
           last_name   TEXT
    );
    ---
    --- Load some sample data
    ---
    INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5);
    INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5);
    INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4);
    INSERT INTO books VALUES (4, 'Perl Cookbook', 5);
    INSERT INTO books VALUES (5, 'Designing with Web Standards', 5);
    INSERT INTO authors VALUES (1, 'Greg', 'Bastien');
    INSERT INTO authors VALUES (2, 'Sara', 'Nasseh');
    INSERT INTO authors VALUES (3, 'Christian', 'Degu');
    INSERT INTO authors VALUES (4, 'Richard', 'Stevens');
    INSERT INTO authors VALUES (5, 'Douglas', 'Comer');
    INSERT INTO authors VALUES (6, 'Tom', 'Christiansen');
    INSERT INTO authors VALUES (7, ' Nathan', 'Torkington');
    INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman');
    INSERT INTO book_authors VALUES (1, 1);
    INSERT INTO book_authors VALUES (1, 2);
    INSERT INTO book_authors VALUES (1, 3);
    INSERT INTO book_authors VALUES (2, 4);
    INSERT INTO book_authors VALUES (3, 5);
    INSERT INTO book_authors VALUES (4, 6);
    INSERT INTO book_authors VALUES (4, 7);
    INSERT INTO book_authors VALUES (5, 8);

=item *

Load the data:

    mysql -ututorial myapp < myapp01_mysql.sql

=item *

Make sure the data loaded correctly:

    $ mysql -ututorial myapp
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4 to server version: 4.1.20
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> show tables;
    +-----------------+
    | Tables_in_myapp |
    +-----------------+
    | authors         |
    | book_authors    |
    | books           |
    +-----------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from books;
    +----+------------------------------------+--------+
    | id | title                              | rating |
    +----+------------------------------------+--------+
    |  1 | CCSP SNRS Exam Certification Guide |      5 |
    |  2 | TCP/IP Illustrated, Volume 1       |      5 |
    |  3 | Internetworking with TCP/IP Vol.1  |      4 |
    |  4 | Perl Cookbook                      |      5 |
    |  5 | Designing with Web Standards       |      5 |
    +----+------------------------------------+--------+
    5 rows in set (0.00 sec)
    
    mysql>

=back

=item *

Update the model:

=over 4

=item *

Delete the existing model:

    rm lib/MyApp/Model/MyAppDB.pm

=item *

Regenerate the model using the Catalyst "_create.pl" script:

    script/myapp_create.pl model MyAppDB DBIC::Schema MyAppDB dbi:mysql:myapp 'tutorial' '' '{ AutoCommit => 1 }'

=back

=back

=item *

Part 4: Authentication

=over 4

=item *

Create the C<.sql> file for the user/roles data:

Open C<myapp02_mysql.sql> in your editor and enter:

    --
    -- Add users and roles tables, along with a many-to-many join table
    --
    CREATE TABLE users (
            id            INT(11) PRIMARY KEY,
            username      TEXT,
            password      TEXT,
            email_address TEXT,
            first_name    TEXT,
            last_name     TEXT,
            active        INT(11)
    );
    CREATE TABLE roles (
            id   INTEGER PRIMARY KEY,
            role TEXT
    );
    CREATE TABLE user_roles (
            user_id INT(11),
            role_id INT(11),
            PRIMARY KEY (user_id, role_id)
    );
    --
    -- Load up some initial test data
    --
    INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe',  'Blow', 1);
    INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe',  1);
    INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No',   'Go',   0);
    INSERT INTO roles VALUES (1, 'user');
    INSERT INTO roles VALUES (2, 'admin');
    INSERT INTO user_roles VALUES (1, 1);
    INSERT INTO user_roles VALUES (1, 2);
    INSERT INTO user_roles VALUES (2, 1);
    INSERT INTO user_roles VALUES (3, 1);

=item *

Load the user/roles data:

    mysql -ututorial myapp < myapp02_mysql.sql

=item *

Create the C<.sql> file for the hashed password data:

Open C<myapp03_mysql.sql> in your editor and enter:

    --
    -- Convert passwords to SHA-1 hashes
    --
    UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1;
    UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2;
    UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3;

=item *

Load the user/roles data:

    mysql -ututorial myapp < myapp03_mysql.sql

=back

=back

=head2 PostgreSQL

B<TODO> -- Please see the latest version of this document for possible updates:
L<http://dev.catalyst.perl.org/repos/Catalyst/trunk/Catalyst-Runtime/lib/Catalyst/Manual/Tutorial/Appendices.pod>


=head1 APPENDIX 3: IMPROVED HASHING SCRIPT

Here is an improved SHA-1 hashing script from Gavin Henry that does
not expose the passwords to "capture" on the command line.

    #!/usr/bin/perl -w
    #===============================================================================
    #
    #         FILE:  enc_pass.pl
    #
    #        USAGE:  ./enc_pass.pl
    #
    #  DESCRIPTION:  Encrypt a Password using SHA-1
    #
    #      OPTIONS:  ---
    # REQUIREMENTS:  ---
    #         BUGS:  ---
    #        NOTES:  ---
    #       AUTHOR:  Gavin Henry (GH), <ghenry@suretecsystems.com>
    #      COMPANY:  Suretec Systems Ltd.
    #      VERSION:  1.0
    #      CREATED:  26/06/2006
    #     REVISION:  ---
    #    COPYRIGHT:  http://search.cpan.org/dist/perl/pod/perlgpl.pod
    #===============================================================================
    
    use strict;
    use warnings;
    use Digest::SHA1;
    use Term::ReadKey;
    
    sub get_pass {
        ReadMode 'noecho';
        chomp( my $pw = ReadLine 0 );
        ReadMode 'normal';
        return $pw;
    }
    
    print "Enter the password to be encrypted: ";
    my $pass = get_pass();
    
    print "\nConfirm the password: ";
    my $verify = get_pass();
    
    if ( $pass eq $verify ) {
        my $sha1_enc = Digest::SHA1->new;
        $sha1_enc->add($pass);
    
        print "\nYour encrypted password is: "
          . $sha1_enc->hexdigest . "\n"
          . "Paste this into your SQL INSERT/COPY Data.\n";
    }
    else {
        print "\nPasswords do not match!\n";
    }


=head1 AUTHOR

Kennedy Clark, C<hkclark@gmail.com>

Please report any errors, issues or suggestions to the author.  The
most recent version of the Catalyst Tutorial can be found at
L<http://dev.catalyst.perl.org/repos/Catalyst/trunk/Catalyst-Runtime/lib/Catalyst/Manual/Tutorial/>.

Copyright 2006, Kennedy Clark, under Creative Commons License
(L<http://creativecommons.org/licenses/by-nc-sa/2.5/>).
Commit	Line	Data
d442cc9f	1	=head1 NAME
	2
	3	Catalyst::Manual::Tutorial::Appendices - Catalyst Tutorial - Part 9: Appendices
	4
	5
	6	=head1 OVERVIEW
	7
	8	This is B<Part 9 of 9> of the Catalyst tutorial.
	9
	10	L<Tutorial Overview\|Catalyst::Manual::Tutorial>
	11
	12	=over 4
	13
	14	=item 1
	15
	16	L<Introduction\|Catalyst::Manual::Tutorial::Intro>
	17
	18	=item 2
	19
	20	L<Catalyst Basics\|Catalyst::Manual::Tutorial::CatalystBasics>
	21
	22	=item 3
	23
	24	L<Basic CRUD\|Catalyst::Manual::Tutorial_BasicCRUD>
	25
	26	=item 4
	27
	28	L<Authentication\|Catalyst::Manual::Tutorial::Authentication>
	29
	30	=item 5
	31
	32	L<Authorization\|Catalyst::Manual::Tutorial::Authorization>
	33
	34	=item 6
	35
	36	L<Debugging\|Catalyst::Manual::Tutorial::Debugging>
	37
	38	=item 7
	39
	40	L<Testing\|Catalyst::Manual::Tutorial::Testing>
	41
	42	=item 8
	43
	44	L<AdvancedCRUD\|Catalyst::Manual::Tutorial::AdvancedCRUD>
	45
	46	=item 9
	47
	48	B<Appendices>
	49
	50	=back
	51
	52
	53	=head1 DESCRIPTION
	54
	55	This part of the tutorial provides supporting information relevant to
	56	the Catalyst tutorial.
	57
	58
	59	=head1 APPENDIX 1: CUT AND PASTE FOR POD-BASED EXAMPLES
	60
	61	You may notice that Pod indents example code with four spaces. This
	62	section provides some quick advice to "un-indent" this text in common
	63	editors.
	64
65	=head2 "Un-indenting" with Vi/Vim
66
67	When cutting and pasting multi-line text from Pod-based documents, the
68	following vi/vim regexs can be helpful to "un-indent" the inserted text
69	(do NOT type the quotes, they are only included to show spaces in the
70	regex patterns). I<Note that all 3 of the regexs end in 4 spaces>:
71
72	=over 4
73
74	=item *
75
76	":0,$s/^ "
77
78	Removes four leading spaces from the entire file (from the first line,
79	C<0>, to the last line, C<$>).
80
81	=item *
82
83	"%s/^ "
84
85	A shortcut for the previous item (C<%> specifies the entire file; so
86	this removes four leading spaces from every line).
87
88	=item *
89
90	":.,$s/^ "
91
92	Removes the first four spaces from the line the cursor is on at the time
93	the regex command is executed (".") to the last line of the file.
94
95	=item *
96
97	":.,44s/^ "
98
99	Removes four leading space from the current line through line 44
100	(obviously adjust the C<44> to the appropriate value in your example).
101
102	=back
103
104	=head2 "Un-indenting" with Emacs
105
106	Although there author has not used emacs for many years (apologies to
107	the emacs fans out there), here is a quick hint to get you started. To
108	replace the leading spaces of every line in a file, use:
109
110	M-x replace-regexp<RET>
111	Replace regexp: ^ <RET>
112	with: <RET>
113
114	All of that will occur on the single line at the bottom of your screen.
115	Note that "<RET>" represents the return key/enter. Also, there are
116	four spaces after the "^" on the "Replace regexp:" line and no spaces
117	entered on the last line.
118
119	You can limit the replacement operation by selecting text first (depending
120	on your version of emacs, you can either use the mouse or experiment with
121	commands such as C<C-SPC> to set the mark at the cursor location and
122	C<C-E<lt>> and C<C-E<gt>> to set the mark at the beginning and end of the
123	file respectively.
124
125
126	=head1 APPENDIX 2: USING MYSQL AND POSTGRESQL
127
128	The main database used in this tutorial is the very simple yet powerful
129	SQLite. This section provides information that can be used to "convert"
130	the tutorial to use MySQL and PostgreSQL. However, note that part of
131	the beauty of the MVC architecture is that very little database-specific
132	code is spread throughout the system (at least when MVC is "done
133	right"). Consequently, converting from one database to another is
134	relatively painless with most Catalyst applications. In general, you
135	just need to adapt the schema definition C<.sql> file you use to
136	initialize your database and adjust a few configuration parameters.
137
138	Also note that the purpose of the data definition statements for this
139	section are not designed to take maximum advantage of the various
140	features in each database for issues such as referential integrity and
141	field types/constraints.
142
143	=head2 MySQL
144
145	Use the following steps to adapt the tutorial to MySQL. Thanks to Jim
146	Howard for the help.
147
148	=over 4
149
150	=item *
151
152	Part 2: Catalyst Basics
153
154	=over 4
155
156	=item *
157
158	Install the required software:
159
160	=over 4
161
162	=item *
163
164	The MySQL database server and client utility.
165
166	=item *
167
168	The Perl C<DBD::MySQL> module
169
170	=back
171
172	For CentOS users (see
173	L<Catalyst::Manual::Installation::CentOS4\|Catalyst::Manual::Installation::CentOS4>),
174	you can use the following commands to install the software and start the MySQL
175	daemon:
176
177	yum -y install mysql mysql-server
178	service mysqld start
179
180	=item *
181
182	Create the database and set the permissions:
183
184	$ mysql
185	Welcome to the MySQL monitor. Commands end with ; or \g.
186	Your MySQL connection id is 2 to server version: 4.1.20
187
188	Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
189
190	mysql> create database myapp;
191	Query OK, 1 row affected (0.01 sec)
192
193	mysql> grant all on myapp.* to tutorial@'localhost';
194	Query OK, 0 rows affected (0.00 sec)
195
196	mysql> flush privileges;
197	Query OK, 0 rows affected (0.00 sec)
198
199	mysql> quit
200	Bye
201
202	=item *
203
204	Create the C<.sql> file and load the data:
205
206	=over 4
207
208	=item *
209
210	Open the C<myapp01_mysql.sql> in your editor and enter:
211
212	--
213	-- Create a very simple database to hold book and author information
214	--
215	DROP TABLE IF EXISTS books;
216	DROP TABLE IF EXISTS book_authors;
217	DROP TABLE IF EXISTS authors;
218	CREATE TABLE books (
219	id INT(11) PRIMARY KEY AUTO_INCREMENT,
220	title TEXT ,
221	rating INT(11)
222	);
223	-- 'book_authors' is a many-to-many join table between books & authors
224	CREATE TABLE book_authors (
225	book_id INT(11),
226	author_id INT(11),
227	PRIMARY KEY (book_id, author_id)
228	);
229	CREATE TABLE authors (
230	id INT(11) PRIMARY KEY AUTO_INCREMENT,
231	first_name TEXT,
232	last_name TEXT
233	);
234	---
235	--- Load some sample data
236	---
237	INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5);
238	INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5);
239	INSERT INTO books VALUES (3, 'Internetworking with TCP/IP Vol.1', 4);
240	INSERT INTO books VALUES (4, 'Perl Cookbook', 5);
241	INSERT INTO books VALUES (5, 'Designing with Web Standards', 5);
242	INSERT INTO authors VALUES (1, 'Greg', 'Bastien');
243	INSERT INTO authors VALUES (2, 'Sara', 'Nasseh');
244	INSERT INTO authors VALUES (3, 'Christian', 'Degu');
245	INSERT INTO authors VALUES (4, 'Richard', 'Stevens');
246	INSERT INTO authors VALUES (5, 'Douglas', 'Comer');
247	INSERT INTO authors VALUES (6, 'Tom', 'Christiansen');
248	INSERT INTO authors VALUES (7, ' Nathan', 'Torkington');
249	INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman');
250	INSERT INTO book_authors VALUES (1, 1);
251	INSERT INTO book_authors VALUES (1, 2);
252	INSERT INTO book_authors VALUES (1, 3);
253	INSERT INTO book_authors VALUES (2, 4);
254	INSERT INTO book_authors VALUES (3, 5);
255	INSERT INTO book_authors VALUES (4, 6);
256	INSERT INTO book_authors VALUES (4, 7);
257	INSERT INTO book_authors VALUES (5, 8);
258
259	=item *
260
261	Load the data:
262
263	mysql -ututorial myapp < myapp01_mysql.sql
264
265	=item *
266
267	Make sure the data loaded correctly:
268
269	$ mysql -ututorial myapp
270	Reading table information for completion of table and column names
271	You can turn off this feature to get a quicker startup with -A
272
273	Welcome to the MySQL monitor. Commands end with ; or \g.
274	Your MySQL connection id is 4 to server version: 4.1.20
275
276	Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
277
278	mysql> show tables;
279	+-----------------+
280	\| Tables_in_myapp \|
281	+-----------------+
282	\| authors \|
283	\| book_authors \|
284	\| books \|
285	+-----------------+
286	3 rows in set (0.00 sec)
287
288	mysql> select * from books;
289	+----+------------------------------------+--------+
290	\| id \| title \| rating \|
291	+----+------------------------------------+--------+
292	\| 1 \| CCSP SNRS Exam Certification Guide \| 5 \|
293	\| 2 \| TCP/IP Illustrated, Volume 1 \| 5 \|
294	\| 3 \| Internetworking with TCP/IP Vol.1 \| 4 \|
295	\| 4 \| Perl Cookbook \| 5 \|
296	\| 5 \| Designing with Web Standards \| 5 \|
297	+----+------------------------------------+--------+
298	5 rows in set (0.00 sec)
299
300	mysql>
301
302	=back
303
304	=item *
305
306	Update the model:
307
308	=over 4
309
310	=item *
311
312	Delete the existing model:
313
314	rm lib/MyApp/Model/MyAppDB.pm
315
316	=item *
317
318	Regenerate the model using the Catalyst "_create.pl" script:
319
320	script/myapp_create.pl model MyAppDB DBIC::Schema MyAppDB dbi:mysql:myapp 'tutorial' '' '{ AutoCommit => 1 }'
321
322	=back
323
324	=back
325
326	=item *
327
328	Part 4: Authentication
329
330	=over 4
331
332	=item *
333
334	Create the C<.sql> file for the user/roles data:
335
336	Open C<myapp02_mysql.sql> in your editor and enter:
337
338	--
339	-- Add users and roles tables, along with a many-to-many join table
340	--
341	CREATE TABLE users (
342	id INT(11) PRIMARY KEY,
343	username TEXT,
344	password TEXT,
345	email_address TEXT,
346	first_name TEXT,
347	last_name TEXT,
348	active INT(11)
349	);
350	CREATE TABLE roles (
351	id INTEGER PRIMARY KEY,
352	role TEXT
353	);
354	CREATE TABLE user_roles (
355	user_id INT(11),
356	role_id INT(11),
357	PRIMARY KEY (user_id, role_id)
358	);
359	--
360	-- Load up some initial test data
361	--
362	INSERT INTO users VALUES (1, 'test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1);
363	INSERT INTO users VALUES (2, 'test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1);
364	INSERT INTO users VALUES (3, 'test03', 'mypass', 't03@na.com', 'No', 'Go', 0);
365	INSERT INTO roles VALUES (1, 'user');
366	INSERT INTO roles VALUES (2, 'admin');
367	INSERT INTO user_roles VALUES (1, 1);
368	INSERT INTO user_roles VALUES (1, 2);
369	INSERT INTO user_roles VALUES (2, 1);
370	INSERT INTO user_roles VALUES (3, 1);
371
372	=item *
373
374	Load the user/roles data:
375
376	mysql -ututorial myapp < myapp02_mysql.sql
377
378	=item *
379
380	Create the C<.sql> file for the hashed password data:
381
382	Open C<myapp03_mysql.sql> in your editor and enter:
383
384	--
385	-- Convert passwords to SHA-1 hashes
386	--
387	UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 1;
388	UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 2;
389	UPDATE users SET password = 'e727d1464ae12436e899a726da5b2f11d8381b26' WHERE id = 3;
390
391	=item *
392
393	Load the user/roles data:
394
395	mysql -ututorial myapp < myapp03_mysql.sql
396
397	=back
398
399	=back
400
401	=head2 PostgreSQL
402
403	B<TODO> -- Please see the latest version of this document for possible updates:
404	L<http://dev.catalyst.perl.org/repos/Catalyst/trunk/Catalyst-Runtime/lib/Catalyst/Manual/Tutorial/Appendices.pod>
405
406
407	=head1 APPENDIX 3: IMPROVED HASHING SCRIPT
408
409	Here is an improved SHA-1 hashing script from Gavin Henry that does
410	not expose the passwords to "capture" on the command line.
411
412	#!/usr/bin/perl -w
413	#===============================================================================
414	#
415	# FILE: enc_pass.pl
416	#
417	# USAGE: ./enc_pass.pl
418	#
419	# DESCRIPTION: Encrypt a Password using SHA-1
420	#
421	# OPTIONS: ---
422	# REQUIREMENTS: ---
423	# BUGS: ---
424	# NOTES: ---
425	# AUTHOR: Gavin Henry (GH), <ghenry@suretecsystems.com>
426	# COMPANY: Suretec Systems Ltd.
427	# VERSION: 1.0
428	# CREATED: 26/06/2006
429	# REVISION: ---
430	# COPYRIGHT: http://search.cpan.org/dist/perl/pod/perlgpl.pod
431	#===============================================================================
432
433	use strict;
434	use warnings;
435	use Digest::SHA1;
436	use Term::ReadKey;
437
438	sub get_pass {
439	ReadMode 'noecho';
440	chomp( my $pw = ReadLine 0 );
441	ReadMode 'normal';
442	return $pw;
443	}
444
445	print "Enter the password to be encrypted: ";
446	my $pass = get_pass();
447
448	print "\nConfirm the password: ";
449	my $verify = get_pass();
450
451	if ( $pass eq $verify ) {
452	my $sha1_enc = Digest::SHA1->new;
453	$sha1_enc->add($pass);
454
455	print "\nYour encrypted password is: "
456	. $sha1_enc->hexdigest . "\n"
457	. "Paste this into your SQL INSERT/COPY Data.\n";
458	}
459	else {
460	print "\nPasswords do not match!\n";
461	}
462
463
464	=head1 AUTHOR
465
466	Kennedy Clark, C<hkclark@gmail.com>
467
468	Please report any errors, issues or suggestions to the author. The
469	most recent version of the Catalyst Tutorial can be found at
470	L<http://dev.catalyst.perl.org/repos/Catalyst/trunk/Catalyst-Runtime/lib/Catalyst/Manual/Tutorial/>.
471
472	Copyright 2006, Kennedy Clark, under Creative Commons License
473	(L<http://creativecommons.org/licenses/by-nc-sa/2.5/>).