sqlite3

sqlite3 [options] [databasefile] ['SQLstatments']

Terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results in multiple formats.
Can be used within shell scripts and other applications to provide batch processing features.

-init file execute file, containng SQL and meta-commands.
-echo output commands before execution. (useful if commands are being executed from a file)
-[no]header  
-separator sep output field separator. Default |.
-nullvalue string string used to represent NULL values. Default '' (empty string).
-version
-help
Results display format:
-list with the separator (|?, by default) between each field value.
-column in a table like form, using whitespace characters to separate the columns and align the output.
-html simple HTML tables.
-line one value per line, rows separated by a blank line. Easily parsed by other programs

sqlite3 db "vacuum;" can be used to "clean up" the database.

If the database file does not exist, it will be silently created (i.e. no message like "database does not exist is displayed").

Example: create a new database file named mydata.db, create a table named memos and insert a couple of records into it;


       $ sqlite3 mydata.db  -column -header
       SQLite version 3.7.13  with MAC OSX Mavericks 10.9
       Enter ".help" for instructions
       sqlite> create table memos(text, priority INTEGER);
       sqlite> insert into memos values('deliver project description', 10);
       sqlite> insert into memos values('lunch with Chris', 100);
       sqlite> select * from memos order by priority;
        text                         priority  
        ---------------------------  ----------
        deliver project description  10        
        lunch with Chris             100 
       sqlite>

ATTACH to existing or create new database files or
attach to multiple databases within the same interactive session.
Useful for migrating data between databases, possibly changing the schema.

SQL statements (separated by semi-colons) can be supplied as an argument. For example:

sqlite3 -line mydata.db 'select * from memos where priority>20; text=lunch with Chris priority=100'

META-COMMANDS

