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)
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
release integer,
path text, -- filesystem path
- mode text, -- perms, use text for octal rep?
- username text, -- name of owner
- groupname text, -- group of owner
+ mode text not null, -- perms, use text for octal rep?
+ username text not null, -- name of owner
+ groupname text not null, -- group of owner
uid integer, -- numeric uid, generally ignored
gid integer, -- numeric gid, generally ignored
- filetype varchar default 'r',
+ configuration integer not null default 0, -- boolean if config file
+ filetype varchar not null default 'r',
-- r regular file
-- d directory
-- s symlink
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)))
)
without rowid
;
-create view installedfiles as
-select PF.package, PF.version, PF.release,
-printf('%s-%s-%s', PF.package, PF.version, PF.release) as pkgid,
-PF.path, PF.hash, PF.filetype
-from packagefiles PF
-left join packages P
-on P.package = PF.package and P.version = PF.version and P.release = PF.release
-where
-P.status = 'installed'
+create view packagefiles_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from packagefiles
;
create view installed_ref_count as
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
+left join packages_pkgid P on P.pkgid = PF.pkgid
+;
+
+create view installedfiles as
+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,
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
;
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
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
projects / zpackage / blobdiff