211 changes: 211 additions & 0 deletions t/data/oracle/schema-1.5.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,211 @@
CREATE TABLE t_category (
category_id number(11) NOT NULL,
display_name varchar2(256) NOT NULL,
description varchar2(4000) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (category_id),
CONSTRAINT t_category_display_name UNIQUE (display_name)
);


CREATE SEQUENCE sq_t_message_message_id;

CREATE TABLE t_message (
message_id number(11) NOT NULL,
alert_id number(45) NOT NULL,
from_address varchar2(256) NOT NULL,
recipient nvarchar2(64) NOT NULL,
subject_line varchar2(512) NOT NULL,
body_text clob NOT NULL,
body_html clob NOT NULL,
short_body varchar2(160) NOT NULL,
template_id number(11) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (message_id)
);


CREATE TABLE t_user (
user_id varchar2(32) NOT NULL,
name varchar2(256),
last4_pid varchar2(4) NOT NULL,
pidm number(11) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
mobile_phone varchar2(11),
mobile_phone_source varchar2(64),
reason_for_change varchar2(128),
im_id varchar2(512),
opt_in date,
opt_in_confirm date,
mobile_phone_2 varchar2(11),
PRIMARY KEY (user_id),
CONSTRAINT t_user_pidm UNIQUE (pidm)
);


CREATE SEQUENCE sq_t_population_group_group_;

CREATE TABLE t_population_group (
group_id number(11) NOT NULL,
group_name varchar2(256) NOT NULL,
group_description varchar2(256) NOT NULL,
group_role number(11),
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
group_type varchar2(256),
group_sql clob NOT NULL,
active number(1) NOT NULL,
source varchar2(256) NOT NULL,
private number(1) NOT NULL,
fpm_bldg_no varchar2(11) NOT NULL,
PRIMARY KEY (group_id)
);


CREATE SEQUENCE sq_t_role_role_id;

CREATE TABLE t_role (
role_id number(11) NOT NULL,
role_name varchar2(64) NOT NULL,
role_desc varchar2(128) NOT NULL,
PRIMARY KEY (role_id)
);


CREATE SEQUENCE sq_t_alert_alert_id;

CREATE TABLE t_alert (
alert_id number(11) NOT NULL,
category number(11) NOT NULL,
title varchar2(64) NOT NULL,
allow_email_opt_out number(1) NOT NULL,
enabled number(1) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (alert_id)
);


CREATE TABLE t_user_groups (
user_id varchar2(32) NOT NULL,
group_id number(11) NOT NULL,
PRIMARY KEY (user_id, group_id)
);


CREATE TABLE t_user_roles (
user_id varchar2(32) NOT NULL,
role_id number(11) NOT NULL,
PRIMARY KEY (user_id, role_id)
);


CREATE TABLE t_category_defaults (
category_id number(11) NOT NULL,
user_id varchar2(32) NOT NULL,
default_email number(1) NOT NULL,
default_sms number(1) NOT NULL,
default_push number(1) NOT NULL,
default_im number(1) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (category_id, user_id)
);


CREATE TABLE t_alert_roles (
alert_id number(11) NOT NULL,
role_id number(11) NOT NULL,
PRIMARY KEY (alert_id, role_id)
);

ALTER TABLE t_alert ADD CONSTRAINT t_alert_category_fk FOREIGN KEY (category) REFERENCES t_category (category_id);

ALTER TABLE t_user_groups ADD CONSTRAINT t_user_groups_group_id_fk FOREIGN KEY (group_id) REFERENCES t_population_group (group_id) ON DELETE CASCADE;

ALTER TABLE t_user_roles ADD CONSTRAINT t_user_roles_role_id_fk FOREIGN KEY (role_id) REFERENCES t_role (role_id) ON DELETE CASCADE;

ALTER TABLE t_category_defaults ADD CONSTRAINT t_category_defaults_category FOREIGN KEY (category_id) REFERENCES t_category (category_id);

ALTER TABLE t_category_defaults ADD CONSTRAINT t_category_defaults_user_id FOREIGN KEY (user_id) REFERENCES t_user (user_id);

ALTER TABLE t_alert_roles ADD CONSTRAINT t_alert_roles_alert_id_fk FOREIGN KEY (alert_id) REFERENCES t_alert (alert_id) ON DELETE CASCADE;

ALTER TABLE t_alert_roles ADD CONSTRAINT t_alert_roles_role_id_fk FOREIGN KEY (role_id) REFERENCES t_role (role_id);

