Documentation Source Text

Check-in [24c4bece59]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add documentation on the edit() function and .excel command in the CLI.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 24c4bece599c5c174312a02a580d2e7fd414bb28fb92bd1237a90888beed1e64
User & Date: drh 2018-01-11 00:44:47.731
Context
2018-01-11
18:29
Add sqlite3_vtab_nochange() to the change log. (check-in: 3b8a8bd89a user: drh tags: trunk)
00:44
Add documentation on the edit() function and .excel command in the CLI. (check-in: 24c4bece59 user: drh tags: trunk)
2018-01-10
20:28
Add the "Export to Excel" subparagraph under "CSV Export" section of the CLI documentation. (check-in: 1728ebf59c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
77
78
79
80
81
82
83



84
85
86
87
88
89
90
  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].



  <li> Databases are opened using 
       [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when
       the --append flag is used on the command line or with the
       .open command.
</ol>
<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.







>
>
>







77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].
  <li> Added the [edit() SQL function].
  <li> Added the [export to excel|.excel command] to simplify exporting
       database content to a spreadsheet.
  <li> Databases are opened using 
       [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when
       the --append flag is used on the command line or with the
       .open command.
</ol>
<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.
Changes to pages/cli.in.
156
157
158
159
160
161
162

163
164
165
166
167
168
169
.databases             List names and files of attached databases
.dbinfo ?DB?           Show status information about the database
.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
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN

.exit                  Exit this program
.expert                EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX







>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
.databases             List names and files of attached databases
.dbinfo ?DB?           Show status information about the database
.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
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN
.excel                 Display the output of next command in a spreadsheet
.exit                  Exit this program
.expert                EXPERIMENTAL. Suggest indexes for specified queries
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
184
185
186
187
188
189
190
191


192
193
194
195
196
197
198
199
200
201
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME


.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file
.output ?FILENAME?     Send output to FILENAME or stdout
.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
.save FILE             Write in-memory database into FILE
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off







|
>
>


|







185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once (-e|-x|FILE)     Output for the next SQL command only to FILE
                         or invoke system text editor (-e) or spreadsheet (-x)
                         on the output.
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file
.output ?FILE?         Send output to FILE or stdout
.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
.save FILE             Write in-memory database into FILE
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
431
432
433
434
435
436
437




















438
439
440
441
442
443
444
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tclscript>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>





















<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate reading content from a file into a table column, and writing the
content of a column into a file, respectively.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tclscript>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>

<p>If the ".output" or ".once" commands have an argument of "-e" then
output is collected into a temporary file and the system text editor is
invoked on that text file.  Thus, the command ".once -e" achieves the
same result as ".once '|open -f'" but with the benefit of being portable
across all systems.

<p>If the ".output" or ".once" commands have a "-x" argument, that causes
them to accumulate output as Comma-Separated-Values (CSV) in a temporary
file, then invoke the default system utility for viewing CSV files
(usually a spreadsheet program) on the result.  This is a quick way of
sending the result of a query to a spreadsheet for easy viewing:

<tclscript>DisplayCode {
sqlite3> (((.once -x)))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>

<p>The ".excel" command is an alias for ".once -x".  It does exactly the same
thing.

<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate reading content from a file into a table column, and writing the
content of a column into a file, respectively.
462
463
464
465
466
467
468







































469
470
471
472
473
474
475
}</tclscript>

<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].








































<tcl>hd_fragment schema</tcl>
<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







485
486
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
}</tclscript>

<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].

<tcl>hd_fragment editfunc {edit() SQL function}</tcl>
<h2>The edit() SQL funtion</h2>

<p>The CLI has another build-in SQL function named edit().  Edit() takes
one or two arguments.  The first argument is a value - usually a large
multi-line string to be edited.  The second argument is the name of a
text editor.  If the second argument is omitted, the VISUAL environment
variable is used.  The edit() function writes its first argument into a
temporary file, invokes the editor on the temporary file, rereads the file
back into memory after the editor is done, then returns the edited text.

<p>The edit() function can be used to make changes to large text
values.  For example:

<tclscript>DisplayCode {
sqlite> (((UPDATE docs SET body=edit(body) WHERE name='report-15';)))
}</tclscript>

<p>In this example, the content of the docs.body field for the entry where
docs.name is "report-15" will be sent to the editor.  After the editor returns,
the result will be written back into the docs.body field.

<p>The default operation of edit() is to invoke a text editor.  But by using
an alternative edit program in the second argument, you can also get it to edit
images or other non-text resources.  For example, if you want to modify a JPEG
image that happens to be stored in a field of a table, you could run:

<tclscript>DisplayCode {
sqlite> (((UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';)))
}</tclscript>

<p>The edit program can also be used as a viewer, by simply ignoring the
return value.  For example, to merely look at the image above, you might run:

<tclscript>DisplayCode {
sqlite> (((SELECT length(edit(img,'gimp')) WHERE id='pic-1542';)))
}</tclscript>


<tcl>hd_fragment schema</tcl>
<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655












656

657
658



















659
660
661
662
663
664
665
turned on.)

<p>The line ".once <i>FILENAME</i>" causes all query output to go into
the named file instead of being printed on the console.  In the example
above, that line causes the CSV content to be written into a file named
"C:/work/dataout.csv".

<tcl>hd_fragment exexcel* {export to excel}</tcl>
<h2> Export to Excel </h2>

<p>The final line of the example (the ".system c:/work/dataout.csv")
has the same effect as double-clicking on the c:/work/dataout.csv file
in windows.  This will typically bring up a spreadsheet program to display
the CSV file.

<p>That command only works as written on Windows.  
The equivalent line on a Mac would be:

<tclscript>DisplayCode {
sqlite> (((.system open dataout.csv)))
}</tclscript>

<p>On Linux and other unix systems you will need to enter something like:


<tclscript>DisplayCode {












sqlite> (((.system libreoffice dataout.csv)))

}</tclscript>




















<tcl>hd_fragment dump {.dump}</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>








<
<
<
















>
>
>
>
>
>
>
>
>
>
>
>
|
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







692
693
694
695
696
697
698



699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
turned on.)

<p>The line ".once <i>FILENAME</i>" causes all query output to go into
the named file instead of being printed on the console.  In the example
above, that line causes the CSV content to be written into a file named
"C:/work/dataout.csv".




<p>The final line of the example (the ".system c:/work/dataout.csv")
has the same effect as double-clicking on the c:/work/dataout.csv file
in windows.  This will typically bring up a spreadsheet program to display
the CSV file.

<p>That command only works as written on Windows.  
The equivalent line on a Mac would be:

<tclscript>DisplayCode {
sqlite> (((.system open dataout.csv)))
}</tclscript>

<p>On Linux and other unix systems you will need to enter something like:


<tclscript>DisplayCode {
sqlite> (((.system xdg-open dataout.csv)))
}</tclscript>

<tcl>hd_fragment exexcel* {export to excel}</tcl>
<h2> Export to Excel </h2>

<p>To make it easier to export to a spreadsheet, the CLI provides the
".excel" command, which capture the output of a single query and sends
that result to the default spreadsheet program on the host computer.
Use it like this:

<tclscript>DisplayCode {
sqlite> (((.excel)))
sqlite> (((SELECT * FROM tab;)))
}</tclscript>

<p>
The code above writes the output of the query as CSV into a temporary
file, invokes the default handler for CSV files (usually the preferred
spreadsheet program such as Excel or LibreOffice), then deletes the
temporary file.  This is essentially a short-hand method of doing
the sequence of ".csv", ".once", and ".system" commands described above.

<p>
The ".excel" command is really an alias for ".once -x".  The -x option
to .once causes it to writes results as CSV into a temporary file that
is named with a ".csv" suffix, then invoke the systems default handler
for CSV files.

<p>
There is also a ".once -e" command which works similarly, except that
it names the temporary file with a ".txt" suffix so that the default
text editor for the system will be invoked, instead of the default
spreadsheet.

<tcl>hd_fragment dump {.dump}</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>