Title: Theory / Unimportant / CS / Programming / Sqlite / Command line
Tags: comment

Instruction: sqlite3
Page: https://www.sqlite.org/cli.html
Effect: open sqlite3 command line with an in memory empty database
Chapter: 1

Instruction: sqlite3 db
Page: https://www.sqlite.org/cli.html
Effect: open sqlite3 command line with db
Chapter: 1
Variables: db path of a sqlite database

Instruction: .archive ...
Effect: Manage SQL archives

Instruction: .auth ON|OFF
Effect: Show authorizer callbacks

Instruction: .backup ?DB? FILE
Effect: Backup DB (default "main") to FILE

Instruction: .bail on|off
Effect: Stop after hitting an error. Default OFF

Instruction: .binary on|off
Effect: Turn binary output on or off. Default OFF

Instruction: .cd DIRECTORY
Effect: Change the working directory to DIRECTORY

Instruction: .changes on|off
Effect: Show number of rows changed by SQL

Instruction: .check GLOB
Effect: Fail if output since .testcase does not match

Instruction: .clone NEWDB
Effect: Clone data into NEWDB from the existing database

Instruction: .connection [close] [#]
Effect: Open or close an auxiliary database connection

Instruction: .databases
Effect: List names and files of attached databases
Chapter: 6

Instruction: .dbconfig ?op? ?val?
Effect: List or change sqlite3_db_config() options

Instruction: .dbinfo ?DB?
Effect: Show status information about the database

Instruction: .dump ?OBJECTS?
Effect: output a sql command to recreate the database

Instruction: .echo on|off
Effect: Turn command echo on or off

Instruction: .eqp on|off|full|...
Effect: Enable or disable automatic EXPLAIN QUERY PLAN

Instruction: .excel
Effect: Display the output of next command in spreadsheet

Instruction: .exit ?CODE?
Effect: Exit this program with return-code CODE

Instruction: .expert
Effect: EXPERIMENTAL. Suggest indexes for queries

Instruction: .explain ?on|off|auto?
Effect: Change the EXPLAIN formatting mode. Default: auto

Instruction: .filectrl CMD ...
Effect: Run various sqlite3_file_control() operations

Instruction: .fullschema ?--indent?
Effect: Show schema and the content of sqlite_stat tables

Instruction: .headers on|off
Effect: Turn display of headers on or off

Instruction: .help ?-all? ?PATTERN?
Effect: Show help text for PATTERN

Instruction: .import FILE TABLE
Effect: Import data from FILE into TABLE
Chapter: 8
Section: 5

Instruction: .imposter INDEX TABLE
Effect: Create imposter table TABLE on index INDEX

Instruction: .indexes ?TABLE?
Effect: Show names of all indexes matching TABLE

Instruction: .limit ?LIMIT? ?VAL?
Effect: Display or change the value of an SQLITE_LIMIT

Instruction: .lint OPTIONS
Effect: Report potential schema issues.

Instruction: .load FILE ?ENTRY?
Effect: Load an extension library

Instruction: .log FILE|off
Effect: Turn logging on or off. FILE can be stderr/stdout

Instruction: .mode MODE ?TABLE?
Effect: Set output formatting to MODE
Variables: MODE one of
ascii, box, csv, column, html, insert, json, line, list, markdown, quote, table, tabs, tcl

Instruction: .nonce STRING
Effect: Disable safe mode for one command if the nonce matches

Instruction: .nullvalue STRING
Effect: Use STRING in place of NULL values

Instruction: .once
Effect: Output for the next SQL command to stdout

Instruction: .open ?OPTIONS? ?FILE?
Effect: Close existing database and reopen FILE
Chapter: 7

Instruction: .parameter CMD ...
Effect: Manage SQL parameter bindings

Instruction: .print STRING...
Effect: Print literal STRING

Instruction: .progress N
Effect: Invoke progress handler after every N opcodes

Instruction: .prompt MAIN CONTINUE
Effect: Replace the standard prompts

Instruction: .quit
Effect: Exit this program

Instruction: .read FILE
Effect: Execute the content of FILE as if it was written in the CLI

Instruction: .recover
Effect: Recover as much data as possible from corrupt db. Ouptut a query recreating the database that was recovered
Chapter: 11

Instruction: .restore ?DB? FILE
Effect: Restore content of DB (default "main") from FILE

Instruction: .save FILE
Effect: Write in-memory database into FILE

Instruction: .scanstats on|off
Effect: Turn sqlite3_stmt_scanstatus() metrics on or off

Instruction: .schema ?PATTERN?
Effect: Show the CREATE statements for each tables matching PATTERN

Instruction: .selftest ?OPTIONS?
Effect: Run tests defined in the SELFTEST table

Instruction: .separator COL ?ROW?
Effect: Change the column and row separators

Instruction: .session ?NAME? CMD ...
Effect: Create or control sessions

Instruction: .sha3sum ...
Effect: Compute a SHA3 hash of database content (independant of on-disk representation)

Instruction: .shell CMD ARGS...
Effect: Run CMD ARGS... in a system shell

Instruction: .show
Effect: Show the current values for various settings

Instruction: .stats ?ARG?
Effect: Show stats or turn stats on or off

Instruction: .system CMD ARGS...
Effect: Run CMD ARGS... in a system shell

Instruction: .tables ?TABLE?
Effect: List names of tables matching LIKE pattern TABLE

Instruction: .testcase NAME
Effect: Begin redirecting output to 'testcase-out.txt'

Instruction: .testctrl CMD ...
Effect: Run various sqlite3_test_control() operations

Instruction: .timeout MS
Effect: Try opening locked tables for MS milliseconds

Instruction: .timer on|off
Effect: Turn SQL timer on or off

Instruction: .trace ?OPTIONS?
Effect: Output each SQL statement as it is run

Instruction: .vfsinfo ?AUX?
Effect: Information about the top-level VFS

Instruction: .vfslist
Effect: List all available VFSes

Instruction: .vfsname ?AUX?
Effect: Print the name of the VFS stack

Instruction: .width WIDTH_COLUMN_1 ... WIDTH_COLUMN_N
Effect: Set minimum column widths for each column. 0 mean "automatically choosen"

Question: The kind of SQLite CLI input
Answer: sql statement
Page: https://www.sqlite.org/cli.html
Chapter: 4
Section: 1
Answer2: dot-command
Answer3: CLI comments

Name: A dot command
Definition: A command that changes SQL behaviour
Page: https://www.sqlite.org/cli.html
Chapter: 4
Section: 1
Context: sqlite3 CLI
Denoted by: .commandName

Language: sqlite3
Instruction: # comment
Other fields (not shown on cards):
Chapter: 4
Page: https://www.sqlite.org/cli.html
Context: CLI
Section: 1

Instruction: .mode
Effect: print current mode

Instruction: .mode insert table
Effect: output a sql command that would allow to input the result in table

Fla: --new
Effect: the database is reset prior to being opened. Any prior data is destroyed. This is a destructive overwrite of prior data and no confirmation is requested, so use this option carefully.
Prefix: .open

Fla: --readonly
Effect: opens the database in read-only mode.
Prefix: .open file

Fla: --deserialize
Effect: the entire content of the on-disk file to be read into memory and then opened as an in-memory database
Prefix: .open file

Fla: --append
Effect: causes the SQLite database to be appended to an existing file rather than working as a stand-alone file
Prefix: .open file

Fla: --zip
Effect: causes the specified input file to be interpreted as a ZIP archive instead of as an SQLite database file.
Prefix: .open file

Fla: --hexdb
Effect: causes the database content to be to be read from subsequent lines of input in a hex format, rather than from a separate file on disk
Prefix: .open file

Instruction: .output
Effect: Send output of all successive queries to stdout
Chapter: 8
Section: 1

Fla: filename
Effect: Send output to filename instead of stdout
Prefix: .output | .once
Chapter: 8
Section: 1

Fla: | cmd
Effect: pipe output to cmd
Prefix: .output | .once
Chapter: 8
Section: 1

Fla: -e
Effect: set output in a file and open it in the system text editor
Prefix: .once | .open
Chapter: 8
Section: 1

Fla: -x
Effect: save result in a temp csv and open it in the system csv reader
Prefix: .once | .open
Chapter: 8
Section: 1

Instruction: .read |cmd
Effect: Execute cmd and uses its stdout as if it were written in the CLI
Context: sqlite3
Chapter: 8
Section: 2

Instruction: readfile(filePath)
Context: sqlite3
Chapter: 8
Section: 3
Returns: A blob with content of filePath

Instruction: writefile(
  filePath,
  blob
)
Effect: Write at filePath blob
Context: sqlite3
Chapter: 8
Section: 3

Instruction: edit(value)
Effect: open an editor with default content value
Context: sqlite3
Chapter: 8
Section: 4
Returns: the content of the file when editor is closed

Instruction: edit(
  value,
  editor
)
Effect: open editor with default content value
Context: sqlite3
Chapter: 8
Section: 4
Returns: the content of the file when editor is closed

Instruction: .import FILE
Effect: Import data from csv FILE in a new table whose schema comes from csv's first line
Chapter: 8
Section: 5

Fla: --skip n
Effect: Don't ipmort the first n lines
Prefix: .import FILE
Chapter: 8
Section: 5

Instruction: .open file.zip
Effect: Put the content of file.zip into a database named file.zip allowing to list zip's content
Chapter: 9

Fla: --lost-and-found tableName
Effect: Put orphaned rows into a new table tableName
Prefix: .recover
Chapter: 11
Section: sqlite3

Fla: "sqlCommand"
Effect: don't open cli, instead execute sqlCommand
Page: https://www.sqlite.org/cli.html
Prefix: sqlite3
Chapter: 20

Fla: -A ARGS...
Effect: run ".archive ARGS" and exit

Fla: -append
Effect: append the database to the end of the file

Fla: -ascii
Effect: set output mode to 'ascii'

Fla: -bail
Effect: stop after hitting an error

Fla: -batch
Effect: force batch I/O

Fla: -box
Effect: set output mode to 'box'

Fla: -column
Effect: set output mode to 'column'

Fla: -cmd COMMAND
Effect: run "COMMAND" before reading stdin

Fla: -csv
Effect: set output mode to 'csv'

Fla: -deserialize
Effect: open the database using sqlite3_deserialize()

Fla: -echo
Effect: print commands before execution

Fla: -init FILENAME
Effect: read/process named file

Fla: -[no]header
Effect: turn headers on or off

Fla: -help
Effect: show this message

Fla: -html
Effect: set output mode to HTML

Fla: -interactive
Effect: force interactive I/O

Fla: -json
Effect: set output mode to 'json'

Fla: -line
Effect: set output mode to 'line'

Fla: -list
Effect: set output mode to 'list'

Fla: -lookaside SIZE N
Effect: use N entries of SZ bytes for lookaside memory

Fla: -markdown
Effect: set output mode to 'markdown'

Fla: -maxsize N
Effect: maximum size for a --deserialize database

Fla: -memtrace
Effect: trace all memory allocations and deallocations

Fla: -mmap N
Effect: default mmap size set to N

Fla: -newline SEP
Effect: set output row separator. Default: '\n'

Fla: -nofollow
Effect: refuse to open symbolic links to database files

Fla: -nonce STRING
Effect: set the safe-mode escape nonce

Fla: -nullvalue TEXT
Effect: set text string for NULL values. Default ''

Fla: -pagecache SIZE N
Effect: use N slots of SZ bytes each for page cache memory

Fla: -quote
Effect: set output mode to 'quote'

Fla: -readonly
Effect: open the database read-only

Fla: -safe
Effect: enable safe-mode. No change occur instead to the explicitly listed database
Chapter: 22
Section: 1

Fla: -separator SEP
Effect: set output column separator. Default: '|'

Fla: -stats
Effect: print memory stats before each finalize

Fla: -table
Effect: set output mode to 'table'

Fla: -tabs
Effect: set output mode to 'tabs'

Fla: -version
Effect: show SQLite version

Fla: -vfs NAME
Effect: use NAME as the default VFS

Fla: -zip
Effect: open the file as a ZIP Archive