Control the output format, examine the currently attached database files, or perform administrative operations upon the attached databases (such as rebuilding indices). Meta-commands are prefixed with a dot (.) (unlike mysql where they are prefixed with \.


       sqlite> .help       rearranged by ed
          .databases              List names and files of attached databases
          .tables [?TABL?]       List names of tables
                                   If TABL specified, only list tables matching LIKE pattern TABL.

          .schema [?TABL?]       Show the CREATE statements
                                   If TABL specified, only show tables matching LIKE pattern TABL.

          .dump ?TABL? ...      Dump the database in an SQL text format
                                   If TABL specified, only dump tables matching LIKE pattern TABL.
          .read FILENAME         Execute SQL in FILENAME
          .echo ON|OFF           
          .output FILENAME       
          .output stdout         

          .explain ON|OFF      Turn output mode suitable for EXPLAIN on or off.default on
          .import FILE TABLE     Import data from FILE into TABLE
          .indices ?TABL?       Show names of all indices
                                   If TABL specified, only show indices for tables matching LIKE pattern TABL.
          .log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
          .mode MODE ?TABL?     Set output mode where MODE is one of:

csv Comma-separated values

label,weight,source,identifier,version,conditions
"google chrome",200.0,EQHXZ8M8AV,com.google.Chrome,,{errors=[-67013]}
"google chrome (canary)",300.0,EQHXZ8M8AV,com.google.Chrome.canary,,{errors=[-67013]}
column Left-aligned columns. (See .width)

label          weight      source      identifier         version     conditions       
-------------  ----------  ----------  -----------------  ----------  -----------------
google chrome  200.0       EQHXZ8M8AV  com.google.Chrome              {errors=[-67013]}
google chrome  300.0       EQHXZ8M8AV  com.google.Chrome              {errors=[-67013]}


label|weight|source|identifier|version|conditions
google chrome|200.0|EQHXZ8M8AV|com.google.Chrome||{errors=[-67013]}
google chrome (canary)|300.0|EQHXZ8M8AV|com.google.Chrome.canary||{errors=[-67013]}


html HTML <table> code
 <TR><TH>label</TH> <TH>weight</TH> <TH>source</TH>
  <TH>identifier</TH> <TH>version</TH> <TH>conditions</TH> </TR>
<TR><TD>google chrome</TD> <TD>200.0</TD> <TD>EQHXZ8M8AV</TD>
   <TD>com.google.Chrome</TD> <TD></TD> <TD>{errors=[-67013]}</TD> </TR>
<TR><TD>google chrome (canary)</TD> <TD>300.0</TD> <TD>EQHXZ8M8AV</TD>
   <TD>com.google.Chrome.canary</TD> <TD></TD> <TD>{errors=[-67013]}</TD> </TR> 
label weight source identifier version conditions
google chrome 200.0 EQHXZ8M8AV com.google.Chrome {errors=[-67013]}
google chrome (canary) 300.0 EQHXZ8M8AV com.google.Chrome.canary {errors=[-67013]}
insert SQL insert statements for TABLE
INSERT INTO table(label,weight,source,identifier,version,conditions)
    VALUES('google chrome',200.0,'EQHXZ8M8AV','com.google.Chrome',NULL,'{errors=[-67013]}');
INSERT INTO table(label,weight,source,identifier,version,conditions) 
   VALUES('google chrome (canary)',300.0,'EQHXZ8M8AV','com.google.Chrome.canary',NULL,'{errors=[-67013]}');
line One value per line
     label = google chrome
    weight = 200.0
    source = EQHXZ8M8AV
identifier = com.google.Chrome
   version = 
conditions = {errors=[-67013]} 
     label = google chrome (canary)
    weight = 300.0
    source = EQHXZ8M8AV
identifier = com.google.Chrome.canary
   version = 
conditions = {errors=[-67013]}
list Values delimited by .separator string label,weight,source,identifier,version,conditions google chrome,200.0,EQHXZ8M8AV,com.google.Chrome,,{errors=[-67013]} google chrome (canary),300.0,EQHXZ8M8AV,com.google.Chrome.canary,,{errors=[-67013]}
tabs Tab-separated values
label weight source identifier version conditions
google chrome 200.0 EQHXZ8M8AV com.google.Chrome {errors=[-67013]}
google chrome (canary) 300.0 EQHXZ8M8AV com.google.Chrome.canary {errors=[-67013]}
tcl TCL list elements
"label" "weight" "source" "identifier" "version" "conditions"
"google chrome" "200.0" "EQHXZ8M8AV" "com.google.Chrome" "" "{errors=[-67013]}"
"google chrome (canary)" "300.0" "EQHXZ8M8AV" "com.google.Chrome.canary" "" "{errors=[-67013]}"

.header(s) ON|OFF Turn display of headers on or off .width NUM1 NUM2 ... Set column widths for "column" mode .bail ON|OFF Stop after hitting an error. Default OFF .prompt MAIN CONTINUE Replace the prompts .backup ?DB? FILE default "main" .restore ?DB? FILE default "main" .nullvalue STRING Print STRING in place of NULL values .separator STRING Change separator used by output mode and .import .show current values for various settings echo: off eqp: off explain: off headers: off mode: list nullvalue: "" output: stdout colseparator: "|" rowseparator: "\n" stats: off width: .stats ON|OFF Turn stats on or off .timeout MS Try opening locked tables for MS milliseconds .trace FILE|off Output each SQL statement as it is run .vfsname ?AUX? Output the name of the VFS stack unix .timer ON|OFF Turn the CPU timer measurement on or off Run Time: real 0.001 user 0.000098 sys 0.000046 .exit .quit .help

INIT FILE

The sequence of initialization is :
  1. The defaults:
              mode            = LIST
              separator       = "|"
              main prompt     = "sqlite> "
              continue prompt = "   ...> "
  2. ~/.sqliterc if it is readable, (It should generally only contain meta-commands.)
  3. -init file if it is readable
  4. command line options

See also

SQLite.org current version as of 12/14/15 is 3.9.2. The sqlite-doc package data manuliption commands

Example

see ~/.bin/0ShowQuarantineEvents.sh
~/Library/Preferences/com.apple.LaunchServices.QuarantineEventsV2

CREATE TABLE LSQuarantineEvent (
LSQuarantineEventIdentifier TEXT PRIMARY KEY NOT NULL,
LSQuarantineTimeStamp REAL, yuck
LSQuarantineAgentBundleIdentifier TEXT,
LSQuarantineAgentName TEXT,
LSQuarantineDataURLString TEXT,
LSQuarantineSenderName TEXT,
LSQuarantineSenderAddress TEXT,
LSQuarantineTypeNumber INTEGER,
LSQuarantineOriginTitle TEXT,
LSQuarantineOriginURLString TEXT,
LSQuarantineOriginAlias BLOB );
CREATE INDEX LSQuarantineEventIndex ON LSQuarantineEvent ( LSQuarantineEventIdentifier );