Introduction to SQLite and introduction to SQLite

Introduction to SQLite and introduction to SQLite

I. Why SQLite?

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.

Other reasons for using SQLite

If you want to use SQLite in your company's commercial software project. You also need to evaluate it based on several reference factors mentioned in "how to select an open-source project.
1. License)
SQLite uses the Public Domain protocol, which is the best and can be used with confidence.
2. Popularity of users
Over the past few years, more and more people have used SQLite (which can be reflected by Google Trends ). Some large companies have begun to integrate it into products (such as Google's Gears, Apple's Safari, and Adobe's AIR ). This shows that its robustness and stability won't be too big a problem.
3. Development Activity
If you get a rough idea about the Change Log of SQLite, we can see that there are basically updates every 1-2 months in the last five years. It indicates that the development activity is still very high.
From the above non-technical factors, SQLite is very reliable for commercial company software projects.

Ii. Advantages and Disadvantages of SQLite

A. Advantages and features of SQLite Technology

SQLite is a lightweight, cross-platform relational database. Since it is known as a relational database, it is a matter of course to support things commonly used in the SQL92 standard (such as views, transactions, triggers, and so on). I will not go into detail today. Today, I will talk about some special things.

1.Lightweight
First, it has its first feature: lightweight. Presumably, the author of SQLite attaches great importance to this feature, and even its Logo uses "feathers" to show its lightness.
SQLite is different from the C/S database software. It is a database engine in the process, so there is no database client or server. To use SQLite, you only need to include a dynamic library to enjoy all its functions. In addition, the size of the dynamic library is quite small, and version 3.6.27 is hundreds of kb.

3.Green Software
Another feature of SQLite is green: its core engine does not rely on third-party software and does not require "installation environment" (such as Oledb ). Therefore, it can save a lot of trouble during deployment.

4.Single file
The so-called "single file" means that all the information in the database (such as tables, views, triggers, and so on) is contained in one file. This file can be copied to other directories or other machines.

5.Cross-platform/portability
If we only support mainstream operating systems (Windows and Linux), we will not boast much about it. In addition to mainstream operating systems, SQLite also supports many small embedded systems, such as Android, Windows Mobile, Symbin, Palm, and VxWorks, which can be used on iPhone, Android, and other Mobile phones.

6.In-memory database)
In this year, the memory is getting cheaper and cheaper. Many common PCs start to measure the memory in GB (servers are even more expensive ). At this time, the memory database features of SQLite become more and more useful.
The SQLite API does not distinguish whether the database currently operated is in memory or in files (transparent to the storage media ). Therefore, if you think that disk I/O may become a bottleneck, you can switch to the memory mode. During the switchover, the code for operating SQLite basically does not need to be changed. As long as the file is loaded to the memory at the beginning, it is OK to Dump the memory database back to the file at the end. In this case, the "online backup API" mentioned above comes in handy. Smart students should understand why I expect the backup function so much?

 

B. Technical disadvantages and deficiencies

We talked about the features and advantages above. To avoid the suspicion of hand-writing soft texts with a gun, let's talk about some shortcomings of SQLite. If you want to use it in the future, consider these disadvantages.

1.Concurrent access Lock Mechanism
SQLite's performance in concurrent (including multi-process and multi-thread) read/write has never been ideal. The database may be exclusive to write operations, leading to blocking or error of other read/write operations.

2.Incomplete SQL standard support
On its official website, it lists the SQL92 standards that are not supported. What I personally feel is that foreign key constraints are not supported.

3.Network File System (NFS)
Sometimes you need to access the SQLite database files on other machines and place the database files in the shared network directory. At this time, you should be careful. When SQLite files are stored in NFS, concurrent read/write operations may cause problems (such as data corruption ). It is said that some NFS file locks have bugs.

Iii. Programming Language Interface

SQLite supports programming interfaces in many languages. This is great for people like me who like to mix multiple programming languages. Next I will give a rough introduction.

