2 CREATE DATABASE opnfv_pharos;
8 CREATE TABLE resource (
9 resource_id INT UNSIGNED AUTO_INCREMENT,
10 name VARCHAR(100) NOT NULL,
11 slavename VARCHAR(50),
12 description VARCHAR(300),
14 bookable BOOLEAN DEFAULT false,
15 active BOOLEAN DEFAULT true,
16 PRIMARY KEY (resource_id)
21 server_id INT UNSIGNED AUTO_INCREMENT,
22 resource_id INT NOT NULL,
28 PRIMARY KEY (server_id)
33 pod_id INT UNSIGNED AUTO_INCREMENT,
34 resource_id INT NOT NULL,
41 user_id INT UNSIGNED AUTO_INCREMENT,
42 name VARCHAR(100) NOT NULL,
43 email VARCHAR(100) NOT NULL UNIQUE,
44 password VARCHAR(100) NOT NULL,
46 creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
52 role_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
54 description VARCHAR(300)
58 CREATE TABLE user_role (
61 description VARCHAR(300),
62 PRIMARY KEY (user_id, role_id)
66 DROP TABLE user_resource;
67 CREATE TABLE user_resource (
69 resource_id INT NOT NULL,
70 PRIMARY KEY (user_id, resource_id)
77 booking_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
78 resource_id INT NOT NULL,
80 starttime DATETIME NOT NULL,
81 endtime DATETIME NOT NULL,
82 creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
94 describe user_resource;
99 INSERT INTO pod_type (name, description) VALUES ("ci_pod", "PODS for CI usage only");
100 INSERT INTO pod_type (name, description) VALUES ("dev_pod", "PODS development");
104 INSERT INTO resource (name, slavename, description, link) VALUES ("Linux Foundation POD 1", "lf-pod1", "Some description", "https://wiki.opnfv.org/display/pharos/Lf+Lab");
105 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='lf-pod1';
107 INSERT INTO resource (name, slavename, description, link) VALUES ("Linux Foundation POD 2", "lf-pod2", "Some description", "https://wiki.opnfv.org/display/pharos/Lf+Lab");
108 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='lf-pod2';
110 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");
111 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='ericsson-pod2';
113 INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 2", "intel-pod2", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod2");
114 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod2';
116 INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 5", "intel-pod5", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod5");
117 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod5';
119 INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 6", "intel-pod6", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod6");
120 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod6';
122 INSERT INTO resource (name, slavename, description, link) VALUES ("Intel POD 8", "intel-pod8", "Some description", "https://wiki.opnfv.org/display/pharos/Intel+Pod8");
123 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod8';
125 INSERT INTO resource (name, slavename, description, link) VALUES ("Huawei POD 1", "huawei-pod1", "Some description", "https://wiki.opnfv.org/display/pharos/Huawei+Hosting");
126 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod1';
136 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");
137 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod3';
139 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");
140 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='dell-pod1';
142 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");
143 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='dell-pod2';
145 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");
146 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='orange-pod2';
148 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");
149 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='arm-build1';
151 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");
152 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='ericsson-pod1';
154 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");
155 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod2';
157 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");
158 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod3';
160 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");
161 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='huawei-pod4';
163 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");
164 INSERT INTO pod (resource_id) SELECT resource_id FROM resource where slavename='intel-pod9';
167 SELECT * FROM resource;
171 INSERT INTO role (name, description) VALUES ("admin", "Administrator of the system");
172 INSERT INTO role (name, description) VALUES ("lab_owner", "Owner of a lab.");
173 INSERT INTO role (name, description) VALUES ("troubleshooter", "A person who can book a pod for troubleshooting.");
176 INSERT INTO user (name, password, email, company) VALUES ("Jose Lausuch", md5("opnfv"), "jose.lausuch@ericsson.com", "Ericsson");
177 INSERT INTO user (name, password, email, company) VALUES ("Daniel Smith", md5("opnfv"), "daniel.smith@ericsson.com", "Ericsson");
178 INSERT INTO user (name, password, email, company) VALUES ("Jack Morgan", md5("opnfv"), "jack.morgan@intel.com", "Intel");
179 INSERT INTO user (name, password, email, company) VALUES ("Fatih Degirmenci", md5("opnfv"), "fatih.degirmenci@ericsson.com", "Ericsson");
180 INSERT INTO user (name, password, email, company) VALUES ("Trevor Cooper", md5("opnfv"), "trevor.cooper@intel.com", "Intel");
183 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";
184 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";
185 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";
186 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";
187 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";
188 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";
189 SELECT * FROM user_role;
191 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";
192 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";
193 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";
194 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";
195 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";
196 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";
197 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";
198 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";
199 SELECT * FROM user_resource;