New User Questions

General Discussion
1

Greetings,

I’m a DBA / database developer primarily using MS SQL.  I’m about to start on a new project and I want to use this opportunity to refine my work flow.  Currently I use DDL triggers to capture any DDL changes, and I have a system in place to distribute those changes.  It works, but it’s very basic, and I’m looking to improve this by incorporating source control, branching, and having the ability to comment changes.  I just came across Liquibase yesterday, and after watching the videos and reading the docs, I have a few questions. 

My first question is, what is the best way to generate changelogs? 

Is typing the XML the only way, or are there other tools that can help?  The way I see it, there are several different types of changes to apply.  First, there are table changes.  As you say in your videos, this is a case where using a diff isn’t sufficient.  What I would love is if there’s an ERD tool that can generate liquibase change logs (so if I rename a column, it renames it, and if I delete and add, it creates changelogs for the delete and add). 

Then there are data changes.  I could see data changes handled in multiple ways.  The easiest would be to have a tool where I could either manually enter values, or run a query and produce the required insert or merge statements. 

The last kind of change is to a programmatic object, such as a function or stored procedure.  I currently like working in SSMS to create these, as I get the benefits of easy look ups, quick testing, context highlighting, etc.  I don’t feel like I need a separate editor, but I do need a way to generate the change log.  If that could be done automatically when I commit a change, or if I could take a diff to just capture these changes, since they lack the state issue that tables contain.  (There are permissions to consider, but I typically have those automated). 

Second, what is the current state of an IDE?  I’ve seen mention of one, but I think I’ve also read that it’s out of date? 

Thanks in advance.  It appears like you have the heart of a really slick system here, and I hope I can find a way to incorporate it into my workflow.

2

There is a generateChangeLog command available for the command line, ant and maven that will take an existing database and create a changelog for it’s current state.  That be said, the diff support in liquibase (which is used to create the changelog) is relatively basic since I consider it to be a secondary tool/sanity check and not something central to the normal liquibase process.  For example, the diff/generateChangeLog will be able to capture tables, views, procs, indexes, foreign key constains, and a few other things, but it can’t tell the difference between clustered and non-clustered indexes, detect triggers, or other database-specific and/or more advanced objects.

What I would suggest doing is using generateChangeLog to create your starter changelog file, then manually inspect and modify it as needed based on how you know your database is supposed to be.  From that point on you would use the normal liquibase process of adding a changeSet to a changeLog file and updating your database. 

If you find your generated changelog is too wrong, you can always use a built-in database tool to generate a database dump that will be fully valid and begin your databaseChangeLog with an that reads that file in.

The rest of your question is really around the IDE.  I had made a proof-of-concept IDE, but unfortunately I was not able to dedicate the time to keep it up to date and keep the core library advancing as well, and so it has been put on hold for now.  It is definitely something that would be very helpful to people since it would solve your requests to be able to modify the database through a tool and have those changes added to a changeLog file.  You can use the liquibase diff tool, but it suffers from the same limitations listed above, as well as the inherent problems in database diff tools (http://blog.liquibase.org/2007/06/the-problem-with-database-diffs.html).

Depending on what works best for your process, you may want to consider http://blog.liquibase.org/2010/05/liquibase-formatted-sql.html.  That would allow you to write your sql in your normal SQL editor with the syntax highlighting and completion, but then rather than executing it in there, copy the SQL to your changelog sql file and execute it from within liquibase.  You loose the database-independence and auto-rollback support, but it may keep you closer to your existing toolset.

Nathan