SQLite is a great embedded database (part 2)

Part 1
Part 3

This part will address the difficult issues of using SQLite through working with its programming interface (API).


')
What is the SQLite API? This is the C function set of sqlite3_XXX .

The headers of these functions are in sqlite3.h, and the description of the API in the form of one large HTML is here .

We will begin to use the API, and later we will gradually move on to wrapping it in a cozy framework.

Let's try to create a small project in C ++ (I did in MS Visual Studio) that does something with the SQLite database.

There are two ways to use SQLite in your project.

"Compile" code (if it is C or C ++). Or download and use sqlite3.dll (makes it easy to update SQLite and is not tied to the language).

In any case, you need to create a new project (console) application (I used MS Visual Studio) and add the following main.cpp to it:

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

const char * SQL = "CREATE TABLE IF NOT EXISTS foo (a, b, c); INSERT INTO FOO VALUES (1,2,3); INSERT INTO FOO SELECT * FROM FOO;" ;

int main ( int argc , char ** argv ) {

sqlite3 * db = 0 ; // handle of the object connection to the database
char * err = 0 ;

// open connection
if ( sqlite3_open ( "my_cosy_database.dblite" , & db ) )
fprintf ( stderr , "Error opening / creating database:% s \ n" , sqlite3_errmsg ( db ) ) ;
// execute SQL
else if ( sqlite3_exec ( db , SQL , 0 , 0 , & err ) )
{
fprintf ( stderr , "SQL Error:% sn" , err ) ;
sqlite3_free ( err ) ;
}
// close the connection
sqlite3_close ( db ) ;
return 0 ;
}

Further, if we want to build a project with SQLite code, then we need:

- download the source code in the form of amalgamation ;
- extract sqlite3.h and sqlite3.c from it and add them to the project.

If we want to use sqlite3.dll, we need:

- download SQLite DLL and unzip;
- execute “LIB.EXE / DEF:sqlite3.def” in the folder where it was unpacked to get sqlite3.lib (make sure that the paths to lib.exe are registered via the vcvars32.bat call);
- include in the project sqlite3.lib;
- download amalgamation and extract sqlite3.h from it;
- include in the project sqlite3.h;

Compile, execute (in the second case, the DLL must be accessible to the executable file).

When you first start, the file "my_cosy_database.dblite" from the database will be created, in it - one table and two entries in it.
On subsequent launches, the program will join the already existing database and double the number of records in the table.

Let's deal with the project code.

Using SQLite assumes that we want to execute commands in the SQL language in some database (your KO!).

You can imagine working with SQLite database as working with files in Windows. We open the file and get the _handle_ file to which a certain system object (file) is “attached”. By passing this handle to various functions, we ask the system to do something with the file. Then - close the file. Also in SQLite. We open the file from the database and get the handle of the “connection to the database” object. Then we execute certain SQL commands through calls to functions that receive this handle, and, at the end, close the connection.

As we can see, there is nothing original, special or cunning.

SQLite DB file extension is not standardized. Some make it ".sqlite3", but you can put any.

Function

int sqlite3_open (
const char * filename , / * Database filename (UTF-8) * /
sqlite3 ** ppDb / * OUT: SQLite db handle * /
) ;

creates or opens a database in the specified file (UTF-8!) and puts the connection handle in db. It returns 0 (success) or an error code. The closing sqlite3_close call is needed anyway (even with the sqlite3_open error).

Function

int sqlite3_exec (
sqlite3 *, / * An open database * /
const char * sql , / * SQL to be evaluated (UTF-8) * /
int ( * callback ) ( void *, int , char **, char ** ) , / * Callback function * /
void *, / * 1st argument to callback * /
char ** errmsg / * Error msg written here * /
) ;

executes a SQL command (consisting of one or more SQL statements in UTF-8, separated by ";") in the context of the specified (open) database connection.

The callback function and the custom argument to it are needed if SELECT is made (to retrieve data). More on this later, while not using.

In the errmsg parameter, you can get the error text and later clear it with sqlite3_free. If we pass NULL here, we won’t get the error text.

Like sqlite3_open, 0 or an error code is returned.

The “CREATE TABLE” command contains the “IF NOT EXISTS” clarification. This means that the table is created if it is not. If there is, then nothing happens (and there is no error).

Actually, that's all. Despite the fact that in real projects neither sqlite3_open (there is a more powerful call sqlite3_open_v2), nor sqlite3_exec (usually used a bunch of SQL-compiling calls, binding parameters, etc.) are used, this is a working project.

He has another important mission. As already mentioned, each time it starts, it doubles the number of rows in the foo table. After 20 starts in the table there will be ~ 2 million records.

Those. The 21st launch inserts ~ 2 million records.

On my machine (Windows 7 x64, i5 2.8 Ghz, HDD, not SSD) it took ~ 15 seconds. Let this poor and synthetic test, but it still gives a certain idea about the performance of SQLite.

To be continued.