Category Archives: Development

Defold Game architecture diagram connecting game engine, backend, cloud, database, and live operations

Building Defold HTML5 Games in GitHub Codespaces — A Complete Guide

How to set up a fully headless Defold development environment in the cloud, complete with unit testing, linting, and a live HTML5 demo — no desktop engine install required.

Why?

Defold is a fantastic free game engine, but its editor is a desktop application. If you want to develop Defold games on a Chromebook, an iPad, a locked-down work laptop, or just want your entire toolchain reproducible in the cloud, GitHub Codespaces is a great answer. This guide walks through everything I learned setting up a complete Defold → HTML5 pipeline from scratch: building with bob.jar, testing Lua with busted, linting with luacheck, formatting with StyLua, and serving a playable “Hello World” demo — all inside a browser tab.

What is Defold?

Defold is a free, open-source, cross-platform game engine developed by the Defold Foundation (originally by King). It’s designed for 2D games with first-class support for HTML5/WebAssembly, making it ideal for web games, instant games, and playable ads. Here’s what makes it stand out:

  • Zero cost, no royalties — completely free for commercial use, no revenue sharing, no seat licenses.
  • Lightweight & fast — the engine core is ~1-2 MB; HTML5 builds start around 2-3 MB gzipped. Cold starts are near-instant.
  • Lua + native extensions — game logic is written in Lua 5.1 (LuaJIT), with the ability to drop into C/C++/Rust for performance-critical code via native extensions.
  • Component-based architecture — game objects are composed of components (sprites, labels, collision objects, scripts, factories, etc.) attached to collections (scenes). No heavy inheritance hierarchies.
  • Built-in physics — Box2D for 2D physics with collision groups, raycasts, and joints.
  • First-class HTML5/WASM — one-click (or one-command) export to WebAssembly with asm.js fallback. Supports PWA, WebGL 1/2, and custom engine templates.
  • Live update — push content updates to live games without app store review (mobile) or re-deployment (web).
  • Headless CI-friendly build toolbob.jar is a standalone Java CLI that compiles, bundles, and packages projects without the editor. Perfect for automation, GitHub Actions, and Codespaces.
  • Small, helpful community — active forums, Discord, and extensive documentation. The engine has been battle-tested in production games like Plague Inc., Cosmic Top Secret, and many King titles.

Defold’s philosophy is “batteries included but not forced” — you get a complete 2D toolkit (rendering, physics, audio, input, GUI, particles, tilemaps, Spine/DragonBones animation) without being locked into a specific workflow or visual editor. The editor is there if you want it, but the project format is plain text, so you can version-control everything and even hand-author projects like we do in this guide.

The Architecture

Defold’s build system is Java-based, which is what makes this possible. The engine ships bob.jar — the same build tool the desktop editor uses internally. Given a project folder of text assets (collections, game objects, scripts), bob compiles them into a runnable bundle for any platform, including HTML5 via WebAssembly.

Our final layout:

defold/
├── bob.jar                      # Defold build tool
├── setup_environment.sh         # One-command environment restore
└── testproject/
    ├── Makefile                 # test / lint / fmt / build / bundle / serve
    ├── game.project             # Engine configuration
    ├── .luacheckrc              # Linter config with Defold globals
    ├── main/
    │   ├── main.collection      # Bootstrap collection
    │   ├── hello.go             # Game object (script + label components)
    │   ├── hello.script         # Game logic entry point
    │   ├── hello.label          # Text label definition
    │   └── game_logic.lua       # Pure-Lua module (unit tested!)
    ├── fonts/
    │   ├── RocknRollOne-Regular.ttf
    │   ├── rocknroll.font       # Defold font resource
    │   └── OFL.txt              # License (required by SIL OFL)
    └── spec/
        └── game_logic_spec.lua  # busted test suite

Step 1: Install the Toolchain

Note: for setting up the environment you can also copy the bash file at the bottom of the page and have an Ai agent execute it for you

Java + the system library nobody warns you about

bob.jar needs Java. Codespaces usually has it, but if not:

sudo apt-get update && sudo apt-get install -y default-jdk

The first build failure you’ll likely hit is cryptic:

UnsatisfiedLinkError: .../libmodelc_shared.so: libXi.so.6:
cannot open shared object file: No such file or directory

Headless containers lack X11 libraries that Defold’s native asset tools link against. One line fixes it:

sudo apt-get install -y libxi6

Download bob.jar

Get it from the official release (pin your version for reproducibility):

mkdir -p defold && cd defold
curl -sL -o bob.jar \
  https://github.com/defold/defold/releases/download/1.13.1/bob.jar
java --enable-native-access=ALL-UNNAMED -jar bob.jar --version

Gotcha: On modern JDKs (17+), bob’s native loaders trigger restricted-access warnings and can fail outright. Always run it with --enable-native-access=ALL-UNNAMED.

Lua tooling

Defold embeds Lua 5.1 (technically LuaJIT), so match your local tools accordingly:

sudo apt-get install -y lua5.1 luarocks
sudo luarocks install luacheck   # linter
sudo luarocks install busted     # BDD test framework
sudo luarocks install luaunit    # xUnit alternative

