Versioning your Oracle Database with SQLcl and Liquibase

0ed10d1154c696886ca483fe827cb299?s=47 thatjeffsmith
February 05, 2020

Versioning your Oracle Database with SQLcl and Liquibase

Overview of Liquibase, and how we enhanced it in Oracle SQLcl, your modern Oracle Database command line interface.

SQLcl will generate the changelogs for you, order them properly, and automatically generate rollback scnearios for you - all in your familiar query tool.

And, it's all free!

The 5 minute video demonstration can be viewed here
https://www.youtube.com/watch?v=7A-anQoi6tI&feature=youtu.be

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

February 05, 2020
Tweet

Transcript

  1. Versioning your Oracle Database Jeff Smith, Master Product Manager With

    SQLcl and Liquibase 1
  2. • Your application schema(s) • Tables • Views • Stored

    Procedures • Everything in a schema! Versioning what, exactly?
  3. v3..vN Upgrade v2 Deploy v1 Back to v2 ??? Rollback!

    Database Deployments & Upgrades
  4. • Home grown solution? • Bespoke scripts? • Liquibase |

    Flyway? • Something else? How are you handling upgrades today?
  5. • Less coding • Familiar, friendly interface • Database object

    dependency ordering is taken care of • Updates automatically generate rollback scenarios Our solution: leverage Liquibase + EASY For FREE
  6. • Open source project • Deploys versions of your database

    schema/user • Supports popular DBMS solutions, including Oracle • Installs, upgrades, or rollbacks Liquibase – Source Control for your DB
  7. • XML • JSON • YAML • SQL Liquibase -

    changesets
  8. Controller/master => many changelogs

  9. “Liquibase uses the DATABASECHANGELOG table to track which changeSets have

    been ran.” DATABASECHANGELOG TABLE
  10. • Executable via command line, • Apache Ant, • Apache

    Maven, • servlet container, • Spring Framework. Liquibase – How it runs
  11. • …YOU code the upgrade AND rollback changelogs • YOU

    worry about the order of changelogs to be applied A lot of power, but…
  12. SQL Developer Family of Solutions 1 2 Time Tested Launched

    in 2005 An Industry Standard 5M+ Oracle DBAs & Developers Always Improving Quarterly Releases 4
  13. • Replaced the Oracle library for Liquibase • Mounted it

    as ‘lb’ command in SQLcl What we did SQLcl: a modern CLI for your Oracle Database
  14. • 25MB download, only requires a JRE • Supports SQL*Plus

    commands, adds many more • 11gR2 & higher • Avail in OCI YUM, OTN, DB $ORCLE_HOMEs SQLcl: manage & query your Oracle DB
  15. • Leverages DBMS_METADATA to gen XML changelogs • Operates as

    connected USER, writes to $SQL_PATH • Parses XML, determines object creation order How it works
  16. • GEN schema/object as LB changelogs • Automatically sorted for

    creation • Easily rollback to previous state • Preview UPDATE SQL SQLcl and the LB command
  17. Help & Docs Examples

  18. 1. Create a directory to hold v1 changelogs 2. Login

    as app schema in DEV 3. >lb genschema 4. Login as app schema in PROD/TEST 5. >lb update <controller.xml> false Generate and Update
  19. From DDL to Changelog… Bespoke Upgrade Script

  20. Generating changelog for our object Preview the DB work to

    be applied via ‘lb updatesql’ command
  21. Deploy a changelog

  22. • We handle the code for rollback • DESC >

    INFO for terse screenshots ☺ Oops…ROLLBACK!
  23. • GEN APEX • NUKE IT • LB UPDATE •

    Try running an APP In-house scenarios (ORDS + APEX) Spoiler: It worked.
  24. Already use Liquibase? Just add our Jar Add to your

    LB classpath
  25. Bonus: GEN APEX Apps and REST Services

  26. • Shuffle in changelogs at the page level vs application

    level • Granular updates vs completely rebuilding an App DEEP APEX Support Available
  27. • LB command added for version 19.2.1 of SQLcl •

    Follow @oraclesqlcl for product news and updates Availability - NOW
  28. Live Demo: The Movie! YouTube 5 Minute Demo

  29. Thank you! Master Product Manager @thatjeffsmith jeff.d.smith@oracle.com https://www.thatjeffsmith Jeff Smith