create view preserve_packages as
with package_libraries_status as (
select P.package, P.release, P.version, P.status, EL.soname
from
elflibraries EL
join packagefiles PF on PF.hash = EL.file
join packages P
on P.package = PF.package and P.version = PF.version and P.release = PF.release
)
, package_libraries_needed_status as (
select --distinct
P.package, P.release, P.version, P.status, EN.needed as soname, group_concat(PF.path) as needed_by
from
elfneeded EN
join packagefiles PF on PF.hash = EN.file
join packages P
on P.package = PF.package and P.version = PF.version and P.release = PF.release
group by P.package, P.release, P.version, P.status, EN.needed
)
select
PLS.package, PLS.version, PLS.release, PLS.soname
,PLN.package as npackage, PLN.version as nversion, PLN.release as nrelease, PLN.needed_by
--,PLI.soname, PLI.package, PLI.version, PLI.release
from package_libraries_needed_status PLN
join package_libraries_status PLS
on PLN.soname = PLS.soname
and PLN.status = 'installed'
and PLS.status != 'installed'
left join package_libraries_status PLI
on PLI.status = 'installed'
and PLI.soname = PLN.soname
where PLI.package is null
;