Market Simulation (Part 22): Getting Started with SQL (V) - MQL5 Articles

Introduction

Welcome to another installment in our series on building a market replication/simulation system.

In the previous article, "Market Simulation (Part 21): First Steps in SQL (IV)", we explored, in an abstract sense, the distinction between relational and non-relational databases. Our primary goal there was to elucidate the fundamental operational principles of databases. This understanding is crucial because while it might seem logical to code certain functionalities from scratch, established implementations often provide ready-made solutions that achieve the desired outcome more efficiently.

This rationale underpins our current focus on SQL, rather than immediately diving into MQL5 programming. It's essential to establish a common understanding of why SQL is preferred over developing numerous custom routines to build such an implementation.

Specifically, this implementation will streamline the development of a command system for our replication/simulation system. We require an effective method to store order and position data to facilitate studies within the simulation environment.

From my perspective, creating extensive routines for this purpose is entirely unnecessary, especially since MQL5 supports SQLite integration. For those inclined to continuously write custom code, we'll demonstrate a superior alternative.

The time saved by not having to implement, test, and configure procedures for creating a database can be far more productively allocated to other aspects of the project.

Consequently, when we initiate the actual system development to make our replication/simulation system a viable alternative to demo accounts for strategy analysis, the process will be significantly faster and less prone to operational issues. This efficiency will stem from our use of SQL to construct the order system.

However, the concepts introduced in the previous article remain largely theoretical if not put into practice. Yet, this topic extends beyond our immediate project, potentially benefiting other areas—for instance, if you consider developing an Expert Advisor that learns to trade using a database.

To achieve this, you'll need to apply the principles outlined in the preceding article. A crucial point, which I'll state upfront: I do not intend (at least currently) to explain how to create a database for an MQL5 Expert Advisor to learn trading a specific symbol.

Such an explanation would entail delving into many other database concepts and principles. If this particularly interests you, I recommend an in-depth study of game algorithms.

While creating a database or programming an MQL5 Expert Advisor are relatively straightforward tasks, for a database to be truly effective and for an Expert Advisor to genuinely "learn" market behavior, you must know how to populate the database with relevant data. Studying game algorithms is the simplest way to grasp how to generate such data.

Alternatively, more complex avenues exist, like studying wave motion or even the principles of heat distribution. However, these approaches are considerably more challenging, though they might yield results similar to those from game algorithms.

Let's begin by practically applying the concepts from the previous article. As I prefer to break down topics for better comprehension, we'll proceed to the first section of this article.

Creating a Simple Database

We won't dwell on this topic for long, as it has been covered extensively in several prior articles on SQL. However, since the previous article suggested using a separate program for direct interaction with SQLite—the implementation we'll leverage through MQL5—we need to address specific details related to SQLite usage.

Our first point of focus will be the relationship between SQLite data types and the data we intend to store in the database. A notable feature of SQLite regarding data types is its more concise set. Far from being a disadvantage, this can be a significant benefit depending on the development context, offering an advantage over other implementations.

The second advantage I just mentioned is SQLite's dynamic data typing, contrasting with the static nature of many other implementations. You might wonder, "How can SQLite data be dynamic? And why is this an advantage?" To understand this, refer to the brief table below:

Notice that this table lists SQLite's types. Now, for comparison, let's examine how things appear in other SQL implementations. We'll start with MySQL, whose type table is shown below:

As you can see, there are many more types, and selecting the correct one is crucial to prevent future issues. The same applies to PostgreSQL, which has its own type table, shown below.

PostgreSQL, as you can see, offers even more types. One might conclude that this complexity could be overwhelming for anyone venturing into SQL.

However, this isn't entirely accurate. SQL is designed to adapt to our needs, enabling cross-application database usage within SQL environments.

The number of available types can either simplify or complicate type selection. Erroneous type selection might necessitate a database rebuild later, which, while a relatively simple task using purpose-built scripts, can be daunting for novices.

Observing these tables, it's immediately clear that SQLite's use of fewer types translates into less maintenance. However, this simplicity introduces other considerations not relevant here. What's germane is that SQLite's limited types facilitate dynamic typing; data types adjust based on the data being stored.

