[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
    Likes Received:
    12
    Best Answers:
    0
    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:
    1.  
    2. function db.formatQuery(statement, values)
    3.    if statement and values then
    4.       local query = statement
    5.       local key = {}
    6.       local value = {}
    7.       for l in ipairs(values) do
    8.          key[l] = "`"..values[l][1].."`"
    9.          value[l] = values[l][2]
    10.          if type(value[l]) == "number" then
    11.             value[l]=""..value[l]..""
    12.          else
    13.             value[l]="`"..value[l].."`"
    14.          end
    15.       end
    16.       key = table.concat(key, ",")
    17.       value = table.concat(value, ",")
    18.       return query.." ("..key..") VALUES ("..value..")"
    19.    end
    20.  
    PHP:
    1.  
    2.          local hash = sha1(password)
    3.          statement = "INSERT INTO `accounts`"
    4.          values = {{"id", account},
    5.             {"password", hash},
    6.             {"email", ""},
    7.             {"premend", 0},
    8.             {"blocked", 0},
    9.             {"warnings", 0}}
    10.          query = db.formatQuery(statement, values)
    11.          db.executeQuery(query)
    12.  
     
    whitevo likes this.
  2. Cornex

    Cornex Web Developer Staff Member Global Moderator

    Joined:
    Jun 15, 2008
    Messages:
    3,429
    Likes Received:
    1,124
    Best Answers:
    5
    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
    Likes Received:
    12
    Best Answers:
    0
    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,780
    Likes Received:
    421
    Best Answers:
    6
    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
    Likes Received:
    12
    Best Answers:
    0
    [​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:
    1. function db.formatQuery(statement, values)
    2.    if statement and values then
    3.       local query = statement.." "
    4.       local key = {}
    5.       local value = {}
    6.       for _,v in ipairs(values) do
    7.          key[_] = "`"..v[1].."`"
    8.          if v[2] then
    9.             value[_] = v[2]
    10.             if type(value[_]) == "number" then
    11.                value[_]=""..v[2]..""
    12.             else
    13.                value[_]="'"..escapeString(v[2]).."'"
    14.             end
    15.          end
    16.       end
    17.       key = table.concat(key, ",")
    18.       query = query.."("..key..")"
    19.       if #value > 0 then
    20.         value = table.concat(value, ",")
    21.          query = query.." VALUES ("..value..")"
    22.       end
    23.       return query..";"
    24.    end
    25.  
    26. function createClass(parent)
    27.    local newClass = {}
    28.    function newClass:new(instance)
    29.       local instance = instance or {}
    30.       setmetatable(instance, {__index = newClass})
    31.       return instance
    32.    end
    33.  
    34.    if(parent ~= nil) then
    35.       setmetatable(newClass, {__index = parent})
    36.    end
    37.  
    38.    function newClass:getSelf()
    39.       return newClass
    40.    end
    41.  
    42.    function newClass:getParent()
    43.       return baseClass
    44.    end
    45.  
    46.    function newClass:isa(class)
    47.       local tmp = newClass
    48.       while(tmp ~= nil) do
    49.          if(tmp == class) then
    50.             return true
    51.          end
    52.  
    53.          tmp = tmp:getParent()
    54.       end
    55.  
    56.       return false
    57.    end
    58.  
    59.    function newClass:setAttributes(attributes)
    60.       for k, v in pairs(attributes) do
    61.          newClass[k] = v
    62.       end
    63.    end
    64.  
    65.    return newClass
    66.  
    67. Account = createClass(nil)
    68. Account:setAttributes({
    69.       id = -1,
    70.       password = "",
    71.       email = "",
    72.       premend = 0,
    73.       blocked = 0,
    74.       warnings = 0})
    75.  
    76. function Account:getId()
    77.    return self.id
    78.  
    79. function Account:getPassword()
    80.    return self.password
    81.  
    82. function Account:getEmail()
    83.    return self.email
    84.  
    85. function Account:getPremend()
    86.    return self.premend
    87.  
    88. function Account:getBlocked()
    89.    return self.blocked
    90.  
    91. function Account:getWarnings()
    92.    return self.warnings
    93.  
    94. function Account:setId(_id)
    95.    self.id = _id
    96.  
    97. function Account:setPassword(_password)
    98.    self.password = _password
    99.  
    100. function Account:setEmail(_email)
    101.    self.email = _email
    102.  
    103. function Account:setPremend(_premend)
    104.    self.premend = _premend
    105.  
    106. function Account:setBlocked(_blocked)
    107.    self.blocked = _blocked
    108.  
    109. function Account:setWarnings(_warnings)
    110.    self.warnings = _warnings
    111.  
    112. function Account:Load(id)
    113.    if not id then
    114.       return false
    115.    end
    116.    local account = db.getResult("SELECT * FROM `accounts` WHERE `id` = "..id.. ";")
    117.  
    118.    if (account:getID() == -1) then
    119.       return false
    120.    else
    121.       local acc = Account:new()
    122.       acc:setAttributes({
    123.             id = id,
    124.             password = account:getDataString("password"),
    125.             email = account:getDataString("email"),
    126.             premend = account:getDataInt("premend"),
    127.             blocked = account:getDataInt("blocked"),
    128.             warnings = account:getDataInt("warnings")})
    129.       account:free()
    130.       return acc
    131.    end
    132.  
    133. function Account:Save()
    134.    local statement = "INSERT INTO `accounts`"
    135.    local values = {{"id", self.id},
    136.       {"password", self.password},
    137.       {"email", self.email},
    138.       {"premend", self.premend},
    139.       {"blocked", self.blocked},
    140.       {"warnings", self.warnings}}
    141.    local query = db.formatQuery(statement, values)
    142.    print(query)
    143.    db.executeQuery(query)
    144.  
    145. function Account:Create(account)
    146.    local acc = Account:new()
    147.    acc:setAttributes(account)
    148.    if Account:Load(acc:getId()) then
    149.       return false
    150.    else
    151.       acc:Save()
    152.       return acc
    153.    end

    Here's a small talkaction/command to create account with "/cmd account,password"
    Code (PHP):
    1.  
    2. function onSay(cid, words, param)
    3.    if param == '' then
    4.       doPlayerSendTextMessage(cid,MESSAGE_STATUS_CONSOLE_ORANGE, "Command param required.")
    5.       return false
    6.    end
    7.  
    8.    param = string.explode(param, ",")
    9.    local account = tonumber(param[1])
    10.    local pass = param[2]
    11.  
    12.    local msg = function(m)
    13.       doPlayerSendTextMessage(cid, MESSAGE_STATUS_CONSOLE_ORANGE, m)
    14.    end
    15.    if account then
    16.       if pass == '' then
    17.          msg("Empty password.")
    18.          return false
    19.       end
    20.  
    21.       local account = Account:Create({id = account,
    22.                                       password = sha1(pass)})
    23.       if account then
    24.          msg("Created account: "..account:getId()..".")
    25.       else
    26.          msg("Account already exists.")
    27.       end
    28.    else
    29.       msg("Empty account number.")
    30.    end
    31.    return false
     
    Cornex likes this.

Share This Page

Loading...