1. User's Guide
  2. Database Application CI/CD
  3. Examples

4.4 Examples

This section provides examples that make some assumptions around connections and source schemas, and depend on having connections created.

4.4.1 Single Schema

This example consists of a single development schema and a single production schema. After it is created, the production schema includes objects from the development schema and the Liquibase changelog objects.

Overview

  1. Create a SQLcl database project.
  2. Initialize a git repository in the database project and commit initial files to the main branch.
  3. Create a new branch called base-release, run project export to export the objects from the connection hr-dev and commit the files to the branch.
  4. Run project stage to compare the branch base-release to the main branch and generate the changelogs for installing the base release.
  5. Run project release to move the development code to a versioned release called 1.0.
  6. Run project gen-artifact to create a deployable zip file of the release called <projectname>-<version>.zip.
  7. Connect to the production schema and deploy the artifact with project deploy command.

Connection Definitions

hr-dev: This is the connection to use as a development schema.

hr-prod: This is the connection to use as the production schema.

Development Connection and Objects

In the development schema, install the objects for the HR sample schema. These objects are available on Github. Clone the oracle-samples/db-samples-schemas repository.

In this example, an hr user is set up with the password oracle. 

$> git clone git@github.com:oracle-samples/db-sample-schemas.git
$> cd db-sample-schemas/human_resources
$> sql <SYSTEM_USER>/<SYSTEMPASSWORD>@<DATABASE_URL>

SQL> @hr_install
Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.
 
Enter a password for the user HR: ******
Enter a tablespace for HR [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]: YES
Old HR schema has been dropped.
******  Creating REGIONS table ....
******  Creating COUNTRIES table ....
******  Creating LOCATIONS table ....
******  Creating DEPARTMENTS table .....
******  Creating JOBS table ....
******  Creating EMPLOYEES table ....
******  Creating JOB_HISTORY table ....
******  Creating EMP_DETAILS_VIEW view ...
******  Creating indexes ...
******  Adding table column comments ...

****** Populating REGIONS table ....
****** Populating COUNTRIES table ....
****** Populating LOCATIONS table ....
****** Populating DEPARTMENTS table ....
****** Populating JOBS table ....
****** Populating EMPLOYEES table ....
****** Populating JOB_HISTORY table ....
  
Installationverification   
___________________________
Verification:              
 
Table             provided    actual
______________ ___________ _________
regions                  5         5
countries               25        25
departments             27        27
locations               23        23
employees              107       107
jobs                    19        19
job_history             10        10

Create the connection in Oracle SQL Developer extension for VSCode or Oracle SQLcl. 

SQL> connect -save hr-dev -savepwd hr/oracle@localhost/freepdb1
Name: hr-dev
Connect String: localhost/freepdb1
User: hr
Password: ******
Connected.
SQL> connect -name hr-dev
Connected.

Use a login.sql file to customise the login experience. Set up the user, database, project, git status, and current working directory.

–-login.sql
set sqlformat ansiconsole
set sqlprompt "@|red _USER|@@@|green _O_RELEASE|@@|blue   🚥  >|@"
set statusbar default editmode linecol git project cwd
set statusbar on

Steps

  1. Create a SQLcl database project.

    Choose a directory to create the project and run the project init command. 

    SQL> project init -name demo -schemas hr -makeroot
------------------------
PROJECT DETAILS
------------------------
Project name:    demo
Schema(s):       HR
Directory:       /Users/abc/sandbox/projects/demo
Connection name: hr-dev
Config Only:     false
Project root     demo
Your project has been successfully created.

    This command creates the project and the makeroot option creates a project folder.

  2. Initialize the Git repository.

    In the project directory, initialize a Git repository and commit the initial files to the main branch. The main branch is called main. On commit, it is important to use the Conventional Commits standard for commit messages so that it is useful on release to identify features, bugs, chores and other types. The code block shows Git running in SQLcl using the host command denoted by the ! character preceding the Git command. This character allows the user to run any command available on the host shell. 

    SQL>cd demo
