Custom rating column in the database

[ownedbycats]

In the Calibre database, a custom rating column with half-stars stores its numbers in a strange way. I have books with all 10 ratings, and matching up book ids in the database shows:

1 - unused?
2 - unused?
3 - 3 stars
4 - 4 stars
5 - 2 stars
6 - 1 star
7 - 5 stars
8 - 1.5 stars
9 - 3.5 stars
10 - 2.5 stars
11 - unused?
12 - 0.5 stars
13 - 4.5 stars

Why are 1, 2, and 11 unused?

[chaley]

Almost certainly you got the wrong column. It is (in theory) impossible to put a value in a Rating column outside the range 0 .. 10. Furthermore, the value 2 will show 1 star and any value over 10 will show 5 stars.

The code that translates the number into stars is

def rating_to_stars(value, allow_half_stars=False, star='★', half='⯨'):
    r = max(0, min(int(value or 0), 10)) # value is limited to 0 .. 10
    ans = star * (r // 2) # generate (r int_divide by 2) stars.
    if allow_half_stars and r % 2: # if half stars and r mod 2 != 0, add it
        ans += half
    return ans

[ownedbycats]

that's odd -- looking at custom_columns showed that #ratings had an id of 40, so I was looking at custom_column_40:



And all the book IDs and stuff were in books_custom_column_40_link.

[chaley]

Originally Posted by ownedbycats

that's odd -- looking at custom_columns showed that #ratings had an id of 40, so I was looking at custom_column_40:

Attachment 192809

And all the book IDs and stuff were in books_custom_column_40_link.

I don't know what you are showing me. It looks like rating values containing the correct values.

The values in the _link table are row IDs, not ratings. ID 3 has the value of 6, or 3 stars.

[ownedbycats]

custom_columns:



custom_column_40 is what I showed above

books_custom_column_40_link:



from what it looks like, book 6337 has a value of 3, which is assigned to 6 (which displays as 3 stars).

Not sure why ids 1, 2, and 11 are not present in custom_column_40, or why the values are in a seemingly random order (I can see why the even whole-stars would appear above the odd half-stars, but other than that is confusing).

[chaley]

The _link table is a standard many-to-one SQL link table. It matches book_ids to the data table ID. Nothing in this table should be interpreted as a rating. The data table contains the actual rating value, referenced by the data table ID which in this case is a row number.

The order of rows in a _link table depends on the order that values were created and deleted. For example, it should be that if you remove rating X from all books then all those books will disappear from the _link table and the line containing X will disappear from the data table. If you later add X to some book then a line will appear at the end of the _link table and X will appear at the end of the data table with a new row number.

[ownedbycats]

ah, I think I see what happened -- I think I added half-star (odd) ratings to a few books, but then I copied everything over from the other column (which only has evens) and overwrote them.