Talking about SQLite-Implementation and Application

1. Preface

I haven't paid much attention to SQLite for a few days. Today, I opened its homepage and found that its latest version is 3.6.22. What surprised me more is that there are more and more users, in addition, there are more and more recipients of the email list, And suddenly feel that they are too old. At the same time, I had to talk a few words.

First, let's see who are using SQLite. The homepage lists a long string of NB users, including Adobe, Apple, Firefox, and even google, Microsoft, and SUN.

Firefox: This is the file in the installation directory of V3.5.7 on my machine:

SQLite 3.6.16.1 can be found.

Google is said to have used SQLite in its Desktop for Mac, Google Gears, Android, and even Chrome, google engineers have made great contributions to the full-text search function of SQLite (contribution ). There are also Apple, Micorsoft, and SUN, which will not be listed here. For details, see http://www.sqlite.org/famous.html. The participation of these companies should be of great help to the Development of SQLite, especially for users like Google.

 

2. Implementation and Application

Let's talk about SQLite from the perspective of implementation and application. Let's take a look at the features (functions) of SQLite.

Features

Simple: SQLite is a lightweight, self-contained DBMS: a header file and a dynamic library file, you have all the functions of the relational database. Simple is the most obvious philosophy of SQLite. It provides few and simple APIs. You only need a DLL file, and your program will immediately have a powerful database engine. This is a wonderful thing.

Small (small): I used VS 2005 to compile 3.6.11 in Windows, and the Release version was 368 K, which took less than 20 seconds-while it took several minutes to compile MySQL. When I insert 10000 int data records, the memory overhead is 660 kb, and the disk overhead is 92 KB.

Transaction: a transaction is the most basic requirement of modern commercial data processing systems, and Access, whether in the executable file size (read the Access2003 executable file size is 6.32 MB, the two are not an order of magnitude), or transaction features, cannot be compared with SQLite.

Concurrency ), improve the read/write concurrency as much as possible. If you are still worried, read this article: http://www.dbanotes.net/database/sqlite_cms.html.

SQL92: SQLite supports the vast majority of standard SQL statements. You only need a few hundred KB of space to get almost all the operations of a MB general-purpose DBMS.

Convenience: If your program uses SQLite, you only need to copy your program directory.

Open Source: This is the most powerful part. Open source means that you can read its source code, and you can modify it at any time to add your own features, which is completely free of charge. Open source is a kind of spirit.

 

Implementation

Now, let's talk about my personal experience from the perspective of implementation. This is also my concern.

SQLite is an excellent embedded database management system. It has two meanings: first, it is often embedded into applications as a dynamic library; on the other hand, it is usually used for embedded devices or other desktop applications with low requirements. If you use it as a memory database, I personally think it is not very suitable. After all, its write concurrency is not very good. At this time, TimesTen may be better, and Berkey DB may be a good choice. The application scenarios, implementations, and resource requirements of embedded databases such as SQLite and Primary databases are different.

(1) Transaction Processing

There are two core issues of transactions: concurrency control and recovery. A system that solves the problem of concurrency control and recovery allows its users to assume that the program is executed atomically-as if no other program is executed simultaneously; and it is reliable-there will be no failure. The abstraction of atomicity and reliability is called a transaction ). In fact, transactions are not DBMS patents. Any distributed system is faced with concurrency and recovery issues. The solution is transactions, but we often hear about the transactions in DBMS.

The concurrency control ensures the atomic execution of the transaction, which makes the transactions that are staggered run in a sequential order, with no staggered execution at all. If the result of the staggered execution is consistent with the result of the sequential execution, it is called serializable ).

Recovery allows the database to only contain the results of normal transactions. If an error occurs during the execution of a transaction, the restoration algorithm must clear the effect of partially completing the transaction.

• Concurrency Control

SQLite only supports database-level locks. What does database-level locks mean? -- This means that only one write operation can be allowed at the same time. That is to say, transaction T1 inserts A data record in Table A, and transaction T2 inserts A data record in Table B. These two operations cannot be performed simultaneously, even if your machine has 100 CPUs, it cannot be performed at the same time, but it can only be performed in sequence. Table-level locks cannot be used in parallel, not to mention the meta-group-level lock. However, SQLite tries its best to apply for the X lock as soon as possible, and does not apply for the X lock until the data block is actually written to the disk. This is very clever and effective.

• Restore

SQLite Restoration Technology is a typical example of shadow paging technology.

Common restoration technologies of DBMS include shadow paging and log-based technologies. The former is used in early database management systems, such as System R, which is hard to see in modern DBMS.

Compared with the log-based technology, the shadow paging technology has the advantages of simple implementation, eliminating the overhead of log writing and fast recovery (no redo or undo is required ). The disadvantage of shadow paging is that multiple blocks must be output during transaction commit, which results in a high overhead and is measured in blocks, it is difficult to apply to cases where multiple transactions can be executed concurrently-this is a fatal drawback.

(2) Query Processing

SQLite queries are essentially an SQL Compiler and a virtual machine. The implementation of these functions only uses more than 10 files. The whole implementation is simple and effective, but there are still some problems. First, SQLite Dictionary data is very simple. In fact, its dictionary is a table sqlite_mater. All information is obtained by parsing the SQL statement in sqlite_master, and parsing an SQL statement, both require lexical analysis, syntax analysis, and even the generation of virtual machine code. This process takes a long time, and the query plan is not reused. Secondly, the query optimization is relatively simple, especially the connection operation, which is only done through loops (the same is true for MySQL ). However, we cannot impose too much pressure on tens of thousands of codes.

(3) Storage Model

SQLite files are physically divided into blocks of the same size. Logically, they are divided into B-trees. Each table corresponds to a B-Tree. Without complicated logical organization of data blocks like Oracle or InnoDB, such on-demand distribution of data blocks will inevitably affect disk read/write performance. However, in the final analysis, it still comes from its application scenarios.

(4) buffer zone management

Buffer Management is undoubtedly very important for DBMS. SQLite is relatively simple in other aspects, but it is still a great effort to manage the Buffer. SQLite uses the LRU algorithm commonly used by DBMS. In a newer version, SQLite adopts a similar approach to a virtual file system, it enables the default buffer management subsystem to easily switch to other buffer management algorithms, which is very flexible.

(5) I/O

SQLite uses simple blocking I/O. In a newer version, asynchronous I/O is used as an optional extension. However, since SQLite does not have logs, therefore, if your data is critical, do not use asynchronous I/O of SQLite. On the other hand, in fact, many embedded operating systems, such as Windows CE, do not support asynchronous I/O (however, this can be solved through multithreading ).

However, these "disadvantages" are not the disadvantages of SQLite. Compared with DBMS, they are just its advantages. This implementation is simple and requires less resources. It is enough for embedded devices or applications with low requirements.

 

Application scenarios

Embedded devices: this should be the main scenario of SQLite applications. Many companies use SQLite in their embedded applications, including google Android.

Desktop applications: If you hate fopen, fread, and fwrite functions, SQLite is a good choice. It has simple interfaces and supports transactions. A cookie cleanup software named CookieCrumbler was accidentally launched a few days ago. After opening it, I found that there was a sqlite3.dll, which shocked me.

Websites: If your website has a low Access volume or a small amount of data, SQLite can replace the overhead MySQL and complicated Access.

 

Postscript

SQLite is constantly developing, and the power of open source is huge. I believe it will become more and more widely used. Wish ~