diff options
Diffstat (limited to 'ecomp-sdk/epsdk-app-os/db-scripts')
5 files changed, 204 insertions, 0 deletions
diff --git a/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkDDLMySql_1707_OS.sql b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkDDLMySql_1707_OS.sql new file mode 100644 index 00000000..1b1ce059 --- /dev/null +++ b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkDDLMySql_1707_OS.sql @@ -0,0 +1,12 @@ +-- --------------------------------------------------------------------------------------------------------------- +-- This script adds tables for the 1707 OPEN-SOURCE version of the ECOMP SDK application database. +-- The DDL 1707 COMMON script must be executed first! +-- --------------------------------------------------------------------------------------------------------------- + +SET FOREIGN_KEY_CHECKS=1; + +USE ecomp_sdk; + +-- No additional tables required at this time + +commit; diff --git a/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkDMLMySql_1707_OS.sql b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkDMLMySql_1707_OS.sql new file mode 100644 index 00000000..839baa07 --- /dev/null +++ b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkDMLMySql_1707_OS.sql @@ -0,0 +1,40 @@ +-- --------------------------------------------------------------------------------------------------------------- +-- This script populates tables in the 1707 OPEN-SOURCE version of the ECOMP SDK application database. +-- The DML 1707 COMMON script must be executed first! +-- --------------------------------------------------------------------------------------------------------------- + +SET FOREIGN_KEY_CHECKS=1; + +USE ecomp_sdk; + +-- fn_menu +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (1, 'Root', NULL, 10, NULL, 'menu_home', 'N', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); -- we need even though it's inactive +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (5000, 'Sample Pages', 1, 30, 'sample.htm', 'menu_sample', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-documents-book'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (2, 'Home', 1, 10, 'welcome.htm', 'menu_home', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-building-home'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (8, 'Reports', 1, 40, 'report.htm', 'menu_reports', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-misc-piechart'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (9, 'Profile', 1, 90, 'userProfile', 'menu_profile', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-people-oneperson'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (10, 'Admin', 1, 110, 'role_list.htm', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-content-star'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (84, 'All Reports', 8, 50, 'report', 'menu_reports', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/reports.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) values (87, 'Create Reports', 8, 120, 'report_wizard.htm?r_action=report.create', 'menu_reports', 'Y', NULL, 'r_action=report.create', NULL, NULL, 'APP', 'N', NULL); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) values (88, 'Sample Dashboard', 8, 130, 'report_dashboard', 'menu_reports', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (89, 'Import', 8, 140, 'report_wizard.htm?action=report.import', 'menu_reports', 'N', null, null, null, null, 'APP', 'N', null); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (94, 'Self', 9, 40,'userProfile#/self_profile', 'menu_profile', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/profile.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (101, 'Roles', 10, 20, 'admin#/admin', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/users.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (102, 'Role Functions', 10, 30, 'admin#/role_function_list', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (105, 'Cache Admin', 10, 40, 'admin#/jcs_admin', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/cache.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (108, 'Usage', 10, 80, 'admin#/usage_list', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/users.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (121, 'Collaboration', 5000, 100, 'samplePage#/collaborate_list', 'menu_sample', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/bubble.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (930, 'Search', 9, 15, 'userProfile', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/search_profile.png'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (150022, 'Menus', 10, 60, 'admin#/admin_menu_edit', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (150038,'Notebook',5000,135,'samplePage#/notebook','menu_sample','Y',NULL,NULL,NULL,NULL,'APP','N',NULL); + +-- fn_user +Insert into fn_user (USER_ID,ORG_ID,MANAGER_ID,FIRST_NAME,MIDDLE_NAME,LAST_NAME,PHONE,FAX,CELLULAR,EMAIL,ADDRESS_ID,ALERT_METHOD_CD,HRID,ORG_USER_ID,ORG_CODE,LOGIN_ID,LOGIN_PWD,LAST_LOGIN_DATE,ACTIVE_YN,CREATED_ID,CREATED_DATE,MODIFIED_ID,MODIFIED_DATE,IS_INTERNAL_YN,ADDRESS_LINE_1,ADDRESS_LINE_2,CITY,STATE_CD,ZIP_CODE,COUNTRY_CD,LOCATION_CLLI,ORG_MANAGER_USERID,COMPANY,DEPARTMENT_NAME,JOB_TITLE,TIMEZONE,DEPARTMENT,BUSINESS_UNIT,BUSINESS_UNIT_NAME,COST_CENTER,FIN_LOC_CODE,SILO_STATUS) values (1,null,null,'Demo',null,'User',null,null,null,'demo@email.com',null,null,null,'demo',null,'demo','demo',str_to_date('24-OCT-16','%d-%M-%Y'),'Y',null,str_to_date('17-OCT-16','%d-%M-%Y'),1,str_to_date('24-OCT-16','%d-%M-%Y'),'N',null,null,null,'NJ',null,'US',null,null,null,null,null,10,null,null,null,null,null,null); + +-- fn_app +Insert into fn_app (APP_ID,APP_NAME,APP_IMAGE_URL,APP_DESCRIPTION,APP_NOTES,APP_URL,APP_ALTERNATE_URL,APP_REST_ENDPOINT,ML_APP_NAME,ML_APP_ADMIN_ID,MOTS_ID,APP_PASSWORD,OPEN,ENABLED,THUMBNAIL,APP_USERNAME,UEB_KEY,UEB_SECRET,UEB_TOPIC_NAME) VALUES (1,'Default',null,'Some Default Description','Some Default Note',null,null,null,'ECPP','?','1','app_password_here','N','N',null,'Default',null,null,'ECOMP-PORTAL-INBOX'); + +-- fn_user_role +Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,1,null,1); + +commit;
\ No newline at end of file diff --git a/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkMySql_Rollback_1707_to_1610.2_OS.sql b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkMySql_Rollback_1707_to_1610.2_OS.sql new file mode 100644 index 00000000..cb57443d --- /dev/null +++ b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkMySql_Rollback_1707_to_1610.2_OS.sql @@ -0,0 +1,57 @@ +-- --------------------------------------------------------------------------------------------------------------- +-- This rollback script is for the OpenSource ECOMP SDK App database from version 1707 to 1610.2. +-- change size on 3 name columns from fn_user for the Opensource version +-- changed DS1 icon names to DS2 icon names +-- removed unnecessary fn_menu entries, updated fn_menu actions +-- --------------------------------------------------------------------------------------------------------------- + +USE ecomp_sdk; + +update fn_menu +set image_src = 'ion-home' +where menu_id = 2 and label = 'Home'; + +update fn_menu +set image_src = 'ion-android-apps' +where menu_id = 5000 and label = 'Sample Pages'; + +update fn_menu +set image_src = 'ion-ios-paper' +where menu_id = 8 and label = 'Reports'; + +update fn_menu +set image_src = 'ion-person' +where menu_id = 9 and label = 'Profile'; + +update fn_menu +set image_src = 'ion-gear-a' +where menu_id = 10 and label = 'Admin'; + +update fn_menu +set action = 'collaborate_list.htm' +where menu_id = 121 and label = 'Collaboration'; + +update fn_menu +set action = 'notebook.htm' +where menu_id = 150038 and label = 'Notebook'; + +update fn_menu +set action = 'admin' +where menu_id = 101 and label = 'Roles'; + +delete from fn_menu +where menu_id = 150022 and label = 'Menus'; + +delete from fn_menu +where menu_id = 89 and label = 'Import'; + +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (13, 'Application Logout', 1, 130, 'app_logout.htm', 'menu_logout', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', 'icon-sign-out'); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (92, 'Import User', 9, 30, null, 'menu_profile_import', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (103, 'Broadcast Messages', 10, 50, 'admin#/broadcast_list', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', '/static/fusion/images/bubble.png'); + +alter table fn_user + CHANGE COLUMN FIRST_NAME FIRST_NAME VARCHAR(25) NULL DEFAULT NULL , + CHANGE COLUMN MIDDLE_NAME MIDDLE_NAME VARCHAR(25) NULL DEFAULT NULL , + CHANGE COLUMN LAST_NAME LAST_NAME VARCHAR(25) NULL DEFAULT NULL; + +commit; diff --git a/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkMySql_Upgrade_1610.2_to_1707_OS.sql b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkMySql_Upgrade_1610.2_to_1707_OS.sql new file mode 100644 index 00000000..81522f82 --- /dev/null +++ b/ecomp-sdk/epsdk-app-os/db-scripts/EcompSdkMySql_Upgrade_1610.2_to_1707_OS.sql @@ -0,0 +1,59 @@ +-- --------------------------------------------------------------------------------------------------------------- +-- This script upgrades the OpenSource ECOMP SDK App database from version 1610.2 to 1707. +-- change size on 3 name columns from fn_user for the Opensource version +-- changed DS1 icon names to DS2 icon names +-- removed unnecessary fn_menu entries, updated fn_menu actions +-- --------------------------------------------------------------------------------------------------------------- + +USE ecomp_sdk; + +update fn_menu +set image_src = 'icon-building-home' +where menu_id = 2 and label = 'Home'; + +update fn_menu +set image_src = 'icon-documents-book' +where menu_id = 5000 and label = 'Sample Pages'; + +update fn_menu +set image_src = 'icon-misc-piechart' +where menu_id = 8 and label = 'Reports'; + +update fn_menu +set image_src = 'icon-people-oneperson' +where menu_id = 9 and label = 'Profile'; + +update fn_menu +set image_src = 'icon-content-star' +where menu_id = 10 and label = 'Admin'; + +update fn_menu +set action = 'samplePage#/collaborate_list' +where menu_id = 121 and label = 'Collaboration'; + +update fn_menu +set action = 'samplePage#/notebook' +where menu_id = 150038 and label = 'Notebook'; + +update fn_menu +set action = 'admin#/admin' +where menu_id = 101 and label = 'Roles'; + +delete from fn_menu +where menu_id = 13 and label = 'Application Logout'; + +delete from fn_menu +where menu_id = 92 and label = 'Import User'; + +delete from fn_menu +where menu_id = 103 and label = 'Broadcast Messages'; + +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (89, 'Import', 8, 140, 'report_wizard.htm?action=report.import', 'menu_reports', 'Y', null, null, null, null, 'APP', 'N', null); +INSERT INTO fn_menu (MENU_ID, LABEL, PARENT_ID, SORT_ORDER, ACTION, FUNCTION_CD, ACTIVE_YN, SERVLET, QUERY_STRING, EXTERNAL_URL, TARGET, MENU_SET_CD, SEPARATOR_YN, IMAGE_SRC) VALUES (150022, 'Menus', 10, 60, 'admin#/admin_menu_edit', 'menu_admin', 'Y', NULL, NULL, NULL, NULL, 'APP', 'N', NULL); + +alter table fn_user + CHANGE COLUMN FIRST_NAME FIRST_NAME VARCHAR(50) NULL DEFAULT NULL , + CHANGE COLUMN MIDDLE_NAME MIDDLE_NAME VARCHAR(50) NULL DEFAULT NULL , + CHANGE COLUMN LAST_NAME LAST_NAME VARCHAR(50) NULL DEFAULT NULL; + +commit; diff --git a/ecomp-sdk/epsdk-app-os/db-scripts/readme.txt b/ecomp-sdk/epsdk-app-os/db-scripts/readme.txt new file mode 100644 index 00000000..5f1be1bc --- /dev/null +++ b/ecomp-sdk/epsdk-app-os/db-scripts/readme.txt @@ -0,0 +1,36 @@ +This Readme file contains a description of open source scripts located in + + epsdk-app-os / db-scripts / + +*************************************************************************************************************************************** + +Directions: + +DDL +For an Opensource instance run only script EcompSdkDDLMySql_1707_Common.sql; + +EcompSdkDDLMySql_1707_Common.sql - common DDL entries +EcompSdkDDLMySql_1707_OS.sql is only a placeholder at this time. + +DML + +For an Opensource instance run script EcompSdkDMLMySql_1707_Common.sql and script EcompSdkDMLMySql_1707_OS.sql; + +EcompSdkDMLMySql_1707_Common.sql - common DML entries +EcompSdkDMLMySql_1707_OS.sql - DML entries for Opensource needs + +To Upgrade the OpenSource 1610.2 version to the 1707 version, run script EcompSdkMySql_Upgrade_1610.2_to_1707_OS.sql +and to remove those changes run EcompSdkMySql_Rollback_1707_to_1610.2_OS.sql. + +EcompSdkMySql_Upgrade_1610.2_to_1707_OS.sql +EcompSdkMySql_Rollback_1707_to_1610.2_OS.sql + + +*************************************************************************************************************************************** +epsdk-app-os / db-scripts / +*************************************************************************************************************************************** +EcompSdkDDLMySql_1707_OS.sql It is empty for now; just a logical placeholder +EcompSdkDMLMySql_1707_OS.sql This script populates tables in the 1707 OPEN-SOURCE version of the ECOMP SDK application database. + After The DML 1707 COMMON script is run. +EcompSdkMySql_Upgrade_1610.2_to_1707_OS.sql This script upgrades the ECOMP SDK App database from version 1610.2 to 1707. +EcompSdkMySql_Rollback_1707_to_1610.2_OS.sql This script rolls-back the upgrade for the ECOMP SDK App database |