Php文档 Php问答行业资讯 Php论坛 Php手册 Php博客

游戏榜单

软件榜单

关闭导航

热搜榜

热门下载

热门标签

关闭搜索

php爱好者> php文档>Catalyst::Manual::Tutorial::10_Appendices

Catalyst::Manual::Tutorial::10_Appendices

时间：2010-08-30 来源：horsley

Catalyst::Manual::Tutorial::10_Appendices 学习笔记（2010-8-30 星期一）文章来源：http://search.cpan.org/~hkclark/Catalyst-Manual-5.8004/lib/Catalyst/Manual/Tutorial/10_Appendices.pod 概述： ...... 附录1: 如何拷贝粘贴例子程序中的 POD 文档略...... 附录2: 使用 POSTGRESQL 或 MYSQL数据库前面例子都是使用的 SQLite 数据库，本章我们讲述如何转化成 PostgreSQL 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

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

$ 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
...

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

接下来几步:

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', '[email protected]', 'Joe', 'Blow', 1);
INSERT INTO users (username, password, email_address, first_name, last_name, active)
    VALUES ('test02', 'mypass', '[email protected]', 'Jane', 'Doe', 1);
INSERT INTO users (username, password, email_address, first_name, last_name, active)
    VALUES ('test03', 'mypass', '[email protected]', '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:

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

略....