X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=316f3e47ba42354640d334341d0771bafd12f0aa;hb=55218d5fc4522f66ffd3602fbc4410ebe5f0e670;hp=c7040349b38dc2004ed9970eb3e9ca9a414da33a;hpb=b6e8d469897f58ed6ddd5e428acdac0f4a597e4e;p=zpackage

diff --git a/db.sql b/db.sql
index c704034..316f3e4 100644
--- a/db.sql
+++ b/db.sql
@@ -15,6 +15,17 @@ CREATE TABLE files (
 )
 ;
 
+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 (
@@ -194,6 +205,23 @@ begin
 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
@@ -449,7 +477,10 @@ modified as (
 	select distinct
 	SS.path, 
 	SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
-	SS.mtime, SS.hash, SS.configuration, SS.target, SS.device,
+	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