X-Git-Url: https://pd.if.org/git/?p=zpackage;a=blobdiff_plain;f=db.sql;h=658865031684e5ad4c943ac3177c446b9e4e8a4e;hp=967cdb395390a8474aa37babf0295db76ddd630f;hb=1323381f0eeb38466d8afc185a5295e4b5024bd5;hpb=293e281d6bd4b9c2c1de146184835ed34dc87b05 diff --git a/db.sql b/db.sql index 967cdb3..6588650 100644 --- a/db.sql +++ b/db.sql @@ -472,128 +472,6 @@ libs(file,needs,provider) as ( ) select * from libs; -create view syncinfo as -with -preserved as ( - select BASE.*, 'preserved' as rstatus - from packagefiles_status BASE - join elflibraries EL on EL.file = BASE.hash - where - BASE.status in ('removed', 'updated') - and BASE.hash in (select hash from packagefiles_status where - status in ('installed')) -), -syncstatus as ( - select distinct BASE.*, - case when P.status = 'installing' and BASE.status = 'installed' - then 'updating' - when BASE.status in ('removed','updated') - and BASE.path in (select path from preserved) then - 'preserved' - else - BASE.status - end as rstatus - from packagefiles_status BASE - left join packages P on P.package = BASE.package - and BASE.status in ('installed', 'removing') - and P.status = 'installing' -), --- new file: in installing, not in installed or updating or removing -newfiles as ( - select distinct - path,username,uid,groupname,gid,mode,filetype,mtime,hash, - configuration,target,device, null as ohash - from syncstatus SS - where path not in (select path from syncstatus where - rstatus in ('installed', 'updating', 'removing') - ) - and rstatus in ('installing') -), --- modified: retained, but with different metadata -modified as ( - select distinct - SS.path, - SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype, - 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 - on SS.path = OS.path and SS.pkgid is not OS.pkgid - -- preserved? - and OS.rstatus in ('installed','updating','removing') - and (SS.mds is not OS.mds or SS.hash is not OS.hash) - where - SS.rstatus in ('installing') -), --- preserve: libraries needed by something in installed or installing -/* -needed as ( - select distinct - ED.library - from elfdeps ED - where status in ('installed', 'installing') - and library is not null -), -*/ -needed as ( - select NL.file as needslib, NL.provider as libraryhash - from needed_libraries NL - inner join syncstatus SS on SS.hash = NL.file - and SS.status in ('installed', 'installing') -), -preserve as ( - select distinct - path,username,uid,groupname,gid,mode,filetype,mtime,hash, - configuration,target,device, null as ohash - from syncstatus SS - join needed N on SS.hash = N.libraryhash - where SS.rstatus in ('removing', 'removed') --- where SS.hash in (select libraryhash from needed) --- where path in (select library from needed) --- and SS.rstatus in ('removing', 'removed') -), --- remove: cur, not preserved, not in final set -remove as ( - select distinct - path,username,uid,groupname,gid,mode,filetype,mtime,hash, - configuration,target,device, null as ohash - from syncstatus SS - where path not in ( - select path from syncstatus where - rstatus in ('installed', 'installing') - ) - and path not in (select path from preserve) - and rstatus in ('removing', 'updating') -), --- expired: libraries that had been preserved, but aren't needed now -expired as ( - select distinct - path,username,uid,groupname,gid,mode,filetype,mtime,hash, - configuration,target,device, null as ohash - from syncstatus BASE - where hash in (select file from elflibraries where file is not null) - and path not in (select path from preserve) - and rstatus in ('removed','updated') -) -select 'update' as op, * -,(select group_concat(pkgid, ' ') - from syncstatus SS - where SS.rstatus = 'installing' and SS.path = M.path -) as pkglist -from modified M -union -select 'remove' as op, *, null, null, null from remove -union -select 'obsolete' as op, *, null, null, null from expired -union -select 'new' as op, *, null, null, null from newfiles -union -select 'preserve' as op, *, null, null, null from preserve -; - /* * tables for repository info, essentially materalized views */ @@ -608,4 +486,148 @@ create table repository_libsneeded ( selfsat integer ); +create view syncinfo as +with +-- paths to libraries we need to keep around +preserve as ( +select distinct 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 +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 + 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.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 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 +where +PI.status = 'installing' + +-- 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 +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 + case when PFC.configuration = 1 then 2 else 0 end + as configuration, + PFR.target, PFR.device, + null as ohash, + 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 + +where +PU.status in ('removing') +and PFI.path is null +and PFC.path is null +and PFR.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 +; + commit;