· 5 years ago · Aug 28, 2020, 02:40 PM
1drop table IF EXISTS users_roles CASCADE;
2drop table IF EXISTS users CASCADE;
3drop table IF EXISTS roles CASCADE;
4drop table IF EXISTS requests CASCADE;
5drop table IF EXISTS sources CASCADE;
6drop table IF EXISTS answers CASCADE;
7drop table IF EXISTS files CASCADE;
8
9
10create TABLE roles(
11role_id int PRIMARY KEY,
12role_name VARCHAR(25) NOT NULL
13);
14create TABLE users(
15id serial PRIMARY KEY,
16first_name VARCHAR(25) NOT NULL,
17last_name VARCHAR(25) NOT NULL,
18comsi_id VARCHAR(8) NOT NULL UNIQUE,
19password VARCHAR(100) NOT NULL,
20location VARCHAR(60),
21business_group VARCHAR(70),
22cost_center VARCHAR(40),
23department VARCHAR(40),
24reporter_manager VARCHAR(60)
25);
26
27create TABLE users_roles(
28user_id int NOT NULL,
29role_id int,
30FOREIGN KEY (user_id) REFERENCES users(id),
31FOREIGN KEY (role_id) REFERENCES roles(role_id)
32);
33
34insert into roles(role_id,role_name) values
35(1,'ROLE_ADMIN'),
36(2,'ROLE_OPERATOR'),
37(3,'ROLE_USER');
38
39insert into users(first_name,last_name,comsi_id,password, location,
40business_group,cost_center,department,reporter_manager) values
41('Adam','Kozak','GG2ADAM','admin1','Lodz','FICC','1234567','GM-BDAA, CL Market Data Lodz',
42'Patrick Doe'),
43('Admin','Adminowicz','GG2ADMIN','admin1','Lodz','MICC','421233','GM-Cloud, GCP Lodz',
44'John Doe'),
45('Ola','Operator','GG2OPERA','admin1','Frankfurt','DMC','721233','Important, GCP Frankfurt',
46'Lorem Ipsum'),
47('Artur','Stepien','GG2STEP','admin1','Lodz','FICC','1234567','GM-BDAA, CL Market Data Lodz',
48'Adrian Nowaczyk'),
49('Pawel','Welfle','GG2WELF','admin1','Lodz','FICC','1234567','GM-BDAA, CL Market Data Lodz',
50'Adrian Nowaczyk'),
51('Lukasz','Jankowski','GG2JANZ','admin1','Lodz','FICC','1234567','GM-BDAA, CL Market Data Lodz',
52'Adrian Nowaczyk');
53
54
55insert into users_roles(user_id,role_id) values
56(1,3),
57(2,1),
58(3,2),
59(4,1),
60(5,1),
61(6,1);
62
63
64create TABLE requests (
65 id serial NOT NULL,
66 date TIMESTAMP NOT NULL,
67 status VARCHAR(30) NOT NULL,
68 headline VARCHAR(60) NOT NULL,
69 message VARCHAR(6048) NOT NULL,
70 location VARCHAR(60),
71 business_group VARCHAR(70),
72 cost_center VARCHAR(40),
73 department VARCHAR(40),
74 reporter_manager VARCHAR(60),
75 deadline_date TIMESTAMP NOT NULL,
76 deadline_comments VARCHAR(1055),
77 sources VARCHAR(2055),
78 german_language BOOLEAN NOT NULL,
79 CONSTRAINT "requests_pk" PRIMARY KEY ("id"),
80 assignee_id serial,
81 FOREIGN KEY(assignee_id) REFERENCES users(id),
82 requestor_id serial NOT NULL,
83 FOREIGN KEY(requestor_id) REFERENCES users(id)
84);
85
86
87insert into requests(date,status,headline,message,location,business_group,cost_center,department,reporter_manager,
88deadline_date,deadline_comments,sources,german_language,assignee_id,requestor_id) values
89('2020-07-05 12:32:01','NEW','Help with access','I cant login into my ztbdev account',
90'Lodz','BDAA Market Data','123456','Department, Branch in Lodz','Adrian Nowaczyk','2020-07-30 14:30:00','Do it fast!',
91'www.helpme.com',false,3,5),
92('2020-02-05 22:32:01','PENDING','Help with access','I cant login into my ztbdev account',
93'Lodz','BDAA Cloud','123456','Department , Branch in Lodz','Adrian Nowaczyk','2020-08-14 12:30:00','Do it fast!',
94'www.helpme.com',false,3,5),
95('2020-06-05 12:21:01','IN PROGRESS','Help with login','I cant log on to my computer',
96'Lodz','BDAA Market Data','654321','Department, Branch in Lodz','Adrian Nowaczyk','2020-09-02 12:30:00','Help',
97'www.helpme.com',true,3,6);
98
99create TABLE sources (
100 id SERIAL PRIMARY KEY,
101 source_name VARCHAR(100) NOT NULL
102);
103
104insert into sources(source_name)values
105('none'),
106('BMI'),
107('Börsenzeitung'),
108('Capital IQ Other'),
109('Capital IQ Research'),
110('Coba Research'),
111('EIU'),
112('EMIS'),
113('Euromonitor'),
114('Factiva'),
115('Fitch free'),
116('Frost & Sullivan'),
117('FT.com'),
118('GBRDIRECT'),
119('Genios'),
120('Handelsregsiter'),
121('Internal Sources'),
122('Internet'),
123('Markus'),
124('Mergemarkets'),
125('Moodys'),
126('PI Filings'),
127('PI Int''l Private Cos'),
128('RM Online'),
129('S&P'),
130('Sharepoint'),
131('SNL'),
132('Statista'),
133('Statistisches Bundesamt'),
134('T1.com Deals'),
135('T1.com Other'),
136('Thomson EIKON'),
137('Unternehmensregister4');
138
139create TABLE answers(
140id serial PRIMARY KEY,
141date TIMESTAMP NOT NULL,
142subject varchar(50) NOT NULL,
143message varchar(2048) NOT NULL,
144request_id serial,
145FOREIGN KEY(request_id) REFERENCES requests(id),
146sender_id serial,
147FOREIGN KEY(sender_id) REFERENCES users(id)
148);
149
150insert into answers(date,sender_id, subject,message, request_id) values
151('2020-07-05 12:52:01',5,'Validation of the results',
152'Sed blandit mollis leo eu ultrices. Aliquam quis tortor eget sem lobortis congue ac ut dui.Duis vitae lorem eget lorem facilisis iaculis. Duis ut molestie massa, sit amet gravida dui.In hac habitasse platea dictumst. Maecenas porta commodo felis, ac ornare enim tincidunt nec.Nunc ut elit vel justo suscipit blandit non eu tellus.',1),
153('2020-07-05 13:04:24',3,'Validation of the results',
154'Sed blandit mollis leo eu ultrices. Aliquam quis tortor eget sem lobortis congue ac ut dui.Duis vitae lorem eget lorem facilisis iaculis. Duis ut molestie massa, sit amet gravida dui.In hac habitasse platea dictumst. Maecenas porta commodo felis, ac ornare enim tincidunt nec.Nunc ut elit vel justo suscipit blandit non eu tellus.',1),
155('2020-07-05 14:25:37',5,'Validation of the results',
156'Sed blandit mollis leo eu ultrices. Aliquam quis tortor eget sem lobortis congue ac ut dui.Duis vitae lorem eget lorem facilisis iaculis. Duis ut molestie massa, sit amet gravida dui.In hac habitasse platea dictumst. Maecenas porta commodo felis, ac ornare enim tincidunt nec.Nunc ut elit vel justo suscipit blandit non eu tellus.',1);
157
158