Developing an example Protection plugin

[xoft]

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.