X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=b1343d00a3b6a7f829452af0868f1de2c04798e1;hb=ae83ee4a50e12a6df6676a2cc2af70618ff67943;hp=eb7c461bcc426ec0bee8c4ab96db564cee1c128d;hpb=3f852ae0fbb42655a1baf66d914007f02fd4720e;p=zpackage diff --git a/db.sql b/db.sql index eb7c461..b1343d0 100644 --- a/db.sql +++ b/db.sql @@ -128,9 +128,9 @@ create table packagefiles ( 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 @@ -157,6 +157,7 @@ create table packagefiles ( check (not (filetype = 'h' and target is null)), check (target is null or length(target) between 1 and 4095), check (hash is null or length(hash) between 1 and 1024), + check (path not in ('.', '..')), check (not (filetype = 'r' and hash is null)), check (not (filetype = 'c' and device is null)), check (not (filetype = 'b' and device is null)), @@ -171,6 +172,7 @@ without rowid 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, *, @@ -278,15 +280,37 @@ create table elflibraries ( ) 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, @@ -373,6 +397,8 @@ create table repository ( 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 ( @@ -392,155 +418,207 @@ create table packagesource ( ); 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, - 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 -), +-- 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, * -,(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 +-- 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 'obsolete' as op, *, null, null, null from expired +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 'new' as op, *, null, null, null 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, *, null, null, null 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;