Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle Change Management with SQLcl and Liquibase

Oracle Change Management with SQLcl and Liquibase

Updated for 2022, SQLcl and Liquibase is even more powerful. Automated changeLogs, updates, and rollbacks, but now also offering your favorite community edition Liquibase commands and parameters.

And, you can now take our SQLcl JARs and drop them into your existing Liquibase installs for easier, faster, more powerful automated Oracle Database change management and CI/CD.

thatjeffsmith

October 04, 2022
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. 1
    SQLcl & Liquibase
    Jeff Smith
    Distinguished Product Manager
    [email protected]
    Zachary Talke
    Product Manager
    [email protected]
    Automated Change Management for your Database

    View full-size slide

  2. 2
    Liquibase: Track, version, and deploy database changes
    • Open-Source Project (Java)
    • Supports 14+ databases, including Oracle
    • Used by millions of developers
    • Available from
    • Ant
    • CLI
    • Maven
    • Spring Boot
    • Gradle
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  3. • Capture current state to a ChangeLog (XML, JSON, YAML, SQL)
    List of objects in a schema, with their definitions
    • Create a changeSet (also represented as XML)
    For an object, register changes, e.g. add a column
    • Apply update to your database
    Liquibase processes changeset, applies to the object
    • Changeset is logged as applied
    • Oops! rollback
    3
    How Core Liquibase Works
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  4. 4 Copyright © 2022, Oracle and/or its affiliates
    Found for any controlled SCHEMA in your
    database
    • What’s been applied
    • By whom
    • When
    • Tags
    • Comments
    • Labels
    The Source of Truth: DATABASECHANGELOG

    View full-size slide

  5. 5
    ChangeSets
    Define database changes
    • Table
    createTable dropTable setTableRemarks renameTable
    • Columns
    addColumn dropColumn renameColumn modifyDataType
    • Index
    createIndex dropIndex
    • View
    createView dropView renameView
    Example courtesy of docs.Liquibase.com
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  6. 6
    ChangeLogs
    An ordered list of ChangeSets
    • preConditions
    Sanity checks must be met or no-go
    • Includes
    Attach additional changeSets
    • Contexts
    “tags’ that allow for targeted updates
    Example courtesy of docs.Liquibase.com
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  7. 7
    • Developers build the changeSets
    • changeSets offer only a subset of Oracle Database support
    • Developers build the rollback scenarios
    • Developers sort the changeLogs
    A lot of power and flexibility, but
    Object Type LB SQLcl Object LB SQLcl
    CLUSTER
    X √
    TYPE BODY
    X √
    DB LINK
    X √
    TYPE SPEC
    X √
    DIMENSION
    X √
    PUBLIC SYNONYM
    X √
    FUNCTION
    X √
    TRIGGER
    X √
    SCHEDULAR OBJECTS
    X √
    SYNONYM
    X √
    MATERIALIZED VIEW
    X √
    TABLE
    √* √
    MATERIALIZED VIEW LOG
    X √
    REF CONSTRAINT
    √ √
    OBJECT GRANTS
    X √
    SEQUENCE
    √ √
    PACKAGE BODY
    X √
    VIEW
    √ √
    PACKAGE SPEC
    X √
    INDEX
    √ √
    PROCEDURE
    X √
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  8. 8
    • Partitioned
    • Index
    Organized
    • Sharded
    • Temporary
    • Object
    • Editioned
    • External
    Tables are supported, but not all types of tables
    • Compressed
    • Encrypted
    • In-Memory
    • Blockchain
    • BFILE
    • BINARY DOUBLE
    • INTERVAL
    • SDO_GEOMETRY
    • IS_JSON/JSON
    • User Defined Types
    • Nested Tables
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  9. 9
    Oracle-enhanced Liquibase support
    • Liquibase Community Edition Open-Source Project
    • Integrated into our Oracle Database CLI
    • Add features
    • Make it easy
    • Supported by Oracle
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  10. 10 Copyright © 2022, Oracle and/or its affiliates
    NEW commands
    DDL, CTAS, INFO, LIQUIBASE,
    DATAPUMP, LOAD, REPEAT, ALIAS
    NEW tricks
    Automatic formatting, JavaScript engine,
    OCI APIs, SQL Injection detection
    SQLcl, your modern Command Line Interface
    Updated editor
    Native vi & EMACS support, or simply
    WYSIWYG editing
    Tab Complete Insight
    SQL History
    https://www.oracle.com/sqlcl

    View full-size slide

  11. 11
    Accessible from anywhere, easily
    OCI Yum Repo HomeBrew Oracle Container Registry oracle.com/sqlcl
    Oracle Database SQL Developer OCI Cloud Shell
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  12. Enhancements:
    • Automated XML changeLogs
    • Automated Updates
    • Automated Rollbacks
    • ORDS & APEX
    • 100% Oracle Database schema
    support, including data types
    • SQLDev Web Reporting
    12
    liquibase (lb) SQLcl command
    Community Edition, version 4.15
    Enhanced Community Edition for Oracle DB - FREE
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  13. 13
    • Partitioned
    • Index
    Organized
    • Sharded
    • Temporary
    • Object
    • Editioned
    • External
    All objects, all data types - SUPPORTED
    • Compressed
    • Encrypted
    • In-Memory
    • Blockchain
    • BFILE
    • BINARY DOUBLE
    • INTERVAL
    • SDO_GEOMETRY
    • IS_JSON/JSON
    • User Defined Types
    • Nested Tables
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  14. • Familiar user interface
    • Proven core technology, Liquibase
    • Run ANY SQLCL script/command
    • Less coding, more automation
    • Enhanced repository/reporting – EVERY executed
    SQL/SCRIPT is captured w/changeSet
    14
    Benefits
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  15. 15
    Automated ChangeSets & Ordering
    Based on the current session state, SQLcl builds a changeLog with
    changeSets, properly ordered. Any Oracle Database object and data type
    can be represented with XML and optional SQL changeSets.
    Automated Updates & Rollbacks
    The database (DBMS_METADATA) determines the delta SQL to get from
    state A to B on an update. SQLcl records current state before applying any
    update, which allows for automated rollbacks.
    Database Tools Support
    Built into SQLcl. Reporting via SQL Developer Web. ChangeSets for both
    ORDS REST API modules and Oracle APEX Applications.
    Copyright © 2022, Oracle and/or its affiliates
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  16. 16 Copyright © 2022, Oracle and/or its affiliates
    SQLcl Liquibase (LB) Commands/Help/Examples
    liquibase | lb
    lb help
    lb help generate-schema
    lb help generate-schema -ex

    View full-size slide

  17. 17 Copyright © 2022, Oracle and/or its affiliates
    Commands/Help/Examples

    View full-size slide

  18. 18 Copyright © 2022, Oracle and/or its affiliates
    Commands/Help/Examples

    View full-size slide

  19. 19 Copyright © 2022, Oracle and/or its affiliates
    Example Flow
    Dev App Schema
    • Build up schema in your dev
    database
    • Create local/GIT v1 folder
    • Connect with SQLcl
    lb generate-schema
    Deploy to Prod
    Connect SQLcl to prod
    cd /v1
    lb update -changelog-file
    controller.xml
    lb tag –tag v1
    Dev Updates
    • Do database work, new columns,
    stored procedures, updated views,
    etc.
    • Create v2 branch/folder
    • Connect with SQLcl
    lb generate-schema
    Deploy to Prod
    Connect SQLcl to prod
    cd /v2
    lb update -changelog-file
    controller.xml
    lb tag –tag v2

    View full-size slide

  20. 20
    PROBLEMS!
    Dev App Schema
    • Build up schema in your dev
    database
    • Create local/GIT v1 folder
    • Connect with SQLcl
    lb generate-schema
    Deploy to Prod
    Connect SQLcl to prod
    cd /vX
    lb update -changelog-file
    controller.xml
    lb tag –tag vN
    Dev Code Fix
    • Do database work, new columns,
    stored procedures, updated views,
    etc.
    • Create v2 branch/folder
    • Connect with SQLcl
    lb rollback-sql -tag vN-1
    -change-log
    controller.xml
    UNDO Changes
    • changeSet is hand coded or DB
    Change is captured via generate-
    schema
    • Deploy fix
    lb update -changelog-file
    controller.xml
    lb tag –tag vN+1
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  21. 21 Copyright © 2022, Oracle and/or its affiliates
    Generate sorted XML & SQL for an entire schema
    SQL> liquibase generate-schema -split -sql

    View full-size slide

  22. 22
    Enhance Reporting in SQL Developer Web
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  23. 23
    Enhance Reporting in SQL Developer Web
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  24. 24
    lb generate-ords-module
    -module-name 101
    lb generate-apex-object
    -applicationid 4400
    Creating changeSets
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  25. 25
    Example: SQLcl script/Load command in a changeSet
    Batch LOAD 10k records < 3 seconds
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  26. 26
    • Liquibase version 4.15 shipped with SQLcl
    • Many more commands || parameters
    FULL Liquibase support
    • Liquibase has changed command names
    Be ready to update your scripts
    • SQLcl JARs available for your existing Liquibase integrations/installs
    Upgrading to SQLcl 22.3? Things you need to know
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  27. 27
    Full Support, More commands? Example: DIFF
    SQL> lb diff -reference-url jdbc:oracle:thin:@localhost:1521/orcl
    -reference-username hrrest -reference-password oracle
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  28. 28
    Liquibase syntax has changed, but backwards compatible
    Copyright © 2022, Oracle and/or its affiliates

    View full-size slide

  29. 29 Copyright © 2022, Oracle and/or its affiliates
    SQLcl 22.3: Getting the Most From Liquibase
    Revolutionizing
    Liquibase:
    Dynamic Object
    Transformation
    Enhance Your
    Current
    Environment
    With SQLcl
    Liquibase
    Carry Over
    Current
    Projects to
    SQLcl
    Liquibase
    Maximize Your
    APEX & Oracle
    REST Data
    Service
    DevOps
    Access All the
    Great Features
    of Open-
    Source
    Liquibase

    View full-size slide

  30. 30 Copyright © 2022, Oracle and/or its affiliates
    How Dynamic Object Transformation Revolutionizes Liquibase
    Open-source Liquibase does NOT support
    automatic object updates
    Liquibase Changelog
    Contains table:
    Departments
    Columns: ID, Name,
    Manager, Location,
    Headcount,
    Retention
    Database
    Already Contains:
    Departments
    Columns: First Four
    Already Present.
    Missing
    Headcount,
    Retention
    If your changelog encounters an pre-existing object with no corresponding
    DATABASECHANGELOG entry , it will FAIL the entire update:
    Open-source Liquibase cannot alter the database and Departments table to add
    the Headcount and Retention columns

    View full-size slide

  31. 31 Copyright © 2022, Oracle and/or its affiliates
    How Dynamic Object Transformation Revolutionizes Liquibase
    SQLcl allows you to describe the desired state, and
    the system decides how to get there, dynamically.
    Liquibase Changelog
    Contains table:
    Departments
    Columns: ID, Name,
    Manager, Location,
    Headcount,
    Retention
    Database
    Already Contains:
    Departments
    Columns: First Four
    Already Present.
    Missing
    Headcount,
    Retention
    SQLcl communicates to the database via SXML the current and desired states, and
    the database responds with the required DDL to execute.
    The database and Departments table can be altered to add the Headcount and
    Retention columns

    View full-size slide

  32. 32 Copyright © 2022, Oracle and/or its affiliates
    How Dynamic Object Transformation Revolutionizes Liquibase
    How It Works
    Generate Schema
    from DEV
    Connect to Target
    environment
    Verify the SQL Run the Update
    • Capture database state, generate
    changelogs
    • Utilize SQLcl exclusive
    commands:
    o generate-schema
    o generate-object
    o generate-ords-module/generate-
    ords-schema
    SQL> lb generate-schema
    • The schema can be at any state
    • This example:
    o We have same tables from ‘Dev’, but
    they lack several columns as
    described in DEV changeLog
    SQL> lb update-sql –changelog-file [name]
    • Tip: lb update-sql to view the SQL
    for the update
    • Examine and changeLog as
    necessary
    SQL> lb update –changelog-file [name]
    • Deploy the changeLog
    • Objects are added, updated as
    necessary to reach requested
    state

    View full-size slide

  33. 33 Copyright © 2022, Oracle and/or its affiliates
    How SQLcl Liquibase Can Enhance Your Current Environment
    • SQLcl Liquibase benefits, even for your existing
    Community Edition installs
    • Liquibase can use SQLcl dynamic changelogs and
    extended functionality
    • Copy the following JARs to Liquibase lib dir:
    o dbtools-liquibase.jar (sqlcl/lib/ext)
    o dbtools-apex.jar (sqlcl/lib/ext)
    o guava-with-lf.jar (sqlcl/lib)
    o xmlparserv2_sans_jaxp_services.jar (sqlcl/lib)
    o dbtools-common.jar (sqlcl/lib)
    • Add to liquibase.properties:
    change-exec-listener-class:
    liquibase.changelog.visitor.OracleActionChangeListener

    View full-size slide

  34. 34 Copyright © 2022, Oracle and/or its affiliates
    How to Carry Over Current Projects to SQLcl Liquibase
    • SQLcl Liquibase supports
    Community Edition changelogs
    • Use existing Liquibase changeLogs
    with SQLcl
    o Including ones from non-Oracle
    databases

    View full-size slide

  35. 35 Copyright © 2022, Oracle and/or its affiliates
    All the Great Open-Source Liquibase Features Still Included
    SQLcl Liquibase
    All Open-Source Features Included
    Full Command Documentation on docs.oracle.com
    https://docs.oracle.com/en/database/oracle/sql-developer-command-line/22.3/
    Tag:
    Version1
    Tag & Rollback
    Mark the current state of
    your database with the
    tag command and use
    rollback to return the
    database to the state of
    one of your tags
    Diff
    Compare the state of two
    databases and output
    the result
    DB-DOC
    Generate database
    documentation in the
    Javadoc format on your
    database and
    changelogs
    Status
    States a list of the
    undeployed
    changesets
    And Much More...
    Snapshot, History,
    Calculate-Checksum, etc...

    View full-size slide

  36. 36 Copyright © 2022, Oracle and/or its affiliates
    Amplify Your APEX & Oracle REST Data Services (ORDS) DevOps
    • DevOps automation for your APEX
    applications
    • Build low-code apps and APIs, faster
    • Create changeLogs for APEX and
    ORDS:
    o generate-apex-object
    ▪ Create an APEX object changelog
    o generate-ords-schema
    ▪ Create a changelog for all ORDS API
    modules and children
    o generate-ords-module
    ▪ Create a changelog for an ORDS module
    and its children Oracle DB &
    APEX
    Oracle REST
    Data Services
    Capture
    Deploy
    Rollback
    And More

    View full-size slide

  37. 37 Copyright © 2022, Oracle and/or its affiliates
    Jeff Smith, Distinguished Product Manager
    [email protected]
    Twitter @thatjeffsmith
    @oraclesqldev
    @oraclerest
    Blogs https://www.thatjeffsmith.com
    Thank you!
    Download SQLcl
    www.oracle.com/sqlcl
    SQLcl Liquibase Docs
    https://bit.ly/3SQLids

    View full-size slide