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, -- sha256 of content
       	size integer, -- bigint?  certainly need > 2GB
       	compression text, -- always xz?
       	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
	pkgid	text, -- the three above joined with '-'

	-- metadata columns
	description	text,
	architecture	text,
	url	text,
	status	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),
	check (typeof(release) = 'integer'),
	check (release > 0)
	-- TODO enforce name and release conventions
)
without rowid
;

-- handle package status history with a logging trigger.
create trigger logpkgstatus after update of status on packages
begin insert into zpmlog (action,target,info)
       	values (printf('status change %s %s', OLD.status, NEW.status),
		printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
		NULL); END;

create table packagetags (
	-- package id triple
	package	text,
	version	text,
	release	integer,
	tag	text,
	set_time integer default (strftime('%s', 'now')),
	primary key (package,version,release,tag),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade
);

-- packagefile hash is columns as text, joined with null bytes, then
-- sha256 sum of that
-- package checksum is package columns as text, joined with null bytes,
-- other than the checksum and install_time column
-- then that hashed.  finally, that hash, plus the ascii sorted
-- hashes of the package files all joined with newlines, hashed.
-- really don't like this.

-- 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 view installedfiles as
select PF.package, PF.version, PF.release,
printf('%s-%s-%s', PF.package, PF.version, PF.release) as pkgid,
PF.path, PF.hash, PF.filetype
from packagefiles PF
left join packages P
on P.package = PF.package and P.version = PF.version and P.release = PF.release
where
P.status = 'installed'
;

create view installed_ref_count as
select I.path, count(*) as refcount
from installedfiles I
group by I.path
;

create table pathtags (
	-- 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 primary key, -- hash of blob
	elftype	text,
	foreign key (file) references files on delete cascade
)
without rowid
;

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

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

-- zpm actions
-- not sure how machine readable this needs to be,
-- do not at all for now, figure it out later
-- could be worth logging all commands in a history table,
-- the zpm driver could do that and capture the exit status
-- as well
-- might want the history table to note a "group" to tie together
-- sub-invocations, probably an environment variable set if not
-- already set by zpm, probably a uuid or a timestamp
create table zpmlog (
	ts	text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
       	-- timestamp of action
	action	text,
	target	text, -- packagename, repo name, etc
	info	text -- human readable
);

create table history (
	ts	integer, -- again, probably needs timestamp sub second
	cmd	text,
	args	text,
	status	integer
);

create table repository (
	name	text primary key, -- our name for a repo
	url	text not null,
	priority	integer not null default 1,
	refreshed	integer -- last refresh time
);

-- urls for downloading packages.  possibly unneeded
create table repository_packages (
	repo	text,
	pkg	text, -- glob pattern?  in which case others not needed
	version	text,
	release	text,
	url	text
);

-- track which repository a package was cloned from, i.e. where we got it
create table packagesource (
	name	text,
	version	text,
	release	integer,
	repository	text references repository
);

commit;