Warrior_EA/Database/DatabaseOperationsManager.mqh

//+------------------------------------------------------------------+
//|                                                       Warrior_EA |
//|                                                     AnimateDread |
//|                                                                  |
//+------------------------------------------------------------------+
#include "..\System\PrintVerbose.mqh"
#define DB_RETRY_ATTEMPTS   3
#define DB_RETRY_DELAY_MS   20
//--- ERR_DATABASE_NO_MORE_DATA: what GetLastError() reports after DatabaseRead() steps a statement to
//--- completion with nothing to read back (SQLite's DONE). For INSERT/UPDATE that is the SUCCESS
//--- outcome, not a failure - see PrepareAndExecuteBound().
#define DB_NO_MORE_DATA     5126
class CDatabaseOperationsManager
  {
private:
   bool              m_verboseMode;
   int               m_databaseHandle;
   // Table/column names can't be bound as SQL parameters, so they must be
   // restricted to a safe character set before being concatenated into a query.
   bool              IsValidIdentifier(const string name)
     {
      int len = StringLen(name);
      if(len == 0)
         return false;
      for(int i = 0; i < len; i++)
        {
         ushort c = StringGetCharacter(name, i);
         bool isAlnum = (c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '_';
         if(!isAlnum)
            return false;
        }
      return true;
     }
   // Shared guard for every public method below: refuse an identifier that failed IsValidIdentifier(),
   // printing which operation was refused (the verb) and which identifier failed.
   bool              RequireValidIdentifier(const string tableName, const string verb)
     {
      if(IsValidIdentifier(tableName))
         return true;
      Print("Refusing to " + verb + " table with invalid identifier: " + tableName);
      return false;
     }
   // Join array elements into one comma-separated string, appending `suffix` to each element
   // first (e.g. suffix "=?" turns column names into an UPDATE ... SET list). Shared by every
   // column/placeholder list built below so the "comma unless this is the last element" shape
   // exists exactly once.
   string            JoinWithCommas(const string &items[], const string suffix = "")
     {
      string result = "";
      int n = ArraySize(items);
      for(int i = 0; i < n; i++)
        {
         result += items[i] + suffix;
         if(i < n - 1)
            result += ", ";
        }
      return result;
     }
   // Build a "?, ?, ..." bound-parameter placeholder list of the given length.
   string            PlaceholderList(int count)
     {
      string items[];
      ArrayResize(items, count);
      for(int i = 0; i < count; i++)
         items[i] = "?";
      return JoinWithCommas(items);
     }
   // Prepare and execute a query that has no bound parameters (DDL, or already-safe DML)
   bool              PrepareAndExecuteQuery(string query)
     {
      if(m_databaseHandle == INVALID_HANDLE)
         return false;
      int attempts = 0;
      while(attempts < DB_RETRY_ATTEMPTS)
        {
         if(DatabaseExecute(m_databaseHandle, query))
            return true;
         Print("DatabaseExecute error: Attempt " + IntegerToString(attempts + 1) + " failed for query: " + query);
         Sleep(DB_RETRY_DELAY_MS);
         attempts++;
        }
      return false;
     }
   // Is this GetLastError() code the kind a retry can actually fix - the DB momentarily busy/locked
   // by another chart on the shared COMMON database (DatabaseConnectionManager.mqh opens it
   // DATABASE_OPEN_COMMON) - versus a genuine defect (constraint, misuse, bad SQL) that will fail
   // identically on attempt 2? Only PrepareAndExecuteQuery had this retry before; every trade-journal
   // row and pattern-weight update went through PrepareAndExecuteBound with none.
   bool              IsRetryableDbError(const int error)
     {
      return (error == ERR_DATABASE_BUSY || error == ERR_DATABASE_LOCKED || error == ERR_DATABASE_CONNECT);
     }
   // Prepare a statement, bind parameters in order, execute it once, and finalize. Retries the WHOLE
   // prepare/bind/step sequence (not just the step) on a busy/locked error, same DB_RETRY_ATTEMPTS/
   // DB_RETRY_DELAY_MS as PrepareAndExecuteQuery - a stale prepared handle from a failed attempt is
   // never reused.
   bool              PrepareAndExecuteBound(string query, const string &params[])
     {
      if(m_databaseHandle == INVALID_HANDLE)
         return false;
      int attempts = 0;
      while(true)
        {
         int handle = DatabasePrepare(m_databaseHandle, query);
         if(handle == INVALID_HANDLE)
           {
            int prepErr = GetLastError();
            if(IsRetryableDbError(prepErr) && attempts + 1 < DB_RETRY_ATTEMPTS)
              {
               attempts++;
               Sleep(DB_RETRY_DELAY_MS);
               continue;
              }
            Print("Failed to prepare bound query: " + query);
            return false;
           }
         bool bindFailed = false;
         for(int i = 0; i < ArraySize(params); i++)
           {
            if(!DatabaseBind(handle, i, params[i]))
              {
               Print("Failed to bind parameter " + IntegerToString(i) + " for query: " + query);
               DatabaseFinalize(handle);
               bindFailed = true;
               break;
              }
           }
         if(bindFailed)
            return false;
         // INSERT/UPDATE have no result set, so DatabaseRead() returns false on
         // success too (nothing to read) - the real signal is the error code. A
         // completed DML step can surface as either 0 or DB_NO_MORE_DATA (5126,
         // SQLite's DONE): the statement RAN, there is just nothing to read back.
         // The tester agent reports 5126 where the live terminal reports 0 for
         // the same completed step; treating it as failure printed a phantom
         // "Failed to insert/update" pair for EVERY row a backtest journaled
         // (~11.7k lines per run) while all of the data landed correctly - and
         // that spam would bury any real error. Genuine failures (constraint,
         // misuse) surface as other codes and still fail here; busy/locked
         // retries the whole statement instead.
         ResetLastError();
         DatabaseRead(handle);
         int error = GetLastError();
         DatabaseFinalize(handle);
         if(error != 0 && error != DB_NO_MORE_DATA)
           {
            if(IsRetryableDbError(error) && attempts + 1 < DB_RETRY_ATTEMPTS)
              {
               attempts++;
               Sleep(DB_RETRY_DELAY_MS);
               continue;
              }
            Print("Failed to execute bound query (error " + IntegerToString(error) + "): " + query);
            return false;
           }
         return true;
        }
      return false; // unreachable - every path through the loop above returns or continues
     }
public:
   //constructor
                     CDatabaseOperationsManager(bool verbose = false)
     {
      m_verboseMode = verbose;
      m_databaseHandle = INVALID_HANDLE;
     }
   void              SetDatabaseHandle(int dbHandle)
     {
      m_databaseHandle = dbHandle;
     }
   // Create a table in the database
   bool              CreateTable(string tableName, string tableSchema)
     {
      if(!RequireValidIdentifier(tableName, "create"))
         return false;
      if(!DatabaseTableExists(m_databaseHandle, tableName))
        {
         string createTableQuery = "CREATE TABLE " + tableName + " (" + tableSchema + ")";
         if(PrepareAndExecuteQuery(createTableQuery))
            return true;
         Print("Failed to create table " + tableName);
         return false;
        }
      return true; // Table already exists
     }
   bool              DeleteTable(string tableName)
     {
      if(!RequireValidIdentifier(tableName, "delete"))
         return false;
      string deleteTableQuery = "DROP TABLE IF EXISTS " + tableName;
      if(PrepareAndExecuteQuery(deleteTableQuery))
        {
         PrintVerbose("Table " + tableName + " deleted successfully.");
         return true;
        }
      Print("Failed to delete table " + tableName);
      return false;
     }
   // Insert a trade record into the database. Column names are our own fixed
   // schema field names (never user data); values are bound as parameters.
   bool              InsertTradeRecord(string tableName, const string &columns[], const string &values[])
     {
      if(!RequireValidIdentifier(tableName, "insert into"))
         return false;
      string insertQuery = "INSERT INTO " + tableName + " (" + JoinWithCommas(columns) +
                            ") VALUES (" + PlaceholderList(ArraySize(columns)) + ")";
      if(PrepareAndExecuteBound(insertQuery, values))
         return true;
      Print("Failed to insert trade record into " + tableName);
      return false;
     }
   struct RecordCount
     {
      int            count;
     };
   // Fetch the number of records in the specified table
   bool              FetchRecordCount(string tableName, int &count)
     {
      if(!RequireValidIdentifier(tableName, "query"))
         return false;
      RecordCount countStruct;
      string countQuery = "SELECT COUNT(*) as count FROM " + tableName;
      int handle = DatabasePrepare(m_databaseHandle, countQuery);
      if(handle == INVALID_HANDLE)
        {
         Print("Failed to prepare COUNT query for " + tableName);
         return false;
        }
      bool ok = DatabaseReadBind(handle, countStruct);
      if(ok)
         count = countStruct.count;
      DatabaseFinalize(handle);
      return ok;
     }
   //--- Targeted lookups. These answer ProcessSignal/UpdateSignalsWeights' questions inside SQLite
   //--- and return one row or one pair of numbers, so per-signal cost stays flat no matter how large
   //--- a table grows. They exist because the original design pulled ENTIRE tables into MQL struct
   //--- arrays for every question - the real constraint behind the historical 1000-row cap: SQLite
   //--- has no such limit, but materializing thousands of string-bearing structs per signal does.
   struct OpenTradeRow
     {
      double         entryPrice;
     };
   // Entry price of the still-open ("NA") trade for pattern+direction, if one exists
   bool              FetchOpenTradeEntry(string tableName, string pattern, string direction,
                                         double &entryPrice, bool &found)
     {
      found = false;
      if(!RequireValidIdentifier(tableName, "query"))
         return false;
      string q = "SELECT entryPrice FROM " + tableName +
                 " WHERE pattern=? AND direction=? AND result='NA' LIMIT 1";
      int handle = DatabasePrepare(m_databaseHandle, q);
      if(handle == INVALID_HANDLE)
        {
         Print("Failed to prepare open-trade query for " + tableName);
         return false;
        }
      if(!DatabaseBind(handle, 0, pattern) || !DatabaseBind(handle, 1, direction))
        {
         Print("Failed to bind open-trade query for " + tableName);
         DatabaseFinalize(handle);
         return false;
        }
      OpenTradeRow row;
      if(DatabaseReadBind(handle, row))
        {
         entryPrice = row.entryPrice;
         found = true;
        }
      DatabaseFinalize(handle);
      return true;
     }
   struct TimeKeyRow
     {
      long           k;
     };
   // Time key (yyyymmddhhmm as a number) of the table's NEWEST row. Rows are inserted in
   // chronological order, so max ROWID carries the latest timestamp. `quiet` suppresses the
   // prepare-failure print so existence probes over possibly-absent tables don't spam the log.
   bool              FetchNewestTimeKey(string tableName, long &key, bool &found, const bool quiet = false)
     {
      found = false;
      if(!RequireValidIdentifier(tableName, "query"))
         return false;
      string q = "SELECT (((year*100+month)*100+day)*100+hour)*100+minutes AS k FROM " +
                 tableName + " ORDER BY ROWID DESC LIMIT 1";
      int handle = DatabasePrepare(m_databaseHandle, q);
      if(handle == INVALID_HANDLE)
        {
         if(!quiet)
            Print("Failed to prepare newest-time query for " + tableName);
         return false;
        }
      TimeKeyRow row;
      if(DatabaseReadBind(handle, row))
        {
         key = row.k;
         found = true;
        }
      DatabaseFinalize(handle);
      return true;
     }
   struct WinLossRow
     {
      int            wins;
      int            losses;
     };
   // Closed-trade outcome counts with open time strictly before beforeKey (yyyymmddhhmm). The bound
   // is what keeps a resumed/mixed database's not-yet-current rows out of the weights in the tester.
   bool              FetchWinLossCounts(string tableName, long beforeKey, int &wins, int &losses)
     {
      wins = 0;
      losses = 0;
      if(!RequireValidIdentifier(tableName, "query"))
         return false;
      string q = "SELECT COALESCE(SUM(result='Profit'),0), COALESCE(SUM(result='Loss'),0) FROM " +
                 tableName + " WHERE result!='NA' AND (((year*100+month)*100+day)*100+hour)*100+minutes < " +
                 IntegerToString(beforeKey);
      int handle = DatabasePrepare(m_databaseHandle, q);
      if(handle == INVALID_HANDLE)
        {
         Print("Failed to prepare win-loss query for " + tableName);
         return false;
        }
      WinLossRow row;
      bool ok = DatabaseReadBind(handle, row);
      if(ok)
        {
         wins = row.wins;
         losses = row.losses;
        }
      DatabaseFinalize(handle);
      return ok;
     }
   // Fetch trade records from specified table and read into a structure, then populate the dynamic array with the data
   template <typename T>
   bool              FetchTradeRecords(string tableName, T &tradeRecordStructure, T &tradeRecords[])
     {
      if(!RequireValidIdentifier(tableName, "query"))
         return false;
      int recordCount;
      if(!FetchRecordCount(tableName, recordCount))
         return false;
      ArrayResize(tradeRecords, recordCount);
      string selectQuery = "SELECT * FROM " + tableName;
      int handle = DatabasePrepare(m_databaseHandle, selectQuery);
      if(handle == INVALID_HANDLE)
        {
         Print("Failed to prepare SELECT query for " + tableName);
         return false;
        }
      int i = 0;
      for(; i < recordCount && DatabaseReadBind(handle, tradeRecordStructure); i++)
         tradeRecords[i] = tradeRecordStructure;
      DatabaseFinalize(handle);
      if(i < recordCount)
        {
         // The read loop can exit early (a concurrent delete on the shared COMMON db between the
         // COUNT and SELECT above, or a transient read failure) - trim the array to what was
         // actually read instead of leaving trailing zero/empty-string structs that downstream
         // reporting would otherwise silently count as real (empty-symbol, 0-profit) trades.
         Print(__FUNCTION__ + ": only read " + IntegerToString(i) + " of " + IntegerToString(recordCount) +
               " expected rows from " + tableName + " - trimming to actual rows read.");
         ArrayResize(tradeRecords, i);
        }
      return true;
     }
   // Update the exit price/result of the still-open ("NA") trade matching pattern+direction
   bool              UpdateTradeRecord(string tableName, const string &columns[], const string &values[], string pattern, string direction)
     {
      if(!RequireValidIdentifier(tableName, "update"))
         return false;
      string updateQuery = "UPDATE " + tableName + " SET " + JoinWithCommas(columns, "=?") +
                            " WHERE pattern=? AND direction=? AND result='NA'";
      string params[];
      ArrayResize(params, ArraySize(values) + 2);
      for(int i = 0; i < ArraySize(values); i++)
         params[i] = values[i];
      params[ArraySize(values)] = pattern;
      params[ArraySize(values) + 1] = direction;
      if(PrepareAndExecuteBound(updateQuery, params))
         return true;
      Print("Failed to update trade records in " + tableName);
      return false;
     }
   // Delete the oldest entry from a specified table
   bool              DeleteOldestEntry(string tableName)
     {
      if(!RequireValidIdentifier(tableName, "delete from"))
         return false;
      string deleteQuery = "DELETE FROM " + tableName + " WHERE ROWID = (SELECT MIN(ROWID) FROM " + tableName + ")";
      if(PrepareAndExecuteQuery(deleteQuery))
        {
         PrintVerbose("Oldest entry deleted from " + tableName + " successfully.");
         return true;
        }
      Print("Failed to delete oldest entry from " + tableName);
      return false;
     }
  };
//+------------------------------------------------------------------+