First 5 minutes of SQLite

浏览数：24 / 时间：2015年06月12日

What is SQLite?

SQLite is light-weight RDBMS, it is use the file system rather than the C/S client, it is written by C and was widely used in the embedded system, for instance, the iOS system.

How to use the database?

Since the database is written in C, it has some useful interface for C/C++, the user interface is. Basically we need to understand two useful objects, database_connection and prepared_statement they are just the pointer of the C struct. The database_connection object is sqlite3, and the prepared_statement object sqlite3_stmt.
You can control the 2 basic objects by varies routines provided by SQLite below:
sqlite3_open()
sqlite3_prepare()
sqlite3_step()
sqlite3_column()
sqlite3_finalize()
sqlite3_close()

How to run the SQL statement?
Create a prepared statement using sqlite3_prepare().
Evaluate the prepared statement by calling sqlite3_step() one or more times.
For queries, extract results by calling sqlite3_column() in between two calls to sqlite3_step().
Destroy the prepared statement using sqlite3_finalize().
Or you can use one wrapper routine instead, sqlite_exec, here’s the detail of this routine:

int sqlite3_exec(
sqlite3*,                                  /* An open database */
const char *sql,                           /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *,                                    /* 1st argument to callback */
char **errmsg                              /* Error msg written here */);
For instance, you can run statement like this:

char *errmsg;
const char *createSQL = "CREATE TABLE IF NOT EXISTS PEOPLE"
    "(ID INTEGER PRIMARY KEY AUTOINCREMENT, FIELD_DATA TEXT)";
int result = sqlite3_exec(database, createSQL, NULL, NULL, &errmsg);

The result will be one integer of below:

    #define SQLITE_OK           0   /* Successful result */
    /* beginning-of-error-codes */
    #define SQLITE_ERROR        1   /* SQL error or missing database */
    #define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
    #define SQLITE_PERM         3   /* Access permission denied */
    #define SQLITE_ABORT        4   /* Callback routine requested an abort */
    #define SQLITE_BUSY         5   /* The database file is locked */
    #define SQLITE_LOCKED       6   /* A table in the database is locked */
    #define SQLITE_NOMEM        7   /* A malloc() failed */
    #define SQLITE_READONLY     8   /* Attempt to write a readonly database */
    #define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
    #define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
    #define SQLITE_CORRUPT     11   /* The database disk image is malformed */
    #define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */
    #define SQLITE_FULL        13   /* Insertion failed because database is full */
    #define SQLITE_CANTOPEN    14   /* Unable to open the database file */
    #define SQLITE_PROTOCOL    15   /* Database lock protocol error */
    #define SQLITE_EMPTY       16   /* Database is empty */
    #define SQLITE_SCHEMA      17   /* The database schema changed */
    #define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
    #define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
    #define SQLITE_MISMATCH    20   /* Data type mismatch */
    #define SQLITE_MISUSE      21   /* Library used incorrectly */
    #define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
    #define SQLITE_AUTH        23   /* Authorization denied */
    #define SQLITE_FORMAT      24   /* Auxiliary database format error */
    #define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
    #define SQLITE_NOTADB      26   /* File opened that is not a database file */
    #define SQLITE_NOTICE      27   /* Notifications from sqlite3_log() */
    #define SQLITE_WARNING     28   /* Warnings from sqlite3_log() */
    #define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
    #define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
    /* end-of-error-codes */