schema.sql

-- Release train statistics database schema

CREATE TABLE IF NOT EXISTS commits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sha TEXT UNIQUE NOT NULL,
    commit_date DATE NOT NULL,
    message TEXT NOT NULL,
    type TEXT,
    scope TEXT,
    is_dependency_update BOOLEAN DEFAULT 0,
    release_tag TEXT,
    major_version INTEGER
);

CREATE TABLE IF NOT EXISTS releases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tag TEXT UNIQUE NOT NULL,
    release_date DATE NOT NULL,
    is_minor BOOLEAN DEFAULT 0,
    major_version INTEGER,
    minor_version INTEGER,
    patch_version INTEGER
);

-- Indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_commits_type ON commits(type);
CREATE INDEX IF NOT EXISTS idx_commits_scope ON commits(scope);
CREATE INDEX IF NOT EXISTS idx_commits_date ON commits(commit_date);
CREATE INDEX IF NOT EXISTS idx_commits_release ON commits(release_tag);
CREATE INDEX IF NOT EXISTS idx_commits_dep_update ON commits(is_dependency_update);
CREATE INDEX IF NOT EXISTS idx_commits_major_version ON commits(major_version);
CREATE INDEX IF NOT EXISTS idx_releases_date ON releases(release_date);
CREATE INDEX IF NOT EXISTS idx_releases_major_version ON releases(major_version);