SQL>pwd
/Users/abc/sandbox/projects/demo
SQL> !git init --initial-branch=main
Initialized empty Git repository in /Users/abc/sandbox/projects/demo/.git/
SQL> !git add .
SQL> !git commit -m "chore: initializing repository with default project files"   
[main (root-commit) 6cc2434] chore: initializing repository with default project files
 8 files changed, 145 insertions(+)
 create mode 100644 .dbtools/filters/project.filters
 create mode 100644 .dbtools/project.config.json
 create mode 100644 .dbtools/project.sqlformat.xml
 create mode 100644 .gitignore
 create mode 100644 dist/README.md
 create mode 100644 dist/install.sql
 create mode 100644 src/README.md
 create mode 100644 src/database/README.md
    In the demo folder, there are several folders and files:
    • .dbtools: Contains the configuration files for the project.
    • src: Contains the source files exported from the schema.
    • dist: Contains the generated install files and releases.
    • artifact: Contains the zip files generated for a release.

  3. Create the base-release branch and commit exported files.

    Create a branch in Git called base-release and check it out. Run project export to export the objects from the hr-dev connection. 

    SQL> !git checkout -b base-release
Switched to a new branch 'base-release'
SQL> project export
The current connection localhost/freepdb1 HR will be used for all operations.
*** INDEXES ***
*** PROCEDURES ***
*** SEQUENCES ***
*** TABLES ***
*** TRIGGERS ***
*** VIEWS ***
*** COMMENTS ***
*** REF_CONSTRAINTS ***
--------------------------------
TABLE                          6
PROCEDURE                      2
REF_CONSTRAINT                10
CREATE_USER                    1
TRIGGER                        2
SEQUENCE                       3
INDEX                         11
VIEW                           1
COMMENT                        7
--------------------------------
Exported 43 objects.
Elapsed  13 sec.
SQL>!git status
On branch base-release
Untracked files:
  (use "git add <file>..." to include in what will be committed)
    src/database/hr/
SQL>!git add src
SQL>!git status
On branch base-release
Changes to be committed:
  (use "git restore --staged <file>..." to unstage)
    new file:   src/database/hr/comments/countries.sql
...
    new file:   src/database/hr/comments/regions.sql
    new file:   src/database/hr/create_users/create_user.sql
    new file:   src/database/hr/indexes/dept_location_ix.sql
...
    new file:   src/database/hr/indexes/loc_state_province_ix.sql
    new file:   src/database/hr/procedures/add_job_history.sql
    new file:   src/database/hr/procedures/secure_dml.sql
    new file:   src/database/hr/ref_constraints/countr_reg_fk.sql
...
    new file:   src/database/hr/ref_constraints/loc_c_id_fk.sql
    new file:   src/database/hr/sequences/departments_seq.sql
    new file:   src/database/hr/sequences/employees_seq.sql
    new file:   src/database/hr/sequences/locations_seq.sql
    new file:   src/database/hr/tables/countries.sql
...
    new file:   src/database/hr/tables/regions.sql
    new file:   src/database/hr/triggers/secure_employees.sql
    new file:   src/database/hr/triggers/update_job_history.sql
    new file:   src/database/hr/views/emp_details_view.sql
 
!git commit -m "chore: base export of hr schema"
[base-release b481c41] chore: base export of hr schema
 44 files changed, 609 insertions(+)
 create mode 100644 src/database/hr/comments/countries.sql
 ...
 create mode 100644 src/database/hr/comments/regions.sql
 create mode 100644 src/database/hr/create_users/create_user.sql
 create mode 100644 src/database/hr/indexes/dept_location_ix.sql
 ...
 create mode 100644 src/database/hr/indexes/loc_state_province_ix.sql
 create mode 100644 src/database/hr/procedures/add_job_history.sql
 create mode 100644 src/database/hr/procedures/secure_dml.sql
 create mode 100644 src/database/hr/ref_constraints/countr_reg_fk.sql
 ...
 create mode 100644 src/database/hr/ref_constraints/loc_c_id_fk.sql
 create mode 100644 src/database/hr/sequences/departments_seq.sql
 ...
 create mode 100644 src/database/hr/sequences/locations_seq.sql
 create mode 100644 src/database/hr/tables/countries.sql
 ...
 create mode 100644 src/database/hr/tables/regions.sql
 create mode 100644 src/database/hr/triggers/secure_employees.sql
 create mode 100644 src/database/hr/triggers/update_job_history.sql
 create mode 100644 src/database/hr/views/emp_details_view.sql

  4. Use project stage to create changelogs for installing the base release.

    Run the project stage command to compare the base-release branch to the main branch and generate the changelogs for installing the base release. 

    SQL> project stage
