-create view syncconflicts as
-with
--- metadata different
-md_conflict as (
- select path, count(distinct
- printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
- count(distinct PFA.hash) as hashcount
- from packagefiles PFA
- join packages PA
- on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
- where PA.status in ('installing', 'installed')
- group by path
- having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
- ) > 1 or count(distinct PFA.hash) > 1)
-)
-select PFH.*,
- printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
- printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
-'hash' as conflict
-from packagefiles PFH
-where path in (select path from md_conflict where hashcount > 1)
-union
-select PFM.*,
- printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
- printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
-'md' as conflict
-from packagefiles PFM
-where path in (select path from md_conflict where mdcount > 1)
-;