PHP SQLite study notes and FAQ analysis page 1/2_PHP tutorial

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 FAQ
(1) How to create an auto-grow field?

Short answer: Columns declared as INTEGER PRIMARY KEY will grow automatically.

Longer answer: If you declare a column of the table as INTEGER PRIMARY KEY, then whenever you insert a NULL value in that column, NULL is automatically converted to a value greater than the maximum value in the column. An integer of 1, which will be 1 if the list is empty. (If the maximum possible primary key is 9223372036854775807, that one, the key value will be a random unused number.) For example, there is the following list:

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

On this table, the following statement

INSERT INTO t1 VALUES(NULL,123);

is logically equivalent to:

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

There is a new API called sqlite3_last_insert_rowid(), which will return the most recently inserted integer value.

Note that this integer will be 1 greater than the maximum value in the column in the table before the insertion. This key value is unique in the current table. But it is possible to overlap with values ​​that have been deleted from the table. To create a unique key value throughout the life cycle of the table, you need to add an AUTOINCREMENT statement to the INTEGER PRIMARY KEY. Then, the new key value will be 1 greater than the maximum value that ever existed in the table. If the largest possible integer value ever exists in the data table, INSERT will fail with an SQLITE_FULL error code.


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

(2)SQLite3 support What data type?

NULL
INTEGER
REAL
TEXT
BLOB
But in fact, sqlite3 also accepts the following data types:
smallint 16-bit integer.
interger 32-bit integer.
decimal(p,s) The exact value of p and the decimal integer of size s. The exact value p refers to the total number of digits (digits), and s refers to the number of digits after the decimal point. If not specified, the system will set p=5; s=0.
float 32-bit real number.
double 64-bit real number.
char(n) n-length string, n cannot exceed 254.
varchar(n) is a string with a variable length and a maximum length of n. n cannot exceed 4000.
graphic(n) is the same as char(n), but its unit is two characters, double-bytes, and n cannot exceed 127. This form is to support fonts with a length of two characters, such as Chinese characters.
vargraphic(n) A two-character string with variable length and a maximum length of n, n cannot exceed 2000.
date contains year, month, and date.
time includes hours, minutes and seconds.
timestamp contains year, month, day, hour, minute, second and thousandth of a second.

See http://www.sqlite.org/datatype3.html.
-------------------------- -------------------------------------------------- ----

(3)SQLite allows inserting 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, a floating point number into a Boolean column, or a date value into a character column. The data type specified in CREATE TABLE does not restrict the insertion of any data into the column. Any column can accept strings of any length (except for one case: columns marked INTEGER PRIMARY KEY can only store 64-bit integers. When inserting data other than integers into such columns, an error will occur. .

But SQLite does use the declared column type to indicate the format you expect. So, 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 the integer; otherwise, it inserts the string.This property is sometimes called type or column affinity.


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

(4) Why does SQLite not allow the use of 0 and 0.0 as primary keys in two different rows of the same table?

The primary key must be of numeric type. Changing the primary key to TEXT type will 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 previous question). Therefore, this value is not unique.


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

(5)Multiple Can an application or multiple instances of an application access the same database file simultaneously?

Multiple processes can open the same database at the same time. Multiple processes can perform SELECT operations at the same time, but only one process can make changes to the database at any one time.

SQLite uses read and write locks to control access to the database. (In systems such as Win95/98/ME that do not support read and write locks, a probabilistic simulation is used instead.) But be careful when using it: If the database file is stored on an NFS file system, this lock mechanism may Not working properly. This is because fcntl() file locking is not implemented correctly on many NFS. When multiple processes may access the database at the same time, you should avoid placing database files on NFS. On Windows, Microsoft's documentation says: 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 told me: For network files, the implementation of file locks has many bugs and is unreliable. If what they say is right, sharing a database between two or more Windows machines may cause undesirable problems.

We realize that no other embedded SQL database engine can handle as much concurrency as SQLite. SQLite allows multiple processes to open a database at the same time and read a database at the same time. When any process wants 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 writing process to finish its work. Typically, other embedded SQL database engines only allow one process to connect to the database at a time.

However, Client/Server database engines (such as PostgreSQL, MySQL, or Oracle) usually support higher levels of concurrency and allow multiple processes to write to the same database at the same time. This mechanism is possible on a Client/Server structured database 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 Client/Server database. But experience shows that many applications often require much less concurrency than their designers imagined.

When SQLite attempts to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. This behavior can be adjusted in C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.
-------------------------------------------------- ----------------------------