begin;

PRAGMA application_id = 0x5a504442;
PRAGMA user_version = 1;

-- should be faster with rowid due to the blob content
-- these are really just blobs of data
-- TODO copyright and license information should probably
-- go here
CREATE TABLE files (
	hash text primary key,
       	size integer,
       	compression text,
       	content blob
)
;

-- information about packages
-- a package is identified by a package,version,release triple
create table packages (
	-- primary key columns
	package	text,
	version	text, -- the upstream version string
	release	integer, -- the local release number

	-- metadata columns
	description	text,
	architecture	text,
	url	text,
	licenses	text, -- hash of actual license?  need table for more than one?
	packager	text,
	build_time	integer default (strftime('%s', 'now')),
	install_time	integer,
	checksum	text, -- checksum of package contents.  null for incompleted packages
	primary key (package,version,release)
)
without rowid
;

-- files contained in a package
create table packagefiles (
	-- package id triple
	package	text,
	version	text,
	release	integer,

	path	text, -- filesystem path
	mode	text, -- perms, use text for octal rep?
	username	text, -- name of owner
	groupname	text, -- group of owner
	uid	integer, -- numeric uid, generally ignored
	gid	integer, -- numeric gid, generally ignored
	filetype varchar default 'r',
	-- r regular file
	-- d directory
	-- s symlink
	-- h hard link -- not supported
	-- c character special and b device special files add dev number column
	-- b block special
	-- p fifos (i.e. pipe)
	target	text, -- link target for links
	-- device file dev numbers, should probably be a separate table
	devmajor	integer,
	devminor	integer,
	hash	text, -- null if no actual content, i.e. anything but a regular file
	mtime	integer, -- seconds since epoch, finer resolution probably not needed
	primary key (package,version,release,path),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade
)
without rowid
;

create table packagefiletags (
	-- package id triple
	package	text,
	version	text,
	release	integer,

	path	text, -- filesystem path
	tag	text,
	primary key (package,version,release,path,tag)
)
without rowid
;

create table elfinfo (
	file	text, -- hash of blob
	elftype	text,
	foreign key (file) references files on delete cascade
);

-- TODO just elf information?
-- and just hash, not package?
create table elflibraries (
	file	text primary key,
	soname	text,
	foreign key (file) references files on delete cascade
)
without rowid
;

create table elfneeded (
	file	text,
	needed	text, -- soname of dependency
	primary key (file, needed),
	foreign key (file) references files on delete cascade
)
without rowid
;

-- package scripts: table of package, stage, file
create table scripts (
	package	text,
	version	text,
	release	integer,
	stage	text,
	hash	text
);

-- package dependencies: table of package, dependency, dep type (package, soname)
create table packagedeps (
	package	text,
	version	text,
	release	integer,
	required	text, -- package name
	-- following can be null for not checked
	minversion	text,
	minrelease	integer,
	maxversion	text,
	maxrelease	integer
);

-- capability labels
create table provides (
	package	text,
	subpackage	text,
	label	text -- a capability label
);

create table requires (
	package	text,
	subpackage	text,
	label	text -- a capability label
);

create table packagegroups (
	package	text,
	"group"	text
);

commit;