Support for Liquibase
Thomas Kellerer
we are using Liquibase (www.liquibase.org) to manage our DB model
scripts.
We use an XSLT script to turn our Architect file into our initial
changeset. Unfortunately any change that we apply later we need to
manually add to the Liquibase files.
I would like to add support for Liquibase to the "Compare DataModel"
feature.
I first thought about adding a third option to the CompareDM panel
(SQL, English, Liquibase) but after looking into the source code I
think the easiest way would be to create a new DDL generator for
Liquibase which "simply" outputs XML rather than SQL.
That would have the added "benefit" that generating the initial model
would not need the XSLT either.
The only drawback that I can see is, that Liquibase is not strictly
speaking "DDL" so the naming would be a bit misleading - in the source
code as well as in the "Create SQL for" dropdowns.
But as far as I can tell, creating a Liquibase DDL generator would be
the most flexible solution to cover the whole process.
What do you think? Do you have any objections to creating a "DDL"
generator that outputs XML?
Regards
Thomas
Thomas Kellerer
a DDL generator is the only sensible way because that will add the
support for the initial script as well as for delta scripts.
I'm pretty much done with it, so I could commit it. It just needs a
bit more testing.
I have some questions regarding the DDL generation though:
SQLScriptDialog.java iterates over all generated DDLStatements and
puts the resulting SQL into the dialog.
Wouldn't it make more sense if it called generateDDLScript() instead?
Because that would also add the statements that are returned from
DDLGenerator.writeDDLTransactionBegin() and
DDLGenerator.writeDDLTransactionEnd() (which I'm using to generate the
enclosing <changeSet> tags)
I also noted that DDLGenerator is missing a dropIndex() method. So
when an index is removed from the model, the DM compare will not
reflect this.
When a column that is part of the primary key is dropped, the
DDLGenerator is called in the following order:
1) dropColumn(),
2) dropPrimaryKey()
3) addPrimaryKey()
(unless I am missing something)
This will most probably fail on most databases, so the correct order
would be dropPrimaryKey(), dropColumn(), addPrimaryKey()
I had to make two other minor changes in order to integrate my
LiquibaseDDLGenerator:
I added a new StatementType (XMLTAG) in DDLStatement to flag the
things generated by LiquibaseDDLGenerator properly.
In order to make Liqubase appear in the SQL dropdowns, I added the
following to default_database_types.ini
[Database Types_13]
Name=Liquibase XML
DDL Generator=ca.sqlpower.architect.ddl.LiquibaseDDLGenerator
I am not sure if this is 100% correct, but I thought this a better
solution than hardcoding it to DDLUtils.getDDLTypes()
So if there are no major objections to this approach I would like to
commit the new DDL Generator ;)
Regards
Thomas
On Feb 14, 6:55 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:
Jonathan Fuerth
Sorry for the delayed response--we had a long weekend here in Toronto.
After looking at this closer I think implementing Liquibase support as
a DDL generator is the only sensible way because that will add the
support for the initial script as well as for delta scripts.
I'm pretty much done with it, so I could commit it. It just needs a
bit more testing.
Sounds great. To answer your original question, I don't have any issues with creating a "DDL Generator" that actually outputs Liquibase XML.
I have some questions regarding the DDL generation though:
SQLScriptDialog.java iterates over all generated DDLStatements and
puts the resulting SQL into the dialog.
Wouldn't it make more sense if it called generateDDLScript() instead?
Because that would also add the statements that are returned from
DDLGenerator.writeDDLTransactionBegin() and
DDLGenerator.writeDDLTransactionEnd() (which I'm using to generate the
enclosing <changeSet> tags)
We do that because we still need the individual statements when it comes time to execute (that is, when you press the Execute button rather than saving the file). It's a significantly more robust approach than trying to cut up a big string into individual statements later on.
I also noted that DDLGenerator is missing a dropIndex() method. So
when an index is removed from the model, the DM compare will not
reflect this.
Yes, there are a number of holes regarding indexes in Compare DM functionality. Index modeling and forward engineering was added in a rush to support GQguru. You might recall the first attempt at an index UI was pitiful--I actually removed it prior to release because we were better off without any index support. We did come back with a second attempt at UI, but Compare DM support kind of fell off the radar.
When a column that is part of the primary key is dropped, the
DDLGenerator is called in the following order:
1) dropColumn(),
2) dropPrimaryKey()
3) addPrimaryKey()
(unless I am missing something)
This will most probably fail on most databases, so the correct order
would be dropPrimaryKey(), dropColumn(), addPrimaryKey()
Agreed.
I had to make two other minor changes in order to integrate my
LiquibaseDDLGenerator:
I added a new StatementType (XMLTAG) in DDLStatement to flag the
things generated by LiquibaseDDLGenerator properly.
Makes sense.
In order to make Liqubase appear in the SQL dropdowns, I added the
following to default_database_types.ini
[Database Types_13]
Name=Liquibase XML
DDL Generator=ca.sqlpower.architect.ddl.LiquibaseDDLGenerator
I am not sure if this is 100% correct, but I thought this a better
solution than hardcoding it to DDLUtils.getDDLTypes()
What about going back to your original UI idea here and adding a new radio button on the Compare DM panel? In the action attached to the Start button, you could treat it as the same case as "DDL," but stick in the Liquibase generator. In the future, if we come up with more such things, we could work in a combo box like we already use for DDL.
I think the main problem with creating a database type for Liquibase is that it isn't a database platform you can create connections to. Although the DDL generator API is the most natural place to fit Liquibase support into the code, it's not really appropriate to expose that detail in the UI.
So if there are no major objections to this approach I would like to
commit the new DDL Generator ;)
No objections. Sounds like a cool feature!
-Jonathan
Thomas Kellerer
> > puts the resulting SQL into the dialog.
> > Wouldn't it make more sense if it called generateDDLScript() instead?
> > Because that would also add the statements that are returned from
> > DDLGenerator.writeDDLTransactionBegin() and
> > DDLGenerator.writeDDLTransactionEnd() (which I'm using to generate the
> > enclosing <changeSet> tags)
>
> We do that because we still need the individual statements when it comes
> time to execute (that is, when you press the Execute button rather than
> saving the file). It's a significantly more robust approach than trying to
> cut up a big string into individual statements later on.
Hmm, but the loop that is SQLScriptDialog is essentially the same as
loop in DDLGenerator. generateDDLScript() and when using that, the
script window would contain a "complete" script (from the generator's
point of view)
> > In order to make Liqubase appear in the SQL dropdowns, I added the
> > following to default_database_types.ini
> > [Database Types_13]
> > Name=Liquibase XML
> > DDL Generator=ca.sqlpower.architect.ddl.LiquibaseDDLGenerator
>
> > I am not sure if this is 100% correct, but I thought this a better
> > solution than hardcoding it to DDLUtils.getDDLTypes()
>
> What about going back to your original UI idea here and adding a new radio
> button on the Compare DM panel? In the action attached to the Start button,
> you could treat it as the same case as "DDL," but stick in the Liquibase
> generator. In the future, if we come up with more such things, we could work
> in a combo box like we already use for DDL.
Then we would need to rework the "Forward Engineer SQL Script" as well
in order to select between "SQL Generation" and "Liquibase". The
current approache does not need any change in either dialog...
Regards
Thomas
Thomas Kellerer
> What about going back to your original UI idea here and adding a new radio
> button on the Compare DM panel? In the action attached to the Start button,
> you could treat it as the same case as "DDL," but stick in the Liquibase
> generator. In the future, if we come up with more such things, we could work
> in a combo box like we already use for DDL.
I'll do that.
The CompareDMPanel will have a third radio button (SQL, Liquibase,
English)
The DDLExportPanel will have a checkbox at the bottom that will select
Liquibase (and disable the other controls).
Regards
Thomas