Release Train Statistics

Generate statistics for Renovate release trains based on commit and release data.

Setup

1. Create the database:

   sqlite3 release_train_stats.db < schema.sql

2. Insert data for all release trains using git commands:

   # Insert all releases
   git tag -l --format="%(refname:short)|%(creatordate:short)" | ruby insert_data.rb --tags
   
   # Insert commits for version 41
   git log 41.0.0..42.0.0 --pretty=format:"%H|%cd|%s" --date=short | ruby insert_data.rb
   
   # Insert commits for version 42
   git log 42.0.0..43.0.0 --pretty=format:"%H|%cd|%s" --date=short | ruby insert_data.rb
   
   # etc...

3. Run queries to generate stats:

   # Stats for a specific version (replace <MAJOR_VERSION> with 41, 42, etc.)
   sqlite3 release_train_stats.db < queries.sql
   # i.e.
   sed 's/<MAJOR_VERSION>/41/g' queries.sql | sqlite3 release_train_stats.db
   
   # Or run specific queries
   sqlite3 release_train_stats.db "SELECT type, COUNT(*) FROM commits WHERE major_version = 41 GROUP BY type;"
   
   # Compare all versions
   sqlite3 release_train_stats.db "SELECT major_version, COUNT(*) FROM commits GROUP BY major_version;"

Database Schema

commits table

• sha: Unique commit hash
• commit_date: Date of the commit
• message: Full commit message
• type: Conventional commit type (feat, fix, chore, etc.)
• scope: Conventional commit scope
• is_dependency_update: Boolean flag for dependency updates
• release_tag: Associated release tag (e.g., '41.0.0')
• major_version: Major version number (e.g., 41) - auto-extracted from release_tag

releases table

• tag: Release tag (e.g., '41.0.0')
• release_date: Date of the release
• is_minor: Boolean flag for minor releases (vs patch)
• major_version: Major version number (e.g., 41)
• minor_version: Minor version number (e.g., 0, 1, 2)
• patch_version: Patch version number (e.g., 0, 1, 2)

Inserting Data

From Git Commands (Recommended)

Pipe git output directly into the script:

Insert commits from a release train:

# For commits in version 41.x
git -C ../renovate log 41.0.0..42.0.0 --pretty=format:"%H|%cd|%s" --date=short | ruby insert_data.rb --major-version 41

# or, more iterable
MAJOR=39
git -C ../renovate log ${MAJOR}.0.0..$((MAJOR+1)).0.0 --pretty=format:"%H|%cd|%s" --date=short | ruby insert_data.rb --major-version $MAJOR

Insert releases:

# For all 41.x tags
git -C ../renovate tag -l "41.*" --format="%(refname:short)|%(creatordate:short)" | ruby insert_data.rb --tags

From Ruby Code

The script also provides helper functions for programmatic use:

require_relative 'insert_data'

db = get_db

# Single commit
insert_commit(db,
  sha: 'abc123',
  date: '2025-06-19',
  message: 'feat(presets): add new preset',
  release_tag: '41.0.0'
)

# Batch commits
commits = [
  { sha: 'abc123', date: '2025-06-19', message: 'feat: new feature' },
  { sha: 'def456', date: '2025-06-20', message: 'fix: bug fix' }
]
insert_commits_batch(db, commits)

# Batch releases
releases = [
  { tag: '41.0.0', date: '2025-06-19', is_minor: true },
  { tag: '41.0.1', date: '2025-06-20', is_minor: false }
]
insert_releases_batch(db, releases)

Querying Multiple Release Trains

The database now supports storing commits and releases across multiple major versions. Use major_version to filter:

# Stats for version 41
sqlite3 release_train_stats.db "SELECT COUNT(*) FROM commits WHERE major_version = 41;"

# Compare all versions
sqlite3 release_train_stats.db "SELECT major_version, COUNT(*) as commits FROM commits GROUP BY major_version ORDER BY major_version DESC;"

# Use the queries.sql file (replace <MAJOR_VERSION> with your version)
sed 's/<MAJOR_VERSION>/41/g' queries.sql | sqlite3 release_train_stats.db

Notes

• Conventional commit parsing is automatic (extracts type and scope)
• Dependency updates are auto-detected based on common patterns
• Minor releases are determined by semantic versioning (X.Y.0)
• Version numbers are automatically extracted from release tags