Overview

This page explains how to create a custom image of the WorkflowGen database in order to add custom code or SQL scripts to execute. Keep in mind that this guide provides simple examples, so you're not limited to what is shown here. The examples will use the Linux version of the image. All code examples are in PowerShell.

Prerequisites

• A Linux machine with Docker installed. See the specific instructions for your distribution to install and configure the Docker engine. See Install Docker Engine for installation instructions. It might not have all the supported distributions.

• A Mac with Docker Desktop for Mac installed.

• A Windows 10 Pro machine with Docker Desktop for Windows installed and the Linux containers enabled. It is recommended to use the WSL 2 (Windows Subsystem for Linux) backend if available. Otherwise, use the default.

Simple example

Creating your own WorkflowGen image is as simple as creating a Dockerfile file. Create a context directory and add an empty file called Dockerfile to it:

File tree:

context-dir/
    Dockerfile

You'll then open the Dockerfile and add some code to it:

FROM advantys/workflowgen-sql:7.18.3-ubuntu-18.04

This instruction says that you want to base your image on the WorkflowGen database image. From here, you can add other instructions to add some files or scripts to execute and perform the custom initialization that you want. For example, you could add a custom SQL script and execute it:

File tree:

context-dir/
    Dockerfile
    myscript.sql
    customcode.ps1

Dockerfile:

FROM advantys/workflowgen-sql:7.18.3-ubuntu-18.04
​
COPY ./myscript.sql /usr/local/mycorporation/scripts/
COPY ./customcode.ps1 /usr/local/bin/
CMD /usr/local/bin/customcode.ps1

As you can see, PowerShell is available in the Linux container so that scripting can be done easily between Windows and Linux. If you prefer Bash scripting, you can use it just as easily by replacing the .ps1 script by your own .sh script.

customcode.ps1:

<#
.SYNOPSIS
    Custom script that creates a new database if not exists and executes
    a custom SQL script with a prepared variable.
.NOTES
    File name: customcode.ps1
#>
#requires -Version 7.0
​
Import-Module SqlServer
Import-Module /usr/local/lib/Utils.psm1 -Function "Get-EnvVar"
Import-Module /usr/local/lib/Crypto.psm1
​
$saPassword = Get-EnvVar "SA_PASSWORD"
$aSecret = Get-EnvVar "MYCORPORATION_SECRET"
​
$myCustomDatabaseName = "MYDB"
$myDatabasePath = Join-Path "/" "var" "opt" "mssql" "data" "$myCustomDatabaseName.mdf"
$myScriptPath = Join-Path "/" "usr" "local" "mycorporation" "scripts" "myscript.sql"
$commonArgs = @{
    ServerInstance = "localhost"
    Username = "sa"
    Password = $saPassword
}
​
# MYDB not created
if (-not (Test-Path $myDatabasePath)) {
    $secretSalt = Get-Salt # from Crypto.psm1
    $secretPassHash = Get-PasswordHash ($salt + $aSecret)
​
    Invoke-Sqlcmd "CREATE DATABASE [`$(DATABASE_NAME)] CONTAINMENT = PARTIAL" `
        -Variable ,"DATABASE_NAME=$myCustomDatabaseName" `
        @commonArgs
    Invoke-Sqlcmd -InputFile $myScriptPath `
        -Variable ,"A_SECRET=$secretPassHash" `
        @commonArgs
}
​
if ($args.Count -gt 0 {
    Invoke-Expression $args
} else {
    # Restart the SQL Server process so that the logs
    # are written in the standard output file
    Stop-Process -Name sqlservr -Force
    Start-Process -FilePath /opt/mssql/bin/sqlservr -Wait
}

This script looks like a lot but is in fact simple. It begins by importing the SqlServer, Utils, and Crypto libraries, then gets some information from the environment and initializes variables based on that. Afterward, it checks if the database has already been created by checking for the existence of an .mdf file before hashing and salting a custom secret, creating a custom database and executing a script with a prepared variable. If there are no arguments passed to the script, it restarts the SQL Server process so that its logs are written to the standard output. If there are some arguments, it executes what is passed.

The condition to check if the database is already created is there because the script will be executed after each container restart and run. Therefore, the state files including the .mdf and .ldf files of all the databases inside SQL Server will already be there. There will be no need to re-create the database.

The last part is a general good practice in a Docker container. For example, if you're debugging the container and only want to prompt a PowerShell command line after the start sequence, you would pass powershell as an argument to the run command like so:

 docker container run -it `
     # ...
     mycorporation/workflowgen-sql:7.18.3-ubuntu-18.04 /usr/local/bin/customcode.ps1 pwsh

 docker container run -it \
     # ...
     mycorporation/workflowgen-sql:7.18.3-ubuntu-18.04 /usr/local/bin/customcode.ps1 pwsh

The powershell argument will be executed by the Invoke-Expression command and a new PowerShell command prompt will be displayed.

Now, all you have to do is build the container:

Set-Location path/to/context-dir
docker container build `
    -t mycorporation/workflowgen-sql:7.18.3-ubuntu-18.04 `
    .

cd path/to/context-dir
docker container build \
    -t mycorporation/workflowgen-sql:7.18.3-ubuntu-18.04 \
    .

Scripts available in the image

Advantys has developed some PowerShell scripts and modules to take care of some functionalities. Here's a list of those scripts with their descriptions:

• docker-entrypoint.ps1: This is the main script that gets executed when you run a container. It handles the parsing of environment variables, initialization of the WorkflowGen database, etc.

  • Windows path: C:\docker-entrypoint.ps1

  • Linux path: /usr/local/bin/docker-entrypoint.ps1

• C:\monitor-database.ps1 (Windows version only): This script handles the monitoring of the database process, as well as gathering the container logs and redirecting them to the standard output. The Linux version doesn't need this script because the SQL Server process runs in the foreground and writes its logs in the standard output.

• /usr/local/bin/healthcheck.ps1 (Linux version only): This script handles the periodic check that indicates if the database is working properly or not. This is defined in the Dockerfile and is handled by the Docker engine. The Windows version also has a healthcheck defined. It's a simple command that doesn't need its own script. In Kubernetes, it's ignored, so you must provide a liveness probe. See the Configure Liveness, Readiness and Startup Probes Kubernetes article for more information.

• *.psm1: Various developed PowerShell modules available in the image.

  • Windows path: C:\

  • Linux path: /usr/local/lib/

• /usr/local/bin/set-state.ps1 (Linux version only): Sets the state of the container, such as bringing the WorkflowGen database offline or online.

  • Windows path: C:\

  • Linux path: /usr/local/bin/