Generate SQL file from history changes

General Discussion
1

Our test databases have been updated very often using Liquibase (and only Liquibase).  But now some DB admins have asked us to show in SQL what we changed.  The problem is that I don’t have any SQL stored (using UpdateSQL) so I’ll have to do it from history.  Ideally by providing a start and end date.

I’m using the Maven plugin to run Liquibase.


Thanks!

2

Hey Nathan,

Thanks for the reply.

Some direction could be useful yes.  I was thinking along the lines of polling the databasechangelog table using a Java job with date boundaries.  Then getting the changes and the liquibase xml files from that given period and try to recreate the sql.  I am totally unfamiliar with the liquibase java api.


 


3

There isn’t anything built into liquibase currently that will get you that. The dbdoc output lists pending sql, but only the changes historically.


You could re-run updateSQL mode against a blank database, but there wouldn’t be a technical guarantee that the output SQL is the same as what would have been ran against a particular server.


Programatically you could build up the sql that ran against a given database using the liquibase java API, I could give you a little more direction on that if that is something you would be up for.


Nathan

4

If you look at the liquibase.Liquibase class, you can get a good idea of the main access point to liquibase and/or normal usages of the rest of the codebase. On the Liquibase object there are methods like changeLogSync() and update(), but not a listRanChangeSets() like I thought there might be.


Instead, if you look at the rollback() method you can see an example of close to what you need to do. There is a ChangeLogIterator class for filtering and iterating over a changelog.


You code would look something like:


  1. ResourceAccessor resourceAccessor = new FileSystemResourceAccessor("/path/to/changelog/root");
  2. Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(conn))
  3. DatabaseChangeLog changeLog = ChangeLogParserFactory.getInstance()                      .getParser(changeLogFile, resourceAccessor)
  4.  .parse(changeLogFile, null, resourceAccessor);
  5.  
  7.         LoggingExecutor exec = new LoggingExecutor(ExecutorService.getInstance().getExecutor(database), output, database);
  8.         ExecutorService.getInstance().setExecutor(database, exec);
  9. ChangeLogIterator logIterator = new ChangeLogIterator(changeLog,
  1.     new AlreadyRanChangeSetFilter(database.getRanChangeSetList()),
  2.     new ContextChangeSetFilter(contexts),
  3.     new DbmsChangeSetFilter(database));

  5. logIterator.run(new YourCustomLogSqlVisitor(database), database);


The YourCustomLogSqlVisitor class is an implementation of liquibase.changelog.visitor.ChangeSetVisitor with a visit() method that would call changeSet.execute(). Notice that execute() will not actually run the change because you set the Executor to be the LoggingExecutor instead in the above code.


Hopefully that helps, let me know if you have questions


Nathan