SQLite tutorial (1): SQLite database introduction, sqlite Database

SQLite tutorial (1): SQLite database introduction, sqlite Database

I. Introduction:

SQLite is currently the most popular open-source embedded database. compared with many other embedded storage engines (NoSQL), such as BerkeleyDB and MemBASE, SQLite supports 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). The management is simple and can be considered as unnecessary.
2). Easy to operate. The database files generated by SQLite can be seamlessly transplanted on various platforms.
3). It can be easily embedded into other applications in multiple forms, such as static libraries and dynamic libraries.
4). easy to maintain.

To sum up, the main advantage of SQLite is its flexibility, speed, and high reliability. To achieve this goal, SQLite designers have made many key functional choices. 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 precisely because of the sacrifice of these functions that they have made for simplicity, while simplicity has for efficiency and high reliability.

Ii. Main advantages of SQLite:

1. Consistent file format:

This is explained in the official documentation of SQLite. We should not compare SQLite with Oracle or PostgreSQL, but regard it as fopen and fwrite. Compared with data files in custom formats, 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, for example, if you create an index based on certain information to improve the performance of accessing or sorting such data, the transaction function provided by SQLite cannot be effectively guaranteed when operating common files.

2. Applications on embedded or mobile devices:

Because SQLite consumes less resources and does not require any management overhead, the advantages of SQLite are beyond doubt for mobile devices such as PDAs and smartphones.

3. internal database:

In some application scenarios, We need to filter or clear 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 determined by a single record. Instead, it needs to perform special calculations with the historical data of a short period of time, then, the calculation result is used to determine whether the current data is valid. In this application, we can use SQLite to buffer this part of historical data. Another simple scenario is also applicable to SQLite, that is, pre-Calculation of statistical data. For example, if we are running a real-time  service program, we may need to summarize the data every 10 seconds to form hourly statistics, this statistical data can greatly reduce the amount of data queried by users, thus greatly improving the query efficiency of the front-end program. In this application, we can cache all the  within one hour in SQLite. When the entire point is reached, the cached data is calculated and cleared.

4. Data Analysis:

You can make full use of SQLite's SQL features to complete simple data statistical analysis. This is incomparable to CSV files.

5. product Demo and test:

When Demo is required for customers, SQLite can be used as our background database. Compared with other relational databases, SQLite reduces the system deployment time. SQLite also plays the same role in product functional testing.

Iii. disadvantages of SQLite compared with RDBMS:

1. C/S application:

If you have multiple clients that need to access data in the database at the same time, especially data operations between them must be completed through network transmission. In this case, SQLite should not be selected. Since the data management mechanism of SQLite is more dependent on the OS file system, the efficiency of this operation is low.

2. Large data volume:

Limited by the operating system's file system, the efficiency of processing large data volumes is low. Storage of large data volumes cannot even be supported.

3. High concurrency:

Since SQLite only provides coarse-grained data locks, such as read/write locks, a large amount of data is locked in each lock operation, even if only a small amount of data is accessed. In other words, we can think that SQLite only provides table-level locks without row-level locks. In this synchronization mechanism, the concurrency performance is difficult to be efficient.

Iv. personalized features:

1. Zero Configuration:

SQLite does not require any Initialization Configuration File, nor does it install or Uninstall. Of course, there is no start or stop of the server instance. You do not need to create a user or assign permissions during use. In the case of system disasters, such as power supply problems and host problems, SQLite does not need to perform any operations.

2. There is 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 runs in the same process space as the main program. Therefore, the communication between them is completely intra-process communication, which is more efficient than inter-process communication. However, it should be pointed out that this structure does have poor protection during actual operation. For example, a problem occurs in the application and causes the process to crash, since SQLite is located in the same process space as the process on which it depends, SQLite also exits. However, independent server processes do not have this problem. They will complete their work in an environment with better confidentiality.

3. single disk file:

SQLite databases are stored in a single disk file of the file system and can be accessed and copied as long as they have the permission. The main benefit of this is the ease of carrying and sharing. Other database engines usually store data in a disk directory, and a group of files under the Directory constitute the data files of the database. Although we can directly access these files, our programs cannot operate on them. Only the database instance process can do this. The advantage is higher security and better performance, but it also pays a complicated cost for installation and maintenance.

4. platform independence:

This has been explained previously. Compared with SQLite, many database engines cannot back up data directly in this way. They can only use various dump and restore tools provided by the database system to export data from the database to a local file, load the data to the target database. This method has obvious efficiency problems. First, you need to export data to another file. If the data volume is large, the export process will be time-consuming. However, this is only a small part of the operation, because data import usually takes more time. Data Import requires a lot of verification procedures. During Storage, data is not stored in a simple sequence, but stored in different file locations according to certain data structures, algorithms, and policies. Therefore, compared with directly copying database files, the performance is very poor.

5. Weak type:

Unlike most databases that support static data, the data type in SQLite is considered as an attribute of a value. Therefore, for a data table column, even if the type of the column is given when the table is declared, we can still insert any data type, for example, the Integer column is saved to the string 'Hello '. The only exception to this feature is the integer primary key column. In this case, we can only store integer data in this column.

6. Compile the SQL statement into the VM code:

Many database products parse SQL statements into a complex, nested data structure, and then hand over the executor to traverse the data structure to complete the specified operation. Compared with this, SQLite will compile the SQL statement into a bytecode before executing it on its own virtual machine. This method provides better performance and better debugging capabilities.