box-server/common/utils/sqlUtil.lua

local root = {}

local function formatColumnValue(v)
	if type(v) == "string" then
		return string.format("'%s'", v)
	end
	return v
end

function root.is_table_exist(database, tableName)
	return string.format(
		"SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' and table_name ='%s'",
		database,
		tableName
	)
end

function root.create_table(tableName, columnNameOptions, primaryKey, secondaryKeys)
	local str = "CREATE TABLE " .. tableName .. " ("
	for key, value in pairs(columnNameOptions) do
		str = str .. key .. " " .. value .. ","
	end
	if secondaryKeys then
		str = str .. string.format("PRIMARY KEY (%s), %s)", primaryKey, secondaryKeys)
	else
		str = str .. string.format("PRIMARY KEY (%s))", primaryKey)
	end

	str = str .. "DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci;"
	return str
end

function root.get_able_column_options(database, tableName)
	return string.format(
		"select COLUMN_NAME, IS_NULLABLE, COLUMN_TYPE, DATA_TYPE, COLUMN_COMMENT, COLUMN_DEFAULT from information_schema.columns where table_schema = '%s' and table_name = '%s'",
		database,
		tableName
	)
end

function root.alter_column_type(tableName, columnName, ops)
	return string.format("alter table %s alter column %s %s;", tableName, columnName, ops)
end

function root.alter_table_comment(tableName, commment)
	return string.format("alter table %s comment %s;", tableName, commment)
end

function root.alter_table_add_column(tableName, columnName, ops)
	return string.format("alter table %s add %s %s;", tableName, columnName, ops)
end

function root.select_table(tableName)
	return string.format("select * from %s;", tableName)
end

function root.select_table_by_key(tableName, key, value)
	return string.format("select * from %s where %s = %s;", tableName, key, formatColumnValue(value))
end

function root.insert(tableName, row)
	local columns, values = {}, {}
	for k, v in pairs(row) do
		table.insert(columns, k)
		table.insert(values, formatColumnValue(v))
	end

	return string.format(
		"insert into %s(%s) values(%s);",
		tableName,
		table.concat(columns, ","),
		table.concat(values, ",")
	)
end

function root.insert_or_update_data(tableName, row, key)
	local sql = root.insert(tableName, row)
	if string.sub(sql, #sql) == ";" then
		sql = string.sub(sql, 1, #sql - 1)
	end

	local updates = {}
	for k, v in pairs(row) do
		table.insert(updates, string.format("%s=%s", k, formatColumnValue(v)))
	end
	return string.format("%s on duplicate key update %s;", sql, table.concat(updates, ","))
end

function root.delete_by_key(tableName, key, value)
	return string.format("delete from %s where %s = %s;", tableName, key, formatColumnValue(value))
end

function root.update_data(tableName, row, key, value)
	local body = {}
	for k, v in pairs(row) do
		table.insert(body, string.format("%s=%s", k, formatColumnValue(v)))
	end

	local setvalues = table.concat(body, ",")

	return string.format("update %s set %s where %s =%s;", tableName, setvalues, key, formatColumnValue(value))
end

function root.getMax(tableName, key)
	return string.format("select max(%s) as %s from %s", key, key, tableName)
end

function root.replace(tableName, row)
	local columns, values = {}, {}
	for k, v in pairs(row) do
		table.insert(columns, k)
		table.insert(values, formatColumnValue(v))
	end

	return string.format(
		"replace into %s (%s) values(%s)",
		tableName,
		table.concat(columns, ","),
		table.concat(values, ",")
	)
end

function root.escape_sql(sql)
	local result = ""

	for i = 1, string.len(sql) do
		local escape = nil
		local char_num = string.byte(string.sub(sql, i, i))

		if char_num == 0 then
			escape = "0"
		elseif char_num == string.byte("'") then
			escape = "'"
		elseif char_num == string.byte('"') then
			escape = '"'
		elseif char_num == string.byte("\b") then
			escape = "b"
		elseif char_num == string.byte("\n") then
			escape = "n"
		elseif char_num == string.byte("\r") then
			escape = "r"
		elseif char_num == string.byte("\t") then
			escape = "t"
		elseif char_num == 26 then
			escape = "Z"
		elseif char_num == string.byte("\\") then
			escape = "\\"
		end

		if escape then
			result = result .. "\\" .. escape
		else
			result = result .. string.sub(sql, i, i)
		end
	end

	return result
end

function root.is_database_exist(dbName)
	return string.format("show databases like '%s'", dbName)
end

function root.create_catabase(dbName)
	return string.format(
		"create database if not exists '%s' default character set utf8mb4 default collate utf8mb4_bin;",
		dbName
	)
end

return root