Examples of CRUD operations in SQLite via ORM objects

We have studied all the functions required for the implementation of the complete lifecycle of information in the database, that is CRUD (Create, Read, Update, Delete). But before proceeding to practice, we need to complete the ORM layer.

From the previous few sections, it is already clear that the unit of work with the database is a record: it can be a record in a database table or an element in the results of a query. To read a single record at the ORM level, let's introduce the DBRow class. Each record is generated by an SQL query, so its handle is passed to the constructor.

As we know, a record can consist of several columns, the number and types of which allow us to find DatabaseColumn functions. To expose this information to an MQL program using DBRow, we reserved the relevant variables: columns and an array of structures DBRowColumn (the last one contains three fields for storing the name, type, and size of the column).

In addition, DBRow objects may, if necessary, cache in themselves the values obtained from the database. For this purpose, the data array of type MqlParam is used. Since we do not know in advance what type of values will be in a particular column, we use MqlParam as a kind of universal type Variant available in other programming environments.

The cursor variable tracks the current record number from the query results. Until the request is completed, cursor equals -1.

The virtual method DBread is responsible for executing the query; it calls DatabaseRead.

We will see later why we needed a virtual method. The public method next, which uses DBread, provides "scrolling" through the result records and looks like this.

If the query is accessed for the first time, we allocate memory and read the column information. If caching was requested, we additionally populate the data array. To do this, the overloaded operator '[]' is called for each column. In it, depending on the type of value, we call the appropriate DatabaseColumn function and put the resulting value in one or another field of the MqlParam structure.

The getBlob method is provided to fully read binary data from BLOB fields (use type uchar as S to get a byte array if there is no more specific information about the content format).

For the described methods, the process of executing a query and reading its results can be represented by the following pseudo-code (it leaves behind the scenes the existing DBSQLite and DBQuery classes, but we will bring them all together soon):

It is not elegant to explicitly write a loop through the columns every time, so the class provides a method for obtaining the values of all fields of the record.

Also, the class received for convenience overloads of the operator '[]' and the getBlob method for reading fields by their names instead of indexes. For example,

This way you can access selected columns.

But still getting the elements of the record individually, as a MqlParam array, can not be called a truly OOP approach. It would be preferable to read the entire database table record into an object, an application structure. Recall that the MQL5 API provides a suitable function: DatabaseReadBind. This is where we get the advantage of the ability to describe a derived class DBRow and override its virtual method DBRead.

This class of DBRowStruct is a template and expects as parameter S one of the simple structures allowed to be bound in DatabaseReadBind.

With a derived class, we can get objects from the base almost seamlessly.

Now it's time to turn the pseudo-code into working code by linking DBRow/DBRowStruct with DBQuery. In DBQuery, we add an autopointer to the DBRow object, which will contain data about the current record from the results of the query (if it was executed). Using an autopointer frees the calling code from worrying about freeing DBRow objects: they are deleted either with DBQuery or when re-created due to query restart (if required). The initialization of the DBRow or DBRowStruct object is completed by a template method start.

The DBValue type is a dummy structure that is needed only to instruct the program to create the underlying DBRow object, without violating the compilability of the line with the DatabaseReadBind call.

With the start method, all of the above pseudo-code fragments become working due to the following preparation of the request:

This example reads meta-information about the configuration of a particular table from the database (we created it in the example DBcreateTableFromStruct.mq5 in the section Executing queries without MQL5 data binding): each column is described by a separate record with several fields (SQLite standard), which is formalized in the structure DBTableColumn.

To save the user from having to write a loop every time with the translation of results records into structure objects, the DBQuery class provides a template method readAll that populates a referenced array of structures with information from the query results. A similar readAll method fills an array of pointers to DBRow objects (this is more suitable for receiving the results of synthetic queries with columns from different tables).

In a quartet of operations, the CRUD method DBRowStruct::get is responsible for the letter R (Read). To make the reading of an object more functionally complete, we will support point recovery of an object from the database by its identifier.

The vast majority of tables in SQLite databases have a primary key rowid (unless the developer for one reason or another used the "WITHOUT ROWID" option in the description), so the new read method will take a key value as a parameter. By default, the name of the table is assumed to be equal to the type of the receiving structure but can be changed to an alternative one through the table parameter. Considering that such a request is a one-time request and should return one record, it makes sense to place the read method directly to the class DBSQLite and manage short-lived objects DBQuery and DBRowStruct<S> inside.

The main work is done by the SQL query "SELECT * FROM '%s' WHERE %s=%ld;", which returns a record with all fields from the specified table by matching the rowid key.

Now you can create a specific object from the database like this (it is assumed that the identifier of interest to us must be stored somewhere).

Finally, in some complex cases where maximum flexibility in querying is required (for example, a combination of several tables, usually a SELECT with a JOIN, or nested queries), we still have to allow an explicit SQL command to get a selection, although this violates the ORM principle. This possibility is opened by the method DBSQLite::prepare, which we have already presented in the context of the management of prepared queries.

We have considered all the main ways of reading.

However, we don't have anything to read from the database yet, because we skipped the step of adding records.

Let's try to implement object creation (C). Recall that in our object concept, structure types semi-automatically define database tables (using DB_FIELD macros). For example, the Struct structure allowed the creation of a "Struct" table in the database with a set of columns corresponding to the fields of the structure. We provided this with a template method createTable in the DBSQLite class. Now, by analogy, you need to write a template method insert, which would add a record to this table.

An object of a structure is passed to the method, for the type of which the filled DBEntity<S>::prototype <S> array must exist (it is filled with macros). Thanks to this array, we can form a list of parameters (more precisely, their substitutes '?n'): this is done by the static method qlist. However, the preparation of the query is still half a battle. In the code below, we will need to bind the input data based on the properties of the object.

