Adwizard/Database/Database.mqh

<EFBFBD><EFBFBD>//+------------------------------------------------------------------+
//|                                                     Database.mqh |
//|                                      Copyright 2024, Yuriy Bykov |
//|                            https://www.mql5.com/ru/users/antekov |
//+------------------------------------------------------------------+
#property copyright "Copyright 2024, Yuriy Bykov"
#property link      "https://www.mql5.com/ru/users/antekov"
#property version   "1.12"

// <?>@B sql-D09;>2 A>740=8O AB@C:BC@K  @07=KE B8?>2
#resource "db.opt.schema.sql" as string dbOptSchema
#resource "db.cut.schema.sql" as string dbCutSchema
#resource "db.adv.schema.sql" as string dbAdvSchema

// "8? 107K 40==KE
enum ENUM_DB_TYPE {
   DB_TYPE_OPT,   //  >?B8<870F88
   DB_TYPE_CUT,   //  4;O ?>41>@0 3@C?? (C@570==0O  >?B8<870F88)
   DB_TYPE_ADV,   //  M:A?5@B0 (8B>3>2>3> A>25B=8:0)
};

#include "../Utils/Macros.mqh"

#define DB CDatabase

//+------------------------------------------------------------------+
//| ;0AA 4;O @01>BK A 107>9 40==KE                                  |
//+------------------------------------------------------------------+
class CDatabase {
   static int        s_db;          // %5=4; A>548=5=8O A 
   static string     s_fileName;    // <O D09;0 
   static int        s_common;      // $;03 8A?>;L7>20=8O >1I59 ?0?:8 40==KE
   static bool       s_res;         //  57C;LB0B 2K?>;=5=8O 70?@>A>2

public:
   static int        Id();          // %5=4; A>548=5=8O A 
   static bool       Res();         //  57C;LB0B 2K?>;=5=8O 70?@>A>2

   // >;=>5 8;8 :>@>B:>5 8<O D09;0 
   static string     FileName(bool full = false);

   static bool       IsOpen();      // B:@KB0 ;8 ?

   // !>740=85 ?CAB>9  ?> 7040==>9 AE5<5
   static void       Create(string p_schema);

   // >4:;NG5=85 :  A 7040==K< 8<5=5< 8 B8?><
   static bool       Connect(string p_fileName,
                             ENUM_DB_TYPE p_dbType = DB_TYPE_OPT
                            );

   static void       Close();       // 0:@KB85 

   // K?>;=5=85 >4=>3> 70?@>A0 : 
   static bool       Execute(string query, int attempt = 0);

   // K?>;=5=85 =5A:>;L:8E 70?@>A>2 :  2 >4=>9 B@0=70:F88
   static bool       ExecuteTransaction(string &queries[], int attempt = 0);

   // K?>;=5=85 70?@>A0 :  =0 2AB02:C A 2>72@0B>< 845=B8D8:0B>@0 =>2>9 70?8A8
   static ulong      Insert(string query);

   static string     GetValue(string query);
};

int    CDatabase::s_db       =  INVALID_HANDLE;
string CDatabase::s_fileName = "database.sqlite";
int    CDatabase::s_common   =  DATABASE_OPEN_COMMON;
bool   CDatabase::s_res      =  true;


//+------------------------------------------------------------------+
//| %5=4; A>548=5=8O A                                             |
//+------------------------------------------------------------------+
int CDatabase::Id() {
   return s_db;
}

//+------------------------------------------------------------------+
//|  57C;LB0B 2K?>;=5=8O 70?@>A>2                                    |
//+------------------------------------------------------------------+
bool CDatabase::Res() {
   return s_res;
}

//+------------------------------------------------------------------+
//| >;=>5 8;8 :>@>B:>5 8<O D09;0                                  |
//+------------------------------------------------------------------+
string CDatabase::FileName(bool full = false) {
   string path = "";
   if(full) {
      path = (s_common == DATABASE_OPEN_COMMON ?
              TerminalInfoString(TERMINAL_COMMONDATA_PATH) :
              TerminalInfoString(TERMINAL_DATA_PATH) + "\\MQL5")
             + "\\Files\\";
   }
   return path + s_fileName;
}