# StyLua formatter (binary download)
curl -sL -o /tmp/stylua.zip \
  https://github.com/JohnnyMorganz/StyLua/releases/download/v2.5.2/stylua-linux-x86_64.zip
unzip /tmp/stylua.zip stylua -d /tmp && sudo mv /tmp/stylua /usr/local/bin/

# lua-language-server (IDE autocomplete/diagnostics)
LLS_URL=$(curl -s https://api.github.com/repos/LuaLS/lua-language-server/releases/latest \
  | grep browser_download_url | grep linux-x64 | grep -v musl | cut -d'"' -f4 | head -1)
curl -sL -o /tmp/lls.tar.gz "$LLS_URL"
sudo mkdir -p /opt/lls && sudo tar -xzf /tmp/lls.tar.gz -C /opt/lls
sudo ln -sf /opt/lls/bin/lua-language-server /usr/local/bin/

Step 2: Create a Minimal Project

Defold projects are folders of plain-text files — perfect for hand-authoring when you can’t use the editor.

game.project — the INI-style config. Note the bootstrap paths point at compiled extensions (.collectionc); this is how bob resolves them internally:

[bootstrap]
main_collection = /main/main.collectionc

[display]
width = 640
height = 480

[input]
game_binding = /input/game.input_bindingc

main/main.collection — the root scene. It instantiates one game object:

name: "main"
instances {
  id: "go"
  prototype: "/main/hello.go"
  position { x: 320.0 y: 240.0 z: 0.0 }
  rotation { x: 0.0 y: 0.0 z: 0.0 w: 1.0 }
}

Key lesson: collections reference game object files; they don’t hold components inline. Put your script and label on hello.go.

main/hello.go — the game object owns both components. Position the object at screen center so rotations pivot correctly later:

components {
  id: "hello"
  component: "/main/hello.script"
  position { x: 0.0 y: 0.0 z: 0.0 }
}
components {
  id: "label"
  component: "/main/hello.label"
  position { x: 0.0 y: 0.0 z: 0.1 }
}

input/game.input_binding — maps left mouse button to the action name touch:

mouse_trigger {
  input: MOUSE_BUTTON_LEFT
  action: "touch"
}

Step 3: Hello World Script

local game_logic = require("main.game_logic")

function init(self)
    self.game = game_logic.new()
    msg.post("@render:", "clear_color", {color = vmath.vector4(0.2, 0.5, 0.9, 1.0)})
    -- pulse animation
    go.animate("#label", "scale.x", go.PLAYBACK_LOOP_PINGPONG, 1.2, go.EASING_INOUTSINE, 1.2)
    go.animate("#label", "scale.y", go.PLAYBACK_LOOP_PINGPONG, 1.2, go.EASING_INOUTSINE, 1.2)
    msg.post(".", "acquire_input_focus")
end

function on_input(self, action_id, action)
    if action_id == hash("touch") and action.pressed then
        self.game:add_score(1)
        go.animate(".", "euler.z", go.PLAYBACK_ONCE_FORWARD, 360,
            go.EASING_INOUTQUAD, 0.8, 0, function()
                go.set(".", "euler.z", 0)
            end)
    end
end

function update(self, dt)
    -- rainbow color cycling
    local t = (self.t or 0) + dt
    self.t = t
    local r = 0.5 + 0.5 * math.sin(t * 2.0)
    local g = 0.5 + 0.5 * math.sin(t * 2.0 + 2.0944)
    local b = 0.5 + 0.5 * math.sin(t * 2.0 + 4.1888)
    go.set("#label", "color", vmath.vector4(r, g, b, 1))
end

Three things worth noting:

  1. Rotate the game object ("."), not the component ("#label") — component rotation isn’t reliably animatable.
  2. Pivot matters — because the object sits at screen center and the label has pivot: PIVOT_CENTER, the spin rotates around the text’s visual center instead of flinging it off-screen.
  3. Game logic lives in a pure module — more on that below.

Step 4: Testable Architecture

Engine scripts can’t run under standard Lua interpreters — they depend on Defold globals (go, msg, vmath). The fix is classic hexagonal architecture: keep logic in pure modules and make scripts thin adapters.

-- main/game_logic.lua
local game = {}

function game.new()
    local self = setmetatable({}, { __index = game })
    self.score = 0
    return self
end

function game:add_score(points)
    assert(type(points) == "number" and points > 0, "points must be positive")
    self.score = self.score + points
    return self.score
end

return game
-- spec/game_logic_spec.lua
local game_logic = require("game_logic")

describe("game_logic", function()
    it("adds points to the score", function()
        local state = game_logic.new()
        assert.equals(10, state:add_score(10))
    end)
end)

Run with the module path pointing into main/:

busted --lpath="./main/?.lua" spec

Step 5: Build & Serve the HTML5 Bundle

This is where most of my debugging time went. The critical discovery: always clean-build when bundling, or bob silently reuses stale artifacts:

BOB := java --enable-native-access=ALL-UNNAMED -jar ../bob.jar

bundle:
	$(BOB) --platform wasm-web -a --bundle-output build_html5 clean build bundle

serve:
	cd build_html5/unnamed && python3 -m http.server 8000

Then open the forwarded port in your browser. That’s it — a real Defold game running in WASM, built entirely in the cloud.

