Download MetaTrader 5

SQL and MQL5: Working with SQLite Database

18 April 2014, 12:53
o_O
3
5 300

Small. Fast. Reliable.
Choose any of three.

 

Introduction

Many developers consider using databases in their projects for data storage purposes and yet they remain hesitant about this, knowing how much extra time the SQL server installation may require. And whereas it may not be so difficult for programmers (if a database management system (DBMS) has already been installed for other purposes), it will certainly be an issue for a common user who might eventually be discouraged to install the software altogether.

So many developers choose not to deal with DBMS realizing that solutions they are currently working on will be used by very few people. As a result, they turn to working with files (often having to deal with more than one file, given the variety of data used): CSV, less often XML or JSON, or binary data files with strict structure size, etc.

However, it turns out there is a great alternative to SQL server! And you do not even need to install additional software as everything is done locally in your project, while still allowing you to use the full power of SQL. We are talking about SQLite.

The purpose of this article is to quickly get you started with SQLite. I will therefore not go into subtleties and all imaginable parameter sets and function flags but instead will create a light connection wrapper to execute SQL commands and will demonstrate its use.

To proceed with the article, you need to:

  • Be in a good mood ;)
  • Extract the archive files attached to the article to the MetaTrader 5 client terminal folder
  • Install any convenient SQLite Viewer (e.g. SQLiteStudio)
  • Add the official documentation on SQLite http://www.sqlite.org to Favorites

Contents

1. SQLite Principles
2. SQLite3 API
    2.1. Opening and Closing a Database
    2.2. Execution of SQL Queries
    2.3. Getting Data from Tables
    2.4. Writing Parameter Data with Binding
    2.5. Transactions / Multirow Inserts (Example of Creating the Deal Table of a Trading Account)
3. Compiling 64-Bit Version (sqlite3_64.dll)


1. SQLite Principles

SQLite is an RDBMS whose key feature is the absence of a locally installed SQL server. Your application is seen here as a server. Working with SQLite database is basically working with a file (on a disk drive or in the memory). All data can be archived or moved to another computer without any need to install them in any specific way.

With SQLite, developers and users can benefit from several undeniable advantages:

  • no need to install additional software;
  • data are stored in a local file, thus offering transparency of management, i.e. you can view and edit them, independent of your application;
  • ability to import and export tables to other DBMS;
  • the code uses familiar SQL queries, which allows you to force the application to work with other DBMS at any time.

There are three ways of working with SQLite:

  1. you can use the DLL file with a complete set of API functions;
  2. you can use shell commands to an EXE file;
  3. you can compile your project including source codes of SQLite API.

In this article, I will describe the first option, being the most customary in MQL5.

 

2. SQLite3 API

The connector operation will require the use of the following SQLite functions:

//--- general functions
sqlite3_open
sqlite3_prepare
sqlite3_step
sqlite3_finalize
sqlite3_close
sqlite3_exec

//--- functions for getting error descriptions
sqlite3_errcode
sqlite3_extended_errcode
sqlite3_errmsg

//--- functions for saving data
sqlite3_bind_null
sqlite3_bind_int
sqlite3_bind_int64
sqlite3_bind_double
sqlite3_bind_text
sqlite3_bind_blob

//--- functions for getting data
sqlite3_column_count
sqlite3_column_name
sqlite3_column_type
sqlite3_column_bytes
sqlite3_column_int
sqlite3_column_int64
sqlite3_column_double
sqlite3_column_text
sqlite3_column_blob

You will also need low-level msvcrt.dll functions for working with pointers:

strlen
strcpy
memcpy

Since I'm creating a connector that is supposed to work in 32 and 64 bit terminals, it is important to consider the size of the pointer sent to API functions. Let's separate their names:

// for a 32 bit terminal
#define PTR32                int
#define sqlite3_stmt_p32     PTR32
#define sqlite3_p32          PTR32
#define PTRPTR32             PTR32

