luasql in mudlet
Posted: Mon Apr 05, 2010 9:04 am
I just finished writing a little piece of code that I believe is quite divine, but am plagued by a speed issue.
Essentially, I'm now use sqlite to keep track of my enemies, allies and known merchants. my enemies list and guild member list I can retrieve through in game commands (though, MKO doesn't yet offer a CITY MEMBERS command...). Everything is working great. I'm bringing in a huge block of comma seperated names and using a gsub to parse through them and enter them into the database. I clear the DB on every time to get rid of old enemies, as well.
Here's the problem: When that chunk of text comes in and lua starts processing it, the DB functions are incredibly slow. Mudlet hangs for 10-15 seconds while the commands are parsed in. I don't know if this is an error with my code (maybe, because luasql is very poorly documented) or if I can just expect DB transactions to be insanely slow, and count them out of anything critical.
That said: Loading the DB entries seems very fast. I do it once every time I boot up and I can't see any slowdown or hang at all! It's just when I clear the DB and enter in the new elements that I see a problem. I know it's not the clearing, either, because I added that in AFTER I already knew there was a speed issue.
Here's my code:
[syntax=lua]
highlight = {
triggers = {},
allies = {},
enemies = {},
merchants = {}
}
-- Create the database tables, if it doesn't exist
dCur = dMko:execute[[
CREATE TABLE IF NOT EXISTS enemies (
name TEXT,
org TEXT,
kills NUMERIC,
deaths NUMERIC,
CONSTRAINT pk PRIMARY KEY (name,org) ON CONFLICT FAIL
);
CREATE TABLE IF NOT EXISTS allies (
name TEXT,
org TEXT,
CONSTRAINT pk PRIMARY KEY (name,org) ON CONFLICT FAIL
);
CREATE TABLE IF NOT EXISTS merchants (
name TEXT,
CONSTRAINT pk PRIMARY KEY (name) ON CONFLICT FAIL
);
]]
-- Color config
local colors = {
["allies"] = {
["city"] = "green",
["guild"] = "SpringGreen"
},
["enemies"] = {
["city"] = "VioletRed",
["guild"] = "DeepPink"
},
["merchant"] = "gold",
["default"] = "magenta"
}
function highlight:init()
-- initialize the tables and triggers
local results = {}
iCur = dMko:execute("Select name FROM merchants")
while iCur:fetch(results,"a") do
highlight:mkTrigger("merchants",nil,results.name)
self.merchants[results.name] = true
end
iCur = dMko:execute("SELECT name,org FROM allies")
while iCur:fetch(results,"a") do
highlight:mkTrigger("allies",results.org,results.name)
if not self.allies[results.org] then self.allies[results.org] = {} end
self.allies[results.org][results.name] = true
end
iCur = dMko:execute("SELECT name,org FROM enemies")
while iCur:fetch(results,"a") do
highlight:mkTrigger("enemies",results.org,results.name)
if not self.enemies[results.org] then self.enemies[results.org] = {} end
self.enemies[results.org][results.name] = true
end
iCur:close()
end
function highlight:add(cat,grp,name)
local sql = ""
if cat == "allies" then
sql = [[INSERT INTO allies VALUES ("]]..name..[[","]]..grp..[[")]]
elseif cat == "enemies" then
sql = [[INSERT INTO enemies VALUES ("]]..name..[[","]]..grp..[[",0,0)]]
elseif cat == "merchant" then
sql = [[INSERT INTO merchants VALUES ("]]..name..[[")]]
end
aCur = dMko:execute(sql)
if not self[cat][grp] then self[cat][grp] = {} end
self[cat][grp][name] = true
highlight:mkTrigger(cat,grp,name)
end
function highlight:remove(cat,grp,name)
if not self[cat] or not self[cat][grp] then return end
self[cat][grp][name] = nil
rCur = dMko:execute("DELETE FROM "..cat.." WHERE org='"..grp.."' and name='"..name.."'")
end
function highlight:clear(cat,grp)
if self.triggers[cat] and self.triggers[cat][grp] then
for k,v in pairs(self.triggers[cat][grp]) do
killTrigger(self.triggers[cat][grp][k])
end
end
cCur = dMko:execute("delete from "..cat.." where org = '"..grp.."'")
end
function highlight:exists(name)
-- return the group the element exists in, or nil
local cat = ""
for v,_ in pairs(self.allies) do if v:lower() == name:lower() then return "allies" end end
for v,_ in pairs(self.enemies) do if v:lower() == name:lower() then return "enemies" end end
for _,v in pairs(self.merchants) do if v:lower() == name:lower() then return "merchants" end end
return nil
end
function highlight:mkTrigger(cat,grp,name)
-- process the creation of a trigger
-- kill an existing trigger of the name
if not self.triggers[cat] then self.triggers[cat] = {} end
if not self.triggers[cat][grp] then self.triggers[cat][grp] = {} end
if self.triggers[cat][grp][name] then killTrigger(self.triggers[cat][grp][name]) end
self.triggers[cat][grp][name] = tempRegexTrigger("\\b"..name.."\\b",[[highlight:process("]]..cat..[[","]]..grp..[[","]]..name..[[")]])
end
function highlight:process(cat,grp,name)
local color = ""
-- Grab the color needed
if cat == "merchant" then color = colors["merchant"]
else color = colors[cat][grp] or colors["default"]
end
local c = 1
while selectString(name,c) > -1 do
fg(color)
if cat == "merchant" then setUnderline(true) end
c = c + 1
end
end
highlight:init()
[/syntax]
Edit: As I re-read this, i couldn't help but realize the multiple grammar errors and so forth. Please ignore, it's about 2am and my brain is starting to shut down.
Essentially, I'm now use sqlite to keep track of my enemies, allies and known merchants. my enemies list and guild member list I can retrieve through in game commands (though, MKO doesn't yet offer a CITY MEMBERS command...). Everything is working great. I'm bringing in a huge block of comma seperated names and using a gsub to parse through them and enter them into the database. I clear the DB on every time to get rid of old enemies, as well.
Here's the problem: When that chunk of text comes in and lua starts processing it, the DB functions are incredibly slow. Mudlet hangs for 10-15 seconds while the commands are parsed in. I don't know if this is an error with my code (maybe, because luasql is very poorly documented) or if I can just expect DB transactions to be insanely slow, and count them out of anything critical.
That said: Loading the DB entries seems very fast. I do it once every time I boot up and I can't see any slowdown or hang at all! It's just when I clear the DB and enter in the new elements that I see a problem. I know it's not the clearing, either, because I added that in AFTER I already knew there was a speed issue.
Here's my code:
[syntax=lua]
highlight = {
triggers = {},
allies = {},
enemies = {},
merchants = {}
}
-- Create the database tables, if it doesn't exist
dCur = dMko:execute[[
CREATE TABLE IF NOT EXISTS enemies (
name TEXT,
org TEXT,
kills NUMERIC,
deaths NUMERIC,
CONSTRAINT pk PRIMARY KEY (name,org) ON CONFLICT FAIL
);
CREATE TABLE IF NOT EXISTS allies (
name TEXT,
org TEXT,
CONSTRAINT pk PRIMARY KEY (name,org) ON CONFLICT FAIL
);
CREATE TABLE IF NOT EXISTS merchants (
name TEXT,
CONSTRAINT pk PRIMARY KEY (name) ON CONFLICT FAIL
);
]]
-- Color config
local colors = {
["allies"] = {
["city"] = "green",
["guild"] = "SpringGreen"
},
["enemies"] = {
["city"] = "VioletRed",
["guild"] = "DeepPink"
},
["merchant"] = "gold",
["default"] = "magenta"
}
function highlight:init()
-- initialize the tables and triggers
local results = {}
iCur = dMko:execute("Select name FROM merchants")
while iCur:fetch(results,"a") do
highlight:mkTrigger("merchants",nil,results.name)
self.merchants[results.name] = true
end
iCur = dMko:execute("SELECT name,org FROM allies")
while iCur:fetch(results,"a") do
highlight:mkTrigger("allies",results.org,results.name)
if not self.allies[results.org] then self.allies[results.org] = {} end
self.allies[results.org][results.name] = true
end
iCur = dMko:execute("SELECT name,org FROM enemies")
while iCur:fetch(results,"a") do
highlight:mkTrigger("enemies",results.org,results.name)
if not self.enemies[results.org] then self.enemies[results.org] = {} end
self.enemies[results.org][results.name] = true
end
iCur:close()
end
function highlight:add(cat,grp,name)
local sql = ""
if cat == "allies" then
sql = [[INSERT INTO allies VALUES ("]]..name..[[","]]..grp..[[")]]
elseif cat == "enemies" then
sql = [[INSERT INTO enemies VALUES ("]]..name..[[","]]..grp..[[",0,0)]]
elseif cat == "merchant" then
sql = [[INSERT INTO merchants VALUES ("]]..name..[[")]]
end
aCur = dMko:execute(sql)
if not self[cat][grp] then self[cat][grp] = {} end
self[cat][grp][name] = true
highlight:mkTrigger(cat,grp,name)
end
function highlight:remove(cat,grp,name)
if not self[cat] or not self[cat][grp] then return end
self[cat][grp][name] = nil
rCur = dMko:execute("DELETE FROM "..cat.." WHERE org='"..grp.."' and name='"..name.."'")
end
function highlight:clear(cat,grp)
if self.triggers[cat] and self.triggers[cat][grp] then
for k,v in pairs(self.triggers[cat][grp]) do
killTrigger(self.triggers[cat][grp][k])
end
end
cCur = dMko:execute("delete from "..cat.." where org = '"..grp.."'")
end
function highlight:exists(name)
-- return the group the element exists in, or nil
local cat = ""
for v,_ in pairs(self.allies) do if v:lower() == name:lower() then return "allies" end end
for v,_ in pairs(self.enemies) do if v:lower() == name:lower() then return "enemies" end end
for _,v in pairs(self.merchants) do if v:lower() == name:lower() then return "merchants" end end
return nil
end
function highlight:mkTrigger(cat,grp,name)
-- process the creation of a trigger
-- kill an existing trigger of the name
if not self.triggers[cat] then self.triggers[cat] = {} end
if not self.triggers[cat][grp] then self.triggers[cat][grp] = {} end
if self.triggers[cat][grp][name] then killTrigger(self.triggers[cat][grp][name]) end
self.triggers[cat][grp][name] = tempRegexTrigger("\\b"..name.."\\b",[[highlight:process("]]..cat..[[","]]..grp..[[","]]..name..[[")]])
end
function highlight:process(cat,grp,name)
local color = ""
-- Grab the color needed
if cat == "merchant" then color = colors["merchant"]
else color = colors[cat][grp] or colors["default"]
end
local c = 1
while selectString(name,c) > -1 do
fg(color)
if cat == "merchant" then setUnderline(true) end
c = c + 1
end
end
highlight:init()
[/syntax]
Edit: As I re-read this, i couldn't help but realize the multiple grammar errors and so forth. Please ignore, it's about 2am and my brain is starting to shut down.