//+------------------------------------------------------------------+
//| 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);
}
//+------------------------------------------------------------------+ |