Workflow Scripts Samples

Disclamer

The sample scripts provided on this page are for the purpose of demonstrating the features of RED and are not considered production ready, please use these scripts at your own risk and adjust them to suit your own environment as required.

Export Objects - Metadata, Create DDL, Scripts, Procedures and Templates

This example script will combine the Workflow Script feature with the command line tooling of RED to output metadata to a flat file structure useful for GIT integration, here are the steps performed:

Create an output directory structure that represents the Object Tree in RED
Run WslMetadataService.exe and extract each Object's metadata to a json file with a .metadata extension
Run a RedCli batch script that outputs:
- Procedure, Script and Template files for each Object in the Context
- Generates and outputs Create DDL as .ddl files for each Object in the Context

There are two Scripts involved:

'Export Objects' Workflow Script - This is a wrapper to execute the main script in a separate thread so that RED UI is not locked.
'wsl_workflow_export_objects_example' Powershell script

Script 1: 'Export Objects' Workflow Script

Create a new Host Script Language (HSL) of category 'Workflow' copied from the inbuilt Powershell (64-bit) HSL. Follow the user guide instructions on creating a Host Script Language of category 'Workflow'.

Create a new Host Script object in RED, name it 'Export Objects', assign the script type as the Powershell Workflow HSL you created earlier.

Enter the following code into the Host Script 'Export Objects':

Export Objects - Workflow Powershell wrapper script

Start-Process powershell -ArgumentList "-noexit -command `$host.ui.RawUI.WindowTitle = 'Exporting Metdata from ${env:WSL_META_DSN}, please wait...'; . '$WSL_SCRIPT_wsl_workflow_export_objects_example_CODE$'"
Write-Output 1
Write-Output "Export Objects operation started - You can monitor progress via the separate Powershell window"
Write-Output "NOTE: You can close the separate Powershell window once the process completes, it is left open to display the result only."
Write-Output "Logs are located in the work directory of the script and have a sequence ID of ${ENV:WSL_SEQUENCE}: `n${ENV:WSL_WORKDIR}"

Script Sourcing

The wrapper script above uses the Script Sourcing feature of RED, this is triggered by the token: $WSL_SCRIPT_wsl_workflow_export_objects_example_CODE$ which tells RED to write this script to disk and replace the token with the file path. Therefore it is important to make sure you have the exact referenced Host Script name in the token otherwise the wrapper script will fail to run. If you change the name of script 2 below then this token will need updating to match.

Script 2: ''wsl_workflow_export_example Powershell Script

Create a new Host Script object in RED, name it 'wsl_workflow_export_objects_example', assign the script type as the inbuilt Powershell (64-bit) HSL.

wsl_workflow_export_objects_example Powershell Script

# ScriptVersion:001 MinVersion:10400 MaxVersion:* TargetType:Common ModelType:* ScriptType:PowerShell (64-bit)
# --    (c) Wherescape Inc 2025. WhereScape Inc permits you to copy this Module solely for use with the RED software, and to modify this Module            -- #
# --    for the purposes of using that modified Module with the RED software, but does not permit copying or modification for any other purpose.           -- #
#==============================================================================
# Script Name      :    wsl_workflow_export_objects_example.ps1
# Description      :    Sample Workflow script to export object metadata.
#                  :    Designed to be called by a separate Workflow Script, see the user-guide section 'Workflow Script Samples' for details.
# Author           :    WhereScape Inc
#==============================================================================
# Notes / History
# 001 : Initial release.
#==============================================================================
 
Function Execute-Command ($commandTitle, $commandPath, $commandArguments)
{
    Try {
        $pinfo = New-Object System.Diagnostics.ProcessStartInfo
        $pinfo.FileName = $commandPath
        $pinfo.RedirectStandardError = $true
        $pinfo.RedirectStandardOutput = $true
        $pinfo.UseShellExecute = $false
        $pinfo.WindowStyle = 'Hidden'
        $pinfo.CreateNoWindow = $True
        $pinfo.Arguments = $commandArguments
        $p = New-Object System.Diagnostics.Process
        $p.StartInfo = $pinfo
        $p.Start() | Out-Null
        $stdout = $p.StandardOutput.ReadToEnd()
        $stderr = $p.StandardError.ReadToEnd()
        $p.WaitForExit()
        $p | Add-Member "commandTitle" $commandTitle
        $p | Add-Member "stdout" $stdout
        $p | Add-Member "stderr" $stderr
    }
    Catch {
      throw
    }
    $p
}
 
