Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Question][Blueprint] ScopeId field type #8237

Open
kostas-petrakis opened this issue Dec 10, 2024 · 5 comments · May be fixed by #8248
Open

[Question][Blueprint] ScopeId field type #8237

kostas-petrakis opened this issue Dec 10, 2024 · 5 comments · May be fixed by #8248
Labels
type/question This issue is a question

Comments

@kostas-petrakis
Copy link

kostas-petrakis commented Dec 10, 2024 •
edited
Loading

Question: ScopeId as varchar in BlueprintScope causing Jira plugin issues

I'm working on fixing some PostgreSQL-related issues and noticed that the ScopeId field in the BlueprintScope struct is defined as a string:

type BlueprintScope struct {
	BlueprintId  uint64 `json:"-" gorm:"primaryKey" validate:"required"`
	PluginName   string `json:"-" gorm:"primaryKey;type:varchar(255)" validate:"required"`
	ConnectionId uint64 `json:"-" gorm:"primaryKey" validate:"required"`
	ScopeId      string `json:"scopeId" gorm:"primaryKey;type:varchar(255)" validate:"required"`
}

This is causing errors when configuring the Jira plugin, specifically when ScopeId (which is a string) is compared with board_id (which is a uint64 in the Jira plugin's table)

ERROR: operator does not exist: bigint = character varying (SQLSTATE 42883)\n\x1b[0m\x1b[33m[1.048ms] \x1b[34;1m[rows:0]\x1b[0m SELECT bp.id AS blueprint_id, bp.project_name, bps.scope_id, _tool_jira_boards.* FROM _devlake_blueprint_scopes bps LEFT JOIN _devlake_blueprints bp ON (bp.id = bps.blueprint_id) LEFT JOIN _tool_jira_boards ON (_tool_jira_boards.connection_id = bps.connection_id AND _tool_jira_boards.board_id = bps.scope_id) WHERE bps.plugin_name = 'jira' AND bps.connection_id = 2"


2024/12/10 15:42:34 [Recovery] 2024/12/10 - 15:42:34 panic recovered:
ERROR: operator does not exist: bigint = character varying (SQLSTATE 42883) (500)
Wraps: (2) ERROR: operator does not exist: bigint = character varying (SQLSTATE 42883)

I'm currently working on a workaround that involves checking the database dialect, but a cleaner solution might be to change ScopeId to uint64.

Before I pursue that route, I wanted to check if there's a specific reason why ScopeId is defined as string. Is there a plugin or use case that relies on ScopeId being a string? Otherwise we could possibly convert this field type?
@kostas-petrakis kostas-petrakis added the type/question This issue is a question label Dec 10, 2024
@dosubot DosuBot
Copy link

dosubot bot commented Dec 10, 2024

The ScopeId in the BlueprintScope struct is indeed defined as a string, which is causing issues when compared with bigint fields like board_id in the Jira plugin's table. Unfortunately, there is no specific documentation or comments in the codebase explaining why ScopeId was initially defined as a string, nor is there information on other plugins or modules that might rely on ScopeId being a string [1].

Changing ScopeId to uint64 could potentially resolve the type mismatch issue you're encountering. However, it's important to consider the implications of such a change across the codebase. This change might affect other parts of the system that interact with ScopeId, so thorough testing would be necessary to ensure compatibility and functionality are maintained.

If you decide to proceed with changing the data type, ensure that all related database fields and queries are updated accordingly to prevent further type mismatch errors.

To continue talking to Dosu, mention @dosu.

Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other

@kostas-petrakis kostas-petrakis changed the title [Question][Module Name] Question title [Question][Blueprint] ScopeId field type Dec 10, 2024
@klesh
Copy link
Contributor

klesh commented Dec 12, 2024

Changing BlueprintScope.ScopeId from string to int might resolve the Jira issue, but would break compatibility with other plugins that use a non-integer ScopeId, such as Jenkins (which uses a string).

A better solution might be to convert the Jira board ID to a string before comparison.

Could you also please share the location of the query in question?

@kostas-petrakis
Copy link
Author

Hello @klesh sure, the query causing this issue is this
The best solution would be to transform the field, but there is another workaround for Postgres which would be checking against the dialect and modifying the clause accordingly (though this looks a bit more ugly).

@klesh
Copy link
Contributor

klesh commented Dec 17, 2024

How about replacing the last %s in the SQL with cast(%s as varchar)?

Final product:

LEFT JOIN %s ON (%s.connection_id = bps.connection_id AND cast(%s as varchar) = bps.scope_id)

@kostas-petrakis
Copy link
Author

@klesh perfect! this does the trick, I will open a PR, meanwhile I found another small issue with the scope config, where the regex fails to save most likely due to the escaping (will open a followup).

@kostas-petrakis kostas-petrakis linked a pull request Dec 18, 2024 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
type/question This issue is a question
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

chore(scope-config): fix scope config query kostas-petrakis/incubator-devlake
2 participants
@klesh @kostas-petrakis