[Klasse] Sqlbuilder Klasse

Shyim

Hallo liebe Community,

hiermit realease ich meine Sqlbuilder Klasse. Sie dient erfolgreich für Schreibfaule menschen die keine Lust haben ihre Querys immer selber zumachen.

Code:

Lua

SqlBuilder = {}
SqlBuilder.__index = SqlBuilder
DESC = "DESC"
ASC = "ASC"


if not mysql_ping then
	function mysql_ping()
		return false
	end
end


function SqlBuilder:new(connection)
	local self = setmetatable({},SqlBuilder)
	self.selects = ""
	self.wheres = ""
	self.wheretable = {}
	self.limit_string = ""
	self.order_by_string = ""
	self.isModule = false
	if (connection and getElementType(connection) == "db-connection" or connection and mysql_ping(connection)) then
		self.connection = connection
		if mysql_ping(connection) then
			self.isModule = true
		end
	else
		self.connection = false
	end
	self.escapeString = function (val)
			if self.isModule then
				return mysql_escape_string(self.connection,val)
			else
				return val
			end
		end
	return self
end


function SqlBuilder:limit(int)
	self.limit_string = "LIMIT "..tostring(int)
end


function SqlBuilder:select(feld)
	if not feld then error("Das erste Argument muss angegeben sein!") return end
	if type(feld) == "table" then
		for _,value in pairs(feld) do 
			self.selects = self.selects..("`%s`,"):format(value)
		end
		self.selects = string.sub(self.selects,0,string.len(self.selects) - 1)
	else
		if (string.len(self.selects) > 2) then
			self.selects = self.selects..(",`%s`"):format(feld)
		else
			self.selects = self.selects..("`%s`"):format(feld)
		end
	end
end


function SqlBuilder:order_by(feld,value)
	if not feld then error("Das erste Argument muss angegeben sein!") return end
	if(self.order_by_string == "") then
		self.order_by_string = "ORDER BY "
	end
	if type(feld) == "table" then
		for name,value in pairs(feld) do 
			if string.upper(value) ~= "ASC" and string.upper(value) ~= "DESC" then
				error("Die Value muss ASC oder DESC sein")
				return false
			end
			self.order_by_string = self.order_by_string..(" `%s` %s,"):format(name,string.upper(value))
		end
		self.order_by_string = string.sub(self.order_by_string,0,string.len(self.order_by_string) - 1)
	else
		if string.upper(value) ~= "ASC" and string.upper(value) ~= "DESC" then
			error("Die Value muss ASC oder DESC sein")
			return false
		end
		if ((string.len(self.order_by_string) -9) > 2) then
			self.order_by_string = self.order_by_string..(" ,`%s` %s"):format(feld,string.upper(value))
		else
			self.order_by_string = self.order_by_string..(" `%s` %s"):format(feld,string.upper(value))
		end
	end
end


function SqlBuilder:where(feld,value)
	if not feld then error("Das erste Argument muss angegeben sein!") return end
	if(self.wheres == "") then
		self.wheres = "WHERE "
	end
	if type(feld) == "table" then
		for name,value in pairs(feld) do
			if self.connection and self.isModule == false then
				self.wheres = self.wheres..(" `%s` = %s AND"):format(name,"?")
			else
				self.wheres = self.wheres..(" `%s` = '%s' AND"):format(name,self.escapeString(value))
			end
			table.insert(self.wheretable,value)
		end
		self.wheres = string.sub(self.wheres,0,string.len(self.wheres) - 3)
	else
		if ((string.len(self.wheres) -6) > 2) then
			if self.connection and self.isModule == false then
				self.wheres = self.wheres..(" AND `%s` = %s"):format(feld,"?")
			else
				self.wheres = self.wheres..(" AND `%s` = '%s'"):format(feld,self.escapeString(value))
			end
		else
			if self.connection and self.isModule == false then
				self.wheres = self.wheres..("`%s` = %s"):format(feld,"?")
			else
				self.wheres = self.wheres..("`%s` = '%s'"):format(feld,self.escapeString(value))
			end
		end
		table.insert(self.wheretable,value)
	end
end


