Learning to use SQLite (version 3.7.4) (medium J)

Transaction type:

There are three different types of transactions that start transactions in Different lock states.

Specify the transaction type in the begin command: Begin [deferred | immediate | exclusive] transaction;

Deferred must be used to obtain the lock. By default, the instance is not locked. Transactions started with deferred are delayed and stay in the unlocked state. Multiple connections can start to delay transactions without creating any locks at the same time. In this case, the first read operation on the database gets the shared lock, and the first write operation on the database gets the reserved lock.

When an immediate transaction is started, the view obtains the reserved lock. If the connection succeeds, begin immediate ensures that no other connection is written to the database, but other connections can read the database. Another result of the reserved lock is that no other connection can successfully start the begin immediate or begin exclusive command. When other connections execute the preceding command, SQLite will return the sqlite_busy error. At this time, you can modify the database but cannot submit it. When using commit, there will be a sqlite_busy error. That is to say, the read transaction has not been completed, and you have to wait.

Exclusive transactions will try to obtain the exclusive lock, similar to the immediate transaction. It is worth noting that after obtaining the exclusive lock, the database has no other active links. This connection can be used to read or write any data to the database.

 

Database Management:

Additional database:

SQLite allows the use of the attach command to "Attach" multiple databases to the current connection, and its content is optional globally.

Syntax format: attach [database] filename as database_name;

Filename is the name and path of the SQLite database, and database_name is the logical name of the database and object to be referenced. The primary database is automatically assigned the name "Main. If a temporary object is created, SQLite automatically creates an additional database named temp. The logical name can be used to reference objects in the attached database. At the same time, the object that references the attached database must provide the logical name. This is useful when two databases have the same table.

Use the full name to reference objects in the database:

Separate database:

Detach [database] database_name;

Database cleanup:

Command: reindex (used to re-create the index, re-create all the indexes using the specified sorting name) and vacuum (restructured the database to clear unused space, open state transactions, vacuum is not executed ).

Reindex usage: reindex collation_name;

Reindex table_name | index_name;

Vacuum usage: vacuum
[index-or-table-name]

    

Database Configuration:

SQLite does not have a configuration file, and all its configuration parameters are implemented using Pragma. This pragma has the characteristics of commands and variables.

Common Content in compilation instructions:

A. Connection buffer size:

Controls the number of database pages that a connection can use in memory. Method: Pragma cache_size;

Note: The default_cache_size Compilation instruction can be used to set the size of the permanent buffer for all connections by default.

This setting can be stored in the database. It only takes effect for future connections and does not take effect for the current connections. The buffer can actually store changes to be determined when transactions are reserved for locks.

. The large buffer zone is encouraging the amount of things that can be done before obtaining the exclusive lock.

B. Obtain database information:

Database_list: obtain all attached databases,

For clarity, I have attached the temporary database temp. dB to test. DB.

Index_info: lists information about fields in an index. Index name as a parameter,

        Index_list: lists the index information in a table. The table name is used as a parameter.

Table_info: lists information about all fields in the table.

        

C. Write synchronization:

In general, SQLite will commit all the changes to the disk at a critical moment to ensure transaction persistence. However, you can disable this function because it affects performance.

Compile to indicate synchronous. Three settings of this compilation indicator: full, normal, and off.

Full: pause at key points before continuing to ensure that all data is actually written to the disk. This ensures that the database remains intact after restart even if the system crashes or the power is interrupted. Safe, but slow.

Normal: paused at most key points, but not frequently. A power outage may cause damage to the database. In practice, it may suffer from serious disk operation failures or other hardware faults that are not recovered.

Off: After throwing data to the OS, continue working immediately. Acceleration is about 50 times faster, but the problem is that if the application running SQLite crashes, the data remains secure. If the system crashes or becomes unavailable, data may be damaged.

D. Temporary Storage:

Stores temporary data (temporary tables, indexes, and other objects.

Two compilation instructions control temporary memory:

Temp_store: determines whether to use the memory or disk as the temporary storage; three options: default (use the default items for internal compilation), file (use the OS file), memory

(Memory usage );

Temp_store_directory: directory for storing temporary files, but when file is used as the storage medium.

E. Page size, encoding, and automatic cleanup:

It must be set before creating a database.

The database size can be automatically kept to a minimum using auto_vacuum. When auto_vacuum is started, the database decreases when the deleted transaction is committed. Vacuum does not work for databases that use auto_vacuum.

F. debugging:

Four debugging compilation instructions: integrity_check allows you to view records in reverse order, missing records, malformed records, and damaged indexes. If it is damaged, a string describing the problem is returned. If it is normal, OK is returned. Other compilation instructions track the parser and virtual database engine. These compilation instructions can be enabled as long as debugging information is enabled during database compilation.

     

System directory:

System Table: sqlite_master, which contains information about all tables, views, indexes, and triggers.

     

Rootpage refers to the location of the first B-tree page of the object in the database file.

    

View query plan:

Use explain query plan to view how SQLite performs a query. Add the query text after the command.

     

   

 

 

     ----------------------------《over》-------------------------------             


Note: It took 20 days, totally 21 articles. It will continue to be written later until it goes deep into the kernel and reads the source code. Finally, I learned how to use PHP + SQLite + ...... Construct a website. Source code is the current focus.