• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

[Lua] Format your SQL queries with db.formatQuery

LaloHao

Member
Joined
Sep 14, 2008
Messages
73
Reaction score
14
Location
Mexico
I'm working on a lua account manager but the moment i started making the account creator script i noticed a bad pattern.

zaHnma.png


^ When executing a query everything has to be on one line, in this case its only 6 columns and it looks semi-decent, but can you guess what will happen when i edit the player table?

XAcLfp.png


This is obviously going to look ugly if we don't do something about it, so i rolled a small function to format queries, instead it should look like this:

q6rVGr.png


^ I don't know about you but i find that very pleasant, looks easier to read at least, avoids long lines and having to concatenate with ".."

db.formatQuery
PHP:
function db.formatQuery(statement, values)
   if statement and values then
      local query = statement
      local key = {}
      local value = {}
      for l in ipairs(values) do
         key[l] = "`"..values[l][1].."`"
         value[l] = values[l][2]
         if type(value[l]) == "number" then
            value[l]=""..value[l]..""
         else
            value[l]="`"..value[l].."`"
         end
      end
      key = table.concat(key, ",")
      value = table.concat(value, ",")
      return query.." ("..key..") VALUES ("..value..")"
   end
end

PHP:
         local hash = sha1(password)
         statement = "INSERT INTO `accounts`"
         values = {{"id", account},
            {"password", hash},
            {"email", ""},
            {"premend", 0},
            {"blocked", 0},
            {"warnings", 0}}
         query = db.formatQuery(statement, values)
         db.executeQuery(query)
 
Maybe create something similar to Laravels eloquent model.
Wrote something fast:

lQxO9Ig.png
 
Maybe create something similar to Laravels eloquent model.
Wrote something fast:

lQxO9Ig.png
That seems a nice approach, i'll probably do it once i figure out how to handle the clone players (account managers) in game without modifying the source code.

Perhaps "allowing_clones" in config.lua all together and restrict using onLogin script?
 
Perhaps "allowing_clones" in config.lua all together and restrict using onLogin script?

I believe you'd have a problem if a player exits the client whilst in combat and then tries to log back in.
 
Maybe create something similar to Laravels eloquent model.
Wrote something fast:

lQxO9Ig.png

HBlimr.png


Account:Create returns the account metatable if created or false if it already exists
Account:Load(id) returns the account metatable if loaded or false if it doesn't exists

Account:getId()
Account:getPassword()
Account:getEmail()
Account:getPremend()
Account:getBlocked()
Account:getWarnings()
Account:setId(id)
Account:setPassword(password)
Account:setEmail(email)
Account:setPremend(premend)
Account:setBlocked(blocked)
Account:setWarnings(warnings)

PHP:
function db.formatQuery(statement, values)
   if statement and values then
      local query = statement.." "
      local key = {}
      local value = {}
      for _,v in ipairs(values) do
         key[_] = "`"..v[1].."`"
         if v[2] then
            value[_] = v[2]
            if type(value[_]) == "number" then
               value[_]=""..v[2]..""
            else
               value[_]="'"..escapeString(v[2]).."'"
            end
         end
      end
      key = table.concat(key, ",")
      query = query.."("..key..")"
      if #value > 0 then
         value = table.concat(value, ",")
         query = query.." VALUES ("..value..")"
      end
      return query..";"
   end
end

function createClass(parent)
   local newClass = {}
   function newClass:new(instance)
      local instance = instance or {}
      setmetatable(instance, {__index = newClass})
      return instance
   end

   if(parent ~= nil) then
      setmetatable(newClass, {__index = parent})
   end

   function newClass:getSelf()
      return newClass
   end

   function newClass:getParent()
      return baseClass
   end

   function newClass:isa(class)
      local tmp = newClass
      while(tmp ~= nil) do
         if(tmp == class) then
            return true
         end

         tmp = tmp:getParent()
      end

      return false
   end

   function newClass:setAttributes(attributes)
      for k, v in pairs(attributes) do
         newClass[k] = v
      end
   end

   return newClass
end

Account = createClass(nil)
Account:setAttributes({
      id = -1,
      password = "",
      email = "",
      premend = 0,
      blocked = 0,
      warnings = 0})

function Account:getId()
   return self.id
end

function Account:getPassword()
   return self.password
end

function Account:getEmail()
   return self.email
end

function Account:getPremend()
   return self.premend
end

function Account:getBlocked()
   return self.blocked
end

function Account:getWarnings()
   return self.warnings
end

function Account:setId(_id)
   self.id = _id
end

function Account:setPassword(_password)
   self.password = _password
end

function Account:setEmail(_email)
   self.email = _email
end

function Account:setPremend(_premend)
   self.premend = _premend
end

function Account:setBlocked(_blocked)
   self.blocked = _blocked
end

function Account:setWarnings(_warnings)
   self.warnings = _warnings
end

function Account:Load(id)
   if not id then
      return false
   end
   local account = db.getResult("SELECT * FROM `accounts` WHERE `id` = "..id.. ";")

   if (account:getID() == -1) then
      return false
   else
      local acc = Account:new()
      acc:setAttributes({
            id = id,
            password = account:getDataString("password"),
            email = account:getDataString("email"),
            premend = account:getDataInt("premend"),
            blocked = account:getDataInt("blocked"),
            warnings = account:getDataInt("warnings")})
      account:free()
      return acc
   end
end

function Account:Save()
   local statement = "INSERT INTO `accounts`"
   local values = {{"id", self.id},
      {"password", self.password},
      {"email", self.email},
      {"premend", self.premend},
      {"blocked", self.blocked},
      {"warnings", self.warnings}}
   local query = db.formatQuery(statement, values)
   print(query)
   db.executeQuery(query)
end

function Account:Create(account)
   local acc = Account:new()
   acc:setAttributes(account)
   if Account:Load(acc:getId()) then
      return false
   else
      acc:Save()
      return acc
   end
end

Here's a small talkaction/command to create account with "/cmd account,password"
PHP:
function onSay(cid, words, param)
   if param == '' then
      doPlayerSendTextMessage(cid,MESSAGE_STATUS_CONSOLE_ORANGE, "Command param required.")
      return false
   end

   param = string.explode(param, ",")
   local account = tonumber(param[1])
   local pass = param[2]

   local msg = function(m)
      doPlayerSendTextMessage(cid, MESSAGE_STATUS_CONSOLE_ORANGE, m)
   end
   if account then
      if pass == '' then
         msg("Empty password.")
         return false
      end

      local account = Account:Create({id = account,
                                      password = sha1(pass)})
      if account then
         msg("Created account: "..account:getId()..".")
      else
         msg("Account already exists.")
      end
   else
      msg("Empty account number.")
   end
   return false
end
 
Back
Top