Question - Lua and SQL help

grimmier · Jan 3, 2024

I need some help =(
I am trying to make a Spawnmaster version in Lua.

The goal is to query the zone populate a db with spawns.
then the user can view it in a table and select if they want to ignore or track said mob.
I would like to save the db so subsequent loading of the Lua will have the base already and only have to update information that changes like loc, mobid.

My table is laid out like this.

id INTEGER PRIMARY KEY,
mobname TEXT NOT NULL,
mobzonename TEXT NOT NULL,
mobloc TEXT,
mobid INTEGER DEFAULT 0,

I think my issue is i have no Unique ID that can be used from spawn data. Spawn.ID is only unique to the zone and spawn cycle. Thus the autoincrement id field for primary key.
I found another Lua that was doing sql stuff and heavily used it as a base.
thank you Jackalo for your SoloLootManager Lua. btw that thing sells so much faster than ninjaloot i swear its like 5 items a second.

I can get the mobs to populate the gui, I can not get the toggles to work the debug spits out nil values being passed to the SQL query
so basically I am only working on the in memory database. since it can't make updates and this never get to writing it out.

[CODE lang="Lua" title="SpawnMasterCheck"]-- Reworking SoloLootManager to be a Spawn Checker ~thank you Jackalo~
-- Basically Lua version of MQ2SpawnMaster using Sql Lite.

local SpawnMasterCheck = { _version = '1.0', author = 'Grimmier' }

--- @type Mq
local mq = require('mq')

--- @type ImGui
require('ImGui')

local ImguiHelper = require('mq/ImguiHelper')

-- https://gitlab.com/Knightly1/knightlinc
local Write = require('libraries/Write')
Write.prefix = 'SMC'
Write.loglevel = 'info'

local PackageMan = require('mq/PackageMan')

local lsql = PackageMan.Require('lsqlite3') do
Write.Debug("lsqlite version: %s", lsql.version())
end

local DBPath = string.format('%s\\%s', mq.configDir, 'SpawnChecker.sqlite3')

local Table_Cache = {
Rules = {},
Filtered = {},
Unhandled = {},
Mobs = {},
}

local Lookup = {
Rules = {},
}

local GUI_Main = {
Open = true,
Show = true,

Flags = bit32.bor(ImGuiWindowFlags.NoResize, ImGuiWindowFlags.AlwaysAutoResize),

Action = {

},
}

local GUI_Config = {
Open = false,
Show = false,

--Flags = bit32.bor(0),

Refresh = {
Sort = {
Rules = false,
Unhandled = false,
Mobs = false,
},

Table = {
Rules = false,
Filtered = false,
Unhandled = false,
Mobs = false,
},
},

Search = '',

Table = {
Column_ID = {
ID = 1,
MobName = 2,
MobZoneName = 3,
MobLoc = 4,
MobID = 5,
Action = 6,
Remove = 7,
},

Flags = bit32.bor(
ImGuiTableFlags.Resizable,
ImGuiTableFlags.Sortable,
ImGuiTableFlags.NoSavedSettings,
ImGuiTableFlags.RowBg,
ImGuiTableFlags.BordersV,
ImGuiTableFlags.BordersOuter,
ImGuiTableFlags.SizingStretchProp,
ImGuiTableFlags.ScrollY,
ImGuiTableFlags.Hideable
),

SortSpecs = {
Rules = nil,
Unhandled = nil,
Mobs = nil,
},
},
}

---@class Database
---@field private version integer
---@field private connection any lsqlite3.open() object
local Database = {version = 1} do
---@param dbpath string
---@return Database object
function Database:new(dbpath)
local obj = {}

setmetatable(obj, self)

self.__index = self

self.connection = lsql.open(dbpath)

self.connection:exec('PRAGMA primary_keys=on')

self:Initialize()

self:CheckDatabase()

return obj
end

---@package
function Database:CheckDatabase()
local dbVersion = self:GetConfig('version')

-- If there's a revision, upgrade here in steps to each successive version and recurse this function
if dbVersion == '1' then
Write.Debug("dbVersion is current: v%s", dbVersion)
elseif dbVersion ~= nil then
Write.Fatal("unknown database version: %s", dbVersion)
mq.exit()
end
end

---@package
---Initializes a database with the default tables and values.
function Database:Initialize()
self.connection:exec([[
CREATE TABLE IF NOT EXISTS config(
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);

INSERT INTO config (key, value) VALUES ("version", "1")
ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS enum_action(
enum TEXT PRIMARY KEY
);

INSERT INTO enum_action (enum) VALUES ("unhandled")
ON CONFLICT DO NOTHING;
INSERT INTO enum_action (enum) VALUES ("ignore")
ON CONFLICT DO NOTHING;
INSERT INTO enum_action (enum) VALUES ("track")
ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS rule(
id INTEGER PRIMARY KEY NOT NULL,
mobname TEXT NOT NULL,
mobzonename TEXT,
mobloc TEXT,
mobid LONG DEFAULT 0,
enum_action TEXT DEFAULT "unhandled",
CONSTRAINT fk_enum_action
FOREIGN KEY (enum_action)
REFERENCES enum_action (enum)
);
]])
end

---@package
---Called before Initialize() to wipe the database first.
function Database:Reinitialize()
self.connection:exec([[
DROP TABLE IF EXISTS config;
DROP TABLE IF EXISTS rule;
DROP TABLE IF EXISTS enum_action;
]])

self:Initialize()
end

---@package
---@param key string
---@param value string
function Database:AddConfig(key, value)
local spc = self.connection:prepare([[INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value]])

spc:bind_values(key, value)
spc:step()
spc:finalize()
end

---@package
---@param key string
function Database:RemoveConfig(key)
local spc = self.connection:prepare([[DELETE FROM config WHERE key = ?]])

spc:bind_values(key)
spc:step()
spc:finalize()
end

---@package
---@param key string
---@return string
function Database:GetConfig(key)
local spc = self.connection:prepare([[SELECT value FROM config WHERE key = ?]])

spc:bind_values(key)

for row in spc:nrows() do
spc:finalize()

return row.value
end
end

---@private
function RuleToEntry(rule)
local entry = {
MobName = rule.mobname,
MobZoneName = rule.mobzonename,
MobLoc = rule.mobloc,
MobID = rule.mobID,
Enum_Action = rule.enum_action,
}

return entry
end

---@private
local function EntryToRule(entry)
local rule = {
MobName = entry.mobname,
MobZoneName = entry.mobzonename,
MobLoc = entry.mobloc,
MobID = entry.mobid,
Enum_Action = entry.enum_action,
}

return rule
end

---@package
---@param id any
---@param entry table
function Database:AddRule(id, entry)
local keys, values, conflict
local binds = {}

table.insert(binds, id)

for k,v in pairs(EntryToRule(entry)) do
if #binds > 1 then
keys = keys .. ', ' .. k
values = values .. ', ?'
conflict = conflict .. string.format(', %s = excluded.%s', k, k)
else
keys = k
values = '?'
conflict = string.format('%s = excluded.%s', k, k)
end

table.insert(binds, v)
end

local query = string.format([[INSERT INTO rule (%s) VALUES (%s) ON CONFLICT (id) DO UPDATE SET %s]], keys, values, conflict)

local spc = self.connection:prepare(query)
--DEBUG
-- mq.cmd(string.format('/echo Query: %s ', query))
-- local spc = self.connection:prepare(query)
local spc, err = self.connection:prepare(query)
if not spc then
Write.Debug("Statement preparation failed: %s", err)
return -- handle the error appropriately
end

spc:bind_values(unpack(binds))
spc:step()
spc:finalize()
spc:exec("COMMIT;")

end

---@package
---@param id any
function Database:RemoveRule(id)
local spc = self.connection:prepare([[DELETE FROM rule WHERE id = ?]])

spc:bind_values(id)
spc:step()
spc:finalize()
end

---@package
---@param id string
---@return table
function Database:GetRule(id)
local spc = self.connection:prepare([[SELECT * FROM rule WHERE id = ?]])

spc:bind_values(id)

for row in spc:nrows() do
spc:finalize()

return RuleToEntry(row)
end
end

---@package
---@return table
function Database:GetAllRules()
local spc = self.connection:prepare([[SELECT * FROM rule]])

local newTable = {}

for row in spc:nrows() do
table.insert(newTable, RuleToEntry(row))
end

return newTable
end
end

local DB = Database:new(DBPath)

local function ReinitializeDB()
local title = "SMC > Reinitialize Warning"
local text = "Are you really sure you want to wipe the database?"

if ImguiHelper.Popup.Modal(title, text, { "Yes", "Cancel" }) == 1 then
DB:Reinitialize()
end
end

local function AddRule(entry)
Write.Debug('AddRule [%s] %s', entry.Enum_Action, entry.MobName)

DB:AddRule(entry.MobName, entry)

GUI_Config.Refresh.Table.Rules = true
GUI_Config.Refresh.Table.Filtered = true
GUI_Config.Refresh.Table.Unhandled = true
end

local function RemoveRule(entry)
Write.Debug('RemoveRule [%s] %s', entry.Enum_Action, entry.SpawnLink)

DB:RemoveRule(entry.ID)

GUI_Config.Refresh.Table.Rules = true
GUI_Config.Refresh.Table.Filtered = true
GUI_Config.Refresh.Table.Unhandled = true
end

local function CheckRule(entry)
if Lookup.Rules[entry.MobName] then
return true
else
return false
end
end

local function Compare(entryValue, wantedValue)
if entryValue == wantedValue then
return true
else
return false
end
end

local function SpawnToEntry(spawn)
local entry = {
MobName = spawn.CleanName(),
MobZoneName = mq.TLO.Zone.Name,
MobLoc = spawn.Loc(),
MobID = spawn.ID(), -- If you want to include MobID, keep it here
Enum_Action = 'unhandled',
}
return entry
end

local function InsertTableSpawn(dataTable, spawn, opts)
local entry = SpawnToEntry(spawn)

if opts then
for k,v in pairs(opts) do
entry[k] = v
end
end

table.insert(dataTable, entry)
end

local function TableSortSpecs(a, b)
for i = 1, GUI_Config.Table.SortSpecs.SpecsCount, 1 do
local spec = GUI_Config.Table.SortSpecs:Specs(i)

local delta = 0

if spec.ColumnUserID == GUI_Config.Table.Column_ID.MobName then
if a.mobname < b.mobname then
delta = -1
elseif a.mobname> b.mobname then
delta = 1
end
elseif spec.ColumnUserID == GUI_Config.Table.Column_ID.MobLoc then
if a.mobloc < b.mobloc then
delta = -1
elseif a.mobloc > b.mobloc then
delta = 1
end
elseif spec.ColumnUserID == GUI_Config.Table.Column_ID.MobID then
if a.mobid < b.mobid then
delta = -1
elseif a.mobid > b.mobid then
delta = 1
end
elseif spec.ColumnUserID == GUI_Config.Table.Column_ID.Action then
if a.Enum_Action < b.Enum_Action then
delta = -1
elseif a.Enum_Action > b.Enum_Action then
delta = 1
end
end

if delta ~= 0 then
if spec.SortDirection == ImGuiSortDirection.Ascending then
return delta < 0
else
return delta > 0
end
end
end

return a.Name < b.Name
end

local function RefreshRules()
Table_Cache.Rules = DB:GetAllRules()

local newTable = {}

for k,v in ipairs(Table_Cache.Rules) do
table.insert(newTable, v.ID, k)
end

Lookup.Rules = newTable

GUI_Config.Refresh.Table.Filtered = true
GUI_Config.Refresh.Table.Unhandled = true

GUI_Config.Refresh.Table.Rules = false
end

local function RefreshFiltered()
local splitSearch = {}

for part in string.gmatch(GUI_Config.Search, '[^%s]+') do
table.insert(splitSearch, part)
end

local newTable = {}

for k,v in ipairs(Table_Cache.Rules) do
local found = 0

for _,search in ipairs(splitSearch) do
if string.find(string.lower(v.Name), string.lower(search)) then
found = found + 1
end
end

if #splitSearch == found then
table.insert(newTable, v)
end
end

Table_Cache.Filtered = newTable

GUI_Config.Refresh.Sort.Rules = true

GUI_Config.Refresh.Table.Filtered = false
end

local function RefreshUnhandled()
local newTable = {}

for k,v in ipairs(Table_Cache.Rules) do
if v.Enum_Action == 'unhandled' then
table.insert(newTable, v)
end
end

Table_Cache.Unhandled = newTable

GUI_Config.Refresh.Sort.Unhandled = true

GUI_Config.Refresh.Table.Unhandled = false
end
-- Define a predicate function that checks if the spawn is an NPC

local function RefreshZone()
local newTable = {}
local zoneName = mq.TLO.Zone.Name
local npcs = mq.getFilteredSpawns(function(spawn) return spawn.Type() == 'NPC' end)
mq.cmd('/echo MobCount: Iinit', #npcs)
for i = 1, #npcs do
local spawn = npcs
local entry = SpawnToEntry(spawn)-- Convert spawn data to an entry
--entry.ID = i
entry.MobName = spawn.CleanName
entry.MobZoneName = mq.TLO.Zone.Name
entry.MobLoc = spawn.LocYXZ
entry.MobID = spawn.ID
if entry.MobName then table.insert(newTable, entry) end -- Add the entry to the newTable

--Debug:
-- mq.cmd('/echo ID: ', entry.ID)
end
--debug:
--mq.cmd('/echo MobCount: A', #npcs)

Table_Cache.Mobs = newTable
-- debug:
mq.cmd('/echo TableCount: Mobs', #Table_Cache.Mobs)
---mq.cmd('/echo key: Mobs', Table_Cache.Mobs)
for k, v in pairs(Table_Cache.Mobs) do
--Debug:
-- mq.cmd('/echo ipairs:', k, v)
if not CheckRule(v) then
AddRule(v)
end
end

GUI_Config.Refresh.Sort.Mobs = true
GUI_Config.Refresh.Table.Mobs = false
end

local function DrawRuleRow(entry)

ImGui.TableNextColumn()
ImGui.Text('%s', entry.MobName)

ImGui.TableNextColumn()
ImGui.Text('%s', (entry.MobZoneName))

ImGui.TableNextColumn()
ImGui.Text('%s', (entry.MobLoc))

ImGui.TableNextColumn()
ImGui.Text('%s', (entry.MobID))

ImGui.TableNextColumn()
if ImGui.RadioButton("Ignore", Compare(entry.Enum_Action, 'ignore')) then
entry.Enum_Action = 'ignore'

AddRule(entry)
end
ImGui.SameLine()
if ImGui.RadioButton("Track", Compare(entry.Enum_Action, 'track')) then
entry.Enum_Action = 'track'

AddRule(entry)
end

ImGui.TableNextColumn()
if ImGui.SmallButton("Remove") then RemoveRule(entry) end
end

local function DrawMainGUI()
if GUI_Main.Open then
GUI_Main.Open = ImGui.Begin("Spawn Master Checker", GUI_Main.Open, GUI_Main.Flags)

ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)

if #Table_Cache.Unhandled > 0 then
ImGui.PushStyleColor(ImGuiCol.Button, ImVec4(1, 0.3, 0.3, 1))
ImGui.PushStyleColor(ImGuiCol.ButtonHovered, ImVec4(1, 0.4, 0.4, 1))
ImGui.PushStyleColor(ImGuiCol.ButtonActive, ImVec4(1, 0.5, 0.5, 1))
end
if ImGui.SmallButton("Config") then GUI_Config.Open = not GUI_Config.Open end
if #Table_Cache.Unhandled > 0 then
ImGui.PopStyleColor(3)
end
ImGui.SameLine()
if ImGui.SmallButton("Refresh Zone") then RefreshZone() end
ImGui.SameLine()
-- if ImGui.SmallButton("Track Spawns") then end

ImGui.PopStyleVar()

ImGui.End()
end
end

local function DrawConfigGUI()

if GUI_Config.Open then
GUI_Config.Open, GUI_Config.Show = ImGui.Begin("SMC > Config", GUI_Config.Open)

if not GUI_Config.Show then
ImGui.End()
return GUI_Config.Open
end

ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)

if ImGui.BeginTabBar('##TabBar') then

if ImGui.BeginTabItem("All Rules") then
ImGui.PushItemWidth(-95)
local searchText, selected = ImGui.InputText("Search##RulesSearch", GUI_Config.Search)
ImGui.PopItemWidth()

if selected and GUI_Config.Search ~= searchText then
GUI_Config.Search = searchText
GUI_Config.Refresh.Sort.Rules = true
GUI_Config.Refresh.Table.Filtered = true
end

ImGui.SameLine()
if ImGui.Button("Clear##ClearRulesSearch") then
GUI_Config.Search = ''

GUI_Config.Refresh.Sort.Rules = true
GUI_Config.Refresh.Table.Filtered = true
end

ImGui.Separator()

if ImGui.BeginTable('##RulesTable', 6, GUI_Config.Table.Flags) then
ImGui.TableSetupScrollFreeze(0, 1)
ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobName)
ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobZoneName)
ImGui.TableSetupColumn("Loc", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobLoc)
ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobID)
ImGui.TableSetupColumn("Action", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.Action)
ImGui.TableSetupColumn("Remove", ImGuiTableColumnFlags.DefaultSort, 2, GUI_Config.Table.Column_ID.Remove)

