From a822809a00abd6388959e601b9b6b1572481e4bc Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Sat, 3 Nov 2018 14:28:54 +0000 Subject: [PATCH] add view to count file refs --- db.sql | 9 +++++++++ 1 file changed, 9 insertions(+) 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 ( -- 2.40.0