db.sqlite

# sqlite3 apt-cacher-rs.db < db.sqlite

CREATE TABLE IF NOT EXISTS mirrors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    host TEXT NOT NULL,
    path TEXT NOT NULL,
    first_seen INTEGER NOT NULL DEFAULT (unixepoch(CURRENT_TIMESTAMP)),
    last_seen INTEGER NOT NULL DEFAULT (unixepoch(CURRENT_TIMESTAMP)),
    last_cleanup INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT first UNIQUE (host, path)
) STRICT;

CREATE TABLE IF NOT EXISTS origins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    mirror_id INTEGER NOT NULL,
    distribution TEXT NOT NULL,
    component TEXT NOT NULL,
    architecture TEXT NOT NULL,
    first_seen INTEGER NOT NULL DEFAULT (unixepoch(CURRENT_TIMESTAMP)),
    last_seen INTEGER NOT NULL DEFAULT (unixepoch(CURRENT_TIMESTAMP)),
    CONSTRAINT first UNIQUE (mirror_id, distribution, component, architecture)
) STRICT;

CREATE TABLE IF NOT EXISTS downloads (
    mirror_id INTEGER NOT NULL,
    debname TEXT NOT NULL,
    size INTEGER NOT NULL,
    duration INTEGER NOT NULL,
    client_ip BLOB NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch(CURRENT_TIMESTAMP))
) STRICT;

CREATE TABLE IF NOT EXISTS deliveries (
    mirror_id INTEGER NOT NULL,
    debname TEXT NOT NULL,
    size INTEGER NOT NULL,
    duration INTEGER NOT NULL,
    partial INTEGER NOT NULL,
    client_ip BLOB NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch(CURRENT_TIMESTAMP))
) STRICT;