Modern developer Issue 4: My SQL toolbox

SQL is such a basic and useful language but the underlying thinking is non-intuitive when you come from imperative languages like Java, Ruby and similar.
SQL is centered around sets and operations on them. The straight forward solution might not be the best one.

Limit

Let’s say we need the maximum value in a certain set. Easy:

select max(value) from table

But what if we need the row with the maximum value? Just adding the other columns won’t work since aggregations only work with other aggregations and group bys. Joining with the same table may be straight forward but better is to not do any joins:

select * from (select * from table order by value desc) where rownum<=1

Group by and having

Even duplicate values can be found without joining:

select value from table group by value having count(*) > 1

Grouping is a powerful operation in SQL land:

select max(value), TO_CHAR(time, 'YYYY-MM') from table group by TO_CHAR(time, 'YYYY-MM')

Finding us the maximum value in each month.

Mapping with outer joins

SQL is also good for calculations. Say we have one table with values and one with a mapping like a precalculated log table. Joining both gets the log of each of your values:

select t.value, log.y from table t left outer join log_table log on t.value=log.x

Simple calculations

We can even use a linear interpolation between two values. Say we have only the function values stored for integers but we values between them and these values between them can be interpolated linearly.

select t.value, (t.value-floor(t.value))*f.y + (ceil(t.value)-t.value)*g.y from table t left outer join function_table f on floor(t.value)=f.x left outer join function_table g on ceil(t.value)=g.x

When you need to calculate for large sets of values and insert them into another table it might be better to calculate in SQL and insert in one step without all the conversion and wrapping stuff present in programming languages.

Conditions

Another often overlooked feature is to use a condition:

select case when MOD(t.value, 2) = 0 then 'divisible by 2' else 'not divisible by 2' end from table t

These handful operations are my basic toolbox when working with SQL, almost all queries I need can be formulated with them.

Dates and timestamps

One last reminder: when you work with time always specify the wanted time zone in your query.

Modern developer #3: Framework independent JavaScript architecture

Usually small JavaScript projects start with simple wiring of callbacks onto DOM elements. This works fine when it the project is in its initial state. But in a short time it gets out of hand. Now we have spaghetti wiring and callback hell. Often at this point we try to get help by looking at adopting a framework, hoping to that its coded best practices draw us out of the mud. But now our project is tied to the new framework.
In search of another, framework independent way I stumbled upon scalable architecture by Nicholas Zakas.
It starts by defining modules as independent units. This means:

  • separate JavaScript and DOM elements from the rest of the application
  • Modules must not reference other modules
  • Modules may not register callbacks or even reference DOM elements outside their DOM tree
  • To communicate with the outside world, modules can only call the sandbox

The sandbox is a central hub. We use a pub/sub system:

sandbox.publish({type: 'event_type', data: {}});

sandbox.subscribe('event_type', this.callback.bind(this));

Besides being an event bus, the sandbox is responsible for access control and provides the modules with a consistent interface.
Modules are started and stopped (in case of misbehaving) in the application core. You could also use the core as an anti corruption layer for third party libraries.
This architecture gives a frame for implementation. But implementing it raises other questions:

  • how do the modules update their state?
  • where do we call the backend?

Handling state

A global model would reside or be referenced by the application core. In addition every module has its own model. Updates are always done in application event handlers, not directly in the DOM event handlers.
Let me illustrate. Say we have a module with keeps track of selected entries:

function Module(sandbox) {
  this.sandbox = sandbox;
  this.selectedEntries = [];
}

Traditionally our DOM event handler would update our model:

button.on('click', function(e) {
  this.selectedEntries.push(entry);
});

A better way would be to publish an application event, subscribe the module to this event and handle it in the application event handler:

this.sandbox.subscribe('entry_selected', this.entrySelected.bind(this));

Module.prototype.entrySelected = function(event) {
  this.selectedEntries.push(event.entry);
};

button.on('click', function(e) {
  this.sandbox.publish({type: 'entry_selected', entry: entry});
});

