-- TODO copyright and license information should probably
-- go here
CREATE TABLE files (
- hash text primary key, -- sha256 of content
+ hash text primary key, -- sha256 of (uncompressed) content
size integer, -- bigint? certainly need > 2GB
compression text, -- always xz?
content blob
create view filerefs as
select F.hash,
-count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
+count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) + count(EN.file) as refcount,
+count(PF.hash) as pfrefs,
+count(S.hash) as scriptrefs,
+count(EL.file) as librefs,
+count(EN.file) as needrefs,
+count(N.file) as noterefs
from files F
left join packagefiles PF on PF.hash = F.hash
left join scripts S on S.hash = F.hash
left join elflibraries EL on EL.file = F.hash
+left join elfneeded EN on EN.file = F.hash
left join notes N on N.file = F.hash
group by F.hash
;
from scripts
;
--- package dependencies: table of package, dependency, dep type (package, soname)
+-- package dependencies: table of package, dependency, dep type (package,
+ -- soname)
+-- how to specify min/max/exact
create table packagedeps (
package text,
version text,
release integer,
- requires text, -- package, can be partial
- primary key (package,version,release,package),
+ requires text, -- package, can be partial, minimum
+ primary key (package,version,release,requires),
foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);
create table notes (
id integer primary key, -- rowid alias
- ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
+ ts text default (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
note text not null,
pkgid text, -- package
path text, -- file path involved
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')
-;
projects / zpackage / blobdiff