X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=316f3e47ba42354640d334341d0771bafd12f0aa;hb=55218d5fc4522f66ffd3602fbc4410ebe5f0e670;hp=d5b1d103113f304d55b16e73908dba1118349700;hpb=c8cf77a41735c303259775742e1980848a71f928;p=zpackage diff --git a/db.sql b/db.sql index d5b1d10..316f3e4 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 ( @@ -61,6 +72,13 @@ create view packages_pkgid as select printf('%s-%s-%s', package, version, release) as pkgid, * from packages; +create trigger packages_delete_trigger instead of +delete on packages_pkgid +begin + delete from packages where package = OLD.package + and version = OLD.version and release = OLD.release; +end; + create trigger packages_update_trigger instead of update on packages_pkgid begin @@ -116,19 +134,19 @@ create table packagefiles ( uid integer, -- numeric uid, generally ignored gid integer, -- numeric gid, generally ignored configuration integer not null default 0, -- boolean if config file + confhash text, -- last hash on disk filetype varchar not null default 'r', -- r regular file -- d directory - -- s symlink + -- l symlink -- h hard link -- not supported -- c character special -- not supported -- b block special -- not supported -- c and b device special files add dev number column -- p fifos (i.e. pipe) -- not supported + -- s unix domain socket -- not supported target text, -- link target for links - -- device file dev numbers - devmajor integer, - devminor integer, + device integer, -- device file dev_t hash text, -- null if not a regular file mtime integer, -- seconds since epoch, finer resolution not needed primary key (package,version,release,path), @@ -140,9 +158,9 @@ create table packagefiles ( check (target is null or length(target) between 1 and 4095), check (hash is null or length(hash) between 1 and 1024), check (not (filetype = 'r' and hash is null)), - check (not (filetype = 'c' and (devmajor is null or devminor is null))), - check (not (filetype = 'b' and (devmajor is null or devminor is null))), - check (filetype in ('r','d','s','h','c','b','p')), + check (not (filetype = 'c' and device is null)), + check (not (filetype = 'b' and device is null)), + check (filetype in ('r','d','l','h','c','b','p')), check(length(username) between 1 and 256), check(length(groupname) between 1 and 256), check (configuration = 0 or configuration = 1) @@ -156,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 ; @@ -176,8 +194,7 @@ begin configuration = NEW.configuration, filetype = NEW.filetype, target = NEW.target, - devmajor = NEW.devmajor, - devminor = NEW.devminor, + device = NEW.device, hash = NEW.hash, mtime = NEW.mtime where package = OLD.package @@ -188,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 @@ -212,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, @@ -361,6 +350,16 @@ create table zpmlog ( info text -- human readable ); +create table notes ( + id integer primary key, -- rowid alias + ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')), + note text not null, + pkgid text, -- package + path text, -- file path involved + file text, -- hash of file + ack integer default 0 +); + create table history ( ts integer, -- again, probably needs timestamp sub second cmd text, @@ -466,7 +465,7 @@ syncstatus as ( newfiles as ( select distinct path,username,uid,groupname,gid,mode,filetype,mtime,hash, - target,devminor,devmajor + configuration,target,device, null as ohash from syncstatus SS where path not in (select path from syncstatus where rstatus in ('installed', 'updating', 'removing') @@ -477,9 +476,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.target, SS.devminor, SS.devmajor + 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 @@ -500,7 +502,7 @@ needed as ( preserve as ( select distinct path,username,uid,groupname,gid,mode,filetype,mtime,hash, - target,devminor,devmajor + configuration,target,device, null as ohash from syncstatus SS where path in (select library from needed) and SS.rstatus in ('removing', 'removed') @@ -509,7 +511,7 @@ preserve as ( remove as ( select distinct path,username,uid,groupname,gid,mode,filetype,mtime,hash, - target,devminor,devmajor + configuration,target,device, null as ohash from syncstatus SS where path not in ( select path from syncstatus where @@ -522,21 +524,26 @@ remove as ( expired as ( select distinct path,username,uid,groupname,gid,mode,filetype,mtime,hash, - target,devminor,devmajor + 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') ) -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;