31. --
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
pass TEXT,
is_deleted BOOL DEFAULT false
);
--
CREATE UNIQUE INDEX users_valid_constraint
ON users (name) WHERE NOT is_deleted;
32.
33. -- テーブルを作成する
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
pass TEXT,
is_deleted BOOL DEFAULT false
);
-- 一意な部分インデックスを張る
CREATE UNIQUE INDEX users_valid_constraint
ON users (name) WHERE NOT is_deleted;
34. -- ryuを登録する
testdb=# INSERT INTO users (name, pass, is_deleted)
testdb-# VALUES ('ryu', 'xxx', false);
INSERT 0 1
-- 登録したデータを確認
testdb=# SELECT * FROM users;
id | name | pass | is_deleted
----+------+------+------------
1 | ryu | xxx | f
(1 row)
46. -- ユーザテーブルを作成する -- エントリーテーブルを作成する
CREATE TABLE users ( CREATE TABLE entries (
user_id SERIAL NOT NULL, entry_id SERIAL NOT NULL,
name TEXT, user_id INTEGER NOT NULL,
pass TEXT, event_id INTEGER NOT NULL,
PRIMARY KEY(user_id) stat TEXT NOT NULL,
); message TEXT,
PRIMARY KEY(entry_id),
-- イベントテーブルを作成する FOREIGN KEY(user_id)
CREATE TABLE events ( REFERENCES users(user_id)
event_id SERIAL NOT NULL, ON DELETE CASCADE
user_id INTEGER NOT NULL, ON UPDATE CASCADE,
name TEXT, FOREIGN KEY(event_id)
PRIMARY KEY(event_id), REFERENCES events(event_id)
FOREIGN KEY(user_id) ON DELETE CASCADE
REFERENCES users(user_id) ON UPDATE CASCADE,
ON DELETE CASCADE UNIQUE(user_id, event_id)
ON UPDATE CASCADE );
);
54. -- ちなみに、物理削除するとON DELETE CASCADEで連鎖削除される
testdb=# DELETE FROM users WHERE user_id = 1;
DELETE 1
testdb=# SELECT * FROM users;
user_id | name | pass
---------+------+------
-2 | ken | xxx
(1 row)
testdb=# SELECT * FROM events;
event_id | user_id | name
----------+---------+----------
3 | -2 | △△勉強会
(1 row)
testdb=# SELECT * FROM entries;
entry_id | user_id | event_id | stat | message
----------+---------+----------+------+----------
5 | -2 | 3 | 参加 | よろしく
(1 row)
55.
56.
57.
58.
59. -- ユーザテーブルを作成する -- エントリーテーブルを作成する
CREATE TABLE users ( CREATE TABLE entries (
user_id SERIAL NOT NULL, entry_id SERIAL NOT NULL,
name TEXT, user_id INTEGER NOT NULL,
pass TEXT, event_id INTEGER NOT NULL,
PRIMARY KEY(user_id) stat TEXT NOT NULL,
); message TEXT,
PRIMARY KEY(entry_id),
-- イベントテーブルを作成する FOREIGN KEY(user_id)
CREATE TABLE events ( REFERENCES users(user_id)
event_id SERIAL NOT NULL, ON DELETE CASCADE
user_id INTEGER NOT NULL, ON UPDATE CASCADE,
name TEXT, FOREIGN KEY(event_id)
PRIMARY KEY(event_id), REFERENCES events(event_id)
FOREIGN KEY(user_id) ON DELETE CASCADE
REFERENCES users(user_id) ON UPDATE CASCADE,
ON DELETE CASCADE UNIQUE(user_id, event_id)
ON UPDATE CASCADE );
);
61. -- 削除されたユーザの保存先を作成する
CREATE TABLE deleted_users (
user_id SERIAL NOT NULL,
name TEXT,
pass TEXT
);
-- 削除されたイベントの保存先を作成する
CREATE TABLE deleted_events (
event_id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
name TEXT
);
-- 削除されたエントリの保存先を作成する
CREATE TABLE deleted_entries (
entry_id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
event_id INTEGER NOT NULL,
stat TEXT NOT NULL,
message TEXT
);
62. -- DELETE実行時に実行するFUNCTIONを定義する
CREATE OR REPLACE FUNCTION process_deleted() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
EXECUTE 'INSERT INTO deleted_' ||
TG_RELNAME || ' VALUES(($1).*)' USING OLD;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
63. -- DELETE時に実行するTRIGGERを仕掛ける
CREATE TRIGGER trigger_users_deleted
AFTER DELETE ON users
FOR EACH ROW EXECUTE PROCEDURE process_deleted();
CREATE TRIGGER trigger_events_deleted
AFTER DELETE ON events
FOR EACH ROW EXECUTE PROCEDURE process_deleted();
CREATE TRIGGER trigger_entries_deleted
AFTER DELETE ON entries
FOR EACH ROW EXECUTE PROCEDURE process_deleted();
83. CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
