[Bug] Pages in book are ordered using the filename in alphabetical order, not in natural order

[PichetGoulu]

Komga environment

• OS: Debian GNU/Linux 10 (buster)
• Komga version: 0.35.1
• I am running Komga from the jar
  • Java version: openjdk 11.0.7 2020-04-14 (build 11.0.7+10-post-Debian-3deb10u1)
• I have a problem with the API

Describe the bug

The order of the pages in the endpoint /api/v1/books/{bookId}/pages is incorrect.
Pages in book are ordered using the filename in alphabetical order, not in natural order.

The issue also appears in the WebUI (which also use the /api/v1/books/{bookId}/pages endpoint)

Steps to reproduce

1. Add a zip archive with filenames that are not zero padded

Archive:  003_ch3.0.cbz
  Length      Date    Time    Name
---------  ---------- -----   ----
   377589  2020-05-25 09:02   0.jpeg
  1323445  2020-05-25 09:02   1.jpeg
   595335  2020-05-25 09:02   2.jpeg
  1340328  2020-05-25 09:02   3.jpeg
  1362993  2020-05-25 09:02   4.jpeg
   722877  2020-05-25 09:02   5.jpeg
  1032540  2020-05-25 09:02   6.jpeg
  1143318  2020-05-25 09:02   7.jpeg
  1376726  2020-05-25 09:02   8.jpeg
  1332592  2020-05-25 09:02   9.jpeg
  1443195  2020-05-25 09:02   10.jpeg
  1636716  2020-05-25 09:02   11.jpeg
  1039277  2020-05-25 09:02   12.jpeg
  1142259  2020-05-25 09:02   13.jpeg
  1122575  2020-05-25 09:02   14.jpeg
   973028  2020-05-25 09:02   15.jpeg
  1166706  2020-05-25 09:02   16.jpeg
  1038615  2020-05-25 09:02   17.jpeg
  1148002  2020-05-25 09:02   18.jpeg
  1188400  2020-05-25 09:02   19.jpeg
  1002020  2020-05-25 09:02   20.jpeg
   838397  2020-05-25 09:02   21.jpeg
  1363242  2020-05-25 09:02   22.jpeg
  1456363  2020-05-25 09:02   23.jpeg
  1114856  2020-05-25 09:02   24.jpeg
  1260176  2020-05-25 09:02   25.jpeg
  1489581  2020-05-25 09:02   26.jpeg
   299615  2020-05-25 09:02   27.jpeg
---------                     -------
 31330766                     28 files

2. Query the /api/v1/books/{bookId}/pages endpoint of the newly added book

Expected behavior

[
    {"number":1,"fileName":"0.jpeg","mediaType":"image/jpeg"},
    {"number":2,"fileName":"1.jpeg","mediaType":"image/jpeg"},
    {"number":3,"fileName":"2.jpeg","mediaType":"image/jpeg"},
    {"number":4,"fileName":"3.jpeg","mediaType":"image/jpeg"},
    {"number":5,"fileName":"4.jpeg","mediaType":"image/jpeg"},
    {"number":6,"fileName":"5.jpeg","mediaType":"image/jpeg"},
    {"number":7,"fileName":"6.jpeg","mediaType":"image/jpeg"},
    {"number":8,"fileName":"7.jpeg","mediaType":"image/jpeg"},
    {"number":9,"fileName":"8.jpeg","mediaType":"image/jpeg"},
    {"number":10,"fileName":"9.jpeg","mediaType":"image/jpeg"},
    {"number":11,"fileName":"10.jpeg","mediaType":"image/jpeg"},
    {"number":12,"fileName":"11.jpeg","mediaType":"image/jpeg"},
    {"number":13,"fileName":"12.jpeg","mediaType":"image/jpeg"},
    {"number":14,"fileName":"13.jpeg","mediaType":"image/jpeg"},
    {"number":15,"fileName":"14.jpeg","mediaType":"image/jpeg"},
    {"number":16,"fileName":"15.jpeg","mediaType":"image/jpeg"},
    {"number":17,"fileName":"16.jpeg","mediaType":"image/jpeg"},
    {"number":18,"fileName":"17.jpeg","mediaType":"image/jpeg"},
    {"number":19,"fileName":"18.jpeg","mediaType":"image/jpeg"},
    {"number":20,"fileName":"19.jpeg","mediaType":"image/jpeg"},
    {"number":21,"fileName":"20.jpeg","mediaType":"image/jpeg"},
    {"number":22,"fileName":"21.jpeg","mediaType":"image/jpeg"},
    {"number":23,"fileName":"22.jpeg","mediaType":"image/jpeg"},
    {"number":24,"fileName":"23.jpeg","mediaType":"image/jpeg"},
    {"number":25,"fileName":"24.jpeg","mediaType":"image/jpeg"},
    {"number":26,"fileName":"25.jpeg","mediaType":"image/jpeg"},
    {"number":27,"fileName":"26.jpeg","mediaType":"image/jpeg"},
    {"number":28,"fileName":"27.jpeg","mediaType":"image/jpeg"}
]

