Caching your resource

[anumaz]

Date: 17/09/2014

Author: Anthony '.tree'/anumaz Dupont

Topic: Caching your resource to avoid multi SQL queries

Special thanks for helping out: cat, whoever made the mysql resource I am using, CourtezBoi

Chapters

A. Identifying the need for caching and what should be cached

B. Visual Method

C. Scripting Method

A. Identifying the need for caching and what should be cached

You will probably need caching if your resource contains a lot of data which changes infrequently, but has to be shown/displayed frequently.. An example of this would most likely be a vehicle system or even an account panel, where data is requested often by the client. What you want to identify is that 'data send' from a server-side sql request being transfered to client. Having a lot of that leads to important technical issues and delay with your server, hence reducing its performance.

If your resource corresponds to that, then you most likely will want caching.

You won't want to cache every data of your resource. Doing that would simply result in making a double-weighted resource which you want to avoid for file size's sake.

B. Visual Method

Typically, cache is used when showing data, not updating. Hence, every time the client requests to show data, you'll want to use the cache. Although, every time the client requests to update data, you'll want to directly do that in the database. It is however logical to always use the cache to update, and then export your cache to your database when the resource stop, simply not typical.

First, at your resource start, you will load your table(s) with an sql update query. Secondly, you will want to use that table when a user requests to see data. Keep in mind to also update that table every time a change is made. On the third step, you will want to clear your cache when your resource is stopped.

C. Scripting Method

First step: Loading your data.

  
-- s_cache shall be added server type in your meta.xml 
  
-- On top of your s_cache script, you'll want to create all tables you will want to cache. 
  
table = { } 
  
addEventHandler("onResourceStart", resourceRoot, function () 
    local sql_table = exports.mysql:query_fetch_assoc("SELECT * FROM table_name") 
    for k, v in pairs(sql_table) do 
        table[k] = v 
    end 
end) 
  

Second step: Updating your database and cache, or using cache to show.

  
-- To show your cache table, you'll want to go the simple way. Example below, presuming your table is like this below, which is my username with some data. 
  
local cacheTable = { 
    [".tree"] = "any data", 
    ["anumaz"] = "other data" 
} 
  
addCommandHandler("mydata", function (player) 
    local username = getPlayerName(player) 
    if cacheTable[username] then -- To check if it exists 
        outputChatBox(cacheTable[username], player) -- Presuming it is a single data, and it's a string 
    end 
end) 
  

To update data, you will want to update via the sql query and also to add it to the cache table. A common error is refreshing the cache table every time something is modified to it. Don't even think about it!

  
function updateData(key, data) 
    if exports.mysql:query_free("UPDATE table_name SET column_name='"..data.."' WHERE key='"..exports.mysql:escape_string(key).."'") then 
        cacheTable[key] = data 
        return true 
    else 
        return outputDebugString("SQL error at updateData function"), false 
    end 
end 
  

Third step: Normally, when you stop resource, it should delete everything contained in it. You can also, at the same time, make a 'refresh cache' command.

  
addCommandHandler("refreshcache", function () 
    cacheTable = { } -- Clears the table 
  
    local sql_table = exports.mysql:query("SELECT * FROM table_name") 
    while true do 
        local v = exports.mysql:fetch_assoc(sql_table) 
        if not v then break end 
     
        -- We'll assume the first column in the table is called 'username' and returns a string. Second column is called 'data' and contains all we need. 
        cacheTable[v.username] = v.data 
    end 
    exports.mysql:free_result(sql_table) 
end) 
  

A practical example is available on my gist at https://gist.github.com/tree-/8f97a42d7834c258069f