[de3sw2aq1]

I used to use KoboUtilities and KoboTouchExtended which let me get the current reading progress (% completion, finished) of each book into Calibre. I wanted to find a way to get similar info out of Plato.

I realized the Plato reading states and metadata are just JSON, so I'd normally just use jq. But the weird base10 vs base16 encoding of the id gave me trouble, and the reading states are in a separate file per book. I realized SQLite is powerful enough for this kind of query and works too though.

Maybe this is useful to someone else, thought I'd share it.

SELECT
    json_extract(metadata, "$.title") as title,
    json_extract(metadata, "$.author") as author,
    json_extract(metadata, "$.file.path") as path,
    json_extract(readingstate, "$.finished") as finished,
    json_extract(readingstate, "$.currentPage") as currentPage,
    json_extract(readingstate, "$.pagesCount") as pagesCount,
    CAST(
        json_extract(readingstate, "$.currentPage") AS FLOAT
    ) / json_extract(readingstate, "$.pagesCount") as progress
FROM (
        SELECT
            json(readfile(printf(".reading-states/%X.json", key))) as readingstate,
            value as metadata
        FROM json_each(readfile(".metadata.json"))
    )
WHERE
    readingstate IS NOT NULL
    AND finished = true; -- edit or remove this part to filter the query