function WriteAudit($message, $type="audit", $statusCode="I") {
  $msg = "$type : $message"
  if ($statusCode -in ('E')) {
    Write-Host $msg -ForegroundColor Red
  }
  else {
    Write-Host $msg -ForegroundColor Green
  }
}
 
function Exit-Script([int]$scrResCode=0, $scrResMsg="Success") {
  $scriptExitCode = $scrResCode
  if ($scrResCode -ne 0) {
    WriteAudit $scrResMsg "result" "E"
  }
  else {
    WriteAudit $scrResMsg "result" "S"
  }
  Exit $scriptExitCode
}
 
Function Start-RedMetadataService {
    # Returns System.Diagnostics.Process - this process relies on RED Script Environment variables
 
    $StartInfo = New-Object System.Diagnostics.ProcessStartInfo -Property @{
                    FileName = "${ENV:WSL_BINDIR}WslMetadataService.exe"
                    Arguments = ' --meta-dsn "WSENV~WSL_META_DSN~" --meta-user "WSENV~WSL_META_USER~" --meta-password "WSENV~WSL_META_PWD~" --meta-schema "WSENV~WSL_META_SCHEMA~" --meta-db-type 14 --ws-user "REST_API" --log-level 4 --meta-dsn-arch WSENV~WSL_META_DSN_ARCH~ --meta-con-string "WSENV~WSL_META_CONSTRING~"'
                    UseShellExecute = $false
                    RedirectStandardOutput = $true
                    RedirectStandardError = $true
                    RedirectStandardInput = $true # WslMetadataService requires stdin to be open while in use, once closed the service will stop.
                    CreateNoWindow = $false
                }
 
    #Create process Object
    $RedMdsProcess = New-Object System.Diagnostics.Process
 
    #Assign the process parameters and custom members
    $RedMdsProcess.StartInfo = $StartInfo
    $RedMdsProcess | Add-Member "RedMdsReadyForInput" $false
    $RedMdsProcess | Add-Member "RedMdsUrl" ""
    $RedMdsProcess | Add-Member "RedMdsToken" ""
    $RedMdsProcess | Add-Member "RedMdsErrorLog" ""
    $RedMdsProcess | Add-Member "RedMdsDataLog" ""
 
    # Add wait method
    $waitScript = {
        $timeoutMinutes = @($args[0], 5 -ne $null)[0]
        $startDate = Get-Date
        while (-not $this.RedMdsReadyForInput -and ($startDate.AddMinutes($timeoutMinutes) -gt (Get-Date) -or $timeoutMinutes -eq 0) ) {       
            # Wait briefly before rechecking
            Start-Sleep -Milliseconds 100
        }
    }
    $addMemberSplat = @{
        MemberType = 'ScriptMethod'
        Name = 'WaitForRedMdsReadyForInput'
        Value = $waitScript
    }
    $RedMdsProcess | Add-Member @addMemberSplat
 
    # Add startup command method
    $startRedMdsScript = {
        if($this.Start()){
            # Register Process Object Events
            $OutEvent = Register-ObjectEvent -Action {
                $Sender.RedMdsDataLog += $Event.SourceEventArgs.Data
                # Write-Host $Event.SourceEventArgs.Data
                if($Event.SourceEventArgs.Data -match '^{"message":"http://localhost:\d\d\d\d/","success":true,"token":".*"}') {
                    $Sender.RedMdsReadyForInput = $true   
                    $tokenJson = ConvertFrom-Json $Event.SourceEventArgs.Data
                    $Sender.RedMdsToken = $tokenJson.token # set the access token
                    $Sender.RedMdsUrl = $tokenJson.message # set the URL    
                }
            } -InputObject $this -EventName OutputDataReceived
 
            $ErrEvent = Register-ObjectEvent -Action {
                if($Event.SourceEventArgs.Data -ne $null){
                    $Sender.RedMdsErrorLog += "`n"+$Event.SourceEventArgs.Data
                }
            } -InputObject $this -EventName ErrorDataReceived
 
            $ExitEvent = Register-ObjectEvent -Action {
                # Unregister events
                $OutEvent.Name, $ExitEvent.Name, $ErrEvent.Name |
                    ForEach-Object {Unregister-Event -SourceIdentifier $_}
            } -InputObject $this -EventName Exited
 
            # Begin output redirection
            $this.BeginOutputReadLine()
            $this.BeginErrorReadLine()
            $this.WaitForRedMdsReadyForInput()
        }
    }
    $addMemberSplat = @{
        MemberType = 'ScriptMethod'
        Name = 'StartRedMds'
        Value = $startRedMdsScript
    }
    $RedMdsProcess | Add-Member @addMemberSplat
 
    # Add stop command method
    $stopRedMdsScript = {
        $this.StandardInput.Close()
        $this.WaitForExit()
        $this.Dispose()
    }
    $addMemberSplat = @{
        MemberType = 'ScriptMethod'
        Name = 'StopRedMds'
        Value = $stopRedMdsScript
    }
    $RedMdsProcess | Add-Member @addMemberSplat
 
    # Start process
    $RedMdsProcess.StartRedMds()
 
    return $RedMdsProcess
 
}
 