ImGui.TableHeadersRow()

local sortSpecs = ImGui.TableGetSortSpecs()
if sortSpecs and (sortSpecs.SpecsDirty or GUI_Config.Refresh.Sort.Unhandled) then
if #Table_Cache.Unhandled > 1 then
GUI_Config.Table.SortSpecs = sortSpecs

table.sort(Table_Cache.Unhandled, TableSortSpecs)

GUI_Config.Table.SortSpecs = nil
end

sortSpecs.SpecsDirty = false
GUI_Config.Refresh.Sort.Unhandled = false
end

local clipper = ImGuiListClipper.new()
clipper:Begin(#Table_Cache.Mobs)
while clipper:Step() do
for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
local entry = Table_Cache.Mobs[i + 1]

ImGui.PushID(entry.ID)
--ImGui.PushID(entry.MobName)
ImGui.TableNextRow()

DrawRuleRow(entry)

ImGui.PopID()
end
end
clipper:End()

ImGui.EndTable()
end

ImGui.EndTabItem()
end

if #Table_Cache.Unhandled > 0 then
ImGui.PushStyleColor(ImGuiCol.Tab, ImVec4(1, 0.3, 0.3, 1))
ImGui.PushStyleColor(ImGuiCol.TabHovered, ImVec4(1, 0.4, 0.4, 1))
ImGui.PushStyleColor(ImGuiCol.TabActive, ImVec4(1, 0.5, 0.5, 1))
end
if ImGui.BeginTabItem("Tracking") then
if ImGui.BeginTable('##UnhandledRulesTable', 4, GUI_Config.Table.Flags) then
ImGui.TableSetupScrollFreeze(0, 1)

ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobName)
ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobZoneName)
ImGui.TableSetupColumn("Loc", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobLoc)
ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Config.Table.Column_ID.MobID)

ImGui.TableHeadersRow()

local sortSpecs = ImGui.TableGetSortSpecs()
if sortSpecs and (sortSpecs.SpecsDirty or GUI_Config.Refresh.Sort.Unhandled) then
if #Table_Cache.Unhandled > 1 then
GUI_Config.Table.SortSpecs = sortSpecs

table.sort(Table_Cache.Unhandled, TableSortSpecs)

GUI_Config.Table.SortSpecs = nil
end

sortSpecs.SpecsDirty = false
GUI_Config.Refresh.Sort.Unhandled = false
end

