add view to count file refs

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index c7040349b38dc2004ed9970eb3e9ca9a414da33a..427c1ef4d586a3c6e26ca8871749e573a7732684 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -15,6 +15,15 @@ CREATE TABLE files (
 )
 ;
 
+create view filerefs as
+select F.hash, count(PF.hash) + count(S.hash) + count(EL.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
+group by F.hash
+;
+
 -- information about packages
 -- a package is identified by a package,version,release triple
 create table packages (
@@ -194,6 +203,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