X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=548dba8bcca125c6d5533814a75d8d21f22298e3;hb=aeefcd018ce388d9a4f496994fd9961d13ba62df;hp=020f228d658dcb0f464e92afef8e2de0ab04e910;hpb=383712eefa950c5dc619f8cd5fb60be8e8041502;p=zpackage diff --git a/db.sql b/db.sql index 020f228..548dba8 100644 --- a/db.sql +++ b/db.sql @@ -15,6 +15,17 @@ CREATE TABLE files ( ) ; +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 ( @@ -117,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 @@ -163,7 +174,7 @@ create index packagefile_hash_index on packagefiles (hash); 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 ; @@ -194,6 +205,23 @@ begin 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 @@ -218,51 +246,6 @@ select * from packagefiles_status 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, @@ -295,15 +278,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, @@ -390,6 +395,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 ( @@ -452,6 +459,19 @@ from syncstatus BASE 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; + create view syncinfo as with preserved as ( @@ -493,10 +513,12 @@ newfiles as ( 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 + 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 @@ -507,6 +529,7 @@ modified as ( SS.rstatus in ('installing') ), -- preserve: libraries needed by something in installed or installing +/* needed as ( select distinct ED.library @@ -514,13 +537,23 @@ needed as ( 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 - where path in (select library from needed) - and SS.rstatus in ('removing', 'removed') + 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 ( @@ -545,15 +578,20 @@ expired as ( and path not in (select path from preserve) and rstatus in ('removed','updated') ) -select 'update' as op, * from modified +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, * from remove +select 'remove' as op, *, null, null, null from remove union -select 'obsolete' as op, * from expired +select 'obsolete' as op, *, null, null, null from expired union -select 'new' as op, * from newfiles +select 'new' as op, *, null, null, null from newfiles union -select 'preserve' as op, * from preserve +select 'preserve' as op, *, null, null, null from preserve ; commit;