6.1 Introduction

Database Application Continuous Integration and Continuous Delivery (CI/CD) represents a transformative approach in modern database management and application development. This methodology seamlessly integrates database changes into the development pipeline while ensuring swift and secure deployment to production environments. By combining the rapid feedback mechanisms of Continuous Integration with the streamlined deployment processes of Continuous Delivery, Database CI/CD addresses the critical need for agility and reliability in today's fast-paced software development landscape.

At its core, Database CI/CD aims to:

• Accelerate time-to-market for new features and updates.
• Maintain a consistently high quality of code and database schema.
• Facilitate immediate issue detection and resolution.
• Ensure that both application and database components are always in a deployable state.

This approach not only enhances development efficiency but also significantly improves the end-user experience through frequent, reliable releases.

The project command in Oracle SQLcl is a powerful tool designed to standardize database software versioning and create releasable artifacts, including APEX elements. This command supports a consistent model of development and operations, enabling repeatable builds that can be applied in a specific order.

The key features of SQLcl Projects include:

• Versioned Feature Management: Allows database developers to identify individual changes and create versioned features or issues.
• Release Artifact Creation: Facilitates rolling multiple changes into a single release artifact that can be published for use.
• Ordered Installation: Ensures that artifacts are installable in the correct sequence, taking into account previously installed components in the environment.
• Source Control Integration: Supports creating branches in source control and exporting objects to these branches. Changes are realised using branch diffs and formatted consistently, allowing for easy identification through file checksums.
• Automated Release Packaging: When multiple branches or changes are created, SQLcl Projects can wrap them into a release, automatically setting up the execution of all changesets in the correct order on a target system.

The SQLcl Projects feature represents a significant step towards streamlining database development workflows, enhancing version control, and improving the overall efficiency of database application development and deployment processes.

6.1.1 Supported Objects and Data Types

The supported database object and data types have been tested to work with the project export and project stage commands. All types not included in the following list can be added using the project stage add-custom command with any custom SQL. For more information about the add-custom command, see stage.

The database data types supported are:

• VECTORS

The database object types supported are:

• APEX_APP
• AQ_QUEUE
• AQ_QUEUE_TABLE
• CONSTRAINT
• CONTEXT
• DUALITY VIEWS
• FUNCTION
• INDEX
• JOBS
• MATERIALIZED_VIEW
• MLE ENV
• MLE MODULE
• OBJECT_GRANT
• ORDS Modules
• PACKAGE_BODY
• PACKAGE_SPEC
• PROCEDURE
• PROGRAMS
• REF_CONSTRAINT
• ROLE_GRANT
• SCHEDULES
• SEQUENCE
• SYNONYM
• SYSTEM_GRANT
• TABLE
• TRIGGER
• TYPE_BODY
• TYPE_SPEC
• USER
• VIEW

Note:

• Regarding support for annotations attached to database objects, see Support for Schema Annotations.
• Regarding support for jobs scheduler objects, see Support for Jobs.

6.1.2 Support for Schema Annotations

Oracle SQLcl Projects include partial support for schema annotations. This allows you to use the project command to export and stage database schema objects that have annotations attached to them.

The project export Command

The project export command fully supports annotations. You can export the following object types that have annotations attached to them:

• Tables
• Views
• Indexes
• Materialized views
• Domains

The project stage Command

The project stage command has partial support for annotations.

The following scenarios highlight the current limitations when using the project stage command.

Staging Table Alters with Annotation Changes

When you stage a table where the only difference between the source and the target is in the annotations, the project stage command doesn't generate any DDL or file for that table.

Staging Domain Alters with Annotations

Staging ALTER statements for domains with annotations is not supported. The generated change set contains the following placeholder comment instead of a DDL:

/* Liquibase comments here */
 
/*dbms_metadata_diff does not currently support domains, see Bug 37979876 add customer id to existing bug to escalate.
 
old ddl:
/* The old DDL would be here */
 
 
new ddl:
/* The new DDL would be here */

6.1.3 Support for Jobs

Oracle SQLcl Projects support jobs scheduler objects with some caveats.

Exporting Jobs

The project export command exports all job types except for immediate jobs. You can use the command with jobs that have either inline or named programs and schedules. The following examples illustrate how each type is handled.

• Inline Program and Schedule: In this configuration, the job definition includes embedded logic and timing. The export contains all specified attributes and may include additional attributes required for staging.

  BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
      job_name           => 'my_inline_job',
      job_type           => 'PLSQL_BLOCK',
      job_action         => '
        BEGIN
          -- your program code here
          -- e.g. call a stored procedure or do some logic
          my_schema.my_proc;
        END;',
      start_date         => SYSTIMESTAMP + INTERVAL '5' MINUTE,
      repeat_interval    => 'FREQ=HOURLY; BYMINUTE=0; BYSECOND=0',
      end_date           => NULL,
      enabled            => TRUE,
      auto_drop          => FALSE,
      comments           => 'Job with inline program and schedule'
    );
  END;
  /

