SQL Framework - ShayF 6/2/18

-------------------------------
sql = {
    host = '127.0.0.1',
    port = '3306',
    user = 'mta',
    pass = 'mtasa123',
    database = 'mta'
}
-------------------------------
local db = {}
db.connection = dbConnect('mysql','dbname='..sql.database..';host='..sql.host..';charset=utf8',sql.user,sql.pass,'share=0')
local dataTypes = {{'string','TEXT'},{'number','FLOAT'},{'boolean','BOOLEAN'}}

function getDataType(data)
    if data then
        for i,v in pairs(dataTypes) do
            if v[1] == type(data) then
                return v[1],v[2],data
            end
        end
    end
end

function convert(data)
    local t,_,d = getDataType(data)
    if t == 'string' then
        return tostring(data)
    elseif t == 'number' then
        return tonumber(data)
    elseif t == 'boolean'then
        return d and 1 or 0
    end
end

function db.query(callback,query,...)
    if query and type(query) == 'string' and db.connection then
        if not arg[1] then arg = {} end
        dbQuery(callback,db.connection,query,unpack(arg))
    end
end

function db.createTable(name,data)
    if name and type(name) == 'string' and data and type(data) == 'table' and db.connection then
        local s = 'CREATE TABLE IF NOT EXISTS `??` ('
        local add = {}
        local newT = {}

        for k,v in pairs(data) do
            table.insert(add,'`??` ??')
            table.insert(newT,k)
            table.insert(newT,v)
        end

        local newS = s..table.concat(add,',')..');'
        dbExec(db.connection,newS,name,unpack(newT))
        print(newS)
        iprint(newT)
    end
end
--db.createTable('awsomeness',{name='TEXT',number='FLOAT',bool='BOOLEAN'})

function db.add(name,items)
    if name and items and type(items) == 'table' and db.connection then
        local s = 'REPLACE INTO `??` ('
        local columnStrings,valueStrings = {},{}
        local pack = {}

        for k,v in pairs(items) do
            table.insert(columnStrings,'`??`')
            table.insert(valueStrings,'?')
            table.insert(pack,k)
        end

        for o,e in pairs(items) do
            table.insert(pack,convert(e))
        end

        local newS = s..table.concat(columnStrings,',')..') VALUES ('..table.concat(valueStrings,',')..');'
        dbExec(db.connection,newS,name,unpack(pack))
        print(newS)
        iprint(pack)
    end
end
--db.add('awsomeness',{name='Shay',number=12345,bool=true})

function db.update(name,items,where)
    if name and type(name) == 'string' and items and type(items) == 'table' and db.connection then
        local s = 'UPDATE `??` SET '
        local add = {}
        local newT = {}
        local ws = {}
        local w = ''

        for k,v in pairs(items) do
            table.insert(add,'`??`=?')
            table.insert(newT,k)
            table.insert(newT,convert(v))
        end

        if where and type(where) == 'table' then
            w = ' WHERE '
            for e,r in pairs(where) do
                table.insert(ws,'`??`=?')
                table.insert(newT,e)
                table.insert(newT,convert(r))
            end
        end

        local newS = s..table.concat(add,',')..w..table.concat(ws,' AND ')..';'
        dbExec(db.connection,newS,name,unpack(newT))
    end
end
--db.update('awsomeness',{name='iLoay'},{name='Shay',number=12345})