BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS issues(
        id INTEGER PRIMARY KEY,
        hashkey TEXT NOT NULL UNIQUE,
        uid TEXT NOT NULL,
        type INTEGER NOT NULL DEFAULT 1,
        mtime INTEGER DEFAULT CURRENT_TIMESTAMP,
        ctime INTEGER DEFAULT CURRENT_TIMESTAMP,
        atime INTEGER DEFAULT CURRENT_TIMESTAMP,
        count INTEGER DEFAULT 1,
        deleted INTEGER DEFAULT 0,
        resolved INTEGER DEFAULT 0,
        locked INTEGER DEFAULT 0,
        locked_by TEXT,
        locked_time INTEGER,
        resolver TEXT,
        message TEXT,
        loadname TEXT DEFAULT '<Unbekannt>',
        obj TEXT DEFAULT '<Unbekannt>',
        prog TEXT DEFAULT '<Unbekannt>',
        loc INTEGER,
        titp TEXT,
        tienv TEXT,
        hbobj TEXT,
        caught INTEGER,
        command TEXT,
        verb TEXT);
CREATE TABLE IF NOT EXISTS notes(
        issueid INTEGER,
        time INTEGER DEFAULT CURRENT_TIMESTAMP,
        user STRING NOT NULL,
        txt STRING,
        FOREIGN KEY(issueid) REFERENCES issues(id) ON DELETE CASCADE ON UPDATE CASCADE
        );
CREATE TABLE IF NOT EXISTS stacktraces(
        issueid INTEGER,
        type INTEGER,
        name STRING,
        prog STRING,
        obj STRING,
        loc INTEGER,
        ticks INTEGER,
        FOREIGN KEY(issueid) REFERENCES issues(id) ON DELETE CASCADE ON UPDATE CASCADE
    );
CREATE INDEX IF NOT EXISTS idx_issues_uid on issues(uid);
CREATE INDEX IF NOT EXISTS idx_issues_prog on issues(prog);
CREATE INDEX IF NOT EXISTS idx_issues_loadname on issues(loadname);
CREATE INDEX IF NOT EXISTS idx_issues_type on issues(type);
CREATE INDEX IF NOT EXISTS idx_issues_obj on issues(obj);
CREATE INDEX IF NOT EXISTS idx_notes_id on notes(issueid);
CREATE INDEX IF NOT EXISTS idx_stacktraces_id on stacktraces(issueid);
COMMIT;