[Help][HouseSystem SQLite] Load houses on restart resource

[Chongas]

Hi Guys!

First: sorry for my bad english ^-^

Second: I am developing a zombie roleplay based on the Zombiegamemode and translating and converting to SQLite the house_system by Noneatme

Creation, updating and managing is working perfectly.

The problem comes when I restart the resource / server. The houses disappear!

In the database, the houses are created.

Below is the code that creates the houses if the resource / server is restarted (in server.lua):

-- HOUSE DATABASE EXECUTION -- 
function housesys_startup() 
    if(created == true) then 
        error("Houses Allready created!") 
        return 
    end 
    buildStartTick = getTickCount() 
    local result, numrows = executeSQLQuery("SELECT * FROM casas;" ) 
    if (result and numrows > 0) then 
        for index, row in pairs(result) do 
            local id = row['ID'] 
            local x, y, z = row['X'], row['Y'], row['Z'] 
            local int, intx, inty, intz = row['INTERIOR'], row['INTX'], row['INTY'], row['INTZ'] 
            local money, weap1, weap2, weap3 = row['MONEY'], row['WEAP1'], row['WEAP2'], row['WEAP3'] 
            local locked = row['LOCKED'] 
            local price = row['PRICE'] 
            local owner = row['OWNER'] 
            local rentable = row['RENTABLE'] 
            local rentalprice = row['RENTALPRICE'] 
            local rent1, rent2, rent3, rent4, rent5 = row['RENT1'],row['RENT2'], row['RENT3'], row['RENT4'], row['RENT5'] 
            local weapontable = {} 
            weapontable[1] = weap1 
            weapontable[2] = weap2 
            weapontable[3] = weap3 
            buildHouse(id, x, y, z, int, intx, inty, intz, money, weapontable, locked, price, owner, rentable, rentalprice, rent1, rent2, rent3, rent4, rent5) 
        end 
        dbFree(result) 
    else 
        error("Houses Table not Found/empty!") 
    end 
    created = true 
    setTimer(function() 
        local elapsed = (buildEndTick-buildStartTick) 
        outputServerLog("It took "..(elapsed/1000).." seconds to build all houses.") 
    end, 1000, 1) 
    rentTimer = setTimer(takePlayerRent, 60*60*1000, -1) 
end 

Below the complete code of file server.lua:

addEventHandler( 'onResourceStart', getResourceRootElement(getThisResource()), 
    function( ) 
        executeSQLQuery("CREATE TABLE IF NOT EXISTS casas (ID INTEGER PRIMARY KEY AUTOINCREMENT, X REAL, Y REAL, Z REAL, INTERIOR INTEGER( 10 ), INTX REAL, INTY REAL, INTZ REAL, MONEY INTEGER( 20 ), WEAP1 VARCHAR( 45 ), WEAP2 VARCHAR( 45 ), WEAP3 VARCHAR( 45 ), LOCKED INTEGER( 2 ), PRICE INTEGER( 10 ), OWNER VARCHAR( 32 ), RENTABLE INTEGER( 2 ), RENTALPRICE INTEGER( 10 ), RENT1 VARCHAR( 32 ), RENT2 VARCHAR( 32 ), RENT3 VARCHAR( 32 ), RENT4 VARCHAR( 32 ), RENT5 VARCHAR( 32 ))") 
    end 
) 
  
local max_player_houses = 1 -- Limite de casas por jogador 
local sellhouse_value = 80 -- Porcentagem de revenda 
local open_key = "F3" -- Botão para informações e painel 
  
addEvent("onHouseSystemHouseCreate", true) 
addEvent("onHouseSystemHouseLock", true) 
addEvent("onHouseSystemHouseDeposit", true) 
addEvent("onHouseSystemHouseWithdraw", true) 
addEvent("onHouseSystemWeaponDeposit", true) 
addEvent("onHouseSystemWeaponWithdraw", true) 
addEvent("onHouseSystemRentableSwitch", true) 
addEvent("onHouseSystemRentalprice", true) 
addEvent("onHouseSystemTenandRemove", true) 
addEvent("onHouseSystemInfoBuy", true) 
addEvent("onHouseSystemInfoRent", true) 
addEvent("onHouseSystemInfoEnter", true) 
  
local handler 
  
