Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

19.X SQLcl Teaser: LIQUIBASE

4 Mins Read

LIQUIBASE does ‘Source Control for your Database.’

It’s an Open Source project that allows you to capture changelogs for your database, including Oracle.

What we are doing:

  1. Extending the support for Oracle to include all schema object types
  2. Building an interface directly into SQLcl via a new LB (LIQUIBASE) command
  3. Generating the changelogs for you and managing rollbacks plus the ordering for the changelogs to avoid database object dependency errors.

A Quick Demo

I said this is a teaser, and it REALLY is a teaser – so what I’m showing you isn’t everything we’re doing. And we’ll be talking much more about this when it is available officially (19.x).

But let’s cover a very common scenario this might be useful for.

What I’m going to do is capture a schema as a ‘version 0’ or base copy of my application schema code.

Then I’m going to push that version to a new staging/test environment.

Then I’m going to make a change to my schema, and capture that changelog as a version.NEXT, and generate the SQL that would be used to update my staging/test environment.

Capture Base Version

I’m going to create a directory to hold my changelogs and master/controller file in. I then connect to my application schema in SQLcl, and run the LB command.

My schema has 692 objects in it – and I’m running this on my VBOX image on my laptop, so perf times will vary…

We’re getting XML based versions of your objects via DBMS_METADATA and writing those objects to XML files, and we’re building a controller.xml file to house the changelog for this version.

When I’m done, we can peak into our version 0 directory. Each object gets its own file, and then we have the ‘master’ controller file:

We’re handling all the hard bits for you – gen the changelogs, getting ALL the object properties defined, and ordering it correctly so it can be applied and not fail due to dependency ordering.

Put it Down Somewhere Else

So I’m going to create a new user, grant that user some privs. If I wanted to – I could include that work in the changelog as a custom SQL script, but that’s not really what I want to show today…but you can customize this stuff very easily.

So I login as that new schema, and I do a ‘lb update.’

The ‘false’ says don’t worry about the SCHEMA.

The default behavior is to apply the changelogs with the schema prefix, but I captured in HR and I’m running in LB_DEMO.


As the update runs, we get a running log to the prompt, and I can watch along in the GUI if I so desire.

Let’s Start on Version.NEXT

So now I’m ready to do some more work in my schema, and push it as a new version.

First, I’m going to create a Version.NEXT folder to hold my changelogs for that version.

So I hope into my IDE or CLI, and I run my work…I’m removing a column called TWITTER_HANDLE and I’m adding one called TWITTER, but this could be anything related to the table – a new foreign key, a check constraint (IS_JSON!) – it’s going to be picked up by DBMS_METADTA, and we’re going to create an XML based changelog for this new version.

The single object changelog, via lb gen command.

I have a single XML file in my Version.NEXT folder now (EMPLOYEES_1-TABLE.XML)

Preview the Proposed Upgrade SQL

So I have my changelog for the original version. And I have my new version. What would happen if I did an update based on my new version changelog in my staging/test environment which is at the base version?

It sees that we need to remove one column, and add one column.

If I ran the lb update command, it’d actually apply the changelog live to my connected schema.

When can we have this?

Calendar year 2019 is the closest I can say, but your biggest hint is that I’m even willing to show you anything at this point.

Even further along the calendar, we want to do cool things in the GUI (SQL Developer) around better schema compares.

I’ll be talking more about this on the Conference Circuit this year, maybe KScope Seattle and GLOC in Cleveland.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

