create view syncinfo as with -- paths to libraries we need to keep around preserve as ( select PFL.* from packagefiles PFL join elflibraries EL on EL.file = PFL.hash join elfneeded EN on EN.needed = EL.soname join packagefiles PFN on EN.file = PFN.hash join packages PN on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release where PN.status = 'installing' or PN.status = 'installed' and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release) ), waspreserved as ( select PF.path from packagefiles PF join elflibraries EL on EL.file = PF.hash join elfneeded EN on EN.needed = EL.soname join packagefiles PL on EN.file = PL.hash join packages P on PL.path = P.package and PL.version = P.version and PL.release = P.release where P.status = 'removing' or P.status = 'installed' or P.status = 'updating' ) -- every path in 'installing' is either new or update, or no-op -- but we have to calculate them separately I think. -- once for new, once for update, and once for no-op, might be able -- to combine update and no-op select 'new' as op, printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid, PFI.path, PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype, PFI.mtime, PFI.hash, PFI.configuration -- + case when PFC.configuration = 1 then 2 else 0 end as configuration, PFI.target, PFI.device, null as ohash, printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds, --printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds null as omds from packagefiles PFI join packages PI on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release /* left join packagefiles PFC on PFI.path = PFC.path left join packages PC on (PFI.package is not PC.package or PFI.release is not PC.release or PC.version is not PFI.version) and PC.status in ('installed','removing','updating') and PFC.package = PC.package and PFC.version = PC.version and PFC.release = PC.release */ where PI.status = 'installing' and not exists (select PFC.path from packagefiles PFC join packages PC on PFC.package = PC.package and PFC.version = PC.version and PFC.release = PC.release where PFC.path = PFI.path and PC.status in ('installed','removing','updating') ) /* (PFC.path is null or PC.status not in ('installed','removing','updating') ) */ union select case when PFI.filetype is not 'l' and PFI.filetype is PFC.filetype and PFI.mode is PFC.mode and PFI.username is PFC.username and PFI.groupname is PFC.groupname and PFI.hash is PFC.hash then 'noop' when PFI.filetype is 'l' and PFI.target is PFC.target and PFI.filetype is PFC.filetype and PFI.mode is PFC.mode and PFI.username is PFC.username and PFI.groupname is PFC.groupname and PFI.hash is PFC.hash then 'noop' when PC.status = 'installed' then 'md conflict' else 'update' end as op, printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid, PFI.path, PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype, PFI.mtime, PFI.hash, PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end as configuration, PFI.target, PFI.device, PFC.hash as ohash, printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds, printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds from packagefiles PFI join packages P on PFI.package = P.package and PFI.version = P.version and PFI.release = P.release join packagefiles PFC on PFI.path = PFC.path join packages PC on (PFI.package is not PC.package or PFI.release is not PC.release or PC.version is not PFI.version) -- er, no, it's a conflict if it's in installed and it doesn't match and PC.status in ('installed','updating','removing') and PFC.package = PC.package and PFC.version = PC.version and PFC.release = PC.release where P.status = 'installing' --and PFC.path is not null -- every path in updating and removing is either remove or no-op -- not true, could be an update, but should be handled above union select 'remove', printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid, PFR.path, PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype, PFR.mtime, PFR.hash, PFR.configuration, --PFR.target, coalesce(P.status, ' null status '), --PFR.device, -- PFI.path, coalesce(PFI.status, ' null status '), --PFR.device, PFR.target, PFR.device, null as ohash, --PFI.package, printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds, null as omds from packagefiles PFR join packages PU on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release and PU.status in ('removing','updating') where not exists (select path from packagefiles PFI join packages P on PFI.package = P.package and PFI.version = P.version and PFI.release = P.release and P.status in ('installing','installed') where PFI.path = PFR.path ) and path not in (select path from preserve) -- paths in 'installed' or 'updated' are no-ops union select 'preserve', printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid, PFP.path, PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype, PFP.mtime, PFP.hash, PFP.configuration as configuration, PFP.target, PFP.device, null as ohash, printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds, null as omds from preserve PFP -- TODO don't include installed or installing -- left join packages P on P.package = PFP.package ... -- where P.status not in ('installed','installing') ;