841 lines
39 KiB
Markdown
841 lines
39 KiB
Markdown
## Summary
|
|
<!--Explain briefly what goes wrong and explain why you believe this is a bug and not the intended behavior of SQLite (if it is not a crash).-->**No review yet**
|
|
|
|
## Minimized query
|
|
|
|
```sql
|
|
|
|
.help
|
|
.help .archive
|
|
.help .auth
|
|
.help .backup
|
|
.help .bail
|
|
.help .cd
|
|
.help .changes
|
|
.help .check
|
|
.help .clone
|
|
.help .connection
|
|
.help .databases
|
|
.help .dbconfig
|
|
.help .dbinfo
|
|
.help .dump
|
|
.help .echo
|
|
.help .eqp
|
|
.help .excel
|
|
.help .exit
|
|
.help .expert
|
|
.help .explain
|
|
.help .filectrl
|
|
.help .fullschema
|
|
.help .headers
|
|
.help .help
|
|
.help .import
|
|
.help .imposter
|
|
.help .indexes
|
|
.help .limit
|
|
.help .lint
|
|
.help .load
|
|
.help .log
|
|
.help .mode
|
|
.help .nonce
|
|
.help .nullvalue
|
|
.help .once
|
|
.help .open
|
|
.help .output
|
|
.help .parameter
|
|
.help .print
|
|
.help .progress
|
|
.help .prompt
|
|
.help .quit
|
|
.help .read
|
|
.help .recover
|
|
.help .restore
|
|
.help .save
|
|
.help .scanstats
|
|
.help .schema
|
|
.help .separator
|
|
.help .sha3sum
|
|
.help .shell
|
|
.help .show
|
|
.help .stats
|
|
.help .system
|
|
.help .tables
|
|
.help .timeout
|
|
.help .timer
|
|
.help .trace
|
|
.help .version
|
|
.help .vfsinfo
|
|
.help .vfslist
|
|
.help .vfsname
|
|
.help .width
|
|
|
|
BEGIN DEFERRED TRANSACTION;
|
|
ATTACH DATABASE ':memory:' AS aux15;
|
|
.exit 0
|
|
BEGIN EXCLUSIVE;
|
|
((PRAGMA)) count_changes = NO;
|
|
PRAGMA defer_foreign_keys = TRUE;
|
|
PRAGMA wal_autocheckpoint;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL,
|
|
d REAL
|
|
);
|
|
INSERT INTO T VALUES ('' || ('{"a":1,"b":[1,2,3]}'),'a',40.5,-70.0), (2,'b',-10.25,20.5), (3,'c',9e999,-9e999);
|
|
SELECT b FROM T WHERE ABS(c) > 5 AND ABS(d) > 5;
|
|
PRAGMA encoding = 'UTF-16';
|
|
PRAGMA full_column_names = NO;
|
|
PRAGMA count_changes = ON;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (1, 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
PRAGMA checkpoint_fullsync = 9223372036854775806;
|
|
.progress
|
|
CREATE TABLE t1(a, b, c);
|
|
INSERT INTO t1 VALUES(1,'aaa','bbb');
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 VALUES(2,'ccc','ddd');
|
|
SELECT DISTINCT a AS x, b||c AS y FROM t1 WHERE NOT NOT (y IN ('aaabbb','xxx')) ;
|
|
SELECT DISTINCT a AS x, b||c AS y FROM t1 WHERE +y='aaabbb';
|
|
DROP TRIGGER IF EXISTS t1;
|
|
ANALYZE;
|
|
ALTER TABLE t1 RENAME COLUMN c TO c_r9068;
|
|
PRAGMA writable_schema = OFF;
|
|
SELECT AVG(b) FROM t1;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
CREATE VIEW IF NOT EXISTS v_t1_9739 AS SELECT b FROM t1;
|
|
ALTER TABLE t1 RENAME COLUMN c TO c_r2779;
|
|
PRAGMA count_changes = NO;
|
|
PRAGMA defer_foreign_keys = TRUE;
|
|
PRAGMA wal_autocheckpoint;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL,
|
|
d REAL
|
|
);
|
|
INSERT INTO T VALUES ('' || ('{"a":1,"b":[1,2,3]}'),'a',40.5,-70.0), (2,'b',-10.25,20.5), (3,'c',9e999,-9e999);
|
|
SELECT b FROM T WHERE ABS(c) > 5 AND ABS(d) > 5;
|
|
PRAGMA encoding = 'UTF-16';
|
|
PRAGMA full_column_names = NO;
|
|
PRAGMA count_changes = ON;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (1, 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
PRAGMA checkpoint_fullsync = 9223372036854775806;
|
|
.progress
|
|
CREATE TABLE t1(a, b, c);
|
|
INSERT INTO t1 VALUES(1,'aaa','bbb');
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 VALUES(2,'ccc','ddd');
|
|
SELECT DISTINCT a AS x, b||c AS y FROM t1 WHERE NOT NOT (y IN ('aaabbb','xxx')) ;
|
|
SELECT DISTINCT a AS x, b||c AS y FROM t1 WHERE +y='aaabbb';
|
|
DROP TRIGGER IF EXISTS t1;
|
|
ANALYZE;
|
|
ALTER TABLE t1 RENAME COLUMN c TO c_r9068;
|
|
PRAGMA writable_schema = OFF;
|
|
SELECT AVG(b) FROM t1;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
CREATE VIEW IF NOT EXISTS v_t1_9739 AS SELECT b FROM t1;
|
|
ALTER TABLE t1 RENAME COLUMN c TO c_r2779;
|
|
PRAGMA count_changes = NO;
|
|
PRAGMA defer_foreign_keys = TRUE;
|
|
PRAGMA wal_autocheckpoint;
|
|
CREATE TABLE T (
|
|
a INTEGER,
|
|
b TEXT,
|
|
c REAL,
|
|
d REAL
|
|
);
|
|
INSERT INTO T VALUES ('' || ('{"a":1,"b":[1,2,3]}'),'a',40.5,-70.0), (2,'b',-10.25,20.5), (3,'c',9e999,-9e999);
|
|
SELECT b FROM T WHERE ABS(c) > 5 AND ABS(d) > 5;
|
|
PRAGMA encoding = 'UTF-16';
|
|
PRAGMA full_column_names = NO;
|
|
PRAGMA count_changes = ON;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c INT, d INT);
|
|
INSERT INTO t1 VALUES (1, 'Wernher', 10, 100);
|
|
INSERT INTO t1 VALUES (2, 'von', 20, 200);
|
|
INSERT INTO t1 VALUES (3, 'Braun', 30, 300);
|
|
|
|
CREATE INDEX t1bc ON t1(b, c);
|
|
|
|
PRAGMA writable_schema = ON;
|
|
|
|
.imposter t1bc t2
|
|
|
|
SELECT * FROM t2;
|
|
|
|
SELECT b, c FROM t1 ORDER BY b, c;
|
|
|
|
.quit
|
|
PRAGMA checkpoint_fullsync = 9223372036854775806;
|
|
.progress
|
|
CREATE TABLE t1(a, b, c);
|
|
INSERT INTO t1 VALUES(1,'aaa','bbb');
|
|
INSERT INTO t1 SELECT * FROM t1;
|
|
INSERT INTO t1 VALUES(2,'ccc','ddd');
|
|
SELECT DISTINCT a AS x, b||c AS y FROM t1 WHERE NOT NOT (y IN ('aaabbb','xxx')) ;
|
|
SELECT DISTINCT a AS x, b||c AS y FROM t1 WHERE +y='aaabbb';
|
|
DROP TRIGGER IF EXISTS t1;
|
|
ANALYZE;
|
|
ALTER TABLE t1 RENAME COLUMN c TO c_r9068;
|
|
PRAGMA writable_schema = OFF;
|
|
SELECT AVG(b) FROM t1;
|
|
ALTER TABLE t1 DROP COLUMN a;
|
|
CREATE VIEW IF NOT EXISTS v_t1_9739 AS SELECT b FROM t1;
|
|
ALTER TABLE t1 RENAME COLUMN c TO c_r2779;
|
|
|
|
COMMIT TRANSACTION;
|
|
DELETE FROM T WHERE d IS NULL RETURNING *;
|
|
INSERT INTO T DEFAULT VALUES;
|
|
INSERT OR ROLLBACK INTO T VALUES (0, NULL, '', '');
|
|
SELECT LAG(a, 2) OVER (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW) FROM t1;
|
|
SELECT * FROM T AS a JOIN t1 AS b ON a.rowid = b.rowid;
|
|
DETACH DATABASE aux15;
|
|
COMMIT;
|
|
```
|
|
|
|
## Actual output
|
|
|
|
```sql
|
|
.auth ON|OFF Show authorizer callbacks
|
|
.backup ?DB? FILE Backup DB (default "main") to FILE
|
|
.bail on|off Stop after hitting an error. Default OFF
|
|
.binary on|off Turn binary output on or off. Default OFF
|
|
.cd DIRECTORY Change the working directory to DIRECTORY
|
|
.changes on|off Show number of rows changed by SQL
|
|
.check GLOB Fail if output since .testcase does not match
|
|
.clone NEWDB Clone data into NEWDB from the existing database
|
|
.connection [close] [#] Open or close an auxiliary database connection
|
|
.databases List names and files of attached databases
|
|
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
|
|
.dbinfo ?DB? Show status information about the database
|
|
.dump ?OBJECTS? Render database content as SQL
|
|
.echo on|off Turn command echo on or off
|
|
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
|
|
.excel Display the output of next command in spreadsheet
|
|
.exit ?CODE? Exit this program with return-code CODE
|
|
.expert EXPERIMENTAL. Suggest indexes for queries
|
|
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
|
|
.filectrl CMD ... Run various sqlite3_file_control() operations
|
|
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
|
|
.headers on|off Turn display of headers on or off
|
|
.help ?-all? ?PATTERN? Show help text for PATTERN
|
|
.import FILE TABLE Import data from FILE into TABLE
|
|
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
|
|
.indexes ?TABLE? Show names of indexes
|
|
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
|
|
.lint OPTIONS Report potential schema issues.
|
|
.load FILE ?ENTRY? Load an extension library
|
|
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
|
|
.mode MODE ?OPTIONS? Set output mode
|
|
.nonce STRING Suspend safe mode for one command if nonce matches
|
|
.nullvalue STRING Use STRING in place of NULL values
|
|
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
|
|
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
|
|
.output ?FILE? Send output to FILE or stdout if FILE is omitted
|
|
.parameter CMD ... Manage SQL parameter bindings
|
|
.print STRING... Print literal STRING
|
|
.progress N Invoke progress handler after every N opcodes
|
|
.prompt MAIN CONTINUE Replace the standard prompts
|
|
.quit Exit this program
|
|
.read FILE Read input from FILE or command output
|
|
.recover Recover as much data as possible from corrupt db.
|
|
.restore ?DB? FILE Restore content of DB (default "main") from FILE
|
|
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
|
|
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
|
|
.schema ?PATTERN? Show the CREATE statements matching PATTERN
|
|
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
|
|
.separator COL ?ROW? Change the column and row separators
|
|
.sha3sum ... Compute a SHA3 hash of database content
|
|
.shell CMD ARGS... Run CMD ARGS... in a system shell
|
|
.show Show the current values for various settings
|
|
.stats ?ARG? Show stats or turn stats on or off
|
|
.system CMD ARGS... Run CMD ARGS... in a system shell
|
|
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
|
|
.testcase NAME Begin redirecting output to 'testcase-out.txt'
|
|
.testctrl CMD ... Run various sqlite3_test_control() operations
|
|
.timeout MS Try opening locked tables for MS milliseconds
|
|
.timer on|off Turn SQL timer on or off
|
|
.trace ?OPTIONS? Output each SQL statement as it is run
|
|
.vfsinfo ?AUX? Information about the top-level VFS
|
|
.vfslist List all available VFSes
|
|
.vfsname ?AUX? Print the name of the VFS stack
|
|
.width NUM1 NUM2 ... Set minimum column widths for columnar output
|
|
Nothing matches '.archive'
|
|
.auth ON|OFF Show authorizer callbacks
|
|
.backup ?DB? FILE Backup DB (default "main") to FILE
|
|
Options:
|
|
--append Use the appendvfs
|
|
--async Write to FILE without journal and fsync()
|
|
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
|
|
.bail on|off Stop after hitting an error. Default OFF
|
|
.cd DIRECTORY Change the working directory to DIRECTORY
|
|
.changes on|off Show number of rows changed by SQL
|
|
.check GLOB Fail if output since .testcase does not match
|
|
.clone NEWDB Clone data into NEWDB from the existing database
|
|
.connection [close] [#] Open or close an auxiliary database connection
|
|
.databases List names and files of attached databases
|
|
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
|
|
.dbinfo ?DB? Show status information about the database
|
|
.dump ?OBJECTS? Render database content as SQL
|
|
Options:
|
|
--data-only Output only INSERT statements
|
|
--newlines Allow unescaped newline characters in output
|
|
--nosys Omit system tables (ex: "sqlite_stat1")
|
|
--preserve-rowids Include ROWID values in the output
|
|
OBJECTS is a LIKE pattern for tables, indexes, triggers or views to dump
|
|
Additional LIKE patterns can be given in subsequent arguments
|
|
.echo on|off Turn command echo on or off
|
|
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
|
|
Other Modes:
|
|
trigger Like "full" but also show trigger bytecode
|
|
.excel Display the output of next command in spreadsheet
|
|
--bom Put a UTF8 byte-order mark on intermediate file
|
|
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
|
|
If FILE begins with '|' then open as a pipe
|
|
--bom Put a UTF8 byte-order mark at the beginning
|
|
-e Send output to the system text editor
|
|
-x Send output as CSV to a spreadsheet (same as ".excel")
|
|
.exit ?CODE? Exit this program with return-code CODE
|
|
.expert EXPERIMENTAL. Suggest indexes for queries
|
|
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
|
|
.filectrl CMD ... Run various sqlite3_file_control() operations
|
|
--schema SCHEMA Use SCHEMA instead of "main"
|
|
--help Show CMD details
|
|
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
|
|
.headers on|off Turn display of headers on or off
|
|
.help ?-all? ?PATTERN? Show help text for PATTERN
|
|
.import FILE TABLE Import data from FILE into TABLE
|
|
Options:
|
|
--ascii Use \037 and \036 as column and row separators
|
|
--csv Use , and \n as column and row separators
|
|
--skip N Skip the first N rows of input
|
|
--schema S Target table to be S.TABLE
|
|
-v "Verbose" - increase auxiliary output
|
|
Notes:
|
|
* If TABLE does not exist, it is created. The first row of input
|
|
determines the column names.
|
|
* If neither --csv or --ascii are used, the input mode is derived
|
|
from the ".mode" output mode
|
|
* If FILE begins with "|" then it is a command that generates the
|
|
input text.
|
|
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
|
|
.indexes ?TABLE? Show names of indexes
|
|
If TABLE is specified, only show indexes for
|
|
tables matching TABLE using the LIKE operator.
|
|
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
|
|
.lint OPTIONS Report potential schema issues.
|
|
Options:
|
|
fkey-indexes Find missing foreign key indexes
|
|
.load FILE ?ENTRY? Load an extension library
|
|
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
|
|
.import FILE TABLE Import data from FILE into TABLE
|
|
Options:
|
|
--ascii Use \037 and \036 as column and row separators
|
|
--csv Use , and \n as column and row separators
|
|
--skip N Skip the first N rows of input
|
|
--schema S Target table to be S.TABLE
|
|
-v "Verbose" - increase auxiliary output
|
|
Notes:
|
|
* If TABLE does not exist, it is created. The first row of input
|
|
determines the column names.
|
|
* If neither --csv or --ascii are used, the input mode is derived
|
|
from the ".mode" output mode
|
|
* If FILE begins with "|" then it is a command that generates the
|
|
input text.
|
|
.mode MODE ?OPTIONS? Set output mode
|
|
MODE is one of:
|
|
ascii Columns/rows delimited by 0x1F and 0x1E
|
|
box Tables using unicode box-drawing characters
|
|
csv Comma-separated values
|
|
column Output in columns. (See .width)
|
|
html HTML <table> code
|
|
insert SQL insert statements for TABLE
|
|
json Results in a JSON array
|
|
line One value per line
|
|
list Values delimited by "|"
|
|
markdown Markdown table format
|
|
qbox Shorthand for "box --width 60 --quote"
|
|
quote Escape answers as for SQL
|
|
table ASCII-art table
|
|
tabs Tab-separated values
|
|
tcl TCL list elements
|
|
OPTIONS: (for columnar modes or insert mode):
|
|
--wrap N Wrap output lines to no longer than N characters
|
|
--wordwrap B Wrap or not at word boundaries per B (on/off)
|
|
--ww Shorthand for "--wordwrap 1"
|
|
--quote Quote output text as SQL literals
|
|
--noquote Do not quote output text
|
|
TABLE The name of SQL table used for "insert" mode
|
|
.nonce STRING Suspend safe mode for one command if nonce matches
|
|
.nullvalue STRING Use STRING in place of NULL values
|
|
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
|
|
If FILE begins with '|' then open as a pipe
|
|
--bom Put a UTF8 byte-order mark at the beginning
|
|
-e Send output to the system text editor
|
|
-x Send output as CSV to a spreadsheet (same as ".excel")
|
|
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
|
|
Options:
|
|
--append Use appendvfs to append database to the end of FILE
|
|
--deserialize Load into memory using sqlite3_deserialize()
|
|
--hexdb Load the output of "dbtotxt" as an in-memory db
|
|
--maxsize N Maximum size for --hexdb or --deserialized database
|
|
--new Initialize FILE to an empty database
|
|
--nofollow Do not follow symbolic links
|
|
--readonly Open FILE readonly
|
|
--zip FILE is a ZIP archive
|
|
.output ?FILE? Send output to FILE or stdout if FILE is omitted
|
|
If FILE begins with '|' then open it as a pipe.
|
|
Options:
|
|
--bom Prefix output with a UTF8 byte-order mark
|
|
-e Send output to the system text editor
|
|
-x Send output as CSV to a spreadsheet
|
|
.parameter CMD ... Manage SQL parameter bindings
|
|
clear Erase all bindings
|
|
init Initialize the TEMP table that holds bindings
|
|
list List the current parameter bindings
|
|
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
|
|
PARAMETER should start with one of: $ : @ ?
|
|
unset PARAMETER Remove PARAMETER from the binding table
|
|
.print STRING... Print literal STRING
|
|
.progress N Invoke progress handler after every N opcodes
|
|
--limit N Interrupt after N progress callbacks
|
|
--once Do no more than one progress interrupt
|
|
--quiet|-q No output except at interrupts
|
|
--reset Reset the count for each input and interrupt
|
|
.prompt MAIN CONTINUE Replace the standard prompts
|
|
.quit Exit this program
|
|
.read FILE Read input from FILE or command output
|
|
If FILE begins with "|", it is a command that generates the input.
|
|
.recover Recover as much data as possible from corrupt db.
|
|
--freelist-corrupt Assume the freelist is corrupt
|
|
--recovery-db NAME Store recovery metadata in database file NAME
|
|
--lost-and-found TABLE Alternative name for the lost-and-found table
|
|
--no-rowids Do not attempt to recover rowid values
|
|
that are not also INTEGER PRIMARY KEYs
|
|
.restore ?DB? FILE Restore content of DB (default "main") from FILE
|
|
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
|
|
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
|
|
.schema ?PATTERN? Show the CREATE statements matching PATTERN
|
|
Options:
|
|
--indent Try to pretty-print the schema
|
|
--nosys Omit objects whose names start with "sqlite_"
|
|
.separator COL ?ROW? Change the column and row separators
|
|
.sha3sum ... Compute a SHA3 hash of database content
|
|
Options:
|
|
--schema Also hash the sqlite_schema table
|
|
--sha3-224 Use the sha3-224 algorithm
|
|
--sha3-256 Use the sha3-256 algorithm (default)
|
|
--sha3-384 Use the sha3-384 algorithm
|
|
--sha3-512 Use the sha3-512 algorithm
|
|
Any other argument is a LIKE pattern for tables to hash
|
|
.shell CMD ARGS... Run CMD ARGS... in a system shell
|
|
.show Show the current values for various settings
|
|
.stats ?ARG? Show stats or turn stats on or off
|
|
off Turn off automatic stat display
|
|
on Turn on automatic stat display
|
|
stmt Show statement stats
|
|
vmstep Show the virtual machine step count only
|
|
.system CMD ARGS... Run CMD ARGS... in a system shell
|
|
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
|
|
.timeout MS Try opening locked tables for MS milliseconds
|
|
.timer on|off Turn SQL timer on or off
|
|
.trace ?OPTIONS? Output each SQL statement as it is run
|
|
FILE Send output to FILE
|
|
stdout Send output to stdout
|
|
stderr Send output to stderr
|
|
off Disable tracing
|
|
--expanded Expand query parameters
|
|
--plain Show SQL as it is input
|
|
--stmt Trace statement execution (SQLITE_TRACE_STMT)
|
|
--profile Profile statements (SQLITE_TRACE_PROFILE)
|
|
--row Trace each row (SQLITE_TRACE_ROW)
|
|
--close Trace connection close (SQLITE_TRACE_CLOSE)
|
|
Nothing matches '.version'
|
|
.vfsinfo ?AUX? Information about the top-level VFS
|
|
.vfslist List all available VFSes
|
|
.vfsname ?AUX? Print the name of the VFS stack
|
|
.mode MODE ?OPTIONS? Set output mode
|
|
MODE is one of:
|
|
ascii Columns/rows delimited by 0x1F and 0x1E
|
|
box Tables using unicode box-drawing characters
|
|
csv Comma-separated values
|
|
column Output in columns. (See .width)
|
|
html HTML <table> code
|
|
insert SQL insert statements for TABLE
|
|
json Results in a JSON array
|
|
line One value per line
|
|
list Values delimited by "|"
|
|
markdown Markdown table format
|
|
qbox Shorthand for "box --width 60 --quote"
|
|
quote Escape answers as for SQL
|
|
table ASCII-art table
|
|
tabs Tab-separated values
|
|
tcl TCL list elements
|
|
OPTIONS: (for columnar modes or insert mode):
|
|
--wrap N Wrap output lines to no longer than N characters
|
|
--wordwrap B Wrap or not at word boundaries per B (on/off)
|
|
--ww Shorthand for "--wordwrap 1"
|
|
--quote Quote output text as SQL literals
|
|
--noquote Do not quote output text
|
|
TABLE The name of SQL table used for "insert" mode
|
|
.width NUM1 NUM2 ... Set minimum column widths for columnar output
|
|
Negative values right-justify
|
|
```
|
|
|
|
## Expectation
|
|
|
|
```sql
|
|
.archive ... Manage SQL archives
|
|
.auth ON|OFF Show authorizer callbacks
|
|
.backup ?DB? FILE Backup DB (default "main") to FILE
|
|
.bail on|off Stop after hitting an error. Default OFF
|
|
.cd DIRECTORY Change the working directory to DIRECTORY
|
|
.changes on|off Show number of rows changed by SQL
|
|
.check GLOB Fail if output since .testcase does not match
|
|
.clone NEWDB Clone data into NEWDB from the existing database
|
|
.connection [close] [#] Open or close an auxiliary database connection
|
|
.crlf ?on|off? Whether or not to use \r\n line endings
|
|
.databases List names and files of attached databases
|
|
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
|
|
.dbinfo ?DB? Show status information about the database
|
|
.dbtotxt Hex dump of the database file
|
|
.dump ?OBJECTS? Render database content as SQL
|
|
.echo on|off Turn command echo on or off
|
|
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
|
|
.excel Display the output of next command in spreadsheet
|
|
.exit ?CODE? Exit this program with return-code CODE
|
|
.expert EXPERIMENTAL. Suggest indexes for queries
|
|
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
|
|
.filectrl CMD ... Run various sqlite3_file_control() operations
|
|
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
|
|
.headers on|off Turn display of headers on or off
|
|
.help ?-all? ?PATTERN? Show help text for PATTERN
|
|
.import FILE TABLE Import data from FILE into TABLE
|
|
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
|
|
.indexes ?TABLE? Show names of indexes
|
|
.intck ?STEPS_PER_UNLOCK? Run an incremental integrity check on the db
|
|
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
|
|
.lint OPTIONS Report potential schema issues.
|
|
.load FILE ?ENTRY? Load an extension library
|
|
.log FILE|on|off Turn logging on or off. FILE can be stderr/stdout
|
|
.mode ?MODE? ?OPTIONS? Set output mode
|
|
.nonce STRING Suspend safe mode for one command if nonce matches
|
|
.nullvalue STRING Use STRING in place of NULL values
|
|
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
|
|
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
|
|
.output ?FILE? Send output to FILE or stdout if FILE is omitted
|
|
.parameter CMD ... Manage SQL parameter bindings
|
|
.print STRING... Print literal STRING
|
|
.progress N Invoke progress handler after every N opcodes
|
|
.prompt MAIN CONTINUE Replace the standard prompts
|
|
.quit Stop interpreting input stream, exit if primary.
|
|
.read FILE Read input from FILE or command output
|
|
.recover Recover as much data as possible from corrupt db.
|
|
.restore ?DB? FILE Restore content of DB (default "main") from FILE
|
|
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
|
|
.scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off
|
|
.schema ?PATTERN? Show the CREATE statements matching PATTERN
|
|
.separator COL ?ROW? Change the column and row separators
|
|
.sha3sum ... Compute a SHA3 hash of database content
|
|
.shell CMD ARGS... Run CMD ARGS... in a system shell
|
|
.show Show the current values for various settings
|
|
.stats ?ARG? Show stats or turn stats on or off
|
|
.system CMD ARGS... Run CMD ARGS... in a system shell
|
|
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
|
|
.timeout MS Try opening locked tables for MS milliseconds
|
|
.timer on|off Turn SQL timer on or off
|
|
.trace ?OPTIONS? Output each SQL statement as it is run
|
|
.version Show source, library and compiler versions
|
|
.vfsinfo ?AUX? Information about the top-level VFS
|
|
.vfslist List all available VFSes
|
|
.vfsname ?AUX? Print the name of the VFS stack
|
|
.width NUM1 NUM2 ... Set minimum column widths for columnar output
|
|
.www Display output of the next command in web browser
|
|
.archive ... Manage SQL archives
|
|
Each command must have exactly one of the following options:
|
|
-c, --create Create a new archive
|
|
-u, --update Add or update files with changed mtime
|
|
-i, --insert Like -u but always add even if unchanged
|
|
-r, --remove Remove files from archive
|
|
-t, --list List contents of archive
|
|
-x, --extract Extract files from archive
|
|
Optional arguments:
|
|
-v, --verbose Print each filename as it is processed
|
|
-f FILE, --file FILE Use archive FILE (default is current db)
|
|
-a FILE, --append FILE Open FILE using the apndvfs VFS
|
|
-C DIR, --directory DIR Read/extract files from directory DIR
|
|
-g, --glob Use glob matching for names in archive
|
|
-n, --dryrun Show the SQL that would have occurred
|
|
Examples:
|
|
.ar -cf ARCHIVE foo bar # Create ARCHIVE from files foo and bar
|
|
.ar -tf ARCHIVE # List members of ARCHIVE
|
|
.ar -xvf ARCHIVE # Verbosely extract files from ARCHIVE
|
|
See also:
|
|
http://sqlite.org/cli.html#sqlite_archive_support
|
|
.auth ON|OFF Show authorizer callbacks
|
|
.backup ?DB? FILE Backup DB (default "main") to FILE
|
|
Options:
|
|
--append Use the appendvfs
|
|
--async Write to FILE without journal and fsync()
|
|
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
|
|
.bail on|off Stop after hitting an error. Default OFF
|
|
.cd DIRECTORY Change the working directory to DIRECTORY
|
|
.changes on|off Show number of rows changed by SQL
|
|
.check GLOB Fail if output since .testcase does not match
|
|
.clone NEWDB Clone data into NEWDB from the existing database
|
|
.connection [close] [#] Open or close an auxiliary database connection
|
|
.databases List names and files of attached databases
|
|
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
|
|
.dbinfo ?DB? Show status information about the database
|
|
.dump ?OBJECTS? Render database content as SQL
|
|
Options:
|
|
--data-only Output only INSERT statements
|
|
--newlines Allow unescaped newline characters in output
|
|
--nosys Omit system tables (ex: "sqlite_stat1")
|
|
--preserve-rowids Include ROWID values in the output
|
|
OBJECTS is a LIKE pattern for tables, indexes, triggers or views to dump
|
|
Additional LIKE patterns can be given in subsequent arguments
|
|
.echo on|off Turn command echo on or off
|
|
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
|
|
Other Modes:
|
|
trigger Like "full" but also show trigger bytecode
|
|
.excel Display the output of next command in spreadsheet
|
|
--bom Put a UTF8 byte-order mark on intermediate file
|
|
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
|
|
If FILE begins with '|' then open as a pipe
|
|
--bom Put a UTF8 byte-order mark at the beginning
|
|
-e Send output to the system text editor
|
|
--plain Use text/plain output instead of HTML for -w option
|
|
-w Send output as HTML to a web browser (same as ".www")
|
|
-x Send output as CSV to a spreadsheet (same as ".excel")
|
|
.exit ?CODE? Exit this program with return-code CODE
|
|
.expert EXPERIMENTAL. Suggest indexes for queries
|
|
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
|
|
.filectrl CMD ... Run various sqlite3_file_control() operations
|
|
--schema SCHEMA Use SCHEMA instead of "main"
|
|
--help Show CMD details
|
|
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
|
|
.headers on|off Turn display of headers on or off
|
|
.help ?-all? ?PATTERN? Show help text for PATTERN
|
|
.import FILE TABLE Import data from FILE into TABLE
|
|
Options:
|
|
--ascii Use \037 and \036 as column and row separators
|
|
--csv Use , and \n as column and row separators
|
|
--skip N Skip the first N rows of input
|
|
--schema S Target table to be S.TABLE
|
|
-v "Verbose" - increase auxiliary output
|
|
Notes:
|
|
* If TABLE does not exist, it is created. The first row of input
|
|
determines the column names.
|
|
* If neither --csv or --ascii are used, the input mode is derived
|
|
from the ".mode" output mode
|
|
* If FILE begins with "|" then it is a command that generates the
|
|
input text.
|
|
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
|
|
.indexes ?TABLE? Show names of indexes
|
|
If TABLE is specified, only show indexes for
|
|
tables matching TABLE using the LIKE operator.
|
|
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
|
|
.lint OPTIONS Report potential schema issues.
|
|
Options:
|
|
fkey-indexes Find missing foreign key indexes
|
|
.load FILE ?ENTRY? Load an extension library
|
|
.log FILE|on|off Turn logging on or off. FILE can be stderr/stdout
|
|
.import FILE TABLE Import data from FILE into TABLE
|
|
Options:
|
|
--ascii Use \037 and \036 as column and row separators
|
|
--csv Use , and \n as column and row separators
|
|
--skip N Skip the first N rows of input
|
|
--schema S Target table to be S.TABLE
|
|
-v "Verbose" - increase auxiliary output
|
|
Notes:
|
|
* If TABLE does not exist, it is created. The first row of input
|
|
determines the column names.
|
|
* If neither --csv or --ascii are used, the input mode is derived
|
|
from the ".mode" output mode
|
|
* If FILE begins with "|" then it is a command that generates the
|
|
input text.
|
|
.mode ?MODE? ?OPTIONS? Set output mode
|
|
MODE is one of:
|
|
ascii Columns/rows delimited by 0x1F and 0x1E
|
|
box Tables using unicode box-drawing characters
|
|
csv Comma-separated values
|
|
column Output in columns. (See .width)
|
|
html HTML <table> code
|
|
insert SQL insert statements for TABLE
|
|
json Results in a JSON array
|
|
line One value per line
|
|
list Values delimited by "|"
|
|
markdown Markdown table format
|
|
qbox Shorthand for "box --wrap 60 --quote"
|
|
quote Escape answers as for SQL
|
|
table ASCII-art table
|
|
tabs Tab-separated values
|
|
tcl TCL list elements
|
|
OPTIONS: (for columnar modes or insert mode):
|
|
--escape T ctrl-char escape; T is one of: symbol, ascii, off
|
|
--wrap N Wrap output lines to no longer than N characters
|
|
--wordwrap B Wrap or not at word boundaries per B (on/off)
|
|
--ww Shorthand for "--wordwrap 1"
|
|
--quote Quote output text as SQL literals
|
|
--noquote Do not quote output text
|
|
TABLE The name of SQL table used for "insert" mode
|
|
.nonce STRING Suspend safe mode for one command if nonce matches
|
|
.nullvalue STRING Use STRING in place of NULL values
|
|
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
|
|
If FILE begins with '|' then open as a pipe
|
|
--bom Put a UTF8 byte-order mark at the beginning
|
|
-e Send output to the system text editor
|
|
--plain Use text/plain output instead of HTML for -w option
|
|
-w Send output as HTML to a web browser (same as ".www")
|
|
-x Send output as CSV to a spreadsheet (same as ".excel")
|
|
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
|
|
Options:
|
|
--append Use appendvfs to append database to the end of FILE
|
|
--deserialize Load into memory using sqlite3_deserialize()
|
|
--hexdb Load the output of "dbtotxt" as an in-memory db
|
|
--ifexist Only open if FILE already exists
|
|
--maxsize N Maximum size for --hexdb or --deserialized database
|
|
--new Initialize FILE to an empty database
|
|
--normal FILE is an ordinary SQLite database
|
|
--nofollow Do not follow symbolic links
|
|
--readonly Open FILE readonly
|
|
--zip FILE is a ZIP archive
|
|
.output ?FILE? Send output to FILE or stdout if FILE is omitted
|
|
If FILE begins with '|' then open it as a pipe.
|
|
If FILE is 'off' then output is disabled.
|
|
Options:
|
|
--bom Prefix output with a UTF8 byte-order mark
|
|
-e Send output to the system text editor
|
|
--plain Use text/plain for -w option
|
|
-w Send output to a web browser
|
|
-x Send output as CSV to a spreadsheet
|
|
.parameter CMD ... Manage SQL parameter bindings
|
|
clear Erase all bindings
|
|
init Initialize the TEMP table that holds bindings
|
|
list List the current parameter bindings
|
|
set PARAMETER VALUE Given SQL parameter PARAMETER a value of VALUE
|
|
PARAMETER should start with one of: $ : @ ?
|
|
unset PARAMETER Remove PARAMETER from the binding table
|
|
.print STRING... Print literal STRING
|
|
.progress N Invoke progress handler after every N opcodes
|
|
--limit N Interrupt after N progress callbacks
|
|
--once Do no more than one progress interrupt
|
|
--quiet|-q No output except at interrupts
|
|
--reset Reset the count for each input and interrupt
|
|
.prompt MAIN CONTINUE Replace the standard prompts
|
|
.quit Stop interpreting input stream, exit if primary.
|
|
.read FILE Read input from FILE or command output
|
|
If FILE begins with "|", it is a command that generates the input.
|
|
.recover Recover as much data as possible from corrupt db.
|
|
--ignore-freelist Ignore pages that appear to be on db freelist
|
|
--lost-and-found TABLE Alternative name for the lost-and-found table
|
|
--no-rowids Do not attempt to recover rowid values
|
|
that are not also INTEGER PRIMARY KEYs
|
|
.restore ?DB? FILE Restore content of DB (default "main") from FILE
|
|
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
|
|
.scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off
|
|
.schema ?PATTERN? Show the CREATE statements matching PATTERN
|
|
Options:
|
|
--indent Try to pretty-print the schema
|
|
--nosys Omit objects whose names start with "sqlite_"
|
|
.separator COL ?ROW? Change the column and row separators
|
|
.sha3sum ... Compute a SHA3 hash of database content
|
|
Options:
|
|
--schema Also hash the sqlite_schema table
|
|
--sha3-224 Use the sha3-224 algorithm
|
|
--sha3-256 Use the sha3-256 algorithm (default)
|
|
--sha3-384 Use the sha3-384 algorithm
|
|
--sha3-512 Use the sha3-512 algorithm
|
|
Any other argument is a LIKE pattern for tables to hash
|
|
.shell CMD ARGS... Run CMD ARGS... in a system shell
|
|
.show Show the current values for various settings
|
|
.stats ?ARG? Show stats or turn stats on or off
|
|
off Turn off automatic stat display
|
|
on Turn on automatic stat display
|
|
stmt Show statement stats
|
|
vmstep Show the virtual machine step count only
|
|
.system CMD ARGS... Run CMD ARGS... in a system shell
|
|
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
|
|
.timeout MS Try opening locked tables for MS milliseconds
|
|
.timer on|off Turn SQL timer on or off
|
|
.trace ?OPTIONS? Output each SQL statement as it is run
|
|
FILE Send output to FILE
|
|
stdout Send output to stdout
|
|
stderr Send output to stderr
|
|
off Disable tracing
|
|
--expanded Expand query parameters
|
|
--plain Show SQL as it is input
|
|
--stmt Trace statement execution (SQLITE_TRACE_STMT)
|
|
--profile Profile statements (SQLITE_TRACE_PROFILE)
|
|
--row Trace each row (SQLITE_TRACE_ROW)
|
|
--close Trace connection close (SQLITE_TRACE_CLOSE)
|
|
.version Show source, library and compiler versions
|
|
.vfsinfo ?AUX? Information about the top-level VFS
|
|
.vfslist List all available VFSes
|
|
.vfsname ?AUX? Print the name of the VFS stack
|
|
.mode ?MODE? ?OPTIONS? Set output mode
|
|
MODE is one of:
|
|
ascii Columns/rows delimited by 0x1F and 0x1E
|
|
box Tables using unicode box-drawing characters
|
|
csv Comma-separated values
|
|
column Output in columns. (See .width)
|
|
html HTML <table> code
|
|
insert SQL insert statements for TABLE
|
|
json Results in a JSON array
|
|
line One value per line
|
|
list Values delimited by "|"
|
|
markdown Markdown table format
|
|
qbox Shorthand for "box --wrap 60 --quote"
|
|
quote Escape answers as for SQL
|
|
table ASCII-art table
|
|
tabs Tab-separated values
|
|
tcl TCL list elements
|
|
OPTIONS: (for columnar modes or insert mode):
|
|
--escape T ctrl-char escape; T is one of: symbol, ascii, off
|
|
--wrap N Wrap output lines to no longer than N characters
|
|
--wordwrap B Wrap or not at word boundaries per B (on/off)
|
|
--ww Shorthand for "--wordwrap 1"
|
|
--quote Quote output text as SQL literals
|
|
--noquote Do not quote output text
|
|
TABLE The name of SQL table used for "insert" mode
|
|
.width NUM1 NUM2 ... Set minimum column widths for columnar output
|
|
Negative values right-justify
|
|
```
|
|
|
|
## Flag
|
|
|
|
```
|
|
|
|
```
|
|
|