SQLite usage Summary 1

Preface:... 1
I. version... 1
Ii. Basic compilation... 2
Iii. SQLite operations... 2
(1) Basic Process... 2
(2) SQL statement operations... 4
(3) Operating binary... 8
(4) Transaction Processing... 10
4. encrypt the database... 10
5. Postscript... 25

Preface:
Sqlite3 is really useful. Small and fast. However, for non-Microsoft products, the help documentation is always insufficient. I have studied it again these days, and I have some gains. Here I will list my research on sqlite3 In order to forget it.

It should be noted that I am a cross-platform expert and do not like to use Windows only. I used to write code for UNIX platforms. What I wrote below is not verified, but I have tried not to use any windows things. I only use standard C or standard C ++. However, I have never tried to compile it in other systems or compilers. Therefore, if the descriptions below are incorrect, they will not be modified.

The following code is still written in VC, because I think VC is a good ide that can speed up code writing (for example, using vassist ). The compiling environment I mentioned below is vc2003. If you feel that you are used to writing code quickly with VI in UNIX, you don't have to worry about my instructions. You just need to conform to your habits, because I use standard C or C ++. It will not cause inconvenience to anyone.

I. Version
You can download the latest SQLite code and compiled version from www.sqlite.org. When I write this article, the latest code is 3.3.17.

I haven't downloaded the new SQLite code for a long time, so I don't know that SQLite has changed so much. In the past, many files are merged into a sqlite3.c file. It would be nice to use this file separately, saving you the trouble of copying a bunch of files and worrying about any omissions. But it also brings about a problem: this file is too large, and it is close to 70 thousand lines of code. The whole machine of VC is slowing down. If you do not need to modify the code, you do not need to open the sqlite3.c file, and the machine will not be slow. However, I want to write the encryption function by modifying the SQLite code, which is quite painful at that time. If the individual level is relatively high, we recommend that you use simple editors such as ultraedit or notepad. The speed will be much faster.

Ii. Basic Compilation
I don't want to talk about this. Create a DOS console blank project in VC, add sqlite3.c and sqlite3.h to the project, and create a new main. cpp file. Write in:

Extern "C"
{
# Include "./sqlite3.h"
};
Int main (INT, char **)
{
Return 0;
}

Why extern "C "? If you ask this question, I don't want to say too much. This is the basis of C ++. To use a piece of C code in C ++, you must use extern "C. Although C ++ and C have overlapping syntaxes, they are two different things, and the memory layout is completely different, in the C ++ compiler, C code is not included in the extern "C", which will cause the compiler to not know how to describe the memory layout for the C code.

In sqlite3.c, the whole code segment extern "C" may have been included, but when you encounter a. c file, you will consciously include it again, and there is nothing wrong with it.

The basic project is established in this way. Compilation, which can be passed. But there are a bunch of warnings. You can ignore it.

Iii. SQLite operations
SQLite provides some C function interfaces that you can use to operate databases. By using these interfaces, you can pass some standard SQL statements (in char * type) to the SQLite function, and SQLite will operate the database for you.

SQLite is a file-type database like MS Access. That is to say, a database is a file. Many tables can be created in this database, and indexes and triggers can be created. However, what it actually gets is a file. Back up this file to back up the entire database.

SQLite does not need any database engine, which means that if you need SQLite to save some user data, you don't even need to install the database (if you have to install sqlserver to run a small software, it's too dark ).

The following describes basic database operations.

(1) Basic Process
I .1 Key Data Structure

The most common type in SQLite is sqlite3. Since the database is opened, SQLite needs to prepare memory for this type until the database is closed. This type is used throughout the process. When the database is opened, this type of variable represents the database you want to operate on. The following is a detailed introduction.

I .2 open the database

Int sqlite3_open (file name, sqlite3 **);

Use this function to start database operations.

You need to input two parameters. One is the database file name, for example, C: // dongchunguang_database.db.

The file name does not need to exist. If the file does not exist, SQLite will automatically create it. If it exists, try to open it as a database file.

The sqlite3 ** parameter is the key data structure mentioned above. Do not close the underlying details of this structure.

The Return Value of the function indicates whether the operation is correct. If it is sqlite_ OK, the operation is normal. Related return values SQLite defines some macros. For more information about these macros, see the sqlite3.h file. There are detailed definitions in it (by the way, sqlite3 code annotation rate claims to be very high, but it is indeed very high. As long as you can read English, SQLite can help you learn a lot ).

The following describes how to shut down the database and give a reference code.

I .3 shut down the database

Int sqlite3_close (sqlite3 *);

If you have enabled a database with sqlite3_open, do not forget to use this function to close the database.

The following is a simple code:

Extern "C"
{
# Include "./sqlite3.h"
};
Int main (INT, char **)
{
Sqlite3 * DB = NULL; // declare SQLite key structure pointer
Int result;
// Open the database
// You need to pass in the pointer of the database, because the sqlite3_open function needs to allocate memory for this pointer, And the DB pointer must point to this memory Zone
Result = sqlite3_open ("C: // dcg_database.db", & dB );
If (result! = Sqlite_ OK)
{
// Failed to open the database
Return-1;
}
// Database operation code
//...
// The database is successfully opened.
// Close the database
Sqlite3_close (db );
Return 0;
}

This is a database operation process.