diff options
Diffstat (limited to '')
| -rw-r--r-- | www/cli.html | 2007 |
1 files changed, 2007 insertions, 0 deletions
diff --git a/www/cli.html b/www/cli.html new file mode 100644 index 0000000..01fda85 --- /dev/null +++ b/www/cli.html @@ -0,0 +1,2007 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>Command Line Shell For SQLite</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +Command Line Shell For SQLite +</div> +<div class="fancy_toc"> +<a onclick="toggle_toc()"> +<span class="fancy_toc_mark" id="toc_mk">►</span> +Table Of Contents +</a> +<div id="toc_sub"><div class="fancy-toc1"><a href="#getting_started">1. Getting Started</a></div> +<div class="fancy-toc1"><a href="#double_click_startup_on_windows">2. Double-click Startup On Windows</a></div> +<div class="fancy-toc1"><a href="#special_commands_to_sqlite3_dot_commands_">3. Special commands to sqlite3 (dot-commands)</a></div> +<div class="fancy-toc1"><a href="#rules_for_dot_commands_sql_and_more">4. Rules for "dot-commands", SQL and More</a></div> +<div class="fancy-toc2"><a href="#line_structure">4.1. Line Structure</a></div> +<div class="fancy-toc2"><a href="#dot_command_arguments">4.2. Dot-command arguments</a></div> +<div class="fancy-toc2"><a href="#dot_command_execution">4.3. Dot-command execution</a></div> +<div class="fancy-toc1"><a href="#changing_output_formats">5. Changing Output Formats</a></div> +<div class="fancy-toc1"><a href="#querying_the_database_schema">6. Querying the database schema</a></div> +<div class="fancy-toc1"><a href="#opening_database_files">7. Opening Database Files</a></div> +<div class="fancy-toc1"><a href="#redirecting_i_o">8. Redirecting I/O</a></div> +<div class="fancy-toc2"><a href="#writing_results_to_a_file">8.1. Writing results to a file</a></div> +<div class="fancy-toc2"><a href="#reading_sql_from_a_file">8.2. Reading SQL from a file</a></div> +<div class="fancy-toc2"><a href="#file_i_o_functions">8.3. File I/O Functions</a></div> +<div class="fancy-toc2"><a href="#the_edit_sql_function">8.4. The edit() SQL function</a></div> +<div class="fancy-toc2"><a href="#importing_files_as_csv_or_other_formats">8.5. Importing files as CSV or other formats</a></div> +<div class="fancy-toc2"><a href="#export_to_csv">8.6. Export to CSV</a></div> +<div class="fancy-toc3"><a href="#_export_to_excel_">8.6.1. Export to Excel </a></div> +<div class="fancy-toc3"><a href="#_export_to_tsv_tab_separated_values_">8.6.2. Export to TSV (tab separated values)</a></div> +<div class="fancy-toc1"><a href="#accessing_zip_archives_as_database_files">9. Accessing ZIP Archives As Database Files</a></div> +<div class="fancy-toc2"><a href="#how_zip_archive_access_is_implemented">9.1. How ZIP archive access is implemented</a></div> +<div class="fancy-toc1"><a href="#converting_an_entire_database_to_a_text_file">10. Converting An Entire Database To A Text File</a></div> +<div class="fancy-toc1"><a href="#recover_data_from_a_corrupted_database">11. Recover Data From a Corrupted Database</a></div> +<div class="fancy-toc1"><a href="#loading_extensions">12. Loading Extensions</a></div> +<div class="fancy-toc1"><a href="#cryptographic_hashes_of_database_content">13. Cryptographic Hashes Of Database Content</a></div> +<div class="fancy-toc1"><a href="#database_content_self_tests">14. Database Content Self-Tests</a></div> +<div class="fancy-toc1"><a href="#sqlite_archive_support">15. SQLite Archive Support</a></div> +<div class="fancy-toc2"><a href="#_sqlite_archive_create_command_">15.1. SQLite Archive Create Command </a></div> +<div class="fancy-toc2"><a href="#_sqlite_archive_extract_command_">15.2. SQLite Archive Extract Command </a></div> +<div class="fancy-toc2"><a href="#_sqlite_archive_list_command_">15.3. SQLite Archive List Command </a></div> +<div class="fancy-toc2"><a href="#_sqlite_archive_insert_and_update_commands_">15.4. SQLite Archive Insert And Update Commands </a></div> +<div class="fancy-toc2"><a href="#_sqlite_archive_remove_command_">15.5. SQLite Archive Remove Command </a></div> +<div class="fancy-toc2"><a href="#_operations_on_zip_archives_">15.6. Operations On ZIP Archives </a></div> +<div class="fancy-toc2"><a href="#_sql_used_to_implement_sqlite_archive_operations_">15.7. SQL Used To Implement SQLite Archive Operations </a></div> +<div class="fancy-toc1"><a href="#sql_parameters">16. SQL Parameters</a></div> +<div class="fancy-toc1"><a href="#index_recommendations_sqlite_expert_">17. Index Recommendations (SQLite Expert)</a></div> +<div class="fancy-toc1"><a href="#working_with_multiple_database_connections">18. Working With Multiple Database Connections</a></div> +<div class="fancy-toc1"><a href="#miscellaneous_extension_features">19. Miscellaneous Extension Features</a></div> +<div class="fancy-toc1"><a href="#other_dot_commands">20. Other Dot Commands</a></div> +<div class="fancy-toc1"><a href="#using_sqlite3_in_a_shell_script">21. Using sqlite3 in a shell script</a></div> +<div class="fancy-toc1"><a href="#marking_the_end_of_an_sql_statement">22. Marking The End Of An SQL Statement</a></div> +<div class="fancy-toc1"><a href="#command_line_options">23. Command-line Options</a></div> +<div class="fancy-toc2"><a href="#the_safe_command_line_option">23.1. The --safe command-line option</a></div> +<div class="fancy-toc3"><a href="#bypassing_safe_restrictions_for_specific_commands">23.1.1. Bypassing --safe restrictions for specific commands</a></div> +<div class="fancy-toc1"><a href="#compiling_the_sqlite3_program_from_sources">24. Compiling the sqlite3 program from sources</a></div> +<div class="fancy-toc2"><a href="#_do_it_yourself_builds_">24.1. Do-It-Yourself Builds </a></div> +</div> +</div> +<script> +function toggle_toc(){ +var sub = document.getElementById("toc_sub") +var mk = document.getElementById("toc_mk") +if( sub.style.display!="block" ){ +sub.style.display = "block"; +mk.innerHTML = "▼"; +} else { +sub.style.display = "none"; +mk.innerHTML = "►"; +} +} +</script> +</div> + + + + +<a name="intro"></a> + +<h1 id="getting_started"><span>1. </span>Getting Started</h1> + +<p>The SQLite project provides a simple command-line program named +<b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows) +that allows the user to manually enter and execute SQL +statements against an SQLite database or against a +<a href="#zipdb">ZIP archive</a>. This document provides a brief +introduction on how to use the <b>sqlite3</b> program. + +</p><p>Start the <b>sqlite3</b> program by typing "sqlite3" at the +command prompt, optionally followed +by the name of the file that holds the SQLite database +(or <a href="#zipdb">ZIP archive</a>). If the named +file does not exist, a new database file with the given name will be +created automatically. If no database file is specified on the +command-line, a temporary database is created and automatically deleted when +the "sqlite3" program exits. + +</p><p>On startup, the <b>sqlite3</b> program will show a brief banner +message then prompt you to enter SQL. Type in SQL statements (terminated +by a semicolon), press "Enter" and the SQL will be executed.</p> + +<p>For example, to create a new SQLite database named "ex1" +with a single table named "tbl1", you might do this:</p> + +<div class="codeblock"><pre>$ <b>sqlite3 ex1</b> +SQLite version 3.36.0 2021-06-18 18:36:39 +Enter ".help" for usage hints. +sqlite> <b>create table tbl1(one text, two int);</b> +sqlite> <b>insert into tbl1 values('hello!',10);</b> +sqlite> <b>insert into tbl1 values('goodbye', 20);</b> +sqlite> <b>select * from tbl1;</b> +hello!|10 +goodbye|20 +sqlite> +</pre></div> + +<p>Terminate the sqlite3 program by typing your system +End-Of-File character (usually a Control-D). Use the interrupt +character (usually a Control-C) to stop a long-running SQL statement.</p> + +<p>Make sure you type a semicolon at the end of each SQL command! +The sqlite3 program looks for a semicolon to know when your SQL command is +complete. If you omit the semicolon, sqlite3 will give you a +continuation prompt and wait for you to enter more text to +complete the SQL command. This feature allows you to +enter SQL commands that span multiple lines. For example:</p> + + +<div class="codeblock"><pre>sqlite> <b>CREATE TABLE tbl2 (</b> + ...> <b> f1 varchar(30) primary key,</b> + ...> <b> f2 text,</b> + ...> <b> f3 real</b> + ...> <b>);</b> +sqlite> +</pre></div> + +<a name="dblclick"></a> + +<h1 id="double_click_startup_on_windows"><span>2. </span>Double-click Startup On Windows</h1> + +<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause +the command-line shell to pop-up a terminal window running SQLite. However, +because double-clicking starts the sqlite3.exe without command-line arguments, +no database file will have been specified, so SQLite will use a temporary +database that is deleted when the session exits. +To use a persistent disk file as the database, enter the ".open" command +immediately after the terminal window starts up: + +</p><div class="codeblock"><pre>SQLite version 3.36.0 2021-06-18 18:36:39 +Enter ".help" for usage hints. +Connected to a transient in-memory database. +Use ".open FILENAME" to reopen on a persistent database. +sqlite> <b>.open ex1.db</b> +sqlite> +</pre></div> + +<p>The example above causes the database file named "ex1.db" to be opened +and used. The "ex1.db" file is created if it does not previously exist. +You might want to +use a full pathname to ensure that the file is in the directory that you +think it is in. Use forward-slashes as the directory separator character. +In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p> + +<p>Alternatively, you can create a new database using the default temporary +storage, then save that database into a disk file using the ".save" command: + +</p><div class="codeblock"><pre>SQLite version 3.36.0 2021-06-18 18:36:39 +Enter ".help" for usage hints. +Connected to a transient in-memory database. +Use ".open FILENAME" to reopen on a persistent database. +sqlite> <i>... many SQL commands omitted ...</i> +sqlite> <b>.save ex1.db</b> +sqlite> +</pre></div> + +<p>Be careful when using the ".save" command as it will overwrite any +preexisting database files having the same name without prompting for +confirmation. As with the ".open" command, you might want to use a +full pathname with forward-slash directory separators to avoid ambiguity. + +<a name="dotcmd"></a> + +</p><h1 id="special_commands_to_sqlite3_dot_commands_"><span>3. </span>Special commands to sqlite3 (dot-commands)</h1> + +<p> +Most of the time, sqlite3 just reads lines of input and passes them +on to the SQLite library for execution. +But input lines that begin with a dot (".") +are intercepted and interpreted by the sqlite3 program itself. +These "dot commands" are typically used to change the output format +of queries, or to execute certain prepackaged query statements. +There were originally just a few dot commands, but over the years +many new features have accumulated so that today there are over 60. +</p> + +<p> +For a listing of the available dot commands, you can enter ".help" with +no arguments. Or enter ".help TOPIC" for detailed information about TOPIC. +The list of available dot-commands follows: +</p> + +<div class="codeblock"><pre>sqlite> <b>.help</b> +.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 +.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 ?TABLE? Set output mode +.nonce STRING Disable safe mode for one command if the 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 +.recover Recover as much data as possible from corrupt db. +.restore ?DB? FILE Restore content of DB (default "main") from FILE +.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 PATTERN +.selftest ?OPTIONS? Run tests defined in the SELFTEST table +.separator COL ?ROW? Change the column and row separators +.session ?NAME? CMD ... Create or control sessions +.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 +sqlite> +</pre></div> + +<a name="dotrules"></a> + +<h1 id="rules_for_dot_commands_sql_and_more"><span>4. </span>Rules for "dot-commands", SQL and More</h1> + +<h2 id="line_structure"><span>4.1. </span>Line Structure</h2> + +<p>The CLI's input is parsed into a sequence consisting of: + </p><ul> + <li>SQL statements;</li> + <li>dot-commands; or</li> + <li>CLI comments</li> + </ul> +<p>SQL statements are free-form, and can be spread across multiple lines, + with whitespace or SQL comments embedded anywhere. + They are terminated by either a ';' character at the end of an input line, + or a '/' character or the word "go" on a line by itself. + When not at the end of an input line, the ';' character + acts to separate SQL statements. + Trailing whitespace is ignored for purposes of termination. +</p><p>A dot-command has a more restrictive structure: +</p><ul> +<li>It must begin with its "." at the left margin + with no preceding whitespace.</li> +<li>It must be entirely contained on a single input line.</li> +<li>It cannot occur in the middle of an ordinary SQL + statement. In other words, it cannot occur at a + continuation prompt.</li> +<li>There is no comment syntax for dot-commands.</li> +</ul> +<p>The CLI also accepts whole-line comments that +begin with a '#' character and extend to the end of the line. +There can be no with whitespace prior to the '#'. + +</p><h2 id="dot_command_arguments"><span>4.2. </span>Dot-command arguments</h2> +<p>The arguments passed to dot-commands are parsed from the command tail, + per these rules: +</p><ol> + <li>The trailing newline and any other trailing whitespace is discarded;</li> + <li>Whitespace immediately following the dot-command name, or any argument + input end bound is discarded;</li> + <li>An argument input begins with any non-whitespace character;</li> + <li>An argument input ends with a character which + depends upon its leading character thusly:</li> + <ul> + <li>for a leading single-quote ('), a single-quote acts + as the end delimiter;</li> + <li>for a leading double-quote ("), an unescaped double-quote + acts as the end delimiter;</li> + <li>for any other leading character, the end delimiter is + any whitespace; and</li> + <li>the command tail end acts as the end delimiter for any argument;</li> + </ul> + <li>Within a double-quoted argument input, a backslash-escaped double-quote + is part of the argument rather than its terminating quote;</li> + <li>Within a double-quoted argument, traditional C-string literal, backslash + escape sequence translation is done; and</li> + <li>Argument input delimiters (the bounding quotes or whitespace) + are discarded to yield the passed argument.</li> +</ol> + +<h2 id="dot_command_execution"><span>4.3. </span>Dot-command execution</h2> +<p>The dot-commands +are interpreted by the sqlite3.exe command-line program, not by +SQLite itself. So none of the dot-commands will work as an argument +to SQLite interfaces such as <a href="c3ref/prepare.html">sqlite3_prepare()</a> or <a href="c3ref/exec.html">sqlite3_exec()</a>. + +<a name="dotmode"></a> + +</p><h1 id="changing_output_formats"><span>5. </span>Changing Output Formats</h1> + +<p>The sqlite3 program is able to show the results of a query +in 14 different output formats: +</p><div class="columns" style="columns:8em auto;"> +<ul style="padding-top:0;padding-left:4em;"> +<li> ascii +</li><li> box +</li><li> csv +</li><li> column +</li><li> html +</li><li> insert +</li><li> json +</li><li> line +</li><li> list +</li><li> markdown +</li><li> quote +</li><li> table +</li><li> tabs +</li><li> tcl +</li></ul></div> + +<p>You can use the ".mode" dot command to switch between these output +formats. +The default output mode is "list". In +list mode, each row of a query result is written on one line of +output and each column within that row is separated by a specific +separator string. The default separator is a pipe symbol ("|"). +List mode is especially useful when you are going to send the output +of a query to another program (such as AWK) for additional processing.</p> + +<div class="codeblock"><pre>sqlite> <b>.mode list</b> +sqlite> <b>select * from tbl1;</b> +hello!|10 +goodbye|20 +sqlite> +</pre></div> + +<p>Type ".mode" with no arguments to show the current mode: + +</p><div class="codeblock"><pre>sqlite> <b>.mode</b> +current output mode: list +sqlite> +</pre></div> + +<p>Use the ".separator" dot command to change the separator. +For example, to change the separator to a comma and +a space, you could do this:</p> + +<div class="codeblock"><pre>sqlite> <b>.separator ", "</b> +sqlite> <b>select * from tbl1;</b> +hello!, 10 +goodbye, 20 +sqlite> +</pre></div> + +<p>The next ".mode" command might reset the ".separator" back to some +default value (depending on its arguments). +So you will likely need to repeat the ".separator" command whenever you +change modes if you want to continue using a non-standard separator. + +<a name="dotmodequote"></a> + +</p><p>In "quote" mode, the output is formatted as SQL literals. Strings are +enclosed in single-quotes and internal single-quotes are escaped by doubling. +Blobs are displayed in hexadecimal blob literal notation (Ex: x'abcd'). +Numbers are displayed as ASCII text and NULL values are shown as "NULL". +All columns are separated from each other by a comma (or whatever alternative +character is selected using ".separator"). + +</p><div class="codeblock"><pre>sqlite> <b>.mode quote</b> +sqlite> <b>select * from tbl1;</b> +'hello!',10 +'goodbye',20 +sqlite> +</pre></div> + +<p>In "line" mode, each column in a row of the database +is shown on a line by itself. Each line consists of the column +name, an equal sign and the column data. Successive records are +separated by a blank line. Here is an example of line mode +output:</p> + +<div class="codeblock"><pre>sqlite> <b>.mode line</b> +sqlite> <b>select * from tbl1;</b> +one = hello! +two = 10 + +one = goodbye +two = 20 +sqlite> +</pre></div> + +<a name="clmnr"></a> + + +<p>In column mode, each record is shown on a separate line with the +data aligned in columns. For example:</p> + +<div class="codeblock"><pre>sqlite> <b>.mode column</b> +sqlite> <b>select * from tbl1;</b> +one two +-------- --- +hello! 10 +goodbye 20 +sqlite> +</pre></div> + +<p>In "column" mode (and also in "box", "table", and "markdown" modes) +the width of columns adjusts automatically. But you can override this, +providing a speicified width for each column using the ".width" command. +The arguments to ".width" are integers which are the number of +characters to devote to each column. Negative numbers mean right-justify. +Thus:</p> + +<div class="codeblock"><pre>sqlite> <b>.width 12 -6</b> +sqlite> <b>select * from tbl1;</b> +one two +------------ ------ +hello! 10 +goodbye 20 +sqlite> +</pre></div> + +<p>A width of 0 means the column width is chosen automatically. +Unspecified column widths become zero. Hence, the command +".width" with no arguments resets all column widths to zero and +hence causes all column widths to be determined automatically. + +</p><p>The "column" mode is a tabular output format. Other +tabular output formats are "box", "markdown", and "table": + +</p><div class="codeblock"><pre>sqlite> <b>.width</b> +sqlite> <b>.mode markdown</b> +sqlite> <b>select * from tbl1;</b> +| one | two | +|---------|-----| +| hello! | 10 | +| goodbye | 20 | +sqlite> <b>.mode table</b> +sqlite> <b>select * from tbl1;</b> ++---------+-----+ +| one | two | ++---------+-----+ +| hello! | 10 | +| goodbye | 20 | ++---------+-----+ +sqlite> <b>.mode box</b> +sqlite> <b>select * from tbl1;</b> +┌─────────┬─────┐ +│ one │ two │ +├─────────┼─────┤ +│ hello! │ 10 │ +│ goodbye │ 20 │ +└─────────┴─────┘ +sqlite> +</pre></div> + +<a name="wrap1"></a> + + +<p>The columnar modes accept some addition options to control formatting. +The "--wrap <i>N</i>" option (where <i>N</i> is an integer) causes columns +to wrap text that is longer than N characters. Wrapping is disabled if +N is zero. + +</p><div class="codeblock"><pre>sqlite> <b>insert into tbl1 values('The quick fox jumps over a lazy brown dog.',90);</b> +sqlite> <b>.mode box --wrap 30</b> +sqlite> <b>select * from tbl1 where two>50;</b> +┌────────────────────────────────┬─────┐ +│ one │ two │ +├────────────────────────────────┼─────┤ +│ The quick fox jumps over a laz │ 90 │ +│ y brown dog. │ │ +└────────────────────────────────┴─────┘ +sqlite> +</pre></div> + +<p>Wrapping happens after exactly <i>N</i> characters, +which might be in the middle of a word. +To wrap at a word boundary, add the "--wordwrap on" option +(or just "-ww" for short): + +</p><div class="codeblock"><pre>sqlite> <b>.mode box --wrap 30 -ww</b> +sqlite> <b>select * from tbl1 where two>50;</b> +┌─────────────────────────────┬─────┐ +│ one │ two │ +├─────────────────────────────┼─────┤ +│ The quick fox jumps over a │ 90 │ +│ lazy brown dog. │ │ +└─────────────────────────────┴─────┘ +sqlite> +</pre></div> + +<p>The "--quote" option causes the results in each column to be +quoted like an SQL literal, as in the "quote" mode. See the on-line +help for additional options. + +<a name="qbox"></a> + +</p><p>The command ".mode box --wrap 60 --quote" is so useful for general-purpose +database queries that it is given its own alias. Instead of typing out +that whole 27-character command, you can just say ".mode qbox". + +</p><p>Another useful output mode is "insert". In insert mode, the output +is formatted to look like SQL INSERT statements. Use insert +mode to generate text that can later be used to input data into a +different database.</p> + +<p>When specifying insert mode, you have to give an extra argument +which is the name of the table to be inserted into. For example:</p> + +<div class="codeblock"><pre>sqlite> <b>.mode insert new_table</b> +sqlite> <b>select * from tbl1 where two<50;</b> +INSERT INTO "new_table" VALUES('hello',10); +INSERT INTO "new_table" VALUES('goodbye',20); +sqlite> +</pre></div> + + +<p>Other output modes include "csv", "json", and "tcl". Try these +yourself to see what they do. +</p> + +<a name="schema"></a> + +<h1 id="querying_the_database_schema"><span>6. </span>Querying the database schema</h1> + +<p>The sqlite3 program provides several convenience commands that +are useful for looking at the schema of the database. There is +nothing that these commands do that cannot be done by some other +means. These commands are provided purely as a shortcut.</p> + +<a name="dtables"></a> + +<p>For example, to see a list of the tables in the database, you +can enter ".tables".</p> + + +<div class="codeblock"><pre>sqlite> <b>.tables</b> +tbl1 tbl2 +sqlite> +</pre></div> + + +<p>The ".tables" command is similar to setting list mode then +executing the following query:</p> + +<div class="codeblock"><pre>SELECT name FROM sqlite_schema +WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' +ORDER BY 1 +</pre></div> + +<p>But the ".tables" command does more. It queries the <a href="schematab.html">sqlite_schema</a> table +for all <a href="lang_attach.html">attached</a> databases, not just the primary database. And it arranges +its output into neat columns. + +</p><p>The ".indexes" command works in a similar way to list all of +the indexes. If the ".indexes" command is given an argument which is +the name of a table, then it shows just indexes on that table. + +<a name="dschema"></a> + +</p><p>The ".schema" command shows the complete schema for the database, +or for a single table if an optional tablename argument is provided: + +</p><div class="codeblock"><pre>sqlite> <b>.schema</b> +create table tbl1(one varchar(10), two smallint) +CREATE TABLE tbl2 ( + f1 varchar(30) primary key, + f2 text, + f3 real +); +sqlite> <b>.schema tbl2</b> +CREATE TABLE tbl2 ( + f1 varchar(30) primary key, + f2 text, + f3 real +); +sqlite> +</pre></div> + + +<p>The ".schema" command is roughly the same as setting +list mode, then entering the following query:</p> + +<div class="codeblock"><pre>SELECT sql FROM sqlite_schema +ORDER BY tbl_name, type DESC, name +</pre></div> + +<p>As with ".tables", the ".schema" command shows the schema for +all <a href="lang_attach.html">attached</a> databases. If you only want to see the schema for +a single database (perhaps "main") then you can add an argument +to ".schema" to restrict its output: + +</p><div class="codeblock"><pre>sqlite> <b>.schema main.*</b> +</pre></div> + +<p>The ".schema" command can be augmented with the "--indent" option, +in which case it tries to reformat the various CREATE statements of +the schema so that they are more easily readable by humans. + +<a name="dotdatabases"></a> + +</p><p>The ".databases" command shows a list of all databases open in +the current connection. There will always be at least 2. The first +one is "main", the original database opened. The second is "temp", +the database used for temporary tables. There may be additional +databases listed for databases attached using the ATTACH statement. +The first output column is the name the database is attached with, +and the second result column is the filename of the external file. +There may be a third result column which will be either "'r/o'" or +"'r/w'" depending on whether the database file is read-only or read-write. +And there might be a fourth result column showing the result of +<a href="c3ref/txn_state.html">sqlite3_txn_state()</a> for that database file. + +</p><div class="codeblock"><pre>sqlite> <b>.databases</b> +</pre></div> + +<a name="fullschema"></a> + +<p>The ".fullschema" dot-command works like the ".schema" command in +that it displays the entire database schema. But ".fullschema" also +includes dumps of the statistics tables "sqlite_stat1", "sqlite_stat3", +and "sqlite_stat4", if they exist. The ".fullschema" command normally +provides all of the information needed to exactly recreate a query +plan for a specific query. When reporting suspected problems with +the SQLite query planner to the SQLite development team, developers +are requested to provide the complete ".fullschema" output as part +of the trouble report. Note that the sqlite_stat3 and sqlite_stat4 +tables contain samples of index entries and so might contain sensitive +data, so do not send the ".fullschema" output of a proprietary database +over a public channel.</p> + +<a name="dotopen"></a> + +<h1 id="opening_database_files"><span>7. </span>Opening Database Files</h1> + +<p>The ".open" command opens a new database connection, after first closing the +previously opened database command. In its simplest form, the ".open" command merely +invokes <a href="c3ref/open.html">sqlite3_open()</a> on the file named as its argument. Use the name ":memory:" +to open a new in-memory database that disappears when the CLI exits or when the +".open" command is run again. + +</p><p>If the --new option is included with ".open", then 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. + +</p><p>The --readonly option opens the database in read-only mode. Write will be +prohibited. + +</p><p>The --deserialize option causes the entire content of the on-disk file to be +read into memory and then opened as an in-memory database using the +<a href="c3ref/deserialize.html">sqlite3_deserialize()</a> interface. This will, of course, require a lot of memory +if you have a large database. Also, any changes you make to the database will not +be saved back to disk unless you explicitly save them using the ".save" or ".backup" +commands. + +</p><p>The --append option causes the SQLite database to be appended to an existing +file rather than working as a stand-alone file. See the +<a href="https://www.sqlite.org/src/file/ext/misc/appendvfs.c">appendvfs extension</a> for +more information. + +</p><p>The --zip option causes the specified input file to be interpreted as a ZIP archive +instead of as an SQLite database file. + +</p><p>The --hexdb option 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. +The "dbtotxt" command-line tool can be used to generate +the appropriate text for a database. The --hexdb option is intended for use by the +SQLite developers for testing purposes. We do not know of any use cases for this +option outside of internal SQLite testing and development. + +</p><h1 id="redirecting_i_o"><span>8. </span>Redirecting I/O</h1> + +<a name="dotoutput"></a> + +<h2 id="writing_results_to_a_file"><span>8.1. </span>Writing results to a file</h2> + +<p>By default, sqlite3 sends query results to standard output. You +can change this using the ".output" and ".once" commands. Just put +the name of an output file as an argument to .output and all subsequent +query results will be written to that file. Or use the .once command +instead of .output and output will only be redirected for the single next +command before reverting to the console. Use .output with no arguments to +begin writing to standard output again. For example:</p> + +<div class="codeblock"><pre>sqlite> <b>.mode list</b> +sqlite> <b>.separator |</b> +sqlite> <b>.output test_file_1.txt</b> +sqlite> <b>select * from tbl1;</b> +sqlite> <b>.exit</b> +$ <b>cat test_file_1.txt</b> +hello|10 +goodbye|20 +$ +</pre></div> + +<p>If the first character of the ".output" or ".once" filename is a pipe +symbol ("|") then the remaining characters are treated as a command and the +output is sent to that command. This makes it easy to pipe the results +of a query into some other process. For example, the +"open -f" command on a Mac opens a text editor to display the content that +it reads from standard input. So to see the results of a query +in a text editor, one could type:</p> + +<div class="codeblock"><pre>sqlite> <b>.once | open -f</b> +sqlite> <b>SELECT * FROM bigTable;</b> +</pre></div> + +<p>If the ".output" or ".once" commands have an argument of "-e" then +output is collected into a temporary file and the system text editor is +invoked on that text file. Thus, the command ".once -e" achieves the +same result as ".once '|open -f'" but with the benefit of being portable +across all systems. + +</p><p>If the ".output" or ".once" commands have a "-x" argument, that causes +them to accumulate output as Comma-Separated-Values (CSV) in a temporary +file, then invoke the default system utility for viewing CSV files +(usually a spreadsheet program) on the result. This is a quick way of +sending the result of a query to a spreadsheet for easy viewing: + +</p><div class="codeblock"><pre>sqlite> <b>.once -x</b> +sqlite> <b>SELECT * FROM bigTable;</b> +</pre></div> + +<a name="dotexcel"></a> + +<p>The ".excel" command is an alias for ".once -x". It does exactly the same +thing. + +<a name="dotread"></a> + +</p><h2 id="reading_sql_from_a_file"><span>8.2. </span>Reading SQL from a file</h2> + +<p>In interactive mode, sqlite3 reads input text (either SQL statements +or <a href="cli.html#dotcmd">dot-commands</a>) from the keyboard. You can also redirect input from +a file when you launch sqlite3, of course, but then you do not have the +ability to interact with the program. Sometimes it is useful to run an +SQL script contained in a file entering other commands from the command-line. +For this, the ".read" dot-command is provided. + +</p><p>The ".read" command takes a single argument which is (usually) the name +of a file from which to read input text. + +</p><div class="codeblock"><pre>sqlite> <b>.read myscript.sql</b> +</pre></div> + +<p>The ".read" command temporarily stops reading from the keyboard and instead +takes its input from the file named. Upon reaching the end of the file, +input reverts back to the keyboard. The script file may contain dot-commands, +just like ordinary interactive input. + +</p><p>If the argument to ".read" begins with the "|" character, then instead of +opening the argument as a file, it runs the argument (without the leading "|") +as a command, then uses the output of that command as its input. Thus, if you +have a script that generates SQL, you can execute that SQL directly using +a command similar to the following: + +</p><div class="codeblock"><pre>sqlite> <b>.read |myscript.bat</b> +</pre></div> + + +<a name="fileio"></a> + +<h2 id="file_i_o_functions"><span>8.3. </span>File I/O Functions</h2> + +<p>The command-line shell adds two <a href="appfunc.html">application-defined SQL functions</a> that +facilitate reading content from a file into a table column, and writing the +content of a column into a file, respectively. + +</p><p>The readfile(X) SQL function reads the entire content of the file named +X and returns that content as a BLOB. This can be used to load content into +a table. For example: + +</p><div class="codeblock"><pre>sqlite> <b>CREATE TABLE images(name TEXT, type TEXT, img BLOB);</b> +sqlite> <b>INSERT INTO images(name,type,img</b>) + ...> <b> VALUES('icon','jpeg',readfile('icon.jpg'));</b> +</pre></div> + +<p>The writefile(X,Y) SQL function write the blob Y into the file named X +and returns the number of bytes written. Use this function to extract +the content of a single table column into a file. For example: + +</p><div class="codeblock"><pre>sqlite> <b>SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';</b> +</pre></div> + +<p>Note that the readfile(X) and writefile(X,Y) functions are extension +functions and are not built into the core SQLite library. These routines +are available as a <a href="loadext.html">loadable extension</a> in the +<a href="http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c">ext/misc/fileio.c</a> +source file in the <a href="download.html#srctree">SQLite source code repositories</a>. + +<a name="editfunc"></a> + +</p><h2 id="the_edit_sql_function"><span>8.4. </span>The edit() SQL function</h2> + +<p>The CLI has another built-in SQL function named edit(). Edit() takes +one or two arguments. The first argument is a value - often a large +multi-line string to be edited. The second argument is the invocation +for a text editor. (It may include options to affect the editor's +behavior.) If the second argument is omitted, the VISUAL environment +variable is used. The edit() function writes its first argument into a +temporary file, invokes the editor on the temporary file, rereads the file +back into memory after the editor is done, then returns the edited text. + +</p><p>The edit() function can be used to make changes to large text +values. For example: + +</p><div class="codeblock"><pre>sqlite> <b>UPDATE docs SET body=edit(body) WHERE name='report-15';</b> +</pre></div> + +<p>In this example, the content of the docs.body field for the entry where +docs.name is "report-15" will be sent to the editor. After the editor returns, +the result will be written back into the docs.body field. + +</p><p>The default operation of edit() is to invoke a text editor. But by using +an alternative edit program in the second argument, you can also get it to edit +images or other non-text resources. For example, if you want to modify a JPEG +image that happens to be stored in a field of a table, you could run: + +</p><div class="codeblock"><pre>sqlite> <b>UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';</b> +</pre></div> + +<p>The edit program can also be used as a viewer, by simply ignoring the +return value. For example, to merely look at the image above, you might run: + +</p><div class="codeblock"><pre>sqlite> <b>SELECT length(edit(img,'gimp')) WHERE id='pic-1542';</b> +</pre></div> + +<a name="csv"></a> + +<h2 id="importing_files_as_csv_or_other_formats"><span>8.5. </span>Importing files as CSV or other formats</h2> + +<p>Use the ".import" command to import CSV (comma separated value) +or similarly delimited data into an SQLite table. +The ".import" command takes two arguments which are the +source from which data is to be read and the name of the +SQLite table into which the data is to be inserted. The source argument +is the name of a file to be read or, if it begins with a "|" character, +it specifies a command which will be run to produce the input data. + +</p><p>Note that it may be important to set the "mode" before running the + ".import" command. This is prudent to prevent the command-line shell +from trying to interpret the input file text as some format other than +how the file is structured. If the --csv or --ascii options are used, +they control import input delimiters. Otherwise, the delimiters are +those in effect for the current output mode. + +</p><p>To import into a table not in the "main" schema, the --schema option +may be used to specify that the table is in some other schema. This can +be useful for ATTACH'ed databases or to import into a TEMP table. + +</p><p>When .import is run, its treatment of the first input row depends +upon whether the target table already exists. If it does not exist, +the table is automatically created and the content of the first input +row is used to set the name of all the columns in the table. In this +case, the table data content is taken from the second and subsequent +input rows. If the target table already exists, every row of the +input, including the first, is taken to be actual data content. If +the input file contains an initial row of column labels, you can make +the .import command skip that initial row using the "--skip 1" option. + +</p><p>Here is an example usage, loading a pre-existing temporary table +from a CSV file which has column names in its first row: + +</p><div class="codeblock"><pre>sqlite> <b>.import --csv --skip 1 --schema temp C:/work/somedata.csv tab1</b> +</pre></div> + +<p>While reading input data in modes other than 'ascii', ".import" +interprets input as records composed of fields according to the RFC 4180 +specification with this exception: The input record and field separators +are as set by the mode or by use of the .separator command. Fields are +always subject to quote removal to reverse quoting done per RFC 4180, +except in ascii mode. + +</p><p>To import data with arbitrary delimiters and no quoting, +first set ascii mode (".mode ascii"), then set the field +and record delimiters using the ".separators" command. This +will suppress dequoting. Upon ".import", the data will be split +into fields and records according to the delimiters so specified. + +<a name="csvout"></a> + +</p><h2 id="export_to_csv"><span>8.6. </span>Export to CSV</h2> + +<p>To export an SQLite table (or part of a table) as CSV, simply set +the "mode" to "csv" and then run a query to extract the desired rows +of the table. The output will formatted as CSV per RFC 4180. + +</p><div class="codeblock"><pre>sqlite> <b>.headers on</b> +sqlite> <b>.mode csv</b> +sqlite> <b>.once c:/work/dataout.csv</b> +sqlite> <b>SELECT * FROM tab1;</b> +sqlite> <b>.system c:/work/dataout.csv</b> +</pre></div> + +<p>In the example above, the ".headers on" line causes column labels to +be printed as the first row of output. This means that the first row of +the resulting CSV file will contain column labels. If column labels are +not desired, set ".headers off" instead. (The ".headers off" setting is +the default and can be omitted if the headers have not been previously +turned on.) + +</p><p>The line ".once <i>FILENAME</i>" causes all query output to go into +the named file instead of being printed on the console. In the example +above, that line causes the CSV content to be written into a file named +"C:/work/dataout.csv". + +</p><p>The final line of the example (the ".system c:/work/dataout.csv") +has the same effect as double-clicking on the c:/work/dataout.csv file +in windows. This will typically bring up a spreadsheet program to display +the CSV file. + +</p><p>That command only works as written on Windows. +The equivalent line on a Mac would be: + +</p><div class="codeblock"><pre>sqlite> <b>.system open dataout.csv</b> +</pre></div> + +<p>On Linux and other unix systems you will need to enter something like: + + +</p><div class="codeblock"><pre>sqlite> <b>.system xdg-open dataout.csv</b> +</pre></div> + +<a name="exexcel*"></a> + +<h3 id="_export_to_excel_"><span>8.6.1. </span> Export to Excel </h3> + +<p>To simplify export to a spreadsheet, the CLI provides the +".excel" command which captures the output of a single query and sends +that output to the default spreadsheet program on the host computer. +Use it like this: + +</p><div class="codeblock"><pre>sqlite> <b>.excel</b> +sqlite> <b>SELECT * FROM tab;</b> +</pre></div> + +<p> +The command above writes the output of the query as CSV into a temporary +file, invokes the default handler for CSV files (usually the preferred +spreadsheet program such as Excel or LibreOffice), then deletes the +temporary file. This is essentially a short-hand method of doing +the sequence of ".csv", ".once", and ".system" commands described above. + +</p><p> +The ".excel" command is really an alias for ".once -x". The -x option +to .once causes it to writes results as CSV into a temporary file that +is named with a ".csv" suffix, then invoke the systems default handler +for CSV files. + +</p><p> +There is also a ".once -e" command which works similarly, except that +it names the temporary file with a ".txt" suffix so that the default +text editor for the system will be invoked, instead of the default +spreadsheet. + +<a name="extsv*"></a> + +</p><h3 id="_export_to_tsv_tab_separated_values_"><span>8.6.2. </span> Export to TSV (tab separated values)</h3> + +<p> +Exporting to pure TSV, without any field quoting, can be done by +entering ".mode tabs" before running a query. However, the output +will not be read correctly in tabs mode by the ".import" command +if it contains doublequote characters. To get TSV quoted per +RFC 4180 so that it can be input in tabs mode with ".import", +first enter ".mode csv", then enter '.separator "\t"' +before running a query. + +<a name="zipdb"></a> + +</p><h1 id="accessing_zip_archives_as_database_files"><span>9. </span>Accessing ZIP Archives As Database Files</h1> + +<p>In addition to reading and writing SQLite database files, +the <b>sqlite3</b> program will also read and write ZIP archives. +Simply specify a ZIP archive filename in place of an SQLite database +filename on the initial command line, or in the ".open" command, +and <b>sqlite3</b> will automatically detect that the file is a +ZIP archive instead of an SQLite database and will open it as such. +This works regardless of file suffix. So you can open JAR, DOCX, +and ODP files and any other file format that is really a ZIP +archive and SQLite will read it for you. + +</p><p>A ZIP archive appears to be a database containing a single table +with the following schema: + +</p><div class="codeblock"><pre>CREATE TABLE zip( + name, // Name of the file + mode, // Unix-style file permissions + mtime, // Timestamp, seconds since 1970 + sz, // File size after decompression + rawdata, // Raw compressed file data + data, // Uncompressed file content + method // ZIP compression method code +); +</pre></div> + +<p>So, for example, if you wanted to see the compression efficiency +(expressed as the size of the compressed content relative to the +original uncompressed file size) for all files in the ZIP archive, +sorted from most compressed to least compressed, you could run a +query like this: + +</p><div class="codeblock"><pre>sqlite> SELECT name, (100.0*length(rawdata))/sz FROM zip ORDER BY 2; +</pre></div> + +<p>Or using <a href="cli.html#fileio">file I/O functions</a>, you can extract elements of the +ZIP archive: + +</p><div class="codeblock"><pre>sqlite> SELECT writefile(name,content) FROM zip + ...> WHERE name LIKE 'docProps/%'; +</pre></div> + +<h2 id="how_zip_archive_access_is_implemented"><span>9.1. </span>How ZIP archive access is implemented</h2> + +<p>The command-line shell uses the <a href="zipfile.html">Zipfile virtual table</a> to +access ZIP archives. You can see this by running the ".schema" +command when a ZIP archive is open: + +</p><div class="codeblock"><pre>sqlite> .schema +CREATE VIRTUAL TABLE zip USING zipfile('document.docx') +/* zip(name,mode,mtime,sz,rawdata,data,method) */; +</pre></div> + +<p>When opening a file, if the command-line client discovers that the +file is ZIP archive instead of an SQLite database, it actually opens +an <a href="inmemorydb.html">in-memory database</a> and then in that in-memory database it creates +an instance of the <a href="zipfile.html">Zipfile virtual table</a> that is attached to the +ZIP archive. + +</p><p>The special processing for opening ZIP archives is a trick of the +command-line shell, not the core SQLite library. So if you want to +open a ZIP archive as a database in your application, you will need to +activate the <a href="zipfile.html">Zipfile virtual table</a> module then run an appropriate +<a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. + + +<a name="dump"></a> + +</p><h1 id="converting_an_entire_database_to_a_text_file"><span>10. </span>Converting An Entire Database To A Text File</h1> + +<p>Use the ".dump" command to convert the entire contents of a +database into a single UTF-8 text file. This file can be converted +back into a database by piping it back into <b>sqlite3</b>.</p> + +<p>A good way to make an archival copy of a database is this:</p> + + +<div class="codeblock"><pre>$ <b>sqlite3 ex1 .dump | gzip -c >ex1.dump.gz</b> +</pre></div> + + +<p>This generates a file named <b>ex1.dump.gz</b> that contains everything +you need to reconstruct the database at a later time, or on another +machine. To reconstruct the database, just type:</p> + + +<div class="codeblock"><pre>$ <b>zcat ex1.dump.gz | sqlite3 ex2</b> +</pre></div> + + +<p>The text format is pure SQL so you +can also use the .dump command to export an SQLite database +into other popular SQL database engines. Like this:</p> + + +<div class="codeblock"><pre>$ <b>createdb ex2</b> +$ <b>sqlite3 ex1 .dump | psql ex2</b> +</pre></div> + +<a name="recover"></a> + +<h1 id="recover_data_from_a_corrupted_database"><span>11. </span>Recover Data From a Corrupted Database</h1> + +<p>Like the ".dump" command, ".recover" attempts to convert the entire +contents of a database file to text. The difference is that instead of +reading data using the normal SQL database interface, ".recover" +attempts to reassemble the database based on data extracted directly from +as many database pages as possible. If the database is corrupt, ".recover" +is usually able to recover data from all uncorrupted parts of the database, +whereas ".dump" stops when the first sign of corruption is encountered. + +</p><p>If the ".recover" command recovers one or more rows that it cannot +attribute to any database table, the output script creates a "lost_and_found" +table to store the orphaned rows. The schema of the lost_and_found +table is as follows: + +</p><div class="codeblock"><pre>CREATE TABLE lost_and_found( + rootpgno INTEGER, -- root page of tree pgno is a part of + pgno INTEGER, -- page number row was found on + nfield INTEGER, -- number of fields in row + id INTEGER, -- value of rowid field, or NULL + c0, c1, c2, c3... -- columns for fields of row +); +</pre></div> + +<p>The "lost_and_found" table contains one row for each orphaned row recovered +from the database. Additionally, there is one row for each recovered index +entry that cannot be attributed to any SQL index. This is because, in an +SQLite database, the same format is used to store SQL index entries and +WITHOUT ROWID table entries. + +</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> + <tr style="text-align:left"><th style="width:15ex">Column</th><th>Contents + </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>rootpgno</td><td> Even though it may not be possible to attribute the + row to a specific database table, it may be part of a tree structure + within the database file. In this case, the root page number of that + tree structure is stored in this column. Or, if the page the row was + found on is not part of a tree structure, this column stores a copy of + the value in column "pgno" - the page number of the page the row was + found on. In many, although not all, cases, all rows in the + lost_and_found table with the same value in this column belong to the + same table. + + </td></tr><tr style="text-align:left"><td>pgno</td><td> The page number of the page on which this row was found. + + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>nfield</td><td> The number of fields in this row. + + </td></tr><tr style="text-align:left"><td>id</td><td> If the row comes from a WITHOUT ROWID table, this column + contains NULL. Otherwise, it contains the 64-bit integer rowid value for + the row. + + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>c0, c1, c2...</td><td> The values for each column of the row + are stored in these columns. The ".recover" command creates the + lost_and_found table with as many columns as required by the longest + orphaned row. +</td></tr></table> + +<p>If the recovered database schema already contains a table named +"lost_and_found", the ".recover" command uses the name "lost_and_found0". If +the name "lost_and_found0" is also already taken, "lost_and_found1", and so +on. The default name "lost_and_found" may be overridden by invoking ".recover" +with the --lost-and-found switch. For example, to have the output script call +the table "orphaned_rows": + +</p><div class="codeblock"><pre>sqlite> .recover --lost-and-found orphaned_rows +</pre></div> + +<a name="dotload"></a> + +<h1 id="loading_extensions"><span>12. </span>Loading Extensions</h1> + +<p>You can add new custom <a href="appfunc.html">application-defined SQL functions</a>, +<a href="datatype3.html#collation">collating sequences</a>, <a href="vtab.html">virtual tables</a>, and <a href="vfs.html">VFSes</a> to the command-line +shell at run-time using the ".load" command. First, build the +extension as a DLL or shared library (as described in the +<a href="loadext.html">Run-Time Loadable Extensions</a> document) then type: + +</p><div class="codeblock"><pre>sqlite> .load /path/to/my_extension +</pre></div> + +<p>Note that SQLite automatically adds the appropriate extension suffix +(".dll" on windows, ".dylib" on Mac, ".so" on most other unixes) to the +extension filename. It is generally a good idea to specify the full +pathname of the extension. + +</p><p>SQLite computes the entry point for the extension based on the extension +filename. To override this choice, simply add the name of the extension +as a second argument to the ".load" command. + +</p><p>Source code for several useful extensions can be found in the +<a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a> +subdirectory of the SQLite source tree. You can use these extensions +as-is, or as a basis for creating your own custom extensions to address +your own particular needs. + + +<a name="sha3sum"></a> + +</p><h1 id="cryptographic_hashes_of_database_content"><span>13. </span>Cryptographic Hashes Of Database Content</h1> + +<p>The ".sha3sum" dot-command computes a +<a href="https://en.wikipedia.org/wiki/SHA-3">SHA3</a> hash of the <em>content</em> +of the database. To be clear, the hash is computed over the database content, +not its representation on disk. This means, for example, that a <a href="lang_vacuum.html">VACUUM</a> +or similar data-preserving transformation does not change the hash. + +</p><p>The ".sha3sum" command supports options "--sha3-224", "--sha3-256", +"--sha3-384", and "--sha3-512" to define which variety of SHA3 to use +for the hash. The default is SHA3-256. + +</p><p>The database schema (in the <a href="schematab.html">sqlite_schema</a> table) is not normally +included in the hash, but can be added by the "--schema" option. + +</p><p>The ".sha3sum" command takes a single optional argument which is a +<a href="lang_expr.html#like">LIKE</a> pattern. If this option is present, only tables whose names match +the <a href="lang_expr.html#like">LIKE</a> pattern will be hashed. + +</p><p>The ".sha3sum" command is implemented with the help of the +<a href="https://www.sqlite.org/src/file/ext/misc/shathree.c">extension function "sha3_query()"</a> +that is included with the command-line shell. + +<a name="selftest"></a> + +</p><h1 id="database_content_self_tests"><span>14. </span>Database Content Self-Tests</h1> + +<p>The ".selftest" command attempts to verify that a database is +intact and is not corrupt. +The .selftest command looks for a table in schema named "selftest" +and defined as follows: + +</p><div class="codeblock"><pre>CREATE TABLE selftest( + tno INTEGER PRIMARY KEY, -- Test number + op TEXT, -- 'run' or 'memo' + cmd TEXT, -- SQL command to run, or text of "memo" + ans TEXT -- Expected result of the SQL command +); +</pre></div> + +<p>The .selftest command reads the rows of the selftest table in +selftest.tno order. +For each 'memo' row, it writes the text in 'cmd' to the output. For +each 'run' row, it runs the 'cmd' text as SQL and compares the result +to the value in 'ans', and shows an error message if the results differ. + +</p><p>If there is no selftest table, the ".selftest" command runs +<a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>. + +</p><p>The ".selftest --init" command creates the selftest table if it +does not already exists, then appends entries that check the SHA3 +hash of the content of all tables. Subsequent runs of ".selftest" +will verify that the database has not been changed in any way. To +generate tests to verify that a subset of the tables is unchanged, +simply run ".selftest --init" then <a href="lang_delete.html">DELETE</a> the selftest rows that +refer to tables that are not constant. + +<a name="sqlar"></a> + +</p><h1 id="sqlite_archive_support"><span>15. </span>SQLite Archive Support</h1> + +<p>The ".archive" dot-command and the "-A" command-line option +provide built-in support for the +<a href="sqlar.html">SQLite Archive format</a>. The interface is similar to +that of the "tar" command on unix systems. Each invocation of the ".ar" +command must specify a single command option. The following commands +are available for ".archive": + +</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> + <tr style="text-align:left"><th style="width:15ex">Option</th><th style="width:17ex">Long Option</th><th>Purpose + </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-c</td><td>--create</td><td>Create a new archive containing specified files. + </td></tr><tr style="text-align:left"><td>-x</td><td>--extract</td><td>Extract specified files from archive. + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-i</td><td>--insert</td><td>Add files to existing archive. + </td></tr><tr style="text-align:left"><td>-r</td><td>--remove</td><td>Remove files from the archive. + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-t</td><td>--list</td><td>List the files in the archive. + </td></tr><tr style="text-align:left"><td>-u</td><td>--update</td><td>Add files to existing archive <em>if</em> they have changed. +</td></tr></table> + +<p>As well as the command option, each invocation of ".ar" may specify +one or more modifier options. Some modifier options require an argument, +some do not. The following modifier options are available: + +</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> + <tr style="text-align:left"><th style="width:15ex">Option</th><th style="width:17ex">Long Option</th><th>Purpose + </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-v</td><td>--verbose</td><td>List each file as it is processed. + </td></tr><tr style="text-align:left"><td>-f FILE</td><td>--file FILE</td><td>If specified, use file FILE as the + archive. Otherwise, assume that the current "main" database is the + archive to be operated on. + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-a FILE</td><td>--append FILE</td><td>Like --file, use file FILE as the + archive, but open the file using the + <a href="https://sqlite.org/src/file/ext/misc/appendvfs.c">apndvfs VFS</a> so that + the archive will be appended to the end of FILE if FILE already exists. + </td></tr><tr style="text-align:left"><td>-C DIR</td><td>--directory DIR</td><td>If specified, interpret all relative + paths as relative to DIR, instead of the current working directory. + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-g</td><td>--glob</td><td>Use <a href="lang_corefunc.html#glob">glob(<i>Y</i>,<i>X</i>)</a> to match arguments + against names in the archive. + </td></tr><tr style="text-align:left"><td>-n</td><td>--dryrun</td><td>Show the SQL that would be run to carry out the + archive operation, but do not actually change anything. + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>--</td><td>--</td><td>All subsequent command line words are command arguments, + not options. +</td></tr></table> + +<p> +For command-line usage, add the short style command-line options immediately +following the "-A", without an intervening space. All subsequent arguments +are considered to be part of the .archive command. For example, the following +commands are equivalent: + +</p><div class="codeblock"><pre>sqlite3 new_archive.db -Acv file1 file2 file3 +sqlite3 new_archive.db ".ar -cv file1 file2 file3" +</pre></div> + +<p> +Long and short style options may be mixed. For example, the following are +equivalent: + +</p><div class="codeblock"><pre><i>-- Two ways to create a new archive named "new_archive.db" containing</i> +<i>-- files "file1", "file2" and "file3".</i> +.ar -c --file new_archive.db file1 file2 file3 +.ar -f new_archive.db --create file1 file2 file3 +</pre></div> + +<p>Alternatively, the first argument following to ".ar" may be the concatenation +of the short form of all required options (without the "-" characters). In +this case arguments for options requiring them are read from the command line +next, and any remaining words are considered command arguments. For example: + +</p><div class="codeblock"><pre><i>-- Create a new archive "new_archive.db" containing files "file1" and</i> +<i>-- "file2" from directory "dir1".</i> +.ar cCf dir1 new_archive.db file1 file2 file3 +</pre></div> + +<h2 id="_sqlite_archive_create_command_"><span>15.1. </span> SQLite Archive Create Command </h2> + +<p>Create a new archive, overwriting any existing archive (either in the current +"main" db or in the file specified by a --file option). Each argument following +the options is a file to add to the archive. Directories are imported +recursively. See above for examples. + +</p><h2 id="_sqlite_archive_extract_command_"><span>15.2. </span> SQLite Archive Extract Command </h2> + +<p>Extract files from the archive (either to the current working directory or +to the directory specified by a --directory option). +Files or directories whose names match the arguments, +as affected by the --glob option, are extracted. +Or, if no arguments follow the options, all files and directories are extracted. +Any specified directories are extracted recursively. It is an error if any +specified names or match patterns cannot be found in the archive. + +</p><div class="codeblock"><pre><i>-- Extract all files from the archive in the current "main" db to the</i> +<i>-- current working directory. List files as they are extracted. </i> +.ar --extract --verbose + +<i>-- Extract file "file1" from archive "ar.db" to directory "dir1".</i> +.ar fCx ar.db dir1 file1 + +<i>-- Extract files with ".h" extension to directory "headers".</i> +.ar -gCx headers *.h +</pre></div> + +<h2 id="_sqlite_archive_list_command_"><span>15.3. </span> SQLite Archive List Command </h2> + +<p>List the contents of the archive. If no arguments are specified, then all +files are listed. Otherwise, only those which match the arguments, +as affected by the --glob option, are listed. Currently, +the --verbose option does not change the behaviour of this command. That may +change in the future. + +</p><div class="codeblock"><pre><i>-- List contents of archive in current "main" db.</i>. +.ar --list +</pre></div> + +<a name="arinsup"></a> + +<h2 id="_sqlite_archive_insert_and_update_commands_"><span>15.4. </span> SQLite Archive Insert And Update Commands </h2> + +<p> The --update and --insert commands work like --create command, except that +they do not delete the current archive before commencing. New versions of +files silently replace existing files with the same names, but otherwise +the initial contents of the archive (if any) remain intact. + +</p><p> For the --insert command, all files listed are inserted into the archive. +For the --update command, files are only inserted if they do not previously +exist in the archive, or if their "mtime" or "mode" is different from what +is currently in the archive. + +</p><p> Compatibility node: Prior to SQLite version 3.28.0 (2019-04-16) only +the --update option was supported but that option worked like --insert in that +it always reinserted every file regardless of whether or not it had changed. + +</p><h2 id="_sqlite_archive_remove_command_"><span>15.5. </span> SQLite Archive Remove Command </h2> + +<p> The --remove command deletes files and directories which match the +provided arguments (if any) as affected by the --glob option. +It is an error to provide arguments which match nothing in the archive. + +</p><h2 id="_operations_on_zip_archives_"><span>15.6. </span> Operations On ZIP Archives </h2> + +<p>If FILE is a ZIP archive rather than an SQLite Archive, the ".archive" +command and the "-A" command-line option still work. This is accomplished +using of the <a href="zipfile.html">zipfile</a> extension. +Hence, the following commands are roughly equivalent, +differing only in output formatting: + +</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> + <tr style="text-align:left"><th>Traditional Command</th><th>Equivalent sqlite3.exe Command + </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>unzip archive.zip</td><td>sqlite3 -Axf archive.zip + </td></tr><tr style="text-align:left"><td>unzip -l archive.zip</td><td>sqlite3 -Atvf archive.zip + </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>zip -r archive2.zip dir</td><td>sqlite3 -Acf archive2.zip dir +</td></tr></table> + +<h2 id="_sql_used_to_implement_sqlite_archive_operations_"><span>15.7. </span> SQL Used To Implement SQLite Archive Operations </h2> + +<p>The various SQLite Archive Archive commands are implemented using SQL statements. +Application developers can easily add SQLite Archive Archive reading and writing +support to their own projects by running the appropriate SQL. + +</p><p>To see what SQL statements are used to implement an SQLite Archive +operation, add the --dryrun or -n option. This causes the SQL to be +displayed but inhibits the execution of the SQL. + +</p><p>The SQL statements used to implement SQLite Archive operations make use of +various <a href="loadext.html">loadable extensions</a>. These extensions are all available in +the <a href="https://sqlite.org/src">SQLite source tree</a> in the +<a href="https://sqlite.org/src/file/ext/misc">ext/misc/ subfolder</a>. +The extensions needed for full SQLite Archive support include: + +</p><ol> +<li><p> +<a href="https://sqlite.org/src/file/ext/misc/fileio.c">fileio.c</a> — +This extension adds SQL functions readfile() and writefile() for +reading and writing content from files on disk. The fileio.c +extension also includes fsdir() table-valued function for listing +the contents of a directory and the lsmode() function for converting +numeric st_mode integers from the stat() system call into human-readable +strings after the fashion of the "ls -l" command. + +</p></li><li><p> +<a href="https://sqlite.org/src/file/ext/misc/sqlar.c">sqlar.c</a> — +This extension adds the sqlar_compress() and sqlar_uncompress() +functions that are needed to compress and uncompress file content +as it is inserted and extracted from an SQLite Archive. + +</p></li><li><p> +<a href="zipfile.html">zipfile.c</a> — +This extension implements the "zipfile(FILE)" table-valued function +which is used to read ZIP archives. This extension is only needed +when reading ZIP archives instead of SQLite archives. + +</p></li><li><p> +<a href="https://sqlite.org/src/file/ext/misc/appendvfs.c">appendvfs.c</a> — +This extension implements a new <a href="vfs.html">VFS</a> that allows an SQLite database +to be appended to some other file, such as an executable. This +extension is only needed if the --append option to the .archive +command is used. +</p></li></ol> + +<a name="param"></a> + +<h1 id="sql_parameters"><span>16. </span>SQL Parameters</h1> + +<p>SQLite allows <a href="lang_expr.html#varparam">bound parameters</a> to appear in an SQL statement anywhere +that a literal value is allowed. The values for these parameters are set +using the <a href="c3ref/bind_blob.html">sqlite3_bind_...()</a> family of APIs. + +</p><p>Parameters can be either named or unnamed. An unnamed parameter is a single +question mark ("?"). Named parameters are a "?" followed immediately by a number +(ex: "?15" or "?123") or one of the characters "$", ":", or "@" followed by an +alphanumeric name (ex: "$var1", ":xyz", "@bingo"). + +</p><p>This command-line shell leaves unnamed parameters unbound, meaning that they +will have a value of an SQL NULL, but named parameters might be assigned values. +If there exists a TEMP table named "sqlite_parameters" with a schema like this: + +</p><div class="codeblock"><pre>CREATE TEMP TABLE sqlite_parameters( + key TEXT PRIMARY KEY, + value +) WITHOUT ROWID; +</pre></div> + +<p>And if there is an entry in that table where the key column exactly matches +the name of parameter (including the initial "?", "$", ":", or "@" character) +then the parameter is assigned the value of the value column. If no entry exists, +the parameter defaults to NULL. + +</p><p>The ".parameter" command exists to simplify managing this table. The +".parameter init" command (often abbreviated as just ".param init") creates +the temp.sqlite_parameters table if it does not already exist. The ".param list" +command shows all entries in the temp.sqlite_parameters table. The ".param clear" +command drops the temp.sqlite_parameters table. The ".param set KEY VALUE" and +".param unset KEY" commands create or delete entries from the +temp.sqlite_parameters table. + +</p><p>The VALUE passed to ".param set KEY VALUE" can be either a SQL literal +or any other SQL expression or query which can be evaluated to yield a value. +This allows values of differing types to be set. +If such evaluation fails, the provided VALUE is instead quoted and inserted +as text. +Because such initial evaluation may or may not fail depending upon +the VALUE content, the reliable way to get a text value is to enclose it +with single-quotes protected from the above-described command-tail parsing. +For example, (unless one intends a value of -1365):<br> +</p><div class="codeblock"><pre>.parameter init +.parameter set @phoneNumber "'202-456-1111'" +</pre></div> +<p>Note that the double-quotes serve to protect the single-quotes +and ensure that the quoted text is parsed as one argument. + +</p><p>The temp.sqlite_parameters table only provides values for parameters in the +command-line shell. The temp.sqlite_parameter table has no effect on queries +that are run directly using the SQLite C-language API. Individual applications +are expected to implement their own parameter binding. You can search for +"sqlite_parameters" in the +<a href="https://sqlite.org/src/file/src/shell.c.in">command-line shell source code</a> +to see how the command-line shell does parameter binding, and use that as +a hint for how to implement it yourself. + +<a name="expert"></a> + +</p><h1 id="index_recommendations_sqlite_expert_"><span>17. </span>Index Recommendations (SQLite Expert)</h1> + +<p><b>Note: This command is experimental. It may be removed or the +interface modified in incompatible ways at some point in the future. + +</b></p><p>For most non-trivial SQL databases, the key to performance is creating +the right SQL indexes. In this context "the right SQL indexes" means those +that cause the queries that an application needs to optimize run fast. The +".expert" command can assist with this by proposing indexes that might +assist with specific queries, were they present in the database. + +</p><p>The ".expert" command is issued first, followed by the SQL query +on a separate line. For example, consider the following session: + +</p><div class="codeblock"><pre>sqlite> CREATE TABLE x1(a, b, c); <i>-- Create table in database </i> +sqlite> .expert +sqlite> SELECT * FROM x1 WHERE a=? AND b>?; <i>-- Analyze this SELECT </i> +CREATE INDEX x1_idx_000123a7 ON x1(a, b); + +0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?) + +sqlite> CREATE INDEX x1ab ON x1(a, b); <i>-- Create the recommended index </i> +sqlite> .expert +sqlite> SELECT * FROM x1 WHERE a=? AND b>?; <i>-- Re-analyze the same SELECT </i> +(no new indexes) + +0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?) +</pre></div> + +<p>In the above, the user creates the database schema (a single table - "x1"), +and then uses the ".expert" command to analyze a query, in this case +"SELECT * FROM x1 WHERE a=? AND b>?". The shell tool recommends that the +user create a new index (index "x1_idx_000123a7") and outputs the plan +that the query would use in <a href="eqp.html">EXPLAIN QUERY PLAN</a> format. The user then creates +an index with an equivalent schema and runs the analysis on the same query +again. This time the shell tool does not recommend any new indexes, and +outputs the plan that SQLite will use for the query given the existing +indexes. + +</p><p>The ".expert" command accepts the following options: + +</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> +<tr style="text-align:left"><th> Option </th><th> Purpose +</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td> ‑‑verbose + </td><td> If present, output a more verbose report for each query analyzed. +</td></tr><tr style="text-align:left"><td> ‑‑sample PERCENT + </td><td> This parameter defaults to 0, causing the ".expert" command to + recommend indexes based on the query and database schema alone. + This is similar to the way the <a href="optoverview.html">SQLite query planner</a> selects + indexes for queries if the user has not run the <a href="lang_analyze.html">ANALYZE</a> command + on the database to generate data distribution statistics. + <div style="margin-top:1ex"> + If this option is passed a non-zero argument, the ".expert" command + generates similar data distribution statistics for all indexes + considered based on PERCENT percent of the rows currently stored in + each database table. For databases with unusual data distributions, + this may lead to better index recommendations, particularly if the + application intends to run ANALYZE. + <div style="margin-top:1ex"> + For small databases and modern CPUs, there is usually no reason not + to pass "--sample 100". However, gathering data distribution + statistics can be expensive for large database tables. If the + operation is too slow, try passing a smaller value for the --sample + option. +</div></div></td></tr></table> + +<p>The functionality described in this section may be integrated into other +applications or tools using the +<a href="http://www.sqlite.org/src/dir?ci=trunk&name=ext/expert"> +SQLite expert extension</a> code. + +</p><p>A database schema which incorporate SQL custom functions made available +via the extension load mechanism may need special provision to work with +the .expert feature. Because the feature uses additional connections to +implement its functionality, those custom functions must be made available +to those additional connections. This can be done by means of the extension +load/usage options described at +<a href="c3ref/auto_extension.html"> +Automatically Load Statically Linked Extensions</a> +and <a href="loadext.html#persist"> +Persistent Loadable Extensions</a>. + +<a name="dotconn"></a> + +</p><h1 id="working_with_multiple_database_connections"><span>18. </span>Working With Multiple Database Connections</h1> + +<p> +Beginning with version 3.37.0 (2021-11-27), the CLI has the ability to +hold multiple <a href="c3ref/sqlite3.html">database connections</a> open at once. Only one database connection +is active at a time. The inactive connections are still open but are idle. + +</p><p> +Use the ".connection" dot-command (often abbreviated as just ".conn") to see a +list of database connections and an indication of which one is currently active. +Each database connection is identified by an integer between 0 and 9. (There +can be at most 10 simultaneously open connections.) Change to another database +connection, creating it if it does not already exist, by typing the ".conn" +command followed by its number. Close a database connection by typing +".conn close N" where N is the connection number. + +</p><p> +Though the underlying SQLite database connections are completely independent +of one another, many of the CLI settings, such as the output format, are +shared across all database connections. Thus, changing the <a href="cli.html#dotmode">output mode</a> in +one connection will change it in them all. On the other hand, some +<a href="cli.html#dotcmd">dot-commands</a> such as <a href="cli.html#dotopen">.open</a> only affect the current connection. + +<a name="miscfeatures"></a> + +</p><h1 id="miscellaneous_extension_features"><span>19. </span>Miscellaneous Extension Features</h1> + +<p> +The CLI is built with several SQLite extensions that are not +included with the SQLite library. A few add features +not described in the preceding sections, namely: +</p><ul> +<li>the UINT collating sequence which treats +unsigned integers embedded in text according to +their value, along with other text, for ordering; +</li><li>decimal arithmetic as provided by the <a href="floatingpoint.html#decext">decimal extension</a>; +</li><li>the <a href="series.html">generate_series</a>() table-valued function; and +</li><li>support for POSIX extended regular expressions +bound to the <a href="lang_expr.html#regexp">REGEXP</a> operator. +</li></ul> + +<a name="dotother"></a> + +<h1 id="other_dot_commands"><span>20. </span>Other Dot Commands</h1> + +<p>There are many other dot-commands available in the command-line +shell. See the ".help" command for a complete list for any particular +version and build of SQLite. + +<a name="insh"></a> + +</p><h1 id="using_sqlite3_in_a_shell_script"><span>21. </span>Using sqlite3 in a shell script</h1> + +<p> +One way to use sqlite3 in a shell script is to use "echo" or +"cat" to generate a sequence of commands in a file, then invoke sqlite3 +while redirecting input from the generated command file. This +works fine and is appropriate in many circumstances. But as +an added convenience, sqlite3 allows a single SQL command to be +entered on the command line as a second argument after the +database name. When the sqlite3 program is launched with two +arguments, the second argument is passed to the SQLite library +for processing, the query results are printed on standard output +in list mode, and the program exits. This mechanism is designed +to make sqlite3 easy to use in conjunction with programs like +"awk". For example:</p> + +<div class="codeblock"><pre>$ <b>sqlite3 ex1 'select * from tbl1' \</b> +> <b> | awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'</b> +<tr><td>hello<td>10 +<tr><td>goodbye<td>20 +$ +</pre></div> + +<a name="endsh"></a> + +<h1 id="marking_the_end_of_an_sql_statement"><span>22. </span>Marking The End Of An SQL Statement</h1> + +<p> +SQLite commands are normally terminated by a semicolon. In the CLI +you can also use the word "GO" (case-insensitive) or a slash character +"/" on a line by itself to end a command. These are used by SQL Server +and Oracle, respectively, and are supported by the SQLite CLI for +compatibility. These won't work in <b>sqlite3_exec()</b>, +because the CLI translates these inputs into a semicolon before passing +them down into the SQLite core.</p> + +<a name="clopts"></a> + +<h1 id="command_line_options"><span>23. </span>Command-line Options</h1> + +<p> +There are many command-line options available to the CLI. Use the --help +command-line option to see a list: + +</p><div class="codeblock"><pre>$ <b>sqlite3 --help</b> +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. +OPTIONS include: + -A ARGS... run ".archive ARGS" and exit + -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 commands 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 + -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 + -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' + -version show SQLite version + -vfs NAME use NAME as the default VFS + -zip open the file as a ZIP Archive +</pre></div> + +<p>The CLI is flexible regarding command-line option formatting. +Either one or two leading "-" characters are permitted. +Thus "-box" and "--box" mean the same thing. +Command-line options are processed from left to right. +Hence a "--box" option will override a prior "--quote" option. + +</p><p> +Most of the command-line options are self-explanatory, but a few merit additional +discussion below. + +<a name="safemode"></a> + +</p><h2 id="the_safe_command_line_option"><span>23.1. </span>The --safe command-line option</h2> + +<p>The --safe command-line option attempts to disable all features of the CLI that +might cause any changes to the host computer other than changes to the specific database +file named on the command-line. The idea is that if you receive a large SQL script +from an unknown or untrusted source, you can run that script to see what it does without +risking an exploit by using the --safe option. The --safe option disables (among other +things): + +</p><ul> +<li> The <a href="cli.html#dotopen">.open command</a>, unless the --hexdb option is used or the filename is ":memory:". + This prevents the script from reading or writing any database files not named on + the original command-line. +</li><li> The <a href="lang_attach.html">ATTACH</a> SQL command. +</li><li> SQL functions that have potentially harmful side-effects, such as + edit(), fts3_tokenizer(), load_extension(), readfile() and writefile(). +</li><li> The <a href="cli.html#sqlar">.archive command</a>. +</li><li> The .backup and .save commands. +</li><li> The <a href="cli.html#csv">.import command</a>. +</li><li> The <a href="cli.html#dotload">.load command</a>. +</li><li> The .log command. +</li><li> The .shell and .system commands. +</li><li> The .excel, .once and .output commands. +</li><li> Other commands that can have deleterious side effects. + +</li></ul> + +<p>Basically, any feature of the CLI that reads or writes from a file on disk other +than the main database file is disabled. + +</p><h3 id="bypassing_safe_restrictions_for_specific_commands"><span>23.1.1. </span>Bypassing --safe restrictions for specific commands</h3> + +<p>If the "--nonce NONCE" option is also included on the command-line, for some +large and arbitrary NONCE string, then the ".nonce NONCE" command (with the +same large nonce string) will permit the next SQL statement or dot-command +to bypass the --safe restrictions. + +</p><p>Suppose you want to run a suspicious script and the script requires one or +two of the features that --safe normally disables. For example, suppose it +needs to ATTACH one additional database. Or suppose the script needs to load +a specific extension. This can be accomplished by preceding the (carefully +audited) ATTACH statement or the ".load" command with an appropriate ".nonce" +command and supplying the same nonce value using the "--nonce" command-line +option. Those specific commands will then be allowed to execute normally, +but all other unsafe commands will still be restricted. + +</p><p>The use of ".nonce" is dangerous in the sense that a mistake can allow a +hostile script to damage your system. Therefore, use ".nonce" carefully, +sparingly, and as a last resort when there are no other ways to get a +script to run under --safe mode. + +<a name="compiling"></a> + +</p><h1 id="compiling_the_sqlite3_program_from_sources"><span>24. </span>Compiling the sqlite3 program from sources</h1> + +<p> +To compile the command-line shell on unix systems and on Windows with MinGW, +the usual configure-make command works: + +</p><div class="codeblock"><pre>sh configure; make +</pre></div> + +<p> +The configure-make works whether you are building from the canonical sources +from the source tree, or from an amalgamated bundle. There are few +dependencies. When building from canonical sources, a working +<a href="https://www.tcl.tk/man/tcl8.3/UserCmd/tclsh.htm">tclsh</a> is required. +If using an amalgamation bundle, all the preprocessing work normally +done by tclsh will have already been carried out and only normal build +tools are required. + +</p><p> +A working <a href="https://zlib.net">zlib compression library</a> is +needed in order for the <a href="cli.html#sqlar">.archive command</a> to operate. + +</p><p> +On Windows with MSVC, use nmake with the Makefile.msc: + +</p><div class="codeblock"><pre>nmake /f Makefile.msc +</pre></div> + +<p> +For correct operation of the <a href="cli.html#sqlar">.archive command</a>, make a copy of the +<a href="https://zlib.net">zlib source code</a> into the compat/zlib subdirectory +of the source tree and compile this way: + +</p><div class="codeblock"><pre>nmake /f Makefile.msc USE_ZLIB=1 +</pre></div> + +<h2 id="_do_it_yourself_builds_"><span>24.1. </span> Do-It-Yourself Builds </h2> + +<p> +The source code to the sqlite3 command line interface is in a single +file named "shell.c". The shell.c source file is generated from other +sources, but most of the code for shell.c can be found in +<a href="https://sqlite.org/src/file/src/shell.c.in">src/shell.c.in</a>. +(Regenerate shell.c by typing "make shell.c" from the canonical source tree.) +<a href="howtocompile.html">Compile</a> the shell.c file (together +with the <a href="amalgamation.html">sqlite3 library source code</a>) to generate +the executable. For example:</p> + +<div class="codeblock"><pre>gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread -lz -lm +</pre></div> + +<p> +The following additional compile-time options are recommended in order to +provide a full-featured command-line shell: + +</p><ul> +<li> <a href="compile.html#threadsafe">-DSQLITE_THREADSAFE=0</a> +</li><li> <a href="compile.html#enable_explain_comments">-DSQLITE_ENABLE_EXPLAIN_COMMENTS</a> +</li><li> <a href="compile.html#have_zlib">-DSQLITE_HAVE_ZLIB</a> +</li><li> <a href="compile.html#introspection_pragmas">-DSQLITE_INTROSPECTION_PRAGMAS</a> +</li><li> <a href="compile.html#enable_unknown_sql_function">-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION</a> +</li><li> <a href="compile.html#enable_stmtvtab">-DSQLITE_ENABLE_STMTVTAB</a> +</li><li> <a href="compile.html#enable_dbpage_vtab">-DSQLITE_ENABLE_DBPAGE_VTAB</a> +</li><li> <a href="compile.html#enable_dbstat_vtab">-DSQLITE_ENABLE_DBSTAT_VTAB</a> +</li><li> <a href="compile.html#enable_offset_sql_func">-DSQLITE_ENABLE_OFFSET_SQL_FUNC</a> +</li><li> <a href="compile.html#enable_json1">-DSQLITE_ENABLE_JSON1</a> +</li><li> <a href="compile.html#enable_rtree">-DSQLITE_ENABLE_RTREE</a> +</li><li> <a href="compile.html#enable_fts4">-DSQLITE_ENABLE_FTS4</a> +</li><li> <a href="compile.html#enable_fts5">-DSQLITE_ENABLE_FTS5</a> +</li></ul> +<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/cli.in?m=c517a02dadecc02f8">2022-12-27 16:51:24</a> UTC </small></i></p> + |