local saveableValues = { 
    ["MONEY"] = "MONEY", 
    ["WEAP1"] = "WEAP1", 
    ["WEAP2"] = "WEAP2", 
    ["WEAP3"] = "WEAP3", 
    ["LOCKED"] = "LOCKED", 
    ["OWNER"] = "OWNER", 
    ["RENTABLE"] = "RENTABLE", 
    ["RENTALPRICE"] = "RENTALPRICE", 
    ["RENT1"] = "RENT1", 
    ["RENT2"] = "RENT2", 
    ["RENT3"] = "RENT3", 
    ["RENT4"] = "RENT4", 
    ["RENT5"] = "RENT5", 
} 
  
  
local created = false -- DONT EDIT 
local houseid = 0 -- Define the Houseid,  
  
local house = {} -- The House array 
local houseData = {} -- The House Data arry 
local houseInt = {} -- The House Interior array 
local houseIntData = {} -- The House Interior Data Array  
  
local buildStartTick 
local buildEndTick 
  
local rentTimer 
  
-- SHUTDOWN EVENT HANDLER -- 
addEventHandler("onResourceStop", getResourceRootElement(), function() 
    -- Free the arrays -- 
    for index, houses in pairs(house) do 
        houses = nil 
    end 
    for index, houseDatas in pairs(houseData) do 
        houseDatas = nil 
    end 
    for index, houseInts in pairs(houseInt) do 
        houseInts = nil 
    end 
    for index, houseIntDatas in pairs(houseIntData) do 
        houseIntDatas = nil 
    end 
     
    houseid = 0 
    created = false 
end) 
  
-------------- 
-- Comandos -- 
-------------- 
  
-- /desalugar -- 
addCommandHandler("desalugar", function(thePlayer) 
    if(getElementData(thePlayer, "house:lastvisit")) and (getElementData(thePlayer, "house:lastvisit") ~= false)  then 
        local id = tonumber(getElementData(thePlayer, "house:lastvisit")) 
        if(isPlayerRentedHouse(thePlayer, id) == false) then 
            outputChatBox("Você não alugou essa casa.", thePlayer, 255, 255, 255) 
            return 
        end 
        local sucess = removeHouseTenand(id, thePlayer) 
        if(sucess == true) then 
            outputChatBox("Locação encerrada.", thePlayer, 255, 255, 255) 
        else 
            outputChatBox("Ocorreu um erro.", thePlayer, 255, 255, 255) 
        end 
    end 
end) 
  
-- /alugar -- 
addCommandHandler("alugar", function(thePlayer) 
    if(getElementData(thePlayer, "house:lastvisit")) and (getElementData(thePlayer, "house:lastvisit") ~= false)  then 
        local id = tonumber(getElementData(thePlayer, "house:lastvisit")) 
        if(houseData[id]["OWNER"] == getPlayerName(thePlayer)) then 
            outputChatBox("Você não pode alugar sua propria casa.", thePlayer, 255, 255, 255) 
            return 
        end 
        if(tonumber(houseData[id]["RENTABLE"]) ~= 1) then 
            outputChatBox("Está casa não é alugavel.", thePlayer, 255, 255, 255) 
            return 
        end 
        if(getPlayerRentedHouse(thePlayer) ~= false) then 
            outputChatBox("Você já está hospedado em uma casa, primeiro use /desalugar", thePlayer, 255, 255, 255) 
            return 
        end 
        local sucess = addHouseTenand(thePlayer, id) 
        if(sucess == true) then 
            outputChatBox("Agora você está hospedado nessa casa.", thePlayer, 255, 255, 255) 
        else 
            outputChatBox("Você não pode alugar essa casa.", thePlayer, 255, 255, 255) 
        end 
    end 
end) 
  
-- /criarcasa -- 
addCommandHandler("criarcasa", function(thePlayer) 
    if(hasObjectPermissionTo ( thePlayer, "function.kickPlayer", false ) ) then 
        if(getElementInterior(thePlayer) ~= 0) then 
            outputChatBox("Você não está do lado de fora.", thePlayer, 255, 255, 255) 
            return 
        end 
        if(isPedInVehicle(thePlayer) == true) then 
            outputChatBox("Por favor, saia do seu veículo.", thePlayer, 255, 255, 255) 
            return 
        end 
        -- INSERT SECURITY OPTIONS LIKE ADMINLEVEL HERE( if(adminlevel > shit) then ...) 
        triggerClientEvent(thePlayer, "onClientHouseSystemGUIStart", thePlayer) 
    else 
        outputChatBox("Você não tem permissão para isso.", thePlayer, 255, 255, 255) 
    end 
end) 
  
