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

4.3 Quick Start

This section introduces the SQLcl Projects feature using a simple setup and list of tasks. The quick start covers:

  • How to initialize a new project (for an application with a single schema called demo) .
  • Work to be done as part of a sprint:
    • Ticket-1
      • Create the standard sample dept and emp tables
      • Load the tables with sample data
    • Ticket-2
      • Add a new column named email to the emp table
      • Add a new function named get_display_name
  • How to complete a release, and then generate and deploy an artifact.

Topics

4.3.1 Prerequisites

This Quick Start has the following prerequisites:

  • You must create two databases, DEV and PROD, with the demo schema pre-created. You need to connect as the demo user from SQLcl. 
    create user demo identified by demo;
grant resource to demo;
grant connect to demo;
grant create view to demo;
alter user demo quota unlimited on system;
  • You require two named connections in SQLcl: demo-dev and demo-prod. The following examples show how to add and manage named connections in SQLcl.
    -Add a new named connection
-Note: -savepwd is an optional parameter to save the password.
SQL> conn -savepwd -save demo-dev demo/demo@//myserver.com:1521/mypdb
 
-List current connections
SQL> connmgr list
 
-To connect to a named connection
SQL> conn -name demo-dev

4.3.2 Assumptions

This quick start assumes the following:

  • A Git project named demo-project is used.

    All work in the repository is "local" only, a remote server (such as, Github or Gitlab) is not needed for this demo.

  • The term ticket (or issue) refers to a unit of work, typically tracked in a system such as Jira.
  • SQLcl can run operating system level commands using the host command.
    • For example, host date prints the system date.
    • A shortcut for the host command is !<cmd>. For example, !date is the equivalent of host date.
    • This demo uses a lot of host commands. To avoid exiting and entering SQLcl each time, the ! shortcut is used to execute them directly within SQLcl.

4.3.3 Project Setup

Create a Git repository.

Note:

This demo uses a local Git repository. In most cases, a clone from a remote Git repository is used as a starting point. 
-Create and go into new folder
mkdir demo-project
cd demo-project
 
-Start SQLcl
sql /nolog
 
-Initialize git repo
SQL> !git init --initial-branch=main

Initialize the repository for SQLcl Projects.

-The schema that is used for this demo is called "demo".
 
SQL> project init -name demo_project -schemas demo
 
------------------------
PROJECT DETAILS
------------------------
Project name:    demo_project
Schema(s):       DEMO
Directory:       /private/tmp/demo-project
Connection name: 
Project root:    demo-project
Your project has been successfully created
  
-Root folder now looks like:
- .
- ├── .dbtools
- │   ├── filters
- │   │   └── project.filters
- │   ├── project.config.json
- │   └── project.sqlformat.xml
- ├── README.md
- ├── dist
- │   ├── README.md
- │   └── install.sql
- └── src
-     ├── README.md
-     └── database
-         ├── README.md
-         └── demo
 
-Add and commit the initial files to the repository
 
SQL> !git add --all
SQL> !git commit -m "initial"
 
[main (root-commit) 5fa6ebd] initial
 9 files changed, 188 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 README.md
 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

4.3.4 Ticket-1

In practice, most development is done as part of a ticket in a project management system, such as Jira or GitHub. In this section, ticket-1 is created and has the following requirements:

  1. Create emp and dept tables.
  2. Add the initial data.

In the following steps, you will:

  1. Create and check out a Git branch named ticket-1.

  2. Create the tables in the database schema.

  3. Export the objects from the schema to the project's src folder.

  4. Register the changes in the project's dist folder.

    • Changes are detected automatically by comparing the feature branch to the main branch.
    • Changes are created in the next release as part of ticket-1.
    • Changes are captured as Liquibase changelogs/sets.

  5. Add a custom script to load the initial data for the emp and dept tables.

  6. Commit and merge changes to complete the ticket.

Steps

  1. Connect to the the demo-dev server.
    SQL> conn -name demo-dev
Connected.
  2. Create a new branch called ticket-1.
    SQL> !git checkout -b ticket-1
Switched to a new branch 'ticket-1'
  3. Create database objects.
    SQL> create table dept( 
     deptno     number(2,0),  
     dname      varchar2(14), 
     loc        varchar2(13), 
     constraint pk_dept primary key (deptno)  
);

