X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=e1565e69aee73f8b6541b84522b8b058122e31cc;hb=57218db95d0c469d3a2de65c63a784e819cbf041;hp=5583d02702afd49fd662dd5471fd062b9d9deb4f;hpb=cc00928022a5739378371d23c4b7f64e71797ade;p=zpackage

diff --git a/db.sql b/db.sql
index 5583d02..e1565e6 100644
--- a/db.sql
+++ b/db.sql
@@ -90,12 +90,13 @@ 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, -- perms, use text for octal rep?
+	username	text not null, -- name of owner
+	groupname	text not null, -- group of owner
 	uid	integer, -- numeric uid, generally ignored
 	gid	integer, -- numeric gid, generally ignored
-	filetype varchar default 'r',
+	configuration integer not null default 0, -- boolean if config file
+	filetype varchar not null default 'r',
 	-- r regular file
 	-- d directory
 	-- s symlink
@@ -110,20 +111,17 @@ create table packagefiles (
 	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
+	foreign key (package,version,release) references packages (package,version,release) on delete cascade,
+	check (not (filetype = 'l' and target is null)),
+	check (not (filetype = 'r' and hash is null)),
+	check (not (filetype = 'c' and (devmajor is null or devminor is null)))
 )
 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 packagefiles_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from packagefiles
 ;
 
 create view installed_ref_count as
@@ -132,6 +130,50 @@ from installedfiles I
 group by I.path
 ;
 
+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 view install_status as
+select 'new' as op, PN.*
+from packagefiles_status PN
+left join installed_ref_count RC on RC.path = PN.path
+where RC.refcount is null
+and PN.status = 'installing'
+
+union all
+
+select 'update' as op, PN.*
+from packagefiles_status PN
+inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
+left join installed_ref_count RC on RC.path = PN.path
+where RC.refcount = 1
+and PN.status = 'installing'
+
+union all
+
+select 'conflict' as op, PI.*
+from packagefiles_status PN
+inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
+where PN.status = 'installing'
+
+union all
+
+select 'remove' as op, PI.*
+from installedfiles PI
+left join packagefiles_status PN
+    on PI.path = PN.path and PI.package = PN.package
+where PN.path is null
+and PN.status = 'installing'
+;
+
 create table pathtags (
 	-- package id triple
 	package	text,