Database and Events

SQL database operations and the event system (BridgeFunction) on the server.

Database (SQL)

Running Queries

SQLQuery(queryString, callback)

The callback receives a query object with results.

SELECT Example

local queryStr = "SELECT Name, Level FROM Character WHERE AccountID = 'admin'"
SQLQuery(queryStr, function(query)
    if query.result > 0 then
        while query:fetch() do
            local name = query:getString("Name")
            local level = query:getNumber("Level")
            LogPrint(string.format("%s - Level %d", name, level))
        end
    else
        LogPrint("No results")
    end
end)

INSERT Example

local insertQuery = string.format(
    "INSERT INTO MyTable (Name, Value) VALUES ('%s', %d)",
    "PlayerName", 100
)
SQLQuery(insertQuery, function(query)
    LogPrint("Inserted!")
end)

UPDATE Example

local updateQuery = string.format(
    "UPDATE Character SET Level = %d WHERE Name = '%s'",
    400, "PlayerName"
)
SQLQuery(updateQuery, function(query)
    LogPrint("Updated!")
end)

Query Object

-- Properties
query.result    -- 0 = no results, >0 = has results
query.rowCount  -- Number of rows
query.colCount  -- Number of columns

-- Methods
query:fetch()             -- Move to next row (returns boolean)
query:getNumber("col")    -- Read number
query:getSingle("col")    -- Read float
query:getString("col")    -- Read string

CRITICAL: Validate in Callbacks!

Always check if player is still online in SQL callbacks:

function MyPlugin:loadData(aIndex)
    local playerName = GetObjectName(aIndex)

    SQLQuery("SELECT ...", function(query)
        -- CRITICAL: Validate!
        if GetObjectConnected(aIndex) ~= OBJECT_ONLINE then
            return
        end

        if GetObjectName(aIndex) ~= playerName then
            return
        end

        -- Now it's safe to use aIndex
    end)
end

Creating Tables

BridgeFunction:push("OnLoadScript", function()
    local createTable = [[
        IF NOT EXISTS (SELECT * FROM sys.objects
                      WHERE object_id = OBJECT_ID(N'MyTable'))
        BEGIN
            CREATE TABLE MyTable (
                Name VARCHAR(10) PRIMARY KEY,
                Value INT NOT NULL DEFAULT 0
            )
        END
    ]]

    SQLQuery(createTable, function(query)
        LogPrint("Table created!")
    end)
end)

Event System (BridgeFunction)

Register callbacks for game events:

BridgeFunction:push(eventName, callback)

Core Events

-- On script load (first time)
BridgeFunction:push("OnReadScript", function()
    CommandAddInfo("/mycommand", MyPlugin.Code)
end)

-- After OnReadScript
BridgeFunction:push("OnLoadScript", function()
    -- Create tables, initialize data
end)

-- On server shutdown
BridgeFunction:push("OnShutScript", function()
    -- Save data, cleanup
end)

-- Every second
BridgeFunction:push("OnTimerThread", function()
    -- Periodic tasks
end)

Player Events

-- Player enters game
BridgeFunction:push("OnCharacterEntry", function(aIndex)
    local name = GetObjectName(aIndex)
    NoticeSend(aIndex, 1, "Welcome!")
end)

-- Player disconnects
BridgeFunction:push("OnCharacterClose", function(aIndex)
    local name = GetObjectName(aIndex)
    LogPrint(name .. " disconnected")
end)

-- Level up
BridgeFunction:push("OnUserLevelUp", function(aIndex)
    local level = GetObjectLevel(aIndex)
    NoticeSend(aIndex, 1, "Level UP! " .. level)
end)

-- Master level up
BridgeFunction:push("OnUserMasterLevelUp", function(aIndex)
    local mlevel = GetObjectMasterLevel(aIndex)
    NoticeSend(aIndex, 1, "Master Level UP! " .. mlevel)
end)

-- Reset/Master Reset
BridgeFunction:push("OnUserReset", function(aIndex)
    local resets = GetObjectReset(aIndex)
    NoticeSendToAll(1, GetObjectName(aIndex) .. " reset! Total: " .. resets)
end)

