Introduction to SQLite Usage

1. SQLite Introduction

SQLite is currently the most popular open source embedded database, compared with many other embedded storage engines (NoSQL), such as BerkeleyDB, membase, etc., SQLite can well support the basic characteristics of relational database, 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 due to the different scenarios. Below we will list the main features of SQLite:

1. Management is simple and can even be thought of without management.
2. Easy to operate, SQLite-generated database files can be ported seamlessly across platforms.
3. It can be easily embedded into other applications in many forms, such as static libraries, dynamic libraries, and so on.
4. Easy to maintain.

In summary, the main advantage of SQLite is dexterity, fast and high reliability. In order to achieve this goal, SQLite designers have made a number of key tradeoffs in functionality, while at the same time losing support for key RDBMS features such as high concurrency, granular 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 easy to change, but simply to be efficient and highly reliable.

2. Reasons to choose SQLite at work

1. License Agreement (License)
SQLite uses the public domain protocol, which is the most cool one, can be assured to use the bold.

2. Popularity of users
In recent years, there have been more and more people using SQLite (from Google Trends). Some big companies are also starting to integrate it into their products (such as Google's gears, Apple Safari, Adobe's Air). This shows that its robustness, stability, and so on are not too much of a problem.

3. The level of development activity
If you get a rough look at the change log on SQLite, you can see that the last 5 years have basically been updated every 1-2 months. Indicates that the development activity is still very high.
From the above-mentioned non-technical factors, sqlite for commercial companies, software projects are very reliable.

3. SQLite pros and cons

(1). The advantages and characteristics of SQLite technology

SQLite is a lightweight, cross-platform relational database. Since the so-called relational database, support SQL92 standard commonly used gadgets (such as views, transactions, triggers, etc.) is taken for granted, we do not elaborate today. Today, I would like to talk about some special things.

1. Lightweight
First of all, it's a feature: lightweight. It must be the author of SQLite value this feature, even its logo is the use of "feathers" to show off its lightness.
Unlike the database software for SQLite and C/s mode, it is a database engine within the process, so there is no client and server for the database. With SQLite, you can use only one of its dynamic libraries to enjoy its full functionality. and the dynamic library size is also very small, 3.6.27 version is hundreds of K

2. Green Software
Another feature of SQLite is green: Its core engine itself does not rely on third-party software, and it does not require "installation environment" (such as OLE DB, etc.). So you can save a lot of trouble when you deploy.

3. Single File
The so-called "single file" is that all the information in the database (such as tables, views, triggers, etc.) is contained within a single file. This file can be copied to other directories or other machines, but also replicable.

4. Cross-platform/portability
If the light supports the mainstream operating system (Windows,linux), then there is nothing to brag about. In addition to the main operating system, SQLite also supports a lot of small embedded systems, such as Android, Windows Mobile, Symbin, Palm, VxWorks, etc., that is, iphone,android and other mobile phones can be used.
　　
5. In-Memory databases (in-memory database)
These days, memory is getting cheaper, and many ordinary PCs are starting to measure memory in gigabytes (not to mention the server). At this point, SQLite's memory database features are becoming more usable. The SQLite API does not differentiate whether the current operating database is in memory or in a file (for storage media is transparent). So if you think that disk I/O can be a bottleneck, consider switching to memory mode. When switching, the operation of the code of SQLite basically do not change, as long as the beginning of the file load to memory, the end of the memory of the database dump back to the file is OK.

(2). Technical shortcomings and deficiencies

In front of the light chat features and advantages, in order to avoid the gunman writing soft article suspicion, and then say some of the shortcomings of SQLite. Yours faithfully crossing in the future if you want to use it, these shortcomings should be weighed.

1. Locking mechanism for concurrent access
SQLite's performance in concurrent (including multi-process and multi-threaded) reading and writing has been suboptimal. The database may be exclusive to write operations, causing other read and write operations to be blocked or faulted.

2.SQL standard support not complete
On its official website, there are specific examples of what SQL92 standards are not supported. I personally feel rather uncomfortable is not support foreign key constraints.

