Hi! I'm writing a script which among other thing extracts metadata in custom columns directly from metadata.db. I know there are tools like calibredb that can do this, but I'm rolling my own as a practical excercise in interaction with SQLite from scripts.
I noticed something curious: whenever the value in a single-value custom column is changed (I observed this with integer and date columns), a new row is inserted in addition to the old value, which is not removed. Then you end up with multiple rows for the same book record instead of just one, with only db metadata (rowid) to specify the order of these. When I extract them I have to do an additional subquery for max(rowid) to get the current value. The id column in the custom_column_x table is always null in these cases.
calibre obviously handles this in some manner (I haven't been able to find the relevant code in source), but to me it seems a bit messy designwise to have multiple rows and rely (explicitly or implicitly) on db metadata features like rowid. It might also be a potential source for non-obvious bugs down the road. Maybe it is something which is left after support for multiple values with _link tables were added?
Based on the maturity of calibre I have a suspicion that this is intended behaviour for some reason I haven't been able to discern, in that case I'd be happy to know why
