DBMS choice

The My Visual Database (MVDB) development environment allows you to use two DBMSs: SQLite and MySQL. The type of DBMS used can be changed during the development process at any time. But which DBMS should you choose for your application?

To get started, take a close look at the comparison table of these two DBMS, and then read the comments about each aspect in the light of use in projects created in MVDB.

Architecture

On the one hand, the SQLite file architecture simplifies the implementation of data access, on the other hand, it imposes restrictions on the sharing of the database by several users, since at the time of writing data or performing data sampling using the TDataSet class component, the file is locked, during which other users do not have ability to read or write data. With a large number of users and frequent editing operations, this can interfere with the normal operation of the application: users will receive a message that the database file is unavailable.

The client-server architecture is devoid of the above disadvantages and provides for the simultaneous operation of a large number of users, the limitations of which will be associated only with the performance of the database server. However, the database server itself requires more attention: you need to tune it and monitor its operation and performance. Performing a data backup will also require a lot of effort (see Data storage).

Data types

Although the available data types differ significantly, MVDB internals minimize these differences when developing applications by using abstract data types:

Below are lists of data types for each DBMS

SQLite Data Types

• NULL – NULL value
• INTEGER – signed integer value, uses 1, 2, 3, 4, 6, or 8 bytes depending on the number order
• REAL – floating point number, takes 8 bytes to store a number in IEEE format
• TEXT – text string, UTF-8, UTF-16BE or UTF-16LE encodings are used for storage
• BLOB – BLOB data type, an array of binary data (intended primarily for storing images, audio and video).

MySQL Data Types

• TINYINT – very small integer values
• SMALLINT – small integer values
• MEDIUMINT – average integer values
• INT or INTEGER – standard integer values
• BIGINT – large integer values
• FLOAT – small floating point values (accurate to one value after the point). Always signed values
• DOUBLE, BOUBLE PRECISION, REAL — Standard floating point values. Always signed
• DECIMAL, NUMERIC – Unpacked floating point value, always signed.
• DATE – date
• DATETIME – date and time in one value
• TIMESTAMP – timestamp timestamp
• TIME – time
• YEAR – year, 2 or 4 numbers (4 – by default)
• CHAR – fixed length string value, spaces are always added to the right up to the specified length when sorting
• VARCHAR – variable length string value
• TINYBLOB, TINYTEXT – value of type BLOB or TEXT, 255 (2^8 – 1) characters – maximum length
• BLOB, TEXT – value of type BLOB or TEXT, 65535 (2^16 – 1) characters – maximum length
• MEDIUMBLOB, MEDIUMTEXT – value of type BLOB or TEXT, 16777215 (2^24 – 1) characters – maximum length
• LONGBLOB, LONGTEXT – value of type BLOB or TEXT, 4294967296 (2^32 – 1) characters – maximum length
• ENUM – enumeration
• SET – set

Data storage

When using SQLite, the data file is called sqlite.db and by default is located in the same folder as the project executable file. If you have changed the path and file name of the used database, you can see it in the settings.ini file, in the server parameter of the Options section. The settins.ini file itself is usually located next to the executable or in the %appdata%\MyVisualDatabase\Configs\%projectname% folder, where %appdata% is the application data storage folder and %projectname% is the name of your project (executable file). The location of the initialization file is changed in the application settings, if there are two files (local and in the application data folder) settings.ini, then the latter is preferred.

If the project uses MySQL, then the data is located in the server folder (file storage). You can find out exactly where by looking at the datadir parameter in the my.ini configuration file, which is located in the C:\Document and Settings\All Users\MySQL\MySQL Server 8.0\ folder (for server version 8.0). Inside the file storage folder are server service files, as well as schema folders, which contain files with individual tables.

As a result, SQLite backup/restore/migration is a trivial file copy operation, but for MySQL you need to use special data upload/load utilities or the MySQL administrator tool (Workbench – for Windows, phpMyAdmin – for the web server).

Triggers, Stored Procedures, and Indexing

SQLite does not support stored procedures, otherwise the features are the same as MySQL. However, there are no tools in the MVDB development environment to implement these functions. Although the system automatically creates primary and secondary keys, maintains data integrity and adds the ability to cascade delete related records, this limits the convenience of the development environment and to implement triggers and procedures, you must use third-party tools or create / modify them programmatically via SQL queries every time after how you change the structure of the database from the development environment.

Security and rights system

Despite the significant differences between these two DBMSs in terms of providing secure access, this difference is leveled in MVDB, since support for multiple users, roles, and setting access rights is implemented on the client side. Thus, when using the built-in rights system, security will be equally low. On the other hand, the built-in multi-user access system will work equally on both SQLite and MySQL, which makes it easy to scale the application.

Purchase and ownership cost

Both DBMS are distributed freely, however, the use of the MySQL DBMS requires the availability of appropriate equipment and qualified specialists to configure and maintain the DBMS itself. Deploying an SQLite application comes down to copying files, it is also possible to launch and run the application from removable media. And to deploy a client-server application, you will need to pre-install and configure the server.

SQL Compatibility

MySQL has more differences from the SQL standard than SQLite, which should be taken into account when writing queries, triggers, and stored procedures. However, if you use the standard no-code MVDB development tools, you will never know about these differences. At the same time, if you actively use such functionality as calculated fields, SQL queries to display tables or reports, then when changing the DBMS, you must carefully check the SQL commands for compatibility.

Performance

Data processing in the SQLite DBMS is performed by the resources of the computer on which the application is installed, and performance is determined by the capabilities of this computer, as well as the limitations of the file operating system.

MySQL can be scaled by installing the database server on a dedicated high-performance server, which ensures comfortable work for many users, regardless of the capacity of the computers on which the client part of the program is installed.

Work via the Internet

Every second client who contacts me for development asks about the possibility of working together with the application over the Internet. The answer to this question is negative: full-fledged work will not work, since the Internet is a network without a guaranteed connection. Forwarded data packets can be lost, they can be “stolen” by intruders. Therefore, for data exchange on the Internet, it is necessary to use secure protocols with the ability to encrypt and resume data transfer, for example, HTTPS.

But the possibility of using the Internet is still available for applications using the SQLite and MySQL DBMS.

Cloud storage

You can use cloud storage to host the SQLite database file there. But the simultaneous work of several users in the data recording mode will most likely lead to a loss of information, since the result of the work of the first user will be overwritten by the result of the second, since in this mode the entire file is updated. However, if, according to the regulations, one user is involved in entering data, and all the others only read information, then such a system will work, provided that after entering the data, local copies of cloud files have been successfully updated.

Direct access to the server

You can connect to the MySQL server by IP address both on the local network and on the global network if you perform port forwarding on the router, and the IP address will be fixed. However, this connection can be unstable, so your application needs to take steps to automatically reconnect if the connection to the database server is lost. It is also necessary to limit the amount of data transmitted, since with an increase in the size, the probability of a packet loss or a connection break increases.

Remote access to a computer

ПSuitable for any DBMS, since the user connects to his work computer located in the local network of the enterprise (MySQL version) or using the file system to access data (SQLite version). This method is safe, does not limit the performance of the DBMS, but has its own peculiarities of use. In particular, you won’t be able to print the report, but you can upload it to a file and then download it.

Virtual workstation

A variant of remote access, in which the connection is made not to the user’s computer, but to a special server on which virtual workstations are organized.