Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
add schema for data-records and data-uploads
<?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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.