=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<Part 9 of 9> of the Catalyst tutorial.
+This is B<Part 10 of 10> for the Catalyst tutorial.
L<Tutorial Overview|Catalyst::Manual::Tutorial>
=item 3
-L<Basic CRUD|Catalyst::Manual::Tutorial_BasicCRUD>
+L<More Catalyst Basics|Catalyst::Manual::Tutorial::MoreCatalystBasics>
=item 4
-L<Authentication|Catalyst::Manual::Tutorial::Authentication>
+L<Basic CRUD|Catalyst::Manual::Tutorial::BasicCRUD>
=item 5
-L<Authorization|Catalyst::Manual::Tutorial::Authorization>
+L<Authentication|Catalyst::Manual::Tutorial::Authentication>
=item 6
-L<Debugging|Catalyst::Manual::Tutorial::Debugging>
+L<Authorization|Catalyst::Manual::Tutorial::Authorization>
=item 7
-L<Testing|Catalyst::Manual::Tutorial::Testing>
+L<Debugging|Catalyst::Manual::Tutorial::Debugging>
=item 8
-L<AdvancedCRUD|Catalyst::Manual::Tutorial::AdvancedCRUD>
+L<Testing|Catalyst::Manual::Tutorial::Testing>
=item 9
+L<Advanced CRUD|Catalyst::Manual::Tutorial::AdvancedCRUD>
+
+=item 10
+
B<Appendices>
=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>
+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<DBD::Pg> module
+
+=back
+
+=item *
+
+Create the database and a user for the database
+
+ $ createuser -P catmyapp
+ Enter password for new role: <catalyst>
+ Enter it again: <catalyst>
+ 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<myapp01_psql.sql> 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<myapp02_psql.sql> 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<myapp03_psql.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 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
projects / catagits/Catalyst-Manual.git / blobdiff