)
;
+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 (
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