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
$ 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
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 }'
=back
For CentOS users (see
-L<Catalyst::Manual::Installation::CentOS4|Catalyst::Manual::Installation::CentOS4>),
+L<Catalyst::Manual::Installation::CentOS4>),
you can use the following commands to install the software and start the MySQL
daemon:
mysql> exit
Bye
-
+
=item *
--
-- Create a very simple database to hold book and author information
--
- CREATE TABLE IF NOT EXISTS `book` (
+ 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 IF NOT EXISTS `book_author` (
+ 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 `author` (
+ 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,
---
--- Load some sample data
---
- INSERT INTO `book` (`id`, `title`, `rating`) VALUES
+ 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_author` (`book_id`, `author_id`) VALUES
+ INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(4, 7),
(5, 8);
- INSERT INTO `author` (`id`, `first_name`, `last_name`) VALUES
+ INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES
(1, 'Greg', 'Bastien'),
(2, 'Sara', 'Nasseh'),
(3, 'Christian', 'Degu'),
(7, 'Nathan', 'Torkington'),
(8, 'Jeffrey', 'Zeldman');
- ALTER TABLE `book_author`
- ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ 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 *
--
-- Add users and roles tables, along with a many-to-many join table
--
- CREATE TABLE IF NOT EXISTS `role` (
+ 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 `user` (
+ CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL,
`username` text CHARACTER SET utf8,
`password` text CHARACTER SET utf8,
`active` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- CREATE TABLE IF NOT EXISTS `user_role` (
+ 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`),
--
-- Load up some initial test data
--
- INSERT INTO `role` (`id`, `role`) VALUES
+ INSERT INTO `roles` (`id`, `role`) VALUES
(1, 'user'),
(2, 'admin');
- INSERT INTO `user` (`id`, `username`, `password`, `email_address`, `first_name`, `last_name`, `active`) VALUES
+ 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_role` (`user_id`, `role_id`) VALUES
+ INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(1, 2);
- ALTER TABLE `user_role`
- ADD CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
- ADD CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ 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 *
Regenerate the model using the Catalyst "_create.pl" script:
script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
- components=TimeStamp,EncodedColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
+ components=TimeStamp,PassphraseColumn dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
=back
--
-- 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 *