From c768a9948f77da5eb2359ea95815436acb0dfe16 Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Tue, 21 Aug 2018 17:33:16 +0000 Subject: [PATCH] add views useful for installs and uninstalls --- db.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) diff --git a/db.sql b/db.sql index d134208..5f227c8 100644 --- a/db.sql +++ b/db.sql @@ -101,6 +101,23 @@ create table packagefiles ( 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 installed_ref_count as +select I.path, count(*) as refcount +from installedfiles I +group by I.path +; + create table pathtags ( -- package id triple package text, -- 2.40.0