| Subject: | bug in http://search.cpan.org/~hkclark/Catalyst-Manual-5.8004/lib/Catalyst/Manual/Tutorial/10_Appendices.pod#MySQL and a fix solution |
In the Catalyst::Manual::Tutorial::10_Appendices the MySQL section is
wrong and out of date, as it told us. Fortunetly, I use MySQL as
backend so I can provide a fix for it.
1; The database and user creation don't use password for the MySQL
user, we could fix this thing. The database creation procedure is
almost the same as in the tutorial:
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Show quoted text
mysql> CREATE DATABASE `myapp`;
Query OK, 1 row affected (0.01 sec)
Show quoted textmysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost'
IDENTIFIED BY 'yourpassword';
Query OK, 0 rows affected (0.00 sec)
Show quoted textmysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Show quoted textmysql> exit
Bye
2; The *.sql example files are wrong at all. First of all, the original
example use table name book, author, book_author and not books, authors
and book_authors. Same as the user, role and user_role. This isn't a
big problem at all, but it might confuse someone else who follow the
tutorial step by step. More bigger problem is that you don't use
extended inserts, so with most of modern mysql versions it won't work
(like mine, which is 5.1). And the bigest problem is, there are no
foreign keys but that is a most important part of the catalyst
tutorial. The correct SQL examples looks like this:
myapp01_mysql.sql:
DROP TABLE IF EXISTS `author`;
CREATE TABLE IF NOT EXISTS `author` (
`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;
DROP TABLE IF EXISTS `book`;
CREATE TABLE IF NOT EXISTS `book` (
`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;
DROP TABLE IF EXISTS `book_author`;
CREATE TABLE IF NOT EXISTS `book_author` (
`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;
INSERT INTO `author` (`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');
INSERT INTO `book` (`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
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(3, 5),
(4, 6),
(4, 7),
(5, 8);
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;
myapp02_mysql.sql:
DROP TABLE IF EXISTS `role`;
CREATE TABLE IF NOT EXISTS `role` (
`id` int(11) NOT NULL,
`role` text CHARACTER SET utf8,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
`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;
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE IF NOT EXISTS `user_role` (
`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;
INSERT INTO `role` (`id`, `role`) VALUES
(1, 'user'),
(2, 'admin');
INSERT INTO `user` (`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
(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;
As you can see in this two examples, we use InnoDB storage engine
instead of MyISAM. InnoDB fully support the foreign keys, so we can use
it and it will work for sure in the tutorial. You may notice the
readers that MySQL 5.0 or above is requied to use InnoDB storage engine
and the foreign keys. Anybody should test it that is enabled or not:
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Show quoted textmysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.01 sec)
Show quoted textmysql> exit
Bye
3; The database Schema to create, I use this command:
script/myapp_create.pl model DB DBIC::Schema MyApp::Schema
create=static components=TimeStamp,EncodedColumn
dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'
I made the tutorial from the begining to the end and it works me great
with MySQL. The other parts of the mysql appendicies are good, there is
no need to fix anything.