Notes and advanced for SQLite (Quick Start)

I believe that bloggers or passers-by who read my blog will think that SQLite is small, easy to learn, and simple. Next, I will upload my notes to my blog to help some people and myself. Because I know this is the case.

A few days ago, I saw someone reply to my post asking what SQLite databases are used for. In fact, such a question can be posted on Wikipedia or Baidu, but SQLite is a small database, it is mainly used in embedded devices such as mobile phones. The following are some notes about sqlite3 and the advanced section:

1. Automatic classification of types: for example, the text with double quotation marks or single quotation marks in the SQL statement is set as text. If the text does not contain quotation marks or decimal points or indexes, it is defined as integer; if the text is not enclosed in quotation marks but has a decimal point or index, it is defined as real. If the value is null, it is defined as null. BLOB data is identified by the symbol 'abc.

2. In sqlite3.0, the reason why the value is defined is only related to the value itself, and it has nothing to do with columns and variables. We call it "weak type ". All other database engines accept the limitation of the static type system. The type of all values is determined by the attribute of the column to which the database engine belongs, and the value is irrelevant. However, to maximize the compatibility between SQLite and other databases, SQLite proposes a "type affinity", which means that it supports the "type affinity" of columns ". The column type affinity is recommended for the data stored in the column. We should also note that the suggestion is not forced. Theoretically, any column can store any type of data. Only for some columns, if the recommended type is provided, the database will store the data according to the recommended type, and the preferentially used data type is called "affinity type ".

(1) In sqlite3.0, each column in the database is defined as one of the following affinity types: Text, numbers (real numbers), integers, and none. (In this case, I fill in an integer value to a text field. In this case, integer data is stored as text .)

Rules:

(1) If the data type includes the string "int", it is defined as an integer affinity.

(2) If the data type in the column includes any of the following strings: "char", "clob", or "text", the column has a text affinity. note that the varchar type includes the string "char", so it also has the text type affinity.

(3) If the data type of a column includes the string "blob" or if the data type is embodied, the column has no type affinity.

(4). Otherwise, it will have the affinity of the number (real number) type.

3. Operators

All mathematical operators (all operators, rather than the chain operator "|") have a numerical affinity first, if one or both of them cannot be converted to numbers, the operation result will be null. For Concatenation Operators, all operators will first have text affinity. If any of the operators cannot be converted to text (because it is null or blob), the concatenation operator will be null.

4. Classification, sorting, and hybrid selection

When a value is selected using the order clause, the null value is first selected, then the integer and real number are selected in order, and then the text value is selected in the memcmp () Order, finally, blob values are selected in the memcmp () Order. values of no storage type are converted before selection.

5. User-Defined proofreading Sequence

Binary-use memcmp () to compare string data, regardless of text encoding. (Default comparison order)

Reverse-compares binary text in reverse order.

Nocase-the same as binary, but before comparison, 26 uppercase letters will be converted to lowercase letters. [This change can be implemented by writing SQLite user-defined functions, and these user-defined functions are available in the SQLite API. After writing it, compile it and generate the. exe file .]

For binary comparison operators (=, <, >,< = and> =), if the operand is a column, the default comparison type of the column is determined by the comparison sequence used. if both operands are columns, the default comparison type of the left operand determines the comparison sequence to be used. if neither of the two operands is a column, binary comparison is used.

6. sql92 features not supported by SQLite

The order of this list is related to when a feature may be added to SQLite. Features close to the top of the list are more likely to be added in the near future. Features close to the bottom of the list are not directly planned.

7. SQLite does not support Indexing in groups (in simple terms, it means the order of the index when storing data in the database, and how to place the data). This means that, if your index is in integer order, the record will arrange the data in the database in integer order, first 1, second, and third. (Set SQLite page cache: Pragma page_size = size ;)

Let's look at an example: Create Table wibble2 as select * From wibble;

Delete from wibble;

Insert into wibble select * From wibble2 order by key)

Drop table wibble2;

8. How to use a trigger in SQLite to execute the cancel and redo logic: the idea is to create a special table to save the information required for database revocation and redo changes. Because each table in the database needs to be involved in revocation and redo, each Delete, insert, and update generates a trigger, and they can generate registration items in the revocation diary table, this registration item will be revoked. The items in the withdrawal table are composed of General SQL statements. To complete the revocation, the SQL statement can be run again.

Create trigger name [Before | after] Database event on [database name]. Table Name [For each row] [when expression] Begin Trigger execution action End