BridgeFunction:push("OnUserMasterReset", function(aIndex)
    NoticeSendToAll(1, GetObjectName(aIndex) .. " master reset!")
end)

-- Player dies
BridgeFunction:push("OnUserDie", function(aIndex, bIndex)
    local deadName = GetObjectName(aIndex)
    local killerName = GetObjectName(bIndex)
    LogPrint(deadName .. " killed by " .. killerName)
end)

-- Player respawns
BridgeFunction:push("OnUserRespawn", function(aIndex, killerType)
    NoticeSend(aIndex, 1, "You respawned!")
end)

Monster Events

-- Monster dies
BridgeFunction:push("OnMonsterDie", function(aIndex, bIndex)
    local monsterClass = GetObjectClass(aIndex)
    local killerName = GetObjectName(bIndex)
    -- aIndex = monster, bIndex = killer
end)

Item Events

-- Pick up item
BridgeFunction:push("OnItemPick", function(aIndex, itemIndex, item)
    local itemName = ItemGetName(item.index)
    -- Return 1 to block pickup
    return 0
end)

-- Drop item
BridgeFunction:push("OnItemDrop", function(aIndex, slot, x, y, item)
    -- Return 1 to block drop
    return 0
end)

-- Use item
BridgeFunction:push("OnItemUse", function(aIndex, sourceSlot, targetSlot)
    -- Return 1 to block use
    return 0
end)

Command Events

BridgeFunction:push("OnCommandManager", function(aIndex, code, args)
    if code == MyPlugin.Code then
        MyPlugin:handleCommand(aIndex, args)
        return 1  -- Processed
    end
    return 0  -- Not processed
end)

Network Packets

Register Packet IDs

PluginPacket = PluginPacket or {}
PluginPacket.MyPlugin = 15000  -- Must match client!

Receive from Client

MagicWorld:pushRecv(PluginPacket.MyPlugin, function(aIndex, packet)
    local action = packet:getString()
    local value = packet:getNumber()

    -- Process request
    -- Send response
end)

Send to Client

MagicWorld:sendData(aIndex, PluginPacket.MyPlugin, {
    "response",
    123,
    "data"
})

Complete Example - Ranking System

Ranking = {}
Ranking.ScriptVersion = "1.0.0"

PluginPacket.Ranking = 15050

-- Create table
BridgeFunction:push("OnLoadScript", function()
    SQLQuery([[
        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Ranking'))
        BEGIN
            CREATE TABLE Ranking (
                Name VARCHAR(10) PRIMARY KEY,
                Points INT NOT NULL DEFAULT 0
            )
        END
    ]], function(query) end)
end)

-- Track kills
BridgeFunction:push("OnUserDie", function(aIndex, bIndex)
    local deadName = GetObjectName(aIndex)
    local killerName = GetObjectName(bIndex)

    if deadName == killerName then return end

    -- Update killer
    local updateQuery = string.format([[
        UPDATE Ranking SET Points = Points + 10 WHERE Name = '%s'
        IF @@ROWCOUNT = 0
            INSERT INTO Ranking (Name, Points) VALUES ('%s', 10)
    ]], killerName, killerName)

    SQLQuery(updateQuery, function(q) end)
    NoticeSend(bIndex, 1, "+10 ranking points!")
end)

-- Show ranking
function Ranking:show(aIndex)
    SQLQuery("SELECT TOP 10 Name, Points FROM Ranking ORDER BY Points DESC",
    function(query)
        if GetObjectConnected(aIndex) ~= OBJECT_ONLINE then return end

        if query.result > 0 then
            while query:fetch() do
                local name = query:getString("Name")
                local points = query:getNumber("Points")

                MagicWorld:sendData(aIndex, PluginPacket.Ranking, {
                    name, points
                })
            end
        end
    end)
end

LogColor(3, "[Ranking] Loaded - Version: " .. Ranking.ScriptVersion)

Best Practices

1. ALWAYS validate player in SQL callbacks - Check OBJECT_ONLINE and name
2. Use OnLoadScript for table creation
3. Return 1 in events to block default behavior
4. Keep OnTimerThread light - Runs every second
5. Log important events for debugging

That's database and events! One more guide to go: utilities and commands.