X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=d74803da8800e5115cacc3f7e3c8cd6e7277f931;hb=c9b5db2d4b9c4691bbcc2c61a47c4fc279eb057b;hp=bf6fd22a07c01b886d9d6e26a9dc1557d5132c03;hpb=1543353d5f552717ef55bea0a70f5ef80072ce3d;p=zpackage diff --git a/db.sql b/db.sql index bf6fd22..d74803d 100644 --- a/db.sql +++ b/db.sql @@ -33,7 +33,7 @@ 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 primary key (package,version,release), check (typeof(package) = 'text'), check (typeof(version) = 'text'), @@ -52,6 +52,9 @@ create table packages ( 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; @@ -71,7 +74,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 @@ -97,14 +100,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 @@ -137,22 +132,63 @@ create table packagefiles ( 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 = 'r' and hash is null)), - check (not (filetype = 'c' and (devmajor is null or devminor 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 (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, * +select printf('%s-%s-%s', package, version, release) as pkgid, *, +printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds from packagefiles ; +create trigger packagefiles_update_trigger instead of +update on packagefiles_pkgid +begin + update packagefiles + set package = NEW.package, + version = NEW.version, + release = NEW.release, + path = NEW.path, + mode = NEW.mode, + username = NEW.username, + groupname = NEW.groupname, + uid = NEW.uid, + gid = NEW.gid, + configuration = NEW.configuration, + filetype = NEW.filetype, + target = NEW.target, + devmajor = NEW.devmajor, + devminor = NEW.devminor, + hash = NEW.hash, + mtime = NEW.mtime + where package = OLD.package + and version = OLD.version + and release = OLD.release + and path = OLD.path + ; +end +; + create view installed_ref_count as select I.path, count(*) as refcount from installedfiles I group by I.path ; +create view sync_status_ref_count as +select path, status, count(*) as refcount +from packagefiles_status +where status in ('installed', 'installing', 'removing') +group by path, status +; + create view packagefiles_status as select P.status, PF.* from packagefiles_pkgid PF @@ -197,6 +233,16 @@ left join packagefiles_status PN 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 ( @@ -214,30 +260,20 @@ 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 ; @@ -245,8 +281,7 @@ without rowid create table elfneeded ( file text, needed text, -- soname of dependency - primary key (file, needed), - foreign key (file) references files on delete cascade + primary key (file, needed) ) without rowid ; @@ -345,4 +380,151 @@ 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 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, + target,devminor,devmajor + 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.target, SS.devminor, SS.devmajor + 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 +), +preserve as ( + select distinct + path,username,uid,groupname,gid,mode,filetype,mtime,hash, + target,devminor,devmajor + 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, + target,devminor,devmajor + 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, + target,devminor,devmajor + 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 +union +select 'remove' as op, * from remove +union +select 'obsolete' as op, * from expired +union +select 'new' as op, * from newfiles +union +select 'preserve' as op, * from preserve +; + commit;