CREATE TABLE package (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	version TEXT NOT NULL,
	architecture TEXT NOT NULL,
	source TEXT NOT NULL,
	hasscripts BOOL,
	multiarch TEXT
		CHECK (multiarch IN ('allowed', 'foreign', 'no', 'same')),
	UNIQUE (name, architecture));

CREATE TABLE depends (
	id INTEGER PRIMARY KEY,
	pid INTEGER NOT NULL REFERENCES package(id) ON DELETE CASCADE,
	dependee TEXT NOT NULL,
        archqual TEXT);

CREATE TABLE provides (
	id INTEGER PRIMARY KEY,
	pid INTEGER NOT NULL REFERENCES package(id) ON DELETE CASCADE,
	provided TEXT NOT NULL);

CREATE TABLE content (
	id INTEGER PRIMARY KEY,
	pid INTEGER NOT NULL REFERENCES package(id) ON DELETE CASCADE,
	filename TEXT NOT NULL,
	hash TEXT NOT NULL);

CREATE INDEX depends_pid_index ON depends(pid);
CREATE INDEX provides_pid_index ON provides(pid);
CREATE INDEX content_pid_index ON content(pid);