3. Network File System (hereinafter referred to as NFS)
Sometimes it is necessary to access the SQLite database files on other machines, and the database files will be placed on the network shared directory. You must be careful at this time. When SQLite files are placed on NFS, there may be problems (such as data corruption) in the case of concurrent read and write. The cause is said to be due to a bug in some NFS file lock implementations.

4. Various programming language interfaces

SQLite supports programming interfaces in many languages. This is great for people like me who are mixed with a variety of programming languages. I'm about to introduce you.

(1) C + +
Since SQLite itself is written in C, its own API is also the C interface. So C + + is most straightforward to use. If you don't like the process-oriented C API style, you can also find a C + + packaging library. Students who want to reinvent the wheel can also pack their own.

(2) Java
If you want to access SQLite in Java, you can use SQLite's JDBC driver, or through a dedicated SQLite packaging library. I personally recommend the JDBC approach, in case you want to change the database in the future, the code will not change greatly.

(3) Python
Pysqlite is the first choice for Python to manipulate SQLite. Starting with Python 2.5, it has been integrated into the Python standard library. It seems that the Python community still likes SQLite.

(4). NET
For students who like. NET, they can access it through System.Data.SQLite.

(5) Ruby
Ruby can manipulate the SQLite database through Sqlite-ruby, but I didn't use it.

(6) Perl
There are dbd::sqlite on the CPAN, but I didn't use them.

5. C Language Programming interface

The following is a detailed introduction to the C + + interface , after all, the interface of the other language libraries are based on C, understanding the following interface for learning the relevant framework is very helpful.

5.1 Basic
Of these, 5 APIs belong to the core interface in SQLite-C + + interfaces. In this blog we will focus on their usage and the core SQLite objects they are involved in, such as Database_connection and Prepared_statement. The interface provided by SQLite is very easy to understand and master compared to other APIs provided by the database engine, such as OCI, MySQL API, etc.

5.2 Core objects and interfaces

(1) Core objects:
The main two objects in SQLite are:database_connection and prepared_statement, database_connection objects are sqlite3_ The open () interface function creates and returns a function that must be called before the application uses any other SQLite interface functions to obtain the Database_connnection object, and in subsequent APIs calls, The object is required as an input parameter to do the work accordingly.
As for prepare_statement, we can simply treat it as a compiled SQL statement, so all functions related to SQL statement execution need the object as an input parameter to complete the specified SQL operation.

(2). Core interface:

$ sqlite3_open.
This function has been mentioned above, which is the entry function to manipulate SQLite database. The database_connection object returned by this function is a handle parameter for many other SQLite APIs. Note that we can either open a database file that already exists or create a new database file through this function. For the Database_connection object returned by this function, we can share a pointer to the object between multiple threads to complete any operations related to the database. However, in the case of multithreading, it is more recommended that you create separate Database_connection objects for each thread. There is also a point for this function that requires additional clarification, and there is no need to create multiple database connection objects in order to access multiple databases, because the attach command that comes with SQLite makes it easy to access multiple databases in one connection.

Sqlite3_prepare.
The function converts the SQL text to a Prepared_statement object and returns a pointer to the object after the function executes. In fact, the function does not evaluate the parameter to specify the SQL statement, it simply initializes the SQL text to the state to be executed. Finally, for the new application, we can use the SQLITE3_PREPARE_V2 interface function to replace the function to do the same work.

$ sqlite3_step.
The function is used to evaluate the Prepared_statement object returned by the Sqlite3_prepare function, and after the function is executed, the internal pointer to the Prepared_statement object points to the first row of the result set it returns. If you intend to further iterate over the subsequent rows of data, you will need to call the function continuously until all the rows of data have been traversed. However, for DML statements such as INSERT, update, and delete, the function executes once.

$4. Sqlite3_column
This function is used to get the data for the specified column of the current row, but strictly speaking, this function does not exist in SQLite's interface function, but rather is done by a set of related interface functions, where each function returns different types of data, such as:

sqlite3_column_blobsqlite3_column_bytessqlite3_column_bytes16sqlite3_column_doublesqlite3_column_intsqlite3_column_int64sqlite3_column_textsqlite3_column_text16sqlite3_column_typesqlite3_column_valuesqlite3_column_count

Where the Sqlite3_column_count function is used to get the field data in the current result set. Here is the pseudo-code that iterates through each row of data in the result set using the Sqlite3_step and Sqlite3_column functions, noting that as the sample code simplifies the judgment of the field type:

