[Database] sqlite3 usage Summary 1)

Preface:
/V * C ,~ $ X, A3 P3 J2 R! A; m, K + u: P: Q. x 'C' g/L $ A + V2] 7 S) L
I. Version :? /R0 Z. D5 C6 V1 C (^

! S % W1 C6 ^ 3 P/Q 2. Basic compilation % \/A % o $ T: i7 g

! Y3 C2 X) M4 _ + u & I 3. SQLite operations
6 o/J '1 X % _/L1 N & _ * {& K
$ P T1 z & G9 G-Q (1) Basic Process '[6 R3 N) {; \-G + @
0 ^ % B) A2 I9 K
(2) SQL statement operations
(P) H & V! V8 m'1 {8 Q. m )@;'
8 y-T1 K) N # M ([(3) binary; q; P0 J 'x. m "g K/n
-H9 A8 L; Z' \ 5 u
(4) Transaction Processing) I + H # Prepare a $ t! @ 7 m (~
"A $ Y5 @; _" G
4. Database Encryption
(? # Y! X. U3 [8 h 'P
; T "P. P8 K3 p! Z & M! '% Q v. Postscript & _: O (| (A1 X6 W

0 E * n, Y3 g C /?, K1 x
* S. R: J6 G. l4 G3 K5 _ $ F/D * x
; S;]! X2 U $ P;] ^ 6? ! S: ^ 6 N $ p * L/W-H ([1 S (] * w
0 p * t? 8] % C
Preface: 2 B9 o! N * W0 K6 N "D! C +] 8 o
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.
*~! E "g1 Z2] 'r | 6] # H. K8 I # Y8 ~ 6 f. o % j7 E
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.
& I t ;? 7 N/B9 F, L: Q %}.} "X4 D * K' $ I9 C/N * P
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. 2 J "~ 7 R2} (x1 U + |! M7 u

& Amp; X/K-T 'F' [; s 'H
/E, s f/a; ^, J # K5 R: J + U (B2 @.~ 'G8 S $ H
I. Version
"U" U "A1 R3 W8 x0 I 'B-V3 C * \, V can download the latest SQLite code and build from the http://www.sqlite.org/website. When I write this article, the latest code is 3.3.17. % K $} V & G (d # M: U0 s
4 A0 h g $ Z: U: '0 ~ & K: V * g
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: the file is too large, and it is close to 70 thousand lines of code. The whole machine of VC is slowed 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. ~ 8 A7 O: B/T ,'('

2 [, z5 o x: Z "K3x9 I * ^ 7 J! M; Z4 G5 B) N $ F "Z

$ R7 H-Z3 ~ 4 W &?! Y/C-G 2. Basic Compilation
8 P. K-n! @) Y + Q: I don't want to talk about this. Create a blank project in the DOS console in VC, add sqlite3.c and sqlite3.h to the project, and create a new main. cpp file. Write in :/~ $ J * E. Q # L * A9 P3 m
7 n'v9' 4 Z9 J5 \ 2 Z! }
Extern "C"
% @ & X. A, M & H9 L) B9 J. B + _ & B! W2 s; i8 {4 _/H3 I/s "O
{/O7 D8 P # N4 _ "G' P @ 6 V

'A3 c) D-D1 [5 U4? /A 'd $ A $ G' R0 Z # include "./sqlite3.h" 8 R: P2 H6 N. B) '5 Z2 d

6 V % Y: V $ Q # P! O # J % u}; 2 F ~, J5 A # x' x
"W" n! F. a f ++ _
Int main (INT, char **) 1g; F (s6 S +} $ u
/S #? 'Q * t (E 'l, F8 o -\
{5 W1 X: U4 [% A2 Z, m
} 5 z'f: D7 A, M2 \
Return 0; 7 l0 j9 ^ 5 B '@/C6 U: B6 X1 n

6 V, N $ {. U & W: s w! J}
, M'] "P, I! O: d
2 L * @ 0} 8 u8 e t) Z4 \) m2' 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. # V, H (Q;} 7 H "F
(~ 5 D0 C # A $ J2 D2 K
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. 0 n + R + H: A7 n % @ # K % H
M9 ~ B2 Q * x. y "d
The basic project is established in this way. Compilation, which can be passed. But there are a bunch of warnings. You can ignore it. 3] 1 V; R, H5 K + d $ @ 4 A8 Z

$ U $ Y ('3 F3 C7 B6 u
7 S; H-P "O: Z4 R * X-r/M $ C3 K4 {6? : E 'p4 W
Iii. SQLite operations 3 m) S. R + L $ D0]
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.
4 E! A # I + ^ 0 O (|
2 Z % N ^'t (\ "@-| SQLite is a file-type database like MS Access. That is to say, a database is a file and many tables can be created in this database, indexes and triggers can be created, but what it actually gets is a file. Back up this file to back up the entire database. 'F (n8 ~, C-S
8 U (I4 P8 U $ N5 J) Q! ^
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 ).
9 p (V4 '& W + F6 [7? 6 A4 T5 V4 z6 V5 U0 U6 J8 n
The following describes basic database operations.
0g * g (R (} 9 d']-[, @ 1 C; I, Q % @
(1) Basic Process
. Z! T9 D5 E7 {I .1 Key Data Structure 1 B2 R (K: U1 p * l0 v

* Sqlite3 * is the most common type in a * z0 {% R6 ssqlite. 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.
7 Y & x L) F "Z: G/Y: B
) X0 y $ Y: C: N1 Z! Li.2 open the database) W) K6 | O (d $ B-u
& ^; U & O * z; T6 U1 m-m
Int sqlite3_open (file name, sqlite3 **); % C &] * T6 D7 J-G
7g Q! F5 Z-B * w # G/[% _
Use this function to start database operations. & R & W5 ^ 0}: x, u. K

