fms/db.sql at master · podatech/fms · GitHub

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
# noinspection SqlNoDataSourceInspectionForFile

CREATE TABLE t_users(
  id_user VARCHAR(10),
  username VARCHAR(14),
  password VARCHAR(255),
  status ENUM('1','2','4'),
  created_date TIMESTAMP,
  created_by VARCHAR(10),
  updated_date TIMESTAMP,
  updated_by VARCHAR(10)
);

-- 1:Non Active, 2:Active, 4:Hold

ALTER TABLE t_users
  ADD CONSTRAINT t_users_id_user_pk PRIMARY KEY(id_user);

CREATE TABLE t_users_roles(
  id_role VARCHAR(10),
  role VARCHAR(30),
  created_date TIMESTAMP,
  created_by VARCHAR(10),
  updated_date TIMESTAMP,
  updated_by VARCHAR(10)
);

ALTER TABLE t_users_roles
  ADD CONSTRAINT t_users_roles_pk PRIMARY KEY(id_role);

-- t_posts is representing Question

CREATE TABLE t_posts(
  id_post VARCHAR(10),
  post TEXT,
  created_date TIMESTAMP,
  created_by VARCHAR(10),
  updated_date TIMESTAMP,
  updated_by VARCHAR(10)
);

ALTER TABLE t_posts
  ADD CONSTRAINT t_posts_id_post_pk PRIMARY KEY(id_post);

CREATE TABLE t_tags(
  id_tag BIGINT NOT NULL,
  tag TEXT,
  created_date TIMESTAMP,
  created_by VARCHAR(10),
  updated_date TIMESTAMP,
  updated_by VARCHAR(10)
);

ALTER TABLE t_tags
  ADD CONSTRAINT t_tags_id_tag_pk PRIMARY KEY(id_tag);

CREATE TABLE t_streams(
  id_post VARCHAR(10),
  id_tag BIGINT,
  id_role VARCHAR(10),
  created_date TIMESTAMP,
  created_by VARCHAR(10),
  updated_date TIMESTAMP,
  updated_by VARCHAR(10)
);

ALTER TABLE t_streams
  ADD CONSTRAINT t_streams_t_post_fk
FOREIGN KEY(id_post)
REFERENCES t_posts(id_post)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

ALTER TABLE t_streams
  ADD CONSTRAINT t_streams_t_tags_fk
FOREIGN KEY(id_tag)
REFERENCES t_tags(id_tag)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

ALTER TABLE t_streams
  ADD CONSTRAINT t_streams_t_users_roles_fk
FOREIGN KEY(id_role)
REFERENCES t_users_roles(id_role)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

CREATE TABLE t_answers(
  id_answer VARCHAR(10),
  answer TEXT,
  id_post VARCHAR(10),
  created_date TIMESTAMP,
  created_by VARCHAR(10),
  updated_date TIMESTAMP,
  updated_by VARCHAR(10)
);

ALTER TABLE t_answers
  ADD CONSTRAINT t_answers_id_answer_pk PRIMARY KEY(id_answer);

ALTER TABLE t_answers
  ADD CONSTRAINT t_answers_t_posts_fk
FOREIGN KEY(id_post)
REFERENCES t_posts(id_post)
  ON DELETE SET NULL
  ON UPDATE CASCADE;

-- Maybe some tables incorrect. The development process is still working on progress.
-- Add post title to t_posts
ALTER TABLE `t_posts` ADD `title` VARCHAR(255) NOT NULL AFTER `id_post`;