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 (uncompressed) content
       	size integer, -- bigint?  certainly need > 2GB
       	compression text, -- always xz?
       	content blob
)
;

create view filerefs as
select F.hash,
count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) + count(EN.file) as refcount,
count(PF.hash) as pfrefs,
count(S.hash) as scriptrefs,
count(EL.file) as librefs,
count(EN.file) as needrefs,
count(N.file) as noterefs
from files F
left join packagefiles PF on PF.hash = F.hash
left join scripts S on S.hash = F.hash
left join elflibraries EL on EL.file = F.hash
left join elfneeded EN on EN.file = F.hash
left join notes N on N.file = F.hash
group by F.hash
;

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

	-- 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,
	hash	text, -- see integ.c for package hash details
	primary key (package,version,release),
	check (typeof(package) = 'text'),
	check (typeof(version) = 'text'),
	check (typeof(release) = 'integer'),
	check (release > 0),
	-- enforce name and version conventions
	check(instr(package,' ') = 0),
	check(instr(package,'/') = 0),
	check(instr(package,':') = 0),
	check(instr(version,' ') = 0),
	check(instr(version,'-') = 0),
	check(instr(version,'/') = 0),
	check(instr(version,':') = 0),
	check(length(package) < 64),
	check(length(package) > 0),
	check(length(version) < 32),
	check(length(version) > 0)
)
without rowid
;

create index package_status_index on packages (status);
create index package_package_index on packages (package);

create view packages_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
from packages;

create trigger packages_delete_trigger instead of
delete on packages_pkgid
begin
	delete from packages where package = OLD.package
	and version = OLD.version and release = OLD.release;
end;

create trigger packages_update_trigger instead of
update on packages_pkgid
begin
	update packages
	set package = NEW.package,
	version = NEW.version,
	release = NEW.release,
	description = NEW.description,
	architecture = NEW.architecture,
	url = NEW.url,
	status = NEW.status,
	licenses = NEW.licenses,
	packager = NEW.packager,
	build_time = NEW.build_time,
	install_time = NEW.install_time,
	hash = NEW.hash
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	;
end
;

-- 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 on update cascade
);

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

	path	text, -- filesystem path
	mode	text not null default '0644', -- perms, use text for octal rep?
	username	text not null default 'root', -- name of owner
	groupname	text not null default 'root', -- group of owner
	uid	integer, -- numeric uid, generally ignored
	gid	integer, -- numeric gid, generally ignored
	configuration integer not null default 0, -- boolean if config file
	confhash text, -- last hash on disk
	filetype varchar not null default 'r',
	-- r regular file
	-- d directory
	-- l symlink
	-- h hard link -- not supported
	-- c character special -- not supported
	-- b block special -- not supported
	-- c and b device special files add dev number column
	-- p fifos (i.e. pipe) -- not supported
	-- s unix domain socket -- not supported
	target	text, -- link target for links
	device	integer, -- device file dev_t
	hash	text, -- null if not a regular file
	mtime	integer, -- seconds since epoch, finer resolution not needed
	primary key (package,version,release,path),
	foreign key (package,version,release)
		references packages (package,version,release)
		on delete cascade on update cascade,
	check (not (filetype = 'l' and target is null)),
	check (not (filetype = 'h' and target is null)),
	check (target is null or length(target) between 1 and 4095),
	check (hash is null or length(hash) between 1 and 1024),
	check (path not in ('.', '..')),
	check (not (filetype = 'r' and hash is null)),
	check (not (filetype = 'c' and device is null)),
	check (not (filetype = 'b' and device is null)),
	check (filetype in ('r','d','l','h','c','b','p')),
	check(length(username) between 1 and 256),
	check(length(groupname) between 1 and 256),
	check (configuration = 0 or configuration = 1)
)
without rowid
;

create index packagefile_package_index on packagefiles (package);
create index packagefile_path_index on packagefiles (path);
create index packagefile_hash_index on packagefiles (hash);
create index pkgfile_configuration on packagefiles (configuration) where configuration = 1;