　　◇ C/C ++
Since SQLite is written in C, its built-in APIs are also C interfaces. Therefore, C/C ++ is the most straightforward to use. If you do not like the process-oriented c api style, you can find another C ++ packaging library. If you want to re-invent the wheel, you can package one by yourself.
◇ Java
If you want to use Java to access SQLite, you can use the JDBC driver of SQLite or a specialized SQLite packaging library. I personally suggest using the JDBC Method. If you want to change the database in the future, you don't need to change the code.
◇ Python
Pysqlite is the first choice for Python to operate SQLite. It has been integrated into the Python Standard Library since Python 2.5. It seems that the Python community is quite fond of SQLite.
◇. Net
If you like. net, you can access it through System. Data. SQLite.
◇ Ruby
Ruby can operate the SQLite database through SQLite-Ruby, but I have never used it.
◇ Perl
There is DBD: SQLite on CPAN, but I have never used it.

* ***** The C/C ++ interface is described in detail below. After all, interfaces of other language libraries are implemented based on C *****

A. Overview:

Five of the C/C ++ interfaces provided by SQLite are core interfaces. In this blog, we will mainly introduce their usage and the core SQLite objects they involve, such as database_connection and prepared_statement. Compared with APIs provided by other database engines, such as OCI and MySQL APIs, the APIs provided by SQLite are very easy to understand and master.

B. core objects and interfaces:

1. Core object: the two most important objects in SQLite are database_connection and prepared_statement. The database_connection object is created and returned by the sqlite3_open () interface function. before using any other SQLite interface function, you must call this function to obtain the database_connnection object, in other subsequent APIs calls, the object must be used as an input parameter to complete the corresponding work. As for prepare_statement, we can simply regard it as the compiled SQL statement. Therefore, all functions related to SQL statement execution also need this object as an input parameter to complete the specified SQL operation.

2. Core interfaces:
1). sqlite3_open

As mentioned above, this function is the entry function for operating the SQLite database. The database_connection object returned by this function is a handle parameter of many other SQLite APIs. Note: You can use this function to open existing database files or create new database files. For the database_connection object returned by this function, we can share the pointer of this object among multiple threads to complete any database-related operations. However, in the case of multithreading, we recommend that you create an independent database_connection object for each thread. This function also requires additional instructions. We do not need to create multiple database connection objects to access multiple databases, the ATTACH Command provided by SQLite allows you to easily access multiple databases in one connection.

2). sqlite3_prepare

This function converts SQL text to a prepared_statement object and returns a pointer to this object after the function is executed. In fact, this function does not evaluate the parameter-specified SQL statement. It only initializes the SQL text to the State to be executed. Finally, we can use the sqlite3_prepare_v2 interface function in place of the new application to complete the same work.

3). sqlite3_step

Evaluate the prepared_statement object returned by the sqlite3_prepare function. After the function is executed, the internal pointer of the prepared_statement object points to the first row of the returned result set. If you want to further iterate the data rows, you need to call the function until all the data rows have been traversed. However, for DML statements such as INSERT, UPDATE, and DELETE, this function can be executed once.

4). sqlite3_column

This function is used to obtain the data of the specified column in the current row. However, in strict sense, this function does not exist in the SQLite interface function, A group of related interface functions are used to complete this function. Each function returns different types of data, such:

Sqlite3_column_blob
Sqlite3_column_bytes
Sqlite3_column_bytes16
Sqlite3_column_double
Sqlite3_column_int
Sqlite3_column_int64
Sqlite3_column_text
Sqlite3_column_text16
Sqlite3_column_type
Sqlite3_column_value
Sqlite3_column_count

The sqlite3_column_count function is used to obtain the field data in the current result set. Use

The pseudo code of each row of data in the sqlite3_step and sqlite3_column Function Iteration result sets. Note that the following Sample Code simplifies the determination of the field type:

Int fieldCount = sqlite3_column_count (...);
While (sqlite3_step (...) <> EOF ){

For (int I = 0; I <fieldCount; ++ I ){
Int v = sqlite3_column_int (..., I );

}}

5). sqlite3_finalize

This function is used to destroy the prepared statement object. Otherwise, memory leakage may occur.