Actual behavior

[
    {"number":1,"fileName":"0.jpeg","mediaType":"image/jpeg"},
    {"number":2,"fileName":"1.jpeg","mediaType":"image/jpeg"},
    {"number":3,"fileName":"10.jpeg","mediaType":"image/jpeg"},
    {"number":4,"fileName":"11.jpeg","mediaType":"image/jpeg"},
    {"number":5,"fileName":"12.jpeg","mediaType":"image/jpeg"},
    {"number":6,"fileName":"13.jpeg","mediaType":"image/jpeg"},
    {"number":7,"fileName":"14.jpeg","mediaType":"image/jpeg"},
    {"number":8,"fileName":"15.jpeg","mediaType":"image/jpeg"},
    {"number":9,"fileName":"16.jpeg","mediaType":"image/jpeg"},
    {"number":10,"fileName":"17.jpeg","mediaType":"image/jpeg"},
    {"number":11,"fileName":"18.jpeg","mediaType":"image/jpeg"},
    {"number":12,"fileName":"19.jpeg","mediaType":"image/jpeg"},
    {"number":13,"fileName":"2.jpeg","mediaType":"image/jpeg"},
    {"number":14,"fileName":"20.jpeg","mediaType":"image/jpeg"},
    {"number":15,"fileName":"21.jpeg","mediaType":"image/jpeg"},
    {"number":16,"fileName":"22.jpeg","mediaType":"image/jpeg"},
    {"number":17,"fileName":"23.jpeg","mediaType":"image/jpeg"},
    {"number":18,"fileName":"24.jpeg","mediaType":"image/jpeg"},
    {"number":19,"fileName":"25.jpeg","mediaType":"image/jpeg"},
    {"number":20,"fileName":"26.jpeg","mediaType":"image/jpeg"},
    {"number":21,"fileName":"27.jpeg","mediaType":"image/jpeg"},
    {"number":22,"fileName":"3.jpeg","mediaType":"image/jpeg"},
    {"number":23,"fileName":"4.jpeg","mediaType":"image/jpeg"},
    {"number":24,"fileName":"5.jpeg","mediaType":"image/jpeg"},
    {"number":25,"fileName":"6.jpeg","mediaType":"image/jpeg"},
    {"number":26,"fileName":"7.jpeg","mediaType":"image/jpeg"},
    {"number":27,"fileName":"8.jpeg","mediaType":"image/jpeg"},
    {"number":28,"fileName":"9.jpeg","mediaType":"image/jpeg"}
]

Additional context

The issue appears after upgrading from 0.24.10 to 0.35.1.
I don't know if it's due to the database upgrade, but using the /api/v1/books/{bookId}/analyze endpoint to force a rescan of the book did not solve the problem.