Other modules can now listen on selecting entries. The module itself does not need to know who selected the entry. All the internal communication of selection is visible. This makes it possible to use event sourcing.

Calling the backend

No module should directly call the backend. For this a special module called extension is used. Extensions encapsulate cross cutting concerns and shield communication with other systems.

Summary

This architecture keeps UI parts together with their corresponding code, flattens callbacks and centralizes the communication with the help of application events and encapsulates outside communication. On top of that it is simple and small.

Modern developer Issue #2: RPM like deployment on Windows

Deployment is a crucial step in every development project. Without shipping no one would ever see our work (and we get no feedback if our work is good).

drawer

Often we fear deploying to production because of the effort involved and the errors we make. Questions like ‘what if we forget a step?’ or ‘what if the new version we install is buggy?’ buzz in our mind.

fears

Deployment needs to be a non-event, a habit. For this we need to automate every step besides the first one: clicking a button to start deployment.

deploy

On Linux we have wonderful tools for this but what if you are stuck with deploying to Windows?

brave

Fear not, brave developer! Even on Windows we can use a package manager to install and rollback buggy versions. Let me introduce you to chocolatey.

choco

Chocolatey (or choco in short) uses the common NuGet package format. Formerly developed for the .net platform we can use it for other platforms, too. In our following example we use a simple Java application which we install as a service and as a task.
Setting up we need a directory structure for the package like this:

folders

We need to create two files: one which specifies our package (my_project.nuspec) and one script which holds the deployment steps (chocolateyinstall.ps1). The specification file holds things like the package name, the package version (which can be overwritten when building the package), some pointers to project, source and license URLs. We can configure files and directories which will be copied to the package: in our example we use a directory containing our archives (aptly named archives) and a directory containing the installation steps (named tools). Here is a simple example:

<?xml version="1.0" encoding="utf-8"?>
<!-- Do not remove this test for UTF-8: if “Ω” doesn’t appear as greek uppercase omega letter enclosed in quotation marks, you should use an editor that supports UTF-8, not this one. -->
<package xmlns="http://schemas.microsoft.com/packaging/2015/06/nuspec.xsd">
  <metadata>
    <id>my_project</id>
    <title>My Project (Install)</title>
    <version>0.1</version>
    <authors>Me</authors>
    <owners>Me</owners>
    <summary></summary>
    <description>Just an example</description>
    <projectUrl>http://localhost/my_project</projectUrl>
    <packageSourceUrl>http://localhost/git</packageSourceUrl>
    <tags>example</tags>
    <copyright>My company</copyright>
    <licenseUrl>http://localhost/license</licenseUrl>
    <requireLicenseAcceptance>false</requireLicenseAcceptance>
    <releaseNotes></releaseNotes>
  </metadata>
  <files>
    <file src="tools\**" target="tools" />
    <file src="archives\**" target="archives" />
  </files>
</package>

This file tells choco how to build the packages and what to include. For the deployment process we need a script file written in Powershell.

powershell

A Powershell primer

Powershell is not as bad as you might think. Let’s take a look at some basic Powershell syntax.

Variables

Variables are started with a $ sign. As in many other languages ‘=’ is used for assignments.

$ErrorActionPreference = 'Stop'

Strings

Strings can be used with single (‘) and double quotes (“).

$serviceName = 'My Project'
$installDir = "c:\examples"

In double quoted strings we can interpolate by using a $ directly or with curly braces.

$packageDir = "$installDir\my_project"
$packageDir = "${installDir}\my_project"