local clipper = ImGuiListClipper.new()
clipper:Begin(#Table_Cache.Unhandled)
while clipper:Step() do
for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
local entry = Table_Cache.Unhandled[i + 1]

ImGui.PushID(entry.ID)

ImGui.TableNextRow()

DrawRuleRow(entry)

ImGui.PopID()
end
end
clipper:End()

ImGui.EndTable()
end

ImGui.EndTabItem()
end
if #Table_Cache.Unhandled > 0 then
ImGui.PopStyleColor(3)
end

ImGui.EndTabBar()
end

ImGui.PopStyleVar()

ImGui.End()
end
end

local function DrawGUI()
DrawMainGUI()
DrawConfigGUI()
end

-- Kickstart the data
GUI_Config.Refresh.Table.Rules = true
GUI_Config.Refresh.Table.Filtered = true
GUI_Config.Refresh.Table.Unhandled = true

mq.imgui.init('DrawMainGUI', DrawGUI)

mq.bind("/smcconfig", function() GUI_Config.Open = not GUI_Config.Open end)
mq.bind("/smcreinitialize", ReinitializeDB)
mq.bind("/smcquit", function() GUI_Main.Open = not GUI_Main.Open end)

while GUI_Main.Open do

mq.delay(50)
if GUI_Config.Refresh.Table.Rules then RefreshRules() end
if GUI_Config.Refresh.Table.Filtered then RefreshFiltered() end
if GUI_Config.Refresh.Table.Unhandled then RefreshUnhandled() end

end[/CODE]

grimmier · Jan 4, 2024

Kinda working still can't sort. and definately isn't writing to the db file.
would love to make the ignore and track radio buttons move mobs to different tabs.
once I can get the db to persist and write. we can make a flag for tracked or not and then filter the db to only load in tracked mobs (toggleable idealy)

Lua:

-- Reworking SoloLootManager to be a Spawn Checker ~thank you Jackalo~
-- Basically LUA version of MQ2SpawnMaster using Sql Lite.

local SpawnMasterCheck = { _version = '1.0', author = 'Grimmier' }

--- @type Mq
local mq = require('mq')

--- @type ImGui
require('ImGui')

local ImguiHelper = require('mq/ImguiHelper')

-- https://gitlab.com/Knightly1/knightlinc
local Write = require('libraries/Write')
Write.prefix = 'SMC'
Write.loglevel = 'info'

local PackageMan = require('mq/PackageMan')

local lsql = PackageMan.Require('lsqlite3') do
  Write.Debug("lsqlite version: %s", lsql.version())
end

local DBPath = string.format('%s\\%s', mq.configDir, 'SpawnChecker.sqlite3')

local Table_Cache = {
  Rules = {},
  Filtered = {},
  Unhandled = {},
  Mobs = {},
}

local Lookup = {
  Rules = {},
}

local GUI_Main = {
  Open  = true,
  Show  = true,

  -- Flags = bit32.bor(ImGuiWindowFlags.NoResize, ImGuiWindowFlags.AlwaysAutoResize),


  --Flags = bit32.bor(0),

  Refresh = {
    Sort = {
      Rules     = false,
      Unhandled = false,
      Mobs = false,
    },

    Table = {
      Rules     = false,
      Filtered  = false,
      Unhandled = false,
      Mobs = false,
    },
  },

  Search = '',

  Table = {
    Column_ID = {
      ID          = 1,
      MobName     = 2,
      MobZoneName = 3,
      MobLoc      = 4,
      MobID       = 5,
      Action      = 6,
      Remove      = 7,
    },

    Flags = bit32.bor(
      ImGuiTableFlags.AlwaysAutoResize,
      ImGuiTableFlags.Resizable,
      ImGuiTableFlags.Sortable,
      ImGuiTableFlags.RowBg,
      ImGuiTableFlags.BordersV,
      ImGuiTableFlags.BordersOuter,
      ImGuiTableFlags.SizingStretchProp,
      ImGuiTableFlags.ScrollY,
      ImGuiTableFlags.Hideable
    ),

    SortSpecs = {
      Rules     = nil,
      Unhandled = nil,
      Mobs = nil,
    },
  },
}



---@class Database
---@field private version integer
---@field private connection any lsqlite3.open() object
local Database = {version = 1} do
  ---@param dbpath string
  ---@return Database object
  function Database:new(dbpath)
    local obj = {}

    setmetatable(obj, self)

    self.__index = self

    self.connection = lsql.open(dbpath)

    self.connection:exec('PRAGMA primary_keys=on')

    self:Initialize()

    self:CheckDatabase()

    return obj
  end

  ---@package
  function Database:CheckDatabase()
    local dbVersion = self:GetConfig('version')

    -- If there's a revision, upgrade here in steps to each successive version and recurse this function
    if dbVersion == '1' then
      Write.Debug("dbVersion is current: v%s", dbVersion)
    elseif dbVersion ~= nil then
      Write.Fatal("unknown database version: %s", dbVersion)
      mq.exit()
    end
  end

  ---@package
  ---Initializes a database with the default tables and values.
  function Database:Initialize()
    self.connection:exec([[
      CREATE TABLE IF NOT EXISTS config(
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
      );

      INSERT INTO config (key, value) VALUES ("version", "1")
        ON CONFLICT DO NOTHING;

      CREATE TABLE IF NOT EXISTS enum_action(
        enum TEXT PRIMARY KEY
      );

      INSERT INTO enum_action (enum) VALUES ("unhandled")
        ON CONFLICT DO NOTHING;
      INSERT INTO enum_action (enum) VALUES ("ignore")
        ON CONFLICT DO NOTHING;
      INSERT INTO enum_action (enum) VALUES ("track")
        ON CONFLICT DO NOTHING;

      CREATE TABLE IF NOT EXISTS rule(
        id INTEGER PRIMARY KEY NOT NULL,
        mobname TEXT NOT NULL,
        mobzonename TEXT,
        mobloc TEXT,
        mobid LONG DEFAULT 0,
        enum_action TEXT DEFAULT "unhandled",
        CONSTRAINT fk_enum_action
          FOREIGN KEY (enum_action)
          REFERENCES enum_action (enum)
      );
    ]])
  end

  ---@package
  ---Called before Initialize() to wipe the database first.
  function Database:Reinitialize()
    self.connection:exec([[
      DROP TABLE IF EXISTS config;
      DROP TABLE IF EXISTS rule;
      DROP TABLE IF EXISTS enum_action;
    ]])

    self:Initialize()
  end

  ---@package
  ---@param key string
  ---@param value string
  function Database:AddConfig(key, value)
    local spc = self.connection:prepare([[INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value]])

    spc:bind_values(key, value)
    spc:step()
    spc:finalize()
  end

  ---@package
  ---@param key string
  function Database:RemoveConfig(key)
    local spc = self.connection:prepare([[DELETE FROM config WHERE key = ?]])

    spc:bind_values(key)
    spc:step()
    spc:finalize()
  end

  ---@package
  ---@param key string
  ---@return string
  function Database:GetConfig(key)
    local spc = self.connection:prepare([[SELECT value FROM config WHERE key = ?]])

    spc:bind_values(key)

    for row in spc:nrows() do
      spc:finalize()

      return row.value
    end
  end

  ---@private
  function RuleToEntry(rule)
    local entry = {
      MobName = rule.mobname,
      MobZoneName = rule.mobzonename,
      MobLoc = rule.mobloc,
      MobID = rule.mobID,
      Enum_Action = rule.enum_action,
    }

    return entry
  end

  ---@private
  local function EntryToRule(entry)
    local rule = {
      MobName = entry.mobname,
      MobZoneName = entry.mobzonename,
      MobLoc = entry.mobloc,
      MobID = entry.mobid,
      Enum_Action = entry.enum_action,
    }

    return rule
  end

  ---@package
  ---@param id any
  ---@param entry table
  function Database:AddRule(id, entry)
    local keys, values, conflict
    local binds = {}

    table.insert(binds, id)

    for k,v in pairs(EntryToRule(entry)) do
      if #binds > 1 then
        keys = keys .. ', ' .. k
        values = values .. ', ?'
        conflict = conflict .. string.format(', %s = excluded.%s', k, k)
      else
        keys = k
        values = '?'
        conflict = string.format('%s = excluded.%s', k, k)
      end

      table.insert(binds, v)
    end

    local query = string.format([[INSERT INTO rule (%s) VALUES (%s) ON CONFLICT (id) DO UPDATE SET %s]], keys, values, conflict)

    local spc = self.connection:prepare(query)
    --DEBUG
    -- mq.cmd(string.format('/echo Query: %s ', query))
    -- local spc = self.connection:prepare(query)
     local spc, err = self.connection:prepare(query)
   if not spc then
      Write.Debug("Statement preparation failed: %s", err)
     return -- handle the error appropriately
   end

    spc:bind_values(unpack(binds))
    spc:step()
    spc:finalize()
    spc:exec("COMMIT;")

  end



  ---@package
  ---@param id any
  function Database:RemoveRule(id)
    local spc = self.connection:prepare([[DELETE FROM rule WHERE id = ?]])

    spc:bind_values(id)
    spc:step()
    spc:finalize()
  end

  ---@package
  ---@param id string
  ---@return table
  function Database:GetRule(id)
    local spc = self.connection:prepare([[SELECT * FROM rule WHERE id = ?]])

    spc:bind_values(id)

    for row in spc:nrows() do
      spc:finalize()

      return RuleToEntry(row)
    end
  end

  ---@package
  ---@return table
  function Database:GetAllRules()
    local spc = self.connection:prepare([[SELECT * FROM rule]])

    local newTable = {}

    for row in spc:nrows() do
      table.insert(newTable, RuleToEntry(row))
    end

    return newTable
  end
end

local DB = Database:new(DBPath)

local function ReinitializeDB()
  local title = "SMC > Reinitialize Warning"
  local text = "Are you really sure you want to wipe the database?"

  if ImguiHelper.Popup.Modal(title, text, { "Yes", "Cancel" }) == 1 then
    Database:Reinitialize()
  end
end

local function AddRule(entry)
  Write.Debug('AddRule [%s] %s', entry.Enum_Action, entry.MobName)

  Database:AddRule(entry.MobName, entry)

  GUI_Main.Refresh.Table.Rules = true
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
end

local function RemoveRule(entry)
  Write.Debug('RemoveRule [%s] %s', entry.Enum_Action, entry.SpawnLink)

  Database:RemoveRule(entry.ID)

  GUI_Main.Refresh.Table.Rules = true
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
end

local function CheckRule(entry)
  if Lookup.Rules[entry.MobName] then
    return true
  else
    return false
  end
end

local function Compare(entryValue, wantedValue)
  if entryValue == wantedValue then
    return true
  else
    return false
  end
end

local function SpawnToEntry(spawn)
  local entry = {
    MobName = spawn.CleanName(),
    MobZoneName = mq.TLO.Zone.Name,
    MobLoc = spawn.Loc(),
    MobID = spawn.ID(), -- If you want to include MobID, keep it here
    Enum_Action = 'unhandled',
  }
  return entry
end

local function InsertTableSpawn(dataTable, spawn, opts)
  local entry = SpawnToEntry(spawn)

  if opts then
    for k,v in pairs(opts) do
        entry[k] = v
    end
  end

  table.insert(dataTable, entry)
end

local function TableSortSpecs(a, b)
  for i = 1, GUI_Main.Table.SortSpecs.SpecsCount, 1 do
    local spec = GUI_Main.Table.SortSpecs:Specs(i)

    local delta = 0

    if spec.ColumnUserID == GUI_Main.Table.Column_ID.MobName then
      if a.mobname < b.mobname then
        delta = -1
      elseif a.mobname> b.mobname then
        delta = 1
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.MobLoc then
      if a.mobloc < b.mobloc then
        delta = -1
      elseif a.mobloc > b.mobloc then
        delta = 1
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.MobID then
      if a.mobid < b.mobid then
        delta = -1
      elseif a.mobid > b.mobid then
        delta = 1
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.Action then
      if a.Enum_Action < b.Enum_Action then
        delta = -1
      elseif a.Enum_Action > b.Enum_Action then
        delta = 1
      end
    end

    if delta ~= 0 then
      if spec.SortDirection == ImGuiSortDirection.Ascending then
        return delta < 0
      else
        return delta > 0
      end
    end
  end

  return a.Name < b.Name
end

local function RefreshRules()
  Table_Cache.Rules = Database:GetAllRules()

  local newTable = {}

  for k,v in ipairs(Table_Cache.Rules) do
    table.insert(newTable, v.ID, k)
  end

  Lookup.Rules = newTable

  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true

  GUI_Main.Refresh.Table.Rules = false
end

local function RefreshFiltered()
  local splitSearch = {}

  for part in string.gmatch(GUI_Main.Search, '[^%s]+') do
    table.insert(splitSearch, part)
  end

  local newTable = {}

  for k,v in ipairs(Table_Cache.Rules) do
    local found = 0

    for _,search in ipairs(splitSearch) do
      if string.find(string.lower(v.Name), string.lower(search)) then
        found = found + 1
      end
    end

    if #splitSearch == found then
      table.insert(newTable, v)
    end
  end

  Table_Cache.Filtered = newTable

  GUI_Main.Refresh.Sort.Rules = true

  GUI_Main.Refresh.Table.Filtered = false
end

local function RefreshUnhandled()
  local newTable = {}

  for k,v in ipairs(Table_Cache.Rules) do
    if v.Enum_Action == 'unhandled' then
      table.insert(newTable, v)
    end
  end

  Table_Cache.Unhandled = newTable

  GUI_Main.Refresh.Sort.Unhandled = true

  GUI_Main.Refresh.Table.Unhandled = false
end
-- Define a predicate function that checks if the spawn is an NPC


local function RefreshZone()
  local newTable = {}
  local zoneName = mq.TLO.Zone.Name
  local npcs = mq.getFilteredSpawns(function(spawn) return spawn.Type() == 'NPC' end)
  mq.cmd('/echo MobCount: Iinit', #npcs)
  for i = 1, #npcs do
    local spawn = npcs[i]
    local entry  = SpawnToEntry(spawn)-- Convert spawn data to an entry
    --entry.ID = i
    entry.MobName = spawn.CleanName
    entry.MobZoneName = mq.TLO.Zone.Name
    entry.MobLoc = spawn.LocYXZ
    entry.MobID = spawn.ID
    if entry.MobName then  table.insert(newTable, entry) end -- Add the entry to the newTable

   --Debug:
    -- mq.cmd('/echo ID: ', entry.ID)
  end
  --debug:
  --mq.cmd('/echo MobCount: A', #npcs)

  Table_Cache.Mobs = newTable
   -- debug:
    mq.cmd('/echo TableCount: Mobs', #Table_Cache.Mobs)
    ---mq.cmd('/echo key: Mobs', Table_Cache.Mobs)
   for k, v in pairs(Table_Cache.Mobs) do
    --Debug:
   -- mq.cmd('/echo ipairs:', k,  v)
    if not CheckRule(v) then
      AddRule(v)
    end
  end
 
  GUI_Main.Refresh.Sort.Mobs = true
  GUI_Main.Refresh.Table.Mobs = false
end

local function DrawRuleRow(entry)
 
  ImGui.TableNextColumn()
  ImGui.Text('%s', entry.MobName)

  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobZoneName))

  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobLoc))

  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobID))

  ImGui.TableNextColumn()
  if ImGui.RadioButton("Ignore", Compare(entry.Enum_Action, 'ignore')) then
    entry.Enum_Action = 'ignore'

    AddRule(entry)
  end
  ImGui.SameLine()
  if ImGui.RadioButton("Track", Compare(entry.Enum_Action, 'track')) then
    entry.Enum_Action = 'track'

    AddRule(entry)
  end

  ImGui.TableNextColumn()
  if ImGui.SmallButton("Remove") then RemoveRule(entry) end
