MySQL_logger_1

//+------------------------------------------------------------------+
//|                                               MySQL_logger_1.mq4 |
//|                      Copyright © 2006, MetaQuotes Software Corp. |
//|                                        http://www.metaquotes.net |
//+------------------------------------------------------------------+
#property copyright "Copyright © 2010, CAJAX!"
#property link      "http://cajax.net"
 
//enter your connection details here
string host = "localhost";
string user = "root";
string password = "";
string DB = "mt4";
int clientflag = 0;
int port = 3306;
string socket = "";
 
 
//mysql related stuff
//requires libmysql. It can by found in any mysql related software, or from mysql distribution.
//don't forget to find STABLE libmysql to not crash whole MT someday
//DLL calls should be allowed to run
#import "libmysql.dll"
int mysql_init(int db);
int mysql_errno(int TMYSQL);
int mysql_real_connect(int TMYSQL, string host, string user, string password, 
                       string DB,int port,int socket,int clientflag);
int mysql_real_query(int TMSQL, string query, int length);
void mysql_close(int TMSQL);

//minimal interval between writing balance details to database in seconds. 0 - write always
extern int balance_write_min_interval=0;

// if true, will write balance details only if changed some value
extern bool balance_write_only_changes=true;
// "0" and "true" combination stores changed as soon as possible without writing duplicates

//when true all ticks are stored. Set true only for one advisor per symbol to prevent writing duplicates.
extern bool write_ticks=true;

//when true writes details of previous bar on given symbol and timeframe
extern bool write_bars=true;

#import
int mysql;

