PhpSQLite study notes and FAQs page 1/2

PhpSQLite learning materials collection, and solutions to some common problems until you learn! Find information before learning
SQLite SQL
ATTACH DATABASE
BEGIN TRANSACTION
Comment
COMMIT TRANSACTION
COPY
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DELETE
DETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
END TRANSACTION
EXPLAIN
Expression
INSERT
On conflict clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE


Sqlite FAQs
(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 valuees (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 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.

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 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 ).


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

(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. (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 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 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.
---------------------------------------------------------------------------