Warrior_EA/Database/DatabaseOperationsManager.mqh

//+------------------------------------------------------------------+
//|                                                       Warrior_EA |
//|                                                     AnimateDread |
//|                                                                  |
//+------------------------------------------------------------------+
#include "..\System\PrintVerbose.mqh"
#define DB_RETRY_ATTEMPTS   3
#define DB_RETRY_DELAY_MS   20
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;
     }
   // 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;
     }
   // Prepare a statement, bind parameters in order, execute it once, and finalize
   bool              PrepareAndExecuteBound(string query, const string &params[])
     {
      if(m_databaseHandle == INVALID_HANDLE)
         return false;
      int handle = DatabasePrepare(m_databaseHandle, query);
      if(handle == INVALID_HANDLE)
        {
         Print("Failed to prepare bound query: " + query);
         return 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);
            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.
      ResetLastError();
      DatabaseRead(handle);
      int error = GetLastError();
      DatabaseFinalize(handle);
      if(error != 0)
        {
         Print("Failed to execute bound query (error " + IntegerToString(error) + "): " + query);
         return false;
        }
      return true;
     }
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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to create table with invalid identifier: " + tableName);
         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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to delete table with invalid identifier: " + tableName);
         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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to insert into table with invalid identifier: " + tableName);
         return false;
        }
      string insertQuery = "INSERT INTO " + tableName + " (";
      string placeholders = "";
      for(int i = 0; i < ArraySize(columns); i++)
        {
         insertQuery += columns[i];
         placeholders += "?";
         if(i < ArraySize(columns) - 1)
           {
            insertQuery += ", ";
            placeholders += ", ";
           }
        }
      insertQuery += ") VALUES (" + placeholders + ")";
      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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to query table with invalid identifier: " + tableName);
         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;
     }
   // 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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to query table with invalid identifier: " + tableName);
         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;
        }
      for(int i = 0; i < recordCount && DatabaseReadBind(handle, tradeRecordStructure); i++)
         tradeRecords[i] = tradeRecordStructure;
      DatabaseFinalize(handle);
      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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to update table with invalid identifier: " + tableName);
         return false;
        }
      string updateQuery = "UPDATE " + tableName + " SET ";
      for(int i = 0; i < ArraySize(columns); i++)
        {
         updateQuery += columns[i] + "=?";
         if(i < ArraySize(columns) - 1)
            updateQuery += ", ";
        }
      updateQuery += " 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(!IsValidIdentifier(tableName))
        {
         Print("Refusing to delete from table with invalid identifier: " + tableName);
         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;
     }
  };
//+------------------------------------------------------------------+