Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Updates to the "opcode.html" and "sqlite.html" documents. |
|---|---|
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
45a6eaaaa7cacbccedc1096f64850313 |
| User & Date: | drh 2013-11-18 23:39:48 |
Context
|
2013-11-19
| ||
| 00:20 | Update the list of keywords to include COVERING, WITH, and WITHOUT. COVERING and WITH are currently unused. check-in: 74e28675d4 user: drh tags: trunk | |
|
2013-11-18
| ||
| 23:39 | Updates to the "opcode.html" and "sqlite.html" documents. check-in: 45a6eaaaa7 user: drh tags: trunk | |
| 20:57 | Correct minor typo. check-in: 9589b9982b user: mistachkin tags: trunk | |
Changes
Changes to pages/opcode.in.
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 |
<p>Every SQL statement that SQLite interprets results in a program for the virtual machine. But if you precede the SQL statement with the keyword [EXPLAIN] the virtual machine will not execute the program. Instead, the instructions of the program will be returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates.</p> <p>You can use the <b>sqlite3.exe</b> command-line interface (CLI) tool to see the instructions generated by an SQL statement. The following is an example:</p> <tcl> proc Code {body} { hd_puts {<blockquote><tt>} regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body regsub -all {\(\(\(} $body {<b>} body regsub -all {\)\)\)} $body {</b>} body regsub -all { } $body {\ } body regsub -all \n $body <br>\n body hd_puts $body hd_puts {</tt></blockquote>} } Code { $ (((sqlite3 ex1.db))) sqlite> (((.explain))) sqlite> (((explain delete from tbl1 where two<20;))) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- --------- -- ------- 0 Trace 0 0 0 explain.. 00 1 Goto 0 20 0 00 2 OpenRead 0 2 0 00 tbl 3 SetNumColumns 0 2 0 00 4 Rewind 0 11 0 00 5 Column 0 1 2 00 tbl.two 6 Integer 20 3 0 00 7 Ge 3 10 2 cs(BINARY) 6a 8 Rowid 0 1 0 00 9 FifoWrite 1 0 0 00 10 Next 0 5 0 00 11 Close 0 0 0 00 12 OpenWrite 0 2 0 00 tbl 13 SetNumColumns 0 2 0 00 14 FifoRead 1 18 0 00 15 NotExists 0 17 1 00 16 Delete 0 1 0 tbl 00 17 Goto 0 14 0 00 18 Close 0 0 0 00 19 Halt 0 0 0 00 20 Transaction 0 1 0 00 21 VerifyCookie 0 1 0 00 22 TableLock -1 2 0 tbl 00 23 Goto 0 2 0 00 } </tcl> <p>All you have to do is add the [EXPLAIN] keyword to the front of the SQL statement. But if you use the ".explain" command in the CLI, it will set up the output mode to make the program more easily viewable.</p> <p>Depending on compile-time options, you can put the SQLite virtual machine in a mode where it will trace its execution by writing messages to standard output. The non-standard SQL "PRAGMA" comments can be used to turn tracing on and off. To |
| | < | | | | | > | | > | | < | > | > | | > | | < < > | | | | | | | < > > | | |
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 |
<p>Every SQL statement that SQLite interprets results in a program for the virtual machine. But if you precede the SQL statement with the keyword [EXPLAIN] the virtual machine will not execute the program. Instead, the instructions of the program will be returned like a query result. This feature is useful for debugging and for learning how the virtual machine operates.</p> <p>You can use the [CLI | sqlite3.exe command-line interface (CLI)] tool to see the instructions generated by an SQL statement. The following is an example:</p> <tcl> proc Code {body} { hd_puts {<blockquote><pre>} regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body regsub -all {\(\(\(} $body {<b>} body regsub -all {\)\)\)} $body {</b>} body regsub -all { } $body {\ } body hd_puts $body hd_puts {</pre></blockquote>} } Code { $ (((sqlite3 ex1.db))) sqlite> (((.explain))) sqlite> (((explain delete from tbl1 where two<20;))) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 23 0 00 2 Null 0 1 0 00 r[1]=NULL 3 OpenRead 0 2 0 2 00 root=2 iDb=0; tbl1 4 Explain 0 0 0 SCAN TABLE tbl1 00 5 Noop 0 0 0 00 Begin WHERE-loop0: tbl1 6 Rewind 0 14 0 00 7 Column 0 1 2 00 r[2]=tbl1.two 8 Ge 3 13 2 (BINARY) 6a if r[3]>=r[2] goto 13 9 Noop 0 0 0 00 Begin WHERE-core 10 Rowid 0 4 0 00 r[4]=rowid 11 RowSetAdd 1 4 0 00 rowset(1)=r[4] 12 Noop 0 0 0 00 End WHERE-core 13 Next 0 7 0 01 14 Noop 0 0 0 00 End WHERE-loop0: tbl1 15 Close 0 0 0 00 16 OpenWrite 0 2 0 3 00 root=2 iDb=0; tbl1 17 RowSetRead 1 21 4 00 r[4]=rowset(1) 18 NotExists 0 20 4 1 00 intkey=r[4] 19 Delete 0 1 0 tbl1 00 20 Goto 0 17 0 00 21 Close 0 0 0 00 22 Halt 0 0 0 00 23 Transaction 0 1 0 00 24 VerifyCookie 0 1 0 00 25 TableLock 0 2 1 tbl1 00 iDb=0 root=2 write=1 26 Integer 20 3 0 00 r[3]=20 27 Goto 0 2 0 00 } </tcl> <p>All you have to do is add the [EXPLAIN] keyword to the front of the SQL statement. But if you use the ".explain" command in the [CLI], it will set up the output mode to make the program more easily viewable.</p> <p>Depending on compile-time options, you can put the SQLite virtual machine in a mode where it will trace its execution by writing messages to standard output. The non-standard SQL "PRAGMA" comments can be used to turn tracing on and off. To |
Changes to pages/sqlite.in.
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 .. 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 ... 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 ... 260 261 262 263 264 265 266 267 268 269 270 271 272 273 ... 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 ... 320 321 322 323 324 325 326 327 328 329 330 331 332 333 ... 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 ... 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 |
<h1 align=center> Command Line Shell For SQLite </h1> <p>The SQLite library includes a simple command-line utility named <b>sqlite3</b> (or <b>sqlite3.exe</b> on windows) that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <h3>Getting Started</h3> <p>To start the <b>sqlite3</b> program, just type "sqlite3" followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The <b>sqlite3</b> program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.</p> <p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p> ................................................................................ ...> ((( f1 varchar(30) primary key,))) ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();))) sqlite> }</tcl> <h3>Aside: Querying the SQLITE_MASTER table</h3> <p>The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:</p> <tcl>Code { $ (((sqlite3 ex1))) SQLite version 3.6.11 Enter ".help" for instructions sqlite> (((select * from sqlite_master;))) type = table name = tbl1 tbl_name = tbl1 rootpage = 3 sql = create table tbl1(one varchar(10), two smallint) sqlite> }</tcl> <p> But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table. </p> <p> The schema for TEMPORARY tables is not stored in the "sqlite_master" table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named "sqlite_temp_master". The "sqlite_temp_master" table is temporary itself. </p> <h3>Special commands to sqlite3</h3> <p> Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. ................................................................................ <tcl>Code { sqlite> (((.help))) .backup ?DB? FILE Backup DB (default "main") to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? ... Dump the database in an SQL text format .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. .genfkey ?OPTIONS? Options are: --no-drop: Do not drop old fkey triggers. --ignore-errors: Ignore tables with fkey errors --exec: Execute generated SQL immediately See file tool/genfkey.README in the source distribution for further information. .header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices TABLE Show names of all indices on TABLE .iotrace FILE Enable I/O diagnostic logging to FILE .load FILE ?ENTRY? Load an extension library .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML <table> code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements .nullvalue STRING Print STRING in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .schema ?TABLE? Show the CREATE statements .separator STRING Change separator used by output mode and .import .show Show the current values for various settings .tables ?PATTERN? List names of tables matching a LIKE pattern .timeout MS Try opening locked tables for MS milliseconds .timer ON|OFF Turn the CPU timer measurement on or off .width NUM NUM ... Set column widths for "column" mode sqlite> }</tcl> <h3>Changing Output Formats</h3> <p>The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.</p> ................................................................................ <p>If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.</p> <p>The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:</p> <tcl>Code { ................................................................................ <p>The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.</p> <h3>Writing results to a file</h3> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to ................................................................................ sqlite> (((.exit))) $ (((cat test_file_1.txt))) hello|10 goodbye|20 $ }</tcl> <h3>Querying the database schema</h3> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.</p> ................................................................................ SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1 </pre></blockquote> <p>In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you'll find exactly the above query.</p> <p>The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. ................................................................................ $ (((createdb ex2))) $ (((sqlite3 ex1 .dump | psql ex2))) }</tcl> <h3>Other Dot Commands</h3> <p>The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:</p> <tcl>Code { sqlite> (((.explain))) sqlite> (((explain delete from tbl1 where two<20;))) addr opcode p1 p2 p3 ---- ------------ ----- ----- ------------------------------------- 0 ListOpen 0 0 1 Open 0 1 tbl1 2 Next 0 9 3 Field 0 1 4 Integer 20 0 5 Ge 0 2 6 Key 0 0 7 ListWrite 0 0 8 Goto 0 2 9 Noop 0 0 10 ListRewind 0 0 11 ListRead 0 14 12 Delete 0 0 13 Goto 0 11 14 ListClose 0 0 }</tcl> <p>The ".timeout" command sets the amount of time that the <b>sqlite3</b> program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.</p> <p>And finally, we mention the ".exit" command which causes the sqlite3 program to exit.</p> <h3>Using sqlite3 in a shell script</h3> <p> One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This |
| | | > | > < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > | < < | < < < | | > > | > > > > < > > > > > > > < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < |
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 .. 71 72 73 74 75 76 77 78 79 80 81 82 83 84 .. 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 ... 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 ... 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 ... 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 ... 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 ... 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 |
<h1 align=center> Command Line Shell For SQLite </h1> <p>The SQLite library includes a simple command-line utility named <b>sqlite3</b> (or <b>sqlite3.exe</b> on windows) that allows the user to manually enter and execute SQL statements against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <h3>Getting Started</h3> <p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally followed by the name the file that holds the SQLite database. If the file does not exist, a new database file with the given name will be created automatically. If no database file is specified, a temporary database is created, then deleted when the "sqlite3" program exits. The <b>sqlite3</b> program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.</p> <p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p> ................................................................................ ...> ((( f1 varchar(30) primary key,))) ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();))) sqlite> }</tcl> <h3>Special commands to sqlite3</h3> <p> Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. ................................................................................ <tcl>Code { sqlite> (((.help))) .backup ?DB? FILE Backup DB (default "main") to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. .header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML <table> code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements .nullvalue STRING Use STRING in place of NULL values .open ?FILENAME? Close existing database and reopen FILENAME .output FILENAME Send output to FILENAME .output stdout Send output to the screen .print STRING... Print literal STRING .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .schema ?TABLE? Show the CREATE statements If TABLE specified, only show tables matching LIKE pattern TABLE. .separator STRING Change separator used by output mode and .import .show Show the current values for various settings .stats ON|OFF Turn stats on or off .tables ?TABLE? List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE. .timeout MS Try opening locked tables for MS milliseconds .trace FILE|off Output each SQL statement as it is run .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode .timer ON|OFF Turn the CPU timer measurement on or off sqlite> }</tcl> <h3>Rules for "dot-commands</h3> <p>Ordinary SQL statements are very much free-form, can be spread across multiple lines, and can have whitespace and comments anywhere. But dot-commands are not like that. The dot-commands are more restrictive: <ul> <li>A dot-command must begin with the "." at the left margin with no preceeding whitespace. <li>The dot-command must be entirely contained on a single input line. <li>A dot-command cannot occur in the middle of an ordinary SQL statement. In other words, a dot-command cannot occur at a continuation prompt. <li>Dot-commands do not recognize comments. </ul> <p>And, of course, it is important to remember that the dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself. So none of the dot-commands will work as an argument to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()]. <h3>Changing Output Formats</h3> <p>The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.</p> ................................................................................ <p>If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.</p> <p>You can specify a negative column width to get right-justified columns.</p> <p>The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:</p> <tcl>Code { ................................................................................ <p>The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.</p> <p>The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:</p> <tcl>Code { sqlite> (((.explain))) sqlite> (((explain delete from tbl1 where two<20;))) addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 18 0 00 2 Null 0 1 0 00 r[1]=NULL 3 OpenRead 0 2 0 2 00 root=2 iDb=0; tbl1 4 Rewind 0 10 0 00 5 Column 0 1 2 00 r[2]=tbl1.two 6 Ge 3 9 2 (BINARY) 6a if r[3]>=r[2] goto 10 7 Rowid 0 4 0 00 r[4]=rowid 8 RowSetAdd 1 4 0 00 rowset(1)=r[4] 9 Next 0 7 0 01 10 Close 0 0 0 00 11 OpenWrite 0 2 0 2 00 root=2 iDb=0; tbl1 12 RowSetRead 1 16 4 00 r[4]=rowset(1) 13 NotExists 0 12 4 1 00 intkey=r[4] 14 Delete 0 1 0 tbl1 00 15 Goto 0 12 0 00 16 Close 0 0 0 00 17 Halt 0 0 0 00 18 Transaction 0 1 0 00 19 VerifyCookie 0 1 0 00 20 TableLock 0 2 1 tbl1 00 iDb=0 root=2 write=1 21 Integer 20 3 0 00 r[3]=20 22 Goto 0 2 0 00 }</tcl> <p>Notice how the shell changes the indentation of some opcodes to help show the loop structure of the VDBE program. <h3>Writing results to a file</h3> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to ................................................................................ sqlite> (((.exit))) $ (((cat test_file_1.txt))) hello|10 goodbye|20 $ }</tcl> <p>If the first character of the ".output" filename is a pipe symbol ("|") then the remaining characters are treated as a command and the output is sent to that command. This makes it easy to pipe the results of a query into some other process. For example, a Mac, the "open -f" command opens a text editor to display the content that it reads from standard input. So to see the results of a query in a text editor, one could type:</p> <tcl>Code { sqlite3> (((.output '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) sqlite3> (((.output stdout))) }</tcl> <h3>Querying the database schema</h3> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.</p> ................................................................................ SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1 </pre></blockquote> <p>In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you'll find a query very much like the above.</p> <p>The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. ................................................................................ $ (((createdb ex2))) $ (((sqlite3 ex1 .dump | psql ex2))) }</tcl> <h3>Other Dot Commands</h3> <p>There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular version and build of SQLite. <h3>Using sqlite3 in a shell script</h3> <p> One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This |