DatabaseTransactionBegin

bool  DatabaseTransactionBegin(

   int  database      // 在DatabaseOpen中接收的数据库句柄

   );

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

//| Script program start function                                    |

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

void OnStart()

  {

//--- create the file name

   string filename=AccountInfoString(ACCOUNT_SERVER) +"_"+IntegerToString(AccountInfoInteger(ACCOUNT_LOGIN))+".sqlite";

//--- 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;

     }

//--- if the DEALS table already exists, delete it

   if(!DeleteTable(db, "DEALS"))

     {

      DatabaseClose(db);

      return;

     }

//--- create the DEALS table

   if(!CreateTableDeals(db))

     {

      DatabaseClose(db);

      return;

     }

//---  request the entire trading history

   datetime from_date=0;

   datetime to_date=TimeCurrent();

//--- request the history of deals in the specified interval

   HistorySelect(from_date, to_date);

   int deals_total=HistoryDealsTotal();

   PrintFormat("Deals in the trading history: %d ", deals_total);



//--- measure the transaction execution speed using DatabaseTransactionBegin/DatabaseTransactionCommit

   ulong start=GetMicrosecondCount();

   bool fast_transactions=true;

   InsertDeals(db, fast_transactions);

   double fast_transactions_time=double(GetMicrosecondCount()-start)/1000;

   PrintFormat("Transations WITH    DatabaseTransactionBegin/DatabaseTransactionCommit: time=%.1f milliseconds", fast_transactions_time);



//--- delete the DEALS table, and then create it again

   if(!DeleteTable(db, "DEALS"))

     {

      DatabaseClose(db);

      return;

     }

//--- create a new DEALS table

   if(!CreateTableDeals(db))

     {

      DatabaseClose(db);

      return;

     }



//--- test again, this time without using DatabaseTransactionBegin/DatabaseTransactionCommit

   fast_transactions=false;

   start=GetMicrosecondCount();

   InsertDeals(db, fast_transactions);

   double slow_transactions_time=double(GetMicrosecondCount()-start)/1000;

   PrintFormat("Transations WITHOUT DatabaseTransactionBegin/DatabaseTransactionCommit: time=%.1f milliseconds", slow_transactions_time);

//--- report gain in time

   PrintFormat("Use of DatabaseTransactionBegin/DatabaseTransactionCommit provided acceleration by %.1f times", double(slow_transactions_time)/fast_transactions_time);

//--- close the database

   DatabaseClose(db);

  }

/*

Results:

   Deals in the trading history: 2737

   Transations WITH    DatabaseTransactionBegin/DatabaseTransactionCommit: time=48.5 milliseconds

   Transations WITHOUT DatabaseTransactionBegin/DatabaseTransactionCommit: time=25818.9 milliseconds

   Use of DatabaseTransactionBegin/DatabaseTransactionCommit provided acceleration by 532.8 times

*/

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

//| Deletes 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 table with code ", GetLastError());

      return(false);

     }

//--- the table has been successfully deleted

   return(true);

  }

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

//| Creates the DEALS table                                          |

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

bool CreateTableDeals(int database)

  {

//--- check if the table exists

   if(!DatabaseTableExists(database, "DEALS"))

      //--- create the table

      if(!DatabaseExecute(database, "CREATE TABLE DEALS("

                          "ID          INT KEY NOT NULL,"

                          "ORDER_ID    INT     NOT NULL,"

                          "POSITION_ID INT     NOT NULL,"

                          "TIME        INT     NOT NULL,"

                          "TYPE        INT     NOT NULL,"

                          "ENTRY       INT     NOT NULL,"

                          "SYMBOL      CHAR(10),"

                          "VOLUME      REAL,"

                          "PRICE       REAL,"

                          "PROFIT      REAL,"

                          "SWAP        REAL,"

                          "COMMISSION  REAL,"

                          "MAGIC       INT,"

                          "REASON      INT );"))

        {

         Print("DB: create the table DEALS failed with code ", GetLastError());

         return(false);

        }

//--- the table has been successfully created

   return(true);

  }

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

