Selection of a DBMS for a mobile Delphi application
This article is based on - the motives for developing a mobile application, recently released on iOS and Android platforms. This event could be called mediocre and very few people interesting if it were not for one big and several minor “buts”: the whole development (including the server) was conducted on Delphi, and strangely enough, it was not SQLite that was used as a DBMS. The author certainly understands that currently there are mobile Delphi applications already available, including those available in official stores, but does not observe the abundance of Russian-language publications designed, at a minimum, to warn readers against mistakes made by the developer of such projects. What is written here aims to help those who are now choosing a DBMS for their creation, or have already stopped at some version, but want to make sure that their decision is correct.
Before you begin, you need to give a brief idea of ​​the developed application - this is a shopping list, having in its stock some unique functionality. If someone already uses (or has used) one of the many analogues, now he could skeptically snort - they say why another one, and so there is plenty to choose from, which can only be advised to continue reading to familiarize yourself with its principal feature.
SQLite is the undisputed leader of mobile DBMS, however, some of its shortcomings and the application’s functionality that requires non-trivial data analysis didn’t allow to dwell on it - the search for alternatives led to Interbase XE7 , more precisely to its embedded version, which Delphi comes with and requires a minimum of effort to be included in the application. Interbase, of course, is not an ideal either, and it has minuses, the ways of dealing with which are given below. It is important to note that this DBMS is commercial, therefore two editions are proposed: IBLite is free, it will be about it, and Interbase ToGo is paid, but with such pricing policy that completely excludes its use in free applications; IBLite restrictions are severe, but ways of existence will be shown with them (in conjunction with FireDAC ).
So, let's start the justification of the choice of the DBMS with the key possibility of the project - recommendations for filling lists with goods. The essence of the action is as follows: imagine that the weekend before last you would add such items as toothpaste, oranges and beef to the lists, and in the past - pork, oranges and toffee again. With a considerable degree of probability, it can be argued that this Saturday and Sunday a new list must be filled with oranges and meat (it is in this generalized form, since nothing can be said about a specific type of meat). Actually these two products will be offered to the user. The example with regularity on weekends is just one of the options, there are products added every day, every other day, at the beginning of the month, etc. - the application analyzes 21 cases in total, which, together with the need for generalization, makes the amount of calculations very decent.
')
On the device, the recommendations look like this:
Such calculations are best performed completely on the DBMS side, because the overhead of copying data from the database to the application structures, as well as the complexity and, as a result, error susceptibility of the processing algorithms of these structures, can lead to tens of seconds, which is unacceptable. The solution is stored procedures (hereinafter referred to as HP), which are present only in Interbase.
Another serious argument for CP is the requirements of the background operation (without locking the interface), as well as its early cancellation - because we are talking about the duration of a few seconds. In the case of SQLite, the complexity of solving such a problem is much more, since it is required to put all the numerous queries to the database and processing their results into a separate thread and independently respond to the cancel flag. The same CP call in FireDAC can be made asynchronous , which automatically solves the tasks:
Stored procedures have another, non-obvious, advantage - the ability to track dependencies both among themselves and from other database objects: tables, views and everything else. If during development it was necessary, for example, to change or delete a field in a table, and the request code is stored in the application in the
After a rather big spoon of honey from the procedures, let's move on to the same big spoon of tar from the absence of some possibilities in Interbase. The bitterness will go on increasing, so as not to immediately shock the reader with some, so to speak, features of this DBMS.
Above it was said about the requirement to generalize the goods when issuing recommendations, which is implemented, including, at the expense of a hierarchical directory of goods. So SQLite has the means to speed up work with trees due to generalized table expressions (CTE) of the form
and the opponent is not, offering to solve such problems through recursive CP .
Note. Written right at the time of publication of the article, but in the recently released Interbase 2017 added partial support for CTE - without the recursive part, which was promised later.
The next unpleasant surprise is related to indexed search by string fields. When adding a new product, the application offers the user options based on the characters already entered:
SQLite, in such a case, provides a very powerful (even redundant for this example) full-text search engine , which has obviously high speed of work; Interbase uses the index only when searching at the beginning of a line, whereas it is required to search for a match from any position. In other words, this condition will use the index
but already used in the application
On a small set of data, the problem weakly manifests itself - the current product guide contains 700 entries, an index-free enumeration of which on the iPhone 5c takes, in the worst case, 240 ms, which is noticeable when typing, but is still in the comfort zone.
The most bitter, even dumbfounded lack of Interbase (especially considering what year it is today) was the inability to apply derived (derived) tables:
Instead, you need to create a view (which is preferable to the option further, because it can be “deployed” by the optimizer) and connect to it.
or use CP by changing the connection type
The left external connection has to be activated due to one chronic problem that may appear when executing such a code: with internal connection (
Note. Written fairly at the time of publication of the article, however, support for derived tables has been added to the recently released Interbase 2017 .
The second important function of the application is the synchronization of lists between devices.
It, in the case of a very slow network channel and a large amount of data (if there is a photo), may well take several minutes - accordingly, its removal into a separate stream is required. However, because of the chain of restrictions, the implementation becomes more complicated: first, FireDAC requires you to establish a new connection to the database that components running in the non-mainstream will use, but secondly, IBLite does not allow you to create several simultaneous connections. The obvious solution to the problem is to close the first, primary connection, through which the data displayed in the interface is obtained; if you do this in the usual way, via the
Unfortunately, the author does not know whether there is a similar snag in SQLite, but Interbase is subject to it, so the mention will be superfluous - the point is that updating a table in a
There are two ways to fight: the first is to add an artificial sort to the loop
and the second is in using a temporary table
The most reliable way to protect the structure of the database and its data can be called encryption; it is available in SQLite, but ruthlessly cut from free IBLite. The good news is that there is another mechanism that allows curious users who do not know the password to block the connection to the database, and it works in the case of copying the database to the machine where the Interbase server is installed with full administrative access - the method is to enable Embedded User Authentication ( EUA) for the desired database. If the database is just being created, the code will look like this:
Otherwise, the command is applied.
Switching to EUA, among other things, makes it possible to exclude the admin.ib file from the application, saving almost 500 Kb:
After activating the EUA, it is recommended to increase the reliability of storing the password (at the same time increasing the restriction on its length from 8 to 32 bytes):
The last line of defense - in the case of extracting the password from the executable file or manually editing the database itself, can be the removal of the source code of the CP, triggers and views using a script that modifies the system tables :
where the strings 'TR_' and 'VW_' need to be replaced with your templates for naming triggers and views, respectively.
Before you begin, you need to give a brief idea of ​​the developed application - this is a shopping list, having in its stock some unique functionality. If someone already uses (or has used) one of the many analogues, now he could skeptically snort - they say why another one, and so there is plenty to choose from, which can only be advised to continue reading to familiarize yourself with its principal feature.
Between the devil and the deep sea
SQLite is the undisputed leader of mobile DBMS, however, some of its shortcomings and the application’s functionality that requires non-trivial data analysis didn’t allow to dwell on it - the search for alternatives led to Interbase XE7 , more precisely to its embedded version, which Delphi comes with and requires a minimum of effort to be included in the application. Interbase, of course, is not an ideal either, and it has minuses, the ways of dealing with which are given below. It is important to note that this DBMS is commercial, therefore two editions are proposed: IBLite is free, it will be about it, and Interbase ToGo is paid, but with such pricing policy that completely excludes its use in free applications; IBLite restrictions are severe, but ways of existence will be shown with them (in conjunction with FireDAC ).
The main advantage of Interbase
So, let's start the justification of the choice of the DBMS with the key possibility of the project - recommendations for filling lists with goods. The essence of the action is as follows: imagine that the weekend before last you would add such items as toothpaste, oranges and beef to the lists, and in the past - pork, oranges and toffee again. With a considerable degree of probability, it can be argued that this Saturday and Sunday a new list must be filled with oranges and meat (it is in this generalized form, since nothing can be said about a specific type of meat). Actually these two products will be offered to the user. The example with regularity on weekends is just one of the options, there are products added every day, every other day, at the beginning of the month, etc. - the application analyzes 21 cases in total, which, together with the need for generalization, makes the amount of calculations very decent.
')
On the device, the recommendations look like this:
Such calculations are best performed completely on the DBMS side, because the overhead of copying data from the database to the application structures, as well as the complexity and, as a result, error susceptibility of the processing algorithms of these structures, can lead to tens of seconds, which is unacceptable. The solution is stored procedures (hereinafter referred to as HP), which are present only in Interbase.
Another serious argument for CP is the requirements of the background operation (without locking the interface), as well as its early cancellation - because we are talking about the duration of a few seconds. In the case of SQLite, the complexity of solving such a problem is much more, since it is required to put all the numerous queries to the database and processing their results into a separate thread and independently respond to the cancel flag. The same CP call in FireDAC can be made asynchronous , which automatically solves the tasks:
- you just need to set the
TFDStoredProc.ResourceOptions.CmdExecModeproperty toamAsync - call the
TFDStoredProc.Openmethod - for interruption use call
TFDStoredProc.AbortJob(True) - handle the completion of the CP in the
TFDStoredProc.AfterOpenevent
Stored procedures have another, non-obvious, advantage - the ability to track dependencies both among themselves and from other database objects: tables, views and everything else. If during development it was necessary, for example, to change or delete a field in a table, and the request code is stored in the application in the
TFDQuery , then the task will be simple only if there are up to several dozen; when there are more than a hundred requests, keeping an eye on everyone is a big problem. HP and any professional IDE will reduce such difficulties to almost zero.Three reasons for SQLite
After a rather big spoon of honey from the procedures, let's move on to the same big spoon of tar from the absence of some possibilities in Interbase. The bitterness will go on increasing, so as not to immediately shock the reader with some, so to speak, features of this DBMS.
CTE
Above it was said about the requirement to generalize the goods when issuing recommendations, which is implemented, including, at the expense of a hierarchical directory of goods. So SQLite has the means to speed up work with trees due to generalized table expressions (CTE) of the form
WITH RECURSIVE CTE_NAME(Field1...FieldN) AS ( SELECT ... UNION ALL SELECT ... ) SELECT Field1...FieldN FROM CTE_NAME; and the opponent is not, offering to solve such problems through recursive CP .
Note. Written right at the time of publication of the article, but in the recently released Interbase 2017 added partial support for CTE - without the recursive part, which was promised later.
Full text search
The next unpleasant surprise is related to indexed search by string fields. When adding a new product, the application offers the user options based on the characters already entered:
SQLite, in such a case, provides a very powerful (even redundant for this example) full-text search engine , which has obviously high speed of work; Interbase uses the index only when searching at the beginning of a line, whereas it is required to search for a match from any position. In other words, this condition will use the index
WHERE STRING_FIELD_UPPER LIKE '%' but already used in the application
WHERE STRING_FIELD_UPPER CONTAINING '' On a small set of data, the problem weakly manifests itself - the current product guide contains 700 entries, an index-free enumeration of which on the iPhone 5c takes, in the worst case, 240 ms, which is noticeable when typing, but is still in the comfort zone.
Derived tables
The most bitter, even dumbfounded lack of Interbase (especially considering what year it is today) was the inability to apply derived (derived) tables:
SELECT ... FROM TABLE_1 JOIN ( SELECT ... FROM TABLE_2 WHERE ... GROUP BY ... ) ON ... Instead, you need to create a view (which is preferable to the option further, because it can be “deployed” by the optimizer) and connect to it.
SELECT ... FROM TABLE_1 JOIN VIEW_NAME ON ... or use CP by changing the connection type
SELECT ... FROM TABLE_1 T_1 LEFT JOIN SP_NAME(T_1.FIELD_NAME) ON 0 = 0 The left external connection has to be activated due to one chronic problem that may appear when executing such a code: with internal connection (
JOIN ), the DBMS does not take into account the dependence of the procedure call on the table fields, as a result, the values ​​for the CP parameters cannot be determined yet unread table entries.Note. Written fairly at the time of publication of the article, however, support for derived tables has been added to the recently released Interbase 2017 .
Work with data in the stream
The second important function of the application is the synchronization of lists between devices.
It, in the case of a very slow network channel and a large amount of data (if there is a photo), may well take several minutes - accordingly, its removal into a separate stream is required. However, because of the chain of restrictions, the implementation becomes more complicated: first, FireDAC requires you to establish a new connection to the database that components running in the non-mainstream will use, but secondly, IBLite does not allow you to create several simultaneous connections. The obvious solution to the problem is to close the first, primary connection, through which the data displayed in the interface is obtained; if you do this in the usual way, via the
TFDConnection.Close method, then all data sets associated with this connection will be cleared, as a result of which the user will be discouraged by empty lists. Fortunately, FireDAC itself proposes a way out of the situation - the mode of operation without a connection established , keeping the data sets open. The full sequence of actions becomes:- enter the special mode of the main connection through the
TFDConnection.Offlinemethod, which will break the physical connection with the database, but will not introduce any visual changes; - start a new thread, where to perform the second (conditionally) connection to the database;
- wait for the end of the work flow;
- close the second connection;
- if the
TFDConnection.ResourceOptions.AutoConnect = Trueproperty, then nothing else is required, because the main connection will automatically go into normal mode for any action that requires accessing the database through it.
Unstable cursor problem
Unfortunately, the author does not know whether there is a similar snag in SQLite, but Interbase is subject to it, so the mention will be superfluous - the point is that updating a table in a
FOR loop built on it can lead to unexpected behavior. This is a construction like this: FOR SELECT REC_ID, /* . */ ... FROM TABLE_1 ... WHERE ... INTO REC_ID, ... DO BEGIN UPDATE TABLE_1 SET ... WHERE REC_ID = :REC_ID; ... END There are two ways to fight: the first is to add an artificial sort to the loop
FOR SELECT REC_ID, /* . */ ... FROM TABLE_1 ... WHERE ... ORDER BY REC_ID DESC INTO REC_ID, ... DO BEGIN UPDATE TABLE_1 SET ... WHERE REC_ID = :REC_ID; ... END and the second is in using a temporary table
INSERT INTO TMP_TABLE SELECT REC_ID, /* . */ ... FROM TABLE_1 ... WHERE ...; FOR SELECT REC_ID, ... FROM TMP_TABLE INTO REC_ID, ... DO BEGIN UPDATE TABLE_1 SET ... WHERE REC_ID = :REC_ID; ... END DB protection
The most reliable way to protect the structure of the database and its data can be called encryption; it is available in SQLite, but ruthlessly cut from free IBLite. The good news is that there is another mechanism that allows curious users who do not know the password to block the connection to the database, and it works in the case of copying the database to the machine where the Interbase server is installed with full administrative access - the method is to enable Embedded User Authentication ( EUA) for the desired database. If the database is just being created, the code will look like this:
CREATE DATABASE '__' WITH ADMIN OPTION Otherwise, the command is applied.
ALTER DATABASE ADD ADMIN OPTION; Switching to EUA, among other things, makes it possible to exclude the admin.ib file from the application, saving almost 500 Kb:
After activating the EUA, it is recommended to increase the reliability of storing the password (at the same time increasing the restriction on its length from 8 to 32 bytes):
ALTER DATABASE SET PASSWORD DIGEST 'SHA-1'; ALTER USER SYSDBA SET PASSWORD '_'; The last line of defense - in the case of extracting the password from the executable file or manually editing the database itself, can be the removal of the source code of the CP, triggers and views using a script that modifies the system tables :
UPDATE RDB$PROCEDURES SET RDB$PROCEDURE_SOURCE = NULL WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0; UPDATE RDB$TRIGGERS SET RDB$TRIGGER_SOURCE = NULL WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0 AND RDB$FLAGS = 1 AND RDB$TRIGGER_NAME STARTING WITH 'TR_'; UPDATE RDB$RELATIONS SET RDB$VIEW_SOURCE = NULL WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0 AND RDB$FLAGS = 1 AND RDB$RELATION_TYPE = 'VIEW' AND RDB$RELATION_NAME STARTING WITH 'VW_'; where the strings 'TR_' and 'VW_' need to be replaced with your templates for naming triggers and views, respectively.
Source: https://habr.com/ru/post/311658/
All Articles