Debugging tip: verify your deploy actually happened by comparing the archive size bob reports against what the server returns from archive/archive_files.json. Mismatch = stale bundle.

Step 6: Custom Fonts

Drop a TTF in the project and define a .font resource:

font: "/fonts/RocknRollOne-Regular.ttf"
material: "/builtins/fonts/font-df.material"
size: 48
output_format: TYPE_DISTANCE_FIELD

Critical pairing rule: distance-field fonts must use label-df.material; bitmap fonts need label-fnt.material. Mixing them produces ugly blocky outlines. Also remember labels require a size field — and it’s a Vector4 scale, not a number.

If you ship an OFL font, include its license file. It’s a legal requirement and takes two seconds.

Step 7: Disaster Recovery — The Setup Script

Everything above is captured in one idempotent script, setup_environment.sh. It checks each dependency before installing, pins versions, validates downloads, works under both root and non-root users, and finishes by running the full verification suite (tool versions + tests + lint). Run it in any fresh codespace and you’re back in business:

./setup_environment.sh
# ==> Environment fully restored and verified.

Commit it alongside the project. Your environment becomes as reproducible as your code.

Gotchas Cheat Sheet

GotchaFix
Platform js-web not supportedUse wasm-web
libXi.so.6: cannot open shared object fileapt-get install libxi6
Native access errors on modern JDKjava --enable-native-access=ALL-UNNAMED
Changes don’t appear after rebuildBundle must include clean build — stale cache lies
Error shows truncated path (main.collectio)Display quirk; not the real problem
Label won’t compileNeeds text, size (Vector4!), font, AND material
Blocky font renderingDF font ↔ label-df.material, bitmap ↔ label-fnt.material
Spin animation does nothingAnimate "." not "#component" for rotation
Rotation swings off-screenPivot = game object position; center the object, zero the component offset
.luacheckrc syntax errorIt’s Lua: -- comments and {} tables, not # or []

Wrapping Up

What we ended up with: a cloud-native Defold workflow with version-pinned tooling, automated quality gates (make check runs lint + format validation + 8 unit tests), one-command bundling, live preview over a forwarded port, and full disaster recovery in a single script. Total cost: free tier Codespaces hours.

The same pattern extends naturally — add a GitHub Actions workflow that runs make check && make bundle on every push and publishes build_html5/unnamed/ to GitHub Pages, and you’ve got continuous deployment for a browser game. But that’s a post for another day.

#!/usr/bin/env bash
###############################################################################
# Defold HTML5 dev environment bootstrap
#
# Restores the complete toolchain needed to build/test/serve this project:
#   - bob.jar (Defold build tool) + libxi6 system dependency
#   - Lua 5.1, Luarocks, Luacheck, busted, luaunit (testing)
#   - StyLua (formatter), lua-language-server (LSP)
#
# Usage:
#   ./setup_environment.sh          # install everything + verify
#   ./setup_environment.sh --no-verify   # skip verification step
#
# Assumes it is run from the repository root or from defold/.
###############################################################################
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
# Resolve defold/ dir whether script lives in defold/ or repo root
if [[ -d "$SCRIPT_DIR/defold" ]]; then DEFOLD_DIR="$SCRIPT_DIR/defold"; else DEFOLD_DIR="$SCRIPT_DIR"; fi

BOB_VERSION="1.13.1"
BOB_URL="https://github.com/defold/defold/releases/download/${BOB_VERSION}/bob.jar"
STYLUA_VERSION="2.5.2"

log() { printf '\n\033[1;36m==> %s\033[0m\n' "$*"; }

# Run a command with sudo only if not already root (codespace/root containers)
maybe_sudo() {
    if [[ $(id -u) -eq 0 ]]; then "$@"; else sudo "$@"; fi
}

apt_install() {
    maybe_sudo apt-get update -qq
    maybe_sudo apt-get install -y "$@"
}

###############################################################################
log "1/7 Checking Java (needed for bob.jar)"
###############################################################################
if ! command -v java >/dev/null 2>&1; then
    echo "Java not found. Installing default-jdk..."
    apt_install default-jdk
fi
java -version 2>&1 | head -1

###############################################################################
log "2/7 Installing system dependency libxi6 (required by bob's native tools)"
###############################################################################
dpkg -s libxi6 >/dev/null 2>&1 || apt_install libxi6

###############################################################################
log "3/7 Downloading bob.jar ${BOB_VERSION}"
###############################################################################
if [[ -f "$DEFOLD_DIR/bob.jar" ]] && java -jar "$DEFOLD_DIR/bob.jar" --version 2>/dev/null | grep -q "$BOB_VERSION"; then
    echo "bob.jar ${BOB_VERSION} already present, skipping download."
else
    curl -sL -o "$DEFOLD_DIR/bob.jar" "$BOB_URL"
    [[ $(stat -c%s "$DEFOLD_DIR/bob.jar") -gt 1000000 ]] || { echo "ERROR: bob.jar download failed"; exit 1; }
fi

###############################################################################
log "4/7 Installing Lua 5.1 + Luarocks + testing frameworks"
###############################################################################
command -v lua5.1 >/dev/null 2>&1 || apt_install lua5.1
command -v luarocks >/dev/null 2>&1 || apt_install luarocks
luarocks show luacheck >/dev/null 2>&1 || maybe_sudo luarocks install luacheck
luarocks show busted >/dev/null 2>&1   || maybe_sudo luarocks install busted
luarocks show luaunit >/dev/null 2>&1  || maybe_sudo luarocks install luaunit

