aboutsummaryrefslogtreecommitdiffstats
path: root/ccsdk-app-common/db-scripts/oom-ddl-postgres-1707-common.sql
diff options
context:
space:
mode:
authorNicolas Hu <jh245g@att.com>2017-10-06 10:28:45 -0400
committerNicolas Hu <jh245g@att.com>2017-10-06 10:40:45 -0400
commitc19189718469498377b194eea2f9a247bb79c4f9 (patch)
tree4987d0fa9c60bb5f1c2938e37194fdcbb2c2fd5b /ccsdk-app-common/db-scripts/oom-ddl-postgres-1707-common.sql
parentd6c693de3070a6cd470356541324468d9fa42c59 (diff)
Refactor CCSDK/Dashboard project
Change-Id: I34451fbebbe7bbaaf1cd23aed5cef0f9a9ba37b7 Issues-Id: CCSDK-111 Signed-off-by: Nicolas Hu <jh245g@att.com>
Diffstat (limited to 'ccsdk-app-common/db-scripts/oom-ddl-postgres-1707-common.sql')
-rw-r--r--ccsdk-app-common/db-scripts/oom-ddl-postgres-1707-common.sql532
1 files changed, 532 insertions, 0 deletions
diff --git a/ccsdk-app-common/db-scripts/oom-ddl-postgres-1707-common.sql b/ccsdk-app-common/db-scripts/oom-ddl-postgres-1707-common.sql
new file mode 100644
index 0000000..c42eb0f
--- /dev/null
+++ b/ccsdk-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);