[Bug][Jira] Saving regex pattern fails

[kostas-petrakis]

Search before asking

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

What happened

When using PostgreSQL and adding the Cross-domain pattern for Jira GORM fails to store the Regex field under PostgreSQL (works for MySQL).
The reason is the field type used (bytea) and the safe escaping (?) of the string when saving to PostgreSQL, the operation is successful if the user omits the Regex (tested by overriding the value).
If I use the following pattern:

https://github.com/{namespace}/{repo_name}/commit/{commit_sha}

The following error is produced:

ERROR: invalid input syntax for type bytea (SQLSTATE 22P02)\n\x1b[0m\x1b[33m[0.710ms] \x1b[34;1m[rows:0]\x1b[0m UPDATE \"_tool_jira_scope_configs\" SET \"created_at\"='2024-12-10 12:49:55.974',\"updated_at\"='2025-01-20 18:50:53.826',\"entities\"='[\"TICKET\",\"CROSS\"]',\"connection_id\"=1,\"name\"='team-test-jira-config',\"epic_key_field\"='',\"story_point_field\"='customfield_11252',\"remotelink_commit_sha_pattern\"='',\"remotelink_repo_pattern\"='[{\"pattern\":\"https://github.com/{namespace}/{repo_name}/commit/{commit_sha}\",\"regex\":\"https://github.com/(?P\\u003cnamespace\\u003e\\\\S+)/(?P\\u003crepo_name\\u003e\\\\S+)/commit/(?P\\u003ccommit_sha\\u003e\\\\w{40})\"}]',\"type_mappings\"='{\"Backend\":{\"standardType\":\"REQUIREMENT\",\"statusMappings\":null},\"Frontend\":{\"standardType\":\"REQUIREMENT\",\"statusMappings\":null},\"Story\":
{\"standardType\":\"REQUIREMENT\",\"statusMappings\":null},\"Subtask\":{\"standardType\":\"REQUIREMENT\",\"statusMappings\":null},\"Task\":{\"standardType\":\"REQUIREMENT\",\"statusMappings\":null}}',\"application_type\"='' WHERE \"id\" = 1"
time="2025-01-20 18:50:53" level=error msg="HTTP 500 error\n\tcaused by: ERROR: invalid input syntax for type bytea (SQLSTATE 22P02) (500)\n\tWraps: (2) ERROR: invalid input syntax for type bytea (SQLSTATE 22P02)\n\tError types: (1) *hintdetail.withDetail (2) *pgconn.PgError"

What do you expect to happen

The Regex is properly saved for both MySQL and PostgreSQL databases.

How to reproduce

Spin up Apache Devlake with PostgreSQL, add a Jira integration with Cross-Domain and add a pattern.

Anything else

Any suggestions how to solve this would be more than welcome, currently looking into a solution using BeforeUpdate GORM hook, in order to see if the regex pattern can be actually properly transformed for PostgreSQL.

Version

31dacd8

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

[dosubot]

The issue you're encountering seems to be related to the handling of the Regex field in PostgreSQL, specifically with the bytea type. This type is used for storing binary data, and it appears that the string is not being properly escaped or encoded for PostgreSQL, leading to the error.

To address this, you might consider using a BeforeUpdate GORM hook to transform the regex pattern before saving it to the database. This hook can be used to ensure that the data is properly formatted for PostgreSQL. Here's a basic example of how you might implement such a hook:

func (config *JiraScopeConfig) BeforeUpdate(tx *gorm.DB) (err error) {
    // Transform the Regex field to ensure it's properly formatted for PostgreSQL
    config.Regex = transformRegexForPostgreSQL(config.Regex)
    return nil
}

func transformRegexForPostgreSQL(regex string) string {
    // Implement your transformation logic here
    // For example, you might escape certain characters or encode the string
    return regex
}

This is a simplified example, and you'll need to implement the actual transformation logic based on your specific requirements. Additionally, ensure that the Regex field is stored as a string type in the database schema, rather than bytea, if it is intended to store text data.

If you have existing migration scripts, you might also want to check if the Regex field is defined with the correct data type for PostgreSQL. Adjusting the schema might be necessary if the field is currently set to bytea and should be a text type instead.

If you decide to proceed with a pull request, make sure to test the changes thoroughly to ensure compatibility with both MySQL and PostgreSQL.

To continue talking to Dosu, mention @dosu.

---

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