sql-rapback-datastore.h2-schema.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of h2-mock-database Show documentation
There is a newer version: 2.0.7
/*
 * Unless explicitly acquired and licensed from Licensor under another license, the contents of
 * this file are subject to the Reciprocal Public License ("RPL") Version 1.5, or subsequent
 * versions as allowed by the RPL, and You may not copy or use this file in either source code
 * or executable form, except in compliance with the terms and conditions of the RPL
 *
 * All software distributed under the RPL is provided strictly on an "AS IS" basis, WITHOUT
 * WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, AND LICENSOR HEREBY DISCLAIMS ALL SUCH
 * WARRANTIES, INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
 * PARTICULAR PURPOSE, QUIET ENJOYMENT, OR NON-INFRINGEMENT. See the RPL for specific language
 * governing rights and limitations under the RPL.
 *
 * http://opensource.org/licenses/RPL-1.5
 *
 * Copyright 2012-2015 Open Justice Broker Consortium
 */
drop schema if exists rapback_datastore;
CREATE schema rapback_datastore;
use rapback_datastore;

CREATE TABLE IDENTIFICATION_CATEGORY (IDENTIFICATION_CATEGORY_ID INT AUTO_INCREMENT NOT NULL, IDENTIFICATION_CATEGORY_CODE VARCHAR(10) NOT NULL, IDENTIFICATION_CATEGORY_DESCRIPTION VARCHAR(100) NOT NULL, IDENTIFICATION_CATEGORY_TYPE VARCHAR(10) NOT NULL);

ALTER TABLE IDENTIFICATION_CATEGORY ADD CONSTRAINT identification_category_id PRIMARY KEY (IDENTIFICATION_CATEGORY_ID);

CREATE SEQUENCE IDENTIFICATION_CATEGORY_IDENTIFICATION_CATEGORY_ID_seq;

CREATE TABLE AGENCY_PROFILE (AGENCY_ID INT AUTO_INCREMENT NOT NULL, AGENCY_ORI VARCHAR(20) NOT NULL, AGENCY_NAME VARCHAR(100) NOT NULL, FBI_SUBSCRIPTION_QUALIFICATION BOOLEAN DEFAULT FALSE NOT NULL);

ALTER TABLE AGENCY_PROFILE ADD CONSTRAINT agency_id PRIMARY KEY (AGENCY_ID);

CREATE SEQUENCE AGENCY_PROFILE_AGENCY_ID_seq;

CREATE TABLE DEPARTMENT (DEPARTMENT_ID INT AUTO_INCREMENT NOT NULL, DEPARTMENT_NAME VARCHAR(50) NOT NULL, AGENCY_ID INT NOT NULL);

ALTER TABLE DEPARTMENT ADD CONSTRAINT department_id PRIMARY KEY (DEPARTMENT_ID);

CREATE SEQUENCE DEPARTMENT_DEPARTMENT_ID_seq;

CREATE TABLE JOB_TITLE (JOB_TITLE_ID BIGINT AUTO_INCREMENT NOT NULL, DEPARTMENT_ID INT NOT NULL, TITLE_DESCRIPTION VARCHAR(50) NOT NULL);

ALTER TABLE JOB_TITLE ADD CONSTRAINT job_title_id PRIMARY KEY (JOB_TITLE_ID);

CREATE SEQUENCE JOB_TITLE_JOB_TITLE_ID_seq;

CREATE TABLE OJBC_USER (OJBC_USER_ID BIGINT AUTO_INCREMENT NOT NULL, JOB_TITLE_ID BIGINT NOT NULL, FEDERATION_ID VARCHAR(100) NOT NULL);

ALTER TABLE OJBC_USER ADD CONSTRAINT ojbc_user_id PRIMARY KEY (OJBC_USER_ID);

CREATE SEQUENCE OJBC_USER_OJBC_USER_ID_seq;

CREATE TABLE JOB_TITLE_PRIVILEGE (JOB_TITLE_PRIVILEGE_ID INT AUTO_INCREMENT NOT NULL, JOB_TITLE_ID BIGINT NOT NULL, IDENTIFICATION_CATEGORY_ID INT NOT NULL);

ALTER TABLE JOB_TITLE_PRIVILEGE ADD CONSTRAINT job_title_privilege_id PRIMARY KEY (JOB_TITLE_PRIVILEGE_ID);

