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
 )
 
 -- 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
 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 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,
 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
        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
 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
 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
 
 -- 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
        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
 
 where
 not exists (select path from