-- /entrar -- 
addCommandHandler("entrar", function(thePlayer) 
    if(getElementData(thePlayer, "house:lastvisit")) and (getElementData(thePlayer, "house:lastvisit") ~= false)  then 
        local house = getElementData(thePlayer, "house:lastvisit") 
        if(house) then 
            local id = tonumber(house) 
            if(tonumber(houseData[id]["LOCKED"]) == 0) or (houseData[id]["OWNER"] == getPlayerName(thePlayer)) or (isPlayerRentedHouse(thePlayer, id) == true) then 
                local int, intx, inty, intz, dim = houseIntData[id]["INT"], houseIntData[id]["X"], houseIntData[id]["Y"], houseIntData[id]["Z"], id 
                setElementData(thePlayer, "house:in", true) 
                setInPosition(thePlayer, intx, inty, intz, int, false, dim) 
                unbindKey(thePlayer, open_key, "down", togglePlayerInfomenue, id) 
                setElementData(thePlayer, "house:lastvisitINT", id) 
                if(houseData[id]["OWNER"] == getPlayerName(thePlayer)) or (isPlayerRentedHouse(thePlayer, id) == true) then 
                    bindKey(thePlayer, open_key, "down", togglePlayerHousemenue, id) 
                end 
            else 
                outputChatBox("Você não tem as chaves dessa casa.", thePlayer, 255, 255, 255) 
            end 
        end 
    end 
end) 
  
-- /sair -- 
addCommandHandler("sair", function(thePlayer) 
    if(getElementData(thePlayer, "house:lastvisitINT")) and (getElementData(thePlayer, "house:lastvisitINT") ~= false)  then 
        local house = getElementData(thePlayer, "house:lastvisitINT") 
        if(house) then 
            local id = tonumber(house) 
            local x, y, z = houseData[id]["X"], houseData[id]["Y"], houseData[id]["Z"] 
            setElementData(thePlayer, "house:in", false) 
            setInPosition(thePlayer, x, y, z, 0, false, 0) 
        end 
    end 
end) 
  
-- /comprar -- 
addCommandHandler("comprar", function(thePlayer) 
    if(getElementData(thePlayer, "house:lastvisit")) and (getElementData(thePlayer, "house:lastvisit") ~= false)  then 
        local house = getElementData(thePlayer, "house:lastvisit") 
        if(house) then 
            local id = house 
            local owner = houseData[id]["OWNER"] 
            if(owner ~= "Ninguém") then 
                outputChatBox("Você não pode comprar essa casa.", thePlayer, 255, 255, 255) 
            else 
                local houses = 0 
                for index, col in pairs(getElementsByType("colshape")) do 
                    if(getElementData(col, "house") == true) and (houseData[getElementData(col, "ID")]["OWNER"] == getPlayerName(thePlayer)) then 
                        houses = houses+1 
                        if(houses == max_player_houses) then 
                            outputChatBox("Você alcançou o máximo de casas! Venda sua casa primeiro.", thePlayer, 255, 255, 255) 
                            return 
                        end 
                    end 
                end 
                local money = getPlayerMoney(thePlayer) 
                local price = houseData[id]["PRICE"] 
                if(money < price) then outputChatBox("Você precisa de #ed3636R$"..(price-money).."#FFFFFF para comprar essa casa.", thePlayer, 255, 255, 255, true) return end 
                setHouseData(id, "OWNER", getPlayerName(thePlayer)) 
                givePlayerMoney(thePlayer, -price) 
                outputChatBox("Você agora é o dono desta casa.", thePlayer, 255, 255, 255) 
                setElementModel(houseData[id]["PICKUP"], 1272) 
                setElementModel(houseData[id]["BLIP"], 32) 
            end 
        end 
    end 
end) 
  
-- /vender -- 
addCommandHandler("vender", function(thePlayer) 
    if(getElementData(thePlayer, "house:lastvisit")) and (getElementData(thePlayer, "house:lastvisit") ~= false)  then 
        local house = getElementData(thePlayer, "house:lastvisit") 
        if(house) then 
            local id = house 
            local owner = houseData[id]["OWNER"] 
            if(owner ~= getPlayerName(thePlayer)) then 
                outputChatBox("Você não pode vender essa casa.", thePlayer, 255, 255, 255) 
            else 
                local price = houseData[id]["PRICE"] 
                setHouseData(id, "OWNER", "Ninguém") 
                setHouseData(id, "RENTABLE", 0) 
                setHouseData(id, "RENTALPRICE", 0) 
                for i = 1, 5, 1 do 
                    setHouseData(id, "RENT"..i, "Ninguém") 
                end 
                givePlayerMoney(thePlayer, math.floor(price/100*sellhouse_value)) 
                outputChatBox("Você vendeu a casa e recebeu #ed3636R$"..math.floor(price/100*sellhouse_value).."#FFFFFF.", thePlayer, 255, 255, 255, true) 
                setElementModel(houseData[id]["PICKUP"], 1273) 
                setElementModel(houseData[id]["BLIP"], 31) 
            end 
        end 
    end 
end) 
  
