The following is a random assortment of techniques used by the SQLite developers to trace, examine, and understand the behavior of the core SQLite library.
These techniques are designed to aid in understanding the core SQLite library itself, not applications that merely use SQLite.
Use the ".eqp full" option on the command-line shell
When you have a SQL script that you are debugging or trying to understand, it is often useful to run it in the command-line shell with the ".eqp full" setting. When ".eqp" is set to FULL, the shell automatically shows the EXPLAIN and EXPLAIN QUERY PLAN output for each command prior to actually running that command.
For added readability, also set ".echo on" so that the output contains the original SQL text.
Use compile-time options to enable debugging features.
Suggested compile-time options include:
The SQLITE_ENABLE_SELECTTRACE and SQLITE_ENABLE_WHERETRACE options are not documented in compile-time options document because they are not officially supported. What they do is activate the ".selecttrace" and ".wheretrace" dot-commands in the command-line shell, which provide low-level tracing output for the logic that generates code for SELECT statements and WHERE clauses, respectively.
Call sqlite3TreeViewExpr() and similar from the debugger.
When compiled with SQLITE_DEBUG, SQLite includes routines that will print out various internal parse tree structures as ASCII-art graphs. This can be very useful in a debugging in order to understand the variables that SQLite is working with.
For example, in gdb, to see the complete hierarchy of an Expr node (that is to say, the Expr node and all of its children), given a pointer "pExpr" to that node, type:
The two "0" parameters do server a purpose in some contexts, but for using these routine to print a parse tree as ASCII-art on the terminal, they should both be "0".
Other similar tree-display routines include:
Breakpoints on test_addoptrace
When debugging the bytecode generator, it is often useful to know where a particular opcode is being generated. To find this easily, run the script in a debugger. Set a breakpoint on the "test_addoptrace" routine. Then run the "PRAGMA vdbe_addoptrace=ON;" followed by the SQL statement in question. Each opcode will be displayed as it is appended to the VDBE program, and the breakpoint will fire immediately thereafter. Step until reaching the opcode and question then look backwards in the stack to see where and how it was generated.
This only works when compiled with SQLITE_DEBUG.
Using the ".selecttrace" and ".wheretrace" shell commands
When the command-line shell and the core SQLite library are both compiled with SQLITE_DEBUG and SQLITE_ENABLE_SELECTTRACE and SQLITE_ENABLE_WHERETRACE, then the shell has two commands used to turn on debugging facilities for the most intricate parts of the code generator - the logic dealing with SELECT statements and WHERE clauses, respectively. The ".selecttrace" and ".wheretrace" commands each take a numeric argument which can be expressed in hexadecimal. Each bit turns on various parts of debugging. Values of "0xfff" and "0xff" are commonly used. Use an argument of "0" to turn all tracing output back off.
Disable the lookaside memory allocator
When looking for memory allocation problems (memory leaks, use-after-free errors, buffer overflows, etc) it is sometimes useful to disable the lookaside memory allocator then run the test under valgrind. The lookaside memory allocator can be disabled at start-time using the SQLITE_CONFIG_LOOKASIDE interface. The command-line shell will use that interface to disable lookaside if it is started with the "--lookaside 0 0" command line option.
SQLite is in the Public Domain.
https://sqlite.org/debugging.html