User:Klaas van Buiten/sandbox

SQLite pocketManual

CommandLine

Usage: sqlite3 [OPTIONS] FILENAME [SQL]

   FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist.
   SQL is a dot-, SQL-command or Pragma. OPTIONS include:

(a-l) -append append the database to the end of the file -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -box set output mode to 'box' -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -deserialize open the database using sqlite3_deserialize() -echo print inputs before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -lookaside SIZE N use N entries of SZ bytes for lookaside memory

(m-z) -markdown set output mode to 'markdown' -maxsize N maximum size for a --deserialize database -memtrace trace all memory allocations and deallocations -mmap N default mmap size set to N -newline SEP set output row separator. Default: '\n' -nofollow refuse to open symbolic links to database files -nonce STRING set the safe-mode escape nonce -nullvalue TEXT set text string for NULL values. Default -pagecache SIZE N use N slots of SZ bytes each for page cache memory -pcachetrace trace all page cache operations -quote set output mode to 'quote' -readonly open the database read-only -safe enable safe-mode -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -table set output mode to 'table' -tabs set output mode to 'tabs' -unsafe-testing allow unsafe commands and modes for testing -version show SQLite version -vfs NAME use NAME as the default VFS

dotCommands

SQLite3.45 .helpScreen (a-e) .archive ... Manage SQL archivesa .auth (ON,OFF) Show authorizer callbacks .backup ?DB? FILE Backup DB (default "main") to FILE .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 .crnl (on,off) Turn binary output on or off. Default OFF .databases List names and files of attached databases .dbinfo ?DB? Show status information about the database .dump ?TABLE? ... Dump the database in an SQL text formatt .echo (on,off) Turn command echo on or off .eqp (on,off,trigger,full) En-/disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in a spreadsheet .exit Exit (=.q) this program .expert EXPERIMENTAL. Suggest indexes for specified queries More info at https://en.wikibooks.org/wiki/ SQLite/Release3#DotCommands       (f-r) .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers (on,off) Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .imposter INDEX TABLE Create imposter table TABLE on index INDEX .indexes ?TABLE? Show names of all indexest .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 ?TABLE? Set output modem for tablet .nullvalue STRING Use STRING in place of NULL values .once (-e,-x,FILE) Output for the next SQL command only to FILEe .open ?OPTIONS? ?FILE? Close existing database and reopen FILEo .output ?FILE? Send output to FILE or stdout .print STRING... Print literal STRING .prompt MAIN CONTINUE Replace the standard prompts .quit Quit (=.ex) this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE Source sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_

(s-z) .save FILE Write in-memory database into FILE .scanstats (on,off) Turn sqlite3_stmt_scanstatus() metrics on or off .schema ?PATTERN? Show the CREATE statements matching PATTERNs1 .selftest ?--init? Run tests defined in the SELFTEST table .separator COL ?ROW? Change separator for column and optionally rows2 .sha3sum ?OPTIONS...? Compute a SHA3 hash of database content .shell CMD ARGS... Run CMD ARGS... in OS shell (=sy) .show Show the current values for various settings .stats (on,off) Show stats or turn stats on or off .system CMD ARGS... Run CMD ARGS... in OS shell (=.sh) .tables ?TABLE? List names of tablest .testcase NAME Begin redirecting output to 'testcase-out.txt' .timeout MS Try opening locked tables for MS milliseconds .timer (on,off) Turn SQL timer on or off .trace (FILE,off) Output each SQL statement as it is run .version Show detailed SQLite version info .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 widths for "column" modew

a .ar [OPTION...] [FILE...] The .ar command manages sqlar archives. Examples: .ar -cf archive.sar foo bar # Create archive.sar from files foo and bar .ar -tf archive.sar # List members of archive.sar .ar -xvf archive.sar # Verbosely extract files from archive.sar Each command line must feature exactly one command option: -c, --create Create a new archive -u, --update Update or add files to an existing archive -t, --list List contents of archive -x, --extract Extract files from archive And zero or more optional options: -v, --verbose Print each filename as it is processed -f FILE, --file FILE Operate on archive FILE (default is current db) -a FILE, --append FILE Operate on FILE opened using the apndvfs VFS -C DIR, --directory DIR Change to directory DIR to read/extract files -n, --dryrun Show the SQL that would have occurred See also: sar-support

e or invoke system text editor (-e) or spreadsheet (-x) on the output. l lintOptions: fkey-indexes Find missing foreign key indexes m mode: ascii delimited by 0x1F and 0x1E; csv Comma-separated values; column Left-aligned columns (See .width); html HTML <table> code; insert SQL insert statements for TABLE; line One value per line list delimited by "|"; quote Escape answers as for SQL tabs Tab-separated values; tcl TCL list elements o openOptions: --new option starts with an empty file Other options: --readonly --append --zip s1 schemaOption: --indent for pretty-printing s2 separator for both the output mode and .import: t If TABLE specified, only dump tables matching LIKE pattern TABLE. w Negative values right-justify