[Question] Why the database does not have unique constraints?

Cologler commented 7 months ago

Steps to reproduce

I have no idea where they come from. It might be because I'm refreshing the database while simultaneously importing data.

Expected behavior

The database is guarded by unique constraints.

Actual behavior

A lot of duplicated chapters:

Crash logs

No response

Mihon version

latest

Android version

12

Device

phone

Other details

Why the database does not have unique constraints like:

(manga_id, url) on chapters table
(source, url) on mangas table
(sort) on categories table
And more

Some related issues:

27
120
185

Acknowledgements

[X] I have searched the existing issues and this is a new ticket, NOT a duplicate or related to another open or closed issue.
[X] I have written a short but informative title.
[X] I have gone through the FAQ and troubleshooting guide.
[X] I have updated the app to version 0.16.5.
[X] I have updated all installed extensions.
[X] I will fill out all of the requested information in this form.

Cologler commented 7 months ago

When two elements have the same key, if we query one from the database, it would be the first one; if we query all from the database and then map by associateBy(), it would be the last one.

This inconsistency leads to numerous issues.

Cologler commented 7 months ago

Even though the chapter_id of history has unique constraints, if the (manga_id, url) of chapter lacks unique constraints, then this is not unique:

https://github.com/mihonapp/mihon/blob/c94d212ef471012990bfe02ba43044de476ab9cd/app/src/main/java/eu/kanade/tachiyomi/data/backup/restore/restorers/MangaRestorer.kt#L323C13-L323C106

This issue can lead to restoration failure with:

// <-- this is in my tachiyomi_restore.txt
ResultSet returned more than 1 row for history.sq:getHistoryByChapterUrl

By the way, since this is a relative URL, it might be duplicated across different sources. TachiyomiSY has already tried to fix this issue by use mangaId:

mihonapp / mihon