A "RETURNING rowid" statement has been added to the "INSERT" command, so when the query succeeds, we expect a single result row with one value: new rowid.

The source code of the insert method has one point to which special attention should be paid. To bind values to query parameters, we call the object.bindAll(q) method. This means that in the application structure that you want to integrate with the base, you need to implement such a method that provides all member variables for the engine.

In addition, to identify objects, it is assumed that there is a field with a primary key, and only the object "knows" what this field is. So, the structure has the rowid method, which serves a dual action: first, it transfers the record identifier assigned in the database to the object, and second, it allows finding out this identifier from the object, if it has already been assigned earlier.

The DBSQLite::update (U) method for changing a record is similar in many ways to insert, and therefore it is proposed to familiarize yourself with it. Its basis is the SQL query "UPDATE '%s' SET (%s)=(%s) WHERE rowid=%ld;", which is supposed to pass all the fields of the structure (bindAll() object) and key (rowid() object).

Finally, we mention that the point deletion (D) of a record by an object is implemented in the method DBSQLite::remove (word delete is an MQL5 operator).

Let's show all methods in an example script DBfillTableFromStructArray.mq5, where the Struct new structure is defined.

We will make several values of commonly used types as fields of the structure.

In the string field image, the calling code will specify the name of the graphic resource or the name of the file, and at the time of binding to the database, the corresponding binary data will be copied as a BLOB. Subsequently, when we read data from the database into Struct objects, the binary data will end up in the image string but, of course, with distortions (because the line will break on the first null byte). To accurately extract BLOBs from the database, you will need to call the method DBRow::getBlob (based on DatabaseColumnBlob).

Creating meta-information about fields of the Struct structure provides the following macros. Based on them, an MQL program can automatically create a table in the database for Struct objects, as well as initiate the binding of the data passed to the queries based on the properties of the objects (this binding should not be confused with the reverse binding for obtaining query results, i.e. DatabaseReadBind).

To fill a small test array of structures, the script has input variables: they specify a trio of currencies whose quotes will fall into the number field. We have also embedded two standard images into the script in order to test the work with BLOBs: they will "go" to the image field. The timestamp field will be automatically populated by our ORM classes with the current insertion or modification timestamp of the record. The primary key in the id field will have to be populated by SQLite itself.

Since the values for the input query variables (those same '?n') are bound, ultimately, using the functions DatabaseBind or DatabaseBindArray under the numbers, our bindAll structure in the method should establish a correspondence between the numbers and their fields: a simple numbering is assumed in the order of declaration.

Method rowid is very simple.

Having defined the structure, we describe a test array of 4 elements. Only 2 of them have attached images. All objects have zero identifiers because they are not yet in the database.

In the main OnStart function, we create or open a database (by default MQL5Book/DB/Example2.sqlite). Just in case, we try to delete the "Struct" table in order to ensure reproducibility of the results and debugging when the script is repeated, then we will create a table for the Struct structure.

Instead of adding objects one at a time, we use a loop:

In this loop, we will use an alternative implementation of the insert method, which takes an array of objects as input at once and processes them in a single request, which is more efficient (but the general ditch of the method is the previously considered insert method for one object).

Now let's try to select records from the database according to some conditions, for example, those that do not have an image assigned. To do this, let's prepare an SQL query wrapped in the DBQuery object, and then we get its results in two ways: through binding to Struct structures or via the instances of the generic class DBRow.

Both options should give the same result, albeit presented differently (see the log below).

Next, our script pauses for 1 second so that we can notice the changes in the timestamps of the next entries that we will change.

To objects in the result[] array, we assign the "yuan.bmp" image located in the folder next to the script. Then we update the objects in the database.

After running the script, you can make sure that all four records have BLOBs in the database navigator built into MetaEditor, as well as the difference in timestamps for the first two and the last two records.

Let's demonstrate the extraction of binary data. We will first see how a BLOB is mapped to the image string field (binary data is not for the log, we only do this for demonstration purposes).

Then we read the entire data with getBlob (total length is greater than the line above).

We need to get the temp.bmp.raw file, identical to MQL5/Files/Images/dollar.bmp.raw, which is created in the method Struct::bindAll for debugging purposes. Thus, it is easy to verify the exact correspondence of written and read binary data.

Note that since we are storing the resource's binary content in the database, it is not a BMP source file: resources produce color normalization and store a headerless array of pixels with meta-information about the image.

While running, the script generates a detailed log. In particular, the creation of a database and a table is marked with the following lines.

The SQL query for inserting an array of objects is prepared once and then executed many times with pre-binding different data (only one iteration is shown here). The number of DatabaseBind function calls matches the '?n' variables in the query ('?4' is automatically replaced by our classes with the SQL STRFTIME('%s') function call to get the current UTC timestamp).

Next, an array of structures with already assigned primary keys rowid is output to the log in the first column.

Selecting records without images gives the following result (we execute this query twice with different methods: the first time we fill the array of Struct structures, and the second is the DBRow array, from which for each field we get the "value" in the form of MqlParam).

The second part of the script updates a couple of found records without images and adds BLOBs to them.

Finally, when getting binary data in two ways — incompatible, via the image string field as a result of reading the entire DatabaseReadBind object (this is only done to visualize the sequence of bytes in the log) and compatible, via DatabaseRead and DatabaseColumnBlob — we get different results: of course, the second method is correct: the length and contents of the BLOB in 4096 bytes are restored.

Summarizing the intermediate result of developing our own ORM wrapper, we present a generalized scheme of its classes.

ORM Class Diagram (MQL5<->SQL)