Table DEPT created.
 
SQL> create table emp(  
     empno      number(4,0),
     ename      varchar2(10),   
     job        varchar2(9),
     mgr        number(4,0),
     hiredate   date,   
     sal        number(7,2),
     comm       number(7,2),
     deptno     number(2,0),
     constraint pk_emp    primary key (empno),   
     constraint fk_deptno foreign key (deptno) references dept (deptno)   
);

Table EMP created.
 
SQL> create index idx_emp_deptno on emp(deptno);
Index IDX_EMP_DEPTNO created.
  4. Export all the objects to the src folder.
    SQL> project export

The current connection //myserver.com:1521/mypdb DEMO will be used for all operations
*** INDEXES ***
*** TABLES ***
*** REF_CONSTRAINTS ***
-------------------------------
TABLE                         2
REF_CONSTRAINT                1
INDEX                         1
-------------------------------
Exported 4 objects
Elapsed 20 sec

    The src folder now contains all the scripts required to recreate the objects in the demo schema.

    The changes in the src folder are used to detect changes that need to be added to the dist folder for each release.

    The folder structure looks as follows:

    └── src                                                                                                                                                                                                                                         
    ├── README.md                                                                                                                                                                                                                               
    └── database                                                                                                                                                                                                                                
        ├── README.md                                                                                                                                                                                                                           
        └── demo                                                                                                                                                                                                                                
            ├── indexes                                                                                                                                                                                                                         
            │   └── idx_emp_deptno.sql                                                                                                                                                                                                          
            ├── ref_constraints                                                                                                                                                                                                                 
            │   └── fk_deptno.sql                                                                                                                                                                                                               
            └── tables                                                                                                                                                                                                                          
                ├── dept.sql                                                                                                                                                                                                                    
                └── emp.sql

    Add and commit the newly created src files.

    Note:

    It is very important to commit all src changes because only committed changes are used to determine changes for dist/release. 
    SQL> !git add --all
SQL> !git commit -m "TICKET-1: Added dept emp tables, and get_display_name function"
 
[ticket-1 812db72] TICKET-1: Added dept emp tables, and get_display_name function
 4 files changed, 44 insertions(+)
 create mode 100644 src/database/demo/indexes/idx_emp_deptno.sql
 create mode 100644 src/database/demo/ref_constraints/fk_deptno.sql
 create mode 100644 src/database/demo/tables/dept.sql
 create mode 100644 src/database/demo/tables/emp.sql
  5. Generate the scripts and Liquibase changelogs for the current ticket.

    This happens in the background where the changes in branches/ticket-1/src are compared against branches/main/src. 

    SQL> project stage 
Stage successfully created, please review and commit your changes to repository

    The dist folder now becomes as follows:

    Note:

    • All current work is stored in the releases/next folder. After a release is ready, this folder is converted to a release number and a new or empty next folder is created.
    • Changelogs work as follows: main.changelog.xml references next/release.changelog.xml, which references ticket-1/stage.changelog.xml, which references all the files that are part of ticket-1. 
    ├── dist
│   ├── README.md
│   ├── install.sql
│   ├── releases
│   │   ├── main.changelog.xml
│   │   └── next
│   │       ├── changes
│   │       │   └── ticket-1
│   │       │       ├── code
│   │       │       │   └── _custom
│   │       │       ├── demo
│   │       │       │   ├── index
│   │       │       │   │   └── idx_emp_deptno.sql
│   │       │       │   ├── ref_constraint
│   │       │       │   │   └── fk_deptno.sql
│   │       │       │   └── table
│   │       │       │       ├── dept.sql
│   │       │       │       └── emp.sql
│   │       │       └── stage.changelog.xml
│   │       ├── code
│   │       │   └── code.changelog.xml
│   │       └── release.changelog.xml
│   └── utils
│       ├── prechecks.sql
│       └── recompile.sql
  6. Add and commit the files.

    It is recommended to review all generated files before adding to the repository.

    SQL> !git add --all
SQL> !git status
 
