add schema for data-records and data-uploads

data-record-schema.xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <changeSet id="add tables for independent-blob schema"
        author="DWOOD">
    	<comment>Create tables for file-record and file-upload schema</comment>
        <createTable tableName="mrs_file_records" schemaName="mrsdba">
            <column name="file_record_id" type="serial">
               <constraints primaryKey="true" nullable="false"></constraints>
            </column>
            <column name="device_modality_detail_id" type="int" >
                <constraints references="mrs_device_modality_details(device_modality_detail_id)" foreignKeyName="mrs_file_records_device_modality_id_fk" nullable="false"></constraints>
            </column>
            <column name="usid" type="character varying" >
                <constraints references="mrs_subjects(usid)" foreignKeyName="mrs_file_records_usid_fk" nullable="false"></constraints>
            </column>
            <column name="segment_interval_id" type="int" ></column>
            <column name="attributes" type="jsonb" ></column>
        </createTable>
        <createTable tableName="mrs_file_upload_statuses" schemaName="mrsdba">
            <column name="file_upload_status_id" type="serial">
               <constraints primaryKey="true" nullable="false"></constraints>
            </column>
            <column name="label" type="character varying" ></column>
            <column name="description" type="character varying" ></column>
        </createTable>
        <createTable tableName="mrs_file_uploads" schemaName="mrsdba">
            <column name="file_upload_id" type="serial">
               <constraints primaryKey="true" nullable="false"></constraints>
            </column>
            <column name="file_record_id" type="int" ></column>
            <column name="username" type="character varying" ></column>
            <column name="start_time" type="timestamp without time zone" ></column>
            <column name="end_time" type="timestamp without time zone" ></column>
            <column name="file_upload_status_id" type="int" >
                <constraints references="mrs_file_upload_statuses(file_upload_status_id)" foreignKeyName="mrs_file_uploads_file_upload_status_id_fk" nullable="false"></constraints>
            </column>
            <column name="file_record_json" type="jsonb" ></column>
        </createTable>
        <addForeignKeyConstraint constraintName="mrs_file_records_segment_interval_id_fk"
            baseTableName="mrs_file_records" baseColumnNames="segment_interval_id"
            referencedTableName="mrs_study_intervals" referencedColumnNames="segment_interval_id"
        />
        <!-- constraints on jsonb data -->
        <sql>
            ALTER TABLE mrs_file_records ADD CONSTRAINT mrs_file_records_attr_acquisition_date CHECK (length(attributes->>'acquisitionDate') > 0  AND (attributes->>'acquisitionDate') IS NOT NULL );
            ALTER TABLE mrs_file_records ADD CONSTRAINT mrs_file_records_attr_filepath CHECK (length(attributes->>'filePath') > 0  AND (attributes->>'filePath') IS NOT NULL );
            ALTER TABLE mrs_file_records ADD CONSTRAINT mrs_file_records_attr_filecount CHECK ((attributes->>'fileCount')::integer >= 0 AND (attributes->>'fileCount') IS NOT NULL );
            ALTER TABLE mrs_file_records ADD CONSTRAINT mrs_file_records_attr_filesizebytes CHECK ((attributes->>'fileSizeBytes')::integer >= 0 AND (attributes->>'fileSizeBytes') IS NOT NULL );
            ALTER TABLE mrs_file_records ADD CONSTRAINT mrs_file_records_attr_checksum CHECK (length(attributes->>'checksum') > 0  AND (attributes->>'checksum') IS NOT NULL );
        </sql>
        <!-- Hist tables -->
        <createTable tableName="mrs_file_records_hist" schemaName="mrsdba">
            <column name="file_record_id" type="int"></column>
            <column name="device_modality_detail_id" type="int" ></column>
            <column name="usid" type="character varying" ></column>
            <column name="segment_interval_id" type="int" ></column>
            <column name="attributes" type="jsonb" ></column>
            <column name="mod_user" type="character varying" ></column>
            <column name="mod_date" type="timestamp without time zone" ></column>
        </createTable>
        <createTable tableName="mrs_file_upload_statuses_hist" schemaName="mrsdba">
            <column name="file_upload_status_id" type="int"></column>
            <column name="label" type="character varying"></column>
            <column name="description" type="character varying"></column>
            <column name="mod_user" type="character varying"></column>
            <column name="mod_date" type="timestamp without time zone" ></column>
        </createTable>
        <createTable tableName="mrs_file_uploads_hist" schemaName="mrsdba">
            <column name="file_upload_id" type="int"></column>
            <column name="file_record_id" type="int" ></column>
            <column name="username" type="character varying" ></column>
            <column name="start_time" type="timestamp without time zone" ></column>
            <column name="end_time" type="timestamp without time zone" ></column>
            <column name="file_upload_status_id" type="character varying" ></column>
            <column name="file_record_json" type="jsonb" ></column>
            <column name="mod_user" type="character varying" ></column>
            <column name="mod_date" type="timestamp without time zone" ></column>
        </createTable>
        <!-- seed data -->
        <sql>
            INSERT INTO mrs_file_upload_statuses (label, description)
            SELECT 'transferring', 'Uploading data to server'
            UNION SELECT 'cancelled', 'Cancelled by user'
            UNION SELECT 'completed', 'Successfully uploaded';

            INSERT INTO mrs_file_upload_statuses_hist (label, description, mod_user, mod_date)
            SELECT 'transferring', 'Uploading data to server', 'DWOOD', now()
            UNION SELECT 'cancelled', 'Cancelled by user', 'DWOOD', now()
            UNION SELECT 'completed', 'Successfully uploaded', 'DWOOD', now()
        </sql>
    	<rollback>
            <dropTable tableName="mrs_file_records" schemaName="mrsdba" />
            <dropTable tableName="mrs_file_uploads" schemaName="mrsdba" />
            <dropTable tableName="mrs_file_upload_statuses" schemaName="mrsdba" />
            <dropTable tableName="mrs_file_records_hist" schemaName="mrsdba" />
            <dropTable tableName="mrs_file_uploads_hist" schemaName="mrsdba" />
            <dropTable tableName="mrs_file_upload_statuses_hist" schemaName="mrsdba" />
    	</rollback>
    </changeSet>
</databaseChangeLog>