"Alphabetical" sorting is not alphabetical - Sorted by ASCII code instead

[anobit]

• Gitea version (or commit ref): 1.15.0+dev-171-gf719ffc78
• Git version: Not relevant
• Operating system:
  • Not relevant
• Database (use [x]):
  • sqlite3
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (https://try.gitea.io/AnoBit?sort=alphabetically)
  • No
• Log gist:
  • Not relevant

Description

The alphabetical sorting is currently not alphabetical, as lower- and uppercase letters are "sorted" seperately. This is likely due to ASCII Code comparison (e.g. "B = 66", "a" = 97).
In the linked example i made three repositories to showcase this: Beta and Omega, starting both with uppercase letters and alpha, starting with a lowercase. With an alphabetical sorting it would be expected to be sorted as "alpha", "Beta", "Omega". Instead, "alpha" is pushed to the back.

Among other things, this can be an error source for people migrating old, inconsistently named VCS to Gitea.

Screenshots

[lafriks]

What database are you using?

[anobit]

On the system i migrate to sqlite3. Which one the try.gitea.io instance uses, i do not know.

[lafriks]

Yes, this is how sqlite does sorting, so it is only sqlite speciffic problem. To have case insensitive sorting sqlite needs COLLATE NOCASE ASC but I don't know if xorm supports that
cc @lunny

[lunny]

Just use OrderBy("name COLLATE NOCASE ASC") if it's sqlite

[KaKi87]

Hello,

I don't know about repo names, but the same issue is present for repo file names.
Personally I use MySQL.

Thanks

[wxiaoguang]

I can see 2 problems here.

The first one is for "database collation". I think SQLite is too special for this case, and it doesn't seem worth to make XORM more complex to support the special sort order. It's highly recommended to use some rich-featured databases like MySQL/PgSQL, then you could convert the table/column collation to what you need.

The second one, I think it could be fixed by #29611

(I think this issue could be closed after #29611 gets merged)