// for a 64 bit terminal
#define PTR64                long
#define sqlite3_stmt_p64     PTR64
#define sqlite3_p64          PTR64
#define PTRPTR64             PTR64

If necessary, all API functions will be overloaded for 32 and 64 bit pointers. Please note that all pointers of the connector will be 64 bit. They will be converted to 32 bit directly in the overloaded API functions. The API function import source code is provided in SQLite3Import.mqh


SQLite Data Types

There are five data types in SQLite Version 3

Type
Description
NULL NULL value.
INTEGER Integer stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value stored.
REAL 8-byte real number.
TEXT Text string with the end character \0 stored using UTF-8 or UTF-16 encoding.
BLOB Arbitrary binary data


You can also use other type names, e.g. BIGINT or INT accepted in various DBMS to specify the data type of a field when creating a table from a SQL query. In this case, SQLite will convert them in one of its intrinsic types, in this case to INTEGER. For further information about data types and their relations, please read the documentation http://www.sqlite.org/datatype3.html


2.1. Opening and Closing a Database

As you already know, a database in SQLite3 is a regular file. So opening a database is in fact equal to opening a file and getting its handle.

It is done using the sqlite3_open function:

int sqlite3_open(const uchar &filename[], sqlite3_p64 &ppDb);

filename [in]  - a pathname or file name if the file is being opened at the current location.
ppDb     [out] - variable that will store the file handle address.

The function returns SQLITE_OK in case of success or else an error code.

A database file is closed using the sqlite3_close function:

int sqlite3_close(sqlite3_p64 ppDb);

ppDb [in] - file handle

The function returns SQLITE_OK in case of success or else an error code.


Let us create database opening and closing functions in the connector.

//+------------------------------------------------------------------+
//| CSQLite3Base class                                               |
//+------------------------------------------------------------------+
class CSQLite3Base
  {
   sqlite3_p64       m_db;             // pointer to database file
   bool              m_bopened;        // flag "Is m_db handle valid"
   string            m_dbfile;         // path to database file

public:
                     CSQLite3Base();   // constructor
   virtual          ~CSQLite3Base();   // destructor


public:
   //--- connection to database 
   bool              IsConnected();
   int               Connect(string dbfile);
   void              Disconnect();
   int               Reconnect();
  };
//+------------------------------------------------------------------+
//| Constructor                                                      |
//+------------------------------------------------------------------+
CSQLite3Base::CSQLite3Base()
  {
   m_db=NULL;
   m_bopened=false;
  }
