diff options
Diffstat (limited to 'oom-app-common/db-scripts/oom-ddl-postgres-1707-common.sql')
| -rw-r--r-- | oom-app-common/db-scripts/oom-ddl-postgres-1707-common.sql | 532 |
1 files changed, 532 insertions, 0 deletions
diff --git a/oom-app-common/db-scripts/oom-ddl-postgres-1707-common.sql b/oom-app-common/db-scripts/oom-ddl-postgres-1707-common.sql new file mode 100644 index 0000000..c42eb0f --- /dev/null +++ b/oom-app-common/db-scripts/oom-ddl-postgres-1707-common.sql @@ -0,0 +1,532 @@ +-- --------------------------------------------------------------------------------------------------------------- +-- This script creates tables for the ONAP Operations Manager Dashboard web app. +-- Same tables for both internal and external use. +-- ------------------------------------------------------------------------------------------------------------------ + +-- Assumes no schema name for maximum flexibility +-- CREATE SCHEMA (schema name); +-- SET SEARCH_PATH = (schema name); + +create table fn_lu_timezone ( + timezone_id serial primary key, + timezone_name character varying(100) not null, + timezone_value character varying(100) not null +); + +-- this sequence is named in Fusion.hbm.xml +create sequence seq_fn_user; +create table fn_user ( + user_id serial primary key, + org_id int, + manager_id int, + first_name character varying(25), + middle_name character varying(25), + last_name character varying(25), + phone character varying(25), + fax character varying(25), + cellular character varying(25), + email character varying(50), + address_id int, + alert_method_cd character varying(10), + hrid character varying(20), + org_user_id character varying(20), + org_code character varying(30), + login_id character varying(25), + login_pwd character varying(25), + last_login_date timestamp, + active_yn character varying(1) default 'y' not null, + created_id int, + created_date timestamp default now(), + modified_id int, + modified_date timestamp default now(), + is_internal_yn character(1) default 'n' not null, + address_line_1 character varying(100), + address_line_2 character varying(100), + city character varying(50), + state_cd character varying(3), + zip_code character varying(11), + country_cd character varying(3), + location_clli character varying(8), + org_manager_userid character varying(6), + company character varying(100), + department_name character varying(100), + job_title character varying(100), + timezone int, + department character varying(25), + business_unit character varying(25), + business_unit_name character varying(100), + cost_center character varying(25), + fin_loc_code character varying(10), + silo_status character varying(10) +); + +-- this sequence is named in Fusion.hbm.xml +create sequence seq_fn_role; +create table fn_role ( + role_id serial primary key, + role_name character varying(50) not null, + active_yn character varying(1) default 'y' not null, + priority numeric(4,0) +); + +create table fn_audit_action ( + audit_action_id integer primary key, + class_name character varying(500) not null, + method_name character varying(50) not null, + audit_action_cd character varying(20) not null, + audit_action_desc character varying(200), + active_yn character varying(1) +); + +create table fn_audit_action_log ( + audit_log_id serial primary key, + audit_action_cd character varying(200), + action_time timestamp, + user_id int, + class_name character varying(100), + method_name character varying(50), + success_msg character varying(20), + error_msg character varying(500) +); + +create table fn_lu_activity ( + activity_cd character varying(50) not null primary key, + activity character varying(50) not null +); + +-- this sequence is named in Fusion.hbm.xml +create sequence seq_fn_audit_log; +create table fn_audit_log ( + log_id serial primary key, + user_id int not null, + activity_cd character varying(50) not null, + audit_date timestamp default now() not null, + comments character varying(1000), + affected_record_id_bk character varying(500), + affected_record_id character varying(4000), + constraint fk_fn_audit_ref_209_fn_user foreign key (user_id) references fn_user(user_id) +); + +create table fn_datasource ( + id serial primary key, + name character varying(50), + driver_name character varying(256), + server character varying(256), + port integer, + user_name character varying(256), + password character varying(256), + url character varying(256), + min_pool_size integer, + max_pool_size integer, + adapter_id integer, + ds_type character varying(20) +); + +create table fn_function ( + function_cd character varying(30) not null primary key, + function_name character varying(50) not null +); + +create table fn_lu_alert_method ( + alert_method_cd character varying(10) not null, + alert_method character varying(50) not null +); + +create table fn_lu_broadcast_site ( + broadcast_site_cd character varying(50) not null, + broadcast_site_descr character varying(100) +); + +create table fn_lu_call_times ( + call_time_id int not null, + call_time_amount int not null, + call_time_display character varying(50) not null +); + +create table fn_lu_city ( + city_cd character varying(2) not null, + city character varying(100) not null, + state_cd character varying(2) not null, + primary key (city_cd, state_cd) +); + +create table fn_lu_country ( + country_cd character varying(3) not null primary key, + country character varying(100) not null, + full_name character varying(100), + webphone_country_label character varying(30) +); + +create table fn_lu_menu_set ( + menu_set_cd character varying(10) not null primary key, + menu_set_name character varying(50) not null +); + +create table fn_lu_priority ( + priority_id int not null, + priority character varying(50) not null, + active_yn character(1) not null, + sort_order numeric(5,0) +); + +create table fn_lu_role_type ( + role_type_id int not null, + role_type character varying(50) not null +); + +create table fn_lu_state ( + state_cd character varying(2) not null, + state character varying(100) not null +); + +create table fn_lu_tab_set ( + tab_set_cd character varying(30) not null, + tab_set_name character varying(50) not null +); + +-- this sequence is named in Fusion.hbm.xml +create sequence seq_fn_menu; +create table fn_menu ( + menu_id serial primary key, + label character varying(100), + parent_id int, + sort_order numeric(4,0), + action character varying(200), + function_cd character varying(30), + active_yn character varying(1) default 'y' not null, + servlet character varying(50), + query_string character varying(200), + external_url character varying(200), + target character varying(25), + menu_set_cd character varying(10) default 'app', + separator_yn character(1) default 'n', + image_src character varying(100), + constraint fk_fn_menu_ref_196_fn_menu foreign key (parent_id) references fn_menu(menu_id), + constraint fk_fn_menu_menu_set_cd foreign key (menu_set_cd) references fn_lu_menu_set(menu_set_cd), + constraint fk_fn_menu_ref_223_fn_funct foreign key (function_cd) references fn_function(function_cd) +); + +create table fn_org ( + org_id int not null, + org_name character varying(50) not null, + access_cd character varying(10) +); + +create table fn_restricted_url ( + restricted_url character varying(250) not null, + function_cd character varying(30) not null +); + +create table fn_role_composite ( + parent_role_id int not null, + child_role_id int not null, + constraint fk_fn_role_composite_child foreign key (child_role_id) references fn_role(role_id), + constraint fk_fn_role_composite_parent foreign key (parent_role_id) references fn_role(role_id) +); + +create table fn_role_function ( + role_id int not null, + function_cd character varying(30) not null, + constraint fk_fn_role__ref_198_fn_role foreign key (role_id) references fn_role(role_id) +); + +create table fn_tab ( + tab_cd character varying(30) not null, + tab_name character varying(50) not null, + tab_descr character varying(100), + action character varying(100) not null, + function_cd character varying(30) not null, + active_yn character(1) not null, + sort_order int not null, + parent_tab_cd character varying(30), + tab_set_cd character varying(30) +); + +create table fn_tab_selected ( + selected_tab_cd character varying(30) not null, + tab_uri character varying(40) not null +); + +create table fn_user_pseudo_role ( + pseudo_role_id int not null, + user_id int not null +); + +create table fn_user_role ( + user_id int not null, + role_id int not null, + priority numeric(4,0), + app_id int default 1, + constraint fk_fn_user__ref_172_fn_user foreign key (user_id) references fn_user(user_id), + constraint fk_fn_user__ref_175_fn_role foreign key (role_id) references fn_role(role_id) +); + +create table fn_xmltype ( + id int not null, + xml_document text +); + +create table schema_info ( + schema_id character varying(25) not null, + schema_desc character varying(75) not null, + datasource_type character varying(100), + connection_url varchar(200) not null, + user_name varchar(45) not null, + password varchar(45) null default null, + driver_class varchar(100) not null, + min_pool_size int not null, + max_pool_size int not null, + idle_connection_test_period int not null +); + +create table fn_app ( + app_id serial primary key, + app_name varchar(100) not null default '?', + app_image_url varchar(256) default null, + app_description varchar(512) default null, + app_notes varchar(4096) default null, + app_url varchar(256) default null, + app_alternate_url varchar(256) default null, + app_rest_endpoint varchar(2000) default null, + ml_app_name varchar(50) not null default '?', + ml_app_admin_id varchar(7) not null default '?', + mots_id int default null, + app_password varchar(256) not null default '?', + open char(1) default 'n', + enabled char(1) default 'y', + thumbnail bytea, + app_username varchar(50), + ueb_key varchar(256) default null, + ueb_secret varchar(256) default null, + ueb_topic_name varchar(256) default null +); + +create table fn_workflow ( + id serial primary key, + name varchar(20) NOT NULL unique, + description varchar(500) DEFAULT NULL, + run_link varchar(300) DEFAULT NULL, + suspend_link varchar(300) DEFAULT NULL, + modified_link varchar(300) DEFAULT NULL, + active_yn varchar(300) DEFAULT NULL, + created varchar(300) DEFAULT NULL, + created_by int DEFAULT NULL, + modified varchar(300) DEFAULT NULL, + modified_by int DEFAULT NULL, + workflow_key varchar(50) DEFAULT NULL +); + +create table fn_schedule_workflows ( + id_schedule_workflows serial primary key, + workflow_server_url varchar(45) default null, + workflow_key varchar(45) not null, + workflow_arguments varchar(45) default null, + startdatetimecron varchar(45) default null, + enddatetime timestamp default now(), + start_date_time timestamp default now(), + recurrence varchar(45) default null + ); + +create table fn_license ( + id int not null, + app_id int not null, + ip_address character varying(100) not null, + quantum_version_id int not null, + created_date timestamp default now(), + modified_date timestamp default now(), + created_id int, + modified_id int, + end_date timestamp default '2036-01-19 03:14:07' +); + +create table fn_license_app ( + id int not null, + app_name character varying(100) not null, + ctxt_name character varying(100) +); + +create table fn_license_contact ( + id int not null, + license_id integer, + sbcid character varying(20) +); + +create table fn_license_history ( + id int not null, + license_id int, + app_id int, + ip_address character varying(100), + quantum_version_id int, + created_date timestamp default now(), + modified_date timestamp default now(), + created_id int, + modified_id int +); + +create table fn_license_version ( + id int not null, + quantum_version character varying(25) +); + +create table fn_lu_message_location ( + message_location_id int primary key, + message_location_descr character varying(30) not null +); + +create table ecd_endpoint ( + user_id int not null primary key, + name character varying(64), + url character varying(512) +); + +alter table ecd_endpoint + add constraint fk_ecd_endpoint_ref_fn_user foreign key (user_id) references fn_user(user_id); + +create view v_url_access as + select distinct m.action as url, + m.function_cd + from fn_menu m + where (m.action is not null) +union + select distinct t.action as url, + t.function_cd + from fn_tab t + where (t.action is not null) +union + select r.restricted_url as url, + r.function_cd + from fn_restricted_url r; + +alter table fn_audit_log + add constraint fk_fn_audit_ref_205_fn_lu_ac foreign key (activity_cd) references fn_lu_activity(activity_cd); + +alter table fn_role_function + add constraint fk_fn_role__ref_201_fn_funct foreign key (function_cd) references fn_function(function_cd); + +alter table fn_lu_alert_method + add constraint fn_lu_alert_method_alert_method_cd primary key (alert_method_cd); + +alter table fn_lu_broadcast_site + add constraint fn_lu_broadcast_site_broadcast_site_cd primary key (broadcast_site_cd); + +alter table fn_lu_call_times + add constraint fn_lu_call_times_call_time_id primary key (call_time_id); + +alter table fn_lu_priority + add constraint fn_lu_priority_priority_id primary key (priority_id); + +alter table fn_lu_role_type + add constraint fn_lu_role_type_role_type_id primary key (role_type_id); + +alter table fn_lu_state + add constraint fn_lu_state_state_cd primary key (state_cd); + +alter table fn_lu_tab_set + add constraint fn_lu_tab_set_tab_set_cd primary key (tab_set_cd); + +alter table fn_org + add constraint fn_org_org_id primary key (org_id); + +alter table fn_restricted_url + add constraint fn_restricted_url_restricted_urlfunction_cd primary key (restricted_url, function_cd); + +alter table fn_role_composite + add constraint fn_role_composite_parent_role_idchild_role_id primary key (parent_role_id, child_role_id); + +alter table fn_role_function + add constraint fn_role_function_role_idfunction_cd primary key (role_id, function_cd); + +alter table fn_tab + add constraint fn_tab_tab_cd primary key (tab_cd); + +alter table fn_tab_selected + add constraint fn_tab_selected_selected_tab_cdtab_uri primary key (selected_tab_cd, tab_uri); + +alter table fn_user_pseudo_role + add constraint fn_user_pseudo_role_pseudo_role_iduser_id primary key (pseudo_role_id, user_id); + +alter table fn_user_role + add constraint fn_user_role_user_idrole_id primary key (user_id, role_id, app_id); + +alter table fn_license + add constraint fn_license_id primary key (id); + +alter table fn_license_contact + add constraint fn_license_contact_id primary key (id); + +alter table fn_license_history + add constraint fn_license_history_id primary key (id); + +alter table fn_license_version + add constraint fn_license_version_id primary key (id); + +create index fn_audit_log_activity_cd on fn_audit_log using btree(activity_cd); + +create index fn_audit_log_user_id on fn_audit_log using btree(user_id); + +create index fn_menu_function_cd on fn_menu using btree(function_cd); + +create index fn_org_access_cd on fn_org using btree(access_cd); + +create index fn_role_function_function_cd on fn_role_function using btree (function_cd); + +create index fn_role_function_role_id on fn_role_function using btree(role_id); + +create index fn_user_address_id on fn_user using btree(address_id); + +create index fn_user_alert_method_cd on fn_user using btree (alert_method_cd); + +create unique index fn_user_hrid on fn_user using btree (hrid); + +create unique index fn_user_login_id on fn_user using btree(login_id); + +create index fn_user_org_id on fn_user using btree(org_id); + +create index fn_user_role_role_id on fn_user_role using btree(role_id); + +create index fn_user_role_user_id on fn_user_role using btree(user_id); + +create unique index fn_xmltype_id on fn_xmltype using btree(id); + +create index fk_fn_user__ref_178_fn_app_IDX on fn_user_role using btree(app_id); + +create index fn_license_app_id on fn_license_app using btree(id); + +alter table fn_user_role + add constraint fk_fn_user__ref_178_fn_app foreign key (app_id) references fn_app(app_id); + +alter table fn_tab + add constraint fk_fn_tab_function_cd foreign key (function_cd) references fn_function(function_cd); + +alter table fn_tab_selected + add constraint fk_fn_tab_selected_tab_cd foreign key (selected_tab_cd) references fn_tab(tab_cd); + +alter table fn_tab + add constraint fk_fn_tab_set_cd foreign key (tab_set_cd) references fn_lu_tab_set(tab_set_cd); + +alter table fn_user + add constraint fk_fn_user_ref_110_fn_org foreign key (org_id) references fn_org(org_id); + +alter table fn_user + add constraint fk_fn_user_ref_123_fn_lu_al foreign key (alert_method_cd) references fn_lu_alert_method(alert_method_cd); + +alter table fn_user + add constraint fk_fn_user_ref_197_fn_user foreign key (manager_id) references fn_user(user_id); + +alter table fn_user + add constraint fk_fn_user_ref_198_fn_user foreign key (created_id) references fn_user(user_id); + +alter table fn_user + add constraint fk_fn_user_ref_199_fn_user foreign key (modified_id) references fn_user(user_id); + +alter table fn_user_pseudo_role + add constraint fk_pseudo_role_pseudo_role_id foreign key (pseudo_role_id) references fn_role(role_id); + +alter table fn_user_pseudo_role + add constraint fk_pseudo_role_user_id foreign key (user_id) references fn_user(user_id); + +alter table fn_restricted_url + add constraint fk_restricted_url_function_cd foreign key (function_cd) references fn_function(function_cd); + +alter table fn_license + add constraint fn_license_r02 foreign key (quantum_version_id) references fn_license_version(id); |