1 -- ---------------------------------------------------------------------------------------------------------------
2 -- This is the 3.2.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 SET GLOBAL character_set_client = utf8;
15 SET GLOBAL character_set_connection = utf8;
16 SET GLOBAL character_set_database = utf8;
17 SET GLOBAL character_set_results = utf8;
18 SET GLOBAL character_set_server = utf8;
20 create database portal;
24 -- ------------------ create table section
26 -- name: cr_favorite_reports; type: table
28 create table cr_favorite_reports (
29 user_id integer not null,
30 rep_id integer not null
33 -- name: cr_filehist_log; type: table
35 create table cr_filehist_log (
36 schedule_id numeric(11,0) not null,
37 url character varying(4000),
38 notes character varying(3500),
42 -- name: cr_folder; type: table
44 create table cr_folder (
45 folder_id integer not null,
46 folder_name character varying(50) not null,
47 descr character varying(500),
48 create_id integer not null,
49 create_date timestamp not null,
50 parent_folder_id integer,
51 public_yn character varying(1) default 'n' not null
54 -- name: cr_folder_access; type: table
56 create table cr_folder_access (
57 folder_access_id numeric(11,0) not null,
58 folder_id numeric(11,0) not null,
59 order_no numeric(11,0) not null,
60 role_id numeric(11,0),
61 user_id numeric(11,0),
62 read_only_yn character varying(1) default 'n' not null
65 -- name: cr_hist_user_map; type: table
67 create table cr_hist_user_map (
68 hist_id int(11) not null,
69 user_id int(11) not null
72 -- name: cr_lu_file_type; type: table
74 create table cr_lu_file_type (
75 lookup_id numeric(2,0) not null,
76 lookup_descr character varying(255) not null,
77 active_yn character(1) default 'y',
78 error_code numeric(11,0)
81 -- name: cr_raptor_action_img; type: table
83 create table cr_raptor_action_img (
84 image_id character varying(100) not null,
85 image_loc character varying(400)
88 -- name: cr_raptor_pdf_img; type: table
90 create table cr_raptor_pdf_img (
91 image_id character varying(100) not null,
92 image_loc character varying(400)
95 -- name: cr_remote_schema_info; type: table
97 create table cr_remote_schema_info (
98 schema_prefix character varying(5) not null,
99 schema_desc character varying(75) not null,
100 datasource_type character varying(100)
103 -- name: cr_report; type: table
105 create table cr_report (
106 rep_id numeric(11,0) not null,
107 title character varying(100) not null,
108 descr character varying(255),
109 public_yn character varying(1) default 'n' not null,
111 create_id numeric(11,0),
112 create_date timestamp default now(),
113 maint_id numeric(11,0),
114 maint_date timestamp default now(),
115 menu_id character varying(500),
116 menu_approved_yn character varying(1) default 'n' not null,
117 owner_id numeric(11,0),
118 folder_id integer default 0,
119 dashboard_type_yn character varying(1) default 'n',
120 dashboard_yn character varying(1) default 'n'
123 -- name: cr_report_access; type: table
125 create table cr_report_access (
126 rep_id numeric(11,0) not null,
127 order_no numeric(11,0) not null,
128 role_id numeric(11,0),
129 user_id numeric(11,0),
130 read_only_yn character varying(1) default 'n' not null
133 -- name: cr_report_dwnld_log; type: table
135 create table cr_report_dwnld_log (
136 user_id numeric(11,0) not null,
137 rep_id integer not null,
138 file_name character varying(100) not null,
139 dwnld_start_time timestamp default now() not null,
140 record_ready_time timestamp default now(),
141 filter_params character varying(2000)
144 -- name: cr_report_email_sent_log; type: table
146 create table cr_report_email_sent_log (
147 log_id integer not null,
148 schedule_id numeric(11,0),
149 gen_key character varying(25) not null,
150 rep_id numeric(11,0) not null,
151 user_id numeric(11,0),
152 sent_date timestamp default now(),
153 access_flag character varying(1) default 'y' not null,
154 touch_date timestamp default now()
157 -- name: cr_report_file_history; type: table
159 create table cr_report_file_history (
160 hist_id int(11) not null,
161 sched_user_id numeric(11,0) not null,
162 schedule_id numeric(11,0) not null,
163 user_id numeric(11,0) not null,
164 rep_id numeric(11,0),
166 recurrence character varying(50),
167 file_type_id numeric(2,0),
168 file_name character varying(80),
170 file_size numeric(11,0),
171 raptor_url character varying(4000),
172 error_yn character(1) default 'n',
173 error_code numeric(11,0),
174 deleted_yn character(1) default 'n',
175 deleted_by numeric(38,0)
178 -- name: cr_report_log; type: table
180 create table cr_report_log (
181 rep_id numeric(11,0) not null,
182 log_time timestamp not null,
183 user_id numeric(11,0) not null,
184 action character varying(2000) not null,
185 action_value character varying(50),
186 form_fields character varying(4000)
189 -- name: cr_report_schedule; type: table
191 create table cr_report_schedule (
192 schedule_id numeric(11,0) not null,
193 sched_user_id numeric(11,0) not null,
194 rep_id numeric(11,0) not null,
195 enabled_yn character varying(1) not null,
196 start_date timestamp default now(),
197 end_date timestamp default now(),
198 run_date timestamp default now(),
199 recurrence character varying(50),
200 conditional_yn character varying(1) not null,
201 condition_sql character varying(4000),
202 notify_type integer default 0,
203 max_row integer default 1000,
204 initial_formfields character varying(3500),
205 processed_formfields character varying(3500),
206 formfields character varying(3500),
207 condition_large_sql text,
208 encrypt_yn character(1) default 'n',
209 attachment_yn character(1) default 'y'
212 -- name: cr_report_schedule_users; type: table
214 create table cr_report_schedule_users (
215 schedule_id numeric(11,0) not null,
216 rep_id numeric(11,0) not null,
217 user_id numeric(11,0) not null,
218 role_id numeric(11,0),
219 order_no numeric(11,0) not null
222 -- name: cr_report_template_map; type: table
224 create table cr_report_template_map (
225 report_id integer not null,
226 template_file character varying(200)
229 -- name: cr_schedule_activity_log; type: table
231 create table cr_schedule_activity_log (
232 schedule_id numeric(11,0) not null,
233 url character varying(4000),
234 notes character varying(2000),
238 -- name: cr_table_join; type: table
240 create table cr_table_join (
241 src_table_name character varying(30) not null,
242 dest_table_name character varying(30) not null,
243 join_expr character varying(500) not null
246 -- name: cr_table_role; type: table
248 create table cr_table_role (
249 table_name character varying(30) not null,
250 role_id numeric(11,0) not null
253 -- name: cr_table_source; type: table
255 create table cr_table_source (
256 table_name character varying(30) not null,
257 display_name character varying(30) not null,
258 pk_fields character varying(200),
259 web_view_action character varying(50),
260 large_data_source_yn character varying(1) default 'n' not null,
261 filter_sql character varying(4000),
262 source_db character varying(50)
265 -- name: fn_lu_timezone; type: table
267 create table fn_lu_timezone (
268 timezone_id int(11) not null,
269 timezone_name character varying(100) not null,
270 timezone_value character varying(100) not null
273 create table fn_user (
274 user_id int(11) not null primary key auto_increment,
277 first_name character varying(50),
278 middle_name character varying(50),
279 last_name character varying(50),
280 phone character varying(25),
281 fax character varying(25),
282 cellular character varying(25),
283 email character varying(50),
284 address_id numeric(11,0),
285 alert_method_cd character varying(10),
286 hrid character varying(20),
287 org_user_id CHARACTER VARYING(60),
288 org_code character varying(30),
289 login_id character varying(60),
290 login_pwd character varying(100),
291 last_login_date timestamp,
292 active_yn character varying(1) default 'y' not null,
294 created_date timestamp default now(),
296 modified_date timestamp default now(),
297 is_internal_yn character(1) default 'n' not null,
298 address_line_1 character varying(100),
299 address_line_2 character varying(100),
300 city character varying(50),
301 state_cd character varying(3),
302 zip_code character varying(11),
303 country_cd character varying(3),
304 location_clli character varying(8),
305 org_manager_userid CHARACTER VARYING(20),
306 company character varying(100),
307 department_name character varying(100),
308 job_title character varying(100),
310 department character varying(25),
311 business_unit character varying(25),
312 business_unit_name character varying(100),
313 cost_center character varying(25),
314 fin_loc_code character varying(10),
315 silo_status character varying(10),
316 is_system_user character(1) default 'N',
317 language_id int(2) default 1
320 create table fn_language(
321 language_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
322 language_name VARCHAR(100) NOT NULL,
323 language_alias VARCHAR(100) NOT NULL
326 create table fn_display_text(
327 id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
328 language_id int(11) NOT NULL,
329 text_id int(11) NOT NULL,
330 text_label VARCHAR(100) NOT NULL
333 -- name: fn_role; type: table
335 create table fn_role (
336 role_id int(11) not null primary key auto_increment,
337 role_name character varying(300) not null,
338 active_yn character varying(1) default 'y' not null,
339 priority numeric(4,0),
340 app_id int(11) default null,
341 app_role_id int(11) default null
345 -- name: fn_audit_action; type: table
347 create table fn_audit_action (
348 audit_action_id integer not null,
349 class_name character varying(500) not null,
350 method_name character varying(50) not null,
351 audit_action_cd character varying(20) not null,
352 audit_action_desc character varying(200),
353 active_yn character varying(1)
356 -- name: fn_audit_action_log; type: table
358 create table fn_audit_action_log (
359 audit_log_id integer not null primary key auto_increment,
360 audit_action_cd character varying(200),
361 action_time timestamp,
362 user_id numeric(11,0),
363 class_name character varying(100),
364 method_name character varying(50),
365 success_msg character varying(20),
366 error_msg character varying(500)
369 -- name: fn_lu_activity; type: table
371 create table fn_lu_activity (
372 activity_cd character varying(50) not null primary key,
373 activity character varying(50) not null
376 -- name: fn_audit_log; type: table
378 create table fn_audit_log (
379 log_id int(11) not null primary key auto_increment,
380 user_id int(11) not null,
381 activity_cd character varying(50) not null,
382 audit_date timestamp default now() not null,
383 comments character varying(1000),
384 affected_record_id_bk character varying(500),
385 affected_record_id character varying(4000),
386 constraint fk_fn_audit_ref_209_fn_user foreign key (user_id) references fn_user(user_id)
389 -- name: fn_broadcast_message; type: table
391 create table fn_broadcast_message (
392 message_id int(11) not null primary key auto_increment,
393 message_text character varying(1000) not null,
394 message_location_id numeric(11,0) not null,
395 broadcast_start_date timestamp not null default now(),
396 broadcast_end_date timestamp not null default now(),
397 active_yn character(1) default 'y' not null,
398 sort_order numeric(4,0) not null,
399 broadcast_site_cd character varying(50)
402 -- name: fn_chat_logs; type: table
404 create table fn_chat_logs (
405 chat_log_id integer not null,
406 chat_room_id integer,
408 message character varying(1000),
409 message_date_time timestamp
412 -- name: fn_chat_room; type: table
414 create table fn_chat_room (
415 chat_room_id integer not null,
416 name character varying(50) not null,
417 description character varying(500),
419 created_date timestamp default now(),
420 updated_date timestamp default now()
423 -- name: fn_chat_users; type: table
425 create table fn_chat_users (
426 chat_room_id integer,
428 last_activity_date_time timestamp,
429 chat_status character varying(20),
433 -- name: fn_datasource; type: table
435 create table fn_datasource (
436 id integer not null primary key auto_increment,
437 name character varying(50),
438 driver_name character varying(256),
439 server character varying(256),
441 user_name character varying(256),
442 password character varying(256),
443 url character varying(256),
444 min_pool_size integer,
445 max_pool_size integer,
447 ds_type character varying(20)
450 -- name: fn_function; type: table
452 create table fn_function (
453 function_cd character varying(30) not null primary key,
454 function_name character varying(50) not null
457 -- name: fn_lu_alert_method; type: table
459 create table fn_lu_alert_method (
460 alert_method_cd character varying(10) not null,
461 alert_method character varying(50) not null
464 -- name: fn_lu_broadcast_site; type: table
466 create table fn_lu_broadcast_site (
467 broadcast_site_cd character varying(50) not null,
468 broadcast_site_descr character varying(100)
471 -- name: fn_lu_menu_set; type: table
473 create table fn_lu_menu_set (
474 menu_set_cd character varying(10) not null primary key,
475 menu_set_name character varying(50) not null
478 -- name: fn_lu_priority; type: table
480 create table fn_lu_priority (
481 priority_id numeric(11,0) not null,
482 priority character varying(50) not null,
483 active_yn character(1) not null,
484 sort_order numeric(5,0)
487 -- name: fn_lu_role_type; type: table
489 create table fn_lu_role_type (
490 role_type_id numeric(11,0) not null,
491 role_type character varying(50) not null
494 -- name: fn_lu_tab_set; type: table
496 create table fn_lu_tab_set (
497 tab_set_cd character varying(30) not null,
498 tab_set_name character varying(50) not null
501 -- name: fn_menu; type: table
503 create table fn_menu (
504 menu_id int(11) not null primary key auto_increment,
505 label character varying(100),
507 sort_order numeric(4,0),
508 action character varying(200),
509 function_cd character varying(30),
510 active_yn character varying(1) default 'y' not null,
511 servlet character varying(50),
512 query_string character varying(200),
513 external_url character varying(200),
514 target character varying(25),
515 menu_set_cd character varying(10) default 'app',
516 separator_yn character(1) default 'n',
517 image_src character varying(100),
518 constraint fk_fn_menu_ref_196_fn_menu foreign key (parent_id) references fn_menu(menu_id),
519 constraint fk_fn_menu_menu_set_cd foreign key (menu_set_cd) references fn_lu_menu_set(menu_set_cd)
522 create index idx_fn_menu_label on fn_menu(label);
524 -- name: fn_org; type: table
526 create table fn_org (
527 org_id int(11) not null,
528 org_name character varying(50) not null,
529 access_cd character varying(10)
532 -- name: fn_restricted_url; type: table
534 create table fn_restricted_url (
535 restricted_url character varying(250) not null,
536 function_cd character varying(30) not null
539 -- name: fn_role_composite; type: table
541 create table fn_role_composite (
542 parent_role_id int(11) not null,
543 child_role_id int(11) not null,
544 constraint fk_fn_role_composite_child foreign key (child_role_id) references fn_role(role_id),
545 constraint fk_fn_role_composite_parent foreign key (parent_role_id) references fn_role(role_id)
548 -- name: fn_role_function; type: table
550 create table fn_role_function (
551 role_id int(11) not null,
552 function_cd character varying(30) not null,
553 constraint fk_fn_role__ref_198_fn_role foreign key (role_id) references fn_role(role_id)
556 -- name: fn_tab; type: table
558 create table fn_tab (
559 tab_cd character varying(30) not null,
560 tab_name character varying(50) not null,
561 tab_descr character varying(100),
562 action character varying(100) not null,
563 function_cd character varying(30) not null,
564 active_yn character(1) not null,
565 sort_order numeric(11,0) not null,
566 parent_tab_cd character varying(30),
567 tab_set_cd character varying(30)
570 -- name: fn_tab_selected; type: table
572 create table fn_tab_selected (
573 selected_tab_cd character varying(30) not null,
574 tab_uri character varying(40) not null
577 -- name: fn_user_pseudo_role; type: table
579 create table fn_user_pseudo_role (
580 pseudo_role_id int(11) not null,
581 user_id int(11) not null
584 -- name: fn_user_role; type: table
586 create table fn_user_role (
587 user_id int(10) not null,
588 role_id int(10) not null,
589 priority numeric(4,0),
590 app_id int(11) default 2,
591 constraint fk_fn_user__ref_172_fn_user foreign key (user_id) references fn_user(user_id),
592 constraint fk_fn_user__ref_175_fn_role foreign key (role_id) references fn_role(role_id)
595 -- name: schema_info; type: table
597 create table schema_info (
598 SCHEMA_ID CHARACTER VARYING(25) not null,
599 SCHEMA_DESC CHARACTER VARYING(75) not null,
600 DATASOURCE_TYPE CHARACTER VARYING(100),
601 CONNECTION_URL VARCHAR(200) not null,
602 USER_NAME VARCHAR(45) not null,
603 PASSWORD VARCHAR(45) null default null,
604 DRIVER_CLASS VARCHAR(100) not null,
605 MIN_POOL_SIZE INT not null,
606 MAX_POOL_SIZE INT not null,
607 IDLE_CONNECTION_TEST_PERIOD INT not null
610 -- ----------------------------------------------------------
611 -- name: fn_app; type: table
612 -- ----------------------------------------------------------
613 create table fn_app (
614 app_id int(11) primary key not null auto_increment,
615 app_name varchar(100) not null default '?',
616 app_image_url varchar(256) default null,
617 app_description varchar(512) default null,
618 app_notes varchar(4096) default null,
619 app_url varchar(256) default null,
620 app_alternate_url varchar(256) default null,
621 app_rest_endpoint varchar(2000) default null,
622 ml_app_name varchar(50) not null default '?',
623 ml_app_admin_id varchar(7) not null default '?',
624 mots_id int(11) default null,
625 app_password varchar(256) default null,
626 open char(1) default 'N',
627 enabled char(1) default 'Y',
628 thumbnail mediumblob null default null,
629 app_username varchar(50),
630 ueb_key varchar(256) default null,
631 ueb_secret varchar(256) default null,
632 ueb_topic_name varchar(256) default null,
633 uses_cadi char(1) default null,
634 ack_app char(1) default 'Y',
635 mode_of_integration varchar(50) default null,
636 app_type int(11) not null default 1,
637 auth_central char(1) not null default 'N',
638 auth_namespace varchar(100) null default null
641 -- ------------------ functional menu tables -------------------
643 -- table structure for table fn_menu_functional
645 create table fn_menu_functional (
646 menu_id int(11) not null auto_increment,
647 column_num int(2) not null,
648 text varchar(100) not null,
649 parent_menu_id int(11) default null,
650 url varchar(128) not null default '',
651 active_yn varchar(1) not null default 'y',
652 image_src varchar(100) default null,
653 primary key (menu_id),
654 key fk_fn_menu_func_parent_menu_id_idx (parent_menu_id),
655 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
658 -- table structure for table fn_menu_functional_ancestors
661 create table fn_menu_functional_ancestors (
662 id int(11) not null auto_increment,
663 menu_id int(11) not null,
664 ancestor_menu_id int(11) not null,
665 depth int(2) not null,
667 key fk_fn_menu_func_anc_menu_id_idx (menu_id),
668 key fk_fn_menu_func_anc_anc_menu_id_idx (ancestor_menu_id),
669 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,
670 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
673 -- table structure for table fn_menu_functional_roles
675 create table fn_menu_functional_roles (
676 id int(11) not null auto_increment,
677 menu_id int(11) not null,
678 app_id int(11) not null,
679 role_id int(10) not null,
681 key fk_fn_menu_func_roles_menu_id_idx (menu_id),
682 key fk_fn_menu_func_roles_app_id_idx (app_id),
683 key fk_fn_menu_func_roles_role_id_idx (role_id),
684 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,
685 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,
686 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
688 -- ----------------------------------------------------------
689 -- NAME: FN_WORKFLOW; TYPE: TABLE
690 -- ----------------------------------------------------------
691 create table fn_workflow (
692 id mediumint(9) not null auto_increment,
693 name varchar(20) not null,
694 description varchar(500) default null,
695 run_link varchar(300) default null,
696 suspend_link varchar(300) default null,
697 modified_link varchar(300) default null,
698 active_yn varchar(300) default null,
699 created varchar(300) default null,
700 created_by int(11) default null,
701 modified varchar(300) default null,
702 modified_by int(11) default null,
703 workflow_key varchar(50) default null,
705 UNIQUE KEY name (name)
709 -- ----------------------------------------------------------
710 -- NAME: FN_SCHEDULE_WORKFLOWS; TYPE: TABLE
711 -- ----------------------------------------------------------
712 create table fn_schedule_workflows (
713 id_schedule_workflows bigint(25) primary key not null auto_increment,
714 workflow_server_url varchar(45) default null,
715 workflow_key varchar(45) not null,
716 workflow_arguments varchar(45) default null,
717 startDateTimeCron varchar(45) default null,
718 endDateTime TIMESTAMP default NOW(),
719 start_date_time TIMESTAMP default NOW(),
720 recurrence varchar(45) default null
724 -- ----------------------------------------------------------
725 -- NAME: FN_SHARED_CONTEXT; TYPE: TABLE
726 -- ----------------------------------------------------------
727 create table fn_shared_context (
728 id int(11) not null auto_increment,
729 create_time timestamp not null,
730 context_id character varying(64) not null,
731 ckey character varying(128) not null,
732 cvalue character varying(1024),
734 UNIQUE KEY session_key (context_id, ckey) );
737 -- ----------------------------------------------------------
738 -- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
739 -- ----------------------------------------------------------
740 create table fn_qz_job_details (
741 SCHED_NAME VARCHAR(120) not null,
742 JOB_NAME VARCHAR(200) not null,
743 JOB_GROUP VARCHAR(200) not null,
744 DESCRIPTION VARCHAR(250) null,
745 JOB_CLASS_NAME VARCHAR(250) not null,
746 IS_DURABLE VARCHAR(1) not null,
747 IS_NONCONCURRENT VARCHAR(1) not null,
748 IS_UPDATE_DATA VARCHAR(1) not null,
749 REQUESTS_RECOVERY VARCHAR(1) not null,
751 primary key (SCHED_NAME,JOB_NAME,JOB_GROUP)
754 -- ----------------------------------------------------------
755 -- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
756 -- ----------------------------------------------------------
757 create table fn_qz_triggers (
758 SCHED_NAME VARCHAR(120) not null,
759 TRIGGER_NAME VARCHAR(200) not null,
760 TRIGGER_GROUP VARCHAR(200) not null,
761 JOB_NAME VARCHAR(200) not null,
762 JOB_GROUP VARCHAR(200) not null,
763 DESCRIPTION VARCHAR(250) null,
764 NEXT_FIRE_TIME BIGINT(13) null,
765 PREV_FIRE_TIME BIGINT(13) null,
766 PRIORITY INTEGER null,
767 TRIGGER_STATE VARCHAR(16) not null,
768 TRIGGER_TYPE VARCHAR(8) not null,
769 START_TIME BIGINT(13) not null,
770 END_TIME BIGINT(13) null,
771 CALENDAR_NAME VARCHAR(200) null,
772 MISFIRE_INSTR SMALLINT(2) null,
774 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
775 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
776 REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
779 -- ----------------------------------------------------------
780 -- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
781 -- ----------------------------------------------------------
782 create table fn_qz_simple_triggers (
783 SCHED_NAME VARCHAR(120) not null,
784 TRIGGER_NAME VARCHAR(200) not null,
785 TRIGGER_GROUP VARCHAR(200) not null,
786 REPEAT_COUNT BIGINT(7) not null,
787 REPEAT_INTERVAL BIGINT(12) not null,
788 TIMES_TRIGGERED BIGINT(10) not null,
789 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
790 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
791 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
794 -- ----------------------------------------------------------
795 -- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
796 -- ----------------------------------------------------------
797 create table fn_qz_cron_triggers (
798 SCHED_NAME VARCHAR(120) not null,
799 TRIGGER_NAME VARCHAR(200) not null,
800 TRIGGER_GROUP VARCHAR(200) not null,
801 CRON_EXPRESSION VARCHAR(120) not null,
802 TIME_ZONE_ID VARCHAR(80),
803 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
804 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
805 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
808 -- ----------------------------------------------------------
809 -- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
810 -- ----------------------------------------------------------
811 create table fn_qz_simprop_triggers (
812 SCHED_NAME VARCHAR(120) not null,
813 TRIGGER_NAME VARCHAR(200) not null,
814 TRIGGER_GROUP VARCHAR(200) not null,
815 STR_PROP_1 VARCHAR(512) null,
816 STR_PROP_2 VARCHAR(512) null,
817 STR_PROP_3 VARCHAR(512) null,
820 LONG_PROP_1 BIGINT null,
821 LONG_PROP_2 BIGINT null,
822 DEC_PROP_1 NUMERIC(13,4) null,
823 DEC_PROP_2 NUMERIC(13,4) null,
824 BOOL_PROP_1 VARCHAR(1) null,
825 BOOL_PROP_2 VARCHAR(1) null,
826 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
827 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
828 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
831 -- ----------------------------------------------------------
832 -- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
833 -- ----------------------------------------------------------
834 create table fn_qz_blob_triggers (
835 SCHED_NAME VARCHAR(120) not null,
836 TRIGGER_NAME VARCHAR(200) not null,
837 TRIGGER_GROUP VARCHAR(200) not null,
839 primary key (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
840 INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
841 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
842 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
845 -- ----------------------------------------------------------
846 -- NAME: FN_QZ_CALENDARS; TYPE: TABLE
847 -- ----------------------------------------------------------
848 create table fn_qz_calendars (
849 SCHED_NAME VARCHAR(120) not null,
850 CALENDAR_NAME VARCHAR(200) not null,
851 CALENDAR BLOB not null,
852 primary key (SCHED_NAME,CALENDAR_NAME)
856 -- ----------------------------------------------------------
857 -- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
858 -- ----------------------------------------------------------
859 create table fn_qz_paused_trigger_grps (
860 SCHED_NAME VARCHAR(120) not null,
861 TRIGGER_GROUP VARCHAR(200) not null,
862 primary key (SCHED_NAME,TRIGGER_GROUP)
865 -- ----------------------------------------------------------
866 -- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
867 -- ----------------------------------------------------------
868 create table fn_qz_fired_triggers (
869 SCHED_NAME VARCHAR(120) not null,
870 ENTRY_ID VARCHAR(95) not null,
871 TRIGGER_NAME VARCHAR(200) not null,
872 TRIGGER_GROUP VARCHAR(200) not null,
873 INSTANCE_NAME VARCHAR(200) not null,
874 FIRED_TIME BIGINT(13) not null,
875 SCHED_TIME BIGINT(13) not null,
876 PRIORITY INTEGER not null,
877 STATE VARCHAR(16) not null,
878 JOB_NAME VARCHAR(200) null,
879 JOB_GROUP VARCHAR(200) null,
880 IS_NONCONCURRENT VARCHAR(1) null,
881 REQUESTS_RECOVERY VARCHAR(1) null,
882 primary key (SCHED_NAME,ENTRY_ID)
885 -- ----------------------------------------------------------
886 -- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
887 -- ----------------------------------------------------------
888 create table fn_qz_scheduler_state (
889 SCHED_NAME VARCHAR(120) not null,
890 INSTANCE_NAME VARCHAR(200) not null,
891 LAST_CHECKIN_TIME BIGINT(13) not null,
892 CHECKIN_INTERVAL BIGINT(13) not null,
893 primary key (SCHED_NAME,INSTANCE_NAME)
896 -- ----------------------------------------------------------
897 -- NAME: FN_QZ_LOCKS; TYPE: TABLE
898 -- ----------------------------------------------------------
899 create table fn_qz_locks (
900 SCHED_NAME VARCHAR(120) not null,
901 LOCK_NAME VARCHAR(40) not null,
902 primary key (SCHED_NAME,LOCK_NAME)
905 -- ----------------------------------------------------------
906 -- NAME: FN_MENU_FAVORITES; TYPE: TABLE
907 -- ----------------------------------------------------------
909 create table fn_menu_favorites (
910 user_id int(11) not null,
911 menu_id int(11) not null,
912 primary key (user_id,menu_id)
915 -- FACELIFT - Table for Events, News and Resources
917 create table fn_common_widget_data(
918 id int auto_increment,
919 category varchar(32),
922 content varchar(4096),
923 event_date varchar(10), -- YYYY-MM-DD
928 create table fn_app_contact_us (
929 app_id int(11) not null,
930 contact_name varchar(128) default null,
931 contact_email varchar(128) default null,
932 url varchar(256) default null,
933 active_yn varchar(2) default null,
934 description varchar(1024) default null,
935 primary key (app_id),
936 constraint fk_fn_a_con__ref_202_fn_app foreign key (app_id) references fn_app (app_id)
940 create table fn_pers_user_app_sel (
941 id int(11) not null auto_increment,
942 user_id int(11) not null,
943 app_id int(11) not null,
944 status_cd char(1) not null,
946 constraint fk_1_fn_pers_user_app_sel_fn_user foreign key (user_id) references fn_user (user_id),
947 constraint fk_2_fn_pers_user_app_sel_fn_app foreign key (app_id) references fn_app (app_id)
952 -- new 1702 tables/views
953 -- 1702 Additions for User Notifications
954 -- ----------------------------------------------------------
955 -- NAME: ep_notification; TYPE: TABLE
956 -- ----------------------------------------------------------
957 create table ep_notification (
958 notification_ID int(11) primary key not null auto_increment,
959 is_for_online_users char(1) default 'N',
960 is_for_all_roles char(1) default 'N',
961 active_YN char(1) default 'Y',
962 msg_header varchar(100),
963 msg_description varchar(2000),
964 msg_source varchar(50) default 'EP',
965 start_time timestamp default now(),
966 end_time timestamp null,
968 creator_ID int(11) null default null,
969 created_date timestamp null default null,
970 notification_hyperlink varchar(512) null default null -- new column for 1710
973 -- ----------------------------------------------------------
974 -- NAME: ep_role_notification; TYPE: TABLE
975 -- ----------------------------------------------------------
976 create table ep_role_notification (
977 ID int(11) primary key not null auto_increment,
978 notification_ID int(11),
980 recv_user_id int(11) null,
981 constraint fk_ep_role_notif_fn_role foreign key (role_ID) references fn_role(role_id),
982 constraint fk_ep_role_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
985 -- ----------------------------------------------------------
986 -- NAME: ep_user_notification; TYPE: TABLE
987 -- ----------------------------------------------------------
988 create table ep_user_notification (
989 ID int(11) primary key not null auto_increment,
991 notification_ID int(11),
992 is_viewed char(1) default 'N',
993 updated_time timestamp default now(),
994 constraint fk_ep_urole_notif_fn_user foreign key (User_ID) references fn_user(user_id),
995 constraint fk_ep_urole_notif_fn_notif foreign key (notification_ID) references ep_notification(notification_ID)
998 -- ----------------------------------------------------------
999 -- NAME: ep_pers_user_app_sort; TYPE: Table
1000 -- ----------------------------------------------------------
1002 CREATE TABLE ep_pers_user_app_sort (
1003 id int(11) not null primary key auto_increment,
1004 user_id int(11) not null,
1005 sort_pref char(1) not null,
1006 unique key uk_1_ep_pers_user_app_sort (user_id),
1007 constraint fk_ep_pers_user_app_sort_fn_user foreign key (user_id) references fn_user(user_id)
1009 -- ----------------------------------------------------------
1010 -- NAME: ep_pers_user_app_man_sort; TYPE: Table
1011 -- ----------------------------------------------------------
1013 CREATE TABLE ep_pers_user_app_man_sort (
1014 id int(11) not null primary key auto_increment,
1015 user_id int(11) not null,
1016 app_id int(11) not null,
1017 sort_order int(11) not null,
1018 unique key uk_1_ep_pers_user_app_man_sort (user_id, app_id),
1019 constraint fk_ep_pers_app_man_sort_fn_user foreign key (user_id) references fn_user(user_id),
1020 constraint fk_ep_pers_app_man_sort_fn_app foreign key (app_id) references fn_app(app_id)
1023 -- ----------------------------------------------------------
1024 -- NAME: ep_widget_catalog; TYPE: Table
1025 -- ----------------------------------------------------------
1027 CREATE TABLE ep_widget_catalog (
1028 widget_id int(11) not null auto_increment,
1029 wdg_name varchar(100) not null default '?',
1030 service_id int(11) default null,
1031 wdg_desc varchar(200) default null,
1032 wdg_file_loc varchar(256) not null default '?',
1033 all_user_flag char(1) not null default 'N',
1034 primary key (widget_id)
1037 -- ----------------------------------------------------------
1038 -- NAME: ep_widget_catalog_role; TYPE: Table
1039 -- ----------------------------------------------------------
1040 create table ep_widget_catalog_role (
1041 widget_id int(10) not null,
1042 app_id int(11) default '1',
1043 role_id int(10) not null,
1044 key fk_ep_widget_catalog_role_fn_widget (widget_id),
1045 key fk_ep_widget_catalog_role_ref_fn_role (role_id),
1046 key fk_ep_widget_catalog_role_app_id (app_id),
1047 constraint fk_ep_widget_catalog_role_fn_widget foreign key (widget_id) references ep_widget_catalog (widget_id),
1048 constraint fk_ep_widget_catalog_role_ref_fn_role foreign key (role_id) references fn_role (role_id),
1049 constraint fk_ep_widget_catalog_role_app_id foreign key (app_id) references fn_app (app_id)
1052 -- ----------------------------------------------------------
1053 -- NAME: ep_pers_user_widget_placement; TYPE: Table
1054 -- ----------------------------------------------------------
1055 CREATE TABLE ep_pers_user_widget_placement (
1056 id int(11) not null primary key auto_increment,
1057 user_id int(11) not null,
1058 widget_id int(11) not null,
1063 unique key uk_1_ep_pers_user_widg_place (user_id, widget_id),
1064 constraint fk_ep_pers_user_widg_place_fn_user foreign key (user_id) references fn_user(user_id),
1065 constraint fk_ep_pers_user_widg_place_ep_widg foreign key (widget_id) references ep_widget_catalog(widget_id)
1068 -- ----------------------------------------------------------
1069 -- NAME: ep_pers_user_widget_sel; TYPE: TABLE
1070 -- ----------------------------------------------------------
1071 CREATE TABLE ep_pers_user_widget_sel (
1072 id int(11) not null primary key auto_increment,
1073 user_id int(11) not null,
1074 widget_id int(11) not null,
1075 status_cd char(1) not null,
1076 unique key uk_1_ep_pers_user_widg_sel_user_widg (user_id, widget_id),
1077 CONSTRAINT fk_1_ep_pers_user_wid_sel_fn_user FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
1078 CONSTRAINT fk_2_ep_pers_user_wid_sel_ep_wid FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
1081 -- ----------------------------------------------------------
1082 -- NAME: ep_widget_catalog_files; TYPE: TABLE
1083 -- ----------------------------------------------------------
1084 CREATE TABLE ep_widget_catalog_files (
1085 file_id int(11) not null primary key auto_increment,
1087 widget_name VARCHAR(100) NOT NULL,
1088 framework_js LONGBLOB NULL,
1089 controller_js LONGBLOB NULL,
1090 markup_html LONGBLOB NULL,
1091 widget_css LONGBLOB NULL
1094 -- ----------------------------------------------------------
1095 -- NAME: fn_role_v; TYPE: VIEW
1096 -- All roles without an APP_ID are Portal only.
1097 -- ----------------------------------------------------------
1098 create view fn_role_v as
1099 select fn_role.role_id as role_id,
1100 fn_role.role_name as role_name,
1101 fn_role.active_yn as active_yn,
1102 fn_role.priority as priority,
1103 fn_role.app_id as app_id,
1104 fn_role.app_role_id as app_role_id
1105 from fn_role where isnull(fn_role.app_id);
1107 -- end new 1702 tables/views
1109 -- new 1707 tables/views
1111 -- ----------------------------------------------------------
1112 -- NAME: ep_user_roles_request; TYPE: TABLE
1113 -- ----------------------------------------------------------
1115 create table ep_user_roles_request (
1116 req_id int(11) not null primary key auto_increment,
1117 user_id int(11) not null,
1118 app_id int(11) not null,
1119 created_date timestamp default now(),
1120 updated_date timestamp default now(),
1121 request_status character varying(50) not null,
1122 constraint fk_user_roles_req_fn_user foreign key (user_id) references fn_user(user_id),
1123 constraint fk_user_roles_req_fn_app foreign key (app_id) references fn_app(app_id)
1127 -- ----------------------------------------------------------
1128 -- NAME: ep_user_roles_request_det; TYPE: TABLE
1129 -- ----------------------------------------------------------
1130 create table ep_user_roles_request_det (
1131 id int(11) not null primary key auto_increment,
1132 req_id int(11) default null,
1133 requested_role_id int(10) not null,
1134 request_type character varying(10) not null,
1135 constraint fk_user_roles_req_fn_req_id foreign key (req_id) references ep_user_roles_request(req_id),
1136 constraint fk_user_roles_req_fn_role_id foreign key (requested_role_id) references fn_role(role_id)
1139 -- ----------------------------------------------------------
1140 -- NAME: ep_microservice; TYPE: TABLE
1141 -- ----------------------------------------------------------
1143 CREATE TABLE ep_microservice (
1144 id INT(11) NOT NULL AUTO_INCREMENT,
1145 name VARCHAR(50) NULL DEFAULT NULL,
1146 description VARCHAR(50) NULL DEFAULT NULL,
1147 appId INT(11) NULL DEFAULT NULL,
1148 endpoint_url VARCHAR(200) NULL DEFAULT NULL,
1149 security_type VARCHAR(50) NULL DEFAULT NULL,
1150 username VARCHAR(50) NULL DEFAULT NULL,
1151 password VARCHAR(50) NULL DEFAULT NULL,
1152 active CHAR(1) NOT NULL DEFAULT 'Y',
1154 CONSTRAINT FK_FN_APP_EP_MICROSERVICE FOREIGN KEY (appId) REFERENCES fn_app (app_id)
1157 -- ----------------------------------------------------------
1158 -- NAME: ep_microservice_parameter; TYPE: TABLE
1159 -- ----------------------------------------------------------
1161 CREATE TABLE ep_microservice_parameter (
1162 id INT(11) NOT NULL AUTO_INCREMENT,
1163 service_id INT(11) NULL DEFAULT NULL,
1164 para_key VARCHAR(50) NULL DEFAULT NULL,
1165 para_value VARCHAR(50) NULL DEFAULT NULL,
1167 CONSTRAINT FK_EP_MICROSERIVCE_EP_MICROSERVICE_PARAMETER FOREIGN KEY (service_id) REFERENCES ep_microservice (id)
1171 -- ----------------------------------------------------------
1172 -- NAME: ep_widget_preview_files; TYPE: TABLE
1173 -- ----------------------------------------------------------
1175 CREATE TABLE ep_widget_preview_files (
1176 preview_id INT(11) NOT NULL AUTO_INCREMENT,
1177 html_file LONGBLOB NULL,
1178 css_file LONGBLOB NULL,
1179 javascript_file LONGBLOB NULL,
1180 framework_file LONGBLOB NULL,
1181 PRIMARY KEY (preview_id)
1184 -- ----------------------------------------------------------
1185 -- NAME: ep_widget_microservice; TYPE: TABLE
1186 -- ----------------------------------------------------------
1188 CREATE TABLE ep_widget_microservice (
1189 id INT(11) NOT NULL AUTO_INCREMENT,
1190 widget_id INT(11) NOT NULL DEFAULT '0',
1191 microservice_id INT(11) NOT NULL DEFAULT '0',
1193 CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_MICROSERVICE FOREIGN KEY (microservice_id) REFERENCES ep_microservice (id),
1194 CONSTRAINT FK_EP_WIDGET_MICROSERVICE_EP_WIDGET FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id)
1197 -- ----------------------------------------------------------
1198 -- NAME: ep_basic_auth_account; TYPE: TABLE
1199 -- ----------------------------------------------------------
1201 create table ep_basic_auth_account (
1202 id INT(11) NOT NULL AUTO_INCREMENT,
1203 ext_app_name VARCHAR(50) NOT NULL,
1204 username VARCHAR(50) NOT NULL,
1205 password VARCHAR(50) null default null,
1206 active_yn char(1) NOT NULL default 'Y',
1210 -- ----------------------------------------------------------
1211 -- NAME: ep_widget_catalog_parameter; TYPE: TABLE
1212 -- ----------------------------------------------------------
1214 create table ep_widget_catalog_parameter (
1215 id INT(11) NOT NULL AUTO_INCREMENT,
1216 widget_id INT(11) NOT NULL,
1217 user_id INT(11) NOT NULL,
1218 param_id INT(11) NOT NULL,
1219 user_value VARCHAR(50) NULL,
1221 CONSTRAINT EP_FN_USER_WIDGET_PARAMETER_FK FOREIGN KEY (user_id) REFERENCES fn_user (user_id),
1222 CONSTRAINT EP_WIDGET_CATALOG_WIDGET_PARAMETER_FK FOREIGN KEY (widget_id) REFERENCES ep_widget_catalog (widget_id),
1223 CONSTRAINT EP_PARAMETER_ID_WIDGET_PARAMETER_FK FOREIGN KEY (param_id) REFERENCES ep_microservice_parameter (id)
1226 -- ----------------------------------------------------------
1227 -- NAME: ep_web_analytics_source; TYPE: TABLE
1228 -- ----------------------------------------------------------
1230 create table ep_web_analytics_source(
1231 resource_id int(11) NOT NULL auto_increment,
1232 app_id int(11) NOT NULL,
1233 report_source varchar(500),
1234 report_name varchar(500),
1235 PRIMARY KEY (resource_id),
1236 FOREIGN KEY (app_id) REFERENCES fn_app(app_id)
1239 -- Machine Learning Tables
1240 -- ----------------------------------------------------------
1241 -- NAME: ep_ml_model; TYPE: TABLE
1242 -- ----------------------------------------------------------
1244 create table ep_ml_model(
1245 time_stamp timestamp default now(),
1246 group_id int(11) NOT NULL,
1248 PRIMARY KEY (time_stamp,group_id)
1250 -- ----------------------------------------------------------
1251 -- NAME: ep_ml_rec; TYPE: TABLE
1252 -- ----------------------------------------------------------
1254 create table ep_ml_rec(
1255 time_stamp timestamp default now(),
1256 org_user_id varchar(20) NOT NULL,
1257 rec varchar(4000) DEFAULT NULL,
1258 PRIMARY KEY (time_stamp,org_user_id)
1261 -- ----------------------------------------------------------
1262 -- NAME: ep_ml_user; TYPE: TABLE
1263 -- ----------------------------------------------------------
1265 create table ep_ml_user(
1266 time_stamp timestamp default now(),
1267 org_user_id varchar(20) NOT NULL,
1268 group_id int(11) NOT NULL,
1269 PRIMARY KEY (time_stamp,org_user_id)
1272 -- ----------------------------------------------------------
1273 -- NAME: ep_endpoints; TYPE: TABLE
1274 -- ----------------------------------------------------------
1276 create table ep_endpoints (
1277 id INT(11) NOT NULL AUTO_INCREMENT,
1278 url VARCHAR(50) NOT NULL,
1282 -- ----------------------------------------------------------
1283 -- NAME: ep_endpoints_basic_auth_account; TYPE: TABLE
1284 -- ----------------------------------------------------------
1286 create table ep_endpoints_basic_auth_account (
1287 id INT(11) NOT NULL AUTO_INCREMENT,
1288 ep_id INT(11) DEFAULT NULL,
1289 account_id INT(11) DEFAULT NULL,
1291 CONSTRAINT ep_endpoints_basic_auth_account_account_id_fk FOREIGN KEY (account_id) REFERENCES ep_basic_auth_account (id),
1292 CONSTRAINT ep_endpoints_basic_auth_account_ep_id_fk FOREIGN KEY (ep_id) REFERENCES ep_endpoints (id)
1296 -- end new 1707 tables/views
1298 -- new 1710 tables/views
1300 -- ----------------------------------------------------------
1301 -- NAME: ep_app_function; TYPE: TABLE
1302 -- ----------------------------------------------------------
1304 CREATE TABLE ep_app_function (
1305 app_id INT(11) NOT NULL,
1306 function_cd VARCHAR(250) NOT NULL,
1307 function_name VARCHAR(500) NOT NULL,
1308 PRIMARY KEY (function_cd, app_id),
1309 INDEX fk_ep_app_function_app_id (app_id),
1310 CONSTRAINT fk_ep_app_function_app_id FOREIGN KEY (app_id) REFERENCES fn_app (app_id)
1313 -- ----------------------------------------------------------
1314 -- NAME: ep_app_role_function; TYPE: TABLE
1315 -- ----------------------------------------------------------
1317 CREATE TABLE `ep_app_role_function` (
1318 `id` INT(11) NOT NULL AUTO_INCREMENT,
1319 `app_id` INT(11) NOT NULL,
1320 `role_id` INT(11) NOT NULL,
1321 `function_cd` VARCHAR(250) NOT NULL,
1322 `role_app_id` VARCHAR(20) NULL DEFAULT NULL,
1324 UNIQUE INDEX `UNIQUE KEY` (`app_id`, `role_id`, `function_cd`),
1325 CONSTRAINT `fk_ep_app_role_function_app_id` FOREIGN KEY (`app_id`) REFERENCES `fn_app` (`app_id`),
1326 CONSTRAINT `fk_ep_app_role_function_ep_app_func` FOREIGN KEY (`app_id`, `function_cd`) REFERENCES `ep_app_function` (`app_id`, `function_cd`),
1327 CONSTRAINT `fk_ep_app_role_function_role_id` FOREIGN KEY (`role_id`) REFERENCES `fn_role` (`role_id`)
1330 -- end new 1710 tables/views
1332 -- ----------------------------------------------------------
1333 -- NAME: QUARTZ TYPE: INDEXES
1334 -- ----------------------------------------------------------
1335 create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
1336 create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
1337 create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
1338 create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
1339 create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
1340 create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
1341 create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
1342 create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
1343 create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
1344 create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
1345 create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
1346 create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
1347 create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
1348 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);
1349 create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
1350 create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
1351 create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
1352 create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
1353 create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
1354 create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
1357 -- ------------------ create view section
1359 -- name: v_url_access; type: view
1361 create view v_url_access as
1362 select distinct m.action as url,
1365 where (m.action is not null)
1367 select distinct t.action as url,
1370 where (t.action is not null)
1372 select r.restricted_url as url,
1374 from fn_restricted_url r;
1376 -- ------------------ alter table add constraint primary key section
1378 -- name: cr_favorite_reports_user_idrep_id; type: constraint
1380 alter table cr_favorite_reports
1381 add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
1383 -- name: cr_folder_folder_id; type: constraint
1385 alter table cr_folder
1386 add constraint cr_folder_folder_id primary key (folder_id);
1388 -- name: cr_folder_access_folder_access_id; type: constraint
1390 alter table cr_folder_access
1391 add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
1393 -- name: cr_hist_user_map_hist_iduser_id; type: constraint
1395 alter table cr_hist_user_map
1396 add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
1398 -- name: cr_lu_file_type_lookup_id; type: constraint
1400 alter table cr_lu_file_type
1401 add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
1403 -- name: cr_raptor_action_img_image_id; type: constraint
1405 alter table cr_raptor_action_img
1406 add constraint cr_raptor_action_img_image_id primary key (image_id);
1408 -- name: cr_raptor_pdf_img_image_id; type: constraint
1410 alter table cr_raptor_pdf_img
1411 add constraint cr_raptor_pdf_img_image_id primary key (image_id);
1413 -- name: cr_remote_schema_info_schema_prefix; type: constraint
1415 alter table cr_remote_schema_info
1416 add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
1418 -- name: cr_report_rep_id; type: constraint
1420 alter table cr_report
1421 add constraint cr_report_rep_id primary key (rep_id);
1423 -- name: cr_report_access_rep_idorder_no; type: constraint
1425 alter table cr_report_access
1426 add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
1428 -- name: cr_report_email_sent_log_log_id; type: constraint
1430 alter table cr_report_email_sent_log
1431 add constraint cr_report_email_sent_log_log_id primary key (log_id);
1433 -- name: cr_report_file_history_hist_id; type: constraint
1435 alter table cr_report_file_history
1436 add constraint cr_report_file_history_hist_id primary key (hist_id);
1438 -- name: cr_report_schedule_schedule_id; type: constraint
1440 alter table cr_report_schedule
1441 add constraint cr_report_schedule_schedule_id primary key (schedule_id);
1443 -- name: cr_report_schedule_users_schedule_idrep_iduser_idorder_no; type: constraint
1445 alter table cr_report_schedule_users
1446 add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
1448 -- name: cr_report_template_map_report_id; type: constraint
1450 alter table cr_report_template_map
1451 add constraint cr_report_template_map_report_id primary key (report_id);
1453 -- name: cr_table_role_table_namerole_id; type: constraint
1455 alter table cr_table_role
1456 add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
1458 -- name: cr_table_source_table_name; type: constraint
1460 alter table cr_table_source
1461 add constraint cr_table_source_table_name primary key (table_name);
1463 -- name: fn_audit_action_audit_action_id; type: constraint
1465 alter table fn_audit_action
1466 add constraint fn_audit_action_audit_action_id primary key (audit_action_id);
1469 -- name: fk_fn_audit_ref_205_fn_lu_ac; type: constraint
1471 alter table fn_audit_log
1472 add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
1474 -- name: fk_fn_role__ref_201_fn_funct; type: constraint
1476 alter table fn_role_function
1477 add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
1479 -- name: fn_chat_logs_chat_log_id; type: constraint
1481 alter table fn_chat_logs
1482 add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
1484 -- name: fn_chat_room_chat_room_id; type: constraint
1486 alter table fn_chat_room
1487 add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
1489 -- name: fn_chat_users_id; type: constraint
1491 alter table fn_chat_users
1492 add constraint fn_chat_users_id primary key (id);
1494 -- name: fn_lu_alert_method_alert_method_cd; type: constraint
1496 alter table fn_lu_alert_method
1497 add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
1499 -- name: fn_lu_broadcast_site_broadcast_site_cd; type: constraint
1501 alter table fn_lu_broadcast_site
1502 add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
1504 -- name: fn_lu_priority_priority_id; type: constraint
1506 alter table fn_lu_priority
1507 add constraint fn_lu_priority_priority_id primary key (priority_id);
1509 -- name: fn_lu_role_type_role_type_id; type: constraint
1511 alter table fn_lu_role_type
1512 add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
1514 -- name: fn_lu_tab_set_tab_set_cd; type: constraint
1516 alter table fn_lu_tab_set
1517 add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
1519 -- name: fn_lu_timezone_timezone_id; type: constraint
1521 alter table fn_lu_timezone
1522 add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
1524 -- name: fn_org_org_id; type: constraint
1527 add constraint fn_org_org_id primary key (org_id);
1529 -- name: fn_restricted_url_restricted_urlfunction_cd; type: constraint
1531 alter table fn_restricted_url
1532 add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
1534 -- name: fn_role_composite_parent_role_idchild_role_id; type: constraint
1536 alter table fn_role_composite
1537 add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
1539 -- name: fn_role_function_role_idfunction_cd; type: constraint
1541 alter table fn_role_function
1542 add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
1544 -- name: fn_tab_tab_cd; type: constraint
1547 add constraint fn_tab_tab_cd primary key (tab_cd);
1549 -- name: fn_tab_selected_selected_tab_cdtab_uri; type: constraint
1551 alter table fn_tab_selected
1552 add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
1554 -- name: fn_user_pseudo_role_pseudo_role_iduser_id; type: constraint
1556 alter table fn_user_pseudo_role
1557 add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
1559 -- name: fn_user_role_user_idrole_id; type: constraint
1561 alter table fn_user_role
1562 add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
1563 -- ------------------ create index section
1565 -- name: cr_report_create_idpublic_yntitle; type: index
1567 create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
1569 -- name: cr_table_join_dest_table_name; type: index
1571 create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
1573 -- name: cr_table_join_src_table_name; type: index
1575 create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
1577 -- name: fn_audit_log_activity_cd; type: index
1579 create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
1581 -- name: fn_audit_log_user_id; type: index
1583 create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
1585 -- name: fn_org_access_cd; type: index
1587 create index fn_org_access_cd using btree on fn_org (access_cd);
1589 -- name: fn_role_function_function_cd; type: index
1591 create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
1593 -- name: fn_role_function_role_id; type: index
1595 create index fn_role_function_role_id using btree on fn_role_function (role_id);
1597 -- name: fn_user_address_id; type: index
1599 create index fn_user_address_id using btree on fn_user (address_id);
1601 -- name: fn_user_alert_method_cd; type: index
1603 create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd);
1605 -- name: fn_user_hrid; type: index
1607 create unique index fn_user_hrid using btree on fn_user (hrid);
1609 -- name: fn_user_login_id; type: index
1611 create unique index fn_user_login_id using btree on fn_user (login_id);
1613 -- name: fn_user_org_id; type: index
1615 create index fn_user_org_id using btree on fn_user (org_id);
1617 -- name: fn_user_role_role_id; type: index
1619 create index fn_user_role_role_id using btree on fn_user_role (role_id);
1621 -- name: fn_user_role_user_id; type: index
1623 create index fn_user_role_user_id using btree on fn_user_role (user_id);
1625 -- name: fk_fn_user__ref_178_fn_app_idx; type: index
1627 create index fk_fn_user__ref_178_fn_app_idx on fn_user_role (app_id);
1629 -- name: fn_role_name_app_id_idx; type: index
1631 create unique index fn_role_name_app_id_idx using btree on fn_role (role_name,app_id);
1635 create index ep_notif_recv_user_id_idx using btree on ep_role_notification (recv_user_id);
1639 -- ------------------ alter table add constraint foreign key section
1641 -- name: fk_fn_user__ref_178_fn_app; type: fk constraint
1643 alter table fn_user_role
1644 add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
1646 -- name: fk_cr_repor_ref_14707_cr_repor; type: fk constraint
1648 alter table cr_report_schedule
1649 add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
1651 -- name: fk_cr_repor_ref_14716_cr_repor; type: fk constraint
1653 alter table cr_report_schedule_users
1654 add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
1656 -- name: fk_cr_repor_ref_17645_cr_repor; type: fk constraint
1658 alter table cr_report_log
1659 add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
1661 -- name: fk_cr_repor_ref_8550_cr_repor; type: fk constraint
1663 alter table cr_report_access
1664 add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
1666 -- name: fk_cr_report_rep_id; type: fk constraint
1668 alter table cr_report_email_sent_log
1669 add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
1671 -- name: fk_cr_table_ref_311_cr_tab; type: fk constraint
1673 alter table cr_table_join
1674 add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
1676 -- name: fk_cr_table_ref_315_cr_tab; type: fk constraint
1678 alter table cr_table_join
1679 add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
1681 -- name: fk_cr_table_ref_32384_cr_table; type: fk constraint
1683 alter table cr_table_role
1684 add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
1686 -- name: fk_fn_tab_function_cd; type: fk constraint
1689 add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
1691 -- name: fk_fn_tab_selected_tab_cd; type: fk constraint
1693 alter table fn_tab_selected
1694 add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
1696 -- name: fk_fn_tab_set_cd; type: fk constraint
1699 add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
1701 -- name: fk_fn_user_ref_110_fn_org; type: fk constraint
1704 add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id);
1706 -- name: fk_fn_user_ref_123_fn_lu_al; type: fk constraint
1709 add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd);
1711 -- name: fk_fn_user_ref_197_fn_user; type: fk constraint
1714 add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id);
1716 -- name: fk_fn_user_ref_198_fn_user; type: fk constraint
1719 add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id);
1721 -- name: fk_fn_user_ref_199_fn_user; type: fk constraint
1724 add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id);
1726 -- name: fk_parent_key_cr_folder; type: fk constraint
1728 alter table cr_folder
1729 add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
1731 -- name: fk_pseudo_role_pseudo_role_id; type: fk constraint
1733 alter table fn_user_pseudo_role
1734 add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
1736 -- name: fk_pseudo_role_user_id; type: fk constraint
1738 alter table fn_user_pseudo_role
1739 add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
1741 -- name: fk_restricted_url_function_cd; type: fk constraint
1743 alter table fn_restricted_url
1744 add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
1746 -- name: fk_timezone; type: fk constraint
1749 add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id);
1751 -- name: sys_c0014614; type: fk constraint
1753 alter table cr_report_file_history
1754 add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
1756 -- name: sys_c0014615; type: fk constraint
1758 alter table cr_report_file_history
1759 add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
1761 -- name: sys_c0014616; type: fk constraint
1763 alter table cr_hist_user_map
1764 add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
1766 -- name: sys_c0014617; type: fk constraint
1768 alter table cr_hist_user_map
1769 add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);
1771 -- name: sys_c0014618; type: fk constraint
1773 alter table fn_menu_favorites
1774 add constraint sys_c0014618 foreign key (user_id) references fn_user(user_id);
1777 -- name: sys_c0014619; type: fk constraint
1779 alter table fn_menu_favorites
1780 add constraint sys_c0014619 foreign key (menu_id) references fn_menu_functional(menu_id);