I have an Oracle table, it’s a very simple table, 3 columns.
But it has a couple of things that some may find, special.
When described by our Liquibase changeSet, as generated by SQLcl, it looks like this –
But, and it’s a big butt. What if I need to deploy this to an environment where partitioning and compression isn’t available, desired, or required?
Currently SQLcl uses the session settings for DDL transformations to generate the DDL on the update for all the changeSets in a changeLog.
So if I look at the proposed DDL for my changeSet (via the update-sql command), we can see what would be issued on an update for our changeLog.
-- Changeset fancy_pancy_table.xml::dca1214f6f09b4a30f2f478a17ed71554e108004::(HR)-Generated CREATE TABLE "FANCY_PANCY" ( "A" VARCHAR2(20) NOT NULL ENABLE, "B" VARCHAR2(20), "C" VARCHAR2(20) NOT NULL ENABLE, CONSTRAINT "FANCY_PANCY_PK" PRIMARY KEY ("A") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING TABLESPACE "USERS" ENABLE ) PCTFREE 0 PCTUSED 40 INITRANS 1 COMPRESS BASIC STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY HASH ("C") (PARTITION "SYS_P803" SEGMENT CREATION DEFERRED COMPRESS BASIC TABLESPACE "USERS");
Now let’s turn some things off
Now if we run the update-sql command again, we can see the new proposed ddl –
And let’s actually run it, then look at our resulting table again.
We’re going to be making this more flexible going forward
In a future release, we’ll have the SET DDL apply to both:
- generate-object and generate-schema for creating changeSets
- update and updated-sql
So you can keep the storage properties COMPLETELY out of the changeSets and/or you’ll be able to ignore them when doing updates.
If you have strong opinions on this, this is my invitation to you to share those, here.
And, this will be documented in the SQLcl Docs, Liquibase chapter ASAP. Also, we have a major product underway to upgrade our ‘Quick Reference’ doc for SQLcl into a full, complete product doc set of books. This will take awhile, but I expect we’ll deliver those changes in chunks.
One of my goals is to have many more ‘how-to’ style examples for things like this, included WITH the docs.