AgileAdam.com

Quick Script: Convert a CSV file to a SQLite database file

/ August 27, 2025

Updated on October 4, 2025 to detect INTEGER/REAL/TEXT column types.

Here’s a quick bash script to convert a .csv file into a .sqlite file so you can query your CSV data in tools like TablePlus.

The file size is only slightly larger than the original CSV, and you can share it (it’s just a file!).

Just create the file csv-to-sqlite somewhere in your PATH and chmod +x csv-to-sqlite to make it executable.

#!/bin/bash

# csv-to-sqlite - Convert CSV file to SQLite database with type detection
# Usage: csv-to-sqlite [options] <csvfile>

set -e

# Default values
table_name="main"
show_help=false

# Function to show usage
show_usage() {
    cat << EOF
Usage: $0 [OPTIONS] <csvfile>

Convert a CSV file to an SQLite database with automatic type detection.

Arguments:
    csvfile                 Path to the CSV file to import

Options:
    -t, --table-name NAME   Name for the table (default: main)
    -h, --help             Show this help message

Examples:
    $0 ~/mycsvfile.csv
    $0 --table-name employees ~/data/employees.csv
    $0 -t products ~/products.csv

Output:
    Creates a SQLite database with the same name as the CSV file but with
    .sqlite extension in the same directory as the input file.
    
    Column types are automatically detected:
    - INTEGER for whole numbers
    - REAL for decimal numbers
    - TEXT for everything else
EOF
}

# Parse command line arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        -t|--table-name)
            table_name="$2"
            shift 2
            ;;
        -h|--help)
            show_usage
            exit 0
            ;;
        -*)
            echo "Error: Unknown option $1" >&2
            show_usage
            exit 1
            ;;
        *)
            # This should be the CSV file
            if [[ -z "${csv_file:-}" ]]; then
                csv_file="$1"
            else
                echo "Error: Multiple CSV files specified" >&2
                show_usage
                exit 1
            fi
            shift
            ;;
    esac
done

# Check if CSV file was provided
if [[ -z "${csv_file:-}" ]]; then
    echo "Error: CSV file not specified" >&2
    show_usage
    exit 1
fi

# Check if CSV file exists
if [[ ! -f "$csv_file" ]]; then
    echo "Error: File '$csv_file' not found" >&2
    exit 1
fi

# Expand tilde in path
csv_file=$(eval echo "$csv_file")

# Generate SQLite database filename
# Remove extension and add .sqlite
base_db_file="${csv_file%.*}.sqlite"
db_file="$base_db_file"

# If file exists, add timestamp suffix
if [[ -f "$db_file" ]]; then
    timestamp=$(date +"%Y%m%d%H%M%S")
    # Extract directory, basename without extension, and add timestamp
    db_dir=$(dirname "$base_db_file")
    db_name=$(basename "$base_db_file" .sqlite)
    db_file="${db_dir}/${db_name}_${timestamp}.sqlite"
    echo "Warning: '$base_db_file' already exists, creating '$db_file' instead"
fi

# Validate table name (basic check for SQL injection prevention)
if [[ ! "$table_name" =~ ^[a-zA-Z_][a-zA-Z0-9_]*$ ]]; then
    echo "Error: Invalid table name '$table_name'. Table names must start with a letter or underscore and contain only letters, numbers, and underscores." >&2
    exit 1
fi

echo "Converting '$csv_file' to SQLite database '$db_file'"
echo "Table name: $table_name"

