最近使用LuaSqlite3时,不得不说的——事务提交ATTACH语句的巨坑
在做不同来源的db数据合并新db时，遇见的
lua 事务无法处理附加数据库操作
--[[ AnsiToUTF8(InputString):将传入的字符串转为UTF8字符串,并将其返回 PRINT(InputString):将传入的字符串在日志输出中打印 FileDelete(FilePath):删除FilePath,返回Boolean FileCopy(FilePathA,FilePathB):将文件A复制到文件B,返回Boolean FileExists(FilePath):判断FilePath是否存在,返回Boolean --]] --package.path=";?.lua;" --PRINT(package.path) --package.cpath=package.cpath..";?.dll" --PRINT(package.cpath) function newStack () return {""} -- starts with an empty string end function addString (stack, s) table.insert(stack, s) -- push 's' into the the stack for i=table.getn(stack)-1, 1, -1 do if string.len(stack[i]) > string.len(stack[i+1]) then break end stack[i] = stack[i] .. table.remove(stack) end end function rows(cur,mode) return function(cur) local t = {} if(nil ~= cur:fetch(t, mode or 'a')) then return t else return nil end end,cur end function print(...) local t = {} for i=1,select('#',...) do table.insert(t,tostring(select(i,...))) end PRINT(table.concat(t," ")) end function OnStart(SavePath,MasterPath,SlavePath) print("OnStart 1 : "..os.clock()) --os.execute("lua.exe") require("luasql.sqlite3") g_env = assert(luasql.sqlite3()) local lfs = require("lfs") print(luasql) print(lfs.mkdir) --newfile=io.open("log.txt","w+") g_SavePath=SavePath..'Mir200\\Envir\\LuaEnvir\\sqliteDB\\' g_MasterPath=MasterPath..'Mir200\\Envir\\LuaEnvir\\sqliteDB\\' g_SlavePath=SlavePath..'Mir200\\Envir\\LuaEnvir\\sqliteDB\\' print("lua合区路径: "..g_SavePath) print("\r\n") print("lua主区路径: "..g_MasterPath) print("\r\n") print("lua从区路径: "..g_SlavePath) print("\r\n") --os.execute('mkdir '..g_SavePath) -- 创建合区luaDB目录 lfs.mkdir(SavePath.."Mir200\\") lfs.mkdir(SavePath.."Mir200\\Envir\\") lfs.mkdir(SavePath.."Mir200\\Envir\\LuaEnvir\\") lfs.mkdir(g_SavePath) print('删除旧的合区db！') local res,err = os.remove(g_SavePath..'LUADB.db') print(res,err) g_MasterRoleTab={} -- 主库角色名记录 g_MasterItemTab={} -- 主库物品makeIndex记录 g_SlaveRoleTab={} -- 从库角色名记录 g_SlaveItemTab={} -- 从库物品makeIndex记录 g_masterTabNameTab = {} -- 主库luaDB所有的表名 g_slaveTabNameTab = {} -- 从库luaDB所有的表名 g_masterOldIdTab ={} -- 主库luaDB所有的表ID记录 g_slaveOldIdTab = {} -- 从库luaDB所有的表ID记录 g_MasterConn =assert(g_env:connect(AnsiToUTF8(g_MasterPath..'LUADB.db'))) g_SlaveConn = assert(g_env:connect(AnsiToUTF8(g_SlavePath..'LUADB.db'))) g_SaveConn = assert(g_env:connect(AnsiToUTF8(g_SavePath..'LUADB.db'))) g_SaveConn:execute("PRAGMA synchronous = OFF;") local masterSysRes = g_MasterConn:execute("SELECT name FROM sqlite_master where type ='table' order by name;") local slaveSysRes = g_SlaveConn:execute("SELECT name FROM sqlite_master where type ='table' order by name;") for r in rows(masterSysRes) do if r == nil then break end local tabName= r["name"] print("主库表名:"..tabName) table.insert(g_masterTabNameTab,tabName) end print('----------------------') for r in rows(slaveSysRes) do if r == nil then break end local tabName= r["name"] print("从库表名:"..tabName) table.insert(g_slaveTabNameTab,tabName) end print('----------------------') masterSysRes:close() slaveSysRes:close() for i=1,#g_masterTabNameTab do --local tabName = string.lower(g_masterTabNameTab[i]) local tabName = g_masterTabNameTab[i] local sqlstr = string.format( "SELECT * FROM %s;", tabName) g_masterOldIdTab[tabName] = {} local res = assert(g_MasterConn:execute(sqlstr)) local coltypes = res:getcoltypes() for r in rows(res,'n') do for i=2,#coltypes do local coltype = string.lower(coltypes[i]) if string.find(coltype,'char') or string.find(coltype,'text') then r[i] = string.format("%q",r[i]) end end g_masterOldIdTab[tabName][r[1]] = table.concat(r,',',2) end res:close() end for i=1,#g_slaveTabNameTab do --local tabName = string.lower(g_slaveTabNameTab[i]) local tabName = g_slaveTabNameTab[i] local sqlstr = string.format( "SELECT * FROM %s;", tabName) g_slaveOldIdTab[tabName] = {} local res = assert(g_SlaveConn:execute(sqlstr)) local coltypes = res:getcoltypes() for r in rows(res,'n') do for i=2,#coltypes do local coltype = string.lower(coltypes[i]) if string.find(coltype,'char') or string.find(coltype,'text') then r[i] = string.format("%q",r[i]) end end g_slaveOldIdTab[tabName][r[1]] = table.concat(r,',',2) end res:close() end print("OnStart 2 : "..os.clock()) end function OnAccountChanged(OldAccount,NewAccount) end -- sFlag说明:HumDataDB_M,HeroDataDB_M,UserShopItemDB_M,HumDataDB_S,HeroDataDB_S,UserShopItemDB_S function OnMakeIndexChanging(RoleName,OldMakeIndex,NewMakeIndex,sFlag) --if OldMakeIndex ~= NewMakeIndex then if string.find(sFlag,"_S$") then --newfile:write("从区物品makeindex改变："..RoleName..":"..OldMakeIndex.." ====> "..NewMakeIndex.." "..sFlag) --newfile:write("\r\n") --table.insert( g_SlaveItemTab,{OldMakeIndex,NewMakeIndex}) g_SlaveItemTab[tostring(OldMakeIndex)] = tostring(NewMakeIndex) else --newfile:write("主区物品makeindex改变："..RoleName..":"..OldMakeIndex.." ====> "..NewMakeIndex.." "..sFlag) --newfile:write("\r\n") --table.insert( g_MasterItemTab,{OldMakeIndex,NewMakeIndex}) g_MasterItemTab[tostring(OldMakeIndex)] = tostring(NewMakeIndex) end --end end -- function OnRoleNameChanged(Account,OldRoleName,NewRoleName) -- if OldRoleName ~= NewRoleName then print(string.format("账号:%s 旧角色名:%s 新角色名:%s",Account,OldRoleName,NewRoleName)) --newfile:write("从区玩家名改变： "..Account..":"..OldRoleName.." ====>"..NewRoleName) --newfile:write("\r\n") --table.insert( g_SlaveRoleTab,{OldRoleName,NewRoleName}) g_SlaveRoleTab[OldRoleName]=NewRoleName -- end end function OnGetMRoleName( RoleName ) --print( "主区存在玩家名:" .. name ) --table.insert( g_MasterRoleTab,RoleName ) g_MasterRoleTab[RoleName] = true end function OnStop() print("OnStop 1 : "..os.clock()) local sqlTab = {} local sqlTab1= {} local sqlstr = string.format("ATTACH '%s' AS 'A';",AnsiToUTF8(g_MasterPath.."LUADB.db")) table.insert(sqlTab1,sqlstr) for i=1,#g_masterTabNameTab do -- 复制主表结构 local tabName = string.lower(g_masterTabNameTab[i]) sqlstr = string.format( "CREATE TABLE IF NOT EXISTS %s AS SELECT * FROM A.%s WHERE 1=0;",tabName,tabName) table.insert(sqlTab1,sqlstr) if string.find(tabName,"^all") then tabName = g_masterTabNameTab[i] for id,item in pairs(g_masterOldIdTab[tabName]) do local invalid = true local newID = g_MasterItemTab[id] if newID then invalid = false end if not invalid then sqlstr = string.format("INSERT INTO %s VALUES(%s,%s);",tabName,newID,item) table.insert( sqlTab,sqlstr ) end end elseif string.find(tabName,"^role") then --清除已删除角色数据 tabName = g_masterTabNameTab[i] for id,item in pairs(g_masterOldIdTab[tabName]) do local invalid = true if g_MasterRoleTab[id] then invalid = false else local taskID,name = string.match(id,"(%S+)&(%S+)$") if name and g_MasterRoleTab[name] then invalid = false end end if not invalid then sqlstr = string.format("INSERT INTO %s VALUES(%q,%s);",tabName,id,item) table.insert( sqlTab,sqlstr ) end end end end print("OnStop 2 : "..os.clock()) -- 复制从表结构 sqlstr = string.format("ATTACH '%s' AS 'B';",AnsiToUTF8(g_SlavePath.."LUADB.db")) table.insert(sqlTab1,sqlstr) for i=1,#g_slaveTabNameTab do local tabName = string.lower(g_slaveTabNameTab[i]) sqlstr = string.format( "CREATE TABLE IF NOT EXISTS %s AS SELECT * FROM B.%s WHERE 1=0;",tabName,tabName) table.insert(sqlTab1,sqlstr) if string.find(tabName,"^all") then --清除不存在物品数据 tabName = g_slaveTabNameTab[i] for id,item in pairs(g_slaveOldIdTab[tabName]) do local invalid = true local newID = g_SlaveItemTab[id] if newID then invalid = false end if not invalid then sqlstr = string.format("INSERT INTO %s VALUES(%s,%s);",tabName,newID,item) table.insert( sqlTab,sqlstr ) end end elseif string.find(tabName,"^role") then -- 清除已删除角色数据 tabName = g_slaveTabNameTab[i] for id,item in pairs(g_slaveOldIdTab[tabName]) do local invalid = true local newName= g_SlaveRoleTab[id] local newID = '' if g_SlaveRoleTab[id] then invalid = false newID = newName else local taskID,name = string.match(id,"(%S+)&(%S+)$") if name and g_SlaveRoleTab[name] then invalid = false newID = taskID..'&'..newName end end if not invalid then sqlstr = string.format("INSERT INTO %s VALUES(%q,%s);",tabName,newID,item) table.insert( sqlTab,sqlstr ) end end end end print("OnStop 3 : "..os.clock()) print(type(g_SlaveConn)) print(type(g_MasterConn)) print(type(g_SaveConn)) local res,err for i=1,#sqlTab1 do local str = sqlTab1[i] --print(str) res,err = assert(g_SaveConn:execute(str)) --print(res,err) end g_SaveConn:setautocommit(false) for i=1,#sqlTab do local str = sqlTab[i] --print(str) res,err = assert(g_SaveConn:execute(str)) --print(res,err) end assert(g_SaveConn:commit()) print("OnStop 3 : "..os.clock()) print("OnStop 4 : "..os.clock()) print("执行sql语句条数:" .. #sqlTab) print('关闭从库链接!') g_SlaveConn:close() print('关闭主库链接!') g_MasterConn:close() print('关闭合区库链接!') g_SaveConn:close() print('关闭数据库操作环境!') g_env:close() print("合区成功！！！！") print("OnStop 5 : "..os.clock()) end
