sqlite数据库操作和分页

meinvwudi

local sqlite3_util = {}


local env = sqlite3.sqlite3()
local toutf8 = iconv.new("UTF-8", "GBK")
local togbk = iconv.new("GBK", "UTF-8")

function sqlite3_util.gbk2utf8(str)
    local s, err = toutf8:iconv(str)
    if err then
        sqlite3_util.send_error_to_tj_user(actor, err)
        return str
    end
    return s
end


function sqlite3_util.utf82gbk(str)
    local s, err = togbk:iconv(str)
    if err then
        sqlite3_util.send_error_to_tj_user(actor, err)
        return str
    end
    return s
end


-- 打开数据库连接
function sqlite3_util.open(db_name)

    return env:connect(db_name)
end

-- 关闭数据库连接
function sqlite3_util.close()
    return env:close()
end

-- 错误处理函数，只对tj_user类型用户发送错误信息
function sqlite3_util.send_error_to_tj_user(actor, err)
    if tj_user[getconst(actor, "<$USERCOUNT>")] then
        sendmsg6(actor, err)
    end
end

function sqlite3_util.insert(actor, db, table_name, data) -- 传入k,v insert 数据

    local columns = {}
    local values = {}

    -- 构建列名和值列表，注意：这里直接在SQL中插入值，增加了SQL注入风险
    local sql_values = {}
    for k, v in pairs(data) do
        table.insert(columns, k)
        table.insert(values, v)
        -- 这里应当对v进行适当的转义，以避免SQL注入
        sql_values[#sql_values + 1] = "'" .. db:escape(v) .. "'"
    end

    -- 构建插入的SQL语句，直接使用值列表
    local sql_insert = string.format(
        "INSERT INTO %s (%s) VALUES (%s)",
        table_name,
        table.concat(columns, ', '),
        table.concat(sql_values, ', ')
    )

    sql_insert = sqlite3_util.gbk2utf8(sql_insert)

    -- 执行SQL语句
    local res, err = db:execute(sql_insert)

    if err then
        sendmsg6(actor, tostring(err))
        sqlite3_util.send_error_to_tj_user(actor, err)
        return nil, err
    end
    return res, nil
end


-- 查询数据
function sqlite3_util.select(actor, db, table_name, columns, where_clause)
    local select_sql = string.format("SELECT %s FROM %s %s",
        columns or '*', 
        table_name,
        where_clause or '')
    
    select_sql = sqlite3_util.gbk2utf8(select_sql) 
    local res, err = db:execute(select_sql)
    
    if err then
        sqlite3_util.send_error_to_tj_user(actor, err)
        return nil, err
    end

    local results = {}
    local row
    
    while true do
        local row = {res:fetch()}
        if #row == 0 then break end
        for k, v in ipairs(row) do
            row[k] = sqlite3_util.utf82gbk(v)
        end
        table.insert(results, row)
    end
    return results, nil
end

-- 更新数据
function sqlite3_util.update(actor, db, table_name, data, where_clause)

    local set_clause = ''
    
    -- 构建 SET 子句
    for k, v in pairs(data) do
        set_clause = set_clause .. k .. ' = "' .. v .. '", '
    end
    set_clause = set_clause:sub(1, -3) -- 去除最后一个逗号和空格

    -- 构建完整的 SQL 语句
    local sql_update = string.format("UPDATE %s SET %s %s",
        table_name,
        set_clause,
        where_clause or '')

    -- 执行SQL语句
    sql_update = sqlite3_util.gbk2utf8(sql_update)
    local res, err = db:execute(sql_update)

    if err then
        sqlite3_util.send_error_to_tj_user(actor, err)
        return nil, err
    end

    return res, nil

end

-- 删除数据
function sqlite3_util.delete(actor, db, table_name, where_clause)
    local sql = string.format("DELETE FROM %s %s",
        table_name,
        where_clause or '')
    
    sql = sqlite3_util.gbk2utf8(sql)
    local res, err = db:execute(sql)
    
    if err then
        sqlite3_util.send_error_to_tj_user(actor, err)
        return nil, err
    end
    
    return res, nil
end

return sqlite3_util

meinvwudi

数据库的分页

require("Envir/Market_Def/NpcLua/zh.lua") --个人封装函数

local toutf8 = iconv.new("UTF-8", "GBK")

local function GBK2UTF8(str)
    local s, err = toutf8:iconv(str)
    if err then
        return str
    end
    return s
end
local togbk = iconv.new("GBK", "UTF-8")

local function UTF82GBK(str)
    local s, err = togbk:iconv(str)
    if err then
        return str
    end
    return s
end


-- 函数用于获取分页数据
function get_paginated_data(actor, conn, table_name, current_page, rows_per_page, order)
    -- 计算开始的行号
    local start_row = (current_page - 1) * rows_per_page

    -- 准备并执行 SQL 查询
    local query = string.format("SELECT * FROM %s ", table_name)
    if order then 
        query = query .. order
    end
    
    query  = query .. " LIMIT " .. rows_per_page .. " OFFSET " .. start_row
    query = GBK2UTF8(query)

    local res, err = conn:execute(query)

    -- 检查是否有错误
    if not res then
        -- sendmsg6(actor, "Error executing query: " .. err)
        return nil
    end

    -- 获取查询结果
    local page_data = {}
    while true do
        local row = res:fetch({mode="table"})
        if not row then break end
        for i=1,#row do
            row[i] = UTF82GBK(row[i])
        end
        
        table.insert(page_data, row)
    end

    -- for k, v in pairs(page_data) do 
    --     sendmsg6(actor, k .. "=" .. tbl2json(v))
    -- end

    -- 关闭查询结果
    res:close()

    -- 获取总行数
    local total_rows_query = string.format("SELECT COUNT(*) FROM %s", table_name)
    local total_rows_res, err = conn:execute(total_rows_query)
    if not total_rows_res then
        -- print("Error executing count query: " .. err)
        return nil
    end

    -- 读取总行数
    local total_rows = total_rows_res:fetch({mode="number"})[1]
    total_rows_res:close()

    -- 计算总页数
    local total_pages = math.ceil(total_rows / rows_per_page)

    -- 创建页面链接列表
    local page_links = {}
    for i = 1, math.min(11, total_pages) do
        table.insert(page_links, i)
    end

    -- 添加上一页和下一页的链接
    if current_page > 1 then
        table.insert(page_links, 1, {label = '上一页', page = current_page - 1})
    end
    if current_page < total_pages then
        table.insert(page_links, #page_links + 1, {label = '下一页', page = current_page + 1})
    end

    return page_data, page_links, total_pages
end

meinvwudi

require(“Envir/Market_Def/NpcLua/zh.lua”) –个人封装函数

– 分页函数
function paginate_data(actor, data_table, current_page, items_per_page)
– 计算总页数
local total_pages = math.ceil(#data_table / items_per_page)

-- 检查当前页数是否超出范围
if current_page < 1 or current_page > total_pages then
    current_page = 1 -- 默认显示第一页
end

-- 计算当前页数据的起始和结束索引
local start_index = (current_page - 1) * items_per_page + 1
local end_index = math.min(start_index + items_per_page - 1, #data_table)

-- 获取当前页的数据
local page_data = {}
for i = start_index, end_index do
    table.insert(page_data, data_table[i])
end

-- 创建分页链接列表
local page_links = {}
-- 上一页链接
if current_page > 1 then
    table.insert(page_links, 1, {label = '上一页', page = current_page - 1})
end
-- 显示10页链接，中间省略号处理
for i = 1, math.min(10, total_pages) do
    if i == current_page then
        table.insert(page_links, i)
    else
        table.insert(page_links, {label = i, page = i})
    end
end
-- 如果总页数大于10页，且当前页不在前10页，显示省略号
if total_pages > 10 and current_page > 5 then
    table.insert(page_links, {label = '...', page = nil})
end
-- 如果总页数大于10页，且当前页接近尾部，显示最后几页
if total_pages > 10 and current_page >= total_pages - 4 then
    for i = total_pages - 4, total_pages do
        if i == current_page then
            table.insert(page_links, i)
        else
            table.insert(page_links, {label = i, page = i})
        end
    end
end
-- 下一页链接
if current_page < total_pages then
    table.insert(page_links, {label = '下一页', page = current_page + 1})
end

return page_data, page_links, total_pages

end

– 示例使用
– local data_table = {
– {“John”, “Doe”, 30},
– {“Jane”, “Smith”, 25},
– {“Michael”, “Johnson”, 35},
– – 更多数据…
– }

– local current_page = 2
– local items_per_page = 10

– local page_data, page_links, total_pages = paginate_data(data_table, current_page, items_per_page)

– – 输出当前页数据
– for i, row in ipairs(page_data) do
– print(“Row ” .. i .. “: ” .. table.concat(row, “, ”))
– end

– – 输出分页链接
– for i, link in ipairs(page_links) do
– if type(link) == “table” then
– print(“Link ” .. i .. “: ” .. link.label .. “ (Page ” .. link.page .. “)”)
– else
– print(“Page ” .. i .. “: ” .. link)
– end
– end