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