X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=catagits%2FCatalyst-Manual.git;a=blobdiff_plain;f=lib%2FCatalyst%2FManual%2FTutorial%2F10_Appendices.pod;h=8c4b513868bcdd9f1dbf9cc66a6637e1e7e5624e;hp=f81ec45f56cce7f511db0d6237564bd42a41e6a9;hb=7ce05098c9b1df9078e709e5a724e821a3b3b00d;hpb=fb51e5fbcd95d478241001eed00b24e78b10a572 diff --git a/lib/Catalyst/Manual/Tutorial/10_Appendices.pod b/lib/Catalyst/Manual/Tutorial/10_Appendices.pod index f81ec45..8c4b513 100644 --- a/lib/Catalyst/Manual/Tutorial/10_Appendices.pod +++ b/lib/Catalyst/Manual/Tutorial/10_Appendices.pod @@ -126,11 +126,11 @@ commands such as C to set the mark at the cursor location and C> and C> to set the mark at the beginning and end of the file respectively. -Also, Stefan Kangas sent in the following tip about an alternate -approach using the command C to redo the indentation -for the currently selected region (adhering to indent rules in the -current major mode). You can run the command by typing M-x -indent-region or pressing the default keybinding C-M-\ in cperl-mode. +Also, Stefan Kangas sent in the following tip about an alternate +approach using the command C to redo the indentation +for the currently selected region (adhering to indent rules in the +current major mode). You can run the command by typing M-x +indent-region or pressing the default keybinding C-M-\ in cperl-mode. Additional details can be found here: L @@ -158,9 +158,9 @@ field types/constraints. =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 +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 @@ -175,13 +175,13 @@ Chapter 3: More Catalyst Basics Install the PostgreSQL server and client and DBD::Pg: -If you are following along in Debian 5, you can quickly install these +If you are following along in Debian 6, you can quickly install these items via this command: sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl -To configure the permissions, you can open -C and change this line (near the +To configure the permissions, you can open +C and change this line (near the bottom): # "local" is for Unix domain socket connections only @@ -199,8 +199,8 @@ And then restart PostgreSQL: =item * -Create the database and a user for the database (note that we are -using "EcatalystE" to represent the hidden password of +Create the database and a user for the database (note that we are +using "EcatalystE" to represent the hidden password of "catalyst"): $ sudo -u postgres createuser -P catappuser @@ -232,7 +232,7 @@ Open the C in your editor and enter: 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 -- @@ -244,20 +244,20 @@ Open the C in your editor and enter: -- 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 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 --- @@ -288,7 +288,7 @@ Open the C in your editor and enter: Load the data: $ psql -U catappuser -W catappdb -f myapp01_psql.sql - Password for user catappuser: + 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 @@ -309,24 +309,24 @@ Make sure the data loaded correctly: $ psql -U catappuser -W catappdb Password for user catappuser: Welcome to psql 8.3.7, 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=> \dt List of relations - Schema | Name | Type | Owner + Schema | Name | Type | Owner --------+--------------+-------+------------ public | authors | table | catappuser public | book_authors | table | catappuser public | books | table | catappuser (3 rows) - + catappdb=> select * from books; - id | title | rating + id | title | rating ----+------------------------------------+-------- 1 | CCSP SNRS Exam Certification Guide | 5 2 | TCP/IP Illustrated, Volume 1 | 5 @@ -334,8 +334,8 @@ Make sure the data loaded correctly: 4 | Perl Cookbook | 5 5 | Designing with Web Standards | 5 (5 rows) - - catappdb=> + + catappdb=> =back @@ -344,13 +344,13 @@ Make sure the data loaded correctly: 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 @@ -360,7 +360,7 @@ Generate the model using the Catalyst "_create.pl" script: $ 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 \ + create=static components=TimeStamp,PassphraseColumn \ 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' =back @@ -382,7 +382,7 @@ Add Datetime Columns to Our Existing Books Table 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 \ + create=static components=TimeStamp,PassphraseColumn \ 'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }' @@ -401,7 +401,7 @@ Open C in your editor and enter: -- -- Add users and roles tables, along with a many-to-many join table -- - + CREATE TABLE users ( id SERIAL PRIMARY KEY, username TEXT, @@ -411,24 +411,24 @@ Open C in your editor and enter: last_name TEXT, active INTEGER ); - + CREATE TABLE roles ( id SERIAL PRIMARY KEY, role TEXT ); - + CREATE TABLE user_roles ( 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 (username, password, email_address, first_name, last_name, active) + 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) + 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); @@ -467,7 +467,7 @@ 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 + 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 @@ -481,22 +481,22 @@ Modify C to match the following (the only difference is the C line): #!/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; } -Run the C as per the "normal" flow of the +Run the C as per the "normal" flow of the tutorial: $ perl -Ilib set_hashed_passwords.pl @@ -513,11 +513,8 @@ You can verify that it worked with this command: =head2 MySQL -B This section is out of date 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. +Howard for the help and Zsolt Zemancsik for the up to date fixes. =over 4 @@ -544,35 +541,67 @@ The Perl C module =back For CentOS users (see -L), +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 +For Debian users you can use the following commands to install the software and start the MySQL +daemon: + + apt-get install mysql-client mysql-server + /etc/init.d/mysql start + +B The tutorial is based on Foreign Keys in database which is supported by InnoDB. +Only MySQL 5.0 and above supports InnoDB storage Engine so you need to have InnoDB support +in you MySQL. You can simply figure out that your install supports it or not: + + # mysql -u root -p + Enter password: + Welcome to the MySQL monitor. Commands end with ; or \g. + + Type 'help;' or '\h' for help. Type '\c' to clear the current input + statement. + + mysql> SHOW VARIABLES LIKE 'have_innodb'; + +---------------+-------+ + | Variable_name | Value | + +---------------+-------+ + | have_innodb | YES | + +---------------+-------+ + 1 row in set (0.01 sec) + + mysql> exit + Bye + +If the Value is "YES" you can use your setup (Debian based mysql supports it by default). +Else, you need to configure your my.cnf or start your MySQL daemon without --skip-innodb option. + =item * Create the database and set the permissions: - $ mysql + # mysql -u root -p + Enter password: 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; + + Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. + + mysql> CREATE DATABASE `myapp`; Query OK, 1 row affected (0.01 sec) - - mysql> grant all on myapp.* to tutorial@'localhost'; + + mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword'; Query OK, 0 rows affected (0.00 sec) - - mysql> flush privileges; + + mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) - - mysql> quit + + mysql> exit Bye + =item * Create the C<.sql> file and load the data: @@ -586,69 +615,77 @@ Open the C 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) - ); + CREATE TABLE IF NOT EXISTS `books` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` text CHARACTER SET utf8, + `rating` int(11) DEFAULT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- '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 - ); + CREATE TABLE IF NOT EXISTS `book_authors` ( + `book_id` int(11) NOT NULL DEFAULT '0', + `author_id` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`book_id`,`author_id`), + KEY `author_id` (`author_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + CREATE TABLE IF NOT EXISTS `authors` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `first_name` text CHARACTER SET utf8, + `last_name` text CHARACTER SET utf8, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; --- --- 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); + INSERT INTO `books` (`id`, `title`, `rating`) VALUES + (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); + + INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES + (1, 1), + (1, 2), + (1, 3), + (2, 4), + (3, 5), + (4, 6), + (4, 7), + (5, 8); + + INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES + (1, 'Greg', 'Bastien'), + (2, 'Sara', 'Nasseh'), + (3, 'Christian', 'Degu'), + (4, 'Richard', 'Stevens'), + (5, 'Douglas', 'Comer'), + (6, 'Tom', 'Christiansen'), + (7, 'Nathan', 'Torkington'), + (8, 'Jeffrey', 'Zeldman'); + + ALTER TABLE `book_authors` + ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; =item * Load the data: - mysql -ututorial myapp < myapp01_mysql.sql + mysql -u tutorial -p myapp < myapp01_mysql.sql =item * Make sure the data loaded correctly: - $ mysql -ututorial myapp + $ mysql -u tutorial -p 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 | @@ -658,7 +695,7 @@ Make sure the data loaded correctly: | books | +-----------------+ 3 rows in set (0.00 sec) - + mysql> select * from books; +----+------------------------------------+--------+ | id | title | rating | @@ -670,7 +707,7 @@ Make sure the data loaded correctly: | 5 | Designing with Web Standards | 5 | +----+------------------------------------+--------+ 5 rows in set (0.00 sec) - + mysql> =back @@ -691,8 +728,8 @@ Delete the existing model: Regenerate the model using the Catalyst "_create.pl" script: - script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \ - dbi:mysql:myapp '_username_here_' '_password_here_' '{ AutoCommit => 1 }' + script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \ + dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }' =back @@ -713,42 +750,69 @@ 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) - ); - 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) - ); + CREATE TABLE IF NOT EXISTS `roles` ( + `id` int(11) NOT NULL, + `role` text CHARACTER SET utf8, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + CREATE TABLE IF NOT EXISTS `users` ( + `id` int(11) NOT NULL, + `username` text CHARACTER SET utf8, + `password` text CHARACTER SET utf8, + `email_address` text CHARACTER SET utf8, + `first_name` text CHARACTER SET utf8, + `last_name` text CHARACTER SET utf8, + `active` int(11) DEFAULT NULL, + PRIMARY KEY (`id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + CREATE TABLE IF NOT EXISTS `user_roles` ( + `user_id` int(11) NOT NULL DEFAULT '0', + `role_id` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`user_id`,`role_id`), + KEY `role_id` (`role_id`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- 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); + INSERT INTO `roles` (`id`, `role`) VALUES + (1, 'user'), + (2, 'admin'); + + INSERT INTO `users` (`id`, `username`, `password`, `email_address`, `first_name`, `last_name`, `active`) VALUES + (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); + + INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES + (1, 1), + (2, 1), + (3, 1), + (1, 2); + + ALTER TABLE `user_roles + ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; =item * Load the user/roles data: - mysql -ututorial myapp < myapp02_mysql.sql + mysql -u tutorial -p myapp < myapp02_mysql.sql + +=item * + +Update the model: + +=over 4 + +=item * + +Regenerate the model using the Catalyst "_create.pl" script: + + script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \ + components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }' + +=back =item * @@ -759,15 +823,15 @@ 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; + UPDATE users SET password = '{SSHA}esgz64CpHMo8pMfgIIszP13ft23z/zio04aCwNdm0wc6MDeloMUH4g==' WHERE id = 1; + UPDATE users SET password = '{SSHA}FpGhpCJus+Ea9ne4ww8404HH+hJKW/fW+bAv1v6FuRUy2G7I2aoTRQ==' WHERE id = 2; + UPDATE users SET password = '{SSHA}ZyGlpiHls8qFBSbHr3r5t/iqcZE602XLMbkSVRRNl6rF8imv1abQVg==' WHERE id = 3; =item * Load the user/roles data: - mysql -ututorial myapp < myapp03_mysql.sql + mysql -u tutorial -p myapp < myapp03_mysql.sql =back @@ -778,9 +842,10 @@ Load the user/roles data: Kennedy Clark, C -Please report any errors, issues or suggestions to the author. The -most recent version of the Catalyst Tutorial can be found at -L. +Feel free to contact the author for any errors or suggestions, but the +best way to report issues is via the CPAN RT Bug system at +L. -Copyright 2006-2008, Kennedy Clark, under Creative Commons License +Copyright 2006-2011, Kennedy Clark, under the +Creative Commons Attribution Share-Alike License Version 3.0 (L).