Learn How To Read SQLcl Liquibase and XML Files

SQLcl and Liquibase
Written By Zachary Talke

Sign Ups For My July 25th SQLcl Liquibase Webinar Are Now Live!

SIGN UP HERE

I’ll be walking you through everything you need to know to get started with SQLcl Liquibase for free, show you all the fundamentals, and answer any questions.

My SQLcl Liquibase 101 Hands-on Workshop Has Also Just Been Published!

Try The Workshop For Free

Get access to a free Oracle Database to get hands on experience with SQLcl and Liquibase through the Oracle LiveLabs learning platform

SQLcl and Liquibase

With SQLcl, Oracle’s free command line tool for working with your Oracle Database, you get access to a wide range of features that help simplify your database work and make it even easier. One of these included features is SQLcl Liquibase.


SQLcl Liquibase automates all of your database object change work. This means when you create a table, procedure, constraint, or other type of database object, rather than having to manually replicate it to other users or databases and risk errors, you can automate these deployments with simple commands saving you time and energy. And if you don’t like the deployment you made or made a mistake? No worries, rollback functionality is also included. You can capture and deploy entire database schemas at once or focus on individual objects one at a time.


SQLcl Liquibase is built on top of the open-source Liquibase platform and contains all of its great features with much more extended functionality. We’ve added commands optimized for capturing:


If this is your first introduction to the tool, you can get a nice primer on it with my Learn SQLcl Liquibase in 6 Minutes article.


What Are Changelog Files?

In this article I’ll be giving you a crash course in understanding SQLcl Liquibase’s files. These files are called changelogs. We will be specifically focusing on changelogs in SQLcl Liquibase (vs. the open-source Liquibase platform). This will allow us to cover Oracle’s added Liquibase features. However, the core of what you will learn still applies to open-source Liquibase.

When you capture (save) a database object or entire schema using one of the liquibase generate commands, these objects are saved to changelog files which are generated by the command. Generally, you will have one changelog file created for each database object:

  • If you run liquibase generate-object to capture one database object, you will have one changelog file created

  • If you run liquibase generate-schema to capture an entire Oracle Database schema, many changelog files will be created with one for each object. Additionally, a controller.xml changelog file will also be created that manages the deployment of all the individual object changelogs and what order they get applied in

Within changelog files are metadata information and changeset sections. Changesets are the actual set of instructions for the database changes being applied. These changeset instructions are the specific information being read when you run the liquibase update command to populate a new database user schema or update an existing one.

Understanding XML and The XML Prolog

By default, changelogs are saved in the XML file format although other options are available. XML stands for Extensible Markup Language and is a standard markup language and file format for storing, transmitting, and reconstructing data. The format was created with the goal of being both human and machine readable.

  • This is a Nice Article on some advantages and disadvantages of XML vs. other data types

The first section of a changelog file is called the XML Prolog and it defines the XML file. This is potentially present in all XML files, not just Liquibase changelogs:

  • <?xml version="1.0" encoding="UTF-8"?>

    • This line is called the XML prolog

      • The XML prolog is optional. If it exists, it must come first in the document

    • XML documents can contain international characters, like Norwegian øæå or French êèé

    • To avoid errors, you should specify the encoding used, or save your XML files as UTF-8

    • UTF-8 is the default character encoding for XML documents

    • There are currently only 2 versions of XML: 1.0 and 1.1. Use of 1.0 is most common and 1.1 use is very rare

    • XML 1.0 operates on the principle that “everything not permitted is forbidden” while 1.1 operates on “everything not forbidden is permitted”

Understanding Changelog Metadata

