[koreader]statistics 插件数据库连接

前言

最近开始打算开发koreader统计插件转换到博客站点，该插件为koreader统计插件，可支持按天，按周，按月，按书籍，按月历统计。很好用！官方的代码如下：

plugins/statistics.koplugin/main.lua

local db_location = DataStorage:getSettingsDir() .. "/statistics.sqlite3"

建表语句

book

书籍表，及书籍表对应的唯一键约束，采用书籍标题，作者名，书籍md5作为唯一约束。

local sql_stmt = [[
        -- book
        CREATE TABLE IF NOT EXISTS book
            (
                id integer PRIMARY KEY autoincrement,
                title text,
                authors text,
                notes      integer,
                last_open  integer,
                highlights integer,
                pages      integer,
                series text,
                language text,
                md5 text,
                total_read_time  integer,
                total_read_pages integer
            );
    ]]
        conn:exec(sql_stmt)
    -- Index
    sql_stmt = [[
        CREATE UNIQUE INDEX IF NOT EXISTS book_title_authors_md5 ON book(title, authors, md5);
    ]]
    conn:exec(sql_stmt)

page_stat_data

该表是书籍页面统计信息，可以统计每页阅读的时间

-- Mainly so we don't duplicate the schema twice between the creation/upgrade codepaths
local STATISTICS_DB_PAGE_STAT_DATA_SCHEMA = [[
    CREATE TABLE IF NOT EXISTS page_stat_data
        (
            id_book     integer,
            page        integer NOT NULL DEFAULT 0,
            start_time  integer NOT NULL DEFAULT 0,
            duration    integer NOT NULL DEFAULT 0,
            total_pages integer NOT NULL DEFAULT 0,
            UNIQUE (id_book, page, start_time),
            FOREIGN KEY(id_book) REFERENCES book(id)
        );
]]

page_stat_data_start_time

local STATISTICS_DB_PAGE_STAT_DATA_INDEX = [[
    CREATE INDEX IF NOT EXISTS page_stat_data_start_time ON page_stat_data(start_time);
]]

视图 page_stat

最关键的一张视图，用于阅读统计的原子查询，看注释，该视图引用了外部表，用于细粒度的统计阅读时间，我猜测是因为书籍在阅读过程中会调整比如样式，字体等，会影响整体的页数，为了让页数调整后不至于影响统计数据，引入该变量，该变量会使用更细粒度的页阅读时间来统计。

local STATISTICS_DB_PAGE_STAT_VIEW_SCHEMA = [[
    -- Create the numbers table, used as a source of extra rows when scaling pages in the page_stat view
    CREATE TABLE IF NOT EXISTS numbers
        (
            number INTEGER PRIMARY KEY
        );
    WITH RECURSIVE counter AS
        (
            SELECT 1 as N UNION ALL
            SELECT N + 1 FROM counter WHERE N < 1000
        )
        INSERT INTO numbers SELECT N AS number FROM counter;
    -- Create the page_stat view
    -- This view rescales data from the page_stat_data table to the current number of book pages
    -- c.f., https://github.com/koreader/koreader/pull/6761#issuecomment-705660154
    CREATE VIEW IF NOT EXISTS page_stat AS
        SELECT id_book, first_page + idx - 1 AS page, start_time, duration / (last_page - first_page + 1) AS duration
        FROM (
            SELECT id_book, page, total_pages, pages, start_time, duration,
                -- First page_number for this page after rescaling single row
                ((page - 1) * pages) / total_pages + 1 AS first_page,
                -- Last page_number for this page after rescaling single row
                max(((page - 1) * pages) / total_pages + 1, (page * pages) / total_pages) AS last_page,
                idx
            FROM page_stat_data
            JOIN book ON book.id = id_book
            -- Duplicate rows for multiple pages as needed (as a result of rescaling)
            JOIN (SELECT number as idx FROM numbers) AS N ON idx <= (last_page - first_page + 1)
        );
]]

查询方法

getTotalStats

查询总阅读时间

function ReaderStatistics:getTotalStats()
    self:insertDB()
    local conn = SQ3.open(db_location)
    local sql_stmt = [[
        SELECT sum(duration)
        FROM   page_stat;
    ]]
    local total_books_time = conn:rowexec(sql_stmt)
    if total_books_time == nil then
        total_books_time = 0
    end
    local total_stats = {}
    sql_stmt = [[
        SELECT id
        FROM   book
        ORDER  BY last_open DESC;
    ]]
    local id_book_tbl = conn:exec(sql_stmt)
    local nr_books
    if id_book_tbl ~= nil then
        nr_books = #id_book_tbl.id
    else
        nr_books = 0
    end
    local user_duration_format = G_reader_settings:readSetting("duration_format")
    for i=1, nr_books do
        local id_book = tonumber(id_book_tbl[1][i])
        sql_stmt = [[
            SELECT title
            FROM   book
            WHERE  id = %d;
        ]]
        local book_title = conn:rowexec(string.format(sql_stmt, id_book))
        sql_stmt = [[
            SELECT sum(duration)
            FROM   page_stat
            WHERE  id_book = %d;
        ]]
        local total_time_book = conn:rowexec(string.format(sql_stmt,id_book))
        if total_time_book == nil then
            total_time_book = 0
        end
        table.insert(total_stats, {
            book_title,
            datetime.secondsToClockDuration(user_duration_format, total_time_book, false, true),
            callback = function()
                local kv = self.kv
                UIManager:close(self.kv)

                self.kv = KeyValuePage:new{
                    title = book_title,
                    kv_pairs = self:getBookStat(id_book),
                    value_align = "right",
                    single_page = true,
                    callback_return = function()
                        UIManager:show(kv)
                        self.kv = kv
                    end,
                    close_callback = function() self.kv = nil end,
                }
                UIManager:show(self.kv)
            end,
        })
    end
    conn:close()

    return T(_("Total time spent reading: %1"), datetime.secondsToClockDuration(user_duration_format, total_books_time, false, true)), total_stats
