X-Git-Url: https://gerrit.onap.org/r/gitweb?a=blobdiff_plain;f=ecomp-portal-DB-os%2FPortalDMLMySql_2_1_OS.sql;fp=ecomp-portal-DB-os%2FPortalDMLMySql_2_1_OS.sql;h=d6e15a1fbeac250eea1862f13a1d87afdbdb39fb;hb=21a8761f684745bb300e075c7e98ad897ace9eed;hp=0000000000000000000000000000000000000000;hpb=3af8af1310d5a27cb58be29505573f0bbdc1717c;p=portal.git diff --git a/ecomp-portal-DB-os/PortalDMLMySql_2_1_OS.sql b/ecomp-portal-DB-os/PortalDMLMySql_2_1_OS.sql new file mode 100644 index 00000000..d6e15a1f --- /dev/null +++ b/ecomp-portal-DB-os/PortalDMLMySql_2_1_OS.sql @@ -0,0 +1,113 @@ +-- --------------------------------------------------------------------------------------------------------------- +-- This is the default data for the 2.1.0 Version of Portal database called portal - the Opensource project +-- First run the common Opensource DML; then run this file to add The Opensource only data +USE portal; + +set foreign_key_checks=1; + +-- 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@openecomp.org',NULL,NULL,NULL,'demo',NULL,'demo','95LidzVz7nSpsTsRUrDNVA==','2016-10-20 15:11:16','Y',NULL,'2016-10-14 21:00:00',1,'2016-10-20 15:11:16','N',NULL,NULL,NULL,'NJ',NULL,'US',NULL,NULL,NULL,NULL,NULL,10,NULL,NULL,NULL,NULL,NULL,NULL); + +-- fn_appokYTaDrhzibcbGVq5mjkVQ== +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, APP_TYPE, AUTH_CENTRAL, AUTH_NAMESPACE) values (1,'Default','assets/images/tmp/portal1.png','Some Default Description','Some Default Note','http://localhost','http://localhost','http://localhost:8080/ecompportal','EcompPortal','',NULL,'okYTaDrhzibcbGVq5mjkVQ==','N','N',NULL,'portal','EkrqsjQqZt4ZrPh6',NULL,NULL,1,'Y',NULL); + +-- fn_user_role +Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,1,NULL,1); +Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,950,NULL,1); +Insert into fn_user_role (USER_ID,ROLE_ID,PRIORITY,APP_ID) values (1,999,NULL,1); + +INSERT INTO cr_report + (rep_id, title, descr, public_yn, report_xml, create_id, create_date, maint_id, maint_date, menu_id, menu_approved_yn, owner_id, folder_id, dashboard_type_yn, dashboard_yn) + VALUES ( + 15, + 'Application Usage Report Wid', + '', + 'Y', + '\n\n Application Usage Report Wid\n \n local\n mysql\n BarChart3D\n 700\n 500\n false\n false\n false\n 27\n 2017-01-28-05:00\n SELECT \n l.date audit_date, \n app_id app_id, \n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name) app_name, \n IFNULL(r.ct,0) ct \nfrom\n(\n select a.Date, app_id, app_name\n from (\n select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date\n from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c\n ) a, \n (\n SELECT \n app_id, app_name\n from\n (\n select @rn := @rn+1 AS rowId, app_id, app_name from \n (\n select app_id, app_name, ct from \n (\n select affected_record_id, count(*) ct\n from fn_audit_log l\n where audit_date > date_add( curdate(), interval -6 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a, fn_app f\n where a.affected_record_id = f.app_id\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm where rowId <= 4\n )b\n where a.Date between date_add( curdate(), interval -6 day) and curdate()\n) l left outer join\n(\n select app_name, DATE(audit_date) audit_date_1 ,count(*) ct from fn_audit_log a, fn_app b\n where user_id = [USER_ID]\n and audit_date > date_add( curdate(), interval -6 day)\n and activity_cd in (\'tab_access\', \'app_access\')\n and a.affected_record_id = b.app_id\n and b.app_id <> 1\n and b.app_id in \n (\n SELECT \n app_id\n from\n (\n select @rn := @rn+1 AS rowId, app_id from \n (\n select app_id, ct from \n (\n select affected_record_id app_id, count(*) ct\n from fn_audit_log \n where audit_date > date_add( curdate(), interval -6 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm \n )\n group by app_name, DATE(audit_date)\n) r\non l.Date = r.audit_date_1\nand l.app_name = r.app_name\n \n \n \n 0\n Your Search didn\'t yield any results.\n left\n \n 1\n NNNNNNN\n 100\n 100\n N\n N\n N\n N\n N|\n \n \n DUAL\n \n DUAL\n \n \n du0\n l.date\n l.date\n audit_date_1\n 10\n nullpxpx\n Left\n 1\n true\n true\n VARCHAR2\n false\n LEGEND\n 1\n \n \n false\n VARCHAR2\n \n \n \n \n du0\n app_id\n app_id\n app_id\n 10\n Left\n 2\n true\n true\n VARCHAR2\n false\n VARCHAR2\n \n \n du0\n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)\n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)\n app_name\n 10\n nullpxpx\n Left\n 3\n true\n true\n VARCHAR2\n false\n 2\n \n \n true\n VARCHAR2\n \n \n \n \n du0\n IFNULL(r.ct,0)\n IFNULL(r.ct,0)\n ct\n 10\n nullpxpx\n Left\n 4\n true\n true\n VARCHAR2\n false\n 0\n 1\n \n \n false\n VARCHAR2\n \n \n \n \n \n \n false\n false\n 500\n \n vertical\n N\n bottom\n up90\n \n \n true\n true\n true\n false\n false\n false\n true\n false\n 30\n 50\n 60\n 100\n \n NULL\n N\n N\n N\n N\n N\n N\n\n', + 1, + now(), + 1, + now(), + '', + 'N', + (select user_id from fn_user where org_user_id = 'demo'), + NULL, + 'N', + 'N' + ); + +-- new for 1707 +INSERT INTO cr_report + (rep_id, title, descr, public_yn, report_xml, create_id, create_date, maint_id, maint_date, menu_id, menu_approved_yn, owner_id, folder_id, dashboard_type_yn, dashboard_yn) + VALUES ( + 18, + 'Application Usage bar Wid', + '', + 'Y', + '\n\n Application Usage Line Wid\n \n local\n mysql\n TimeSeriesChart\n N\n 700\n 300\n false\n false\n false\n 27\n 2017-01-28-05:00\n SELECT \n l.date audit_date, \n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name) app_name, \n IFNULL(r.ct,0) ct \nfrom\n(\n select a.Date, app_id, app_name\n from (\n select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date\n from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c\n ) a, \n (\n SELECT \n app_id, app_name\n from\n (\n select @rn := @rn+1 AS rowId, app_id, app_name from \n (\n select app_id, app_name, ct from \n (\n select affected_record_id, count(*) ct\n from fn_audit_log l\n where audit_date > date_add( curdate(), interval -30 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a, fn_app f\n where a.affected_record_id = f.app_id\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm where rowId <= 4\n )b\n where a.Date between date_add( curdate(), interval -30 day) and curdate()\n) l left outer join\n(\n select app_name, DATE(audit_date) audit_date_1 ,count(*) ct from fn_audit_log a, fn_app b\n where user_id = [USER_ID]\n and audit_date > date_add( curdate(), interval -30 day)\n and activity_cd in (\'tab_access\', \'app_access\')\n and a.affected_record_id = b.app_id\n and b.app_id <> 1\n and b.app_id in \n (\n SELECT \n app_id\n from\n (\n select @rn := @rn+1 AS rowId, app_id from \n (\n select app_id, ct from \n (\n select affected_record_id app_id, count(*) ct\n from fn_audit_log \n where audit_date > date_add( curdate(), interval -30 day)\n and affected_record_id not in ( 1, -1)\n and activity_cd in (\'tab_access\', \'app_access\')\n and user_id = [USER_ID]\n group by affected_record_id\n ) a\n order by ct desc \n ) b,\n (SELECT @rn := 0) t2\n ) mm \n )\n group by app_name, DATE(audit_date)\n) r\non l.Date = r.audit_date_1\nand l.app_name = r.app_name\n \n \n \n 0\n Your Search didn\'t yield any results.\n left\n \n 1\n NNNNNNN\n 100\n 100\n N\n N\n N\n N\n N|\n \n \n DUAL\n \n DUAL\n \n \n du0\n l.date\n l.date\n audit_date_1\n 10\n nullpxpx\n Left\n 1\n true\n true\n VARCHAR2\n false\n LEGEND\n 1\n false\n false\n false\n VARCHAR2\n false\n \n \n du0\n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)\n IF(CHAR_LENGTH(l.app_name) >14, CONCAT(CONCAT(SUBSTR(l.app_name,1,7),\'...\'), SUBSTR(l.app_name, CHAR_LENGTH(l.app_name)-3,CHAR_LENGTH(l.app_name))) , l.app_name)\n app_name\n 10\n nullpxpx\n Left\n 2\n true\n true\n VARCHAR2\n false\n 0\n 2\n \n \n true\n false\n false\n VARCHAR2\n \n \n false\n \n \n du0\n IFNULL(r.ct,0)\n IFNULL(r.ct,0)\n ct\n 10\n nullpxpx\n Left\n 3\n true\n true\n VARCHAR2\n false\n 0\n 1\n \n \n false\n false\n false\n VARCHAR2\n \n \n false\n \n \n \n \n false\n false\n 500\n \n vertical\n N\n bottom\n down45\n true\n true\n true\n false\n false\n false\n true\n line\n false\n false\n false\n 30\n 50\n 60\n 100\n \n NULL\n false\n N\n N\n N\n N\n N\n N\n\n', + 1, + now(), + 1, + now(), + '', + 'N', + (select user_id from fn_user where org_user_id = 'demo'), + NULL, + 'N', + 'N' + ); + +INSERT INTO cr_report + (rep_id, title, descr, public_yn, report_xml, create_id, create_date, maint_id, maint_date, menu_id, menu_approved_yn, owner_id, folder_id, dashboard_type_yn, dashboard_yn) + VALUES ( + 20, + 'Average time spend on portal', + '', + 'Y', + '\n\n Average time spend on portal\n \n local\n mysql\n TimeSeriesChart\n N\n 700\n 300\n false\n true\n false\n 27\n 2017-01-28-05:00\n SELECT \n d.dat audit_date, \n \'# of Minutes\' app, \n coalesce(diff, null, 0) mins \nfrom\n(\n select * from\n (\n select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as dat\n from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b\n cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c \n ) d where d.dat between date_add( curdate(), interval -30 day) and curdate()\n) d left outer join\n(\n select dat, mi, mx, TIMESTAMPDIFF(MINUTE, coalesce(mi, null, 0), coalesce(mx, null, 0)) + 30 diff\n from\n (\n select DATE(audit_date) dat, coalesce(min(audit_date), null, 0) mi, coalesce(max(audit_date), null, 0) mx\n from fn_audit_log \n where user_id = [USER_ID] and DATE(audit_date) between CURDATE()-300 and CURDATE()\n group by DATE(audit_date)\n ) a\n) a\non a.dat = d.dat\norder by 1\n \n \n \n 0\n Your Search didn\'t yield any results.\n left\n \n 1\n NNNNNNN\n 100\n 100\n N\n N\n N\n N\n N|\n \n \n DUAL\n \n DUAL\n \n \n du0\n d.dat\n d.dat\n audit_date_1\n 10\n nullpxpx\n Left\n 1\n true\n true\n VARCHAR2\n false\n LEGEND\n 1\n false\n false\n false\n VARCHAR2\n false\n \n \n du0\n \'# of Minutes\'\n \'# of Minutes\'\n app\n 10\n nullpxpx\n Left\n 2\n true\n true\n VARCHAR2\n false\n true\n VARCHAR2\n \n \n du0\n coalesce(diff, null, 0)\n coalesce(diff, null, 0)\n mins\n 10\n nullpxpx\n Left\n 3\n true\n true\n VARCHAR2\n false\n 0\n 1\n \n \n false\n VARCHAR2\n \n \n \n \n \n \n false\n false\n 500\n \n vertical\n N\n bottom\n down45\n true\n true\n true\n false\n false\n false\n true\n line\n false\n false\n false\n 30\n 50\n 60\n 100\n \n NULL\n false\n N\n N\n N\n N\n N\n N\n\n', + 1, + now(), + 1, + now(), + '', + 'N', + (select user_id from fn_user where org_user_id = 'demo'), + NULL, + 'N', + 'N' + ); + + +insert into ep_app_function (app_id, function_cd, function_name) values +(1, 'url|edit_notification|*', 'User Notification'), +(1, 'url|getAdminNotifications|*', 'Admin Notifications'), +(1, 'url|login|*', 'Login'), +(1, 'menu|menu_admin|*','Admin Menu'), +(1,'menu|menu_home|*','Home Menu'), +(1, 'menu|menu_logout|*','Logout Menu'), +(1, 'menu|menu_web_analytics|*','Web Analytics'), +(1, 'url|saveNotification|*','publish notifications'), +(1, 'url|url_role.htm|*','role page'), +(1, 'url|url_welcome.htm|*','welcome page'), +(1, 'menu|menu_acc_admin|*','Admin Acc Menu'); + + +insert into ep_app_role_function (id, app_id, role_id, function_cd, role_app_id) values +(1, 1, 1, 'url|login|*', null), +(2, 1, 1, 'menu|menu_admin|*', null), +(3, 1, 1, 'menu|menu_home|*', null), +(4, 1, 1, 'menu|menu_logout|*', null), +(5, 1, 16, 'url|login|*', null), +(6, 1, 16, 'menu|menu_home|*', null), +(7, 1, 16, 'menu|menu_logout|*', null), +(8, 1, 950, 'url|edit_notification|*', null), +(9, 1, 950, 'url|getAdminNotifications|*', null), +(10, 1, 950, 'url|saveNotification|*', null), +(11, 1, 999, 'url|edit_notification|*', null), +(12, 1, 999, 'url|getAdminNotifications|*', null), +(13, 1, 999, 'menu|menu_admin|*', null), +(14, 1, 1010, 'menu|menu_web_analytics|*', null), +(15, 1, 2115, 'menu|menu_web_analytics|*', null), +(16, 1 , 1, 'menu|menu_acc_admin|*' , null), +(17, 1 , 999 ,'menu|menu_acc_admin|*', null); +commit; \ No newline at end of file