Market Simulation (Part 19): First Steps in SQL (II)

Introduction

Hello everyone, and welcome to another article in the series on how to build a replication/modeling system.

In the previous article, «Market Simulation (Part 18): First Steps in SQL (I)», we began exploring the first commands that can be used in SQL. The goal was to create an initial database for obtaining information and performing future queries. We showed that in MetaEditor you can use the same things as in any SQL program or script. This means we can also use the same code, but inside an executable file created in MQL5. As a result, it can be run directly from MetaTrader 5.

However, there is a command in SQL that will not work if we try to execute it using MetaEditor. It will return an error when we attempt to run it. But I want to make one thing absolutely clear: all SQL commands can be used without any problems in MQL5 and MetaEditor. This is because MetaTrader 5 uses SQLite. Although we can use any command inside an MQL5 executable file, this is not possible directly in MetaEditor. To avoid misunderstandings, let’s take a look at which command we’re talking about.

The SQL Command That MetaEditor Will Never Execute

So, now it’s time to experiment. We’re just starting to learn, so the «side effects» will be minimal. However, I want to warn you that you should never execute any SQL commands on an important database without first understanding what they do. If you’re going to try something new, do it as safely as possible to avoid trouble later. As the saying goes, faith can move mountains, but it won’t restore your data.

Now let’s take a look at this «magic» command. In the animation below, you can see what it’s about.

Please note that the DROP command, when used in SQL, allows us to permanently delete something. If we use this command without proper care, we can run into serious problems. Be aware that SQL will not ask for any confirmation—it will simply delete whatever we specify.

However, when using MetaEditor, the database file itself will not be deleted. But if we send the same command that caused an error in the animation for SQL execution, our database file will indeed be deleted.

At first, we might mistakenly think that MetaEditor refuses to execute the DROP command. But as you could see in the animation, that is not the case, since it was possible to delete a previously created table.

You might also think that if we try to use the CREATE DATABASE command in MetaEditor, it won’t work—just like the USE command—and that is true. However, both commands are implemented in such a way that we don’t need to enter them manually.

That said, MetaEditor does not delete files from the disk-based database. So there is one key difference: for security reasons, MetaEditor does not allow the execution of the DROP DATABASE command, which in SQL means deleting the database file itself.

So, we have just demonstrated (and you can verify this) that there is an SQL command that MetaEditor does not execute. Apart from this command, all others will run without issues. This helps explain the DROP command, which is nothing more than a command for permanently deleting something—whether it’s a column, a table, or even an entire database. Be very careful with this command, as it does not ask for confirmation before executing.

There are ways to prevent accidental deletions without proper permissions, but that would require a more in-depth explanation of how to work with SQL, which is not our goal here. Our goal is to give you the foundation needed to understand what we will do next—namely, using SQL within an executable file created in MQL5.

Inserting data into a database

The next command we will look at is responsible for inserting information into a database. I do not recommend editing the database file manually. The correct approach is always to use SQL commands for this purpose. It doesn’t matter whether the database is small or contains only a few columns and records—you should never edit it manually. Always use SQL commands.

The command used to insert data into a database is quite simple, but you should be careful. In fact, you need to pay attention when using it, as it works on a «key–value» principle. Anyone familiar with programming in Python will quickly understand this idea.

This is because the SQL command for inserting data into a database is very similar to creating a dictionary in Python. In other words, you use a key and assign it a value. However, in SQL, things work a bit differently. Still, the core idea remains the same. Let's see how it actually works.

Now, we won’t show this same command in MetaEditor. I believe it’s already clear that the commands we need to execute will be the same, so there’s no point in repeating the same thing. To simplify the explanation of the command, we will use a small SQL code example that is very simple and easy to understand.

Take a look at the code below:

01. CREATE DATABASE IF NOT EXISTS MT5_Tutor_DB;
02. USE MT5_Tutor_DB;
03. 
04. CREATE TABLE IF NOT EXISTS tb_Quotes
05. (
06.     of_day DATE,
07.     symbol CHAR(6),
08.     price DECIMAL(5, 2)
09. );
10. 
11. INSERT INTO tb_Quotes (of_day, symbol , price)
12.                VALUES ('2023-07-06', 'BOVA11' , 105.61);
13. 
14. SELECT * FROM tb_Quotes;

SQL Script

For now, don’t worry about line 14. At this stage, it’s only there so we can view the result more easily. This code can be used both in MetaEditor and directly on an SQL server. However, if you plan to use it in MetaEditor, keep in mind that lines 01 and 02 must be executed differently, as explained in previous articles. If in doubt, refer back to those articles to understand how to proceed in MetaEditor and run the same code. What matters to us here are lines 4 and 11, where we create the table and insert data, respectively.

