SQLite Tutorial (i): SQLite Database Introduction _sqlite

First, Introduction:

SQLite is currently the most popular open source embedded database, and many other embedded storage engine (NoSQL), such as BerkeleyDB, Membase, and so on, SQLite can well support the basic characteristics of relational databases, such as standard SQL syntax, transactions, Data tables and indexes, and so on. In fact, although SQLite has the basic characteristics of many relational databases, there is no more comparability between them because of the different application scenarios. Here's a list of the main features of SQLite:

1. Management is simple and can even be considered unnecessary management.
2. Easy to operate, the database files generated by SQLite can be ported seamlessly on every platform.
3. It can be easily embedded into other applications in many forms, such as Static library, dynamic library and so on.
4). Easy to maintain.

To sum up, SQLite's main advantage lies in Dexterity, quickness and high reliability. In order to achieve this goal, SQLite's designers there are a number of key trade-offs in functionality, while at the same time losing some support for RDBMS-critical functionality, 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 it is simple and simple, but also for efficiency and high reliability.

second, the main advantages of SQLite:

1. Consistent file format:

As explained in SQLite's official document, we should not compare SQLite with Oracle or PostgreSQL, but rather think of it as fopen and fwrite. Compared with our custom format data files, SQLite not only provides good portability, such as the big-end small, 32/64-bit platform-related issues, but also provides the efficiency of data access, such as indexing based on some information, so as to improve access to or sort the performance of this type of data, The transaction function provided by SQLite is not effective in the operation of ordinary files.

2. Applications on embedded or mobile devices:

Because SQLite consumes less resources at run time and does not require any administrative overhead, the advantages of SQLite for PDAs, smartphones and other mobile devices are unquestionable.

3. Internal Database:

In some scenarios, data filtering or data cleanup is needed for data that is inserted into the database server to ensure the validity of the data that is eventually inserted into the database server. Sometimes, the data is valid, can not be judged by a single record, but need and a short period of time before the historical data for special calculations, and then by the results of the calculation to determine whether the current data is legitimate. In this application, we can buffer this part of the historical data with SQLite. There is also a simple scenario that applies to SQLite, which is the calculation of statistical data. For example, we are running data real-time collection of service programs, we may need to be aggregated every 10 seconds of data, the formation of hourly statistics, the statistics can greatly reduce the amount of data users query, so as to greatly improve the query efficiency of the front-end program. In this application, we can cache the data collected within 1 hours in SQLite, and when the whole point is reached, the data is emptied after the cached data is computed.

4. Data analysis:

Can make full use of SQLite to provide SQL features, complete simple data statistical analysis function. This is unmatched by the CSV file.

5. Product Demo and test:

When you need to demo to the customer, you can use SQLite as our background database, compared with other relational databases, using SQLite reduces the amount of system deployment time. For functional testing of a product, SQLite can also play the same role.

third, compared with the RDBMS SQLite some disadvantages:

1. C/S application:

If you have multiple clients that need to access the data in the database at the same time, especially if the data operations between them need to be transmitted over the network. In this case, you should not choose SQLite. Because the SQLite data management mechanism relies more on the OS file system, it is less efficient under this operation.

2. Large amount of data:

The file system, which is limited by the operating system, is less efficient when dealing with large amounts of data. It is not even possible to provide support for storage of very large amounts of data.

3. High Concurrency:

Since SQLite only provides data locks of coarse granularity, such as read-write locks, a large amount of data is locked in each lock operation, even if only a tiny fraction of the data is accessed. In other words, we can assume that SQLite simply provides a table-level lock and does not provide row-level locks. Under this synchronization mechanism, concurrent performance is difficult to be efficient.

Iv. Personalized Features:

1.0 Configuration:

The SQLite itself does not require any initialization configuration files, and there is no process for installing and uninstalling them. Of course, there is no starting and stopping of the server instance. You do not need to create users and divide permissions during use. In the event of a system disaster, such as power problems, host problems, and so on, for SQLite, do not need to do any action.

2. No stand-alone servers:

Unlike other relational databases, SQLite does not have a separate server process for client programs to access and provide related services. SQLite as an embedded database, its running environment is in the same process space as the main program, so the communication between them is entirely in-process communication, and it is more efficient than communication between processes. However, it is particularly necessary to point out that the structure does have a poor protection problem when it actually runs, such as when an application problem causes the process to crash, and because SQLite is in the same process space as the process it relies on, then SQLite exits. However, for stand-alone server processes, this is not a problem and they will do their work in a more airtight environment.

3. Single Disk File:

The SQLite database is stored in a single disk file in the file system and can be accessed and copied as long as there is permission, which is the main benefit of portability and sharing. The other database engine basically holds the database in a disk directory, and then a set of files in that directory makes up the data file for that database. Although we can access these files directly, our programs cannot manipulate them, and only the database instance process can do so. This benefits from higher security and better performance, but it also costs a complex installation and maintenance effort.

4. Platform Independence:

This has been explained earlier. Compared to SQLite, many database engines do not back up data directly in this way, only through the various dump and restore tools provided by the database system, leading the data in the database to the local file and then to the target database. There is an obvious efficiency problem with this approach, first you need to export to another file, and if the volume of data is large, the process of exporting will be time-consuming. This is only a small part of the operation, however, because data import often takes more time. Data in the import requires a lot of verification process, in storage, it is not a simple sequential storage, but need to follow a certain data structure, algorithms and strategies stored in different file locations. Therefore, compared with the direct copy of the database file, its performance is very poor.

5. Weak type:

Unlike most databases that support static types, the data type in SQLite is considered a property of a numeric value. So for a datasheet column, even if the column type is given when the table is declared, we can still insert any type when inserting the data, such as an integer column being stored in the string ' Hello '. The only exception to this feature is the primary key column for an integral type, in which case we can only store the integer data in that column.

6. SQL statements are compiled into virtual machine code:

Many database products parse SQL statements into complex, nested data structures that are then handed to the executor to traverse the data structure to complete the specified operation. Rather than this, sqlite compiles the SQL statements into bytecode before handing them over to their own virtual machines. This approach provides better performance and superior debugging capabilities.