Operations with SQL databases

MetaEditor provides options for convenient operations with databases. These capabilities are implemented based on the popular SQLite engine integration. The entire database is located in a single file on a user PC's hard disk.

The editor provides access to the main functions for working with databases, allowing you to:

• Create and connect databases
• View tables and perform quick data queries
• Create and execute SQL queries, rollback changes

The development of trading strategies is associated with processing of large amounts of data, and that is why databases are widely used in algo trading. The usage of databases will enable you to:

• Analyze trading history and quotes
• Save and analyze optimization and testing results
• Prepare and exchange data with other analysis packages
• Store settings and MQL5 program states

Creating a database #

The quick database creation functionality is available from the MQL5 Wizard. You can easily create your first table and define its fields.

The following field types are available:

• integer – integer values
• real – real values
• text – string values
• blob – arrays of binary data

Fields can also be marked with flags:

• primary – a primary key which uniquely identifies each record in the table. Only one field in a table can be used as a primary key. Values in this field must be unique.
• unique – a field with values, which cannot be repeated. An attempt to write an already existing value will cause an error. You can use this flag for the fields, which must have unique values. For example, it can be set for a field used for trade tickets.

You can also create databases by using the Navigator context menu:

• Create database – creates an empty database file of the selected format. The .db extension is used by default.
• Create from file – create a database based on an existing *.SQL database.

Once the database is created, you will be redirected to the appropriate Navigator section. All data operations are performed from this section.

Import tables #

You can create tables in a database based on ready-made CSV-files. Click "Import Table" in the database menu, select a file and set the following parameters:

• Table name in the database.
• Automatic or manual file encoding detection.
• Data separator: comma, semicolon, tab, or space.
• Skipping of the specified number of lines at the beginning.
• Comment prefix.
• Whether the file has column names. If you enable this option, values from the first line of the file will be imported as column names.
• How line breaks will be determined: line feed only (LF, default) or carriage return and line feed (CRLF) characters.
• Whether data should be added to a new table or to an existing one.
• Which quotes are used for strings in the file: single or double. Quotes will be removed during import.

Working with the database #

The Navigator provides a separate tab for working with databases. Click "Open" in its context menu or in the "File" menu, and select the database file. Appropriate tables will appear in the Navigator.

Double-click on the table name to quickly query the first 1,000 records.

To execute a database query, enter it in the right part of the editor and click "Execute". In case of a query error, the corresponding message will be added to the log. An example of a simple query creating a table:

The COMPANY table has 5 fields: Record ID, Name, Age, Address and Salary. The ID field serves as a key. The key enables unique identification of each record and it can be used in different tables to link them together. This is similar to how a position ID links all deals and orders related to a particular position.

If a table column contains time data specified in minutes (UNIX time), seconds or microseconds since 1970.01.01, left-click on it and select the required format. After that, the time will be displayed in the usual format, YYYY.MM.DD hh:mm:ss.

To save a table as a file, query all data from the table using a query "SELECT * FROM [table name]", and then click "Export" in the context menu. The export operation provides the same table options as import.