2020-06-07 16:26:32.231  INFO 10645 --- [http-nio-8080-exec-8] o.g.k.application.tasks.TaskReceiver     : Sending task: AnalyzeBook(bookId=19768)
2020-06-07 16:26:32.239  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.application.tasks.TaskHandler  : Executing task: AnalyzeBook(bookId=19768)
2020-06-07 16:26:32.240  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.domain.service.BookLifecycle   : Analyze and persist book: Book(name=003_ch3.0, url=file:/<path>/003_ch3.0.cbz, fileLastModified=2020-05-25T09:02:36.553099, fileSize=31333288, number=3, id=19768, seriesId=16429, libraryId=3, createdDate=2020-05-25T09:03:01.779817, lastModifiedDate=2020-06-06T11:20:12.806437)
2020-06-07 16:26:32.240  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.domain.service.BookAnalyzer    : Trying to analyze book: Book(name=003_ch3.0, url=file:/<path>/003_ch3.0.cbz, fileLastModified=2020-05-25T09:02:36.553099, fileSize=31333288, number=3, id=19768, seriesId=16429, libraryId=3, createdDate=2020-05-25T09:03:01.779817, lastModifiedDate=2020-06-06T11:20:12.806437)
2020-06-07 16:26:32.257  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.domain.service.BookAnalyzer    : Detected media type: application/zip
2020-06-07 16:26:32.561  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.domain.service.BookAnalyzer    : Book has 28 pages
2020-06-07 16:26:32.562  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.domain.service.BookAnalyzer    : Trying to generate cover for book: Book(name=003_ch3.0, url=file:/<path>/003_ch3.0.cbz, fileLastModified=2020-05-25T09:02:36.553099, fileSize=31333288, number=3, id=19768, seriesId=16429, libraryId=3, createdDate=2020-05-25T09:03:01.779817, lastModifiedDate=2020-06-06T11:20:12.806437)
2020-06-07 16:26:32.744  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.k.application.tasks.TaskReceiver     : Sending task: RefreshBookMetadata(bookId=19768)
2020-06-07 16:26:32.746  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.application.tasks.TaskHandler  : Task AnalyzeBook(bookId=19768) executed in 506ms
2020-06-07 16:26:32.751  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.application.tasks.TaskHandler  : Executing task: RefreshBookMetadata(bookId=19768)
2020-06-07 16:26:32.752  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.k.domain.service.MetadataLifecycle   : Refresh metadata for book: Book(name=003_ch3.0, url=file:/<path>/003_ch3.0.cbz, fileLastModified=2020-05-25T09:02:36.553099, fileSize=31333288, number=3, id=19768, seriesId=16429, libraryId=3, createdDate=2020-05-25T09:03:01.779817, lastModifiedDate=2020-06-06T11:20:12.806437)
2020-06-07 16:26:32.755  INFO 10645 --- [DefaultMessageListenerContainer-1] o.g.komga.application.tasks.TaskHandler  : Task RefreshBookMetadata(bookId=19768) executed in 3.73ms

[gotson]

Thanks for noticing this one. Indeed the move from Hibernate to jOOQ had this side effect. Once you have the version with the fix you can Analyze your books again to fix the problem.

[gotson]

🎉 This issue has been resolved in version 0.35.2 🎉

The release is available on:

• v0.35.2
• GitHub release

Your semantic-release bot 📦🚀

[PichetGoulu]

Hello, thanks for the quick fix !

Sadly, after upgrading to 0.36.0, re-analyzing the library (and the book) and restarting Komga, the order in the API reply of /api/v1/books/{bookId}/pages is still wrong.

In the database, the field NUMBER in the MEDIA_PAGE table that was NULL in 0.35.1 is now properly filled in the natural order.
But it seems that this number is not picked up to fill the API reply.

Also, the image retreived with /api/v1/books/{bookId}/pages/{pageNumber} match the (wrong) reply of /api/v1/books/{bookId}/pages, not the one referenced in the database.

SELECT * FROM MEDIA_PAGE WHERE BOOK_ID = 19768

