Warrior_EA/Database/DatabaseOperationsManager.mqh

//+------------------------------------------------------------------+
//|                                                       Warrior_EA |
//|                                                     AnimateDread |
//|                                                                  |
//+------------------------------------------------------------------+
#include "..\System\PrintVerbose.mqh"
class CDatabaseOperationsManager
  {
private:
   bool              m_verboseMode;
   int               m_databaseHandle;
   int               m_queryHandle;
   // Prepare and execute a query
   bool              PrepareAndExecuteQuery(string query)
     {
      if(m_databaseHandle == INVALID_HANDLE)
         return false;
      int attempts = 0;
      while(attempts < 5)
        {
         if(DatabaseExecute(m_databaseHandle, query))
           {
            return true; // Success, exit the loop
           }
         Print("DatabaseExecute error: Attempt " + IntegerToString(attempts + 1) + " failed for query: " + query);
         Sleep(1000); // Wait for a second before retrying
         attempts++;
        }
      FinalizeQuery();
      return false; // After 5 attempts, give up
     }
   // Finalize the query handle
   void              FinalizeQuery()
     {
      if(m_queryHandle != INVALID_HANDLE)
        {
         DatabaseFinalize(m_queryHandle);
         m_queryHandle = INVALID_HANDLE;
        }
     }
public:
   //constructor
                     CDatabaseOperationsManager(bool verbose = false)
     {
      m_verboseMode = verbose;
      m_databaseHandle = INVALID_HANDLE;
      m_queryHandle = INVALID_HANDLE;
     }
   // Destructor
                    ~CDatabaseOperationsManager()
     {
      FinalizeQuery();
     }
   void              SetDatabaseHandle(int dbHandle)
     {
      m_databaseHandle = dbHandle;
     }
   // Create a table in the database
   bool              CreateTable(string tableName, string tableSchema)
     {
      if(!DatabaseTableExists(m_databaseHandle, tableName))
        {
         string createTableQuery = "CREATE TABLE " + tableName + " (" + tableSchema + ")";
         if(PrepareAndExecuteQuery(createTableQuery))
           {
            return true;
           }
         else
           {
            Print("Failed to create table " + tableName);
            FinalizeQuery();
            return false;
           }
        }
      else
        {
         // Table already exists
         return true;
        }
     }
   bool              DeleteTable(string tableName)
     {
      string deleteTableQuery = "DROP TABLE IF EXISTS " + tableName;
      if(PrepareAndExecuteQuery(deleteTableQuery))
        {
         PrintVerbose("Table " + tableName + " deleted successfully.");
         return true;
        }
      else
        {
         Print("Failed to delete table " + tableName);
         FinalizeQuery();
         return false;
        }
     }
   // Insert a trade record into the database
   bool              InsertTradeRecord(string tableName, const string &columns[], const string &values[])
     {
      string insertQuery = "INSERT INTO " + tableName + " (";
      for(int i = 0; i < ArraySize(columns); i++)
        {
         insertQuery += columns[i];
         if(i < ArraySize(columns) - 1)
            insertQuery += ", ";
        }
      insertQuery += ") VALUES (";
      for(int i = 0; i < ArraySize(values); i++)
        {
         insertQuery += "'" + values[i] + "'";
         if(i < ArraySize(values) - 1)
            insertQuery += ", ";
        }
      insertQuery += ")";
      if(PrepareAndExecuteQuery(insertQuery))
        {
         return true;
        }
      else
        {
         Print("Failed to insert trade record into " + tableName);
         FinalizeQuery();
         return false;
        }
     }
   struct RecordCount
     {
      int            count;
     };
   // Fetch the number of records in the specified table
   bool              FetchRecordCount(string tableName, int &count)
     {
      RecordCount countStruct;
      string countQuery = "SELECT COUNT(*) as count FROM " + tableName;
      if(!PrepareAndExecuteQuery(countQuery))
         return false;
      m_queryHandle = DatabasePrepare(m_databaseHandle, countQuery);
      if(m_queryHandle == INVALID_HANDLE)
        {
         Print("Failed to prepare COUNT query for " + tableName);
         FinalizeQuery();
         return false;
        }
      if(DatabaseReadBind(m_queryHandle, countStruct))
         count = countStruct.count;
      FinalizeQuery();
      return true;
     }
   // 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[])
     {
      int recordCount;
      if(!FetchRecordCount(tableName, recordCount))
         return false;
      // Resize the array once based on the record count
      ArrayResize(tradeRecords, recordCount);
      string selectQuery = "SELECT * FROM " + tableName;
      if(!PrepareAndExecuteQuery(selectQuery))
         return false;
      m_queryHandle = DatabasePrepare(m_databaseHandle, selectQuery);
      if(m_queryHandle == INVALID_HANDLE)
        {
         Print("Failed to prepare SELECT query for " + tableName);
         FinalizeQuery();
         return false;
        }
      for(int i = 0; i < recordCount && DatabaseReadBind(m_queryHandle, tradeRecordStructure); i++)
        {
         tradeRecords[i] = tradeRecordStructure;
        }
      FinalizeQuery();
      return true;
     }
   // Update trade records in the database based on specified criteria
   bool              UpdateTradeRecord(string tableName, const string &columns[], const string &values[], const string &condition)
     {
      string updateQuery = "UPDATE " + tableName + " SET ";
      for(int i = 0; i < ArraySize(columns); i++)
        {
         updateQuery += columns[i] + "='" + values[i] + "'";
         if(i < ArraySize(columns) - 1)
            updateQuery += ", ";
        }
      updateQuery += " WHERE " + condition;
      if(PrepareAndExecuteQuery(updateQuery))
        {
         return true;
        }
      else
        {
         Print("Failed to update trade records in " + tableName);
         FinalizeQuery();
         // Stop execution upon encountering an error
         return false;
        }
     }
   // Delete the oldest entry from a specified table
   bool              DeleteOldestEntry(string tableName)
     {
      string deleteQuery = "DELETE FROM " + tableName + " WHERE ROWID = (SELECT MIN(ROWID) FROM " + tableName + ")";
      if(PrepareAndExecuteQuery(deleteQuery))
        {
         PrintVerbose("Oldest entry deleted from " + tableName + " successfully.");
         return true;
        }
      else
        {
         Print("Failed to delete oldest entry from " + tableName);
         FinalizeQuery();
         return false;
        }
     }
  };
//+------------------------------------------------------------------+