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 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
;
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
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 (