//+------------------------------------------------------------------+
//|                                                     Database.mqh |
//|                                                       GrayMatrix |
//|                                                                  |
//+------------------------------------------------------------------+
#property copyright "Jay Davis"
#property link      "https://512jay.github.io"
#property version   "1.00"
input string DatabaseName = "DATABASE";  //\AppData\Roaming\MetaQuotes\Terminal\Common\DATABASE
struct DBMqlTradeRequest
  {
   ENUM_TRADE_REQUEST_ACTIONS    action;           // Trade operation type
   ulong                         magic;            // Expert Advisor ID (magic number)
   ulong                         order;            // Order ticket
   string                        symbol;           // Trade symbol
   double                        volume;           // Requested volume for a deal in lots
   double                        price;            // Price
   double                        stoplimit;        // StopLimit level of the order
   double                        sl;               // Stop Loss level of the order
   double                        tp;               // Take Profit level of the order
   ulong                         deviation;        // Maximal possible deviation from the requested price
   ENUM_ORDER_TYPE               type;             // Order type
   ENUM_ORDER_TYPE_FILLING       type_filling;     // Order execution type
   ENUM_ORDER_TYPE_TIME          type_time;        // Order expiration type
   datetime                      expiration;       // Order expiration time (for the orders of ORDER_TIME_SPECIFIED type)
   string                        comment;          // Order comment
   ulong                         position;         // Position ticket
   ulong                         position_by;      // The ticket of an opposite position
  };
//+------------------------------------------------------------------+
//| Creates a new database if it does not exist                      |
//+------------------------------------------------------------------+
bool CreateDataBase(string filename, bool createNew = false)
  {
   if(!createNew)
      return false;
   Print(__FUNCTION__, " ", filename);

//--- open/create the database in the common terminal folder
   int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | DATABASE_OPEN_COMMON);
   if(db==INVALID_HANDLE)
     {
      Print("DB: ", filename, " open failed with code ", GetLastError());
      return false;
     }
//--- create the MqlTradeRequest table
   if(!CreateTableMqlTradeRequest(db, createNew))
     {
      Print("Table created");
      DatabaseClose(db);
      return false;
     }
   return true;
  }
//+------------------------------------------------------------------+
//| Opens the deals table and returns the handle                     |
//+------------------------------------------------------------------+
int OpenDatabase(string filename)
  {
//--- open/create the database in the common terminal folder
   int db=DatabaseOpen(filename, DATABASE_OPEN_READWRITE | DATABASE_OPEN_CREATE | DATABASE_OPEN_COMMON);
   if(db==INVALID_HANDLE)
     {
      Print("DB: ", filename, " open failed with code ", GetLastError());
      return -1;
     }
   return db;
  }
//+------------------------------------------------------------------+
//| Inserts a MqlTradeRequest into DB without a db handle            |
//+------------------------------------------------------------------+
bool InsertMqlTradeRequest(MqlTradeRequest & request)
  {
   MqlTradeRequest temp = request;
   bool inserted = false;
   int db = OpenDatabase(DatabaseName);
   inserted = InsertMqlTradeRequest(db, request);
   if(!inserted)
     {
      Print("Insert failed taking measures\nCreated database: ", DatabaseName);
      if(!DatabaseTableExists(db, "MQLTRADEREQUEST"))
         if(CreateTableMqlTradeRequest(db, true))
           {
            db = OpenDatabase(DatabaseName);
            inserted = InsertMqlTradeRequest(db, temp);
           }
     }
   DatabaseClose(db);
   return inserted;
  }