Although this code works, it should not be used when creating a database. The reason is that it allows data to be duplicated indefinitely. And if you know anything about databases, you understand that duplicate values should be avoided. Databases are designed to prevent such situations. However, even with that goal, if SQL scripts are written in an amateur way, duplicate values will likely occur. This amateur approach is visible in the code above. Still, since our goal here is to explain how to add values to a database, we can tolerate duplicates and even some insertion errors, such as fields with NULL values.

To understand what’s happening, note that on line 04 we create the table only if it does not already exist in the database. This table will have the following columns. For example, on line 06 we specify that we want a column whose content represents a date.

On line 07, we define a column with a width of six characters. This allows us to store the name of the symbol. This naming convention follows the standard used by B3 (the Brazilian stock exchange).

On line 08, we specify a field with a width of five characters, two of which are used for decimal values. This allows us to store prices from 0 to 999.99, which is more than sufficient for symbols traded on B3. Note that we are not defining any keys or constraints. The absence of these constraints is what allows duplicate values to exist.

Now let’s look at what happens on line 11. Notice that the command to insert data into the table begins with INSERT INTO. Immediately after that, we specify which table the values will be inserted into —in our case, tb_Quotes, which we just created. Then we declare the names of the columns that will receive the values.

Now pay attention: we can list the columns in any order, but we must use the exact same names as defined in the table. If we use a different name, the command will result in an error. After defining the column order, we add the word VALUES and begin specifying the values to be inserted. At this stage, the values must follow the same order as the declared columns. In other words, if we declare the price column before the symbol column, we must provide the price before the symbol in the values list.

This may seem a bit confusing, but if you use the code below, you will get the same result as in the script shown above for inserting values.

INSERT INTO tb_Quotes (of_day, price, symbol)
               VALUES ('2023-07-06', 105.61, 'BOVA11');

SQL Script

Please note that I changed the order in which the columns are declared. Therefore, I also had to change the order of the values. This is the main aspect you should pay attention to. Now you might wonder: why is the date specified in this particular format? The reason is that this is the format expected by MySQL —that is, year first, then month, and finally day. Some databases allow other formats, which are then converted to this format when the data is stored. An example is SQL Server, which allows the day–month–year format. In any case, inside the database, the format will always be year–month–day.

If you run the script shown above, the result will display accordingly.

Now pay attention: if you execute line 11 again, the same information will be duplicated in the database. The result will reflect this duplication:

You might think this isn’t a problem—that you can simply delete the duplicate row. Yes, it’s easy to remove it, but first you need to understand that this example is simple. Now imagine a real database: it may contain a huge number of records. Having duplicate entries can make the database completely unusable. It also makes it difficult to modify or update specific records. That’s why creating a database requires careful study and planning.

Although the script above is conceptually correct (since it runs), it is not ideal because it allows duplicate data. Moreover, it does not allow modifying or updating records. To better understand this, let’s use another way of inserting data into the database. Instead of declaring all columns and values at once, we will do it step by step, which is actually common in many scenarios. Thus, the code to insert the same values as in the previous script would look like this:

01. CREATE DATABASE IF NOT EXISTS MT5_Tutor_DB;
02. USE MT5_Tutor_DB;
03. 
04. CREATE TABLE IF NOT EXISTS tb_Quotes
05. (
06.     of_day DATE,
07.     symbol CHAR(6),
08.     price DECIMAL(5, 2)
09. );
10. 
11. INSERT INTO tb_Quotes (of_day) VALUES ('2023-07-06');
12. INSERT INTO tb_Quotes (symbol) VALUES ('BOVA11');
13. INSERT INTO tb_Quotes (price) VALUES (105.61);
14.                
15. SELECT * FROM tb_Quotes;

SQL Script

Notice that here we are literally using the «key–value» concept, similar to how dictionaries are created in Python. And yes, we can do this in SQL, and the database will store the values in the specified table. However, pay attention to the result of executing this script— you can see it just below.

«But what happened? Why did we get this result instead of the previous one?» There are two reasons for this. The first is that we are simply adding data to the database. The second is that the database is structured in a way that allows duplicate values. If we try to modify any record, we won’t be able to do so if the database is created as shown in these scripts. We need to create some way to select a specific record so that we can manipulate it and change its value. This way, we’ll be able to insert data one by one using the «key–value» format. But how can we do that?

The first step is to turn one of the columns into a unique key. This is the starting point. By doing so, we prevent duplicate records in the database. But an important question arises: what is the best way to do this?

Some people usually create a separate column just for this purpose, which is not a bad idea. However, the type of solution depends on each specific case. In our case, there is no need to create an additional column just to serve as a unique key. This can be done simply by choosing an appropriate existing column. So, let's think about it.