//+------------------------------------------------------------------+
//| B:@KB0 ;8 ?                                                   |
//+------------------------------------------------------------------+
bool CDatabase::IsOpen() {
   return (s_db != INVALID_HANDLE);
}

//+------------------------------------------------------------------+
//| !>740=85 ?CAB>9                                                |
//+------------------------------------------------------------------+
void CDatabase::Create(string p_schema) {
   s_res = Execute(p_schema);
   if(s_res) {
      PrintFormat(__FUNCTION__" | Database successfully created from %s", "db.*.schema.sql");
   }
}

//+------------------------------------------------------------------+
//| 0:@KB85                                                       |
//+------------------------------------------------------------------+
void CDatabase::Close() {
   if(s_db != INVALID_HANDLE) {
      DatabaseClose(s_db);
      //PrintFormat(__FUNCTION__" | Close database %s with handle %d",
      //            s_fileName, s_db);
      s_db = INVALID_HANDLE;
   }
}

//+------------------------------------------------------------------+
//| @>25@:0 ?>4:;NG5=8O : 1075 40==KE A 7040==K< 8<5=5<             |
//+------------------------------------------------------------------+
bool CDatabase::Connect(string p_fileName, ENUM_DB_TYPE p_dbType = DB_TYPE_OPT) {
// A;8 1070 40==KE >B:@KB0, B> 70:@>5< 5Q
   Close();

   s_res = true;

// A;8 7040=> 8<O D09;0, B> 70?><=8< 53>
   s_fileName = p_fileName;

// #AB0=>28< D;03 >1I59 ?0?:8 4;O  >?B8<870F88 8 M:A?5@B0
   s_common = (p_dbType != DB_TYPE_CUT ? DATABASE_OPEN_COMMON : 0);

// B:@K205< 107C 40==KE
// @>1C5< >B:@KBL ACI5AB2CNI89 D09; 
   s_db = DatabaseOpen(s_fileName, DATABASE_OPEN_READWRITE | s_common);

// A;8 D09;  =5 =0945=, B> ?KB05<AO A>740BL 53> ?@8 >B:@KB88
   if(!IsOpen()) {
      s_db = DatabaseOpen(s_fileName,
                          DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | s_common);

      // !>>1I05< >1 >H81:5 ?@8 =5C40G5
      if(!IsOpen()) {
         PrintFormat(__FUNCTION__" | ERROR: %s Connect failed with code %d",
                     s_fileName, GetLastError());
         return false;
      }
      if(p_dbType == DB_TYPE_OPT) {
         Create(dbOptSchema);
      } else if(p_dbType == DB_TYPE_CUT) {
         Create(dbCutSchema);
      } else {
         Create(dbAdvSchema);
      }
   }

   return true;
}

//+------------------------------------------------------------------+
//| K?>;=5=85 >4=>3> 70?@>A0 :                                    |
//+------------------------------------------------------------------+
bool CDatabase::Execute(string query, int attempt = 0) {
   s_res = DatabaseExecute(s_db, query);
   if(!s_res) {
      if((_LastError == ERR_DATABASE_LOCKED || _LastError == ERR_DATABASE_BUSY) && attempt < 20) {
         PrintFormat(__FUNCTION__" | ERROR: ERR_DATABASE_LOCKED. Repeat Transaction in DB [%s]",
                     s_fileName);
         Execute(query, attempt + 1);

      } else {
         // !>>1I05< > =59
         PrintFormat(__FUNCTION__" | ERROR: Execution failed in DB [%s], query:\n"
                     "%s\n"
                     "error code = %d",
                     s_fileName, query, _LastError);
      }
   }
   return s_res;
}

