Analysis of common problems and summary in SQLite

1. Create data
If no table is added to the database, the database is not created and no files are generated on the hard disk. If the database already exists, the database is opened.

2. How to generate the sqlite3.lib file through sqlite3.dll and sqlite3.def
LIB/DEF: sqlite3.def/machine: IX86

3. When sqlite3_open opens a database, a new database file will be generated if the database does not exist. If other query statements are executed, for example, sqlite3_prepare, the database is explicitly specified in programming and there is data in it. Why is the query failure caused by incorrect database name path. The general practice is to first check whether the database file exists. If yes, use sqlite3_open to open the database; otherwise, create a new database.

4. How to create an auto-increment Field
Columns declared as integer primary key will automatically grow.

5. What data types does SQLite3 support?
NULL
INTEGER
REAL
TEXT
BLOB
But in fact, sqlite3 also accepts the following data types:
Smallint is an integer of 16 bits.
An integer of 32-bit interger.
Decimal (p, s) p exact value and the decimal integer of s size. Exact value p refers to the number of digits after the decimal point. If not specified, the system is set to p = 5; s = 0.
Float 32-bit real number.
The real number of the double 64-bit element.
Char (n) n length string, n cannot exceed 254.
A string with an unfixed varchar (n) length and a maximum length of n. n cannot exceed 4000.
Graphic (n) is the same as char (n), but it is measured in double-bytes. n cannot exceed 127. This form supports two character-length fonts, such as Chinese characters.
For a dual-character string with a variable vargraphic (n) length and a maximum length of n, n cannot exceed 2000.
Date contains the year, month, and date.
Time contains hours, minutes, and seconds.
Timestamp includes year, month, day, hour, minute, second, And 1‰ seconds.

6. SQLite allows inserting strings 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 one case: 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 ).

7. Why does SQLite not allow the use of 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.

8. Can multiple applications or multiple instances of 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. (Replace it with a probabilistic simulation in a system that does not support read/write locks such as Win95, 98, or ME .) 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 fatfile system and do not run 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 requests 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 higher 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.

When SQLite attempts to access a file locked by other processes, the default action is to return SQLITE_BUSY. You can use the sqlite3_busy_handler () or sqlite3_busy_timeout () API function in the C code to adjust this line.

9. Is SQLite thread secure?
Threads are evil ). Avoid using them.
SQLite is thread-safe. As many users ignore the suggestions we gave in the previous section, we made such concessions. However, to ensure thread security, SQLite must set the SQLITE_THREADSAFE preprocessing macro to 1 during compilation. On Windows and Linux, compiled binary distributions are set in this way. If you are not sure whether the library you are using is thread-safe, you can call the sqlite3_threadsafe () interface to find out.

10. How to list all tables and indexes in the SQLite database?
If you run sqlite3 to access your database, you can enter ". tables" to obtain a list of all tables. Alternatively, you can enter ". schema" to view the entire database mode, including the indexes of all tables. Enter these commands, followed by a LIKE pattern match that can limit the displayed table.

11. Is there a known size limit for the SQLite database?
In Windows and Unix, SQLite of version 2.7.4 can reach the power of 41 bytes (2 t bytes) of 2 ). The old version is 2's 31 Power bytes (2 GB ).
SQLite version 2.8 limits the size of a record to 1 MB. SQLite 3.0 does not limit the capacity of a single record.
The table name, index table name, view name, trigger name, and field name have no length restrictions. However, the SQL function name (created by the sqlite3_create_function () API function) cannot exceed 255 characters.

12. What is the maximum VARCHAR field in SQLite?
SQLite does not force the length of VARCHAR. You can declare a VARCHAR (10) In SQLITE. SQLite is happy to allow 500 characters. And the 500 characters are unblocked and will never be truncated.
 
13. In SQLite, how does one add or delete a column in a table?
SQLite supports limited alter table. 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 );
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. Does SQLite support paging?
 
SQLite paging is the simplest in the world. If I want to Select * From Account Limit 9 Offset 10 for the data in the Account table From 11 to 20;
The preceding statement retrieves data from the Account table, skipping 10 rows and taking 9 rows. This feature is enough for many medium-sized websites to use this feature. You can also write select * from account limit10, 9 in the same way as above. This writing method is also supported by MySQL.