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;