rewrite syncinfo view

diff --git a/schema/syncinfo.sql b/schema/syncinfo.sql
index 332152c15a40d1b62a885c27b26d277c94b68724..6739cce2a731ec28c51418da9e253a5fd803334b 100644 (file)
--- a/schema/syncinfo.sql
+++ b/schema/syncinfo.sql
@@ -27,16 +27,65 @@ waspreserved as (
 )
 
 -- 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
-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'
+'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')
+)
+*/
+
+-- 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
+case
 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'
+when PC.status = 'installed' then 'md conflict'
 else 'update'
 end as op,
        printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
@@ -48,22 +97,23 @@ end as op,
        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 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')
+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
-PI.status = 'installing'
-and (
-       --P.status is null or
-       PFC.path is null
-       or
-               P.status in ('installed','removing','updating')
-)
+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
@@ -83,7 +133,8 @@ select 'remove',
        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')
+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