1 -- ---------------------------------------------------------------------------------------------------------------
2 -- This is the 1707 Open Source Version of Ecomp Portal database called portal
3 -- based on 1610.2 Open Source Version and 1702 Version of Ecomp Portal database called portal from
4 -- Branchfeature/1610.2_OpenSource/ecomp_portal_core ecomp-portal-BE ecomp-portal-resources sql scripts
5 -- includes new additions for the 1707 release
6 -- Integrated Notifications. Web Analytics, Basic Authentication and Widgets
8 -- note to : database admin, set the mysql system variable called lower_case_table_names
9 -- it can be set 3 different ways:
10 -- command-line options (cmd-line),
11 -- options valid in configuration files (option file), or
12 -- server system variables (system var).
14 -- it needs to be set to 1, then table names are stored in lowercase on disk and comparisons are not case sensitive.
15 -- MySql/MariaDB Version compatibility information
16 -- bash-4.2$ mysql --version – cluster version
17 -- mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1
19 -- All versions newer or older than these DO NOT necessarily mean they are compatible.
20 -- -----------------------------------------------------------------------------------------------------------------
23 set foreign_key_checks=1;
25 create database portal;
29 -- ------------------ create table section
31 -- name: cr_favorite_reports; type: table
33 create table cr_favorite_reports (
34 user_id integer not null,
35 rep_id integer not null
38 -- name: cr_filehist_log; type: table
40 create table cr_filehist_log (
41 schedule_id numeric(11,0) not null,
42 url character varying(4000),
43 notes character varying(3500),
47 -- name: cr_folder; type: table
49 create table cr_folder (
50 folder_id integer not null,
51 folder_name character varying(50) not null,
52 descr character varying(500),
53 create_id integer not null,
54 create_date timestamp not null,
55 parent_folder_id integer,
56 public_yn character varying(1) default 'n' not null
59 -- name: cr_folder_access; type: table
61 create table cr_folder_access (
62 folder_access_id numeric(11,0) not null,
63 folder_id numeric(11,0) not null,
64 order_no numeric(11,0) not null,
65 role_id numeric(11,0),
66 user_id numeric(11,0),
67 read_only_yn character varying(1) default 'n' not null
70 -- name: cr_hist_user_map; type: table
72 create table cr_hist_user_map (
73 hist_id int(11) not null,
74 user_id int(11) not null
77 -- name: cr_lu_file_type; type: table
79 create table cr_lu_file_type (
80 lookup_id numeric(2,0) not null,
81 lookup_descr character varying(255) not null,
82 active_yn character(1) default 'y',
83 error_code numeric(11,0)
86 -- name: cr_raptor_action_img; type: table
88 create table cr_raptor_action_img (
89 image_id character varying(100) not null,
90 image_loc character varying(400)
93 -- name: cr_raptor_pdf_img; type: table
95 create table cr_raptor_pdf_img (
96 image_id character varying(100) not null,
97 image_loc character varying(400)
100 -- name: cr_remote_schema_info; type: table
102 create table cr_remote_schema_info (
103 schema_prefix character varying(5) not null,
104 schema_desc character varying(75) not null,
105 datasource_type character varying(100)
108 -- name: cr_report; type: table
110 create table cr_report (
111 rep_id numeric(11,0) not null,
112 title character varying(100) not null,
113 descr character varying(255),
114 public_yn character varying(1) default 'n' not null,
116 create_id numeric(11,0),
117 create_date timestamp default now(),
118 maint_id numeric(11,0),
119 maint_date timestamp default now(),
120 menu_id character varying(500),
121 menu_approved_yn character varying(1) default 'n' not null,
122 owner_id numeric(11,0),
123 folder_id integer default 0,
124 dashboard_type_yn character varying(1) default 'n',
125 dashboard_yn character varying(1) default 'n'
128 -- name: cr_report_access; type: table
130 create table cr_report_access (
131 rep_id numeric(11,0) not null,
132 order_no numeric(11,0) not null,
133 role_id numeric(11,0),
134 user_id numeric(11,0),
135 read_only_yn character varying(1) default 'n' not null
138 -- name: cr_report_dwnld_log; type: table
140 create table cr_report_dwnld_log (
141 user_id numeric(11,0) not null,
142 rep_id integer not null,
143 file_name character varying(100) not null,
144 dwnld_start_time timestamp default now() not null,
145 record_ready_time timestamp default now(),
146 filter_params character varying(2000)
149 -- name: cr_report_email_sent_log; type: table
151 create table cr_report_email_sent_log (
152 log_id integer not null,
153 schedule_id numeric(11,0),
154 gen_key character varying(25) not null,
155 rep_id numeric(11,0) not null,
156 user_id numeric(11,0),
157 sent_date timestamp default now(),
158 access_flag character varying(1) default 'y' not null,
159 touch_date timestamp default now()
162 -- name: cr_report_file_history; type: table
164 create table cr_report_file_history (
165 hist_id int(11) not null,
166 sched_user_id numeric(11,0) not null,
167 schedule_id numeric(11,0) not null,
168 user_id numeric(11,0) not null,
169 rep_id numeric(11,0),
171 recurrence character varying(50),
172 file_type_id numeric(2,0),
173 file_name character varying(80),
175 file_size numeric(11,0),
176 raptor_url character varying(4000),
177 error_yn character(1) default 'n',
178 error_code numeric(11,0),
179 deleted_yn character(1) default 'n',
180 deleted_by numeric(38,0)
183 -- name: cr_report_log; type: table
185 create table cr_report_log (
186 rep_id numeric(11,0) not null,
187 log_time timestamp not null,
188 user_id numeric(11,0) not null,
189 action character varying(2000) not null,
190 action_value character varying(50),
191 form_fields character varying(4000)
194 -- name: cr_report_schedule; type: table
196 create table cr_report_schedule (
197 schedule_id numeric(11,0) not null,
198 sched_user_id numeric(11,0) not null,
199 rep_id numeric(11,0) not null,
200 enabled_yn character varying(1) not null,
201 start_date timestamp default now(),
202 end_date timestamp default now(),
203 run_date timestamp default now(),
204 recurrence character varying(50),
205 conditional_yn character varying(1) not null,
206 condition_sql character varying(4000),
207 notify_type integer default 0,
208 max_row integer default 1000,
209 initial_formfields character varying(3500),
210 processed_formfields character varying(3500),
211 formfields character varying(3500),
212 condition_large_sql text,
213 encrypt_yn character(1) default 'n',
214 attachment_yn character(1) default 'y'
217 -- name: cr_report_schedule_users; type: table
219 create table cr_report_schedule_users (
220 schedule_id numeric(11,0) not null,
221 rep_id numeric(11,0) not null,
222 user_id numeric(11,0) not null,
223 role_id numeric(11,0),
224 order_no numeric(11,0) not null
227 -- name: cr_report_template_map; type: table
229 create table cr_report_template_map (
230 report_id integer not null,
231 template_file character varying(200)
234 -- name: cr_schedule_activity_log; type: table
236 create table cr_schedule_activity_log (
237 schedule_id numeric(11,0) not null,
238 url character varying(4000),
239 notes character varying(2000),
243 -- name: cr_table_join; type: table
245 create table cr_table_join (
246 src_table_name character varying(30) not null,
247 dest_table_name character varying(30) not null,
248 join_expr character varying(500) not null
251 -- name: cr_table_role; type: table
253 create table cr_table_role (
254 table_name character varying(30) not null,
255 role_id numeric(11,0) not null
258 -- name: cr_table_source; type: table
260 create table cr_table_source (
261 table_name character varying(30) not null,
262 display_name character varying(30) not null,
263 pk_fields character varying(200),
264 web_view_action character varying(50),
265 large_data_source_yn character varying(1) default 'n' not null,
266 filter_sql character varying(4000),
267 source_db character varying(50)
270 -- name: fn_lu_timezone; type: table
272 create table fn_lu_timezone (
273 timezone_id int(11) not null,
274 timezone_name character varying(100) not null,
275 timezone_value character varying(100) not null
278 create table fn_user (
279 user_id int(11) not null primary key auto_increment,
282 first_name character varying(50),
283 middle_name character varying(50),
284 last_name character varying(50),
285 phone character varying(25),
286 fax character varying(25),
287 cellular character varying(25),
288 email character varying(50),
289 address_id numeric(11,0),
290 alert_method_cd character varying(10),
291 hrid character varying(20),
292 org_user_id CHARACTER VARYING(20),
293 org_code character varying(30),
294 login_id character varying(25),
295 login_pwd character varying(100),
296 last_login_date timestamp,
297 active_yn character varying(1) default 'y' not null,
299 created_date timestamp default now(),
301 modified_date timestamp default now(),
302 is_internal_yn character(1) default 'n' not null,
303 address_line_1 character varying(100),
304 address_line_2 character varying(100),
305 city character varying(50),
306 state_cd character varying(3),
307 zip_code character varying(11),
308 country_cd character varying(3),
309 location_clli character varying(8),
310 org_manager_userid CHARACTER VARYING(20),
311 company character varying(100),
312 department_name character varying(100),
313 job_title character varying(100),
315 department character varying(25),
316 business_unit character varying(25),
317 business_unit_name character varying(100),
318 cost_center character varying(25),
319 fin_loc_code character varying(10),
320 silo_status character varying(10)
323 -- name: fn_role; type: table
325 create table fn_role (
326 role_id int(11) not null primary key auto_increment,
327 role_name character varying(50) not null,
328 active_yn character varying(1) default 'y' not null,
329 priority numeric(4,0),
330 app_id int(11) default null,
331 app_role_id int(11) default null
335 -- name: fn_audit_action; type: table
337 create table fn_audit_action (
338 audit_action_id integer not null,
339 class_name character varying(500) not null,
340 method_name character varying(50) not null,
341 audit_action_cd character varying(20) not null,
342 audit_action_desc character varying(200),
343 active_yn character varying(1)
346 -- name: fn_audit_action_log; type: table
348 create table fn_audit_action_log (
349 audit_log_id integer not null primary key auto_increment,
350 audit_action_cd character varying(200),
351 action_time timestamp,
352 user_id numeric(11,0),
353 class_name character varying(100),
354 method_name character varying(50),
355 success_msg character varying(20),
356 error_msg character varying(500)
359 -- name: fn_lu_activity; type: table
361 create table fn_lu_activity (
362 activity_cd character varying(50) not null primary key,
363 activity character varying(50) not null
366 -- name: fn_audit_log; type: table
368 create table fn_audit_log (
369 log_id int(11) not null primary key auto_increment,
370 user_id int(11) not null,
371 activity_cd character varying(50) not null,
372 audit_date timestamp default now() not null,
373 comments character varying(1000),
374 affected_record_id_bk character varying(500),
375 affected_record_id character varying(4000),
376 constraint fk_fn_audit_ref_209_fn_user foreign key (user_id) references fn_user(user_id)
379 -- name: fn_broadcast_message; type: table
381 create table fn_broadcast_message (
382 message_id int(11) not null primary key auto_increment,
383 message_text character varying(1000) not null,
384 message_location_id numeric(11,0) not null,
385 broadcast_start_date timestamp not null default now(),
386 broadcast_end_date timestamp not null default now(),
387 active_yn character(1) default 'y' not null,
388 sort_order numeric(4,0) not null,
389 broadcast_site_cd character varying(50)
392 -- name: fn_chat_logs; type: table
394 create table fn_chat_logs (
395 chat_log_id integer not null,
396 chat_room_id integer,
398 message character varying(1000),
399 message_date_time timestamp
402 -- name: fn_chat_room; type: table
404 create table fn_chat_room (
405 chat_room_id integer not null,
406 name character varying(50) not null,
407 description character varying(500),
409 created_date timestamp default now(),
410 updated_date timestamp default now()
413 -- name: fn_chat_users; type: table
415 create table fn_chat_users (
416 chat_room_id integer,
418 last_activity_date_time timestamp,
419 chat_status character varying(20),
423 -- name: fn_datasource; type: table
425 create table fn_datasource (
426 id integer not null primary key auto_increment,
427 name character varying(50),
428 driver_name character varying(256),
429 server character varying(256),
431 user_name character varying(256),
432 password character varying(256),
433 url character varying(256),
434 min_pool_size integer,
435 max_pool_size integer,
437 ds_type character varying(20)
440 -- name: fn_function; type: table
442 create table fn_function (
443 function_cd character varying(30) not null primary key,
444 function_name character varying(50) not null
447 -- name: fn_lu_alert_method; type: table
449 create table fn_lu_alert_method (
450 alert_method_cd character varying(10) not null,
451 alert_method character varying(50) not null
454 -- name: fn_lu_broadcast_site; type: table
456 create table fn_lu_broadcast_site (
457 broadcast_site_cd character varying(50) not null,
458 broadcast_site_descr character varying(100)
461 -- name: fn_lu_menu_set; type: table
463 create table fn_lu_menu_set (
464 menu_set_cd character varying(10) not null primary key,
465 menu_set_name character varying(50) not null
468 -- name: fn_lu_priority; type: table
470 create table fn_lu_priority (
471 priority_id numeric(11,0) not null,
472 priority character varying(50) not null,
473 active_yn character(1) not null,
474 sort_order numeric(5,0)
477 -- name: fn_lu_role_type; type: table
479 create table fn_lu_role_type (
480 role_type_id numeric(11,0) not null,
481 role_type character varying(50) not null
484 -- name: fn_lu_tab_set; type: table
486 create table fn_lu_tab_set (
487 tab_set_cd character varying(30) not null,
488 tab_set_name character varying(50) not null
491 -- name: fn_menu; type: table
493 create table fn_menu (
494 menu_id int(11) not null primary key auto_increment,
495 label character varying(100),
497 sort_order numeric(4,0),
498 action character varying(200),
499 function_cd character varying(30),
500 active_yn character varying(1) default 'y' not null,
501 servlet character varying(50),
502 query_string character varying(200),
503 external_url character varying(200),
504 target character varying(25),
505 menu_set_cd character varying(10) default 'app',
506 separator_yn character(1) default 'n',
507 image_src character varying(100),
508 constraint fk_fn_menu_ref_196_fn_menu foreign key (parent_id) references fn_menu(menu_id),
509 constraint fk_fn_menu_menu_set_cd foreign key (menu_set_cd) references fn_lu_menu_set(menu_set_cd),
510 constraint fk_fn_menu_ref_223_fn_funct foreign key (function_cd) references fn_function(function_cd)
513 -- name: fn_org; type: table
515 create table fn_org (
516 org_id int(11) not null,
517 org_name character varying(50) not null,
518 access_cd character varying(10)
521 -- name: fn_restricted_url; type: table
523 create table fn_restricted_url (
524 restricted_url character varying(250) not null,
525 function_cd character varying(30) not null
528 -- name: fn_role_composite; type: table
530 create table fn_role_composite (
531 parent_role_id int(11) not null,
532 child_role_id int(11) not null,
533 constraint fk_fn_role_composite_child foreign key (child_role_id) references fn_role(role_id),
534 constraint fk_fn_role_composite_parent foreign key (parent_role_id) references fn_role(role_id)
537 -- name: fn_role_function; type: table
539 create table fn_role_function (
540 role_id int(11) not null,
541 function_cd character varying(30) not null,
542 constraint fk_fn_role__ref_198_fn_role foreign key (role_id) references fn_role(role_id)
545 -- name: fn_tab; type: table
547 create table fn_tab (
548 tab_cd character varying(30) not null,
549 tab_name character varying(50) not null,
550 tab_descr character varying(100),
551 action character varying(100) not null,
552 function_cd character varying(30) not null,
553 active_yn character(1) not null,
554 sort_order numeric(11,0) not null,
555 parent_tab_cd character varying(30),
556 tab_set_cd character varying(30)
559 -- name: fn_tab_selected; type: table
561 create table fn_tab_selected (
562 selected_tab_cd character varying(30) not null,
563 tab_uri character varying(40) not null
566 -- name: fn_user_pseudo_role; type: table
568 create table fn_user_pseudo_role (
569 pseudo_role_id int(11) not null,
570 user_id int(11) not null
573 -- name: fn_user_role; type: table
575 create table fn_user_role (
576 user_id int(10) not null,
577 role_id int(10) not null,
578 priority numeric(4,0),
579 app_id int(11) default 2,
580 constraint fk_fn_user__ref_172_fn_user foreign key (user_id) references fn_user(user_id),
581 constraint fk_fn_user__ref_175_fn_role foreign key (role_id) references fn_role(role_id)
584 -- name: schema_info; type: table
586 create table schema_info (
587 SCHEMA_ID CHARACTER VARYING(25) not null,
588 SCHEMA_DESC CHARACTER VARYING(75) not null,
589 DATASOURCE_TYPE CHARACTER VARYING(100),
590 CONNECTION_URL VARCHAR(200) not null,
591 USER_NAME VARCHAR(45) not null,
592 PASSWORD VARCHAR(45) null default null,
593 DRIVER_CLASS VARCHAR(100) not null,
594 MIN_POOL_SIZE INT not null,
595 MAX_POOL_SIZE INT not null,
596 IDLE_CONNECTION_TEST_PERIOD INT not null
599 -- ----------------------------------------------------------
600 -- name: fn_app; type: table
601 -- ----------------------------------------------------------
602 create table fn_app (
603 app_id int(11) primary key not null auto_increment,
604 app_name varchar(100) not null default '?',
605 app_image_url varchar(256) default null,
606 app_description varchar(512) default null,
607 app_notes varchar(4096) default null,
608 app_url varchar(256) default null,
609 app_alternate_url varchar(256) default null,
610 app_rest_endpoint varchar(2000) default null,
611 ml_app_name varchar(50) not null default '?',
612 ml_app_admin_id varchar(7) not null default '?',
613 mots_id int(11) default null,
614 app_password varchar(256) not null default '?',
615 open char(1) default 'n',
616 enabled char(1) default 'y',
617 thumbnail mediumblob null default null,
618 app_username varchar(50),
619 ueb_key varchar(256) default null,
620 ueb_secret varchar(256) default null,
621 ueb_topic_name varchar(256) default null,
622 app_type int(11) not null default 1
625 -- ------------------ functional menu tables -------------------
627 -- table structure for table fn_menu_functional
629 create table fn_menu_functional (
630 menu_id int(11) not null auto_increment,
631 column_num int(2) not null,
632 text varchar(100) not null,
633 parent_menu_id int(11) default null,
634 url varchar(128) not null default '',
635 active_yn varchar(1) not null default 'y',
636 image_src varchar(100) default null,
637 primary key (menu_id),
638 key fk_fn_menu_func_parent_menu_id_idx (parent_menu_id),
639 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
642 -- table structure for table fn_menu_functional_ancestors
645 create table fn_menu_functional_ancestors (
646 id int(11) not null auto_increment,
647 menu_id int(11) not null,
648 ancestor_menu_id int(11) not null,
649 depth int(2) not null,
651 key fk_fn_menu_func_anc_menu_id_idx (menu_id),
652 key fk_fn_menu_func_anc_anc_menu_id_idx (ancestor_menu_id),
653 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,
654 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
657 -- table structure for table fn_menu_functional_roles
659 create table fn_menu_functional_roles (
660 id int(11) not null auto_increment,
661 menu_id int(11) not null,
662 app_id int(11) not null,
663 role_id int(10) not null,
665 key fk_fn_menu_func_roles_menu_id_idx (menu_id),
666 key fk_fn_menu_func_roles_app_id_idx (app_id),
667 key fk_fn_menu_func_roles_role_id_idx (role_id),
668 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,
669 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,
670 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
672 -- ----------------------------------------------------------
673 -- NAME: FN_WORKFLOW; TYPE: TABLE
674 -- ----------------------------------------------------------
675 create table fn_workflow (
676 id mediumint(9) not null auto_increment,
677 name varchar(20) not null,
678 description varchar(500) default null,
679 run_link varchar(300) default null,
680 suspend_link varchar(300) default null,
681 modified_link varchar(300) default null,
682 active_yn varchar(300) default null,
683 created varchar(300) default null,
684 created_by int(11) default null,
685 modified varchar(300) default null,
686 modified_by int(11) default null,
687 workflow_key varchar(50) default null,
689 UNIQUE KEY name (name)
693 -- ----------------------------------------------------------
694 -- NAME: FN_SCHEDULE_WORKFLOWS; TYPE: TABLE
695 -- ----------------------------------------------------------
696 create table fn_schedule_workflows (
697 id_schedule_workflows bigint(25) primary key not null auto_increment,
698 workflow_server_url varchar(45) default null,
699 workflow_key varchar(45) not null,
700 workflow_arguments varchar(45) default null,
701 startDateTimeCron varchar(45) default null,
702 endDateTime TIMESTAMP default NOW(),
703 start_date_time TIMESTAMP default NOW(),
704 recurrence varchar(45) default null
708 -- ----------------------------------------------------------
709 -- NAME: FN_SHARED_CONTEXT; TYPE: TABLE
710 -- ----------------------------------------------------------
711 create table fn_shared_context (
712 id int(11) not null auto_increment,
713 create_time timestamp not null,
714 context_id character varying(64) not null,
715 ckey character varying(128) not null,
716 cvalue character varying(1024),
718 UNIQUE KEY session_key (context_id, ckey) );
721 -- ----------------------------------------------------------
722 -- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
723 -- ----------------------------------------------------------
724 create table fn_qz_job_details (
725 SCHED_NAME VARCHAR(120) not null,
726 JOB_NAME VARCHAR(200) not null,
727 JOB_GROUP VARCHAR(200) not null,
728 DESCRIPTION VARCHAR(250) null,
729 JOB_CLASS_NAME VARCHAR(250) not null,
730 IS_DURABLE VARCHAR(1) not null,
731 IS_NONCONCURRENT VARCHAR(1) not null,
732 IS_UPDATE_DATA VARCHAR(1) not null,
733 REQUESTS_RECOVERY VARCHAR(1) not null,
735 primary key (SCHED_NAME,JOB_NAME,JOB_GROUP)
738 -- ----------------------------------------------------------
739 -- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
740 -- ----------------------------------------------------------
741 create table fn_qz_triggers (
742 SCHED_NAME VARCHAR(120) not null,
743 TRIGGER_NAME VARCHAR(200) not null,
744 TRIGGER_GROUP VARCHAR(200) not null,
745 JOB_NAME VARCHAR(200) not null,
746 JOB_GROUP VARCHAR(200) not null,
747 DESCRIPTION VARCHAR(250) null,
748 NEXT_FIRE_TIME BIGINT(13) null,
749 PREV_FIRE_TIME BIGINT(13) null,
750 PRIORITY INTEGER null,
751 TRIGGER_STATE VARCHAR(16) not null,
752 TRIGGER_TYPE VARCHAR(8) not null,
753 START_TIME BIGINT(13) not null,
754 END_TIME BIGINT(13) null,
755 CALENDAR_NAME VARCHAR(200) null,
756 MISFIRE_INSTR SMALLINT(2) null,
758 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
759 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
760 REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
763 -- ----------------------------------------------------------
764 -- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
765 -- ----------------------------------------------------------
766 create table fn_qz_simple_triggers (
767 SCHED_NAME VARCHAR(120) not null,
768 TRIGGER_NAME VARCHAR(200) not null,
769 TRIGGER_GROUP VARCHAR(200) not null,
770 REPEAT_COUNT BIGINT(7) not null,
771 REPEAT_INTERVAL BIGINT(12) not null,
772 TIMES_TRIGGERED BIGINT(10) not null,
773 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
774 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
775 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
778 -- ----------------------------------------------------------
779 -- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
780 -- ----------------------------------------------------------
781 create table fn_qz_cron_triggers (
782 SCHED_NAME VARCHAR(120) not null,
783 TRIGGER_NAME VARCHAR(200) not null,
784 TRIGGER_GROUP VARCHAR(200) not null,
785 CRON_EXPRESSION VARCHAR(120) not null,
786 TIME_ZONE_ID VARCHAR(80),
787 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
788 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
789 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
792 -- ----------------------------------------------------------
793 -- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
794 -- ----------------------------------------------------------
795 create table fn_qz_simprop_triggers (
796 SCHED_NAME VARCHAR(120) not null,
797 TRIGGER_NAME VARCHAR(200) not null,
798 TRIGGER_GROUP VARCHAR(200) not null,
799 STR_PROP_1 VARCHAR(512) null,
800 STR_PROP_2 VARCHAR(512) null,
801 STR_PROP_3 VARCHAR(512) null,
804 LONG_PROP_1 BIGINT null,
805 LONG_PROP_2 BIGINT null,
806 DEC_PROP_1 NUMERIC(13,4) null,
807 DEC_PROP_2 NUMERIC(13,4) null,
808 BOOL_PROP_1 VARCHAR(1) null,
809 BOOL_PROP_2 VARCHAR(1) null,
810 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
811 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
812 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
815 -- ----------------------------------------------------------
816 -- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
817 -- ----------------------------------------------------------
818 create table fn_qz_blob_triggers (
819 SCHED_NAME VARCHAR(120) not null,
820 TRIGGER_NAME VARCHAR(200) not null,
821 TRIGGER_GROUP VARCHAR(200) not null,
823 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
824 INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
825 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
826 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
829 -- ----------------------------------------------------------
830 -- NAME: FN_QZ_CALENDARS; TYPE: TABLE
831 -- ----------------------------------------------------------
832 create table fn_qz_calendars (
833 SCHED_NAME VARCHAR(120) not null,
834 CALENDAR_NAME VARCHAR(200) not null,
835 CALENDAR BLOB not null,
836 primary key (SCHED_NAME,CALENDAR_NAME)
840 -- ----------------------------------------------------------
841 -- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
842 -- ----------------------------------------------------------
843 create table fn_qz_paused_trigger_grps (
844 SCHED_NAME VARCHAR(120) not null,
845 TRIGGER_GROUP VARCHAR(200) not null,
846 primary key (SCHED_NAME,TRIGGER_GROUP)
849 -- ----------------------------------------------------------
850 -- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
851 -- ----------------------------------------------------------
852 create table fn_qz_fired_triggers (
853 SCHED_NAME VARCHAR(120) not null,
854 ENTRY_ID VARCHAR(95) not null,
855 TRIGGER_NAME VARCHAR(200) not null,
856 TRIGGER_GROUP VARCHAR(200) not null,
857 INSTANCE_NAME VARCHAR(200) not null,
858 FIRED_TIME BIGINT(13) not null,
859 SCHED_TIME BIGINT(13) not null,
860 PRIORITY INTEGER not null,
861 STATE VARCHAR(16) not null,
862 JOB_NAME VARCHAR(200) null,
863 JOB_GROUP VARCHAR(200) null,
864 IS_NONCONCURRENT VARCHAR(1) null,
865 REQUESTS_RECOVERY VARCHAR(1) null,
866 primary key (SCHED_NAME,ENTRY_ID)
869 -- ----------------------------------------------------------
870 -- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
871 -- ----------------------------------------------------------
872 create table fn_qz_scheduler_state (
873 SCHED_NAME VARCHAR(120) not null,
874 INSTANCE_NAME VARCHAR(200) not null,
875 LAST_CHECKIN_TIME BIGINT(13) not null,
876 CHECKIN_INTERVAL BIGINT(13) not null,
877 primary key (SCHED_NAME,INSTANCE_NAME)
880 -- ----------------------------------------------------------
881 -- NAME: FN_QZ_LOCKS; TYPE: TABLE
882 -- ----------------------------------------------------------
883 create table fn_qz_locks (
884 SCHED_NAME VARCHAR(120) not null,
885 LOCK_NAME VARCHAR(40) not null,
886 primary key (SCHED_NAME,LOCK_NAME)
889 -- ----------------------------------------------------------
890 -- NAME: FN_MENU_FAVORITES; TYPE: TABLE
891 -- ----------------------------------------------------------
893 create table fn_menu_favorites (
894 user_id int(11) not null,
895 menu_id int(11) not null,
896 primary key (user_id,menu_id)
899 -- FACELIFT - Table for Events, News and Resources
901 create table fn_common_widget_data(
902 id int auto_increment,
903 category varchar(32),
906 content varchar(4096),
907 event_date varchar(10), -- YYYY-MM-DD
912 create table fn_app_contact_us (
913 app_id int(11) not null,
914 contact_name varchar(128) default null,
915 contact_email varchar(128) default null,
916 url varchar(256) default null,
917 active_yn varchar(2) default null,
918 description varchar(1024) default null,
919 primary key (app_id),
920 constraint fk_fn_a_con__ref_202_fn_app foreign key (app_id) references fn_app (app_id)
924 create table fn_pers_user_app_sel (
925 id int(11) not null auto_increment,
926 user_id int(11) not null,
927 app_id int(11) not null,
928 status_cd char(1) not null,
930 constraint fk_1_fn_pers_user_app_sel_fn_user foreign key (user_id) references fn_user (user_id),
931 constraint fk_2_fn_pers_user_app_sel_fn_app foreign key (app_id) references fn_app (app_id)
936 -- new 1702 tables/views
937 -- 1702 Additions for User Notifications
938 -- ----------------------------------------------------------
939 -- NAME: ep_notification; TYPE: TABLE
940 -- ----------------------------------------------------------
941 create table ep_notification (
942 notification_ID int(11) primary key not null auto_increment,
943 is_for_online_users char(1) default 'N',
944 is_for_all_roles char(1) default 'N',
945 active_YN char(1) default 'Y',
946 msg_header varchar(100),
947 msg_description varchar(2000),
948 msg_source varchar(50) default 'EP',
949 start_time timestamp default now(),
950 end_time timestamp null,
952 creator_ID int(11) null default null,
953 created_date timestamp null default null
956 -- ----------------------------------------------------------
957 -- NAME: ep_role_notification; TYPE: TABLE
958 -- ----------------------------------------------------------
959 create table ep_role_notification (
960 ID int(11) primary key not null auto_increment,
961 notification_ID int(11),
963 recv_user_id int(11) null,
964 constraint fk_ep_role_notif_fn_role foreign key (role_ID) references fn_role(role_id),
965 constraint fk_ep_role_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
968 -- ----------------------------------------------------------
969 -- NAME: ep_user_notification; TYPE: TABLE
970 -- ----------------------------------------------------------
971 create table ep_user_notification (
972 ID int(11) primary key not null auto_increment,
974 notification_ID int(11),
975 is_viewed char(1) default 'N',
976 updated_time timestamp default now(),
977 constraint fk_ep_urole_notif_fn_user foreign key (User_ID) references fn_user(user_id),
978 constraint fk_ep_urole_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
981 -- ----------------------------------------------------------
982 -- NAME: ep_pers_user_app_sort; TYPE: Table
983 -- ----------------------------------------------------------
985 CREATE TABLE ep_pers_user_app_sort (
986 id int(11) not null primary key auto_increment,
987 user_id int(11) not null,
988 sort_pref char(1) not null,
989 unique key uk_1_ep_pers_user_app_sort (user_id),
990 constraint fk_ep_pers_user_app_sort_fn_user foreign key (user_id) references fn_user(user_id)
992 -- ----------------------------------------------------------
993 -- NAME: ep_pers_user_app_man_sort; TYPE: Table
994 -- ----------------------------------------------------------
996 CREATE TABLE ep_pers_user_app_man_sort (
997 id int(11) not null primary key auto_increment,
998 user_id int(11) not null,
999 app_id int(11) not null,
1000 sort_order int(11) not null,
1001 unique key uk_1_ep_pers_user_app_man_sort (user_id, app_id),
1002 constraint fk_ep_pers_app_man_sort_fn_user foreign key (user_id) references fn_user(user_id),
1003 constraint fk_ep_pers_app_man_sort_fn_app foreign key (app_id) references fn_app(app_id)
1006 -- ----------------------------------------------------------
1007 -- NAME: ep_widget_catalog; TYPE: Table
1008 -- ----------------------------------------------------------
1010 CREATE TABLE ep_widget_catalog (
1011 widget_id int(11) not null auto_increment,
1012 wdg_name varchar(100) not null default '?',
1013 service_id int(11) default null,
1014 wdg_desc varchar(200) default null,
1015 wdg_file_loc varchar(256) not null default '?',
1016 all_user_flag char(1) not null default 'N',
1017 primary key (widget_id)
1020 -- ----------------------------------------------------------
1021 -- NAME: ep_widget_catalog_role; TYPE: Table
1022 -- ----------------------------------------------------------
1023 create table ep_widget_catalog_role (
1024 widget_id int(10) not null,
1025 app_id int(11) default '1',
1026 role_id int(10) not null,
1027 key fk_ep_widget_catalog_role_fn_widget (widget_id),
1028 key fk_ep_widget_catalog_role_ref_fn_role (role_id),
1029 key fk_ep_widget_catalog_role_app_id (app_id),
1030 constraint fk_ep_widget_catalog_role_fn_widget foreign key (widget_id) references ep_widget_catalog (widget_id),
1031 constraint fk_ep_widget_catalog_role_ref_fn_role foreign key (role_id) references fn_role (role_id),
1032 constraint fk_ep_widget_catalog_role_app_id foreign key (app_id) references fn_app (app_id)
1035 -- ----------------------------------------------------------
1036 -- NAME: ep_pers_user_widget_placement; TYPE: Table
1037 -- ----------------------------------------------------------
1038 CREATE TABLE ep_pers_user_widget_placement (
1039 id int(11) not null primary key auto_increment,
1040 user_id int(11) not null,
1041 widget_id int(11) not null,
1046 unique key uk_1_ep_pers_user_widg_place (user_id, widget_id),
1047 constraint fk_ep_pers_user_widg_place_fn_user foreign key (user_id) references fn_user(user_id),
1048 constraint fk_ep_pers_user_widg_place_ep_widg foreign key (widget_id) references ep_widget_catalog(widget_id)
1051 -- ----------------------------------------------------------
1052 -- NAME: ep_pers_user_widget_sel; TYPE: TABLE
1053 -- ----------------------------------------------------------
1054 CREATE TABLE ep_pers_user_widget_sel (
1055 id int(11) not null primary key auto_increment,
1056 user_id int(11) not null,
1057 widget_id int(11) not null,
1058 status_cd char(1) not null,
1059 unique key uk_1_ep_pers_user_widg_sel_user_widg (user_id, widget_id),
1060 CONSTRAINT fk_1_ep_pers_user_wid_sel_fn_user FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
1061 CONSTRAINT fk_2_ep_pers_user_wid_sel_ep_wid FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
1064 -- ----------------------------------------------------------
1065 -- NAME: ep_widget_catalog_files; TYPE: TABLE
1066 -- ----------------------------------------------------------
1067 CREATE TABLE ep_widget_catalog_files (
1068 file_id int(11) not null primary key auto_increment,
1070 widget_name VARCHAR(100) NOT NULL,
1071 framework_js LONGBLOB NULL,
1072 controller_js LONGBLOB NULL,
1073 markup_html LONGBLOB NULL,
1074 widget_css LONGBLOB NULL
1077 -- ----------------------------------------------------------
1078 -- NAME: fn_role_v; TYPE: VIEW
1079 -- All roles without an APP_ID are Portal only.
1080 -- ----------------------------------------------------------
1081 create view fn_role_v as
1082 select fn_role.role_id as role_id,
1083 fn_role.role_name as role_name,
1084 fn_role.active_yn as active_yn,
1085 fn_role.priority as priority,
1086 fn_role.app_id as app_id,
1087 fn_role.app_role_id as app_role_id
1088 from fn_role where isnull(fn_role.app_id);
1090 -- end new 1702 tables/views
1092 -- new 1707 tables/views
1094 -- ----------------------------------------------------------
1095 -- NAME: ep_user_roles_request; TYPE: TABLE
1096 -- ----------------------------------------------------------
1098 create table ep_user_roles_request (
1099 req_id int(11) not null primary key auto_increment,
1100 user_id int(11) not null,
1101 app_id int(11) not null,
1102 created_date timestamp default now(),
1103 updated_date timestamp default now(),
1104 request_status character varying(50) not null,
1105 constraint fk_user_roles_req_fn_user foreign key (user_id) references fn_user(user_id),
1106 constraint fk_user_roles_req_fn_app foreign key (app_id) references fn_app(app_id)
1110 -- ----------------------------------------------------------
1111 -- NAME: ep_user_roles_request_det; TYPE: TABLE
1112 -- ----------------------------------------------------------
1113 create table ep_user_roles_request_det (
1114 id int(11) not null primary key auto_increment,
1115 req_id int(11) default null,
1116 requested_role_id int(10) not null,
1117 request_type character varying(10) not null,
1118 constraint fk_user_roles_req_fn_req_id foreign key (req_id) references ep_user_roles_request(req_id),
1119 constraint fk_user_roles_req_fn_role_id foreign key (requested_role_id) references fn_role(role_id)
1122 -- ----------------------------------------------------------
1123 -- NAME: ep_microservice; TYPE: TABLE
1124 -- ----------------------------------------------------------
1126 CREATE TABLE ep_microservice (
1127 id INT(11) NOT NULL AUTO_INCREMENT,
1128 name VARCHAR(50) NULL DEFAULT NULL,
1129 description VARCHAR(50) NULL DEFAULT NULL,
1130 appId INT(11) NULL DEFAULT NULL,
1131 endpoint_url VARCHAR(200) NULL DEFAULT NULL,
1132 security_type VARCHAR(50) NULL DEFAULT NULL,
1133 username VARCHAR(50) NULL DEFAULT NULL,
1134 password VARCHAR(50) NULL DEFAULT NULL,
1135 active CHAR(1) NOT NULL DEFAULT 'Y',
1137 CONSTRAINT FK_FN_APP_EP_MICROSERVICE FOREIGN KEY (appId) REFERENCES fn_app (app_id)
1140 -- ----------------------------------------------------------
1141 -- NAME: ep_microservice_parameter; TYPE: TABLE
1142 -- ----------------------------------------------------------
1144 CREATE TABLE ep_microservice_parameter (
1145 id INT(11) NOT NULL AUTO_INCREMENT,
1146 service_id INT(11) NULL DEFAULT NULL,
1147 para_key VARCHAR(50) NULL DEFAULT NULL,
1148 para_value VARCHAR(50) NULL DEFAULT NULL,
1150 CONSTRAINT FK_EP_MICROSERIVCE_EP_MICROSERVICE_PARAMETER FOREIGN KEY (service_id) REFERENCES ep_microservice (id)
1154 -- ----------------------------------------------------------
1155 -- NAME: ep_widget_preview_files; TYPE: TABLE
1156 -- ----------------------------------------------------------
1158 CREATE TABLE ep_widget_preview_files (
1159 preview_id INT(11) NOT NULL AUTO_INCREMENT,
1160 html_file LONGBLOB NULL,
1161 css_file LONGBLOB NULL,
1162 javascript_file LONGBLOB NULL,
1163 framework_file LONGBLOB NULL,
1164 PRIMARY KEY (preview_id)
1167 -- ----------------------------------------------------------
1168 -- NAME: ep_widget_microservice; TYPE: TABLE
1169 -- ----------------------------------------------------------
1171 CREATE TABLE ep_widget_microservice (
1172 id INT(11) NOT NULL AUTO_INCREMENT,
1173 widget_id INT(11) NOT NULL DEFAULT '0',
1174 microservice_id INT(11) NOT NULL DEFAULT '0',
1176 CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_MICROSERVICE FOREIGN KEY (microservice_id) REFERENCES ep_microservice (id),
1177 CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_WIDGET FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
1180 -- ----------------------------------------------------------
1181 -- NAME: ep_basic_auth_account; TYPE: TABLE
1182 -- ----------------------------------------------------------
1184 create table ep_basic_auth_account (
1185 id INT(11) NOT NULL AUTO_INCREMENT,
1186 ext_app_name VARCHAR(50) NOT NULL,
1187 username VARCHAR(50) NOT NULL,
1188 password VARCHAR(50) NOT NULL,
1189 active_yn char(1) NOT NULL default 'Y',
1193 -- ----------------------------------------------------------
1194 -- NAME: ep_widget_catalog_parameter; TYPE: TABLE
1195 -- ----------------------------------------------------------
1197 create table ep_widget_catalog_parameter (
1198 id INT(11) NOT NULL AUTO_INCREMENT,
1199 widget_id INT(11) NOT NULL,
1200 user_id INT(11) NOT NULL,
1201 param_id INT(11) NOT NULL,
1202 user_value VARCHAR(50) NULL,
1204 CONSTRAINT EP_FN_USER_WIDGET_PARAMETER_FK FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
1205 CONSTRAINT EP_WIDGET_CATALOG_WIDGET_PARAMETER_FK FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id),
1206 CONSTRAINT EP_PARAMETER_ID_WIDGET_PARAMETER_FK FOREIGN KEY (param_id) REFERENCES ep_microservice_parameter (id)
1209 -- ----------------------------------------------------------
1210 -- NAME: ep_web_analytics_source; TYPE: TABLE
1211 -- ----------------------------------------------------------
1213 create table ep_web_analytics_source(
1214 resource_id int(11) NOT NULL auto_increment,
1215 app_id int(11) NOT NULL,
1216 report_source varchar(500),
1217 report_name varchar(500),
1218 PRIMARY KEY (resource_id),
1219 FOREIGN KEY (app_id) REFERENCES fn_app(app_id)
1222 -- Machine Learning Tables
1223 -- ----------------------------------------------------------
1224 -- NAME: ep_ml_model; TYPE: TABLE
1225 -- ----------------------------------------------------------
1227 create table ep_ml_model(
1228 time_stamp timestamp default now(),
1229 group_id int(11) NOT NULL,
1231 PRIMARY KEY (time_stamp,group_id)
1233 -- ----------------------------------------------------------
1234 -- NAME: ep_ml_rec; TYPE: TABLE
1235 -- ----------------------------------------------------------
1237 create table ep_ml_rec(
1238 time_stamp timestamp default now(),
1239 org_user_id varchar(20) NOT NULL,
1240 rec varchar(4000) DEFAULT NULL,
1241 PRIMARY KEY (time_stamp,org_user_id)
1244 -- ----------------------------------------------------------
1245 -- NAME: ep_ml_user; TYPE: TABLE
1246 -- ----------------------------------------------------------
1248 create table ep_ml_user(
1249 time_stamp timestamp default now(),
1250 org_user_id varchar(20) NOT NULL,
1251 group_id int(11) NOT NULL,
1252 PRIMARY KEY (time_stamp,org_user_id)
1255 -- ----------------------------------------------------------
1256 -- NAME: ep_endpoints; TYPE: TABLE
1257 -- ----------------------------------------------------------
1259 create table ep_endpoints (
1260 id INT(11) NOT NULL AUTO_INCREMENT,
1261 url VARCHAR(50) NOT NULL,
1265 -- ----------------------------------------------------------
1266 -- NAME: ep_endpoints_basic_auth_account; TYPE: TABLE
1267 -- ----------------------------------------------------------
1269 create table ep_endpoints_basic_auth_account (
1270 id INT(11) NOT NULL AUTO_INCREMENT,
1271 ep_id INT(11) DEFAULT NULL,
1272 account_id INT(11) DEFAULT NULL,
1274 CONSTRAINT ep_endpoints_basic_auth_account_account_id_fk FOREIGN KEY (account_id) REFERENCES ep_basic_auth_account (id),
1275 CONSTRAINT ep_endpoints_basic_auth_account_ep_id_fk FOREIGN KEY (ep_id) REFERENCES ep_endpoints (id)
1279 -- end new 1707 tables/views
1281 -- ----------------------------------------------------------
1282 -- NAME: QUARTZ TYPE: INDEXES
1283 -- ----------------------------------------------------------
1284 create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
1285 create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
1286 create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
1287 create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
1288 create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
1289 create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
1290 create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
1291 create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
1292 create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
1293 create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
1294 create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
1295 create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
1296 create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
1297 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);
1298 create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
1299 create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
1300 create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
1301 create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
1302 create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
1303 create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
1306 -- ------------------ create view section
1308 -- name: v_url_access; type: view
1310 create view v_url_access as
1311 select distinct m.action as url,
1314 where (m.action is not null)
1316 select distinct t.action as url,
1319 where (t.action is not null)
1321 select r.restricted_url as url,
1323 from fn_restricted_url r;
1325 -- ------------------ alter table add constraint primary key section
1327 -- name: cr_favorite_reports_user_idrep_id; type: constraint
1329 alter table cr_favorite_reports
1330 add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
1332 -- name: cr_folder_folder_id; type: constraint
1334 alter table cr_folder
1335 add constraint cr_folder_folder_id primary key (folder_id);
1337 -- name: cr_folder_access_folder_access_id; type: constraint
1339 alter table cr_folder_access
1340 add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
1342 -- name: cr_hist_user_map_hist_iduser_id; type: constraint
1344 alter table cr_hist_user_map
1345 add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
1347 -- name: cr_lu_file_type_lookup_id; type: constraint
1349 alter table cr_lu_file_type
1350 add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
1352 -- name: cr_raptor_action_img_image_id; type: constraint
1354 alter table cr_raptor_action_img
1355 add constraint cr_raptor_action_img_image_id primary key (image_id);
1357 -- name: cr_raptor_pdf_img_image_id; type: constraint
1359 alter table cr_raptor_pdf_img
1360 add constraint cr_raptor_pdf_img_image_id primary key (image_id);
1362 -- name: cr_remote_schema_info_schema_prefix; type: constraint
1364 alter table cr_remote_schema_info
1365 add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
1367 -- name: cr_report_rep_id; type: constraint
1369 alter table cr_report
1370 add constraint cr_report_rep_id primary key (rep_id);
1372 -- name: cr_report_access_rep_idorder_no; type: constraint
1374 alter table cr_report_access
1375 add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
1377 -- name: cr_report_email_sent_log_log_id; type: constraint
1379 alter table cr_report_email_sent_log
1380 add constraint cr_report_email_sent_log_log_id primary key (log_id);
1382 -- name: cr_report_file_history_hist_id; type: constraint
1384 alter table cr_report_file_history
1385 add constraint cr_report_file_history_hist_id primary key (hist_id);
1387 -- name: cr_report_schedule_schedule_id; type: constraint
1389 alter table cr_report_schedule
1390 add constraint cr_report_schedule_schedule_id primary key (schedule_id);
1392 -- name: cr_report_schedule_users_schedule_idrep_iduser_idorder_no; type: constraint
1394 alter table cr_report_schedule_users
1395 add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
1397 -- name: cr_report_template_map_report_id; type: constraint
1399 alter table cr_report_template_map
1400 add constraint cr_report_template_map_report_id primary key (report_id);
1402 -- name: cr_table_role_table_namerole_id; type: constraint
1404 alter table cr_table_role
1405 add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
1407 -- name: cr_table_source_table_name; type: constraint
1409 alter table cr_table_source
1410 add constraint cr_table_source_table_name primary key (table_name);
1412 -- name: fn_audit_action_audit_action_id; type: constraint
1414 alter table fn_audit_action
1415 add constraint fn_audit_action_audit_action_id primary key (audit_action_id);
1418 -- name: fk_fn_audit_ref_205_fn_lu_ac; type: constraint
1420 alter table fn_audit_log
1421 add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
1423 -- name: fk_fn_role__ref_201_fn_funct; type: constraint
1425 alter table fn_role_function
1426 add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
1428 -- name: fn_chat_logs_chat_log_id; type: constraint
1430 alter table fn_chat_logs
1431 add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
1433 -- name: fn_chat_room_chat_room_id; type: constraint
1435 alter table fn_chat_room
1436 add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
1438 -- name: fn_chat_users_id; type: constraint
1440 alter table fn_chat_users
1441 add constraint fn_chat_users_id primary key (id);
1443 -- name: fn_lu_alert_method_alert_method_cd; type: constraint
1445 alter table fn_lu_alert_method
1446 add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
1448 -- name: fn_lu_broadcast_site_broadcast_site_cd; type: constraint
1450 alter table fn_lu_broadcast_site
1451 add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
1453 -- name: fn_lu_priority_priority_id; type: constraint
1455 alter table fn_lu_priority
1456 add constraint fn_lu_priority_priority_id primary key (priority_id);
1458 -- name: fn_lu_role_type_role_type_id; type: constraint
1460 alter table fn_lu_role_type
1461 add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
1463 -- name: fn_lu_tab_set_tab_set_cd; type: constraint
1465 alter table fn_lu_tab_set
1466 add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
1468 -- name: fn_lu_timezone_timezone_id; type: constraint
1470 alter table fn_lu_timezone
1471 add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
1473 -- name: fn_org_org_id; type: constraint
1476 add constraint fn_org_org_id primary key (org_id);
1478 -- name: fn_restricted_url_restricted_urlfunction_cd; type: constraint
1480 alter table fn_restricted_url
1481 add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
1483 -- name: fn_role_composite_parent_role_idchild_role_id; type: constraint
1485 alter table fn_role_composite
1486 add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
1488 -- name: fn_role_function_role_idfunction_cd; type: constraint
1490 alter table fn_role_function
1491 add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
1493 -- name: fn_tab_tab_cd; type: constraint
1496 add constraint fn_tab_tab_cd primary key (tab_cd);
1498 -- name: fn_tab_selected_selected_tab_cdtab_uri; type: constraint
1500 alter table fn_tab_selected
1501 add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
1503 -- name: fn_user_pseudo_role_pseudo_role_iduser_id; type: constraint
1505 alter table fn_user_pseudo_role
1506 add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
1508 -- name: fn_user_role_user_idrole_id; type: constraint
1510 alter table fn_user_role
1511 add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
1512 -- ------------------ create index section
1514 -- name: cr_report_create_idpublic_yntitle; type: index
1516 create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
1518 -- name: cr_table_join_dest_table_name; type: index
1520 create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
1522 -- name: cr_table_join_src_table_name; type: index
1524 create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
1526 -- name: fn_audit_log_activity_cd; type: index
1528 create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
1530 -- name: fn_audit_log_user_id; type: index
1532 create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
1534 -- name: fn_menu_function_cd; type: index
1536 create index fn_menu_function_cd using btree on fn_menu (function_cd);
1538 -- name: fn_org_access_cd; type: index
1540 create index fn_org_access_cd using btree on fn_org (access_cd);
1542 -- name: fn_role_function_function_cd; type: index
1544 create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
1546 -- name: fn_role_function_role_id; type: index
1548 create index fn_role_function_role_id using btree on fn_role_function (role_id);
1550 -- name: fn_user_address_id; type: index
1552 create index fn_user_address_id using btree on fn_user (address_id);
1554 -- name: fn_user_alert_method_cd; type: index
1556 create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd);
1558 -- name: fn_user_hrid; type: index
1560 create unique index fn_user_hrid using btree on fn_user (hrid);
1562 -- name: fn_user_login_id; type: index
1564 create unique index fn_user_login_id using btree on fn_user (login_id);
1566 -- name: fn_user_org_id; type: index
1568 create index fn_user_org_id using btree on fn_user (org_id);
1570 -- name: fn_user_role_role_id; type: index
1572 create index fn_user_role_role_id using btree on fn_user_role (role_id);
1574 -- name: fn_user_role_user_id; type: index
1576 create index fn_user_role_user_id using btree on fn_user_role (user_id);
1578 -- name: fk_fn_user__ref_178_fn_app_idx; type: index
1580 create index fk_fn_user__ref_178_fn_app_idx on fn_user_role (app_id);
1582 -- name: fn_role_name_app_id_idx; type: index
1584 create unique index fn_role_name_app_id_idx using btree on fn_role (role_name,app_id);
1588 create index ep_notif_recv_user_id_idx using btree on ep_role_notification (recv_user_id);
1592 -- ------------------ alter table add constraint foreign key section
1594 -- name: fk_fn_user__ref_178_fn_app; type: fk constraint
1596 alter table fn_user_role
1597 add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
1599 -- name: fk_cr_repor_ref_14707_cr_repor; type: fk constraint
1601 alter table cr_report_schedule
1602 add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
1604 -- name: fk_cr_repor_ref_14716_cr_repor; type: fk constraint
1606 alter table cr_report_schedule_users
1607 add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
1609 -- name: fk_cr_repor_ref_17645_cr_repor; type: fk constraint
1611 alter table cr_report_log
1612 add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
1614 -- name: fk_cr_repor_ref_8550_cr_repor; type: fk constraint
1616 alter table cr_report_access
1617 add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
1619 -- name: fk_cr_report_rep_id; type: fk constraint
1621 alter table cr_report_email_sent_log
1622 add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
1624 -- name: fk_cr_table_ref_311_cr_tab; type: fk constraint
1626 alter table cr_table_join
1627 add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
1629 -- name: fk_cr_table_ref_315_cr_tab; type: fk constraint
1631 alter table cr_table_join
1632 add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
1634 -- name: fk_cr_table_ref_32384_cr_table; type: fk constraint
1636 alter table cr_table_role
1637 add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
1639 -- name: fk_fn_tab_function_cd; type: fk constraint
1642 add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
1644 -- name: fk_fn_tab_selected_tab_cd; type: fk constraint
1646 alter table fn_tab_selected
1647 add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
1649 -- name: fk_fn_tab_set_cd; type: fk constraint
1652 add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
1654 -- name: fk_fn_user_ref_110_fn_org; type: fk constraint
1657 add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id);
1659 -- name: fk_fn_user_ref_123_fn_lu_al; type: fk constraint
1662 add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd);
1664 -- name: fk_fn_user_ref_197_fn_user; type: fk constraint
1667 add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id);
1669 -- name: fk_fn_user_ref_198_fn_user; type: fk constraint
1672 add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id);
1674 -- name: fk_fn_user_ref_199_fn_user; type: fk constraint
1677 add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id);
1679 -- name: fk_parent_key_cr_folder; type: fk constraint
1681 alter table cr_folder
1682 add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
1684 -- name: fk_pseudo_role_pseudo_role_id; type: fk constraint
1686 alter table fn_user_pseudo_role
1687 add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
1689 -- name: fk_pseudo_role_user_id; type: fk constraint
1691 alter table fn_user_pseudo_role
1692 add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
1694 -- name: fk_restricted_url_function_cd; type: fk constraint
1696 alter table fn_restricted_url
1697 add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
1699 -- name: fk_timezone; type: fk constraint
1702 add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id);
1704 -- name: sys_c0014614; type: fk constraint
1706 alter table cr_report_file_history
1707 add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
1709 -- name: sys_c0014615; type: fk constraint
1711 alter table cr_report_file_history
1712 add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
1714 -- name: sys_c0014616; type: fk constraint
1716 alter table cr_hist_user_map
1717 add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
1719 -- name: sys_c0014617; type: fk constraint
1721 alter table cr_hist_user_map
1722 add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);
1724 -- name: sys_c0014618; type: fk constraint
1726 alter table fn_menu_favorites
1727 add constraint sys_c0014618 foreign key (user_id) references fn_user(user_id);
1730 -- name: sys_c0014619; type: fk constraint
1732 alter table fn_menu_favorites
1733 add constraint sys_c0014619 foreign key (menu_id) references fn_menu_functional(menu_id);