[Question][Blueprint] ScopeId field type

[kostas-petrakis]

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?

[dosubot]

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}

[klesh]

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]

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]

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]

@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).