X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=2e34ad38b6f0fbfc67ba5fc82ba510fe242f3f27;hb=8d39ba5617367d688e653ab40192a9c7f9a5c187;hp=816cbe55e6e5f43562cffb693160c6cc9525a182;hpb=be13fb0871a85e0ab3c6c274f836eef375a78497;p=zpackage diff --git a/db.sql b/db.sql index 816cbe5..2e34ad3 100644 --- a/db.sql +++ b/db.sql @@ -52,10 +52,35 @@ create table packages ( without rowid ; +create index package_status_index on packages (status); + create view packages_pkgid as select printf('%s-%s-%s', package, version, release) as pkgid, * from packages; +create trigger packages_update_trigger instead of +update on packages_pkgid +begin + update packages + set package = NEW.package, + version = NEW.version, + release = NEW.release, + description = NEW.description, + architecture = NEW.architecture, + url = NEW.url, + status = NEW.status, + licenses = NEW.licenses, + packager = NEW.packager, + build_time = NEW.build_time, + install_time = NEW.install_time, + checksum = NEW.checksum + where package = OLD.package + and version = OLD.version + and release = OLD.release + ; +end +; + -- handle package status history with a logging trigger. create trigger logpkgstatus after update of status on packages begin insert into zpmlog (action,target,info) @@ -71,7 +96,7 @@ create table packagetags ( tag text, set_time integer default (strftime('%s', 'now')), primary key (package,version,release,tag), - foreign key (package,version,release) references packages (package,version,release) on delete cascade + 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 @@ -111,7 +136,7 @@ create table packagefiles ( hash text, -- null if no actual content, i.e. anything but a regular file mtime integer, -- seconds since epoch, finer resolution probably not needed primary key (package,version,release,path), - foreign key (package,version,release) references packages (package,version,release) on delete 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 = 'r' and hash is null)), check (not (filetype = 'c' and (devmajor is null or devminor is null))) @@ -120,7 +145,8 @@ without rowid ; 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 ; @@ -130,6 +156,13 @@ 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 @@ -142,6 +175,7 @@ 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 @@ -156,6 +190,7 @@ 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 @@ -165,13 +200,23 @@ 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 PN.status = 'installing' +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 ( @@ -182,7 +227,9 @@ create table pathtags ( path text, -- filesystem path tag text, - primary key (package,version,release,path,tag) + primary key (package,version,release,path,tag), + foreign key (package,version,release,path) + references packagefiles on delete cascade on update cascade ) without rowid ; @@ -209,7 +256,8 @@ without rowid -- and just hash, not package? create table elflibraries ( file text primary key, - soname text + soname text, + foreign key (file) references files on delete cascade ) without rowid ; @@ -217,7 +265,8 @@ without rowid create table elfneeded ( file text, needed text, -- soname of dependency - primary key (file, needed) + primary key (file, needed), + foreign key (file) references files on delete cascade ) without rowid ; @@ -230,7 +279,7 @@ create table scripts ( stage text, hash text, primary key (package,version,release,stage), - foreign key (package,version,release) references packages (package,version,release) on delete cascade + foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade ); create view scripts_pkgid as @@ -243,12 +292,11 @@ create table packagedeps ( package text, version text, release integer, - required text, -- package name - -- following can be null for not checked - minversion text, - minrelease integer, - maxversion text, - maxrelease integer + requires text, -- package name (only) + minimum text, + maximum text, + primary key (package,version,release,package), + foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade ); -- capability labels