SQL and MQL5: Working with SQLite Database
--- | 18 April, 2014
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:
- you can use the DLL file with a complete set of API functions;
- you can use shell commands to an EXE file;
- 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:
- sqlite3_prepare - verification and receiving the list of statements;
- sqlite3_step - executing these statements;
- 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:
- sqlite3_prepare
- sqlite3_column_count - find out the number of columns of the obtained table
- While current step result sqlite3_step == SQLITE_ROW
- sqlite3_column_хх - read the string cells
- 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:
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:
- sqlite3_prepare
- Call sqlite3_bind_хх one after another and write required data to the statement
- sqlite3_step
- 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)
- Download SQLite source code (amalgamation) and find sqlite3.c file.
- Download sqlite-dll-win32 and extract sqlite3.dll file from it.
- 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.
- Create DLL project selecting Release configuration for 64 bit platforms.
- 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.
- 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)
- 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!