(T % P. y6 M! W6 G8 S $ H requires two parameters. One is the database file name, for example, C: \ dongchunguang_database.db.
+ A-| % W! Z (}, K8 P "E" J $ ^-G' n. d % T, W
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. 1 f w + '1 v $ K-J3 o "V (I 'G
, C0 \ # U % Y4 X-@ $ o, u ''! Z
The sqlite3 ** parameter is the key data structure mentioned above. Do not close the underlying details of this structure. 'W4 ~ -E4g 'K:] 7x2

8 h: O, A/F! J'b7 G! The Return Value of the I & W/H 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 ).
2 E/Z1 K4 Z2 O & P
2 W0 l % W "^ 0 U) O-q The following describes how to shut down the database and then give a reference code.
. S-y k: ^ 7? 2 D; s
* Q (V (K2} "K3 [, T. bi.3 shut down the Database" | 3 B6 G1 t) @: w (G9 i8 Q3 v

(A) P's * \, O; {: C "I 'p! Hint sqlite3_close (sqlite3 *);
3 d u % Y1 u * U1 o, e + X3 I2 K1 A1 {, C9 z5 C8 J0 C (}
If you have enabled a database with sqlite3_open, do not forget to use this function to close the database.
"S, U * B (K/C * ^
1 J $ R "B -~ The simple code below % K I0 Q is as follows:
1}: A (R + U/\ 3 a c "] # S (H & {6 A"] + R. Q % U3 Z (G
Extern "C"/A1 e-^ 5 N, B
7 R, n' u * _ 4 A2 M * Z9] 5 t
{
-J, _. y9 \/M/d
, Y3 R0 P/I "]-S; ^ 4 h #? & @ # Include "./sqlite3.h" 4}) B9 V # O % E/

Z6 |) _ 9 E; a}; "V ~. A u C3 O5 B
"U % H7 B-x' W ;~
Int main (INT, char **) 0 m' ^. Q # L3 F + S) y7 ~

+ L/_ d; V6 p! L5 ~ 3] 7 @ 8 B {(@ 3 L3 R8 ^, A8 J8 J
L + E8 n8 K! X
Sqlite3 * DB = NULL; // declare SQLite key structure pointer 7} 7 m) D; q (W. R "H0 F0 z *{
Q6 B! H0 O4 _, B! M2 B
Int result;/} D (T (T3 R0 C # P; m
4 J1 @ 7 J's/B * V % l8]
1 S4 M: '_ + D4]-D + u

4 I/J, D. I + I9 C * \ s/u // open the database: X! T7 U, e "V0 E; o

) U % x $ X8? (L8 {8 y // the pointer to the DB pointer needs to be passed in, because the sqlite3_open function needs to allocate memory for this pointer, And the DB pointer must point to this memory zone.
$ W9 U5 Q! T % K $ Z-R * R0 Z % T3 Q
Result = sqlite3_open ("C: \ dcg_database.db", & dB);/J % C H2 J: Z/Z $ q
9 H3 J3 R % G & V6 e-A8 B
If (result! = Sqlite_ OK)
3 H3 J/I $ n7': e4 [
6 I4 [# D3 P4 '{
/]-B (u7 B # T: q'z5 @) y9 m + M) P-B8} f % G
// Failed to open the database
8 C4 W8 S/l0 \/m2 h +?
* O & N2 @ 'U + \. W3 C: mreturn-1;
(D4 Y & l0 V. N "} s % d'] 9 H9 T + Z4 U. V-M J6 F7 g/J-V $ J
}
, E :~ # O7 I2 j9 x/z6 S5 M5 L3 T/P) l! W) O3 |! N
// Database operation code 0 [/y6 a-u # I0 L $}
6 F: \, A & D3 Y3 ^
//...
& R-B5 U (I + C, Q! A # H "E! U9 Q5 j7]; m (M3 x3'
& X0 C. L4 ~. V8 \; H2 G $ P
% Q! U1 S5 B9 y, O & S7 ?. A/R, D
// The database is successfully opened. T1 I. U6 ^ & I! E1 P5 M % M5 E: S
/^) J8 R + C8 B4 I
// Close the database
4 S, N: U0 N1 K )?
"W1 B" O: L; x + N5 | & esqlite3_close (db); "J/R1 Y4 F L .? 6 E7 [+ t0 F % d. d
; E. H; C. x9 s
Return 0;
3 A + _ 4 K2 W4 L # A0 C & _ 4 I6 | 'p3 U5 B, I
}
({5 B 'B "W4 m
$ M0 m2 ~ 6 Q3 R4 I0 R7 H3 X # r This is a database operation process.