From d60a7ec96f26d9c1d7a60be0febafda0bbb731fd Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Fri, 7 Dec 2018 07:46:52 +0000 Subject: [PATCH] fix syncinfo --- schema/syncinfo.sql | 110 +++++++++++++++----------------------------- 1 file changed, 38 insertions(+), 72 deletions(-) diff --git a/schema/syncinfo.sql b/schema/syncinfo.sql index 3b38f6b..332152c 100644 --- a/schema/syncinfo.sql +++ b/schema/syncinfo.sql @@ -2,7 +2,8 @@ create view syncinfo as with -- paths to libraries we need to keep around preserve as ( -select distinct PFL.* +select +PFL.* from packagefiles PFL join elflibraries EL on EL.file = PFL.hash join elfneeded EN on EN.needed = EL.soname @@ -27,76 +28,44 @@ waspreserved as ( -- every path in 'installing' is either new or update, or no-op select -case -when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new' -when - printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is - printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) - and PFI.hash is PFC.hash +case when --PFC.path is null + P.status is null + and PFC.path not in (select path from waspreserved) + -- if the path is in preserved, this will an update with no old md. + then 'new' +when 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' 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.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, + 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 PI on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release -left join packages PC -on PC.package = PI.package and PC.status in ('installed','removing','updating') -left join packagefiles PFC - on PFC.package = PC.package - and PFC.version = PC.version - and PFC.release = PC.release - and PFC.path = PFI.path +left join packagefiles PFC on PFI.path = PFC.path +and (PFI.package is not PFC.package or PFI.release is not PFC.release or PFC.version is not PFI.version) +left join packages P + on PFC.package = P.package and PFC.version = P.version and PFC.release = P.release +and P.status in ('installed','removing','updating') where PI.status = 'installing' +and ( + --P.status is null or + PFC.path is null + or + P.status in ('installed','removing','updating') +) --- every path in updating is either remove or no-op -union -select 'remove', - printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid, - PFU.path, - PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype, - PFU.mtime, PFU.hash, - PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end - as configuration, - PFU.target, PFU.device, - null as ohash, - printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds, - null as omds -from packagefiles PFU -join packages PU -on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release --- inner join because the installing package must exist or this shouldn't --- be an 'updating' package -join packages P on PU.package = P.package and P.status = 'installing' -left join packagefiles PFI on - PFI.package = P.package - and PFI.version = P.version - and PFI.release = P.release - and PFI.path = PFU.path - --- handle paths owned by other installed packages -left join packages PI on PI.status = 'installed' -left join packagefiles PFC -on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path - -where -PU.status in ('updating') -and PFI.path is null -and PFC.path is null -and PFU.path not in (select path from preserve) - --- every path in removing is either remove or no-op +-- 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', @@ -104,29 +73,26 @@ select 'remove', PFR.path, PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype, PFR.mtime, PFR.hash, - PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end - as configuration, - PFR.target, PFR.device, + 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 -left join packages P on PU.package = P.package and P.status = 'installing' -left join packagefiles PFI on PFI.path = PFR.path -and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release - --- handle paths owned by other installed packages -left join packages PI on PI.status = 'installed' -left join packagefiles PFC -on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path +on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release and PU.status in ('removing','updating') where -PU.status in ('removing') -and PFI.path is null -and PFC.path is null -and PFR.path not in (select path from preserve) +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 + +) -- paths in 'installed' or 'updated' are no-ops -- 2.40.0