Analysis of common problems and summary in SQLite

1. Create data
If you do not add any tables to the database, this database is not established, does not generate any files on the hard disk, and if the database already exists, it will open the database.

2. How to generate Sqlite3.lib files through Sqlite3.dll and Sqlite3.def
Lib/def:sqlite3.def/machine:ix86

3, Sqlite3_open Open a database, if the database does not exist, it will be reborn into a database file. If the subsequent execution of other query statements will fail, such as Sqlite3_prepare, the program appears clearly specified database and there is also data, why the query failed,this is mainly caused by incorrect database name paths. It is common practice to first check that the database file exists, open the database using Sqlite3_open if it exists, or create a new database.

4. How to set up auto-grow field
The column declared as an integer PRIMARY key will grow automatically.

5. What type of data does SQLite3 support?
Null
INTEGER
REAL
TEXT
Blob
But in fact, Sqlite3 also accepts the following data types:
smallint a 16-bit integer.
Interger a 32-bit integer.
Decimal (P,S) p exact value and S-Size decimal integer, the exact value p is the total number (digits) size value, s refers to the number of digits after the decimal point. If there is no special designation, the system will be set to p=5; S=0.
Float 32-bit real number.
A double 64-bit real number.
char (n) n-length string, n cannot exceed 254.
The varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters double-bytes,n cannot exceed 127. This pattern is designed to support two character-length fonts, such as Chinese characters.
Vargraphic (n) variable-length double-character string with a maximum length of n, N cannot exceed 2000.
Date contains the year, month, and date.
Time contains hours, minutes, seconds.
Timestamp contains the year, month, day, time, minute, second, 1 per thousand seconds.

6. SQLite allows the insertion of strings into an integer field
This is a feature, not a bug. SQLite does not enforce 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 into a Boolean column, or insert a date-type value into a character column. The data type specified in the CREATE table does not restrict the insertion of any data into the column. Any column can accept a string of any length (onlyexcept in one case:A column marked as an integer PRIMARY key can store only 64-bit integers, which produces an error when inserting data into such a column except for integers.

However, SQLite does use the declared column type to indicate the format you expect. SoFor example, when you insert a string into an integer column, SQLite attempts to convert the string to an integer. If it can be converted, it inserts the integer; otherwise, the string is inserted. This feature is sometimes referred to as type or column affinity(Type or column affinity).

7. Why is SQLite not allowed to use 0 and 0.0 as a primary key on two different rows of the same table?
The primary key must be a numeric type, and changing the primary key to a text type will not work.
Each row must have a unique primary key.for a numeric column, SQLite thinks ' 0 ' and ' 0.0 ' are the sameBecause they are equal when compared as integers (see previous question). So, this value is not unique.

8, multiple applications or multiple instances of an application can access the same database file at the same time?

Multiple processes can open the same database at the same time. Multiple processes canSelect operation at the same time, but at any one time,only one process can make changes to the database.

SQLite uses read and write locks to control access to the database. (In a system that does not support read and write locks, such as win95/98/me, a probabilistic simulation is used instead.) But be careful when using:This locking mechanism may not work properly if the database files reside on an NFS file system. This is because the FCNTL () file lock is not implemented correctly on many NFS. Avoid placing database files on NFS When multiple processes may have simultaneous access to the database. On Windows, Microsoft's documentation says: If you use the FAT file system without running the Share.exe daemon, the lock may not be working properly. Those who have a lot of experience on Windows told me: for network files, file lock implementation has a lot of bugs, is unreliable. If they are right, sharing a database between two or more Windows machines can cause undesirable problems.

We realized that no other embedded SQL database engine could handle so much concurrency like SQLite.SQLite allows multiple processes to open a database at the same time, while reading a database. When there are any processes that want to write,It must lock the database file during the update process. But that's usually just a few milliseconds. Other processes only need to wait for the write process to finish the job. 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) typically supports a higher level of concurrency and allows multiple processes to write the same database at the same time. This mechanism is possible on the database of the CLIENT/SERVER structure because there is always a single server process that controls and coordinates access to the database well. If your application requires a lot of concurrency, then you should consider using a database with a client/server structure. But experience has shown that many applications require much less concurrency than their designers think.

when SQLite tries to access a file that is locked by another process, the default behavior is to return sqlite_busy. Can be used in C codeSqlite3_busy_handler ()Orsqlite3_busy_timeout ()The API function adjusts this behavior.

9. Is SQLite thread safe?
The thread is the Devil (Threads is evil). Avoid using them.
SQLite is thread-safe. Because many users ignore the advice we gave in the last paragraph, we made this concession. However, in order to achieve thread safety, SQLite must set the SQLITE_THREADSAFE preprocessing macro to 1 at compile time. On Windows and Linux, this is set up in a good compiled binary release. If you are unsure whether the library you are using is thread-safe, you can call the Sqlite3_threadsafe () interface to find out.

10. How do I list all the tables and indexes in the SQLite database?
If you run the Sqlite3 command line to access your database, you can type ". Tables" to get a list of all the tables. Alternatively, you can enter ". Schema" to see the entire database schema, including the index of all tables. Enter these commands, followed by a like pattern match to limit the displayed table.

11. Does the SQLite database have a known size limit?
Under Windows and UNIX, version 2.7.4 SQLite can reach 2 of 41 bytes (2T bytes)。 The old version is 2 of the 31 square bytes (2G bytes).
SQLite version 2.8 Limits the capacity of a record to 1M.SQLite version 3.0 has no limit on the capacity of a single record.
Table names, Index table names, view names, trigger names, and field names do not have a length limit. However, the name of the SQL function (created by the Sqlite3_create_function () API function) must not exceed 255 characters.

12. What is the maximum varchar field in SQLite?
SQLite does not enforce the length of varchar. You can declare a varchar in SQLite, and SQLite can happily allow you to put in 500 characters. And these 500 characters are intact,It will never be truncated.

13. In SQLite, how do I add or delete a column on a table?
SQLite has limited ALTER TABLE support. You can use it to add a column to the end of the table to change the name of the table. If you need to make more complex changes to the table structure, you must re-build the table. When rebuilding, you can put existing data into a temporary table, delete the original table, create a new table, and then copy the data back from the staging table.

For example, suppose you have a T1 table with "A", "B", "C" three columns, and if you want to remove column C, the following procedure describes how to do it:

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. Do you support paging in SQLite?

SQLite paging is the simplest in the world. If I'm going to 11-20 of the Account table data Select * From account Limit 9 Offset 10;
The above statement means getting the data from the Account table, skipping 10 rows, and fetching 9 rows. This feature is enough for many web-medium websites to use this. You can also write a select * from the account limit10,9 with the same effect as above. This syntax is also supported by MySQL.

Analysis of common problems and summary in SQLite