ALTER TABLE t_population_group ADD CONSTRAINT t_population_group_group_role_fk FOREIGN KEY (group_role) REFERENCES t_role (role_id);

CREATE INDEX t_alert_idx_category on t_alert (category);

CREATE INDEX t_user_groups_idx_group_id on t_user_groups (group_id);

CREATE INDEX t_user_roles_idx_role_id on t_user_roles (role_id);

CREATE INDEX t_category_defaults_idx_cate on t_category_defaults (category_id);

CREATE INDEX t_category_defaults_idx_acce on t_category_defaults (user_id);

CREATE INDEX t_alert_roles_idx_alert_id on t_alert_roles (alert_id);

CREATE INDEX t_alert_roles_idx_role_id on t_alert_roles (role_id);

CREATE OR REPLACE TRIGGER ai_t_message_message_id
BEFORE INSERT ON t_message
FOR EACH ROW WHEN (
new.message_id IS NULL OR new.message_id = 0
)
BEGIN
SELECT sq_t_message_message_id.nextval
INTO :new.message_id
FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ai_t_population_group_group_
BEFORE INSERT ON t_population_group
FOR EACH ROW WHEN (
new.group_id IS NULL OR new.group_id = 0
)
BEGIN
SELECT sq_t_population_group_group_.nextval
INTO :new.group_id
FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ai_t_role_role_id
BEFORE INSERT ON t_role
FOR EACH ROW WHEN (
new.role_id IS NULL OR new.role_id = 0
)
BEGIN
SELECT sq_t_role_role_id.nextval
INTO :new.role_id
FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ai_t_alert_alert_id
BEFORE INSERT ON t_alert
FOR EACH ROW WHEN (
new.alert_id IS NULL OR new.alert_id = 0
)
BEGIN
SELECT sq_t_alert_alert_id.nextval
INTO :new.alert_id
FROM dual;
END;
/
207 changes: 207 additions & 0 deletions t/data/oracle/schema-1.6.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,207 @@
CREATE TABLE t_category (
category_id number(11) NOT NULL,
display_name varchar2(256) NOT NULL,
description varchar2(4000) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (category_id)
);

CREATE SEQUENCE sq_t_group_group_id;

CREATE TABLE t_group (
group_id number(11) NOT NULL,
group_name varchar2(256) NOT NULL,
group_description varchar2(256) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
group_type varchar2(256),
group_sql clob NOT NULL,
active number(1) NOT NULL,
source varchar2(256) NOT NULL,
private number(1) NOT NULL,
fpm_bldg_no varchar2(11) NOT NULL,
PRIMARY KEY (group_id)
);

CREATE SEQUENCE sq_t_message_message_id;

CREATE TABLE t_message (
message_id number(11) NOT NULL,
alert_id number(11) NOT NULL,
from_address varchar2(256) NOT NULL,
recipient varchar2(64) NOT NULL,
subject_line varchar2(512) NOT NULL,
body_text clob NOT NULL,
body_html clob NOT NULL,
short_body varchar2(160) NOT NULL,
template_id number(11) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (message_id)
);

CREATE SEQUENCE sq_t_role_role_id;

CREATE TABLE t_role (
role_id number(11) NOT NULL,
role_name varchar2(64) NOT NULL,
role_desc varchar2(128) NOT NULL,
PRIMARY KEY (role_id)
);

CREATE TABLE t_user (
user_id varchar2(32) NOT NULL,
name varchar2(256),
last4_pid varchar2(4) NOT NULL,
pidm number(11) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
mobile_phone varchar2(11),
mobile_phone_source varchar2(64),
reason_for_change varchar2(128),
im_id varchar2(512),
opt_in date,
opt_in_confirm date,
mobile_phone_2 varchar2(11),
PRIMARY KEY (user_id),
CONSTRAINT t_user_pidm UNIQUE (pidm)
);

CREATE SEQUENCE sq_t_alert_alert_id;

CREATE TABLE t_alert (
alert_id number(11) NOT NULL,
category number(11) NOT NULL,
title varchar2(64) NOT NULL,
allow_email_opt_out number(1) NOT NULL,
enabled number(1) NOT NULL,
added date DEFAULT CURRENT_TIMESTAMP NOT NULL,
added_by varchar2(32) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (alert_id)
);