function SqlBuilder:get(tblname)
	if not tblname then error("Es muss ein Tabellenname angegeben sein!") return end
	local output = ""
	if(self.selects == "") then
		output = ("SELECT * FROM `%s` %s %s %s"):format(tblname,self.wheres,self.order_by_string,self.limit_string)
	else
		output = ("SELECT %s FROM `%s` %s %s %s"):format(self.selects,tblname,self.wheres,self.order_by_string,self.limit_string)
	end
	if(self.connection) then
		if(self.isModule) then
			return mysql_query(self.connection,output)
		else
			return dbQuery(self.connection,output,unpack(self.wheretable))
		end
	else
		return output
	end
end


function SqlBuilder:insert(tbl,dataTable)
	if not tbl then error("Es muss ein Tabellenname angegeben sein!") return end
	local spalteString = ""
	local valueString = ""
	local inserttable = {}
	for name,data in pairs(dataTable) do 
		spalteString = spalteString..("`%s`,"):format(name)
		if self.connection and self.isModule == false then
			valueString = valueString..("%s,"):format("?")
			table.insert(inserttable,data)
		else
			valueString = valueString..("'%s',"):format(data)
		end
	end
	spalteString = string.sub(spalteString,0,string.len(spalteString) - 1)
	valueString = string.sub(valueString,0,string.len(valueString) - 1)
	local output = ("INSERT INTO %s (%s) VALUES(%s) "):format(tbl,spalteString,valueString)
	if(self.connection) then
		if(self.isModule) then
			return mysql_query(self.connection,output)
		else
			return dbExec(self.connection,output,unpack(inserttable))
		end
	else
		return output
	end
end


function SqlBuilder:update(tbl,dataTable)
	if not tbl then error("Es muss ein Tabellenname angegeben sein!") return end
	local updateString = ""
	local updateTable = {}


	for name,value in pairs(dataTable) do 
		if self.connection and self.isModule == false then
			updateString = updateString..("`%s` = %s,"):format(name,"?")
			table.insert(updateTable,value)
		else
			updateString = updateString..("`%s` = '%s',"):format(name,self.escapeString(value))
		end
	end
	updateString = string.sub(updateString,0,string.len(updateString) - 1)
	local output = ("UPDATE `%s` SET %s %s"):format(tbl,updateString,self.wheres)
	if(self.connection) then
		if(self.isModule) then
			return mysql_query(self.connection,output)
		else
			for _,val in pairs(self.wheretable) do 
				table.insert(updateTable,val)
			end
			return dbExec(self.connection,output,unpack(updateTable))
		end
	else
		return output
	end
end


function SqlBuilder:delete(tbl)
	if not tbl then error("Es muss ein Tabellenname angegeben sein!") return end


	local output = ("DELETE FROM `%s` %s"):format(tbl,self.wheres)
	if(self.connection) then
		if(self.isModule) then
			return mysql_query(self.connection,output)
		else
			return dbExec(self.connection,output,unpack(self.wheretable))
		end
	else
		return output
	end
end


function SqlBuilder:reset()
	self.selects = ""
	self.wheres = ""
	self.limit_string = ""
	self.order_by_string = ""
end

Alles anzeigen

Beispiele zur Nutzung:

Lua

local builder = SqlBuilder:new();


builder:get("userdata") --return SELECT * FROM userdata


builder:limit(10)
builder:get("userdata") --return SELECT * FROM userdata LIMIT 10


builder:select("Name")
builder:get("userdata") --return SELECT `Name` FROM userdata LIMIT 10


builder:where("Name","Shyim")
builder:get("userdata") --return SELECT `Name` FROM userdata WHERE `Name` = 'Shyim' LIMIT 10


local data = {}
data["Name"] = "TestUser"
data["Geld"] = "500"
builder:insert("userdata",data) --return INSERT INTO userdata (`Name`,`Geld`) VALUES('TestUser','500')


builder:reset() -- resetet alle where,select,orderby,limits


local data = {}
data["Name"] = "TestUser"
data["Geld"] = "100"
builder:update("userdata",data) --UPDATE `userdata` SET `Name` = 'TestUser',`Geld` = '1000'


local data = {}
data["Name"] = "TestUser"
data["Geld"] = "100"
builder:where("Name","Testuser")
builder:update("userdata",data) --UPDATE `userdata` SET `Name` = 'TestUser',`Geld` = '1000' WHERE `Name` = 'TestUser'




builder:delete("userdata") -- `DELETE FROM userdata`

Alles anzeigen

mfg,
Shyim

PS: sry das ich das unter "Nützliche Funktionen" gepostet hab *pinch*

Jetzt mitmachen!