(sqlite) pragma statement

reference : https://www.sqlite.org/pragma.html

 

1. what is it?

 

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data.

 

2. some pragma statements that I want to know

 

* the word "schema" in pragma statement

 : schema is for an attached database or main or temp. If omitted, main is assumed.

 

PRAGMA schema.auto_vacuum
 : auto-vacuum status in the database.
 : default is "0" which is none
 : "1" or "full" -> freelist pages are moved to the end of the database file and truncated -> every transaction commit
 : "2" or "incremental" -> the separate incremental_vacuum pragma must be invoked to cause the auto-vacuum to occur.

 

PRAGMA schema.incremental_vacuum
 : incremental vacuum pragma causes up to N pages to be removed from the freelist.
 : the database file is truncated by the same amount.
 : this pragma has no effect if the database is not in "auto vacuume=incremental" mode, or if there are no pages on the freelist.
 : if there are fewer than N pages on the freelist, or if N is less than 1, or if the "(N)" argument is omitted, then the entire freelist is cleared.

 

PRAGMA schema.journal_mode
 : current journal_mode status of the database
 : The WAL journaling mode uses a write-ahead log.

PRAGMA schema.max_page_count
 : maximum number of pages in the database file

PRAGMA schema.page_count
 : the total number of pages in the database file.

PRAGMA schema.page_size
 : return the page size of the database.
 : the default pase size is increased from 1024 to 4096 since SQLite version 3.12.0(16.03.29.) and is recommended

PRAGMA pragma_list
 : a list of PRAGMA commands

PRAGMA schema.schema_version
 : get the value of the schema-version in the database header. (at offset 40)

PRAGMA schema.user_version
 : get the value of the user-version in the database header. (at offset 60)

PRAGMA schema.secure_delete
 : the secure-delete setting. When on, sqlite overwrites deleted content with zeroes.
 : this if usually off for performance reason(I/O)
 : fast is intermediate setting in between on and off -> secure delete when I/O would not be increased.

PRAGMA schema.table_info(table-name)
 : table info -> generated columns hidden columns are not shown.

PRAGMA schema.table_xinfo(table-name)
 : table info -> generated columns hidden columns are shown.

PRAGMA table_list
 : information about the table 
 : this is supported from SQLite version 3.37.0

PRAGMA wal_autocheckpoint
 : checkpoint interval. when the WAL mode is on, a checkpoint will be run automatically whenever the WAL equals or
exceed N pages in length. Setting the auto-checkpoint size to zero or a negative value turns auto-checkpointing off.
 : all automatic checkpoints are PASSIVE
 : setting the auto-checkpoint size to zero or a negative value turns auto-checkpointing off
 : checkpoint : Applications using WAL do not have to do anything in order to for these checkpoints to occur.
but if they want to, applications can adjust the automatic checkpoint threshold or they can turn off the automatic
checkpoints and run checkpoints during idle moments or in a separate thread or process.
 : by default, a checkpoint automatically occurs when WAL reaches a threshold size of 1000 pages.

PRAGMA wal_checkpoint
 : it causes checkpoint to occur