Debian Codegraph @

www/cli.html

raw
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&gt; <b>create table tbl1(one text, two int);</b>
256 sqlite&gt; <b>insert into tbl1 values('hello!',10),('goodbye',20);</b>
257 sqlite&gt; <b>select * from tbl1;</b>
258 ┌───────────┬─────┐
259 │ one │ two │
260 ├───────────┼─────┤
261 │ 'hello!' │ 10 │
262 │ 'goodbye' │ 20 │
263 └───────────┴─────┘
264 sqlite&gt;
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&gt; <b>CREATE TABLE tbl2 (</b>
280 ...&gt; <b> f1 varchar(30) primary key,</b>
281 ...&gt; <b> f2 text,</b>
282 ...&gt; <b> f3 real</b>
283 ...&gt; <b>);</b>
284 sqlite&gt;
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&gt; <b>.open ex1.db</b>
304 sqlite&gt;
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&gt; <i>... many SQL commands omitted ...</i>
322 sqlite&gt; <b>.save ex1.db</b>
323 sqlite&gt;
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&nbsp;</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&gt; <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 &#91;close&#93; &#91;#&#93; 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&gt;
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>&emsp;
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&gt; <b>.tables</b>
566 tbl1 tbl2
567 sqlite&gt;
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&gt; <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&gt; <b>.schema tbl2</b>
600 CREATE TABLE tbl2 (
601 f1 varchar(30) primary key,
602 f2 text,
603 f3 real
604 );
605 sqlite&gt;
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&gt; <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&gt; <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&gt; <b>.mode list --colsep "|"</b>
720 sqlite&gt; <b>.output test_file_1.txt</b>
721 sqlite&gt; <b>select * from tbl1;</b>
722 sqlite&gt; <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&gt; <b>.once | open -f</b>
738 sqlite&gt; <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&gt; <b>.once -x</b>
754 sqlite&gt; <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&gt; <b>.www</b>
771 sqlite&gt; <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&gt; <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&gt; <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&gt; <b>CREATE TABLE images(name TEXT, type TEXT, img BLOB);</b>
820 sqlite&gt; <b>INSERT INTO images(name,type,img</b>)
821 ...&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <b>.mode csv --titles on</b>
932 sqlite&gt; <b>.once c:/work/dataout.csv</b>
933 sqlite&gt; <b>SELECT * FROM tab1;</b>
934 sqlite&gt; <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&gt; <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&gt; <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&gt; <b>.excel</b>
976 sqlite&gt; <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&gt; 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&gt; SELECT writefile(name,content) FROM zip
1053 ...&gt; 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&gt; .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 &gt;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,&nbsp;c1,&nbsp;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&gt; .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&gt; .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&nbsp;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&nbsp;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> &mdash;
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> &mdash;
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> &mdash;
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> &mdash;
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&gt; CREATE TABLE x1(a, b, c); <i>-- Create table in database </i>
1546 sqlite&gt; .expert
1547 sqlite&gt; SELECT * FROM x1 WHERE a=? AND b&gt;?; <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&gt;?)
1551
1552 sqlite&gt; CREATE INDEX x1ab ON x1(a, b); <i>-- Create the recommended index </i>
1553 sqlite&gt; .expert
1554 sqlite&gt; SELECT * FROM x1 WHERE a=? AND b&gt;?; <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&gt;?)
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&gt;?". 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> &#8209;&#8209;verbose
1575 </td><td> If present, output a more verbose report for each query analyzed.
1576 </td></tr><tr style="text-align:left"><td> &#8209;&#8209;sample&nbsp;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 &gt; <b> | awk '{printf "&lt;tr&gt;&lt;td&gt;%s&lt;td&gt;%s\n",$1,$2 }'</b>
1686 &lt;tr&gt;&lt;td&gt;hello&lt;td&gt;10
1687 &lt;tr&gt;&lt;td&gt;goodbye&lt;td&gt;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 -&#91;no&#93;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