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.

Transcript

1. 1 SQLcl & Liquibase Jeff Smith Distinguished Product Manager jeff.d.smith@oracle.com Zachary Talke Product Manager zachary.talke@oracle.com Automated Change Management for your Database
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
17. 17 Copyright © 2022, Oracle and/or its affiliates Commands/Help/Examples
18. 18 Copyright © 2022, Oracle and/or its affiliates Commands/Help/Examples
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
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
21. 21 Copyright © 2022, Oracle and/or its affiliates Generate sorted XML & SQL for an entire schema SQL> liquibase generate-schema -split -sql
22. 22 Enhance Reporting in SQL Developer Web Copyright © 2022, Oracle and/or its affiliates
23. 23 Enhance Reporting in SQL Developer Web Copyright © 2022, Oracle and/or its affiliates
24. 24 lb generate-ords-module -module-name 101 lb generate-apex-object -applicationid 4400 Creating changeSets Copyright © 2022, Oracle and/or its affiliates
25. 25 Example: SQLcl script/Load command in a changeSet Batch LOAD 10k records < 3 seconds Copyright © 2022, Oracle and/or its affiliates
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
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
28. 28 Liquibase syntax has changed, but backwards compatible Copyright © 2022, Oracle and/or its affiliates
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
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
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
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
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
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
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...
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
37. 37 Copyright © 2022, Oracle and/or its affiliates Jeff Smith, Distinguished Product Manager jeff.d.smith@oracle.com Twitter @thatjeffsmith @oraclesqldev @oraclerest Blogs https://www.thatjeffsmith.com Thank you! Download SQLcl www.oracle.com/sqlcl SQLcl Liquibase Docs https://bit.ly/3SQLids