X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=548dba8bcca125c6d5533814a75d8d21f22298e3;hb=5405c7c75dc410d93818fabdfa290dbaa4505aac;hp=fab5b24752c99aae59417bfe07c379c7a3fa857f;hpb=0643628a3b9c359a78862d09828e83b562feea08;p=zpackage

diff --git a/db.sql b/db.sql
index fab5b24..548dba8 100644
--- a/db.sql
+++ b/db.sql
@@ -5,33 +5,121 @@ 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,
+	hash text primary key, -- sha256 of 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) as refcount
+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 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 (
-	package	text,
-	version	text, -- the upstream version string
-	release	integer, -- the local release number
+	-- 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,
-	checksum	text, -- checksum of package contents.  null for incompleted packages
-	primary key (package,version,release)
+	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
@@ -40,73 +128,213 @@ create table packagefiles (
 	release	integer,
 
 	path	text, -- filesystem path
-	mode	text, -- perms, use text for octal rep?
-	username	text, -- name of owner
-	groupname	text, -- group of owner
+	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
-	--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 for symlinks
-	hash	text, -- null if no actual content, i.e. anything but a regular file
-	mtime	integer, -- seconds since epoch, finer resolution probably not needed
+	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
+	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 (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 table elfinfo (
-	file	text, -- hash of blob
-	elftype	text,
-	foreign key (file) references files on delete cascade
-);
+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 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,
-	foreign key (file) references files on delete cascade
+	soname	text
 )
 without rowid
 ;
+create index elf_library_name_index on elflibraries(soname);
 
 create table elfneeded (
-	file	text,
+	file	text, -- hash of file
 	needed	text, -- soname of dependency
-	primary key (file, needed),
-	foreign key (file) references files on delete cascade
+	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
+	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)
 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
+	requires text, -- package name (only)
+	minimum text,
+	maximum text,
+	primary key (package,version,release,package),
+	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 );
 
 -- capability labels
@@ -127,4 +355,243 @@ create table packagegroups (
 	"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-%d %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 syncconflicts as
+with
+preserved as (
+	select BASE.*, 'preserved' as rstatus
+	from packagefiles_status BASE
+	join elflibraries EL on EL.file = BASE.hash
+	where
+	BASE.status in ('removed', 'updated')
+	and BASE.hash in (select hash from packagefiles_status where
+		status in ('installed'))
+),
+syncstatus as (
+	select distinct BASE.*,
+       	case when P.status = 'installing' and BASE.status = 'installed'
+		then 'updating'
+	when BASE.status in ('removed','updated')
+		and BASE.path in (select path from preserved) then
+		'preserved'
+	else
+		BASE.status
+	end as rstatus
+	from packagefiles_status BASE
+	left join packages P on P.package = BASE.package
+		and BASE.status in ('installed', 'removing')
+		and P.status = 'installing'
+),
+-- metadata different
+md_conflict as (
+	select path, count(distinct mds) as mdcount,
+       	count(distinct hash) as hashcount
+	from syncstatus SS
+	where SS.rstatus in ('installing', 'installed')
+	group by path
+	having (count(distinct mds) > 1 or count(distinct hash) > 1)
+)
+select BASE.*, 'hash' as conflict
+from syncstatus BASE
+where path in (select path from md_conflict where hashcount > 1)
+union
+select BASE.*, 'md' as conflict
+from syncstatus BASE
+where path in (select path from md_conflict where mdcount > 1)
+;
+
+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;
+
+create view syncinfo as
+with
+preserved as (
+	select BASE.*, 'preserved' as rstatus
+	from packagefiles_status BASE
+	join elflibraries EL on EL.file = BASE.hash
+	where
+	BASE.status in ('removed', 'updated')
+	and BASE.hash in (select hash from packagefiles_status where
+		status in ('installed'))
+),
+syncstatus as (
+	select distinct BASE.*,
+       	case when P.status = 'installing' and BASE.status = 'installed'
+		then 'updating'
+	when BASE.status in ('removed','updated')
+		and BASE.path in (select path from preserved) then
+		'preserved'
+	else
+		BASE.status
+	end as rstatus
+	from packagefiles_status BASE
+	left join packages P on P.package = BASE.package
+		and BASE.status in ('installed', 'removing')
+		and P.status = 'installing'
+),
+-- new file: in installing, not in installed or updating or removing
+newfiles as (
+	select distinct
+       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+	configuration,target,device, null as ohash
+	from syncstatus SS
+	where path not in (select path from syncstatus where
+		rstatus in ('installed', 'updating', 'removing')
+	)
+	and rstatus in ('installing')
+),
+-- modified: retained, but with different metadata
+modified as (
+	select distinct
+	SS.path, 
+	SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
+	SS.mtime, SS.hash,
+	SS.configuration + case when OS.configuration = 1 then 2 else 0 end
+	as configuration,
+       	SS.target, SS.device,
+	OS.hash as ohash, SS.mds, OS.mds as omds
+	from syncstatus SS
+	join syncstatus OS
+	on SS.path = OS.path and SS.pkgid is not OS.pkgid
+	-- preserved?
+	and OS.rstatus in ('installed','updating','removing')
+	and (SS.mds is not OS.mds or SS.hash is not OS.hash)
+	where
+	SS.rstatus in ('installing')
+),
+-- preserve: libraries needed by something in installed or installing
+/*
+needed as (
+	select distinct
+       	ED.library
+	from elfdeps ED
+	where status in ('installed', 'installing')
+	and library is not null
+),
+*/
+needed as (
+	select NL.file as needslib, NL.provider as libraryhash
+	from needed_libraries NL
+	inner join syncstatus SS on SS.hash = NL.file
+	and SS.status in ('installed', 'installing')
+),
+preserve as (
+	select distinct
+       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+	configuration,target,device, null as ohash
+	from syncstatus SS
+	join needed N on SS.hash = N.libraryhash
+	where SS.rstatus in ('removing', 'removed')
+--	where SS.hash in (select libraryhash from needed)
+--	where path in (select library from needed)
+--	and SS.rstatus in ('removing', 'removed')
+),
+-- remove: cur, not preserved, not in final set
+remove as (
+	select distinct
+       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+	configuration,target,device, null as ohash
+	from syncstatus SS
+	where path not in (
+		select path from syncstatus where
+		rstatus in ('installed', 'installing')
+	)
+	and path not in (select path from preserve)
+	and rstatus in ('removing', 'updating')
+),
+-- expired: libraries that had been preserved, but aren't needed now
+expired as (
+	select distinct
+       	path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+	configuration,target,device, null as ohash
+	from syncstatus BASE
+	where hash in (select file from elflibraries where file is not null)
+	and path not in (select path from preserve)
+	and rstatus in ('removed','updated')
+)
+select 'update' as op, *
+,(select group_concat(pkgid, ' ')
+       	from syncstatus SS
+       	where SS.rstatus = 'installing' and SS.path = M.path
+) as pkglist
+from modified M
+union
+select 'remove' as op, *, null, null, null from remove
+union
+select 'obsolete' as op, *, null, null, null from expired
+union
+select 'new' as op, *, null, null, null from newfiles
+union
+select 'preserve' as op, *, null, null, null from preserve
+;
+
 commit;