postgres.sql 6.41 KB
Newer Older
w  
Qiang Xue committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165
/**
 * This is the database schema for testing PostgreSQL support of yii Active Record.
 * To test this feature, you need to create a database named 'yii' on 'localhost'
 * and create an account 'test/test' which owns this test database.
 */
CREATE SCHEMA test;

CREATE TABLE test.users
(
	id SERIAL NOT NULL PRIMARY KEY,
	username VARCHAR(128) NOT NULL,
	password VARCHAR(128) NOT NULL,
	email VARCHAR(128) NOT NULL
);

INSERT INTO test.users (username, password, email) VALUES ('user1','pass1','email1');
INSERT INTO test.users (username, password, email) VALUES ('user2','pass2','email2');
INSERT INTO test.users (username, password, email) VALUES ('user3','pass3','email3');

CREATE TABLE test.user_friends
(
	id INTEGER NOT NULL,
	friend INTEGER NOT NULL,
	PRIMARY KEY (id, friend),
	CONSTRAINT FK_user_id FOREIGN KEY (id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_friend_id FOREIGN KEY (friend)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.user_friends VALUES (1,2);
INSERT INTO test.user_friends VALUES (1,3);
INSERT INTO test.user_friends VALUES (2,3);

CREATE TABLE test.profiles
(
	id SERIAL NOT NULL PRIMARY KEY,
	first_name VARCHAR(128) NOT NULL,
	last_name VARCHAR(128) NOT NULL,
	user_id INTEGER NOT NULL,
	CONSTRAINT FK_profile_user FOREIGN KEY (user_id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.profiles (first_name, last_name, user_id) VALUES ('first 1','last 1',1);
INSERT INTO test.profiles (first_name, last_name, user_id) VALUES ('first 2','last 2',2);

CREATE TABLE test.posts
(
	id SERIAL NOT NULL PRIMARY KEY,
	title VARCHAR(128) NOT NULL,
	create_time TIMESTAMP NOT NULL,
	author_id INTEGER NOT NULL,
	content TEXT,
	CONSTRAINT FK_post_author FOREIGN KEY (author_id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 1',TIMESTAMP '2004-10-19 10:23:54',1,'content 1');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 2',TIMESTAMP '2004-10-19 10:23:54',2,'content 2');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 3',TIMESTAMP '2004-10-19 10:23:54',2,'content 3');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 4',TIMESTAMP '2004-10-19 10:23:54',2,'content 4');
INSERT INTO test.posts (title, create_time, author_id, content) VALUES ('post 5',TIMESTAMP '2004-10-19 10:23:54',3,'content 5');

CREATE TABLE test.comments
(
	id SERIAL NOT NULL PRIMARY KEY,
	content TEXT NOT NULL,
	post_id INTEGER NOT NULL,
	author_id INTEGER NOT NULL,
	CONSTRAINT FK_post_comment FOREIGN KEY (post_id)
		REFERENCES test.posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_user_comment FOREIGN KEY (author_id)
		REFERENCES test.users (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 1',1, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 2',1, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 3',1, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 4',2, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 5',2, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 6',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 7',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 8',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 9',3, 2);
INSERT INTO test.comments (content, post_id, author_id) VALUES ('comment 10',5, 3);

CREATE TABLE test.categories
(
	id SERIAL NOT NULL PRIMARY KEY,
	name VARCHAR(128) NOT NULL,
	parent_id INTEGER,
	CONSTRAINT FK_category_category FOREIGN KEY (parent_id)
		REFERENCES test.categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.categories (name, parent_id) VALUES ('cat 1',NULL);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 2',NULL);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 3',NULL);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 4',1);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 5',1);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 6',5);
INSERT INTO test.categories (name, parent_id) VALUES ('cat 7',5);

CREATE TABLE test.post_category
(
	category_id INTEGER NOT NULL,
	post_id INTEGER NOT NULL,
	PRIMARY KEY (category_id, post_id),
	CONSTRAINT FK_post_category_post FOREIGN KEY (post_id)
		REFERENCES test.posts (id) ON DELETE CASCADE ON UPDATE RESTRICT,
	CONSTRAINT FK_post_category_category FOREIGN KEY (category_id)
		REFERENCES test.categories (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.post_category (category_id, post_id) VALUES (1,1);
INSERT INTO test.post_category (category_id, post_id) VALUES (2,1);
INSERT INTO test.post_category (category_id, post_id) VALUES (3,1);
INSERT INTO test.post_category (category_id, post_id) VALUES (4,2);
INSERT INTO test.post_category (category_id, post_id) VALUES (1,2);
INSERT INTO test.post_category (category_id, post_id) VALUES (1,3);

CREATE TABLE test.orders
(
	key1 INTEGER NOT NULL,
	key2 INTEGER NOT NULL,
	name VARCHAR(128),
	PRIMARY KEY (key1, key2)
);

INSERT INTO test.orders (key1,key2,name) VALUES (1,2,'order 12');
INSERT INTO test.orders (key1,key2,name) VALUES (1,3,'order 13');
INSERT INTO test.orders (key1,key2,name) VALUES (2,1,'order 21');
INSERT INTO test.orders (key1,key2,name) VALUES (2,2,'order 22');

CREATE TABLE test.items
(
	id SERIAL NOT NULL PRIMARY KEY,
	name VARCHAR(128),
	col1 INTEGER NOT NULL,
	col2 INTEGER NOT NULL,
	CONSTRAINT FK_order_item FOREIGN KEY (col1,col2)
		REFERENCES test.orders (key1,key2) ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO test.items (name,col1,col2) VALUES ('item 1',1,2);
INSERT INTO test.items (name,col1,col2) VALUES ('item 2',1,2);
INSERT INTO test.items (name,col1,col2) VALUES ('item 3',1,3);
INSERT INTO test.items (name,col1,col2) VALUES ('item 4',2,2);
INSERT INTO test.items (name,col1,col2) VALUES ('item 5',2,2);

CREATE TABLE public.yii_types
(
	int_col INT NOT NULL,
	int_col2 INTEGER DEFAULT 1,
	char_col CHAR(100) NOT NULL,
	char_col2 VARCHAR(100) DEFAULT 'something',
	char_col3 TEXT,
	numeric_col NUMERIC(4,3) NOT NULL,
	real_col REAL DEFAULT 1.23,
	blob_col BYTEA,
	time TIMESTAMP,
	bool_col BOOL NOT NULL,
	bool_col2 BOOLEAN DEFAULT TRUE
);