//| Adds deals to the database table                                 |

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

bool InsertDeals(int database, bool begintransaction=true)

  {

//--- Auxiliary variables

   ulong    deal_ticket;         // deal ticket

   long     order_ticket;        // the ticket of the order by which the deal was executed

   long     position_ticket;     // ID of the position to which the deal belongs

   datetime time;                // deal execution time

   long     type ;               // deal type

   long     entry ;              // deal direction

   string   symbol;              // the symbol fro which the deal was executed

   double   volume;              // operation volume

   double   price;               // price

   double   profit;              // financial result

   double   swap;                // swap

   double   commission;          // commission

   long     magic;               // Magic number

   long     reason;              // deal execution reason or source

//--- go through all deals and add to the database

   bool failed=false;

   int deals=HistoryDealsTotal();

//--- if fast transaction performance method is used

   if(begintransaction)

     {

      // --- lock the database before executing transactions

      DatabaseTransactionBegin(database);

     }

   for(int i=0; i<deals; i++)

     {

      deal_ticket=    HistoryDealGetTicket(i);

      order_ticket=   HistoryDealGetInteger(deal_ticket, DEAL_ORDER);

      position_ticket=HistoryDealGetInteger(deal_ticket, DEAL_POSITION_ID);

      time= (datetime)HistoryDealGetInteger(deal_ticket, DEAL_TIME);

      type=           HistoryDealGetInteger(deal_ticket, DEAL_TYPE);

      entry=          HistoryDealGetInteger(deal_ticket, DEAL_ENTRY);

      symbol=         HistoryDealGetString(deal_ticket, DEAL_SYMBOL);

      volume=         HistoryDealGetDouble(deal_ticket, DEAL_VOLUME);

      price=          HistoryDealGetDouble(deal_ticket, DEAL_PRICE);

      profit=         HistoryDealGetDouble(deal_ticket, DEAL_PROFIT);

      swap=           HistoryDealGetDouble(deal_ticket, DEAL_SWAP);

      commission=     HistoryDealGetDouble(deal_ticket, DEAL_COMMISSION);

      magic=          HistoryDealGetInteger(deal_ticket, DEAL_MAGIC);

      reason=         HistoryDealGetInteger(deal_ticket, DEAL_REASON);

      //--- add each deal using the following request

      string request_text=StringFormat("INSERT INTO DEALS (ID,ORDER_ID,POSITION_ID,TIME,TYPE,ENTRY,SYMBOL,VOLUME,PRICE,PROFIT,SWAP,COMMISSION,MAGIC,REASON)"

                                       "VALUES (%d, %d, %d, %d, %d, %d, '%s', %G, %G, %G, %G, %G, %d, %d)",

                                       deal_ticket, order_ticket, position_ticket, time, type, entry, symbol, volume, price, profit, swap, commission, magic, reason);

      if(!DatabaseExecute(database, request_text))

        {

         PrintFormat("%s: failed to insert deal #%dwith code %d", __FUNCTION__, deal_ticket, GetLastError());

         PrintFormat("i=%d: deal #%d  %s", i, deal_ticket, symbol);

         failed=true;

         break;

        }

     }

//--- check for transaction execution errors

   if(failed)

     {

      //--- if fast transaction performance method is used

      if(begintransaction)

        {

         //--- roll back all transactions and unlock the database

         DatabaseTransactionRollback(database);

        }

      Print("%s: DatabaseExecute() failed with code ", __FUNCTION__, GetLastError());

      return(false);

     }

//--- if fast transaction performance method is used

   if(begintransaction)

     {

      //--- all transactions have been performed successfully - record changes and unlock the database

      DatabaseTransactionCommit(database);

     }

//--- successful completion

   return(true);

  }

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