dosubot[bot] commented on issue #8817:
URL:
https://github.com/apache/incubator-devlake/issues/8817#issuecomment-4152780444
<!-- Answer -->
Sure! Since the child tables reference the PR by its internal `github_id`
(not the human-visible `number`), you'll first need to look up the `github_id`.
Here's a single script you can run against DevLake's database — just replace
`<connection_id>` and `<repo_id>` with your actual values:
```sql
-- Step 1: Find the PR's internal github_id
SET @gh_id = (
SELECT github_id FROM _tool_github_pull_requests
WHERE connection_id = <connection_id> AND repo_id = <repo_id> AND number =
47788
);
-- Step 2: Delete child records
DELETE FROM _tool_github_pull_request_comments WHERE connection_id =
<connection_id> AND pull_request_id = @gh_id;
DELETE FROM _tool_github_pull_request_commits WHERE connection_id =
<connection_id> AND pull_request_id = @gh_id;
DELETE FROM _tool_github_pull_request_labels WHERE connection_id =
<connection_id> AND pull_id = @gh_id;
DELETE FROM _tool_github_pull_request_reviews WHERE connection_id =
<connection_id> AND pull_request_id = @gh_id;
DELETE FROM _tool_github_pull_request_issues WHERE connection_id =
<connection_id> AND pull_request_id = @gh_id;
DELETE FROM _tool_github_reviewers WHERE connection_id =
<connection_id> AND pull_request_id = @gh_id;
-- Step 3: Delete the PR itself
DELETE FROM _tool_github_pull_requests WHERE connection_id =
<connection_id> AND github_id = @gh_id;
```
Note: the labels table uses `pull_id` instead of `pull_request_id` — that's
intentional in the schema
[[1]](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/github/models/pr_label.go).
You can find your `connection_id` and `repo_id` from the DevLake config UI,
or by querying `SELECT connection_id, repo_id, number FROM
_tool_github_pull_requests WHERE number = 47788;`. Back up your DB before
running this, just in case!
<!-- Dosu Comment Footer -->
*To reply, just mention
[@dosu](https://go.dosu.dev/dosubot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=brand-link).*
---
<sup>How did I do?
[Good](https://app.dosu.dev/response-feedback/672df8ac-efba-47c7-bb0a-6877946c2ffd?feedback_type=great_response&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-great_response)
|
[Irrelevant](https://app.dosu.dev/response-feedback/672df8ac-efba-47c7-bb0a-6877946c2ffd?feedback_type=irrelevant_answer&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-irrelevant_answer)
|
[Incorrect](https://app.dosu.dev/response-feedback/672df8ac-efba-47c7-bb0a-6877946c2ffd?feedback_type=incorrect_sources&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-incorrect_sources)
|
[Verbose](https://app.dosu.dev/response-feedback/672df8ac-efba-47c7-bb0a-6877946c2ffd?feedback_type=too_verbose&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-too_verbose)
| [Hallucination](https://app.dosu.dev/response-feedback/672df8ac-efba
-47c7-bb0a-6877946c2ffd?feedback_type=hallucination&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-hallucination)
| [Report
🐛](https://app.dosu.dev/response-feedback/672df8ac-efba-47c7-bb0a-6877946c2ffd?feedback_type=bug_report&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-bug_report)
|
[Other](https://app.dosu.dev/response-feedback/672df8ac-efba-47c7-bb0a-6877946c2ffd?feedback_type=other&utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=feedback-other)</sup>
[](https://app.dosu.dev/b4e8e847-d479-4541-83a8-d88d83fea5c9/ask?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=chat-badge) [](https://cursor.com/link/prompt?text=Sure%21%20Since%20the%20child%20tables%20reference%20the%20PR%20by%20its%20internal%20%60github_id%60%20%28not%20the%20human-visible%20%60number%60%29%2C%20you%27ll%20first%20need%20to%20look%20up%20the%20%60github_id%60.%20Here%27s%20a%20single%20script%20you%20can%20run%20against%20DevLake%27s%20database%20%E2%80%94%20just%20replace%20%60%26lt%3Bconnection_id%26gt%3B%60%20and%20%60%26lt%3Brepo_id%26gt%3B%60%20with%20your%20actual%20values%3A%0A%0A%60%60%60sql%0A--%20Step%201%3A%20Find%20the%20PR%27s%20internal%20github_id%0ASET%20%40gh_id%20%3D%20%28%0A%20%20SELECT%20github_id%20FROM%20_tool_github_pull_requests%0A%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20
AND%20repo_id%20%3D%20%3Crepo_id%3E%20AND%20number%20%3D%2047788%0A%29%3B%0A%0A--%20Step%202%3A%20Delete%20child%20records%0ADELETE%20FROM%20_tool_github_pull_request_comments%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20AND%20pull_request_id%20%3D%20%40gh_id%3B%0ADELETE%20FROM%20_tool_github_pull_request_commits%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20AND%20pull_request_id%20%3D%20%40gh_id%3B%0ADELETE%20FROM%20_tool_github_pull_request_labels%20%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20AND%20pull_id%20%3D%20%40gh_id%3B%0ADELETE%20FROM%20_tool_github_pull_request_reviews%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20AND%20pull_request_id%20%3D%20%40gh_id%3B%0ADELETE%20FROM%20_tool_github_pull_request_issues%20%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20AND%20pull_request_id%20%3D%20%40gh_id%3B%0ADELETE%20FROM%20_tool_github_reviewers%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id
%3E%20AND%20pull_request_id%20%3D%20%40gh_id%3B%0A%0A--%20Step%203%3A%20Delete%20the%20PR%20itself%0ADELETE%20FROM%20_tool_github_pull_requests%20%20%20%20%20%20%20%20%20WHERE%20connection_id%20%3D%20%3Cconnection_id%3E%20AND%20github_id%20%3D%20%40gh_id%3B%0A%60%60%60%0A%0ANote%3A%20the%20labels%20table%20uses%20%60pull_id%60%20instead%20of%20%60pull_request_id%60%20%E2%80%94%20that%27s%20intentional%20in%20the%20schema%20%5B%5B1%5D%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/github/models/pr_label.go%29.%0A%0AYou%20can%20find%20your%20%60connection_id%60%20and%20%60repo_id%60%20from%20the%20DevLake%20config%20UI%2C%20or%20by%20querying%20%60SELECT%20connection_id%2C%20repo_id%2C%20number%20FROM%20_tool_github_pull_requests%20WHERE%20number%20%3D%2047788%3B%60.%20Back%20up%20your%20DB%20before%20running%20this%2C%20just%20in%20case%21) [](https://go.dosu.dev/discord-bot?utm_source=github&utm_medium=bot-comment&utm_campaign=github-assistant&utm_content=join-discord) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/incubator-devlake/issues/8817)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
