GLua - Programmierung in Garry's Mod

---------------------------------------
---------------------------------------
--      Einfach Gaming Roleplay      --
--             (EGM:RP)              --
--                                   --
--            Created by             --
--      Pascal 'P4sca1' Sthamer,     --
--       Jan 'Oninoni' Ziegler,      --
--    Konstantin 'Airfox' Zisiadis   --
--                                   --
--   This software is only allowed   --
--    on official 'Einfach Gaming'   --
--     servers owned by 'P4sca1'     --
---------------------------------------
---------------------------------------

---------------------------------------
--        Server | SQL System        --
---------------------------------------

require("mysqloo")

-- Initializes the mysql connection with the date given in the config.
function SQL:Init()
    self.db = mysqloo.connect(self.ip, self.username, self.password, self.database, self.port)

    function self.db.onConnected(db)
        LogInfo("Successfully connected to mysql sever")

        db:setCharacterSet("utf8")

        -- Returning something in SQL.Connected prevents default sql initialization.
        if hook.Run("SQL.Connected") == nil then
            hook.Run("SQL.CreateTables")
            hook.Run("SQL.Ready")
        end
    end
            
    function self.db.onConnectionFailed(db, error)
        LogError("Failed to connect to mysql server: " .. error)
    end

    hook.Run("SQL.Connect", self.db)

    self.db:connect()
end

-- Initialize sql on startup.
hook.Add("Initialize", "SQL.Initialize", function()
    SQL:Init()
end)

-- Used to access the db object from outside.
--
-- @return Database db
function SQL:GetDbObject()
    return self.db
end

-- Checks whether the mysql database is connected.
--
-- @return Boolean isConnected
function SQL:IsConnected()
    return self.db and self.db:status() == mysqloo.DATABASE_CONNECTED
end

-- Used to bind the callback to onSuccess and onFailure.
local function addQueryCallback(query, callback)
    local callTrace = debug.traceback()
    local args = { query }

    function query.onAborted(query)
        table.insert(args, false)
        table.insert(args, "aborted")

        if callback then
            callback(unpack(args))
        end
    end

    function query.onError(query, error)
        table.insert(args, false)
        table.insert(args, error)

        if callback then
            callback(unpack(args))
        end

        -- Trace only printed because ErrorNoHalt has a 512 character limit and screws up \n because the string is not written completely.
        ErrorNoHalt(error .. "\n")
        print(callTrace)
    end

    function query.onSuccess(query, results)
        table.insert(args, true)
        table.insert(args, results)

        if callback then
            callback(unpack(args))
        end
    end
end

-- Executes the sql string and executes the callback afterwards.
--
-- @param String sqlString
-- @param? Function callback(query, success, results)
-- @param? Boolean wait - can cause huge lag, defaults to false.
-- @return Query query
function SQL:Query(sqlString, callback, wait)
    LogDebug(sqlString)

    local query = self.db:query(sqlString)
    addQueryCallback(query, callback)

    query:start()

    if wait then
        query:wait()
    end

    return query
end

-- Prepares the query and executes it with the given parameters.
-- Executes the callback afterwards.
--
-- @param String sqlString
-- @param Table params or Any value if there is only one parameter.
-- @param? Function callback(query, success, results)
-- @param? Boolean wait - can cause huge lag, defaults to false.
-- @return PreparedQuery query
function SQL:PreparedQuery(sqlString, params, callback, wait)
    if not istable(params) then
        params = { params }
    end

    -- Print sql string and params when in debug mode.
    LogDebug(sqlString)

    if Config.LogLevel == 0 then
        PrintTable(params)
    end

    local preparedQuery = self.db:prepare(sqlString)
    addQueryCallback(preparedQuery, callback)

    -- Append parameters to query.
    for k, v in pairs(params) do
        local varType = type(v)

        if varType == "number" then
            preparedQuery:setNumber(k, v)
        elseif varType == "boolean" then
            preparedQuery:setBoolean(k, v)
        else
            preparedQuery:setString(k, v)
        end
    end

    preparedQuery:start()

    if wait then
        preparedQuery:wait()
    end

    return preparedQuery
end

-- Creates a new sql table if it does not exist. Runs synchron.
--
-- @param String tableName
-- @param Table columns
-- @param? Function callback(query, success, data)
-- @return Query query
function SQL:CreateTableIfNotExists(tableName, columns, callback)
    local sqlString = "CREATE TABLE IF NOT EXISTS `" .. tableName .. "` ("

    for k, column in pairs(columns) do
        if k > 1 then
            sqlString = sqlString .. ", "
        end

        sqlString = sqlString .. "`" .. column.name .. "` " .. column.definition
    end

    sqlString = sqlString .. ") CHARACTER SET utf8"

    return self:Query(sqlString, callback, true)
end

-- Inserts the data into the table.
-- 
-- @param String tableName
-- @param Table data
-- @param? Function callback(query, success, data)
-- @param? Boolean wait - can cause huge lag, defaults to false.
-- @return PreparedQuery query
function SQL:Insert(tableName, data, callback, wait)
    local keys = {}
    local values = {}

    for key, value in pairs(data) do
        table.insert(keys, key)
        table.insert(values, value)
    end

    local sqlString = "INSERT INTO `" .. tableName .. "` ("

    for k, v in SortedPairs(keys) do
        if k > 1 then
            sqlString = sqlString .. ", "
        end

        sqlString = sqlString .. "`" .. v .. "`"
    end

    sqlString = sqlString .. ") VALUES ("

    for i = 1, #keys do
        if i > 1 then
            sqlString = sqlString .. ", "
        end

        sqlString = sqlString .. "?"
    end

    sqlString = sqlString .. ")"

    return self:PreparedQuery(sqlString, values, callback, wait)
end