Is there a way to whitelist/blacklist particular OBJECTS when doing a diff?

General Discussion
1

There are some cases where I KNOW that a source table vs. a reference table are different and ALWAYS will be, so not something I want to worry about or change.
Is there any way to specify a list where that particular table (or view or index, etc.) is just IGNORED as part of the diff?

1 Like
2

Hi @JLG ,

You might want to look at the diffExcludeObjects parameter.

https://docs.liquibase.com/tools-integrations/maven/commands/maven-diff.html?Highlight=Exclude

While not knowing your current setup, hope this helps.

Regards,
Eduard

3

TY Eduard for your reply.
I am actually running this from a command line, and it appears that the --diffExcludeObjects" is an unknown option:
Error:
SEVERE: Unexpected error running Liquibase: Unknown option: ‘diffExcludeObjects’
liquibase.exception.CommandLineParsingException: Unknown option: ‘diffExcludeObjects’
at liquibase.integration.commandline.Main.parseOptionArgument(Main.java:1216)
at liquibase.integration.commandline.Main.parseOptions(Main.java:1155)
at liquibase.integration.commandline.Main$1.run(Main.java:259)
at liquibase.integration.commandline.Main$1.run(Main.java:193)
at liquibase.Scope.child(Scope.java:169)
at liquibase.Scope.child(Scope.java:145)
at liquibase.integration.commandline.Main.run(Main.java:193)
at liquibase.integration.commandline.Main.main(Main.java:156)

Here is my command line:

liquibase --outputFile=NNN.to.BBB.Diff.txt --diffTypes=tables --diffExcludeObjects=“table:AUDIT_","table:PMERR”,“table:PM_SRC_*”,“table:PM_TARGET_HEARTBEAT”–driver=oracle.jdbc.OracleDriver --classpath=C:\Users\liquibase\Documents\CDC\Liquibase\examples\sql\ojdbc6-11.2.0.jar --DefaultSchemaName=EDW --url=jdbc:oracle:thin:@//MACHINENAME:1521/rdsdv02 --username=YYYYYY --password=yyyy --referenceUrl=jdbc:oracle:thin:@//MACHINENAME:1521/edwdv02 --referenceUsername=NNN --referencePassword=nnnnn diff

1 Like
4

Hi @JLG ,

diffIncludeObjects and diffExcludeObjects are attribute names for Maven. You may want to have a look at this link : Including and Excluding Objects from a Database

Hope this helps!

Let us know if you face any issues with this.

Rakhi Agrawal

1 Like
5

Thanks for all the help so far – this works, but NOT 100%.

First if I use something like
excludeObjects=“AUDIT_","PMERR”,“EDW_PROCESS_CONTROL”,“EDW_RUN_CONTROL”,“PM_SRC_EVENT”,“PM_SRC_HEARTBEAT”,“PM_TGT_HEARTBEAT”-
it DOES NOT use the wildcards in the table names, so it misses things like AUDIT_CONTROL.
It also misses all of the PMERR_nnnnn tables – they are still included in the diff.

IF I do THIS:
excludeObjects=“AUDIT_CONTROL”,“AUDIT_CONTROL_EDW”,“EDW_PROCESS_CONTROL”,“EDW_RUN_CONTROL”,“PMERR_DATA”,“PMERR_MSG”,“PMERR_SESS”,“PMERR_TRANS”,“PM_SRC_EVENT”,“PM_SRC_HEARTBEAT”,“PM_TGT_HEARTBEAT”
it WILL exclude each of those tables from the MISSING part of the diff, but NOT in the “UNEXPECTED TABLES” diff results.

Can the exclude be forced to ignore on BOTH sides of the diff (so the UNEXPECTED TABLES doesn’t show up?)

6

excludeObjects accepts an object name, which is the regexp (regular expression), exclude any object from the database which name matches the regexp .

So for example: “table:*._lock” will match a table that ends with _lock".
In your example, you can try using such regexp to exclude required tables from diff output.

Regarding your second concern, sorry but I’m not clear and not able to understand it. Could you be more precise on it?

Hope the first solution helps.
Thanks!

7

Thanks for the help with this – regex worked – I missed that initially in the document.
i DID try this:

“AUDIT*.”,“PMERR*.” but they did NOT filter out the various AUDIT_nnn and PMERR_NNN tables. I DID test these in a regex tester and it should have found those tables.

As for the second, I PUT in this to exclude the object:
…
,“PM_TGT_HEARTBEAT”

BUT when I run the diff, that table appears in the diff as a “Unexpected table” - I want to not have the diff look at EITHER the source OR target for this table – just ignore it all together.

8

Actually I realized I had a typo – the regex did NOT work.
I used:
“AUDIT*.” but it did NOT filter any of the AUDIT_NNN tables – I had to explicitly enter them.

AND the exclusions do NOT seem to take into account the target db – just the reference db. Is there any way to make the exclusions cover BOTH (so that “unexpected table” is not shown)?

9

Could you please give a try with regex AUDIT_.* or AUDIT_* once?

Please let us know the results!
Thanks!

Rakhi Agrawal

10

Thanks — that change in the regex fixed the issue.

I NOW still have ONE issue for the diff – it does NOT ingore one of the tables I put on the exclude list on the TARGET side. I am still getting:
Unexpected Table(s):
PM_TGT_HEARTBEAT

Even though that is listed in the exclude:
–excludeObjects=“AUDIT.","PMERR.”,“PM_TGT_HEARTBEAT”

Is there any way to force that? Also is there a way to have liquibase return a return code of 0 if no diffs are found and NON-zero if diffs are found?

11

Folks,
I NOW still have ONE issue for the diff – it does NOT ingore one of the tables I put on the exclude list on the TARGET side. I am still getting:
Unexpected Table(s):
PM_TGT_HEARTBEAT

Even though that is listed in the exclude:
–excludeObjects=“AUDIT. ","PMERR. ”,“PM_TGT_HEARTBEAT”

Any ideas on how to exclude a table from the TARGET side?