-- /excluircasa -- 
addCommandHandler("excluircasa", function(thePlayer, cmd, id) 
    if(hasObjectPermissionTo ( thePlayer, "function.kickPlayer", false ) ) then 
        id = tonumber(id) 
        if not(id) then return end 
        if not(house[id]) then 
            outputChatBox("Não há casas com o #ed3636ID "..id.."#FFFFFF.", thePlayer, 255, 255, 255, true) 
            return 
        end 
        local result = executeSQLQuery("DELETE FROM casas WHERE ID = '"..id.."';") 
        if(result) then 
            destroyElement(houseData[id]["BLIP"]) 
            destroyElement(houseData[id]["PICKUP"]) 
            destroyElement(houseIntData[id]["PICKUP"]) 
            houseData[id] = nil 
            houseIntData[id] = nil 
            destroyElement(house[id]) 
            

[iMr.3a[Z]eF]

debugscript 3?

[VoltBrad]

I just want to confirm. The houses are dynamic and you put them down in game. They save to the SQlite database but, when you restart it doesn't load them? I just want to confirm this is your issue.

If this is the case.. Please try printing the row data aswell as the data once the variables have been set. We need to know if the information is even being loaded in the first place. Do you have any other systems that are using the same connection details / table without a hitch?

[Chongas]

debugscript 3?

Debugscript3 doesn't show nothing about this script.

I just want to confirm. The houses are dynamic and you put them down in game. They save to the SQlite database but, when you restart it doesn't load them? I just want to confirm this is your issue.

If this is the case.. Please try printing the row data aswell as the data once the variables have been set. We need to know if the information is even being loaded in the first place. Do you have any other systems that are using the same connection details / table without a hitch?

Yes, that's the problem.

I have other scripts that use the same database, but not the same table.

Could you show me an example of how I could do that you quoted?

[VoltBrad]

I am fairly new to LUA so don't chew my head off experieicned coders but, Here try this. Let me know what it outputs in the server log.

-- HOUSE DATABASE EXECUTION -- 
function housesys_startup() 
    if(created == true) then 
        error("Houses Allready created!") 
        return 
    end 
    buildStartTick = getTickCount() 
    local result, numrows = executeSQLQuery("SELECT * FROM casas;" ) 
    if (result and numrows > 0) then 
        for index, row in pairs(result) do 
            local id = row['ID'] 
            outputServerLog ( "Row ID" .. tostring(id) .. ) 
            local x, y, z = row['X'], row['Y'], row['Z'] 
            outputServerLog(tostring(x) .. "," .. tostring(y) .. "," .. tostring(z)) 
            local int, intx, inty, intz = row['INTERIOR'], row['INTX'], row['INTY'], row['INTZ'] 
            local money, weap1, weap2, weap3 = row['MONEY'], row['WEAP1'], row['WEAP2'], row['WEAP3'] 
            local locked = row['LOCKED'] 
            local price = row['PRICE'] 
            local owner = row['OWNER'] 
            local rentable = row['RENTABLE'] 
            local rentalprice = row['RENTALPRICE'] 
            local rent1, rent2, rent3, rent4, rent5 = row['RENT1'],row['RENT2'], row['RENT3'], row['RENT4'], row['RENT5'] 
            local weapontable = {} 
            weapontable[1] = weap1 
            weapontable[2] = weap2 
            weapontable[3] = weap3 
            buildHouse(id, x, y, z, int, intx, inty, intz, money, weapontable, locked, price, owner, rentable, rentalprice, rent1, rent2, rent3, rent4, rent5) 
        end 
        dbFree(result) 
    else 
        error("Houses Table not Found/empty!") 
    end 
    created = true 
    setTimer(function() 
        local elapsed = (buildEndTick-buildStartTick) 
        outputServerLog("It took "..(elapsed/1000).." seconds to build all houses.") 
    end, 1000, 1) 
    rentTimer = setTimer(takePlayerRent, 60*60*1000, -1) 
end