//+------------------------------------------------------------------+
//| Destructor                                                       |
//+------------------------------------------------------------------+
CSQLite3Base::~CSQLite3Base()
  {
   Disconnect();
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool CSQLite3Base::IsConnected()
  {
   return(m_bopened && m_db);
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Connect(string dbfile)
  {
   if(IsConnected())
      return(SQLITE_OK);
   m_dbfile=dbfile;
   return(Reconnect());
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
void CSQLite3Base::Disconnect()
  {
   if(IsConnected())
      ::sqlite3_close(m_db);
   m_db=NULL;
   m_bopened=false;
  }
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Reconnect()
  {
   Disconnect();
   uchar file[];
   StringToCharArray(m_dbfile,file);
   int res=::sqlite3_open(file,m_db);
   m_bopened=(res==SQLITE_OK && m_db);
   return(res);
  }

The connector can now open and close a database. Now check its performance with a simple script:

#include <MQH\Lib\SQLite3\SQLite3Base.mqh>

CSQLite3Base sql3; // database connector
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
void OnStart()
{   
//--- open database connection
   if(sql3.Connect("SQLite3Test.db3")!=SQLITE_OK)
      return;
//--- close connection
    sql3.Disconnect();
}

Run the script in debug mode, take a deep breath and check the operation of each string. As a result, a database file will appear in the MetaTrader 5 terminal installation folder. Congratulate yourself on this success and proceed to the next section.


2.2. Execution of SQL Queries

Any SQL query in SQLite3 has to go through at least three stages:

  1. sqlite3_prepare - verification and receiving the list of statements;
  2. sqlite3_step - executing these statements;
  3. sqlite3_finalize - finalizing and memory clearing.

This structure is mainly suitable for creating or deleting tables, as well as for writing non-binary data, i.e. for the cases when an SQL query does not imply returning any data except for its execution success status.

If the query involves receiving data or writing binary data, sqlite3_column_хх or sqlite3_bind_хх function is used at the second stage, respectively. These functions are described in details in the next section.

Let's write CSQLite3Base::Query method for executing a simple SQL query:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Query(string query)
  {
//--- check connection
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
//--- check query string
   if(StringLen(query)<=0)
      return(SQLITE_DONE);
   sqlite3_stmt_p64 stmt=0; // variable for pointer
//--- get pointer
   PTR64 pstmt=::memcpy(stmt,stmt,0);
   uchar str[];
   StringToCharArray(query,str);
//--- prepare statement and check result
   int res=::sqlite3_prepare(m_db,str,-1,pstmt,NULL);
   if(res!=SQLITE_OK)
      return(res);
//--- execute
   res=::sqlite3_step(pstmt);
//--- clean
   ::sqlite3_finalize(pstmt);
//--- return result
   return(res);
  }

As you can see, sqlite3_prepare, sqlite3_step and sqlite3_finalize functions are following one after another.

Consider execution of CSQLite3Base::Query when working with tables in SQLite:

// Create the table (CREATE TABLE)
sql3.Query("CREATE TABLE IF NOT EXISTS `TestQuery` (`ticket` INTEGER, `open_price` DOUBLE, `comment` TEXT)");

After executing this command, the table appears in the database:

// Rename the table  (ALTER TABLE  RENAME)
sql3.Query("ALTER TABLE `TestQuery` RENAME TO `Trades`");

// Add the column (ALTER TABLE  ADD COLUMN)
sql3.Query("ALTER TABLE `Trades` ADD COLUMN `profit`");

After executing these commands, we receive the table with a new name and an additional field:

// Add the row (INSERT INTO)
sql3.Query("INSERT INTO `Trades` VALUES(3, 1.212, 'info', 1)");

// Update the row (UPDATE)
sql3.Query("UPDATE `Trades` SET `open_price`=5.555, `comment`='New price'  WHERE(`ticket`=3)")

The following entry appears in the table after the new row is added and changed:

Finally, the following commands should be executed one after another to clean up the database.

// Delete all rows from the table (DELETE FROM)
sql3.Query("DELETE FROM `Trades`")

// Delete the table (DROP TABLE)
sql3.Query("DROP TABLE IF EXISTS `Trades`");

// Compact database (VACUUM)
sql3.Query("VACUUM");

Before moving to the next section, we need the method that receives an error description. From my own experience I can say that the error code can provide plenty of information but the error's description shows the place in SQL query text where an error has appeared simplifying its detection and fixing.

const PTR64 sqlite3_errmsg(sqlite3_p64 db);

db [in] - handle received by function sqlite3_open

The pointer is returned to the string containing the error description.

In the connector, we should add the method for receiving this string from the pointer using strcpy and strlen.

//+------------------------------------------------------------------+
//| Error message                                                    |
//+------------------------------------------------------------------+
string CSQLite3Base::ErrorMsg()
  {
   PTR64 pstr=::sqlite3_errmsg(m_db);  // get message string
   int len=::strlen(pstr);             // length of string
   uchar str[];
   ArrayResize(str,len+1);             // prepare buffer
   ::strcpy(str,pstr);                 // read string to buffer
   return(CharArrayToString(str));     // return string
  }


2.3. Getting Data from Tables

As I have already mentioned at the beginning of section 2.2, data reading is performed using sqlite3_column_хх functions. This can be schematically shown as follows:

  1. sqlite3_prepare
  2. sqlite3_column_count - find out the number of columns of the obtained table
  3. While current step result sqlite3_step == SQLITE_ROW
    1. sqlite3_column_хх - read the string cells
  4. sqlite3_finalize

Since we are approaching an extensive section concerning data reading and writing, it is a good time to describe three container classes used in the entire data exchange. The necessary data model depends on how the data is stored in the database:

Database
|
Table is an array of rows.
|
Row is an array of cells.
|
Cell is a byte buffer of an arbitrary length.


//+------------------------------------------------------------------+
//| CSQLite3Table class                                              |
//+------------------------------------------------------------------+
class CSQLite3Table
  {

public:
   string            m_colname[]; // column name
   CSQLite3Row       m_data[];    // database rows
//...
  };
//+------------------------------------------------------------------+
//| CSQLite3Row class                                                |
//+------------------------------------------------------------------+
class CSQLite3Row
  {

public:
   CSQLite3Cell      m_data[];
//...
  };
//+------------------------------------------------------------------+
//| CSQLite3Cell class                                               |
//+------------------------------------------------------------------+
class CSQLite3Cell
  {

public:
   enCellType        type;
   CByteImg          buf;
//...
  };

As you can see, CSQLite3Row and CSQLite3Table connections are primitive - these are conventional data arrays. CSQLite3Cell cell class also has uchar data array + Data type field. Byte array is implemented in CByteImage class (similar to well-known CFastFile).

I have created the following enumeration to facilitate connector's operation and manage cell data types:

enum enCellType
  {
   CT_UNDEF,
   CT_NULL,
   CT_INT,
   CT_INT64,
   CT_DBL,
   CT_TEXT,
   CT_BLOB,
   CT_LAST
  };

Note that CT_UNDEF type has been added to five basic SQLite3 types to identify the initial cell status. The entire INTEGER type divided into CT_INT and CT_INT64 ones according to similarly divided sqlite3_bind_intXX and sqlite3_column_intXX functions.

Getting Data

In order to get data from the cell, we should create the method generalizing sqlite3_column_хх type functions. It will check data type and size and write it to CSQLite3Cell.

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool CSQLite3Base::ReadStatement(sqlite3_stmt_p64 stmt,int column,CSQLite3Cell &cell)
  {
   cell.Clear();
   if(!stmt || column<0)
      return(false);
   int bytes=::sqlite3_column_bytes(stmt,column);
   int type=::sqlite3_column_type(stmt,column);
//---
   if(type==SQLITE_NULL)
      cell.type=CT_NULL;
   else if(type==SQLITE_INTEGER)
     {
      if(bytes<5)
         cell.Set(::sqlite3_column_int(stmt,column));
      else
         cell.Set(::sqlite3_column_int64(stmt,column));
     }
   else if(type==SQLITE_FLOAT)
      cell.Set(::sqlite3_column_double(stmt,column));
   else if(type==SQLITE_TEXT || type==SQLITE_BLOB)
     {
      uchar dst[];
      ArrayResize(dst,bytes);
      PTR64 ptr=0;
      if(type==SQLITE_TEXT)
         ptr=::sqlite3_column_text(stmt,column);
      else
         ptr=::sqlite3_column_blob(stmt,column);
      ::memcpy(dst,ptr,bytes);
      if(type==SQLITE_TEXT)
         cell.Set(CharArrayToString(dst));
      else
         cell.Set(dst);
     }
   return(true);
  }

The function is quite large, but it only reads data from the current statement and stores them in a cell.

We should also overload CSQLite3Base::Query function by adding CSQLite3Table container table for received data as the first parameter.

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Query(CSQLite3Table &tbl,string query)
  {
   tbl.Clear();
//--- check connection
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
//--- check query string
   if(StringLen(query)<=0)
      return(SQLITE_DONE);
//---
   sqlite3_stmt_p64 stmt=NULL;
   PTR64 pstmt=::memcpy(stmt,stmt,0);
   uchar str[]; StringToCharArray(query,str);
   int res=::sqlite3_prepare(m_db, str, -1, pstmt, NULL); if(res!=SQLITE_OK) return(res);
   int cols=::sqlite3_column_count(pstmt); // get column count
   bool b=true;
   while(::sqlite3_step(pstmt)==SQLITE_ROW) // in loop get row data
     {
      CSQLite3Row row; // row for table
      for(int i=0; i<cols; i++) // add cells to row
        {
         CSQLite3Cell cell;
         if(ReadStatement(pstmt,i,cell)) row.Add(cell); else { b=false; break; }
        }
      tbl.Add(row); // add row to table
      if(!b) break; // if error enabled
     }
// get column name
   for(int i=0; i<cols; i++)
     {
      PTR64 pstr=::sqlite3_column_name(pstmt,i); if(!pstr) { tbl.ColumnName(i,""); continue; }
      int len=::strlen(pstr);
      ArrayResize(str,len+1);
      ::strcpy(str,pstr);
      tbl.ColumnName(i,CharArrayToString(str));
     }
   ::sqlite3_finalize(stmt);  // clean
   return(b?SQLITE_DONE:res); // return result code
  }

We have all functions necessary for receiving data. Let's pass to their examples:

// Read data (SELECT)
CSQLite3Table tbl;
sql3.Query(tbl, "SELECT * FROM `Trades`")

Print out the result of the query in the terminal using the following command Print(TablePrint(tbl)). We will see the following entries in the journal (the order is from bottom to top):

// Sample calculation of stat. data from the tables (COUNT, MAX, AVG ...)
sql3.Query(tbl, "SELECT COUNT(*) FROM `Trades` WHERE(`profit`>0)")   
sql3.Query(tbl, "SELECT MAX(`ticket`) FROM `Trades`")
sql3.Query(tbl, "SELECT SUM(`profit`) AS `sumprof`, AVG(`profit`) AS `avgprof` FROM `Trades`")

// Get the names of all tables in the base
sql3.Query(tbl, "SELECT `name` FROM `sqlite_master` WHERE `type`='table' ORDER BY `name`;");

The query result is printed out the same way using Print(TablePrint(tbl)). We can see the existing table:

As can be seen from the examples, query execution results are placed into tbl variable. After that, you can easily obtain and process them at your discretion.


2.4. Writing Parameter Data with Binding

Another topic that can be of importance for newcomers is writing data to the database having "incovenient" format. Of course, we mean binary data here. It cannot be passed directly in a common INSERT or UPDATE text statement, as a string is considered complete when the first zero is encountered. The same issue occurs when the string itself contains single quotes '.

Late binding may be useful in some cases, especially when the table is wide. It would be difficult and unreliable to write all fields to a single line, as you can easily miss something. The functions of sqlite3_bind_хх series are necessary for the binding operation.

In order to apply binding, a template should be inserted instead of passed data. I will consider one of the cases - "?" sign. In other words, UPDATE query will look as follows:

UPDATE `Trades` SET `open_price`=?, `comment`=? WHERE(`ticket`=3)


Then, sqlite3_bind_double and sqlite3_bind_text functions should be executed one after another to place data to open_price and comment. Generally, working with bind functions can be represented the following way:

  1. sqlite3_prepare
  2. Call sqlite3_bind_хх one after another and write required data to the statement
  3. sqlite3_step
  4. sqlite3_finalize

By the number of types sqlite3_bind_xx completely repeats the reading functions described above. Thus, you can easily combine them in the connector at CSQLite3Base::BindStatement:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
bool CSQLite3Base::BindStatement(sqlite3_stmt_p64 stmt,int column,CSQLite3Cell &cell)
  {
   if(!stmt || column<0)
      return(false);
   int bytes=cell.buf.Len();
   enCellType type=cell.type;
//---
   if(type==CT_INT)        return(::sqlite3_bind_int(stmt, column+1, cell.buf.ViewInt())==SQLITE_OK);
   else if(type==CT_INT64) return(::sqlite3_bind_int64(stmt, column+1, cell.buf.ViewInt64())==SQLITE_OK);
   else if(type==CT_DBL)   return(::sqlite3_bind_double(stmt, column+1, cell.buf.ViewDouble())==SQLITE_OK);
   else if(type==CT_TEXT)  return(::sqlite3_bind_text(stmt, column+1, cell.buf.m_data, cell.buf.Len(), SQLITE_STATIC)==SQLITE_OK);
   else if(type==CT_BLOB)  return(::sqlite3_bind_blob(stmt, column+1, cell.buf.m_data, cell.buf.Len(), SQLITE_STATIC)==SQLITE_OK);
   else if(type==CT_NULL)  return(::sqlite3_bind_null(stmt, column+1)==SQLITE_OK);
   else                    return(::sqlite3_bind_null(stmt, column+1)==SQLITE_OK);
  }

The only objective of this method is to write the buffer of the passed cell to the statement.

Let's add CQLite3Table::QueryBind method in a similar way. Its first argument is data string for writing:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::QueryBind(CSQLite3Row &row,string query) // UPDATE <table> SET <row>=?, <row2>=?  WHERE (cond)
  {
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
//---
   if(StringLen(query)<=0 || ArraySize(row.m_data)<=0)
      return(SQLITE_DONE);
//---
   sqlite3_stmt_p64 stmt=NULL;
   PTR64 pstmt=::memcpy(stmt,stmt,0);
   uchar str[];
   StringToCharArray(query,str);
   int res=::sqlite3_prepare(m_db, str, -1, pstmt, NULL);
   if(res!=SQLITE_OK)
      return(res);
//---
   bool b=true;
   for(int i=0; i<ArraySize(row.m_data); i++)
     {
      if(!BindStatement(pstmt,i,row.m_data[i]))
        {
         b=false;
         break;
        }
     }
   if(b)
      res=::sqlite3_step(pstmt); // executed
   ::sqlite3_finalize(pstmt);    // clean
   return(b?res:SQLITE_ERROR);   // result
  }

Its objective is to write the string to the appropriate parameters.


2.5. Transactions / Multirow Inserts

Before proceeding with this topic, you need to know one more SQLite API function. In the previous section, I have described the three-stage handling of requests: prepare+step+finalize. However, there is an alternative (in some cases, simple or even critical) solution – sqlite3_exec function:

int sqlite3_exec(sqlite3_p64 ppDb, const char &sql[], PTR64 callback, PTR64 pvoid, PTRPTR64 errmsg);

ppDb [in] - database handle
sql  [in] - SQL query
The remaining three parameters are not considered yet in relation to MQL5.

It returns SQLITE_OK in case of success or else an error code.

Its main objective is to execute the query in a single call without creating three-stage constructions.

Let's add its call to the connector:

//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
int CSQLite3Base::Exec(string query)
  {
   if(!IsConnected())
      if(!Reconnect())
         return(SQLITE_ERROR);
   if(StringLen(query)<=0)
      return(SQLITE_DONE);
   uchar str[];
   StringToCharArray(query,str);
   int res=::sqlite3_exec(m_db,str,NULL,NULL,NULL);
   return(res);
  }

The resulting method is easy to use. For example, you can execute table deletion (DROP TABLE) or database compact (VACUUM) command the following way:

sql3.Exec("DROP TABLE `Trades`");

sql3.Exec("VACUUM");


Transactions

Now, suppose that we have to add several thousands of rows to the table. If we insert all this into the loop:

for (int i=0; i<N; i++)
   sql3.Query("INSERT INTO `Table` VALUES(1, 2, 'text')");

the execution will be very slow (more than 10(!) seconds). Thus, such implementation is not recommended in SQLite. The most appropriate solution here is to use transactions: all SQL statements are entered into a common list and then passed as a single query.

The following SQL statements are used to write transaction start and end:

BEGIN
...
COMMIT

All the contents is executed at the last COMMIT statement. ROLLBACK statement is used in case the loop should be interrupted or already added statements should not be executed.

As an example, all account deals are added to the table.

#include <MQH\Lib\SQLite3\SQLite3Base.mqh>
//+------------------------------------------------------------------+
//|                                                                  |
//+------------------------------------------------------------------+
void OnStart()
  {
   CSQLite3Base sql3;

//--- open database connection
   if(sql3.Connect("Deals.db3")!=SQLITE_OK) return;
//---
   if(sql3.Query("CREATE TABLE IF NOT EXISTS `Deals` (`ticket` INTEGER PRIMARY KEY, `open_price` DOUBLE, `profit` DOUBLE, `comment` TEXT)")!=SQLITE_DONE)
     {
      Print(sql3.ErrorMsg());
      return;
     }

//--- create transaction
   if(sql3.Exec("BEGIN")!=SQLITE_OK)
     {
      Print(sql3.ErrorMsg());
      return;
     }
   HistorySelect(0,TimeCurrent());
//--- dump all deals from terminal to table 
   for(int i=0; i<HistoryDealsTotal(); i++)
     {
      CSQLite3Row row;
      long ticket=(long)HistoryDealGetTicket(i);
      row.Add(ticket);
      row.Add(HistoryDealGetDouble(ticket, DEAL_PRICE));
      row.Add(HistoryDealGetDouble(ticket, DEAL_PROFIT));
      row.Add(HistoryDealGetString(ticket, DEAL_COMMENT));
      if(sql3.QueryBind(row,"REPLACE INTO `Deals` VALUES("+row.BindStr()+")")!=SQLITE_DONE)
        {
         sql3.Exec("ROLLBACK");
         Print(sql3.ErrorMsg());
         return;
        }
     }
//--- end transaction
   if(sql3.Exec("COMMIT")!=SQLITE_OK)
      return;

//--- get statistical information from table
   CSQLite3Table tbl;
   CSQLite3Cell cell;

   if(sql3.Query(tbl,"SELECT COUNT(*) FROM `Deals` WHERE(`profit`>0)")!=SQLITE_DONE)
     {
      Print(sql3.ErrorMsg());
      return;
     }
   tbl.Cell(0,0,cell);
   Print("Count(*)=",cell.GetInt64());
//---
   if(sql3.Query(tbl,"SELECT SUM(`profit`) AS `sumprof`, AVG(`profit`) AS `avgprof` FROM `Deals`")!=SQLITE_DONE)
     {
      Print(sql3.ErrorMsg());
      return;
     }
   tbl.Cell(0,0,cell);
   Print("SUM(`profit`)=",cell.GetDouble());
   tbl.Cell(0,1,cell);
   Print("AVG(`profit`)=",cell.GetDouble());
  }

After the script is applied to the account, it inserts account deals to the table instantly.

Statistics is displayed in the terminal journal

You can toy around with the script: comment out the lines containing BEGIN, ROLLBACK and COMMIT. If there are over hundreds of deals on your account, you will see the difference immediately. By the way, according to some tests, SQLite transactions work faster than in MySQL or PostgreSQL.


3. Compiling 64-Bit Version (sqlite3_64.dll)

  1. Download SQLite source code (amalgamation) and find sqlite3.c file.
  2. Download sqlite-dll-win32 and extract sqlite3.dll file from it.
  3. Execute LIB.EXE /DEF:sqlite3.def console command in the folder where dll file has been extracted. Make sure that the paths to lib.exe file are set in PATH system variable or find it in your Visual Studio.
  4. Create DLL project selecting Release configuration for 64 bit platforms.
  5. Add downloaded sqlite3.c and obtained sqlite3.def files to the project. If the compiler does not accept some functions from def file, just comment them out.
  6. The following parameters should be set in the project settings:
    C/C++ --> General --> Debug Information Format = Program Database (/Zi)
    C/C++ --> Precompiled Headers --> Create/Use Precompiled Header = Not Using Precompiled Headers (/Yu)
  7. Compile and get 64 bit dll.


Conclusion

I hope that the article will become your indispensable guide in mastering SQLite. Perhaps, you will use it in your future projects. This brief overview has provided some insight into the functionality of SQLite as a perfect and reliable solution for applications.

In this article, I have described all cases you may face when handling trading data. As a homework, I recommend that you develop a simple tick collector inserting ticks into the table for each symbol. You can find class library source code and test scripts in the attachment below.

I wish you good luck and big profits!

Translated from Russian by MetaQuotes Software Corp.
Original article: https://www.mql5.com/ru/articles/862

Attached files |
sql_ft__mql5.zip (662.69 KB)
Last comments | Go to discussion (3)
Alain Verleyen
Alain Verleyen | 28 Apr 2014 at 09:20
Wonderful idea and article, I didn't think to that. It would be great however to have a native support in mql5, so that could be used in Market's product.
dzsoni
dzsoni | 13 May 2014 at 09:55

First of all  warm thanks for your work!

I ported it to mql4 with success! (some #property strict, solve the compilation errors.)

And as I use  32bit platform I had to comment out and delete some 64bit related import functions. The terrminal uses early binding, so 64 bit-dll also try to load during the program load. (altough it is not used, only the 32bit.dll.). It made error. 

But anyway, I like your implementation! ( I wish I colud write so good prog as yours.)

268615
268615 | 12 Jan 2015 at 12:33
I am using a 64 bit version of MetaTrader 5 and am not able to get the code working. I tried removing the references to 32 bit code but still get an error "Sqlite3_32.dll" cannot load. Can you tell is the code referencing this dll anywhere ? I need to get it working for 64 bit.
Step on New Rails: Custom Indicators in MQL5 Step on New Rails: Custom Indicators in MQL5

I will not list all of the new possibilities and features of the new terminal and language. They are numerous, and some novelties are worth the discussion in a separate article. Also there is no code here, written with object-oriented programming, it is a too serous topic to be simply mentioned in a context as additional advantages for developers. In this article we will consider the indicators, their structure, drawing, types and their programming details, as compared to MQL4. I hope that this article will be useful both for beginners and experienced developers, maybe some of them will find something new.

Here Comes the New MetaTrader 5 and MQL5 Here Comes the New MetaTrader 5 and MQL5

This is just a brief review of MetaTrader 5. I can't describe all the system's new features for such a short time period - the testing started on 2009.09.09. This is a symbolical date, and I am sure it will be a lucky number. A few days have passed since I got the beta version of the MetaTrader 5 terminal and MQL5. I haven't managed to try all its features, but I am already impressed.

Using text files for storing input parameters of Expert Advisors, indicators and scripts Using text files for storing input parameters of Expert Advisors, indicators and scripts

The article describes the application of text files for storing dynamic objects, arrays and other variables used as properties of Expert Advisors, indicators and scripts. The files serve as a convenient addition to the functionality of standard tools offered by MQL languages.

How to create an indicator of non-standard charts for MetaTrader Market How to create an indicator of non-standard charts for MetaTrader Market

Through offline charts, programming in MQL4, and reasonable willingness, you can get a variety of chart types: "Point & Figure", "Renko", "Kagi", "Range bars", equivolume charts, etc. In this article, we will show how this can be achieved without using DLL, and therefore such "two-for-one" indicators can be published and purchased from the Market.