OPNFV Infra Dashboard

JIRA: RELENG-12

Change-Id: I7451a3d234e4e5d946cdb905d5720be6159b6544
Signed-off-by: jose.lausuch <jose.lausuch@ericsson.com>

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;
+
+
+
+