CREATE SEQUENCE JOB_TITLE_PRIVILEGE_JOB_TITLE_PRIVILEGE_ID_seq;

CREATE TABLE AGENCY_CONTACT_EMAIL (AGENCY_CONTACT_EMAIL_ID INT AUTO_INCREMENT NOT NULL, AGENCY_ID INT NOT NULL, AGENCY_EMAIL VARCHAR(50) NOT NULL);

CREATE SEQUENCE AGENCY_CONTACT_EMAIL_AGENCY_CONTACT_EMAIL_ID_seq;

CREATE TABLE SUBSCRIPTION_CATEGORY (SUBSCRIPTION_CATEGORY_CODE VARCHAR(2) NOT NULL, SUBSCRIPTION_CATEGORY_DESCRIPTION VARCHAR(100) NOT NULL);

ALTER TABLE SUBSCRIPTION_CATEGORY ADD CONSTRAINT subscription_category_code PRIMARY KEY (SUBSCRIPTION_CATEGORY_CODE);

CREATE TABLE RAP_BACK_SUBSCRIPTION_TERM (RAP_BACK_SUBSCRIPTION_TERM_CODE VARCHAR(1) NOT NULL, RAP_BACK_SUBSCRIPTION_TERM_DESC VARCHAR(50) NOT NULL);

ALTER TABLE RAP_BACK_SUBSCRIPTION_TERM ADD CONSTRAINT rap_back_subscription_term_code PRIMARY KEY (RAP_BACK_SUBSCRIPTION_TERM_CODE);

CREATE TABLE RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT (RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT_CODE VARCHAR(1) NOT NULL, RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT_DESC VARCHAR(100) NOT NULL);

ALTER TABLE RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT ADD CONSTRAINT rap_back_activity_notification_format_code PRIMARY KEY (RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT_CODE);

CREATE TABLE RAP_BACK_CATEGORY (RAP_BACK_CATEGORY_CODE VARCHAR(2) NOT NULL, RAP_BACK_CATEGORY_DESCRIPTION VARCHAR(100) NOT NULL);

ALTER TABLE RAP_BACK_CATEGORY ADD CONSTRAINT rap_back_category_code PRIMARY KEY (RAP_BACK_CATEGORY_CODE);

CREATE TABLE FINGER_PRINTS_TYPE (FINGER_PRINTS_TYPE_ID INT AUTO_INCREMENT NOT NULL, FINGER_PRINTS_TYPE VARCHAR(30) NOT NULL);

ALTER TABLE FINGER_PRINTS_TYPE ADD CONSTRAINT finger_prints_type_pk PRIMARY KEY (FINGER_PRINTS_TYPE_ID);

CREATE SEQUENCE FINGER_PRINTS_TYPE_FINGER_PRINTS_TYPE_ID_seq_1;

CREATE TABLE RESULTS_SENDER (RESULTS_SENDER_ID INT AUTO_INCREMENT NOT NULL, RESULTS_SENDER VARCHAR(20) NOT NULL);

ALTER TABLE RESULTS_SENDER ADD CONSTRAINT results_sender_pk PRIMARY KEY (RESULTS_SENDER_ID);

CREATE SEQUENCE RESULTS_SENDER_RESULTS_SENDER_ID_seq_2_1;

