+++ /dev/null
--- ---------------------------------------------------------------------------------------------------------------
--- This is the 1707 Open Source Version of Ecomp Portal database called portal
--- based on 1610.2 Open Source Version and 1702 Version of Ecomp Portal database called portal from
--- Branchfeature/1610.2_OpenSource/ecomp_portal_core ecomp-portal-BE ecomp-portal-resources sql scripts
--- includes new additions for the 1707 release
--- Integrated Notifications. Web Analytics, Basic Authentication and Widgets
-
--- note to : database admin, set the mysql system variable called lower_case_table_names
--- it can be set 3 different ways:
--- command-line options (cmd-line),
--- options valid in configuration files (option file), or
--- server system variables (system var).
-
--- it needs to be set to 1, then table names are stored in lowercase on disk and comparisons are not case sensitive.
--- MySql/MariaDB Version compatibility information
--- bash-4.2$ mysql --version – cluster version
--- mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1
-
--- All versions newer or older than these DO NOT necessarily mean they are compatible.
--- -----------------------------------------------------------------------------------------------------------------
-
-
-set foreign_key_checks=1;
-
-create database portal;
-
-use portal;
-
--- ------------------ create table section
---
--- name: cr_favorite_reports; type: table
---
-create table cr_favorite_reports (
- user_id integer not null,
- rep_id integer not null
-);
---
--- name: cr_filehist_log; type: table
---
-create table cr_filehist_log (
- schedule_id numeric(11,0) not null,
- url character varying(4000),
- notes character varying(3500),
- run_time timestamp
-);
---
--- name: cr_folder; type: table
---
-create table cr_folder (
- folder_id integer not null,
- folder_name character varying(50) not null,
- descr character varying(500),
- create_id integer not null,
- create_date timestamp not null,
- parent_folder_id integer,
- public_yn character varying(1) default 'n' not null
-);
---
--- name: cr_folder_access; type: table
---
-create table cr_folder_access (
- folder_access_id numeric(11,0) not null,
- folder_id numeric(11,0) not null,
- order_no numeric(11,0) not null,
- role_id numeric(11,0),
- user_id numeric(11,0),
- read_only_yn character varying(1) default 'n' not null
-);
---
--- name: cr_hist_user_map; type: table
---
-create table cr_hist_user_map (
- hist_id int(11) not null,
- user_id int(11) not null
-);
---
--- name: cr_lu_file_type; type: table
---
-create table cr_lu_file_type (
- lookup_id numeric(2,0) not null,
- lookup_descr character varying(255) not null,
- active_yn character(1) default 'y',
- error_code numeric(11,0)
-);
---
--- name: cr_raptor_action_img; type: table
---
-create table cr_raptor_action_img (
- image_id character varying(100) not null,
- image_loc character varying(400)
-);
---
--- name: cr_raptor_pdf_img; type: table
---
-create table cr_raptor_pdf_img (
- image_id character varying(100) not null,
- image_loc character varying(400)
-);
---
--- name: cr_remote_schema_info; type: table
---
-create table cr_remote_schema_info (
- schema_prefix character varying(5) not null,
- schema_desc character varying(75) not null,
- datasource_type character varying(100)
-);
---
--- name: cr_report; type: table
---
-create table cr_report (
- rep_id numeric(11,0) not null,
- title character varying(100) not null,
- descr character varying(255),
- public_yn character varying(1) default 'n' not null,
- report_xml text,
- create_id numeric(11,0),
- create_date timestamp default now(),
- maint_id numeric(11,0),
- maint_date timestamp default now(),
- menu_id character varying(500),
- menu_approved_yn character varying(1) default 'n' not null,
- owner_id numeric(11,0),
- folder_id integer default 0,
- dashboard_type_yn character varying(1) default 'n',
- dashboard_yn character varying(1) default 'n'
-);
---
--- name: cr_report_access; type: table
---
-create table cr_report_access (
- rep_id numeric(11,0) not null,
- order_no numeric(11,0) not null,
- role_id numeric(11,0),
- user_id numeric(11,0),
- read_only_yn character varying(1) default 'n' not null
-);
---
--- name: cr_report_dwnld_log; type: table
---
-create table cr_report_dwnld_log (
- user_id numeric(11,0) not null,
- rep_id integer not null,
- file_name character varying(100) not null,
- dwnld_start_time timestamp default now() not null,
- record_ready_time timestamp default now(),
- filter_params character varying(2000)
-);
---
--- name: cr_report_email_sent_log; type: table
---
-create table cr_report_email_sent_log (
- log_id integer not null,
- schedule_id numeric(11,0),
- gen_key character varying(25) not null,
- rep_id numeric(11,0) not null,
- user_id numeric(11,0),
- sent_date timestamp default now(),
- access_flag character varying(1) default 'y' not null,
- touch_date timestamp default now()
-);
---
--- name: cr_report_file_history; type: table
---
-create table cr_report_file_history (
- hist_id int(11) not null,
- sched_user_id numeric(11,0) not null,
- schedule_id numeric(11,0) not null,
- user_id numeric(11,0) not null,
- rep_id numeric(11,0),
- run_date timestamp,
- recurrence character varying(50),
- file_type_id numeric(2,0),
- file_name character varying(80),
- file_blob blob,
- file_size numeric(11,0),
- raptor_url character varying(4000),
- error_yn character(1) default 'n',
- error_code numeric(11,0),
- deleted_yn character(1) default 'n',
- deleted_by numeric(38,0)
-);
---
--- name: cr_report_log; type: table
---
-create table cr_report_log (
- rep_id numeric(11,0) not null,
- log_time timestamp not null,
- user_id numeric(11,0) not null,
- action character varying(2000) not null,
- action_value character varying(50),
- form_fields character varying(4000)
-);
---
--- name: cr_report_schedule; type: table
---
-create table cr_report_schedule (
- schedule_id numeric(11,0) not null,
- sched_user_id numeric(11,0) not null,
- rep_id numeric(11,0) not null,
- enabled_yn character varying(1) not null,
- start_date timestamp default now(),
- end_date timestamp default now(),
- run_date timestamp default now(),
- recurrence character varying(50),
- conditional_yn character varying(1) not null,
- condition_sql character varying(4000),
- notify_type integer default 0,
- max_row integer default 1000,
- initial_formfields character varying(3500),
- processed_formfields character varying(3500),
- formfields character varying(3500),
- condition_large_sql text,
- encrypt_yn character(1) default 'n',
- attachment_yn character(1) default 'y'
-);
---
--- name: cr_report_schedule_users; type: table
---
-create table cr_report_schedule_users (
- schedule_id numeric(11,0) not null,
- rep_id numeric(11,0) not null,
- user_id numeric(11,0) not null,
- role_id numeric(11,0),
- order_no numeric(11,0) not null
-);
---
--- name: cr_report_template_map; type: table
---
-create table cr_report_template_map (
- report_id integer not null,
- template_file character varying(200)
-);
---
--- name: cr_schedule_activity_log; type: table
---
-create table cr_schedule_activity_log (
- schedule_id numeric(11,0) not null,
- url character varying(4000),
- notes character varying(2000),
- run_time timestamp
-);
---
--- name: cr_table_join; type: table
---
-create table cr_table_join (
- src_table_name character varying(30) not null,
- dest_table_name character varying(30) not null,
- join_expr character varying(500) not null
-);
---
--- name: cr_table_role; type: table
---
-create table cr_table_role (
- table_name character varying(30) not null,
- role_id numeric(11,0) not null
-);
---
--- name: cr_table_source; type: table
---
-create table cr_table_source (
- table_name character varying(30) not null,
- display_name character varying(30) not null,
- pk_fields character varying(200),
- web_view_action character varying(50),
- large_data_source_yn character varying(1) default 'n' not null,
- filter_sql character varying(4000),
- source_db character varying(50)
-);
---
--- name: fn_lu_timezone; type: table
---
-create table fn_lu_timezone (
- timezone_id int(11) not null,
- timezone_name character varying(100) not null,
- timezone_value character varying(100) not null
-);
-
-create table fn_user (
- user_id int(11) not null primary key auto_increment,
- org_id int(11),
- manager_id int(11),
- first_name character varying(50),
- middle_name character varying(50),
- last_name character varying(50),
- phone character varying(25),
- fax character varying(25),
- cellular character varying(25),
- email character varying(50),
- address_id numeric(11,0),
- alert_method_cd character varying(10),
- hrid character varying(20),
- org_user_id CHARACTER VARYING(20),
- org_code character varying(30),
- login_id character varying(25),
- login_pwd character varying(100),
- last_login_date timestamp,
- active_yn character varying(1) default 'y' not null,
- created_id int(11),
- created_date timestamp default now(),
- modified_id int(11),
- modified_date timestamp default now(),
- is_internal_yn character(1) default 'n' not null,
- address_line_1 character varying(100),
- address_line_2 character varying(100),
- city character varying(50),
- state_cd character varying(3),
- zip_code character varying(11),
- country_cd character varying(3),
- location_clli character varying(8),
- org_manager_userid CHARACTER VARYING(20),
- company character varying(100),
- department_name character varying(100),
- job_title character varying(100),
- timezone int(11),
- department character varying(25),
- business_unit character varying(25),
- business_unit_name character varying(100),
- cost_center character varying(25),
- fin_loc_code character varying(10),
- silo_status character varying(10)
-);
---
--- name: fn_role; type: table
---
-create table fn_role (
- role_id int(11) not null primary key auto_increment,
- role_name character varying(50) not null,
- active_yn character varying(1) default 'y' not null,
- priority numeric(4,0),
- app_id int(11) default null,
- app_role_id int(11) default null
-
-);
---
--- name: fn_audit_action; type: table
---
-create table fn_audit_action (
- audit_action_id integer not null,
- class_name character varying(500) not null,
- method_name character varying(50) not null,
- audit_action_cd character varying(20) not null,
- audit_action_desc character varying(200),
- active_yn character varying(1)
-);
---
--- name: fn_audit_action_log; type: table
---
-create table fn_audit_action_log (
- audit_log_id integer not null primary key auto_increment,
- audit_action_cd character varying(200),
- action_time timestamp,
- user_id numeric(11,0),
- class_name character varying(100),
- method_name character varying(50),
- success_msg character varying(20),
- error_msg character varying(500)
-);
---
--- name: fn_lu_activity; type: table
---
-create table fn_lu_activity (
- activity_cd character varying(50) not null primary key,
- activity character varying(50) not null
-);
---
--- name: fn_audit_log; type: table
---
-create table fn_audit_log (
- log_id int(11) not null primary key auto_increment,
- user_id int(11) not null,
- activity_cd character varying(50) not null,
- audit_date timestamp default now() not null,
- comments character varying(1000),
- affected_record_id_bk character varying(500),
- affected_record_id character varying(4000),
- constraint fk_fn_audit_ref_209_fn_user foreign key (user_id) references fn_user(user_id)
-);
---
--- name: fn_broadcast_message; type: table
---
-create table fn_broadcast_message (
- message_id int(11) not null primary key auto_increment,
- message_text character varying(1000) not null,
- message_location_id numeric(11,0) not null,
- broadcast_start_date timestamp not null default now(),
- broadcast_end_date timestamp not null default now(),
- active_yn character(1) default 'y' not null,
- sort_order numeric(4,0) not null,
- broadcast_site_cd character varying(50)
-);
---
--- name: fn_chat_logs; type: table
---
-create table fn_chat_logs (
- chat_log_id integer not null,
- chat_room_id integer,
- user_id integer,
- message character varying(1000),
- message_date_time timestamp
-);
---
--- name: fn_chat_room; type: table
---
-create table fn_chat_room (
- chat_room_id integer not null,
- name character varying(50) not null,
- description character varying(500),
- owner_id integer,
- created_date timestamp default now(),
- updated_date timestamp default now()
-);
---
--- name: fn_chat_users; type: table
---
-create table fn_chat_users (
- chat_room_id integer,
- user_id integer,
- last_activity_date_time timestamp,
- chat_status character varying(20),
- id integer not null
-);
---
--- name: fn_datasource; type: table
---
-create table fn_datasource (
- id integer not null primary key auto_increment,
- name character varying(50),
- driver_name character varying(256),
- server character varying(256),
- port integer,
- user_name character varying(256),
- password character varying(256),
- url character varying(256),
- min_pool_size integer,
- max_pool_size integer,
- adapter_id integer,
- ds_type character varying(20)
-);
---
--- name: fn_function; type: table
---
-create table fn_function (
- function_cd character varying(30) not null primary key,
- function_name character varying(50) not null
-);
---
--- name: fn_lu_alert_method; type: table
---
-create table fn_lu_alert_method (
- alert_method_cd character varying(10) not null,
- alert_method character varying(50) not null
-);
---
--- name: fn_lu_broadcast_site; type: table
---
-create table fn_lu_broadcast_site (
- broadcast_site_cd character varying(50) not null,
- broadcast_site_descr character varying(100)
-);
---
--- name: fn_lu_menu_set; type: table
---
-create table fn_lu_menu_set (
- menu_set_cd character varying(10) not null primary key,
- menu_set_name character varying(50) not null
-);
---
--- name: fn_lu_priority; type: table
---
-create table fn_lu_priority (
- priority_id numeric(11,0) not null,
- priority character varying(50) not null,
- active_yn character(1) not null,
- sort_order numeric(5,0)
-);
---
--- name: fn_lu_role_type; type: table
---
-create table fn_lu_role_type (
- role_type_id numeric(11,0) not null,
- role_type character varying(50) not null
-);
---
--- name: fn_lu_tab_set; type: table
---
-create table fn_lu_tab_set (
- tab_set_cd character varying(30) not null,
- tab_set_name character varying(50) not null
-);
---
--- name: fn_menu; type: table
---
-create table fn_menu (
- menu_id int(11) not null primary key auto_increment,
- label character varying(100),
- parent_id int(11),
- sort_order numeric(4,0),
- action character varying(200),
- function_cd character varying(30),
- active_yn character varying(1) default 'y' not null,
- servlet character varying(50),
- query_string character varying(200),
- external_url character varying(200),
- target character varying(25),
- menu_set_cd character varying(10) default 'app',
- separator_yn character(1) default 'n',
- image_src character varying(100),
- constraint fk_fn_menu_ref_196_fn_menu foreign key (parent_id) references fn_menu(menu_id),
- constraint fk_fn_menu_menu_set_cd foreign key (menu_set_cd) references fn_lu_menu_set(menu_set_cd),
- constraint fk_fn_menu_ref_223_fn_funct foreign key (function_cd) references fn_function(function_cd)
-);
---
--- name: fn_org; type: table
---
-create table fn_org (
- org_id int(11) not null,
- org_name character varying(50) not null,
- access_cd character varying(10)
-);
---
--- name: fn_restricted_url; type: table
---
-create table fn_restricted_url (
- restricted_url character varying(250) not null,
- function_cd character varying(30) not null
-);
---
--- name: fn_role_composite; type: table
---
-create table fn_role_composite (
- parent_role_id int(11) not null,
- child_role_id int(11) not null,
- constraint fk_fn_role_composite_child foreign key (child_role_id) references fn_role(role_id),
- constraint fk_fn_role_composite_parent foreign key (parent_role_id) references fn_role(role_id)
-);
---
--- name: fn_role_function; type: table
---
-create table fn_role_function (
- role_id int(11) not null,
- function_cd character varying(30) not null,
- constraint fk_fn_role__ref_198_fn_role foreign key (role_id) references fn_role(role_id)
-);
---
--- name: fn_tab; type: table
---
-create table fn_tab (
- tab_cd character varying(30) not null,
- tab_name character varying(50) not null,
- tab_descr character varying(100),
- action character varying(100) not null,
- function_cd character varying(30) not null,
- active_yn character(1) not null,
- sort_order numeric(11,0) not null,
- parent_tab_cd character varying(30),
- tab_set_cd character varying(30)
-);
---
--- name: fn_tab_selected; type: table
---
-create table fn_tab_selected (
- selected_tab_cd character varying(30) not null,
- tab_uri character varying(40) not null
-);
---
--- name: fn_user_pseudo_role; type: table
---
-create table fn_user_pseudo_role (
- pseudo_role_id int(11) not null,
- user_id int(11) not null
-);
---
--- name: fn_user_role; type: table
---
-create table fn_user_role (
- user_id int(10) not null,
- role_id int(10) not null,
- priority numeric(4,0),
- app_id int(11) default 2,
- constraint fk_fn_user__ref_172_fn_user foreign key (user_id) references fn_user(user_id),
- constraint fk_fn_user__ref_175_fn_role foreign key (role_id) references fn_role(role_id)
-);
---
--- name: schema_info; type: table
---
-create table schema_info (
- SCHEMA_ID CHARACTER VARYING(25) not null,
- SCHEMA_DESC CHARACTER VARYING(75) not null,
- DATASOURCE_TYPE CHARACTER VARYING(100),
- CONNECTION_URL VARCHAR(200) not null,
- USER_NAME VARCHAR(45) not null,
- PASSWORD VARCHAR(45) null default null,
- DRIVER_CLASS VARCHAR(100) not null,
- MIN_POOL_SIZE INT not null,
- MAX_POOL_SIZE INT not null,
- IDLE_CONNECTION_TEST_PERIOD INT not null
-
-);
--- ----------------------------------------------------------
--- name: fn_app; type: table
--- ----------------------------------------------------------
-create table fn_app (
- app_id int(11) primary key not null auto_increment,
- app_name varchar(100) not null default '?',
- app_image_url varchar(256) default null,
- app_description varchar(512) default null,
- app_notes varchar(4096) default null,
- app_url varchar(256) default null,
- app_alternate_url varchar(256) default null,
- app_rest_endpoint varchar(2000) default null,
- ml_app_name varchar(50) not null default '?',
- ml_app_admin_id varchar(7) not null default '?',
- mots_id int(11) default null,
- app_password varchar(256) not null default '?',
- open char(1) default 'n',
- enabled char(1) default 'y',
- thumbnail mediumblob null default null,
- app_username varchar(50),
- ueb_key varchar(256) default null,
- ueb_secret varchar(256) default null,
- ueb_topic_name varchar(256) default null,
- app_type int(11) not null default 1
-);
-
--- ------------------ functional menu tables -------------------
---
--- table structure for table fn_menu_functional
---
-create table fn_menu_functional (
- menu_id int(11) not null auto_increment,
- column_num int(2) not null,
- text varchar(100) not null,
- parent_menu_id int(11) default null,
- url varchar(128) not null default '',
- active_yn varchar(1) not null default 'y',
- image_src varchar(100) default null,
- primary key (menu_id),
- key fk_fn_menu_func_parent_menu_id_idx (parent_menu_id),
- constraint fk_fn_menu_func_parent_menu_id foreign key (parent_menu_id) references fn_menu_functional (menu_id) on delete no action on update no action
-);
---
--- table structure for table fn_menu_functional_ancestors
---
-
-create table fn_menu_functional_ancestors (
- id int(11) not null auto_increment,
- menu_id int(11) not null,
- ancestor_menu_id int(11) not null,
- depth int(2) not null,
- primary key (id),
- key fk_fn_menu_func_anc_menu_id_idx (menu_id),
- key fk_fn_menu_func_anc_anc_menu_id_idx (ancestor_menu_id),
- constraint fk_fn_menu_func_anc_anc_menu_id foreign key (ancestor_menu_id) references fn_menu_functional (menu_id) on delete no action on update no action,
- constraint fk_fn_menu_func_anc_menu_id foreign key (menu_id) references fn_menu_functional (menu_id) on delete no action on update no action
-);
---
--- table structure for table fn_menu_functional_roles
---
-create table fn_menu_functional_roles (
- id int(11) not null auto_increment,
- menu_id int(11) not null,
- app_id int(11) not null,
- role_id int(10) not null,
- primary key (id),
- key fk_fn_menu_func_roles_menu_id_idx (menu_id),
- key fk_fn_menu_func_roles_app_id_idx (app_id),
- key fk_fn_menu_func_roles_role_id_idx (role_id),
- constraint fk_fn_menu_func_roles_app_id foreign key (app_id) references fn_app (app_id) on delete no action on update no action,
- constraint fk_fn_menu_func_roles_menu_id foreign key (menu_id) references fn_menu_functional (menu_id) on delete no action on update no action,
- constraint fk_fn_menu_func_roles_role_id foreign key (role_id) references fn_role (role_id) on delete no action on update no action
-);
--- ----------------------------------------------------------
--- NAME: FN_WORKFLOW; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_workflow (
- id mediumint(9) not null auto_increment,
- name varchar(20) not null,
- description varchar(500) default null,
- run_link varchar(300) default null,
- suspend_link varchar(300) default null,
- modified_link varchar(300) default null,
- active_yn varchar(300) default null,
- created varchar(300) default null,
- created_by int(11) default null,
- modified varchar(300) default null,
- modified_by int(11) default null,
- workflow_key varchar(50) default null,
- primary key (id),
- UNIQUE KEY name (name)
-);
-
-
--- ----------------------------------------------------------
--- NAME: FN_SCHEDULE_WORKFLOWS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_schedule_workflows (
- id_schedule_workflows bigint(25) primary key not null auto_increment,
- workflow_server_url varchar(45) default null,
- workflow_key varchar(45) not null,
- workflow_arguments varchar(45) default null,
- startDateTimeCron varchar(45) default null,
- endDateTime TIMESTAMP default NOW(),
- start_date_time TIMESTAMP default NOW(),
- recurrence varchar(45) default null
- );
-
-
--- ----------------------------------------------------------
--- NAME: FN_SHARED_CONTEXT; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_shared_context (
- id int(11) not null auto_increment,
- create_time timestamp not null,
- context_id character varying(64) not null,
- ckey character varying(128) not null,
- cvalue character varying(1024),
- primary key (id),
- UNIQUE KEY session_key (context_id, ckey) );
-
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_job_details (
-SCHED_NAME VARCHAR(120) not null,
-JOB_NAME VARCHAR(200) not null,
-JOB_GROUP VARCHAR(200) not null,
-DESCRIPTION VARCHAR(250) null,
-JOB_CLASS_NAME VARCHAR(250) not null,
-IS_DURABLE VARCHAR(1) not null,
-IS_NONCONCURRENT VARCHAR(1) not null,
-IS_UPDATE_DATA VARCHAR(1) not null,
-REQUESTS_RECOVERY VARCHAR(1) not null,
-JOB_DATA BLOB null,
-primary key (SCHED_NAME,JOB_NAME,JOB_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_triggers (
-SCHED_NAME VARCHAR(120) not null,
-TRIGGER_NAME VARCHAR(200) not null,
-TRIGGER_GROUP VARCHAR(200) not null,
-JOB_NAME VARCHAR(200) not null,
-JOB_GROUP VARCHAR(200) not null,
-DESCRIPTION VARCHAR(250) null,
-NEXT_FIRE_TIME BIGINT(13) null,
-PREV_FIRE_TIME BIGINT(13) null,
-PRIORITY INTEGER null,
-TRIGGER_STATE VARCHAR(16) not null,
-TRIGGER_TYPE VARCHAR(8) not null,
-START_TIME BIGINT(13) not null,
-END_TIME BIGINT(13) null,
-CALENDAR_NAME VARCHAR(200) null,
-MISFIRE_INSTR SMALLINT(2) null,
-JOB_DATA BLOB null,
-primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
-FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
-REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_simple_triggers (
-SCHED_NAME VARCHAR(120) not null,
-TRIGGER_NAME VARCHAR(200) not null,
-TRIGGER_GROUP VARCHAR(200) not null,
-REPEAT_COUNT BIGINT(7) not null,
-REPEAT_INTERVAL BIGINT(12) not null,
-TIMES_TRIGGERED BIGINT(10) not null,
-primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
-FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_cron_triggers (
-SCHED_NAME VARCHAR(120) not null,
-TRIGGER_NAME VARCHAR(200) not null,
-TRIGGER_GROUP VARCHAR(200) not null,
-CRON_EXPRESSION VARCHAR(120) not null,
-TIME_ZONE_ID VARCHAR(80),
-primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
-FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_simprop_triggers (
- SCHED_NAME VARCHAR(120) not null,
- TRIGGER_NAME VARCHAR(200) not null,
- TRIGGER_GROUP VARCHAR(200) not null,
- STR_PROP_1 VARCHAR(512) null,
- STR_PROP_2 VARCHAR(512) null,
- STR_PROP_3 VARCHAR(512) null,
- INT_PROP_1 INT null,
- INT_PROP_2 INT null,
- LONG_PROP_1 BIGINT null,
- LONG_PROP_2 BIGINT null,
- DEC_PROP_1 NUMERIC(13,4) null,
- DEC_PROP_2 NUMERIC(13,4) null,
- BOOL_PROP_1 VARCHAR(1) null,
- BOOL_PROP_2 VARCHAR(1) null,
- primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
- FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
- REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_blob_triggers (
-SCHED_NAME VARCHAR(120) not null,
-TRIGGER_NAME VARCHAR(200) not null,
-TRIGGER_GROUP VARCHAR(200) not null,
-BLOB_DATA BLOB null,
-primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
-INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
-FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_CALENDARS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_calendars (
-SCHED_NAME VARCHAR(120) not null,
-CALENDAR_NAME VARCHAR(200) not null,
-CALENDAR BLOB not null,
-primary key (SCHED_NAME,CALENDAR_NAME)
-);
-
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_paused_trigger_grps (
-SCHED_NAME VARCHAR(120) not null,
-TRIGGER_GROUP VARCHAR(200) not null,
-primary key (SCHED_NAME,TRIGGER_GROUP)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_fired_triggers (
-SCHED_NAME VARCHAR(120) not null,
-ENTRY_ID VARCHAR(95) not null,
-TRIGGER_NAME VARCHAR(200) not null,
-TRIGGER_GROUP VARCHAR(200) not null,
-INSTANCE_NAME VARCHAR(200) not null,
-FIRED_TIME BIGINT(13) not null,
-SCHED_TIME BIGINT(13) not null,
-PRIORITY INTEGER not null,
-STATE VARCHAR(16) not null,
-JOB_NAME VARCHAR(200) null,
-JOB_GROUP VARCHAR(200) null,
-IS_NONCONCURRENT VARCHAR(1) null,
-REQUESTS_RECOVERY VARCHAR(1) null,
-primary key (SCHED_NAME,ENTRY_ID)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_scheduler_state (
-SCHED_NAME VARCHAR(120) not null,
-INSTANCE_NAME VARCHAR(200) not null,
-LAST_CHECKIN_TIME BIGINT(13) not null,
-CHECKIN_INTERVAL BIGINT(13) not null,
-primary key (SCHED_NAME,INSTANCE_NAME)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_QZ_LOCKS; TYPE: TABLE
--- ----------------------------------------------------------
-create table fn_qz_locks (
-SCHED_NAME VARCHAR(120) not null,
-LOCK_NAME VARCHAR(40) not null,
-primary key (SCHED_NAME,LOCK_NAME)
-);
-
--- ----------------------------------------------------------
--- NAME: FN_MENU_FAVORITES; TYPE: TABLE
--- ----------------------------------------------------------
-
-create table fn_menu_favorites (
- user_id int(11) not null,
- menu_id int(11) not null,
- primary key (user_id,menu_id)
-);
-
--- FACELIFT - Table for Events, News and Resources
-
-create table fn_common_widget_data(
- id int auto_increment,
- category varchar(32),
- href varchar(512),
- title varchar(256),
- content varchar(4096),
- event_date varchar(10), -- YYYY-MM-DD
- sort_order int,
- primary key (id)
-);
-
-create table fn_app_contact_us (
- app_id int(11) not null,
- contact_name varchar(128) default null,
- contact_email varchar(128) default null,
- url varchar(256) default null,
- active_yn varchar(2) default null,
- description varchar(1024) default null,
- primary key (app_id),
- constraint fk_fn_a_con__ref_202_fn_app foreign key (app_id) references fn_app (app_id)
-);
-
--- new 1610.2
-create table fn_pers_user_app_sel (
- id int(11) not null auto_increment,
- user_id int(11) not null,
- app_id int(11) not null,
- status_cd char(1) not null,
- primary key(id),
- constraint fk_1_fn_pers_user_app_sel_fn_user foreign key (user_id) references fn_user (user_id),
- constraint fk_2_fn_pers_user_app_sel_fn_app foreign key (app_id) references fn_app (app_id)
-);
-
--- end new 1610.2
-
--- new 1702 tables/views
- -- 1702 Additions for User Notifications
- -- ----------------------------------------------------------
- -- NAME: ep_notification; TYPE: TABLE
- -- ----------------------------------------------------------
- create table ep_notification (
- notification_ID int(11) primary key not null auto_increment,
- is_for_online_users char(1) default 'N',
- is_for_all_roles char(1) default 'N',
- active_YN char(1) default 'Y',
- msg_header varchar(100),
- msg_description varchar(2000),
- msg_source varchar(50) default 'EP',
- start_time timestamp default now(),
- end_time timestamp null,
- priority int(11),
- creator_ID int(11) null default null,
- created_date timestamp null default null
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_role_notification; TYPE: TABLE
- -- ----------------------------------------------------------
- create table ep_role_notification (
- ID int(11) primary key not null auto_increment,
- notification_ID int(11),
- role_ID int(11),
- recv_user_id int(11) null,
- constraint fk_ep_role_notif_fn_role foreign key (role_ID) references fn_role(role_id),
- constraint fk_ep_role_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_user_notification; TYPE: TABLE
- -- ----------------------------------------------------------
- create table ep_user_notification (
- ID int(11) primary key not null auto_increment,
- User_ID int(11),
- notification_ID int(11),
- is_viewed char(1) default 'N',
- updated_time timestamp default now(),
- constraint fk_ep_urole_notif_fn_user foreign key (User_ID) references fn_user(user_id),
- constraint fk_ep_urole_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_pers_user_app_sort; TYPE: Table
- -- ----------------------------------------------------------
-
- CREATE TABLE ep_pers_user_app_sort (
- id int(11) not null primary key auto_increment,
- user_id int(11) not null,
- sort_pref char(1) not null,
- unique key uk_1_ep_pers_user_app_sort (user_id),
- constraint fk_ep_pers_user_app_sort_fn_user foreign key (user_id) references fn_user(user_id)
- );
- -- ----------------------------------------------------------
- -- NAME: ep_pers_user_app_man_sort; TYPE: Table
- -- ----------------------------------------------------------
-
- CREATE TABLE ep_pers_user_app_man_sort (
- id int(11) not null primary key auto_increment,
- user_id int(11) not null,
- app_id int(11) not null,
- sort_order int(11) not null,
- unique key uk_1_ep_pers_user_app_man_sort (user_id, app_id),
- constraint fk_ep_pers_app_man_sort_fn_user foreign key (user_id) references fn_user(user_id),
- constraint fk_ep_pers_app_man_sort_fn_app foreign key (app_id) references fn_app(app_id)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_widget_catalog; TYPE: Table
- -- ----------------------------------------------------------
-
- CREATE TABLE ep_widget_catalog (
- widget_id int(11) not null auto_increment,
- wdg_name varchar(100) not null default '?',
- service_id int(11) default null,
- wdg_desc varchar(200) default null,
- wdg_file_loc varchar(256) not null default '?',
- all_user_flag char(1) not null default 'N',
- primary key (widget_id)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_widget_catalog_role; TYPE: Table
- -- ----------------------------------------------------------
- create table ep_widget_catalog_role (
- widget_id int(10) not null,
- app_id int(11) default '1',
- role_id int(10) not null,
- key fk_ep_widget_catalog_role_fn_widget (widget_id),
- key fk_ep_widget_catalog_role_ref_fn_role (role_id),
- key fk_ep_widget_catalog_role_app_id (app_id),
- constraint fk_ep_widget_catalog_role_fn_widget foreign key (widget_id) references ep_widget_catalog (widget_id),
- constraint fk_ep_widget_catalog_role_ref_fn_role foreign key (role_id) references fn_role (role_id),
- constraint fk_ep_widget_catalog_role_app_id foreign key (app_id) references fn_app (app_id)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_pers_user_widget_placement; TYPE: Table
- -- ----------------------------------------------------------
- CREATE TABLE ep_pers_user_widget_placement (
- id int(11) not null primary key auto_increment,
- user_id int(11) not null,
- widget_id int(11) not null,
- x int(11) not null,
- y int(11),
- height int(11),
- width int(11),
- unique key uk_1_ep_pers_user_widg_place (user_id, widget_id),
- constraint fk_ep_pers_user_widg_place_fn_user foreign key (user_id) references fn_user(user_id),
- constraint fk_ep_pers_user_widg_place_ep_widg foreign key (widget_id) references ep_widget_catalog(widget_id)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_pers_user_widget_sel; TYPE: TABLE
- -- ----------------------------------------------------------
- CREATE TABLE ep_pers_user_widget_sel (
- id int(11) not null primary key auto_increment,
- user_id int(11) not null,
- widget_id int(11) not null,
- status_cd char(1) not null,
- unique key uk_1_ep_pers_user_widg_sel_user_widg (user_id, widget_id),
- CONSTRAINT fk_1_ep_pers_user_wid_sel_fn_user FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
- CONSTRAINT fk_2_ep_pers_user_wid_sel_ep_wid FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_widget_catalog_files; TYPE: TABLE
- -- ----------------------------------------------------------
- CREATE TABLE ep_widget_catalog_files (
- file_id int(11) not null primary key auto_increment,
- widget_id int(11),
- widget_name VARCHAR(100) NOT NULL,
- framework_js LONGBLOB NULL,
- controller_js LONGBLOB NULL,
- markup_html LONGBLOB NULL,
- widget_css LONGBLOB NULL
- );
-
- -- ----------------------------------------------------------
- -- NAME: fn_role_v; TYPE: VIEW
- -- All roles without an APP_ID are Portal only.
- -- ----------------------------------------------------------
- create view fn_role_v as
- select fn_role.role_id as role_id,
- fn_role.role_name as role_name,
- fn_role.active_yn as active_yn,
- fn_role.priority as priority,
- fn_role.app_id as app_id,
- fn_role.app_role_id as app_role_id
- from fn_role where isnull(fn_role.app_id);
-
--- end new 1702 tables/views
-
--- new 1707 tables/views
-
- -- ----------------------------------------------------------
- -- NAME: ep_user_roles_request; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_user_roles_request (
- req_id int(11) not null primary key auto_increment,
- user_id int(11) not null,
- app_id int(11) not null,
- created_date timestamp default now(),
- updated_date timestamp default now(),
- request_status character varying(50) not null,
- constraint fk_user_roles_req_fn_user foreign key (user_id) references fn_user(user_id),
- constraint fk_user_roles_req_fn_app foreign key (app_id) references fn_app(app_id)
- );
-
-
- -- ----------------------------------------------------------
- -- NAME: ep_user_roles_request_det; TYPE: TABLE
- -- ----------------------------------------------------------
-create table ep_user_roles_request_det (
- id int(11) not null primary key auto_increment,
- req_id int(11) default null,
- requested_role_id int(10) not null,
- request_type character varying(10) not null,
- constraint fk_user_roles_req_fn_req_id foreign key (req_id) references ep_user_roles_request(req_id),
- constraint fk_user_roles_req_fn_role_id foreign key (requested_role_id) references fn_role(role_id)
- );
-
- -- ----------------------------------------------------------
- -- NAME: ep_microservice; TYPE: TABLE
- -- ----------------------------------------------------------
-
-CREATE TABLE ep_microservice (
- id INT(11) NOT NULL AUTO_INCREMENT,
- name VARCHAR(50) NULL DEFAULT NULL,
- description VARCHAR(50) NULL DEFAULT NULL,
- appId INT(11) NULL DEFAULT NULL,
- endpoint_url VARCHAR(200) NULL DEFAULT NULL,
- security_type VARCHAR(50) NULL DEFAULT NULL,
- username VARCHAR(50) NULL DEFAULT NULL,
- password VARCHAR(50) NULL DEFAULT NULL,
- active CHAR(1) NOT NULL DEFAULT 'Y',
- PRIMARY KEY (id),
- CONSTRAINT FK_FN_APP_EP_MICROSERVICE FOREIGN KEY (appId) REFERENCES fn_app (app_id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_microservice_parameter; TYPE: TABLE
- -- ----------------------------------------------------------
-
-CREATE TABLE ep_microservice_parameter (
- id INT(11) NOT NULL AUTO_INCREMENT,
- service_id INT(11) NULL DEFAULT NULL,
- para_key VARCHAR(50) NULL DEFAULT NULL,
- para_value VARCHAR(50) NULL DEFAULT NULL,
- PRIMARY KEY (id),
- CONSTRAINT FK_EP_MICROSERIVCE_EP_MICROSERVICE_PARAMETER FOREIGN KEY (service_id) REFERENCES ep_microservice (id)
-);
-
-
- -- ----------------------------------------------------------
- -- NAME: ep_widget_preview_files; TYPE: TABLE
- -- ----------------------------------------------------------
-
-CREATE TABLE ep_widget_preview_files (
- preview_id INT(11) NOT NULL AUTO_INCREMENT,
- html_file LONGBLOB NULL,
- css_file LONGBLOB NULL,
- javascript_file LONGBLOB NULL,
- framework_file LONGBLOB NULL,
- PRIMARY KEY (preview_id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_widget_microservice; TYPE: TABLE
- -- ----------------------------------------------------------
-
-CREATE TABLE ep_widget_microservice (
- id INT(11) NOT NULL AUTO_INCREMENT,
- widget_id INT(11) NOT NULL DEFAULT '0',
- microservice_id INT(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (id),
- CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_MICROSERVICE FOREIGN KEY (microservice_id) REFERENCES ep_microservice (id),
- CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_WIDGET FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_basic_auth_account; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_basic_auth_account (
- id INT(11) NOT NULL AUTO_INCREMENT,
- ext_app_name VARCHAR(50) NOT NULL,
- username VARCHAR(50) NOT NULL,
- password VARCHAR(50) NOT NULL,
- active_yn char(1) NOT NULL default 'Y',
- PRIMARY KEY (id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_widget_catalog_parameter; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_widget_catalog_parameter (
- id INT(11) NOT NULL AUTO_INCREMENT,
- widget_id INT(11) NOT NULL,
- user_id INT(11) NOT NULL,
- param_id INT(11) NOT NULL,
- user_value VARCHAR(50) NULL,
- PRIMARY KEY (id),
- CONSTRAINT EP_FN_USER_WIDGET_PARAMETER_FK FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
- CONSTRAINT EP_WIDGET_CATALOG_WIDGET_PARAMETER_FK FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id),
- CONSTRAINT EP_PARAMETER_ID_WIDGET_PARAMETER_FK FOREIGN KEY (param_id) REFERENCES ep_microservice_parameter (id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_web_analytics_source; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_web_analytics_source(
- resource_id int(11) NOT NULL auto_increment,
- app_id int(11) NOT NULL,
- report_source varchar(500),
- report_name varchar(500),
- PRIMARY KEY (resource_id),
- FOREIGN KEY (app_id) REFERENCES fn_app(app_id)
-);
-
- -- Machine Learning Tables
- -- ----------------------------------------------------------
- -- NAME: ep_ml_model; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_ml_model(
- time_stamp timestamp default now(),
- group_id int(11) NOT NULL,
- model longblob,
- PRIMARY KEY (time_stamp,group_id)
-);
- -- ----------------------------------------------------------
- -- NAME: ep_ml_rec; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_ml_rec(
- time_stamp timestamp default now(),
- org_user_id varchar(20) NOT NULL,
- rec varchar(4000) DEFAULT NULL,
- PRIMARY KEY (time_stamp,org_user_id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_ml_user; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_ml_user(
- time_stamp timestamp default now(),
- org_user_id varchar(20) NOT NULL,
- group_id int(11) NOT NULL,
- PRIMARY KEY (time_stamp,org_user_id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_endpoints; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_endpoints (
- id INT(11) NOT NULL AUTO_INCREMENT,
- url VARCHAR(50) NOT NULL,
- PRIMARY KEY (id)
-);
-
- -- ----------------------------------------------------------
- -- NAME: ep_endpoints_basic_auth_account; TYPE: TABLE
- -- ----------------------------------------------------------
-
-create table ep_endpoints_basic_auth_account (
- id INT(11) NOT NULL AUTO_INCREMENT,
- ep_id INT(11) DEFAULT NULL,
- account_id INT(11) DEFAULT NULL,
- PRIMARY KEY (id),
- CONSTRAINT ep_endpoints_basic_auth_account_account_id_fk FOREIGN KEY (account_id) REFERENCES ep_basic_auth_account (id),
- CONSTRAINT ep_endpoints_basic_auth_account_ep_id_fk FOREIGN KEY (ep_id) REFERENCES ep_endpoints (id)
-
-);
-
--- end new 1707 tables/views
-
--- ----------------------------------------------------------
--- NAME: QUARTZ TYPE: INDEXES
--- ----------------------------------------------------------
-create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
-create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
-create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
-create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
-create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
-create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
-create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
-create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
-create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
-create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
-create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
-create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
-create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
-create index idx_fn_qz_t_nft_st_misfire_grp on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_group,trigger_state);
-create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
-create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
-create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
-create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
-create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
-create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
-
-
--- ------------------ create view section
---
--- name: v_url_access; type: view
---
-create view v_url_access as
- select distinct m.action as url,
- m.function_cd
- from fn_menu m
- where (m.action is not null)
-union
- select distinct t.action as url,
- t.function_cd
- from fn_tab t
- where (t.action is not null)
-union
- select r.restricted_url as url,
- r.function_cd
- from fn_restricted_url r;
-
--- ------------------ alter table add constraint primary key section
---
--- name: cr_favorite_reports_user_idrep_id; type: constraint
---
-alter table cr_favorite_reports
- add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
---
--- name: cr_folder_folder_id; type: constraint
---
-alter table cr_folder
- add constraint cr_folder_folder_id primary key (folder_id);
---
--- name: cr_folder_access_folder_access_id; type: constraint
---
-alter table cr_folder_access
- add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
---
--- name: cr_hist_user_map_hist_iduser_id; type: constraint
---
-alter table cr_hist_user_map
- add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
---
--- name: cr_lu_file_type_lookup_id; type: constraint
---
-alter table cr_lu_file_type
- add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
---
--- name: cr_raptor_action_img_image_id; type: constraint
---
-alter table cr_raptor_action_img
- add constraint cr_raptor_action_img_image_id primary key (image_id);
---
--- name: cr_raptor_pdf_img_image_id; type: constraint
---
-alter table cr_raptor_pdf_img
- add constraint cr_raptor_pdf_img_image_id primary key (image_id);
---
--- name: cr_remote_schema_info_schema_prefix; type: constraint
---
-alter table cr_remote_schema_info
- add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
---
--- name: cr_report_rep_id; type: constraint
---
-alter table cr_report
- add constraint cr_report_rep_id primary key (rep_id);
---
--- name: cr_report_access_rep_idorder_no; type: constraint
---
-alter table cr_report_access
- add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
---
--- name: cr_report_email_sent_log_log_id; type: constraint
---
-alter table cr_report_email_sent_log
- add constraint cr_report_email_sent_log_log_id primary key (log_id);
---
--- name: cr_report_file_history_hist_id; type: constraint
---
-alter table cr_report_file_history
- add constraint cr_report_file_history_hist_id primary key (hist_id);
---
--- name: cr_report_schedule_schedule_id; type: constraint
---
-alter table cr_report_schedule
- add constraint cr_report_schedule_schedule_id primary key (schedule_id);
---
--- name: cr_report_schedule_users_schedule_idrep_iduser_idorder_no; type: constraint
---
-alter table cr_report_schedule_users
- add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
---
--- name: cr_report_template_map_report_id; type: constraint
---
-alter table cr_report_template_map
- add constraint cr_report_template_map_report_id primary key (report_id);
---
--- name: cr_table_role_table_namerole_id; type: constraint
---
-alter table cr_table_role
- add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
---
--- name: cr_table_source_table_name; type: constraint
---
-alter table cr_table_source
- add constraint cr_table_source_table_name primary key (table_name);
---
--- name: fn_audit_action_audit_action_id; type: constraint
---
-alter table fn_audit_action
- add constraint fn_audit_action_audit_action_id primary key (audit_action_id);
---
---
--- name: fk_fn_audit_ref_205_fn_lu_ac; type: constraint
---
-alter table fn_audit_log
- add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
---
--- name: fk_fn_role__ref_201_fn_funct; type: constraint
---
-alter table fn_role_function
- add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
---
--- name: fn_chat_logs_chat_log_id; type: constraint
---
-alter table fn_chat_logs
- add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
---
--- name: fn_chat_room_chat_room_id; type: constraint
---
-alter table fn_chat_room
- add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
---
--- name: fn_chat_users_id; type: constraint
---
-alter table fn_chat_users
- add constraint fn_chat_users_id primary key (id);
---
--- name: fn_lu_alert_method_alert_method_cd; type: constraint
---
-alter table fn_lu_alert_method
- add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
---
--- name: fn_lu_broadcast_site_broadcast_site_cd; type: constraint
---
-alter table fn_lu_broadcast_site
- add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
---
--- name: fn_lu_priority_priority_id; type: constraint
---
-alter table fn_lu_priority
- add constraint fn_lu_priority_priority_id primary key (priority_id);
---
--- name: fn_lu_role_type_role_type_id; type: constraint
---
-alter table fn_lu_role_type
- add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
---
--- name: fn_lu_tab_set_tab_set_cd; type: constraint
---
-alter table fn_lu_tab_set
- add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
---
--- name: fn_lu_timezone_timezone_id; type: constraint
---
-alter table fn_lu_timezone
- add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
---
--- name: fn_org_org_id; type: constraint
---
-alter table fn_org
- add constraint fn_org_org_id primary key (org_id);
---
--- name: fn_restricted_url_restricted_urlfunction_cd; type: constraint
---
-alter table fn_restricted_url
- add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
---
--- name: fn_role_composite_parent_role_idchild_role_id; type: constraint
---
-alter table fn_role_composite
- add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
---
--- name: fn_role_function_role_idfunction_cd; type: constraint
---
-alter table fn_role_function
- add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
---
--- name: fn_tab_tab_cd; type: constraint
---
-alter table fn_tab
- add constraint fn_tab_tab_cd primary key (tab_cd);
---
--- name: fn_tab_selected_selected_tab_cdtab_uri; type: constraint
---
-alter table fn_tab_selected
- add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
---
--- name: fn_user_pseudo_role_pseudo_role_iduser_id; type: constraint
---
-alter table fn_user_pseudo_role
- add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
---
--- name: fn_user_role_user_idrole_id; type: constraint
---
-alter table fn_user_role
- add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
--- ------------------ create index section
---
--- name: cr_report_create_idpublic_yntitle; type: index
---
-create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
---
--- name: cr_table_join_dest_table_name; type: index
---
-create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
---
--- name: cr_table_join_src_table_name; type: index
---
-create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
---
--- name: fn_audit_log_activity_cd; type: index
---
-create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
---
--- name: fn_audit_log_user_id; type: index
---
-create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
---
--- name: fn_menu_function_cd; type: index
---
-create index fn_menu_function_cd using btree on fn_menu (function_cd);
---
--- name: fn_org_access_cd; type: index
---
-create index fn_org_access_cd using btree on fn_org (access_cd);
---
--- name: fn_role_function_function_cd; type: index
---
-create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
---
--- name: fn_role_function_role_id; type: index
---
-create index fn_role_function_role_id using btree on fn_role_function (role_id);
---
--- name: fn_user_address_id; type: index
---
-create index fn_user_address_id using btree on fn_user (address_id);
---
--- name: fn_user_alert_method_cd; type: index
---
-create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd);
---
--- name: fn_user_hrid; type: index
---
-create unique index fn_user_hrid using btree on fn_user (hrid);
---
--- name: fn_user_login_id; type: index
---
-create unique index fn_user_login_id using btree on fn_user (login_id);
---
--- name: fn_user_org_id; type: index
---
-create index fn_user_org_id using btree on fn_user (org_id);
---
--- name: fn_user_role_role_id; type: index
---
-create index fn_user_role_role_id using btree on fn_user_role (role_id);
---
--- name: fn_user_role_user_id; type: index
---
-create index fn_user_role_user_id using btree on fn_user_role (user_id);
---
--- name: fk_fn_user__ref_178_fn_app_idx; type: index
---
-create index fk_fn_user__ref_178_fn_app_idx on fn_user_role (app_id);
- --
- -- name: fn_role_name_app_id_idx; type: index
- --
- create unique index fn_role_name_app_id_idx using btree on fn_role (role_name,app_id);
-
--- new for 1707
-
-create index ep_notif_recv_user_id_idx using btree on ep_role_notification (recv_user_id);
-
--- end new for 1707
-
--- ------------------ alter table add constraint foreign key section
---
--- name: fk_fn_user__ref_178_fn_app; type: fk constraint
---
-alter table fn_user_role
- add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
---
--- name: fk_cr_repor_ref_14707_cr_repor; type: fk constraint
---
-alter table cr_report_schedule
- add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
---
--- name: fk_cr_repor_ref_14716_cr_repor; type: fk constraint
---
-alter table cr_report_schedule_users
- add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
---
--- name: fk_cr_repor_ref_17645_cr_repor; type: fk constraint
---
-alter table cr_report_log
- add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
---
--- name: fk_cr_repor_ref_8550_cr_repor; type: fk constraint
---
-alter table cr_report_access
- add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
---
--- name: fk_cr_report_rep_id; type: fk constraint
---
-alter table cr_report_email_sent_log
- add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
---
--- name: fk_cr_table_ref_311_cr_tab; type: fk constraint
---
-alter table cr_table_join
- add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
---
--- name: fk_cr_table_ref_315_cr_tab; type: fk constraint
---
-alter table cr_table_join
- add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
---
--- name: fk_cr_table_ref_32384_cr_table; type: fk constraint
---
-alter table cr_table_role
- add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
---
--- name: fk_fn_tab_function_cd; type: fk constraint
---
-alter table fn_tab
- add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
---
--- name: fk_fn_tab_selected_tab_cd; type: fk constraint
---
-alter table fn_tab_selected
- add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
---
--- name: fk_fn_tab_set_cd; type: fk constraint
---
-alter table fn_tab
- add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
---
--- name: fk_fn_user_ref_110_fn_org; type: fk constraint
---
-alter table fn_user
- add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id);
---
--- name: fk_fn_user_ref_123_fn_lu_al; type: fk constraint
---
-alter table fn_user
- add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd);
---
--- name: fk_fn_user_ref_197_fn_user; type: fk constraint
---
- alter table fn_user
- add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id);
---
--- name: fk_fn_user_ref_198_fn_user; type: fk constraint
---
-alter table fn_user
- add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id);
---
--- name: fk_fn_user_ref_199_fn_user; type: fk constraint
---
-alter table fn_user
- add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id);
---
--- name: fk_parent_key_cr_folder; type: fk constraint
---
-alter table cr_folder
- add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
---
--- name: fk_pseudo_role_pseudo_role_id; type: fk constraint
---
-alter table fn_user_pseudo_role
- add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
---
--- name: fk_pseudo_role_user_id; type: fk constraint
---
-alter table fn_user_pseudo_role
- add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
---
--- name: fk_restricted_url_function_cd; type: fk constraint
---
-alter table fn_restricted_url
- add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
---
--- name: fk_timezone; type: fk constraint
---
-alter table fn_user
- add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id);
---
--- name: sys_c0014614; type: fk constraint
---
-alter table cr_report_file_history
- add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
---
--- name: sys_c0014615; type: fk constraint
---
-alter table cr_report_file_history
- add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
---
--- name: sys_c0014616; type: fk constraint
---
-alter table cr_hist_user_map
- add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
---
--- name: sys_c0014617; type: fk constraint
---
-alter table cr_hist_user_map
- add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);
---
--- name: sys_c0014618; type: fk constraint
---
-alter table fn_menu_favorites
-add constraint sys_c0014618 foreign key (user_id) references fn_user(user_id);
-
---
--- name: sys_c0014619; type: fk constraint
---
-alter table fn_menu_favorites
-add constraint sys_c0014619 foreign key (menu_id) references fn_menu_functional(menu_id);
-
-commit;
\ No newline at end of file
Code Review / portal.git / blobdiff