SQLite complete Chinese FAQ

--------------------------------------------------------------------------------

(1) how to create an auto-increment field?

A simple answer: a field declared as integer primary key will be automatically added.

Here is a detailed answer: Starting from SQLite 2.3.4, if you declare a field in a table as integer primary key, no matter when you insert a null value to the field in the table, the null value will be automatically replaced with an integer greater than the maximum value of 1 for all rows of the field in the table; if the table is empty, it is replaced with 1. For example, suppose you have a data table like this:

Create Table T1 (
A integer primary key,
B integer
);
In this data table, declare

Insert into T1 valuees (null, 123 );
Logically, it is equivalent:

Insert into T1 values (select max (a) from T1) + 1,123 );
A new API function sqlite3_last_insert_rowid () returns the integer key of the most recent insert operation.

Note that this integer key is always 1 larger than the last key in the previously inserted Table. The new key is unique relative to the existing key in the table, but it may overlap the key value that was previously deleted from the table. To always obtain the unique key in the entire table, add the keyword autoincrement before the declaration of the integer primary key. In this way, the selected key will always be 1 larger than the existing maximum key in the table. If the maximum key already exists in the table, the insert operation fails and returns a sqlite_full error code.

--------------------------------------------------------------------------------

(2) what data types does SQLite support?

See http://www.sqlite.org/datatype3.html.

--------------------------------------------------------------------------------

(3) Why can I insert a string into the integer field of the SQLite database?

This is a feature, not a bug. You can put any information in any field without worrying about the type of the field declaration. You can insert a string of any length to an integer field, insert a floating point to a Boolean field, or insert a date to a character field. The data type you specify for this field in the create table command does not limit the data inserted into this field. All fields can be inserted into strings of any length. Except for the integer primary key field. This field can only store one 64-bit integer; otherwise, an error occurs.

However, SQLite will use the declared field type by default. Therefore, for example, if you want to insert a string into a field declared as integer, SQLite will try to convert it into an integer. If the conversion is successful, the integer is inserted. Otherwise, the string is inserted. This feature is sometimes called type or column affinity.

--------------------------------------------------------------------------------

(4) Why does SQLite think the expression '0' = '00' is true?

The expression is invalid after 2.7.0. See datatypes in SQLite Version 3

--------------------------------------------------------------------------------

(5) Why does SQLite not allow '0' and '0. 0' to be used as the primary keys of two different rows in the same table?

Your primary key must be of the numeric type. You can change the type to text.

Each row must have a unique primary key. As a numeric field, SQLite considers '0' and '0. the values of 0' are the same, because their comparison on numbers is equal (see the previous problem), so the values are not unique.

--------------------------------------------------------------------------------

(6) Why can't I read the SQLite database created in sparcstation in Linux box?

You need to upgrade your SQLite library to 2.6.3 or later.

X86 processors are of the little-Endian type, while those of the world's big-Endian type. The new version of SQLite solves this problem.

Note: Big endian and little endian are different ways for the CPU to process the number of multi-word segments. For example, the Unicode code of the Chinese character is 6c49. When I write a file, do I write 6C in front or 49 in front? If you write 6C in front, it is big endian. Write 49 in front, that is, little endian.

--------------------------------------------------------------------------------

(7) can multiple examples of multiple applications or the same application simultaneously access the same database file?

Multi-process can open the same database at the same time, or select at the same time. However, only one process can change the database immediately.

SQLite uses read/write locks to control database access. (The Win95/98/me operating system does not support read/write locking. In versions earlier than 2.7.0, this means that only one process can read the database at a time in windows. In version 2.7.0, this problem is solved by executing a user interval probability read/write lock policy in the Windows interface code .) However, if the database file is in an NFS file system, the locking mechanism for controlling concurrent reading may fail. This is because the fcntl () file of NFS is sometimes locked. If multiple processes may read the database concurrently, avoid placing the database file in the NFS file system. According to Microsoft's documentation, if you do not run the cmd.exe background program, the lock in the FAT file system may not work. People who are very experienced in Windows tell me that the locking of network files has many problems and is not reliable. If so, sharing an SQLite database file in two or more Windows systems will cause unpredictable problems.

We know that no other embedded SQL database engine supports more concurrency than SQLite. SQLite allows multiple processes to open and read databases at the same time. When any process needs to be written, the entire database will be locked in this process. However, this generally takes only a few milliseconds. Other processes only need to wait and continue other transactions. Other embedded SQL database engines often only allow single-process access to the database.