On branch ticket-1
Changes to be committed:
  (use "git restore --staged <file>..." to unstage)
        new file:   dist/releases/main.changelog.xml
        new file:   dist/releases/next/changes/ticket-1/demo/index/idx_emp_deptno.sql
        new file:   dist/releases/next/changes/ticket-1/demo/ref_constraint/fk_deptno.sql
        new file:   dist/releases/next/changes/ticket-1/demo/table/dept.sql
        new file:   dist/releases/next/changes/ticket-1/demo/table/emp.sql
        new file:   dist/releases/next/changes/ticket-1/stage.changelog.xml
        new file:   dist/releases/next/code/code.changelog.xml
        new file:   dist/releases/next/release.changelog.xml
        new file:   dist/utils/prechecks.sql
        new file:   dist/utils/recompile.sql

SQL> !git commit -m "TICKET-1: Added dept emp tables"
 
[ticket-1 261cceb] TICKET-1: Added dept emp tables
 10 files changed, 396 insertions(+)
 create mode 100644 dist/releases/main.changelog.xml
 create mode 100644 dist/releases/next/changes/ticket-1/demo/index/idx_emp_deptno.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/ref_constraint/fk_deptno.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/table/dept.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/table/emp.sql
 create mode 100644 dist/releases/next/changes/ticket-1/stage.changelog.xml
 create mode 100644 dist/releases/next/code/code.changelog.xml
 create mode 100644 dist/releases/next/release.changelog.xml
 create mode 100644 dist/utils/prechecks.sql
 create mode 100644 dist/utils/recompile.sql
  7. Add sample data for each table using a custom .sql file.

    Use the project stage add-custom command to generate custom files, as it auto-generates all the Liquibase-header information. 

    SQL> project stage add-custom -file-name dept-data.sql
SQL> project stage add-custom -file-name emp-data.sql

    The custom files now appear in dist under the _custom folder. 

    ├── dist
│   ├── releases
│   │   └── next
│   │       ├── changes
│   │       │   └── ticket-1
│   │       │       ├── code
│   │       │       │   └── _custom
│   │       │       │       ├── dept-data.sql
│   │       │       │       └── emp-data.sql

    The following command displays the contents of dept-data.sql. 

    SQL> !cat dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql
(For Windows, use !type .\dist\releases\next\changes\ticket-1\code\_custom\dept-data.sql)

-- liquibase formatted sql
-- changeset SqlCl:f56fcfec11f7d7cb99d74403a0c2727a765ddb99 stripComments:false logicalFilePath:ticket-1/code/_custom/dept-data.sql
-- sqlcl_snapshot dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql:null:null:custom
  8. Add the sample data for each file.

    In dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql, copy and paste the following statements to the end of the file and then save the file. 

    insert into demo.dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into demo.dept values(20, 'RESEARCH', 'DALLAS');