• Named Program and Schedule: In this configuration, the job contains predefined program name and schedule name attributes. The export includes these attributes. Additionally, if you'd exported a job that was in an enabled state, the exported Data Definition Language (DDL) includes a DBMS_METADATA.ENABLE block at the end. When exporting jobs with named dependencies, SQLcl automatically exports the associated program and schedule objects.

  BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
      job_name      => 'my_named_job',
      program_name  => 'my_program',
      schedule_name => 'my_schedule',
      enabled       => TRUE,
      comments      => 'Job with named program and schedule'
    );
  END;
  /

Staging Jobs

The project stage command generates DDLs for jobs differently than for other objects. Because jobs require careful handling of their enabled state, the command applies a different mechanism based on a job's status in the source and target branches to generate the DDL. The following table illustrates this mechanism.

Table 6-1 Stage DDL Generation for Jobs

Job State in Main/Master Branch	Job State in Current Branch	Stage Behavior
Disabled	Disabled	Applies changes directly, without disabling or enabling the job.
Enabled	Enabled	Disables the job before changes, and re-enables it after.
Enabled	Disabled	Disables the job before changes.
Disabled	Enabled	Enables the job after changes.

Note:

The term master or main branch does not strictly refer to branches named master or main; rather, it denotes the target branch against which the current branch is being staged.

For drop operations, the project stage command generates the appropriate DDLs to remove jobs and other scheduler objects, such as programs and schedules. However, certain attributes may be commented out in the DDL blocks. You should review and uncomment these statements as needed to ensure safe and intentional deletion. Here's an example DDL for dropping a job:

-- DROPPING A JOB
/*  Uncomment drop statement after ensuring it is performing the correct actions
BEGIN
  DBMS_SCHEDULER.DROP_JOB(
   job_name => '"OUSSAMA"."MY_JOB"'
   --force => true -- Uncomment to forcibly drop the job
   --defer => true -- Either choose force or defer!
);
END;
*/

Similarly, the project stage command generates DDLs for dropping programs and schedules.

6.1.4 Database CI/CD Concepts

This section covers the concepts relevant to understanding SQLcl Projects.

• Database Continous Integration (CI) and Database Continuous Delivery (CD)
• Database Project Scaffolding
• Advanced Database Object Explorer
• Automated Database Migration Script Generation
• Building Releasable Artifacts

Database Continous Integration (CI) and Database Continuous Delivery (CD)

Database continuous integration (CI) is the process of frequently integrating database schema and logic changes into application development, aiming to provide immediate feedback on any issues. Database continuous delivery (CD) focuses on quickly and safely deploying those changes to production, ensuring that both application and database code are always in a deployable state. Together, database CI/CD reduces time-to-market and delivers consistent end-user value through frequent, high-quality releases.

Database Project Scaffolding

When initializing SQLcl Projects (using the project init command), SQLcl creates a set of files that are used to manage the creation of files and changelogs.

There are three folders in the initial setup:

• .dbtools: This folder contains the following:

  • Project filters that are used by project export to filter out objects that will be exported.
  • Project format rules that are used to format the code when it is exported.
  • Project configuration settings.

• src: This folder is where the exported objects from the database get placed. This is broken down by schema and objects types.

• dist: The release artifacts are created in this folder. This folder gets populated by the project stage command and the project release command compresses its contents to create a release artifact.

──.dbtools
│   ├── filters
│   │   └── project.filters
│   ├── project.config.json
│   └── project.sqlformat.xml
├── dist
│   └── install.sql
└── src
│   └── database
├── .gitignore
└── README.md

Advanced Database Object Explorer

One of the main features of SQLcl Projects is exporting database objects to the source control repository. The export includes the SQL needed to create the object and a "snapshot" of the object's metadata. The snapshot is stored as a comment and is prefixed with sqlcl_snapshot. It contains the following information:

• Checksum calculated on the exported code
• Object type
• Object name
• Schema name
• SXML representation of the object as exported (see DBMS_METADATA SXML)

The following path is used for exported objects:

src/database/<SCHEMA>/<OBJECT_TYPE>/<OBJECT_NAME>[.sql|.pks|.pkb]

For example, a table called doc in the HR schema, the file path is src/database/hr/tables/doc.sql.

create table hr.doc (
    id number
);
-- sqlcl_snapshot {"hash":"d992dceaa87a936e8242bdc81201f93030084ab5","type":"TABLE","name":"DOC","schemaName":"HR","sxml":"<TABLE xmlns=\"http://xmlns.abc.com/ku\" version=\"1.0\"> <SCHEMA>HR</SCHEMA> <NAME>DOC</NAME> <RELATIONAL_TABLE><COL_LIST> <COL_LIST_ITEM><NAME>ID</NAME><DATATYPE>NUMBER</DATATYPE> </COL_LIST_ITEM></COL_LIST><DEFAULT_COLLATION>USING_NLS_COMP</DEFAULT_COLLATION><PHYSICAL_PROPERTIES> <HEAP_TABLE></HEAP_TABLE></PHYSICAL_PROPERTIES> </RELATIONAL_TABLE></TABLE>"}

