From fbe6d9dc6b9f04e8be219ad4b442df9e08c16b7b Mon Sep 17 00:00:00 2001 From: Lunny Xiao Date: Tue, 10 Dec 2024 13:15:06 -0800 Subject: Use batch database operations instead of one by one to optimze api pulls (#32680) Resolve #31492 The response time for the Pull Requests API has improved significantly, dropping from over `2000ms` to about `350ms` on my local machine. It's about `6` times faster. A key area for further optimization lies in batch-fetching data for `apiPullRequest.ChangedFiles, apiPullRequest.Additions, and apiPullRequest.Deletions`. Tests `TestAPIViewPulls` does exist and new tests added. - This PR also fixes some bugs in `GetDiff` functions. - This PR also fixes data inconsistent in test data. For a pull request, the head branch's reference should be equal to the reference in `pull/xxx/head`. --- models/fixtures/review.yml | 20 +++++++++-- models/issues/pull_list.go | 59 ++++++++++++++++++++++++++++++++ models/issues/pull_list_test.go | 64 +++++++++++++++++++++++++++++++++++ models/issues/pull_test.go | 20 ++--------- models/issues/review_list.go | 11 ++---- models/issues/review_test.go | 32 +++++++++++------- models/organization/team_list.go | 5 +++ models/organization/team_list_test.go | 25 ++++++++++++++ 8 files changed, 195 insertions(+), 41 deletions(-) create mode 100644 models/issues/pull_list_test.go create mode 100644 models/organization/team_list_test.go (limited to 'models') diff --git a/models/fixtures/review.yml b/models/fixtures/review.yml index 0438ceadae..5b8bbceca9 100644 --- a/models/fixtures/review.yml +++ b/models/fixtures/review.yml @@ -4,6 +4,7 @@ reviewer_id: 1 issue_id: 2 content: "Demo Review" + original_author_id: 0 updated_unix: 946684810 created_unix: 946684810 - @@ -12,6 +13,7 @@ reviewer_id: 534543 issue_id: 534543 content: "Invalid Review #1" + original_author_id: 0 updated_unix: 946684810 created_unix: 946684810 - @@ -20,6 +22,7 @@ reviewer_id: 1 issue_id: 343545 content: "Invalid Review #2" + original_author_id: 0 updated_unix: 946684810 created_unix: 946684810 - @@ -28,6 +31,7 @@ reviewer_id: 1 issue_id: 2 content: "Pending Review" + original_author_id: 0 updated_unix: 946684810 created_unix: 946684810 - @@ -36,6 +40,7 @@ reviewer_id: 1 issue_id: 3 content: "New review 1" + original_author_id: 0 updated_unix: 946684810 created_unix: 946684810 - @@ -61,8 +66,8 @@ type: 1 reviewer_id: 4 issue_id: 3 - original_author_id: 0 content: "New review 5" + original_author_id: 0 commit_id: 8091a55037cd59e47293aca02981b5a67076b364 stale: true updated_unix: 946684813 @@ -73,9 +78,9 @@ reviewer_id: 2 issue_id: 3 content: "New review 3 rejected" + original_author_id: 0 updated_unix: 946684814 created_unix: 946684814 - original_author_id: 0 - id: 10 @@ -83,6 +88,7 @@ reviewer_id: 100 issue_id: 3 content: "a deleted user's review" + original_author_id: 0 official: true updated_unix: 946684815 created_unix: 946684815 @@ -112,6 +118,7 @@ reviewer_id: 5 issue_id: 11 content: "old review from user5" + original_author_id: 0 updated_unix: 946684820 created_unix: 946684820 @@ -121,6 +128,7 @@ reviewer_id: 5 issue_id: 11 content: "duplicate review from user5 (latest)" + original_author_id: 0 updated_unix: 946684830 created_unix: 946684830 @@ -130,6 +138,7 @@ reviewer_id: 6 issue_id: 11 content: "singular review from org6 and final review for this pr" + original_author_id: 0 updated_unix: 946684831 created_unix: 946684831 @@ -139,6 +148,7 @@ reviewer_id: 20 issue_id: 20 content: "review request for user20" + original_author_id: 0 updated_unix: 946684832 created_unix: 946684832 @@ -148,6 +158,7 @@ reviewer_id: 20 issue_id: 20 content: "review approved by user20" + original_author_id: 0 updated_unix: 946684833 created_unix: 946684833 @@ -158,6 +169,7 @@ reviewer_team_id: 5 issue_id: 20 content: "review request for team5" + original_author_id: 0 updated_unix: 946684834 created_unix: 946684834 @@ -168,6 +180,7 @@ reviewer_team_id: 0 issue_id: 20 content: "review request for user15" + original_author_id: 0 updated_unix: 946684835 created_unix: 946684835 @@ -177,6 +190,7 @@ reviewer_id: 1 issue_id: 2 content: "Review Comment" + original_author_id: 0 updated_unix: 946684810 created_unix: 946684810 @@ -186,6 +200,7 @@ reviewer_id: 5 issue_id: 3 content: "reviewed by user5" + original_author_id: 0 commit_id: 4a357436d925b5c974181ff12a994538ddc5a269 updated_unix: 946684816 created_unix: 946684816 @@ -196,5 +211,6 @@ reviewer_id: 5 issue_id: 3 content: "review request for user5" + original_author_id: 0 updated_unix: 946684817 created_unix: 946684817 diff --git a/models/issues/pull_list.go b/models/issues/pull_list.go index 9155ea0834..59010aa9d0 100644 --- a/models/issues/pull_list.go +++ b/models/issues/pull_list.go @@ -16,6 +16,7 @@ import ( "code.gitea.io/gitea/modules/log" "code.gitea.io/gitea/modules/util" + "xorm.io/builder" "xorm.io/xorm" ) @@ -240,6 +241,64 @@ func (prs PullRequestList) GetIssueIDs() []int64 { }) } +func (prs PullRequestList) LoadReviewCommentsCounts(ctx context.Context) (map[int64]int, error) { + issueIDs := prs.GetIssueIDs() + countsMap := make(map[int64]int, len(issueIDs)) + counts := make([]struct { + IssueID int64 + Count int + }, 0, len(issueIDs)) + if err := db.GetEngine(ctx).Select("issue_id, count(*) as count"). + Table("comment").In("issue_id", issueIDs).And("type = ?", CommentTypeReview). + GroupBy("issue_id").Find(&counts); err != nil { + return nil, err + } + for _, c := range counts { + countsMap[c.IssueID] = c.Count + } + return countsMap, nil +} + +func (prs PullRequestList) LoadReviews(ctx context.Context) (ReviewList, error) { + issueIDs := prs.GetIssueIDs() + reviews := make([]*Review, 0, len(issueIDs)) + + subQuery := builder.Select("max(id) as id"). + From("review"). + Where(builder.In("issue_id", issueIDs)). + And(builder.In("`type`", ReviewTypeApprove, ReviewTypeReject, ReviewTypeRequest)). + And(builder.Eq{ + "dismissed": false, + "original_author_id": 0, + "reviewer_team_id": 0, + }). + GroupBy("issue_id, reviewer_id") + // Get latest review of each reviewer, sorted in order they were made + if err := db.GetEngine(ctx).In("id", subQuery).OrderBy("review.updated_unix ASC").Find(&reviews); err != nil { + return nil, err + } + + teamReviewRequests := make([]*Review, 0, 5) + subQueryTeam := builder.Select("max(id) as id"). + From("review"). + Where(builder.In("issue_id", issueIDs)). + And(builder.Eq{ + "original_author_id": 0, + }).And(builder.Neq{ + "reviewer_team_id": 0, + }). + GroupBy("issue_id, reviewer_team_id") + if err := db.GetEngine(ctx).In("id", subQueryTeam).OrderBy("review.updated_unix ASC").Find(&teamReviewRequests); err != nil { + return nil, err + } + + if len(teamReviewRequests) > 0 { + reviews = append(reviews, teamReviewRequests...) + } + + return reviews, nil +} + // HasMergedPullRequestInRepo returns whether the user(poster) has merged pull-request in the repo func HasMergedPullRequestInRepo(ctx context.Context, repoID, posterID int64) (bool, error) { return db.GetEngine(ctx). diff --git a/models/issues/pull_list_test.go b/models/issues/pull_list_test.go new file mode 100644 index 0000000000..8b814a0d0f --- /dev/null +++ b/models/issues/pull_list_test.go @@ -0,0 +1,64 @@ +// Copyright 2024 The Gitea Authors. All rights reserved. +// SPDX-License-Identifier: MIT + +package issues_test + +import ( + "testing" + + "code.gitea.io/gitea/models/db" + issues_model "code.gitea.io/gitea/models/issues" + "code.gitea.io/gitea/models/unittest" + + "github.com/stretchr/testify/assert" +) + +func TestPullRequestList_LoadAttributes(t *testing.T) { + assert.NoError(t, unittest.PrepareTestDatabase()) + + prs := []*issues_model.PullRequest{ + unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}), + unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}), + } + assert.NoError(t, issues_model.PullRequestList(prs).LoadAttributes(db.DefaultContext)) + for _, pr := range prs { + assert.NotNil(t, pr.Issue) + assert.Equal(t, pr.IssueID, pr.Issue.ID) + } + + assert.NoError(t, issues_model.PullRequestList([]*issues_model.PullRequest{}).LoadAttributes(db.DefaultContext)) +} + +func TestPullRequestList_LoadReviewCommentsCounts(t *testing.T) { + assert.NoError(t, unittest.PrepareTestDatabase()) + + prs := []*issues_model.PullRequest{ + unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}), + unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}), + } + reviewComments, err := issues_model.PullRequestList(prs).LoadReviewCommentsCounts(db.DefaultContext) + assert.NoError(t, err) + assert.Len(t, reviewComments, 2) + for _, pr := range prs { + assert.EqualValues(t, reviewComments[pr.IssueID], 1) + } +} + +func TestPullRequestList_LoadReviews(t *testing.T) { + assert.NoError(t, unittest.PrepareTestDatabase()) + + prs := []*issues_model.PullRequest{ + unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}), + unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}), + } + reviewList, err := issues_model.PullRequestList(prs).LoadReviews(db.DefaultContext) + assert.NoError(t, err) + // 1, 7, 8, 9, 10, 22 + assert.Len(t, reviewList, 6) + assert.EqualValues(t, 1, reviewList[0].ID) + assert.EqualValues(t, 7, reviewList[1].ID) + assert.EqualValues(t, 8, reviewList[2].ID) + assert.EqualValues(t, 9, reviewList[3].ID) + assert.EqualValues(t, 10, reviewList[4].ID) + assert.EqualValues(t, 22, reviewList[5].ID) +} diff --git a/models/issues/pull_test.go b/models/issues/pull_test.go index 675c90527d..cb7b47263d 100644 --- a/models/issues/pull_test.go +++ b/models/issues/pull_test.go @@ -79,7 +79,7 @@ func TestPullRequestsNewest(t *testing.T) { func TestLoadRequestedReviewers(t *testing.T) { assert.NoError(t, unittest.PrepareTestDatabase()) - pull := unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}) + pull := unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}) assert.NoError(t, pull.LoadIssue(db.DefaultContext)) issue := pull.Issue assert.NoError(t, issue.LoadRepo(db.DefaultContext)) @@ -93,7 +93,7 @@ func TestLoadRequestedReviewers(t *testing.T) { assert.NotNil(t, comment) assert.NoError(t, pull.LoadRequestedReviewers(db.DefaultContext)) - assert.Len(t, pull.RequestedReviewers, 1) + assert.Len(t, pull.RequestedReviewers, 6) comment, err = issues_model.RemoveReviewRequest(db.DefaultContext, issue, user1, &user_model.User{}) assert.NoError(t, err) @@ -234,22 +234,6 @@ func TestPullRequest_UpdateCols(t *testing.T) { unittest.CheckConsistencyFor(t, pr) } -func TestPullRequestList_LoadAttributes(t *testing.T) { - assert.NoError(t, unittest.PrepareTestDatabase()) - - prs := []*issues_model.PullRequest{ - unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 1}), - unittest.AssertExistsAndLoadBean(t, &issues_model.PullRequest{ID: 2}), - } - assert.NoError(t, issues_model.PullRequestList(prs).LoadAttributes(db.DefaultContext)) - for _, pr := range prs { - assert.NotNil(t, pr.Issue) - assert.Equal(t, pr.IssueID, pr.Issue.ID) - } - - assert.NoError(t, issues_model.PullRequestList([]*issues_model.PullRequest{}).LoadAttributes(db.DefaultContext)) -} - // TODO TestAddTestPullRequestTask func TestPullRequest_IsWorkInProgress(t *testing.T) { diff --git a/models/issues/review_list.go b/models/issues/review_list.go index a5ceb21791..bc7d7ec0f0 100644 --- a/models/issues/review_list.go +++ b/models/issues/review_list.go @@ -47,14 +47,9 @@ func (reviews ReviewList) LoadReviewersTeams(ctx context.Context) error { } } - teamsMap := make(map[int64]*organization_model.Team, 0) - for _, teamID := range reviewersTeamsIDs { - team, err := organization_model.GetTeamByID(ctx, teamID) - if err != nil { - return err - } - - teamsMap[teamID] = team + teamsMap, err := organization_model.GetTeamsByIDs(ctx, reviewersTeamsIDs) + if err != nil { + return err } for _, review := range reviews { diff --git a/models/issues/review_test.go b/models/issues/review_test.go index 942121fd8f..50330e3ff2 100644 --- a/models/issues/review_test.go +++ b/models/issues/review_test.go @@ -126,42 +126,48 @@ func TestGetReviewersByIssueID(t *testing.T) { user2 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 2}) org3 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 3}) user4 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 4}) + user5 := unittest.AssertExistsAndLoadBean(t, &user_model.User{ID: 5}) expectedReviews := []*issues_model.Review{} expectedReviews = append(expectedReviews, &issues_model.Review{ + ID: 7, Reviewer: org3, Type: issues_model.ReviewTypeReject, UpdatedUnix: 946684812, }, &issues_model.Review{ + ID: 8, Reviewer: user4, Type: issues_model.ReviewTypeApprove, UpdatedUnix: 946684813, }, &issues_model.Review{ + ID: 9, Reviewer: user2, Type: issues_model.ReviewTypeReject, UpdatedUnix: 946684814, - }) + }, + &issues_model.Review{ + ID: 10, + Reviewer: user_model.NewGhostUser(), + Type: issues_model.ReviewTypeReject, + UpdatedUnix: 946684815, + }, + &issues_model.Review{ + ID: 22, + Reviewer: user5, + Type: issues_model.ReviewTypeRequest, + UpdatedUnix: 946684817, + }, + ) allReviews, err := issues_model.GetReviewsByIssueID(db.DefaultContext, issue.ID) assert.NoError(t, err) for _, review := range allReviews { assert.NoError(t, review.LoadReviewer(db.DefaultContext)) } - if assert.Len(t, allReviews, 3) { - for i, review := range allReviews { - assert.Equal(t, expectedReviews[i].Reviewer, review.Reviewer) - assert.Equal(t, expectedReviews[i].Type, review.Type) - assert.Equal(t, expectedReviews[i].UpdatedUnix, review.UpdatedUnix) - } - } - - allReviews, err = issues_model.GetReviewsByIssueID(db.DefaultContext, issue.ID) - assert.NoError(t, err) - assert.NoError(t, allReviews.LoadReviewers(db.DefaultContext)) - if assert.Len(t, allReviews, 3) { + if assert.Len(t, allReviews, 5) { for i, review := range allReviews { assert.Equal(t, expectedReviews[i].Reviewer, review.Reviewer) assert.Equal(t, expectedReviews[i].Type, review.Type) diff --git a/models/organization/team_list.go b/models/organization/team_list.go index cc2a50236a..4ceb405e31 100644 --- a/models/organization/team_list.go +++ b/models/organization/team_list.go @@ -131,3 +131,8 @@ func GetTeamsByOrgIDs(ctx context.Context, orgIDs []int64) (TeamList, error) { teams := make([]*Team, 0, 10) return teams, db.GetEngine(ctx).Where(builder.In("org_id", orgIDs)).Find(&teams) } + +func GetTeamsByIDs(ctx context.Context, teamIDs []int64) (map[int64]*Team, error) { + teams := make(map[int64]*Team, len(teamIDs)) + return teams, db.GetEngine(ctx).Where(builder.In("`id`", teamIDs)).Find(&teams) +} diff --git a/models/organization/team_list_test.go b/models/organization/team_list_test.go new file mode 100644 index 0000000000..5526446e22 --- /dev/null +++ b/models/organization/team_list_test.go @@ -0,0 +1,25 @@ +// Copyright 2024 The Gitea Authors. All rights reserved. +// SPDX-License-Identifier: MIT + +package organization_test + +import ( + "testing" + + "code.gitea.io/gitea/models/db" + org_model "code.gitea.io/gitea/models/organization" + "code.gitea.io/gitea/models/unittest" + + "github.com/stretchr/testify/assert" +) + +func Test_GetTeamsByIDs(t *testing.T) { + assert.NoError(t, unittest.PrepareTestDatabase()) + + // 1 owner team, 2 normal team + teams, err := org_model.GetTeamsByIDs(db.DefaultContext, []int64{1, 2}) + assert.NoError(t, err) + assert.Len(t, teams, 2) + assert.Equal(t, "Owners", teams[1].Name) + assert.Equal(t, "team1", teams[2].Name) +} -- cgit v1.2.3