insert into demo.dept values(30, 'SALES', 'CHICAGO');
insert into demo.dept values(40, 'OPERATIONS', 'BOSTON');

    In dist/releases/next/changes/ticket-1/code/_custom/emp-data.sql, copy and paste the following statements to the end of the file and then save the file. 

    insert into demo.emp values( 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
insert into demo.emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
insert into demo.emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
insert into demo.emp values( 7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
insert into demo.emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into demo.emp values( 7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20);
insert into demo.emp values( 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20);
insert into demo.emp values( 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
insert into demo.emp values( 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
insert into demo.emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
insert into demo.emp values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
insert into demo.emp values( 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
insert into demo.emp values( 7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30);
insert into demo.emp values( 7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10);
  9. Run each file to ensure that the data is loaded into the table.
    SQL> @dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql
SQL> @dist/releases/next/changes/ticket-1/code/_custom/emp-data.sql
SQL> commit;
 
Commit complete.
  10. Add and commit the new custom files.
    SQL> !git add --all
SQL> !git commit -m "TICKET-1: data files"
 
[ticket-1 ac2488d] TICKET-1: data files
 3 files changed, 33 insertions(+)
 create mode 100644 dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql
 create mode 100644 dist/releases/next/changes/ticket-1/code/_custom/emp-data.sql
  11. To highlight the order of operations that have automatically been managed, enter the following command:
    SQL> !cat dist/releases/next/changes/ticket-1/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="demo/table/dept.sql" relativeToChangelogFile="true"/>
<include file="demo/table/emp.sql" relativeToChangelogFile="true"/>
<include file="demo/ref_constraint/fk_deptno.sql" relativeToChangelogFile="true"/>
<include file="demo/index/idx_emp_deptno.sql" relativeToChangelogFile="true"/>
<!--END-->
<!--BEGIN CUSTOM-->
<include file="code/_custom/dept-data.sql" relativeToChangelogFile="true"/>
<include file="code/_custom/emp-data.sql" relativeToChangelogFile="true"/>
<!--END CUSTOM-->

    Ticket-1 is now complete.

  12. Merge the changes into the main branch.

    This is done through a Merge Request or a Pull Request (depending on your Git provider). Since this is a local repository, you can only merge directly into main. 

    SQL> !git checkout main
 
Switched to branch 'main'
 
SQL> !git merge "ticket-1"
 
Updating f9660ae..ac2488d
Fast-forward
 dist/releases/main.changelog.xml                                      |   9 +++++++
 dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql        |  11 ++++++++
 dist/releases/next/changes/ticket-1/code/_custom/emp-data.sql         |  20 +++++++++++++++
 dist/releases/next/changes/ticket-1/demo/index/idx_emp_deptno.sql     |   8 ++++++
 dist/releases/next/changes/ticket-1/demo/ref_constraint/fk_deptno.sql |   9 +++++++
 dist/releases/next/changes/ticket-1/demo/table/dept.sql               |  13 ++++++++++
 dist/releases/next/changes/ticket-1/demo/table/emp.sql                |  18 +++++++++++++
 dist/releases/next/changes/ticket-1/stage.changelog.xml               |  16 ++++++++++++
 dist/releases/next/code/code.changelog.xml                            |   8 ++++++
 dist/releases/next/release.changelog.xml                              |  11 ++++++++
 dist/utils/prechecks.sql                                              |  81 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 dist/utils/recompile.sql                                              | 225 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 src/database/demo/indexes/idx_emp_deptno.sql                          |   7 ++++++
 src/database/demo/ref_constraints/fk_deptno.sql                       |   8 ++++++
 src/database/demo/tables/dept.sql                                     |  12 +++++++++
 src/database/demo/tables/emp.sql                                      |  17 +++++++++++++
 16 files changed, 473 insertions(+)
 create mode 100644 dist/releases/main.changelog.xml
 create mode 100644 dist/releases/next/changes/ticket-1/code/_custom/dept-data.sql
 create mode 100644 dist/releases/next/changes/ticket-1/code/_custom/emp-data.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/index/idx_emp_deptno.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/ref_constraint/fk_deptno.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/table/dept.sql
 create mode 100644 dist/releases/next/changes/ticket-1/demo/table/emp.sql
 create mode 100644 dist/releases/next/changes/ticket-1/stage.changelog.xml
 create mode 100644 dist/releases/next/code/code.changelog.xml
 create mode 100644 dist/releases/next/release.changelog.xml
 create mode 100644 dist/utils/prechecks.sql
 create mode 100644 dist/utils/recompile.sql
 create mode 100644 src/database/demo/indexes/idx_emp_deptno.sql
 create mode 100644 src/database/demo/ref_constraints/fk_deptno.sql
 create mode 100644 src/database/demo/tables/dept.sql
 create mode 100644 src/database/demo/tables/emp.sql

4.3.5 Ticket-2

Now that the first ticket is complete, work can start on TICKET-2. The purpose of this ticket is to highlight the automated DDL management along with soft objects (that is, re-runnable objects such as packages, views, and so on) creation.

TICKET-2 has the following requirements:

  1. Add a new column emp.email.
  2. Add a new function get_display_name.

The steps are:

  1. Create and check out a Git branch called ticket-2.
  2. Alter the emp table and add the new column in the database.
  3. Export the emp table. This will show the new table structure in the src/.../tables/emp.sql file.
  4. Run the project stage command to register the alter table statements.
  5. Create a new function, get_display_name, in the database and run project export.
  6. Rerun the project stage command to include the get_display_name function.
  7. Commit and merge changes to complete the ticket.

Steps

  1. Create the ticket-2 branch.
    SQL> !git checkout -b ticket-2
Switched to a new branch 'ticket-2'
  2. Make the table changes to the development database.
    SQL> alter table emp add email varchar2(255);
Table EMP altered.
  3. Run the project export command to get the latest version of the table.

    Note:

    Use the -o argument to only export the demo.emp table. 
    SQL> project export -o emp

The current connection //myserver.com:1521/mypdb DEMO will be used for all operations
*** INDEXES ***
*** TABLES ***
*** REF_CONSTRAINTS ***
-------------------------------
TABLE                         1
REF_CONSTRAINT                1
INDEX                         1
-------------------------------
Exported 3 objects
Elapsed 13 sec
  4. Run the git diff command to show what was changed in the src/database/demo/tables/emp.sql file.

    Note:

    Because the column name is changed, the whole file is marked as changed to add additional spacing.

    Only the logical changes are listed below to show the real differences. The diff highlights that the new column (email) is added.

    !git --no-pager diff src/database/demo/tables/emp.sql

-    deptno   number(2, 0)
+    deptno   number(2, 0),
+    email    varchar2(255 byte)
  5. Add and commit changes, which is required before running the project stage command.

    This happens in the background where the changes in branches/ticket-1/src are compared against branches/main/src. 

    SQL> !git add --all
SQL> !git commit -m "TICKET-2: Added emp.email"

[ticket-2 a3e7508] TICKET-2: Added emp.email
 1 file changed, 3 insertions(+), 2 deletions(-)
  6. Run project stage to automatically generate the alter table statement required to add the email column.
    SQL> project stage
Stage successfully created, please review andcommityour changes to repository

    The following files have been added:

    ├── dist
│   ├── releases
│   │   └── next
│   │       ├── changes
│   │       │   └── ticket-2
│   │       │       ├── code
│   │       │       │   └── _custom
│   │       │       ├── demo
│   │       │       │   └── table
│   │       │       │       └── emp.sql
│   │       │       └── stage.changelog.xml
  7. The output of dist/releases/next/changes/ticket-2/demo/table/emp.sql is shown below.

    It automatically detected the modification in the emp table and generated the correct alter statement. 

    SQL> !cat dist/releases/next/changes/ticket-2/demo/table/emp.sql

-- liquibase formatted sql
-- changeset DEMO:cffad61078f4afbd0680bfacbe818e5f089ce535 stripComments:false logicalFilePath:ticket-2/demo/table/emp.sql
-- sqlcl_snapshot src/database/demo/tables/emp.sql:1d12b2ddb0fe34a061ad35dc1990fc3d84d5a676:9c26d3ba57756671939cf399cb7e96ae0d3ac84e:alter

alter table demo.emp add (
    email varchar2(255)
)
/
  8. Add and commit changes.
    SQL> !git add --all
SQL> !git commit -m "TICKET-2: emp.email alter commands"

[ticket-2 582cd63] TICKET-2: emp.email alter commands
 3 files changed, 20 insertions(+)
 create mode 100644 dist/releases/next/changes/ticket-2/demo/table/emp.sql
 create mode 100644 dist/releases/next/changes/ticket-2/stage.changelog.xml
  9. Create a new function called get_display_name. This highlights how the source code can automatically be managed with SQLcl Projects.
    SQL> create or replace function get_display_name(p_ename in emp.ename%type)
return varchar2
as
begin
    return initcap(p_ename);
end get_display_name;
/
 
Function GET_DISPLAY_NAME compiled
  10. Similar to table changes, running a project export command detects supported object changes.
    SQL> project export
 
*** FUNCTIONS ***
*** INDEXES ***
*** TABLES ***
*** REF_CONSTRAINTS ***
-------------------------------
TABLE                         2
REF_CONSTRAINT                1
INDEX                         1
FUNCTION                      1
-------------------------------
Exported 5 objects
Elapsed 14 sec
  11. Add and commit followed by project stage to add to the release.
    SQL> !git add --all
SQL> !git commit -m "TICKET-2: added get_display_name"
 
[ticket-2 7cf60a4] TICKET-2: added get_display_name
 1 file changed, 10 insertions(+)
 create mode 100644 src/database/demo/functions/get_display_name.sql

SQL> project stage
Stage successfully created, please review and commit your changes to repository

    The function was added to the dist/next folder under a common code folder and not ticket-2.

    This is done because re-runnable code (packages, functions, and so on) may be modified multiple times within a sprint. In most cases, it only needs to be compiled once.

    SQL> !git status
On branch ticket-2
Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git restore <file>..." to discard changes in working directory)
        modified:   dist/releases/next/code/code.changelog.xml
        modified:   dist/releases/next/release.changelog.xml
 
Untracked files:
  (use "git add <file>..." to include in what will be committed)
        dist/releases/next/code/demo/
  12. Add and commit the dist changes.
    SQL> !git add --all
SQL> !git commit -m "TICKET-2: dist changes for get_display_name"
 
[ticket-2 706fdb9] TICKET-2: dist changes for get_display_name
 3 files changed, 13 insertions(+)
 create mode 100644 dist/releases/next/code/demo/function/get_display_name.sql
  13. Now that all work on ticket-2 is complete, merge back to main.
    SQL> !git checkout main
 Switched to branch 'main'
 
SQL> !git merge "ticket-2"
 
Updating d6abe9d..706fdb9
Fast-forward
 dist/releases/next/changes/ticket-2/demo/table/emp.sql     |  8 ++++++++
 dist/releases/next/changes/ticket-2/stage.changelog.xml    | 11 +++++++++++
 dist/releases/next/code/code.changelog.xml                 |  1 +
 dist/releases/next/code/demo/function/get_display_name.sql | 11 +++++++++++
 dist/releases/next/release.changelog.xml                   |  2 ++
 src/database/demo/functions/get_display_name.sql           | 10 ++++++++++
 src/database/demo/tables/emp.sql                           |  5 +++--
 7 files changed, 46 insertions(+), 2 deletions(-)
 create mode 100644 dist/releases/next/changes/ticket-2/demo/table/emp.sql
 create mode 100644 dist/releases/next/changes/ticket-2/stage.changelog.xml
 create mode 100644 dist/releases/next/code/demo/function/get_display_name.sql
 create mode 100644 src/database/demo/functions/get_display_name.sql

4.3.6 Final Steps

After the sprint is complete, the following needs to be done:

  • Generate a release (project release)
    • The release command renames the dist/next folder to the release name (in the following example, 1.0.0 ) and then generates a new (empty) dist/next folder ready for the next sprint's changes.
  • Generate an artifact (project gen-artifact)
    • gen-artifact creates a zip file that contains a single install.sql file, which points to all the different sub-folders in the dist folder. In this example, it only contains one folder (1.0.0). Over time, the artifact will contain additional releases, as this is what allows Liquibase to upgrade from any point in time.
  • Deploy the artifact (project deploy).
    • The deploy command takes an artifact and upgrades an environment to the latest version. For example, if the artifact includes versions 1-10 and the current environment is on version 8, the installer auto-detects the current state and only runs the changes related to version 9 and version 10.

4.3.6.1 Generate Release 1.0.0

Generating a release:
  • Moves the contents of dist/next to the name of the release. For example, if generating release 1.0.0, then dist/next > dist/1.0.0.
  • Create a new dist/next folder that is open for the next set of work for the next release.
  1. Generate release 1.0.0
    SQL> project release -version 1.0.0
Process completed successfully

    The dist folder now looks like below. Note the dist/next folder has been emptied and there is a new "fresh" release.changelog.xml. 

    ├── dist
│   ├── README.md
│   ├── install.sql
│   ├── releases
│   │   ├── 1.0.0
│   │   │   ├── changes
│   │   │   │   ├── main
│   │   │   │   │   ├── code
│   │   │   │   │   │   └── _custom
│   │   │   │   │   └── stage.changelog.xml
│   │   │   │   ├── ticket-1
│   │   │   │   │   ├── code
│   │   │   │   │   │   └── _custom
│   │   │   │   │   │       ├── dept-data.sql
│   │   │   │   │   │       └── emp-data.sql
│   │   │   │   │   ├── demo
│   │   │   │   │   │   ├── index
│   │   │   │   │   │   │   └── idx_emp_deptno.sql
│   │   │   │   │   │   ├── ref_constraint
│   │   │   │   │   │   │   └── fk_deptno.sql
│   │   │   │   │   │   └── table
│   │   │   │   │   │       ├── dept.sql
│   │   │   │   │   │       └── emp.sql
│   │   │   │   │   └── stage.changelog.xml
│   │   │   │   └── ticket-2
│   │   │   │       ├── code
│   │   │   │       │   └── _custom
│   │   │   │       ├── demo
│   │   │   │       │   └── table
│   │   │   │       │       └── emp.sql
│   │   │   │       └── stage.changelog.xml
│   │   │   ├── code
│   │   │   │   ├── code.changelog.xml
│   │   │   │   └── demo
│   │   │   │       └── function
│   │   │   │           └── get_display_name.sql
│   │   │   └── release.changelog.xml
│   │   ├── main.changelog.xml
│   │   └── next
│   │       └── release.changelog.xml
  2. Add the changed files and commit.
    SQL> !git add --all
SQL> !git add .
SQL> !git commit -m "release 1.0.0"
 
[main 5e1ac12] release 1.0.0
 16 files changed, 25 insertions(+), 4 deletions(-)
 create mode 100644 dist/releases/1.0/changes/main/stage.changelog.xml
 rename dist/releases/{next => 1.0}/changes/ticket-1/code/_custom/dept-data.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-1/code/_custom/emp-data.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-1/demo/index/idx_emp_deptno.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-1/demo/ref_constraint/fk_deptno.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-1/demo/table/dept.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-1/demo/table/emp.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-1/stage.changelog.xml (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-2/demo/table/emp.sql (100%)
 rename dist/releases/{next => 1.0}/changes/ticket-2/stage.changelog.xml (100%)
 rename dist/releases/{next => 1.0}/code/code.changelog.xml (100%)
 rename dist/releases/{next => 1.0}/code/demo/function/get_display_name.sql (100%)
 create mode 100644 dist/releases/1.0/release.changelog.xml
  3. (Optional) Git tag the release.
    !git tag release-1.0.0

    At this point, the dist/release/next folder is ready for the next set of work for the new release.

4.3.6.2 Generate an Artifact

An artifact contains all the releases that enable the deployment to automatically detect the instance's current version and the required releases to run through to update to the latest. SQLcl generates the artifact as a zipped file. By default, this file is not included in the Git repository because it is a binary file that can become large over time.

Note:

Artifacts must be stored in an artifact repository such as JFrog's Artifactory.

Generate the artifact as follows:

SQL> project gen-artifact -version 1.0.0
Your artifact has been generated demo_project-1.0.0.zip

The artifact directory now contains the following artifact:

├── artifact
 │   └── demo_project-1.0.0.zip

By default, the artifact directory is included in the .gitignore file and not added to the repository. It is best practice to move these to an artifact repository (for example, JFrog).

4.3.6.3 Deploy to PROD

Use the artifact that was generated in the previous step and run the deploy command on a different database. The following steps deploy the artifact on the demo-prod connection.
  1. Connect to the production server.
    SQL> connect -name demo-prod
  2. Deploy on the Prod server.
    SQL> project deploy -file artifact/demo_project-1.0.0.zip
 
Starting the migration...
Running Changeset: ticket-1/demo/table/dept.sql::1e18b33eb500d4ccf3880f65c0d06846c793c509::DEMO
Running Changeset: ticket-1/demo/table/emp.sql::1d12b2ddb0fe34a061ad35dc1990fc3d84d5a676::DEMO
Running Changeset: ticket-1/demo/ref_constraint/fk_deptno.sql::55dee6178861d12324b4e858b5465b7f4c1a4fcd::DEMO
Running Changeset: ticket-1/demo/index/idx_emp_deptno.sql::ab1f05f1b3b979156e8b2c8e1608231ae86a14c5::DEMO
Running Changeset: ticket-1/code/_custom/dept-data.sql::f56fcfec11f7d7cb99d74403a0c2727a765ddb99::SqlCl
Running Changeset: ticket-1/code/_custom/emp-data.sql::113b7d3b31e5ae089898777aaa7f556ff6f8dbf7::SqlCl
Running Changeset: ticket-2/demo/table/emp.sql::cffad61078f4afbd0680bfacbe818e5f089ce535::DEMO
Running Changeset: ../demo/function/get_display_name.sql::4c4e98c47aeddbc5ca84a31d8ab7e1c066296976::DEMO
Liquibase: Update has been successful. Rows affected: 26
Installing/updating schemas
--Starting Liquibase at 2024-11-05T17:00:18.968636 (version [local build] #0 built at 2024-08-14 18:40+0000)
 
UPDATE SUMMARY
Run:                          8
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            8
 
 
Produced logfile: sqlcl-lb-1730851216381.log
 
Operation completed successfully.
 
 Migration has been completed