###############################################################################
log "5/7 Installing StyLua formatter"
###############################################################################
if ! command -v stylua >/dev/null 2>&1; then
    TMPZIP="$(mktemp /tmp/stylua.XXXX.zip)"
    curl -sL -o "$TMPZIP" "https://github.com/JohnnyMorganz/StyLua/releases/download/v${STYLUA_VERSION}/stylua-linux-x86_64.zip"
    INSTALL_DIR="$(mktemp -d /tmp/stylua_install.XXXX)"
    unzip -o "$TMPZIP" stylua -d "$INSTALL_DIR" >/dev/null
    maybe_sudo mv "$INSTALL_DIR/stylua" /usr/local/bin/
    rm -rf "$TMPZIP" "$INSTALL_DIR"
fi

###############################################################################
log "6/7 Installing lua-language-server"
###############################################################################
if ! command -v lua-language-server >/dev/null 2>&1; then
    LLS_URL=$(curl -s https://api.github.com/repos/LuaLS/lua-language-server/releases/latest \
        | grep browser_download_url | grep linux-x64 | grep -v musl | cut -d'"' -f4 | head -1)
    [[ -n "$LLS_URL" ]] || { echo "ERROR: could not resolve lua-language-server download URL"; exit 1; }
    TARGZ="$(mktemp /tmp/lls.XXXX.tar.gz)"
    curl -sL -o "$TARGZ" "$LLS_URL"
    maybe_sudo mkdir -p /opt/lua-language-server
    maybe_sudo tar -xzf "$TARGZ" -C /opt/lua-language-server
    maybe_sudo ln -sf /opt/lua-language-server/bin/lua-language-server /usr/local/bin/lua-language-server
    rm -f "$TARGZ"
fi

###############################################################################
log "7/7 Verification"
###############################################################################
if [[ " $* " == *" --no-verify "* ]]; then
    echo "Skipping verification."
    exit 0
fi

cd "$DEFOLD_DIR/testproject"
FAIL=0

# run_check <label> <command...> — records failure without aborting (set -e safe)
run_check() {
    local label="$1"; shift
    echo "--- $label ---"
    if ! "$@"; then
        FAIL=1
    fi
}

run_check "bob.jar" java --enable-native-access=ALL-UNNAMED -jar ../bob.jar --version
run_check "luacheck version" luacheck --version
run_check "stylua" stylua --version
run_check "lua-language-server" lua-language-server --version
run_check "busted tests" busted --lpath="./main/?.lua" spec
run_check "lint" luacheck main spec

echo ""
if [[ $FAIL -eq 0 ]]; then
    echo -e "\033[1;32mEnvironment fully restored and verified.\033[0m"
    echo "Next steps:"
    echo "  cd $DEFOLD_DIR/testproject"
    echo "  make bundle        # build HTML5 bundle into build_html5/"
    echo "  make serve         # serve at http://localhost:8000"
else
    echo -e "\033[1;31mSome checks failed — review output above.\033[0m" >&2
    exit 1
fi

Comprehensive Guide to Helping an Ai Coding Agent Identify and Avoid Common Coding Bad Practices

Introduction

In large projects, subtle anti-patterns can slip through reviews—like importing modules mid-file or conditionally. These non-standard import placements obscure dependencies, make static analysis unreliable, and lead to unpredictable runtime errors. This web article dives into that practice, outlines a broader set of coding bad practices, and even provides a ready-to-use AI coding agent prompt to catch every issue across your codebase.

What Is Non-Standard Import Placement?

Imports or require statements buried inside functions, conditional branches, or midway through a file violate expectations of where dependencies live. Best practices and most style guides mandate that:

  • All imports sit at the top of the file, immediately after any module docstring or comments.
  • Conditional or lazy loading only happens with clear justification and documentation.

When imports are scattered:

  1. Static analysis tools can’t reliably determine your project’s dependency graph.
  2. Developers hunting for missing or outdated modules lose time tracing hidden import logic.
  3. You risk circular dependencies, initialization bugs, or runtime surprises.

A Broader List of Coding Bad Practices

Below is a table of widespread anti-patterns—some classic hygiene issues and others that modern AI agents might inject or overlook:

Bad PracticeDescription
Spaghetti CodeCode with no clear structure making maintenance difficult.
Hardcoding ValuesEmbedding constants directly instead of using config or constants.
Magic Numbers/StringsUsing unexplained literals instead of named constants.
Global State AbuseOverusing global variables causing unpredictable side effects.
Poor Naming ConventionsUsing vague or misleading variable and function names.
Lack of ModularityWriting large monolithic blocks instead of reusable functions.
Copy-Paste ProgrammingDuplicating code rather than abstracting shared logic.
No Error HandlingIgnoring exceptions or failing to validate inputs.
OverengineeringAdding unnecessary complexity or abstraction.
Under-documentationFailing to comment or explain non-obvious logic.
Tight CouplingMaking modules overly dependent on each other.
Ignoring Style GuidesNot following language-specific conventions or style guides.
Dead CodeLeaving unused or unreachable code paths in the codebase.
Inconsistent FormattingMixing indentation styles or inconsistent code layout.
Not Using Version Control ProperlyCommitting broken code, poor commit messages, ignoring branching.
Non-standard Import PlacementPlacing imports mid-file or conditionally instead of at the top.
Missing Security ChecksOmitting authentication, authorization, or input sanitization.
Inefficient AlgorithmsUsing suboptimal logic that hurts performance.
Hallucinated DependenciesReferencing non-existent libraries or methods from AI suggestions.
Incomplete Code GenerationLeaving functions or loops unfinished due to AI cutoffs.
Prompt-biased SolutionsGenerating code that only fits the prompt and fails general cases.
Missing Corner CasesOverlooking edge cases and error conditions in logic.
Incorrect Error MessagesProviding vague or misleading error feedback to users.
Logging Sensitive DataWriting confidential information to logs without sanitization.
Violating SOLID PrinciplesBreaking single responsibility or open/closed design rules.
Race ConditionsFailing to handle concurrency leading to unpredictable bugs.

Crafting an AI Coding Agent Prompt

To ensure an AI auditor doesn’t skip files, ignore edge cases, or take shortcuts, use the following prompt. It instructs the agent to comprehensively scan every line, record each finding, and tally occurrences of every bad practice.

## Prompt

You are an expert AI code auditor. Your mission is to exhaustively scan every file and line of the codebase and uncover all instances of known bad practices. Do not skip or shortcut any part of the project, even if the code is large or complex. Report every finding with precise details and clear remediation guidance.

## Scope
- Analyze every source file, configuration, script, and module.
- Treat all code as in-scope; do not assume any file is irrelevant.

## Bad Practices to Detect
- Spaghetti Code
- Hardcoding Values
- Magic Numbers/Strings
- Global State Abuse
- Poor Naming Conventions
- Lack of Modularity
- Copy-Paste Programming
- No Error Handling
- Overengineering
- Under-documentation
- Tight Coupling
- Ignoring Style Guides
- Dead Code
- Inconsistent Formatting
- Improper Version Control Usage
- Non-standard Import Placement
- Missing Security Checks
- Inefficient Algorithms
- Hallucinated Dependencies
- Incomplete Code Generation
- Prompt-biased Solutions
- Missing Corner Cases
- Incorrect Error Messages
- Logging Sensitive Data
- Violating SOLID Principles
- Race Conditions

## Analysis Instructions
1. Traverse the entire directory tree and open every file.
2. Inspect every line—do not skip blank or comment lines.
3. Identify code snippets matching any bad practice.
4. For each instance, document:
   - File path
   - Line number(s)
   - Exact snippet
   - Bad practice name
   - Explanation of why it’s problematic
   - Suggested refactoring

5. Keep a running tally of occurrences per bad practice.

## Output Requirements
- Use Markdown with a section per file.
- Subheadings for each issue.
- End with a summary table listing each bad practice and its total count.
- If the repo is too large, process in ordered batches (e.g., by folder), confirming coverage before proceeding.
- Do not conclude until every file has been reviewed.

Begin the full project audit now, acknowledging you will not take shortcuts.

Next Steps

  • Integrate this prompt into your AI workflow or CI pipeline.
  • Pair it with linters and static analyzers (ESLint, Flake8, Prettier) for automated, real-time checks.
  • Enforce code review policies that catch both human and AI-introduced anti-patterns.

By combining clear style guidelines, automated linting, and an uncompromising AI audit prompt, you’ll dramatically improve code quality, maintainability, and security—project-wide.

An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

How to create a C# console application that will solve crosswords

This tutorial will cover the following tasks in C#:

  • How to count the characters in a string
  • How to assign a file’s directory location to a variable
  • How to create a list variable
  • How to pull/read a CSV file column into a list variable
  • How to clean strings using Regex to remove non alpha numeric characters as the strings are being read into a list
  • How to remove duplicate word entries from a list
  • How to order a list
  • How to write variables to the console, including a list’s elements

Assumptions:

You already know how to create projects in Visual Studio.

If you do not how to do this search online using the following term “how to create C# console applications in visual studio”.

Prerequisites:

First you will need to generate a CSV file with random words using this site:

https://onlinerandomtools.com/generate-random-csv

For the option “how many columns to generate” set the value to 1.

For testing purposes create 1000 rows.

Download the csv file generated and save it using the name “words”.

Summary of how the code works:

The code works by reducing the initial list (i.e. the supplied CSV file of random words) down to only words that match the number of characters of the user word, typically referred to as “string length”.

Once that subset of words has been created the code will then compare the user word’s letters against each letter, referencing the relative position, in each word in the subset.

Note: there is still significant room for optimization but the code is functional and works well as an accessible, human readable tutorial.

Use case example:

If the user enters the word “apple” the dictionary subset will be reduced down to 5 letter words only. These five letter words are then compared to the user word, each word and letter at a time. So if the first word in the list was “cabin” the comparison would jump to the next word in the list as the “a” in “apple” does not match the “c” in “cabin”. If the next word in the dictionary was “acorn” the first letters would match but the comparison would jump to the next word when the “c” and “p” did not match.

Instructions:

Create a C# console application called CrosswordSolver in Visual Studio.