//+------------------------------------------------------------------+
//| K?>;=5=85 =5A:>;L:8E 70?@>A>2 :  2 >4=>9 B@0=70:F88           |
//+------------------------------------------------------------------+
bool CDatabase::ExecuteTransaction(string &queries[], int attempt = 0) {
// B:@K205< B@0=70:F8N
   DatabaseTransactionBegin(s_db);

   s_res = true;
// B?@02;O5< 2A5 70?@>AK =0 2K?>;=5=85
   FOREACH(queries) {
      s_res &= DatabaseExecute(s_db, queries[i]);
      if(!s_res) break;
   }

// A;8 2 :0:><-B> 70?@>A5 2>7=8:;0 >H81:0, B>
   if(!s_res) {
// B<5=O5< B@0=70:F8N
      DatabaseTransactionRollback(s_db);
      if((_LastError == ERR_DATABASE_LOCKED || _LastError == ERR_DATABASE_BUSY) && attempt < 20) {
         PrintFormat(__FUNCTION__" | ERROR: ERR_DATABASE_LOCKED. Repeat Transaction in DB [%s]",
                     s_fileName);
         Sleep(rand() % 50);
         ExecuteTransaction(queries, attempt + 1);

      } else {
         // !>>1I05< > =59
         PrintFormat(__FUNCTION__" | ERROR: Transaction failed in DB [%s], error code=%d",
                     s_fileName, _LastError);
      }

   } else {
// =0G5 - ?>4B25@6405< B@0=70:F8N
      DatabaseTransactionCommit(s_db);
//PrintFormat(__FUNCTION__" | Transaction done successfully");
   }
   return s_res;
}

//+------------------------------------------------------------------+
//| K?>;=5=85 70?@>A0 :  =0 2AB02:C A 2>72@0B>< 845=B8D8:0B>@0    |
//| =>2>9 70?8A8                                                     |
//+------------------------------------------------------------------+
ulong CDatabase::Insert(string query) {
   ulong res = 0;

   if(StringFind(query, "RETURNING rowid;") == -1) {
      StringReplace(query, ";", "");
      query += " RETURNING rowid;";
   }

// K?>;=O5< 70?@>A
   int request = DatabasePrepare(s_db, query);

// A;8 =5B >H81:8
   if(request != INVALID_HANDLE) {
      // !B@C:BC@0 40==KE 4;O GB5=8O >4=>9 AB@>:8 @57C;LB0B0 70?@>A0
      struct Row {
         int         rowid;
      } row;

      // '8B05< 40==K5 87 ?5@2>9 AB@>:8 @57C;LB0B0
      if(DatabaseReadBind(request, row)) {
         res = row.rowid;
      } else {
         // !>>1I05< >1 >H81:5 ?@8 =5>1E>48<>AB8
         PrintFormat(__FUNCTION__" | ERROR: Reading row in DB [%s] for request \n%s\nfailed with code %d",
                     s_fileName, query, GetLastError());
         s_res = false;
      }
      DatabaseFinalize(request);
   } else {
      // !>>1I05< >1 >H81:5 ?@8 =5>1E>48<>AB8
      PrintFormat(__FUNCTION__" | ERROR: Request in DB [%s] \n%s\nfailed with code %d",
                  s_fileName, query, GetLastError());
      s_res = false;
   }
   return res;
}


//+------------------------------------------------------------------+
//| >;CG5=85 7=0G5=8O 2 2845 AB@>:8 ?> 7040==><C :;NGC              |
//+------------------------------------------------------------------+
string CDatabase::GetValue(string query) {
   string value = NULL; // >72@0I05<>5 7=0G5=85

// K?>;=O5< 70?@>A
   int request = DatabasePrepare(DB::Id(), query);

// A;8 =5B >H81:8
   if(request != INVALID_HANDLE) {
      // '8B05< 40==K5 87 ?5@2>9 AB@>:8 @57C;LB0B0
      DatabaseRead(request);

      if(!DatabaseColumnText(request, 0, value)) {
         // !>>1I05< >1 >H81:5 ?@8 =5>1E>48<>AB8
         PrintFormat(__FUNCTION__" | ERROR: Reading row in DB [adv] for request \n%s\n"
                     "failed with code %d",
                     query, GetLastError());
         s_res = false;
      }
      DatabaseFinalize(request);
   } else {
      // !>>1I05< >1 >H81:5 ?@8 =5>1E>48<>AB8
      PrintFormat(__FUNCTION__" | ERROR: Request in DB [adv] \n%s\nfailed with code %d",
                  query, GetLastError());
      s_res = false;
   }

   return value;
}

//+------------------------------------------------------------------+