如何在添加新列时保留 SQLITE 中的数据

我有一个当前正在运行的应用程序，我使用一个 sqlite 表来存储游戏的高分。然后当我需要添加洗牌功能时，我通过首先检查列是否存在来添加表中的列。

当前代码

local function setupDatabase()
    local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory)
    local db = sqlite3.open(dbPath)

    local playerSetup = [[
        CREATE TABLE playerInfo(id INTEGER PRIMARY KEY autoincrement, highscore);
        INSERT INTO playerInfo VALUES(NULL, '0');
    ]]
    db:exec(playerSetup)

    if not dbColumnExists( db, "playerInfo", "shuffles") then
        local sql = "alter table playerInfo add column shuffles"
        db:exec(sql)
        db:close()
        M.shuffles = 3  -- 设置为默认值
        M.savePlayerInfo()
    else
        db:close()
    end
end
setupDatabase()

现在我想添加保存分数功能，这是我的新代码:

local function setupDatabase()
    local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory)
    local db = sqlite3.open(dbPath)

    local playerSetup = [[
        CREATE TABLE playerInfo(id INTEGER PRIMARY KEY autoincrement, highscore);
        INSERT INTO playerInfo VALUES(NULL, '0');
    ]]
    db:exec(playerSetup)

    if not dbColumnExists( db, "playerInfo", "shuffles") then
        local sql = "alter table playerInfo add column shuffles"
        db:exec(sql)
        --db:close()
        M.shuffles = 3  -- 设置为默认值
        M.savePlayerInfo()
    end
    if not dbColumnExists( db, "playerInfo", "startscore") then
        local sql = "alter table playerInfo add column startscore"
        db:exec(sql)
        M.savePlayerInfo()
        M.saveScore()
    end
    if not dbColumnExists( db, "playerInfo", "savecount") then
        local sql = "alter table playerInfo add column savecount"
        db:exec(sql)
        M.savecount = 3
        M.savePlayerInfo()
    end
    db:close()
end
setupDatabase()

面对的问题:

我现在面临的问题是，当有人安装 LIVE 应用程序并购买换牌，比如用 10 个积分来换牌，然后安装新更新时，10 个积分会被清除。

是否有更好的方法来确保我保留了数据库中现有的换牌积分？

谢谢

数据的加载和保存

M.loadPlayerInfo = function()
    local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory)
    local db = sqlite3.open(dbPath)
    for row in db:nrows("SELECT * FROM playerInfo WHERE id = 1") do
        M.highscore = tonumber(row.highscore)
        M.shuffles = tonumber(row.shuffles)
        M.startscore = tonumber(row.startscore)
        M.savecount = tonumber(row.savecount)
    end
    db:close()
end

M.savePlayerInfo = function()
    local dbPath = system.pathForFile("appInfo.db3", system.DocumentsDirectory)
    local db = sqlite3.open(dbPath)
    local update = "UPDATE playerInfo SET highscore='" .. M.highscore .."', shuffles='" .. M.shuffles .. "', savecount='" .. M.savecount .. "' WHERE id=1"
    db:exec(update)
    db:close()
end