# Detect column types using awk
# This analyzes the first 1000 rows to determine INTEGER, REAL, or TEXT types
create_table_sql=$(awk -F',' '
BEGIN {
    SAMPLE_SIZE = 1000
}

# Process header row
NR == 1 {
    for (i = 1; i <= NF; i++) {
        # Remove quotes and whitespace from header
        gsub(/^[ \t"]+|[ \t"]+$/, "", $i)
        # Use column number if header is empty
        if ($i == "" || length($i) == 0) {
            headers[i] = "col" i
        } else {
            headers[i] = $i
        }
        # Initialize all columns as INTEGER
        types[i] = "INTEGER"
    }
    next
}

# Process data rows (sample only)
NR <= SAMPLE_SIZE + 1 {
    for (i = 1; i <= NF; i++) {
        value = $i
        # Remove quotes and whitespace
        gsub(/^[ \t"]+|[ \t"]+$/, "", value)
        
        # Skip empty values
        if (value == "") continue
        
        # If already TEXT, skip further checks
        if (types[i] == "TEXT") continue
        
        # Check if value is an integer
        if (types[i] == "INTEGER") {
            if (value !~ /^-?[0-9]+$/) {
                # Not an integer, check if its a real number
                if (value ~ /^-?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$/) {
                    types[i] = "REAL"
                } else {
                    types[i] = "TEXT"
                }
            }
        }
        # Check if value is a real number
        else if (types[i] == "REAL") {
            if (value !~ /^-?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$/) {
                types[i] = "TEXT"
            }
        }
    }
}

END {
    # Generate CREATE TABLE statement
    print "CREATE TABLE IF NOT EXISTS temp_table ("
    for (i = 1; i <= length(headers); i++) {
        # Escape double quotes in column names
        safe_name = headers[i]
        gsub(/"/, "\"\"", safe_name)
        printf "    \"%s\" %s", safe_name, types[i]
        if (i < length(headers)) {
            print ","
        } else {
            print ""
        }
    }
    print ");"
}
' "$csv_file")

# Import CSV to SQLite with proper schema
sqlite3 "$db_file" << EOF
-- Create temporary table with detected types
$create_table_sql

-- Import CSV data
.mode csv
.import "$csv_file" temp_table

-- Create final table with correct name
CREATE TABLE "$table_name" AS SELECT * FROM temp_table;

-- Drop temporary table
DROP TABLE temp_table;
EOF

echo "Successfully created SQLite database: $db_file"
echo "Table '$table_name' contains $(sqlite3 "$db_file" "SELECT COUNT(*) FROM $table_name;") rows"
echo ""

# Show schema
echo "Table schema:"
sqlite3 "$db_file" "PRAGMA table_info($table_name);" | awk -F'|' '{printf "  %-20s %s\n", $2, $3}'
echo ""
echo "You can now open this SQLite database in applications like TablePlus for convenient data viewing and manipulation."

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

#!/bin/bash

# csv-to-sqlite - Convert CSV file to SQLite database with type detection

# Usage: csv-to-sqlite [options] <csvfile>

set -e

# Default values

table_name="main"

show_help=false

# Function to show usage

show_usage() {

cat << EOF

Usage: $0 [OPTIONS] <csvfile>

Convert a CSV file to an SQLite database with automatic type detection.

Arguments:

csvfile Path to the CSV file to import

Options:

-t, --table-name NAME Name for the table (default: main)

-h, --help Show this help message

Examples:

$0 ~/mycsvfile.csv

$0 --table-name employees ~/data/employees.csv

$0 -t products ~/products.csv

Output:

Creates a SQLite database with the same name as the CSV file but with

.sqlite extension in the same directory as the input file.

Column types are automatically detected:

- INTEGER for whole numbers

- REAL for decimal numbers

- TEXT for everything else

EOF

}

# Parse command line arguments

while [[ $# -gt 0 ]]; do

case $1 in

-t|--table-name)

table_name="$2"

shift 2

;;

-h|--help)

show_usage

exit 0

;;

-*)

echo "Error: Unknown option $1" >&2

show_usage

exit 1

;;

# This should be the CSV file

if [[ -z "${csv_file:-}" ]]; then

csv_file="$1"

else

echo "Error: Multiple CSV files specified" >&2

show_usage

exit 1

shift

;;

esac

done

# Check if CSV file was provided

if [[ -z "${csv_file:-}" ]]; then

echo "Error: CSV file not specified" >&2

show_usage

exit 1

# Check if CSV file exists

if [[ ! -f "$csv_file" ]]; then

echo "Error: File '$csv_file' not found" >&2

exit 1

# Expand tilde in path

csv_file=$(eval echo "$csv_file")

# Generate SQLite database filename

# Remove extension and add .sqlite

base_db_file="${csv_file%.*}.sqlite"

db_file="$base_db_file"

# If file exists, add timestamp suffix

if [[ -f "$db_file" ]]; then

timestamp=$(date +"%Y%m%d%H%M%S")

# Extract directory, basename without extension, and add timestamp

db_dir=$(dirname "$base_db_file")

db_name=$(basename "$base_db_file" .sqlite)

db_file="${db_dir}/${db_name}_${timestamp}.sqlite"

echo "Warning: '$base_db_file' already exists, creating '$db_file' instead"

# Validate table name (basic check for SQL injection prevention)

if [[ ! "$table_name" =~ ^[a-zA-Z_][a-zA-Z0-9_]*$ ]]; then

echo "Error: Invalid table name '$table_name'. Table names must start with a letter or underscore and contain only letters, numbers, and underscores." >&2

exit 1

echo "Converting '$csv_file' to SQLite database '$db_file'"

echo "Table name: $table_name"

# Detect column types using awk

# This analyzes the first 1000 rows to determine INTEGER, REAL, or TEXT types