end



local function DrawMainGUI()
  if GUI_Main.Open then
    GUI_Main.Open = ImGui.Begin("Spawn Master Checker", GUI_Main.Open, GUI_Main.Flags)

    ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)

    if #Table_Cache.Unhandled > 0 then
      ImGui.PushStyleColor(ImGuiCol.Button, ImVec4(1, 0.3, 0.3, 1))
      ImGui.PushStyleColor(ImGuiCol.ButtonHovered, ImVec4(1, 0.4, 0.4, 1))
      ImGui.PushStyleColor(ImGuiCol.ButtonActive, ImVec4(1, 0.5, 0.5, 1))
    end

    if #Table_Cache.Unhandled > 0 then
      ImGui.PopStyleColor(3)
    end
    ImGui.SameLine()
    if ImGui.SmallButton("Refresh Zone") then RefreshZone() end
    ImGui.SameLine()
  --  if ImGui.SmallButton("Track Spawns") then  end

    ImGui.PopStyleVar()


    ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)

    if ImGui.BeginTabBar('##TabBar') then

      if ImGui.BeginTabItem("All NPCs") then
        ImGui.PushItemWidth(-95)
        local searchText, selected = ImGui.InputText("Search##RulesSearch", GUI_Main.Search)
        ImGui.PopItemWidth()

        if selected and GUI_Main.Search ~= searchText then
          GUI_Main.Search = searchText
          GUI_Main.Refresh.Sort.Mobs = true
          GUI_Main.Refresh.Table.Filtered = true
        end

        ImGui.SameLine()
        if ImGui.Button("Clear##ClearRulesSearch") then
          GUI_Main.Search = ''

          GUI_Main.Refresh.Sort.Mobs = true
          GUI_Main.Refresh.Table.Filtered = true
        end

        ImGui.Separator()

        if ImGui.BeginTable('##RulesTable', 6, GUI_Main.Table.Flags) then
          ImGui.TableSetupScrollFreeze(0, 1)
           ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobName)
          ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobZoneName)
          ImGui.TableSetupColumn("Loc (x,y,z)", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobLoc)
          ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobID)
          ImGui.TableSetupColumn("Action", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.Action)
          ImGui.TableSetupColumn("Remove", ImGuiTableColumnFlags.DefaultSort, 2, GUI_Main.Table.Column_ID.Remove)

          ImGui.TableHeadersRow()


          local sortSpecs = ImGui.TableGetSortSpecs()
          if sortSpecs and (sortSpecs.SpecsDirty or GUI_Main.Refresh.Sort.Rules) then
            if #Table_Cache.Filtered > 1 then
              GUI_Main.Table.SortSpecs = sortSpecs

              table.sort(Table_Cache.Filtered, TableSortSpecs)

              GUI_Main.Table.SortSpecs = nil
            end

            sortSpecs.SpecsDirty = false
            GUI_Main.Refresh.Sort.Mobs = false
          end


          local clipper = ImGuiListClipper.new()
          clipper:Begin(#Table_Cache.Mobs)
          while clipper:Step() do
            for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
              local entry = Table_Cache.Mobs[i + 1]

              ImGui.PushID(entry.ID)
              --ImGui.PushID(entry.MobName)
              ImGui.TableNextRow()

             DrawRuleRow(entry)

              ImGui.PopID()
            end
          end
          clipper:End()

          ImGui.EndTable()
        end

        ImGui.EndTabItem()
      end

      if #Table_Cache.Unhandled > 0 then
        ImGui.PushStyleColor(ImGuiCol.Tab, ImVec4(1, 0.3, 0.3, 1))
        ImGui.PushStyleColor(ImGuiCol.TabHovered, ImVec4(1, 0.4, 0.4, 1))
        ImGui.PushStyleColor(ImGuiCol.TabActive, ImVec4(1, 0.5, 0.5, 1))
      end
      if ImGui.BeginTabItem("Tracking") then
        if ImGui.BeginTable('##UnhandledRulesTable', 4, GUI_Main.Table.Flags) then
          ImGui.TableSetupScrollFreeze(0, 1)

          ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobName)
          ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobZoneName)
          ImGui.TableSetupColumn("Loc", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobLoc)
          ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobID)



          ImGui.TableHeadersRow()

          local sortSpecs = ImGui.TableGetSortSpecs()
          if sortSpecs and (sortSpecs.SpecsDirty or GUI_Main.Refresh.Sort.Unhandled) then
            if #Table_Cache.Unhandled > 1 then
              GUI_Main.Table.SortSpecs = sortSpecs

              table.sort(Table_Cache.Unhandled, TableSortSpecs)

              GUI_Main.Table.SortSpecs = nil
            end

            sortSpecs.SpecsDirty = false
            GUI_Main.Refresh.Sort.Unhandled = false
          end

          local clipper = ImGuiListClipper.new()
          clipper:Begin(#Table_Cache.Unhandled)
          while clipper:Step() do
            for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
              local entry = Table_Cache.Unhandled[i + 1]

              ImGui.PushID(entry.ID)

              ImGui.TableNextRow()

              DrawRuleRow(entry)

              ImGui.PopID()
            end
          end
          clipper:End()

          ImGui.EndTable()
        end

        ImGui.EndTabItem()
      end
      if #Table_Cache.Unhandled > 0 then
        ImGui.PopStyleColor(3)
      end

      ImGui.EndTabBar()
    end

    ImGui.PopStyleVar()

    ImGui.End()
  end
end

local function DrawGUI()
  DrawMainGUI()
end

-- Kickstart the data
RefreshZone()
GUI_Main.Refresh.Table.Rules = true
GUI_Main.Refresh.Table.Filtered = true
GUI_Main.Refresh.Table.Unhandled = true

mq.imgui.init('DrawMainGUI', DrawGUI)

mq.bind("/smcconfig", function() GUI_Main.Open = not GUI_Main.Open end)
mq.bind("/smcreinitialize", ReinitializeDB)
mq.bind("/smcquit", function() GUI_Main.Open = not GUI_Main.Open end)

while GUI_Main.Open do

  mq.delay(50)
  if GUI_Main.Refresh.Table.Mobs then RefreshRules() end
  if GUI_Main.Refresh.Table.Filtered then RefreshFiltered() end
  if GUI_Main.Refresh.Table.Unhandled then RefreshUnhandled() end

 
end

grimmier · Jan 4, 2024

I removed the ignore toggle.
Goals:

Ideally I would like to have it so if you check the Track toggle it moves to the tracking tab. (and only tracked mobs appear here)
clicking the remove button would remove from tracking and uncheck the toggle.
The database should only write to disk the mobs you want to track,
- It should be referenced when loading the zone data, populating any spawns found with matching zone and mob names.
Reoccurring Pooling of the zone data should filter only the tracked mobs
- unless its on initial load or clicking the refresh zone button, or the current zone changes.

I can check the checkbox on the top item on each tab but currently they are both initially the same data and the mob will disappear from all mobs if track is checked. Only the top checkbox works though. none of the remove buttons are working yet.
not much has changed but adding the code again
** Update -- found my mistake preventing sorting from working. time for bed omg its 630am i haven't slept yet. **

Lua:

-- Reworking SoloLootManager to be a Spawn Checker ~thank you Jackalo~
-- Basically LUA version of MQ2SpawnMaster using Sql Lite.

local SpawnMasterCheck = { _version = '1.0', author = 'Grimmier' }

--- @type Mq
local mq = require('mq')

--- @type ImGui
require('ImGui')

local ImguiHelper = require('mq/ImguiHelper')

-- https://gitlab.com/Knightly1/knightlinc
local Write = require('libraries/Write')
Write.prefix = 'SMC'
Write.loglevel = 'info'

local PackageMan = require('mq/PackageMan')

local lsql = PackageMan.Require('lsqlite3') do
  Write.Debug("lsqlite version: %s", lsql.version())
end

local DBPath = string.format('%s\\%s', mq.configDir, 'SpawnChecker.sqlite3')

local Table_Cache = {
  Rules = {},
  Filtered = {},
  Unhandled = {},
  Mobs = {},
}

local Lookup = {
  Rules = {},
}

local GUI_Main = {
  Open  = true,
  Show  = true,

  -- Flags = bit32.bor(ImGuiWindowFlags.NoResize, ImGuiWindowFlags.AlwaysAutoResize),


  --Flags = bit32.bor(0),

  Refresh = {
    Sort = {
      Rules     = true,
      Filtered  = true,
      Unhandled = true,
      Mobs = false,
    },

    Table = {
      Rules     = true,
      Filtered  = true,
      Unhandled = false,
      Mobs = false,
    },
  },

  Search = '',

  Table = {
    Column_ID = {
      ID          = 1,
      MobName     = 2,
      MobLoc      = 3,
      MobZoneName = 4,
      MobID       = 5,
      Action      = 6,
      Remove      = 7,
    },

    Flags = bit32.bor(
      ImGuiTableFlags.AlwaysAutoResize,
      ImGuiTableFlags.Resizable,
      ImGuiTableFlags.Sortable,
      ImGuiTableFlags.RowBg,
      ImGuiTableFlags.BordersV,
      ImGuiTableFlags.BordersOuter,
      --ImGuiTableFlags.SizingStretchProp,
      ImGuiTableFlags.ScrollY,
      ImGuiTableFlags.ScrollX,
      ImGuiTableFlags.Hideable
    ),

    SortSpecs = {
      Rules     = nil,
      Unhandled = nil,
      Filtered = nil,
      Mobs = nil,
    },
  },
}



---@class Database
---@field private version integer
---@field private connection any lsqlite3.open() object
local Database = {version = 1} do
  ---@param dbpath string
  ---@return Database object
  function Database:new(dbpath)
    local obj = {}

    setmetatable(obj, self)

    self.__index = self

    self.connection = lsql.open(dbpath)

    self.connection:exec('PRAGMA primary_keys=on')

    self:Initialize()

    self:CheckDatabase()

    return obj
  end

  ---@package
  function Database:CheckDatabase()
    local dbVersion = self:GetConfig('version')

    -- If there's a revision, upgrade here in steps to each successive version and recurse this function
    if dbVersion == '1' then
      Write.Debug("dbVersion is current: v%s", dbVersion)
    elseif dbVersion ~= nil then
      Write.Fatal("unknown database version: %s", dbVersion)
      mq.exit()
    end
  end

  ---@package
  ---Initializes a database with the default tables and values.
  function Database:Initialize()
    self.connection:exec([[
      CREATE TABLE IF NOT EXISTS config(
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
      );

      INSERT INTO config (key, value) VALUES ("version", "1")
        ON CONFLICT DO NOTHING;

      CREATE TABLE IF NOT EXISTS enum_action(
        enum TEXT PRIMARY KEY
      );

      INSERT INTO enum_action (enum) VALUES ("unhandled")
        ON CONFLICT DO NOTHING;
      INSERT INTO enum_action (enum) VALUES ("track")
        ON CONFLICT DO NOTHING;

      CREATE TABLE IF NOT EXISTS rule(
        id INTEGER PRIMARY KEY NOT NULL,
        mobname TEXT NOT NULL,
        mobzonename TEXT,
        mobloc TEXT,
        MobID LONG DEFAULT 0,
        enum_action TEXT DEFAULT "unhandled",
        CONSTRAINT fk_enum_action
          FOREIGN KEY (enum_action)
          REFERENCES enum_action (enum)
      );
    ]])
  end

  ---@package
  ---Called before Initialize() to wipe the database first.
  function Database:Reinitialize()
    self.connection:exec([[
      DROP TABLE IF EXISTS config;
      DROP TABLE IF EXISTS rule;
      DROP TABLE IF EXISTS enum_action;
    ]])

    self:Initialize()
  end

  ---@package
  ---@param key string
  ---@param value string
  function Database:AddConfig(key, value)
    local spc = self.connection:prepare([[INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value]])

    spc:bind_values(key, value)
    spc:step()
    spc:finalize()
  end

  ---@package
  ---@param key string
  function Database:RemoveConfig(key)
    local spc = self.connection:prepare([[DELETE FROM config WHERE key = ?]])

    spc:bind_values(key)
    spc:step()
    spc:finalize()
  end

  ---@package
  ---@param key string
  ---@return string
  function Database:GetConfig(key)
    local spc = self.connection:prepare([[SELECT value FROM config WHERE key = ?]])

    spc:bind_values(key)

    for row in spc:nrows() do
      spc:finalize()

      return row.value
    end
  end

  ---@private
  function RuleToEntry(rule)
    local entry = {
      MobName = rule.mobname,
      MobZoneName = rule.mobzonename,
      MobLoc = rule.mobloc,
      MobID = rule.MobID,
      Enum_Action = rule.enum_action,
    }

    return entry
  end

  ---@private
  local function EntryToRule(entry)
    local rule = {
      MobName = entry.mobname,
      MobZoneName = entry.mobzonename,
      MobLoc = entry.mobloc,
      MobID = entry.MobID,
      Enum_Action = entry.enum_action,
    }

    return rule
  end


---@package
---@param id any
---@param entry table
function Database:AddRule(id, entry)
    -- Prepare the columns and values for the insert operation
    local columns = {"mobname", "mobzonename", "mobloc", "MobID", "enum_action"}
    local valuesPlaceholders = {}
    local updates = {}
    local binds = {}

    -- Fill the valuesPlaceholders and updates arrays
    for _, col in ipairs(columns) do
        table.insert(valuesPlaceholders, "?")
        if col ~= "mobname" and col ~= "mobzonename" and col ~= "id" then
            table.insert(updates, string.format("%s = excluded.%s", col, col))
        end
    end

    -- Fill the binds array with the entry values in the same order as columns
    for _, col in ipairs(columns) do
        table.insert(binds, entry[col])
    end

    -- Convert the arrays to comma-separated strings
    local keys = table.concat(columns, ", ")
    local values = table.concat(valuesPlaceholders, ", ")
    local conflict = table.concat(updates, ", ")

    -- Construct the query string
    local query = string.format([[
        INSERT INTO rule (%s) VALUES (%s)
        ON CONFLICT(mobname, mobzonename) DO UPDATE SET %s
    ]], keys, values, conflict)

    -- Prepare, bind, execute, and finalize the SQL statement
    local spc, err = self.connection:prepare(query)
    if not spc then
        Write.Debug("Statement preparation failed: %s", err)
        return -- handle the error appropriately
    end

    spc:bind_values(unpack(binds))
    spc:step()
    spc:finalize()
end




  ---@package
  ---@param id any
  function Database:RemoveRule(id)
    local spc = self.connection:prepare([[DELETE FROM rule WHERE id = ?]])

    spc:bind_values(id)
    spc:step()
    spc:finalize()
  end

  ---@package
  ---@param id string
  ---@return table
  function Database:GetRule(id)
    local spc = self.connection:prepare([[SELECT * FROM rule WHERE id = ?]])

    spc:bind_values(id)

    for row in spc:nrows() do
      spc:finalize()

      return RuleToEntry(row)
    end
  end

  ---@package
  ---@return table
  function Database:GetAllRules()
    local spc = self.connection:prepare([[SELECT * FROM rule]])

    local newTable = {}

    for row in spc:nrows() do
      table.insert(newTable, RuleToEntry(row))
    end

    return newTable
  end
end

local DB = Database:new(DBPath)

local function ReinitializeDB()
  local title = "SMC > Reinitialize Warning"
  local text = "Are you really sure you want to wipe the database?"

  if ImguiHelper.Popup.Modal(title, text, { "Yes", "Cancel" }) == 1 then
    Database:Reinitialize()
  end
end

local function AddRule(entry)
  Write.Debug('AddRule [%s] %s', entry.Enum_Action, entry.MobName)

  Database:AddRule(entry.MobName, entry)

  GUI_Main.Refresh.Table.Rules = true
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
end

local function RemoveRule(entry)
  Write.Debug('RemoveRule [%s] %s', entry.Enum_Action, entry.SpawnLink)

  Database:RemoveRule(entry.ID)

  GUI_Main.Refresh.Table.Rules = true
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
end

local function CheckRule(entry)
  if Lookup.Rules[entry.MobName] then
    return true
  else
    return false
  end
end

local function Compare(entryValue, wantedValue)
  if entryValue == wantedValue then
    return true
  else
    return false
  end
end

local function SpawnToEntry(spawn)
  local entry = {
    MobName = spawn.CleanName(),
    MobZoneName = mq.TLO.Zone.Name,
    MobLoc = spawn.Loc(),
    MobID = spawn.ID(), -- If you want to include MobID, keep it here
    Enum_Action = 'unhandled',
  }
  return entry
end

local function InsertTableSpawn(dataTable, spawn, opts)
  local entry = SpawnToEntry(spawn)

  if opts then
    for k,v in pairs(opts) do
        entry[k] = v
    end
  end

  table.insert(dataTable, entry)
end

local function TableSortSpecs(a, b)
  for i = 1, GUI_Main.Table.SortSpecs.SpecsCount, 1 do
    local spec = GUI_Main.Table.SortSpecs:Specs(i)

    local delta = 0

    if spec.ColumnUserID == GUI_Main.Table.Column_ID.MobName then
     
      if a.MobName < b.MobName then
        delta = -1
      elseif a.MobName> b.MobName then
        delta = 1
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.MobID then
      if a.MobID < b.MobID then
        delta = -1
      elseif a.MobID > b.MobID then
        delta = 1
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.MobLoc then
      if a.MobLoc < b.MobLoc then
        delta = -1
      elseif a.MobLoc > b.MobLoc then
        delta = 1
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.Action then
      if a.Enum_Action < b.Enum_Action then
        delta = -1
      elseif a.Enum_Action > b.Enum_Action then
        delta = 1
      end
    end

    if delta ~= 0 then
      if spec.SortDirection == ImGuiSortDirection.Ascending then
        return delta < 0
      else
        return delta > 0
      end
    end
  end

  return a.MobName < b.MobName
end

local function RefreshRules()
  Table_Cache.Rules = Database:GetAllRules()

  local newTable = {}

  for k,v in ipairs(Table_Cache.Rules) do
    table.insert(newTable, v.ID, k)
  end

  Lookup.Rules = newTable

  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true

  GUI_Main.Refresh.Table.Rules = false
end

local function RefreshFiltered()
  local splitSearch = {}

  for part in string.gmatch(GUI_Main.Search, '[^%s]+') do
    table.insert(splitSearch, part)
  end

  local newTable = {}

  for k,v in ipairs(Table_Cache.Rules) do
    local found = 0

    for _,search in ipairs(splitSearch) do
      if string.find(string.lower(v.Name), string.lower(search)) then
        found = found + 1
      end
    end

    if #splitSearch == found then
      table.insert(newTable, v)
    end
  end

  Table_Cache.Filtered = newTable

  GUI_Main.Refresh.Sort.Rules = true

  GUI_Main.Refresh.Table.Filtered = false
end

local function RefreshUnhandled()
  local newTable = {}

  for k,v in ipairs(Table_Cache.Rules) do
    if v.Enum_Action == 'unhandled' then
      table.insert(newTable, v)
    end
  end

  Table_Cache.Unhandled = newTable

  GUI_Main.Refresh.Sort.Unhandled = true

  GUI_Main.Refresh.Table.Unhandled = false
end
-- Define a predicate function that checks if the spawn is an NPC


local function RefreshZone()
  local newTable = {}
  local zoneName = mq.TLO.Zone.Name
  local npcs = mq.getFilteredSpawns(function(spawn) return spawn.Type() == 'NPC' end)
  mq.cmd('/echo MobCount: Iinit', #npcs)
  for i = 1, #npcs do
    local spawn = npcs[i]
    InsertTableSpawn(newTable, spawn)
  end
  --debug:
  --mq.cmd('/echo MobCount: A', #npcs)
  Table_Cache.Rules = newTable
 -- Table_Cache.Unhandled = newTable
  Table_Cache.Mobs = newTable
   -- debug:
    mq.cmd('/echo TableCount: Mobs', #Table_Cache.Mobs)
    ---mq.cmd('/echo key: Mobs', Table_Cache.Mobs)
   for k, v in pairs(Table_Cache.Mobs) do
    --Debug:
   -- mq.cmd('/echo ipairs:', k,  v)
    if not CheckRule(v) then
      AddRule(v)
    end
  end


  GUI_Main.Refresh.Sort.Mobs = true
  GUI_Main.Refresh.Table.Mobs = false
end

local function DrawRuleRow(entry)
 
  ImGui.TableNextColumn()
  ImGui.Text('%s', entry.MobName)
 
  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobLoc))
 
  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobZoneName))


  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobID))

  ImGui.TableNextColumn()

  ImGui.SameLine()
  if ImGui.RadioButton("Track", Compare(entry.Enum_Action, 'track')) then
    entry.Enum_Action = 'track'

    AddRule(entry)
  end

  ImGui.TableNextColumn()
  if ImGui.SmallButton("Remove") then RemoveRule(entry) end
