Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Updates to the sqlite3 command-line shell documentation. Ticket #2144. (CVS 3573) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
3a422bb9eedf5357ce4d3bed68981c00 |
| User & Date: | drh 2007-01-08 14:31:36 |
Context
|
2007-01-08
| ||
| 16:19 | Fix another round-off problem in strftime(). Ticket #2153. (CVS 3574) (check-in: d49ddc5f user: drh tags: trunk) | |
| 14:31 | Updates to the sqlite3 command-line shell documentation. Ticket #2144. (CVS 3573) (check-in: 3a422bb9 user: drh tags: trunk) | |
| 13:40 | Add the $(BEXE) suffix to every mention of "lemon" in the Makefile.in, so that the build will work on OS/2. Ticket #2149. (CVS 3571) (check-in: f5989a0b user: drh tags: trunk) | |
Changes
Changes to src/shell.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains code to implement the "sqlite" command line 13 13 ** utility for accessing SQLite databases. 14 14 ** 15 -** $Id: shell.c,v 1.157 2006/12/19 18:47:41 drh Exp $ 15 +** $Id: shell.c,v 1.158 2007/01/08 14:31:36 drh Exp $ 16 16 */ 17 17 #include <stdlib.h> 18 18 #include <string.h> 19 19 #include <stdio.h> 20 20 #include <assert.h> 21 21 #include "sqlite3.h" 22 22 #include <ctype.h> ................................................................................ 817 817 return rc; 818 818 } 819 819 820 820 /* 821 821 ** Text of a help message 822 822 */ 823 823 static char zHelp[] = 824 + ".bail ON|OFF Stop after hitting an error. Default OFF\n" 824 825 ".databases List names and files of attached databases\n" 825 826 ".dump ?TABLE? ... Dump the database in an SQL text format\n" 826 827 ".echo ON|OFF Turn command echo on or off\n" 827 828 ".exit Exit this program\n" 828 829 ".explain ON|OFF Turn output mode suitable for EXPLAIN on or off.\n" 829 830 ".header(s) ON|OFF Turn display of headers on or off\n" 830 831 ".help Show this message\n"
Changes to www/sqlite.tcl.
1 1 # 2 2 # Run this Tcl script to generate the sqlite.html file. 3 3 # 4 -set rcsid {$Id: sqlite.tcl,v 1.24 2006/08/19 13:32:05 drh Exp $} 4 +set rcsid {$Id: sqlite.tcl,v 1.25 2007/01/08 14:31:36 drh Exp $} 5 5 source common.tcl 6 -header {sqlite: A command-line access program for SQLite databases} 6 +header {sqlite3: A command-line access program for SQLite databases} 7 7 puts { 8 -<h2>sqlite: A command-line access program for SQLite databases</h2> 8 +<h2>sqlite3: A command-line access program for SQLite databases</h2> 9 9 10 10 <p>The SQLite library includes a simple command-line utility named 11 -<b>sqlite</b> that allows the user to manually enter and execute SQL 11 +<b>sqlite3</b> that allows the user to manually enter and execute SQL 12 12 commands against an SQLite database. This document provides a brief 13 -introduction on how to use <b>sqlite</b>. 13 +introduction on how to use <b>sqlite3</b>. 14 14 15 15 <h3>Getting Started</h3> 16 16 17 -<p>To start the <b>sqlite</b> program, just type "sqlite" followed by 17 +<p>To start the <b>sqlite3</b> program, just type "sqlite3" followed by 18 18 the name the file that holds the SQLite database. If the file does 19 19 not exist, a new one is created automatically. 20 -The <b>sqlite</b> program will 20 +The <b>sqlite3</b> program will 21 21 then prompt you to enter SQL. Type in SQL statements (terminated by a 22 22 semicolon), press "Enter" and the SQL will be executed.</p> 23 23 24 24 <p>For example, to create a new SQLite database named "ex1" 25 25 with a single table named "tbl1", you might do this:</p> 26 26 } 27 27 ................................................................................ 35 35 regsub -all { } $body {\ } body 36 36 regsub -all \n $body <br>\n body 37 37 puts $body 38 38 puts {</tt></blockquote>} 39 39 } 40 40 41 41 Code { 42 -$ (((sqlite ex1))) 43 -SQLite version 2.0.0 42 +$ (((sqlite3 ex1))) 43 +SQLite version 3.3.10 44 44 Enter ".help" for instructions 45 45 sqlite> (((create table tbl1(one varchar(10), two smallint);))) 46 46 sqlite> (((insert into tbl1 values('hello!',10);))) 47 47 sqlite> (((insert into tbl1 values('goodbye', 20);))) 48 48 sqlite> (((select * from tbl1;))) 49 49 hello!|10 50 50 goodbye|20 51 51 sqlite> 52 52 } 53 53 54 54 puts { 55 -<p>You can terminate the sqlite program by typing your systems 55 +<p>You can terminate the sqlite3 program by typing your systems 56 56 End-Of-File character (usually a Control-D) or the interrupt 57 57 character (usually a Control-C).</p> 58 58 59 59 <p>Make sure you type a semicolon at the end of each SQL command! 60 -The sqlite looks for a semicolon to know when your SQL command is 61 -complete. If you omit the semicolon, sqlite will give you a 60 +The sqlite3 program looks for a semicolon to know when your SQL command is 61 +complete. If you omit the semicolon, sqlite3 will give you a 62 62 continuation prompt and wait for you to enter more text to be 63 63 added to the current SQL command. This feature allows you to 64 64 enter SQL commands that span multiple lines. For example:</p> 65 65 } 66 66 67 67 Code { 68 68 sqlite> (((CREATE TABLE tbl2 ())) ................................................................................ 81 81 a special table named "sqlite_master". 82 82 You can execute "SELECT" statements against the 83 83 special sqlite_master table just like any other table 84 84 in an SQLite database. For example:</p> 85 85 } 86 86 87 87 Code { 88 -$ (((sqlite ex1))) 89 -SQlite vresion 2.0.0 88 +$ (((sqlite3 ex1))) 89 +SQlite vresion 3.3.10 90 90 Enter ".help" for instructions 91 91 sqlite> (((select * from sqlite_master;))) 92 92 type = table 93 93 name = tbl1 94 94 tbl_name = tbl1 95 95 rootpage = 3 96 96 sql = create table tbl1(one varchar(10), two smallint) ................................................................................ 110 110 The schema for TEMPORARY tables is not stored in the "sqlite_master" table 111 111 since TEMPORARY tables are not visible to applications other than the 112 112 application that created the table. The schema for TEMPORARY tables 113 113 is stored in another special table named "sqlite_temp_master". The 114 114 "sqlite_temp_master" table is temporary itself. 115 115 </p> 116 116 117 -<h3>Special commands to sqlite</h3> 117 +<h3>Special commands to sqlite3</h3> 118 118 119 119 <p> 120 -Most of the time, sqlite just reads lines of input and passes them 120 +Most of the time, sqlite3 just reads lines of input and passes them 121 121 on to the SQLite library for execution. 122 122 But if an input line begins with a dot ("."), then 123 -that line is intercepted and interpreted by the sqlite program itself. 123 +that line is intercepted and interpreted by the sqlite3 program itself. 124 124 These "dot commands" are typically used to change the output format 125 125 of queries, or to execute certain prepackaged query statements. 126 126 </p> 127 127 128 128 <p> 129 129 For a listing of the available dot commands, you can enter ".help" 130 130 at any time. For example: 131 131 </p>} 132 132 133 133 Code { 134 134 sqlite> (((.help))) 135 +.bail ON|OFF Stop after hitting an error. Default OFF 135 136 .databases List names and files of attached databases 136 -.dump ?TABLE? ... Dump the database in a text format 137 +.dump ?TABLE? ... Dump the database in an SQL text format 137 138 .echo ON|OFF Turn command echo on or off 138 139 .exit Exit this program 139 140 .explain ON|OFF Turn output mode suitable for EXPLAIN on or off. 140 141 .header(s) ON|OFF Turn display of headers on or off 141 142 .help Show this message 143 +.import FILE TABLE Import data from FILE into TABLE 142 144 .indices TABLE Show names of all indices on TABLE 143 -.mode MODE Set mode to one of "line(s)", "column(s)", 144 - "insert", "list", or "html" 145 -.mode insert TABLE Generate SQL insert statements for TABLE 146 -.nullvalue STRING Print STRING instead of nothing for NULL data 145 +.load FILE ?ENTRY? Load an extension library 146 +.mode MODE ?TABLE? Set output mode where MODE is one of: 147 + csv Comma-separated values 148 + column Left-aligned columns. (See .width) 149 + html HTML <table> code 150 + insert SQL insert statements for TABLE 151 + line One value per line 152 + list Values delimited by .separator string 153 + tabs Tab-separated values 154 + tcl TCL list elements 155 +.nullvalue STRING Print STRING in place of NULL values 147 156 .output FILENAME Send output to FILENAME 148 157 .output stdout Send output to the screen 149 158 .prompt MAIN CONTINUE Replace the standard prompts 150 159 .quit Exit this program 151 160 .read FILENAME Execute SQL in FILENAME 152 161 .schema ?TABLE? Show the CREATE statements 153 -.separator STRING Change separator string for "list" mode 162 +.separator STRING Change separator used by output mode and .import 154 163 .show Show the current values for various settings 155 -.tables ?PATTERN? List names of tables matching a pattern 164 +.tables ?PATTERN? List names of tables matching a LIKE pattern 156 165 .timeout MS Try opening locked tables for MS milliseconds 157 166 .width NUM NUM ... Set column widths for "column" mode 158 167 sqlite> 159 168 } 160 169 161 170 puts { 162 171 <h3>Changing Output Formats</h3> 163 172 164 -<p>The sqlite program is able to show the results of a query 165 -in five different formats: "line", "column", "list", "html", and "insert". 173 +<p>The sqlite3 program is able to show the results of a query 174 +in eight different formats: "csv", "column", "html", "insert", 175 +"line", "tabs", and "tcl". 166 176 You can use the ".mode" dot command to switch between these output 167 177 formats.</p> 168 178 169 179 <p>The default output mode is "list". In 170 180 list mode, each record of a query result is written on one line of 171 181 output and each column within that record is separated by a specific 172 182 separator string. The default separator is a pipe symbol ("|"). ................................................................................ 283 293 sqlite> (((select * from tbl1;))) 284 294 INSERT INTO 'new_table' VALUES('hello',10); 285 295 INSERT INTO 'new_table' VALUES('goodbye',20); 286 296 sqlite> 287 297 } 288 298 289 299 puts { 290 -<p>The last output mode is "html". In this mode, sqlite writes 300 +<p>The last output mode is "html". In this mode, sqlite3 writes 291 301 the results of the query as an XHTML table. The beginning 292 302 <TABLE> and the ending </TABLE> are not written, but 293 303 all of the intervening <TR>s, <TH>s, and <TD>s 294 304 are. The html output mode is envisioned as being useful for 295 305 CGI.</p> 296 306 } 297 307 298 308 puts { 299 309 <h3>Writing results to a file</h3> 300 310 301 -<p>By default, sqlite sends query results to standard output. You 311 +<p>By default, sqlite3 sends query results to standard output. You 302 312 can change this using the ".output" command. Just put the name of 303 313 an output file as an argument to the .output command and all subsequent 304 314 query results will be written to that file. Use ".output stdout" to 305 315 begin writing to standard output again. For example:</p>} 306 316 307 317 Code { 308 318 sqlite> (((.mode list))) ................................................................................ 315 325 goodbye|20 316 326 $ 317 327 } 318 328 319 329 puts { 320 330 <h3>Querying the database schema</h3> 321 331 322 -<p>The sqlite program provides several convenience commands that 332 +<p>The sqlite3 program provides several convenience commands that 323 333 are useful for looking at the schema of the database. There is 324 334 nothing that these commands do that cannot be done by some other 325 335 means. These commands are provided purely as a shortcut.</p> 326 336 327 337 <p>For example, to see a list of the tables in the database, you 328 338 can enter ".tables".</p> 329 339 } ................................................................................ 332 342 sqlite> (((.tables))) 333 343 tbl1 334 344 tbl2 335 345 sqlite> 336 346 } 337 347 338 348 puts { 339 -<p>The ".tables" command is the same as setting list mode then 349 +<p>The ".tables" command is similar to setting list mode then 340 350 executing the following query:</p> 341 351 342 352 <blockquote><pre> 343 -SELECT name FROM sqlite_master WHERE type='table' 344 -UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' 345 -ORDER BY name; 353 +SELECT name FROM sqlite_master 354 +WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' 355 +UNION ALL 356 +SELECT name FROM sqlite_temp_master 357 +WHERE type IN ('table','view') 358 +ORDER BY 1 346 359 </pre></blockquote> 347 360 348 -<p>In fact, if you look at the source code to the sqlite program 361 +<p>In fact, if you look at the source code to the sqlite3 program 349 362 (found in the source tree in the file src/shell.c) you'll find 350 363 exactly the above query.</p> 351 364 352 365 <p>The ".indices" command works in a similar way to list all of 353 366 the indices for a particular table. The ".indices" command takes 354 367 a single argument which is the name of the table for which the 355 368 indices are desired. Last, but not least, is the ".schema" command. ................................................................................ 391 404 <p>Or, if you give an argument to ".schema" because you only 392 405 want the schema for a single table, the query looks like this:</p> 393 406 394 407 <blockquote><pre> 395 408 SELECT sql FROM 396 409 (SELECT * FROM sqlite_master UNION ALL 397 410 SELECT * FROM sqlite_temp_master) 398 -WHERE tbl_name LIKE '%s' AND type!='meta' 399 -ORDER BY type DESC, name 411 +WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' 412 +ORDER BY substr(type,2,1), name 413 +</pre></blockquote> 414 + 415 +<p> 416 +You can supply an argument to the .schema command. If you do, the 417 +query looks like this: 418 +</p> 419 + 420 +<blockquote><pre> 421 +SELECT sql FROM 422 + (SELECT * FROM sqlite_master UNION ALL 423 + SELECT * FROM sqlite_temp_master) 424 +WHERE tbl_name LIKE '%s' 425 + AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' 426 +ORDER BY substr(type,2,1), name 400 427 </pre></blockquote> 401 428 402 -<p>The <b>%s</b> in the query above is replaced by the argument 403 -to ".schema", of course. Notice that the argument to the ".schema" 404 -command appears to the right of an SQL LIKE operator. So you can 405 -use wildcards in the name of the table. For example, to get the 406 -schema for all tables whose names contain the character string 407 -"abc" you could enter:</p>} 429 +<p>The "%s" in the query is replace by your argument. This allows you 430 +to view the schema for some subset of the database.</p> 431 +} 408 432 409 433 Code { 410 434 sqlite> (((.schema %abc%))) 411 435 } 412 436 413 437 puts { 414 438 <p> ................................................................................ 432 456 } 433 457 434 458 puts { 435 459 <h3>Converting An Entire Database To An ASCII Text File</h3> 436 460 437 461 <p>Use the ".dump" command to convert the entire contents of a 438 462 database into a single ASCII text file. This file can be converted 439 -back into a database by piping it back into <b>sqlite</b>.</p> 463 +back into a database by piping it back into <b>sqlite3</b>.</p> 440 464 441 465 <p>A good way to make an archival copy of a database is this:</p> 442 466 } 443 467 444 468 Code { 445 -$ (((echo '.dump' | sqlite ex1 | gzip -c >ex1.dump.gz))) 469 +$ (((echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz))) 446 470 } 447 471 448 472 puts { 449 473 <p>This generates a file named <b>ex1.dump.gz</b> that contains everything 450 474 you need to reconstruct the database at a later time, or on another 451 475 machine. To reconstruct the database, just type:</p> 452 476 } 453 477 454 478 Code { 455 -$ (((zcat ex1.dump.gz | sqlite ex2))) 479 +$ (((zcat ex1.dump.gz | sqlite3 ex2))) 456 480 } 457 481 458 482 puts { 459 -<p>The text format used is the same as used by 460 -<a href="http://www.postgresql.org/">PostgreSQL</a>, so you 483 +<p>The text format is pure SQL so you 461 484 can also use the .dump command to export an SQLite database 462 -into a PostgreSQL database. Like this:</p> 485 +into other popular SQL database engines. Like this:</p> 463 486 } 464 487 465 488 Code { 466 489 $ (((createdb ex2))) 467 -$ (((echo '.dump' | sqlite ex1 | psql ex2))) 468 -} 469 - 470 -puts { 471 -<p>You can almost (but not quite) go the other way and export 472 -a PostgreSQL database into SQLite using the <b>pg_dump</b> utility. 473 -Unfortunately, when <b>pg_dump</b> writes the database schema information, 474 -it uses some SQL syntax that SQLite does not understand. 475 -So you cannot pipe the output of <b>pg_dump</b> directly 476 -into <b>sqlite</b>. 477 -But if you can recreate the 478 -schema separately, you can use <b>pg_dump</b> with the <b>-a</b> 479 -option to list just the data 480 -of a PostgreSQL database and import that directly into SQLite.</p> 481 -} 482 - 483 -Code { 484 -$ (((sqlite ex3 <schema.sql))) 485 -$ (((pg_dump -a ex2 | sqlite ex3))) 490 +$ (((sqlite3 ex1 .dump | psql ex2))) 486 491 } 487 492 488 493 puts { 489 494 <h3>Other Dot Commands</h3> 490 495 491 496 <p>The ".explain" dot command can be used to set the output mode 492 497 to "column" and to set the column widths to values that are reasonable ................................................................................ 517 522 12 Delete 0 0 518 523 13 Goto 0 11 519 524 14 ListClose 0 0 520 525 } 521 526 522 527 puts { 523 528 524 -<p>The ".timeout" command sets the amount of time that the <b>sqlite</b> 529 +<p>The ".timeout" command sets the amount of time that the <b>sqlite3</b> 525 530 program will wait for locks to clear on files it is trying to access 526 531 before returning an error. The default value of the timeout is zero so 527 532 that an error is returned immediately if any needed database table or 528 533 index is locked.</p> 529 534 530 535 <p>And finally, we mention the ".exit" command which causes the 531 -sqlite program to exit.</p> 536 +sqlite3 program to exit.</p> 532 537 533 -<h3>Using sqlite in a shell script</h3> 538 +<h3>Using sqlite3 in a shell script</h3> 534 539 535 540 <p> 536 -One way to use sqlite in a shell script is to use "echo" or 537 -"cat" to generate a sequence of commands in a file, then invoke sqlite 541 +One way to use sqlite3 in a shell script is to use "echo" or 542 +"cat" to generate a sequence of commands in a file, then invoke sqlite3 538 543 while redirecting input from the generated command file. This 539 544 works fine and is appropriate in many circumstances. But as 540 -an added convenience, sqlite allows a single SQL command to be 545 +an added convenience, sqlite3 allows a single SQL command to be 541 546 entered on the command line as a second argument after the 542 -database name. When the sqlite program is launched with two 547 +database name. When the sqlite3 program is launched with two 543 548 arguments, the second argument is passed to the SQLite library 544 549 for processing, the query results are printed on standard output 545 550 in list mode, and the program exits. This mechanism is designed 546 -to make sqlite easy to use in conjunction with programs like 551 +to make sqlite3 easy to use in conjunction with programs like 547 552 "awk". For example:</p>} 548 553 549 554 Code { 550 -$ (((sqlite ex1 'select * from tbl1' |))) 555 +$ (((sqlite3 ex1 'select * from tbl1' |))) 551 556 > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'))) 552 557 <tr><td>hello<td>10 553 558 <tr><td>goodbye<td>20 554 559 $ 555 560 } 556 561 557 562 puts { 558 563 <h3>Ending shell commands</h3> 559 564 560 565 <p> 561 566 SQLite commands are normally terminated by a semicolon. In the shell 562 567 you can also use the word "GO" (case-insensitive) or a slash character 563 568 "/" on a line by itself to end a command. These are used by SQL Server 564 -and Oracle, respectively. These won't work in <b>sqlite_exec()</b>, 569 +and Oracle, respectively. These won't work in <b>sqlite3_exec()</b>, 565 570 because the shell translates these into a semicolon before passing them 566 571 to that function.</p> 567 572 } 568 573 569 574 puts { 570 -<h3>Compiling the sqlite program from sources</h3> 575 +<h3>Compiling the sqlite3 program from sources</h3> 571 576 572 577 <p> 573 -The sqlite program is built automatically when you compile the 574 -sqlite library. Just get a copy of the source tree, run 578 +The sqlite3 program is built automatically when you compile the 579 +SQLite library. Just get a copy of the source tree, run 575 580 "configure" and then "make".</p> 576 581 } 577 582 footer $rcsid