Stage successfully created, please review and commit your changes to repository.
SQL> !git status
On branch base-release
Untracked files:
  (use "git add <file>..." to include in what will be committed)
    dist/releases/
SQL> !find dist/releases/next
dist/releases/next
dist/releases/next/changes
dist/releases/next/changes/base-release
dist/releases/next/changes/base-release/code
dist/releases/next/changes/base-release/code/_custom
dist/releases/next/changes/base-release/stage.changelog.xml
dist/releases/next/changes/base-release/hr
dist/releases/next/changes/base-release/hr/ref_constraint
dist/releases/next/changes/base-release/hr/ref_constraint/dept_loc_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/emp_job_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/jhist_job_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/jhist_dept_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/loc_c_id_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/countr_reg_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/emp_dept_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/jhist_emp_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/dept_mgr_fk.sql
dist/releases/next/changes/base-release/hr/ref_constraint/emp_manager_fk.sql
dist/releases/next/changes/base-release/hr/table
dist/releases/next/changes/base-release/hr/table/jobs.sql
dist/releases/next/changes/base-release/hr/table/departments.sql
dist/releases/next/changes/base-release/hr/table/regions.sql
dist/releases/next/changes/base-release/hr/table/countries.sql
dist/releases/next/changes/base-release/hr/table/employees.sql
dist/releases/next/changes/base-release/hr/table/job_history.sql
dist/releases/next/changes/base-release/hr/table/locations.sql
dist/releases/next/changes/base-release/hr/index
dist/releases/next/changes/base-release/hr/index/emp_name_ix.sql
dist/releases/next/changes/base-release/hr/index/jhist_department_ix.sql
dist/releases/next/changes/base-release/hr/index/emp_manager_ix.sql
dist/releases/next/changes/base-release/hr/index/emp_department_ix.sql
dist/releases/next/changes/base-release/hr/index/loc_country_ix.sql
dist/releases/next/changes/base-release/hr/index/jhist_employee_ix.sql
dist/releases/next/changes/base-release/hr/index/jhist_job_ix.sql
dist/releases/next/changes/base-release/hr/index/loc_city_ix.sql
dist/releases/next/changes/base-release/hr/index/dept_location_ix.sql
dist/releases/next/changes/base-release/hr/index/emp_job_ix.sql
dist/releases/next/changes/base-release/hr/index/loc_state_province_ix.sql
dist/releases/next/changes/base-release/hr/sequence
dist/releases/next/changes/base-release/hr/sequence/departments_seq.sql
dist/releases/next/changes/base-release/hr/sequence/locations_seq.sql
dist/releases/next/changes/base-release/hr/sequence/employees_seq.sql
dist/releases/next/release.changelog.xml
dist/releases/next/code
dist/releases/next/code/code.changelog.xml
dist/releases/next/code/hr
dist/releases/next/code/hr/trigger
dist/releases/next/code/hr/trigger/update_job_history.sql
dist/releases/next/code/hr/trigger/secure_employees.sql
dist/releases/next/code/hr/comment
dist/releases/next/code/hr/comment/jobs.sql
dist/releases/next/code/hr/comment/departments.sql
dist/releases/next/code/hr/comment/regions.sql
dist/releases/next/code/hr/comment/countries.sql
dist/releases/next/code/hr/comment/employees.sql
dist/releases/next/code/hr/comment/job_history.sql
dist/releases/next/code/hr/comment/locations.sql
dist/releases/next/code/hr/procedure
dist/releases/next/code/hr/procedure/secure_dml.sql
dist/releases/next/code/hr/procedure/add_job_history.sql
dist/releases/next/code/hr/view
dist/releases/next/code/hr/view/emp_details_view.sql

  5. Run project release to complete the release.

    Run the project release command to move the development code to a versioned release called 1.0. 

    SQL> project release -version 1.0 -verbose