end



local function DrawMainGUI()
  if GUI_Main.Open then
    GUI_Main.Open = ImGui.Begin("Spawn Master Checker", GUI_Main.Open, GUI_Main.Flags)

    ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)

    if #Table_Cache.Unhandled > 0 then
      ImGui.PushStyleColor(ImGuiCol.Button, ImVec4(1, 0.3, 0.3, 1))
      ImGui.PushStyleColor(ImGuiCol.ButtonHovered, ImVec4(1, 0.4, 0.4, 1))
      ImGui.PushStyleColor(ImGuiCol.ButtonActive, ImVec4(1, 0.5, 0.5, 1))
    end

    if #Table_Cache.Unhandled > 0 then
      ImGui.PopStyleColor(3)
    end
    ImGui.SameLine()
    if ImGui.SmallButton("Refresh Zone") then RefreshZone() end
    ImGui.SameLine()
    if ImGui.SmallButton("Track Spawns") then  end

    ImGui.PopStyleVar()


    ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)

    if ImGui.BeginTabBar('##TabBar') then

      if ImGui.BeginTabItem(string.format('%s', mq.TLO.Zone.Name)) then
        ImGui.PushItemWidth(-95)
        local searchText, selected = ImGui.InputText("Search##RulesSearch", GUI_Main.Search)
        ImGui.PopItemWidth()

        if selected and GUI_Main.Search ~= searchText then
          GUI_Main.Search = searchText
          GUI_Main.Refresh.Sort.Rules = true
          GUI_Main.Refresh.Table.Unhandled = true
        end

        ImGui.SameLine()
        if ImGui.Button("Clear##ClearRulesSearch") then
          GUI_Main.Search = ''

          GUI_Main.Refresh.Sort.Rules = true
          GUI_Main.Refresh.Table.Unhandled = true
        end

        ImGui.Separator()

        if ImGui.BeginTable('##RulesTable', 6, GUI_Main.Table.Flags) then
          ImGui.TableSetupScrollFreeze(0, 1)
           ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobName)
          ImGui.TableSetupColumn("Loc (x,y,z)", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobLoc)
          ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobZoneName)
          ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobID)
          ImGui.TableSetupColumn("Action", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.Action)
          ImGui.TableSetupColumn("Remove", ImGuiTableColumnFlags.DefaultSort, 2, GUI_Main.Table.Column_ID.Remove)

          ImGui.TableHeadersRow()

          local sortSpecs = ImGui.TableGetSortSpecs()
       
       --|DEBUG ME this breaks if enabled  
          -- if sortSpecs and (sortSpecs.SpecsDirty or GUI_Main.Refresh.Sort.Rules) then
            -- if #Table_Cache.Unhandled > 1 then
              -- GUI_Main.Table.SortSpecs = sortSpecs

              -- table.sort(Table_Cache.Unhandled, TableSortSpecs)

              -- GUI_Main.Table.SortSpecs = nil
            -- end

            -- sortSpecs.SpecsDirty = false
            -- GUI_Main.Refresh.Sort.Rules = false
          -- end

          local clipper = ImGuiListClipper.new()
          clipper:Begin(#Table_Cache.Unhandled)
          while clipper:Step() do
            for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
              local entry = Table_Cache.Unhandled[i + 1]

              ImGui.PushID(entry.ID)

              ImGui.TableNextRow()

              DrawRuleRow(entry)

              ImGui.PopID()
            end
          end
          clipper:End()

          ImGui.EndTable()
        end

        ImGui.EndTabItem()
      end

      if #Table_Cache.Filtered > 0 then
        ImGui.PushStyleColor(ImGuiCol.Tab, ImVec4(1, 0.3, 0.3, 1))
        ImGui.PushStyleColor(ImGuiCol.TabHovered, ImVec4(1, 0.4, 0.4, 1))
        ImGui.PushStyleColor(ImGuiCol.TabActive, ImVec4(1, 0.5, 0.5, 1))
      end
      if ImGui.BeginTabItem("Tracking") then
        if ImGui.BeginTable('##FilteredRulesTable', 6, GUI_Main.Table.Flags) then
          ImGui.TableSetupScrollFreeze(0, 1)

           ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobName)
          ImGui.TableSetupColumn("Loc (x,y,z)", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobLoc)
          ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobZoneName)
          ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobID)
          ImGui.TableSetupColumn("Action", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.Action)
          ImGui.TableSetupColumn("Remove", ImGuiTableColumnFlags.DefaultSort, 2, GUI_Main.Table.Column_ID.Remove)



          ImGui.TableHeadersRow()

          local sortSpecs = ImGui.TableGetSortSpecs()
          --if sortSpecs and (sortSpecs.SpecsDirty or GUI_Main.Refresh.Sort.Filtered) then
            -- if #Table_Cache.Filtered > 1 then
              -- GUI_Main.Table.SortSpecs = sortSpecs

              -- table.sort(Table_Cache.Filtered, TableSortSpecs)

              -- GUI_Main.Table.SortSpecs = nil
            -- end

            -- sortSpecs.SpecsDirty = false
            -- GUI_Main.Refresh.Sort.Filtered = false
          -- end

          local clipper = ImGuiListClipper.new()
          clipper:Begin(#Table_Cache.Filtered)
          while clipper:Step() do
            for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
              local entry = Table_Cache.Filtered[i + 1]

              ImGui.PushID(entry.ID)

              ImGui.TableNextRow()

              DrawRuleRow(entry)

              ImGui.PopID()
            end
          end
          clipper:End()

          ImGui.EndTable()
        end

        ImGui.EndTabItem()
      end
      if #Table_Cache.Filtered > 0 then
        ImGui.PopStyleColor(3)
      end

      ImGui.EndTabBar()
    end

    ImGui.PopStyleVar()

    ImGui.End()
  end
end

local function DrawGUI()
  DrawMainGUI()
end

-- Kickstart the data
RefreshZone()
GUI_Main.Refresh.Table.Rules = true
GUI_Main.Refresh.Table.Filtered = true
GUI_Main.Refresh.Table.Unhandled = true

mq.imgui.init('DrawMainGUI', DrawGUI)

mq.bind("/smcconfig", function() GUI_Main.Open = not GUI_Main.Open end)
mq.bind("/smcreinitialize", ReinitializeDB)
mq.bind("/smcquit", function() GUI_Main.Open = not GUI_Main.Open end)

while GUI_Main.Open do

  mq.delay(50)
  if GUI_Main.Refresh.Table.Mobs then RefreshRules() end
  if GUI_Main.Refresh.Table.Filtered then RefreshFiltered() end
  if GUI_Main.Refresh.Table.Unhandled then RefreshUnhandled() end

 
end

grimmier · Jan 4, 2024

** Update
Sorting, searching are working on the tabs.
radio buttons are working. so only the tracking tab has mobs you checked.

Work in Progress

Still unable to get the DB to write to disc.
want to only write the tracking side of the table not the unhandled mobs.
then use this data to refine the zone searches.

Lua:

-- Reworking SoloLootManager to be a Spawn Checker ~thank you Jackalo~
-- Basically LUA version of MQ2SpawnMaster using Sql Lite.
local SpawnMasterCheck = { _version = '1.0', author = 'Grimmier' }
--- @type Mq
local mq = require('mq')
--- @type ImGui
require('ImGui')
local ImguiHelper = require('mq/ImguiHelper')
-- https://gitlab.com/Knightly1/knightlinc
local Write = require('libraries/Write')
Write.prefix = 'SMC'
Write.loglevel = 'info'
local PackageMan = require('mq/PackageMan')
local lsql = PackageMan.Require('lsqlite3') do
  Write.Debug("lsqlite version: %s", lsql.version())
end
local DBPath = string.format('%s\\%s', mq.configDir, 'SpawnChecker.sqlite3')
local Table_Cache = {
  Rules = {},
  Filtered = {},
  Unhandled = {},
  Mobs = {},
}
local Lookup = {
  Rules = {},
}
local GUI_Main = {
  Open  = true,
  Show  = true,
  -- Flags = bit32.bor(ImGuiWindowFlags.NoResize, ImGuiWindowFlags.AlwaysAutoResize),
  --Flags = bit32.bor(0),
  Refresh = {
    Sort = {
      Rules     = true,
      Filtered  = true,
      Unhandled = true,
      Mobs = false,
    },
    Table = {
      Rules     = true,
      Filtered  = true,
      Unhandled = false,
      Mobs = false,
    },
  },
  Search = '',
  Table = {
    Column_ID = {
      ID          = 1,
      MobName     = 2,
      MobLoc      = 3,
      MobZoneName = 4,
      MobID       = 5,
      Action      = 6,
      Remove      = 7,
    },
    Flags = bit32.bor(
      ImGuiTableFlags.AlwaysAutoResize,
      ImGuiTableFlags.Resizable,
      ImGuiTableFlags.Sortable,
      ImGuiTableFlags.RowBg,
      ImGuiTableFlags.BordersV,
      ImGuiTableFlags.BordersOuter,
      --ImGuiTableFlags.SizingStretchProp,
      ImGuiTableFlags.ScrollY,
      ImGuiTableFlags.ScrollX,
      ImGuiTableFlags.Hideable
    ),
    SortSpecs = {
      Rules     = nil,
      Unhandled = nil,
      Filtered = nil,
      Mobs = nil,
    },
  },
}
---@class Database
---@field private version integer
---@field private connection any lsqlite3.open() object
local Database = {version = 1} do
  ---@param dbpath string
  ---@return Database object
  function Database:new(dbpath)
    local obj = {}
    setmetatable(obj, self)
    self.__index = self
    self.connection = lsql.open(dbpath)
    self.connection:exec('PRAGMA primary_keys=on')
    self:Initialize()
    self:CheckDatabase()
    return obj
  end
  ---@package
  function Database:CheckDatabase()
    local dbVersion = self:GetConfig('version')
    -- If there's a revision, upgrade here in steps to each successive version and recurse this function
    if dbVersion == '1' then
      Write.Debug("dbVersion is current: v%s", dbVersion)
    elseif dbVersion ~= nil then
      Write.Fatal("unknown database version: %s", dbVersion)
      mq.exit()
    end
  end
  ---@package
  ---Initializes a database with the default tables and values.
  function Database:Initialize()
    self.connection:exec([[
      CREATE TABLE IF NOT EXISTS config(
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL
      );
      INSERT INTO config (key, value) VALUES ("version", "1")
        ON CONFLICT DO NOTHING;
      CREATE TABLE IF NOT EXISTS enum_action(
        enum TEXT PRIMARY KEY
      );
      INSERT INTO enum_action (enum) VALUES ("unhandled")
        ON CONFLICT DO NOTHING;
      INSERT INTO enum_action (enum) VALUES ("track")
        ON CONFLICT DO NOTHING;
      CREATE TABLE IF NOT EXISTS rule(
        id INTEGER PRIMARY KEY NOT NULL,
        mobname TEXT NOT NULL,
        mobzonename TEXT,
        mobloc TEXT,
        MobID LONG DEFAULT 0,
        enum_action TEXT DEFAULT "unhandled",
        CONSTRAINT fk_enum_action
          FOREIGN KEY (enum_action)
          REFERENCES enum_action (enum)
      );
    ]])
  end
  ---@package
  ---Called before Initialize() to wipe the database first.
  function Database:Reinitialize()
    self.connection:exec([[
      DROP TABLE IF EXISTS config;
      DROP TABLE IF EXISTS rule;
      DROP TABLE IF EXISTS enum_action;
    ]])
    self:Initialize()
  end
  ---@package
  ---@param key string
  ---@param value string
  function Database:AddConfig(key, value)
    local spc = self.connection:prepare([[INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value]])
    spc:bind_values(key, value)
    spc:step()
    spc:finalize()
  end
  ---@package
  ---@param key string
  function Database:RemoveConfig(key)
    local spc = self.connection:prepare([[DELETE FROM config WHERE key = ?]])
    spc:bind_values(key)
    spc:step()
    spc:finalize()
  end
  ---@package
  ---@param key string
  ---@return string
  function Database:GetConfig(key)
    local spc = self.connection:prepare([[SELECT value FROM config WHERE key = ?]])
    spc:bind_values(key)
    for row in spc:nrows() do
      spc:finalize()
      return row.value
    end
  end
  ---@private
  function RuleToEntry(rule)
    local entry = {
      MobName = rule.mobname,
      MobZoneName = rule.mobzonename,
      MobLoc = rule.mobloc,
      MobID = rule.MobID,
      Enum_Action = rule.enum_action,
    }
    return entry
  end
  ---@private
  local function EntryToRule(entry)
    local rule = {
      MobName = entry.mobname,
      MobZoneName = entry.mobzonename,
      MobLoc = entry.mobloc,
      MobID = entry.MobID,
      Enum_Action = entry.enum_action,
    }
    return rule
  end
---@package
---@param id any
---@param entry table
function Database:AddRule(id, entry)
    -- Prepare the columns and values for the insert operation
    local columns = {"mobname", "mobzonename", "mobloc", "MobID", "enum_action"}
    local valuesPlaceholders = {}
    local updates = {}
    local binds = {}
    -- Fill the valuesPlaceholders and updates arrays
    for _, col in ipairs(columns) do
        table.insert(valuesPlaceholders, "?")
        if col ~= "mobname" and col ~= "mobzonename" and col ~= "id" then
            table.insert(updates, string.format("%s = excluded.%s", col, col))
        end
    end
    -- Fill the binds array with the entry values in the same order as columns
    for _, col in ipairs(columns) do
        table.insert(binds, entry[col])
    end
    -- Convert the arrays to comma-separated strings
    local keys = table.concat(columns, ", ")
    local values = table.concat(valuesPlaceholders, ", ")
    local conflict = table.concat(updates, ", ")
    -- Construct the query string
    local query = string.format([[
        INSERT INTO rule (%s) VALUES (%s)
        ON CONFLICT(mobname, mobzonename) DO UPDATE SET %s
    ]], keys, values, conflict)
    -- Prepare, bind, execute, and finalize the SQL statement
    local spc, err = self.connection:prepare(query)
    if not spc then
        Write.Debug("Statement preparation failed: %s", err)
        return -- handle the error appropriately
    end
    spc:bind_values(unpack(binds))
    spc:step()
    spc:finalize()
end
  ---@package
  ---@param id any
  function Database:RemoveRule(id, entry)
    -- Prepare the columns and values for the Deletion operation
    local columns = {"mobname", "mobzonename"}
    local valuesPlaceholders = {}
    local updates = {}
    local binds = {}
    -- Fill the valuesPlaceholders and updates arrays
    for _, col in ipairs(columns) do
        table.insert(valuesPlaceholders, "?")
        if col ~= "mobname" and col ~= "mobzonename" and col ~= "id" then
            table.insert(updates, string.format("%s = excluded.%s", col, col))
        end
    end
    -- Fill the binds array with the entry values in the same order as columns
    for _, col in ipairs(columns) do
        table.insert(binds, entry[col])
    end
    -- Convert the arrays to comma-separated strings
    local keys = table.concat(columns, ", ")
    local values = table.concat(valuesPlaceholders, ", ")
    local conflict = table.concat(updates, ", ")
    -- Construct the query string
    local query = string.format([[
        DELETE FROM rule (%s) VALUES (%s)
    ]], keys, values)
    -- Prepare, bind, execute, and finalize the SQL statement
    local spc, err = self.connection:prepare(query)
    if not spc then
        Write.Debug("Statement preparation failed: %s", err)
        return -- handle the error appropriately
    end
    spc:bind_values(unpack(binds))
    spc:step()
    spc:finalize()
  end
  ---@package
  ---@param id string
  ---@return table
  function Database:GetRule(id)
    local spc = self.connection:prepare([[SELECT * FROM rule WHERE id = ?]])
    spc:bind_values(id)
    for row in spc:nrows() do
      spc:finalize()
      return RuleToEntry(row)
    end
  end
  ---@package
  ---@return table
  function Database:GetAllRules()
    local spc = self.connection:prepare([[SELECT * FROM rule]])
    local newTable = {}
    for row in spc:nrows() do
      table.insert(newTable, RuleToEntry(row))
    end
    return newTable
  end
end
local DB = Database:new(DBPath)
local function ReinitializeDB()
  local title = "SMC > Reinitialize Warning"
  local text = "Are you really sure you want to wipe the database?"
  if ImguiHelper.Popup.Modal(title, text, { "Yes", "Cancel" }) == 1 then
    Database:Reinitialize()
  end
end
local function AddRule(entry)
  Write.Debug('AddRule [%s] %s', entry.Enum_Action, entry.MobName)
  Database:AddRule(entry.MobName, entry)
  GUI_Main.Refresh.Table.Rules = true
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
end
local function RemoveRule(entry)
  Write.Debug('RemoveRule [%s] %s', entry.Enum_Action, entry.MobName)
  Database:RemoveRule(entry.MobName, entry)
  GUI_Main.Refresh.Table.Rules = true
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
end
local function CheckRule(entry)
  if Lookup.Rules[entry.MobName] then
    return true
  else
    return false
  end
end
local function Compare(entryValue, wantedValue)
  if entryValue == wantedValue then
    return true
  else
    return false
  end
end
local function SpawnToEntry(spawn, row)
  local entry = {
    ID = row,
    MobName = spawn.CleanName(),
    MobZoneName = mq.TLO.Zone.Name,
    MobLoc = spawn.Loc(),
    MobID = spawn.ID(), -- If you want to include MobID, keep it here
    Enum_Action = 'unhandled',
  }
  return entry
end
local function InsertTableSpawn(dataTable, spawn, row, opts)
  local entry = SpawnToEntry(spawn, row)
  if opts then
    for k,v in pairs(opts) do
        entry[k] = v
    end
  end
  table.insert(dataTable, entry)
end
local function TableSortSpecs(a, b)
  for i = 1, GUI_Main.Table.SortSpecs.SpecsCount do
    local spec = GUI_Main.Table.SortSpecs:Specs(i)
    local delta = 0
    if spec.ColumnUserID == GUI_Main.Table.Column_ID.MobName then
      if a.MobName and b.MobName then
        if a.MobName < b.MobName then
         delta = -1
        elseif a.MobName> b.MobName then
          delta = 1
        end
      else
        return  0
      end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.MobID then
      if a.MobID and b.MobID then
      if a.MobID < b.MobID then
        delta = -1
      elseif a.MobID > b.MobID then
        delta = 1
      end
    else
      return  0
    end
    elseif spec.ColumnUserID == GUI_Main.Table.Column_ID.Action then
      if a.Enum_Action < b.Enum_Action then
        delta = -1
      elseif a.Enum_Action > b.Enum_Action then
        delta = 1
      end
    end
    if delta ~= 0 then
      if spec.SortDirection == ImGuiSortDirection.Ascending then
        return delta < 0
      else
        return delta > 0
      end
    end
  end
  return a.MobName < b.MobName
end
local function RefreshRules()
  Table_Cache.Rules = Database:GetAllRules()
  local newTable = {}
  for k,v in ipairs(Table_Cache.Rules) do
    table.insert(newTable, v.ID, k)
  end
  Lookup.Rules = newTable
  GUI_Main.Refresh.Table.Filtered = true
  GUI_Main.Refresh.Table.Unhandled = true
  GUI_Main.Refresh.Table.Rules = false
end
local function RefreshUnhandled()
  local splitSearch = {}
  for part in string.gmatch(GUI_Main.Search, '[^%s]+') do
    table.insert(splitSearch, part)
  end
  local newTable = {}
  for k,v in ipairs(Table_Cache.Rules) do
    local found = 0
    for _,search in ipairs(splitSearch) do
      if string.find(string.lower(v.MobName), string.lower(search)) then
        found = found + 1
      end
    end
    if #splitSearch == found then
      table.insert(newTable, v)
    end
  end
  Table_Cache.Unhandled = newTable
  GUI_Main.Refresh.Sort.Rules = true
  GUI_Main.Refresh.Table.Unhandled = false
end
local function RefreshFiltered()
  local newTable = {}
  for k,v in ipairs(Table_Cache.Rules) do
    if v.Enum_Action == 'track' then
      table.insert(newTable, v)
    end
  end
  Table_Cache.Filtered = newTable
  GUI_Main.Refresh.Sort.Filtered = true
  GUI_Main.Refresh.Table.Filtered = false
end
local function RefreshZone()
  local newTable = {}
  local zoneName = mq.TLO.Zone.Name
  local npcs = mq.getFilteredSpawns(function(spawn) return spawn.Type() == 'NPC' end)
  mq.cmd('/echo MobCount: Iinit', #npcs)
  for i = 1, #npcs do
    local spawn = npcs[i]

    InsertTableSpawn(newTable, spawn, i)
  end
  --debug: --mq.cmd('/echo MobCount: A', #npcs)
   Table_Cache.Rules = newTable
   -- debug: mq.cmd('/echo TableCount: Rules', #Table_Cache.Rules)
   ---mq.cmd('/echo key: Mobs', Table_Cache.Mobs)
   for k, v in pairs(Table_Cache.Rules) do
    --Debug: -- mq.cmd('/echo ipairs:', k,  v)
    if not CheckRule(v) then
      AddRule(v)
    end
  end
  GUI_Main.Refresh.Sort.Mobs = true
  GUI_Main.Refresh.Table.Mobs = false
end
local function DrawRuleRow(entry)
  ImGui.TableNextColumn()
  ImGui.Text('%s', entry.MobName)
  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobLoc))
  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobZoneName))
  ImGui.TableNextColumn()
  ImGui.Text('%s', (entry.MobID))
  ImGui.TableNextColumn()
  ImGui.SameLine()
  if ImGui.RadioButton("Track", Compare(entry.Enum_Action, 'track')) then
    entry.Enum_Action = 'track'
    AddRule(entry)
  end
  ImGui.SameLine()
  if ImGui.RadioButton("Ignore", Compare(entry.Enum_Action, 'unhandled')) then
    entry.Enum_Action = 'unhandled'
    AddRule(entry)
  end
  ImGui.TableNextColumn()
  if ImGui.SmallButton("Remove") then RemoveRule(entry) end