FILE_NAME|MEDIA_TYPE|NUMBER|BOOK_ID|
---------|----------|------|-------|
0.jpeg   |image/jpeg|     0|  19768|
1.jpeg   |image/jpeg|     1|  19768|
2.jpeg   |image/jpeg|     2|  19768|
3.jpeg   |image/jpeg|     3|  19768|
4.jpeg   |image/jpeg|     4|  19768|
5.jpeg   |image/jpeg|     5|  19768|
6.jpeg   |image/jpeg|     6|  19768|
7.jpeg   |image/jpeg|     7|  19768|
8.jpeg   |image/jpeg|     8|  19768|
9.jpeg   |image/jpeg|     9|  19768|
10.jpeg  |image/jpeg|    10|  19768|
11.jpeg  |image/jpeg|    11|  19768|
12.jpeg  |image/jpeg|    12|  19768|
13.jpeg  |image/jpeg|    13|  19768|
14.jpeg  |image/jpeg|    14|  19768|
15.jpeg  |image/jpeg|    15|  19768|
16.jpeg  |image/jpeg|    16|  19768|
17.jpeg  |image/jpeg|    17|  19768|
18.jpeg  |image/jpeg|    18|  19768|
19.jpeg  |image/jpeg|    19|  19768|
20.jpeg  |image/jpeg|    20|  19768|
21.jpeg  |image/jpeg|    21|  19768|
22.jpeg  |image/jpeg|    22|  19768|
23.jpeg  |image/jpeg|    23|  19768|
24.jpeg  |image/jpeg|    24|  19768|
25.jpeg  |image/jpeg|    25|  19768|
26.jpeg  |image/jpeg|    26|  19768|
27.jpeg  |image/jpeg|    27|  19768|

/api/v1/books/19768/pages

[
    {"number":1,"fileName":"0.jpeg","mediaType":"image/jpeg"},
    {"number":2,"fileName":"1.jpeg","mediaType":"image/jpeg"},
    {"number":3,"fileName":"10.jpeg","mediaType":"image/jpeg"},
    {"number":4,"fileName":"11.jpeg","mediaType":"image/jpeg"},
    {"number":5,"fileName":"12.jpeg","mediaType":"image/jpeg"},
    {"number":6,"fileName":"13.jpeg","mediaType":"image/jpeg"},
    {"number":7,"fileName":"14.jpeg","mediaType":"image/jpeg"},
    {"number":8,"fileName":"15.jpeg","mediaType":"image/jpeg"},
    {"number":9,"fileName":"16.jpeg","mediaType":"image/jpeg"},
    {"number":10,"fileName":"17.jpeg","mediaType":"image/jpeg"},
    {"number":11,"fileName":"18.jpeg","mediaType":"image/jpeg"},
    {"number":12,"fileName":"19.jpeg","mediaType":"image/jpeg"},
    {"number":13,"fileName":"2.jpeg","mediaType":"image/jpeg"},
    {"number":14,"fileName":"20.jpeg","mediaType":"image/jpeg"},
    {"number":15,"fileName":"21.jpeg","mediaType":"image/jpeg"},
    {"number":16,"fileName":"22.jpeg","mediaType":"image/jpeg"},
    {"number":17,"fileName":"23.jpeg","mediaType":"image/jpeg"},
    {"number":18,"fileName":"24.jpeg","mediaType":"image/jpeg"},
    {"number":19,"fileName":"25.jpeg","mediaType":"image/jpeg"},
    {"number":20,"fileName":"26.jpeg","mediaType":"image/jpeg"},
    {"number":21,"fileName":"27.jpeg","mediaType":"image/jpeg"},
    {"number":22,"fileName":"3.jpeg","mediaType":"image/jpeg"},
    {"number":23,"fileName":"4.jpeg","mediaType":"image/jpeg"},
    {"number":24,"fileName":"5.jpeg","mediaType":"image/jpeg"},
    {"number":25,"fileName":"6.jpeg","mediaType":"image/jpeg"},
    {"number":26,"fileName":"7.jpeg","mediaType":"image/jpeg"},
    {"number":27,"fileName":"8.jpeg","mediaType":"image/jpeg"},
    {"number":28,"fileName":"9.jpeg","mediaType":"image/jpeg"}
]

[gotson]

i should have tested end to end, if the saving was incorrect, of course the loading would be too. I'll fix that.

[gotson]

🎉 This issue has been resolved in version 0.37.0 🎉

The release is available on:

• v0.37.0
• GitHub release

Your semantic-release bot 📦🚀

[PichetGoulu]

Works fine on 0.37.0, thanks for the fix !