Move the CSV file called “Words” into the bin directory of the project folder, i.e. CrosswordSolver\CrosswordSolver\bin

Open the project CrosswordSolver and paste the C# code below into the default window replacing the default cs page code.

The hardcoded example of a user word is:

string userWord = “a****”;

The user can use * to represent characters unknown, for example ap*le.

Note: The CSV file you randomly generated may have no examples of 5 letter words begining with the letter “a” so experiment with other characters.

You can test the letter comparison functionality by uncommenting the two sections of code immediately following the comments “Test letter comparison”.

To test your CSV file has been read into memory you can uncomment the section of code immediately following “Test that dictionary has been read into memory”.

The C# code:

using System;
using System.IO;
using System.Linq;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace CrosswordSolver
{
    class Program
    {
        static void Main(string[] args)
        {
            int c = 0;
            //User input
            //NOTE: Use * to represent characters unknown 
            string userWord = "a****";
            int wordLength = userWord.Length;

            //Assign directory location of the csv file containing the collection of words to a variable
            string projectFolder = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
            string file = Path.Combine(projectFolder, "words.csv");

            //Display dictionary location in console
            Console.WriteLine("Dictionary location: " + file);

            var dictionary = new List<string>();
            using (var rd = new StreamReader(file))

            //Pull file column into dictionary list without cleaning
            //{
            //    while (!rd.EndOfStream)
            //    {
            //        var splits = rd.ReadLine().Split(',');
            //        dictionary.Add(splits[0]);
            //    }
            //}

            //Pull file column into dictionary list while cleaning
            {
                while (!rd.EndOfStream)
                {
                    var splits = rd.ReadLine().Split(',');
                    //string clean is done with Regex
                    dictionary.Add(Regex.Replace(splits[0], "[^A-Za-z0-9 ]", ""));
                }
            }

            //Test that dictionary has been read into memory
            //Console.WriteLine("The dictionary contains the following words:");
            //foreach (var element in dictionary)
            //Console.WriteLine(element);

            //Remove duplicate word entries
            //c = dictionary.Count;
            //Console.WriteLine("The dictionary contains " + c + " words");
            dictionary = dictionary.Distinct().ToList();
            //c = dictionary.Count;
            //Console.WriteLine("The dictionary contains " + c + " words");

            // Count the elements in the List and display test parameters
            c = dictionary.Count;
            Console.WriteLine("The dictionary contains " + c + " words");
            Console.WriteLine("User entered the string: " + userWord);
            Console.WriteLine(userWord + " has " + wordLength + " characters");
            userWord = userWord.ToLower();

            //Reduce the dataset size based on number of characters in string
            IEnumerable<string> query =
                dictionary.Where(word => word.Length == wordLength);

            var subSet = new List<string>();
            foreach (var word in query)
                subSet.Add(word);

            //Order List
            subSet = subSet.OrderBy(x => x).ToList();

            c = subSet.Count;
            if (c != 0)
            {
                Console.WriteLine("The dictionary contains " + c + " words that are " + wordLength + " characters in length");

                //Begin character and position match check
                var result = new List<string>();
                foreach (var word in subSet)

                {
                    for (int i = 0; i <= wordLength - 1; i++)
                    {

                        if ((word.ToLower()[i] == userWord[i]) | (userWord[i] == '*'))
                        {

                            //Test letter comparison (Letters match)
                            //Console.WriteLine(
                            //"Letter " + i + ", which is " + "\"" + word[i] + "\"" + ", of the word " + "\"" + word + "\"" +
                            //" matches letter " + i + ", which is " + "\"" + userWord[i] + "\"" + ", of the user input " + "\"" + userWord + "\""
                            //);

                            if (i == wordLength - 1)
                            { result.Add(word); }

                        }
                        else
                        {
                            //Test letter comparison (Letters do not match)
                            //Console.WriteLine(
                            //"Letter " + i + ", which is " + "\"" + word[i] + "\"" + ", of the word " + "\"" + word + "\"" +
                            //" does not match letter " + i + ", which is " + "\"" + userWord[i] + "\"" + ", of the user input " + "\"" + userWord + "\""
                            //);

                            break;
                        }
                    }
                }

                //Test words that do not match
                //foreach (var word in subSetToRemove)
                //Console.WriteLine(word);

                bool isEmpty = !result.Any();
                if (isEmpty)
                {
                    Console.WriteLine("No matches found");
                }
                else
                {
                    c = result.Count();
                    Console.WriteLine("Potential matches found: " + c);
                    foreach (var word in result)
                        Console.WriteLine(word);
                }
            }
            else
            {
                Console.WriteLine("No words of " + wordLength + " characters long found");
            }
            Console.ReadKey();
        }
    }
}

 

If you found this code useful be sure to like the post and comment. ☮

If you would like to know how to create a csv file with C# see this tutorial link.

If you would like to know how to create a console application in Visual Studio that won’t open a command window when it runs see this tutorial link.

 

How to create a job that will test whether SQL Server database mail is working

The following script will create a job that will run every minute to test if database mail can be sent from a job scheduled to run by the Sql Server Agent.

Simply find and replace the email address below with the email address you want to target:

testoperator@mail.com

Then run the script.