6). sqlite3_close

This function is used to close the previously opened database_connection object. All prepared_statements objects related to this object must be destroyed before this.

C. Parameter binding:

Like most relational databases, SQLite SQL text also supports variable binding to reduce the number of times SQL statements are dynamically parsed, thus improving the efficiency of data query and data operations. To complete this operation, we need to use the APIs, sqlite3_reset, and sqlite3_bind interfaces provided by SQLite.

See the following example:

Void test_parameter_binding (){

// 1. Insert multiple data entries without parameter binding. Char strSQL [128];
For (int I = 0; I <MAX_ROWS; ++ I ){

Sprintf (strSQL, "insert into testtable values (% d)", I); values (..., strSQL); sqlite3_step (prepared_stmt); sqlite3_finalize (prepared_stmt );

}

// 2. Insert multiple data entries when the parameter is bound.
String strSQLWithParameter = "insert into testtable values (?) "; Sqlite3_prepare_v2 (..., strSQL );
For (int I = 0; I <MAX_ROWS; ++ I ){

Sqlite3_bind (..., I); sqlite3_step (prepared_stmt); sqlite3_reset (prepared_stmt );

} Sqlite3_finalize (prepared_stmt );

}

The first thing to note here is that the SQL statement "insert into testtable values (?) "Question mark (?) Represents the placeholder of parameter variables. This rule is consistent in many relational databases, so it is more convenient for database transplantation. From the sample code above, we can see that the execution efficiency of parameter binding is higher than that of generating different SQL statements each time, that is, 2) the efficiency is significantly better than 1 ), the following is a comparison of the two methods:

1. On the program surface, the former executes more tasks in the for loop, such as string filling, SQL statement prepare, and prepared_statement object release.

2. The SQLite official document clearly states that the execution efficiency of sqlite3_prepare_v2 is often lower than that of sqlite3_step.

3. When the inserted data volume is large, the efficiency improvement brought by the latter is considerable.

Iv. Common SQL functions:

<Span style = "font-size: 14px;"> // 1. open the database int sqlite3_open (const char * filename, // The database file path sqlite3 ** ppDb // database instance); // 2. execute any SQL statement int sqlite3_exec (sqlite3 *, // an open database instance const char * SQL, // The SQL statement int (* callback) (void *, int, char **, char **), // callback void * after the SQL statement is executed, // The 1st parameter char ** errmsg // error message of the callback function ); // 3. check the validity of SQL statements (preparations before query) int sqlite3_prepare_v2 (sqlite3 * db, // database instance const char * zSql, // The SQL statement int nByte to be checked, // sqlite3_stmt ** ppStmt, // sqlite3_stmt instance for obtaining database data const char ** pzTail); // 4. query a row of Data int sqlite3_step (sqlite3_stmt *); // If a row of data is queried, SQLITE_ROW // 5 is returned. use stmt to obtain the value of a certain field (the subscript of the field starts from 0) double sqlite3_column_double (sqlite3_stmt *, int iCol); // floating point data int sqlite3_column_int (sqlite3_stmt *, int I ); // sqlite3_int64 sqlite3_column_int64 (sqlite3_stmt *, int iCol); // const void * sqlite3_column_blob (sqlite3_stmt *, int iCol ); // const unsigned char * sqlite3_column_text (sqlite3_stmt *, int iCol); // string data </span>

5. Other resources

SQLite resource address:

Official SQLite homepage:
Http://www.sqlite.org/

SQLite Chinese site:
Http://www.sqlite.com.cn/

System. Data. SQLite:
Http://sqlite.phxsoftware.com/

Embedded Database for SQL Study Notes (sqlite, firebird)
Http://www.cnblogs.com/ljzforever/archive/2010/03/09/1681453.html

SQL study notes, written by bloggers in great detail

Http://www.cnblogs.com/stephen-liu74/category/348367.html

SQLite GUI graphical management tool (recommended ):

Navicat for SQLite (powerful import and export functions, practical functions, intuitive operations, some minor defects, and update speed ):
Http://www.navicat.com/en/download/download.html