Cadence Schema Create/Update Idempotent

[johnkost]

Is your feature request related to a problem? Please describe. When I am setting up new Cadence clusters, I want to be able to automatically run the migrations in an idempotent way. If the migrations were flyway or liquibase migrations, I could run the migrations many times and the result would be the same. When I run the setup schema multiple times, it says the database/tables already exist.

Proposed Solution Add a new field to the cadence-*-tool called idempotent that will perform a check to see if the DB or tables exist before trying to create them.

Additional context I think the idea of having the migrations written in Go rather than another DB migration tool is to support the many possibilities of backends. If so, adding the checks dependent on a new field/environment variable should solve the issue.

My use case comes from trying to spin up a new Cadence cluster per PR opened for my application. I wanted to be able to run all the migrations automatically (similar to how we handle flyway); however, after the first run of the chart (which is running the cadence-sql-tool), it errors out due to the tables already existing. We now run all migrations manually but this results in a manual process for each PR opened as we must setup the DB first before being able to roll the Cadence cluster out.

[longquanzheng]

@johnkost I think that's a good proposal. It's probably worth mentioning that the tool should also be idemptent for other error when creating fields/indexes but got existing error.

See what kind of operation each version upgrade may need to execute here: https://github.com/uber/cadence/tree/master/schema/mysql/v57/cadence/versioned

In some rare case, the schema may need to insert some data(DML) too.

Feel free to make PR and I would be happy to review them.

[johnkost]

Ill work to make a PR for this fix. Will link and we can discuss more on that