The operator ‘Test Operator’ and job ‘MailTest’ will be created.

The job is disabled by default, enable it to begin testing.

When you are finished run the commented out section at the bottom of the script to remove the test operator and job.

If you have just setup database mail for the first time the SQL Server Agent will need to be restarted.

/*
FIND AND REPLACE

testoperator@mail.com

*/
USE msdb;
GO

EXEC dbo.sp_add_operator @name = N'Test Operator'
	,@enabled = 1
	,@email_address = N'testoperator@mail.com'
GO

USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 31/07/2019 11:35:43 ******/
IF NOT EXISTS (
		SELECT NAME
		FROM msdb.dbo.syscategories
		WHERE NAME = N'[Uncategorized (Local)]'
			AND category_class = 1
		)
BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
		,@type = N'LOCAL'
		,@name = N'[Uncategorized (Local)]'

	IF (
			@@ERROR <> 0
			OR @ReturnCode <> 0
			)
		GOTO QuitWithRollback
END

DECLARE @jobId BINARY (16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'MailTest'
	,@enabled = 0
	,@notify_level_eventlog = 0
	,@notify_level_email = 3
	,@notify_level_netsend = 0
	,@notify_level_page = 0
	,@delete_level = 0
	,@description = N'No description available.'
	,@category_name = N'[Uncategorized (Local)]'
	,@owner_login_name = N'sa'
	,@notify_email_operator_name = N'Test Operator'
	,@job_id = @jobId OUTPUT

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

/****** Object:  Step [Step 1]    Script Date: 31/07/2019 11:35:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
	,@step_name = N'Step 1'
	,@step_id = 1
	,@cmdexec_success_code = 0
	,@on_success_action = 1
	,@on_success_step_id = 0
	,@on_fail_action = 2
	,@on_fail_step_id = 0
	,@retry_attempts = 0
	,@retry_interval = 0
	,@os_run_priority = 0
	,@subsystem = N'TSQL'
	,@command = N'SELECT 1'
	,@database_name = N'master'
	,@flags = 0

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
	,@start_step_id = 1

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
	,@name = N'Job Schedule'
	,@enabled = 1
	,@freq_type = 4
	,@freq_interval = 1
	,@freq_subday_type = 4
	,@freq_subday_interval = 1
	,@freq_relative_interval = 0
	,@freq_recurrence_factor = 0
	,@active_start_date = 20190731
	,@active_end_date = 99991231
	,@active_start_time = 0
	,@active_end_time = 235959
	,@schedule_uid = N'f0741db6-488e-44da-8f5e-a3f0ed13835e'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
	,@server_name = N'(local)'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0)
	ROLLBACK TRANSACTION

EndSave:
GO

/*
REMOVE OPERATOR AND JOB
*/
/*
USE msdb;
GO

EXEC sp_delete_operator @name = 'Test Operator';

EXEC sp_delete_job @job_name = N'MailTest';

GO
*/

 

How to handle a Database creation request

If you are working as a DBA you may find that developers will ask you to create a database having given little thought to what the database will be used for or the impact the database could have to the resources or security of the hosting environment.

If you find yourself in that situation I would suggest you walk the requester through the questionnaire from the previous article “How to determine where a new database should be deployed“.

Once you have completed that process I would then suggest that an official request to deploy a new database be made using the DATABASE REQUEST FORM provided here link. If you have a change request process I would still suggest you use this form. Having a database specific request form covers more relevant and vital information.

This is a fairly high level request form with most of the technical details still to be defined by the DBA but it provides documentation of the request and states the requester’s initial expectations and requirements.

Following the database deployment if the actual footprint of the database does not match up with what was agreed the form will confirm if the requested resources were under specced or misleading.

The form is outlined as below.

DATABASE REQUEST FORM image

Some important points the form clarifies:

There’s a difference between requester and owner. If the database runs into any problems you don’t want to be contacting the temporary intern that requested it instead of say the department head.

The application the database supports. Most of the time the database name will have some tie-in to the application name but maybe it does not. For instance the database could be named something generic like Reporting which could be the back end for really anything.

The form asks the requester to prepare a profile for the database. I could have named this section “who is your daddy and what does he do?”. If the requester states they are looking for a OLAP reporting database but operationally it’s running as a OLTP transactional database, that could make a big difference in terms of the resources provided for the database and underlying hardware.

Possibly most importantly the form helps to establish the likely impact of the new database with the Resource Impact Estimation section. For example if a requester asks for 10 Gb of space and states they expect space usage to increase by 5 Gb a year but the disk has lost a terabyte in the first few months the form will clarify who got their numbers wrong.

The user and groups section will clarify who should have access to the database. Effectively everything related to data and data access should be okayed by a compliance officer to confirm everything is GDPR compliant. This form will assist the compliance officer in establishing that.

The Business continuity & Upkeep section is really the domain of the DBA but it helps to get requester input on these matters. For instance establishing maintenance windows.

If you have any additional questions you feel should be on the form please feel free to contact me and I’ll add them.

How to get the default error log path for SQL Server with T-SQL

Below is a script to get the default error log path for SQL Server and set it as a variable. 

USE MASTER;
GO

DECLARE @LogPath AS VARCHAR(MAX)
DECLARE @ErrorLogPath TABLE (
	LogDate DATETIME
	,ProcessInfo VARCHAR(255)
	,PathText VARCHAR(MAX)
	);

INSERT INTO @ErrorLogPath
EXEC xp_readerrorlog 0
	,1
	,N'Logging SQL Server messages in file';

SET @LogPath = (
		SELECT REPLACE(REPLACE(REPLACE(PathText, 'Logging SQL Server messages in file ', ''), '''', ''), 'ERRORLOG.', '')
		FROM @ErrorLogPath
		);

SELECT @LogPath AS DefaultLogPath;
GO

 

How to pass arguments from command line to a console application written in C#

This is a simple tutorial on passing arguments or parameter values from command line to a console application written in C#. Using the example below you should be able to edit and expand on the logic to fit your own needs.

First you’ll need to create a new Visual Studio C# console application, to do so follow these steps:

To create and run a console application

  1. Start Visual Studio.

  2. On the menu bar, choose FileNewProject.
  3. Expand Installed, expand Templates, expand Visual C#, and then choose Console Application.
  4. In the Name box, specify a name for your project, and then choose the OK button.
  5. If Program.cs isn’t open in the Code Editor, open the shortcut menu for Program.cs in Solution Explorer, and then choose View Code.
  6. Replace the contents of Program.cs with the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestArgsInput
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
				// Display message to user to provide parameters.
                System.Console.WriteLine("Please enter parameter values.");
                Console.Read();
            }
            else
            {
                // Loop through array to list args parameters.
                for (int i = 0; i < args.Length; i++)
                {
                    Console.Write(args[i] + Environment.NewLine);
                    
                }
                // Keep the console window open after the program has run.
                Console.Read();
            }
        }
    }
}

 

The Main method is the entry point of a C# application. When the application is started, the Main method is the first method that is invoked.

The parameter of the Main method is a String array that represents the command-line arguments. Usually you determine whether arguments exist by testing the Length property as in the example above.

When run the example above will list out the parameters you have provided to the command window. The delimiter for command line separating arguments or parameter values is a single space. For example the following would be interpreted as two arguments or parameter values:

“This is parameter 1” “This is parameter 2”

If the arguments were not enclosed by double quotes each word would be considered an argument.

To pass arguments to the console application when testing the application logic the arguments can be written into the debug section of the project properties as shown below.

TestArgs

So if the app is run with the command line arguments provided as above in the image the command window will list:
Parameter 1
Parameter 2
If you would like to know how to create a console application in Visual Studio that won’t open a command window when it runs see this tutorial link.
If you would like to know how to create a csv file with C# see this tutorial link.

How to solve the SQL Server error ‘String or binary data would be truncated’

The ‘String or binary data would be truncated’ error will occur if an insert or update statement is trying to put too many characters into a field, defined in a table, which has been assigned too few character spaces. For example trying to write an email address with 255 characters into a table where the column email has been assigned 40 characters.

The easy fix is assign more characters to the column or columns you have determined are experiencing the problem. The more complicated but potentially necessary fix might be to change the logic or introduce validation at the source of data entry.

Finding the columns experiencing the problems however can be time consuming.

( . . . without the little script below of course)

SQL Server will kindly direct you to the stored procedure or insert/update statement that is experiencing the problem. However it will not pin point the exact column or columns that cannot be written to. The pain then is determining where the data won’t fit.

To speed things up take the entire query or query section you know to be causing the problem and write the results it into a temp table called #temp, i.e. SELECT * INTO #temp FROM SomeTable

Once the data has been written to the temp table #temp run the scrip below in the same window.

DECLARE @sql VARCHAR(MAX)

SET @sql = (
		SELECT (
				SELECT ',MAX(LEN(' + NAME + ')) AS [' + NAME + ']'
				FROM tempdb.sys.columns
				WHERE object_id = object_id('tempdb..#temp')
				FOR XML PATH('')
				)
		)
SET @sql = 'SELECT ' + RIGHT(@sql, LEN(@sql) - 1) + ' FROM #temp'

EXEC (@sql)
This will output results giving you the max character length of each field.
You can then compare these results to the defined destination table that the data could not be written to.
The source of the error will be where the max character number is greater than the assigned character spaces on the destination table.
For example the last time I used this query it easily highlighted that an agent had written a customers full address to the county name field which had a limit of 30 characters.

 

How to use a while loop to iterate through each table of each database within an instance

Say you have code you want executed against every table on a SQL Server instance, you could use SQL Server’s inbuilt sp_MSForEachDB and sp_MSForEachTable. I’m not a big fan of them though because they are undocumented, so I’d always be concerned Microsoft might decide to kill it with any given patch or service pack update. (I know the likelihood of that is extremely low but I’m a risk adverse kinda guy)

I prefer to use the example below. It may not be the most efficient snippet of code available on the net but it’s good and simple and it’s not going anywhere unless I drop it.

SET NOCOUNT ON

DECLARE @Database TABLE (DbName SYSNAME)
DECLARE @DbName AS SYSNAME

SET @DbName = ''

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases
WHERE NAME <> 'tempdb'
AND state_desc = 'ONLINE'
ORDER BY NAME ASC

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)

	/*
	PUT CODE HERE
	EXAMPLE PRINT Database Name
	*/
	PRINT @DbName
END