SQLite tutorial (1): Introduction to SQLite database

Time:2019-3-31

I. Introduction:

SQLite is the most popular open source embedded database at present. Compared with many other embedded storage engines (NoSQL), such as Berkeley DB, MemBASE, etc., SQLite can well support some basic features of relational database, such as standard SQL grammar, transaction, data table and index. In fact, although SQLite has many basic features of relational databases, there is no more comparability between them due to the different application scenarios. Below we will list the main features of SQLite:

1) Management is simple and can even be considered unnecessary.
2) Easy to operate, the database files generated by SQLite can be transplanted seamlessly on various platforms.
3) It can be easily embedded into other applications in various forms, such as static libraries, dynamic libraries, etc.
4) Easy to maintain.

To sum up, the main advantages of SQLite are agility, speed and reliability. In order to achieve this goal, the designers of SQLite have made a lot of key choices in function. At the same time, they have lost some support for the key functions of RDBMS, 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 because of the sacrifice of these functions that simplicity comes in return for efficiency and reliability.
   
Second, the main advantages of SQLite:

1. Consistent file formats:

As explained in the official document of SQLLite, we should not compare SQLite with Oracle or PostgreSQL, but treat it as fopen and fwrite. Compared with the data files in our customized format, SQLite not only provides good portability, such as platform-related issues such as large-end and small-end, 32/64 bit, but also provides high efficiency of data access, such as indexing based on certain information, so as to improve the performance of accessing or sorting such data. Transaction functions provided by SQLite can not be effectively guaranteed when operating ordinary files. Proof.
   
2. Applications in embedded or mobile devices:

Because SQLite takes up less resources at runtime and does not need any management overhead, there is no doubt that it has advantages for mobile devices such as PDA 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 eventually inserted into the database server. Sometimes, whether the data is valid or not can not be judged by a single record, but needs special calculation with the historical data of a short period of time before, and then judges whether the current data is legitimate by the results of calculation. In this application, we can use SQLite to buffer this part of historical data. Another simple scenario is also applicable to SQLite, which is the pre-calculation of statistical data. For example, we are running a real-time data acquisition service program. We may need to aggregate data every 10 seconds to form hourly statistical data, which can greatly reduce the amount of data users query, thus greatly improving the query efficiency of the front-end program. In this application, we can cache all the collected data within one hour in SQLLite, and empty the data after calculating the cached data when the whole point is reached.
   
4. Data analysis:

Make full use of the features of SQLite to complete simple data statistical analysis. This point is incomparable with CSV files.
   
5. Product Demo and Testing:

When demo is needed for customers, we can use SQLite as our backend database. Compared with other relational databases, using SQLite reduces a lot of system deployment time. For functional testing of products, SQLite can also play the same role.
       
Third, some disadvantages of SQLite compared with RDBMS:

1. C/S applications:

If you have multiple clients that need to access data in the database at the same time, especially the data operations between them need to be done through network transmission. In this case, you should not choose SQLite. Because the data management mechanism of SQLite is more dependent on the file system of OS, its efficiency is low under this kind of operation.
   
2. Large amount of data:

Restricted by the operating system’s file system, it is inefficient to process large amounts of data. For the storage of super-large data volume, it can not even provide support.
   
3. High concurrency:

Because SQLite only provides coarse-grained data locks, such as read-write locks, a large amount of data is locked in every lock operation, even if only a small part of the data is accessed. In other words, we can assume that SQLite only provides table-level locks, not row-level locks. Under this synchronization mechanism, concurrency performance is difficult to be efficient.
   
IV. Individualized characteristics:

1. Zero configuration:

SQLite itself does not require any initialization configuration files, nor does it have the process of installation and uninstallation. Of course, there is no startup or stop of the server instance. In the process of using, there is no need to create users and divide permissions. In case of system disasters, such as power supply problems, host problems, etc., no operation is required for SQLite.
   
2. There are no independent servers:

Unlike other relational databases, SQLite does not have a separate server process for client applications to access and provide related services. As an embedded database, SQLite runs in the same process space as the main program, so the communication between them is entirely intra-process communication, which is more efficient than inter-process communication. However, it should be pointed out that this structure does have some problems of poor protection when it is running. For example, when the application fails, the process crashes. Because the SQLite and the processes it depends on are in the same process space, then the SQLite will exit. However, for stand-alone server processes, this is not a problem, they will do their work in a more closed environment.
   
3. Single disk file:

SQLite’s database is stored in a single disk file of the file system, and can be accessed and copied freely as long as it has permission. The main benefit of this is easy to carry and share. Other database engines basically store the database in a disk directory, and then a group of files under that directory constitute the data files of the database. Although we can access these files directly, our programs can’t manipulate them. Only database instance processes can do this. The benefits of this approach are higher security and better performance, but at the cost of complex installation and maintenance.
   
4. Platform independence:

This has been explained before. Compared with SQLLite, many database engines can’t backup data directly by this way. They can only export the data in the database to the local file first, and then load it to the target database through various dump and restore tools provided by the database system. This method has obvious efficiency problems. First, it needs to export to another file. If the amount of data is large, the export process will be time-consuming. However, this is only a small part of the operation, because data import often takes more time. Data need a lot of validation process when imported. In storage, it is not simple sequential storage, but need to be stored in different file locations according to certain data structures, algorithms and strategies. Therefore, compared with directly copying database files, its performance is very poor.
   
5. Weak types:

Unlike most databases that support static types, data types in SQLite are treated as an attribute of values. So for a data table column, even if the column type is given when declaring the table, we can 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 the integer, in which case we can only store integer data.
   
6. SQL statements are compiled into virtual machine code:

Many database products parse SQL statements into complex, nested data structures, which are then referred to the executor to traverse the data structures to complete the specified operations. In contrast, SQLite compiles the SQL statement into bytecode before it is executed by its own virtual machine. This approach provides better performance and better debugging capability.

Recommended Today

Multi version management of golang in Windows

Multi version management of golang in Windows currentgolangThere are still some incompatibilities among different versions. Recently, we encountered some problemsgo-microThe framework can only run in go 1.13 ~ 1.14, but I installed it under windows1.15So you need to install other versions of golang, so many versionsgolangHow to useWindowsIt’s coexistence on the Internet. If you find […]