Analysis of common problems in SQLite and summary of the detailed _mysql

1. Create data
If you do not add any tables to the database, the database is not established, does not produce any files on the hard disk, and opens the database if the database already exists.

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 will be reborn into a database file. If you continue to execute other query statements will fail, such as sqlite3_prepare, programming in the presence of clearly specified the database and there are data, why the query failed, This is mainly due to incorrect database name paths. The general practice is to first check the database file exists, use Sqlite3_open to open the database if it exists, or create a new database.

4. How to set up automatic growth field
A column declared as an integer PRIMARY key will automatically grow.

5, what type of data SQLite3 support?
Null
INTEGER
Real
TEXT
Blob
In practice, however, 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 means that all have several number (digits) size values, S is the number of several digits after the decimal point. If not specifically specified, the system is set to p=5; S=0.
A float 32-bit real number.
A double 64-bit real number.
char (n) n length of string, n cannot exceed 254.
varchar (n) length is not fixed and its maximum length is n of the string, n can not exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters double-bytes,n cannot exceed 127. This form is designed to support two-character-length fonts, such as Chinese characters.
Vargraphic (N) a two-character string with a variable length and a maximum length of n that cannot exceed 2000.
Date contains the year, month, and date.
Time contains hours, minutes, and seconds.
Timestamp contains years, months, days, hours, minutes, seconds, and 1 per thousand seconds.

6. SQLite allows you to insert a string into an integer field
This is an attribute, 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 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 strings of any length (only except in one case:A column marked with the integer PRIMARY key can store only 64-bit integers, and an error occurs when you insert data into such a column except for integers.

But SQLite does use declared column types to indicate the format you expect. So For 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 primary keys on two lines different from the same table?
The primary key must be a numeric type, and changing the primary key to the text type will not work.
Each row must have a unique primary key. for a numeric column, sqlite that ' 0 ' and ' 0.0 ' are the same, because they are equal when compared as integers (see the previous question). So, that's not the only value.

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 can Select 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 win95/98/me and other systems that do not support read and write locks, a probabilistic simulation is used instead.) But when used, be aware that: This locking mechanism may not work correctly if the database file is stored on an NFS file system. This is because the FCNTL () file lock is not implemented correctly on many NFS. You should avoid putting database files on NFS When you may have multiple processes simultaneously accessing the database. On Windows, Microsoft's documentation says that if you use the FAT file system without running the Share.exe daemon, the lock may not work properly. Those who have a lot of experience on Windows tell me: For network files, the implementation of the file lock has a lot of bugs, is unreliable. If they are right, sharing a database between two or more Windows machines may cause an unexpected problem.

We realize that no other embedded SQL database engine can handle so many concurrency like SQLite. SQLite allows multiple processes to open a database at the same time, while reading a database. When any process wants to write, It must lock the database file during the update process. But that's usually just a few milliseconds ' time. Other processes simply wait for the write process to finish. 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 higher-level concurrency and allows multiple processes to write the same database at the same time. This mechanism is possible on a database of client/server structures because there is always a single server process that controls and coordinates access to the database. 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 need more concurrency than their designers think.

when SQLite attempts to access a file that is locked by another process, the default behavior is to return sqlite_busy. Can be used in C code Sqlite3_busy_handler ()Or sqlite3_busy_timeout ()API functions to adjust this behavior.

9. Is SQLite thread safe?
Thread is the Devil (Threads are evil). Avoid using them.
SQLite is thread-safe. We made this concession because many users would ignore the suggestions we gave in the last paragraph. However, to achieve thread safety, SQLite must set the SQLITE_THREADSAFE preprocessing macro to 1 at compile time. On Windows and Linux, this is done in a good binary release that has been compiled. If you are unsure whether the library you are using is thread-safe, you can call the Sqlite3_threadsafe () interface to find it.

10, how to 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 look at the entire database schema, including the index of all tables. Enter these commands, followed by a like pattern match to restrict the displayed table.

11. Is there a known size limit for the SQLite database?
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-byte (2G bytes).
SQLite version 2.8 Limits the capacity of one record to 1M. SQLite version 3.0 has no limit on the individual record capacity.
The table name, Index table name, view name, trigger name, and field name 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, in the SQLite, varchar field is the longest?
SQLite does not force the length of the varchar. You can declare a varchar in SQLite, SQLite can be happy to allow you to put 500 characters. And these 500 characters are intact, It will never be truncated.

13, in SQLite, how to 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 rebuild 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 temporary table.

For example, suppose you have a T1 table with "A", "B", "C" three columns, and if you want 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. Support Pagination in SQLite?

SQLite paging is the simplest in the world. If I'm going to 11-20 the account table data Select * From account Limit 9 Offset 10;
The above statement represents fetching data from the account table, skipping 10 rows, and taking 9 rows. This feature is enough for many web midsize sites to use this. You can also write the select * from Account limit10,9 and the effect above. This writing is also supported by MySQL.