Database events: Delete Insert Update Update of Field List

Below is a script:

Create Table ex1 (a integer, B text, C real );

Create trigger trigger_ex1_it

After insert on ex1

Begin

Insert into undolog values (null, 'delete from ex1 where rowid = '| new. rowid );

End;

Create trigger trigger_ex1_ut

After update on ex1

Begin

Insert into undolog values (null, 'Update ex1 set a = '| quote (old. a) | ', B =' | quote (old. b) | ', c =' | quote (old. c) | 'where rowid = '| old. rowid );

End;

Create trigger trigger_ex1_dt

Before delete on ex1

Begin

Insert into undolog values (null, 'insert into ex1 (rowid, A, B, C) values ('| old. rowid | ',' | quote (old. a) | ',' | quote (old. b) | ',' | quote (old. c) | ')');

End;

After each insert statement is executed in the ex1 table, the _ excluit trigger generates the text of the delete statement, which will cancel the insert operation. The _ exshortut trigger generates an update statement, which will cancel the function of an update. Trigger_ex1_dt trigger generates a statement, which will cancel the function of a delete statement.

Pay attention to the use of quote () functions in these triggers. Quote () functions are standard in SQLite. It converts its parameters into a form suitable for being contained in SQL statements. The numeric value remains unchanged. A single quotes is added before or after a string, and any inner single quotes is escaped. The quote () function is added to SQLite to perform undo and redo operations.

9. sqlite3 C/C ++ development interface (API function)

Sqlite3 was developed based on sqlite2.8 to support UTF-16 coding, custom text sorting methods, and index blobs fields. The APIs of version 3.0 and version 2.x are very similar, except that the prefix is changed to sqlite3.

SQLite uses a common void * type to point to a UTF-16-encoded string. The client can map void * to any data type that fits their system.

SQLite 3.0 has a total of 83 API functions, in addition to some data structures and predefined (# defines ). (For the complete API introduction, see another document .) however, you can rest assured that these interfaces are not as complex as the number of interfaces implies. the simplest program can still use three functions: sqlite3_open (), sqlite3_exec (), and sqlite3_close ().

To better control the execution of the database engine, you can use the provided sqlite3_prepare () function to compile the SQL statement into bytecode, and then use sqlite3_step () function to execute the compiled bytecode. A group of API functions starting with sqlite3_column _ are used to obtain information in the query result set. Many interface functions are paired and have both UTF-8 and UTF-16 versions. A set of functions are provided to execute User-Defined SQL functions and text sorting functions. [Additional External words: in fact, database operations, Java APIs or C/C ++ libraries are provided for normal Database Operations APIs or methods. I am familiar with these languages, so by the way, there are some commonalities between these languages .]

9.1 how to open and close the database

Typedef struct sqlite3 sqlite3;

Int sqlite3_open (const char *, sqlite3 **);

Int sqlite3_open16 (const void *, sqlite3 **);

Int sqlite3_close (sqlite3 *);

Const char * sqlite3_errmsg (sqlite3 *);

Const void * sqlite3_errmsg16 (sqlite3 *);

Int sqlite3_errcode (sqlite3 *);

Note: The sqlite3_open () function returns an integer to identify the status, instead of returning a pointer to the sqlite3 struct as in version 2. The difference between sqlite3_open () and sqlite3_open16 () Is that sqlite3_open16 () uses UTF-16 encoding (using local host byte order) to pass database file names. To connect to a new database, sqlite3_open16 () converts internal text to UTF-16 encoding, and sqlite3_open () converts text to UTF-8 encoding.

Commands for opening or creating a database will be cached until the database is actually called. Pragma declaration can be used to set options and parameters such as local text encoding or default memory page size.

Sqlite3_errcode () is usually used to obtain the error code returned by the recently called API. Sqlite3_errmsg () is used to obtain the text description corresponding to these error codes. These error messages are returned in UTF-8 encoding and cleared the next time any SQLite API function is called. Sqlite3_errmsg16 () and sqlite3_errmsg () are roughly the same except that the error messages returned are encoded in the native byte order of the UTF-16.

The error codes of sqlite3 have no changes compared with sqlite2. They are:

# Define sqlite_ OK 0/* successful result */

# Define sqlite_error 1/* SQL error or missing database */

# Define sqlite_internal 2/* an 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 sqlite_interrupt ()

# Define sqlite_ioerr 10/* some kind of disk I/O error occurred */

# Define sqlite_0000upt 11/* The database disk image is malformed */

# Define sqlite_notfound 12/* (internal only) table or record not found

# 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/* (internal only) database table is empty */

# Define sqlite_schema 17/* The database schema changed */

# Define sqlite_toobig 18/* too much data for one row of a table */

# Define sqlite_constraint 19/* abort due to contraint 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_row 100/* sqlite_step () has another row ready */

# Define sqlite_done 101/* sqlite_step () has finished executing */

9.2 Execute SQL statements

Typedef int (* sqlite_callback) (void *, Int, char **, char **);

Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite_callback, void *, char **);

