begin;

PRAGMA application_id = 0x5a504442;
PRAGMA user_version = 1;

-- should be faster with rowid due to the blob content
CREATE TABLE files (
	hash text primary key,
       	size integer,
       	compression text,
       	content blob
)
;

create table packages (
	package	text,
	version	text, -- the upstream version string
	release	integer, -- the local release number
	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
;

create table packagefiles (
	package	text,
	version	text,
	release	integer,
	path	text,
	mode	text, -- perms, use text for octal rep?
	username	text, -- name of owner
	groupname	text, -- group of owner
	--filetype	integer default 0, -- 0 regular file, 1 directory, 2 symlink
	-- regular file if null target and not null hash
	-- except that we could not know the hash, or care
	-- directory if null hash and null target
	-- symlink if null hash and not null target
	-- hard link if not null hash and not null target
	-- device special files add dev number column
	-- fifos add mode?  Can encode filetype in mode.
	target	text, -- link target
	hash	text, -- what should go here, null for dir?
	mtime	integer, -- seconds since epoch, but allow finer?
	primary key (package,version,release,path),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade
)
without rowid
;

-- TODO just elf information?
-- and just hash, not package?
create table libraries (
	package	text,
	subpackage	text,
	path	text,
	soname	text
)
;

create table librarydeps (
	package	text,
	subpackage	text,
	path	text,
	soname	text -- soname of dependency
);

-- package scripts: table of package, stage, file
create table scripts (
	package	text,
	subpackage	text,
	stage	text,
	hash	text
);

-- package dependencies: table of package, dependency, dep type (package, soname)
create table packagedeps (
	package	text,
	subpackage	text,
	requires	text, -- package name
	subreq	text, -- if requires only a sub package, probably most common for libs
	minversion	text,
	maxversion	text
);

-- 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;