How to create a "super"-table which will be always created infront of any table?

General Discussion
1

Hello all,

I am new to this community. So sorry if I am doing something wrong.

I have the requirement, that all tables need some specific columns. this are:

  • id (primary key)

  • uuid (unique key for offline handling)

  • createTimestamp

  • createUserId

  • updateTimestamp

  • updateUserId

I would like to have some template file, which I just include into all new files.

Is there any supported solution?

bye Hans

2

Hello Steve,



many thanks for you quick response.

I am using right now the XML format.

Before going on for me it is not clear what you mean with:
"If you intend to use the XML changelog format and enforce that all changes to all database instances are made by developers/DBAs making additions to the XML changelog"
Sounds like you would recommend a different approach?

Back to the previous question.

Here our template table file: 000_DefaultTableTemplate.xml

and the other files including it:
Translation.xml

<databaseChangeLog
xmlns=“http://www.liquibase.org/xml/ns/dbchangelog” xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”

xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"

xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd

http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
&propertiesAll;


The varchar properties are imported as xml entities at one place, so we have some defaults for the developers and to keep them same for the DBAs to administer their storages/tablespaces.
With the above changesets we have nearly our solution, but only nearly.
The issue now is, since we are using the property ${changeset.name}_DefaultTable and we assumed this property gets renewed on every new changeset file like the Translation.xml we get a collision due to duplicateded ids of the changeset. So this lets me assume the properties are immutable, or I am doing something wrong?

We could debug in more depth in the IDE, but we would need some advice there to take a look.

best wishes
3

This is a good place for these kinds of questions. There is also StackOverflow - tag your question ‘liquibase’. There are many more people on StackOverflow, so you may get quicker response there rather than here.

On to your question. As with most things, the answer is ‘it depends’. How do you want to use Liquibase? If you intend to use the XML changelog format and enforce that all changes to all database instances are made by developers/DBAs making additions to the XML changelog (or included XML files) then one of the simplest things to do would be to just use the standard XML ‘entity include’ mechanism. To do that, you would declare the ‘standard’ columns as an XML entity, and then everywhere you use the createTable tag, you include the entity with the standard columns.

http://xmlwriter.net/xml_guide/entity_declaration.shtml

This of course leaves you open to mistakes and other issues.

  • what if someone forgets to include the standard columns? Do you have some other mechanism to check that they are there before deploying to a given database?

  • what if the set of standard columns changes in the future? How will you approach that?

Steve Donie

Principal Software Engineer
Datical, Inc. http://www.datical.com/

4

On the first question - I am not recommending any particular approach to using Liquibase - I just know that there are many different ways it can be used. One of the most common things we have seen is that teams don’t want to author database changes in XML - they want to make changes using the vendor-provided GUIs and then use Liquibase diffChangelog to create the XML. It sounds like your team is OK with editing XML.

But back to your real question.

You are correct that when Liquibase processes the XML files that properties are immutable - once they are set, they cannot change.

I think your options are to either look at something like XSLT or something else to pre-process the XML files or else you could write your own Liquibase changelog tag implementation that ‘extends’ the existing createTable tag and automatically creates the required columns.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

5

Hello together,

From my perspective it would be nice to have a new optional attribute global, which will always be true and will let the property behave as it was before.

So the user/developer of the changesets could decide about the visibility of the properties. If ommited, it will behave as it was (global)

Steve, the other option you suggested would be a more private extension. So the reuse of it is very low from my perspective. So from community perspective a choice which is not extending the usability.

We (me and my team) would like to provide a global/local property patch for this change, but we should know if there is a good chance to get incorporated into the main stream of the liquibase. Can you help us on this decission?

bye Darko

6

This seems like a fairly safe addition, so it seems likely that it could be included into the project mainline. If you include unit tests for the change and ensure that no existing tests break, the chances of it being included are even better.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

7

Hello Steve,

I have created a patch and a pull request like we talked and I promissed.

https://liquibase.jira.com/browse/CORE-2309

Could you verify the pull request to your needs?
If there is something to fix, please let me know.

Right now we are testing additionally in the whole team with a locally build 3.4.0-SNAPSHOT version. I will let you know if we find an issue.

bye,
Darko

8

While I work closely with Nathan (founder and core maintainer of Liquibase) I do not have commit rights to the project. Nathan is in the midst of moving houses, so it might be a few days before he gets to your pull request. He is good about giving feedback though.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/