end

self:getDatesFromAll(0, "weekly", true),

按周统计书籍

self:getDatesFromAll(0, "monthly", true),

按月统计书籍

self:getDatesFromAll(7, "daily_weekday"),

最近一周

self:getDatesFromAll(30, "daily_weekday"),

最近一月（30天）按天统计

self:getDatesFromAll(365, "daily"),

最近一年（365）按天统计

self:getDatesFromAll(365, "weekly"),

最近一年（365）按周统计

self:getDatesFromAll(0, "monthly"),

按月统计所有书籍（可看到指定月份中，所有天数的阅读页数及时间）

getDatesFromAll

最关键的阅读记录数据

-- sdays -> number of days to show
-- ptype -> daily - show daily without weekday name
--          daily_weekday - show daily with weekday name
--          weekly - show weekly
--          monthly - show monthly
--          book_mode = if true than show book in this period
function ReaderStatistics:getDatesFromAll(sdays, ptype, book_mode)
    local results = {}
    local now_t = os.date("*t")
    local from_begin_day = now_t.hour *3600 + now_t.min*60 + now_t.sec
    local now_stamp = os.time()
    local one_day = 86400 -- one day in seconds
    local period_begin = 0
    local user_duration_format = G_reader_settings:readSetting("duration_format")
    if sdays > 0 then
        period_begin = now_stamp - ((sdays-1) * one_day) - from_begin_day
    end
    local sql_stmt_res_book
    if ptype == "daily" or ptype == "daily_weekday" then
        sql_stmt_res_book = sqlDaily()
    elseif ptype == "weekly" then
        sql_stmt_res_book = sqlWeekly()
    elseif ptype == "monthly" then
        sql_stmt_res_book = sqlMonthly()
    end
    self:insertDB()
    local conn = SQ3.open(db_location)
    local result_book = conn:exec(string.format(sql_stmt_res_book, period_begin))
    conn:close()

    if result_book == nil then
        return {}
    end
    for i=1, #result_book.dates do
        local timestamp = tonumber(result_book[4][i])
        local date_text
        if ptype == "daily_weekday" then
            date_text = string.format("%s (%s)",
                os.date("%Y-%m-%d", timestamp),
                datetime.shortDayOfWeekTranslation[os.date("%a", timestamp)])
        elseif ptype == "daily" then
            date_text = result_book[1][i]
        elseif ptype == "weekly" then
            date_text = T(_("%1 Week %2"), os.date("%Y", timestamp), os.date(" %W", timestamp))
        elseif ptype == "monthly" then
            date_text = datetime.longMonthTranslation[os.date("%B", timestamp)] .. os.date(" %Y", timestamp)
        else
            date_text = result_book[1][i]
        end
        if ptype == "monthly" then
            local year_begin = tonumber(os.date("%Y", timestamp))
            local year_end
            local month_begin = tonumber(os.date("%m", timestamp))
            local month_end
            if month_begin == 12 then
                year_end = year_begin + 1
                month_end = 1
            else
                year_end = year_begin
                month_end = month_begin + 1
            end
            local start_month = os.time{year=year_begin, month=month_begin, day=1, hour=0, min=0 }
            local stop_month = os.time{year=year_end, month=month_end, day=1, hour=0, min=0 }
            table.insert(results, {
                date_text,
                T(N_("%1 (%2 page)", "%1 (%2 pages)", tonumber(result_book[2][i])), datetime.secondsToClockDuration(user_duration_format, tonumber(result_book[3][i]), false, true), tonumber(result_book[2][i])),
                callback = function()
                    self:callbackMonthly(start_month, stop_month, date_text, book_mode)
                end,
            })
        elseif ptype == "weekly" then
            local time_book = os.date("%H%M%S%w", timestamp)
            local begin_week = tonumber(result_book[4][i]) - 3600 * tonumber(string.sub(time_book,1,2))
                - 60 * tonumber(string.sub(time_book,3,4)) - tonumber(string.sub(time_book,5,6))
            local weekday = tonumber(string.sub(time_book,7,8))
            if weekday == 0 then weekday = 6 else weekday = weekday - 1 end
            begin_week = begin_week - weekday * 86400
            table.insert(results, {
                date_text,
                T(N_("%1 (%2 page)", "%1 (%2 pages)", tonumber(result_book[2][i])), datetime.secondsToClockDuration(user_duration_format, tonumber(result_book[3][i]), false, true), tonumber(result_book[2][i])),
                callback = function()
                    self:callbackWeekly(begin_week, begin_week + 7 * 86400, date_text, book_mode)
                end,
            })
        else
            local time_book = os.date("%H%M%S", timestamp)
            local begin_day = tonumber(result_book[4][i]) - 3600 * tonumber(string.sub(time_book,1,2))
                - 60 * tonumber(string.sub(time_book,3,4)) - tonumber(string.sub(time_book,5,6))
            table.insert(results, {
                date_text,
                T(N_("%1 (%2 page)", "%1 (%2 pages)", tonumber(result_book[2][i])), datetime.secondsToClockDuration(user_duration_format, tonumber(result_book[3][i]), false, true), tonumber(result_book[2][i])),
                callback = function()
                    self:callbackDaily(begin_day, begin_day + 86400, date_text)
                end,
            })
        end
    end
    return results
end

本文来自：[koreader]statistics 插件数据库连接-小码农，转载请保留本条链接，感谢！