-
Notifications
You must be signed in to change notification settings - Fork 0
/
table.sql
92 lines (84 loc) · 2.46 KB
/
table.sql
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
DROP TABLE IF EXISTS public_assert;
CREATE TABLE public_assert(
aid TEXT PRIMARY KEY,
cid INTEGER NOT NULL,
name TEXT NOT NULL,
model TEXT NOT NULL,
is_fixed INTEGER NOT NULL,
purchase_date TEXT NOT NULL,
manager TEXT NOT NULL,
admin_id INTEGER NOT NULL,
department_id INTEGER NOT NULL,
FOREIGN KEY(cid) REFERENCES category(cid),
FOREIGN KEY(admin_id) REFERENCES user(user_id)
FOREIGN KEY(department_id) REFERENCES department(department_id)
);
DROP TABLE IF EXISTS personal_assert;
CREATE TABLE personal_assert(
aid TEXT PRIMARY KEY,
cid INTEGER NOT NULL,
name TEXT NOT NULL,
model TEXT NOT NULL,
is_fixed INTEGER NOT NULL,
purchase_date TEXT NOT NULL,
admin_id TEXT NOT NULL,
personal_id INTEGER NOT NULL,
FOREIGN KEY(cid) REFERENCES category(cid),
FOREIGN KEY(admin_id) REFERENCES user(user_id)
FOREIGN KEY(personal_id) REFERENCES user(user_id)
);
DROP TABLE IF EXISTS department;
CREATE TABLE department(
department_id INTEGER PRIMARY KEY AUTOINCREMENT,
department_name TEXT NOT NULL,
comment TEXT
);
DROP TABLE IF EXISTS category;
CREATE TABLE category(
cid INTEGER PRIMARY KEY,
name TEXT NOT NULL,
comment TEXT
);
DROP TABLE IF EXISTS type;
CREATE TABLE type(
id INTEGER PRIMARY KEY AUTOINCREMENT,
tid TEXT NOT NULL,
name TEXT NOT NULL,
cid INTEGER NOT NULL,
FOREIGN KEY(cid) REFERENCES category(cid)
);
DROP TABLE IF EXISTS user;
CREATE TABLE user(
user_id INTEGER PRIMARY KEY,
role_id INTEGER DEFAULT 1,
username TEXT NOT NULL,
password TEXT NOT NULL,
create_time TEXT default (datetime('now','localtime')),
gender INTEGER,
department_id INTEGER,
avatar BLOB,
telephone TEXT,
FOREIGN KEY(department_id) REFERENCES department(department_id),
FOREIGN KEY(role_id) REFERENCES role(role_id)
);
DROP TABLE IF EXISTS role;
CREATE TABLE role(
role_id INTEGER PRIMARY KEY,
-- parent_role_id TEXT NOT NULL,
role_name TEXT NOT NULL,
comment TEXT
);
DROP TABLE IF EXISTS permission;
CREATE TABLE permission(
permission_id INTEGER PRIMARY KEY,
permission_name TEXT NOT NULL,
comment TEXT
);
DROP TABLE IF EXISTS role_permission;
CREATE TABLE role_permission(
role_permission_id INTEGER PRIMARY KEY AUTOINCREMENT,
role_id INTEGER NOT NULL,
permission_id INTEGER NOT NULL,
FOREIGN KEY(role_id) REFERENCES role(role_id),
FOREIGN KEY(permission_id) REFERENCES permission(permission_id)
);