SQLite Learning Experience

SQLite is a very famous small open source cross-platform database, as the most popular open source embedded relational database, in the system structure design is playing an increasingly important role.

This article mainly along http://www.cppblog.com/weiym/archive/2012/10/16/193357.html this article inside the study guiding ideology, to step by step study SQLite, In order to quickly use the good sqlite in the actual development.

1. The advantages and disadvantages of sqlite and the applicable occasions 1. 1 advantages

Lightweight: Unlike the traditional database software for C/s mode, it is an in-process database engine that does not exist for clients and servers. With SQLite, just bring a dynamic library with a size of hundreds of K to:
Green components: SQLite's core engine itself does not rely on third-party software, Plug and Play.
Single file: All the information in the database (such as tables, views, triggers, etc.) is stored in a single file, can be copied to other places, replicable not mistaken.
Cross-platform: supports not only mainstream operating systems (Windows/linux), but also many small embedded systems (android,wp,vxworks).
Query efficiency is extremely high: the SQLite API does not differentiate whether the current database is stored in memory or in a disk file, and in order to improve efficiency, you can switch to memory mode. Only need to load the database into memory at the beginning, after reading and writing, then the memory database dump will be on the disk file, read and write memory is much faster than read and write disk.

1.2 Disadvantages

The locking mechanism of concurrent access: SQLite's performance in concurrent reads and writes has been suboptimal, and is not recommended when there is a large number of insert,update accesses.

SQL standard support is not complete:

1.3 Applicable occasions

Suitable for high query speed, less memory, especially embedded operating system, such as various mobile phone operating systems, low concurrent web (99.9% site is low concurrency), PHP environment natively support sqlite,asp.net/.net WinForm can be easily used in System.Data.SQLite.

SQLite itself is written in C, with the API interface is also C interface, so the use of C + + is the most direct. For the Java language, SQLite can be used in the way of the JDBC interface, for Python,pysqlite is the first choice to operate SQLite, it is already one of Python's standard library.

SQLite uses the license agreement when the public domain protocol can be used boldly.

2. Core components of SQL

There are two core objects in SQL, the use of database_connection and prepared_statement, respectively.

The Database_connection object is created and returned through the Sqllite3_open () interface function, which must be called before the application uses any other SQLite interface functions for initialization.

The Prepared_statement object can be simply understood as a compiled SQL statement, and all functions related to SQL statement execution require that the object be used as an input parameter to complete the specified SQL operation.

The core interface functions provided by SQL are

Sqlite_open

Sqlite_prepare

Sqlite_step (Evaluating Prepared_statement objects)

Sqlite3_column (Gets the data for the specified column of the current row)

Sqlite3_finalize for destroying Prepared_statement objects

Sqlite_close for closing Database_connection objects

3. Integrating SQLite on the Codeblock

First go to SQLite official web page Download the latest version of the source code, http://www.sqlite.org/download.html, I downloaded the sqlite-amalgamation-3080704.zip version.

Unzip this package and get the following files:

Add the above file (except the shell.c file) to your project and press CTRL+F9 to compile it.

So powerful SQLite, really works, only three files, can't help admiring the developers. Through the reading of the official website compiling information, learned that there are two versions of the source code, one is mixed, the above download is this, It implements all of the contents of the SQLite library in sqlite3.c, sqlite3.h defines the sqlite3.c interface function, which is easy to integrate into the required engineering files.

Common SQLite shell commands:

. Tables view the current SQLite database command

. Schema View commands for creating SQLite databases

Reference Links:

Http://stackoverflow.com/questions/11574280/how-to-use-sqlite-in-c-program-using-codeblocks

Appendix:

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

SQLite Learning Experience