The next section in a changelog file is the Changelog Metadata. This identifies and defines Liquibase XML elements:

  • xmlns=”http://www.liquibase.org/xml/ns/dbchangelog”

    • xmlns stands for XML Namespace. It declares what the default namespace for the XML document is.

      • A namespace is a uniform resource identifier (URI). Think of it as a collection of unique elements available to use

        • A URI is a way to uniquely identify “anything”

      • XML namespaces are used to provide uniquely named elements and attributes in an XML document

    • In situations where multiple namespaces are included in an XML document, you don’t need to use prefixes to differentiate elements in this namespace from elements coming from others because this is the “default” namespace.

  • xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”

    • Short for XML Namespace:XML Schema Instance

    • While it may seem that due to this naming convention this is a special type of xmlns declaration, this is not the case. xsi is simply a commonly used xmlns prefix that is convenient because of what the acronym is named after. That is why you may have seen it before across other XML documents

      • It is no different than any other xmlns declaration in the format of xmlns:[prefix]

      • Naming it something such as xmlns:d=http://www.w3.org/2001/XMLSchema-instance would functionally accomplish the same result

    • Including a prefix with a namespace declaration allows you to later distinguish which namespace you are referring to with your element you are using. This avoids name conflicts in the case that multiple namespaces in your XML document have elements with the same name

      • Example:

        • You have a namespace xmlns:d=[URL]

        • You want to use the element <table> </table> from this namespace but you have declared another namespace in this XML document that also has a table element

        • Using your prefix “d” you declared in the form of <d:table> </d:table> distinguishes that you are referencing this namespace and not the other

  • xmlns:n0=http://www.oracle.com/xml/ns/dbchangelog-ext

    • A namespace designating the n0 prefix to define XML elements for Oracle’s enhancements on top of the open-source Liquibase platform

  • xsi:schemaLocation=http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd

    • xsi stands for: XML Schema Instance

      • An XML Instance document is a file that contains information or data of what you are trying to describe with your XML schema

      • An XML Schema specifies the structure of an XML document and constraints on its content

    • The schemaLocation attribute has two values separated by a space:

      • The first is the namespace to use

      • The second is the location of the XML schema to use from that namespace

For The controller.xml File: Understanding Include File

After the Changelog Metadata section, the next changelog section will differ depending on if you are looking at a controller.xml file or individual changelog file.

In controller.xml files, the next and final section will be Include File.

  • As mentioned above, when capturing a full database schema, not only are individual changelogs created for each object, but additionally a controller.xml file is created to manage all these object changelogs

  • Include file is where the controller.xml file manages the other changelogs and specifies the order to run them in from top down.

For The Database Object Files: Understanding The Changeset Sections

For changelog files that aren’t controller.xml (aka the individual object changelogs), after the metadata section you will see a (or multiple) Changeset Section/s instead of a include file section. These changeset sections are where the actual database object instructions are located.

Let’s take a look at a changeset for a database table called “movie”:

  • <changeSet id="7aa6aac7e05e7ea3fb809cc51b0b8523a91c3ede" author="(MOVIESTREAM_MANAGER)-Generated" >

    • changeset is the changelog XML element where the information is stored for the actual database changes that are applied when a changelog is ran.

    • id is the changeset ID for a particular changelog. The ID is generated when the changelog is created.

    • author is the author of the changeset. It is automatically labeled as the database user who generated the changelog but it can be manually overwritten and set as you please.

  • <n0:createSxmlObject objectName="MOVIE" objectType="TABLE" ownerName="MOVIESTREAM_MANAGER" >

    • createSxmlObject is one of the SQLcl Liquibase changeset types. createSXMLObject creates any database object that can be stored in SXML. For a full list of SQLcl Liquibase changeset types See Here

      • SXML is a data format from the Oracle Database DBMS_METADATA package. SXML is an XML representation of object metadata that looks like a direct translation of SQL Data Definition Language (DDL) into XML.

        • Oracle supplies many packages, which are automatically installed with Oracle Database, that extend database functionality. One of these packages, DBMS_METADATA, provides a way to retrieve metadata from the database dictionary as XML or SQL Data Definition Language (DDL) statements, and to submit the XML to recreate the object.

        • This functionality is the secret weapon of SQLcl Liquibase. The DBMS_METADATA package is used to generate and execute the specialized changelogs that you see in SQLcl Liquibase compared to the open-source platform. These changelogs transform the state of the database objects dynamically and allows for pre-existing object to be altered. This is a feature not available in the Liquibase open-source client. You can find more information on dynamic object transformation with SQLcl Liquibase Here

    • objectName is the name of the database object.

    • objectType is the database object type.

    • ownerName is the database user that created the changeset.

  • <n0:source><![CDATA[]]></n0:source>

    • source is the XML element from the n0 namespace where the changeset data is stored.

    • CDATA stands for Character Data. It is where blocks of text are stored that are not parsed by the parser but are otherwise recognized as markup. For SQLcl Liquibase changelogs, this field is used to store the changeset data such as the Data Definition Language (DDL), SXML, data URL or file path.

  • <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">

    • Within the CDATA for this changelog, the TABLE element is being used as this changelog specifically creates a table. http://xmlns.oracle.com/ku is the namespace this element comes from. All the elements under this are sub elements of table, defining its properties.

Conclusion

That’s how SQLcl Liquibase changelog files work! In a future post I’ll breakdown how the Liquibase database tables work and help support Liquibase’s functionality.

If you have any questions leave them in the comments or reach out to me at zachary.talke@oracle.com

Zachary Talke
Previous

SQLcl 23.4 Is Out! Here’s Everything You Need To Know and More
Next

SQLcl 23.1 Is Out! Here’s Everything You Need To Know & More