CREATE TABLE SUBSEQUENT_RESULTS (SUBSEQUENT_RESULT_ID INT AUTO_INCREMENT NOT NULL, RAP_SHEET BLOB NOT NULL, UCN VARCHAR(100) NOT NULL, RESULTS_SENDER_ID INT NOT NULL, REPORT_TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

ALTER TABLE SUBSEQUENT_RESULTS ADD CONSTRAINT subsequent_results_pk PRIMARY KEY (SUBSEQUENT_RESULT_ID);

CREATE SEQUENCE SUBSEQUENT_RESULTS_SUBSEQUENT_RESULT_ID_seq;

CREATE TABLE IDENTIFICATION_SUBJECT (SUBJECT_ID INT AUTO_INCREMENT NOT NULL, UCN VARCHAR(100), CRIMINAL_SID VARCHAR(50), CIVIL_SID VARCHAR(50), FIRST_NAME VARCHAR(100) NOT NULL, LAST_NAME VARCHAR(100) NOT NULL, MIDDLE_INITIAL VARCHAR(30), SEX_CODE VARCHAR(1), DOB date);

ALTER TABLE IDENTIFICATION_SUBJECT ADD CONSTRAINT identification_subject_pk PRIMARY KEY (SUBJECT_ID);

CREATE SEQUENCE IDENTIFICATION_SUBJECT_SUBJECT_ID_seq;

CREATE TABLE FBI_RAP_BACK_SUBSCRIPTION (FBI_SUBSCRIPTION_ID VARCHAR(100) NOT NULL, RAP_BACK_EXPIRATION_DATE date NOT NULL, RAP_BACK_START_DATE date NOT NULL, RAP_BACK_TERM_DATE date, RAP_BACK_OPT_OUT_IN_STATE_INDICATOR BOOLEAN NOT NULL, UCN VARCHAR(100) NOT NULL, RAP_BACK_CATEGORY_CODE VARCHAR(2) NOT NULL, RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT_CODE VARCHAR(1) NOT NULL, RAP_BACK_SUBSCRIPTION_TERM_CODE VARCHAR(1) NOT NULL, REPORT_TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

ALTER TABLE FBI_RAP_BACK_SUBSCRIPTION ADD CONSTRAINT fbi_rap_back_subscription_pk PRIMARY KEY (FBI_SUBSCRIPTION_ID);

CREATE TABLE SUBSCRIPTION (ID INT AUTO_INCREMENT NOT NULL, TOPIC VARCHAR(100) NOT NULL, STARTDATE date NOT NULL, ENDDATE date DEFAULT null, LASTVALIDATIONDATE date DEFAULT null, SUBSCRIBINGSYSTEMIDENTIFIER VARCHAR(100) NOT NULL, SUBSCRIPTIONOWNER VARCHAR(100) NOT NULL, SUBJECTNAME VARCHAR(100) NOT NULL, AGENCY_CASE_NUMBER VARCHAR(100), ACTIVE TINYINT NOT NULL, SUBSCRIPTION_CATEGORY_CODE VARCHAR(2), TIMESTAMP TIMESTAMP DEFAULT NOW());

ALTER TABLE SUBSCRIPTION ADD CONSTRAINT subscription_pk PRIMARY KEY (ID);

CREATE UNIQUE INDEX constraint_index_9e ON SUBSCRIPTION(ID);

CREATE TABLE IDENTIFICATION_TRANSACTION (TRANSACTION_NUMBER VARCHAR(100) NOT NULL, SUBJECT_ID INT NOT NULL, OTN VARCHAR(100) NOT NULL, OWNER_ORI VARCHAR(20) NOT NULL, OWNER_PROGRAM_OCA VARCHAR(50) NOT NULL, IDENTIFICATION_CATEGORY VARCHAR(20) NOT NULL, ARCHIVED BOOLEAN DEFAULT FALSE NOT NULL, SUBSCRIPTION_ID INT, AVAILABLE_FOR_SUBSCRIPTION_START_DATE TIMESTAMP DEFAULT NOW() NOT NULL, REPORT_TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

ALTER TABLE IDENTIFICATION_TRANSACTION ADD CONSTRAINT identification_transaction_pk PRIMARY KEY (TRANSACTION_NUMBER);

CREATE TABLE CIVIL_FINGER_PRINTS (FINGER_PRINTS_ID INT AUTO_INCREMENT NOT NULL, TRANSACTION_NUMBER VARCHAR(100) NOT NULL, FINGER_PRINTS_FILE BLOB NOT NULL, FINGER_PRINTS_TYPE_ID INT NOT NULL, REPORT_TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

ALTER TABLE CIVIL_FINGER_PRINTS ADD CONSTRAINT civil_finger_prints_pk PRIMARY KEY (FINGER_PRINTS_ID);

CREATE SEQUENCE CIVIL_FINGER_PRINTS_FINGER_PRINTS_ID_seq;

CREATE TABLE CIVIL_INITIAL_RESULTS (CIVIL_INITIAL_RESULT_ID INT AUTO_INCREMENT NOT NULL, SEARCH_RESULT_FILE BLOB, TRANSACTION_NUMBER VARCHAR(100) NOT NULL, RESULTS_SENDER_ID INT NOT NULL, REPORT_TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

ALTER TABLE CIVIL_INITIAL_RESULTS ADD CONSTRAINT civil_initial_results_pk PRIMARY KEY (CIVIL_INITIAL_RESULT_ID);

CREATE SEQUENCE CIVIL_INITIAL_RESULTS_CIVIL_INITIAL_RESULT_ID_seq;

CREATE TABLE CIVIL_INITIAL_RAP_SHEET (CIVIL_RAPSHEET_ID INT AUTO_INCREMENT NOT NULL, CIVIL_INITIAL_RESULT_ID INT NOT NULL, RAP_SHEET BLOB NOT NULL, TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

CREATE SEQUENCE CIVIL_INITIAL_RAP_SHEET_CIVIL_RAPSHEET_ID_seq;

CREATE TABLE CRIMINAL_INITIAL_RESULTS (CRIMINAL_INITIAL_RESULT_ID INT AUTO_INCREMENT NOT NULL, TRANSACTION_NUMBER VARCHAR(100) NOT NULL, SEARCH_RESULT_FILE BLOB, RESULTS_SENDER_ID INT NOT NULL, REPORT_TIMESTAMP TIMESTAMP DEFAULT NOW() NOT NULL);

ALTER TABLE CRIMINAL_INITIAL_RESULTS ADD CONSTRAINT criminal_initial_results_pk PRIMARY KEY (CRIMINAL_INITIAL_RESULT_ID);

CREATE SEQUENCE CRIMINAL_INITIAL_RESULTS_CRIMINAL_INITIAL_RESULT_ID_seq;

CREATE TABLE SUBSCRIPTION_SUBJECT_IDENTIFIER (ID INT AUTO_INCREMENT NOT NULL, SUBSCRIPTIONID INT NOT NULL, IDENTIFIERNAME VARCHAR(100) NOT NULL, IDENTIFIERVALUE VARCHAR(256) NOT NULL);

ALTER TABLE SUBSCRIPTION_SUBJECT_IDENTIFIER ADD CONSTRAINT subscription_subject_identifier_pk PRIMARY KEY (ID);

CREATE INDEX constraint_index_b ON SUBSCRIPTION_SUBJECT_IDENTIFIER(SUBSCRIPTIONID);

CREATE TABLE NOTIFICATION_MECHANISM (ID INT AUTO_INCREMENT NOT NULL, SUBSCRIPTIONID INT NOT NULL, NOTIFICATIONMECHANISMTYPE VARCHAR(20) NOT NULL, NOTIFICATIONADDRESS VARCHAR(256) NOT NULL);

ALTER TABLE NOTIFICATION_MECHANISM ADD CONSTRAINT notification_mechanism_pk PRIMARY KEY (ID);

CREATE UNIQUE INDEX constraint_index_9 ON NOTIFICATION_MECHANISM(SUBSCRIPTIONID, NOTIFICATIONADDRESS);

CREATE INDEX constraint_index_94 ON NOTIFICATION_MECHANISM(SUBSCRIPTIONID);

ALTER TABLE JOB_TITLE_PRIVILEGE ADD CONSTRAINT identification_category_job_title_privilege_fk FOREIGN KEY (IDENTIFICATION_CATEGORY_ID) REFERENCES IDENTIFICATION_CATEGORY (IDENTIFICATION_CATEGORY_ID);

ALTER TABLE AGENCY_CONTACT_EMAIL ADD CONSTRAINT agency_profile_agency_contact_email_fk FOREIGN KEY (AGENCY_ID) REFERENCES AGENCY_PROFILE (AGENCY_ID);

ALTER TABLE DEPARTMENT ADD CONSTRAINT agency_profile_department_fk FOREIGN KEY (AGENCY_ID) REFERENCES AGENCY_PROFILE (AGENCY_ID);

ALTER TABLE JOB_TITLE ADD CONSTRAINT department_title_fk FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT (DEPARTMENT_ID);

ALTER TABLE JOB_TITLE_PRIVILEGE ADD CONSTRAINT job_title_job_title_privilege_fk FOREIGN KEY (JOB_TITLE_ID) REFERENCES JOB_TITLE (JOB_TITLE_ID);

ALTER TABLE OJBC_USER ADD CONSTRAINT job_title_ojbc_user_fk FOREIGN KEY (JOB_TITLE_ID) REFERENCES JOB_TITLE (JOB_TITLE_ID);

ALTER TABLE SUBSCRIPTION ADD CONSTRAINT subscription_category_subscription_fk FOREIGN KEY (SUBSCRIPTION_CATEGORY_CODE) REFERENCES SUBSCRIPTION_CATEGORY (SUBSCRIPTION_CATEGORY_CODE);

ALTER TABLE FBI_RAP_BACK_SUBSCRIPTION ADD CONSTRAINT rap_back_subscription_term_fbi_rap_back_subscription_fk FOREIGN KEY (RAP_BACK_SUBSCRIPTION_TERM_CODE) REFERENCES RAP_BACK_SUBSCRIPTION_TERM (RAP_BACK_SUBSCRIPTION_TERM_CODE);

ALTER TABLE FBI_RAP_BACK_SUBSCRIPTION ADD CONSTRAINT rap_back_activity_not_format_fbi_rap_back_sub_fk FOREIGN KEY (RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT_CODE) REFERENCES RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT (RAP_BACK_ACTIVITY_NOTIFICATION_FORMAT_CODE);

ALTER TABLE FBI_RAP_BACK_SUBSCRIPTION ADD CONSTRAINT rap_back_category_code_fbi_rap_back_subscription_fk FOREIGN KEY (RAP_BACK_CATEGORY_CODE) REFERENCES RAP_BACK_CATEGORY (RAP_BACK_CATEGORY_CODE);

ALTER TABLE CIVIL_FINGER_PRINTS ADD CONSTRAINT finger_prints_type_civil_finger_prints_fk FOREIGN KEY (FINGER_PRINTS_TYPE_ID) REFERENCES FINGER_PRINTS_TYPE (FINGER_PRINTS_TYPE_ID);

ALTER TABLE CIVIL_INITIAL_RESULTS ADD CONSTRAINT results_sender_civil_initial_results_fk FOREIGN KEY (RESULTS_SENDER_ID) REFERENCES RESULTS_SENDER (RESULTS_SENDER_ID);

ALTER TABLE CRIMINAL_INITIAL_RESULTS ADD CONSTRAINT results_sender_criminal_initial_results_fk FOREIGN KEY (RESULTS_SENDER_ID) REFERENCES RESULTS_SENDER (RESULTS_SENDER_ID);

ALTER TABLE SUBSEQUENT_RESULTS ADD CONSTRAINT results_sender_subsequent_results_fk FOREIGN KEY (RESULTS_SENDER_ID) REFERENCES RESULTS_SENDER (RESULTS_SENDER_ID);

ALTER TABLE IDENTIFICATION_TRANSACTION ADD CONSTRAINT fbi_rap_back_subject_identification_transaction_fk FOREIGN KEY (SUBJECT_ID) REFERENCES IDENTIFICATION_SUBJECT (SUBJECT_ID) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE NOTIFICATION_MECHANISM ADD CONSTRAINT constraint_94 FOREIGN KEY (SUBSCRIPTIONID) REFERENCES SUBSCRIPTION (ID) ON UPDATE RESTRICT ON DELETE CASCADE;

ALTER TABLE SUBSCRIPTION_SUBJECT_IDENTIFIER ADD CONSTRAINT constraint_b FOREIGN KEY (SUBSCRIPTIONID) REFERENCES SUBSCRIPTION (ID) ON UPDATE RESTRICT ON DELETE CASCADE;

ALTER TABLE IDENTIFICATION_TRANSACTION ADD CONSTRAINT subscription_identification_transaction_fk FOREIGN KEY (SUBSCRIPTION_ID) REFERENCES SUBSCRIPTION (ID);

ALTER TABLE CRIMINAL_INITIAL_RESULTS ADD CONSTRAINT identification_transaction_criminal_initial_results_fk FOREIGN KEY (TRANSACTION_NUMBER) REFERENCES IDENTIFICATION_TRANSACTION (TRANSACTION_NUMBER);

ALTER TABLE CIVIL_INITIAL_RESULTS ADD CONSTRAINT identification_transaction_civil_initial_results_fk FOREIGN KEY (TRANSACTION_NUMBER) REFERENCES IDENTIFICATION_TRANSACTION (TRANSACTION_NUMBER);

ALTER TABLE CIVIL_FINGER_PRINTS ADD CONSTRAINT identification_transaction_civil_nists_fk FOREIGN KEY (TRANSACTION_NUMBER) REFERENCES IDENTIFICATION_TRANSACTION (TRANSACTION_NUMBER);

ALTER TABLE CIVIL_INITIAL_RAP_SHEET ADD CONSTRAINT civil_initial_results_civil_initial_rap_sheet_fk FOREIGN KEY (CIVIL_INITIAL_RESULT_ID) REFERENCES CIVIL_INITIAL_RESULTS (CIVIL_INITIAL_RESULT_ID);