pkgdash/internal/database/migrations/sqlite/000001_init_schema.up.sql

create table if not exists comments (
    id integer primary key autoincrement not null,
    comment text,
    package integer not null,
    created timestamp not null default current_timestamp,
    updated timestamp not null default current_timestamp
);


create table if not exists issues (
    id integer primary key autoincrement not null,
    status integer default 0,
    comment varchar,
    created timestamp not null default current_timestamp,
    updated timestamp not null default current_timestamp
);

create table if not exists handlers (
    id integer primary key autoincrement not null,
    package integer not null,
    name varchar,
    coverage number default 0
);

create table if not exists packages (
    id integer primary key autoincrement not null,
    name varchar not null,
    url varchar not null,
    description varchar,
    modules integer default 0,
    issues integer default 0,
    comments integer default 0,
    coverage number default 0,
    created timestamp not null default current_timestamp,
    updated timestamp not null default current_timestamp,
    status integer default 1,
    last_check timestamp
);

CREATE UNIQUE INDEX IF NOT EXISTS unique_idx_url on packages (url);

create table if not exists modules (
    id integer primary key autoincrement not null,
    name varchar not null,
    version varchar not null,
    last_check timestamp not null default current_timestamp
);

CREATE UNIQUE INDEX IF NOT EXISTS unique_idx_name_version on modules (name,version);

create table if not exists packages_modules (
    id integer primary key autoincrement not null,
    package integer,
    module integer not null
);

CREATE UNIQUE INDEX IF NOT EXISTS unique_idx_package_module on packages_modules (package,module);