datetime Prev_Time=0;//time of previous bar time
//+------------------------------------------------------------------+
//| expert initialization function                                   |
//+------------------------------------------------------------------+
int init(){
   mysql = mysql_init(mysql);
   if(mysql != 0) 
      Print("allocated");
      
   //enter here details to connect to your database.

   int res = mysql_real_connect(mysql,host,user,password,DB,port,socket,clientflag);
   int err = GetLastError();
   if(res == mysql) 
   Print("connected");
   else 
   Print("error=", mysql, " ", mysql_errno(mysql), " ");
   
   //using Unicode.Though at present  it doesn't matter
   string query="SET NAMES 'UTF-8'";
   mysql_real_query(mysql, query, StringLen(query));

   //automatically creating (if it doesn't exists) table for given currency .
   query=StringConcatenate("CREATE TABLE IF NOT EXISTS `",tablename_ticks(),"` (",
   "`ID` bigint(15) NOT NULL auto_increment, ",
   "`DATE_TIME` timestamp NULL default CURRENT_TIMESTAMP, ",
   "`ASK` double(15,5) NOT NULL, ",
   "`BID` double(15,5) NOT NULL, ",
   "PRIMARY KEY  (`ID`)",
   ") ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;");
   mysql_real_query(mysql, query, StringLen(query));
   int myerr = mysql_errno(mysql);
   if(myerr > 0)
      Print("error=",myerr," Query: ",query);
   else
      Print (StringConcatenate("Writing ticks to ",tablename_ticks()));
   
   
   
   //automatically creating (if it doesn't exists) table for given currency and timeframe .
   query=StringConcatenate("CREATE TABLE IF NOT EXISTS `",tablename(),"` (",
   "`ID` bigint(15) NOT NULL auto_increment,",
   "`DATE_TIME` timestamp NULL default CURRENT_TIMESTAMP, ",
   "`OPEN` double(15,5) NOT NULL, ",
   "`MAX` double(15,5) NOT NULL, ",
   "`MIN` double(15,5) NOT NULL, ",
   "`CLOSE` double(15,5) NOT NULL, ",
   "`VOLUME` double(15,5) NOT NULL, ",
   "PRIMARY KEY  (`ID`)",
   ") ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;");
   mysql_real_query(mysql, query, StringLen(query));
   myerr = mysql_errno(mysql);
   if(myerr > 0)
      Print("error=",myerr," Query: ",query);
   else
      Print (StringConcatenate("Writing bars to ",tablename()));





   //automatically creating database to store balance state
   query=StringConcatenate(
   "CREATE TABLE IF NOT EXISTS `balance` (",
   "`ID` bigint(20) unsigned NOT NULL auto_increment, ",
   "`DATE_TIME` datetime NOT NULL, ",
   "`BALANCE` float(15,5) NOT NULL, ",
   "`CREDIT` float(15,5) NOT NULL, ",
   "`MARGIN` float(15,5) NOT NULL, ",
   "`FREEMARGIN` float(15,5) NOT NULL, ",
   "`EQUITY` float(15,5) NOT NULL, ",
   "`PROFIT` float(15,5) NOT NULL, ",
   "`OPEN` int(11) NOT NULL, ",
   "PRIMARY KEY  (`ID`)",
   ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;");
   mysql_real_query(mysql, query, StringLen(query));
   return(0);
}
//+------------------------------------------------------------------+
//| expert deinitialization function                                 |
//+------------------------------------------------------------------+
int deinit()
  {
   mysql_close(mysql);
//----
   return(0);
  }
//+------------------------------------------------------------------+
//| expert start function                                            |
//+------------------------------------------------------------------+












int start(){
   
   if(write_ticks==true)write_tick();
   if(write_bars&&Prev_Time!=Time[0]) write_bar();
   Prev_Time=Time[0];  

   writebalance();
  }
//+------------------------------------------------------------------+


//generating table name for given symbol and time period
string tablename()
{
   return (StringConcatenate(Symbol(),"_",Period()));
}

//generating table name for given symbol 
string tablename_ticks()
{
   return (StringConcatenate(Symbol(),"_ticks"));
}




//writing balance details
void writebalance()
{
   //checking write interval
   int lastwrite=0;
   if(GlobalVariableCheck("cajax_mysqllog_bw")) lastwrite=GlobalVariableGet("cajax_mysqllog_bw");
   if(CurTime()-balance_write_min_interval>=lastwrite){
      
      //now check, if there is "always write" option cheked or some changes
      bool do_write=false;
      if(balance_write_only_changes==false)do_write=true;
      
      double balance=NormalizeDouble(AccountBalance(),Digits);
      double credit=NormalizeDouble(AccountCredit(),Digits);
      double margin=NormalizeDouble(AccountMargin(),Digits);
      double freemargin=NormalizeDouble(AccountFreeMargin(),Digits);
      double equity=NormalizeDouble(AccountEquity(),Digits);
      double profit=NormalizeDouble(AccountProfit(),Digits);
      
      //podschet otkrytyh lotov
      int j,open=0,orders=OrdersTotal();
      for(int i=0;i<orders;i++)
      {
         if(OrderSelect(j,SELECT_BY_POS,MODE_TRADES)==true)open++;
      }
      
      //already writing or some conditions to make it writable
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_balance")==false||NormalizeDouble(GlobalVariableGet("balance_mysqllog_bh_balance"),Digits)!=balance)do_write=true;
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_credit")==false||NormalizeDouble(GlobalVariableGet("balance_mysqllog_bh_credit"),Digits)!=credit)do_write=true;
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_margin")==false||NormalizeDouble(GlobalVariableGet("balance_mysqllog_bh_margin"),Digits)!=margin)do_write=true;
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_freemargin")==false||NormalizeDouble(GlobalVariableGet("balance_mysqllog_bh_freemargin"),Digits)!=freemargin)do_write=true;
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_equity")==false||NormalizeDouble(GlobalVariableGet("balance_mysqllog_bh_equity"),Digits)!=equity)do_write=true;
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_profit")==false||NormalizeDouble(GlobalVariableGet("balance_mysqllog_bh_profit"),Digits)!=profit)do_write=true;
      if(do_write==true||GlobalVariableCheck("balance_mysqllog_bh_open")==false||GlobalVariableGet("balance_mysqllog_bh_open")!=open)do_write=true;
      
    
      
      
      if(do_write==true){
      
         //query to add new row to balance log
         string query=StringConcatenate("INSERT INTO  `mt4`.`balance` (`ID` ,`DATE_TIME` ,`BALANCE`,`CREDIT` ,`MARGIN` ,`FREEMARGIN` ,`EQUITY`, `PROFIT` ,`OPEN`)",
         "VALUES (NULL ,  '",
         TimeToStr(CurTime(), TIME_DATE|TIME_SECONDS),"',  ",
         balance,", ",
         credit,", ",
         margin,",  ",
         freemargin,",  ",
         equity,",  ",
         profit,",  ",
         open,");");

         mysql_real_query(mysql, query, StringLen(query));
         int myerr = mysql_errno(mysql);
         if(myerr > 0)
            Print("error=",myerr);
      }else{
         //didn't wrote. Nothing to do here
      }
      //storing current values to global variables
      GlobalVariableSet("balance_mysqllog_bh_balance",balance);
      GlobalVariableSet("balance_mysqllog_bh_credit",credit);
      GlobalVariableSet("balance_mysqllog_bh_margin",margin);
      GlobalVariableSet("balance_mysqllog_bh_freemargin",freemargin);
      GlobalVariableSet("balance_mysqllog_bh_equity",equity);
      GlobalVariableSet("balance_mysqllog_bh_profit",profit);
      GlobalVariableSet("balance_mysqllog_bh_open",open);
      GlobalVariableSet("balance_write_min_interval",CurTime());
   }



}


//writing open,min,max,close, volume of previous bar on opening current
void write_bar()
{
   string query = "";
   int length = 0;
   query = StringConcatenate("INSERT INTO `",tablename(),"`(`ID`,`date_time`,`open`,`max`,`min`,`close`,`volume`) VALUES('','",
   TimeToStr(CurTime(), TIME_DATE|TIME_SECONDS), "',",              
   NormalizeDouble(Open[1], Digits), ",", 
   NormalizeDouble(Low[1], Digits), ",", 
   NormalizeDouble(High[1], Digits), ",", 
   NormalizeDouble(Close[1], Digits), ",", 
   NormalizeDouble(Volume[1], Digits),");");
   length = StringLen(query);
   mysql_real_query(mysql, query, length);
   int myerr = mysql_errno(mysql);
   if(myerr > 0)
      Print("error=",myerr," Query: ",query);
}


//function to store all ticks. should be used only for one advisor per currency pair to prevent duplicates in DB
void write_tick()
{

   string query = "";
   int length = 0;
   query = StringConcatenate("INSERT INTO `",tablename_ticks(),"`(`ID`,`date_time`,`ask`,`bid`) VALUES('','",
   TimeToStr(CurTime(), TIME_DATE|TIME_SECONDS), "',",              
   NormalizeDouble(Ask, Digits), ",", 
   NormalizeDouble(Bid, Digits),");");
   length = StringLen(query);
   mysql_real_query(mysql, query, length);
   int myerr = mysql_errno(mysql);
   if(myerr > 0)
      Print("error=",myerr," Query: ",query);

}