From: Nathan Wagner <nw@hydaspes.if.org>
Date: Sun, 2 Dec 2018 08:36:30 +0000 (+0000)
Subject: refactor syncconflicts view for performance
X-Git-Tag: v0.3.3~8
X-Git-Url: https://pd.if.org/git/?p=zpackage;a=commitdiff_plain;h=7256198c4677c64fde0da2dc0bd14f29db799ca3

refactor syncconflicts view for performance
---

diff --git a/db.sql b/db.sql
index 6588650..1ad3d17 100644
--- a/db.sql
+++ b/db.sql
@@ -416,46 +416,33 @@ create table packagesource (
 );
 
 create view syncconflicts as
-with
-preserved as (
-	select BASE.*, 'preserved' as rstatus
-	from packagefiles_status BASE
-	join elflibraries EL on EL.file = BASE.hash
-	where
-	BASE.status in ('removed', 'updated')
-	and BASE.hash in (select hash from packagefiles_status where
-		status in ('installed'))
-),
-syncstatus as (
-	select distinct BASE.*,
-       	case when P.status = 'installing' and BASE.status = 'installed'
-		then 'updating'
-	when BASE.status in ('removed','updated')
-		and BASE.path in (select path from preserved) then
-		'preserved'
-	else
-		BASE.status
-	end as rstatus
-	from packagefiles_status BASE
-	left join packages P on P.package = BASE.package
-		and BASE.status in ('installed', 'removing')
-		and P.status = 'installing'
-),
+with 
 -- metadata different
 md_conflict as (
-	select path, count(distinct mds) as mdcount,
-       	count(distinct hash) as hashcount
-	from syncstatus SS
-	where SS.rstatus in ('installing', 'installed')
+	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 mds) > 1 or count(distinct hash) > 1)
+	having (count(distinct
+	printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
+	) > 1 or count(distinct PFA.hash) > 1)
 )
-select BASE.*, 'hash' as conflict
-from syncstatus BASE
+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 BASE.*, 'md' as conflict
-from syncstatus BASE
+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)
 ;
 
@@ -628,6 +615,9 @@ select 'preserve',
 	printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
 	null as omds
 from preserve PFP
+	-- TODO don't include installed or installing
+	-- left join packages P on P.package = PFP.package ...
+	-- where P.status not in ('installed','installing')
 ;
 
 commit;