)
;
+create view filerefs as
+select F.hash,
+count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
+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 notes N on N.file = F.hash
+group by F.hash
+;
+
-- information about packages
-- a package is identified by a package,version,release triple
create table packages (
release integer,
path text, -- filesystem path
- mode text not null, -- perms, use text for octal rep?
- username text not null, -- name of owner
- groupname text not null, -- group of owner
+ mode text not null default '0644', -- perms, use text for octal rep?
+ username text not null default 'root', -- name of owner
+ groupname text not null default 'root', -- group of owner
uid integer, -- numeric uid, generally ignored
gid integer, -- numeric gid, generally ignored
configuration integer not null default 0, -- boolean if config file
create index packagefile_package_index on packagefiles (package);
create index packagefile_path_index on packagefiles (path);
create index packagefile_hash_index on packagefiles (hash);
+create index pkgfile_configuration on packagefiles (configuration) where configuration = 1;
create view packagefiles_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *,
-printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
+printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
from packagefiles
;
end
;
+create trigger packagefiles_delete_trigger instead of
+delete on packagefiles_pkgid
+begin
+ delete from packagefiles
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ and path = OLD.path
+ ;
+ update packages set hash = null
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ ;
+end
+;
+
create view installed_ref_count as
select I.path, count(*) as refcount
from installedfiles I
where status = 'installed'
;
-create view install_status as
-
-select 'new' as op, PN.*
-from packagefiles_status PN
-left join installed_ref_count RC on RC.path = PN.path
-where RC.refcount is null
-and PN.status = 'installing'
-
-union all
-
-select 'update' as op, PN.*
-from packagefiles_status PN
-inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
-left join installed_ref_count RC on RC.path = PN.path
-where RC.refcount = 1
-and PN.status = 'installing'
-and PI.hash is not PN.hash
-
-union all
-
-select 'conflict' as op, PI.*
-from packagefiles_status PN
-inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
-where PN.status = 'installing'
-
-union all
-select 'remove' as op, PI.*
-from installedfiles PI
-left join packagefiles_status PN
- on PI.path = PN.path and PI.package = PN.package
- and PI.pkgid != PN.pkgid
-where PN.path is null
-and PI.package in (select package from packages where status = 'installing')
-
-union all
--- remove files in removing, but not installing
-select distinct 'remove' as op, PR.*
-from packagefiles_status PR
-left join packagefiles_status PN
-on PR.path = PN.path
-and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
-where PN.path is null
-and PR.status = 'removing'
-;
-
create table pathtags (
-- package id triple
package text,
)
without rowid
;
+create index elf_library_name_index on elflibraries(soname);
create table elfneeded (
- file text,
+ file text, -- hash of file
needed text, -- soname of dependency
primary key (file, needed)
)
without rowid
;
+create view package_libraries as
+select distinct PF.pkgid, EL.soname
+from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
+;
+
+create view package_libraries_needed as
+with pkglibs as (
+ select distinct EN.needed as soname, PF.pkgid
+ from elfneeded EN
+ join packagefiles_pkgid PF on PF.hash = EN.file
+ ),
+ pkgprovides as (
+ select distinct EL.soname, PF.pkgid
+ from elflibraries EL
+ join packagefiles_pkgid PF on PF.hash = EL.file
+ )
+ select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
+ from pkglibs PL
+ left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
+;
+
-- package scripts: table of package, stage, file
create table scripts (
package text,
priority integer not null default 1,
refreshed integer -- last refresh time
);
+-- force the url to be repourl/info.repo
+-- package urls repourl/pkgid.zpm
-- urls for downloading packages. possibly unneeded
create table repository_packages (
);
create view syncconflicts 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'
-),
+with
-- metadata different
md_conflict as (
- select path, count(distinct mds) as mdcount,
- count(distinct hash) as hashcount
- from syncstatus SS
- where SS.rstatus in ('installing', 'installed')
+ select path, count(distinct
+ printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
+ count(distinct PFA.hash) as hashcount
+ from packagefiles PFA
+ join packages PA
+ on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
+ where PA.status in ('installing', 'installed')
group by path
- having (count(distinct mds) > 1 or count(distinct hash) > 1)
+ having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
+ ) > 1 or count(distinct PFA.hash) > 1)
)
-select BASE.*, 'hash' as conflict
-from syncstatus BASE
+select PFH.*,
+ printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
+ printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
+'hash' as conflict
+from packagefiles PFH
where path in (select path from md_conflict where hashcount > 1)
union
-select BASE.*, 'md' as conflict
-from syncstatus BASE
+select PFM.*,
+ printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
+ printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
+'md' as conflict
+from packagefiles PFM
where path in (select path from md_conflict where mdcount > 1)
;
+create view needed_libraries as
+with recursive
+libs(file,needs,provider) as (
+ select N.file, N.needed as needs, L.file as provider
+ from elfneeded N left join elflibraries L on N.needed = L.soname
+ union
+ select L.file, N.needed as needs, EL.file as provider
+ from libs L
+ join elfneeded N on N.file = L.provider
+ left join elflibraries EL on N.needed = EL.soname
+)
+select * from libs;
+
+/*
+ * tables for repository info, essentially materalized views
+ */
+create table repository_libs (
+ pkgid text,
+ soname text
+);
+
+create table repository_libsneeded (
+ pkgid text,
+ soname text,
+ selfsat integer
+);
+
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, SS.target, SS.device,
- null as ohash
- 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
-),
+-- paths to libraries we need to keep around
preserve as (
- select distinct
- path,username,uid,groupname,gid,mode,filetype,mtime,hash,
- configuration,target,device, null as ohash
- from syncstatus SS
- 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')
+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)
),
--- 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')
+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'
)
-select 'update' as op, * from modified
+-- 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' as op, * from remove
+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 'obsolete' as op, * from expired
-union
-select 'new' as op, * from newfiles
+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' as op, * from preserve
+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')
;
commit;
projects / zpackage / blobdiff