Create prepared statements, implement loadFromDatabase()

1 files 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<int>(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;
 }