-
Notifications
You must be signed in to change notification settings - Fork 28
Expand file tree
/
Copy pathschema.sql
More file actions
120 lines (107 loc) · 3.31 KB
/
schema.sql
File metadata and controls
120 lines (107 loc) · 3.31 KB
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
CREATE TABLE public.employee (
emp_no SERIAL NOT NULL,
birth_date DATE NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
gender TEXT NOT NULL CHECK (gender IN('M', 'F')) NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
CREATE INDEX idx_employee_hire_date ON public.employee (hire_date);
CREATE TABLE public.department (
dept_no TEXT NOT NULL,
dept_name TEXT NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE (dept_name)
);
CREATE TABLE public.dept_manager (
emp_no INT NOT NULL,
dept_no TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, dept_no)
);
CREATE TABLE public.dept_emp (
emp_no INT NOT NULL,
dept_no TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, dept_no)
);
CREATE TABLE public.title (
emp_no INT NOT NULL,
title TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, title, from_date)
);
CREATE TABLE public.salary (
emp_no INT NOT NULL,
amount INT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
);
CREATE INDEX idx_salary_amount ON public.salary (amount);
CREATE TABLE public.audit (
id SERIAL PRIMARY KEY,
operation TEXT NOT NULL,
query TEXT,
user_name TEXT NOT NULL,
changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_audit_operation ON public.audit (operation);
CREATE INDEX idx_audit_username ON public.audit (user_name);
CREATE INDEX idx_audit_changed_at ON public.audit (changed_at);
CREATE OR REPLACE FUNCTION public.log_dml_operations() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO public.audit (operation, query, user_name)
VALUES ('INSERT', current_query(), current_user);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO public.audit (operation, query, user_name)
VALUES ('UPDATE', current_query(), current_user);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO public.audit (operation, query, user_name)
VALUES ('DELETE', current_query(), current_user);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- only log update and delete, otherwise, it will cause too much change.
CREATE TRIGGER salary_log_trigger
AFTER UPDATE OR DELETE ON public.salary
FOR EACH ROW
EXECUTE FUNCTION public.log_dml_operations();
CREATE OR REPLACE VIEW public.dept_emp_latest_date AS
SELECT
emp_no,
MAX(
from_date) AS from_date,
MAX(
to_date) AS to_date
FROM
public.dept_emp
GROUP BY
emp_no;
-- shows only the current department for each employee
CREATE OR REPLACE VIEW public.current_dept_emp AS
SELECT
l.emp_no,
dept_no,
l.from_date,
l.to_date
FROM
public.dept_emp d
INNER JOIN public.dept_emp_latest_date l ON d.emp_no = l.emp_no
AND d.from_date = l.from_date
AND l.to_date = d.to_date;