<commit_before><!--
  ~ Waltz - Enterprise Architecture
  ~ Copyright (C) 2016, 2017, 2018, 2019 Waltz open source project
  ~ See README.md for more information
  ~
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~     http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific
  ~
  -->

<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"
                   logicalFilePath="db.changelog-1.23.xml">

    <!-- 3192: survey_question_list_response -->
    <changeSet id="20200205-3192-1"
               author="rovats">
        <comment>3192: survey question multi-select response table</comment>
        <createTable tableName="survey_question_list_response">
            <column name="survey_instance_id"
                    type="${id.type}">
                <constraints nullable="false"/>
            </column>
            <column name="question_id"
                    type="${id.type}">
                <constraints nullable="false"/>
            </column>
            <column name="response"
                    type="${maxvarchar.type}">
                <constraints nullable="false"/>
            </column>
            <column name="position"
                    type="${int.type}"
                    defaultValueNumeric="0">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="20200205-3192-2"
               author="rovats">
        <comment>3192: survey question multi-select response table fk</comment>
        <addForeignKeyConstraint baseTableName="survey_question_list_response"
                                 baseColumnNames="survey_instance_id,question_id"
                                 constraintName="survey_q_list_resp_survey_q_resp_fk"
                                 referencedTableName="survey_question_response"
                                 referencedColumnNames="survey_instance_id,question_id"
                                 onDelete="CASCADE"/>
    </changeSet>

    <changeSet id="20200205-3192-3"
               author="rovats">
        <comment>3192: survey question response list_response_concat column</comment>
        <addColumn tableName="survey_question_response">
            <column name="list_response_concat"
                    type="${maxvarchar.type}">
                <constraints nullable="true"/>
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="20200224-4719-1"
               author="alimuza">
        <comment>4719: Assessment rating rating scheme item foreign key</comment>
        <addForeignKeyConstraint baseTableName="assessment_rating"
                                 baseColumnNames="rating_id"
                                 constraintName="assessment_rating_rating_scheme_item_fk"
                                 referencedTableName="rating_scheme_item"
                                 referencedColumnNames="id"/>
    </changeSet>

    <changeSet id="20200224-4719-2"
               author="alimuza">
        <comment>4719: Change string note text column to nvarchar(max) to allow string larger than 4000 characters</comment>
        <modifyDataType columnName="note_text"
                        newDataType="${maxvarchar.type}"
                        tableName="entity_named_note"/>
    </changeSet>

    <changeSet id="20200304-4734-1"
               author="woodjes">
        <comment>4734: Surveys: Add owning role to allow multiple survey owners</comment>
        <addColumn tableName="survey_instance">
            <column name="owning_role"
                    type="${role.type}">
                <constraints nullable="true"></constraints>
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="20200304-4734-2"
               author="woodjes">
        <comment>4734: Surveys: Add owning role to allow multiple survey owners</comment>
        <addColumn tableName="survey_instance">
            <column name="owner_id"
                    type="${id.type}">
                <constraints nullable="true"></constraints>
            </column>
        </addColumn>
    </changeSet>
    <changeSet id="20200224-4712-1"
               author="kamransaleem">
        <dropPrimaryKey tableName="logical_flow_decorator"
                        constraintName="data_flow_decorator_pkey" />
    </changeSet>

    <changeSet id="20200224-4712-2"
               author="kamransaleem">
        <createIndex indexName="idx_lfd_lfid_deckind_decid"
                     tableName="logical_flow_decorator"
                     unique="true">
            <column name="logical_flow_id" />
            <column name="decorator_entity_kind" />
            <column name="decorator_entity_id" />
        </createIndex>
    </changeSet>

    <changeSet id="20200224-4712-3"
               author="kamransaleem">
        <addColumn tableName="logical_flow_decorator">
            <column name="id"
                    type="${id.type}"
                    autoIncrement="true">
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="20200224-4712-4"
               author="kamransaleem">
        <addPrimaryKey tableName="logical_flow_decorator"
                       constraintName="logical_flow_decorator_pkey"
                       columnNames="id" />
    </changeSet>

    <!-- 3678 - Remove drill grid -->
    <changeSet id="20200311-3678-1"
               author="woodjes">
        <dropTable tableName="drill_grid_definition"/>
    </changeSet>

    <!-- 4769 - Add rating_editor_role to measurable category -->
    <changeSet id="20200312-4769-1"
               author="davidwatkins73"><commit_msg>MySql / MariaDB: cannot complete 1.23 scripts<commit_after><!--
  ~ Waltz - Enterprise Architecture
  ~ Copyright (C) 2016, 2017, 2018, 2019 Waltz open source project
  ~ See README.md for more information
  ~
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~     http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific
  ~
  -->