How does this manifest practically? To illustrate, we'll create two distinct databases: one using MySQL and another using SQLite.

Both utilize SQL as their foundational language. Let's see how this is done.

The animation below demonstrates the process in MySQL.

<img src=" width="700" height="400">

Pay close attention to the data types being used. The next animation shows a nearly identical procedure, but for SQLite.

<img src=" width="700" height="400">

Observe the types defined in the second animation. While the MySQL animation depicts script-based database creation, this isn't the case for SQLite.

For SQLite, a database must already be created and open in DB Browser. " If so, you've likely stumbled upon this article by chance.

I recommend reviewing previous articles where we demonstrated using the same code in both MySQL and SQLite, albeit with MetaEditor for SQLite execution.

However, in this context, the key difference is that the MySQL code defines column sizes during creation, whereas SQLite does not. Such dynamism can also be implemented in MySQL, but it's less common outside of SQLite implementations. This is because SQL programmers often prefer explicit type and field size definitions to optimize database characteristics, leading to greater maintenance effort across different implementations.

A natural question arises: can MySQL interpret columns created by SQLite, or vice versa? There's no need to worry.

Any SQL implementation can interpret the dimensionality used in columns. Hence, the tables above were presented to highlight the overlap in data types.

However, depending on the implementation, a programmer might choose a specific type to optimize certain database aspects.

Now that we've established these prerequisites, let's practically explore the roles of primary and foreign keys.

Primary and Foreign Keys in Practice

The animations above illustrate the creation of a very simple database designed to store a symbol name, its quote, and the date the quote was obtained. In a real-world scenario, a database programmer would rarely construct a database this way. Typically, one might want to include multiple symbols or additional information, such as the trading name of the company associated with the symbol, directly within the database.

It's also possible for a symbol's name to change, a rare but not unheard-of occurrence. For example, on B3, the company VIA VAREJO's ticker was VVAR3 for some time, then changed to VIIA3.

Imagine the effort required to update every historical record from VVAR3 to VIIA3. This task would be significantly simpler if the foundational database structure had been designed differently.

While this example is very basic, it's practical enough to illustrate a crucial point. Many perceive database creation and maintenance as straightforward, only to make significant errors that later demand immense effort to rectify.

Let's first examine a basic solution using SQLite. To simplify both the task and the explanation, refer to the SQL script below, which will be implemented.

101. PRAGMA FOREIGN_KEYS = ON;
202.
303. CREATE TABLE IF NOT EXISTS tb_Symbols
404. (
505.     id INTEGER PRIMARY KEY,
606.     symbol TEXT
707. );
808.
909. CREATE TABLE IF NOT EXISTS tb_Quotes
1010. (
1111.     of_day TEXT,
1212. --    symbol TEXT,
1313.     price NUMERIC,
1414.     fk_id INTEGER,
1515.     FOREIGN KEY (fk_id) REFERENCES tb_Symbol (id)
1616. );

Code in SQLite

Notice that this code already incorporates elements NOT native to standard SQL. Specifically, line 01 uses an internal SQLite instruction.

Why include this? 19 and thus required explicit activation.

Although this version is quite old, some users might still be using it. With this in mind, let’s analyze the script.

Remember, a database must be open in DB Browser for this script to execute correctly in SQLite.

Line 03 instructs to create the tb_Symbols table if it doesn't already exist. Within this table, line 05 declares a unique id as the primary key.

Line 06 defines the symbol name. Currently, we’re not limiting the number of records this table will hold, as we aim to demonstrate the evolution towards a more appropriate database construction model.

Therefore, although the id value is always unique, the same symbol can be defined across multiple ids. This is easily resolvable, but first, let’s understand how keys function here.

After creating tb_Symbols, we can modify (or, more accurately, create) a new table. This begins on line 09, where we specify the creation of the tb_Quotes table.

Note that line 12, shown as a comment here, was the original, as seen in the animation. Unlike the animation, the symbol name is no longer directly present in this table.

This change necessitates the addition of lines 14 and 15. Pay close attention to this crucial explanation: In line 14, we define a value (fk_id) that must match the data type of the tb_Symbols table's primary key (id).

While SQLite programmers sometimes omit type definitions, relying on the system, we define the type to maintain consistency.