In the «Price» column, values may sometimes be different and sometimes the same, so it may not be a good choice to make it unique. However, the column containing the symbol name will repeat in every new record. The date column is a possible candidate.

But why «possible» and not «definite»? The reason lies in how frequently we plan to insert new records into the database. And why does that matter? Because if we are going to insert records at very short intervals, we may need to create a separate column specifically for that purpose. But if records are inserted once per day, then the date alone may be sufficient.

Thus, if we want to store the daily price of a symbol, we can modify the first script shown earlier as follows:

01. CREATE DATABASE IF NOT EXISTS MT5_Tutor_DB;
02. USE MT5_Tutor_DB;
03. 
04. CREATE TABLE IF NOT EXISTS tb_Quotes
05. (
06.     of_day DATE PRIMARY KEY,
07.     symbol CHAR(6),
08.     price DECIMAL(5, 2)
09. );
10. 
11. INSERT INTO tb_Quotes (of_day, symbol , price)
12.                VALUES ('2023-07-06', 'BOVA11' , 105.61);
13. 
14. SELECT * FROM tb_Quotes;

SQL Script

Notice that everything that was changed in the first script is precisely in line 06, where we added the term PRIMARY KEY. At this point, we are indicating that the value of the of_day column will be the primary key of this table. We can have more than one column as a primary key. However, we CANNOT HAVE duplicate values in the primary key. If we attempt to do so, SQL will treat this as an error and the command will not be executed.

Thus, if we try to execute the INSERT INTO command again with the same date that already exists in the database, we will not succeed. We will need to specify a different date so that the record we are trying to insert is accepted by SQL. This very simple measure ensures database consistency and prevents duplicate records. As previously mentioned, one simple measure solved a whole range of problems.

The result of executing the above script is shown in the image below:

Please note that it differs from the other images. However, at the time of the query, the result will be the same. Naturally, you will think that now we will be able to insert data in a «key-value» format, or more precisely, «column-value». Yes, we can do that. So your first idea would be to create a script, as shown below:

01. CREATE DATABASE IF NOT EXISTS MT5_Tutor_DB;
02. USE MT5_Tutor_DB;
03. 
04. CREATE TABLE IF NOT EXISTS tb_Quotes
05. (
06.     of_day DATE PRIMARY KEY,
07.     symbol CHAR(6),
08.     price DECIMAL(5, 2)
09. );
10. 
11. INSERT INTO tb_Quotes (of_day) VALUES ('2023-07-07');
12. INSERT INTO tb_Quotes (symbol) VALUES ('BOVA11');
13. INSERT INTO tb_Quotes (price) VALUES (105.61);
14. 
15. SELECT * FROM tb_Quotes;

SQL Script

Remember that this script is executed immediately after obtaining the value in of_day. Thus, to prevent SQL from blocking the creation of the record, we change the date. In effect, we are using logic and demonstrating that we understand how primary keys work. However, when executing this script, we encounter an error. And, since this is of professional interest, we try to see what happened in the database, and we get an image like this:

Now you might ask: why did an error occur if the record was created? The reason is that in line 12, we are trying to insert information. In doing so, SQL will attempt to create a new record. But wait a minute. Why should SQL create a new record if I did not specify that we are creating a new primary key? I thought that as long as we are using a primary key, we would be using the same record. In fact, many people initially make this mistake because they do not understand the essence of the concept.

But let us clarify the situation. When we use the INSERT INTO command, we are creating a new record in the database. However, to modify this record (which is precisely what is needed in this case), we must use a different command. Let us now separate these matters and examine them in another topic.

Modifying and updating a record

For SQL to know which record to modify or update, we need a unique key, that is, a primary key. Since we have defined the of_day value as the primary key, the first thing we need to do is try to add it to the database. If it already exists, SQL will not allow a new record to be created.

After defining and registering the primary key in the database, we can modify or update the values of other columns. Note: do not attempt to change the value of the primary key to avoid problems. We need to create a key and then update the registry. Below is the code that performs the update to create a new record using the key-value concept, or more precisely, column-value:

01. CREATE DATABASE IF NOT EXISTS MT5_Tutor_DB;
02. USE MT5_Tutor_DB;
03. 
04. CREATE TABLE IF NOT EXISTS tb_Quotes
05. (
06.     of_day DATE PRIMARY KEY,
07.     symbol CHAR(6),
08.     price DECIMAL(5, 2)
09. );
10. 
11. INSERT INTO tb_Quotes (of_day) VALUES ('2023-07-07');
12. UPDATE tb_Quotes SET symbol = 'BOVA11' WHERE of_day = '2023-07-07';
13. UPDATE tb_Quotes SET price = 105.61 WHERE of_day = '2023-07-07';
14. 
15. SELECT * FROM tb_Quotes;

SQL Script

The result of executing this script can be seen below:

Please note that we take into account the fact that before executing the above script, a record already exists. Therefore, we proceeded in this manner. Then, in line 11, we create a key to indicate that we will add a new record to the database. Once we have created this key, we use the UPDATE command as follows:

1. Immediately after the command name, we specify the table name.
2. After the table name, we use the SET command; it indicates that we are going to modify or update a certain column.
3. Next, we have the column name with its corresponding value, which we will enter into it.
4. For SQL to find the record that needs to be updated, we use the WHERE command, specifying which key will be used.

Here are a few more questions. But we will not complicate matters. Let us keep everything as simple as possible. The goal is to provide you with a foundation for using SQL alongside MetaTrader 5, not to conduct a full SQL course. However, I suggest we delve a little deeper into today's material, because we can do much more here to speed up SQL and avoid overloading it with inefficient queries. This is about didactics, not efficiency.

Alright, now we know how to insert, modify, and update a record. We only need to understand how to delete a record. But again, let us separate the material to avoid confusion.

Deleting a record

Deleting a record from the database is a very simple matter. There are several ways to delete entire branches of records from the database. But, I repeat, we will look at the simplest method so that you can at least delete a record from the database.

To do this, let us assume the following: you added a record for July 7, 2023, but then realized that there was no trading on that day. Therefore, you want to delete this record from the database, since an invalid record could ruin the entire database. One way to do this is to use the following script:

1. USE MT5_Tutor_DB;
2. 
3. DELETE FROM tb_Quotes WHERE of_day = '2023-07-07';
4. 
5. SELECT * FROM tb_Quotes;

SQL Script

Please note that we first select the database in line 01. In line 03, we send a request to delete a record from the database. How was this done? In the simplest way. That is, we first specify the command name, more precisely: DELETE FROM; then the name of the table where the record is located, in this case, tb_Quotes. Next, we indicate what will be deleted. To do this, we use the WHERE command, followed by information that tells SQL what kind of match to look for. In our case, we use the primary key of the day we want to delete from the database.

To complete the script, in line 05 we ask SQL to show us the table. Did you notice how simple it is to delete a record? But the method shown is only one of many possible.

Final thoughts

In this article, we tried to simplify the material as much as possible. I know there are much better ways to do the same thing. If you already have experience and knowledge of SQL programs, you know very well that all the content presented here is the true foundation of SQL. For those who do not know, the term «rice and beans» is a regional expression used to say that we need the bare minimum of things, but at the same time some minimal variety so that we can be satisfied.

But going back to our topic, I want to prepare you a little so that you can do something in SQL using these articles. I do not want you to waste time programming pointless things, creating different subroutines to achieve what is already contained in SQL.

I do this so that you are motivated to always seek adequate and more extensive knowledge, so that you do not stay in your comfort zone and waste time programming things that could be done much more simply, efficiently, and safely if you used the appropriate tool.

Often, many people frown in misunderstanding when they see a programmer or a group of programmers using some tool and say that it does not align with their personal interests. But I want to tell you, dear readers striving to become professional programmers, that you should forget about vanity. Start using the available tools. You will become much more productive, more confident in yourself and in what you do, and, most importantly, you will be much more valued. After all, while others are racking their brains searching for a solution, you, thanks to your more extensive knowledge, will immediately find the right tool and easily create the solution the client needs. The message of this article is the following: 

The world of technology evolves very quickly. By the time we understand something, it is already obsolete.

Think about that. See you in the next article. There are still a few more points that need to be explained about SQL before we can start creating something for our replay/simulation system using the combination of SQL and MQL5. So, see you later.

File	Description
Experts\Expert Advisor.mq5	Demonstrates interaction between Chart Trade and the expert advisor (Mouse Study required for interaction).
Indicators\Chart Trade.mq5	Creates a window for configuring the order to be sent (Mouse Study required for interaction).
Indicators\Market Replay.mq5	Creates controls for interacting with the replay/simulation service (Mouse Study required for interaction).
Indicators\Mouse Study.mq5	Provides interaction between graphical controls and the user (required for both the replay system and live market operations).
Servicios\Market Replay.mq5	Creates and maintains the market replay/simulation service (main file of the entire system).
VS C++ Code Server.cpp	Creates and maintains a socket server, developed in C++ (mini-chat version).
Python Code Server.py	Creates and maintains a Python socket for communication between MetaTrader 5 and Excel.
Indicators\Mini Chat.mq5	Enables a mini-chat via an indicator (server usage required).
Experts\Mini Chat.mq5	Enables a mini-chat using an expert advisor (server required).
Scripts\SQLite.mq5	Demonstrates the use of an SQL script with MQL5.
Files\Script 01.sql	Demonstrates the creation of a simple table with a foreign key.
Files\Script 02.sql	Demonstrates the addition of values to a table.