From: Kennedy Clark Date: Sun, 3 May 2009 19:28:55 +0000 (+0000) Subject: Completely revamp the PostgreSQL section in Appendices.pod and make sure it runs X-Git-Tag: v5.8005~157 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=catagits%2FCatalyst-Manual.git;a=commitdiff_plain;h=e179eee0346481c7fbf046ff616abbf424a72861 Completely revamp the PostgreSQL section in Appendices.pod and make sure it runs --- diff --git a/lib/Catalyst/Manual/Tutorial/Appendices.pod b/lib/Catalyst/Manual/Tutorial/Appendices.pod index fe5606e..c8eb582 100644 --- a/lib/Catalyst/Manual/Tutorial/Appendices.pod +++ b/lib/Catalyst/Manual/Tutorial/Appendices.pod @@ -127,11 +127,11 @@ C> and C> to set the mark at the beginning and end of the file respectively. -=head1 APPENDIX 2: USING MYSQL AND POSTGRESQL +=head1 APPENDIX 2: USING POSTGRESQL AND MYSQL 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 tutorial to use PostgreSQL and MySQL. 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 @@ -144,64 +144,63 @@ 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. +=head2 PostgreSQL + +Use the following steps to adapt the tutorial to PostgreSQL. Thanks +to Caelum (Rafael Kitover) for assistance with the most recent +updates, and Louis Moore, Marcello Romani and Tom Lanyon for help with +earlier versions. =over 4 =item * -Chapter 2: Catalyst Basics +Chapter 3: More Catalyst Basics =over 4 =item * -Install the required software: +Install the PostgreSQL server and client and DBD::Pg: -=over 4 +If you are following along in Debian 5, you can quickly install these +items via this command: -=item * + sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl -The MySQL database server and client utility. +To configure the permissions, you can open +C and change this line (near the +bottom): -=item * + # "local" is for Unix domain socket connections only + local all all ident sameuser -The Perl C module +to: -=back + # "local" is for Unix domain socket connections only + local all all trust -For CentOS users (see -L), -you can use the following commands to install the software and start the MySQL -daemon: +And then restart PostgreSQL: + + sudo /etc/init.d/postgresql-8.3 restart - yum -y install mysql mysql-server - service mysqld start =item * -Create the database and set the permissions: +Create the database and a user for the database (note that we are +using "EcatalystE" to represent the hidden password of +"catalyst"): - $ 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 + $ sudo -u postgres createuser -P catappuser + Enter password for new role: + Enter it again: + Shall the new role be a superuser? (y/n) n + Shall the new role be allowed to create databases? (y/n) n + Shall the new role be allowed to create more new roles? (y/n) n + CREATE ROLE + $ sudo -u postgres createdb -O catappuser catappdb + CREATE DATABASE =item * @@ -211,46 +210,59 @@ Create the C<.sql> file and load the data: =item * -Open the C in your editor and enter: +Open the C in your editor and enter: -- + -- Drops just in case you are reloading + --- + DROP TABLE IF EXISTS books CASCADE; + DROP TABLE IF EXISTS authors CASCADE; + DROP TABLE IF EXISTS book_authors CASCADE; + DROP TABLE IF EXISTS users CASCADE; + DROP TABLE IF EXISTS roles CASCADE; + DROP TABLE IF EXISTS user_roles CASCADE; + + -- -- 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) + id SERIAL PRIMARY KEY, + title TEXT , + rating INTEGER, + -- Manually add these later + -- created TIMESTAMP NOT NULL DEFAULT now(), + -- updated TIMESTAMP ); + + CREATE TABLE authors ( + id SERIAL PRIMARY KEY, + first_name TEXT, + last_name TEXT + ); + -- '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 + book_id INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, + author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE, + PRIMARY KEY (book_id, author_id) ); + --- --- 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 books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5); + INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5); + INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4); + INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5); + INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5); + INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien'); + INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh'); + INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu'); + INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens'); + INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer'); + INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen'); + INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington'); + INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman'); INSERT INTO book_authors VALUES (1, 1); INSERT INTO book_authors VALUES (1, 2); INSERT INTO book_authors VALUES (1, 3); @@ -264,73 +276,108 @@ Open the C in your editor and enter: Load the data: - mysql -ututorial myapp < myapp01_mysql.sql + $ psql -U catappuser -W catappdb -f myapp01_psql.sql + Password for user catappuser: + psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id" + psql:myapp01_psql.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books" + CREATE TABLE + psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors" + CREATE TABLE + psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id" + psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" + CREATE TABLE + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + ... =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 + $ psql -U catappuser -W catappdb + Password for user catappuser: + Welcome to psql 8.3.7, the PostgreSQL interactive terminal. - Type 'help;' or '\h' for help. Type '\c' to clear the buffer. + Type: \copyright for distribution terms + \h for help with SQL commands + \? for help with psql commands + \g or terminate with semicolon to execute query + \q to quit - mysql> show tables; - +-----------------+ - | Tables_in_myapp | - +-----------------+ - | authors | - | book_authors | - | books | - +-----------------+ - 3 rows in set (0.00 sec) + catappdb=> \dt + List of relations + Schema | Name | Type | Owner + --------+--------------+-------+------------ + public | authors | table | catappuser + public | book_authors | table | catappuser + public | books | table | catappuser + (3 rows) - 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) + catappdb=> 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) - mysql> + catappdb=> =back =item * -Update the model: +After the steps where you: + + edit lib/MyApp.pm + + create lib/MyAppDB.pm + + create lib/MyAppDB/Book.pm + + create lib/MyAppDB/Author.pm + + create lib/MyAppDB/BookAuthor.pm -=over 4 =item * -Delete the existing model: +Generate the model using the Catalyst "_create.pl" script: - rm lib/MyApp/Model/MyAppDB.pm + $ rm lib/MyApp/Model/DB.pm # Delete just in case already there + $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ + create=static components=TimeStamp,EncodedColumn \ + 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' + +=back =item * -Regenerate the model using the Catalyst "_create.pl" script: +Chapter 4: Basic CRUD - script/myapp_create.pl model MyAppDB DBIC::Schema MyApp::Schema \ - dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }' +Add Datetime Columns to Our Existing Books Table -=back + $ psql -U catappuser -W catappdb + ... + catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now(); + ALTER TABLE + catappdb=> ALTER TABLE books ADD updated TIMESTAMP; + ALTER TABLE + catappdb=> \q + +Re-generate the model using the Catalyst "_create.pl" script: + + $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ + create=static components=TimeStamp,EncodedColumn \ + 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' -=back =item * -Chapter 4: Authentication +Chapter 5: Authentication =over 4 @@ -338,37 +385,44 @@ Chapter 4: Authentication Create the C<.sql> file for the user/roles data: -Open C in your editor and enter: +Open C 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) + id SERIAL PRIMARY KEY, + username TEXT, + password TEXT, + email_address TEXT, + first_name TEXT, + last_name TEXT, + active INTEGER ); + CREATE TABLE roles ( - id INTEGER PRIMARY KEY, - role TEXT + id SERIAL PRIMARY KEY, + role TEXT ); + CREATE TABLE user_roles ( - user_id INT(11), - role_id INT(11), - PRIMARY KEY (user_id, role_id) + user_id INTEGER REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, + role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE, + 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 users (username, password, email_address, first_name, last_name, active) + VALUES ('test01', 'mypass', 't01@na.com', 'Joe', 'Blow', 1); + INSERT INTO users (username, password, email_address, first_name, last_name, active) + VALUES ('test02', 'mypass', 't02@na.com', 'Jane', 'Doe', 1); + INSERT INTO users (username, password, email_address, first_name, last_name, active) + VALUES ('test03', 'mypass', 't03@na.com', 'No', 'Go', 0); + INSERT INTO roles (role) VALUES ('user'); + INSERT INTO roles (role) VALUES ('admin'); INSERT INTO user_roles VALUES (1, 1); INSERT INTO user_roles VALUES (1, 2); INSERT INTO user_roles VALUES (2, 1); @@ -376,44 +430,89 @@ Open C in your editor and enter: =item * -Load the user/roles data: +Load the data: + + $ psql -U catappuser -W catappdb -f myapp02_psql.sql + Password for user catappuser: + psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id" + psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" + CREATE TABLE + psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id" + psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles" + CREATE TABLE + psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles" + CREATE TABLE + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + INSERT 0 1 + +Confirm with: + + $ psql -U catappuser -W catappdb -c "select * from users" + Password for user catappuser: + id | username | password | email_address | first_name | last_name | active + ----+----------+----------+---------------+------------+-----------+-------- + 1 | test01 | mypass | t01@na.com | Joe | Blow | 1 + 2 | test02 | mypass | t02@na.com | Jane | Doe | 1 + 3 | test03 | mypass | t03@na.com | No | Go | 0 + (3 rows) - mysql -ututorial myapp < myapp02_mysql.sql =item * -Create the C<.sql> file for the hashed password data: +Modify C to match the following (the only difference +is the C line): -Open C in your editor and enter: + #!/usr/bin/perl + + use strict; + use warnings; + + use MyApp::Schema; + + my $schema = MyApp::Schema->connect('dbi:Pg:dbname=catappdb', 'catappuser', 'catalyst'); + + my @users = $schema->resultset('Users')->all; + + foreach my $user (@users) { + $user->password('mypass'); + $user->update; + } - -- - -- 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; +Run the C as per the "normal" flow of the +tutorial: -=item * + $ perl -Ilib set_hashed_passwords.pl -Load the user/roles data: +You can verify that it worked with this command: + + $ psql -U catappuser -W catappdb -c "select * from users" - mysql -ututorial myapp < myapp03_mysql.sql =back =back -=head2 PostgreSQL -Use the following steps to adapt the tutorial to PostgreSQL. Thanks to -Louis Moore for the help who was in turn helped by Marcello Romani and -Tom Lanyon. +=head2 MySQL + +B This section is out of data with the rest of the tutorial. +Consider using SQLite or PostgreSQL since they are current. + +Use the following steps to adapt the tutorial to MySQL. Thanks to Jim +Howard for the help. =over 4 =item * -Chapter 2: Catalyst Basics +Chapter 3: Catalyst Basics =over 4 @@ -425,27 +524,43 @@ Install the required software: =item * -The PostgreSQL database server and client. +The MySQL database server and client utility. =item * -The Perl C module +The Perl C module =back +For CentOS users (see +L), +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 a user for the database +Create the database and set the permissions: - $ createuser -P catappuser - Enter password for new role: - Enter it again: - Shall the new role be a superuser? (y/n) n - Shall the new role be allowed to create databases? (y/n) n - Shall the new role be allowed to create more new roles? (y/n) n - CREATE ROLE - $ createdb -O catappuser catappdb - CREATE DATABASE + $ 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 * @@ -455,37 +570,29 @@ Create the C<.sql> file and load the data: =item * -Open the C in your editor and enter: - +Open the C in your editor and enter: -- -- Create a very simple database to hold book and author information -- - -- The sequence is how we get a unique id in PostgreSQL - -- - CREATE SEQUENCE books_seq START 5 ; - SELECT nextval ('books_seq'); - + DROP TABLE IF EXISTS books; + DROP TABLE IF EXISTS book_authors; + DROP TABLE IF EXISTS authors; CREATE TABLE books ( - id INTEGER PRIMARY KEY DEFAULT nextval('books_seq'), - title TEXT , - rating INTEGER - ); - + 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 INTEGER, - author_id INTEGER, - PRIMARY KEY (book_id, author_id) - ); - - CREATE SEQUENCE authors_seq START 8 ; - SELECT nextval ('authors_seq'); - + book_id INT(11), + author_id INT(11), + PRIMARY KEY (book_id, author_id) + ); CREATE TABLE authors ( - id INTEGER PRIMARY KEY DEFAULT nextval('authors_seq'), - first_name TEXT, - last_name TEXT + id INT(11) PRIMARY KEY AUTO_INCREMENT, + first_name TEXT, + last_name TEXT ); --- --- Load some sample data @@ -501,7 +608,7 @@ Open the C in your editor and enter: 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 (7, ' Nathan', 'Torkington'); INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman'); INSERT INTO book_authors VALUES (1, 1); INSERT INTO book_authors VALUES (1, 2); @@ -516,97 +623,73 @@ Open the C in your editor and enter: Load the data: - $ psql -U catappuser -W catappdb - Password for user catappuser: - Welcome to psql 8.1.8, the PostgreSQL interactive terminal. - - Type: \copyright for distribution terms - \h for help with SQL commands - \? for help with psql commands - \g or terminate with semicolon to execute query - \q to quit - - catappdb=> \i myapp01_psql.sql - - CREATE SEQUENCE - nextval - --------- - 5 - (1 row) - - psql:myapp01_psql.sql:11: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books" - CREATE TABLE - psql:myapp01_psql.sql:19: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table - "book_authors" - CREATE TABLE - CREATE SEQUENCE - nextval - --------- - 8 - (1 row) - - psql:myapp01_psql.sql:30: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" - CREATE TABLE - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - ... + mysql -ututorial myapp < myapp01_mysql.sql =item * Make sure the data loaded correctly: - catappdb=> \dt - List of relations - Schema | Name | Type | Owner - --------+--------------+-------+---------- - public | authors | table | catappuser - public | book_authors | table | catappuser - public | books | table | catappuser - (3 rows) + $ 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 - catappdb=> 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) + Welcome to the MySQL monitor. Commands end with ; or \g. + Your MySQL connection id is 4 to server version: 4.1.20 - catappdb=> \q + 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 * -After the steps where you: +Update the model: - edit lib/MyApp.pm - - create lib/MyAppDB.pm - - create lib/MyAppDB/Book.pm - - create lib/MyAppDB/Author.pm - - create lib/MyAppDB/BookAuthor.pm +=over 4 + +=item * + +Delete the existing model: + rm lib/MyApp/Model/MyAppDB.pm =item * -Generate the model using the Catalyst "_create.pl" script: +Regenerate the model using the Catalyst "_create.pl" script: - script/myapp_create.pl model MyAppDB DBIC::Schema MyAppDB 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' + script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ + dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }' +=back =back =item * -Chapter 4: Authentication +Chapter 5: Authentication =over 4 @@ -614,39 +697,29 @@ Chapter 4: Authentication Create the C<.sql> file for the user/roles data: -Open C in your editor and enter: +Open C in your editor and enter: -- -- Add users and roles tables, along with a many-to-many join table -- - - CREATE SEQUENCE users_seq START 3 ; - SELECT nextval ('users_seq'); - CREATE TABLE users ( - id INTEGER PRIMARY KEY DEFAULT nextval('users_seq'), + id INT(11) PRIMARY KEY, username TEXT, password TEXT, email_address TEXT, first_name TEXT, last_name TEXT, - active INTEGER + active INT(11) ); - - CREATE SEQUENCE roles_seq START 2 ; - SELECT nextval ('roles_seq'); - CREATE TABLE roles ( - id INTEGER PRIMARY KEY DEFAULT nextval('roles_seq'), + id INTEGER PRIMARY KEY, role TEXT ); - CREATE TABLE user_roles ( - user_id INTEGER, - role_id INTEGER, + user_id INT(11), + role_id INT(11), PRIMARY KEY (user_id, role_id) ); - -- -- Load up some initial test data -- @@ -662,63 +735,15 @@ Open C in your editor and enter: =item * -Load the data: - - $ psql -U catappuser -W catappdb - Password for user catappuser: catalyst - Welcome to psql 8.1.8, the PostgreSQL interactive terminal. - - Type: \copyright for distribution terms - \h for help with SQL commands - \? for help with psql commands - \g or terminate with semicolon to execute query - \q to quit - - catappdb=> \i myapp02_psql.sql - - CREATE SEQUENCE - nextval - --------- - 3 - (1 row) - - psql:myapp02_psql.sql:16: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" - CREATE TABLE - CREATE SEQUENCE - nextval - --------- - 2 - (1 row) - - psql:myapp02_psql.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles" - CREATE TABLE - psql:myapp02_psql.sql:30: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_roles_pkey" for table "user_roles" - CREATE TABLE - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - INSERT 0 1 - catappdb=> - - catappdb=> select * from users; - id | username | password | email_address | first_name | last_name | active - ----+----------+----------+---------------+------------+-----------+-------- - 1 | test01 | mypass | t01@na.com | Joe | Blow | 1 - 2 | test02 | mypass | t02@na.com | Jane | Doe | 1 - 3 | test03 | mypass | t03@na.com | No | Go | 0 - (3 rows) +Load the user/roles data: + mysql -ututorial myapp < myapp02_mysql.sql =item * Create the C<.sql> file for the hashed password data: -Open C in your editor and enter: +Open C in your editor and enter: -- -- Convert passwords to SHA-1 hashes @@ -729,87 +754,15 @@ Open C in your editor and enter: =item * -Load in the data - - $ psql -U catappuser -W catappdb - Password for user catappuser: - Welcome to psql 8.1.8, the PostgreSQL interactive terminal. - - Type: \copyright for distribution terms - \h for help with SQL commands - \? for help with psql commands - \g or terminate with semicolon to execute query - \q to quit - - catappdb=> \i myapp03_psql.sql - UPDATE 1 - UPDATE 1 - UPDATE 1 - +Load the user/roles data: + mysql -ututorial myapp < myapp03_mysql.sql =back =back -=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), - # 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