//+------------------------------------------------------------------+
//| Add a request to the database table with handle                  |
//+------------------------------------------------------------------+
bool InsertMqlTradeRequest(int database, MqlTradeRequest & r)
  {
// --- lock the database before executing transactions
   DatabaseTransactionBegin(database);
   string request_text = StringFormat("INSERT INTO MQLTRADEREQUEST ("
                                      "ACTION," //%d
                                      "MAGIC_NUMBER," //%d
                                      "ORDER_TICKET," //%d
                                      "SYMBOL,"    //%s
                                      "VOLUME,"    //%G
                                      "PRICE,"     //%G
                                      "STOPLIMIT," //%G
                                      "STOPLOSS,"  //%G
                                      "TAKEPROFIT,"//%G
                                      "DEVIATION,"//%d
                                      "TYPE,"//%d
                                      "TYPE_FILLING,"//%d
                                      "TYPE_TIME,"//%d
                                      "EXPIRATION,"//%d
                                      "COMMENT,"   //%s.
                                      "POSITION,"//%d
                                      "POSITION_BY)"//%d
                                      "VALUES (%d,%d,%d,'%s',%G,%G,%G,%G,%G,%d,%d,%d,%d,%d,'%s',%d,%d);",
                                      r.action, r.magic, r.order, r.symbol, r.volume, r.price, r.stoplimit,
                                      r.sl, r.tp, r.deviation, r.type, r.type_filling, r.type_time,
                                      r.expiration, r.comment, r.position, r.position_by);
//Print(r.action, r.magic, r.order, r.symbol, r.volume, r.price, r.stoplimit, r.sl, r.tp, r.comment, r.position, r.position_by, TimeCurrent());
   if(!DatabaseExecute(database, request_text)) //request_text
     {
      PrintFormat("%s: failed to insert MqlTradeRequest %s with code %d",
                  __FUNCTION__, r.symbol, GetLastError());
      //--- roll back all transactions and unlock the database
      DatabaseTransactionRollback(database);
      PrintFormat("%s: DatabaseExecute() failed with code %d", __FUNCTION__, GetLastError());
      return(false);
     }
   else
     {
      Print("Inserted: ", 
            EnumToString((ENUM_ORDER_TYPE)r.type), " magic #",
            r.magic, " \"", r.comment, "\" on ", r.symbol, " with (vol ", r.volume,
            ") at (price:", r.price, ") (SL:", r.sl, ") (TP:", r.tp, ") at ", TimeCurrent());
     }
//--- all transactions have been performed successfully - record changes and unlock the database
   DatabaseTransactionCommit(database);
   return(true);
  }
//+------------------------------------------------------------------+
//| Create the MqlTradeRequest table                                 |
//+------------------------------------------------------------------+
bool CreateTableMqlTradeRequest(int database, bool createNew = false)
  {
//--- if the MQLTRADEREQUEST table already exists, delete it
   if(createNew && !DeleteTable(database, "MQLTRADEREQUEST"))
     {
      return(false);
     }
//--- check if the table exists
   if(!DatabaseTableExists(database, "MQLTRADEREQUEST"))
      //--- create the table
      if(!DatabaseExecute(database, "CREATE TABLE MQLTRADEREQUEST("
                          "ACTION INT," // Trade operation type
                          "MAGIC_NUMBER       INT,"       // Expert Advisor ID (magic number)
                          "ORDER_TICKET       INT,"       // Order ticket
                          "SYMBOL      CHAR(25),"  // Trade symbol
                          "VOLUME      REAL,"   // Requested volume for a deal in lots
                          "PRICE       REAL,"    // Price
                          "STOPLIMIT   REAL,"   // StopLimit level of the order
                          "STOPLOSS    REAL,"   // Stop Loss level of the order
                          "TAKEPROFIT  REAL,"   // Take Profit level of the order
                          "DEVIATION   INT,"    // Maximal possible deviation from the requested price
                          "TYPE        INT,"    // Order type
                          "TYPE_FILLING    INT," // Order execution type
                          "TYPE_TIME       INT," // Order expiration type
                          "EXPIRATION      DATETIME,"     // Order expiration time
                          "COMMENT     TINYTEXT,"  // Order comment
                          "POSITION    INT,"       // Position ticket
                          "POSITION_BY INT);"))    // The ticket of an opposite position
        {
         Print("DB: create the MQLTRADEREQUEST table failed with code ", GetLastError());
         return(false);
        }
//--- the table has been successfully created
   Print("Created MQLTRADEREQUEST table.");
   return(true);
  }
//+------------------------------------------------------------------+
//| Delete a table with the specified name from the database         |
//+------------------------------------------------------------------+
bool DeleteTable(int database, string table_name)
  {
   if(!DatabaseExecute(database, "DROP TABLE IF EXISTS "+table_name))
     {
      Print("Failed to drop the DEALS table with code ", GetLastError());
      return(false);
     }
//--- the table has been successfully deleted
   return(true);
  }
//+------------------------------------------------------------------+
//| Queries the database with given string, returns a request handle |
//+------------------------------------------------------------------+
int QueryDatabase(int databaseHandle, string requestText = "SELECT COUNT(*) FROM MQLTRADEREQUEST;")
  {
   int request = DatabasePrepare(databaseHandle, requestText);
   if(request == INVALID_HANDLE)
     {
      Print("DB: ", requestText, " request failed with code ", GetLastError());
      DatabaseClose(databaseHandle);
      return -1;
     }
   return request;
  }
