[Bug][Dora] In some merge modes, the first commit sha of a PR cannot be obtained

[abeizn]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

To calculate PR Coding Time metric, DevLake needs to find the first commit author date of the PR first. In v0.16 and previous versions, DevLake obtains the first commit sha of PR in the following way

select * from commits
left join pull_request_commits on commits.sha = pull_request_commits.commit_sha
where pull_request_commits.pull_request_id = PR_ID
order by commits.authored_date ASC

However, most PRs of apache/incubator-devlake in the past half year were merged with squash and merge mode. In this mode, the original commit information is under the head repo (forked repo) instead of the base repo. The data in the DevLake commits table comes from GitExtractor, so the original commits information does not exist in the commits table, so we cannot find the authored_date of the original first commit.

Note: The merge commit generated after squash merge is not the first commit required by DevLake, because the authored date of this merge commit is the time of PR merge

In addition, this method of obtaining the first commit author is not applicable to rebase and merge, because the commits on the commits page of pr are 1, 2; after pr merge, the commits on the main branch are 1' 2', but only in pr_commits there will be commit 1, 2.

This method is only suitable for ordinary merge.

What do you expect to happen

The first commit sha of a PR can be obtained in all modes(Squash and merge, Rebase and merge, Merge (fast-forward), Merge (non-fast-forward))

Fix plugins

• github
• gitlab
• bitbucket
• azure (@CamilleTeruel please help fix it)
• gitee

How to reproduce

run github plugin and the repo select apache/incubator-devlake, finally, view dora PR coding time metrics

Anything else

@CamilleTeruel
pull_request_commits table schema：

TABLE pull_request_commits (
commit_sha varchar(40) COLLATE utf8mb4_bin NOT NULL,
pull_request_id varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 'This key is generated based on details from the original plugin',
created_at datetime(3) DEFAULT NULL,
updated_at datetime(3) DEFAULT NULL,
_raw_data_params varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
_raw_data_table varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
_raw_data_id bigint unsigned DEFAULT NULL,
_raw_data_remark longtext COLLATE utf8mb4_bin,
commit_authored_date datetime(3) DEFAULT NULL,
commit_author_name varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
commit_author_email varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (commit_sha,pull_request_id),
KEY idx_pull_request_commits_raw_data_params (_raw_data_params)
)

Version

main

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct