API: Allow setting the issue number when creating an issue

[argv-minus-one]

• Gitea version: 1.9.0
• Git version: 2.20.1
• Operating system: Debian Linux amd64
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant

Description

Please allow creating issues through the API (POST /api/v1/repos/{user}/{repository}/issues) with a specific issue number/index. This would allow importing issues from other issue trackers while preserving their issue numbers.

Workaround

This can also be accomplished by changing the database field directly, after the issue is created, using a query like:

UPDATE issue SET `index` = ? WHERE id = ?

…where the first parameter is the desired issue number, and the second parameter is the id field in the JSON response from creating the issue.

[lunny]

@argv-minus-one I think we should not do that. We have a migrations package https://github.com/go-gitea/gitea/tree/master/modules/migrations and we can migrate almost all data from github. Users can contribute this package and migrate via Gitea API.

[argv-minus-one]

I see.

I'll take a look, but I'm not sure if I have time to learn Go and Gitea's internals right now. In the mean time, I've posted issue #7802 for my specific use-case, which is importing from Bugzilla.

[argv-minus-one]

The migrations package seems designed for migrating Git repositories and associated data from GitHub-like sites, and would need a pretty major redesign to handle migrating anything else, such as issues from a separate issue tracker like Bugzilla.

[lunny]

@argv-minus-one I never used bugzilla and I want to know why do you want a special issue number?

[argv-minus-one]

I want to preserve the Bugzilla issue numbers when importing them into Gitea, because I have commit messages and emails that reference them by issue number (like “fixes #123” and “please look at bug 234”).

[guillep2k]

I want to preserve the Bugzilla issue numbers when importing them into Gitea, because I have commit messages and emails that reference them by issue number (like “fixes #123” and “please look at bug 234”).

This looks like a one-time procedure. I'd rather write a script to generate the UPDATE instructions while migrating and run it right after finishing the process. If you do this, remember to do it in strict descending order, otherwise you'll have ID number collisions. After that you need to update the corresponding row ID numbering sequence to at least max(id)+1 (in Postgres it's a sequence, in SQLite I don't know).

[lafriks]

I don't see how this could be implemented without breaking a lot of stuff imho

[jpicht]

I don't see how this could be implemented without breaking a lot of stuff imho

It could, by requiring that the override-issue-number be larger than every issue number already used for this repository. One should limit this ability to the repo owner, obviously.

[argv-minus-one]

[@guillep2k] After that you need to update the corresponding row ID numbering sequence to at least max(id)+1 (in Postgres it's a sequence, in SQLite I don't know).

I don't see any sequence for the index column (containing the issue number) in Postgres. When Gitea goes to create a new issue, it finds the next issue number using a SELECT MAX(`index`) query, rather than calling a sequence manipulation function. Neither Gitea nor the database seem to keep a stored “next issue number” anywhere. Please correct me if I'm wrong!

[@lafriks] I don't see how this could be implemented without breaking a lot of stuff imho

What would break?

[@jpicht] It could, by requiring that the override-issue-number be larger than every issue number already used for this repository.

That would be fine for me, but out of curiosity, why not allow any unused issue number to be used? The database schema already contains a UNIQUE INDEX named UQE_issue_repo_index to ensure that all issues have a unique issue number.

[guillep2k]

[@lafriks] I don't see how this could be implemented without breaking a lot of stuff imho

What would break?

If you see it from the point of view of your database, there might be no problems. But Gitea must support other database engines that won't play nicely with that kind of tinkering. For instance, Postgres uses sequences, while MS SQL Server and MySQL use auto-increment IDs. SQLite is used for light loads where a select max(id) will probably be OK; but as soon as you add many users concurrently accessing the database, that kind of strategy breaks rapidly (it's either long standing table locks or ID collision).

It's pretty buried in the docs, but SQLite is suggested only for tiny installations.

[argv-minus-one]

[@guillep2k] If you see it from the point of view of your database, there might be no problems. But Gitea must support other database engines that won't play nicely with that kind of tinkering. For instance, Postgres uses sequences, while MS SQL Server and MySQL use auto-increment IDs. SQLite is used for light loads where a select max(id) will probably be OK; but as soon as you add many users concurrently accessing the database, that kind of strategy breaks rapidly (it's either long standing table locks or ID collision).

I see. Well, Gitea contains a SELECT MAX query here, which is called from here when creating a new issue, to decide what the new issue number will be. It does not appear to care which database is in use. If this is a performance problem, then perhaps it should be reported?

Anyway, I threw together a Gitea instance on PostgreSQL, then tried creating an issue, increasing its issue number (from #1 to #2) with an UPDATE, then creating another issue. It creates the second issue without complaint and makes it #3, same as on SQLite. So, that doesn't seem to break.

Is there anything else that might break when I do this?

---

Note, by the way, that the issue table contains two auto-increment-like columns: id and index. id is globally unique, and is a normal auto-increment column. index is unique only for a single repository, and starts at 1 for each repository (that is, each repo's first issue is #1, its second issue is #2, and so on).

Normal database auto-increment can't be used for this. PostgreSQL sequences don't seem up to the task, either, unless Gitea were to CREATE SEQUENCE for every single repo on the site (that is, one CREATE SEQUENCE for each row in the repository table).

I only know of two ways to deal with this: something along the lines of SELECT MAX or SELECT … ORDER BY … LIMIT 1, or by querying and immediately updating a column in the repository table for the next issue number (which may not be any faster).

PostgreSQL seems to prefer SELECT … ORDER BY … LIMIT 1, by the way. EXPLAIN ANALYZE SELECT MAX(index) FROM issue WHERE repo_id = 1 yields this:

 Aggregate  (cost=8.16..8.17 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1)
   ->  Index Scan using "IDX_issue_repo_id" on issue  (cost=0.14..8.16 rows=1 width=8) (actual time=0.017..0.019 rows=2 loops=1)
         Index Cond: (repo_id = 1)
 Planning Time: 0.244 ms
 Execution Time: 0.061 ms

Whereas EXPLAIN ANALYZE SELECT index FROM issue WHERE repo_id = 1 ORDER BY index DESC LIMIT 1 yields:

 Limit  (cost=0.14..8.16 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1)
   ->  Index Only Scan Backward using "UQE_issue_repo_index" on issue  (cost=0.14..8.16 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)
         Index Cond: (repo_id = 1)
         Heap Fetches: 1
 Planning Time: 0.158 ms
 Execution Time: 0.041 ms

If I'm reading this right, PostgreSQL answers a SELECT MAX query on an indexed column by scanning the entire index, rather than just fetching the highest value from the index as with SELECT … ORDER BY … LIMIT 1. The actual execution time is slightly faster, but I have only one repo with two issues, so I have no idea how these queries perform on a real-world Gitea database.

[guillep2k]

@argv-minus-one You are exactly right, as I was getting confused with the ID property which is in fact auto-incremented, but the index is not, apparently. I'm sorry about that.

[jpicht]

That would be fine for me, but out of curiosity, why not allow any unused issue number to be used? The database schema already contains a UNIQUE INDEX named UQE_issue_repo_index to ensure that all issues have a unique issue number.

If somebody deleted some issues, for whatever reason, there might be commits referencing the issue. If somebody recycled that issue number, it could become very confusing fast.

So as your use case does not require for them to be inserted out-of-order, this could be a reasonable restriction.

Btw. temporarily creating issues with the desired index - 1 in the database while importing the issues from bugzilla would result in them being created with the same issue number. It's a dirty hack, but it would work.