From: Nathan Wagner Date: Thu, 6 Dec 2018 15:06:58 +0000 (+0000) Subject: separate syncinfo from main schema X-Git-Tag: v0.3.7~1 X-Git-Url: https://pd.if.org/git/?p=zpackage;a=commitdiff_plain;h=e88d7fd4b079dc423ac628539ee891fe6504f6ba separate syncinfo from main schema --- diff --git a/Makefile b/Makefile index e57ecbc..1a4b2b6 100644 --- a/Makefile +++ b/Makefile @@ -159,7 +159,7 @@ zpm-parse: zpm-parse.o lib/parse.o zpm-quote: zpm-quote.o $(CC) $(CFLAGS) $(LDFLAGS) -o $@ $< -db.sql: schema/main.sql schema/syncconflicts.sql +db.sql: schema/main.sql schema/syncconflicts.sql schema/syncinfo.sql echo 'begin;' > $@ cat $+ >> $@ echo 'commit;' >> $@ diff --git a/schema/main.sql b/schema/main.sql index 6ea34f2..c9c79a2 100644 --- a/schema/main.sql +++ b/schema/main.sql @@ -440,150 +440,3 @@ create table repository_libsneeded ( soname text, 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 - -- TODO don't include installed or installing - -- left join packages P on P.package = PFP.package ... - -- where P.status not in ('installed','installing') -; diff --git a/schema/syncinfo.sql b/schema/syncinfo.sql new file mode 100644 index 0000000..3b38f6b --- /dev/null +++ b/schema/syncinfo.sql @@ -0,0 +1,148 @@ +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 +-- 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 + 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 + -- TODO don't include installed or installing + -- left join packages P on P.package = PFP.package ... + -- where P.status not in ('installed','installing') +;