Use of SQLite (including steps to compile and install)

SQLite website
http://www.sqlite.org/

About SQLite

SQLite is a lightweight database, a relational database management system that adheres to acid (atomicity, consistency, isolation, and durability). SQLite implements most of the SQL-92 standards, including transactions, triggers, and most complex queries.
SQLite's design goal is embedded, it occupies a very low resource, currently in many embedded products are using SQLite.
SQLite is a cross-platform, portable, capable of supporting mainstream operating systems such as Windows/linux/unix, while SQLite can be combined with many programming languages, such as Tcl, C #, PHP, and Java. It is easy to use the SQLite library in C + + programs, and Python has built a sqlite module since 2.5, and the module is named Sqlite3.
The first alpha version of SQLite was born in May 2000. Currently, the latest version of SQLite is 3.11.

Features of SQLite

1. Acid Transactions
2. Open Source Code
3. Small, standalone, easy to use, with no lag behind the popular database
4. The entire database is stored in one file and does not require server support
5. Cross-platform, support Windows/linux/unix and other mainstream operating systems
6. Support for multiple development languages, C, c++,php, Perl, Java, C#,python, etc.
7. Simple and easy to use API interface

SQLite Management Client

• Sqliteman, a SQLite client developed with QT, supports multi-lingual, cross-platform.
• SQLite Manager, a SQLite client provided in the form of Firefox extensions.
• SQLite Database Browser, a graphical client to access SQLite databases
• Sqlpro SQL Client, another graphical client to work with SQLite databases
• SQLiteStudio2 (TCL/TK interface): http://sqlitestudio.pl/files/free/stable/
• SQLiteStudio3 (qt/c++ rewrite): http://sqlitestudio.pl/files/sqlitestudio3/complete/

Installation

(i) installation by compiling the source code

Https://www.sqlite.org/download.html

Download File sqlite-autoconf-3110000.tar.gz

// Unzip
Tar zxvf sqlite-autoconf-3110000.tar.gz

// install
Cd sqlite-autoconf-3110000
./configure --prefix=/usr/local
Make
Make install

Source code compilation generates such a number of files:

/usr/bin/sqlite3

/usr/local/include/sqlit3.h

/usr/local/lib/libsqlite3.so

(ii) Installing SQLite through the package

sudo apt-get install sqlite3 Libsqlite3-dev

This is required when compiling the program.

GCC Dbtest.c-o dbtest–lsqlite3

Note: There can be spaces between-L and Sqlite3.

(iii) Do not install the SQLite compiler program
On Linux systems, save the DBTEST.C program in Libsqlite3.so, Sqlite3, and sqlite3.h in the same directory.
You can compile the file by executing this command:

GCC dbtest.c-o dbtest-lsqlite3-l.

Sqlite3 binaries and sqlite3.h can be downloaded to the SQLite website, there are two ways to get the libsqlite3.so:

1) through the source code compilation will be generated in the/usr/local/lib/directory libsqlite3.so files, CP out on it;

2) in the System/usr/lib/x86_64-linux-gnu directory (I am a 64-bit system).

If Libsqlite3-dev is installed, this directory will have the following files:

Small test

The following is the DBTEST.C program, which only opens and closes the operation.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

Int main(void)
{
     Sqlite3 *db = NULL;
     Int rc;
     / / Open the database, if it does not exist will create a new
     Rc = sqlite3_open("my.db",&db);
     If(rc) // is not 0, open failed
     {
         Fprintf(stderr,"Can't open database:%s\n", sqlite3_errmsg(db));
         Sqlite3_close(db);
         Exit(0);
     }
     Else
     {
         Printf("open db success!\n");
         Sqlite3_close(db);
     }
     Return 0;
}

Common errors at compile time

1) No header file found, error message contains the following sentence:

Sqlite3.h: No file or directory

2) The library file is not found, and the error message contains the following sentences:

: Undefined reference to ' Sqlite3_open '
: Undefined reference to ' sqlite3_errmsg '
: Undefined reference to ' sqlite3_close '
: Undefined reference to ' sqlite3_close '

The-L,-l,-I parameters of GCC

The-l parameter is used to specify the library to which the program is to be linked, for example, to link libsqlite3.so in this program, and remove the prefix.

Libraries placed in/lib and/usr/lib and/usr/local/lib can be linked directly with the-l parameter.

If the library file is not in these 3 directories, such as the library that you write, you need to specify the directory of the library file in-l+dir way.

The-include,-i parameter is used to specify the header file directory, and the GCC default directory is/usr/include

SQLite API functions

SQLite is an interface library at the call level that can be embedded in the application. All API functions are named with the sqlite3_ prefix and are declared in the sqlite3.h.

sequence number	API & Description
1	sqlite3_open (const char *filename, sqlite3 **ppdb) If   filename The   parameter is NULL or ': Memory: ', then Sqlite3_open () will create a memory database in RAM, which will only persist for the duration of the session. If the file does not exist, SQLite will automatically create the database file.
2	sqlite3_exec (sqlite3*, const char *sql, sqlite_callback, void *data, Char **errmsg) The first parameter   sqlite3   is an open database object, sqlite_callback   is a callback, Data   as its first parameter, ERRMSG will be returned to get any errors generated by the program. The sqlite3_exec () program resolves and executes each command given by   SQL   parameters until the string ends or an error is encountered.
3	sqlite3_close (sqlite3*) Closes the database connection and frees all resources assigned to the connection. If there is a query that is not completed, Sqlite3_close () returns an error message that sqlite_busy suppress shutdown.