<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"
                   logicalFilePath="db.changelog-1.23.xml">

    <!-- 3192: survey_question_list_response -->
    <changeSet id="20200205-3192-1"
               author="rovats">
        <comment>3192: survey question multi-select response table</comment>
        <createTable tableName="survey_question_list_response">
            <column name="survey_instance_id"
                    type="${id.type}">
                <constraints nullable="false"/>
            </column>
            <column name="question_id"
                    type="${id.type}">
                <constraints nullable="false"/>
            </column>
            <column name="response"
                    type="${maxvarchar.type}">
                <constraints nullable="false"/>
            </column>
            <column name="position"
                    type="${int.type}"
                    defaultValueNumeric="0">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="20200205-3192-2"
               author="rovats">
        <comment>3192: survey question multi-select response table fk</comment>
        <addForeignKeyConstraint baseTableName="survey_question_list_response"
                                 baseColumnNames="survey_instance_id,question_id"
                                 constraintName="survey_q_list_resp_survey_q_resp_fk"
                                 referencedTableName="survey_question_response"
                                 referencedColumnNames="survey_instance_id,question_id"
                                 onDelete="CASCADE"/>
    </changeSet>

    <changeSet id="20200205-3192-3"
               author="rovats">
        <comment>3192: survey question response list_response_concat column</comment>
        <addColumn tableName="survey_question_response">
            <column name="list_response_concat"
                    type="${maxvarchar.type}">
                <constraints nullable="true"/>
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="20200224-4719-1"
               author="alimuza">
        <comment>4719: Assessment rating rating scheme item foreign key</comment>
        <addForeignKeyConstraint baseTableName="assessment_rating"
                                 baseColumnNames="rating_id"
                                 constraintName="assessment_rating_rating_scheme_item_fk"
                                 referencedTableName="rating_scheme_item"
                                 referencedColumnNames="id"/>
    </changeSet>

    <changeSet id="20200224-4719-2"
               author="alimuza">
        <comment>4719: Change string note text column to nvarchar(max) to allow string larger than 4000 characters</comment>
        <modifyDataType columnName="note_text"
                        newDataType="${maxvarchar.type}"
                        tableName="entity_named_note"/>
    </changeSet>

    <changeSet id="20200304-4734-1"
               author="woodjes">
        <comment>4734: Surveys: Add owning role to allow multiple survey owners</comment>
        <addColumn tableName="survey_instance">
            <column name="owning_role"
                    type="${role.type}">
                <constraints nullable="true"></constraints>
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="20200304-4734-2"
               author="woodjes">
        <comment>4734: Surveys: Add owning role to allow multiple survey owners</comment>
        <addColumn tableName="survey_instance">
            <column name="owner_id"
                    type="${id.type}">
                <constraints nullable="true"></constraints>
            </column>
        </addColumn>
    </changeSet>


    <changeSet id="20200224-4712-1"
               author="kamransaleem">
        <dropPrimaryKey tableName="logical_flow_decorator"
                        constraintName="data_flow_decorator_pkey" />
    </changeSet>

    <changeSet id="20200224-4712-2"
               author="kamransaleem">
        <createIndex indexName="idx_lfd_lfid_deckind_decid"
                     tableName="logical_flow_decorator"
                     unique="true">
            <column name="logical_flow_id" />
            <column name="decorator_entity_kind" />
            <column name="decorator_entity_id" />
        </createIndex>
    </changeSet>

    <changeSet id="20200224-4712-3"
               author="kamransaleem"
               dbms="mssql,postgresql">
        <addColumn tableName="logical_flow_decorator">
            <column name="id"
                    type="${id.type}"
                    autoIncrement="true">
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="20200224-4712-4"
               author="kamransaleem"
               dbms="mssql,postgresql">
        <addPrimaryKey tableName="logical_flow_decorator"
                       constraintName="logical_flow_decorator_pkey"
                       columnNames="id" />
    </changeSet>

    <!--  MySQL / MariaDB only -->
    <changeSet id="20200224-4712-3"
               author="kamransaleem"
               dbms="mysql">
        <addColumn tableName="logical_flow_decorator">
            <column name="id"
                    type="${id.type}"
                    autoIncrement="true">
                <constraints nullable="false"
                             primaryKey="true"
                             unique="true" />
            </column>
        </addColumn>
    </changeSet>


    <!-- 3678 - Remove drill grid -->
    <changeSet id="20200311-3678-1"
               author="woodjes">
        <dropTable tableName="drill_grid_definition"/>
    </changeSet>

    <!-- 4769 - Add rating_editor_role to measurable category -->
    <changeSet id="20200312-4769-1"
               author="davidwatkins73">
{'fraction_non_alphanumeric': 0.10874037948639793, 'fraction_numerical': 0.038710660672102416, 'mean_word_length': 1.9151488227454465, 'pattern_counts': {'":': 0, '<': 191, '<?xml version=': 0, '>': 191, 'https://': 0, 'lorem ipsum': 0, 'www.': 10, 'xml': 12}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3746425', 'n_tokens_mistral': 3919, 'n_tokens_neox': 3509, 'n_words': 667}