Oracle Change Management with SQLcl and Liquibase

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

• 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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

• 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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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...

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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