However, Client/Server database engines (such as PostgreSQL, MySQL, and Oracle) usually support higher concurrency and multi-process writing to the same database at the same time. Since there is always a well-controlled server that coordinates database access, this ensures the implementation of the above features. If your application requires high concurrency, you should consider using the Client/Server database. In fact, experience tells us that most applications require less concurrency than their designers think.

When SQLite attempts to operate a file locked by another process, the default action is to return sqlite_busy. You can use the C code to change this line. Use the sqlite3_busy_handler () or sqlite3_busy_timeout () API function.

If two or more processes open the same database at the same time, and one of the processes creates a new table or index, other processes may not immediately see the new table. Other processes may need to be shut down and re-linked to the database.

--------------------------------------------------------------------------------

(8) is SQLite thread-safe?

Sometimes yes. To ensure thread security, SQLite must set the threadsafe preprocessing macro to 1 during compilation. In the default released compiled version, Windows is thread-safe, but Linux is not. If thread security is required, the Linux version must be re-compiled.

"Thread security" refers to the "sqlite3" structure returned by two or three threads that can call different sqlite3_open () at the same time. Instead of using the same sqlite3 structure pointer in multiple threads.

A sqlite3 structure can only be used in the process that calls sqlite3_open to create it. You cannot open a database in one thread and pass the pointer to another thread for use. This is because of the limitations of most multithreading systems (or bugs ?) For example, on redhat9. In these problematic systems, the fcntl () Lock created by one thread cannot be deleted or modified by another thread. Because SQLite relies on the fcntl () lock for concurrency control, serious problems may occur when database connections are transferred between threads.

Maybe there is a way to solve the fcntl () lock problem in Linux, but it is very complicated and it will be extremely difficult to test the correctness. Therefore, SQLite currently does not allow sharing handles between threads.

In UNIX, you cannot use a fork () system call to put an open SQLite database into a sub-process. Otherwise, an error occurs.

--------------------------------------------------------------------------------

(9) How to list all the tables/indexes in an SQLite database?

In the sqlite3 command line program, you can use the ". Tables" command to display all tables or ". schema" to display all table structures and indexes. But do not follow the like statement after the command; otherwise, the display of the table will be restricted.

In C/C ++ programs (or scripts bound to Tcl/Ruby/perl/Python), you can access the table named "sqlite_master. Each SQLite database has a sqlite_master table with a database structure. The sqlite_master table is as follows:

Create Table sqlite_master (
Type text,
Name text,
Tbl_name text,
Rootpage integer,
SQL text
);
For a table, the value of the type field is 'table', and the name field is the table name. Use the following statement to wait for the list of all tables:

Select name from sqlite_master
Where type = 'table'
Order by name;
For an index, type = 'index', name indicates the index name, And tbl_name indicates the name of the table to which the index belongs. For tables and indexes, SQL fields are the original statement text for creating tables or indexes. For automatically created indexes (generally created using primary key or unique), the SQL field is null.

Sqlite_master table is read-only. You cannot use update, insert, or delete for this table. This table is automatically updated by the create table, create index, drop table, and drop index commands.

The temporary table and its indexes are not in the sqlite_master table but in sqlite_temp_master. The sqlite_temp_master and sqlite_master tables work the same way, but they are only visible to programs that create temporary tables. To obtain a temporary table, run the following command:

Select name from
(Select * From sqlite_master Union all
Select * From sqlite_temp_master)
Where type = 'table'
Order by name

--------------------------------------------------------------------------------

(10) Does the SQLite database have a known size limit?

The database size is limited to 2 TB (241 bytes). This is a theoretical limitation. In fact, you should limit the size of the SQLite database to less than GB to avoid running performance problems. If you need to store GB or more data in one database, consider using the Enterprise Edition database designed for this purpose.

The theoretical row quantity limit for a database is 264-1. Obviously, you will first exceed the file size limit before the row quantity limit is reached. Currently, a row can store 230 bytes of data. The basic file format supports a row size of about 262 bytes.

There may also be restrictions on the number of tables, indexes, or the number of fields in the tables and indexes, but no one knows what it is. In fact, when a new database is opened, SQLite needs to read and analyze the initial SQL statements for all tables and index declarations. Therefore, to obtain the best performance when calling sqlite3_open, reduce the number of declared tables. Similarly, even if there is no limit on the number of fields in the table, more than 100 fields appear too much. Only 31 fields starting with the table are optimized. You can add any number of fields to an index, but indexes with more than 30 fields are not used to optimize the query.

