2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
6 * ================================================================================
7 * Licensed under the Apache License, Version 2.0 (the "License");
8 * you may not use this file except in compliance with the License.
9 * You may obtain a copy of the License at
11 * http://www.apache.org/licenses/LICENSE-2.0
13 * Unless required by applicable law or agreed to in writing, software
14 * distributed under the License is distributed on an "AS IS" BASIS,
15 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16 * See the License for the specific language governing permissions and
17 * limitations under the License.
18 * ============LICENSE_END=========================================================
21 -- ---------------------------------------------------------------------------------------------------------------
22 -- This is for the 1610 Version of SDK database called ecomp_sdk for Open Source
24 -- Note to : Database Admin, set the MySQL system variable called lower_case_table_names
26 -- It can be set 3 different ways:
27 -- command-line options (Cmd-line),
28 -- options valid in configuration files (Option file), or
29 -- server system variables (System Var).
31 -- It needs to be set to 1, then table names are stored in lowercase on disk and comparisons are not case sensitive.
33 -- MySql/MariaDB Version compatibility information
35 -- mysql Ver 15.1 Distrib 5.5.35-MariaDB, for Linux (x86_64) using readline 5.1
37 -- bash-4.2$ mysql --version – cluster version
38 -- mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1
40 -- All versions newer or older than these DO NOT necessarily mean they are compatible.
41 -- ------------------------------------------------------------------------------------------------------------------
43 SET FOREIGN_KEY_CHECKS=1;
45 CREATE DATABASE ecomp_sdk;
49 -- ---------- create table SECTION
51 -- NAME: CR_FAVORITE_REPORTS; TYPE: TABLE
53 create table cr_favorite_reports (
54 USER_ID INTEGER NOT NULL,
55 REP_ID INTEGER NOT NULL
59 -- NAME: CR_FILEHIST_LOG; TYPE: TABLE
61 create table cr_filehist_log (
62 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
63 URL CHARACTER VARYING(4000),
64 NOTES CHARACTER VARYING(3500),
69 -- NAME: CR_FOLDER; TYPE: TABLE
71 create table cr_folder (
72 FOLDER_ID INTEGER NOT NULL,
73 FOLDER_NAME CHARACTER VARYING(50) NOT NULL,
74 DESCR CHARACTER VARYING(500),
75 CREATE_ID INTEGER NOT NULL,
76 CREATE_DATE TIMESTAMP NOT NULL,
77 PARENT_FOLDER_ID INTEGER,
78 PUBLIC_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL
82 -- NAME: CR_FOLDER_ACCESS; TYPE: TABLE
84 create table cr_folder_access (
85 FOLDER_ACCESS_ID NUMERIC(11,0) NOT NULL,
86 FOLDER_ID NUMERIC(11,0) NOT NULL,
87 ORDER_NO NUMERIC(11,0) NOT NULL,
88 ROLE_ID NUMERIC(11,0),
89 USER_ID NUMERIC(11,0),
90 READ_ONLY_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL
94 -- NAME: CR_HIST_USER_MAP; TYPE: TABLE
96 create table cr_hist_user_map (
97 HIST_ID INT(11) NOT NULL,
98 USER_ID INT(11) NOT NULL
102 -- NAME: CR_LU_FILE_TYPE; TYPE: TABLE
104 create table cr_lu_file_type (
105 LOOKUP_ID NUMERIC(2,0) NOT NULL,
106 LOOKUP_DESCR CHARACTER VARYING(255) NOT NULL,
107 ACTIVE_YN CHARACTER(1) DEFAULT 'Y',
108 ERROR_CODE NUMERIC(11,0)
112 -- NAME: CR_RAPTOR_ACTION_IMG; TYPE: TABLE
114 create table cr_raptor_action_img (
115 IMAGE_ID CHARACTER VARYING(100) NOT NULL,
116 IMAGE_LOC CHARACTER VARYING(400)
120 -- NAME: CR_RAPTOR_PDF_IMG; TYPE: TABLE
122 create table cr_raptor_pdf_img (
123 IMAGE_ID CHARACTER VARYING(100) NOT NULL,
124 IMAGE_LOC CHARACTER VARYING(400)
128 -- NAME: CR_REMOTE_SCHEMA_INFO; TYPE: TABLE
130 create table cr_remote_schema_info (
131 SCHEMA_PREFIX CHARACTER VARYING(5) NOT NULL,
132 SCHEMA_DESC CHARACTER VARYING(75) NOT NULL,
133 DATASOURCE_TYPE CHARACTER VARYING(100)
137 -- NAME: CR_REPORT; TYPE: TABLE
139 create table cr_report (
140 REP_ID NUMERIC(11,0) NOT NULL,
141 TITLE CHARACTER VARYING(100) NOT NULL,
142 DESCR CHARACTER VARYING(255),
143 PUBLIC_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL,
145 CREATE_ID NUMERIC(11,0),
146 CREATE_DATE TIMESTAMP default now(),
147 MAINT_ID NUMERIC(11,0),
148 MAINT_DATE TIMESTAMP DEFAULT NOW(),
149 MENU_ID CHARACTER VARYING(500),
150 MENU_APPROVED_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL,
151 OWNER_ID NUMERIC(11,0),
152 FOLDER_ID INTEGER DEFAULT 0,
153 DASHBOARD_TYPE_YN CHARACTER VARYING(1) DEFAULT 'N',
154 DASHBOARD_YN CHARACTER VARYING(1) DEFAULT 'N'
158 -- NAME: CR_REPORT_ACCESS; TYPE: TABLE
160 create table cr_report_access (
161 REP_ID NUMERIC(11,0) NOT NULL,
162 ORDER_NO NUMERIC(11,0) NOT NULL,
163 ROLE_ID NUMERIC(11,0),
164 USER_ID NUMERIC(11,0),
165 READ_ONLY_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL
169 -- NAME: CR_REPORT_DWNLD_LOG; TYPE: TABLE
171 create table cr_report_dwnld_log (
172 USER_ID NUMERIC(11,0) NOT NULL,
173 REP_ID INTEGER NOT NULL,
174 FILE_NAME CHARACTER VARYING(100) NOT NULL,
175 DWNLD_START_TIME TIMESTAMP DEFAULT NOW() NOT NULL,
176 RECORD_READY_TIME TIMESTAMP DEFAULT NOW(),
177 FILTER_PARAMS CHARACTER VARYING(2000)
181 -- NAME: CR_REPORT_EMAIL_SENT_LOG; TYPE: TABLE
183 create table cr_report_email_sent_log (
184 LOG_ID INTEGER NOT NULL,
185 SCHEDULE_ID NUMERIC(11,0),
186 GEN_KEY CHARACTER VARYING(25) NOT NULL,
187 REP_ID NUMERIC(11,0) NOT NULL,
188 USER_ID NUMERIC(11,0),
189 SENT_DATE TIMESTAMP DEFAULT NOW(),
190 ACCESS_FLAG CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
191 TOUCH_DATE TIMESTAMP DEFAULT NOW()
195 -- NAME: CR_REPORT_FILE_HISTORY; TYPE: TABLE
197 create table cr_report_file_history (
198 HIST_ID INT(11) NOT NULL,
199 SCHED_USER_ID NUMERIC(11,0) NOT NULL,
200 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
201 USER_ID NUMERIC(11,0) NOT NULL,
202 REP_ID NUMERIC(11,0),
204 RECURRENCE CHARACTER VARYING(50),
205 FILE_TYPE_ID NUMERIC(2,0),
206 FILE_NAME CHARACTER VARYING(80),
208 FILE_SIZE NUMERIC(11,0),
209 RAPTOR_URL CHARACTER VARYING(4000),
210 ERROR_YN CHARACTER(1) DEFAULT 'N',
211 ERROR_CODE NUMERIC(11,0),
212 DELETED_YN CHARACTER(1) DEFAULT 'N',
213 DELETED_BY NUMERIC(38,0)
217 -- NAME: CR_REPORT_LOG; TYPE: TABLE
219 create table cr_report_log (
220 REP_ID NUMERIC(11,0) NOT NULL,
221 LOG_TIME TIMESTAMP NOT NULL,
222 USER_ID NUMERIC(11,0) NOT NULL,
223 ACTION CHARACTER VARYING(2000) NOT NULL,
224 ACTION_VALUE CHARACTER VARYING(50),
225 FORM_FIELDS CHARACTER VARYING(4000)
229 -- NAME: CR_REPORT_SCHEDULE; TYPE: TABLE
231 create table cr_report_schedule (
232 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
233 SCHED_USER_ID NUMERIC(11,0) NOT NULL,
234 REP_ID NUMERIC(11,0) NOT NULL,
235 ENABLED_YN CHARACTER VARYING(1) NOT NULL,
236 START_DATE TIMESTAMP DEFAULT NOW(),
237 END_DATE TIMESTAMP DEFAULT NOW(),
238 RUN_DATE TIMESTAMP DEFAULT NOW(),
239 RECURRENCE CHARACTER VARYING(50),
240 CONDITIONAL_YN CHARACTER VARYING(1) NOT NULL,
241 CONDITION_SQL CHARACTER VARYING(4000),
242 NOTIFY_TYPE INTEGER DEFAULT 0,
243 MAX_ROW INTEGER DEFAULT 1000,
244 INITIAL_FORMFIELDS CHARACTER VARYING(3500),
245 PROCESSED_FORMFIELDS CHARACTER VARYING(3500),
246 FORMFIELDS CHARACTER VARYING(3500),
247 CONDITION_LARGE_SQL TEXT,
248 ENCRYPT_YN CHARACTER(1) DEFAULT 'N',
249 ATTACHMENT_YN CHARACTER(1) DEFAULT 'Y'
253 -- NAME: CR_REPORT_SCHEDULE_USERS; TYPE: TABLE
255 create table cr_report_schedule_users (
256 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
257 REP_ID NUMERIC(11,0) NOT NULL,
258 USER_ID NUMERIC(11,0) NOT NULL,
259 ROLE_ID NUMERIC(11,0),
260 ORDER_NO NUMERIC(11,0) NOT NULL
264 -- NAME: CR_REPORT_TEMPLATE_MAP; TYPE: TABLE
266 create table cr_report_template_map (
267 REPORT_ID INTEGER NOT NULL,
268 TEMPLATE_FILE CHARACTER VARYING(200)
272 -- NAME: CR_SCHEDULE_ACTIVITY_LOG; TYPE: TABLE
274 create table cr_schedule_activity_log (
275 SCHEDULE_ID NUMERIC(11,0) NOT NULL,
276 URL CHARACTER VARYING(4000),
277 NOTES CHARACTER VARYING(2000),
282 -- NAME: CR_TABLE_JOIN; TYPE: TABLE
284 create table cr_table_join (
285 SRC_TABLE_NAME CHARACTER VARYING(30) NOT NULL,
286 DEST_TABLE_NAME CHARACTER VARYING(30) NOT NULL,
287 JOIN_EXPR CHARACTER VARYING(500) NOT NULL
291 -- NAME: CR_TABLE_ROLE; TYPE: TABLE
293 create table cr_table_role (
294 TABLE_NAME CHARACTER VARYING(30) NOT NULL,
295 ROLE_ID NUMERIC(11,0) NOT NULL
299 -- NAME: CR_TABLE_SOURCE; TYPE: TABLE
301 create table cr_table_source (
302 TABLE_NAME CHARACTER VARYING(30) NOT NULL,
303 DISPLAY_NAME CHARACTER VARYING(30) NOT NULL,
304 PK_FIELDS CHARACTER VARYING(200),
305 WEB_VIEW_ACTION CHARACTER VARYING(50),
306 LARGE_DATA_SOURCE_YN CHARACTER VARYING(1) DEFAULT 'N' NOT NULL,
307 FILTER_SQL CHARACTER VARYING(4000),
308 SOURCE_DB CHARACTER VARYING(50)
312 -- NAME: FN_LU_TIMEZONE; TYPE: TABLE
314 create table fn_lu_timezone (
315 TIMEZONE_ID INT(11) NOT NULL,
316 TIMEZONE_NAME CHARACTER VARYING(100) NOT NULL,
317 TIMEZONE_VALUE CHARACTER VARYING(100) NOT NULL
320 create table fn_user (
321 USER_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
324 FIRST_NAME CHARACTER VARYING(25),
325 MIDDLE_NAME CHARACTER VARYING(25),
326 LAST_NAME CHARACTER VARYING(25),
327 PHONE CHARACTER VARYING(25),
328 FAX CHARACTER VARYING(25),
329 CELLULAR CHARACTER VARYING(25),
330 EMAIL CHARACTER VARYING(50),
331 ADDRESS_ID NUMERIC(11,0),
332 ALERT_METHOD_CD CHARACTER VARYING(10),
333 HRID CHARACTER VARYING(20),
334 ORG_USER_ID CHARACTER VARYING(20),
335 ORG_CODE CHARACTER VARYING(30),
336 LOGIN_ID CHARACTER VARYING(25),
337 LOGIN_PWD CHARACTER VARYING(25),
338 LAST_LOGIN_DATE TIMESTAMP,
339 ACTIVE_YN CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
341 CREATED_DATE TIMESTAMP DEFAULT NOW(),
343 MODIFIED_DATE TIMESTAMP default now(),
344 IS_INTERNAL_YN CHARACTER(1) DEFAULT 'N' NOT NULL,
345 ADDRESS_LINE_1 CHARACTER VARYING(100),
346 ADDRESS_LINE_2 CHARACTER VARYING(100),
347 CITY CHARACTER VARYING(50),
348 STATE_CD CHARACTER VARYING(3),
349 ZIP_CODE CHARACTER VARYING(11),
350 COUNTRY_CD CHARACTER VARYING(3),
351 LOCATION_CLLI CHARACTER VARYING(8),
352 ORG_MANAGER_USERID CHARACTER VARYING(20),
353 COMPANY CHARACTER VARYING(100),
354 DEPARTMENT_NAME CHARACTER VARYING(100),
355 JOB_TITLE CHARACTER VARYING(100),
357 DEPARTMENT CHARACTER VARYING(25),
358 BUSINESS_UNIT CHARACTER VARYING(25),
359 BUSINESS_UNIT_NAME CHARACTER VARYING(100),
360 COST_CENTER CHARACTER VARYING(25),
361 FIN_LOC_CODE CHARACTER VARYING(10),
362 SILO_STATUS CHARACTER VARYING(10)
366 -- NAME: FN_ROLE; TYPE: TABLE
368 create table fn_role (
369 ROLE_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
370 ROLE_NAME CHARACTER VARYING(50) NOT NULL,
371 ACTIVE_YN CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
372 PRIORITY NUMERIC(4,0)
376 -- NAME: FN_AUDIT_ACTION; TYPE: TABLE
378 create table fn_audit_action (
379 AUDIT_ACTION_ID INTEGER NOT NULL,
380 CLASS_NAME CHARACTER VARYING(500) NOT NULL,
381 METHOD_NAME CHARACTER VARYING(50) NOT NULL,
382 AUDIT_ACTION_CD CHARACTER VARYING(20) NOT NULL,
383 AUDIT_ACTION_DESC CHARACTER VARYING(200),
384 ACTIVE_YN CHARACTER VARYING(1)
388 -- NAME: FN_AUDIT_ACTION_LOG; TYPE: TABLE
390 create table fn_audit_action_log (
391 AUDIT_LOG_ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
392 AUDIT_ACTION_CD CHARACTER VARYING(200),
393 ACTION_TIME TIMESTAMP,
394 USER_ID NUMERIC(11,0),
395 CLASS_NAME CHARACTER VARYING(100),
396 METHOD_NAME CHARACTER VARYING(50),
397 SUCCESS_MSG CHARACTER VARYING(20),
398 ERROR_MSG CHARACTER VARYING(500)
402 -- NAME: FN_LU_ACTIVITY; TYPE: TABLE
404 create table fn_lu_activity (
405 ACTIVITY_CD CHARACTER VARYING(50) NOT NULL PRIMARY KEY,
406 ACTIVITY CHARACTER VARYING(50) NOT NULL
410 -- NAME: FN_AUDIT_LOG; TYPE: TABLE
412 create table fn_audit_log (
413 LOG_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
414 USER_ID INT(11) NOT NULL,
415 ACTIVITY_CD CHARACTER VARYING(50) NOT NULL,
416 AUDIT_DATE TIMESTAMP DEFAULT NOW() NOT NULL,
417 COMMENTS CHARACTER VARYING(1000),
418 AFFECTED_RECORD_ID_BK CHARACTER VARYING(500),
419 AFFECTED_RECORD_ID CHARACTER VARYING(4000),
420 CONSTRAINT FK_FN_AUDIT_REF_209_FN_USER FOREIGN KEY (USER_ID) REFERENCES FN_USER(USER_ID)
424 -- NAME: FN_BROADCAST_MESSAGE; TYPE: TABLE
426 create table fn_broadcast_message (
427 MESSAGE_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
428 MESSAGE_TEXT CHARACTER VARYING(1000) NOT NULL,
429 MESSAGE_LOCATION_ID NUMERIC(11,0) NOT NULL,
430 BROADCAST_START_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
431 BROADCAST_END_DATE TIMESTAMP NOT NULL DEFAULT NOW(),
432 ACTIVE_YN CHARACTER(1) DEFAULT 'Y' NOT NULL,
433 SORT_ORDER NUMERIC(4,0) NOT NULL,
434 BROADCAST_SITE_CD CHARACTER VARYING(50)
438 -- NAME: FN_CHAT_LOGS; TYPE: TABLE
440 create table fn_chat_logs (
441 CHAT_LOG_ID INTEGER NOT NULL,
442 CHAT_ROOM_ID INTEGER,
444 MESSAGE CHARACTER VARYING(1000),
445 MESSAGE_DATE_TIME TIMESTAMP
449 -- NAME: FN_CHAT_ROOM; TYPE: TABLE
451 create table fn_chat_room (
452 CHAT_ROOM_ID INTEGER NOT NULL,
453 NAME CHARACTER VARYING(50) NOT NULL,
454 DESCRIPTION CHARACTER VARYING(500),
456 CREATED_DATE TIMESTAMP DEFAULT NOW(),
457 UPDATED_DATE TIMESTAMP DEFAULT NOW()
461 -- NAME: FN_CHAT_USERS; TYPE: TABLE
463 create table fn_chat_users (
464 CHAT_ROOM_ID INTEGER,
466 LAST_ACTIVITY_DATE_TIME TIMESTAMP,
467 CHAT_STATUS CHARACTER VARYING(20),
472 -- NAME: FN_DATASOURCE; TYPE: TABLE
474 create table fn_datasource (
475 ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
476 NAME CHARACTER VARYING(50),
477 DRIVER_NAME CHARACTER VARYING(256),
478 SERVER CHARACTER VARYING(256),
480 USER_NAME CHARACTER VARYING(256),
481 PASSWORD CHARACTER VARYING(256),
482 URL CHARACTER VARYING(256),
483 MIN_POOL_SIZE INTEGER,
484 MAX_POOL_SIZE INTEGER,
486 DS_TYPE CHARACTER VARYING(20)
490 -- NAME: FN_FUNCTION; TYPE: TABLE
492 create table fn_function (
493 FUNCTION_CD CHARACTER VARYING(30) NOT NULL PRIMARY KEY,
494 FUNCTION_NAME CHARACTER VARYING(50) NOT NULL
498 -- NAME: FN_LU_ALERT_METHOD; TYPE: TABLE
500 create table fn_lu_alert_method (
501 ALERT_METHOD_CD CHARACTER VARYING(10) NOT NULL,
502 ALERT_METHOD CHARACTER VARYING(50) NOT NULL
506 -- NAME: FN_LU_BROADCAST_SITE; TYPE: TABLE
508 create table fn_lu_broadcast_site (
509 BROADCAST_SITE_CD CHARACTER VARYING(50) NOT NULL,
510 BROADCAST_SITE_DESCR CHARACTER VARYING(100)
513 -- NAME: FN_LU_MENU_SET; TYPE: TABLE
515 create table fn_lu_menu_set (
516 MENU_SET_CD CHARACTER VARYING(10) NOT NULL PRIMARY KEY,
517 MENU_SET_NAME CHARACTER VARYING(50) NOT NULL
521 -- NAME: FN_LU_PRIORITY; TYPE: TABLE
523 create table fn_lu_priority (
524 PRIORITY_ID NUMERIC(11,0) NOT NULL,
525 PRIORITY CHARACTER VARYING(50) NOT NULL,
526 ACTIVE_YN CHARACTER(1) NOT NULL,
527 SORT_ORDER NUMERIC(5,0)
531 -- NAME: FN_LU_ROLE_TYPE; TYPE: TABLE
533 create table fn_lu_role_type (
534 ROLE_TYPE_ID NUMERIC(11,0) NOT NULL,
535 ROLE_TYPE CHARACTER VARYING(50) NOT NULL
538 -- NAME: FN_LU_TAB_SET; TYPE: TABLE
540 create table fn_lu_tab_set (
541 TAB_SET_CD CHARACTER VARYING(30) NOT NULL,
542 TAB_SET_NAME CHARACTER VARYING(50) NOT NULL
546 -- NAME: FN_MENU; TYPE: TABLE
548 create table fn_menu (
549 MENU_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
550 LABEL CHARACTER VARYING(100),
552 SORT_ORDER NUMERIC(4,0),
553 ACTION CHARACTER VARYING(200),
554 FUNCTION_CD CHARACTER VARYING(30),
555 ACTIVE_YN CHARACTER VARYING(1) DEFAULT 'Y' NOT NULL,
556 SERVLET CHARACTER VARYING(50),
557 QUERY_STRING CHARACTER VARYING(200),
558 EXTERNAL_URL CHARACTER VARYING(200),
559 TARGET CHARACTER VARYING(25),
560 MENU_SET_CD CHARACTER VARYING(10) DEFAULT 'APP',
561 SEPARATOR_YN CHARACTER(1) DEFAULT 'N',
562 IMAGE_SRC CHARACTER VARYING(100),
563 CONSTRAINT FK_FN_MENU_REF_196_FN_MENU FOREIGN KEY (PARENT_ID) REFERENCES FN_MENU(MENU_ID),
564 CONSTRAINT FK_FN_MENU_MENU_SET_CD FOREIGN KEY (MENU_SET_CD) REFERENCES FN_LU_MENU_SET(MENU_SET_CD),
565 CONSTRAINT FK_FN_MENU_REF_223_FN_FUNCT FOREIGN KEY (FUNCTION_CD) REFERENCES FN_FUNCTION(FUNCTION_CD)
569 -- NAME: FN_ORG; TYPE: TABLE
571 create table fn_org (
572 ORG_ID INT(11) NOT NULL,
573 ORG_NAME CHARACTER VARYING(50) NOT NULL,
574 ACCESS_CD CHARACTER VARYING(10)
578 -- NAME: FN_RESTRICTED_URL; TYPE: TABLE
580 create table fn_restricted_url (
581 RESTRICTED_URL CHARACTER VARYING(250) NOT NULL,
582 FUNCTION_CD CHARACTER VARYING(30) NOT NULL
586 -- NAME: FN_ROLE_COMPOSITE; TYPE: TABLE
588 create table fn_role_composite (
589 PARENT_ROLE_ID INT(11) NOT NULL,
590 CHILD_ROLE_ID INT(11) NOT NULL,
591 CONSTRAINT FK_FN_ROLE_COMPOSITE_CHILD FOREIGN KEY (CHILD_ROLE_ID) REFERENCES FN_ROLE(ROLE_ID),
592 CONSTRAINT FK_FN_ROLE_COMPOSITE_PARENT FOREIGN KEY (PARENT_ROLE_ID) REFERENCES FN_ROLE(ROLE_ID)
596 -- NAME: FN_ROLE_FUNCTION; TYPE: TABLE
598 create table fn_role_function (
599 ROLE_ID INT(11) NOT NULL,
600 FUNCTION_CD CHARACTER VARYING(30) NOT NULL,
601 CONSTRAINT FK_FN_ROLE__REF_198_FN_ROLE FOREIGN KEY (ROLE_ID) REFERENCES FN_ROLE(ROLE_ID)
605 -- NAME: FN_TAB; TYPE: TABLE
607 create table fn_tab (
608 TAB_CD CHARACTER VARYING(30) NOT NULL,
609 TAB_NAME CHARACTER VARYING(50) NOT NULL,
610 TAB_DESCR CHARACTER VARYING(100),
611 ACTION CHARACTER VARYING(100) NOT NULL,
612 FUNCTION_CD CHARACTER VARYING(30) NOT NULL,
613 ACTIVE_YN CHARACTER(1) NOT NULL,
614 SORT_ORDER NUMERIC(11,0) NOT NULL,
615 PARENT_TAB_CD CHARACTER VARYING(30),
616 TAB_SET_CD CHARACTER VARYING(30)
620 -- NAME: FN_TAB_SELECTED; TYPE: TABLE
622 create table fn_tab_selected (
623 SELECTED_TAB_CD CHARACTER VARYING(30) NOT NULL,
624 TAB_URI CHARACTER VARYING(40) NOT NULL
628 -- NAME: FN_USER_PSEUDO_ROLE; TYPE: TABLE
630 create table fn_user_pseudo_role (
631 PSEUDO_ROLE_ID INT(11) NOT NULL,
632 USER_ID INT(11) NOT NULL
636 -- NAME: FN_USER_ROLE; TYPE: TABLE
638 create table fn_user_role (
639 USER_ID INT(10) NOT NULL,
640 ROLE_ID INT(10) NOT NULL,
641 PRIORITY NUMERIC(4,0),
642 APP_ID INT(11) DEFAULT 1,
643 CONSTRAINT FK_FN_USER__REF_172_FN_USER FOREIGN KEY (USER_ID) REFERENCES FN_USER(USER_ID),
644 CONSTRAINT FK_FN_USER__REF_175_FN_ROLE FOREIGN KEY (ROLE_ID) REFERENCES FN_ROLE(ROLE_ID)
647 -- NAME: SCHEMA_INFO; TYPE: TABLE
649 create table schema_info (
650 SCHEMA_ID CHARACTER VARYING(25) NOT NULL,
651 SCHEMA_DESC CHARACTER VARYING(75) NOT NULL,
652 DATASOURCE_TYPE CHARACTER VARYING(100),
653 CONNECTION_URL VARCHAR(200) NOT NULL,
654 USER_NAME VARCHAR(45) NOT NULL,
655 PASSWORD VARCHAR(45) NULL DEFAULT NULL,
656 DRIVER_CLASS VARCHAR(100) NOT NULL,
657 MIN_POOL_SIZE INT NOT NULL,
658 MAX_POOL_SIZE INT NOT NULL,
659 IDLE_CONNECTION_TEST_PERIOD INT NOT NULL
663 -- ----------------------------------------------------------
664 -- NAME: FN_APP; TYPE: TABLE
665 -- ----------------------------------------------------------
666 create table fn_app (
667 APP_ID int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
668 APP_NAME varchar(100) NOT NULL DEFAULT '?',
669 APP_IMAGE_URL varchar(256) DEFAULT NULL,
670 APP_DESCRIPTION varchar(512) DEFAULT NULL,
671 APP_NOTES varchar(4096) DEFAULT NULL,
672 APP_URL varchar(256) DEFAULT NULL,
673 APP_ALTERNATE_URL varchar(256) DEFAULT NULL,
674 APP_REST_ENDPOINT varchar(2000) DEFAULT NULL,
675 ML_APP_NAME varchar(50) NOT NULL DEFAULT '?',
676 ML_APP_ADMIN_ID varchar(7) NOT NULL DEFAULT '?',
677 MOTS_ID int(11) DEFAULT NULL,
678 APP_PASSWORD varchar(256) NOT NULL DEFAULT '?',
679 OPEN char(1) DEFAULT 'N',
680 ENABLED char(1) DEFAULT 'Y',
681 THUMBNAIL mediumblob,
682 APP_USERNAME varchar(50),
683 UEB_KEY VARCHAR(256) DEFAULT NULL,
684 UEB_SECRET VARCHAR(256) DEFAULT NULL,
685 UEB_TOPIC_NAME VARCHAR(256) DEFAULT NULL
689 -- ----------------------------------------------------------
690 -- NAME: FN_FN_WORKFLOW; TYPE: TABLE
691 -- ----------------------------------------------------------
692 create table fn_workflow (
693 id mediumint(9) NOT NULL AUTO_INCREMENT,
694 name varchar(20) NOT NULL,
695 description varchar(500) DEFAULT NULL,
696 run_link varchar(300) DEFAULT NULL,
697 suspend_link varchar(300) DEFAULT NULL,
698 modified_link varchar(300) DEFAULT NULL,
699 active_yn varchar(300) DEFAULT NULL,
700 created varchar(300) DEFAULT NULL,
701 created_by int(11) DEFAULT NULL,
702 modified varchar(300) DEFAULT NULL,
703 modified_by int(11) DEFAULT NULL,
704 workflow_key varchar(50) DEFAULT NULL,
706 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
723 -- For demo reporting application add : demo_bar_chart, demo_bar_chart_inter, demo_line_chart, demo_pie_chart and demo_util_chart
724 -- demo_scatter_chart, demo_scatter_plot
725 -- ----------------------------------------------------------
726 -- NAME: DEMO_BAR_CHART; TYPE: TABLE
727 -- ----------------------------------------------------------
728 create table demo_bar_chart (
733 -- ----------------------------------------------------------
734 -- NAME: DEMO_BAR_CHART_INTER; TYPE: TABLE
735 -- ----------------------------------------------------------
736 create table demo_bar_chart_inter (
738 num_rpt_sources numeric(10,0),
739 num_det_sources numeric(10,0)
742 -- ----------------------------------------------------------
743 -- NAME: DEMO_LINE_CHART; TYPE: TABLE
744 -- ----------------------------------------------------------
745 create table demo_line_chart (
748 data_value numeric(10,5)
751 -- ----------------------------------------------------------
752 -- NAME: DEMO_PIE_CHART; TYPE: TABLE
753 -- ----------------------------------------------------------
754 create table demo_pie_chart (
756 data_value numeric(10,5)
759 -- ----------------------------------------------------------
760 -- NAME: DEMO_UTIL_CHART; TYPE: TABLE
761 -- ----------------------------------------------------------
762 create table demo_util_chart (
764 util_perc numeric(10,5)
767 -- ----------------------------------------------------------
768 -- NAME: DEMO_SCATTER_CHART; TYPE: TABLE
769 -- ----------------------------------------------------------
770 create table demo_scatter_chart (
771 rainfall numeric(10,2),
772 key_value varchar(20),
773 measurements numeric(10,2)
776 -- ----------------------------------------------------------
777 -- NAME: DEMO_SCATTER_PLOT; TYPE: TABLE
778 -- ----------------------------------------------------------
779 create table demo_scatter_plot
782 VALUEX numeric(25,15),
783 VALUEY numeric(25,15)
786 -- ----------------------------------------------------------
787 -- NAME: FN_QZ_JOB_DETAILS; TYPE: TABLE
788 -- ----------------------------------------------------------
789 create table fn_qz_job_details (
790 SCHED_NAME VARCHAR(120) NOT NULL,
791 JOB_NAME VARCHAR(200) NOT NULL,
792 JOB_GROUP VARCHAR(200) NOT NULL,
793 DESCRIPTION VARCHAR(250) NULL,
794 JOB_CLASS_NAME VARCHAR(250) NOT NULL,
795 IS_DURABLE VARCHAR(1) NOT NULL,
796 IS_NONCONCURRENT VARCHAR(1) NOT NULL,
797 IS_UPDATE_DATA VARCHAR(1) NOT NULL,
798 REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
800 PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
803 -- ----------------------------------------------------------
804 -- NAME: FN_QZ_TRIGGERS; TYPE: TABLE
805 -- ----------------------------------------------------------
806 create table fn_qz_triggers (
807 SCHED_NAME VARCHAR(120) NOT NULL,
808 TRIGGER_NAME VARCHAR(200) NOT NULL,
809 TRIGGER_GROUP VARCHAR(200) NOT NULL,
810 JOB_NAME VARCHAR(200) NOT NULL,
811 JOB_GROUP VARCHAR(200) NOT NULL,
812 DESCRIPTION VARCHAR(250) NULL,
813 NEXT_FIRE_TIME BIGINT(13) NULL,
814 PREV_FIRE_TIME BIGINT(13) NULL,
815 PRIORITY INTEGER NULL,
816 TRIGGER_STATE VARCHAR(16) NOT NULL,
817 TRIGGER_TYPE VARCHAR(8) NOT NULL,
818 START_TIME BIGINT(13) NOT NULL,
819 END_TIME BIGINT(13) NULL,
820 CALENDAR_NAME VARCHAR(200) NULL,
821 MISFIRE_INSTR SMALLINT(2) NULL,
823 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
824 FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
825 REFERENCES FN_QZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
828 -- ----------------------------------------------------------
829 -- NAME: FN_QZ_SIMPLE_TRIGGERS; TYPE: TABLE
830 -- ----------------------------------------------------------
831 create table fn_qz_simple_triggers (
832 SCHED_NAME VARCHAR(120) NOT NULL,
833 TRIGGER_NAME VARCHAR(200) NOT NULL,
834 TRIGGER_GROUP VARCHAR(200) NOT NULL,
835 REPEAT_COUNT BIGINT(7) NOT NULL,
836 REPEAT_INTERVAL BIGINT(12) NOT NULL,
837 TIMES_TRIGGERED BIGINT(10) NOT NULL,
838 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
839 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
840 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
843 -- ----------------------------------------------------------
844 -- NAME: FN_QZ_CRON_TRIGGERS; TYPE: TABLE
845 -- ----------------------------------------------------------
846 create table fn_qz_cron_triggers (
847 SCHED_NAME VARCHAR(120) NOT NULL,
848 TRIGGER_NAME VARCHAR(200) NOT NULL,
849 TRIGGER_GROUP VARCHAR(200) NOT NULL,
850 CRON_EXPRESSION VARCHAR(120) NOT NULL,
851 TIME_ZONE_ID VARCHAR(80),
852 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
853 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
854 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
857 -- ----------------------------------------------------------
858 -- NAME: FN_QZ_SIMPROP_TRIGGERS; TYPE: TABLE
859 -- ----------------------------------------------------------
860 create table fn_qz_simprop_triggers
862 SCHED_NAME VARCHAR(120) NOT NULL,
863 TRIGGER_NAME VARCHAR(200) NOT NULL,
864 TRIGGER_GROUP VARCHAR(200) NOT NULL,
865 STR_PROP_1 VARCHAR(512) NULL,
866 STR_PROP_2 VARCHAR(512) NULL,
867 STR_PROP_3 VARCHAR(512) NULL,
870 LONG_PROP_1 BIGINT NULL,
871 LONG_PROP_2 BIGINT NULL,
872 DEC_PROP_1 NUMERIC(13,4) NULL,
873 DEC_PROP_2 NUMERIC(13,4) NULL,
874 BOOL_PROP_1 VARCHAR(1) NULL,
875 BOOL_PROP_2 VARCHAR(1) NULL,
876 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
877 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
878 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
881 -- ----------------------------------------------------------
882 -- NAME: FN_QZ_BLOB_TRIGGERS; TYPE: TABLE
883 -- ----------------------------------------------------------
884 create table fn_qz_blob_triggers (
885 SCHED_NAME VARCHAR(120) NOT NULL,
886 TRIGGER_NAME VARCHAR(200) NOT NULL,
887 TRIGGER_GROUP VARCHAR(200) NOT NULL,
889 PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
890 INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),
891 FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
892 REFERENCES FN_QZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
895 -- ----------------------------------------------------------
896 -- NAME: FN_QZ_CALENDARS; TYPE: TABLE
897 -- ----------------------------------------------------------
898 create table fn_qz_calendars (
899 SCHED_NAME VARCHAR(120) NOT NULL,
900 CALENDAR_NAME VARCHAR(200) NOT NULL,
901 CALENDAR BLOB NOT NULL,
902 PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
905 -- ----------------------------------------------------------
906 -- NAME: FN_QZ_PAUSED_TRIGGER_GRPS; TYPE: TABLE
907 -- ----------------------------------------------------------
908 create table fn_qz_paused_trigger_grps (
909 SCHED_NAME VARCHAR(120) NOT NULL,
910 TRIGGER_GROUP VARCHAR(200) NOT NULL,
911 PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
914 -- ----------------------------------------------------------
915 -- NAME: FN_QZ_FIRED_TRIGGERS; TYPE: TABLE
916 -- ----------------------------------------------------------
917 create table fn_qz_fired_triggers (
918 SCHED_NAME VARCHAR(120) NOT NULL,
919 ENTRY_ID VARCHAR(95) NOT NULL,
920 TRIGGER_NAME VARCHAR(200) NOT NULL,
921 TRIGGER_GROUP VARCHAR(200) NOT NULL,
922 INSTANCE_NAME VARCHAR(200) NOT NULL,
923 FIRED_TIME BIGINT(13) NOT NULL,
924 SCHED_TIME BIGINT(13) NOT NULL,
925 PRIORITY INTEGER NOT NULL,
926 STATE VARCHAR(16) NOT NULL,
927 JOB_NAME VARCHAR(200) NULL,
928 JOB_GROUP VARCHAR(200) NULL,
929 IS_NONCONCURRENT VARCHAR(1) NULL,
930 REQUESTS_RECOVERY VARCHAR(1) NULL,
931 PRIMARY KEY (SCHED_NAME,ENTRY_ID)
934 -- ----------------------------------------------------------
935 -- NAME: FN_QZ_SCHEDULER_STATE; TYPE: TABLE
936 -- ----------------------------------------------------------
937 create table fn_qz_scheduler_state (
938 SCHED_NAME VARCHAR(120) NOT NULL,
939 INSTANCE_NAME VARCHAR(200) NOT NULL,
940 LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
941 CHECKIN_INTERVAL BIGINT(13) NOT NULL,
942 PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
945 -- ----------------------------------------------------------
946 -- NAME: FN_QZ_LOCKS; TYPE: TABLE
947 -- ----------------------------------------------------------
948 create table fn_qz_locks (
949 SCHED_NAME VARCHAR(120) NOT NULL,
950 LOCK_NAME VARCHAR(40) NOT NULL,
951 PRIMARY KEY (SCHED_NAME,LOCK_NAME)
955 -- name: rcloudinvocation; type: table
957 create table rcloudinvocation (
958 id varchar(128) not null primary key,
959 created timestamp not null,
960 userinfo varchar(2048) not null,
961 notebookid varchar(128) not null,
962 parameters varchar(2048) default null,
963 tokenreaddate timestamp null
967 -- name: rcloudnotebook; type: table
969 create table rcloudnotebook (
970 notebookname varchar(128) not null primary key,
971 notebookid varchar(128) not null
975 -- Name: fn_lu_message_location; Type: TABLE
978 CREATE TABLE fn_lu_message_location (
979 message_location_id numeric(11,0) NOT NULL,
980 message_location_descr character varying(30) NOT NULL
983 -- ------------------ CREATE VIEW SECTION
985 -- NAME: V_URL_ACCESS; TYPE: VIEW
987 CREATE VIEW v_url_access AS
988 SELECT DISTINCT M.ACTION AS URL,
991 WHERE (M.ACTION IS NOT NULL)
993 SELECT DISTINCT T.ACTION AS URL,
996 WHERE (T.ACTION IS NOT NULL)
998 SELECT R.RESTRICTED_URL AS URL,
1000 FROM FN_RESTRICTED_URL R;
1002 -- ------------------ ALTER TABLE ADD CONSTRAINT PRIMARY KEY SECTION
1004 -- NAME: CR_FAVORITE_REPORTS_USER_IDREP_ID; TYPE: CONSTRAINT
1006 alter table cr_favorite_reports
1007 add constraint cr_favorite_reports_user_idrep_id primary key (user_id, rep_id);
1009 -- NAME: CR_FOLDER_FOLDER_ID; TYPE: CONSTRAINT
1011 alter table cr_folder
1012 add constraint cr_folder_folder_id primary key (folder_id);
1014 -- NAME: CR_FOLDER_ACCESS_FOLDER_ACCESS_ID; TYPE: CONSTRAINT
1016 alter table cr_folder_access
1017 add constraint cr_folder_access_folder_access_id primary key (folder_access_id);
1019 -- NAME: CR_HIST_USER_MAP_HIST_IDUSER_ID; TYPE: CONSTRAINT
1021 alter table cr_hist_user_map
1022 add constraint cr_hist_user_map_hist_iduser_id primary key (hist_id, user_id);
1024 -- NAME: CR_LU_FILE_TYPE_LOOKUP_ID; TYPE: CONSTRAINT
1026 alter table cr_lu_file_type
1027 add constraint cr_lu_file_type_lookup_id primary key (lookup_id);
1029 -- NAME: CR_RAPTOR_ACTION_IMG_IMAGE_ID; TYPE: CONSTRAINT
1031 alter table cr_raptor_action_img
1032 add constraint cr_raptor_action_img_image_id primary key (image_id);
1034 -- NAME: CR_RAPTOR_PDF_IMG_IMAGE_ID; TYPE: CONSTRAINT
1036 alter table cr_raptor_pdf_img
1037 add constraint cr_raptor_pdf_img_image_id primary key (image_id);
1039 -- NAME: CR_REMOTE_SCHEMA_INFO_SCHEMA_PREFIX; TYPE: CONSTRAINT
1041 alter table cr_remote_schema_info
1042 add constraint cr_remote_schema_info_schema_prefix primary key (schema_prefix);
1044 -- NAME: CR_REPORT_REP_ID; TYPE: CONSTRAINT
1046 alter table cr_report
1047 add constraint cr_report_rep_id primary key (rep_id);
1049 -- NAME: CR_REPORT_ACCESS_REP_IDORDER_NO; TYPE: CONSTRAINT
1051 alter table cr_report_access
1052 add constraint cr_report_access_rep_idorder_no primary key (rep_id, order_no);
1054 -- NAME: CR_REPORT_EMAIL_SENT_LOG_LOG_ID; TYPE: CONSTRAINT
1056 alter table cr_report_email_sent_log
1057 add constraint cr_report_email_sent_log_log_id primary key (log_id);
1059 -- NAME: CR_REPORT_FILE_HISTORY_HIST_ID; TYPE: CONSTRAINT
1061 alter table cr_report_file_history
1062 add constraint cr_report_file_history_hist_id primary key (hist_id);
1064 -- NAME: CR_REPORT_SCHEDULE_SCHEDULE_ID; TYPE: CONSTRAINT
1066 alter table cr_report_schedule
1067 add constraint cr_report_schedule_schedule_id primary key (schedule_id);
1069 -- NAME: CR_REPORT_SCHEDULE_USERS_SCHEDULE_IDREP_IDUSER_IDORDER_NO; TYPE: CONSTRAINT
1071 alter table cr_report_schedule_users
1072 add constraint cr_report_schedule_users_schedule_idrep_iduser_idorder_no primary key (schedule_id, rep_id, user_id, order_no);
1074 -- NAME: CR_REPORT_TEMPLATE_MAP_REPORT_ID; TYPE: CONSTRAINT
1076 alter table cr_report_template_map
1077 add constraint cr_report_template_map_report_id primary key (report_id);
1079 -- NAME: CR_TABLE_ROLE_TABLE_NAMEROLE_ID; TYPE: CONSTRAINT
1081 alter table cr_table_role
1082 add constraint cr_table_role_table_namerole_id primary key (table_name, role_id);
1084 -- NAME: CR_TABLE_SOURCE_TABLE_NAME; TYPE: CONSTRAINT
1086 alter table cr_table_source
1087 add constraint cr_table_source_table_name primary key (table_name);
1089 -- NAME: FN_AUDIT_ACTION_AUDIT_ACTION_ID; TYPE: CONSTRAINT
1091 alter table fn_audit_action
1092 add constraint fn_audit_action_audit_action_id primary key (audit_action_id);
1094 -- NAME: FN_CHAT_LOGS_CHAT_LOG_ID; TYPE: CONSTRAINT
1096 alter table fn_chat_logs
1097 add constraint fn_chat_logs_chat_log_id primary key (chat_log_id);
1099 -- NAME: FN_CHAT_ROOM_CHAT_ROOM_ID; TYPE: CONSTRAINT
1101 alter table fn_chat_room
1102 add constraint fn_chat_room_chat_room_id primary key (chat_room_id);
1104 -- NAME: FN_CHAT_USERS_ID; TYPE: CONSTRAINT
1106 alter table fn_chat_users
1107 add constraint fn_chat_users_id primary key (id);
1109 -- NAME: FN_LU_ALERT_METHOD_ALERT_METHOD_CD; TYPE: CONSTRAINT
1111 alter table fn_lu_alert_method
1112 add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd);
1114 -- NAME: FN_LU_BROADCAST_SITE_BROADCAST_SITE_CD; TYPE: CONSTRAINT
1116 alter table fn_lu_broadcast_site
1117 add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd);
1119 -- NAME: FN_LU_PRIORITY_PRIORITY_ID; TYPE: CONSTRAINT
1121 alter table fn_lu_priority
1122 add constraint fn_lu_priority_priority_id primary key (priority_id);
1124 -- NAME: FN_LU_ROLE_TYPE_ROLE_TYPE_ID; TYPE: CONSTRAINT
1126 alter table fn_lu_role_type
1127 add constraint fn_lu_role_type_role_type_id primary key (role_type_id);
1129 -- NAME: FN_LU_TAB_SET_TAB_SET_CD; TYPE: CONSTRAINT
1131 alter table fn_lu_tab_set
1132 add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd);
1134 -- NAME: FN_LU_TIMEZONE_TIMEZONE_ID; TYPE: CONSTRAINT
1136 alter table fn_lu_timezone
1137 add constraint fn_lu_timezone_timezone_id primary key (timezone_id);
1139 -- NAME: FN_ORG_ORG_ID; TYPE: CONSTRAINT
1142 add constraint fn_org_org_id primary key (org_id);
1144 -- NAME: FN_RESTRICTED_URL_RESTRICTED_URLFUNCTION_CD; TYPE: CONSTRAINT
1146 alter table fn_restricted_url
1147 add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd);
1149 -- NAME: FN_ROLE_COMPOSITE_PARENT_ROLE_IDCHILD_ROLE_ID; TYPE: CONSTRAINT
1151 alter table fn_role_composite
1152 add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id);
1154 -- NAME: FN_ROLE_FUNCTION_ROLE_IDFUNCTION_CD; TYPE: CONSTRAINT
1156 alter table fn_role_function
1157 add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd);
1159 -- NAME: FN_TAB_TAB_CD; TYPE: CONSTRAINT
1162 add constraint fn_tab_tab_cd primary key (tab_cd);
1164 -- NAME: FN_TAB_SELECTED_SELECTED_TAB_CDTAB_URI; TYPE: CONSTRAINT
1166 alter table fn_tab_selected
1167 add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri);
1169 -- NAME: FN_USER_PSEUDO_ROLE_PSEUDO_ROLE_IDUSER_ID; TYPE: CONSTRAINT
1171 alter table fn_user_pseudo_role
1172 add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id);
1174 -- NAME: FN_USER_ROLE_USER_IDROLE_ID; TYPE: CONSTRAINT
1176 alter table fn_user_role
1177 add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id);
1179 -- Name: fn_lu_message_location_MESSAGE_LOCATION_ID; Type: CONSTRAINT
1182 ALTER TABLE fn_lu_message_location
1183 ADD CONSTRAINT fn_lu_message_location_MESSAGE_LOCATION_ID PRIMARY KEY (message_location_id);
1185 -- ------------------ CREATE INDEX SECTION
1187 -- NAME: CR_REPORT_CREATE_IDPUBLIC_YNTITLE; TYPE: INDEX
1189 create index cr_report_create_idpublic_yntitle using btree on cr_report (create_id, public_yn, title);
1191 -- NAME: CR_TABLE_JOIN_DEST_TABLE_NAME; TYPE: INDEX
1193 create index cr_table_join_dest_table_name using btree on cr_table_join (dest_table_name);
1195 -- NAME: CR_TABLE_JOIN_SRC_TABLE_NAME; TYPE: INDEX
1197 create index cr_table_join_src_table_name using btree on cr_table_join (src_table_name);
1199 -- NAME: FN_AUDIT_LOG_ACTIVITY_CD; TYPE: INDEX
1201 create index fn_audit_log_activity_cd using btree on fn_audit_log (activity_cd);
1203 -- NAME: FN_AUDIT_LOG_USER_ID; TYPE: INDEX
1205 create index fn_audit_log_user_id using btree on fn_audit_log (user_id);
1207 -- NAME: FN_MENU_FUNCTION_CD; TYPE: INDEX
1209 create index fn_menu_function_cd using btree on fn_menu (function_cd);
1211 -- NAME: FN_ORG_ACCESS_CD; TYPE: INDEX
1213 create index fn_org_access_cd using btree on fn_org (access_cd);
1215 -- NAME: FN_ROLE_FUNCTION_FUNCTION_CD; TYPE: INDEX
1217 create index fn_role_function_function_cd using btree on fn_role_function (function_cd);
1219 -- NAME: FN_ROLE_FUNCTION_ROLE_ID; TYPE: INDEX
1221 create index fn_role_function_role_id using btree on fn_role_function (role_id);
1223 -- NAME: FN_USER_ADDRESS_ID; TYPE: INDEX
1225 create index fn_user_address_id using btree on fn_user (address_id);
1227 -- NAME: FN_USER_ALERT_METHOD_CD; TYPE: INDEX
1229 create index fn_user_alert_method_cd using btree on fn_user (alert_method_cd);
1231 -- NAME: FN_USER_HRID; TYPE: INDEX
1233 create unique index fn_user_hrid using btree on fn_user (hrid);
1235 -- NAME: FN_USER_LOGIN_ID; TYPE: INDEX
1237 create unique index fn_user_login_id using btree on fn_user (login_id);
1239 -- NAME: FN_USER_ORG_ID; TYPE: INDEX
1241 create index fn_user_org_id using btree on fn_user (org_id);
1243 -- NAME: FN_USER_ROLE_ROLE_ID; TYPE: INDEX
1245 create index fn_user_role_role_id using btree on fn_user_role (role_id);
1247 -- NAME: FN_USER_ROLE_USER_ID; TYPE: INDEX
1249 create index fn_user_role_user_id using btree on fn_user_role (user_id);
1251 -- NAME: FK_FN_USER__REF_178_FN_APP_idx; TYPE: INDEX
1253 create index fk_fn_user__ref_178_fn_app_IDX on fn_user_role (app_id);
1255 -- ----------------------------------------------------------
1256 -- NAME: QUARTZ TYPE: INDEXES
1257 -- ----------------------------------------------------------
1258 create index idx_fn_qz_j_req_recovery on fn_qz_job_details(sched_name,requests_recovery);
1259 create index idx_fn_qz_j_grp on fn_qz_job_details(sched_name,job_group);
1260 create index idx_fn_qz_t_j on fn_qz_triggers(sched_name,job_name,job_group);
1261 create index idx_fn_qz_t_jg on fn_qz_triggers(sched_name,job_group);
1262 create index idx_fn_qz_t_c on fn_qz_triggers(sched_name,calendar_name);
1263 create index idx_fn_qz_t_g on fn_qz_triggers(sched_name,trigger_group);
1264 create index idx_fn_qz_t_state on fn_qz_triggers(sched_name,trigger_state);
1265 create index idx_fn_qz_t_n_state on fn_qz_triggers(sched_name,trigger_name,trigger_group,trigger_state);
1266 create index idx_fn_qz_t_n_g_state on fn_qz_triggers(sched_name,trigger_group,trigger_state);
1267 create index idx_fn_qz_t_next_fire_time on fn_qz_triggers(sched_name,next_fire_time);
1268 create index idx_fn_qz_t_nft_st on fn_qz_triggers(sched_name,trigger_state,next_fire_time);
1269 create index idx_fn_qz_t_nft_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time);
1270 create index idx_fn_qz_t_nft_st_misfire on fn_qz_triggers(sched_name,misfire_instr,next_fire_time,trigger_state);
1271 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);
1272 create index idx_fn_qz_ft_trig_inst_name on fn_qz_fired_triggers(sched_name,instance_name);
1273 create index idx_fn_qz_ft_inst_job_req_rcvry on fn_qz_fired_triggers(sched_name,instance_name,requests_recovery);
1274 create index idx_fn_qz_ft_j_g on fn_qz_fired_triggers(sched_name,job_name,job_group);
1275 create index idx_fn_qz_ft_jg on fn_qz_fired_triggers(sched_name,job_group);
1276 create index idx_fn_qz_ft_t_g on fn_qz_fired_triggers(sched_name,trigger_name,trigger_group);
1277 create index idx_fn_qz_ft_tg on fn_qz_fired_triggers(sched_name,trigger_group);
1279 -- ------------------ ALTER TABLE ADD CONSTRAINT FOREIGN KEY SECTION
1281 -- NAME: FK_FN_AUDIT_REF_205_FN_LU_AC; TYPE: CONSTRAINT
1283 alter table fn_audit_log
1284 add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd);
1286 -- NAME: FK_FN_ROLE__REF_201_FN_FUNCT; TYPE: CONSTRAINT
1288 alter table fn_role_function
1289 add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd);
1291 -- NAME: FK_FN_USER__REF_178_FN_APP; TYPE: FK CONSTRAINT
1293 alter table fn_user_role
1294 add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id);
1296 -- NAME: FK_CR_REPOR_REF_14707_CR_REPOR; TYPE: FK CONSTRAINT
1298 alter table cr_report_schedule
1299 add constraint fk_cr_repor_ref_14707_cr_repor foreign key (rep_id) references cr_report(rep_id);
1301 -- NAME: FK_CR_REPOR_REF_14716_CR_REPOR; TYPE: FK CONSTRAINT
1303 alter table cr_report_schedule_users
1304 add constraint fk_cr_repor_ref_14716_cr_repor foreign key (schedule_id) references cr_report_schedule(schedule_id);
1306 -- NAME: FK_CR_REPOR_REF_17645_CR_REPOR; TYPE: FK CONSTRAINT
1308 alter table cr_report_log
1309 add constraint fk_cr_repor_ref_17645_cr_repor foreign key (rep_id) references cr_report(rep_id);
1311 -- NAME: FK_CR_REPOR_REF_8550_CR_REPOR; TYPE: FK CONSTRAINT
1313 alter table cr_report_access
1314 add constraint fk_cr_repor_ref_8550_cr_repor foreign key (rep_id) references cr_report(rep_id);
1316 -- NAME: FK_CR_REPORT_REP_ID; TYPE: FK CONSTRAINT
1318 alter table cr_report_email_sent_log
1319 add constraint fk_cr_report_rep_id foreign key (rep_id) references cr_report(rep_id);
1321 -- NAME: FK_CR_TABLE_REF_311_CR_TAB; TYPE: FK CONSTRAINT
1323 alter table cr_table_join
1324 add constraint fk_cr_table_ref_311_cr_tab foreign key (src_table_name) references cr_table_source(table_name);
1326 -- NAME: FK_CR_TABLE_REF_315_CR_TAB; TYPE: FK CONSTRAINT
1328 alter table cr_table_join
1329 add constraint fk_cr_table_ref_315_cr_tab foreign key (dest_table_name) references cr_table_source(table_name);
1331 -- NAME: FK_CR_TABLE_REF_32384_CR_TABLE; TYPE: FK CONSTRAINT
1333 alter table cr_table_role
1334 add constraint fk_cr_table_ref_32384_cr_table foreign key (table_name) references cr_table_source(table_name);
1336 -- NAME: FK_FN_TAB_FUNCTION_CD; TYPE: FK CONSTRAINT
1339 add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd);
1341 -- NAME: FK_FN_TAB_SELECTED_TAB_CD; TYPE: FK CONSTRAINT
1343 alter table fn_tab_selected
1344 add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd);
1346 -- NAME: FK_FN_TAB_SET_CD; TYPE: FK CONSTRAINT
1349 add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd);
1351 -- NAME: FK_FN_USER_REF_110_FN_ORG; TYPE: FK CONSTRAINT
1354 add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id);
1356 -- NAME: FK_FN_USER_REF_123_FN_LU_AL; TYPE: FK CONSTRAINT
1359 add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd);
1361 -- NAME: FK_FN_USER_REF_197_FN_USER; TYPE: FK CONSTRAINT
1364 add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id);
1366 -- NAME: FK_FN_USER_REF_198_FN_USER; TYPE: FK CONSTRAINT
1369 add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id);
1371 -- NAME: FK_FN_USER_REF_199_FN_USER; TYPE: FK CONSTRAINT
1374 add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id);
1376 -- NAME: FK_PARENT_KEY_CR_FOLDER; TYPE: FK CONSTRAINT
1378 alter table cr_folder
1379 add constraint fk_parent_key_cr_folder foreign key (parent_folder_id) references cr_folder(folder_id);
1381 -- NAME: FK_PSEUDO_ROLE_PSEUDO_ROLE_ID; TYPE: FK CONSTRAINT
1383 alter table fn_user_pseudo_role
1384 add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id);
1386 -- NAME: FK_PSEUDO_ROLE_USER_ID; TYPE: FK CONSTRAINT
1388 alter table fn_user_pseudo_role
1389 add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id);
1391 -- NAME: FK_RESTRICTED_URL_FUNCTION_CD; TYPE: FK CONSTRAINT
1393 alter table fn_restricted_url
1394 add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd);
1396 -- NAME: FK_TIMEZONE; TYPE: FK CONSTRAINT
1399 add constraint fk_timezone foreign key (timezone) references fn_lu_timezone(timezone_id);
1401 -- NAME: SYS_C0014614; TYPE: FK CONSTRAINT
1403 alter table cr_report_file_history
1404 add constraint sys_c0014614 foreign key (file_type_id) references cr_lu_file_type(lookup_id);
1406 -- NAME: SYS_C0014615; TYPE: FK CONSTRAINT
1408 alter table cr_report_file_history
1409 add constraint sys_c0014615 foreign key (rep_id) references cr_report(rep_id);
1411 -- NAME: SYS_C0014616; TYPE: FK CONSTRAINT
1413 alter table cr_hist_user_map
1414 add constraint sys_c0014616 foreign key (hist_id) references cr_report_file_history(hist_id);
1416 -- NAME: SYS_C0014617; TYPE: FK CONSTRAINT
1418 alter table cr_hist_user_map
1419 add constraint sys_c0014617 foreign key (user_id) references fn_user(user_id);