15 Comments

  1. Peter Reply

    in sqlcl 19.4 i always get ‘SP2-0042: Unbekannter Befehl “liquibase” – Restliche Zeile wird ignoriert.’
    I have no Idea why

    • Peter

      No, it is not inluded.
      My OS is Oracle Linux 7.7 and i installed sqldeveloper via the 19.4 RPM.
      Greetings
      Peter

    • Peter

      sqlcl DEVPROJECT2/XXXXXXXXXX2@ORADEV1

      SQLcl: Release 19.4 Production auf Fr Feb 07 14:13:32 2020

      Copyright (c) 1982, 2020, Oracle. All rights reserved. Alle Rechte vorbehalten.

      Last Successful login time: Fr Feb 07 2020 14:13:34 +01:00

      Verbunden mit:
      Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 – Production
      Version 18.9.0.0.0

      SQL> help
      Um die Hilfe zu einem Thema anzuzeigen, geben Sie help ein
      Liste der verfügbaren Hilfethemen:

      /
      @
      …………….
      …………….
      INPUT
      LIST
      LOAD*
      NET*
      ………….
      ………….

    • thatjeffsmith Post Author

      It might be missing the JAR, as a workaround, go get the SQLcl.zip and extract it where you’d like it to live…or take the JARs from the lib folder and move them over into your SQLDev dist.

  2. Knut Reply

    Hi,

    with this now available, do you have a short how-to ?
    I try to follow the teaser but with no luck. the lb gen … command to generate the changeset does not show up in the help lb .
    Thanks,
    Knut

    • thatjeffsmith Post Author

      SQL> lb genobject help
      LB GENOBJECT
      Generate a change log for the object identified by supplied object_type and object_name.
      Outputs a file named       _.xml in the current working directory.

      EXAMPLE:
      lb genobject table employees
      Creates - employees_table.xml

      Database Object Types Supported:
      AQ_QUEUE AQ_QUEUE_TABLE AQ_TRANSFORM
      ASSOCIATION AUDIT AUDIT_OBJ
      CLUSTER CONSTRAINT CONTEXT
      DB_LINK DEFAULT_ROLE DIMENSION
      FGA_POLICY FUNCTION INDEX
      JOB LIBRARY MATERIALIZED_VIEW
      MATERIALIZED_VIEW_LOG OBJECT_GRANT OPERATOR
      PACKAGE_SPEC PACKAGE_BODY PROCEDURE
      PROFILE PROXY PUBLIC_SYNONYM
      REF_CONSTRAINT REFRESH_GROUP RESOURCE_COST
      RLS_CONTEXT RLS_GROUP RLS_POLICY
      RMGR_CONSUMER_GROUP RMGR_PLAN RMGR_PLAN_DIRECTIVE
      ROLE ROLLBACK_SEGMENT SEQUENCE
      SYNONYM TABLE TABLESPACE
      TRIGGER TRUSTED_DB_LINK TYPE
      TYPE_SPEC TYPE_BODY USER
      VIEW XMLSCHEMA XS_USER
      XS_ROLE XS_ROLESET XS_ROLE_GRANT
      XS_SECURITY_CLASS XS_DATA_SECURITY XS_ACL
      XS_ACL_PARAM XS_NAMESPACE RMGR_INTITIAL_CONSUMER_GROUP

      SQL>

  3. Knut Reply

    Can’t wait for it.
    Can those changes be tagged with an task-is?

    • thatjeffsmith Post Author

      yes, but manually – we didn’t implement tagging support in the SQLcl interface, but you can of course add them on your own

  4. John Flack Reply

    I wrote a post about source control for your database (DDL) in my blog in which I mentioned one of your posts about using SQL Developer to dump the DDL that you can then put under source control. You replied asking me if I had tried Liquibase, and I’m pretty sure that you were thinking about the coming feature that you posted about here.

    My answer was that I had looked at Liquibase, and I didn’t see much advantage in this over plain SQL scripts. In my new job, I have encountered an open source tool that I had never heard of before, and I wish I’d known about it sooner. It is Flyway (https://flywaydb.org/) and it is awesome. It uses my preferred SQL scripts for deployment. For Oracle databases, it even supports most SQL*Plus commands. Check it out.

  5. Tony Johnson Reply

    We are in the process of going down the LIQUIBASE path and are very interested in this feature as soon as possible. If you need a willing partner to work with you on it please contact me.

    Thanks !!!!!

  7. jp Reply

    This is awesome, is it possible to have a current release of the command line tool in order to try it before adoption ?

  8. Ben Brumm Reply

    Looks incredible Jeff! Something I’ve always wanted to learn more about is managing source control for databases and this looks like a great feature.

Write A Comment