For escaping double quotes inside a double quoting string we need back ticks (`)

"schtasks /end /f /tn `"${serviceName}`" "

Multiline strings are enclosed by @”

$cmdcontent = @"
cd /d ${packageDir}
java -jar ${packageName}.jar >> output.log 2>&1
"@

Method calls

Calling methods looks a mixture of command line calls with uppercase names.

Write-Host "Stopping and deleting current version of ${packageName}"
Get-Date -format yyyyddMMhhmm
Copy-Item $installFile $packageDir

Some helpful methods are:

  • Write-Host or echo: for writing to the console
  • Get-Date: getting the current time
  • Split-Path: returning the specified part of a path
  • Join-Path: concatenating a path with a specified part
  • Start-Sleep: pause n seconds
  • Start-ChocolateyProcessAsAdmin: starting an elevated command
  • Get-Service: retrieving a Windows service
  • Remove-Item: deleting a file or directory
  • Test-Path: testing for existence of a path
  • New-Item: creating a file or directory
  • Copy-Item: copying a file or directory
  • Set-Content: creating a file with the specified contents
  • Out-Null: swallowing output
  • Resolve-Path: display the path after resolving wildcards

The pipe (|) can be used to redirect output.

Conditions

Conditions can be evaluated with if:

if ($(Get-Service "$serviceName" -ErrorAction SilentlyContinue).Status -eq "Running") {
}

-eq is used for testing equality. -ne for difference.

Deploying with Powershell

For installing our package we need to create the target directories and copy our archives:

$packageName = 'myproject'
$installDir = "c:\examples"
$packageDir = "$installDir\my_project"

Write-Host "Making sure $installDir is in place"
if (!(Test-Path -path $installDir)) {New-Item $installDir -Type Directory  | Out-Null}

Write-Host "Making sure $packageDir is in place"
if (!(Test-Path -path $packageDir)) {New-Item $packageDir -Type Directory  | Out-Null}

Write-Host "Installing ${packageName} to ${packageDir}"
Copy-Item $installFile $packageDir

When reinstalling we first need to delete existing versions:

$installDir = "c:\examples"
$packageDir = "$installDir\my_project"

if (Test-Path -path $packageDir) {
  Remove-Item -recurse $(Join-Path $packageDir "\*") -exclude *.conf, *-bak*, *-old*
}

Now we get to the meat creating a Windows service.

$installDir = "c:\examples"
$packageName = 'myproject'
$serviceName = 'My Project'
$packageDir = "$installDir\my_project"
$cmdFile = "$packageDir\$packageName.cmd"

if (!(Test-Path ($cmdFile)))
{
    $cmdcontent = @"
cd /d ${packageDir}
java -jar ${packageName}.jar >> output.log 2>&1
"@
    echo "Dropping a ${packageName}.cmd file"
    Set-Content $cmdFile $cmdcontent -Encoding ASCII -Force
}

if (!(Get-Service "${serviceName}" -ErrorAction SilentlyContinue))
{
  echo "No ${serviceName} Service detected"
  echo "Installing ${serviceName} Service"
  Start-ChocolateyProcessAsAdmin "install `"${serviceName}`" ${cmdFile}" nssm
}

Start-ChocolateyProcessAsAdmin "set `"${serviceName}`" Start SERVICE_DEMAND_START" nssm

First we need to create a command (.cmd) file which starts our java application. Installing a service calling this command file is done via a helper called nssm. We set it to starting manual because we want to start and stop it periodically with the help of a task.

For enabling a reinstall we first stop an existing service.

$installDir = "c:\examples"
$serviceName = 'My Project'
$packageDir = "$installDir\my_project"

if (Test-Path -path $packageDir) {
  Write-Host $(Get-Service "$serviceName" -ErrorAction SilentlyContinue).Status

  if ($(Get-Service "$serviceName" -ErrorAction SilentlyContinue).Status -eq "Running") {
    Start-ChocolateyProcessAsAdmin "Stop-Service `"${serviceName}`""
    Start-Sleep 2
  }
}

Next we install a task with help of the build in schtasks command.

$serviceName = 'My Project'
$installDir = "c:\examples"
$packageDir = "$installDir\my_project"
$cmdFile = "$packageDir\$packageName.cmd"

echo "Installing ${serviceName} Task"
Start-ChocolateyProcessAsAdmin "schtasks /create /f /ru system /sc hourly /st 00:30 /tn `"${serviceName}`" /tr  `"$cmdFile`""

Stopping and deleting the task enables us to reinstall.

$packageName = 'myproject'
$serviceName = 'My Project'
$installDir = "c:\examples"
$packageDir = "$installDir\my_project"

if (Test-Path -path $packageDir) {
  Write-Host "Stopping and deleting current version of ${packageName}"
  Start-ChocolateyProcessAsAdmin "schtasks /delete /f /tn `"${serviceName}`" "
  Start-Sleep 2
  Start-ChocolateyProcessAsAdmin "schtasks /end /f /tn `"${serviceName}`" "
  Remove-Item -recurse $(Join-Path $packageDir "\*") -exclude *.conf, *-bak*, *-old*
}

tl;dr

Putting it all together looks like this:

$ErrorActionPreference = 'Stop'; # stop on all errors

$packageName = 'myproject'
$serviceName = 'My Project'
$installDir = "c:\examples"
$packageDir = "$installDir\my_project"
$cmdFile = "$packageDir\$packageName.cmd"
$currentDatetime = Get-Date -format yyyyddMMhhmm
$scriptDir = "$(Split-Path -parent $MyInvocation.MyCommand.Definition)"
$installFile = (Join-Path $scriptDir -ChildPath "..\archives\$packageName.jar") | Resolve-Path


if (Test-Path -path $packageDir) {
  Write-Host "Stopping and deleting current version of ${packageName}"
  Start-ChocolateyProcessAsAdmin "schtasks /delete /f /tn `"${serviceName}`" "
  Start-Sleep 2
  Start-ChocolateyProcessAsAdmin "schtasks /end /f /tn `"${serviceName}`" "
  Remove-Item -recurse $(Join-Path $packageDir "\*") -exclude *.conf, *-bak*, *-old*

  Write-Host $(Get-Service "$serviceName" -ErrorAction SilentlyContinue).Status

  if ($(Get-Service "$serviceName" -ErrorAction SilentlyContinue).Status -eq "Running") {
    Write-Host "Stopping and deleting current version of ${packageName}"
    Start-ChocolateyProcessAsAdmin "Stop-Service `"${serviceName}`""
    Start-Sleep 2
  }

  if ($(Get-Service "$serviceName"  -ErrorAction SilentlyContinue).Status -ne "Running") {
    Write-Host "Cleaning ${packageDir} directory"
    Remove-Item -recurse $(Join-Path $packageDir "\*") -exclude *.conf, *-bak*, *-old*
  }
}
 
Write-Host "Making sure $installDir is in place"
if (!(Test-Path -path $installDir)) {New-Item $installDir -Type Directory  | Out-Null}

Write-Host "Making sure $packageDir is in place"
if (!(Test-Path -path $packageDir)) {New-Item $packageDir -Type Directory  | Out-Null}

Write-Host "Installing ${packageName} to ${packageDir}"
Copy-Item $installFile $packageDir

if (!(Test-Path ($cmdFile)))
{
    $cmdcontent = @"
cd /d ${packageDir}
java -jar ${packageName}.jar >> output.log 2>&1
"@
    echo "Dropping a ${packageName}.cmd file"
    Set-Content $cmdFile $cmdcontent -Encoding ASCII -Force
}

if (!(Get-Service "${serviceName}" -ErrorAction SilentlyContinue))
{
  echo "No ${serviceName} Service detected"
  echo "Installing ${serviceName} Service"
  Start-ChocolateyProcessAsAdmin "install `"${serviceName}`" ${cmdFile}" nssm
}

Start-ChocolateyProcessAsAdmin "set `"${serviceName}`" Start SERVICE_DEMAND_START" nssm

echo "Installing ${serviceName} Task"
Start-ChocolateyProcessAsAdmin "schtasks /create /f /ru system /sc hourly /st 00:30 /tn `"${serviceName}`" /tr  `"$cmdFile`""

Finally

Now we just need to create the package in our build script. The package will be named my_project.version.nupkg.
On our build machine we need to install choco. On the target machine we need the following tools installed:
chocolatey and nssm (for service management). Now we can create the package with:

  choco pack --version=${version}

Copy it to the target machine and install the current version with:

choco install -f -y c:\\installations\\${archive.name} --version=${version}

Put these steps inside a build script and use your favourite contininuous integration platform and voila.
Done.

deploy