Catalyst::Manual::Tutorial::10_Appendices

Catalyst::Manual::Tutorial::10_Appendices

学习笔记（2010-8-30 星期一）

 

 

文章来源：

 

 

概述：

......

 

附录1: 如何拷贝粘贴例子程序中的 POD 文档

 

略......

 

附录2: 使用 POSTGRESQL 或 MYSQL数据库

 

前面例子都是使用的  数据库，本章我们讲述如何转化成 and MySQL 数据库。一个优秀的 MVC 框架，应该是尽可能的数据库无关的。因此转换 Catalyst 应用的数据库应该是很简单的。一般情况下，我们只需要修改 schema 定义，并添加少量参数配置。

也因为如此，我们的目的也不是把数据库的很多个性化的东西发挥到极致，比如对于约束完整性方面的东西。

PostgreSQL

通过下面几步可以将本章例子迁移到 PostgreSQL 数据库。感谢 xxxx .....

• 针对第三章 : More Catalyst Basics
  • 安装 PostgreSQL 服务器，客户端，以及 DBD::Pg:

    如果你是 Debian 5 平台, 可以使用一下命令快速安装:

sudo aptitude install postgresql libdbd-pg-perl libdatetime-format-pg-perl

配置权限，编辑 /etc/postgresql/8.3/main/pg_hba.conf 修改下面内容（文件底部）:

 

# "local" is for Unix domain socket connections only
local all all ident sameuser

变更为：

# "local" is for Unix domain socket connections only
local all all trust

重启 PostgreSQL 服务：

sudo /etc/init.d/postgresql-8.3 restart

• 创建数据库，添加用户，这里我们用""表示你的密码:

$ sudo -u postgres createuser -P catappuser
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
$ sudo -u postgres createdb -O catappuser catappdb
CREATE DATABASE

• 编写加载数据的 .sql 脚本:
  • 编辑 myapp01_psql.sql :

--
-- Drops just in case you are reloading
---
DROP TABLE IF EXISTS books CASCADE;
DROP TABLE IF EXISTS authors CASCADE;
DROP TABLE IF EXISTS book_authors CASCADE;
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
--

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT ,
    rating INTEGER,
    -- Manually add these later
    -- 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
---

INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5);
INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5);
INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4);
INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5);
INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5);
INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien');
INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh');
INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu');
INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens');
INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer');
INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen');
INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington');
INSERT INTO authors (first_name, last_name) VALUES ('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);

• 加载数据:

$ psql -U catappuser -W catappdb -f myapp01_psql.sql
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
psql:myapp01_psql.sql:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
CREATE TABLE
psql:myapp01_psql.sql:21: NOTICE: CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id"
psql:myapp01_psql.sql:21: 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
...

• 查看一下，确保数据正确:

$ psql -U catappuser -W catappdb
Password for user catappuser: <catalyst>
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
--------+--------------+-------+------------
 public | authors | table | catappuser
 public | book_authors | table | catappuser
 public | books | table | catappuser
(3 rows)

catappdb=> 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)

catappdb=>

• 接下来几步:

edit lib/MyApp.pm

create lib/MyAppDB.pm

create lib/MyAppDB/Book.pm

create lib/MyAppDB/Author.pm

create lib/MyAppDB/BookAuthor.pm

• 生成 model（使用 Catalyst "_create.pl" 脚本）:

$ 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 \
    'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'

• 针对第四章: Basic CRUD

给 Books 表增加 Datetime 字段：

$ psql -U catappuser -W catappdb
...
catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();
ALTER TABLE
catappdb=> ALTER TABLE books ADD updated TIMESTAMP;
ALTER TABLE
catappdb=> \q

 

重新生成  model :

 

$ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
    create=static components=TimeStamp,EncodedColumn \
    'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'

• 针对第五章: Authentication
• Create the .sql file for the user/roles data:

创建脚本 myapp02_psql.sql :

--
-- Add users and roles tables, along with a many-to-many join table
--

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT,
    password TEXT,
    email_address TEXT,
    first_name TEXT,
    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)
    VALUES ('test01', 'mypass', '[e-mail]', 'Joe', 'Blow', 1);
INSERT INTO users (username, password, email_address, first_name, last_name, active)
    VALUES ('test02', 'mypass', '[e-mail]', 'Jane', 'Doe', 1);
INSERT INTO users (username, password, email_address, first_name, last_name, active)
    VALUES ('test03', 'mypass', '[e-mail]', 'No', 'Go', 0);
INSERT INTO roles (role) VALUES ('user');
INSERT INTO roles (role) VALUES ('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);

• Load the data:

$ psql -U catappuser -W catappdb -f myapp02_psql.sql
Password for user catappuser: <catalyst>
psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
psql:myapp02_psql.sql:13: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE will create implicit sequence "roles_id_seq" for serial column "roles.id"
psql:myapp02_psql.sql:18: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for table "roles"
CREATE TABLE
psql:myapp02_psql.sql:24: 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

Confirm with:

$ psql -U catappuser -W catappdb -c "select * from users"
Password for user catappuser: <catalyst>
 id | username | password | email_address | first_name | last_name | active
----+----------+----------+---------------+------------+-----------+--------
  1 | test01 | mypass | [e-mail] | Joe | Blow | 1
  2 | test02 | mypass | [e-mail] | Jane | Doe | 1
  3 | test03 | mypass | [e-mail] | No | Go | 0
(3 rows)

• Modify set_hashed_passwords.pl to match the following (the only difference is the connect 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 set_hashed_passwords.pl as per the "normal" flow of the tutorial:

$ perl -Ilib set_hashed_passwords.pl

You can verify that it worked with this command: 

$ psql -U catappuser -W catappdb -c "select * from users"

 

MySQL

略....