1

When starting to use Liquibase on an existing database, it is often useful, particularly for testing, to have a way to generate the change log to create the current database schema. Liquibase allows you to do this with the “generateChangeLog” command_line command. However this command will generate database change log in XML format only.

So how to generate the change log in SQL format to create the current database schema ? Does it exist any way to convert the database change log in XML format to SQL format ? If not, is there any extension point in Liquibase API to add this feature ?

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
bgillis
  • 261
  • 6
  • 18
  • Which RDBMS are you using? – Eric Hauenstein Feb 28 '14 at 15:35
  • I use mainly Oracle 11.0.2.x. – bgillis Feb 28 '14 at 15:53
  • 1
    After you executed `generateChangeLog` you can run [`updateSQL`](http://www.liquibase.org/documentation/command_line.html) (against an empty database). Not convenient but a way to generate SQL. – Jens Feb 28 '14 at 16:17
  • Thanks Jens... that's indeed a very clever way to solve this. But as you said, you will need an additional empty database to run the command. – bgillis Feb 28 '14 at 16:38
  • 1
    An example of how @Jens solution would work: http://stackoverflow.com/questions/8397488/comparing-databases-and-genrating-sql-script-using-liquibase/8408334#8408334 – Mark O'Connor Feb 28 '14 at 23:10

1 Answers1

2

There is no support currently to generate SQL, but you would be able to write an extension to do it. The *Serializer classes like liquibase.serializer.core.xml.XMLChangeLogSerializer take a changelog object and output into whatever format you want.

With something like FormattedSqlChangeLogSerializer that overrides getValidFileExtensions() to return new String[]{"xml"} you can just run generateChangeLog with outputFile=some.file.sql and get the SQL you generated in your custom serializer class.

The extension system will allow you to create this class in a custom jar.

Nathan Voxland
  • 15,453
  • 2
  • 48
  • 64
  • Thanks Nathan for your input. I've also looked at Liquibase API and find out these classes. I will have a look deeper now I'm sure it is the way to go ;-) – bgillis Mar 01 '14 at 17:29
  • https://gist.github.com/nvoxland/9299105 will go into the next version of liquibase (3.2.0), but I need to figure out a way to know the DB type to generate SQL for instead of hard coding Oracle on line 42. You sould be able to use this as a local extension, though – Nathan Voxland Mar 01 '14 at 23:17
  • Awesome! I guess I could wait for Liquibase 3.2.0 then. To avoid hard coding the database type, would it be possible to get it form `liquibase.database.DatabaseFactory` ? In particular, through the method `public Database openDatabase(String url, String username, String password, String driver, String databaseClass, String driverPropertiesFile, ResourceAccessor resourceAccessor) throws DatabaseException` . Maybe the method attributes could be obtained through system properties set when they are first read from command line. – bgillis Mar 03 '14 at 07:47
  • Yes, the trouble is how to know the database type at that point in the code. A system property is an option but think there may be a couple other ways to get it into there too. It will probably be mid to later March for 3.2.0 if you'd like to wait, otherwise if you build the gist (probably want to change the package) liquibase should still pick it up and use it even in 3.1.1 – Nathan Voxland Mar 03 '14 at 13:56
  • Thanks a lot Nathan... I'm not in a hurry so I will probably wait till 3.2.0 is released. In the meantime, I have already tested the provided serializer class for formatted sql as suggested. It works flawlessly as expected ;-) – bgillis Mar 04 '14 at 08:25