I have written a lot about SQLcl’s Liquibase integration because I’m a great fan of it. One of the things it allows me to do is to generate the changelog for an entire schema. I’m using that a fair bit as part of schema migrations. Liquibase follows the delta approach, where a change depends on the previous. I like to save the expected state along with a migration, which allows me to review the actual vs. expected state in case things go pear-shaped. There has been a little caveat with that until now. Thanks to the wonderful SQLcl dev team, it’s possible to overwrite files when generating the schema. Please allow me to demonstrate.
Let SQLcl protect you from yourself
You can create the changelog as follows:
$ sql martin@localhost/freepdb1
SQLcl: Release 24.1 Production on Fri Apr 05 08:49:05 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Password? (**********?) ******
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
SQL> tables
TABLES
_________
T1
SQL> lb generate-schema -split -sql
--Starting Liquibase at 2024-04-05T08:49:27.830866811 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type - TYPE_SPEC]: 229 ms
[Type - TYPE_BODY]: 117 ms
[Type - SEQUENCE]: 47 ms
[Type - DIRECTORY]: 35 ms
[Type - CLUSTER]: 1508 ms
[Type - TABLE]: 14960 ms
[Type - MATERIALIZED_VIEW_LOG]: 77 ms
[Type - MATERIALIZED_VIEW]: 23 ms
[Type - VIEW]: 1546 ms
[Type - REF_CONSTRAINT]: 120 ms
[Type - DIMENSION]: 46 ms
[Type - PACKAGE_SPEC]: 94 ms
[Type - FUNCTION]: 92 ms
[Type - PROCEDURE]: 92 ms
[Type - DB_LINK]: 292 ms
[Type - SYNONYM]: 57 ms
[Type - INDEX]: 1419 ms
[Type - TRIGGER]: 288 ms
[Type - PACKAGE_BODY]: 92 ms
[Type - JOB]: 43 ms
[Method loadCaptureTable]: 21177 ms
[Method sortCaptureTable]: 26 ms
[Method writeChangeLogs]: 219 ms
Changelog created and written out to file controller.xml
Operation completed successfully.
Nice! The changelog has been generated, each object type (ok, just tables in this example) gets its own directory, and the SQL file is present, too, as a quick glimpse at the file system reveals:
$ tree
.
├── controller.xml
└── table
├── t1_table.sql
└── t1_table.xml
1 directory, 3 files
Now, if I go and make some changes, and run the command again, SQLcl prevents me from doing something silly and creates a new file for anything that exists on disk. Like so:
SQL> lb generate-schema -split -sql
--Starting Liquibase at 2024-04-05T08:57:49.534610807 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type - TYPE_SPEC]: 255 ms
[Type - TYPE_BODY]: 119 ms
[Type - SEQUENCE]: 45 ms
[Type - DIRECTORY]: 35 ms
[Type - CLUSTER]: 1573 ms
[Type - TABLE]: 15180 ms
[Type - MATERIALIZED_VIEW_LOG]: 66 ms
[Type - MATERIALIZED_VIEW]: 21 ms
[Type - VIEW]: 1556 ms
[Type - REF_CONSTRAINT]: 119 ms
[Type - DIMENSION]: 46 ms
[Type - PACKAGE_SPEC]: 92 ms
[Type - FUNCTION]: 93 ms
[Type - PROCEDURE]: 93 ms
[Type - DB_LINK]: 309 ms
[Type - SYNONYM]: 58 ms
[Type - INDEX]: 1415 ms
[Type - TRIGGER]: 297 ms
[Type - PACKAGE_BODY]: 93 ms
[Type - JOB]: 43 ms
[Method loadCaptureTable]: 21508 ms
[Method sortCaptureTable]: 24 ms
[Method writeChangeLogs]: 222 ms
Changelog created and written out to file controller_1.xml
Operation completed successfully.
SQL> !tree
.
├── controller_1.xml
├── controller.xml
├── table
│ ├── t1_table.sql
│ └── t1_table.xml
└── table_1
├── t1_table.sql
└── t1_table.xml
2 directories, 6 files
That’s great because it’s impossible to wipe out important information for which I don’t have a backup. If this extra layer of protection isn’t required, however – maybe because I’m using Git anyway to keep the earlier image of my project files – I don’t need SQLcl to protect me from myself.
The new option
Beginning with SQLcl 24.1.0, you can consciously decide to overwrite files. Here is proof:
SQL> lb generate-schema -split -sql
--Starting Liquibase at 2024-04-05T10:06:25.101831862 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type - TYPE_SPEC]: 385 ms
[Type - TYPE_BODY]: 118 ms
[Type - SEQUENCE]: 45 ms
[Type - DIRECTORY]: 36 ms
[Type - CLUSTER]: 2543 ms
[Type - TABLE]: 14592 ms
[Type - MATERIALIZED_VIEW_LOG]: 61 ms
[Type - MATERIALIZED_VIEW]: 22 ms
[Type - VIEW]: 1540 ms
[Type - REF_CONSTRAINT]: 118 ms
[Type - DIMENSION]: 45 ms
[Type - PACKAGE_SPEC]: 93 ms
[Type - FUNCTION]: 91 ms
[Type - PROCEDURE]: 93 ms
[Type - DB_LINK]: 289 ms
[Type - SYNONYM]: 57 ms
[Type - INDEX]: 1427 ms
[Type - TRIGGER]: 288 ms
[Type - PACKAGE_BODY]: 93 ms
[Type - JOB]: 43 ms
[Method loadCaptureTable]: 21980 ms
[Method sortCaptureTable]: 23 ms
[Method writeChangeLogs]: 204 ms
Changelog created and written out to file controller.xml
Operation completed successfully.
SQL> !tree
.
├── controller.xml
└── table
├── t1_table.sql
└── t1_table.xml
1 directory, 3 files
SQL> lb generate-schema -split -sql -overwrite-files
--Starting Liquibase at 2024-04-05T10:07:33.086194117 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
[Type - TYPE_SPEC]: 272 ms
[Type - TYPE_BODY]: 117 ms
[Type - SEQUENCE]: 45 ms
[Type - DIRECTORY]: 34 ms
[Type - CLUSTER]: 1487 ms
[Type - TABLE]: 14439 ms
[Type - MATERIALIZED_VIEW_LOG]: 73 ms
[Type - MATERIALIZED_VIEW]: 21 ms
[Type - VIEW]: 1543 ms
[Type - REF_CONSTRAINT]: 116 ms
[Type - DIMENSION]: 44 ms
[Type - PACKAGE_SPEC]: 94 ms
[Type - FUNCTION]: 91 ms
[Type - PROCEDURE]: 90 ms
[Type - DB_LINK]: 245 ms
[Type - SYNONYM]: 56 ms
[Type - INDEX]: 1421 ms
[Type - TRIGGER]: 308 ms
[Type - PACKAGE_BODY]: 93 ms
[Type - JOB]: 45 ms
[Method loadCaptureTable]: 20634 ms
[Method sortCaptureTable]: 13 ms
[Method writeChangeLogs]: 200 ms
Changelog created and written out to file controller.xml
Operation completed successfully.
SQL> !tree
.
├── controller.xml
└── table
├── t1_table.sql
└── t1_table.xml
1 directory, 3 files
If you choose the option, you must have a backup of the files you are overwriting; otherwise, the previous contents will be gone. Like, really gone.
Summary
During a sprint, when things are still very much in flow and I’m testing locally, I really appreciate the ability to overwrite existing Liquibase changelogs without having to manually clear the directory out before a call to lb generate-schema. As I said before, understand what the option does and use it only in situations where it makes sense.
Martins Blog 