X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=catagits%2FCatalyst-Manual.git;a=blobdiff_plain;f=lib%2FCatalyst%2FManual%2FTutorial%2FAppendices.pod;h=cad622e6bfb29832e79c3892b908ce82982f5c05;hp=eca683080124890614f8fb0257c2808bda172dc9;hb=3533daff0314522f79dff9c618da087568f1378c;hpb=d712b8268991d1d97723bda87975411c4f74263d diff --git a/lib/Catalyst/Manual/Tutorial/Appendices.pod b/lib/Catalyst/Manual/Tutorial/Appendices.pod index eca6830..cad622e 100644 --- a/lib/Catalyst/Manual/Tutorial/Appendices.pod +++ b/lib/Catalyst/Manual/Tutorial/Appendices.pod @@ -1,11 +1,11 @@ =head1 NAME -Catalyst::Manual::Tutorial::Appendices - Catalyst Tutorial - Part 9: Appendices +Catalyst::Manual::Tutorial::Appendices - Catalyst Tutorial - Part 10: Appendices =head1 OVERVIEW -This is B of the Catalyst tutorial. +This is B for the Catalyst tutorial. L @@ -21,30 +21,34 @@ L =item 3 -L +L =item 4 -L +L =item 5 -L +L =item 6 -L +L =item 7 -L +L =item 8 -L +L =item 9 +L + +=item 10 + B =back @@ -400,8 +404,353 @@ Load the user/roles data: =head2 PostgreSQL -B -- Please see the latest version of this document for possible updates: -L +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. + +=over 4 + +=item * + +Part 2: Catalyst Basics + +=over 4 + +=item * + +Install the required software: + +=over 4 + +=item * + +The PostgreSQL database server and client. + +=item * + +The Perl C module + +=back + +=item * + +Create the database and a user for the database + + $ createuser -P catmyapp + 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 catmyapp mycatapp + CREATE DATABASE + +=item * + +Create the C<.sql> file and load the data: + +=over 4 + +=item * + +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'); + + CREATE TABLE books ( + id INTEGER PRIMARY KEY DEFAULT nextval('books_seq'), + title TEXT , + rating INTEGER + ); + + -- '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'); + + CREATE TABLE authors ( + id INTEGER PRIMARY KEY DEFAULT nextval('authors_seq'), + 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: + + $ psql -U catmyapp -W mycatapp + Password for user catmyapp: 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 + + mycatapp=> \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 + ... + +=item * + +Make sure the data loaded correctly: + + mycatapp=> \dt + List of relations + Schema | Name | Type | Owner + --------+--------------+-------+---------- + public | authors | table | catmyapp + public | book_authors | table | catmyapp + public | books | table | catmyapp + (3 rows) + + mycatapp=> 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) + + mycatapp=> \q + +=back + +=item * + +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 + + +=item * + +Generate the model using the Catalyst "_create.pl" script: + + script/myapp_create.pl model MyAppDB DBIC::Schema MyAppDB 'dbi:Pg:dbname=mycatapp' 'catmyapp' 'catalyst' '{ AutoCommit => 1 }' + + +=back + +=item * + +Part 4: Authentication + +=over 4 + +=item * + +Create the C<.sql> file for the user/roles data: + +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'), + username TEXT, + password TEXT, + email_address TEXT, + first_name TEXT, + last_name TEXT, + active INTEGER + ); + + CREATE SEQUENCE roles_seq START 2 ; + SELECT nextval ('roles_seq'); + + CREATE TABLE roles ( + id INTEGER PRIMARY KEY DEFAULT nextval('roles_seq'), + role TEXT + ); + + CREATE TABLE user_roles ( + user_id INTEGER, + role_id INTEGER, + 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 data: + + $ psql -U catmyapp -W mycatapp + Password for user catmyapp: 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 + + mycatapp=> \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 + mycatapp=> + + mycatapp=> 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) + + +=item * + +Create the C<.sql> file for the hashed password data: + +Open C 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 in the data + + $ psql -U catmyapp -W mycatapp + Password for user catmyapp: + 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 + + mycatapp=> \i myapp03_psql.sql + UPDATE 1 + UPDATE 1 + UPDATE 1 + + + +=back + +=back =head1 APPENDIX 3: IMPROVED HASHING SCRIPT