Introduction to embedded database SQLite

 

Since the emergence of commercial applications decades ago, databases have become a major component of software applications. Just as database management systems are critical, they also become very large and occupy a considerable amount of system resources, increasing management complexity. As software applications gradually modularize modules, a new type of database is more suitable than a large and complex traditional database management system. The embedded database runs directly in the application process and provides the zero-configuration running mode, which consumes a very small amount of resources. This article introduces the popular SQLite database engine and describes how to use it in application development.

SQLite is an open source embedded database engine written by D. Richard hipp in C language. It is completely independent and does not have external dependencies. SQLite supports most sql92 standards, runs on all major operating systems, and supports most computer languages. SQLite is also very robust. The Creator conservatively estimates that SQLite can handle websites with or 00 clicks per day, and sometimes SQLite can handle 10 times the load of the preceding numbers.

SQLite is a lightweight database and an acid-compliant associated database management system. It is designed to be embedded and has been used in many embedded products, it occupies very low resources. In embedded devices, it may only need several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, and UNIX, and can be combined with many programming languages, such as TCL, PHP, Java, and ODBC interfaces, similar to MySQL and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.

Although SQLite is small, the supported SQL statements are not inferior to those of other open-source databases. The supported SQL statements include:

Attach Database
Begin transaction

Comment

Commit transaction
Copy
Create Index
Create Table
Create trigger
Create View
Delete
Detach Database
Drop Index
Drop table
Drop trigger
Drop View
End transaction
Explain

Expression

Insert
On conflict clause
Pragma
Replace
Rollback transaction
Select
Update

It also supports transaction processing. Some people also say that it is like Microsoft's access. Sometimes it is a bit similar, but in fact they are quite different. For example, SQLite supports cross-platform, simple operations, and can directly create databases in many languages. Unlike access, SQLite requires office support. If you are a very small application or want to do embedded development without a suitable database system, you can now consider using SQLite.

SQLite Internal Structure
Internally, SQLite consists of the following components: SQL Compiler, kernel, backend, and attachment. SQLite makes debugging, modification, and expansion of SQLite kernel more convenient by using virtual machines and virtual database engine (vdbe. All SQL statements are compiled into readable assembly that can be executed in the SQLite virtual machine.

SQLite supports databases up to 2 TB in size, and each database is fully stored in a single disk file. These disk files can be moved between computers in different bytes. The data is stored on the disk in the form of B + tree (B + tree) data structure. SQLite obtains database permissions based on the file system.

SQLite Data Type
SQLite does not support static data types, but uses column relationships. This means that its data type does not have the table column attribute, but has the data attribute. When a value is inserted into the database, SQLite checks its type. If this type does not match the associated column, SQLite will try to convert the value to the column type. If the conversion fails, the value is stored as its own type.

SQLite supports null, integer, real, text, and BLOB data types.

Because of the low resource usage, good performance, and zero management costs, embedded databases can be used, it will provide efficient performance for applications that previously could not provide backend databases for persistent data. Currently, there is no need to use text files for persistent storage. The ease of use of embedded databases such as SQLite can accelerate application development and enable small applications to fully support complex SQL statements. This is especially important for applications with small device spaces.

Embedded databases are also important for accelerating application development, especially when used at the database abstraction layer (such as pear: DB or ezsql. Finally, SQLite is under development. New functions will be available in the future and will be more useful to open-source communities.