Creating a release version 1.0 for the current body of work
 
Updated change:dist/releases/main.changelog.xml
Moved folder "dist/releases/next" to "dist/releases/1.0"
Created file:  dist/releases/next
Created change:dist/releases/next/release.changelog.xml
Created change:dist/releases/next/release.changelog.xml
Process completed successfully

    This moves all changes in the next folder to the 1.0 folder under releases. 

    –-Cropped Tree Structure
SQL> !tree
.
├── demo
│   ├── README.md
│   ├── dist
│   │   ├── install.sql
│   │   └── releases
│   │       ├── 1.0
│   │       │   ├── code
│   │       │   ├── release.changelog.xml
│   │       │   └── changes
│   │       │       └── base-release
│   │       │           ├── code
│   │       │                  ...
│   │       │           ├── hr
│   │       │                  ...
│   │       │           └── stage.changelog.xml
│   │       ├── main.changelog.xml
│   │       ├── next
│   │       │   └── release.changelog.xml
│   │       └── util
│   │           ├── prechecks.sql
│   │           └── recompile.sql
│   └── src
│       ├── README.md
│       └── database
│           ├── README.md
│           └── hr
│                 ...

  6. Run project deploy to install the artifact into a database.

    Connect to the production schema and deploy the artifact with the project deploy command. 

    connect -name production-connection
SQL> project deploy -file artifact/demo-1.0.zip -verbose
Check database connection...
Extract the file name: demo-1.0
Artifact decompression in progress...
Artifact decompressed: /var/folders/35/v7k4b96d1d9b61mm93bgjjnr0000gn/T/e6b3d0f0-92d5-4fde-b82c-37ea9ec419c58326209255710673404
Starting the installation...
  7. Check the installed schema to check the installed objects.
    SQL> connect hr/<password>
Connected.
SQL> tables
 
TABLES                     
___________________________
COUNTRIES
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
DEPARTMENTS
DOC
EMPLOYEES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS

    The migrated tables are listed along with the management tables for Liquibase.

4.4.2 Administrator Exports HR to Production Schema

In this example, the steps are the same as in Single Schema, except for the deploy (step 7), which is done in this case as an administrator. All objects that are created need to be prefaced with the schema name. This can be added on export by setting the following configuration item: 

project config set -name export.setTransform.emitSchema -value true -type BOOLEAN

Run the project deploy command. 

SQL> connect adminuser/oracle@database
SQL> drop user hr cascade;
SQL> grant connect,resource, dba to hr identified by oracle;
SQL> project deploy -file artifact/demo-1.0.zip -verbose
Check database connection...
Extract the file name: demo-1.0
Artifact decompression in progress...
Artifact decompressed: /var/folders/35/v7k4b96d1d9b61mm93bgjjnr0000gn/T/e6b3d0f0-92d5-4fde-b82c-37ea9ec419c58326209255710673404
Starting the installation...

After the installation, the main difference in objects is where the Liquibase tables exist.

SQL> show user
USER is "ABC"
SQL> tables
 
TABLES                      
____________________________
DATABASECHANGELOG_ACTIONS   
DATABASECHANGELOG           
DATABASECHANGELOGLOCK       
 
SQL> connect hr/oracle
Connected.
SQL> tables
 
TABLES                     
___________________________
COUNTRIES                  
DEPARTMENTS                
DOC                        
EMPLOYEES                  
JOB_HISTORY                
JOBS                       
LOCATIONS                  
REGIONS