summaryrefslogtreecommitdiffstats
path: root/tools/infra-dashboard/populateDB.txt
diff options
context:
space:
mode:
Diffstat (limited to 'tools/infra-dashboard/populateDB.txt')
-rw-r--r--tools/infra-dashboard/populateDB.txt203
1 files changed, 203 insertions, 0 deletions
diff --git a/tools/infra-dashboard/populateDB.txt b/tools/infra-dashboard/populateDB.txt
new file mode 100644
index 00000000..d3f8f5e4
--- /dev/null
+++ b/tools/infra-dashboard/populateDB.txt
@@ -0,0 +1,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;
+
+
+
+