Note: When you open or create a database file, SQLite adheres to a lazy strategy: when you know that the file is being read and accessed, it is actually opened or created.

SQL strings can consist of multiple SQL statements, for example:

="UPDATE Company set SALARY = 25000.00 where id=1;"  "SELECT * from Company"; 

sqlite3_exec () function If no callback is required, the simplest can be written like this:

rc = sqlite3_exec(db, sql, 0, 0, &errorMsg);
if(errorMsg)
    printf("%s\n", errorMsg);
else
    printf("success!\n");

The part of the error can also be written like this:

if( rc != SQLITE_OK )
{
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
}
else
{
    fprintf(stdout, "success!\n");
}

The prototype of the callback function

typedef int (*sqlite3_callback)(
void*,    /* Data provided in the 4th argument of sqlite3_exec() */
int,      /* The number of columns in row */
char**,   /* An array of strings representing fields in the row */
char**    /* An array of strings representing column names */
);

The first parameter is passed in by the fourth parameter of the sqlite3_exec.

The second parameter is the number of columns in the result row

The third parameter is a pointer to the column data in a row

The fourth parameter is a pointer to the column name in the row

If the third parameter of the SQLITE3_EXEC callback function pointer is not NULL, it is called for each result row from the executed SQL statement.

If an error occurs in the execution of the SQL statement, the execution of the current statement is stopped and subsequent statements are skipped.

Here is a specific example of a callback function:

typedef int (*sqlite3_callback)(
void*,    /* Data provided in the 4th argument of sqlite3_exec() */
int,      /* The number of columns in row */
char**,   /* An array of strings representing fields in the row */
char**    /* An array of strings representing column names */
);

Sqlite3_get_table () function

Int sqlite3_get_table(
   Sqlite3 *db, /* An open database */
   Const char *zSql, /* SQL to be evaluated */
   Char ***pazResult, /* Results of the query */
   Int *pnRow, /* Number of result rows written here */
   Int *pnColumn, /* Number of result columns written here */
   Char **pzErrmsg /* Error msg written here */
);
// release char** query result regardless of whether the database query is successful
Void sqlite3_free_table(char **result);

The 1th parameter of the database object.
The 2nd parameter is a SQL statement that ends with a string.
The 3rd parameter is the query result, which is still a one-dimensional array. Its memory layout is: The first row is the field name, followed by the value of each field.
The 4th parameter is the number of records (how many rows) to query out.
The 5th parameter is the number of fields (how many columns).
The 6th parameter is an error message.

The number of strings returned by Pazresult is actually (*pnrow+1) * (*pncolumn)

Here is a concrete example of a sqlite3_get_table () function:

Result = sqlite3_get_table( db, "select * from table1", &dbResult, &nRow, &nColumn, &errmsg );
If( SQLITE_OK == result ) // The query is successful
{
     Index = nColumn;
     // dbResult The first row of data is the field name, which is the real data from the nColumn index.
     Printf ("Check %d records\n", nRow);

     For( i = 0; i < nRow ; i++ )
     {
          Printf("%d record\n", i+1);
          For( j = 0 ; j < nColumn; j++ )
          {
               Printf("field name: %s > field value: %s\n", dbResult[j], dbResult [index] );
               ++index;
               // The field values of dbResult are contiguous, from 0th index to nColumn-1 index are field names.
// starting with the nColumn index, followed by field values
          }
          Printf( "--------\n" );
     }
}
/ / Release the query results
Sqlite3_free_table( dbResult );

There is one more example:

char query(sqlite3 *db, const char *sql) {
    printf("%s\n", sql);
    char *select_str = "SELECT";
    char *errorMsg;
    char *str_str = strstr(sql, select_str);
    if(str_str) {
        printf("in it, %s\n", str_str);
        int row = 0, column = 0, i = 0;
        char **result;
        sqlite3_get_table(db, sql, &result, &row, &column, &errorMsg);
        printf("row:%d, column:%d\n", row, column);
        for(; i < column * (row + 1); i++) {
            printf("result[%d]=%s\n", i, result[i]);
        }
    }else{
        sqlite3_exec(db, sql, 0, 0, &errorMsg);
    }
    if(errorMsg){
        printf("%s\n", errorMsg);
    }else{
        printf("success!\n");
    }
}

mnemonic constants defined in the sqlite3.h:

#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 */

Other

SQLite Tutorials
Http://www.runoob.com/sqlite/sqlite-tutorial.html

Common Command Learning
http://yuanzhifei89.iteye.com/blog/1123870
http://blog.csdn.net/linchunhua/article/details/7184439

[API]
In the official description: http://www.sqlite.org/cintro.html
Http://www.cnblogs.com/kfqcome/archive/2011/06/27/2136999.html
Http://blog.chinaunix.net/uid-8447633-id-3321394.html

Use VC to convert Sqlite3.def to Sqlite3.lib
http://www.letuknowit.com/topics/20120421/convert-sqlite-def-to-sqlite-lib.html/
Example program for SQLite database operation in Windows system
http://www.letuknowit.com/topics/20120422/use-c-or-cplusplus-connect-to-sqlite-in-windows.html/

Http://www.cnblogs.com/luoxu34/p/5235737.html

Use of SQLite (including steps to compile and install)