1. Introduction:
SQLite is the most popular open source embedded database at present. Compared with many other embedded storage engines (NoSQL), such as BerkeleyDB, MemBASE, etc., SQLite can well support some basic features of relational databases, such as standard SQL syntax, transactions, data tables and indexes. In fact, although SQLite has many basic characteristics of relational databases, there is no more comparability between them due to different application scenarios. Below we will list the main features of SQLite:
1). Management is simple and can even be considered as unnecessary.
2). It is easy to operate, and the database files generated by SQLite can be seamlessly ported on various platforms.
3). It can be embedded in other applications in various forms in many forms, such as static libraries, dynamic libraries, etc.
4). Easy to maintain.
To sum up, SQLite's main advantages are its dexterity, speed and high reliability. In order to achieve this goal, the designers of SQLite have made many key trade-offs in functions. At the same time, they have lost some support for RDBMS key functions, such as high concurrency, fine-grained access control (such as row-level locks), rich built-in functions, stored procedures and complex SQL statements. It is precisely because of the sacrifice of these functions that simplicity is exchanged for efficiency and reliability.
2. The main advantages of SQLite:
1. Consistent file format:
This is explained in the official SQLite documentation. We should not compare SQLite with Oracle or PostgreSQL, but should regard it as fopen and fwrite. Compared with our custom format data files, SQLite not only provides good portability, such as big-end, small-end, 32/64-bit and other platform-related issues, but also provides efficient data access, such as establishing indexes based on certain information, thereby improving the performance of accessing or sorting this type of data. The transaction functions provided by SQLite cannot be effectively guaranteed when operating ordinary files.
2. Applications on embedded or mobile devices:
Since SQLite occupies less resources at runtime and does not require any management overhead, the advantages of SQLite are unquestionable for mobile devices such as PDAs and smartphones.
3. Internal database:
In some application scenarios, we need to filter or clean up the data inserted into the database server to ensure the validity of the data finally inserted into the database server. Sometimes, whether the data is valid cannot be judged through a single record, but special calculations are required with the historical data of a short period of time before, and then the calculation results are used to determine whether the current data is legal. In this application, we can use SQLite to buffer this part of historical data. There is also a simple scenario that also applies to SQLite, that is, pre-calculation of statistics. For example, we are running a service program that collects data in real time. We may need to summarize the data every 10 seconds and form an hourly statistical data. This statistical data can greatly reduce the amount of data during user query, thereby greatly improving the query efficiency of front-end programs. In this application, we can cache all collected data within 1 hour in SQLite, and when the full point is reached, we can calculate the cached data and clear the data.
4. Data analysis:
You can make full use of SQLite to provide SQL features to complete simple data statistical analysis functions. This is incomparable to CSV files.
5. Product Demo and Test:
When you need to demo to customers, you can use SQLite as our backend database. Compared with other relational databases, using SQLite reduces a lot of system deployment time. SQLite can also play the same role in product functional testing.
3. Some disadvantages of SQLite compared to RDBMS:
1. C/S application:
If you have multiple clients that need to access the data in the database at the same time, especially the data operations between them need to be completed through network transmission. In this case, SQLite should not be selected. Since SQLite's data management mechanism relies more on the OS file system, it is less efficient in this operation.
2. Large amount of data:
Due to the operating system's file system, it is less efficient when processing large amounts of data. There is even no support for storage of super large amounts of data.
3. High concurrency:
Since SQLite only provides very coarse-grained data locks, such as read and write locks, a large amount of data will be locked in each locking operation, even if only a small part of the data will be accessed. In other words, we can think that SQLite just provides table-level locks, not row-level locks. Under this synchronization mechanism, concurrent performance is difficult to be efficient.
4. Personalized characteristics:
1. Zero configuration:
SQLite itself does not require any initialization configuration files, nor does it have an installation and uninstallation process. Of course, there is no startup or stopping of server instances. There is no need to create users and divide permissions during use. When a system has a disaster, such as power supply problems, host problems, etc., there is no need to do anything for SQLite.
2. No independent server:
Unlike other relational databases, SQLite does not have a separate server process for client programs to access and provide related services. As an embedded database, SQLite has an operating environment that is located in the same process space as the main program, so the communication between them is completely in-process communication, and it is more efficient than inter-process communication. However, it should be pointed out that this structure does have poor protection when it is actually running. For example, when an application has problems, the process crashes. Since SQLite is in the same process space as the process it depends on, SQLite will also exit at this time. But for standalone server processes, there will be no problem, they will do their job in a better closed environment.
3. Single disk file:
SQLite databases are stored in a single disk file in the file system and can be accessed and copied at will as long as you have permissions. The main benefit of this is that they are easy to carry and share. Other database engines basically store the database in a disk directory, and then form a set of files in that directory to form the data file of the database. Although we can access these files directly, our programs cannot operate them, and only the database instance process can do it. This benefit is that it brings higher security and better performance, but it also pays the cost of complex installation and maintenance.
4. Platform irrelevance:
This has been explained before. Compared with SQLite, many database engines cannot directly backup data through this method. They can only export the data in the database to the local file through various dump and restore tools provided by the database system, and then load it into the target database. This method has obvious efficiency problems. First, it is necessary to export to another file. If the data volume is large, the export process will be time-consuming. However, this is only a small part of this operation, as data import often takes more time. Data needs a lot of verification processes when importing. When storing, it is not simply stored in sequence, but needs to be stored in different file locations according to certain data structures, algorithms and strategies. Therefore, compared with direct copying of database files, its performance is very poor.
5. Weak type:
Unlike most databases that support static type, data types in SQLite are considered as an attribute of numeric values. Therefore, for a data table column, even if the column type is given when declaring the table, we can still insert any type when inserting data, such as Integer's column being stored in the string 'hello'. The only exception to this feature is the primary key column of an integer, in which case we can only store integer data in that column.
6. SQL statements are compiled into virtual machine code:
Many database products will parse SQL statements into complex, nested data structures, and then hand them over to the executor to traverse the data structure to complete the specified operation. In contrast, SQLite will first compile SQL statements into bytecode, and then hand them over to its own virtual machine for execution. This method provides better performance and better debugging capabilities.