| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>Command Line Shell For SQLite</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
Command Line Shell For SQLite |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#getting_started">1. Getting Started</a></div> |
| 126 |
<div class="fancy-toc2"><a href="#sqlite_command_line_program_versus_the_sqlite_library">1.1. SQLite command-line program versus the SQLite library</a></div> |
| 127 |
<div class="fancy-toc2"><a href="#gui_alternatives_to_the_cli">1.2. GUI Alternatives To The CLI</a></div> |
| 128 |
<div class="fancy-toc2"><a href="#starting_the_cli">1.3. Starting the CLI</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#double_click_startup_on_windows">1.4. Double-click Startup On Windows</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#running_the_cli_in_a_web_browser">1.5. Running The CLI In A Web Browser</a></div> |
| 131 |
<div class="fancy-toc1"><a href="#special_commands_dot_commands_">2. Special commands (dot-commands)</a></div> |
| 132 |
<div class="fancy-toc1"><a href="#rules_for_dot_commands_sql_and_more">3. Rules for dot-commands, SQL, and More</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#line_structure">3.1. Line Structure</a></div> |
| 134 |
<div class="fancy-toc2"><a href="#dot_command_arguments">3.2. Dot-command arguments</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#dot_commands_are_evaluated_separately">3.3. Dot-commands are evaluated separately</a></div> |
| 136 |
<div class="fancy-toc1"><a href="#output_formats">4. Output Formats</a></div> |
| 137 |
<div class="fancy-toc1"><a href="#querying_the_database_schema">5. Querying the database schema</a></div> |
| 138 |
<div class="fancy-toc1"><a href="#opening_database_files">6. Opening Database Files</a></div> |
| 139 |
<div class="fancy-toc1"><a href="#redirecting_i_o">7. Redirecting I/O</a></div> |
| 140 |
<div class="fancy-toc2"><a href="#writing_results_to_a_file">7.1. Writing results to a file</a></div> |
| 141 |
<div class="fancy-toc2"><a href="#reading_sql_from_a_file">7.2. Reading SQL from a file</a></div> |
| 142 |
<div class="fancy-toc2"><a href="#file_i_o_functions">7.3. File I/O Functions</a></div> |
| 143 |
<div class="fancy-toc2"><a href="#the_edit_sql_function">7.4. The edit() SQL function</a></div> |
| 144 |
<div class="fancy-toc2"><a href="#importing_files_as_csv_or_other_formats">7.5. Importing files as CSV or other formats</a></div> |
| 145 |
<div class="fancy-toc2"><a href="#export_to_csv">7.6. Export to CSV</a></div> |
| 146 |
<div class="fancy-toc3"><a href="#_export_to_excel_">7.6.1. Export to Excel </a></div> |
| 147 |
<div class="fancy-toc3"><a href="#_export_to_tsv_tab_separated_values_">7.6.2. Export to TSV (tab separated values)</a></div> |
| 148 |
<div class="fancy-toc1"><a href="#accessing_zip_archives_as_database_files">8. Accessing ZIP Archives As Database Files</a></div> |
| 149 |
<div class="fancy-toc2"><a href="#how_zip_archive_access_is_implemented">8.1. How ZIP archive access is implemented</a></div> |
| 150 |
<div class="fancy-toc1"><a href="#converting_an_entire_database_to_a_text_file">9. Converting An Entire Database To A Text File</a></div> |
| 151 |
<div class="fancy-toc1"><a href="#recover_data_from_a_corrupted_database">10. Recover Data From a Corrupted Database</a></div> |
| 152 |
<div class="fancy-toc1"><a href="#loading_extensions">11. Loading Extensions</a></div> |
| 153 |
<div class="fancy-toc1"><a href="#cryptographic_hashes_of_database_content">12. Cryptographic Hashes Of Database Content</a></div> |
| 154 |
<div class="fancy-toc1"><a href="#database_content_self_tests">13. Database Content Self-Tests</a></div> |
| 155 |
<div class="fancy-toc1"><a href="#sqlite_archive_support">14. SQLite Archive Support</a></div> |
| 156 |
<div class="fancy-toc2"><a href="#_sqlite_archive_create_command_">14.1. SQLite Archive Create Command </a></div> |
| 157 |
<div class="fancy-toc2"><a href="#_sqlite_archive_extract_command_">14.2. SQLite Archive Extract Command </a></div> |
| 158 |
<div class="fancy-toc2"><a href="#_sqlite_archive_list_command_">14.3. SQLite Archive List Command </a></div> |
| 159 |
<div class="fancy-toc2"><a href="#_sqlite_archive_insert_and_update_commands_">14.4. SQLite Archive Insert And Update Commands </a></div> |
| 160 |
<div class="fancy-toc2"><a href="#_sqlite_archive_remove_command_">14.5. SQLite Archive Remove Command </a></div> |
| 161 |
<div class="fancy-toc2"><a href="#_operations_on_zip_archives_">14.6. Operations On ZIP Archives </a></div> |
| 162 |
<div class="fancy-toc2"><a href="#_sql_used_to_implement_sqlite_archive_operations_">14.7. SQL Used To Implement SQLite Archive Operations </a></div> |
| 163 |
<div class="fancy-toc1"><a href="#sql_parameters">15. SQL Parameters</a></div> |
| 164 |
<div class="fancy-toc1"><a href="#index_recommendations_sqlite_expert_">16. Index Recommendations (SQLite Expert)</a></div> |
| 165 |
<div class="fancy-toc1"><a href="#working_with_multiple_database_connections">17. Working With Multiple Database Connections</a></div> |
| 166 |
<div class="fancy-toc1"><a href="#miscellaneous_extension_features">18. Miscellaneous Extension Features</a></div> |
| 167 |
<div class="fancy-toc1"><a href="#other_dot_commands">19. Other Dot Commands</a></div> |
| 168 |
<div class="fancy-toc1"><a href="#using_sqlite3_in_a_shell_script">20. Using sqlite3 in a shell script</a></div> |
| 169 |
<div class="fancy-toc1"><a href="#marking_the_end_of_an_sql_statement">21. Marking The End Of An SQL Statement</a></div> |
| 170 |
<div class="fancy-toc1"><a href="#more_details_on_how_to_start_the_cli">22. More Details On How To Start The CLI</a></div> |
| 171 |
<div class="fancy-toc2"><a href="#extra_command_line_arguments">22.1. Extra command-line arguments</a></div> |
| 172 |
<div class="fancy-toc2"><a href="#command_line_options">22.2. Command-line Options</a></div> |
| 173 |
<div class="fancy-toc2"><a href="#the_safe_command_line_option">22.3. The --safe command-line option</a></div> |
| 174 |
<div class="fancy-toc3"><a href="#bypassing_safe_restrictions_for_specific_commands">22.3.1. Bypassing --safe restrictions for specific commands</a></div> |
| 175 |
<div class="fancy-toc2"><a href="#the_unsafe_testing_command_line_option">22.4. The --unsafe-testing command-line option</a></div> |
| 176 |
<div class="fancy-toc2"><a href="#the_no_utf8_and_utf8_command_line_options">22.5. The --no-utf8 and --utf8 command-line options</a></div> |
| 177 |
<div class="fancy-toc1"><a href="#compiling_the_sqlite3_program_from_sources">23. Compiling the sqlite3 program from sources</a></div> |
| 178 |
<div class="fancy-toc2"><a href="#_do_it_yourself_builds_">23.1. Do-It-Yourself Builds </a></div> |
| 179 |
</div> |
| 180 |
</details> |
| 181 |
</div> |
| 182 |
|
| 183 |
|
| 184 |
|
| 185 |
|
| 186 |
<a name="intro"></a> |
| 187 |
|
| 188 |
<h1 id="getting_started"><span>1. </span>Getting Started</h1> |
| 189 |
|
| 190 |
<p>The SQLite project provides a command-line program named |
| 191 |
<b>sqlite3</b> (or <b>sqlite3.exe</b> on Windows) |
| 192 |
that allows the user to run SQL statements interactively |
| 193 |
against an SQLite database. This document provides a brief |
| 194 |
introduction on how to use the <b>sqlite3</b> program. |
| 195 |
|
| 196 |
<a name="clivlib"></a> |
| 197 |
|
| 198 |
</p><h2 id="sqlite_command_line_program_versus_the_sqlite_library"><span>1.1. </span>SQLite command-line program versus the SQLite library</h2> |
| 199 |
|
| 200 |
<p>The SQLite library is code that implements an SQL database engine. |
| 201 |
The "sqlite3" command-line program or "CLI" is an application that |
| 202 |
accepts user input and passes it down into the SQLite library for |
| 203 |
evaluation. Understand that these are two different things. When |
| 204 |
somebody says "SQLite" or "sqlite3" they might be referring to either |
| 205 |
the SQLite library itself, or the CLI that provides a human interface |
| 206 |
to the library. You will often need to use context to figure out exactly |
| 207 |
which of these two things the speaker is referring to. |
| 208 |
|
| 209 |
</p><p>This document is about the CLI, not the underlying SQLite library. |
| 210 |
|
| 211 |
<a name="guis"></a> |
| 212 |
|
| 213 |
</p><h2 id="gui_alternatives_to_the_cli"><span>1.2. </span>GUI Alternatives To The CLI</h2> |
| 214 |
|
| 215 |
<p>The <b>sqlite3</b> program is written by and for the core SQLite |
| 216 |
developers and is the officially supported way to accessing |
| 217 |
SQLite database files interactively. |
| 218 |
However, some users might prefer a Graphical User Interface (GUI). |
| 219 |
Several such programs are available from third-parties. |
| 220 |
One of those is |
| 221 |
<a href="https://visualdb.com/sqlite">Visual DB</a>, |
| 222 |
a sponsor of the SQLite project: |
| 223 |
|
| 224 |
</p><blockquote> |
| 225 |
<a href="https://visualdb.com/sqlite"> |
| 226 |
<img src="images/foreignlogos/visualdb.png" width="200"> |
| 227 |
</a> |
| 228 |
</blockquote> |
| 229 |
|
| 230 |
<p>Thanks to Visual DB for helping us make SQLite better for everyone! |
| 231 |
|
| 232 |
<a name="startup"></a> |
| 233 |
|
| 234 |
</p><h2 id="starting_the_cli"><span>1.3. </span>Starting the CLI</h2> |
| 235 |
|
| 236 |
<p>Start the <b>sqlite3</b> program by typing "sqlite3" at the |
| 237 |
command prompt, optionally followed |
| 238 |
by the name of the file that holds the SQLite database |
| 239 |
(or <a href="#zipdb">ZIP archive</a>). If the named |
| 240 |
file does not exist, a new database file with the given name will be |
| 241 |
created automatically. If no database file is specified on the |
| 242 |
command-line, a transient in-memory database is used. This in-memory |
| 243 |
database is deleted when the program exits. |
| 244 |
|
| 245 |
</p><p>On startup, the <b>sqlite3</b> program will show a brief banner |
| 246 |
message then prompt you to enter SQL. Type in SQL statements (terminated |
| 247 |
by a semicolon), press "Enter" and the SQL will be executed.</p> |
| 248 |
|
| 249 |
<p>For example, to create a new SQLite database named "ex1.db" |
| 250 |
with a single table named "tbl1", you might do this:</p> |
| 251 |
|
| 252 |
<div class="codeblock"><pre>$ <b>sqlite3 ex1.db</b> |
| 253 |
SQLite version 3.36.0 2021-06-18 18:36:39 |
| 254 |
Enter ".help" for usage hints. |
| 255 |
sqlite> <b>create table tbl1(one text, two int);</b> |
| 256 |
sqlite> <b>insert into tbl1 values('hello!',10),('goodbye',20);</b> |
| 257 |
sqlite> <b>select * from tbl1;</b> |
| 258 |
┌───────────┬─────┐ |
| 259 |
│ one │ two │ |
| 260 |
├───────────┼─────┤ |
| 261 |
│ 'hello!' │ 10 │ |
| 262 |
│ 'goodbye' │ 20 │ |
| 263 |
└───────────┴─────┘ |
| 264 |
sqlite> |
| 265 |
</pre></div> |
| 266 |
|
| 267 |
<p>Terminate the sqlite3 program by typing your system |
| 268 |
End-Of-File character (usually a Control-D). Use the interrupt |
| 269 |
character (usually a Control-C) to stop a long-running SQL statement.</p> |
| 270 |
|
| 271 |
<p>Make sure you type a semicolon at the end of each SQL command! |
| 272 |
The sqlite3 program looks for a semicolon to know when your SQL command is |
| 273 |
complete. If you omit the semicolon, sqlite3 will give you a |
| 274 |
continuation prompt and wait for you to enter more text to |
| 275 |
complete the SQL command. This feature allows you to |
| 276 |
enter SQL commands that span multiple lines. For example:</p> |
| 277 |
|
| 278 |
|
| 279 |
<div class="codeblock"><pre>sqlite> <b>CREATE TABLE tbl2 (</b> |
| 280 |
...> <b> f1 varchar(30) primary key,</b> |
| 281 |
...> <b> f2 text,</b> |
| 282 |
...> <b> f3 real</b> |
| 283 |
...> <b>);</b> |
| 284 |
sqlite> |
| 285 |
</pre></div> |
| 286 |
|
| 287 |
<a name="dblclick"></a> |
| 288 |
|
| 289 |
<h2 id="double_click_startup_on_windows"><span>1.4. </span>Double-click Startup On Windows</h2> |
| 290 |
|
| 291 |
<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause |
| 292 |
the command-line shell to pop-up a terminal window running SQLite. However, |
| 293 |
because double-clicking starts the sqlite3.exe without command-line arguments, |
| 294 |
no database file will have been specified, so SQLite will use a transient |
| 295 |
in-memory database that is deleted when the session exits. |
| 296 |
To use a persistent disk file as the database, enter the ".open" command |
| 297 |
immediately after the terminal window starts up: |
| 298 |
|
| 299 |
</p><div class="codeblock"><pre>SQLite version 3.36.0 2021-06-18 18:36:39 |
| 300 |
Enter ".help" for usage hints. |
| 301 |
Connected to a transient in-memory database. |
| 302 |
Use ".open FILENAME" to reopen on a persistent database. |
| 303 |
sqlite> <b>.open ex1.db</b> |
| 304 |
sqlite> |
| 305 |
</pre></div> |
| 306 |
|
| 307 |
<p>The example above causes the database file named "ex1.db" to be opened |
| 308 |
and used. The "ex1.db" file is created if it does not previously exist. |
| 309 |
You might want to |
| 310 |
use a full pathname to ensure that the file is in the directory that you |
| 311 |
think it is in. Use forward-slashes as the directory separator character. |
| 312 |
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p> |
| 313 |
|
| 314 |
<p>Alternatively, you can create a new database using the default temporary |
| 315 |
storage, then save that database into a disk file using the ".save" command: |
| 316 |
|
| 317 |
</p><div class="codeblock"><pre>SQLite version 3.36.0 2021-06-18 18:36:39 |
| 318 |
Enter ".help" for usage hints. |
| 319 |
Connected to a transient in-memory database. |
| 320 |
Use ".open FILENAME" to reopen on a persistent database. |
| 321 |
sqlite> <i>... many SQL commands omitted ...</i> |
| 322 |
sqlite> <b>.save ex1.db</b> |
| 323 |
sqlite> |
| 324 |
</pre></div> |
| 325 |
|
| 326 |
<p>Be careful when using the ".save" command as it will overwrite any |
| 327 |
preexisting database files having the same name without prompting for |
| 328 |
confirmation. As with the ".open" command, you might want to use a |
| 329 |
full pathname with forward-slash directory separators to avoid ambiguity. |
| 330 |
|
| 331 |
</p><h2 id="running_the_cli_in_a_web_browser"><span>1.5. </span>Running The CLI In A Web Browser</h2> |
| 332 |
|
| 333 |
<p>It is possible to compile the CLI using |
| 334 |
<a href="https://en.wikipedia.org/wiki/Emscripten">Emscripten</a> |
| 335 |
so that it runs inside a web-browser. You can experiment with |
| 336 |
recent versions of the CLI at |
| 337 |
<a href="https://sqlite.org/fiddle">https://sqlite.org/fiddle</a>. |
| 338 |
A web-browser tab is not a general-purpose computer, and so not |
| 339 |
all features of the CLI are available in "fiddle". But you can |
| 340 |
use "fiddle" as a sandbox in which to try out SQL commands. |
| 341 |
|
| 342 |
<a name="dotcmd"></a> |
| 343 |
|
| 344 |
</p><h1 id="special_commands_dot_commands_"><span>2. </span>Special commands (dot-commands)</h1> |
| 345 |
|
| 346 |
<p> |
| 347 |
Most of the time, sqlite3 just reads lines of SQL input and passes |
| 348 |
those lines through into the SQLite library for evaluation. |
| 349 |
But input lines that begin with a dot (".") |
| 350 |
are intercepted and interpreted by the sqlite3 program itself. |
| 351 |
These "dot commands" are typically used to change the output format |
| 352 |
of queries, or to execute certain prepackaged query statements. |
| 353 |
There were originally just a few dot commands, but over the years |
| 354 |
many new features have accumulated so that today there are over 60. |
| 355 |
</p> |
| 356 |
|
| 357 |
<p> |
| 358 |
For a listing of the available dot commands, you can enter |
| 359 |
"<tt>.help</tt>" with no arguments. Or enter |
| 360 |
"<tt>.help </tt><i>TOPIC</i>" for detailed information |
| 361 |
about TOPIC. |
| 362 |
The list of available dot-commands for SQLite version |
| 363 |
3.52.0 is as follows: |
| 364 |
</p> |
| 365 |
|
| 366 |
<div class="codeblock"><pre>sqlite> <b>.help</b> |
| 367 |
.archive ... Manage SQL archives |
| 368 |
.auth ON|OFF Show authorizer callbacks |
| 369 |
.backup ?DB? FILE Backup DB (default "main") to FILE |
| 370 |
.bail on|off Stop after hitting an error. Default OFF |
| 371 |
.cd DIRECTORY Change the working directory to DIRECTORY |
| 372 |
.changes on|off Show number of rows changed by SQL |
| 373 |
.check GLOB Fail if output since .testcase does not match |
| 374 |
.clone NEWDB Clone data into NEWDB from the existing database |
| 375 |
.connection [close] [#] Open or close an auxiliary database connection |
| 376 |
.crlf ?on|off? Whether or not to use \r\n line endings |
| 377 |
.databases List names and files of attached databases |
| 378 |
.dbconfig ?op? ?val? List or change sqlite3_db_config() options |
| 379 |
.dbinfo ?DB? Show status information about the database |
| 380 |
.dbtotxt Hex dump of the database file |
| 381 |
.dump ?OBJECTS? Render database content as SQL |
| 382 |
.echo on|off Turn command echo on or off |
| 383 |
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN |
| 384 |
.excel Display the output of next command in spreadsheet |
| 385 |
.exit ?CODE? Exit this program with return-code CODE |
| 386 |
.expert EXPERIMENTAL. Suggest indexes for queries |
| 387 |
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto |
| 388 |
.filectrl CMD ... Run various sqlite3_file_control() operations |
| 389 |
.fullschema ?--indent? Show schema and the content of sqlite_stat tables |
| 390 |
.help ?-all? ?PATTERN? Show help text for PATTERN |
| 391 |
.import FILE TABLE Import data from FILE into TABLE |
| 392 |
.imposter INDEX TABLE Create imposter table TABLE on index INDEX |
| 393 |
.indexes ?TABLE? Show names of indexes |
| 394 |
.intck ?STEPS_PER_UNLOCK? Run an incremental integrity check on the db |
| 395 |
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT |
| 396 |
.lint OPTIONS Report potential schema issues. |
| 397 |
.load FILE ?ENTRY? Load an extension library |
| 398 |
.log FILE|on|off Turn logging on or off. FILE can be stderr/stdout |
| 399 |
.mode ?MODE? ?OPTIONS? Set output mode |
| 400 |
.nonce STRING Suspend safe mode for one command if nonce matches |
| 401 |
.nullvalue STRING Use STRING in place of NULL values |
| 402 |
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE |
| 403 |
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE |
| 404 |
.output ?FILE? Send output to FILE or stdout if FILE is omitted |
| 405 |
.parameter CMD ... Manage SQL parameter bindings |
| 406 |
.print STRING... Print literal STRING |
| 407 |
.progress N Invoke progress handler after every N opcodes |
| 408 |
.prompt MAIN CONTINUE Replace the standard prompts |
| 409 |
.quit Stop interpreting input stream, exit if primary. |
| 410 |
.read FILE Read input from FILE or command output |
| 411 |
.recover Recover as much data as possible from corrupt db. |
| 412 |
.restore ?DB? FILE Restore content of DB (default "main") from FILE |
| 413 |
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...) |
| 414 |
.scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off |
| 415 |
.schema ?PATTERN? Show the CREATE statements matching PATTERN |
| 416 |
.session ?NAME? CMD ... Create or control sessions |
| 417 |
.sha3sum ... Compute a SHA3 hash of database content |
| 418 |
.shell CMD ARGS... Run CMD ARGS... in a system shell |
| 419 |
.stats ?ARG? Show stats or turn stats on or off |
| 420 |
.system CMD ARGS... Run CMD ARGS... in a system shell |
| 421 |
.tables ?TABLE? List names of tables matching LIKE pattern TABLE |
| 422 |
.timeout MS Try opening locked tables for MS milliseconds |
| 423 |
.timer on|off Turn SQL timer on or off |
| 424 |
.trace ?OPTIONS? Output each SQL statement as it is run |
| 425 |
.unmodule NAME ... Unregister virtual table modules |
| 426 |
.version Show source, library and compiler versions |
| 427 |
.vfsinfo ?AUX? Information about the top-level VFS |
| 428 |
.vfslist List all available VFSes |
| 429 |
.vfsname ?AUX? Print the name of the VFS stack |
| 430 |
.www Display output of the next command in web browser |
| 431 |
sqlite> |
| 432 |
</pre></div> |
| 433 |
|
| 434 |
<p>In addition to the dot-commands shown by "<tt>.help</tt>", there are |
| 435 |
undocumented commands used for testing and deprecated commands |
| 436 |
that are retained for backwards compatibility.</p> |
| 437 |
|
| 438 |
<p>Most dot-commands can be abbreviated. For example ".q" is a |
| 439 |
common abbreviation for ".quit". |
| 440 |
|
| 441 |
<a name="dotrules"></a> |
| 442 |
|
| 443 |
</p><h1 id="rules_for_dot_commands_sql_and_more"><span>3. </span>Rules for dot-commands, SQL, and More</h1> |
| 444 |
|
| 445 |
<h2 id="line_structure"><span>3.1. </span>Line Structure</h2> |
| 446 |
|
| 447 |
<p>The CLI's input is a mixed sequence of: |
| 448 |
</p><ul> |
| 449 |
<li>SQL statements</li> |
| 450 |
<li>dot-commands</li> |
| 451 |
<li>CLI comments</li> |
| 452 |
</ul> |
| 453 |
<p>SQL statements are free-form, and can be spread across multiple lines, |
| 454 |
with whitespace or SQL comments embedded anywhere. |
| 455 |
They are terminated by either a ';' character at the end of an input line, |
| 456 |
or a '/' character or the word "go" on a line by itself. |
| 457 |
When not at the end of an input line, the ';' character |
| 458 |
acts to separate SQL statements. |
| 459 |
Trailing whitespace is ignored for purposes of termination. |
| 460 |
</p><p>Dot-command have a specific syntax: |
| 461 |
</p><ul> |
| 462 |
<li> Dot-commands must begin with a "." at the left margin |
| 463 |
with no preceding whitespace.</li> |
| 464 |
<li> Dot-commands must be entirely contained on a single input line. |
| 465 |
</li><li> Dot-commands cannot occur in the middle of an ordinary SQL |
| 466 |
statement. In other words, dot-commands cannot occur at a |
| 467 |
continuation prompt.</li> |
| 468 |
<li> There is no comment syntax for dot-commands.</li> |
| 469 |
<li> Bare (unquoted) semicolons at the end of dot commands are ignored |
| 470 |
(in version 3.52.0 and after).</li> |
| 471 |
</ul> |
| 472 |
|
| 473 |
<p>The CLI also accepts whole-line comments that |
| 474 |
begin with a '#' character and extend to the end of the line. |
| 475 |
There can be no whitespace prior to the initial '#'. |
| 476 |
|
| 477 |
</p><h2 id="dot_command_arguments"><span>3.2. </span>Dot-command arguments</h2> |
| 478 |
|
| 479 |
<p>Dot-commands can be followed by zero or more space-separated |
| 480 |
arguments. Arguments are parsed according to these rules: |
| 481 |
</p><ol> |
| 482 |
<li> Any trailing whitespace and a final ";" are removed, if present. |
| 483 |
</li><li> Arguments are separated from each other and from the initial |
| 484 |
dot-command itself by whitespace. |
| 485 |
</li><li> Text within <tt>'...'</tt> is treated as a single argument with |
| 486 |
the <tt>'</tt> delimiters removed, even if the text contains white space. |
| 487 |
</li><li> Text within <tt>"..."</tt> is treated as a single argument with |
| 488 |
the <tt>"</tt> delimiters removed. |
| 489 |
</li><li> C-style backslash escapes (ex: <tt>\\</tt>, <tt>\n</tt>, |
| 490 |
<tt>\r</tt>, <tt>\"</tt>, <tt>\033</tt>, etc.) work only within |
| 491 |
double-quoted arguments. |
| 492 |
</li></ol> |
| 493 |
|
| 494 |
<h2 id="dot_commands_are_evaluated_separately"><span>3.3. </span>Dot-commands are evaluated separately</h2> |
| 495 |
|
| 496 |
<p>The dot-commands |
| 497 |
are interpreted by the sqlite3.exe command-line program, not by |
| 498 |
the SQLite library. So dot-commands will not work as an argument |
| 499 |
to core SQLite library interfaces such as <a href="c3ref/prepare.html">sqlite3_prepare()</a> |
| 500 |
or <a href="c3ref/exec.html">sqlite3_exec()</a>. |
| 501 |
|
| 502 |
</p><h1 id="output_formats"><span>4. </span>Output Formats</h1> |
| 503 |
|
| 504 |
<p>The CLI can show SQL query results in a rich variety of |
| 505 |
formats. The <a href="climode.html">.mode command</a> is used to control the formatting |
| 506 |
of query results. The details on how the .mode command works |
| 507 |
are extensive and are hence <a href="climode.html">covered in a separate document</a>. |
| 508 |
|
| 509 |
</p><p>Without going into too much detail, here are a few quick examples |
| 510 |
to show how the .mode command works: |
| 511 |
|
| 512 |
</p><blockquote> |
| 513 |
<table border="0" cellpadding="8" cellspacing="0"> |
| 514 |
<tr> |
| 515 |
<td valign="top"><b>.mode box</b> |
| 516 |
</td><td>  |
| 517 |
</td><td> Show query results in a grid formed using Unicode |
| 518 |
box drawing characters. |
| 519 |
</td></tr> |
| 520 |
<tr> |
| 521 |
<td valign="top"><b>.mode quote</b> |
| 522 |
</td><td> |
| 523 |
</td><td> Show query results as lines of comma-separated |
| 524 |
SQL literals, one line per row of output. |
| 525 |
</td></tr> |
| 526 |
<tr> |
| 527 |
<td valign="top"><b>.mode csv</b> |
| 528 |
</td><td> |
| 529 |
</td><td> Show query results as CSV ("Comma-Separated Values"). |
| 530 |
</td></tr> |
| 531 |
<tr> |
| 532 |
<td valign="top"><b>.mode --list</b> |
| 533 |
</td><td> |
| 534 |
</td><td> Show a list of available output modes |
| 535 |
</td></tr> |
| 536 |
<tr> |
| 537 |
<td valign="top"><b>.mode</b> |
| 538 |
</td><td> |
| 539 |
</td><td> Show the current output mode |
| 540 |
</td></tr> |
| 541 |
<tr> |
| 542 |
<td valign="top"><b><nobr>.mode --once box</nobr></b> |
| 543 |
</td><td> |
| 544 |
</td><td> Show the next SQL statement using "box" mode but then |
| 545 |
automatically revert back to the current mode. |
| 546 |
</td></tr> |
| 547 |
</table> |
| 548 |
</blockquote> |
| 549 |
|
| 550 |
<a name="schema"></a> |
| 551 |
|
| 552 |
<h1 id="querying_the_database_schema"><span>5. </span>Querying the database schema</h1> |
| 553 |
|
| 554 |
<p>The sqlite3 program provides several convenience commands that |
| 555 |
are useful for looking at the schema of the database. There is |
| 556 |
nothing that these commands do that cannot be done by some other |
| 557 |
means. These commands are provided purely as a shortcut.</p> |
| 558 |
|
| 559 |
<a name="dtables"></a> |
| 560 |
|
| 561 |
<p>For example, to see a list of the tables in the database, you |
| 562 |
can enter ".tables".</p> |
| 563 |
|
| 564 |
|
| 565 |
<div class="codeblock"><pre>sqlite> <b>.tables</b> |
| 566 |
tbl1 tbl2 |
| 567 |
sqlite> |
| 568 |
</pre></div> |
| 569 |
|
| 570 |
|
| 571 |
<p>The ".tables" command is similar to setting list mode then |
| 572 |
executing the following query:</p> |
| 573 |
|
| 574 |
<div class="codeblock"><pre>SELECT name FROM sqlite_schema |
| 575 |
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' |
| 576 |
ORDER BY 1 |
| 577 |
</pre></div> |
| 578 |
|
| 579 |
<p>But the ".tables" command does more. It queries the <a href="schematab.html">sqlite_schema</a> table |
| 580 |
for all <a href="lang_attach.html">attached</a> databases, not just the primary database. And it arranges |
| 581 |
its output into neat columns. |
| 582 |
|
| 583 |
</p><p>The ".indexes" command works in a similar way to list all of |
| 584 |
the indexes. If the ".indexes" command is given an argument which is |
| 585 |
the name of a table, then it shows just indexes on that table. |
| 586 |
|
| 587 |
<a name="dschema"></a> |
| 588 |
|
| 589 |
</p><p>The ".schema" command shows the complete schema for the database, |
| 590 |
or for a single table if an optional tablename argument is provided: |
| 591 |
|
| 592 |
</p><div class="codeblock"><pre>sqlite> <b>.schema</b> |
| 593 |
create table tbl1(one varchar(10), two smallint) |
| 594 |
CREATE TABLE tbl2 ( |
| 595 |
f1 varchar(30) primary key, |
| 596 |
f2 text, |
| 597 |
f3 real |
| 598 |
); |
| 599 |
sqlite> <b>.schema tbl2</b> |
| 600 |
CREATE TABLE tbl2 ( |
| 601 |
f1 varchar(30) primary key, |
| 602 |
f2 text, |
| 603 |
f3 real |
| 604 |
); |
| 605 |
sqlite> |
| 606 |
</pre></div> |
| 607 |
|
| 608 |
|
| 609 |
<p>The ".schema" command is roughly the same as setting |
| 610 |
list mode, then entering the following query:</p> |
| 611 |
|
| 612 |
<div class="codeblock"><pre>SELECT sql FROM sqlite_schema |
| 613 |
ORDER BY tbl_name, type DESC, name |
| 614 |
</pre></div> |
| 615 |
|
| 616 |
<p>As with ".tables", the ".schema" command shows the schema for |
| 617 |
all <a href="lang_attach.html">attached</a> databases. If you only want to see the schema for |
| 618 |
a single database (perhaps "main") then you can add an argument |
| 619 |
to ".schema" to restrict its output: |
| 620 |
|
| 621 |
</p><div class="codeblock"><pre>sqlite> <b>.schema main.*</b> |
| 622 |
</pre></div> |
| 623 |
|
| 624 |
<p>The ".schema" command can be augmented with the "--indent" option, |
| 625 |
in which case it tries to reformat the various CREATE statements of |
| 626 |
the schema so that they are more easily readable by humans. |
| 627 |
|
| 628 |
<a name="dotdatabases"></a> |
| 629 |
|
| 630 |
</p><p>The ".databases" command shows a list of all databases open in |
| 631 |
the current connection. There will always be at least 2. The first |
| 632 |
one is "main", the original database opened. The second is "temp", |
| 633 |
the database used for temporary tables. There may be additional |
| 634 |
databases listed for databases attached using the ATTACH statement. |
| 635 |
The first output column is the name the database is attached with, |
| 636 |
and the second result column is the filename of the external file. |
| 637 |
There may be a third result column which will be either "'r/o'" or |
| 638 |
"'r/w'" depending on whether the database file is read-only or read-write. |
| 639 |
And there might be a fourth result column showing the result of |
| 640 |
<a href="c3ref/txn_state.html">sqlite3_txn_state()</a> for that database file. |
| 641 |
|
| 642 |
</p><div class="codeblock"><pre>sqlite> <b>.databases</b> |
| 643 |
</pre></div> |
| 644 |
|
| 645 |
<a name="fullschema"></a> |
| 646 |
|
| 647 |
<p>The ".fullschema" dot-command works like the ".schema" command in |
| 648 |
that it displays the entire database schema. But ".fullschema" also |
| 649 |
includes dumps of the statistics tables "sqlite_stat1", "sqlite_stat3", |
| 650 |
and "sqlite_stat4", if they exist. The ".fullschema" command normally |
| 651 |
provides all of the information needed to exactly recreate a query |
| 652 |
plan for a specific query. When reporting suspected problems with |
| 653 |
the SQLite query planner to the SQLite development team, developers |
| 654 |
are requested to provide the complete ".fullschema" output as part |
| 655 |
of the trouble report. Note that the sqlite_stat3 and sqlite_stat4 |
| 656 |
tables contain samples of index entries and so might contain sensitive |
| 657 |
data, so do not send the ".fullschema" output of a proprietary database |
| 658 |
over a public channel.</p> |
| 659 |
|
| 660 |
<a name="dotopen"></a> |
| 661 |
|
| 662 |
<h1 id="opening_database_files"><span>6. </span>Opening Database Files</h1> |
| 663 |
|
| 664 |
<p>The ".open" command opens a new database connection, after first closing the |
| 665 |
previously opened database command. In its simplest form, the ".open" command merely |
| 666 |
invokes <a href="c3ref/open.html">sqlite3_open()</a> on the file named as its argument. Use the name ":memory:" |
| 667 |
to open a new in-memory database that disappears when the CLI exits or when the |
| 668 |
".open" command is run again. |
| 669 |
Or use no name to open a private, temporary on-disk database which |
| 670 |
will also disappear upon exit or use of ".open". |
| 671 |
|
| 672 |
</p><p>If the --new option is included with ".open", then the database is reset prior |
| 673 |
to being opened. Any prior data is destroyed. This is a destructive overwrite of |
| 674 |
prior data and no confirmation is requested, so use this option carefully. |
| 675 |
|
| 676 |
</p><p>If the --ifexists option is included, the ".open" command only works if the |
| 677 |
database file already exists. In other words, --ifexists prevents a new, empty |
| 678 |
database from being created. |
| 679 |
|
| 680 |
</p><p>The --readonly option opens the database in read-only mode. Write will be |
| 681 |
prohibited. |
| 682 |
|
| 683 |
</p><p>The --deserialize option causes the entire content of the on-disk file to be |
| 684 |
read into memory and then opened as an in-memory database using the |
| 685 |
<a href="c3ref/deserialize.html">sqlite3_deserialize()</a> interface. This will, of course, require a lot of memory |
| 686 |
if you have a large database. Also, any changes you make to the database will not |
| 687 |
be saved back to disk unless you explicitly save them using the ".save" or ".backup" |
| 688 |
commands. |
| 689 |
|
| 690 |
</p><p>The --append option causes the SQLite database to be appended to an existing |
| 691 |
file rather than working as a stand-alone file. See the |
| 692 |
<a href="https://sqlite.org/src/file/ext/misc/appendvfs.c">appendvfs extension</a> for |
| 693 |
more information. |
| 694 |
|
| 695 |
</p><p>The --zip option causes the specified input file to be interpreted as a ZIP archive |
| 696 |
instead of as an SQLite database file. |
| 697 |
|
| 698 |
</p><p>The --hexdb option causes the database content to be read from subsequent |
| 699 |
lines of input in a hex format, rather than from a separate file on disk. |
| 700 |
The ".dbtotxt" dot-command and/or the dbtotxt command-line tool can be used to generate |
| 701 |
the appropriate text for a database. The --hexdb option is intended for use by the |
| 702 |
SQLite developers for testing purposes. We do not know of any use cases for this |
| 703 |
option outside of internal SQLite testing and development. |
| 704 |
|
| 705 |
</p><h1 id="redirecting_i_o"><span>7. </span>Redirecting I/O</h1> |
| 706 |
|
| 707 |
<a name="dotoutput"></a> |
| 708 |
|
| 709 |
<h2 id="writing_results_to_a_file"><span>7.1. </span>Writing results to a file</h2> |
| 710 |
|
| 711 |
<p>By default, sqlite3 sends query results to standard output. You |
| 712 |
can change this using the ".output" and ".once" commands. Just put |
| 713 |
the name of an output file as an argument to .output and all subsequent |
| 714 |
query results will be written to that file. Or use the .once command |
| 715 |
instead of .output and output will only be redirected for the single next |
| 716 |
command before reverting to the console. Use .output with no arguments to |
| 717 |
begin writing to standard output again. For example:</p> |
| 718 |
|
| 719 |
<div class="codeblock"><pre>sqlite> <b>.mode list --colsep "|"</b> |
| 720 |
sqlite> <b>.output test_file_1.txt</b> |
| 721 |
sqlite> <b>select * from tbl1;</b> |
| 722 |
sqlite> <b>.exit</b> |
| 723 |
$ <b>cat test_file_1.txt</b> |
| 724 |
hello|10 |
| 725 |
goodbye|20 |
| 726 |
$ |
| 727 |
</pre></div> |
| 728 |
|
| 729 |
<p>If the first character of the ".output" or ".once" filename is a pipe |
| 730 |
symbol ("|") then the remaining characters are treated as a command and the |
| 731 |
output is sent to that command. This makes it easy to pipe the results |
| 732 |
of a query into some other process. For example, the |
| 733 |
"open -f" command on a Mac opens a text editor to display the content that |
| 734 |
it reads from standard input. So to see the results of a query |
| 735 |
in a text editor, one could type:</p> |
| 736 |
|
| 737 |
<div class="codeblock"><pre>sqlite> <b>.once | open -f</b> |
| 738 |
sqlite> <b>SELECT * FROM bigTable;</b> |
| 739 |
</pre></div> |
| 740 |
|
| 741 |
<p>If the ".output" or ".once" commands have an argument of "-e" then |
| 742 |
output is collected into a temporary file and the system text editor is |
| 743 |
invoked on that text file. Thus, the command ".once -e" achieves the |
| 744 |
same result as ".once '|open -f'" but with the benefit of being portable |
| 745 |
across all systems. |
| 746 |
|
| 747 |
</p><p>If the ".output" or ".once" commands have a "-x" argument, that causes |
| 748 |
them to accumulate output as Comma-Separated-Values (CSV) in a temporary |
| 749 |
file, then invoke the default system utility for viewing CSV files |
| 750 |
(usually a spreadsheet program) on the result. This is a quick way of |
| 751 |
sending the result of a query to a spreadsheet for easy viewing: |
| 752 |
|
| 753 |
</p><div class="codeblock"><pre>sqlite> <b>.once -x</b> |
| 754 |
sqlite> <b>SELECT * FROM bigTable;</b> |
| 755 |
</pre></div> |
| 756 |
|
| 757 |
<a name="dotexcel"></a> |
| 758 |
|
| 759 |
<p>The ".excel" command is an alias for ".once -x". It does exactly the same |
| 760 |
thing. |
| 761 |
|
| 762 |
<a name="dotwww"></a> |
| 763 |
|
| 764 |
|
| 765 |
</p><p>The "-w" option to ".output" or ".once" causes the output to be displayed |
| 766 |
in your web-browser. The ".www" command is an alias for ".once -w". Normally |
| 767 |
the data shown in the web-browser is in the form of an HTML table, but you |
| 768 |
can instead show it as plain text by adding the "--plain" argument. |
| 769 |
|
| 770 |
</p><div class="codeblock"><pre>sqlite> <b>.www</b> |
| 771 |
sqlite> <b>SELECT * FROM users WHERE email LIKE '%@aol.com';</b> |
| 772 |
</pre></div> |
| 773 |
|
| 774 |
<a name="dotread"></a> |
| 775 |
|
| 776 |
<h2 id="reading_sql_from_a_file"><span>7.2. </span>Reading SQL from a file</h2> |
| 777 |
|
| 778 |
<p>In interactive mode, sqlite3 reads input text (either SQL statements |
| 779 |
or <a href="cli.html#dotcmd">dot-commands</a>) from the keyboard. You can also redirect input from |
| 780 |
a file when you launch sqlite3, of course, but then you do not have the |
| 781 |
ability to interact with the program. Sometimes it is useful to run an |
| 782 |
SQL script contained in a file while entering other commands from the |
| 783 |
command-line. |
| 784 |
For this, the ".read" dot-command is provided. |
| 785 |
|
| 786 |
</p><p>The ".read" command takes a single argument which is (usually) the name |
| 787 |
of a file from which to read input text. |
| 788 |
|
| 789 |
</p><div class="codeblock"><pre>sqlite> <b>.read myscript.sql</b> |
| 790 |
</pre></div> |
| 791 |
|
| 792 |
<p>The ".read" command temporarily stops reading from the keyboard and instead |
| 793 |
takes its input from the file named. Upon reaching the end of the file, |
| 794 |
input reverts back to the keyboard. The script file may contain dot-commands, |
| 795 |
just like ordinary interactive input. |
| 796 |
|
| 797 |
</p><p>If the argument to ".read" begins with the "|" character, then instead of |
| 798 |
opening the argument as a file, it runs the argument (without the leading "|") |
| 799 |
as a command, then uses the output of that command as its input. Thus, if you |
| 800 |
have a script that generates SQL, you can execute that SQL directly using |
| 801 |
a command similar to the following: |
| 802 |
|
| 803 |
</p><div class="codeblock"><pre>sqlite> <b>.read |myscript.bat</b> |
| 804 |
</pre></div> |
| 805 |
|
| 806 |
|
| 807 |
<a name="fileio"></a> |
| 808 |
|
| 809 |
<h2 id="file_i_o_functions"><span>7.3. </span>File I/O Functions</h2> |
| 810 |
|
| 811 |
<p>The command-line shell adds two <a href="appfunc.html">application-defined SQL functions</a> that |
| 812 |
facilitate reading content from a file into a table column, and writing the |
| 813 |
content of a column into a file, respectively. |
| 814 |
|
| 815 |
</p><p>The readfile(X) SQL function reads the entire content of the file named |
| 816 |
X and returns that content as a BLOB. This can be used to load content into |
| 817 |
a table. For example: |
| 818 |
|
| 819 |
</p><div class="codeblock"><pre>sqlite> <b>CREATE TABLE images(name TEXT, type TEXT, img BLOB);</b> |
| 820 |
sqlite> <b>INSERT INTO images(name,type,img</b>) |
| 821 |
...> <b> VALUES('icon','jpeg',readfile('icon.jpg'));</b> |
| 822 |
</pre></div> |
| 823 |
|
| 824 |
<p>The writefile(X,Y) SQL function write the blob Y into the file named X |
| 825 |
and returns the number of bytes written. Use this function to extract |
| 826 |
the content of a single table column into a file. For example: |
| 827 |
|
| 828 |
</p><div class="codeblock"><pre>sqlite> <b>SELECT writefile('icon.jpg',img) FROM images WHERE name='icon';</b> |
| 829 |
</pre></div> |
| 830 |
|
| 831 |
<p>Note that the readfile(X) and writefile(X,Y) functions are extension |
| 832 |
functions and are not built into the core SQLite library. These routines |
| 833 |
are available as a <a href="loadext.html">loadable extension</a> in the |
| 834 |
<a href="https://sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c">ext/misc/fileio.c</a> |
| 835 |
source file in the <a href="download.html#srctree">SQLite source code repositories</a>. |
| 836 |
|
| 837 |
<a name="editfunc"></a> |
| 838 |
|
| 839 |
</p><h2 id="the_edit_sql_function"><span>7.4. </span>The edit() SQL function</h2> |
| 840 |
|
| 841 |
<p>The CLI has another built-in SQL function named edit(). Edit() takes |
| 842 |
one or two arguments. The first argument is a value - often a large |
| 843 |
multi-line string to be edited. The second argument is the invocation |
| 844 |
for a text editor. (It may include options to affect the editor's |
| 845 |
behavior.) If the second argument is omitted, the VISUAL environment |
| 846 |
variable is used. The edit() function writes its first argument into a |
| 847 |
temporary file, invokes the editor on the temporary file, rereads the file |
| 848 |
back into memory after the editor is done, then returns the edited text. |
| 849 |
|
| 850 |
</p><p>The edit() function can be used to make changes to large text |
| 851 |
values. For example: |
| 852 |
|
| 853 |
</p><div class="codeblock"><pre>sqlite> <b>UPDATE docs SET body=edit(body) WHERE name='report-15';</b> |
| 854 |
</pre></div> |
| 855 |
|
| 856 |
<p>In this example, the content of the docs.body field for the entry where |
| 857 |
docs.name is "report-15" will be sent to the editor. After the editor returns, |
| 858 |
the result will be written back into the docs.body field. |
| 859 |
|
| 860 |
</p><p>The default operation of edit() is to invoke a text editor. But by using |
| 861 |
an alternative edit program in the second argument, you can also get it to edit |
| 862 |
images or other non-text resources. For example, if you want to modify a JPEG |
| 863 |
image that happens to be stored in a field of a table, you could run: |
| 864 |
|
| 865 |
</p><div class="codeblock"><pre>sqlite> <b>UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';</b> |
| 866 |
</pre></div> |
| 867 |
|
| 868 |
<p>The edit program can also be used as a viewer, by simply ignoring the |
| 869 |
return value. For example, to merely look at the image above, you might run: |
| 870 |
|
| 871 |
</p><div class="codeblock"><pre>sqlite> <b>SELECT length(edit(img,'gimp')) WHERE id='pic-1542';</b> |
| 872 |
</pre></div> |
| 873 |
|
| 874 |
<a name="csv"></a> |
| 875 |
|
| 876 |
<h2 id="importing_files_as_csv_or_other_formats"><span>7.5. </span>Importing files as CSV or other formats</h2> |
| 877 |
|
| 878 |
<p>Use the ".import" command to import CSV (comma separated value) |
| 879 |
or similarly delimited data into an SQLite table. |
| 880 |
The ".import" command takes two arguments which are the |
| 881 |
source from which data is to be read and the name of the |
| 882 |
SQLite table into which the data is to be inserted. The source argument |
| 883 |
is the name of a file to be read or, if it begins with a "|" character, |
| 884 |
it specifies a command which will be run to produce the input data. |
| 885 |
|
| 886 |
</p><p>Note that it may be important to set the "mode" before running the |
| 887 |
".import" command. This is prudent to prevent the command-line shell |
| 888 |
from trying to interpret the input file text as some format other than |
| 889 |
how the file is structured. If the --csv or --ascii options are used, |
| 890 |
they control import input delimiters. Otherwise, the delimiters are |
| 891 |
those in effect for the current output mode. |
| 892 |
|
| 893 |
</p><p>To import into a table not in the "main" schema, the --schema option |
| 894 |
may be used to specify that the table is in some other schema. This can |
| 895 |
be useful for ATTACH'ed databases or to import into a TEMP table. |
| 896 |
|
| 897 |
</p><p>When .import is run, its treatment of the first input row depends |
| 898 |
upon whether the target table already exists. If it does not exist, |
| 899 |
the table is automatically created and the content of the first input |
| 900 |
row is used to set the name of all the columns in the table. In this |
| 901 |
case, the table data content is taken from the second and subsequent |
| 902 |
input rows. If the target table already exists, every row of the |
| 903 |
input, including the first, is taken to be actual data content. If |
| 904 |
the input file contains an initial row of column labels, you can make |
| 905 |
the .import command skip that initial row using the "--skip 1" option. |
| 906 |
|
| 907 |
</p><p>Here is an example usage, loading a pre-existing temporary table |
| 908 |
from a CSV file which has column names in its first row: |
| 909 |
|
| 910 |
</p><div class="codeblock"><pre>sqlite> <b>.import --csv --skip 1 --schema temp C:/work/somedata.csv tab1</b> |
| 911 |
</pre></div> |
| 912 |
|
| 913 |
<p>While reading input data in modes other than 'ascii', ".import" |
| 914 |
interprets input as records composed of fields according to the RFC 4180 |
| 915 |
specification with this exception: The input record and field separators |
| 916 |
are as set by the <a href="climode.html#opt-rowsep">--rowsep option</a> and <a href="climode.html#opt-colsep">--colsep option</a> of the |
| 917 |
<a href="climode.html">.mode command</a>. Fields are always subject to quote removal to reverse |
| 918 |
quoting done per RFC 4180, except in --ascii mode. |
| 919 |
To import data with arbitrary delimiters and no quoting, |
| 920 |
use the --ascii option together with --colsep and --rowsep |
| 921 |
options to define the delimiters. |
| 922 |
|
| 923 |
<a name="csvout"></a> |
| 924 |
|
| 925 |
</p><h2 id="export_to_csv"><span>7.6. </span>Export to CSV</h2> |
| 926 |
|
| 927 |
<p>To export an SQLite table (or part of a table) as CSV, simply set |
| 928 |
the "mode" to "csv" and then run a query to extract the desired rows |
| 929 |
of the table. The output will be formatted as CSV per RFC 4180. |
| 930 |
|
| 931 |
</p><div class="codeblock"><pre>sqlite> <b>.mode csv --titles on</b> |
| 932 |
sqlite> <b>.once c:/work/dataout.csv</b> |
| 933 |
sqlite> <b>SELECT * FROM tab1;</b> |
| 934 |
sqlite> <b>.system c:/work/dataout.csv</b> |
| 935 |
</pre></div> |
| 936 |
|
| 937 |
<p>In the example above, the "--titles on" option causes column labels to |
| 938 |
be printed as the first row of output. This means that the first row of |
| 939 |
the resulting CSV file will contain column labels. If column labels are |
| 940 |
not desired, use "--titles off" instead. (The "--titles off" setting is |
| 941 |
the default and can be omitted if the headers have not been previously |
| 942 |
turned on.) |
| 943 |
|
| 944 |
</p><p>The line ".once <i>FILENAME</i>" causes all query output to go into |
| 945 |
the named file instead of being printed on the console. In the example |
| 946 |
above, that line causes the CSV content to be written into a file named |
| 947 |
"C:/work/dataout.csv". |
| 948 |
|
| 949 |
</p><p>The final line of the example (the ".system c:/work/dataout.csv") |
| 950 |
has the same effect as double-clicking on the c:/work/dataout.csv file |
| 951 |
in windows. This will typically bring up a spreadsheet program to display |
| 952 |
the CSV file. |
| 953 |
|
| 954 |
</p><p>That command only works as written on Windows. |
| 955 |
The equivalent line on a Mac would be: |
| 956 |
|
| 957 |
</p><div class="codeblock"><pre>sqlite> <b>.system open dataout.csv</b> |
| 958 |
</pre></div> |
| 959 |
|
| 960 |
<p>On Linux and other unix systems you will need to enter something like: |
| 961 |
|
| 962 |
|
| 963 |
</p><div class="codeblock"><pre>sqlite> <b>.system xdg-open dataout.csv</b> |
| 964 |
</pre></div> |
| 965 |
|
| 966 |
<a name="exexcel*"></a> |
| 967 |
|
| 968 |
<h3 id="_export_to_excel_"><span>7.6.1. </span> Export to Excel </h3> |
| 969 |
|
| 970 |
<p>To simplify export to a spreadsheet, the CLI provides the |
| 971 |
".excel" command which captures the output of a single query and sends |
| 972 |
that output to the default spreadsheet program on the host computer. |
| 973 |
Use it like this: |
| 974 |
|
| 975 |
</p><div class="codeblock"><pre>sqlite> <b>.excel</b> |
| 976 |
sqlite> <b>SELECT * FROM tab;</b> |
| 977 |
</pre></div> |
| 978 |
|
| 979 |
<p> |
| 980 |
The command above writes the output of the query as CSV into a temporary |
| 981 |
file, invokes the default handler for CSV files (usually the preferred |
| 982 |
spreadsheet program such as Excel or LibreOffice), then deletes the |
| 983 |
temporary file. This is essentially a short-hand method of doing |
| 984 |
the sequence of ".csv", ".once", and ".system" commands described above. |
| 985 |
|
| 986 |
</p><p> |
| 987 |
The ".excel" command is really an alias for ".once -x". The -x option |
| 988 |
to .once causes it to writes results as CSV into a temporary file that |
| 989 |
is named with a ".csv" suffix, then invoke the systems default handler |
| 990 |
for CSV files. |
| 991 |
|
| 992 |
</p><p> |
| 993 |
There is also a ".once -e" command which works similarly, except that |
| 994 |
it names the temporary file with a ".txt" suffix so that the default |
| 995 |
text editor for the system will be invoked, instead of the default |
| 996 |
spreadsheet. |
| 997 |
|
| 998 |
<a name="extsv*"></a> |
| 999 |
|
| 1000 |
</p><h3 id="_export_to_tsv_tab_separated_values_"><span>7.6.2. </span> Export to TSV (tab separated values)</h3> |
| 1001 |
|
| 1002 |
<p> |
| 1003 |
Exporting to pure TSV, without any field quoting, can be done by |
| 1004 |
entering ".mode tabs" before running a query. However, the output |
| 1005 |
will not be read correctly in tabs mode by the ".import" command |
| 1006 |
if it contains doublequote characters. To get TSV quoted per |
| 1007 |
RFC 4180 so that it can be input in tabs mode with ".import", run: |
| 1008 |
|
| 1009 |
</p><div class="codeblock"><pre>.mode csv -colsep "\t" |
| 1010 |
</pre></div> |
| 1011 |
|
| 1012 |
<a name="zipdb"></a> |
| 1013 |
|
| 1014 |
<h1 id="accessing_zip_archives_as_database_files"><span>8. </span>Accessing ZIP Archives As Database Files</h1> |
| 1015 |
|
| 1016 |
<p>In addition to reading and writing SQLite database files, |
| 1017 |
the <b>sqlite3</b> program will also read and write ZIP archives. |
| 1018 |
Simply specify a ZIP archive filename in place of an SQLite database |
| 1019 |
filename on the initial command line, or in the ".open" command, |
| 1020 |
and <b>sqlite3</b> will automatically detect that the file is a |
| 1021 |
ZIP archive instead of an SQLite database and will open it as such. |
| 1022 |
This works regardless of file suffix. So you can open JAR, DOCX, |
| 1023 |
and ODP files and any other file format that is really a ZIP |
| 1024 |
archive and SQLite will read it for you. |
| 1025 |
|
| 1026 |
</p><p>A ZIP archive appears to be a database containing a single table |
| 1027 |
with the following schema: |
| 1028 |
|
| 1029 |
</p><div class="codeblock"><pre>CREATE TABLE zip( |
| 1030 |
name, -- Name of the file |
| 1031 |
mode, -- Unix-style file permissions |
| 1032 |
mtime, -- Timestamp, seconds since 1970 |
| 1033 |
sz, -- File size after decompression |
| 1034 |
rawdata, -- Raw compressed file data |
| 1035 |
data, -- Uncompressed file content |
| 1036 |
method -- ZIP compression method code |
| 1037 |
); |
| 1038 |
</pre></div> |
| 1039 |
|
| 1040 |
<p>So, for example, if you wanted to see the compression efficiency |
| 1041 |
(expressed as the size of the compressed content relative to the |
| 1042 |
original uncompressed file size) for all files in the ZIP archive, |
| 1043 |
sorted from most compressed to least compressed, you could run a |
| 1044 |
query like this: |
| 1045 |
|
| 1046 |
</p><div class="codeblock"><pre>sqlite> SELECT name, (100.0*length(rawdata))/sz FROM zip ORDER BY 2; |
| 1047 |
</pre></div> |
| 1048 |
|
| 1049 |
<p>Or using <a href="cli.html#fileio">file I/O functions</a>, you can extract elements of the |
| 1050 |
ZIP archive: |
| 1051 |
|
| 1052 |
</p><div class="codeblock"><pre>sqlite> SELECT writefile(name,content) FROM zip |
| 1053 |
...> WHERE name LIKE 'docProps/%'; |
| 1054 |
</pre></div> |
| 1055 |
|
| 1056 |
<h2 id="how_zip_archive_access_is_implemented"><span>8.1. </span>How ZIP archive access is implemented</h2> |
| 1057 |
|
| 1058 |
<p>The command-line shell uses the <a href="zipfile.html">Zipfile virtual table</a> to |
| 1059 |
access ZIP archives. You can see this by running the ".schema" |
| 1060 |
command when a ZIP archive is open: |
| 1061 |
|
| 1062 |
</p><div class="codeblock"><pre>sqlite> .schema |
| 1063 |
CREATE VIRTUAL TABLE zip USING zipfile('document.docx') |
| 1064 |
/* zip(name,mode,mtime,sz,rawdata,data,method) */; |
| 1065 |
</pre></div> |
| 1066 |
|
| 1067 |
<p>When opening a file, if the command-line client discovers that the |
| 1068 |
file is a ZIP archive instead of an SQLite database, it actually opens |
| 1069 |
an <a href="inmemorydb.html">in-memory database</a> and then in that in-memory database it creates |
| 1070 |
an instance of the <a href="zipfile.html">Zipfile virtual table</a> that is attached to the |
| 1071 |
ZIP archive. |
| 1072 |
|
| 1073 |
</p><p>The special processing for opening ZIP archives is a trick of the |
| 1074 |
command-line shell, not the core SQLite library. So if you want to |
| 1075 |
open a ZIP archive as a database in your application, you will need to |
| 1076 |
activate the <a href="zipfile.html">Zipfile virtual table</a> module then run an appropriate |
| 1077 |
<a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. |
| 1078 |
|
| 1079 |
|
| 1080 |
<a name="dump"></a> |
| 1081 |
|
| 1082 |
</p><h1 id="converting_an_entire_database_to_a_text_file"><span>9. </span>Converting An Entire Database To A Text File</h1> |
| 1083 |
|
| 1084 |
<p>Use the ".dump" command to convert the entire contents of a |
| 1085 |
database into a single UTF-8 text file. This file can be converted |
| 1086 |
back into a database by piping it back into <b>sqlite3</b>.</p> |
| 1087 |
|
| 1088 |
<p>A good way to make an archival copy of a database is this:</p> |
| 1089 |
|
| 1090 |
|
| 1091 |
<div class="codeblock"><pre>$ <b>sqlite3 ex1 .dump | gzip -c >ex1.dump.gz</b> |
| 1092 |
</pre></div> |
| 1093 |
|
| 1094 |
|
| 1095 |
<p>This generates a file named <b>ex1.dump.gz</b> that contains everything |
| 1096 |
you need to reconstruct the database at a later time, or on another |
| 1097 |
machine. To reconstruct the database, just type:</p> |
| 1098 |
|
| 1099 |
|
| 1100 |
<div class="codeblock"><pre>$ <b>zcat ex1.dump.gz | sqlite3 ex2</b> |
| 1101 |
</pre></div> |
| 1102 |
|
| 1103 |
|
| 1104 |
<p>The text format is pure SQL so you |
| 1105 |
can also use the .dump command to export an SQLite database |
| 1106 |
into other popular SQL database engines. Like this:</p> |
| 1107 |
|
| 1108 |
|
| 1109 |
<div class="codeblock"><pre>$ <b>createdb ex2</b> |
| 1110 |
$ <b>sqlite3 ex1 .dump | psql ex2</b> |
| 1111 |
</pre></div> |
| 1112 |
|
| 1113 |
<a name="recover"></a> |
| 1114 |
|
| 1115 |
<h1 id="recover_data_from_a_corrupted_database"><span>10. </span>Recover Data From a Corrupted Database</h1> |
| 1116 |
|
| 1117 |
<p>Like the ".dump" command, ".recover" attempts to convert the entire |
| 1118 |
contents of a database file to text. The difference is that instead of |
| 1119 |
reading data using the normal SQL database interface, ".recover" |
| 1120 |
attempts to reassemble the database based on data extracted directly from |
| 1121 |
as many database pages as possible. If the database is corrupt, ".recover" |
| 1122 |
is usually able to recover data from all uncorrupted parts of the database, |
| 1123 |
whereas ".dump" stops when the first sign of corruption is encountered. |
| 1124 |
|
| 1125 |
</p><p>If the ".recover" command recovers one or more rows that it cannot |
| 1126 |
attribute to any database table, the output script creates a "lost_and_found" |
| 1127 |
table to store the orphaned rows. The schema of the lost_and_found |
| 1128 |
table is as follows: |
| 1129 |
|
| 1130 |
</p><div class="codeblock"><pre>CREATE TABLE lost_and_found( |
| 1131 |
rootpgno INTEGER, -- root page of tree pgno is a part of |
| 1132 |
pgno INTEGER, -- page number row was found on |
| 1133 |
nfield INTEGER, -- number of fields in row |
| 1134 |
id INTEGER, -- value of rowid field, or NULL |
| 1135 |
c0, c1, c2, c3... -- columns for fields of row |
| 1136 |
); |
| 1137 |
</pre></div> |
| 1138 |
|
| 1139 |
<p>The "lost_and_found" table contains one row for each orphaned row recovered |
| 1140 |
from the database. Additionally, there is one row for each recovered index |
| 1141 |
entry that cannot be attributed to any SQL index. This is because, in an |
| 1142 |
SQLite database, the same format is used to store SQL index entries and |
| 1143 |
WITHOUT ROWID table entries. |
| 1144 |
|
| 1145 |
</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> |
| 1146 |
<tr style="text-align:left"><th style="width:15ex">Column</th><th>Contents |
| 1147 |
</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>rootpgno</td><td> Even though it may not be possible to attribute the |
| 1148 |
row to a specific database table, it may be part of a tree structure |
| 1149 |
within the database file. In this case, the root page number of that |
| 1150 |
tree structure is stored in this column. Or, if the page the row was |
| 1151 |
found on is not part of a tree structure, this column stores a copy of |
| 1152 |
the value in column "pgno" - the page number of the page the row was |
| 1153 |
found on. In many, although not all, cases, all rows in the |
| 1154 |
lost_and_found table with the same value in this column belong to the |
| 1155 |
same table. |
| 1156 |
|
| 1157 |
</td></tr><tr style="text-align:left"><td>pgno</td><td> The page number of the page on which this row was found. |
| 1158 |
|
| 1159 |
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>nfield</td><td> The number of fields in this row. |
| 1160 |
|
| 1161 |
</td></tr><tr style="text-align:left"><td>id</td><td> If the row comes from a WITHOUT ROWID table, this column |
| 1162 |
contains NULL. Otherwise, it contains the 64-bit integer rowid value for |
| 1163 |
the row. |
| 1164 |
|
| 1165 |
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>c0, c1, c2...</td><td> The values for each column of the row |
| 1166 |
are stored in these columns. The ".recover" command creates the |
| 1167 |
lost_and_found table with as many columns as required by the longest |
| 1168 |
orphaned row. |
| 1169 |
</td></tr></table> |
| 1170 |
|
| 1171 |
<p>If the recovered database schema already contains a table named |
| 1172 |
"lost_and_found", the ".recover" command uses the name "lost_and_found0". If |
| 1173 |
the name "lost_and_found0" is also already taken, "lost_and_found1", and so |
| 1174 |
on. The default name "lost_and_found" may be overridden by invoking ".recover" |
| 1175 |
with the --lost-and-found switch. For example, to have the output script call |
| 1176 |
the table "orphaned_rows": |
| 1177 |
|
| 1178 |
</p><div class="codeblock"><pre>sqlite> .recover --lost-and-found orphaned_rows |
| 1179 |
</pre></div> |
| 1180 |
|
| 1181 |
<a name="dotload"></a> |
| 1182 |
|
| 1183 |
<h1 id="loading_extensions"><span>11. </span>Loading Extensions</h1> |
| 1184 |
|
| 1185 |
<p>You can add new custom <a href="appfunc.html">application-defined SQL functions</a>, |
| 1186 |
<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 |
| 1187 |
shell at run-time using the ".load" command. First, build the |
| 1188 |
extension as a DLL or shared library (as described in the |
| 1189 |
<a href="loadext.html">Run-Time Loadable Extensions</a> document) then type: |
| 1190 |
|
| 1191 |
</p><div class="codeblock"><pre>sqlite> .load /path/to/my_extension |
| 1192 |
</pre></div> |
| 1193 |
|
| 1194 |
<p>Note that SQLite automatically adds the appropriate extension suffix |
| 1195 |
(".dll" on windows, ".dylib" on Mac, ".so" on most other unixes) to the |
| 1196 |
extension filename. It is generally a good idea to specify the full |
| 1197 |
pathname of the extension. |
| 1198 |
|
| 1199 |
</p><p>SQLite computes the entry point for the extension based on the extension |
| 1200 |
filename. To override this choice, simply add the name of the extension |
| 1201 |
as a second argument to the ".load" command. |
| 1202 |
|
| 1203 |
</p><p>Source code for several useful extensions can be found in the |
| 1204 |
<a href="https://sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a> |
| 1205 |
subdirectory of the SQLite source tree. You can use these extensions |
| 1206 |
as-is, or as a basis for creating your own custom extensions to address |
| 1207 |
your own particular needs. |
| 1208 |
|
| 1209 |
|
| 1210 |
<a name="sha3sum"></a> |
| 1211 |
|
| 1212 |
</p><h1 id="cryptographic_hashes_of_database_content"><span>12. </span>Cryptographic Hashes Of Database Content</h1> |
| 1213 |
|
| 1214 |
<p>The ".sha3sum" dot-command computes a |
| 1215 |
<a href="https://en.wikipedia.org/wiki/SHA-3">SHA3</a> hash of the <em>content</em> |
| 1216 |
of the database. To be clear, the hash is computed over the database content, |
| 1217 |
not its representation on disk. This means, for example, that a <a href="lang_vacuum.html">VACUUM</a> |
| 1218 |
or similar data-preserving transformation does not change the hash. |
| 1219 |
|
| 1220 |
</p><p>The ".sha3sum" command supports options "--sha3-224", "--sha3-256", |
| 1221 |
"--sha3-384", and "--sha3-512" to define which variety of SHA3 to use |
| 1222 |
for the hash. The default is SHA3-256. |
| 1223 |
|
| 1224 |
</p><p>The database schema (in the <a href="schematab.html">sqlite_schema</a> table) is not normally |
| 1225 |
included in the hash, but can be added by the "--schema" option. |
| 1226 |
|
| 1227 |
</p><p>The ".sha3sum" command takes a single optional argument which is a |
| 1228 |
<a href="lang_expr.html#like">LIKE</a> pattern. If this option is present, only tables whose names match |
| 1229 |
the <a href="lang_expr.html#like">LIKE</a> pattern will be hashed. |
| 1230 |
|
| 1231 |
</p><p>The ".sha3sum" command is implemented with the help of the |
| 1232 |
<a href="https://sqlite.org/src/file/ext/misc/shathree.c">extension function "sha3_query()"</a> |
| 1233 |
that is included with the command-line shell. |
| 1234 |
|
| 1235 |
<a name="selftest"></a> |
| 1236 |
|
| 1237 |
</p><h1 id="database_content_self_tests"><span>13. </span>Database Content Self-Tests</h1> |
| 1238 |
|
| 1239 |
<p>The ".selftest" command attempts to verify that a database is |
| 1240 |
intact and is not corrupt. |
| 1241 |
The .selftest command looks for a table in schema named "selftest" |
| 1242 |
and defined as follows: |
| 1243 |
|
| 1244 |
</p><div class="codeblock"><pre>CREATE TABLE selftest( |
| 1245 |
tno INTEGER PRIMARY KEY, -- Test number |
| 1246 |
op TEXT, -- 'run' or 'memo' |
| 1247 |
cmd TEXT, -- SQL command to run, or text of "memo" |
| 1248 |
ans TEXT -- Expected result of the SQL command |
| 1249 |
); |
| 1250 |
</pre></div> |
| 1251 |
|
| 1252 |
<p>The .selftest command reads the rows of the selftest table in |
| 1253 |
selftest.tno order. |
| 1254 |
For each 'memo' row, it writes the text in 'cmd' to the output. For |
| 1255 |
each 'run' row, it runs the 'cmd' text as SQL and compares the result |
| 1256 |
to the value in 'ans', and shows an error message if the results differ. |
| 1257 |
|
| 1258 |
</p><p>If there is no selftest table, the ".selftest" command runs |
| 1259 |
<a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>. |
| 1260 |
|
| 1261 |
</p><p>The ".selftest --init" command creates the selftest table if it |
| 1262 |
does not already exist, then appends entries that check the SHA3 |
| 1263 |
hash of the content of all tables. Subsequent runs of ".selftest" |
| 1264 |
will verify that the database has not been changed in any way. To |
| 1265 |
generate tests to verify that a subset of the tables is unchanged, |
| 1266 |
simply run ".selftest --init" then <a href="lang_delete.html">DELETE</a> the selftest rows that |
| 1267 |
refer to tables that are not constant. |
| 1268 |
|
| 1269 |
<a name="sqlar"></a> |
| 1270 |
|
| 1271 |
</p><h1 id="sqlite_archive_support"><span>14. </span>SQLite Archive Support</h1> |
| 1272 |
|
| 1273 |
<p>The ".archive" dot-command and the "-A" command-line option |
| 1274 |
provide built-in support for the |
| 1275 |
<a href="sqlar.html">SQLite Archive format</a>. The interface is similar to |
| 1276 |
that of the "tar" command on unix systems. Each invocation of the ".ar" |
| 1277 |
command must specify a single command option. The following commands |
| 1278 |
are available for ".archive": |
| 1279 |
|
| 1280 |
</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> |
| 1281 |
<tr style="text-align:left"><th style="width:15ex">Option</th><th style="width:17ex">Long Option</th><th>Purpose |
| 1282 |
</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-c</td><td>--create</td><td>Create a new archive containing specified files. |
| 1283 |
</td></tr><tr style="text-align:left"><td>-x</td><td>--extract</td><td>Extract specified files from archive. |
| 1284 |
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-i</td><td>--insert</td><td>Add files to existing archive. |
| 1285 |
</td></tr><tr style="text-align:left"><td>-r</td><td>--remove</td><td>Remove files from the archive. |
| 1286 |
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-t</td><td>--list</td><td>List the files in the archive. |
| 1287 |
</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. |
| 1288 |
</td></tr></table> |
| 1289 |
|
| 1290 |
<p>As well as the command option, each invocation of ".ar" may specify |
| 1291 |
one or more modifier options. Some modifier options require an argument, |
| 1292 |
some do not. The following modifier options are available: |
| 1293 |
|
| 1294 |
</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> |
| 1295 |
<tr style="text-align:left"><th style="width:15ex">Option</th><th style="width:17ex">Long Option</th><th>Purpose |
| 1296 |
</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-v</td><td>--verbose</td><td>List each file as it is processed. |
| 1297 |
</td></tr><tr style="text-align:left"><td>-f FILE</td><td>--file FILE</td><td>If specified, use file FILE as the |
| 1298 |
archive. Otherwise, assume that the current "main" database is the |
| 1299 |
archive to be operated on. |
| 1300 |
</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 |
| 1301 |
archive, but open the file using the |
| 1302 |
<a href="https://sqlite.org/src/file/ext/misc/appendvfs.c">apndvfs VFS</a> so that |
| 1303 |
the archive will be appended to the end of FILE if FILE already exists. |
| 1304 |
</td></tr><tr style="text-align:left"><td>-C DIR</td><td>--directory DIR</td><td>If specified, interpret all relative |
| 1305 |
paths as relative to DIR, instead of the current working directory. |
| 1306 |
</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 |
| 1307 |
against names in the archive. |
| 1308 |
</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 |
| 1309 |
archive operation, but do not actually change anything. |
| 1310 |
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>--</td><td>--</td><td>All subsequent command line words are command arguments, |
| 1311 |
not options. |
| 1312 |
</td></tr></table> |
| 1313 |
|
| 1314 |
<p> |
| 1315 |
For command-line usage, add the short style command-line options immediately |
| 1316 |
following the "-A", without an intervening space. All subsequent arguments |
| 1317 |
are considered to be part of the .archive command. For example, the following |
| 1318 |
commands are equivalent: |
| 1319 |
|
| 1320 |
</p><div class="codeblock"><pre>sqlite3 new_archive.db -Acv file1 file2 file3 |
| 1321 |
sqlite3 new_archive.db ".ar -cv file1 file2 file3" |
| 1322 |
</pre></div> |
| 1323 |
|
| 1324 |
<p> |
| 1325 |
Long and short style options may be mixed. For example, the following are |
| 1326 |
equivalent: |
| 1327 |
|
| 1328 |
</p><div class="codeblock"><pre><i>-- Two ways to create a new archive named "new_archive.db" containing</i> |
| 1329 |
<i>-- files "file1", "file2" and "file3".</i> |
| 1330 |
.ar -c --file new_archive.db file1 file2 file3 |
| 1331 |
.ar -f new_archive.db --create file1 file2 file3 |
| 1332 |
</pre></div> |
| 1333 |
|
| 1334 |
<p>Alternatively, the first argument following to ".ar" may be the concatenation |
| 1335 |
of the short form of all required options (without the "-" characters). In |
| 1336 |
this case arguments for options requiring them are read from the command line |
| 1337 |
next, and any remaining words are considered command arguments. For example: |
| 1338 |
|
| 1339 |
</p><div class="codeblock"><pre><i>-- Create a new archive "new_archive.db" containing files "file1" and</i> |
| 1340 |
<i>-- "file2" from directory "dir1".</i> |
| 1341 |
.ar cCf dir1 new_archive.db file1 file2 file3 |
| 1342 |
</pre></div> |
| 1343 |
|
| 1344 |
<h2 id="_sqlite_archive_create_command_"><span>14.1. </span> SQLite Archive Create Command </h2> |
| 1345 |
|
| 1346 |
<p>Create a new archive, overwriting any existing archive (either in the current |
| 1347 |
"main" db or in the file specified by a --file option). Each argument following |
| 1348 |
the options is a file to add to the archive. Directories are imported |
| 1349 |
recursively. See above for examples. |
| 1350 |
|
| 1351 |
</p><h2 id="_sqlite_archive_extract_command_"><span>14.2. </span> SQLite Archive Extract Command </h2> |
| 1352 |
|
| 1353 |
<p>Extract files from the archive (either to the current working directory or |
| 1354 |
to the directory specified by a --directory option). |
| 1355 |
Files or directories whose names match the arguments, |
| 1356 |
as affected by the --glob option, are extracted. |
| 1357 |
Or, if no arguments follow the options, all files and directories are extracted. |
| 1358 |
Any specified directories are extracted recursively. It is an error if any |
| 1359 |
specified names or match patterns cannot be found in the archive. |
| 1360 |
|
| 1361 |
</p><div class="codeblock"><pre><i>-- Extract all files from the archive in the current "main" db to the</i> |
| 1362 |
<i>-- current working directory. List files as they are extracted. </i> |
| 1363 |
.ar --extract --verbose |
| 1364 |
|
| 1365 |
<i>-- Extract file "file1" from archive "ar.db" to directory "dir1".</i> |
| 1366 |
.ar fCx ar.db dir1 file1 |
| 1367 |
|
| 1368 |
<i>-- Extract files with ".h" extension to directory "headers".</i> |
| 1369 |
.ar -gCx headers *.h |
| 1370 |
</pre></div> |
| 1371 |
|
| 1372 |
<h2 id="_sqlite_archive_list_command_"><span>14.3. </span> SQLite Archive List Command </h2> |
| 1373 |
|
| 1374 |
<p>List the contents of the archive. If no arguments are specified, then all |
| 1375 |
files are listed. Otherwise, only those which match the arguments, |
| 1376 |
as affected by the --glob option, are listed. Currently, |
| 1377 |
the --verbose option does not change the behaviour of this command. That may |
| 1378 |
change in the future. |
| 1379 |
|
| 1380 |
</p><div class="codeblock"><pre><i>-- List contents of archive in current "main" db.</i>. |
| 1381 |
.ar --list |
| 1382 |
</pre></div> |
| 1383 |
|
| 1384 |
<a name="arinsup"></a> |
| 1385 |
|
| 1386 |
<h2 id="_sqlite_archive_insert_and_update_commands_"><span>14.4. </span> SQLite Archive Insert And Update Commands </h2> |
| 1387 |
|
| 1388 |
<p> The --update and --insert commands work like --create command, except that |
| 1389 |
they do not delete the current archive before commencing. New versions of |
| 1390 |
files silently replace existing files with the same names, but otherwise |
| 1391 |
the initial contents of the archive (if any) remain intact. |
| 1392 |
|
| 1393 |
</p><p> For the --insert command, all files listed are inserted into the archive. |
| 1394 |
For the --update command, files are only inserted if they do not previously |
| 1395 |
exist in the archive, or if their "mtime" or "mode" is different from what |
| 1396 |
is currently in the archive. |
| 1397 |
|
| 1398 |
</p><p> Compatibility node: Prior to SQLite version 3.28.0 (2019-04-16) only |
| 1399 |
the --update option was supported but that option worked like --insert in that |
| 1400 |
it always reinserted every file regardless of whether or not it had changed. |
| 1401 |
|
| 1402 |
</p><h2 id="_sqlite_archive_remove_command_"><span>14.5. </span> SQLite Archive Remove Command </h2> |
| 1403 |
|
| 1404 |
<p> The --remove command deletes files and directories which match the |
| 1405 |
provided arguments (if any) as affected by the --glob option. |
| 1406 |
It is an error to provide arguments which match nothing in the archive. |
| 1407 |
|
| 1408 |
</p><h2 id="_operations_on_zip_archives_"><span>14.6. </span> Operations On ZIP Archives </h2> |
| 1409 |
|
| 1410 |
<p>If FILE is a ZIP archive rather than an SQLite Archive, the ".archive" |
| 1411 |
command and the "-A" command-line option still work. This is accomplished |
| 1412 |
using the <a href="zipfile.html">zipfile</a> extension. |
| 1413 |
Hence, the following commands are roughly equivalent, |
| 1414 |
differing only in output formatting: |
| 1415 |
|
| 1416 |
</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> |
| 1417 |
<tr style="text-align:left"><th>Traditional Command</th><th>Equivalent sqlite3.exe Command |
| 1418 |
</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>unzip archive.zip</td><td>sqlite3 -Axf archive.zip |
| 1419 |
</td></tr><tr style="text-align:left"><td>unzip -l archive.zip</td><td>sqlite3 -Atvf archive.zip |
| 1420 |
</td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>zip -r archive2.zip dir</td><td>sqlite3 -Acf archive2.zip dir |
| 1421 |
</td></tr></table> |
| 1422 |
|
| 1423 |
<h2 id="_sql_used_to_implement_sqlite_archive_operations_"><span>14.7. </span> SQL Used To Implement SQLite Archive Operations </h2> |
| 1424 |
|
| 1425 |
<p>The various SQLite Archive commands are implemented using SQL statements. |
| 1426 |
Application developers can easily add SQLite Archive Archive reading and writing |
| 1427 |
support to their own projects by running the appropriate SQL. |
| 1428 |
|
| 1429 |
</p><p>To see what SQL statements are used to implement an SQLite Archive |
| 1430 |
operation, add the --dryrun or -n option. This causes the SQL to be |
| 1431 |
displayed but inhibits the execution of the SQL. |
| 1432 |
|
| 1433 |
</p><p>The SQL statements used to implement SQLite Archive operations make use of |
| 1434 |
various <a href="loadext.html">loadable extensions</a>. These extensions are all available in |
| 1435 |
the <a href="https://sqlite.org/src">SQLite source tree</a> in the |
| 1436 |
<a href="https://sqlite.org/src/file/ext/misc">ext/misc/ subfolder</a>. |
| 1437 |
The extensions needed for full SQLite Archive support include: |
| 1438 |
|
| 1439 |
</p><ol> |
| 1440 |
<li><p> |
| 1441 |
<a href="https://sqlite.org/src/file/ext/misc/fileio.c">fileio.c</a> — |
| 1442 |
This extension adds SQL functions readfile() and writefile() for |
| 1443 |
reading and writing content from files on disk. The fileio.c |
| 1444 |
extension also includes the fsdir() table-valued function for listing |
| 1445 |
the contents of a directory and the lsmode() function for converting |
| 1446 |
numeric st_mode integers from the stat() system call into human-readable |
| 1447 |
strings after the fashion of the "ls -l" command. |
| 1448 |
|
| 1449 |
</p></li><li><p> |
| 1450 |
<a href="https://sqlite.org/src/file/ext/misc/sqlar.c">sqlar.c</a> — |
| 1451 |
This extension adds the sqlar_compress() and sqlar_uncompress() |
| 1452 |
functions that are needed to compress and uncompress file content |
| 1453 |
as it is inserted and extracted from an SQLite Archive. |
| 1454 |
|
| 1455 |
</p></li><li><p> |
| 1456 |
<a href="zipfile.html">zipfile.c</a> — |
| 1457 |
This extension implements the "zipfile(FILE)" table-valued function |
| 1458 |
which is used to read ZIP archives. This extension is only needed |
| 1459 |
when reading ZIP archives instead of SQLite archives. |
| 1460 |
|
| 1461 |
</p></li><li><p> |
| 1462 |
<a href="https://sqlite.org/src/file/ext/misc/appendvfs.c">appendvfs.c</a> — |
| 1463 |
This extension implements a new <a href="vfs.html">VFS</a> that allows an SQLite database |
| 1464 |
to be appended to some other file, such as an executable. This |
| 1465 |
extension is only needed if the --append option to the .archive |
| 1466 |
command is used. |
| 1467 |
</p></li></ol> |
| 1468 |
|
| 1469 |
<a name="param"></a> |
| 1470 |
|
| 1471 |
<h1 id="sql_parameters"><span>15. </span>SQL Parameters</h1> |
| 1472 |
|
| 1473 |
<p>SQLite allows <a href="lang_expr.html#varparam">bound parameters</a> to appear in an SQL statement anywhere |
| 1474 |
that a literal value is allowed. The values for these parameters are set |
| 1475 |
using the <a href="c3ref/bind_blob.html">sqlite3_bind_...()</a> family of APIs. |
| 1476 |
|
| 1477 |
</p><p>Parameters can be either named or unnamed. An unnamed parameter is a single |
| 1478 |
question mark ("?"). Named parameters are a "?" followed immediately by a number |
| 1479 |
(ex: "?15" or "?123") or one of the characters "$", ":", or "@" followed by an |
| 1480 |
alphanumeric name (ex: "$var1", ":xyz", "@bingo"). |
| 1481 |
|
| 1482 |
</p><p>This command-line shell leaves unnamed parameters unbound, meaning that they |
| 1483 |
will have a value of an SQL NULL, but named parameters might be assigned values. |
| 1484 |
If there exists a TEMP table named "sqlite_parameters" with a schema like this: |
| 1485 |
|
| 1486 |
</p><div class="codeblock"><pre>CREATE TEMP TABLE sqlite_parameters( |
| 1487 |
key TEXT PRIMARY KEY, |
| 1488 |
value |
| 1489 |
) WITHOUT ROWID; |
| 1490 |
</pre></div> |
| 1491 |
|
| 1492 |
<p>And if there is an entry in that table where the key column exactly matches |
| 1493 |
the name of parameter (including the initial "?", "$", ":", or "@" character) |
| 1494 |
then the parameter is assigned the value of the value column. If no entry exists, |
| 1495 |
the parameter defaults to NULL. |
| 1496 |
|
| 1497 |
</p><p>The ".parameter" command exists to simplify managing this table. The |
| 1498 |
".parameter init" command (often abbreviated as just ".param init") creates |
| 1499 |
the temp.sqlite_parameters table if it does not already exist. The ".param list" |
| 1500 |
command shows all entries in the temp.sqlite_parameters table. The ".param clear" |
| 1501 |
command drops the temp.sqlite_parameters table. The ".param set KEY VALUE" and |
| 1502 |
".param unset KEY" commands create or delete entries from the |
| 1503 |
temp.sqlite_parameters table. |
| 1504 |
|
| 1505 |
</p><p>The VALUE passed to ".param set KEY VALUE" can be either a SQL literal |
| 1506 |
or any other SQL expression or query which can be evaluated to yield a value. |
| 1507 |
This allows values of differing types to be set. |
| 1508 |
If such evaluation fails, the provided VALUE is instead quoted and inserted |
| 1509 |
as text. |
| 1510 |
Because such initial evaluation may or may not fail depending upon |
| 1511 |
the VALUE content, the reliable way to get a text value is to enclose it |
| 1512 |
with single-quotes protected from the above-described command-tail parsing. |
| 1513 |
For example, (unless one intends a value of -1365):<br> |
| 1514 |
</p><div class="codeblock"><pre>.parameter init |
| 1515 |
.parameter set @phoneNumber "'202-456-1111'" |
| 1516 |
</pre></div> |
| 1517 |
<p>Note that the double-quotes serve to protect the single-quotes |
| 1518 |
and ensure that the quoted text is parsed as one argument. |
| 1519 |
|
| 1520 |
</p><p>The temp.sqlite_parameters table only provides values for parameters in the |
| 1521 |
command-line shell. The temp.sqlite_parameter table has no effect on queries |
| 1522 |
that are run directly using the SQLite C-language API. Individual applications |
| 1523 |
are expected to implement their own parameter binding. You can search for |
| 1524 |
"sqlite_parameters" in the |
| 1525 |
<a href="https://sqlite.org/src/file/src/shell.c.in">command-line shell source code</a> |
| 1526 |
to see how the command-line shell does parameter binding, and use that as |
| 1527 |
a hint for how to implement it yourself. |
| 1528 |
|
| 1529 |
<a name="expert"></a> |
| 1530 |
|
| 1531 |
</p><h1 id="index_recommendations_sqlite_expert_"><span>16. </span>Index Recommendations (SQLite Expert)</h1> |
| 1532 |
|
| 1533 |
<p><b>Note: This command is experimental. It may be removed or the |
| 1534 |
interface modified in incompatible ways at some point in the future. |
| 1535 |
|
| 1536 |
</b></p><p>For most non-trivial SQL databases, the key to performance is creating |
| 1537 |
the right SQL indexes. In this context "the right SQL indexes" means those |
| 1538 |
that cause the queries that an application needs to optimize to run fast. The |
| 1539 |
".expert" command can assist with this by proposing indexes that might |
| 1540 |
assist with specific queries, were they present in the database. |
| 1541 |
|
| 1542 |
</p><p>The ".expert" command is issued first, followed by the SQL query |
| 1543 |
on a separate line. For example, consider the following session: |
| 1544 |
|
| 1545 |
</p><div class="codeblock"><pre>sqlite> CREATE TABLE x1(a, b, c); <i>-- Create table in database </i> |
| 1546 |
sqlite> .expert |
| 1547 |
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; <i>-- Analyze this SELECT </i> |
| 1548 |
CREATE INDEX x1_idx_000123a7 ON x1(a, b); |
| 1549 |
|
| 1550 |
0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?) |
| 1551 |
|
| 1552 |
sqlite> CREATE INDEX x1ab ON x1(a, b); <i>-- Create the recommended index </i> |
| 1553 |
sqlite> .expert |
| 1554 |
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; <i>-- Re-analyze the same SELECT </i> |
| 1555 |
(no new indexes) |
| 1556 |
|
| 1557 |
0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?) |
| 1558 |
</pre></div> |
| 1559 |
|
| 1560 |
<p>In the above, the user creates the database schema (a single table - "x1"), |
| 1561 |
and then uses the ".expert" command to analyze a query, in this case |
| 1562 |
"SELECT * FROM x1 WHERE a=? AND b>?". The shell tool recommends that the |
| 1563 |
user create a new index (index "x1_idx_000123a7") and outputs the plan |
| 1564 |
that the query would use in <a href="eqp.html">EXPLAIN QUERY PLAN</a> format. The user then creates |
| 1565 |
an index with an equivalent schema and runs the analysis on the same query |
| 1566 |
again. This time the shell tool does not recommend any new indexes, and |
| 1567 |
outputs the plan that SQLite will use for the query given the existing |
| 1568 |
indexes. |
| 1569 |
|
| 1570 |
</p><p>The ".expert" command accepts the following options: |
| 1571 |
|
| 1572 |
</p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0"> |
| 1573 |
<tr style="text-align:left"><th> Option </th><th> Purpose |
| 1574 |
</th></tr><tr style="text-align:left;background-color:#DDDDDD"><td> ‑‑verbose |
| 1575 |
</td><td> If present, output a more verbose report for each query analyzed. |
| 1576 |
</td></tr><tr style="text-align:left"><td> ‑‑sample PERCENT |
| 1577 |
</td><td> This parameter defaults to 0, causing the ".expert" command to |
| 1578 |
recommend indexes based on the query and database schema alone. |
| 1579 |
This is similar to the way the <a href="optoverview.html">SQLite query planner</a> selects |
| 1580 |
indexes for queries if the user has not run the <a href="lang_analyze.html">ANALYZE</a> command |
| 1581 |
on the database to generate data distribution statistics. |
| 1582 |
<div style="margin-top:1ex"> |
| 1583 |
If this option is passed a non-zero argument, the ".expert" command |
| 1584 |
generates similar data distribution statistics for all indexes |
| 1585 |
considered based on PERCENT percent of the rows currently stored in |
| 1586 |
each database table. For databases with unusual data distributions, |
| 1587 |
this may lead to better index recommendations, particularly if the |
| 1588 |
application intends to run ANALYZE. |
| 1589 |
<div style="margin-top:1ex"> |
| 1590 |
For small databases and modern CPUs, there is usually no reason not |
| 1591 |
to pass "--sample 100". However, gathering data distribution |
| 1592 |
statistics can be expensive for large database tables. If the |
| 1593 |
operation is too slow, try passing a smaller value for the --sample |
| 1594 |
option. |
| 1595 |
</div></div></td></tr></table> |
| 1596 |
|
| 1597 |
<p>The functionality described in this section may be integrated into other |
| 1598 |
applications or tools using the |
| 1599 |
<a href="https://sqlite.org/src/dir?ci=trunk&name=ext/expert"> |
| 1600 |
SQLite expert extension</a> code. |
| 1601 |
|
| 1602 |
</p><p>A database schema which incorporates SQL custom functions made available |
| 1603 |
via the extension load mechanism may need special provision to work with |
| 1604 |
the .expert feature. Because the feature uses additional connections to |
| 1605 |
implement its functionality, those custom functions must be made available |
| 1606 |
to those additional connections. This can be done by means of the extension |
| 1607 |
load/usage options described at |
| 1608 |
<a href="c3ref/auto_extension.html"> |
| 1609 |
Automatically Load Statically Linked Extensions</a> |
| 1610 |
and <a href="loadext.html#persist"> |
| 1611 |
Persistent Loadable Extensions</a>. |
| 1612 |
|
| 1613 |
<a name="dotconn"></a> |
| 1614 |
|
| 1615 |
</p><h1 id="working_with_multiple_database_connections"><span>17. </span>Working With Multiple Database Connections</h1> |
| 1616 |
|
| 1617 |
<p> |
| 1618 |
Beginning with version 3.37.0 (2021-11-27), the CLI has the ability to |
| 1619 |
hold multiple <a href="c3ref/sqlite3.html">database connections</a> open at once. Only one database connection |
| 1620 |
is active at a time. The inactive connections are still open but are idle. |
| 1621 |
|
| 1622 |
</p><p> |
| 1623 |
Use the ".connection" dot-command (often abbreviated as just ".conn") to see a |
| 1624 |
list of database connections and an indication of which one is currently active. |
| 1625 |
Each database connection is identified by an integer between 0 and 9. (There |
| 1626 |
can be at most 10 simultaneously open connections.) Change to another database |
| 1627 |
connection, creating it if it does not already exist, by typing the ".conn" |
| 1628 |
command followed by its number. Close a database connection by typing |
| 1629 |
".conn close N" where N is the connection number. |
| 1630 |
|
| 1631 |
</p><p> |
| 1632 |
Though the underlying SQLite database connections are completely independent |
| 1633 |
of one another, many of the CLI settings, such as the output format, are |
| 1634 |
shared across all database connections. Thus, changing the <a href="climode.html">output mode</a> in |
| 1635 |
one connection will change it in them all. On the other hand, some |
| 1636 |
<a href="cli.html#dotcmd">dot-commands</a> such as <a href="cli.html#dotopen">.open</a> only affect the current connection. |
| 1637 |
|
| 1638 |
<a name="miscfeatures"></a> |
| 1639 |
|
| 1640 |
</p><h1 id="miscellaneous_extension_features"><span>18. </span>Miscellaneous Extension Features</h1> |
| 1641 |
|
| 1642 |
<p> |
| 1643 |
The CLI is built with several SQLite extensions that are not |
| 1644 |
included with the SQLite library. A few add features |
| 1645 |
not described in the preceding sections, namely: |
| 1646 |
</p><ul> |
| 1647 |
<li>the <a href="uintcseq.html">UINT collating sequence</a> which treats |
| 1648 |
unsigned integers embedded in text according to |
| 1649 |
their value, along with other text, for ordering; |
| 1650 |
</li><li>decimal arithmetic as provided by the <a href="floatingpoint.html#decext">decimal extension</a>; |
| 1651 |
</li><li>the <a href="series.html">generate_series</a>() table-valued function; |
| 1652 |
</li><li>the <a href="base64.html">base64()</a> and <a href="base85.html">base85()</a> functions which encode a |
| 1653 |
blob to base64 or base85 text or decode the same to a blob; and |
| 1654 |
</li><li>support for POSIX extended regular expressions |
| 1655 |
bound to the <a href="lang_expr.html#regexp">REGEXP</a> operator. |
| 1656 |
</li></ul> |
| 1657 |
|
| 1658 |
<a name="dotother"></a> |
| 1659 |
|
| 1660 |
<h1 id="other_dot_commands"><span>19. </span>Other Dot Commands</h1> |
| 1661 |
|
| 1662 |
<p>There are many other dot-commands available in the command-line |
| 1663 |
shell. See the ".help" command for a complete list for any particular |
| 1664 |
version and build of SQLite. |
| 1665 |
|
| 1666 |
<a name="insh"></a> |
| 1667 |
|
| 1668 |
</p><h1 id="using_sqlite3_in_a_shell_script"><span>20. </span>Using sqlite3 in a shell script</h1> |
| 1669 |
|
| 1670 |
<p> |
| 1671 |
One way to use sqlite3 in a shell script is to use "echo" or |
| 1672 |
"cat" to generate a sequence of commands in a file, then invoke sqlite3 |
| 1673 |
while redirecting input from the generated command file. This |
| 1674 |
works fine and is appropriate in many circumstances. But as |
| 1675 |
an added convenience, sqlite3 allows a single SQL command to be |
| 1676 |
entered on the command line as a second argument after the |
| 1677 |
database name. When the sqlite3 program is launched with two |
| 1678 |
arguments, the second argument is passed to the SQLite library |
| 1679 |
for processing, the query results are printed on standard output |
| 1680 |
in list mode, and the program exits. This mechanism is designed |
| 1681 |
to make sqlite3 easy to use in conjunction with programs like |
| 1682 |
"awk". For example:</p> |
| 1683 |
|
| 1684 |
<div class="codeblock"><pre>$ <b>sqlite3 ex1 'select * from tbl1' \</b> |
| 1685 |
> <b> | awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'</b> |
| 1686 |
<tr><td>hello<td>10 |
| 1687 |
<tr><td>goodbye<td>20 |
| 1688 |
$ |
| 1689 |
</pre></div> |
| 1690 |
|
| 1691 |
<a name="endsh"></a> |
| 1692 |
|
| 1693 |
<h1 id="marking_the_end_of_an_sql_statement"><span>21. </span>Marking The End Of An SQL Statement</h1> |
| 1694 |
|
| 1695 |
<p> |
| 1696 |
SQLite commands are normally terminated by a semicolon. In the CLI |
| 1697 |
you can also use the word "GO" (case-insensitive) or a slash character |
| 1698 |
"/" on a line by itself to end a command. These are used by SQL Server |
| 1699 |
and Oracle, respectively, and are supported by the SQLite CLI for |
| 1700 |
compatibility. These won't work in <b>sqlite3_exec()</b>, |
| 1701 |
because the CLI translates these inputs into a semicolon before passing |
| 1702 |
them down into the SQLite core.</p> |
| 1703 |
|
| 1704 |
<h1 id="more_details_on_how_to_start_the_cli"><span>22. </span>More Details On How To Start The CLI</h1> |
| 1705 |
|
| 1706 |
<p>As stated <a href="#startup">previously</a>, the usual way to start |
| 1707 |
up the CLI is to type "sqlite3" followed by the name of the database file. |
| 1708 |
But the "sqlite3" program accepts many other arguments other than just |
| 1709 |
the database filename. |
| 1710 |
|
| 1711 |
<a name="xtraargs"></a> |
| 1712 |
|
| 1713 |
</p><h2 id="extra_command_line_arguments"><span>22.1. </span>Extra command-line arguments</h2> |
| 1714 |
|
| 1715 |
<p>Additional command-line arguments that occur after the database filename |
| 1716 |
are treated as if they were lines of input text. Each additional argument |
| 1717 |
can be either an SQL statement or a <a href="cli.html#dotcmd">dot-command</a>. They are evaluated in |
| 1718 |
order from left to right. |
| 1719 |
Since both SQLite statements and dot-commands often contain spaces, you will |
| 1720 |
probably need to put each SQL statement or dot-command inside |
| 1721 |
single- or double-quotes (depending on your OS). For example: |
| 1722 |
|
| 1723 |
</p><div class="codeblock"><pre>$ <b>sqlite3 test.db ".mode box" "SELECT * FROM users;"</b> |
| 1724 |
</pre></div> |
| 1725 |
|
| 1726 |
<p>When extra arguments are provided this way, standard input is not read |
| 1727 |
and the CLI exits after it has processed all the extra arguments. |
| 1728 |
|
| 1729 |
<a name="clopts"></a> |
| 1730 |
|
| 1731 |
</p><h2 id="command_line_options"><span>22.2. </span>Command-line Options</h2> |
| 1732 |
|
| 1733 |
<p> |
| 1734 |
Extra arguments that start with the "-" character are command-line options. |
| 1735 |
There are many command-line options available. Use the --help |
| 1736 |
command-line option to see a list: |
| 1737 |
|
| 1738 |
</p><div class="codeblock"><pre>$ <b>sqlite3 --help</b> |
| 1739 |
FILENAME is the name of an SQLite database. A new database is created |
| 1740 |
if the file does not previously exist. Defaults to :memory:. |
| 1741 |
OPTIONS include: |
| 1742 |
-- treat no subsequent arguments as options |
| 1743 |
-A ARGS... run ".archive ARGS" and exit |
| 1744 |
-append append the database to the end of the file |
| 1745 |
-ascii set output mode to 'ascii' |
| 1746 |
-bail stop after hitting an error |
| 1747 |
-batch force batch I/O |
| 1748 |
-box set output mode to 'box' |
| 1749 |
-column set output mode to 'column' |
| 1750 |
-cmd COMMAND run "COMMAND" before reading stdin |
| 1751 |
-csv set output mode to 'csv' |
| 1752 |
-deserialize open the database using sqlite3_deserialize() |
| 1753 |
-echo print inputs before execution |
| 1754 |
-escape T ctrl-char escape; T is one of: symbol, ascii, off |
| 1755 |
-init FILENAME read/process named file |
| 1756 |
-[no]header turn headers on or off |
| 1757 |
-heap SIZE Size of heap for memsys3 or memsys5 |
| 1758 |
-help show this message |
| 1759 |
-html set output mode to HTML |
| 1760 |
-ifexists only open if database already exists |
| 1761 |
-interactive force interactive I/O |
| 1762 |
-json set output mode to 'json' |
| 1763 |
-line set output mode to 'line' |
| 1764 |
-list set output mode to 'list' |
| 1765 |
-lookaside SIZE N use N entries of SZ bytes for lookaside memory |
| 1766 |
-markdown set output mode to 'markdown' |
| 1767 |
-maxsize N maximum size for a --deserialize database |
| 1768 |
-memtrace trace all memory allocations and deallocations |
| 1769 |
-mmap N default mmap size set to N |
| 1770 |
-newline SEP set output row separator. Default: '\n' |
| 1771 |
-nofollow refuse to open symbolic links to database files |
| 1772 |
-nonce STRING set the safe-mode escape nonce |
| 1773 |
-no-rowid-in-view Disable rowid-in-view using sqlite3_config() |
| 1774 |
-nullvalue TEXT set text string for NULL values. Default '' |
| 1775 |
-pagecache SIZE N use N slots of SZ bytes each for page cache memory |
| 1776 |
-pcachetrace trace all page cache operations |
| 1777 |
-quote set output mode to 'quote' |
| 1778 |
-readonly open the database read-only |
| 1779 |
-safe enable safe-mode |
| 1780 |
-separator SEP set output column separator. Default: '|' |
| 1781 |
-stats print memory stats before each finalize |
| 1782 |
-table set output mode to 'table' |
| 1783 |
-tabs set output mode to 'tabs' |
| 1784 |
-unsafe-testing allow unsafe commands and modes for testing |
| 1785 |
-version show SQLite version |
| 1786 |
-vfs NAME use NAME as the default VFS |
| 1787 |
-vfstrace enable tracing of all VFS calls |
| 1788 |
-zip open the file as a ZIP Archive |
| 1789 |
</pre></div> |
| 1790 |
|
| 1791 |
<p>The CLI is flexible regarding command-line option formatting. |
| 1792 |
Either one or two leading "-" characters are permitted. |
| 1793 |
Thus "-box" and "--box" mean the same thing. |
| 1794 |
Command-line options are processed from left to right. |
| 1795 |
Hence a "--box" option will override a prior "--quote" option. |
| 1796 |
|
| 1797 |
</p><p> |
| 1798 |
Most of the command-line options are self-explanatory, but a few merit additional |
| 1799 |
discussion below. |
| 1800 |
|
| 1801 |
<a name="safemode"></a> |
| 1802 |
|
| 1803 |
</p><h2 id="the_safe_command_line_option"><span>22.3. </span>The --safe command-line option</h2> |
| 1804 |
|
| 1805 |
<p>The --safe command-line option attempts to disable all features of the CLI that |
| 1806 |
might cause any changes to the host computer other than changes to the specific database |
| 1807 |
file named on the command-line. The idea is that if you receive a large SQL script |
| 1808 |
from an unknown or untrusted source, you can run that script to see what it does without |
| 1809 |
risking an exploit by using the --safe option. The --safe option disables (among other |
| 1810 |
things): |
| 1811 |
|
| 1812 |
</p><ul> |
| 1813 |
<li> The <a href="cli.html#dotopen">.open command</a>, unless the --hexdb option is used or the filename is ":memory:". |
| 1814 |
This prevents the script from reading or writing any database files not named on |
| 1815 |
the original command-line. |
| 1816 |
</li><li> The <a href="lang_attach.html">ATTACH</a> SQL command. |
| 1817 |
</li><li> SQL functions that have potentially harmful side-effects, such as |
| 1818 |
edit(), fts3_tokenizer(), load_extension(), readfile() and writefile(). |
| 1819 |
</li><li> The <a href="cli.html#sqlar">.archive command</a>. |
| 1820 |
</li><li> The .backup and .save commands. |
| 1821 |
</li><li> The <a href="cli.html#csv">.import command</a>. |
| 1822 |
</li><li> The <a href="cli.html#dotload">.load command</a>. |
| 1823 |
</li><li> The .log command. |
| 1824 |
</li><li> The .shell and .system commands. |
| 1825 |
</li><li> The .excel, .once and .output commands. |
| 1826 |
</li><li> Other commands that can have deleterious side effects. |
| 1827 |
|
| 1828 |
</li></ul> |
| 1829 |
|
| 1830 |
<p>Basically, any feature of the CLI that reads or writes from a file on disk other |
| 1831 |
than the main database file is disabled. |
| 1832 |
|
| 1833 |
</p><h3 id="bypassing_safe_restrictions_for_specific_commands"><span>22.3.1. </span>Bypassing --safe restrictions for specific commands</h3> |
| 1834 |
|
| 1835 |
<p>If the "--nonce NONCE" option is also included on the command-line, for some |
| 1836 |
large and arbitrary NONCE string, then the ".nonce NONCE" command (with the |
| 1837 |
same large nonce string) will permit the next SQL statement or dot-command |
| 1838 |
to bypass the --safe restrictions. |
| 1839 |
|
| 1840 |
</p><p>Suppose you want to run a suspicious script and the script requires one or |
| 1841 |
two of the features that --safe normally disables. For example, suppose it |
| 1842 |
needs to ATTACH one additional database. Or suppose the script needs to load |
| 1843 |
a specific extension. This can be accomplished by preceding the (carefully |
| 1844 |
audited) ATTACH statement or the ".load" command with an appropriate ".nonce" |
| 1845 |
command and supplying the same nonce value using the "--nonce" command-line |
| 1846 |
option. Those specific commands will then be allowed to execute normally, |
| 1847 |
but all other unsafe commands will still be restricted. |
| 1848 |
|
| 1849 |
</p><p>The use of ".nonce" is dangerous in the sense that a mistake can allow a |
| 1850 |
hostile script to damage your system. Therefore, use ".nonce" carefully, |
| 1851 |
sparingly, and as a last resort when there are no other ways to get a |
| 1852 |
script to run under --safe mode. |
| 1853 |
|
| 1854 |
<a name="testing_mode"></a> |
| 1855 |
|
| 1856 |
</p><h2 id="the_unsafe_testing_command_line_option"><span>22.4. </span>The --unsafe-testing command-line option</h2> |
| 1857 |
|
| 1858 |
<p>The --unsafe-testing command-line option enables features of the CLI |
| 1859 |
that are intended for internal testing only. The --unsafe-testing option |
| 1860 |
disables defenses that are built into SQLite, for example |
| 1861 |
<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive">SQLITE_DBCONFIG_DEFENSIVE</a> and <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigtrustedschema">SQLITE_DBCONFIG_TRUSTED_SCHEMA</a>. |
| 1862 |
The --unsafe-testing option also enables features that, if misused, might |
| 1863 |
cause database corruption, memory errors, or |
| 1864 |
similar problems in the CLI itself or in the SQLite library. |
| 1865 |
An example of features that --unsafe-testing enables is the |
| 1866 |
".testctrl assert false" command which deliberately triggers an |
| 1867 |
assertion fault, in order to verify that the assertion fault |
| 1868 |
mechanism is working. |
| 1869 |
</p><p> |
| 1870 |
Misbehavior which requires use of the --unsafe-testing option |
| 1871 |
will generally not be considered a bug. |
| 1872 |
|
| 1873 |
</p><h2 id="the_no_utf8_and_utf8_command_line_options"><span>22.5. </span>The --no-utf8 and --utf8 command-line options</h2> |
| 1874 |
|
| 1875 |
<p>On the Windows platform, when the console is used for input or output, |
| 1876 |
translation is required between character encoding available from or sent to |
| 1877 |
the console and the CLI's internal, UTF-8 text representation. Past versions |
| 1878 |
of the CLI accepted these options to enable or disable use of a translation |
| 1879 |
that relied upon a Windows console feature whereby it could be made to |
| 1880 |
produce or accept UTF-8 on modern versions of the OS.</p> |
| 1881 |
|
| 1882 |
<p>Present CLI versions (3.44.1 or later) do console I/O by reading or writing |
| 1883 |
UTF-16 from/to the Windows console APIs. Because this operates correctly even |
| 1884 |
on Windows versions going back to Window 2000, there is no longer any need |
| 1885 |
for these options. They are still accepted, but without effect.</p> |
| 1886 |
|
| 1887 |
<p>In all cases, non-console text I/O is UTF-8 encoded.</p> |
| 1888 |
|
| 1889 |
<p>On non-Windows platforms, these options are also ignored.</p> |
| 1890 |
|
| 1891 |
<a name="compiling"></a> |
| 1892 |
|
| 1893 |
<h1 id="compiling_the_sqlite3_program_from_sources"><span>23. </span>Compiling the sqlite3 program from sources</h1> |
| 1894 |
|
| 1895 |
<p> |
| 1896 |
To compile the command-line shell on unix systems and on Windows with MinGW, |
| 1897 |
the usual configure-make command works: |
| 1898 |
|
| 1899 |
</p><div class="codeblock"><pre>sh configure; make |
| 1900 |
</pre></div> |
| 1901 |
|
| 1902 |
<p> |
| 1903 |
The configure-make works whether you are building from the canonical sources |
| 1904 |
from the source tree, or from an amalgamated bundle. There are few |
| 1905 |
dependencies. When building from canonical sources, a working |
| 1906 |
<a href="https://www.tcl.tk/man/tcl8.3/UserCmd/tclsh.htm">tclsh</a> is required. |
| 1907 |
If using an amalgamation bundle, all the preprocessing work normally |
| 1908 |
done by tclsh will have already been carried out and only normal build |
| 1909 |
tools are required. |
| 1910 |
|
| 1911 |
</p><p> |
| 1912 |
A working <a href="https://zlib.net">zlib compression library</a> is |
| 1913 |
needed in order for the <a href="cli.html#sqlar">.archive command</a> to operate. |
| 1914 |
|
| 1915 |
</p><p> |
| 1916 |
On Windows with MSVC, use nmake with the Makefile.msc: |
| 1917 |
|
| 1918 |
</p><div class="codeblock"><pre>nmake /f Makefile.msc |
| 1919 |
</pre></div> |
| 1920 |
|
| 1921 |
<p> |
| 1922 |
For correct operation of the <a href="cli.html#sqlar">.archive command</a>, make a copy of the |
| 1923 |
<a href="https://zlib.net">zlib source code</a> into the compat/zlib subdirectory |
| 1924 |
of the source tree and compile this way: |
| 1925 |
|
| 1926 |
</p><div class="codeblock"><pre>nmake /f Makefile.msc USE_ZLIB=1 |
| 1927 |
</pre></div> |
| 1928 |
|
| 1929 |
<h2 id="_do_it_yourself_builds_"><span>23.1. </span> Do-It-Yourself Builds </h2> |
| 1930 |
|
| 1931 |
<p> |
| 1932 |
The source code to the sqlite3 command line interface is in a single |
| 1933 |
file named "shell.c". The shell.c source file is generated from other |
| 1934 |
sources, but most of the code for shell.c can be found in |
| 1935 |
<a href="https://sqlite.org/src/file/src/shell.c.in">src/shell.c.in</a>. |
| 1936 |
(Regenerate shell.c by typing "make shell.c" from the canonical source tree.) |
| 1937 |
<a href="howtocompile.html">Compile</a> the shell.c file (together |
| 1938 |
with the <a href="amalgamation.html">sqlite3 library source code</a>) to generate |
| 1939 |
the executable. For example:</p> |
| 1940 |
|
| 1941 |
<div class="codeblock"><pre>gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread -lz -lm |
| 1942 |
</pre></div> |
| 1943 |
|
| 1944 |
<p> |
| 1945 |
The following additional compile-time options are recommended in order to |
| 1946 |
provide a full-featured command-line shell: |
| 1947 |
|
| 1948 |
</p><ul> |
| 1949 |
<li> <a href="compile.html#threadsafe">-DSQLITE_THREADSAFE=0</a> |
| 1950 |
</li><li> <a href="compile.html#enable_explain_comments">-DSQLITE_ENABLE_EXPLAIN_COMMENTS</a> |
| 1951 |
</li><li> <a href="compile.html#have_zlib">-DSQLITE_HAVE_ZLIB</a> |
| 1952 |
</li><li> <a href="compile.html#introspection_pragmas">-DSQLITE_INTROSPECTION_PRAGMAS</a> |
| 1953 |
</li><li> <a href="compile.html#enable_unknown_sql_function">-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION</a> |
| 1954 |
</li><li> <a href="compile.html#enable_stmtvtab">-DSQLITE_ENABLE_STMTVTAB</a> |
| 1955 |
</li><li> <a href="compile.html#enable_dbpage_vtab">-DSQLITE_ENABLE_DBPAGE_VTAB</a> |
| 1956 |
</li><li> <a href="compile.html#enable_dbstat_vtab">-DSQLITE_ENABLE_DBSTAT_VTAB</a> |
| 1957 |
</li><li> <a href="compile.html#enable_offset_sql_func">-DSQLITE_ENABLE_OFFSET_SQL_FUNC</a> |
| 1958 |
</li><li> <a href="compile.html#enable_json1">-DSQLITE_ENABLE_JSON1</a> |
| 1959 |
</li><li> <a href="compile.html#enable_rtree">-DSQLITE_ENABLE_RTREE</a> |
| 1960 |
</li><li> <a href="compile.html#enable_fts4">-DSQLITE_ENABLE_FTS4</a> |
| 1961 |
</li><li> <a href="compile.html#enable_fts5">-DSQLITE_ENABLE_FTS5</a> |
| 1962 |
</li></ul> |
| 1963 |
|
| 1964 |
|