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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
CREATE DATABASE opnfv_pharos;
USE opnfv_pharos;
SHOW TABLES;
use opnfv_pharos;
DROP TABLE resource;
CREATE TABLE resource (
resource_id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
slavename VARCHAR(50),
description VARCHAR(300),
link VARCHAR(100),
bookable BOOLEAN DEFAULT false,
active BOOLEAN DEFAULT true,
PRIMARY KEY (resource_id)
);
DROP TABLE server;
CREATE TABLE server (
server_id INT UNSIGNED AUTO_INCREMENT,
resource_id INT NOT NULL,
model VARCHAR(200),
cpu VARCHAR(200),
ram VARCHAR(200),
storage VARCHAR(200),
count INT DEFAULT 1,
PRIMARY KEY (server_id)
);
DROP TABLE pod;
CREATE TABLE pod (
pod_id INT UNSIGNED AUTO_INCREMENT,
resource_id INT NOT NULL,
chassis VARCHAR(500),
PRIMARY KEY (pod_id)
);
DROP TABLE user;
CREATE TABLE user(
user_id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
company VARCHAR(100),
creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id)
);
DROP TABLE role;
CREATE TABLE role (
role_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
description VARCHAR(300)
);
DROP TABLE user_role;
CREATE TABLE user_role (
user_id INT NOT NULL,
role_id INT NOT NULL,
description VARCHAR(300),
PRIMARY KEY (user_id, role_id)
);
DROP TABLE user_resource;
CREATE TABLE user_resource (
user_id INT NOT NULL,
resource_id INT NOT NULL,
PRIMARY KEY (user_id, resource_id)
);
DROP TABLE booking;
CREATE TABLE booking(
booking_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
resource_id INT NOT NULL,
user_id INT NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
purpose VARCHAR(300)
);
/*
describe resource;
describe server;
describe pod;
describe pod_type;
describe user;
describe role;
describe user_role;
describe user_resource;
describe booking;
*/
/* POD TYPES */
INSERT INTO pod_type (name, description) VALUES ("ci_pod", "PODS for CI usage only");
INSERT INTO pod_type (name, description) VALUES ("dev_pod", "PODS development");
/* CI PODS */
INSERT INTO resource (name, slavename, description, link) VALUES ("Linux Foundation POD 1", "lf-pod1", "Some description", "https://wiki.opnfv.org/display/pharos/Lf+Lab");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='lf-pod1';
INSERT INTO resource (name, slavename, description, link) VALUES ("Linux Foundation POD 2", "lf-pod2", "Some description", "https://wiki.opnfv.org/display/pharos/Lf+Lab");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='lf-pod2';
INSERT INTO resource (name, slavename, description, link) VALUES ("Ericsson POD 2", "ericsson-pod2", "Some description", "https://wiki.opnfv.org/display/pharos/Ericsson+Hosting+and+Request+Process");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='ericsson-pod2';
INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 2", "intel-pod2", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod2");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod2';
INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 5", "intel-pod5", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod5");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod5';
INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 6", "intel-pod6", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod6");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod6';
INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 8", "intel-pod8", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod8");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod8';
INSERT INTO resource (name, slavename, description, link) VALUES ("Huawei POD 1", "huawei-pod1", "Some description", "https://wiki.opnfv.org/display/pharos/Huawei+Hosting");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod1';
/* SOME DEV PODS */
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Intel POD 3", "intel-pod3", "Some description", true, "https://wiki.opnfv.org/display/pharos/Intel+Pod3");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod3';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Dell POD 1", "dell-pod1", "Some description", true, "https://wiki.opnfv.org/display/pharos/Dell+Hosting");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='dell-pod1';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Dell POD 2", "dell-pod2", "Some description", true, "https://wiki.opnfv.org/display/pharos/Dell+Hosting");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='dell-pod2';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Orange POD 2", "orange-pod2", "Some description", true, "https://wiki.opnfv.org/display/pharos/Opnfv-orange-pod2");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='orange-pod2';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Arm POD 1", "arm-build1", "Some description", true, "https://wiki.opnfv.org/display/pharos/Enea-pharos-lab");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='arm-build1';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Ericsson POD 1", "ericsson-pod1", "Some description", true, "https://wiki.opnfv.org/display/pharos/Ericsson+Hosting+and+Request+Process");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='ericsson-pod1';
INSERT INTO resource (name, slavename, description, bookable,link) VALUES ("Huawei POD 2", "huawei-pod2", "Some description", true, "https://wiki.opnfv.org/display/pharos/Huawei+Hosting");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod2';
INSERT INTO resource (name, slavename, description, bookable,link) VALUES ("Huawei POD 3", "huawei-pod3", "Some description", true, "https://wiki.opnfv.org/display/pharos/Huawei+Hosting");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod3';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Huawei POD 4", "huawei-pod4", "Some description", true, "https://wiki.opnfv.org/display/pharos/Huawei+Hosting");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod4';
INSERT INTO resource (name, slavename, description, bookable, link) VALUES ("Intel POD 9", "intel-pod9", "Some description", true, "https://wiki.opnfv.org/display/pharos/Intel+Pod9");
INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod9';
SELECT * FROM resource;
SELECT * FROM pod;
INSERT INTO role (name, description) VALUES ("admin", "Administrator of the system");
INSERT INTO role (name, description) VALUES ("lab_owner", "Owner of a lab.");
INSERT INTO role (name, description) VALUES ("troubleshooter", "A person who can book a pod for troubleshooting.");
SELECT * FROM role;
INSERT INTO user (name, password, email, company) VALUES ("Jose Lausuch", md5("opnfv"), "jose.lausuch@ericsson.com", "Ericsson");
INSERT INTO user (name, password, email, company) VALUES ("Daniel Smith", md5("opnfv"), "daniel.smith@ericsson.com", "Ericsson");
INSERT INTO user (name, password, email, company) VALUES ("Jack Morgan", md5("opnfv"), "jack.morgan@intel.com", "Intel");
INSERT INTO user (name, password, email, company) VALUES ("Fatih Degirmenci", md5("opnfv"), "fatih.degirmenci@ericsson.com", "Ericsson");
INSERT INTO user (name, password, email, company) VALUES ("Trevor Cooper", md5("opnfv"), "trevor.cooper@intel.com", "Intel");
SELECT * FROM user;
INSERT INTO user_role (user_id, role_id) SELECT user_id,role_id FROM user,role WHERE email="jose.lausuch@ericsson.com" AND role.name="admin";
INSERT INTO user_role (user_id, role_id) SELECT user_id,role_id FROM user,role WHERE email="daniel.smith@ericsson.com" AND role.name="lab_owner";
INSERT INTO user_role (user_id, role_id) SELECT user_id,role_id FROM user,role WHERE email="jack.morgan@intel.com" AND role.name="lab_owner";
INSERT INTO user_role (user_id, role_id) SELECT user_id,role_id FROM user,role WHERE email="jack.morgan@intel.com" AND role.name="troubleshooter";
INSERT INTO user_role (user_id, role_id) SELECT user_id,role_id FROM user,role WHERE email="fatih.degirmenci@ericsson.com" AND role.name="troubleshooter";
INSERT INTO user_role (user_id, role_id) SELECT user_id,role_id FROM user,role WHERE email="trevor.cooper@intel.com" AND role.name="troubleshooter";
SELECT * FROM user_role;
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="daniel.smith@ericsson.com" and slavename="ericsson-pod1";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="daniel.smith@ericsson.com" and slavename="ericsson-pod2";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="jack.morgan@intel.com" and slavename="intel-pod2";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="jack.morgan@intel.com" and slavename="intel-pod3";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="jack.morgan@intel.com" and slavename="intel-pod5";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="jack.morgan@intel.com" and slavename="intel-pod6";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="jack.morgan@intel.com" and slavename="intel-pod8";
INSERT INTO user_resource (user_id, resource_id) SELECT user_id,resource_id FROM user,resource WHERE email="jack.morgan@intel.com" and slavename="intel-pod9";
SELECT * FROM user_resource;
|
