X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=548dba8bcca125c6d5533814a75d8d21f22298e3;hb=b14d452ddce90289441c07447d4d4a27821a5ce3;hp=d4f2d3e0f1d12200a4d497da31c6a5e5aa409330;hpb=82b289d233b84181e0a926a7b1085a321e7eb163;p=zpackage diff --git a/db.sql b/db.sql index d4f2d3e..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 ( @@ -22,7 +33,6 @@ create table packages ( package text not null, version text not null, -- the upstream version string release integer not null, -- the local release number --- pkgid text, -- the three above joined with '-' -- metadata columns description text, @@ -33,31 +43,42 @@ create table packages ( packager text, build_time integer default (strftime('%s', 'now')), install_time integer, - checksum text, -- checksum of package contents. null for incompleted packages + hash text, -- see integ.c for package hash details primary key (package,version,release), check (typeof(package) = 'text'), check (typeof(version) = 'text'), check (typeof(release) = 'integer'), - check (release > 0) - -- TODO enforce name and version conventions - -- check(instr(version,'-') = 0) - -- check(instr(package,'/') = 0) - -- check(instr(package,'/') = 0) - -- check(instr(version,' ') = 0) - -- check(instr(package,' ') = 0) - -- check(instr(package,' ') = 0) - -- check(length(package) < 64) - -- check(length(version) < 32) + check (release > 0), + -- enforce name and version conventions + check(instr(package,' ') = 0), + check(instr(package,'/') = 0), + check(instr(package,':') = 0), + check(instr(version,' ') = 0), + check(instr(version,'-') = 0), + check(instr(version,'/') = 0), + check(instr(version,':') = 0), + check(length(package) < 64), + check(length(package) > 0), + check(length(version) < 32), + check(length(version) > 0) ) without rowid ; create index package_status_index on packages (status); +create index package_package_index on packages (package); 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 @@ -73,7 +94,7 @@ begin packager = NEW.packager, build_time = NEW.build_time, install_time = NEW.install_time, - checksum = NEW.checksum + hash = NEW.hash where package = OLD.package and version = OLD.version and release = OLD.release @@ -99,14 +120,6 @@ create table packagetags ( foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade ); --- packagefile hash is columns as text, joined with null bytes, then --- sha256 sum of that --- package checksum is package columns as text, joined with null bytes, --- other than the checksum and install_time column --- then that hashed. finally, that hash, plus the ascii sorted --- hashes of the package files all joined with newlines, hashed. --- really don't like this. - -- files contained in a package create table packagefiles ( -- package id triple @@ -115,47 +128,60 @@ 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 + 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 and b device special files add dev number column - -- b block special - -- p fifos (i.e. pipe) + -- 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, should probably be a separate table - devmajor integer, - devminor integer, - hash text, -- null if no actual content, i.e. anything but a regular file - mtime integer, -- seconds since epoch, finer resolution probably not needed + 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), - foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade, + foreign key (package,version,release) + references packages (package,version,release) + on delete cascade on update cascade, check (not (filetype = 'l' and target is null)), + 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 (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 (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) ) 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 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 ; create trigger packagefiles_update_trigger instead of update on packagefiles_pkgid begin - update packages + update packagefiles set package = NEW.package, version = NEW.version, release = NEW.release, @@ -168,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 @@ -180,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 @@ -204,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, @@ -264,43 +261,54 @@ create table pathtags ( without rowid ; -create table elfinfo ( - file text primary key, -- hash of blob - elftype text, - foreign key (file) references files on delete cascade -) -without rowid -; - -create table elfdeps ( - file text, - soname text, - dependency text, - primary key (file, soname, dependency), - foreign key (file) references files on delete cascade -) -without rowid +create view elfdeps as +select PF.pkgid, PF.status, PF.path, N.needed as needs, +PL.path as library, PL.pkgid provider, PL.status as library_status +from packagefiles_status PF +join elfneeded N on N.file = PF.hash +left join elflibraries L on N.needed = L.soname +left join packagefiles_status PL on PL.hash = L.file ; -- TODO just elf information? -- and just hash, not package? create table elflibraries ( file text primary key, - soname text, - foreign key (file) references files on delete cascade + soname 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), - foreign key (file) references files on delete cascade + 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, @@ -364,6 +372,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, @@ -377,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 ( @@ -395,4 +415,183 @@ create table packagesource ( repository text references repository ); +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' +), +-- 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') + group by path + having (count(distinct mds) > 1 or count(distinct hash) > 1) +) +select BASE.*, 'hash' as conflict +from syncstatus BASE +where path in (select path from md_conflict where hashcount > 1) +union +select BASE.*, 'md' as conflict +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 ( + 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 + 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 + -- 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 +), +*/ +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 + 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 ( + 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') +), +-- 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') +) +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 +union +select 'obsolete' as op, *, null, null, null from expired +union +select 'new' as op, *, null, null, null from newfiles +union +select 'preserve' as op, *, null, null, null from preserve +; + commit;