The sqlite3_exec function still undertakes a lot of work as it does in sqlite2. The second parameter of the function can compile and execute zero SQL statements. The query result is returned to the callback function. For more information, see api reference.

Typedef struct sqlite3_stmt sqlite3_stmt;

Int sqlite3_prepare (sqlite3 *, const char *, Int, sqlite3_stmt **, const char **);

Int sqlite3_prepare16 (sqlite3 *, const void *, Int, sqlite3_stmt **, const void **);

Int sqlite3_finalize (sqlite3_stmt *);

Int sqlite3_reset (sqlite3_stmt *);

The sqlite3_prepare interface compiles an SQL statement into bytecode and leaves it to the execution function sqlite3-_ exec (). Using this interface to access the database is currently a better method.

The SQL statement processed by sqlite3_prepare () should be UTF-8 encoded. Sqlite3_prepare16 () requires UTF-16 encoding. Only the first SQL statement in the input parameters is compiled. The fourth parameter is used to point to the SQLite statement object stored in the next SQL statement to be compiled in the input parameter. If you call sqlite3_finalize () at any time, a prepared SQL statement is destroyed. Before the database is shut down, all prepared statements must be released and destroyed. Sqlite3_reset ()
The function is used to reset the state of an SQL statement so that it can be executed again.

SQL statement can contain some types such "? "Or "? Nnn "or": AAA ", where" nnn "is an integer and" AAA "is a string. These tags represent some uncertain character values (or wildcards). You can use the sqlite3_bind interface to fill these values later. Each wildcard is assigned a number (determined by its position in the SQL statement, starting from 1). In addition, "nnn" can be used to indicate "? Nnn. Allow the same wildcard to appear multiple times in the same SQL statement. In this case, all the same wildcard will be replaced with the same value. The unbound wildcard is automatically used.
Null Value.

Int sqlite3_bind_blob (sqlite3_stmt *, Int, const void *, int N, void (*) (void *));

Int sqlite3_bind_double (sqlite3_stmt *, Int, double );

Int sqlite3_bind_int (sqlite3_stmt *, Int, INT );

Int sqlite3_bind_int64 (sqlite3_stmt *, Int, long INT );

Int sqlite3_bind_null (sqlite3_stmt *, INT );

Int sqlite3_bind_text (sqlite3_stmt *, Int, const char *, int N, void (*) (void *));

Int sqlite3_bind_text16 (sqlite3_stmt *, Int, const void *, int N, void (*) (void *));

Int sqlite3_bind_value (sqlite3_stmt *, Int, const sqlite3_value *);

These are all interfaces included in sqlite3_bind. They are used to assign values to wildcards in SQL statements. If no wildcard is bound, it is considered as a null value. The bound value is not reset by the sqlite3_reset () function. However, after sqlite3_reset () is called, all wildcards can be assigned a value again.

After the SQL statement is ready (the binding steps are optional), you need to call the following methods for execution:

Int sqlite3_step (sqlite3_stmt *);

If the SQL statement returns a single row result set, the sqlite3_step () function returns sqlite_row. If the SQL statement is successfully executed or normal, sqlite_done is returned. Otherwise, an error code is returned. if the database file cannot be opened, sqlite_busy is returned. if the return value of the function is sqlite_row, the following methods can be used to obtain data in the record set row:

Const void * sqlite3_column_blob (sqlite3_stmt *, int icol );

Int sqlite3_column_bytes (sqlite3_stmt *, int icol );

Int sqlite3_column_bytes16 (sqlite3_stmt *, int icol );

NT sqlite3_column_count (sqlite3_stmt *);

Const char * sqlite3_column_decltype (sqlite3_stmt *, int icol );

Const void * sqlite3_column_decltype16 (sqlite3_stmt *, int icol );

Double sqlite3_column_double (sqlite3_stmt *, int icol );

Int sqlite3_column_int (sqlite3_stmt *, int icol );

Long long int sqlite3_column_int64 (sqlite3_stmt *, int icol );