CREATE TABLE t_category_defaults (
category_id number(11) NOT NULL,
user_id varchar2(32) NOT NULL,
default_email number(1) NOT NULL,
default_sms number(1) NOT NULL,
default_push number(1) NOT NULL,
default_im number(1) NOT NULL,
modified date,
modified_by varchar2(32),
PRIMARY KEY (category_id, user_id)
);

CREATE TABLE t_user_groups (
user_id varchar2(32) NOT NULL,
group_id number(11) NOT NULL,
PRIMARY KEY (user_id, group_id)
);

CREATE TABLE t_user_roles (
user_id varchar2(32) NOT NULL,
role_id number(11) NOT NULL,
PRIMARY KEY (user_id, role_id)
);

CREATE TABLE t_alert_roles (
alert_id number(11) NOT NULL,
role_id number(11) NOT NULL,
PRIMARY KEY (alert_id, role_id)
);

ALTER TABLE t_alert ADD CONSTRAINT t_alert_category_fk FOREIGN KEY (category) REFERENCES t_category (category_id);

ALTER TABLE t_category_defaults ADD CONSTRAINT t_category_defaults_category FOREIGN KEY (category_id) REFERENCES t_category (category_id);

ALTER TABLE t_category_defaults ADD CONSTRAINT t_category_defaults_user_id FOREIGN KEY (user_id) REFERENCES t_user (user_id);

ALTER TABLE t_user_groups ADD CONSTRAINT t_user_groups_group_id_fk FOREIGN KEY (group_id) REFERENCES t_group (group_id) ON DELETE CASCADE;

ALTER TABLE t_user_groups ADD CONSTRAINT t_user_groups_user_id_fk FOREIGN KEY (user_id) REFERENCES t_user (user_id) ON DELETE CASCADE;

ALTER TABLE t_user_roles ADD CONSTRAINT t_user_roles_role_id_fk FOREIGN KEY (role_id) REFERENCES t_role (role_id) ON DELETE CASCADE;

ALTER TABLE t_user_roles ADD CONSTRAINT t_user_roles_user_id_fk FOREIGN KEY (user_id) REFERENCES t_user (user_id) ON DELETE CASCADE;

ALTER TABLE t_alert_roles ADD CONSTRAINT t_alert_roles_alert_id_fk FOREIGN KEY (alert_id) REFERENCES t_alert (alert_id) ON DELETE CASCADE;

ALTER TABLE t_alert_roles ADD CONSTRAINT t_alert_roles_role_id_fk FOREIGN KEY (role_id) REFERENCES t_role (role_id);

CREATE INDEX t_alert_idx_category on t_alert (category);

CREATE INDEX t_category_defaults_idx_cate on t_category_defaults (category_id);

CREATE INDEX t_category_defaults_idx_acce on t_category_defaults (user_id);

CREATE INDEX t_user_groups_idx_group_id on t_user_groups (group_id);

CREATE INDEX t_user_groups_idx_user_id on t_user_groups (user_id);

CREATE INDEX t_user_roles_idx_role_id on t_user_roles (role_id);

CREATE INDEX t_user_roles_idx_user_id on t_user_roles (user_id);

CREATE INDEX t_alert_roles_idx_alert_id on t_alert_roles (alert_id);

CREATE INDEX t_alert_roles_idx_role_id on t_alert_roles (role_id);

CREATE OR REPLACE TRIGGER ai_t_group_group_id
BEFORE INSERT ON t_group
FOR EACH ROW WHEN (
new.group_id IS NULL OR new.group_id = 0
)
BEGIN
SELECT sq_t_group_group_id.nextval
INTO :new.group_id
FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ai_t_message_message_id
BEFORE INSERT ON t_message
FOR EACH ROW WHEN (
new.message_id IS NULL OR new.message_id = 0
)
BEGIN
SELECT sq_t_message_message_id.nextval
INTO :new.message_id
FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ai_t_role_role_id
BEFORE INSERT ON t_role
FOR EACH ROW WHEN (
new.role_id IS NULL OR new.role_id = 0
)
BEGIN
SELECT sq_t_role_role_id.nextval
INTO :new.role_id
FROM dual;
END;
/

CREATE OR REPLACE TRIGGER ai_t_alert_alert_id
BEFORE INSERT ON t_alert
FOR EACH ROW WHEN (
new.alert_id IS NULL OR new.alert_id = 0
)
BEGIN
SELECT sq_t_alert_alert_id.nextval
INTO :new.alert_id
FROM dual;
END;
/