luasql in mudlet

User avatar
tsuujin
Posts: 695
Joined: Fri Feb 26, 2010 12:59 am
Location: California
Contact:

luasql in mudlet

Post by tsuujin »

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.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: luasql in mudlet

Post by Vadi »

You want to do all your transactions in bulk - if you're calling add on every single name, you can expect it to be slow. Add only once with all names - speed here doesn't depend on the size of the query but on the amount of times you do it. Or, turn off autcommit mode, add in all names with those single queries, and commit manually at the end.

On my setup any size of a query takes ~200ms and I'm guessing that's just my HD and it can't be any faster.

User avatar
tsuujin
Posts: 695
Joined: Fri Feb 26, 2010 12:59 am
Location: California
Contact:

Re: luasql in mudlet

Post by tsuujin »

I wasn't aware that sqlite actually supported transactions. Kind of thought they were just psudocode.

naftali
Posts: 138
Joined: Wed Jan 20, 2010 8:42 pm

Re: luasql in mudlet

Post by naftali »

Is there a reason doing this in this way is better than doing it using the db functions?

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: luasql in mudlet

Post by Vadi »

It's not any better

User avatar
tsuujin
Posts: 695
Joined: Fri Feb 26, 2010 12:59 am
Location: California
Contact:

Re: luasql in mudlet

Post by tsuujin »

naftali wrote:Is there a reason doing this in this way is better than doing it using the db functions?
The db frontend mudlet implements tries its damnedest to keep you away from actually knowing what's going on in the DB. It's just a wrapper for luasql to try and make it simpler, as far as I can tell. I want to actually control the DB.

naftali
Posts: 138
Joined: Wed Jan 20, 2010 8:42 pm

Re: luasql in mudlet

Post by naftali »

tsuujin wrote:
naftali wrote:Is there a reason doing this in this way is better than doing it using the db functions?
The db frontend mudlet implements tries its damnedest to keep you away from actually knowing what's going on in the DB. It's just a wrapper for luasql to try and make it simpler, as far as I can tell. I want to actually control the DB.
So for someone like me who wouldn't know luasql if it hit me on the nose it's probably better to use the DB frontend, yes?

User avatar
tsuujin
Posts: 695
Joined: Fri Feb 26, 2010 12:59 am
Location: California
Contact:

Re: luasql in mudlet

Post by tsuujin »

naftali wrote:
tsuujin wrote:
naftali wrote:Is there a reason doing this in this way is better than doing it using the db functions?
The db frontend mudlet implements tries its damnedest to keep you away from actually knowing what's going on in the DB. It's just a wrapper for luasql to try and make it simpler, as far as I can tell. I want to actually control the DB.
So for someone like me who wouldn't know luasql if it hit me on the nose it's probably better to use the DB frontend, yes?
Yes.

Skylark
Posts: 46
Joined: Mon Feb 22, 2010 12:38 am

Re: luasql in mudlet

Post by Skylark »

So if I have a table of names I want added to a database, how would I go about doing that with only a single db:add to avoid slowdown?

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: luasql in mudlet

Post by Vadi »

I believe the "Adding Data" section of manual describes that well.

Post Reply