Package manager schema

gistfile1.sql

BEGIN TRANSACTION;
PRAGMA foreign_keys = OFF;

CREATE TABLE manifest (
    key     TEXT    NOT NULL UNIQUE,
    value   TEXT
);  

CREATE UNIQUE INDEX ix_manifest_key ON manifest (key);

CREATE TABLE repository (
    id          INTEGER     PRIMARY KEY,
    type        TEXT        NOT NULL,
    name        TEXT        NOT NULL,
    path        TEXT        NOT NULL
);  

CREATE TABLE package (
    id          SHA1        NOT NULL,
    name        TEXT        NOT NULL,
    host_arch   TEXT        CHECK(valid_architecture(host_arch)),
    target_arch TEXT        CHECK(valid_architecture(target_arch)),
    version     TEXT        NOT NULL COLLATE version,
    manifest    BLOB        NOT NULL
);

CREATE INDEX ix_package ON package 
    (name, version, host_arch, target_arch);

CREATE TABLE package_source (
    package     SHA1        NOT NULL,
    repository  INTEGER     NOT NULL,

    FOREIGN KEY (package)       REFERENCES package(id),
    FOREIGN KEY (repository)    REFERENCES repository(id)
);

CREATE TABLE feature_candidate (
    id          INTEGER     PRIMARY KEY,
    name        TEXT        NOT NULL,
    version     TEXT        COLLATE version,
    host_arch   TEXT        CHECK(valid_architecture(host_arch)),
    target_arch TEXT        CHECK(valid_architecture(target_arch)),
    package     SHA1        NOT NULL,
    installed   BOOL        DEFAULT 0,

    FOREIGN KEY (package) REFERENCES package(id)
);

CREATE UNIQUE INDEX ix_feature_candidate_package on feature_candidate 
    (name, package, host_arch, target_arch);

CREATE TABLE selected_feature (
    feature     INTEGER     NOT NULL UNIQUE,
    name        TEXT        NOT NULL,
    host_arch   TEXT        CHECK(valid_architecture(host_arch)),
    target_arch TEXT        CHECK(valid_architecture(target_arch)),

    FOREIGN KEY (feature) REFERENCES feature_candidate(id)
);

CREATE UNIQUE INDEX ix_selected_feature ON selected_feature (name, host_arch, target_arch);

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;
COMMIT TRANSACTION;