create_table_sql=$(awk -F',' '

BEGIN {

SAMPLE_SIZE = 1000

}

# Process header row

NR == 1 {

for (i = 1; i <= NF; i++) {

# Remove quotes and whitespace from header

gsub(/^[ \t"]+|[ \t"]+$/, "", $i)

# Use column number if header is empty

if ($i == "" || length($i) == 0) {

headers[i] = "col" i

} else {

headers[i] = $i

}

# Initialize all columns as INTEGER

types[i] = "INTEGER"

}

# Process data rows (sample only)

NR <= SAMPLE_SIZE + 1 {

for (i = 1; i <= NF; i++) {

value = $i

# Remove quotes and whitespace

gsub(/^[ \t"]+|[ \t"]+$/, "", value)

# Skip empty values

if (value == "") continue

# If already TEXT, skip further checks

if (types[i] == "TEXT") continue

# Check if value is an integer

if (types[i] == "INTEGER") {

if (value !~ /^-?[0-9]+$/) {

# Not an integer, check if its a real number

if (value ~ /^-?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$/) {

types[i] = "REAL"

} else {

types[i] = "TEXT"

}

# Check if value is a real number

else if (types[i] == "REAL") {

if (value !~ /^-?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$/) {

types[i] = "TEXT"

}

END {

# Generate CREATE TABLE statement

print "CREATE TABLE IF NOT EXISTS temp_table ("

for (i = 1; i <= length(headers); i++) {

# Escape double quotes in column names

safe_name = headers[i]

gsub(/"/, "\"\"", safe_name)

printf " \"%s\" %s", safe_name, types[i]

if (i < length(headers)) {

print ","

} else {

print ""

}

print ");"

}

' "$csv_file")

# Import CSV to SQLite with proper schema

sqlite3 "$db_file" << EOF

-- Create temporary table with detected types

$create_table_sql

-- Import CSV data

.mode csv

.import "$csv_file" temp_table

-- Create final table with correct name

CREATE TABLE "$table_name" AS SELECT * FROM temp_table;

-- Drop temporary table

DROP TABLE temp_table;

EOF

echo "Successfully created SQLite database: $db_file"

echo "Table '$table_name' contains $(sqlite3 "$db_file" "SELECT COUNT(*) FROM $table_name;") rows"

echo ""

# Show schema

echo "Table schema:"

sqlite3 "$db_file" "PRAGMA table_info($table_name);" | awk -F'|' '{printf " %-20s %s\n", $2, $3}'

echo ""

echo "You can now open this SQLite database in applications like TablePlus for convenient data viewing and manipulation."

As a side note, you can use something like dsq to query a csv file. I’ve stopped using that for this particular task.

Development, Tech Tips

Grouping Focus Bracketed Photos from an OM-1 Mark 2

/ July 12, 2025

The Problem

I have a folder of image (JPG and RAW) files of some focus bracketed shots from my OM-1 Mark 2. I need to find a way to group them… maybe there is exif data that’d help? Or maybe I can use bash to group by timestamp to find all shots within 1 second of the previous shot… if the gap is more than 1 second then it starts a new “series”.

That was my prompt for Claude.

Claude suggested I try this first, to see what’s available:

# Install exiftool if you don't have it
# brew install exiftool  # macOS
# sudo apt install libimage-exiftool-perl  # Ubuntu/Debian

# Check what EXIF data is available
exiftool P7120919.JPG | grep -i bracket
exiftool P7120919.JPG | grep -i focus
exiftool P7120919.JPG | grep -i sequence

# Install exiftool if you don't have it

# brew install exiftool # macOS

# sudo apt install libimage-exiftool-perl # Ubuntu/Debian

# Check what EXIF data is available

exiftool P7120919.JPG | grep -i bracket

exiftool P7120919.JPG | grep -i focus

exiftool P7120919.JPG | grep -i sequence

I ran the commands and replied:

Ooh good news. There appears to be some metadata in both JPG and ORF files.
Focus Bracket Step Size : 5
White Balance Bracket : 0 0
Drive Mode : Focus Bracketing, Shot 7; Electronic shutter

Claude then gave me a bash script. I made a few tweaks, re-prompted, made some tweaks, then it was done.

It turns this flat folder of files:

P7120857.JPG
P7120857.ORF
P7120858.JPG
P7120858.ORF
P7120859.JPG
P7120859.ORF
P7120860.JPG
P7120860.ORF
P7120861.JPG
P7120861.ORF
P7120863.JPG
P7120863.ORF
P7120864.JPG
P7120864.ORF
P7120865.JPG
P7120865.ORF

P7120857.JPG

P7120857.ORF

P7120858.JPG

P7120858.ORF

P7120859.JPG

P7120859.ORF

P7120860.JPG

P7120860.ORF

P7120861.JPG

P7120861.ORF

P7120863.JPG

P7120863.ORF

P7120864.JPG

P7120864.ORF

P7120865.JPG

P7120865.ORF

Into this:

├── focus_bracket_series_1
│   ├── P7120857.JPG
│   ├── P7120857.ORF
│   ├── P7120858.JPG
│   ├── P7120858.ORF
│   ├── P7120859.JPG
│   ├── P7120859.ORF
│   ├── P7120860.JPG
│   ├── P7120860.ORF
│   ├── P7120861.JPG
│   └── P7120861.ORF
├── focus_bracket_series_2
│   ├── P7120863.JPG
│   ├── P7120863.ORF
│   ├── P7120864.JPG
│   ├── P7120864.ORF
│   ├── P7120865.JPG
│   ├── P7120865.ORF

├── focus_bracket_series_1

│ ├── P7120857.JPG

│ ├── P7120857.ORF

│ ├── P7120858.JPG

│ ├── P7120858.ORF

│ ├── P7120859.JPG

│ ├── P7120859.ORF

│ ├── P7120860.JPG

│ ├── P7120860.ORF

│ ├── P7120861.JPG

│ └── P7120861.ORF

├── focus_bracket_series_2

│ ├── P7120863.JPG

│ ├── P7120863.ORF

│ ├── P7120864.JPG

│ ├── P7120864.ORF

│ ├── P7120865.JPG

│ ├── P7120865.ORF

Implementing

Write this script somewhere (e.g., ~/scripts/om1-focus-bracket-grouper.sh )
Make it executable ( chmod +x om1-focus-bracket-grouper.sh )
Go into the flat folder (e.g., cd ~/Pictures/MacroShots )
Run it ( ~/scripts/om1-focus-bracket-grouper.sh )
It will show you what the results will be and then prompt you to hit Y to move the files or N to abort.

#!/bin/bash

# Analyze focus bracket sequences (dry run)
analyze_focus_brackets() {
    echo "=== Focus Bracket Analysis ==="
    echo
    
    local series_num=1
    local prev_shot_num=999
    local series_files=()
    
    # Sort by filename (chronological order) instead of modification time
    for file in $(ls P*.ORF P*.JPG 2>/dev/null | sort); do
        [[ -f "$file" ]] || continue
        
        # Get both drive mode and timestamp for verification
        drive_mode=$(exiftool -DriveMode -T "$file" 2>/dev/null)
        timestamp=$(exiftool -DateTimeOriginal -d "%Y-%m-%d %H:%M:%S" -T "$file" 2>/dev/null)
        
        echo "Processing: $file - Drive Mode: '$drive_mode'"
        
        if [[ "$drive_mode" =~ "Focus Bracketing, Shot "([0-9]+) ]]; then
            shot_num="${BASH_REMATCH[1]}"
            echo "  Found shot number: $shot_num"
            
            # If shot number reset (went backwards), we found a new series
            if [[ $shot_num -lt $prev_shot_num ]]; then
                if [[ ${#series_files[@]} -gt 0 ]]; then
                    echo "Series $((series_num-1)): ${#series_files[@]} shots"
                    printf "  %s\n" "${series_files[@]}"
                    echo
                fi
                
                series_files=()
                echo "--- Starting Series $series_num ---"
                ((series_num++))
            fi
            
            series_files+=("$file (Shot $shot_num, $timestamp)")
            prev_shot_num=$shot_num
        else
            echo "  Not a focus bracket shot, skipping"
        fi
    done
    
    # Print final series
    if [[ ${#series_files[@]} -gt 0 ]]; then
        echo "Series $((series_num-1)): ${#series_files[@]} shots"
        printf "  %s\n" "${series_files[@]}"
    fi
    
    echo
    echo "Total series found: $((series_num-1))"
}

# Function to actually move files after confirmation
move_focus_brackets() {
    echo "Moving files to series folders..."
    
    local series_num=1
    local prev_shot_num=999
    
    for file in $(ls P*.ORF P*.JPG 2>/dev/null | sort); do
        [[ -f "$file" ]] || continue
        
        drive_mode=$(exiftool -DriveMode -T "$file" 2>/dev/null)
        
        if [[ "$drive_mode" =~ "Focus Bracketing, Shot "([0-9]+) ]]; then
            shot_num="${BASH_REMATCH[1]}"
            
            if [[ $shot_num -lt $prev_shot_num ]]; then
                ((series_num++))
            fi
            
            series_dir="focus_bracket_series_$((series_num-1))"
            mkdir -p "$series_dir"
            
            echo "Moving $file to $series_dir"
            mv "$file" "$series_dir/"
            
            prev_shot_num=$shot_num
        fi
    done
}

# Run analysis first
analyze_focus_brackets

echo
read -p "Proceed with moving files? (y/N): " -n 1 -r
echo
if [[ $REPLY =~ ^[Yy]$ ]]; then
    move_focus_brackets
    echo "Done!"
else
    echo "Aborted. Files not moved."
fi

#!/bin/bash

# Analyze focus bracket sequences (dry run)

analyze_focus_brackets() {

echo "=== Focus Bracket Analysis ==="

echo

local series_num=1

local prev_shot_num=999

local series_files=()

# Sort by filename (chronological order) instead of modification time

for file in $(ls P*.ORF P*.JPG 2>/dev/null | sort); do

[[ -f "$file" ]] || continue

# Get both drive mode and timestamp for verification

drive_mode=$(exiftool -DriveMode -T "$file" 2>/dev/null)

timestamp=$(exiftool -DateTimeOriginal -d "%Y-%m-%d %H:%M:%S" -T "$file" 2>/dev/null)

echo "Processing: $file - Drive Mode: '$drive_mode'"

if [[ "$drive_mode" =~ "Focus Bracketing, Shot "([0-9]+) ]]; then

shot_num="${BASH_REMATCH[1]}"

echo " Found shot number: $shot_num"

# If shot number reset (went backwards), we found a new series

if [[ $shot_num -lt $prev_shot_num ]]; then

if [[ ${#series_files[@]} -gt 0 ]]; then

echo "Series $((series_num-1)): ${#series_files[@]} shots"

printf " %s\n" "${series_files[@]}"

echo

series_files=()

echo "--- Starting Series $series_num ---"

((series_num++))

series_files+=("$file (Shot $shot_num, $timestamp)")

prev_shot_num=$shot_num

else

echo " Not a focus bracket shot, skipping"

done

# Print final series

if [[ ${#series_files[@]} -gt 0 ]]; then

echo "Series $((series_num-1)): ${#series_files[@]} shots"

printf " %s\n" "${series_files[@]}"

echo

echo "Total series found: $((series_num-1))"

}

# Function to actually move files after confirmation

move_focus_brackets() {

echo "Moving files to series folders..."

local series_num=1

local prev_shot_num=999

for file in $(ls P*.ORF P*.JPG 2>/dev/null | sort); do

[[ -f "$file" ]] || continue

drive_mode=$(exiftool -DriveMode -T "$file" 2>/dev/null)

if [[ "$drive_mode" =~ "Focus Bracketing, Shot "([0-9]+) ]]; then

shot_num="${BASH_REMATCH[1]}"

if [[ $shot_num -lt $prev_shot_num ]]; then

((series_num++))

series_dir="focus_bracket_series_$((series_num-1))"

mkdir -p "$series_dir"

echo "Moving $file to $series_dir"

mv "$file" "$series_dir/"

prev_shot_num=$shot_num

done

}

# Run analysis first

analyze_focus_brackets

echo

read -p "Proceed with moving files? (y/N): " -n 1 -r

echo

if [[ $REPLY =~ ^[Yy]$ ]]; then

move_focus_brackets

echo "Done!"

else

echo "Aborted. Files not moved."

AI

My AI Journey

/ May 27, 2025

AI Was… Something

August 2020
Began using AI in products like Topaz Sharpen AI, Gigapixel AI, Denoise AI, etc.

June 2022
Began hearing about LLMs.

December 2022
Experienced AI music backing track generation in the Spark 40 guitar amplifier software.

Early Days: Discovery & Wonder

December 2022
Began using ChatGPT. This was my first exposure to LLM-based “AI” as we know it today.

It didn’t take long to realize the importance of having a conversation — of not giving up after the first response. My early use-cases were general knowledge inquiries and poetry generation. It was silly, but it was useful.

My son and I spent a lot of time talking to ChatGPT on the way to his school each day. “How long ago were dinosaurs around?… [answered]… oh, cool! Why aren’t they around anymore?”

read more
Tech Tips

Improved Keyboard Maestro Palettes

/ May 18, 2025

Using some simple emojis, icons, numbers, and spacing you can supercharge your Keyboard Maestro palettes.

It takes a little bit of time to get the spacing right, and it isn’t perfect, but these changes certainly make the palette more useful. Here’s the process.

Step 1 – Change the Sort Order of the Macros

Prepend “##)” to each macro. The number indicates the position. I like to give some breathing room between each “group”. In this example I ended up with this:

00) README 10) Accept 11) Reject 20) Open 21) Close 30) Rotate Left 31) Rotate Right

1
2
3
4
5
6
7

00) README
10) Accept
11) Reject
20) Open
21) Close
30) Rotate Left
31) Rotate Right

read more
Development, Tech Tips

Using a Game Controller to Speed Up Tedious Mac Tasks

/ February 16, 2025
FULL DISCLOSURE: I personally prefer using Keyboard Maestro over Hammerspoon for handling the “actions” in this tutorial. That said, the Hammerspoon approach seemed worth mentioning, even if I don’t have much confidence in my Lua code — so here it is, just in case it’s useful!

If you spend a lot of time performing repetitive tasks on your Mac, using a game controller as an input device can make things much more efficient—and more comfortable. Whether you’re organizing files, editing video, or culling photos, a controller lets you execute common actions with minimal effort.

In this post, I’ll walk through how I use a game controller to speed up photo culling—the process of selecting the best images from a shoot. While this tutorial focuses on photo review, the same approach can be applied to many other workflows.

Culling is an essential but tedious part of a photographer’s workflow. I’ve spent countless hours sorting through tens of thousands of images in ExcireFoto 2025 and Adobe Lightroom, and I quickly realized that using just a keyboard and trackpad was slowing me down. That’s when I started experimenting with using a USB/Bluetooth game controller for culling. Instead of hunching over a keyboard, I can sit back and control everything with a lightweight, ergonomic device in my hands.

Here’s why it works so well:
1. Ergonomic – Game controllers are made to fit your hands. It’s natural to hold them for hours at a time.
2. Quicker actions with muscle memory – Pressing controller buttons becomes second nature, making repetitive tasks much faster.
3. Keeps your eyes on the screen – No need to glance at your keyboard; your fingers always know where to go.
4. Better posture and comfort – You can position your monitor optimally without worrying about your keyboard.
5. Simple to program – Setting up button mappings takes only minutes.
6. Free software – You don’t need expensive tools to get this working.
7. Gamifies tedious work – Making a dull task feel more interactive can increase efficiency.
8. More control over navigation – Assign multiple actions to different buttons or combinations.
9. Works for more than just culling – This method applies to video editing, file organization, and more.
I’ve done various iterations of this over time. Today I realized I’ve never blogged about it. So, here goes!
read more
Development, Tech Tips

Double-tap Modifier Hotkeys in Any Application

/ November 12, 2024

PHPStorm has this great feature where you can double-tap the shift key to open the “Search Everywhere” utility (or at least that’s how I’ve configured my PHPStorm). It’s wonderful and I use it all day. I wanted the same behavior in some of my other applications but quickly realized it’s not achievable out-of-the-box, even with Keyboard Maestro. I tried to use the double-tap usb device key as a trigger but it didn’t work. I ended up solving the problem using Karabiner Elements.

The idea is simple: Use Karabiner Elements to turn a double-tapped left shift into a hotkey I’d not realistically have set up in any application, then use that hotkey as the hotkey in Keyboard Maestro. I chose <cmd-shift-opt-ctrl-f>.

First, here is the code, which you can create as a Complex Modification in Karabiner Elements:

read more
Development

Laravel + Filament + Private Disk on Cloudways

/ September 18, 2024

Introduction

After a long, frustrating debugging exercise, I have discovered a quick tip for my Cloudways + Laravel friends. The “why” is still a touch fuzzy for me at the moment, but I’ll at least explain the symptoms and the fix.

The goal: Add a private file upload to a Filament resource form, ensuring anonymous users cannot download/view the file.

The issue: Everything was working great in my ddev environment. I could preview and open the files if I was logged in, and I would see a 403 if I was logged out. When I moved this to a Cloudways server I was getting automatically logged-out every time I hit a private file URL. Weird, right?

Code

Here’s the final code (the Cloudways “fix” is further down this page):

read more
Tech Tips

Using OBS and Blackhole as an Audio Router

/ July 22, 2024
I’ve been having a difficult timing capturing aggregate audio from my Shure MV7 microphone and my system audio. After trying the BlackHole + Audio Midi Setup unsuccessfully I found some information about how to use OBS Studio + BlackHole to configure, monitor, and send audio from many sources into a single output.

Using this configuration I’m able to record, transcribe, etc. all of this audio as if it were a single input source. An added bonus is that I can monitor the input, adjust levels, etc. from the OBS Studio user interface.

For what it’s worth, this entire process seems to work exactly the same with the VB-CABLE Virtual Audio Device. Just use VB-CABLE instead of BlackHole 16ch in steps 3 and 5 below.

Step 1: Install Blackhole ( brew install blackhole-16ch )

Step 2: Install OBS Studio

Step 3: Open OBS Studio’s Settings. Set Audio ➙ Advanced ➙ Monitoring Device to BlackHole 16ch

Step 4: Add macOS Audio Capture sources to OBS Studio and configure each to use Monitor and Output (monitor is the important piece). In the example below I’m getting audio from my mic and two specific applications (Chrome and Zoom).

Step 5: Use BlackHole 16ch as the “microphone” or “input” in the app of your choosing (audio recorder, transcription tool, etc.)

Bonus Tip

What if you want to control your microphone output to Zoom (or whatever) from within OBS Studio? You can use a second virtual audio device! Using the OBS Audio Monitor plugin you can route the actual audio “Output” from OBS Studio to whatever device you choose. The “Audio Mixer” dock controls the output to BlackHole (via the “Monitor Only” setting and corresponding Monitor -> BlackHole 16ch application setting), and the Audio Monitor dock controls output to VB-CABLE (via the “Monitor and Output” setting).
1. Install the VB-CABLE virtual audio device
2. Install the OBS Audio Monitor plugin
3. Enable the Audio Monitor dock
4. Configure the output to go to VB-CABLE
5. In whichever apps you’d like, use VB-CABLE as the “Microphone” (input) instead of your actual microphone.
6. Make sure only your microphone is set to “Monitor and Output”
7. If desired, you can set a hotkey to mute/unmute your microphone via Settings ➙ Hotkeys ➙ Shure MV7 in (for example)
  
  You can set the same hotkey to both mute and unmute, and it’s a universal hotkey that is available even if OBS isn’t the front window.
Bonus Bonus Tip

One more for ya! Enabling the Waveform plugin in the preview area is a great way to have a visual reminder when your microphone is “hot.”

After disabling all of the unneeded docks here’s what I’m left with:

You can even add additional waveforms for your other input sources:
read more
Tech Tips

You Can’t Fit a Square Title into a Round Url

/ April 30, 2024
For whatever reason I always mix up the format for markdown links.

Here are the possibilities (only one is valid):
1. [https://agileadam.com](View my site)
2. [View my site](https://agileadam.com) ← the correct one!
3. (https://agileadam.com)[View my site]
4. (View my site)[https://agileadam.com]
I’ve come up with a mnemonic I’m going to try employing:

You can’t fit a square title into a round url.

It’s a variation of the idiom, “You can’t fit a square peg into a round hole.”

Square brackets. Round parentheses. You get it.

Update July 12, 2024

This still isn’t landing for me. “Can’t fit a square peg into a round hole” is easy to remember… but which is the peg and which is the hole? Square URL? Square Title? Round Title? Hmmm.

Maybe an acronym would serve me better? Here are my initial thoughts:
- “SLAP” (Square Label, Address in Parentheses)
- “SNAP” (Square Name, Address in Parentheses)
- “SLAP” (Square Label, Address in Parentheses)
If you take a closer look, it’d be easy to accidentally assume “L” means “link” (aka URL), so maybe I should rule those out. That leaves me with “SNAP”, which seems like it’d be worth trying for awhile.

I wish I knew what drives me to go down these rabbit holes.
read more

Development

Writing Tests for Drush Commands

/ March 21, 2024

There are plenty of examples of these in the wild, but I figured I’d show a stripped down version of an automated Kernel test that successfully tests a drush command. The trick here is making sure you set up a logger and that you stub a few methods (if you happen to use $this->logger() and dt() in your Drush commands). Also featured in this example is the use of Faker to generate realistic test data.

<?php

declare(strict_types = 1);

namespace Drupal\mymodule\Tests\Kernel {

  use Drupal\KernelTests\KernelTestBase;
  use Drupal\mymodule\Drush\Commands\QueueCommands;
  use Drush\Log\DrushLoggerManager;
  use Faker\Factory;
  use Faker\Generator;
  use Psr\Log\LoggerInterface;

  /**
   * Tests the QueueCommands Drush command file.
   */
  final class QueueTest extends KernelTestBase {

    /**
     * {@inheritdoc}
     */
    protected static $modules = [
      // Enable all the modules that this test relies on.
      'auto_entitylabel',
      'block',
      'datetime',
      'eck',
      'field',
      'file',
      'image',
      'link',
      'mymodule',
      'mysql',
      'options',
      'path',
      'path_alias',
      'system',
      'text',
      'user',
      'views',
      'views_bulk_operations',
    ];

    /**
     * The Faker generator.
     *
     * @var \Faker\Generator
     */
    protected Generator $faker;

    /**
     * The logger.
     *
     * @var \Drush\Log\DrushLoggerManager|\Psr\Log\LoggerInterface
     */
    protected DrushLoggerManager|LoggerInterface $logger;

    /**
     * The Drush queue commands.
     *
     * @var \Drupal\mymodule\Drush\Commands\QueueCommands
     */
    protected QueueCommands $drushQueueCommands;

    /**
     * Set up the test environment.
     */
    protected function setUp(): void {
      parent::setUp();

      // Install required config.
      $this->installConfig(['mymodule']);

      $this->faker = Factory::create();
      $logger_class = class_exists(DrushLoggerManager::class) ? DrushLoggerManager::class : LoggerInterface::class;
      $this->logger = $this->prophesize($logger_class)->reveal();
      $drushQueueCommands = new QueueCommands();
      $drushQueueCommands->setLogger($this->logger);
    }

    /**
     * Tests the mymodule:close-all-registration-entities drush command.
     */
    public function testCloseAllRegistrationEntities() {
      // Create a single registration entity.
      // Just demoing simple Faker usage for blog post.
      $registration = \Drupal::entityTypeManager()
        ->getStorage('registration')
        ->create([
          'type' => 'appt',
          'field_echeckin_link' => $this->faker->url(),
          'field_epic_id' => $this->faker->randomNumber(9),
          'field_first_name' => $this->faker->firstName(),
          'field_has_echeckin_link' => $this->faker->boolean(),
          'field_outcome' => NULL,
          'field_phone_number' => $this->faker->phoneNumber(),
          'field_sequence_is_open' => 1,
        ]);
      $registration->save();

      // This method exists in my module's Drush command file.
      // It sets field_sequence_is_open field to 0 for all open reg entities.
      // drush mymodule:close-all-registration-entities.
      $this->drushQueueCommands->closeAllRegistrationEntities();

      // Assert that the registration entity was updated.
      $this->assertCount(1, \Drupal::entityTypeManager()
        ->getStorage('registration')
        ->loadByProperties(['field_sequence_is_open' => 0]));
    }

  }
}

namespace {
  if (!function_exists('dt')) {

    /**
     * Stub for dt().
     *
     * @param string $message
     *   The text.
     * @param array $replace
     *   The replacement values.
     *
     *   The text.
     */
    function dt($message, array $replace = []): string {
      return strtr($message, $replace);
    }

  }

  if (!function_exists('drush_op')) {

    /**
     * Stub for drush_op.
     *
     * @param callable $callable
     *   The function to call.
     */
    function drush_op(callable $callable) {
      $args = func_get_args();
      array_shift($args);
      return call_user_func_array($callable, $args);
    }

  }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

<?php

declare(strict_types = 1);

namespace Drupal\mymodule\Tests\Kernel {

use Drupal\KernelTests\KernelTestBase;

use Drupal\mymodule\Drush\Commands\QueueCommands;

use Drush\Log\DrushLoggerManager;

use Faker\Factory;

use Faker\Generator;

use Psr\Log\LoggerInterface;

/**

* Tests the QueueCommands Drush command file.

final class QueueTest extends KernelTestBase {

/**

* {@inheritdoc}

protected static $modules = [

// Enable all the modules that this test relies on.

'auto_entitylabel',

'block',

'datetime',

'eck',

'field',

'file',

'image',

'link',

'mymodule',

'mysql',

'options',

'path',

'path_alias',

'system',

'text',

'user',

'views',

'views_bulk_operations',

];

/**

* The Faker generator.

* @var \Faker\Generator

protected Generator $faker;

/**

* The logger.

* @var \Drush\Log\DrushLoggerManager|\Psr\Log\LoggerInterface

protected DrushLoggerManager|LoggerInterface $logger;

/**

* The Drush queue commands.

* @var \Drupal\mymodule\Drush\Commands\QueueCommands

protected QueueCommands $drushQueueCommands;

/**

* Set up the test environment.

protected function setUp(): void {

parent::setUp();

// Install required config.

$this->installConfig(['mymodule']);

$this->faker = Factory::create();

$logger_class = class_exists(DrushLoggerManager::class) ? DrushLoggerManager::class : LoggerInterface::class;

$this->logger = $this->prophesize($logger_class)->reveal();

$drushQueueCommands = new QueueCommands();

$drushQueueCommands->setLogger($this->logger);

}

/**

* Tests the mymodule:close-all-registration-entities drush command.

public function testCloseAllRegistrationEntities() {

// Create a single registration entity.

// Just demoing simple Faker usage for blog post.

$registration = \Drupal::entityTypeManager()

->getStorage('registration')

->create([

'type' => 'appt',

'field_echeckin_link' => $this->faker->url(),

'field_epic_id' => $this->faker->randomNumber(9),

'field_first_name' => $this->faker->firstName(),

'field_has_echeckin_link' => $this->faker->boolean(),

'field_outcome' => NULL,

'field_phone_number' => $this->faker->phoneNumber(),

'field_sequence_is_open' => 1,

]);

$registration->save();

// This method exists in my module's Drush command file.

// It sets field_sequence_is_open field to 0 for all open reg entities.

// drush mymodule:close-all-registration-entities.

$this->drushQueueCommands->closeAllRegistrationEntities();

// Assert that the registration entity was updated.

$this->assertCount(1, \Drupal::entityTypeManager()

->getStorage('registration')

->loadByProperties(['field_sequence_is_open' => 0]));

}

namespace {

if (!function_exists('dt')) {

/**

* Stub for dt().

* @param string $message

* The text.

* @param array $replace

* The replacement values.

* The text.

function dt($message, array $replace = []): string {

return strtr($message, $replace);

}

if (!function_exists('drush_op')) {

/**

* Stub for drush_op.

* @param callable $callable

* The function to call.

function drush_op(callable $callable) {

$args = func_get_args();

array_shift($args);

return call_user_func_array($callable, $args);

}

I learned this via the migrate_tools project here.

The Problem

Implementing

AI Was… Something

Early Days: Discovery & Wonder

Step 1 – Change the Sort Order of the Macros

Introduction

Code

Bonus Tip

Bonus Bonus Tip

Update July 12, 2024