//+------------------------------------------------------------------+
//| Returns the number of rows in the given table                    |
//+------------------------------------------------------------------+
int GetTableSize(string databaseName, string table = "MQLTRADEREQUEST")
  {
   int db = OpenDatabase(databaseName);
   string requestText = StringFormat("SELECT COUNT(*) FROM %s;", table);
   int request=QueryDatabase(db, requestText);
// get table size
   int size;
   DatabaseRead(request);
   if(!DatabaseColumnInteger(request, 0, size))
     {
      Print("DatabaseRead() failed with code ", GetLastError());
      DatabaseFinalize(request);
      DatabaseClose(db);
      return -1;
     }
//--- remove the query after use
   DatabaseFinalize(request);
   DatabaseClose(db);
   return size;
  }
//+------------------------------------------------------------------+
//| Returns true if the database has more updates                    |
//+------------------------------------------------------------------+
bool CheckForDB_Update(long & rowId, string databaseName, string table = "MQLTRADEREQUEST")
  {
   long currentRowID, db;
   db = OpenDatabase(databaseName);
   currentRowID = GetTableSize(databaseName, table);
   if(currentRowID > rowId)
      return true;
   return false;
  }
//+------------------------------------------------------------------+
//| Downloads the latest updates from the database                   |
//+------------------------------------------------------------------+
bool GetDB_Updates(long &rowID, string databaseName, MqlTradeRequest &array[], string table = "MQLTRADEREQUEST")
  {
   DBMqlTradeRequest temp;
   MqlTradeRequest fromDB[];
   int db = OpenDatabase(databaseName);
   string requestText = StringFormat("SELECT * FROM %s WHERE ROWID > %d;", table, rowID);
   int request = QueryDatabase(db, requestText);
//Print(databaseName, " ", requestText, " handle of a request ", request);
   for(int i=0; DatabaseReadBind(request, temp); i++)
     {
      int size = ArraySize(fromDB);
      ArrayResize(fromDB, size + 1);
      fromDB[i].action = (ENUM_TRADE_REQUEST_ACTIONS) temp.action;
      fromDB[i].comment = temp.comment;
      fromDB[i].deviation = temp.deviation;
      fromDB[i].expiration = temp.expiration;
      fromDB[i].magic = temp.magic;
      fromDB[i].order = temp.order;
      fromDB[i].position = temp.position;
      fromDB[i].position_by = temp.position_by;
      fromDB[i].price = temp.price;
      fromDB[i].sl = temp.sl;
      fromDB[i].stoplimit = temp.stoplimit;
      fromDB[i].tp = temp.tp;
      fromDB[i].type = (ENUM_ORDER_TYPE) temp.type;
      fromDB[i].type_filling = (ENUM_ORDER_TYPE_FILLING) temp.type_filling;
      fromDB[i].type_time = (ENUM_ORDER_TYPE_TIME) temp.type_time;
      fromDB[i].volume = temp.volume;
      fromDB[i].symbol = temp.symbol;
     }
//--- delete request after use
   DatabaseFinalize(request);
   DatabaseClose(db);

   int size = ArraySize(fromDB);
   rowID += size;
   ArrayFree(array);
   for(int i=0; i < size; i++)
     {
      ArrayResize(array, ArraySize(array) + 1);
      array[i].action  = fromDB[i].action;  // = (ENUM_TRADE_REQUEST_ACTIONS) temp.action;
      array[i].comment  = fromDB[i].comment;  // = temp.comment;
      array[i].deviation  = fromDB[i].deviation;  // = temp.deviation;
      array[i].expiration  = fromDB[i].expiration;  // = temp.expiration;
      array[i].magic  = fromDB[i].magic;  // = temp.magic;
      array[i].order  = fromDB[i].order;  // = temp.order;
      array[i].position  = fromDB[i].position;  // = temp.position;
      array[i].position_by  = fromDB[i].position_by;  // = temp.position_by;
      array[i].price  = fromDB[i].price;  // = temp.price;
      array[i].sl  = fromDB[i].sl;  // = temp.sl;
      array[i].stoplimit  = fromDB[i].stoplimit;  // = temp.stoplimit;
      array[i].tp  = fromDB[i].tp;  // = temp.tp;
      array[i].type  = fromDB[i].type;  // = (ENUM_ORDER_TYPE) temp.type;
      array[i].type_filling  = fromDB[i].type_filling;  // = (ENUM_ORDER_TYPE_FILLING) temp.type_filling;
      array[i].type_time  = fromDB[i].type_time;  // = (ENUM_ORDER_TYPE_TIME) temp.type_time;
      array[i].volume  = fromDB[i].volume;  // = temp.volume;
      array[i].symbol  = fromDB[i].symbol;  // = temp.symbol;
     }
   return size > 0;
  }
//+------------------------------------------------------------------+