From: Nathan Wagner <nw@hydaspes.if.org>
Date: Sat, 3 Nov 2018 14:28:54 +0000 (+0000)
Subject: add view to count file refs
X-Git-Tag: v0.2.16~41
X-Git-Url: https://pd.if.org/git/?p=zpackage;a=commitdiff_plain;h=a822809a00abd6388959e601b9b6b1572481e4bc

add view to count file refs
---

diff --git a/db.sql b/db.sql
index 00c5df0..427c1ef 100644
--- 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 (