The table, index, view, trigger, and field names can be any long, but the SQL function name (created by sqlite3_create_function () API) cannot exceed 255 characters.

--------------------------------------------------------------------------------

(11) What is the maximum varchar length in SQLite?

SQLite does not force the length of varchar. You can declare a varchar (10). SQLite allows you to store 500 characters in it. And they will remain intact-they will never be truncated.

--------------------------------------------------------------------------------

(12) Does SQLite support the Blob type?

SQLite 3.0 supports storing BLOB data in any field, regardless of the type of field declaration.

--------------------------------------------------------------------------------

(13) how to add/delete fields from an existing SQLite data table?

SQLite has limited alter table support and can be used to add fields to the end of a table or change the table name. If you want to make more complex changes to the table structure, you need to recreate the table. You can back up data in a temporary table, undo the old table, and recreate the new table Before restoring the data.

For example, if you have a table named "T1" named "A", "B", and "C", you must delete the field "C ". Follow these steps:

Begin transaction;
Create temporary table t1_backup (A, B );
Insert into t1_backup select a, B from T1;
Drop table T1;
Create Table T1 (A, B );
Insert into T1 select a, B from t1_backup;
Drop table t1_backup;
Commit;

--------------------------------------------------------------------------------

(14) I deleted a lot of data but the database file was not reduced. Is it a bug?

No. After you delete data from SQLite, unused disk space is added to an internal "Idle list" to store the data you insert next time. The disk space is not lost, but the disk space is not returned to the operating system.

If you delete a large amount of data and want to reduce the database file, run the VACUUM command. The vacuum command clears the "Idle list" and minimizes the database size. Note that vacuum takes some time (about 0.5 seconds/MB in Linux) and uses twice the size of the database file.

For SQLite version 3.1, one method to replace the VACUUM command is the auto-vacuum mode, which is enabled using the auto_vacuum Pragma syntax.

--------------------------------------------------------------------------------

(15) can SQLite be used for commercial purposes without Copyright charges?

Yes. SQLite is public. No part of the code is declared as ownership. You can use it to do whatever you want.

--------------------------------------------------------------------------------

(16) how to insert a string with single quotes?

Use double single quotes, for example:

Insert into XYZ values ('5 o' 'clock ');

The database to be inserted is: 5 0' clock.

--------------------------------------------------------------------------------

(17) what are sqlite_schema errors?

In SQLite version 3, if a pre-processing SQL statement is invalid and cannot be executed, a sqlite_schema error is returned. When this error occurs, the statement should be re-compiled using the sqlite3_prepare () API function. In SQLite version 3, this error occurs only when sqlite3_prepare ()/sqlite3_step ()/sqlite3_finalize () API function is used to execute SQL statements, but sqlite3_exec. This is different from version 2.

Most of the reasons for this error are that the database has changed after SQL preprocessing (possibly changed by another process ). There may also be the following reasons:

Detach a database
Vacuum operations on a database
A user function definition has been deleted or changed.
A sorting definition is deleted or changed.
An authorization function has changed.
The solution is to re-compile and try again. All API functions related to sqlite3_prepare (), sqlite3_step (), and sqlite3_finalize () should be re-compiled. See the following example:

Int RC;
Sqlite3_stmt * pstmt;
Char zsql [] = "Select .....";

Do {
/* Compile the statement from SQL. Assume success .*/
Sqlite3_prepare (PDB, zsql,-1, & pstmt, 0 );

While (sqlite_row = sqlite3_step (pstmt )){
/* Do something with the row of available data */
}

/* Finalize the statement. If an sqlite_schema error has
** Occured, then the above call to sqlite3_step () will have
** Returned sqlite_error. sqlite3_finalize () will return
** Sqlite_schema. In this case the loop will execute again.
*/
Rc = sqlite3_finalize (pstmt );
} While (rc = sqlite_schema );

--------------------------------------------------------------------------------

(18) Why does round (9.95, 1) return 9.9 instead of 10.0? Shouldn't 9.95 be carried up?

SQLite uses binary operations internally, and 9.95 is represented as 9.949999999999999289457264239899814128875732421875 with a 64-bit IEEE floating point number (which is used internally by SQLite. So when you enter "9.95", SQLite is understood as the above number, and then rounded to 9.9. This problem will always occur when dealing with floating-point binary numbers. The general rule is that a decimal finite floating point number cannot be expressed as a binary finite floating point number. It can only be replaced by the nearest binary number. This approximate number is very close to the original number, but it is slightly different, so you may not get the expected result.