Automated Database Migration Script Generation

As developers add changes to the database, they are exported to the branch. Git branches are used to identify changes between units of work. Using the diffs between the branches, the changed files are identified.

Each object file has a specific checksum and, in some cases, specific SXML for the specification of the object. Using Oracle DBMS_METADATA_DIFF, the SXML files are compared and the DDL to make both objects the same is generated and added to the repository.

Building Releasable Artifacts

To ensure deployment consistency, statements need to run in a particular order. Once a statement is executed, it will never be run again (immutable). Liquibase support, which was introduced in SQLcl to allow users to create changesets for schemas or objects, can be run to install the objects into a database. SQLcl Projects builds on that support to generate changesets for each block of changes on a branch and build those together into a release.

This is achieved by taking the comparison set between branches and generating a hierarchy scaffolding of changelogs that represent the list of comparisons. Then, the comparison set is used to generate the SXML differences, which is then used to generate the DDL for the changes.

After an export of the changes, the project stage command generates the changesets for the differences from the current branch and the main branch.

–-Example
next
├── release.changelog.xml
└── changes
    └── initial-export
        ├── hr
        │   └── table
        │       └── doc.sql
        └── stage.changelog.xml

In this example, there is one table that is exported into doc.sql. This example has been simplified to explain the concept. The project stage command compares the contents of the current branch and by default compares it against the base branch of the project. The project configuration that controls the base branch is git.defaultBranch. This can be shown by using the following command:

project config -list -name git.defaultBranch

To change it, use the same config command with the -set option.

SQL> project config -list -name git.defaultBranch 
 +============================+
 | SETTING NAME      | VALUE  |
 +============================+
 | git.defaultBranch | master |
 +----------------------------+

The project stage command also has a prescribed format. When the command is run, it creates the following structure:

next/changes/<branch_name>

In the example, the branch created for the initial export of the hr.doc table is called initial-export. Each time a new branch is created and the stage command is run, there is a new folder underneath next/changes/<branch_name>.

In this example, the doc.sql file is automatically prefixed with Liquibase-formatted SQL formats and is defined as an individual changeset. doc.sql will be referenced in a changelog file.

-- Liquibase-formatted sql
-- changeset HR:d992dceaa87a936e8242bdc81201f93030084ab5 stripComments:false logicalFilePath:initial-export/hr/table/doc.sql
-- sqlcl_snapshot src/database/hr/tables/doc.sql:null:d992dceaa87a936e8242bdc81201f93030084ab5:create
 
create table hr.doc (
    id number
);

In this example, the stage.changelog.xml references the Liquibase-formatted SQL changeset.

--stage.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<!--BEGIN-->
<include file="hr/table/doc.sql" relativeToChangelogFile="true"/>
<!--END-->
<!--BEGIN CUSTOM-->
<!--END CUSTOM-->
</databaseChangeLog>

The next folder has a release.changelog.xml right at the top. This file is referenced again in the project release command.

--release.changelog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<!--BEGIN CHANGE-->
<include file="changes/initial-export/stage.changelog.xml" relativeToChangelogFile="true"/>
<!--END CHANGE-->
<!--BEGIN CODE-->
<!--END CODE-->
</databaseChangeLog>

When all the changes are created for a release, run the project release command to take all files under the next folder and move them to a "numbered release" folder.

The final structure is:

releases
├── 1.0
│   ├── code
│   │   └── code.changelog.xml
│   ├── release.changelog.xml
│   └── changes
│       └── initial-export
│           ├── _custom
│           ├── hr
│           │   └── table
│           │       └── doc.sql
│           └── stage.changelog.xml
├── main.changelog.xml
├── next
│   └── release.changelog.xml
└── util
    ├── prechecks.sql
    └── recompile.sql

To generate a re-runnable artifact, run the project gen-artifact command. This creates a file in artifacts/<projectname>-<version>.zip. By default, artifacts are not included in the Git repository (To change this, modify the .gitignore file).

The contents of this artifact include:

releases/next/release.changelog.xml
releases/1.0/changes/initial-export/stage.changelog.xml
releases/1.0/changes/initial-export/hr/table/doc.sql
releases/1.0/release.changelog.xml
releases/1.0/code/code.changelog.xml
releases/util/recompile.sql
releases/util/prechecks.sql
releases/main.changelog.xml
README.md
install.sql

The last part is deploying the artifacts into a production database. The command project deploy takes the generated artifact and runs it with SQLcl.

SQL> project deploy -file demo-1.0.zip 
Starting the migration...
Running Changeset: initial-export/hr/table/doc.sql::d992dceaa87a936e8242bdc81201f93030084ab5::HR
Table HR.DOC created.
Liquibase: Update has been successful. Rows affected: 1
Installing/updating schemas
--Starting Liquibase at 2024-09-19T09:09:02.326601 (version [local build] #0 built at 2024-08-14 18:40+0000)
Table HR.DOC created.
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
Produced logfile: sqlcl-lb-1726733337322.log
Operation completed successfully.
Migration has been completed.