# Snowflake to MySQL migrations


## How to create a migration

- Put your XML migration file under `/kafka-db-deploy/snowflake-mysql/{MYSQL_DB_NAME}/build/changelog/dml` directory. Where `MYSQL_DB_NAME` is the destination database name.
- No special rollback sections/tags. To rollback a migration you will have to create a new migration with rollback queries manually.
- The changeset id must be unique across all changesets. Follow best practices below to ensure this.

**NOTE:** If you are adding new database to this migration type (or there are no previous migration files in the directory) you need to make sure database credentials are present in AWS SecretsManager.
Secret name convention: `{Environment}/kafka-db-deploy/MYSQL_CREDENTIALS_{MYSQL_DB_NAME}` - (`MYSQL_DB_NAME` secret key par in uppercase).

Secret must contain these fields:
```json
{
  "username": "example_user",
  "password": "example_pass",
  "uri": "example_hosname:3306/example_db_name"
}
```


## Best Practices

- Only one file per PR.
- Changeset id should match the file ('.xml' filetype excluded)
- If there are multiple changesets in a file, append the sequence number of the changeset

## Migration example


Example: [DS-5334-test-mysql-migration.xml](https://github.com/theorchard/database/pull/14086/files)

```
<?xml version="1.0" encoding="UTF-8"?>
<changelog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <changeset id="DS-5334-test-mysql-migration:1"
      author="ibolshakov"
      run-always="true">
        <precondition>
          <sqlquery>
            <![CDATA[
              SELECT
                ID,

                ...

                LAST_MODIFIED_BY
              FROM FACTS.<SCHEMA_NAME>.TRACK_ARTIST LIMIT 1000;
            ]]>
          </sqlquery>
        </precondition>
        <tableschema
          table-name="track_artist"
          primary-key="ID">
            <column>
              <name>ID</name>
              <type>int64</type>
            </column>

            ...

            <column>
              <name>LAST_MODIFIED_BY</name>
              <type>string</type>
            </column>
        </tableschema>
    </changeset>
</changelog>
```

## XML Fields and Attributes

- `<changeset>`
  - `id` :String: (required) changeset id
  - `author` :String: (required) changeset author
  - `run-on-change` :Boolean: (optional) to run changeset on change
  - `run-always` :Boolean: (optional) to run changeset always

  - `<precondition>`
    - `<sqlquery>` :String: (required) SQL to execute in Snowflake

  - `<tableschema>`
    - `table-name` :String: (required, case-sensitive) destinations table name:
    - `primary-key` :String: (required, uppercase) primary key in the destination table, for composite key — comma-separated list of key columns without spaces
    - `<column>`
      - `<name>` :String: (required, uppercase) column name:
      - `<type>` :String: (required) column data type: