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

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