end
local function DrawMainGUI()
  if GUI_Main.Open then
    GUI_Main.Open = ImGui.Begin("Spawn Master Checker", GUI_Main.Open, GUI_Main.Flags)
    ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)
    if #Table_Cache.Unhandled > 0 then
      ImGui.PushStyleColor(ImGuiCol.Button, ImVec4(1, 0.3, 0.3, 1))
      ImGui.PushStyleColor(ImGuiCol.ButtonHovered, ImVec4(1, 0.4, 0.4, 1))
      ImGui.PushStyleColor(ImGuiCol.ButtonActive, ImVec4(1, 0.5, 0.5, 1))
    end
    if #Table_Cache.Unhandled > 0 then
      ImGui.PopStyleColor(3)
    end
    ImGui.SameLine()
    if ImGui.SmallButton("Refresh Zone") then RefreshZone() end
    ImGui.SameLine()
    if ImGui.SmallButton("...") then  end
    ImGui.PopStyleVar()
    ImGui.PushStyleVar(ImGuiStyleVar.WindowPadding, 2, 2)
    if ImGui.BeginTabBar('##TabBar') then
      if ImGui.BeginTabItem(string.format('%s', mq.TLO.Zone.Name)) then
        ImGui.PushItemWidth(-95)
        local searchText, selected = ImGui.InputText("Search##RulesSearch", GUI_Main.Search)
        ImGui.PopItemWidth()
        if selected and GUI_Main.Search ~= searchText then
          GUI_Main.Search = searchText
          GUI_Main.Refresh.Sort.Rules = true
          GUI_Main.Refresh.Table.Unhandled = true
        end
        ImGui.SameLine()
        if ImGui.Button("Clear##ClearRulesSearch") then
          GUI_Main.Search = ''
          GUI_Main.Refresh.Sort.Rules = true
          GUI_Main.Refresh.Table.Unhandled = true
        end
        ImGui.Separator()
        if ImGui.BeginTable('##RulesTable', 6, GUI_Main.Table.Flags) then
          ImGui.TableSetupScrollFreeze(0, 1)

           ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobName)
          ImGui.TableSetupColumn("Loc (x,y,z)", ImGuiTableColumnFlags.NoSort, 8, GUI_Main.Table.Column_ID.MobLoc)
          ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobZoneName)
          ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobID)
          ImGui.TableSetupColumn("Action", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.Action)
          ImGui.TableSetupColumn("Remove", ImGuiTableColumnFlags.DefaultSort, 2, GUI_Main.Table.Column_ID.Remove)
          ImGui.TableHeadersRow()
          local sortSpecs = ImGui.TableGetSortSpecs()
          if sortSpecs and (sortSpecs.SpecsDirty or GUI_Main.Refresh.Sort.Rules) then
            if #Table_Cache.Unhandled > 1 then
              GUI_Main.Table.SortSpecs = sortSpecs
              table.sort(Table_Cache.Unhandled, TableSortSpecs)
              GUI_Main.Table.SortSpecs = nil
            end
            sortSpecs.SpecsDirty = false
            GUI_Main.Refresh.Sort.Rules = false
          end
          local clipper = ImGuiListClipper.new()
          clipper:Begin(#Table_Cache.Unhandled)
          while clipper:Step() do
            for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
              local entry = Table_Cache.Unhandled[i + 1]
              ImGui.PushID(entry.ID)
              ImGui.TableNextRow()
              DrawRuleRow(entry)
              ImGui.PopID()
            end
          end
          clipper:End()
          ImGui.EndTable()
        end
        ImGui.EndTabItem()
      end
      if #Table_Cache.Filtered > 0 then
        ImGui.PushStyleColor(ImGuiCol.Tab, ImVec4(1, 0.3, 0.3, 1))
        ImGui.PushStyleColor(ImGuiCol.TabHovered, ImVec4(1, 0.4, 0.4, 1))
        ImGui.PushStyleColor(ImGuiCol.TabActive, ImVec4(1, 0.5, 0.5, 1))
      end
      if ImGui.BeginTabItem("Tracking") then
        if ImGui.BeginTable('##FilteredRulesTable', 6, GUI_Main.Table.Flags) then
          ImGui.TableSetupScrollFreeze(0, 1)

           ImGui.TableSetupColumn("Name", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobName)
          ImGui.TableSetupColumn("Loc (x,y,z)", ImGuiTableColumnFlags.NoSort, 8, GUI_Main.Table.Column_ID.MobLoc)
          ImGui.TableSetupColumn("Zone", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobZoneName)
          ImGui.TableSetupColumn("MobID", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.MobID)
          ImGui.TableSetupColumn("Action", ImGuiTableColumnFlags.DefaultSort, 8, GUI_Main.Table.Column_ID.Action)
          ImGui.TableSetupColumn("Remove", ImGuiTableColumnFlags.DefaultSort, 2, GUI_Main.Table.Column_ID.Remove)
          ImGui.TableHeadersRow()
          local sortSpecs = ImGui.TableGetSortSpecs()
          if sortSpecs and (sortSpecs.SpecsDirty or GUI_Main.Refresh.Sort.Filtered) then
            if #Table_Cache.Filtered > 1 then
              GUI_Main.Table.SortSpecs = sortSpecs
              table.sort(Table_Cache.Filtered, TableSortSpecs)
              GUI_Main.Table.SortSpecs = nil
            end
            sortSpecs.SpecsDirty = false
            GUI_Main.Refresh.Sort.Filtered = false
          end
          local clipper = ImGuiListClipper.new()
          clipper:Begin(#Table_Cache.Filtered)
          while clipper:Step() do
            for i = clipper.DisplayStart, clipper.DisplayEnd - 1, 1 do
              local entry = Table_Cache.Filtered[i + 1]
              ImGui.PushID(entry.ID)
              ImGui.TableNextRow()
              DrawRuleRow(entry)
              ImGui.PopID()
            end
          end
          clipper:End()
          ImGui.EndTable()
        end
        ImGui.EndTabItem()
      end
      if #Table_Cache.Filtered > 0 then
        ImGui.PopStyleColor(3)
      end
      ImGui.EndTabBar()
    end
    ImGui.PopStyleVar()
    ImGui.End()
  end
end
local function DrawGUI()
  DrawMainGUI()
end
-- Kickstart the data
RefreshZone()
GUI_Main.Refresh.Table.Rules = true
GUI_Main.Refresh.Table.Filtered = true
GUI_Main.Refresh.Table.Unhandled = true
mq.imgui.init('DrawMainGUI', DrawGUI)
mq.bind("/smcconfig", function() GUI_Main.Open = not GUI_Main.Open end)
mq.bind("/smcreinitialize", ReinitializeDB)
mq.bind("/smcquit", function() GUI_Main.Open = not GUI_Main.Open end)
while GUI_Main.Open do
  mq.delay(50)
  if GUI_Main.Refresh.Table.Mobs then RefreshRules() end
  if GUI_Main.Refresh.Table.Filtered then RefreshFiltered() end
  if GUI_Main.Refresh.Table.Unhandled then RefreshUnhandled() end
end

Knightly · Jan 4, 2024

Throw your code up on github and link it to me and I’ll make some comments, forum posts aren’t the best for code commenting.

I’m not sure what your intent is, but the do/end here probably doesn’t do what you think it does:

Lua:

local lsql = PackageMan.Require('lsqlite3') do
  Write.Debug("lsqlite version: %s", lsql.version())
end

For writing, you want to manage state and not do writing in your imgui frames. Store what you want to write and flag for writing then do work outside imgui.

grimmier · Jan 4, 2024

copy that. and sent

Question - Lua and SQL help

grimmier

grimGUI

grimmier

grimGUI

grimmier

grimGUI

grimmier

grimGUI

Knightly

grimmier

grimGUI

Users who are viewing this thread

Cart