FAQs about SQLite

AboutSQLiteThe FAQ is what we will introduce in this article.SQLiteHow can we solve the problems we encountered? Let's take a look at the content.

1. How to create an auto-increment field?

2,SQLiteWhat data types are supported?

3. SQLite allows you to insert a string into an integer field!

4. Why can't SQLite use 0 and 0.0 as the primary key on two different rows in the same table?

5. Can multiple applications or multiple instances of one application simultaneously access the same database file?

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

Short answer: columns declared as integer primary key will automatically grow.

Long answer: If you declare an integer primary key as a column in the table, when you insert a NULL value into the column, NULL is automatically converted to an integer greater than the maximum value of 1 in the column. If the table is empty, it will be 1. (If the maximum possible primary key is 9223372036854775807, the key value will be a random unused number .) For example, the following list is available:

 
 

  
  
CREATE TABLE t1( a INTEGER PRIMARY KEY, b INTEGER ); 
 
 

On this table, the following statements

 
 

  
  
INSERT INTO t1 VALUES(NULL,123); 
 
 

Logically equivalent:

 
 

  
  
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123); 
 
 

There is a new API called sqlite3_last_insert_rowid (), which returns the last integer inserted.

Note that this integer is 1 larger than the maximum value before the column is inserted in the table. The key value is unique in the current table. However, it may overlap the deleted values in the table. To create a unique KEY value throughout the lifecycle of a table, you must add the AUTOINCREMENT Statement on the integer primary key. Then, the new key value will be 1 larger than the maximum value that can exist in the table. If the maximum possible integer value exists in the data table, INSERT will fail and return the SQLITE_FULL error code.

(2) what data types does SQLite support? See http://www.sqlite.org/datatype3.html.

(3) SQLite allows you to insert a string into an integer field!

This is a feature, not a bug. SQLite does not force data type constraints. Any data can be inserted into any column. You can insert a string of any length into an integer column, insert a floating point number to a Boolean column, or insert a date value to a numeric column. The data type specified in create table does not limit the insertion of any data in this column. Any column can accept strings of any length except in the case 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.

However, SQLite uses the declared column type to indicate the expected format. Therefore, for example, when you insert a string into an integer column, SQLite will try to convert the string into an integer. If it can be converted, it inserts this integer; otherwise, it inserts a string. This feature is sometimes called type or column affinity ).

(4) Why isn't SQLite allowed to use 0 and 0.0 as the primary key on two different rows in the same table?

The primary key must be numeric. Changing the primary key to TEXT does not work.

Each row must have a unique primary key. For a numeric column, SQLite considers '0' and '0. 0' to be the same, because they are equal when compared as integers (see the previous question ). Therefore, the value is not unique.

(5) can multiple instances of multiple applications or one application simultaneously access the same database file?

Multiple processes can open the same database at the same time. Multiple processes can perform the SELECT operation at the same time, but at any time, only one process can change the database.

SQLite uses read and write locks to control database access. In a system that does not support read/write locks, such as Win95, 98, or ME, a probabilistic simulation is used instead .) Note: If the database file is stored in an NFS file system, this locking mechanism may not work properly. This is because the fcntl () file lock is not correctly implemented on many NFS servers.

When multiple processes may access the database at the same time, you should avoid placing database files on NFS. On Windows, Microsoft documents said: If you use the FAT file system without running the cmd.exe daemon, the lock may not work properly. Those who have a lot of experience on Windows tell me that there are many bugs in the implementation of File locks for network files, which are unreliable. If they are right, sharing a database between two or more Windows machines may cause unexpected problems.

We realized that there are no other embedded SQL database engines that can process so many concurrent jobs like SQLite. 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. Typically, other embedded SQL database engines allow only one process to connect to the database at the same time.

However, the Client/Server database engine, such as PostgreSQL, MySQL, or Oracle, usually supports a higher level of concurrency and allows multiple processes to write to the same database at the same time. This mechanism is possible in the database of the Client/Server structure, because there is always a single Server process that controls and coordinates access to the database. If your application requires a lot of concurrency, you should consider using a database with a Client/Server structure. However, experience shows that many applications often require less concurrency than their designers think.

WhenSQLiteWhen attempting to access a file locked by another process, the default behavior is to returnSQLITE_ BUSY. It can be used in C codeSqlite3_busy_handler () orSqliteThe 3_busy_timeout () API function adjusts this line.

Summary: AboutSQLiteThe answers to frequently asked questions have been introduced. I hope you can learn this article to help you!