1 -- ---------------------------------------------------------------------------------------------------------------
2 -- This is the 2.1.0 version of Portal database called portal
4 -- note to : database admin, set the mysql system variable called lower_case_table_names
5 -- it can be set 3 different ways:
6 -- command-line options (cmd-line),
7 -- options valid in configuration files (option file), or
8 -- server system variables (system var).
10 -- it needs to be set to 1, then table names are stored in lowercase on disk and comparisons are not case sensitive.
11 -- -----------------------------------------------------------------------------------------------------------------
12 set foreign_key_checks=1;
14 create database portal;
18 -- ------------------ create table section
20 -- name: cr_favorite_reports; type: table
22 create table cr_favorite_reports (
23 user_id integer not null,
24 rep_id integer not null
27 -- name: cr_filehist_log; type: table
29 create table cr_filehist_log (
30 schedule_id numeric(11,0) not null,
31 url character varying(4000),
32 notes character varying(3500),
36 -- name: cr_folder; type: table
38 create table cr_folder (
39 folder_id integer not null,
40 folder_name character varying(50) not null,
41 descr character varying(500),
42 create_id integer not null,
43 create_date timestamp not null,
44 parent_folder_id integer,
45 public_yn character varying(1) default 'n' not null
48 -- name: cr_folder_access; type: table
50 create table cr_folder_access (
51 folder_access_id numeric(11,0) not null,
52 folder_id numeric(11,0) not null,
53 order_no numeric(11,0) not null,
54 role_id numeric(11,0),
55 user_id numeric(11,0),
56 read_only_yn character varying(1) default 'n' not null
59 -- name: cr_hist_user_map; type: table
61 create table cr_hist_user_map (
62 hist_id int(11) not null,
63 user_id int(11) not null
66 -- name: cr_lu_file_type; type: table
68 create table cr_lu_file_type (
69 lookup_id numeric(2,0) not null,
70 lookup_descr character varying(255) not null,
71 active_yn character(1) default 'y',
72 error_code numeric(11,0)
75 -- name: cr_raptor_action_img; type: table
77 create table cr_raptor_action_img (
78 image_id character varying(100) not null,
79 image_loc character varying(400)
82 -- name: cr_raptor_pdf_img; type: table
84 create table cr_raptor_pdf_img (
85 image_id character varying(100) not null,
86 image_loc character varying(400)
89 -- name: cr_remote_schema_info; type: table
91 create table cr_remote_schema_info (
92 schema_prefix character varying(5) not null,
93 schema_desc character varying(75) not null,
94 datasource_type character varying(100)
97 -- name: cr_report; type: table
99 create table cr_report (
100 rep_id numeric(11,0) not null,
101 title character varying(100) not null,
102 descr character varying(255),
103 public_yn character varying(1) default 'n' not null,
105 create_id numeric(11,0),
106 create_date timestamp default now(),
107 maint_id numeric(11,0),
108 maint_date timestamp default now(),
109 menu_id character varying(500),
110 menu_approved_yn character varying(1) default 'n' not null,
111 owner_id numeric(11,0),
112 folder_id integer default 0,
113 dashboard_type_yn character varying(1) default 'n',
114 dashboard_yn character varying(1) default 'n'
117 -- name: cr_report_access; type: table
119 create table cr_report_access (
120 rep_id numeric(11,0) not null,
121 order_no numeric(11,0) not null,
122 role_id numeric(11,0),
123 user_id numeric(11,0),
124 read_only_yn character varying(1) default 'n' not null
127 -- name: cr_report_dwnld_log; type: table
129 create table cr_report_dwnld_log (
130 user_id numeric(11,0) not null,
131 rep_id integer not null,
132 file_name character varying(100) not null,
133 dwnld_start_time timestamp default now() not null,
134 record_ready_time timestamp default now(),
135 filter_params character varying(2000)
138 -- name: cr_report_email_sent_log; type: table
140 create table cr_report_email_sent_log (
141 log_id integer not null,
142 schedule_id numeric(11,0),
143 gen_key character varying(25) not null,
144 rep_id numeric(11,0) not null,
145 user_id numeric(11,0),
146 sent_date timestamp default now(),
147 access_flag character varying(1) default 'y' not null,
148 touch_date timestamp default now()
151 -- name: cr_report_file_history; type: table
153 create table cr_report_file_history (
154 hist_id int(11) not null,
155 sched_user_id numeric(11,0) not null,
156 schedule_id numeric(11,0) not null,
157 user_id numeric(11,0) not null,
158 rep_id numeric(11,0),
160 recurrence character varying(50),
161 file_type_id numeric(2,0),
162 file_name character varying(80),
164 file_size numeric(11,0),
165 raptor_url character varying(4000),
166 error_yn character(1) default 'n',
167 error_code numeric(11,0),
168 deleted_yn character(1) default 'n',
169 deleted_by numeric(38,0)
172 -- name: cr_report_log; type: table
174 create table cr_report_log (
175 rep_id numeric(11,0) not null,
176 log_time timestamp not null,
177 user_id numeric(11,0) not null,
178 action character varying(2000) not null,
179 action_value character varying(50),
180 form_fields character varying(4000)
183 -- name: cr_report_schedule; type: table
185 create table cr_report_schedule (
186 schedule_id numeric(11,0) not null,
187 sched_user_id numeric(11,0) not null,
188 rep_id numeric(11,0) not null,
189 enabled_yn character varying(1) not null,
190 start_date timestamp default now(),
191 end_date timestamp default now(),
192 run_date timestamp default now(),
193 recurrence character varying(50),
194 conditional_yn character varying(1) not null,
195 condition_sql character varying(4000),
196 notify_type integer default 0,
197 max_row integer default 1000,
198 initial_formfields character varying(3500),
199 processed_formfields character varying(3500),
200 formfields character varying(3500),
201 condition_large_sql text,
202 encrypt_yn character(1) default 'n',
203 attachment_yn character(1) default 'y'
206 -- name: cr_report_schedule_users; type: table
208 create table cr_report_schedule_users (
209 schedule_id numeric(11,0) not null,
210 rep_id numeric(11,0) not null,
211 user_id numeric(11,0) not null,
212 role_id numeric(11,0),
213 order_no numeric(11,0) not null
216 -- name: cr_report_template_map; type: table
218 create table cr_report_template_map (
219 report_id integer not null,
220 template_file character varying(200)
223 -- name: cr_schedule_activity_log; type: table
225 create table cr_schedule_activity_log (
226 schedule_id numeric(11,0) not null,
227 url character varying(4000),
228 notes character varying(2000),
232 -- name: cr_table_join; type: table
234 create table cr_table_join (
235 src_table_name character varying(30) not null,
236 dest_table_name character varying(30) not null,
237 join_expr character varying(500) not null
240 -- name: cr_table_role; type: table
242 create table cr_table_role (
243 table_name character varying(30) not null,
244 role_id numeric(11,0) not null
247 -- name: cr_table_source; type: table
249 create table cr_table_source (
250 table_name character varying(30) not null,
251 display_name character varying(30) not null,
252 pk_fields character varying(200),
253 web_view_action character varying(50),
254 large_data_source_yn character varying(1) default 'n' not null,
255 filter_sql character varying(4000),
256 source_db character varying(50)
259 -- name: fn_lu_timezone; type: table
261 create table fn_lu_timezone (
262 timezone_id int(11) not null,
263 timezone_name character varying(100) not null,
264 timezone_value character varying(100) not null
267 create table fn_user (
268 user_id int(11) not null primary key auto_increment,
271 first_name character varying(50),
272 middle_name character varying(50),
273 last_name character varying(50),
274 phone character varying(25),
275 fax character varying(25),
276 cellular character varying(25),
277 email character varying(50),
278 address_id numeric(11,0),
279 alert_method_cd character varying(10),
280 hrid character varying(20),
281 org_user_id CHARACTER VARYING(20),
282 org_code character varying(30),
283 login_id character varying(25),
284 login_pwd character varying(100),
285 last_login_date timestamp,
286 active_yn character varying(1) default 'y' not null,
288 created_date timestamp default now(),
290 modified_date timestamp default now(),
291 is_internal_yn character(1) default 'n' not null,
292 address_line_1 character varying(100),
293 address_line_2 character varying(100),
294 city character varying(50),
295 state_cd character varying(3),
296 zip_code character varying(11),
297 country_cd character varying(3),
298 location_clli character varying(8),
299 org_manager_userid CHARACTER VARYING(20),
300 company character varying(100),
301 department_name character varying(100),
302 job_title character varying(100),
304 department character varying(25),
305 business_unit character varying(25),
306 business_unit_name character varying(100),
307 cost_center character varying(25),
308 fin_loc_code character varying(10),
309 silo_status character varying(10)
312 alter table fn_user add column language_id int(2) not null default 1;
314 create table fn_language(
315 language_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
316 language_name VARCHAR(100) NOT NULL,
317 language_alias VARCHAR(100) NOT NULL
320 create table fn_display_text(
321 id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
322 language_id int(11) NOT NULL,
323 text_id int(11) NOT NULL,
324 text_label VARCHAR(100) NOT NULL
327 -- name: fn_role; type: table
329 create table fn_role (
330 role_id int(11) not null primary key auto_increment,
331 role_name character varying(300) not null,
332 active_yn character varying(1) default 'y' not null,
333 priority numeric(4,0),
334 app_id int(11) default null,
335 app_role_id int(11) default null
339 -- name: fn_audit_action; type: table
341 create table fn_audit_action (
342 audit_action_id integer not null,
343 class_name character varying(500) not null,
344 method_name character varying(50) not null,
345 audit_action_cd character varying(20) not null,
346 audit_action_desc character varying(200),
347 active_yn character varying(1)
350 -- name: fn_audit_action_log; type: table
352 create table fn_audit_action_log (
353 audit_log_id integer not null primary key auto_increment,
354 audit_action_cd character varying(200),
355 action_time timestamp,
356 user_id numeric(11,0),
357 class_name character varying(100),
358 method_name character varying(50),
359 success_msg character varying(20),
360 error_msg character varying(500)
363 -- name: fn_lu_activity; type: table
365 create table fn_lu_activity (
366 activity_cd character varying(50) not null primary key,
367 activity character varying(50) not null
370 -- name: fn_audit_log; type: table
372 create table fn_audit_log (
373 log_id int(11) not null primary key auto_increment,
374 user_id int(11) not null,
375 activity_cd character varying(50) not null,
376 audit_date timestamp default now() not null,
377 comments character varying(1000),
378 affected_record_id_bk character varying(500),
379 affected_record_id character varying(4000),
380 constraint fk_fn_audit_ref_209_fn_user foreign key (user_id) references fn_user(user_id)
383 -- name: fn_broadcast_message; type: table
385 create table fn_broadcast_message (
386 message_id int(11) not null primary key auto_increment,
387 message_text character varying(1000) not null,
388 message_location_id numeric(11,0) not null,
389 broadcast_start_date timestamp not null default now(),
390 broadcast_end_date timestamp not null default now(),
391 active_yn character(1) default 'y' not null,
392 sort_order numeric(4,0) not null,
393 broadcast_site_cd character varying(50)
396 -- name: fn_chat_logs; type: table
398 create table fn_chat_logs (
399 chat_log_id integer not null,
400 chat_room_id integer,
402 message character varying(1000),
403 message_date_time timestamp
406 -- name: fn_chat_room; type: table
408 create table fn_chat_room (
409 chat_room_id integer not null,
410 name character varying(50) not null,
411 description character varying(500),
413 created_date timestamp default now(),
414 updated_date timestamp default now()
417 -- name: fn_chat_users; type: table
419 create table fn_chat_users (
420 chat_room_id integer,
422 last_activity_date_time timestamp,
423 chat_status character varying(20),
427 -- name: fn_datasource; type: table
429 create table fn_datasource (
430 id integer not null primary key auto_increment,
431 name character varying(50),
432 driver_name character varying(256),
433 server character varying(256),
435 user_name character varying(256),
436 password character varying(256),
437 url character varying(256),
438 min_pool_size integer,
439 max_pool_size integer,
441 ds_type character varying(20)
444 -- name: fn_function; type: table
446 create table fn_function (
447 function_cd character varying(30) not null primary key,
448 function_name character varying(50) not null
451 -- name: fn_lu_alert_method; type: table
453 create table fn_lu_alert_method (
454 alert_method_cd character varying(10) not null,
455 alert_method character varying(50) not null
458 -- name: fn_lu_broadcast_site; type: table
460 create table fn_lu_broadcast_site (
461 broadcast_site_cd character varying(50) not null,
462 broadcast_site_descr character varying(100)
465 -- name: fn_lu_menu_set; type: table
467 create table fn_lu_menu_set (
468 menu_set_cd character varying(10) not null primary key,
469 menu_set_name character varying(50) not null
472 -- name: fn_lu_priority; type: table
474 create table fn_lu_priority (
475 priority_id numeric(11,0) not null,
476 priority character varying(50) not null,
477 active_yn character(1) not null,
478 sort_order numeric(5,0)
481 -- name: fn_lu_role_type; type: table
483 create table fn_lu_role_type (
484 role_type_id numeric(11,0) not null,
485 role_type character varying(50) not null
488 -- name: fn_lu_tab_set; type: table
490 create table fn_lu_tab_set (
491 tab_set_cd character varying(30) not null,
492 tab_set_name character varying(50) not null
495 -- name: fn_menu; type: table
497 create table fn_menu (
498 menu_id int(11) not null primary key auto_increment,
499 label character varying(100),
501 sort_order numeric(4,0),
502 action character varying(200),
503 function_cd character varying(30),
504 active_yn character varying(1) default 'y' not null,
505 servlet character varying(50),
506 query_string character varying(200),
507 external_url character varying(200),
508 target character varying(25),
509 menu_set_cd character varying(10) default 'app',
510 separator_yn character(1) default 'n',
511 image_src character varying(100),
512 constraint fk_fn_menu_ref_196_fn_menu foreign key (parent_id) references fn_menu(menu_id),
513 constraint fk_fn_menu_menu_set_cd foreign key (menu_set_cd) references fn_lu_menu_set(menu_set_cd)
516 create index idx_fn_menu_label on fn_menu(label);
518 -- name: fn_org; type: table
520 create table fn_org (
521 org_id int(11) not null,
522 org_name character varying(50) not null,
523 access_cd character varying(10)
526 -- name: fn_restricted_url; type: table
528 create table fn_restricted_url (
529 restricted_url character varying(250) not null,
530 function_cd character varying(30) not null
533 -- name: fn_role_composite; type: table
535 create table fn_role_composite (
536 parent_role_id int(11) not null,
537 child_role_id int(11) not null,
538 constraint fk_fn_role_composite_child foreign key (child_role_id) references fn_role(role_id),
539 constraint fk_fn_role_composite_parent foreign key (parent_role_id) references fn_role(role_id)
542 -- name: fn_role_function; type: table
544 create table fn_role_function (
545 role_id int(11) not null,
546 function_cd character varying(30) not null,
547 constraint fk_fn_role__ref_198_fn_role foreign key (role_id) references fn_role(role_id)
550 -- name: fn_tab; type: table
552 create table fn_tab (
553 tab_cd character varying(30) not null,
554 tab_name character varying(50) not null,
555 tab_descr character varying(100),
556 action character varying(100) not null,
557 function_cd character varying(30) not null,
558 active_yn character(1) not null,
559 sort_order numeric(11,0) not null,
560 parent_tab_cd character varying(30),
561 tab_set_cd character varying(30)
564 -- name: fn_tab_selected; type: table
566 create table fn_tab_selected (
567 selected_tab_cd character varying(30) not null,
568 tab_uri character varying(40) not null
571 -- name: fn_user_pseudo_role; type: table
573 create table fn_user_pseudo_role (
574 pseudo_role_id int(11) not null,
575 user_id int(11) not null
578 -- name: fn_user_role; type: table
580 create table fn_user_role (
581 user_id int(10) not null,
582 role_id int(10) not null,
583 priority numeric(4,0),
584 app_id int(11) default 2,
585 constraint fk_fn_user__ref_172_fn_user foreign key (user_id) references fn_user(user_id),
586 constraint fk_fn_user__ref_175_fn_role foreign key (role_id) references fn_role(role_id)
589 -- name: schema_info; type: table
591 create table schema_info (
592 SCHEMA_ID CHARACTER VARYING(25) not null,
593 SCHEMA_DESC CHARACTER VARYING(75) not null,
594 DATASOURCE_TYPE CHARACTER VARYING(100),
595 CONNECTION_URL VARCHAR(200) not null,
596 USER_NAME VARCHAR(45) not null,
597 PASSWORD VARCHAR(45) null default null,
598 DRIVER_CLASS VARCHAR(100) not null,
599 MIN_POOL_SIZE INT not null,
600 MAX_POOL_SIZE INT not null,
601 IDLE_CONNECTION_TEST_PERIOD INT not null
604 -- ----------------------------------------------------------
605 -- name: fn_app; type: table
606 -- ----------------------------------------------------------
607 create table fn_app (
608 app_id int(11) primary key not null auto_increment,
609 app_name varchar(100) not null default '?',
610 app_image_url varchar(256) default null,
611 app_description varchar(512) default null,
612 app_notes varchar(4096) default null,
613 app_url varchar(256) default null,
614 app_alternate_url varchar(256) default null,
615 app_rest_endpoint varchar(2000) default null,
616 ml_app_name varchar(50) not null default '?',
617 ml_app_admin_id varchar(7) not null default '?',
618 mots_id int(11) default null,
619 app_password varchar(256) not null default '?',
620 open char(1) default 'N',
621 enabled char(1) default 'Y',
622 thumbnail mediumblob null default null,
623 app_username varchar(50),
624 ueb_key varchar(256) default null,
625 ueb_secret varchar(256) default null,
626 ueb_topic_name varchar(256) default null,
627 app_type int(11) not null default 1,
628 auth_central char(1) not null default 'N',
629 auth_namespace varchar(100) null default null
632 -- ------------------ functional menu tables -------------------
634 -- table structure for table fn_menu_functional
636 create table fn_menu_functional (
637 menu_id int(11) not null auto_increment,
638 column_num int(2) not null,
639 text varchar(100) not null,
640 parent_menu_id int(11) default null,
641 url varchar(128) not null default '',
642 active_yn varchar(1) not null default 'y',
643 image_src varchar(100) default null,
644 primary key (menu_id),
645 key fk_fn_menu_func_parent_menu_id_idx (parent_menu_id),
646 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
649 -- table structure for table fn_menu_functional_ancestors
652 create table fn_menu_functional_ancestors (
653 id int(11) not null auto_increment,
654 menu_id int(11) not null,
655 ancestor_menu_id int(11) not null,
656 depth int(2) not null,
658 key fk_fn_menu_func_anc_menu_id_idx (menu_id),
659 key fk_fn_menu_func_anc_anc_menu_id_idx (ancestor_menu_id),
660 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,
661 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
664 -- table structure for table fn_menu_functional_roles
666 create table fn_menu_functional_roles (
667 id int(11) not null auto_increment,
668 menu_id int(11) not null,
669 app_id int(11) not null,
670 role_id int(10) not null,
672 key fk_fn_menu_func_roles_menu_id_idx (menu_id),
673 key fk_fn_menu_func_roles_app_id_idx (app_id),
674 key fk_fn_menu_func_roles_role_id_idx (role_id),
675 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,
676 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,
677 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
679 -- ----------------------------------------------------------
680 -- NAME: FN_WORKFLOW; TYPE: TABLE
681 -- ----------------------------------------------------------
682 create table fn_workflow (
683 id mediumint(9) not null auto_increment,
684 name varchar(20) not null,
685 description varchar(500) default null,
686 run_link varchar(300) default null,
687 suspend_link varchar(300) default null,
688 modified_link varchar(300) default null,
689 active_yn varchar(300) default null,
690 created varchar(300) default null,
691 created_by int(11) default null,
692 modified varchar(300) default null,
693 modified_by int(11) default null,
694 workflow_key varchar(50) default null,
696 UNIQUE KEY name (name)
700 -- ----------------------------------------------------------
701 -- NAME: FN_SCHEDULE_WORKFLOWS; TYPE: TABLE
702 -- ----------------------------------------------------------
703 create table fn_schedule_workflows (
704 id_schedule_workflows bigint(25) primary key not null auto_increment,
705 workflow_server_url varchar(45) default null,
706 workflow_key varchar(45) not null,
707 workflow_arguments varchar(45) default null,
708 startDateTimeCron varchar(45) default null,
709 endDateTime TIMESTAMP default NOW(),
710 start_date_time TIMESTAMP default NOW(),
711 recurrence varchar(45) default null
715 -- ----------------------------------------------------------
716 -- NAME: FN_SHARED_CONTEXT; TYPE: TABLE
717 -- ----------------------------------------------------------
718 create table fn_shared_context (
719 id int(11) not null auto_increment,
720 create_time timestamp not null,
721 context_id character varying(64) not null,
722 ckey character varying(128) not null,
723 cvalue character varying(1024),
725 UNIQUE KEY session_key (context_id, ckey) );
728 -- ----------------------------------------------------------
729 -- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
730 -- ----------------------------------------------------------
731 create table fn_qz_job_details (
732 SCHED_NAME VARCHAR(120) not null,
733 JOB_NAME VARCHAR(200) not null,
734 JOB_GROUP VARCHAR(200) not null,
735 DESCRIPTION VARCHAR(250) null,
736 JOB_CLASS_NAME VARCHAR(250) not null,
737 IS_DURABLE VARCHAR(1) not null,
738 IS_NONCONCURRENT VARCHAR(1) not null,
739 IS_UPDATE_DATA VARCHAR(1) not null,
740 REQUESTS_RECOVERY VARCHAR(1) not null,
742 primary key (SCHED_NAME,JOB_NAME,JOB_GROUP)
745 -- ----------------------------------------------------------
746 -- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
747 -- ----------------------------------------------------------
748 create table fn_qz_triggers (
749 SCHED_NAME VARCHAR(120) not null,
750 TRIGGER_NAME VARCHAR(200) not null,
751 TRIGGER_GROUP VARCHAR(200) not null,
752 JOB_NAME VARCHAR(200) not null,
753 JOB_GROUP VARCHAR(200) not null,
754 DESCRIPTION VARCHAR(250) null,
755 NEXT_FIRE_TIME BIGINT(13) null,
756 PREV_FIRE_TIME BIGINT(13) null,
757 PRIORITY INTEGER null,
758 TRIGGER_STATE VARCHAR(16) not null,
759 TRIGGER_TYPE VARCHAR(8) not null,
760 START_TIME BIGINT(13) not null,
761 END_TIME BIGINT(13) null,
762 CALENDAR_NAME VARCHAR(200) null,
763 MISFIRE_INSTR SMALLINT(2) null,
765 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
766 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
767 REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
770 -- ----------------------------------------------------------
771 -- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
772 -- ----------------------------------------------------------
773 create table fn_qz_simple_triggers (
774 SCHED_NAME VARCHAR(120) not null,
775 TRIGGER_NAME VARCHAR(200) not null,
776 TRIGGER_GROUP VARCHAR(200) not null,
777 REPEAT_COUNT BIGINT(7) not null,
778 REPEAT_INTERVAL BIGINT(12) not null,
779 TIMES_TRIGGERED BIGINT(10) not null,
780 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
781 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
782 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
785 -- ----------------------------------------------------------
786 -- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
787 -- ----------------------------------------------------------
788 create table fn_qz_cron_triggers (
789 SCHED_NAME VARCHAR(120) not null,
790 TRIGGER_NAME VARCHAR(200) not null,
791 TRIGGER_GROUP VARCHAR(200) not null,
792 CRON_EXPRESSION VARCHAR(120) not null,
793 TIME_ZONE_ID VARCHAR(80),
794 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
795 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
796 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
799 -- ----------------------------------------------------------
800 -- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
801 -- ----------------------------------------------------------
802 create table fn_qz_simprop_triggers (
803 SCHED_NAME VARCHAR(120) not null,
804 TRIGGER_NAME VARCHAR(200) not null,
805 TRIGGER_GROUP VARCHAR(200) not null,
806 STR_PROP_1 VARCHAR(512) null,
807 STR_PROP_2 VARCHAR(512) null,
808 STR_PROP_3 VARCHAR(512) null,
811 LONG_PROP_1 BIGINT null,
812 LONG_PROP_2 BIGINT null,
813 DEC_PROP_1 NUMERIC(13,4) null,
814 DEC_PROP_2 NUMERIC(13,4) null,
815 BOOL_PROP_1 VARCHAR(1) null,
816 BOOL_PROP_2 VARCHAR(1) null,
817 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
818 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
819 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
822 -- ----------------------------------------------------------
823 -- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
824 -- ----------------------------------------------------------
825 create table fn_qz_blob_triggers (
826 SCHED_NAME VARCHAR(120) not null,
827 TRIGGER_NAME VARCHAR(200) not null,
828 TRIGGER_GROUP VARCHAR(200) not null,
830 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
831 INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
832 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
833 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
836 -- ----------------------------------------------------------
837 -- NAME: FN_QZ_CALENDARS; TYPE: TABLE
838 -- ----------------------------------------------------------
839 create table fn_qz_calendars (
840 SCHED_NAME VARCHAR(120) not null,
841 CALENDAR_NAME VARCHAR(200) not null,
842 CALENDAR BLOB not null,
843 primary key (SCHED_NAME,CALENDAR_NAME)
847 -- ----------------------------------------------------------
848 -- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
849 -- ----------------------------------------------------------
850 create table fn_qz_paused_trigger_grps (
851 SCHED_NAME VARCHAR(120) not null,
852 TRIGGER_GROUP VARCHAR(200) not null,
853 primary key (SCHED_NAME,TRIGGER_GROUP)
856 -- ----------------------------------------------------------
857 -- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
858 -- ----------------------------------------------------------
859 create table fn_qz_fired_triggers (
860 SCHED_NAME VARCHAR(120) not null,
861 ENTRY_ID VARCHAR(95) not null,
862 TRIGGER_NAME VARCHAR(200) not null,
863 TRIGGER_GROUP VARCHAR(200) not null,
864 INSTANCE_NAME VARCHAR(200) not null,
865 FIRED_TIME BIGINT(13) not null,
866 SCHED_TIME BIGINT(13) not null,
867 PRIORITY INTEGER not null,
868 STATE VARCHAR(16) not null,
869 JOB_NAME VARCHAR(200) null,
870 JOB_GROUP VARCHAR(200) null,
871 IS_NONCONCURRENT VARCHAR(1) null,
872 REQUESTS_RECOVERY VARCHAR(1) null,
873 primary key (SCHED_NAME,ENTRY_ID)
876 -- ----------------------------------------------------------
877 -- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
878 -- ----------------------------------------------------------
879 create table fn_qz_scheduler_state (
880 SCHED_NAME VARCHAR(120) not null,
881 INSTANCE_NAME VARCHAR(200) not null,
882 LAST_CHECKIN_TIME BIGINT(13) not null,
883 CHECKIN_INTERVAL BIGINT(13) not null,
884 primary key (SCHED_NAME,INSTANCE_NAME)
887 -- ----------------------------------------------------------
888 -- NAME: FN_QZ_LOCKS; TYPE: TABLE
889 -- ----------------------------------------------------------
890 create table fn_qz_locks (
891 SCHED_NAME VARCHAR(120) not null,
892 LOCK_NAME VARCHAR(40) not null,
893 primary key (SCHED_NAME,LOCK_NAME)
896 -- ----------------------------------------------------------
897 -- NAME: FN_MENU_FAVORITES; TYPE: TABLE
898 -- ----------------------------------------------------------
900 create table fn_menu_favorites (
901 user_id int(11) not null,
902 menu_id int(11) not null,
903 primary key (user_id,menu_id)
906 -- FACELIFT - Table for Events, News and Resources
908 create table fn_common_widget_data(
909 id int auto_increment,
910 category varchar(32),
913 content varchar(4096),
914 event_date varchar(10), -- YYYY-MM-DD
919 create table fn_app_contact_us (
920 app_id int(11) not null,
921 contact_name varchar(128) default null,
922 contact_email varchar(128) default null,
923 url varchar(256) default null,
924 active_yn varchar(2) default null,
925 description varchar(1024) default null,
926 primary key (app_id),
927 constraint fk_fn_a_con__ref_202_fn_app foreign key (app_id) references fn_app (app_id)
931 create table fn_pers_user_app_sel (
932 id int(11) not null auto_increment,
933 user_id int(11) not null,
934 app_id int(11) not null,
935 status_cd char(1) not null,
937 constraint fk_1_fn_pers_user_app_sel_fn_user foreign key (user_id) references fn_user (user_id),
938 constraint fk_2_fn_pers_user_app_sel_fn_app foreign key (app_id) references fn_app (app_id)
943 -- new 1702 tables/views
944 -- 1702 Additions for User Notifications
945 -- ----------------------------------------------------------
946 -- NAME: ep_notification; TYPE: TABLE
947 -- ----------------------------------------------------------
948 create table ep_notification (
949 notification_ID int(11) primary key not null auto_increment,
950 is_for_online_users char(1) default 'N',
951 is_for_all_roles char(1) default 'N',
952 active_YN char(1) default 'Y',
953 msg_header varchar(100),
954 msg_description varchar(2000),
955 msg_source varchar(50) default 'EP',
956 start_time timestamp default now(),
957 end_time timestamp null,
959 creator_ID int(11) null default null,
960 created_date timestamp null default null,
961 notification_hyperlink varchar(512) null default null -- new column for 1710
964 -- ----------------------------------------------------------
965 -- NAME: ep_role_notification; TYPE: TABLE
966 -- ----------------------------------------------------------
967 create table ep_role_notification (
968 ID int(11) primary key not null auto_increment,
969 notification_ID int(11),
971 recv_user_id int(11) null,
972 constraint fk_ep_role_notif_fn_role foreign key (role_ID) references fn_role(role_id),
973 constraint fk_ep_role_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
976 -- ----------------------------------------------------------
977 -- NAME: ep_user_notification; TYPE: TABLE
978 -- ----------------------------------------------------------
979 create table ep_user_notification (
980 ID int(11) primary key not null auto_increment,
982 notification_ID int(11),
983 is_viewed char(1) default 'N',
984 updated_time timestamp default now(),
985 constraint fk_ep_urole_notif_fn_user foreign key (User_ID) references fn_user(user_id),
986 constraint fk_ep_urole_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
989 -- ----------------------------------------------------------
990 -- NAME: ep_pers_user_app_sort; TYPE: Table
991 -- ----------------------------------------------------------
993 CREATE TABLE ep_pers_user_app_sort (
994 id int(11) not null primary key auto_increment,
995 user_id int(11) not null,
996 sort_pref char(1) not null,
997 unique key uk_1_ep_pers_user_app_sort (user_id),
998 constraint fk_ep_pers_user_app_sort_fn_user foreign key (user_id) references fn_user(user_id)
1000 -- ----------------------------------------------------------
1001 -- NAME: ep_pers_user_app_man_sort; TYPE: Table
1002 -- ----------------------------------------------------------
1004 CREATE TABLE ep_pers_user_app_man_sort (
1005 id int(11) not null primary key auto_increment,
1006 user_id int(11) not null,
1007 app_id int(11) not null,
1008 sort_order int(11) not null,
1009 unique key uk_1_ep_pers_user_app_man_sort (user_id, app_id),
1010 constraint fk_ep_pers_app_man_sort_fn_user foreign key (user_id) references fn_user(user_id),
1011 constraint fk_ep_pers_app_man_sort_fn_app foreign key (app_id) references fn_app(app_id)
1014 -- ----------------------------------------------------------
1015 -- NAME: ep_widget_catalog; TYPE: Table
1016 -- ----------------------------------------------------------
1018 CREATE TABLE ep_widget_catalog (
1019 widget_id int(11) not null auto_increment,
1020 wdg_name varchar(100) not null default '?',
1021 service_id int(11) default null,
1022 wdg_desc varchar(200) default null,
1023 wdg_file_loc varchar(256) not null default '?',
1024 all_user_flag char(1) not null default 'N',
1025 primary key (widget_id)
1028 -- ----------------------------------------------------------
1029 -- NAME: ep_widget_catalog_role; TYPE: Table
1030 -- ----------------------------------------------------------
1031 create table ep_widget_catalog_role (
1032 widget_id int(10) not null,
1033 app_id int(11) default '1',
1034 role_id int(10) not null,
1035 key fk_ep_widget_catalog_role_fn_widget (widget_id),
1036 key fk_ep_widget_catalog_role_ref_fn_role (role_id),
1037 key fk_ep_widget_catalog_role_app_id (app_id),
1038 constraint fk_ep_widget_catalog_role_fn_widget foreign key (widget_id) references ep_widget_catalog (widget_id),
1039 constraint fk_ep_widget_catalog_role_ref_fn_role foreign key (role_id) references fn_role (role_id),
1040 constraint fk_ep_widget_catalog_role_app_id foreign key (app_id) references fn_app (app_id)
1043 -- ----------------------------------------------------------
1044 -- NAME: ep_pers_user_widget_placement; TYPE: Table
1045 -- ----------------------------------------------------------
1046 CREATE TABLE ep_pers_user_widget_placement (
1047 id int(11) not null primary key auto_increment,
1048 user_id int(11) not null,
1049 widget_id int(11) not null,
1054 unique key uk_1_ep_pers_user_widg_place (user_id, widget_id),
1055 constraint fk_ep_pers_user_widg_place_fn_user foreign key (user_id) references fn_user(user_id),
1056 constraint fk_ep_pers_user_widg_place_ep_widg foreign key (widget_id) references ep_widget_catalog(widget_id)
1059 -- ----------------------------------------------------------
1060 -- NAME: ep_pers_user_widget_sel; TYPE: TABLE
1061 -- ----------------------------------------------------------
1062 CREATE TABLE ep_pers_user_widget_sel (
1063 id int(11) not null primary key auto_increment,
1064 user_id int(11) not null,
1065 widget_id int(11) not null,
1066 status_cd char(1) not null,
1067 unique key uk_1_ep_pers_user_widg_sel_user_widg (user_id, widget_id),
1068 CONSTRAINT fk_1_ep_pers_user_wid_sel_fn_user FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
1069 CONSTRAINT fk_2_ep_pers_user_wid_sel_ep_wid FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
1072 -- ----------------------------------------------------------
1073 -- NAME: ep_widget_catalog_files; TYPE: TABLE
1074 -- ----------------------------------------------------------
1075 CREATE TABLE ep_widget_catalog_files (
1076 file_id int(11) not null primary key auto_increment,
1078 widget_name VARCHAR(100) NOT NULL,
1079 framework_js LONGBLOB NULL,
1080 controller_js LONGBLOB NULL,
1081 markup_html LONGBLOB NULL,
1082 widget_css LONGBLOB NULL
1085 -- ----------------------------------------------------------
1086 -- NAME: fn_role_v; TYPE: VIEW
1087 -- All roles without an APP_ID are Portal only.
1088 -- ----------------------------------------------------------
1089 create view fn_role_v as
1090 select fn_role.role_id as role_id,
1091 fn_role.role_name as role_name,
1092 fn_role.active_yn as active_yn,
1093 fn_role.priority as priority,
1094 fn_role.app_id as app_id,
1095 fn_role.app_role_id as app_role_id
1096 from fn_role where isnull(fn_role.app_id);
1098 -- end new 1702 tables/views
1100 -- new 1707 tables/views
1102 -- ----------------------------------------------------------
1103 -- NAME: ep_user_roles_request; TYPE: TABLE
1104 -- ----------------------------------------------------------
1106 create table ep_user_roles_request (
1107 req_id int(11) not null primary key auto_increment,
1108 user_id int(11) not null,
1109 app_id int(11) not null,
1110 created_date timestamp default now(),
1111 updated_date timestamp default now(),
1112 request_status character varying(50) not null,
1113 constraint fk_user_roles_req_fn_user foreign key (user_id) references fn_user(user_id),
1114 constraint fk_user_roles_req_fn_app foreign key (app_id) references fn_app(app_id)
1118 -- ----------------------------------------------------------
1119 -- NAME: ep_user_roles_request_det; TYPE: TABLE
1120 -- ----------------------------------------------------------
1121 create table ep_user_roles_request_det (
1122 id int(11) not null primary key auto_increment,
1123 req_id int(11) default null,
1124 requested_role_id int(10) not null,
1125 request_type character varying(10) not null,
1126 constraint fk_user_roles_req_fn_req_id foreign key (req_id) references ep_user_roles_request(req_id),
1127 constraint fk_user_roles_req_fn_role_id foreign key (requested_role_id) references fn_role(role_id)
1130 -- ----------------------------------------------------------
1131 -- NAME: ep_microservice; TYPE: TABLE
1132 -- ----------------------------------------------------------
1134 CREATE TABLE ep_microservice (
1135 id INT(11) NOT NULL AUTO_INCREMENT,
1136 name VARCHAR(50) NULL DEFAULT NULL,
1137 description VARCHAR(50) NULL DEFAULT NULL,
1138 appId INT(11) NULL DEFAULT NULL,
1139 endpoint_url VARCHAR(200) NULL DEFAULT NULL,
1140 security_type VARCHAR(50) NULL DEFAULT NULL,
1141 username VARCHAR(50) NULL DEFAULT NULL,
1142 password VARCHAR(50) NOT NULL,
1143 active CHAR(1) NOT NULL DEFAULT 'Y',
1145 CONSTRAINT FK_FN_APP_EP_MICROSERVICE FOREIGN KEY (appId) REFERENCES fn_app (app_id)
1148 -- ----------------------------------------------------------
1149 -- NAME: ep_microservice_parameter; TYPE: TABLE
1150 -- ----------------------------------------------------------
1152 CREATE TABLE ep_microservice_parameter (
1153 id INT(11) NOT NULL AUTO_INCREMENT,
1154 service_id INT(11) NULL DEFAULT NULL,
1155 para_key VARCHAR(50) NULL DEFAULT NULL,
1156 para_value VARCHAR(50) NULL DEFAULT NULL,
1158 CONSTRAINT FK_EP_MICROSERIVCE_EP_MICROSERVICE_PARAMETER FOREIGN KEY (service_id) REFERENCES ep_microservice (id)
1162 -- ----------------------------------------------------------
1163 -- NAME: ep_widget_preview_files; TYPE: TABLE
1164 -- ----------------------------------------------------------
1166 CREATE TABLE ep_widget_preview_files (
1167 preview_id INT(11) NOT NULL AUTO_INCREMENT,
1168 html_file LONGBLOB NULL,
1169 css_file LONGBLOB NULL,
1170 javascript_file LONGBLOB NULL,
1171 framework_file LONGBLOB NULL,
1172 PRIMARY KEY (preview_id)
1175 -- ----------------------------------------------------------
1176 -- NAME: ep_widget_microservice; TYPE: TABLE
1177 -- ----------------------------------------------------------
1179 CREATE TABLE ep_widget_microservice (
1180 id INT(11) NOT NULL AUTO_INCREMENT,
1181 widget_id INT(11) NOT NULL DEFAULT '0',
1182 microservice_id INT(11) NOT NULL DEFAULT '0',
1184 CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_MICROSERVICE FOREIGN KEY (microservice_id) REFERENCES ep_microservice (id),
1185 CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_WIDGET FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
1188 -- ----------------------------------------------------------
1189 -- NAME: ep_basic_auth_account; TYPE: TABLE
1190 -- ----------------------------------------------------------
1192 create table ep_basic_auth_account (
1193 id INT(11) NOT NULL AUTO_INCREMENT,
1194 ext_app_name VARCHAR(50) NOT NULL,
1195 username VARCHAR(50) NOT NULL,
1196 password VARCHAR(50) null default null,
1197 active_yn char(1) NOT NULL default 'Y',
1201 -- ----------------------------------------------------------
1202 -- NAME: ep_widget_catalog_parameter; TYPE: TABLE
1203 -- ----------------------------------------------------------
1205 create table ep_widget_catalog_parameter (
1206 id INT(11) NOT NULL AUTO_INCREMENT,
1207 widget_id INT(11) NOT NULL,
1208 user_id INT(11) NOT NULL,
1209 param_id INT(11) NOT NULL,
1210 user_value VARCHAR(50) NULL,
1212 CONSTRAINT EP_FN_USER_WIDGET_PARAMETER_FK FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
1213 CONSTRAINT EP_WIDGET_CATALOG_WIDGET_PARAMETER_FK FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id),
1214 CONSTRAINT EP_PARAMETER_ID_WIDGET_PARAMETER_FK FOREIGN KEY (param_id) REFERENCES ep_microservice_parameter (id)
1217 -- ----------------------------------------------------------
1218 -- NAME: ep_web_analytics_source; TYPE: TABLE
1219 -- ----------------------------------------------------------
1221 create table ep_web_analytics_source(
1222 resource_id int(11) NOT NULL auto_increment,
1223 app_id int(11) NOT NULL,
1224 report_source varchar(500),
1225 report_name varchar(500),
1226 PRIMARY KEY (resource_id),
1227 FOREIGN KEY (app_id) REFERENCES fn_app(app_id)
1230 -- Machine Learning Tables
1231 -- ----------------------------------------------------------
1232 -- NAME: ep_ml_model; TYPE: TABLE
1233 -- ----------------------------------------------------------
1235 create table ep_ml_model(
1236 time_stamp timestamp default now(),
1237 group_id int(11) NOT NULL,
1239 PRIMARY KEY (time_stamp,group_id)
1241 -- ----------------------------------------------------------
1242 -- NAME: ep_ml_rec; TYPE: TABLE
1243 -- ----------------------------------------------------------
1245 create table ep_ml_rec(
1246 time_stamp timestamp default now(),
1247 org_user_id varchar(20) NOT NULL,
1248 rec varchar(4000) DEFAULT NULL,
1249 PRIMARY KEY (time_stamp,org_user_id)
1252 -- ----------------------------------------------------------
1253 -- NAME: ep_ml_user; TYPE: TABLE
1254 -- ----------------------------------------------------------
1256 create table ep_ml_user(
1257 time_stamp timestamp default now(),
1258 org_user_id varchar(20) NOT NULL,
1259 group_id int(11) NOT NULL,
1260 PRIMARY KEY (time_stamp,org_user_id)
1263 -- ----------------------------------------------------------
1264 -- NAME: ep_endpoints; TYPE: TABLE
1265 -- ----------------------------------------------------------
1267 create table ep_endpoints (
1268 id INT(11) NOT NULL AUTO_INCREMENT,
1269 url VARCHAR(50) NOT NULL,
1273 -- ----------------------------------------------------------
1274 -- NAME: ep_endpoints_basic_auth_account; TYPE: TABLE
1275 -- ----------------------------------------------------------
1277 create table ep_endpoints_basic_auth_account (
1278 id INT(11) NOT NULL AUTO_INCREMENT,
1279 ep_id INT(11) DEFAULT NULL,
1280 account_id INT(11) DEFAULT NULL,
1282 CONSTRAINT ep_endpoints_basic_auth_account_account_id_fk FOREIGN KEY (account_id) REFERENCES ep_basic_auth_account (id),
1283 CONSTRAINT ep_endpoints_basic_auth_account_ep_id_fk FOREIGN KEY (ep_id) REFERENCES ep_endpoints (id)
1287 -- end new 1707 tables/views
1289 -- new 1710 tables/views
1291 -- ----------------------------------------------------------
1292 -- NAME: ep_app_function; TYPE: TABLE
1293 -- ----------------------------------------------------------
1295 CREATE TABLE ep_app_function (
1296 app_id INT(11) NOT NULL,
1297 function_cd VARCHAR(250) NOT NULL,
1298 function_name VARCHAR(250) NOT NULL,
1299 PRIMARY KEY (function_cd, app_id),
1300 INDEX fk_ep_app_function_app_id (app_id),
1301 CONSTRAINT fk_ep_app_function_app_id FOREIGN KEY (app_id) REFERENCES fn_app (app_id)
1304 -- ----------------------------------------------------------
1305 -- NAME: ep_app_role_function; TYPE: TABLE
1306 -- ----------------------------------------------------------
1308 CREATE TABLE `ep_app_role_function` (
1309 `id` INT(11) NOT NULL AUTO_INCREMENT,
1310 `app_id` INT(11) NOT NULL,
1311 `role_id` INT(11) NOT NULL,
1312 `function_cd` VARCHAR(250) NOT NULL,
1313 `role_app_id` VARCHAR(20) NULL DEFAULT NULL,
1315 UNIQUE INDEX `UNIQUE KEY` (`app_id`, `role_id`, `function_cd`),
1316 CONSTRAINT `fk_ep_app_role_function_app_id` FOREIGN KEY (`app_id`) REFERENCES `fn_app` (`app_id`),
1317 CONSTRAINT `fk_ep_app_role_function_ep_app_func` FOREIGN KEY (`app_id`, `function_cd`) REFERENCES `ep_app_function` (`app_id`, `function_cd`),
1318 CONSTRAINT `fk_ep_app_role_function_role_id` FOREIGN KEY (`role_id`) REFERENCES `fn_role` (`role_id`)
1321 -- end new 1710 tables/views
1323 -- ----------------------------------------------------------
1324 -- NAME: QUARTZ TYPE: INDEXES
1325 -- ----------------------------------------------------------
1326 create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
1327 create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
1328 create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
1329 create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
1330 create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
1331 create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
1332 create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
1333 create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
1334 create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
1335 create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
1336 create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
1337 create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
1338 create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
1339 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);
1340 create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
1341 create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
1342 create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
1343 create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
1344 create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
1345 create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
1348 -- ------------------ create view section
1350 -- name: v_url_access; type: view
1352 create view v_url_access as
1353 select distinct m.action as url,
1356 where (m.action is not null)
1358 select distinct t.action as url,
1361 where (t.action is not null)
1363 select r.restricted_url as url,
1365 from fn_restricted_url r;
1367 -- ------------------ alter table add constraint primary key section
1369 -- name: cr_favorite_reports_user_idrep_id; type: constraint
1371 alter table cr_favorite_reports
1372 add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
1374 -- name: cr_folder_folder_id; type: constraint
1376 alter table cr_folder
1377 add constraint cr_folder_folder_id primary key (folder_id);
1379 -- name: cr_folder_access_folder_access_id; type: constraint
1381 alter table cr_folder_access
1382 add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
1384 -- name: cr_hist_user_map_hist_iduser_id; type: constraint
1386 alter table cr_hist_user_map
1387 add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
1389 -- name: cr_lu_file_type_lookup_id; type: constraint
1391 alter table cr_lu_file_type
1392 add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
1394 -- name: cr_raptor_action_img_image_id; type: constraint
1396 alter table cr_raptor_action_img
1397 add constraint cr_raptor_action_img_image_id primary key (image_id);
1399 -- name: cr_raptor_pdf_img_image_id; type: constraint
1401 alter table cr_raptor_pdf_img
1402 add constraint cr_raptor_pdf_img_image_id primary key (image_id);
1404 -- name: cr_remote_schema_info_schema_prefix; type: constraint
1406 alter table cr_remote_schema_info
1407 add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
1409 -- name: cr_report_rep_id; type: constraint
1411 alter table cr_report
1412 add constraint cr_report_rep_id primary key (rep_id);
1414 -- name: cr_report_access_rep_idorder_no; type: constraint
1416 alter table cr_report_access
1417 add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
1419 -- name: cr_report_email_sent_log_log_id; type: constraint
1421 alter table cr_report_email_sent_log
1422 add constraint cr_report_email_sent_log_log_id primary key (log_id);
1424 -- name: cr_report_file_history_hist_id; type: constraint
1426 alter table cr_report_file_history
1427 add constraint cr_report_file_history_hist_id primary key (hist_id);
1429 -- name: cr_report_schedule_schedule_id; type: constraint
1431 alter table cr_report_schedule
1432 add constraint cr_report_schedule_schedule_id primary key (schedule_id);
1434 -- name: cr_report_schedule_users_schedule_idrep_iduser_idorder_no; type: constraint
1436 alter table cr_report_schedule_users
1437 add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
1439 -- name: cr_report_template_map_report_id; type: constraint
1441 alter table cr_report_template_map
1442 add constraint cr_report_template_map_report_id primary key (report_id);
1444 -- name: cr_table_role_table_namerole_id; type: constraint
1446 alter table cr_table_role
1447 add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
1449 -- name: cr_table_source_table_name; type: constraint
1451 alter table cr_table_source
1452 add constraint cr_table_source_table_name primary key (table_name);
1454 -- name: fn_audit_action_audit_action_id; type: constraint
1456 alter table fn_audit_action
1457 add constraint fn_audit_action_audit_action_id primary key (audit_action_id);
1460 -- name: fk_fn_audit_ref_205_fn_lu_ac; type: constraint
1462 alter table fn_audit_log
1463 add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
1465 -- name: fk_fn_role__ref_201_fn_funct; type: constraint
1467 alter table fn_role_function
1468 add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
1470 -- name: fn_chat_logs_chat_log_id; type: constraint
1472 alter table fn_chat_logs
1473 add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
1475 -- name: fn_chat_room_chat_room_id; type: constraint
1477 alter table fn_chat_room
1478 add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
1480 -- name: fn_chat_users_id; type: constraint
1482 alter table fn_chat_users
1483 add constraint fn_chat_users_id primary key (id);
1485 -- name: fn_lu_alert_method_alert_method_cd; type: constraint
1487 alter table fn_lu_alert_method
1488 add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
1490 -- name: fn_lu_broadcast_site_broadcast_site_cd; type: constraint
1492 alter table fn_lu_broadcast_site
1493 add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
1495 -- name: fn_lu_priority_priority_id; type: constraint
1497 alter table fn_lu_priority
1498 add constraint fn_lu_priority_priority_id primary key (priority_id);
1500 -- name: fn_lu_role_type_role_type_id; type: constraint
1502 alter table fn_lu_role_type
1503 add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
1505 -- name: fn_lu_tab_set_tab_set_cd; type: constraint
1507 alter table fn_lu_tab_set
1508 add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
1510 -- name: fn_lu_timezone_timezone_id; type: constraint
1512 alter table fn_lu_timezone
1513 add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
1515 -- name: fn_org_org_id; type: constraint
1518 add constraint fn_org_org_id primary key (org_id);
1520 -- name: fn_restricted_url_restricted_urlfunction_cd; type: constraint
1522 alter table fn_restricted_url
1523 add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
1525 -- name: fn_role_composite_parent_role_idchild_role_id; type: constraint
1527 alter table fn_role_composite
1528 add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
1530 -- name: fn_role_function_role_idfunction_cd; type: constraint
1532 alter table fn_role_function
1533 add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
1535 -- name: fn_tab_tab_cd; type: constraint
1538 add constraint fn_tab_tab_cd primary key (tab_cd);
1540 -- name: fn_tab_selected_selected_tab_cdtab_uri; type: constraint
1542 alter table fn_tab_selected
1543 add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
1545 -- name: fn_user_pseudo_role_pseudo_role_iduser_id; type: constraint
1547 alter table fn_user_pseudo_role
1548 add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
1550 -- name: fn_user_role_user_idrole_id; type: constraint
1552 alter table fn_user_role
1553 add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
1554 -- ------------------ create index section
1556 -- name: cr_report_create_idpublic_yntitle; type: index
1558 create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
1560 -- name: cr_table_join_dest_table_name; type: index
1562 create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
1564 -- name: cr_table_join_src_table_name; type: index
1566 create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
1568 -- name: fn_audit_log_activity_cd; type: index
1570 create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
1572 -- name: fn_audit_log_user_id; type: index
1574 create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
1576 -- name: fn_org_access_cd; type: index
1578 create index fn_org_access_cd using btree on fn_org (access_cd);
1580 -- name: fn_role_function_function_cd; type: index
1582 create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
1584 -- name: fn_role_function_role_id; type: index
1586 create index fn_role_function_role_id using btree on fn_role_function (role_id);
1588 -- name: fn_user_address_id; type: index
1590 create index fn_user_address_id using btree on fn_user (address_id);
1592 -- name: fn_user_alert_method_cd; type: index
1594 create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd);
1596 -- name: fn_user_hrid; type: index
1598 create unique index fn_user_hrid using btree on fn_user (hrid);
1600 -- name: fn_user_login_id; type: index
1602 create unique index fn_user_login_id using btree on fn_user (login_id);
1604 -- name: fn_user_org_id; type: index
1606 create index fn_user_org_id using btree on fn_user (org_id);
1608 -- name: fn_user_role_role_id; type: index
1610 create index fn_user_role_role_id using btree on fn_user_role (role_id);
1612 -- name: fn_user_role_user_id; type: index
1614 create index fn_user_role_user_id using btree on fn_user_role (user_id);
1616 -- name: fk_fn_user__ref_178_fn_app_idx; type: index
1618 create index fk_fn_user__ref_178_fn_app_idx on fn_user_role (app_id);
1620 -- name: fn_role_name_app_id_idx; type: index
1622 create unique index fn_role_name_app_id_idx using btree on fn_role (role_name,app_id);
1626 create index ep_notif_recv_user_id_idx using btree on ep_role_notification (recv_user_id);
1630 -- ------------------ alter table add constraint foreign key section
1632 -- name: fk_fn_user__ref_178_fn_app; type: fk constraint
1634 alter table fn_user_role
1635 add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
1637 -- name: fk_cr_repor_ref_14707_cr_repor; type: fk constraint
1639 alter table cr_report_schedule
1640 add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
1642 -- name: fk_cr_repor_ref_14716_cr_repor; type: fk constraint
1644 alter table cr_report_schedule_users
1645 add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
1647 -- name: fk_cr_repor_ref_17645_cr_repor; type: fk constraint
1649 alter table cr_report_log
1650 add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
1652 -- name: fk_cr_repor_ref_8550_cr_repor; type: fk constraint
1654 alter table cr_report_access
1655 add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
1657 -- name: fk_cr_report_rep_id; type: fk constraint
1659 alter table cr_report_email_sent_log
1660 add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
1662 -- name: fk_cr_table_ref_311_cr_tab; type: fk constraint
1664 alter table cr_table_join
1665 add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
1667 -- name: fk_cr_table_ref_315_cr_tab; type: fk constraint
1669 alter table cr_table_join
1670 add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
1672 -- name: fk_cr_table_ref_32384_cr_table; type: fk constraint
1674 alter table cr_table_role
1675 add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
1677 -- name: fk_fn_tab_function_cd; type: fk constraint
1680 add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
1682 -- name: fk_fn_tab_selected_tab_cd; type: fk constraint
1684 alter table fn_tab_selected
1685 add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
1687 -- name: fk_fn_tab_set_cd; type: fk constraint
1690 add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
1692 -- name: fk_fn_user_ref_110_fn_org; type: fk constraint
1695 add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id);
1697 -- name: fk_fn_user_ref_123_fn_lu_al; type: fk constraint
1700 add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd);
1702 -- name: fk_fn_user_ref_197_fn_user; type: fk constraint
1705 add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id);
1707 -- name: fk_fn_user_ref_198_fn_user; type: fk constraint
1710 add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id);
1712 -- name: fk_fn_user_ref_199_fn_user; type: fk constraint
1715 add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id);
1717 -- name: fk_parent_key_cr_folder; type: fk constraint
1719 alter table cr_folder
1720 add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
1722 -- name: fk_pseudo_role_pseudo_role_id; type: fk constraint
1724 alter table fn_user_pseudo_role
1725 add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
1727 -- name: fk_pseudo_role_user_id; type: fk constraint
1729 alter table fn_user_pseudo_role
1730 add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
1732 -- name: fk_restricted_url_function_cd; type: fk constraint
1734 alter table fn_restricted_url
1735 add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
1737 -- name: fk_timezone; type: fk constraint
1740 add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id);
1742 -- name: sys_c0014614; type: fk constraint
1744 alter table cr_report_file_history
1745 add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
1747 -- name: sys_c0014615; type: fk constraint
1749 alter table cr_report_file_history
1750 add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
1752 -- name: sys_c0014616; type: fk constraint
1754 alter table cr_hist_user_map
1755 add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
1757 -- name: sys_c0014617; type: fk constraint
1759 alter table cr_hist_user_map
1760 add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);
1762 -- name: sys_c0014618; type: fk constraint
1764 alter table fn_menu_favorites
1765 add constraint sys_c0014618 foreign key (user_id) references fn_user(user_id);
1768 -- name: sys_c0014619; type: fk constraint
1770 alter table fn_menu_favorites
1771 add constraint sys_c0014619 foreign key (menu_id) references fn_menu_functional(menu_id);