fix(cdn): deduplicate run-result on getItems (#5841)

sguiheux · web-flow · commit 8d4f75eeda2b · 2021-06-11T15:56:25.000+02:00
diff --git a/engine/cdn/item/dao.go b/engine/cdn/item/dao.go
@@ -319,8 +319,20 @@ func LoadOldItemByStatusAndDuration(ctx context.Context, m *gorpmapper.Mapper, d
 	return getItems(ctx, m, db, query, opts...)
 }
 
-func LoadByRunID(ctx context.Context, m *gorpmapper.Mapper, db gorp.SqlExecutor, itemType sdk.CDNItemType, runID string) ([]sdk.CDNItem, error) {
-	query := gorpmapper.NewQuery("SELECT * FROM item WHERE api_ref->>'run_id'::text = $1 AND type = $2 AND to_delete = false").Args(runID, itemType)
+func LoadRunResultByRunID(ctx context.Context, m *gorpmapper.Mapper, db gorp.SqlExecutor, runID string) ([]sdk.CDNItem, error) {
+	query := gorpmapper.NewQuery(`
+		WITH allResults AS (
+			SELECT api_ref->>'artifact_name' as name, api_ref->>'run_job_id' as run_job_id, id
+			FROM item
+			WHERE api_ref->>'run_id'::text = $1 AND type = $2  AND to_delete = false
+		),
+		deduplication AS (
+			SELECT distinct on (name) *
+			FROM allResults
+			ORDER BY name, run_job_id DESC
+		)
+		SELECT * FROM item WHERE id IN (SELECT id FROM deduplication)
+	`).Args(runID, sdk.CDNTypeItemRunResult)
 	return getItems(ctx, m, db, query)
 
 }
diff --git a/engine/cdn/item_handler.go b/engine/cdn/item_handler.go
@@ -251,7 +251,7 @@ func (s *Service) getArtifacts(ctx context.Context, r *http.Request, w http.Resp
 	if runID == "" {
 		return sdk.WrapError(sdk.ErrWrongRequest, "invalid workflow run")
 	}
-	items, err := item.LoadByRunID(ctx, s.Mapper, s.mustDBWithCtx(ctx), sdk.CDNTypeItemRunResult, runID)
+	items, err := item.LoadRunResultByRunID(ctx, s.Mapper, s.mustDBWithCtx(ctx), runID)
 	if err != nil {
 		return err
 	}
diff --git a/engine/cdn/item_handler_test.go b/engine/cdn/item_handler_test.go
@@ -378,6 +378,17 @@ func TestGetItemsArtefactHandler(t *testing.T) {
 	item2.APIRefHash = refhashLog
 	require.NoError(t, item.Insert(context.Background(), s.Mapper, db, &item2))
 
+	workerSignature.JobID += 1
+	item3 := sdk.CDNItem{
+		Type:   sdk.CDNTypeItemRunResult,
+		Status: sdk.CDNStatusItemCompleted,
+		APIRef: sdk.NewCDNRunResultApiRef(workerSignature),
+	}
+	refhash, err = item3.APIRef.ToHash()
+	require.NoError(t, err)
+	item3.APIRefHash = refhash
+	require.NoError(t, item.Insert(context.Background(), s.Mapper, db, &item3))
+
 	vars := map[string]string{
 		"type": string(sdk.CDNTypeItemRunResult),
 	}
@@ -397,5 +408,5 @@ func TestGetItemsArtefactHandler(t *testing.T) {
 	require.NoError(t, json.Unmarshal(rec.Body.Bytes(), &results))
 
 	require.Equal(t, 1, len(results))
-	require.Equal(t, item1.ID, results[0].ID)
+	require.Equal(t, item3.ID, results[0].ID)
 }

Original file line number	Diff line number	Diff line change
`@@ -251,7 +251,7 @@ func (s Service) getArtifacts(ctx context.Context, r http.Request, w http.Resp`
`251`	`251`	`if runID == "" {`
`252`	`252`	`return sdk.WrapError(sdk.ErrWrongRequest, "invalid workflow run")`
`253`	`253`	`}`
`254`		`- items, err := item.LoadByRunID(ctx, s.Mapper, s.mustDBWithCtx(ctx), sdk.CDNTypeItemRunResult, runID)`
	`254`	`+ items, err := item.LoadRunResultByRunID(ctx, s.Mapper, s.mustDBWithCtx(ctx), runID)`
`255`	`255`	`if err != nil {`
`256`	`256`	`return err`
`257`	`257`	`}`