Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Repeatable Migrations support #128

Closed
flyway opened this issue Jun 25, 2013 · 9 comments
Closed

Repeatable Migrations support #128

flyway opened this issue Jun 25, 2013 · 9 comments

Comments

@ghost
Copy link
Collaborator

@ghost ghost commented Jun 25, 2013

Original author: ospy...@gmail.com (September 25, 2012 10:36:53)

Hello,

and thanks a lot for the effort you put in flyway. We are currently searching for a solution as close as possible to the way we work:

DDL code is maintained in a number of files categorized mostly by type e.g. Tables.sql, Views.sql, while procedural code is kept in a Packages directory with one file per package e.g. Package1.sql, Package2.sql etc.

For Tables and Views changes are appended at the bottom of each file (starting with a comment with the issue number). Example Tables.sql:

...
... # 3453 Add column x
ALTER TABLE x ADD COLUMN y;

  • Packages are edited in place and committed with a similar comment appended on the top of the file.

For us this is a very convenient setup, allowing to apply migrations after a specific issue - revision by opening a minimal set of files and copying from the desired line to the bottom of the file (or using the file as is for packages).

Is this a use case excluded from flyway initial design for some reason? Do you think it could be included at some point in the future?

Thanks again, hope the above makes some sense!

Original issue: http://code.google.com/p/flyway/issues/detail?id=336

@ghost
Copy link
Collaborator Author

@ghost ghost commented Jun 25, 2013

From axel.fontaine.business@gmail.com on October 05, 2012 13:12:27
Hi!

I see where you are coming from. This type of deployment where a lot of logic resides in the DB is indeed no optimally supported at this time.

A workaround could be to have a separate Flyway instance, with its own metadata table, managing only the packages. You could then piggyback on the clean on validation error functionality to edit the files in place and then, using the change in checksum to trigger Flyway to redeploy them.

Cheers
Axel

@ghost
Copy link
Collaborator Author

@ghost ghost commented Jun 25, 2013

From dyo...@gmail.com on January 31, 2013 02:08:04
Thanks for the pointer on the setCleanOnValidationError property. I have it set to true in my properties file (setCleanOnValidationError=true). Edited a stored procedure in place and run the following scenarios,

1, flyway.sh info
Didn't appear to have calculated the checksum. Returned success on all migrated objects
2, flyway.sh validate
Returned errors - ERROR: FlywayException: Validate failed. But didn't clean the migrated objects
3, flyway.sh migrate
Returned "Schema is up to date. No migration necessary."

Did I miss anything on the Clean on Validation feature?

@ghost
Copy link
Collaborator Author

@ghost ghost commented Jun 25, 2013

From axel.fontaine.business@gmail.com on January 31, 2013 07:37:43
The property is called cleanOnValidationError (no set).

Docs: http://flywaydb.org/documentation/commandline/migrate.html

Example from the website for use in a properties file: flyway.cleanOnValidationError=true

@ghost
Copy link
Collaborator Author

@ghost ghost commented Jun 25, 2013

From dyo...@gmail.com on January 31, 2013 20:11:11
Thanks for the response. I just tried cleanOnValidationerror and it didn't make a difference.

In the properties file,
......
cleanOnValidationError=true
......

Output from running flyway.sh,
......
$ flyway.sh -configFile=testdb.properties info
Flyway (Command-line Tool) v.2.0.3

+----------------+----------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+----------------+----------------------------+---------------------+---------+
| 0 | init | 2013-01-10 22:21:35 | Success |
| 1 | test | 2013-01-30 17:49:54 | Success |
| 2 | proc | 2013-01-30 17:50:38 | Success |
+----------------+----------------------------+---------------------+---------+
$ flyway.sh -configFile=testdb.properties validate
Flyway (Command-line Tool) v.2.0.3

Validated 3 migrations (execution time 00:00.045s)
ERROR: FlywayException: Validate failed. Found differences between applied migrations and available migrations: Migration Checksum mismatch for migration db_2__proc.sql: DB=SQL, Classpath=SQL
ERROR: Occured in com.googlecode.flyway.core.Flyway.doValidate() at line 941
$ flyway.sh -configFile=testdb.properties migrate
Flyway (Command-line Tool) v.2.0.3

Current schema version: 2
WARNING: outOfOrder mode is active. Migration run may not be reproducible.
Schema is up to date. No migration necessary.
flyway]$
......

@ghost
Copy link
Collaborator Author

@ghost ghost commented Jun 25, 2013

From axel.fontaine.business@gmail.com on January 31, 2013 22:16:43
As I said:

flyway.cleanOnValidationError=true

not

cleanOnValidationError=true

@ghost
Copy link
Collaborator Author

@ghost ghost commented Jun 25, 2013

From dyo...@gmail.com on January 31, 2013 23:32:53
Yeah I mis-spelled the property name. Sorry.

Looks like one invalid object causes the entire schema to be wiped out. That's a huge risk. I'll have to keep that property out of our production environment.

Ability to migrate Oracle package/procedure/function is the top feature we'd like to have, since they account for the majority of our database objects. Do you plan to add that feature in a future release?

@ospyros
Copy link

@ospyros ospyros commented Jan 8, 2014

+1

@axelfontaine axelfontaine changed the title SCM integration Procedure/Package/Object per file with history in SCM support Nov 24, 2014
@axelfontaine axelfontaine added this to the Flyway 4.0 milestone Nov 24, 2014
@axelfontaine axelfontaine changed the title Procedure/Package/Object per file with history in SCM support Repeatable Scripts support Nov 22, 2015
@KevinSheedy
Copy link

@KevinSheedy KevinSheedy commented Dec 11, 2015

+1 flyway is great for DDL eg create table, alter table
However, it would be great if it had a built-in mechanism for redeploying procedures, functions etc from a single source of the truth in a git repo.

axelfontaine added a commit that referenced this issue Jan 14, 2016
@axelfontaine axelfontaine changed the title Repeatable Scripts support Repeatable Migrations support Feb 1, 2016
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Feb 1, 2016
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Feb 1, 2016

Fixed.

axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Feb 2, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants