Facepunch
sql.QueryTyped
Details
This closes:
- https://github.com/Facepunch/garrysmod-requests/issues/2489
- https://github.com/Facepunch/garrysmod-issues/issues/6023
- https://github.com/Facepunch/garrysmod-issues/issues/5105
It represents all types correctly in Lua:
- TEXT
- INTEGER
- FLOAT
- BOOL/BOOLEAN
- BLOB
- NULL
except INTEGER is pushed as a string if it exceeds 9007199254740991. (for negatives too)
It also allows binding parameters to be sent without having to escape them, can be used like this:
sql.QueryTyped([[INSERT INTO test (nill, b1, b2, i, f, d, s, bloby) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]], nil, true, false, 1, 2.3, 4.5, "s", "ok\0ks")
#include <GarrysMod/Lua/Interface.h>
#include "sqlite/sqlite3.h"
#include <string>
using namespace GarrysMod;
static sqlite3* GMOD_DB;
const uint64_t LUA_NUMBER_MAX_SAFE_INTEGER = 9007199254740991;
static inline char toLowerAscii(char c)
{
return (c >= 'A' && c <= 'Z') ? (c | 32) : c;
}
// simple and fast to check if column type is bool or not
// this is to mimic sqlite's behaviour with default types, as it can accept `INTEGER` and `integer` just fine
static inline bool isBoolOrBoolean(const char* s)
{
size_t len = std::strlen(s);
if (len != 4 && len != 7)
return false;
const char* ref = (len == 4) ? "bool" : "boolean";
for (size_t i = 0; i < len; ++i) {
if (toLowerAscii(s[i]) != ref[i])
return false;
}
return true;
}
int handleError(Lua::ILuaBase* LUA, const char* err)
{
LUA->PushSpecial(Lua::SPECIAL_GLOB);
LUA->GetField(-1, "sql");
LUA->PushString(err, strlen(err));
LUA->SetField(-2, "m_strError");
LUA->Pop();
LUA->Pop();
LUA->PushBool(false);
return 1;
}
LUA_FUNCTION(QueryTyped)
{
const char* query = LUA->CheckString(1);
sqlite3_stmt* stmt;
int rc = sqlite3_prepare_v2(GMOD_DB, query, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
return handleError(LUA, sqlite3_errmsg(GMOD_DB));
}
// count lua arguments (excluding the query) and verify they match the number of sqlite parameters
int luaParamCount = LUA->Top() - 1;
int expectedParamCount = sqlite3_bind_parameter_count(stmt);
if (expectedParamCount > luaParamCount) {
sqlite3_finalize(stmt);
return handleError(LUA, "incorrect number of parameters provided");
}
if (expectedParamCount > 0) {
for (int bindIndex = 1; bindIndex <= expectedParamCount; ++bindIndex) {
int luaIndex = bindIndex + 1; // lua argument 2 maps to SQLite param 1
int paramType = LUA->GetType(luaIndex);
switch (paramType) {
case Lua::Type::NUMBER: {
double num = LUA->GetNumber(luaIndex);
double intPart;
if (modf(num, &intPart) == 0.0) {
// number is a pure integer; bind as integer
rc = sqlite3_bind_int64(stmt, bindIndex, static_cast<sqlite3_int64>(intPart));
}
else {
// number has fractional part; bind as double
rc = sqlite3_bind_double(stmt, bindIndex, num);
}
break;
}
case Lua::Type::BOOL: {
bool b = LUA->GetBool(luaIndex);
rc = sqlite3_bind_int(stmt, bindIndex, b ? 1 : 0);
break;
}
case Lua::Type::STRING: {
unsigned int len = 0;
const char* str = LUA->GetString(luaIndex, &len);
rc = sqlite3_bind_text(stmt, bindIndex, str, static_cast<int>(len), SQLITE_TRANSIENT);
break;
}
case Lua::Type::NIL: {
rc = sqlite3_bind_null(stmt, bindIndex);
break;
}
default:
sqlite3_finalize(stmt);
return handleError(LUA, "unsupported parameter type for binding");
}
if (rc != SQLITE_OK) {
sqlite3_finalize(stmt);
return handleError(LUA, sqlite3_errmsg(GMOD_DB));
}
}
}
LUA->CreateTable(); // local rows = {}
int rowIndex = 1; // local rowIndex = 1
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
int colCount = sqlite3_column_count(stmt);
LUA->PushNumber(rowIndex++);
LUA->CreateTable(); // local columns = {}
for (int i = 0; i < colCount; i++) {
const char* colName = sqlite3_column_name(stmt, i);
switch (sqlite3_column_type(stmt, i)) {
case SQLITE_FLOAT: {
double value = sqlite3_column_double(stmt, i);
LUA->PushNumber(value);
break;
}
case SQLITE_INTEGER: {
sqlite3_int64 intValue = sqlite3_column_int64(stmt, i);
const char* declType = sqlite3_column_decltype(stmt, i);
if (declType && isBoolOrBoolean(declType)) {
LUA->PushBool(intValue != 0);
}
else if (intValue >= -LUA_NUMBER_MAX_SAFE_INTEGER && intValue <= LUA_NUMBER_MAX_SAFE_INTEGER) { // anything higher than (2 ^ 53) - 1 should just be sent as a string
LUA->PushNumber(static_cast<double>(intValue));
}
else {
char buffer[64]; // buffer large enough for a 64-bit integer, to avoid heap allocation
int length = std::snprintf(buffer, sizeof(buffer), "%lld", static_cast<long long>(intValue));
LUA->PushString(buffer, length);
}
break;
}
case SQLITE_TEXT:
case SQLITE_BLOB: {
const void* data = sqlite3_column_blob(stmt, i);
int data_size = sqlite3_column_bytes(stmt, i);
if (data && data_size > 0) {
LUA->PushString((const char*)data, data_size);
}
else {
// fallback to nil if no valid data, this shouldn't happen but to be safe
LUA->PushNil();
}
break;
}
case SQLITE_NULL:
LUA->PushNil();
break;
}
LUA->SetField(-2, colName); // columns[colName] = value
}
LUA->SetTable(-3); // rows[rowIndex] = columns
}
if (rc != SQLITE_DONE) {
sqlite3_finalize(stmt);
LUA->Pop(); // pop row table
return handleError(LUA, sqlite3_errmsg(GMOD_DB));
}
sqlite3_finalize(stmt);
return 1;
}
GMOD_MODULE_OPEN()
{
if (sqlite3_open(":memory:", &GMOD_DB) != SQLITE_OK) {
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(GMOD_DB));
const char* err = sqlite3_errmsg(GMOD_DB);
LUA->PushSpecial(Lua::SPECIAL_GLOB);
LUA->GetField(-1, "print");
LUA->PushString(err, strlen(err));
LUA->PCall(1, 0, 0);
LUA->Pop();
return 0;
}
LUA->PushSpecial(Lua::SPECIAL_GLOB);
LUA->GetField(-1, "sql");
LUA->PushCFunction(QueryTyped);
LUA->SetField(-2, "QueryTyped");
LUA->Pop();
LUA->Pop();
return 0;
}
GMOD_MODULE_CLOSE()
{
return 0;
}
The only other difference between it and sql.Query is that it doesn't execute multiple statements, I could change it to support them, but it's implementation will need to be still different than sql.Query because the latter has an issue where this
print(sql.Query("INSERT hey into bla bla; something fails"))
returns false even though first statement is executed and committed into the database
Added parameters too, which can be used to pass arguments without having to escape them. I think there is a request/issue about it but can't find it.
I have ported this to GMod and after some testing the NULL type keys would be missing from the return table, because you cannot store nil as a table value.
] lua_run sql.Query("CREATE TABLE IF NOT EXISTS test2 ( nill TEXT, b1 BOOL, b2 BOOLEAN, i INTEGER, f FLOAT, d DOUBLE, s TEXT, bloby BLOB )")
> sql.Query("CREATE TABLE IF NOT EXISTS test2 ( nill TEXT, b1 BOOL, b2 BOOLEAN, i INTEGER, f FLOAT, d DOUBLE, s TEXT, bloby BLOB )")...
] lua_run PrintTable( sql.QueryTyped([[INSERT INTO test2 (nill, b1, b2, i, f, d, s, bloby) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]], nil, true, false, 2, 2.3, 4.5, "s", "ok\0ks") )
> PrintTable( sql.QueryTyped([[INSERT INTO test2 (nill, b1, b2, i, f, d, s, bloby) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]], nil, true, false, 2, 2.3, 4.5, "s", "ok\0ks") )...
] lua_run PrintTable( sql.QueryTyped([[SELECT * FROM test2 WHERE i = 2]]) )
> PrintTable( sql.QueryTyped([[SELECT * FROM test2 WHERE i = 2]]) )...
[1]:
["b1"] = true
["b2"] = false
["bloby"] = ok
["d"] = 4.5
["f"] = 2.3
["i"] = 2
["s"] = s
] lua_run PrintTable( sql.QueryTyped([[INSERT INTO test2 (nill, b1, b2, i, f, d, s, bloby) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]], "test", true, false, 2, 2.3, 4.5, "s", "ok\0ks") )
> PrintTable( sql.QueryTyped([[INSERT INTO test2 (nill, b1, b2, i, f, d, s, bloby) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]], "test", true, false, 2, 2.3, 4.5, "s", "ok\0ks") )...
] lua_run PrintTable( sql.QueryTyped([[SELECT * FROM test2 WHERE i = 2]]) )
> PrintTable( sql.QueryTyped([[SELECT * FROM test2 WHERE i = 2]]) )...
[1]:
["b1"] = true
["b2"] = false
["bloby"] = ok
["d"] = 4.5
["f"] = 2.3
["i"] = 2
["s"] = s
[2]:
["b1"] = true
["b2"] = false
["bloby"] = ok
["d"] = 4.5
["f"] = 2.3
["i"] = 2
["nill"] = test
["s"] = s
Also I really don't like the idea of "randomly" turning integers into strings. I get why you did it like that, but it doesn't feel right to do it like that. It would just cause issues, especially for people who don't know about this behavior, or simply won't account for it. I don't know what would be a better way though.
About the numbers part, mysqloo been doing that for years and nothing broken, it's something that you can't really go over. The other solution is BigInt support in gmod, and I'm sure that will be a big pain to deal with really. We can just document about that behaviour and call it a day.
-snip- explained the NULL thing in a later comment.
Also about numbers, I don't think it's fair to call it randomly turning integers into strings, because people should be knowing that numbers are doubles in Lua, which makes integers 53 bits. So it should be easy for them to understand if you go over "2^53-1" it will be made into a string because it can't be represented in Lua correctly.
Wait, hold up. The NULL part is done that on purpose, if you want to check if one is NULL you will just check for nil, that's the normal behaviour really.
[1]:
["b1"] = true
["b2"] = false
["bloby"] = ok
["d"] = 4.5
["f"] = 2.3
["i"] = 2
["s"] = s
To check here for the "nill" key, we just do tbl.nill == nil, nothing wrong with marking it as nil because NULL from sqlite is just nil in Lua
I didn't specifically mean that it IS random, just that it might appear random to someone who doesn't know the behind the scenes details of Lua implementations, which would be the majority of people I imagine.
Regardless, as we don't really see a way around this, it is what it is.
I was just trying to convince you, majority of people who are still learning wouldn't use this function anyway till they hit an issue from here :C
Thanks <33