Just completed exporting our existing database to a liquibase changeset! Great tool!
However, after generating the changelog I used SQL Delta to compare the new database generated by liquibase with our existing pristine database and discovered a number of limitations with generateChagneLog which made the conversion a bit time consuming. I thought I would share what I found so that they can be addressed in future releases. Our current database is MS SQL 2005.
- length of nvarchar columns are lost
- nvarchar(max)/varchar(max) is converted to ntext/text
- missing the ability to set the identity seed value on for auto increment column
- missing option to make primary key non-clustered
- missing ability to create defaults, on those columns where defaults were used it would generate the create default statement in default value which doesn’t work
- missing ability to include non-key columns on create index (http://msdn.microsoft.com/en-us/library/ms190806.aspx)
- missing create statistics
- missing column documentation (sp_addextendedproperty)
- several indexes were not generated, but many were. I think most of them were those with non-key columns but some of those that were generated had non-key columns and I had to replace the create index with raw sql so I’m not sure why some were generated and others not.
- several foreign keys constraints were not generated, but many were, not really sure why. I found a few tables in pristine database defined same foreign key constraint with different names and might have something to do with it, but these should still import so we can use liquibase to drop it.
- missing check constraints