Developing an example Protection plugin

xoft · 06-07-2013, 03:29 AM

Storage startup
The next step is somewhat out-of-order, it was not exactly the most logical step to do, but since it posed an interesting challenge, I decided to tackle it first.
The SQLite DB that we're planning for the data storage needs to have its tables set up properly. We could either distribute an empty DB file with the plugin, or we could set the tables up in code. Since the tables structure may change in the future, which would necessitate this upgrade code anyway, I decided for the second option. So upon plugin startup, the storage needs to open the DB file and then check the structure. We want to ensure that the DB has at least the tables and columns we'll be using later, so the code first defines those tables and columns in variables, and then calls a function for each table that "massages" the current table, if any, into the needed structure. This piece of code is rather interesting and I'm quite proud of coding it, so I'll be commenting on the actual code. It is the cStorage:CreateTable() function inside Storage.lua.

function cStorage:CreateTable(a_TableName, a_Columns)
    local sql = "CREATE TABLE IF NOT EXISTS '" .. a_TableName .. "' (";
    sql = sql .. table.concat(a_Columns, ", ");
    sql = sql .. ")";
    local ErrCode = self.DB:exec(sql);
    if (ErrCode ~= sqlite3.OK) then
        LOGWARNING(PluginPrefix .. "Cannot create DB Table, error " .. ErrCode .. " (" .. self.DB:errmsg() .. ")");
        return false;
    end

First, the table is created, if it doesn't already exist. SQLite has a simple command for that, but it's impossible to know whether it created the table or it did exist before, so we need to continue in either case.

local RemoveExistingColumn = function(UserData, NumCols, Values, Names)
    -- Remove the received column from a_Columns. Search for column name in the Names[] / Values[] pairs
    for i = 1, NumCols do
        if (Names[i] == "name") then
            local ColumnName = Values[i]:lower();
            -- Search the a_Columns if they have that column:
            for j = 1, #a_Columns do
                -- Cut away all column specifiers (after the first space), if any:
                local SpaceIdx = string.find(a_Columns[j], " ");
                if (SpaceIdx ~= nil) then
                    SpaceIdx = SpaceIdx - 1;
                end
                local ColumnTemplate = string.lower(string.sub(a_Columns[j], 1, SpaceIdx));
                -- If it is a match, remove from a_Columns:
                if (ColumnTemplate == ColumnName) then
                    table.remove(a_Columns, j);
                    break;  -- for j
                end
            end  -- for j - a_Columns[]
        end
    end  -- for i - Names[] / Values[]
    return 0;
end
local ErrCode = self.DB:exec("PRAGMA table_info(" .. a_TableName .. ")", RemoveExistingColumn);
if (ErrCode ~= sqlite3.OK) then
    LOGWARNING(PluginPrefix .. "Cannot query DB table structure, error " .. ErrCode .. " (" .. self.DB:errmsg() ..")");
    return false;
end

As a second step, the database is queried for columns in the table, using a special PRAGMA SQL command. That command returns all of the columns' IDs, names, types and whatnot, we simply extract the column name (i-loop) and remove it from the list of columns that the function received (j-loop). Since we can receive columns that don't specify only a name, but also some type specifiers, we need to strip those away before comparing the column names, that's what the string.find() and string.sub() inside the j-loop are for. The code is not exactly top-performance, but since it's executed only once on plugin initialization and only for 2 tables, I think it's better to have less performant code that is easy to understand, rather than a jumping-through-hoops code that no-one understands.

    if (#a_Columns > 0) then
        LOGINFO(PluginPrefix .. "Database table \"" .. a_TableName .. "\" is missing " .. #a_Columns .. " columns, fixing now.");
        for idx, ColumnName in ipairs(a_Columns) do
            local ErrCode = self.DB:exec("ALTER TABLE '" .. a_TableName .. "' ADD COLUMN " .. ColumnName);
            if (ErrCode ~= sqlite3.OK) then
                LOGWARNING(PluginPrefix .. "Cannot add DB table \"" .. a_TableName .. "\" column \"" .. ColumnName .. "\", error " .. ErrCode .. " (" .. self.DB:errmsg() ..")");
                return false;
            end
        end
        LOGINFO(PluginPrefix .. "Database table \"" .. a_TableName .. "\" columns fixed.");
    end
     
    return true;
end

At this point, the a_Columns variable holds all the columns that we'll be needing and that aren't in the DB. So the last step is to alter the DB table, adding each column.

This is from rev 1559 in the MCServer SVN.