This fk_id will serve as our foreign key in the tb_Quotes table. Line 15 is the most critical part of the script, where we designate fk_id as a foreign key and specify its reference: tb_Symbol (id). This means fk_id in tb_Quotes refers to the id column in the tb_Symbol table.

Through this, we establish a referential, or relational, system where quote information is logically separated from the symbol name but intrinsically linked. This relationship, facilitated by primary and foreign keys, demonstrates how a robust database can be built by connecting disparate pieces of information. This structure enables adding more or fewer data to records with minimal cost for maintaining or modifying the original database.

The preceding code can be refined as shown below, representing a more realistic scenario. However, it still doesn't address the potential for duplicate symbol names in the tb_Symbols table.

101. PRAGMA FOREIGN_KEYS = ON;
202.
303. CREATE TABLE IF NOT EXISTS tb_Symbols
404. (
505.     id INTEGER PRIMARY KEY,
606.     symbol TEXT
707. );
808.
909. CREATE TABLE IF NOT EXISTS tb_Quotes
1010. (
1111.     of_day TEXT,
1212.     price NUMERIC,
1313.     fk_id INTEGER REFERENCES tb_Symbol (id)
1414. );

Code in SQLite

Notice the slight modification on line 13. Its purpose remains the same: to link the tb_Quotes table with the tb_Symbols table using a foreign key.

It's more accurate to say we link a record from tb_Quotes to a record in tb_Symbols, rather than linking the tables themselves. Importantly, what data is stored in each table is flexible.

We can add more or fewer fields or columns without directly affecting search performance or results.

Furthermore, depending on requirements, additional information can be appended to one table, benefiting all linked tables without necessitating a full database rewrite. The significance of this might not be immediately apparent, but upon using SQL, you'll discover its immense value in expanding databases or creating relational systems between diverse information types.

Now, how do we prevent duplicate data within the database? It's quite simple: when creating the table, we specify that specific columns must either not contain null values or that their values must be unique. Let's modify the previous code to enforce both conditions: no null values and unique symbol names. The corrected code is as follows:

101. PRAGMA FOREIGN_KEYS = ON;
202.
303. CREATE TABLE IF NOT EXISTS tb_Symbols
404. (
505.     id INTEGER PRIMARY KEY NOT NULL,
606.     symbol TEXT NOT NULL UNIQUE
707. );
808.
909. CREATE TABLE IF NOT EXISTS tb_Quotes
1010. (
1111.     of_day TEXT NOT NULL,
1212.     price NUMERIC NOT NULL,
1313.     fk_id INTEGER NOT NULL,
1414.     FOREIGN KEY (fk_id) REFERENCES tb_Symbol (id)
1515. );

Code in SQLite

Upon executing this script, both tables will be created as intended, mirroring the explanation above. Crucially, attempting to duplicate any record will now result in an SQL error due to the uniqueness constraint.

Similarly, the tables will no longer accept null values where NOT NULL is specified. Notice the simplicity and clarity of this code.

How to Understand the Latest Script

Before moving to new concepts, it is vital to thoroughly understand how the last script functions. We truly need to grasp what's happening here to avoid getting completely lost later. After executing the final SQL script shown above, DB Browser will display the following result:

<img src=" width="700" height="471">

Observe that we have highlighted the two tables here. But how do we actually utilize them?

This might seem like a complex task reserved for master computer technologists. Not at all.

Using this schema is surprisingly straightforward. We've previously demonstrated how to add information to a table using a script in past SQL articles.

Here, we'll employ a very similar approach.

To illustrate the interaction with this modeling approach, let's add some sample data. Since we’ve also covered the basic SELECT command, we can use it here.

First, we'll add a few records to our database. Let's replace the previous script with a slightly modified version.