Add-Type -Path "${ENV:WSLBINDIR}Newtonsoft.Json.dll"
 
# MAIN
# Check for the expected context file otherwise exit
if ( -not (Test-Path -path "$PSScriptRoot\wsla${ENV:WSL_SEQUENCE}.objects") ) {
  Exit-Script 2 "*** Error: Objects file doesn't exist as there were no objects in the current context. ***"
}
 
try {
 
  # initialise common vars
  $Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False
  $baseExportDir = join-path "${ENV:WSL_WORKDIR}" "EXPORT__${ENV:WSL_META_DSN}" # root directory for the export objects, change this to suit your needs
  $redMetadataServiceProcess = Start-RedMetadataService
  $redMetadataServiceUri = $redMetadataServiceProcess.RedMdsUrl
  $redMetadataServiceHeaders = @{"Authorization"= "Bearer $($redMetadataServiceProcess.RedMdsToken)"}
  $redCliLogFile = "${ENV:WSL_WORKDIR}\redCliBatchCmds_${env:WSL_SEQUENCE}.log"
    $resultExitCode = 0
  $resultMsg = "`n`n Exported Objects to: $baseExportDir `n`nLogs are located in the work directory of the script and have a sequence ID of ${ENV:WSL_SEQUENCE}: `n${ENV:WSL_WORKDIR}`n`nYou can now close this Powershell window."  
 
  # Parse json .objects file
  $contextJson = Get-Content "$PSScriptRoot\wsla${ENV:WSL_SEQUENCE}.objects" | Out-String | ConvertFrom-Json
   
  # Initial RedCli batch json hash-table
  $batchHt = [ordered]@{
     "Common Options" = @{
      "exit-on-error" = $false
      "log-level" = 4
      "output-mode" = "json"   
     }
    "Metadata Repository" = @{
      "meta-database" = ""
      "meta-dsn" = ""
      "meta-dsn-arch" = "64"
      "meta-password" = ""
      "meta-user-name" = ""
    }
  }
 
  # Get Objects in Context
  if ($contextJson.objectsByName -ne $null) {
    $htRedObjects = [ordered]@{}
    # Populates a hastable with obj-name as Key and obj-type as Value from RED formatted json
    $contextJson.objectsByName | get-member -type properties | %{ $htRedObjects[$_.name] = $contextJson.objectsByName."$($_.name)".objectType.name }
  }
  else {
    # This script can't work on Jobs and Paramters alone so exit if there are no Objects in the context
        Exit-Script 2 "*** Error: The current context doesn't conatin any Objects. ***"
  }
 
  # initialise RedCli Commands array
  $batchRedCliCmds = @() # array of redcli commands
   
  # Export Host Scripts
  $scrNames = $htRedObjects.Keys | Where-Object {$htRedObjects[$_] -eq "Host Script"}
  if ($scrNames -ne $null) {
    # create output directory
    $scrDir= Join-Path $baseExportDir "Host Script"
    if (-not (Test-Path $scrDir)) {
      $null = New-Item -ItemType Directory -Path $scrDir | Out-Null
    }
    # create cmds
    $scrCmds = @()
    $scrNames | %{
      # build RedCli cmds
      $scrCmds += 'script export --name "'+ $_ +'" -d "'+ $scrDir +'" --file-name auto_name --force'
 
      # export metadata for object via metadata service
      $uri = [URI]::EscapeUriString("$($redMetadataServiceUri)objectsExportByName/$_")
      $createResponse = Invoke-WebRequest -Uri $uri -UseBasicParsing -Method 'GET' -MaximumRedirection 0 -Headers $redMetadataServiceHeaders
      $outFile = Join-Path $scrDir "$_.metadata"
      # Convert JSON to JObject
      $jObject = [Newtonsoft.Json.Linq.JObject]::Parse($createResponse.Content)
      $prettyJson = $jObject.ToString([Newtonsoft.Json.Formatting]::Indented)
      [System.IO.File]::WriteAllLines($outFile, $prettyJson, $Utf8NoBomEncoding)       
    }
    # add RedCli cmds
    $batchRedCliCmds += $scrCmds
  }
 
  # Export Procedures
  $procNames = $htRedObjects.Keys | Where-Object {$htRedObjects[$_] -eq "Procedure"}
  if ($procNames -ne $null) {
    # create output directory
    $procDir= Join-Path $baseExportDir "Procedure"
    if (-not (Test-Path $procDir)) {
      New-Item -ItemType Directory -Path $procDir | Out-Null
    }
    # create cmds
    $procCmds = @()
    $procNames | %{
      # build RedCli cmds
      $procCmds += 'procedure export --name "'+ $_ +'" -d "'+ $procDir +'" --file-name auto_name --force'
     
      # export metadata for object via metadata service
            $uri = [URI]::EscapeUriString("$($redMetadataServiceUri)objectsExportByName/$_")
      $createResponse = Invoke-WebRequest -Uri $uri -UseBasicParsing -Method 'GET' -MaximumRedirection 0 -Headers $redMetadataServiceHeaders
      $outFile = Join-Path $procDir "$_.metadata"
      # Convert JSON to JObject
      $jObject = [Newtonsoft.Json.Linq.JObject]::Parse($createResponse.Content)
      $prettyJson = $jObject.ToString([Newtonsoft.Json.Formatting]::Indented)
      [System.IO.File]::WriteAllLines($outFile, $prettyJson, $Utf8NoBomEncoding)
    }
        # add RedCli cmds
    $batchRedCliCmds += $procCmds  
  }
 
  # Export Templates
  $temNames = $htRedObjects.Keys | Where-Object {$htRedObjects[$_] -eq "Template"}
  if ($temNames -ne $null) {
    # create output directory
    $temDir= Join-Path $baseExportDir "Template"
    if (-not (Test-Path $temDir)) {
      New-Item -ItemType Directory -Path $temDir | Out-Null
    }
    # create cmds
    $temCmds = @()
    $temNames | %{
      # build RedCli cmds
      $temCmds += 'template export --name "'+ $_ +'" -d "'+ $temDir +'" --file-name auto_name --force'  
     
      # export metadata for object via metadata service
            $uri = [URI]::EscapeUriString("$($redMetadataServiceUri)objectsExportByName/$_")
      $createResponse = Invoke-WebRequest -Uri $uri -UseBasicParsing -Method 'GET' -MaximumRedirection 0 -Headers $redMetadataServiceHeaders
      $outFile = Join-Path $temDir "$_.metadata"
      # Convert JSON to JObject
      $jObject = [Newtonsoft.Json.Linq.JObject]::Parse($createResponse.Content)
      $prettyJson = $jObject.ToString([Newtonsoft.Json.Formatting]::Indented)
      [System.IO.File]::WriteAllLines($outFile, $prettyJson, $Utf8NoBomEncoding)
    }
    $batchRedCliCmds += $temCmds   
  }
 
  # Export DDL
  $objNames = $htRedObjects.Keys | Where-Object {$htRedObjects[$_] -notin ("Host Script","Procedure","Template","Connection")}
  if ($objNames -ne $null) {
    # create cmds
    $objCmds = @()
    $objNames | %{
      # create output directory
      $objDir= Join-Path $baseExportDir $htRedObjects[$_]
      if (-not (Test-Path $objDir)) {
        New-Item -ItemType Directory -Path $objDir | Out-Null
      }
      elseif (Test-Path (Join-Path $objDir "$_.ddl")) { # currently 'Redcli object export-ddl' doesn't support overwrite so remove the file first.
        Remove-Item -path (Join-Path $objDir "$_.ddl") -force
      }
      $objCmds += 'object export-ddl --name "'+ $_ +'" -d "'+ $objDir +'"'
       
      # export metadata for object via metadata service
            $uri = [URI]::EscapeUriString("$($redMetadataServiceUri)objectsExportByName/$_")
      $createResponse = Invoke-WebRequest -Uri $uri -UseBasicParsing -Method 'GET' -MaximumRedirection 0 -Headers $redMetadataServiceHeaders
      $outFile = Join-Path $objDir "$_.metadata"
            # Convert JSON to JObject
            $jObject = [Newtonsoft.Json.Linq.JObject]::Parse($createResponse.Content)
      $prettyJson = $jObject.ToString([Newtonsoft.Json.Formatting]::Indented)
      [System.IO.File]::WriteAllLines($outFile, $prettyJson, $Utf8NoBomEncoding)
    }
    $batchRedCliCmds += $objCmds
  }
 
  # Export Connection metadata
  $conNames = $htRedObjects.Keys | Where-Object {$htRedObjects[$_] -eq "Connection"}
  if ($conNames -ne $null) {
    $conNames | %{
      # create output directory
      $conDir= Join-Path $baseExportDir $htRedObjects[$_]
      if (-not (Test-Path $conDir)) {
        New-Item -ItemType Directory -Path $conDir | Out-Null
      }
       
      # export metadata for object via metadata service
            $uri = [URI]::EscapeUriString("$($redMetadataServiceUri)objectsExportByName/$_")
      $createResponse = Invoke-WebRequest -Uri $uri -UseBasicParsing -Method 'GET' -MaximumRedirection 0 -Headers $redMetadataServiceHeaders
      $outFile = Join-Path $conDir "$_.metadata"
            # Convert JSON to JObject
            $jObject = [Newtonsoft.Json.Linq.JObject]::Parse($createResponse.Content)
      $prettyJson = $jObject.ToString([Newtonsoft.Json.Formatting]::Indented)
      [System.IO.File]::WriteAllLines($outFile, $prettyJson, $Utf8NoBomEncoding)
    }
  }
 
  Write-Progress -Activity "RedCli batch operation in progress..." -Status "Running.."
  # add the built up commands array to the RedCli batch json hash-table
  $batchHt["Commands"] = $batchRedCliCmds
  $fileRedCliBatchJson = join-path "${ENV:WSL_WORKDIR}" "redCliBatchCmds_${ENV:WSL_SEQUENCE}.json" 
 
  # Export the RedCli batch json hash-table to a json file with utf8 no-bom
  [System.IO.File]::WriteAllLines($fileRedCliBatchJson, ($batchHt | ConvertTo-Json), $Utf8NoBomEncoding)
   
  # setup and run the RedCli batch script
  $commandArguments = @"
  batch execute -f "$fileRedCliBatchJson" --meta-con-string "${ENV:WSL_META_CONSTRING}"
"@
  $cmdReturn = Execute-Command 'RedCli' "${ENV:WSL_BINDIR}RedCli.exe" $commandArguments
  $cmdReturn.stdout | Out-File -FilePath $redCliLogFile -Append
  $cmdResultJsonLines = ($cmdReturn.stdout -split "`n" | Select-String -Pattern '\{"MessageType":"Result","MessageBody":\{"Outcome":"Failure"').Line
  if ($cmdResultJsonLines.Length -ne $null -and $cmdResultJsonLines.Length -gt 0) {
    WriteAudit "There were $($cmdResultJsonLines.Length) RedCli Failures reported - please view the following log for details:" "E"
    WriteAudit "$redCliLogFile" "E"
  }
 
}
catch {
  Write-Error $_.InvocationInfo.PositionMessage
  Write-Error $_.Exception.Message
  $resultExitCode = 2
  $resultMsg = "Export Objects Failed" 
}
finally {
  try {
    # stop the metadata service
    $redMetadataServiceProcess.StopRedMds()
  }
  catch {}
}
 
# exit the script
Exit-Script $resultExitCode $resultMsg

Content

Space Tools

Workflow Scripts Samples

Disclamer

Export Objects - Metadata, Create DDL, Scripts, Procedures and Templates

Script 1: 'Export Objects' Workflow Script

Script 2: ''wsl_workflow_export_example Powershell Script