[Lua] Format your SQL queries with db.formatQuery

Discussion in 'Mods & Lua Functions' started by LaloHao, Oct 28, 2016.

  1. LaloHao

    LaloHao Member

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

    [​IMG]

    ^ 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?

    [​IMG]

    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:

    [​IMG]

    ^ 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(statementvalues)
       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(statementvalues)
             
    db.executeQuery(query)
     
    whitevo likes this.
  2. Cornex

    Cornex Web Developer Staff Member Global Moderator

    Joined:
    Jun 15, 2008
    Messages:
    3,407
    Maybe create something similar to Laravels eloquent model.
    Wrote something fast:

    [​IMG]
     
    LaloHao likes this.
  3. LaloHao

    LaloHao Member

    Joined:
    Sep 14, 2008
    Messages:
    73
    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?
     
  4. Bogart

    Bogart ...has super panda powers

    Joined:
    Jun 21, 2009
    Messages:
    7,743
    I believe you'd have a problem if a player exits the client whilst in combat and then tries to log back in.
     
  5. LaloHao

    LaloHao Member

    Joined:
    Sep 14, 2008
    Messages:
    73
    [​IMG]

    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(statementvalues)
       if 
    statement and values then
          local query 
    statement.." "
          
    local key = {}
          
    local value = {}
          for 
    _,v in ipairs(values) do
             
    key[_] = "`"..v[1].."`"
             
    if v[2then
                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 ~= nilthen
          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 
    kv 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() == -1then
          
    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(statementvalues)
       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(cidwordsparam)
       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(cidMESSAGE_STATUS_CONSOLE_ORANGEm)
       
    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
     
    Cornex likes this.

Share This Page