From 5fd1519b8d7ee69c9decb7d076d2072af2fc4978 Mon Sep 17 00:00:00 2001 From: nwrl Date: Thu, 7 Aug 2025 14:37:26 -0500 Subject: Create prepared statements, implement loadFromDatabase() --- nameblocker.sp | 176 ++++++++++++++++++++++++++++++++++++++------------------- 1 file changed, 117 insertions(+), 59 deletions(-) diff --git a/nameblocker.sp b/nameblocker.sp index b9f1e7a..9e16b97 100644 --- a/nameblocker.sp +++ b/nameblocker.sp @@ -64,8 +64,12 @@ enum OperatingMode { ArrayList regexlist; ArrayList patternlist; -Database db; -static const char TABLENAME[] = "163687013_SMNameBlocker"; +Database db; static const char DBTABLENAME[] = "163687013_SMNameBlocker"; +DBStatement dbInsert; static const char DBINSERTSTATEMENT[] = "INSERT OR IGNORE INTO ? (regexstr, steamid64) VALUES (?, ?);"; +DBStatement dbDelete; static const char DBDELETESTATEMENT[] = "DELETE FROM ? WHERE regexstr=?;"; +DBStatement dbReplace; static const char DBREPLACESTATEMENT[] = "UPDATE OR IGNORE ? SET regexstr=?, steamid64=? WHERE regexstr=?;"; +DBStatement dbPopulate; static const char DBPOPULATESTATEMENT[] = "SELECT regexstr FROM ?"; + ConVar gcvarOperMode; static const char OPERMODENAME[] = "nameblock_OperatingMode"; const OperatingMode DEFAULTOPERMODE = OP_KICK; ConVar gcvarAdmCmdFlag; static const char ADMCMDFLAGNAME[] = "nameblock_AdminCommandFlag"; const int DEFAULTADMCMDFLAG = ADMFLAG_BAN; @@ -118,8 +122,92 @@ void xRegisterIntConVar(ConVar& cv, int defaultVal, const char[] name, const cha +int initPrepStatements() { + char sqlerr[256 + 1]; + if((dbInsert = SQL_PrepareQuery(db, DBINSERTSTATEMENT, sqlerr, sizeof(sqlerr))) == null) + logAndFail("Could not prepare insert statement: %s", sqlerr); + if((dbDelete = SQL_PrepareQuery(db, DBDELETESTATEMENT, sqlerr, sizeof(sqlerr))) == null) + logAndFail("Could not prepare delete statement: %s", sqlerr); + if((dbReplace = SQL_PrepareQuery(db, DBREPLACESTATEMENT, sqlerr, sizeof(sqlerr))) == null) + logAndFail("Could not prepare replace statement: %s", sqlerr); + if((dbPopulate = SQL_PrepareQuery(db, DBPOPULATESTATEMENT, sqlerr, sizeof(sqlerr))) == null) + logAndFail("Could not prepare populate statement: %s", sqlerr); + + // This might not work / I might have to use Format() instead of binding. We will see + if(SQL_BindParamString(dbInsert, 0, DBTABLENAME, true)) { + SQL_GetError(dbInsert, sqlerr, sizeof(sqlerr)); + logAndFail("Could not bind tablename to insert statement: %s", sqlerr); + } + if(SQL_BindParamString(dbDelete, 0, DBTABLENAME, true)) { + SQL_GetError(dbDelete, sqlerr, sizeof(sqlerr)); + logAndFail("Could not bind tablename to delete statement: %s", sqlerr); + } + if( SQL_BindParamString(dbReplace, 0, DBTABLENAME, true)) { + SQL_GetError(dbReplace, sqlerr, sizeof(sqlerr)); + logAndFail("Could not bind tablename to replace statement: %s", sqlerr); + } + if(SQL_BindParamString(dbPopulate, 0, DBTABLENAME, true)) { + SQL_GetError(dbPopulate, sqlerr, sizeof(sqlerr)); + logAndFail("Could not bind tablename to populate statement: %s", sqlerr); + } + + // If I knew how to do macros this would be much nicer + + return 0; +} + +int loadFromDatabase() { + // Get database handle + char sqlerr[256 + 1]; + db = SQLite_UseDatabase("sourcemod-local", sqlerr, sizeof(sqlerr)); + if(db == null) logAndFail("Could not connect to sql database: %s", sqlerr); + + // Initialize table if it doesn't exist + // I could make this a prepared statement, but I don't believe it's entirely necessary + char sqlcbuf[256 + 1]; + Format(sqlcbuf, sizeof(sqlcbuf), "CREATE TABLE IF NOT EXISTS \"%s\" (id INTEGER NOT NULL, regexstr TEXT NOT NULL ON CONFLICT IGNORE, steamid64 TEXT NOT NULL ON CONFLICT IGNORE, dateof TEXT DEFAULT CURRENT_DATE, timeof TEXT DEFAULT CURRENT_TIME, PRIMARY KEY (id), UNIQUE (regexstr));", DBTABLENAME); + if(!SQL_FastQuery(db, sqlcbuf)) { + SQL_GetError(db, sqlerr, sizeof(sqlerr)); + logAndFail("Could not initialize nameblocker table: %s", sqlerr); + } + + // Initialize and populate datatypes + regexlist = new ArrayList(ByteCountToCells(HANDLE_SIZE)); + if(regexlist == null) logAndFail("Could not initialize regexlist ArrayList"); + + patternlist = new ArrayList(ByteCountToCells(PATTERN_MAX_LEN)); + if(patternlist == null) logAndFail("Could not initialize patternlist ArrayList"); + + // select patterns from nameblock table/database + if(SQL_Execute(dbPopulate)) { + SQL_GetError(dbPopulate, sqlerr, sizeof(sqlerr)); + logAndFail("Population query failed"); + } + + // compile each pattern & insert + Regex cur; char reerr[256]; RegexError reenum; + for(char pattern[PATTERN_MAX_LEN]; SQL_FetchRow(dbPopulate);) { + SQL_FetchString(dbPopulate, 1, pattern, sizeof(pattern)); + cur = CompileRegex(pattern, gcvarRegexCompFlags, reerr, sizeof(reerr), reenum); + if(cur == null) { + LogError("Could not compile regex pattern \"%s\": %s (%d)", pattern, reerr, reenum); + continue; + } + + if(aInsertPattern(pattern, cur, patternlist.Length)) { + LogError("Couldn't add regex \"%s\" to arraylists, continuing", pattern); + CloseHandle(cur); + } + } + + return 0; +} + + + public void OnAllPluginsLoaded() { - if(loadFromDatabase()) logAndFail(DATABASE_FAIL_MSG); + initPrepStatements(); + loadFromDatabase(); // Register convars xRegisterIntConVar(gcvarOperMode, view_as(DEFAULTOPERMODE), OPERMODENAME, "Operating mode (disabled, kick, ban, etc.)"); @@ -206,8 +294,8 @@ public Action cmdReplacePattern(int client, int args) { return Plugin_Handled; } - if(modPattern(DBM_INSERT | DBM_DELETE, index, pattern)) { - ReplyToCommand(client, "Error: could not remove pattern from list"); + if(modPattern(DBM_REPLACE, index, pattern)) { + ReplyToCommand(client, "Error: could not replace pattern in list"); return Plugin_Handled; } @@ -224,37 +312,11 @@ public Action cmdListPatterns(int client, int args) { -int aInsertPattern(char pattern[PATTERN_MAX_LEN], Regex res, int index) { - if(IsNullString(pattern) || index < 0 || index > regexlist.Length) return -1; - - if(index == regexlist.Length) { - regexlist.Push(res); - patternlist.Push(pattern); - - } else { - regexlist.ShiftUp(index); - patternlist.ShiftUp(index); - regexlist.Set(index, res); - patternlist.Set(index, pattern); - - } - - return 0; -} - -int aRemovePattern(int index) { - if(index < 0 || index >= regexlist.Length) return -1; - - regexlist.Erase(index); - patternlist.Erase(index); - - return 0; -} - enum DBMOD_MODE { DBM_UNDEF, - DBM_INSERT = (1<<0), - DBM_DELETE = (1<<1), + DBM_INSERT, + DBM_DELETE, + DBM_REPLACE, DBM_TOOBIG }; @@ -262,8 +324,10 @@ int modPattern(DBMOD_MODE mode, int index, char[] pattern="") { if(index < 0 || index > regexlist.Length) return -1; if(mode <= DBM_UNDEF || mode >= DBM_TOOBIG) return -1; - Transaction modification = SQL_CreateTransaction(); - if(modification == null) return -1; + Transaction dbmod = SQL_CreateTransaction(); + if(dbmod == null) return -1; + + // Update transaction // char errstr[512]; RegexError reerr; // Regex res = CompileRegex(pattern, gcvarRegexCompFlags.IntValue, errstr, sizeof(errstr), reerr); @@ -272,41 +336,35 @@ int modPattern(DBMOD_MODE mode, int index, char[] pattern="") { // return -1; // } - // Update transaction // Update lists // Try to delete before inserting return 0; } -int loadFromDatabase() { - logAndFail("function \"loadFromDatabase\" not implemented"); - - // Get database handle - char sqlerr[256 + 1]; - db = SQLite_UseDatabase("sourcemod-local", sqlerr, sizeof(sqlerr)); - if(db == null) logAndFail("Could not connect to sql database: %s", sqlerr); +int aInsertPattern(char pattern[PATTERN_MAX_LEN], Regex res, int index) { + if(IsNullString(pattern) || res == null || index < 0 || index > regexlist.Length) return -1; - // Initialize table if it doesn't exist - char sqlcbuf[256 + 1]; - Format(sqlcbuf, sizeof(sqlcbuf), "CREATE TABLE IF NOT EXISTS \"%s\" (id INTEGER NOT NULL, regexstr TEXT NOT NULL ON CONFLICT IGNORE, steamid64 TEXT NOT NULL ON CONFLICT IGNORE, dateof TEXT DEFAULT CURRENT_DATE, timeof TEXT DEFAULT CURRENT_TIME, PRIMARY KEY (id), UNIQUE (regexstr));", TABLENAME); - if(!SQL_FastQuery(db, sqlcbuf)) { - SQL_GetError(db, sqlerr, sizeof(sqlerr)); - logAndFail("Could not initialize nameblocker table: %s", sqlerr); - } + if(index == regexlist.Length) { + regexlist.Push(res); + patternlist.Push(pattern); - // Initialize and populate datatypes - regexlist = new ArrayList(ByteCountToCells(HANDLE_SIZE)); - if(regexlist == null) logAndFail("Could not initialize regexlist ArrayList"); + } else { + regexlist.ShiftUp(index); + patternlist.ShiftUp(index); + regexlist.Set(index, res); + patternlist.Set(index, pattern); - patternlist = new ArrayList(ByteCountToCells(PATTERN_MAX_LEN)); - if(patternlist == null) logAndFail("Could not initialize patternlist ArrayList"); + } - // select patterns from nameblock table/database + return 0; +} +int aRemovePattern(int index) { + if(index < 0 || index >= regexlist.Length) return -1; - // compile each pattern - // insert pattern & regex into respective lists + regexlist.Erase(index); + patternlist.Erase(index); return 0; } -- cgit v1.2.3