create view packagefiles_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *,
printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
from packagefiles
;

create trigger packagefiles_update_trigger instead of
update on packagefiles_pkgid
begin
	update packagefiles
	set package = NEW.package,
	version = NEW.version,
	release = NEW.release,
	path = NEW.path,
	mode = NEW.mode,
	username = NEW.username,
	groupname = NEW.groupname,
	uid = NEW.uid,
	gid = NEW.gid,
	configuration = NEW.configuration,
	filetype = NEW.filetype,
	target = NEW.target,
	device = NEW.device,
	hash = NEW.hash,
	mtime = NEW.mtime
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	and path = OLD.path
	;
end
;

create trigger packagefiles_delete_trigger instead of
delete on packagefiles_pkgid
begin
	delete from packagefiles
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	and path = OLD.path
	;
	update packages set hash = null
	where package = OLD.package
	and version = OLD.version
	and release = OLD.release
	;
end
;

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

create view sync_status_ref_count as
select path, status, count(*) as refcount
from packagefiles_status
where status in ('installed', 'installing', 'removing')
group by path, status
;

create view packagefiles_status as
select P.status, PF.*
from packagefiles_pkgid PF
left join packages_pkgid P on P.pkgid = PF.pkgid
;

create view installedfiles as
select * from packagefiles_status
where status = 'installed'
;

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),
	foreign key (package,version,release,path)
	references packagefiles on delete cascade on update cascade
)
without rowid
;

create view elfdeps as
select PF.pkgid, PF.status, PF.path, N.needed as needs,
PL.path as library, PL.pkgid provider, PL.status as library_status
from packagefiles_status PF
join elfneeded N on N.file = PF.hash
left join elflibraries L on N.needed = L.soname
left join packagefiles_status PL on PL.hash = L.file
;

-- TODO just elf information?
-- and just hash, not package?
create table elflibraries (
	file	text primary key,
	soname	text
)
without rowid
;
create index elf_library_name_index on elflibraries(soname);

create table elfneeded (
	file	text, -- hash of file
	needed	text, -- soname of dependency
	primary key (file, needed)
)
without rowid
;

create view package_libraries as
select distinct PF.pkgid, EL.soname
from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
;

create view package_libraries_needed as
with pkglibs as (
		select distinct EN.needed as soname, PF.pkgid
		from elfneeded EN
		join packagefiles_pkgid PF on PF.hash = EN.file
		),
     pkgprovides as (
		     select distinct EL.soname, PF.pkgid
		     from elflibraries EL
		     join packagefiles_pkgid PF on PF.hash = EL.file
		    )
     select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
     from pkglibs PL
     left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
;

-- package scripts: table of package, stage, file
create table scripts (
	package	text,
	version	text,
	release	integer,
	stage	text,
	hash	text,
	primary key (package,version,release,stage),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

create view scripts_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
from scripts
;

-- package dependencies: table of package, dependency, dep type (package,
	-- soname)
-- how to specify min/max/exact
create table packagedeps (
	package	text,
	version	text,
	release	integer,
	requires text, -- package, can be partial, minimum
	primary key (package,version,release,requires),
	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

-- 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 notes (
	id	integer primary key, -- rowid alias
	ts	text default (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
	note	text not null,
	pkgid	text, -- package
	path	text, -- file path involved
	file	text, -- hash of file
	ack	integer default 0
);

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
);
-- force the url to be repourl/info.repo
-- package urls repourl/pkgid.zpm

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


create view needed_libraries as
with recursive
libs(file,needs,provider) as (
	select N.file, N.needed as needs, L.file as provider
	from elfneeded N left join elflibraries L on N.needed = L.soname
	union
	select L.file, N.needed as needs, EL.file as provider
	from libs L
	join elfneeded N on N.file = L.provider
	left join elflibraries EL on N.needed = EL.soname
)
select * from libs;

/*
 * tables for repository info, essentially materalized views
 */
create table repository_libs (
	pkgid text,
	soname text
);

create table repository_libsneeded (
	pkgid text,
	soname text,
	selfsat integer
);