Source

AURLite / aurlite.sql

Full commit
-- One-to-many author to packages
CREATE TABLE author (
    author_id INTEGER PRIMARY KEY,
    txt       TEXT    COLLATE NOCASE
) ;

-- The center of it all.
CREATE TABLE pkg (
    pkg_id      INTEGER PRIMARY KEY,
    author_id   INTEGER,
    name_id     INTEGER,
    version     TEXT,
    description TEXT,
    url         TEXT,
    aur_id      INTEGER
) ;

CREATE TABLE pkg_name (
    name_id INTEGER PRIMARY KEY,
    txt     TEXT
) ;

-- Extra names for multi-packages...
CREATE TABLE pkg_mname (
    mname_id     INTEGER PRIMARY KEY,
    pkg_id       INTEGER,
    name_id      INTEGER
) ;

-- Many-to-many packages to license names...
CREATE TABLE pkg_license (
    license_id      INTEGER PRIMARY KEY,
    pkg_id          INTEGER,
    license_name_id INTEGER
) ;

CREATE TABLE license_name (
    license_name_id INTEGER PRIMARY KEY,
    txt             TEXT
) ;

-- Many-to-many packages to arch.
CREATE TABLE pkg_arch (
    arch_id      INTEGER PRIMARY KEY,
    pkg_id       INTEGER,
    arch_name_id INTEGER
) ;

CREATE TABLE arch_name (
    arch_name_id INTEGER PRIMARY KEY,
    txt          TEXT
) ;

-- One-to-many provides, depends, makedepends, conflicts...
CREATE TABLE pkg_provide (
    provide_id INTEGER PRIMARY KEY,
    pkg_id     INTEGER,
    name_id    INTEGER,
    version    TEXT
) ;

CREATE TABLE pkg_dep (
    dep_id  INTEGER PRIMARY KEY,
    pkg_id  INTEGER,
    name_id INTEGER,
    version TEXT
) ;

CREATE TABLE pkg_makedep (
    makedep_id INTEGER PRIMARY KEY,
    pkg_id     INTEGER,
    name_id    INTEGER,
    version    TEXT
) ;

CREATE TABLE pkg_optdep (
    optdep_id INTEGER PRIMARY KEY,
    pkg_id    INTEGER,
    name_id   INTEGER,
    detail    TEXT
) ;

CREATE TABLE pkg_conflict (
    conflict_id INTEGER PRIMARY KEY,
    pkg_id      INTEGER,
    name_id     INTEGER,
    version     TEXT
) ;

-- CREATE TABLE pkg_sources (
--     source_id  INTEGER PRIMARY KEY,
--     pkg_id     INTEGER,
--     url        TEXT
-- ) ;

-- CREATE TABLE source_checksums (
--     checksum_id INTEGER PRIMARY KEY,
--     source_id   INTEGER,
--     type        INTEGER,
--     value       TEXT
-- ) ;

CREATE INDEX pkg_index    ON pkg_name ( txt ) ;
CREATE INDEX author_index ON author   ( txt COLLATE NOCASE );