int fieldCount = sqlite3_column_count(...);while (sqlite3_step(...) <> EOF) {    for0; i < fieldCount; ++i)    {        int v = sqlite3_column_int(...,i);    }}

$ sqlite3_finalize.
This function is used to destroy the prepared statement object, or it will cause a memory leak.

$6. Sqlite3_close
The function is used to close the previously opened Database_connection object, where all Prepared_statements objects associated with the object must be destroyed first.

(3) parameter binding:
Like most relational databases, SQLite's SQL text also supports variable bindings to reduce the number of times SQL statements are parsed dynamically, thus improving the efficiency of data querying and data manipulation. To do this, we need to use the other two interfaces Apis,sqlite3_reset and Sqlite3_bind provided by SQLite.
See the example below:

void Test_parameter_binding () {//1.Inserts more than one data without parameter binding. Char strsql[ -]; for(int i =0; i < max_rows; ++i) {sprintf (strSQL,"INSERT into testtable values (%d)", i); SQLITE3_PREPARE_V2 (..., strSQL); Sqlite3_step (prepared_stmt); sqlite3_finalize (prepared_stmt); }//2.Insert multiple data in the case of a parameter binding. 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 (?)" The question mark (?) in the Represents a placeholder for a parameter variable, which is consistent across many relational databases, so this is relatively convenient for database porting operations. As can be seen from the above example code, the execution efficiency of the parameter binding is higher than that of each generation of different SQL statements, that is, 2 is significantly better than 1 in efficiency, and the following is a specific comparison of the two formulations:

1. From the surface of the program alone, the former performs more tasks in the For loop, such as the padding of strings, the prepare of SQL statements, and the release of Prepared_statement objects.
2. In SQLite's official documentation, it is clear that sqlite3_prepare_v2 's execution efficiency is often lower than sqlite3_step's efficiency.
3. When the amount of data being inserted is large, the latter's efficiency gains are considerable.

6. SQL Common functions:

//1. Opening a databaseintSqlite3_open (Const Char*filename,file path for the//databaseSqlite3 **ppdb//DB instance);//2. Execute any SQL statementintSqlite3_exec (sqlite3*,//An open DB instance    Const Char*sql,//The SQL statement that needs to be executed    int(*callback) (void*,int,Char**,Char**),//callback After SQL statement is executed    void*,///The 1th parameter of a callback function    Char**errmsg//Error Messages);//3. Checking the legality of SQL statements (preparation before query)intSQLITE3_PREPARE_V2 (Sqlite3 *db,//DB instance    Const Char*zsql,//SQL statements that need to be checked    intNbyte,the maximum byte length of the//SQL statementSqlite3_stmt **ppstmt,//SQLITE3_STMT instance for obtaining database data    Const Char**pztail);//4. Querying a row of dataintSqlite3_step (sqlite3_stmt*);//If a row of data is queried, the Sqlite_row is returned//5. Using stmt to get the value of a field (the subscript of a field starts with 0)DoubleSqlite3_column_double (sqlite3_stmt*,intICOL);//floating point dataintSqlite3_column_int (sqlite3_stmt*,intICOL);//Integer dataSqlite3_int64 Sqlite3_column_int64 (sqlite3_stmt*,intICOL);//long-integer dataConst void*sqlite3_column_blob (sqlite3_stmt*,intICOL);//Binary text dataConst unsigned Char*sqlite3_column_text (sqlite3_stmt*,intICOL);//String data

6. Other Resources

The official homepage of SQLite:
http://www.sqlite.org/
SQLite Chinese Station:
http://www.sqlite.com.cn/
System.Data.SQLite:
http://sqlite.phxsoftware.com/
SQL Learning Notes Embedded database (Sqlite,firebird)
Http://www.cnblogs.com/ljzforever/archive/2010/03/09/1681453.html
SQL study notes, Bo Master wrote very detailed
Http://www.cnblogs.com/stephen-liu74/category/348367.html

SQLite GUI graphical management tool (recommended):
Navicat for SQLite (import, export powerful, functional, intuitive operation):
Http://www.navicat.com/en/download/download.html

Introduction to SQLite Usage