sqlite - 如何在SQL编译过程中找到哪些SQLite pragmas生效？

Question

根据the docs ,

Some pragmas take effect during the SQL compilation stage, not the execution stage. This means if using the C-language sqlite3_prepare(), sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper interface), the pragma may run during the sqlite3_prepare() call, not during the sqlite3_step() call as normal SQL statements do. Or the pragma might run during sqlite3_step() just like normal SQL statements. Whether or not the pragma runs during sqlite3_prepare() or sqlite3_step() depends on the pragma and on the specific release of SQLite.

特定编译指示的文档中未给出最后一条语句中的信息。假设我仍然有兴趣找到它，大概我必须查看来源。但具体在哪里呢？

Answer 1

所有 implemented by the underlying VFS 的编译指示解析时执行。(但是 VFS 中没有实现任何预定义的编译指示。)

否则，最简单的查找方法是 look在生成的 virtual machine instructions的杂注。有些编译指示在 VDBE 代码中不执行任何操作，这意味着它们在解析时完全执行(并且 EXPLAIN 不会阻止这种情况):

> EXPLAIN PRAGMA page_size = 16384;addr  opcode         p1    p2    p3    p4             p5  comment      ----  -------------  ----  ----  ----  -------------  --  -------------0     Init           0     1     0                    00  Start at 1   1     Halt           0     0     0                    00               

但请注意，未知的编译指示会被简单地忽略:

> EXPLAIN PRAGMA give_me_lots_of_money;addr  opcode         p1    p2    p3    p4             p5  comment      ----  -------------  ----  ----  ----  -------------  --  -------------0     Init           0     1     0                    00  Start at 1   1     Halt           0     0     0                    00               

许多仅返回值的编译指示会在解析时生成具有当前值的代码:

> EXPLAIN PRAGMA page_size;addr  opcode         p1    p2    p3    p4             p5  comment      ----  -------------  ----  ----  ----  -------------  --  -------------0     Init           0     1     0                    00  Start at 1   1     Int64          0     1     0     16384          00  r[1]=16384   2     ResultRow      1     1     0                    00  output=r[1]  3     Halt           0     0     0                    00               

但是有些编译指示在执行时会查找值:

> EXPLAIN PRAGMA user_version;addr  opcode         p1    p2    p3    p4             p5  comment      ----  -------------  ----  ----  ----  -------------  --  -------------0     Init           0     1     0                    00  Start at 1   1     Transaction    0     0     0                    00               2     ReadCookie     0     1     6                    00               3     ResultRow      1     1     0                    00  output=r[1]  4     Halt           0     0     0                    00               

一些编译指示可以用 VDBE 代码实现:

> EXPLAIN PRAGMA foreign_key_check;addr  opcode         p1    p2    p3    p4             p5  comment      ----  -------------  ----  ----  ----  -------------  --  -------------0     Init           0     15    0                    00  Start at 15  1     OpenRead       0     2     0     2              00  root=2 iDb=0; MyTable2     String8        0     3     0     MyTable        00  r[3]='MyTable'3     OpenRead       1     3     0     k(2,,)         00  root=3 iDb=0 4     Rewind         0     14    0                    00               5       Column         0     1     8                    00  r[8]=MyTable.Parent6       IsNull         8     13    0                    00  if r[8]==NULL goto 137       MakeRecord     8     1     7     A              00  r[7]=mkrec(r[8])8       Found          1     13    7     0              00  key=r[7]     9       Rowid          0     4     0                    00  r[4]=rowid   10      String8        0     5     0     MyTable        00  r[5]='MyTable'11      Integer        0     6     0                    00  r[6]=0       12      ResultRow      3     4     0                    00  output=r[3..6]13    Next           0     5     0                    00               14    Halt           0     0     0                    00               15    Transaction    0     0     3     0              01  usesStmtJournal=016    Goto           0     1     0                    00               

有些编译指示(例如，default_cache_size)在解析和执行时都会执行；这通常适用于记录在数据库文件中的值(此类写入只能在事务内完成)。

所以最安全的方法是查看src/pragma.c文件中的函数sqlite3Pragma。