Const char * sqlite3_column_name (sqlite3_stmt *, int icol );

Const void * sqlite3_column_name16 (sqlite3_stmt *, int icol );

Const unsigned char * sqlite3_column_text (sqlite3_stmt *, int icol );

Const void * sqlite3_column_text16 (sqlite3_stmt *, int icol );

Int sqlite3_column_type (sqlite3_stmt *, int icol );

The number of columns in the result set returned by the sqlite3_column_count () function. Sqlite3_column_count () can be called at any time after sqlite3_prepare () is executed. Sqlite3_data_count () is similar to sqlite3_column_count () except that it must be called after sqlite3_step. If sqlite3_step () is called and the return value is sqlite_done or an error code is returned, sqlite3_data_count () is called at this time ()
0 is returned. However, sqlite3_column_count () still returns the number of columns in the result set. The returned record set is extracted using several other sqlite3_column _ *** () functions. All these functions use the column number as the second parameter. The column number starts from left to right with zero.

The sqlite3_column_type () function returns the Data Type of the value of column N. The returned values are as follows:

# Define sqlite_integer 1

# Define sqlite_float 2

# Define sqlite_text 3

# Define sqlite_blob 4

# Define sqlite_null 5

Sqlite3_column_decltype () is used to return the type declared in the create table statement. It can be used when the return type is a null string. Sqlite3_column_name () returns the field name of column N. Sqlite3_column_bytes () is used to return the number of bytes of the blobs column encoded by the UTF-8 or the number of bytes of the text string. Sqlite3_column_bytes16 () returns the same result for the blobs column, but for the text string, according to The UTF-16
To calculate the number of bytes. Sqlite3_column_blob () returns BLOB data. Sqlite3_column_text () returns text data encoded by the UTF-8. Sqlite3_column_text16 () returns text data encoded by the UTF-16. Sqlite3_column_int () returns an integer of the local host. Sqlite3_column_int64 () returns a 64-bit integer. Finally, sqlite3_column_double () returns a floating point number.

10. SQLite FAQs

(1) how to create an Automatically increasing field: The field can be declared as integer primary key autoincreate.

(2) Data Types supported by SQLite: see
Http://www.sqlite.org/datatype3.html.

(3) SQLite allows any type of fields to be inserted in a column. (We have mentioned that SQLite is a weak type and does not require other languages, such as Java ), however, fields of any type are automatically converted to the type required by the column. If fields of any type cannot be converted, they are inserted based on the corresponding type. (This is the type affinity I mentioned earlier) except that columns marked as integer primary key can only store 64-bit integers, an error occurs when inserting data other than integers into such columns.

(4) SQLite does not allow 0 and 0.0 as primary keys on two different rows in the same table, because SQLite considers the two rows to be equal. Since they are equal, they are not unique.

(5) multiple processes can open the same database at the same time. Multiple processes can perform the select (read) operation at the same time, but at any time, only one process can change (write) The database ). I think there is a lot of authentication when I see such a sentence, so although it is not the original saying, it generally means this. There are many bugs in the implementation of File locks for network files, it is unreliable. If they are right, sharing a database between two or more Windows machines may cause unexpected problems (relational database calculation ). SQLite allows multiple processes to open a database and read a database at the same time. When any process wants to write data, it must lock the database file during the update process.
But it is usually only a few milliseconds. Other processes only need to wait for the completion of the write process. When SQLite attempts to access a file locked by other processes, the default action is to return sqlite_busy. (Sqlite3 is thread-safe)

(6) How to list all tables and indexes in the SQLite Database :. tables and. schema and data dictionary sqlite_master; dictionary structure: type text, Name text, tbl_name text, rootpage integer, SQL text.

(7) Does the SQLite database have known size restrictions? See limits.html.

(8) In SQLite, how does one add or delete a column in a table? SQLite does not provide complete alter support. You can use it to add a column at the end of the table to change the table name. To make more complex changes to the table structure, you must create a new table. You can put existing data in a temporary table, delete the original table, create a new table, and copy the data from the temporary table. For example, assume that there is a table T1 with columns "A", "B", and "C". To delete column C, the following procedure describes how to do this:

Begin transaction;

Create temporary table t1_backup (a, B); temporary table

Insert into t1_backup select a, B from T1; temporary table copy table T1 data

Drop table T1;

Create Table T1 (A, B );

Insert into T1 select a, B from t1_backup;

Drop table t1_backup;

End transaction;

Commit;