101. PRAGMA FOREIGN_KEYS = ON;
202.
303. DROP TABLE IF EXISTS tb_Quotes;
404. DROP TABLE IF EXISTS tb_Symbols;
505.
606. CREATE TABLE IF NOT EXISTS tb_Symbols
707. (
808.     id INTEGER PRIMARY KEY,
909.     symbol TEXT NOT NULL UNIQUE
1010. );
1111.
1212. CREATE TABLE IF NOT EXISTS tb_Quotes
1313. (
1414.     of_day TEXT NOT NULL,
1515.     price NUMERIC NOT NULL,
1616.     fk_id INTEGER NOT NULL,
1717.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
1818. );
1919.
2020. INSERT INTO tb_Symbols (id, symbol) VALUES(1, 'BOVA11');
2121. INSERT INTO tb_Symbols (id, symbol) VALUES(3, 'PETR4');
2222. INSERT INTO tb_Symbols (id, symbol) VALUES(2, 'WDOQ23');
2323. INSERT INTO tb_Symbols (id, symbol) VALUES(4, 'VALE3');
2424. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES ('17-07-2023', 12.90, 4);
2525. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES ('14-07-2023', 118.12, 2);
2626. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES ('13-07-2023', 119.53, 1);
2727. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES ('12-07-2023', 117.45, 2);
2828. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES ('11-07-2023', 119.30, 3);
2929. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES ('10-07-2023', 120.59, 1);
3030.
3131. SELECT * FROM tb_Quotes;

Script in SQLite

You might be looking at this and thinking, "Wow, this code is complicated; I'll never understand it. " But I ask you: are you going to give up now, just when things are getting more interesting?

After all, there's nothing complicated in the code above. Everything it contains has already been explained in this introductory stage covering SQL language usage.

Perhaps the only potentially confusing part is lines 03 and 04, where we instruct SQL to remove tb_Quotes and tb_Symbols if they exist. Why remove tables before we've even had a chance to use them?

More importantly, why remove them at all? Couldn't we just retain them and add new data to the database?

There's a single answer to all these questions. We need to delete the tables precisely because lines 20 through 29 insert values into them.

If this script were run multiple times, SQL would report an error upon attempting to add values to the tables. This error occurs because we are prevented from duplicating certain fields within the database, as previously established.

After running this script, we'll get the following result:

<img src=" width="700" height="471">

Note that the result displays the exact content of the tb_Quotes table. This is because, on line 31, we requested this information from SQL.

However, simply viewing it can be somewhat disheartening. If we have a very large database with quotes for various symbols, it becomes challenging to identify where each quote belongs, as the response doesn't provide the symbol name or any other context that would help us better understand the data we're querying.

Soon, we'll see how this can be improved with a minor adjustment to the SELECT command on line 31.

Final Thoughts

Before you give up and decide to abandon learning SQL, let me remind you, dear readers, that we are still only covering the most basic elements here. We haven't yet explored some of SQL's more powerful capabilities.

Once you understand them, you will see that SQL is far more practical than it initially appears. While it's likely we'll eventually shift the direction of our current project, as the development process is dynamic, we'll delve further into creating various elements in SQL because it is truly important and useful for you.

Simply assuming you are more capable than an entire community of programmers and developers will only lead to wasted time and opportunities. Rest assured, what comes next will be even more engaging.

As I mentioned, I am still undecided on whether to use SQLite or another SQL implementation. This is due to the inherently dynamic nature of developing the components required for the replication/simulation system.

We haven't yet definitively chosen our next direction. Regardless of how events unfold in the subsequent stages, when we return to MQL5, we will undoubtedly leverage SQL to solve various tasks.

I will not create custom routines to perform functionalities that SQL already offers and assists with. However, the precise practical application of SQL has not yet been finalized.

In the next article, we will explore additional SQL operations. Things are truly gaining momentum now, and the topics we'll examine and explain are almost entirely mapped out.

I want to emphasize, however, that these articles are not an SQL course. My aim is simply to highlight a few points that may prove useful in the future, as there is little sense in using MQL5 or any other language for tasks SQL itself can handle.

This applies not only to database creation and maintenance but to programming in general.

Many people know only a few SQL commands, but they either forget them or lack sufficient interest in deepening their knowledge, thus missing the opportunity to utilize a powerful tool. Many procedures that some recreate in another language integrated with SQL can be performed directly within SQL.

Knowing how to do so will greatly assist in your development efforts. Therefore, strive to deepen your understanding of this topic.

Use these articles merely as a guide; do not consider them the sole or definitive source of information. A hug to everyone; see you in the next article.

Reference

SQLite documentation