Discussion of article "SQL and MQL5: Working with SQLite Database"
There are a couple of important additions (and suggestions for improvement:))
1. The author uses the sqlite_open() function, but there is a more flexible function sqlite_open_v2(), which can work with opening flags, which means: - control access delimitation to the database file; - create temporary in-memory databases; - work with the database by URI, not only in the local file system, etc.
#define SQLITE_OPEN_READONLY 0x00000001 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_READWRITE 0x00000002 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_CREATE 0x00000004 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_DELETEONCLOSE 0x00000008 /* VFS only */ #define SQLITE_OPEN_EXCLUSIVE 0x00000010 /* VFS only */ #define SQLITE_OPEN_AUTOPROXY 0x00000020 /* VFS only */ #define SQLITE_OPEN_URI 0x00000040 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_MEMORY 0x00000080 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_MAIN_DB 0x00000100 /* VFS only */ #define SQLITE_OPEN_TEMP_DB 0x00000200 /* VFS only */ #define SQLITE_OPEN_TRANSIENT_DB 0x00000400 /* VFS only */ #define SQLITE_OPEN_MAIN_JOURNAL 0x00000800 /* VFS only */ #define SQLITE_OPEN_TEMP_JOURNAL 0x00001000 /* VFS only */ #define SQLITE_OPEN_SUBJOURNAL 0x00002000 /* VFS only */ #define SQLITE_OPEN_MASTER_JOURNAL 0x00004000 /* VFS only */ #define SQLITE_OPEN_NOMUTEX 0x00008000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_FULLMUTEX 0x00010000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_SHAREDCACHE 0x00020000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_PRIVATECACHE 0x00040000 /* Ok for sqlite3_open_v2() */ #define SQLITE_OPEN_WAL 0x00080000 /* VFS only */
2. I have been using sqlite to solve the problem of accelerating EA optimisation when it uses an indicator that is "heavy" in calculations. The logical solution in such a situation is to save the indicator readings for the optimisation period in the database during the first run, and during subsequent runs - to directly extract the already calculated readings from the database. So, if you use the sqlite engine in the "default form", the process of sending a lot of INSERT and SELECT queries starts to take a lot of time. Practically it was found that the way out is to use either 1) in-memory database, which is not always good if we want to leave the data for later and 2) #pragma-directives of SQL engine.
The second option is more preferable, because it eliminates the need to dump the database from RAM to disc. It is done in the following way: before creating the first table in the database it is necessary to send it the following queries:
"PRAGMA temp_store = MEMORY;" "PRAGMA page_size = 65536;" "PRAGMA cache_size = 16384;" "PRAGMA journal_mode = OFF;" "PRAGMA locking_mode = EXCLUSIVE;" "PRAGMA synchronous = OFF;"
Well, and after that you can
"CREATE TABLE IF NOT EXISTS"etc.
Good luck to everyone!
There are a couple of important additions (and suggestions for improvement:))
1. The author uses the sqlite_open() function, but there is a more flexible sqlite_open_v2() function that can work with opening flags, which means: - control access delimitation to the database file; - create temporary in-memory databases; - work with the database by URI, not only in the local file system, etc.
2. I have been using sqlite to solve the problem of accelerating EA optimisation when it uses an indicator that is "heavy" in calculations. The logical solution in such a situation is to save the indicator readings for the optimisation period in the database during the first run, and during subsequent runs - to directly extract the already calculated readings from the database. So, if you use the sqlite engine in the "default form", the process of sending a lot of INSERT and SELECT queries starts to take a lot of time. Practically it was found that the way out is to use either 1) in-memory database, which is not always good if we want to leave the data for later and 2) #pragma-directives of SQL engine.
The second option is more preferable, because it eliminates the need to dump the database from RAM to disc. It is done in the following way: before creating the first table in the database you should send the following queries to it:
Well, and after that you can
etc.
Good luck to everyone!
Data of indicator readings when running the tester are by nature a simple array, a stream with sequential access, so it is redundant and not rational to save and read it in SQL base.
The same can be said about the list of trade operations proposed by the author of the article as an example of SQLite application.
Therefore, we must realise that the efficiency of using relational, multi-linked data models, depends primarily on the tasks being solved, and in these examples IMHO, it may be only "pulled by the ears".
.
The data of indicator readings during the tester run is by its nature a simple array, a stream with serial access, so it is redundant and not rational to save and read it in the SQL base.
Read it again, even though it is redundant and not rational: to optimise the Expert Advisor
It is possible to use it and it is good. Another thing is that SQLite should not be used for serious projects. In any case, I would not recommend it. I myself have faced the problem of collisions on it more than once. For example, if a trading robot is attached to different charts, but uses the same base, and the address is to one general-purpose table (say registration/change sessions, accounts), then in any case you will get an error like "table locked". And it doesn't matter that all transactions are completed, cursors are closed and the database was opened in shared mode. This problem is known to SQLite developers as well.
In my opinion, MS Access is the best of file databases with SQL support. No matter how much you scold the small-soft people, but I left SQLite for MS Access and I don't regret it at all. OleDB driver Jet 4.0 is installed even with Win98, so that projects work on all OC Windows.
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.)
The script is not working, it doesn't work out of the box.
smoke the manual if you don't understand what to do and how to do it.
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
New article SQL and MQL5: Working with SQLite Database has been published:
This article is intended for developers who would be interested in using SQL in their projects. It explains the functionality and advantages of SQLite. The article does not require special knowledge of SQLite functions, yet minimum understanding of SQL would be beneficial.
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:
Author: o_O