Top five commands in bold

Run MacOS shell commands from Crab prompt

! at the start of a line sends the rest of the line to the operating system shell to be executed.

  CRAB> ! mkdir ~/Dropbox/backups

• ; doesn't terminate the input, semicolons are sent to the OS along with everything else on the line.
• It's not multi line
• You can't combine ! with SQL or %-commands on the same line

%autoimport filename [table]

Import filename to table, automatically detecting separators and datatypes.


See %import command

%color darkmode | off | default

Change Crab color scheme


EXAMPLES

  %color darkmode

CRAB> select fullpath, bytes, X'65' from files limit 5;
{fullpath: /Volumes/, bytes: 238, X'65': <Binary data>}
{fullpath: /Volumes/BUDevelopment2c/, bytes: 1360, X'65': <Binary data>}
{fullpath: /Volumes/Development2/, bytes: 1, X'65': <Binary data>}
{fullpath: /Volumes/Games/, bytes: 1088, X'65': <Binary data>}
{fullpath: /Volumes/MobileBackups/, bytes: 0, X'65': <Binary data>}
CRAB> select ;
SQLError: near ";": syntax error

CRAB> select fullpath, bytes, X'65' from files limit 5;
{fullpath: /Volumes/, bytes: 238, X'65': <Binary data>}
{fullpath: /Volumes/BUDevelopment2c/, bytes: 1360, X'65': <Binary data>}
{fullpath: /Volumes/Development2/, bytes: 1, X'65': <Binary data>}
{fullpath: /Volumes/Games/, bytes: 1088, X'65': <Binary data>}
{fullpath: /Volumes/MobileBackups/, bytes: 0, X'65': <Binary data>}
CRAB> select ;
SQLError: near ";": syntax error

%cruft on|off

If on Crab prints intro text at start up, and progress counts during scanning.

Default is on when using the Crab command line, and off in batch mode.



See -batch start up option
Startup option to run without Crab command line, and return results to bash shell

%echo on|off

If on each SQL statement is printed before it is executed.

Default is off.

%encoding  encodingType:errorHandler

Specifies how Crab should map byte sequences to characters when reading files, and vice-versa when writing.

Affects reading from the fileslines table, and the %import and %autoimport commands. Also writing to files using writeln(), write() and %output.

Default value is utf8:skipfile

Use %show command to see the current value.

  CRAB> %encoding utf8  

  CRAB> %encoding cp1252:replace   

  CRAB> %encoding utf_8:skipfile   

  CRAB> %encoding ascii:ignore

  CRAB> %encoding binary:80

  CRAB> %encoding binary:-1

  %encoding ascii:ignore

  SELECT writeln('funnyCharsGone.py',data) FROM fileslines 
  WHERE fullpath = '/Users/Kerabatsos/funnyChars.py';

• If no error handler is specified, an error is raised, file reading stops and the query or command is aborted.
• If replace is specified, a character is substituted for characters that don't match
• If ignore is specified, characters that don't match are quietly ignored.
• If skipfile is specified, characters that don't match cause Crab to move on to the next file in the fileslines query. This speeds up searches when you know the file you want has a specific encoding.

  %encoding binary:80

  
  %encoding binary:40
  %mode list
  %headers off

  SELECT linenumber,  hex(data), bytestr(data,".") FROM fileslines 
  WHERE fullpath = '/Users/peterdalloz/Dropbox/.DS_Store'  
  AND data NOT MATCH u('^\x00*$') LIMIT 10;

CRAB> select linenumber, hex(data), bytestr(data,".") from fileslines where fullpath = '/Users/peterdalloz/Dropbox/.DS_Store' and data not match u('^\x00*$') limit 10;
1,00000001427564310000980000000800000098000000200C0000100B0000300C0000400C00000000,....Bud1.............. .......0...@.....
2,000000000000080000000800000000000000000000000000000000000000000B00000002000000F8,........................................
3,0000000B00001000004A004500430054004900560000000000000000000000000000000000000000,.........J.E.C.T.I.V....................
26,0000000000000000000000000000000000000000000000000000000000000003000000010000000C,........................................
27,0000000A004F0042004A00450043005400490056004500436C737650626C6F62000003A162706C69,.....O.B.J.E.C.T.I.V.E.ClsvPblob....bpli
28,73743030DA0102030405060708090A0B0C0D0E5E5F60140C625F1012766965774F7074696F6E7356,st00...............^_`..b_..viewOptionsV
29,657273696F6E5F100F73686F7749636F6E507265766965775F101163616C63756C617465416C6C53,ersion_..showIconPreview_..calculateAllS
30,697A657357636F6C756D6E735F100F7363726F6C6C506F736974696F6E59587465787453697A655F,izesWcolumns_..scrollPositionYXtextSize_
31,100F7363726F6C6C506F736974696F6E585A736F7274436F6C756D6E5F101075736552656C617469,..scrollPositionXZsortColumn_..useRelati
32,766544617465735869636F6E53697A6510010908AA0F1820272F373F474F57D41011121314150C0C,veDatesXiconSize....... '/7?GOW.........

The where clause in this query uses regular expression matching to return lines that are not completely filled with zero bytes: The u() function converts \xnn style escape sequences to bytes, and the regex match function uses this to search for lines that contain something other than repeated 0 byte values.

  
  %encoding binary:-1
  %mode line

  SELECT hex(substr(data,600,40)), bytestr(substr(data,600,40),".") 
  FROM fileslines WHERE fullpath = '/Users/peterdalloz/Dropbox/PYTHONDROPBOX/sqlsys/docs/crab intro.docx';

CRAB> %encoding binary:-1
CRAB> %mode line
CRAB> select hex(substr(data,600,40)), bytestr(substr(data,600,40),".") from fileslines where fullpath = '/Users/peterdalloz/Dropbox/PYTHONDROPBOX/sqlsys/docs/crab intro.docx';
        hex(substr(data,600,40)) = 4E44AD07D2E4F5F71DC7AE2925D8A58937067974EF3D3386F164B63765B28510B5B3391B67239680
bytestr(substr(data,600,40),".") = ND.........)%...7.yt.=3..d.7e.....9.g#..

See Crab's bytestr function
Prints bytes that are printable ascii as ascii characters, everything else as \xnn escape sequences

See Crab's codestr function
Prints chars that are printable ascii as ascii characters, everything else as \unnn escape sequences

See Crab's u() function
Converts strings with \x and \u escape sequences to strings with unicode characters.

See -encoding start up option
Start up option that does the same job as the %encoding command

%explain on|off

If on set output mode suitable for SQL EXPLAIN. If off reset output mode to what it was before.



See %mode command
To read about output modes.

%header on|off

If on display column names in output, when using output mode column or list

Default is on.



See %mode command
To read about output modes.

%import file table

Import delimited data from file to table (e.g. a CSV file)

The default separator is a comma, use the %separator command to change it.
table should already have been created, with suitable datatypes.



See %autoimport command
To import without needing to specify separators

%license emailAddy licenseKey

Make some software developers very happy

  %license headCrab@etia.co.uk a009999_71b42819d3ab456f3d

%matchsep string

Change the separator used between groupn results when there are multiple matches for the group

Default is '|' (pipe symbol). Use %show to see current setting.

  %matchsep "\t"

%mode column|dict|line|list

Change the layout of query results.

Default is %mode dict. Use %show to see current setting.


Available Modes

column, csv, dict, html, insert, json, line, list, python, and tcl



EXAMPLES

  %mode column

CRAB> select * from files limit 3;
fileid      name        bytes       depth       accessed    modified    basename    extension   type        mode        parentpath  fullpath  
1           PYTHONDROP  782         0           2015‑08‑15  2015‑07‑28  PYTHONDROP              d           drwxr‑xr‑x  /Users/pet  /Users/pet
2           .DS_Store   14340       1           2015‑08‑15  2015‑08‑11  .DS_Store               f           ‑rw‑r‑‑r‑‑  /Users/pet  /Users/pet
3           getMssql.p  694         1           2015‑08‑07  2013‑09‑08  getMssql    .py         f           ‑rw‑r‑‑r‑‑  /Users/pet  /Users/pet

Old school fixed-width mode for database output. Values in each row are column aligned, with a default column width of 10 characters. Difficult to read for wide result sets.
Change column widths using the %width command
Turn first row headers on or off with the %headers command

  %mode dict

CRAB> select * from files limit 3;
{fileid: 1, name: PYTHONDROPBOX, bytes: 782, depth: 0, accessed: 2015-08-15T17:52:12, modified: 2015-07-28T10:14:45, basename: PYTHONDROPBOX, extension: , type: d, mode: drwxr-xr-x, parentpath: /Users/peterdalloz/Dropbox/, fullpath: /Users/peterdalloz/Dropbox/PYTHONDROPBOX/}
{fileid: 2, name: .DS_Store, bytes: 14340, depth: 1, accessed: 2015-08-15T14:42:25, modified: 2015-08-11T10:20:52, basename: .DS_Store, extension: , type: f, mode: -rw-r--r--, parentpath: /Users/peterdalloz/Dropbox/PYTHONDROPBOX/, fullpath: /Users/peterdalloz/Dropbox/PYTHONDROPBOX/.DS_Store}
{fileid: 3, name: getMssql.py, bytes: 694, depth: 1, accessed: 2015-08-07T17:31:52, modified: 2013-09-08T22:37:24, basename: getMssql, extension: .py, type: f, mode: -rw-r--r--, parentpath: /Users/peterdalloz/Dropbox/PYTHONDROPBOX/, fullpath: /Users/peterdalloz/Dropbox/PYTHONDROPBOX/getMssql.py}

Dictionary style set of name, value pairs. Convenient for wide results sets. Crab's default output mode for queries from the Crab command line.

  %mode line

CRAB> select * from files limit 3;
    fileid = 1
      name = PYTHONDROPBOX
     bytes = 782
     depth = 0
  accessed = 2015-08-15T17:52:12
  modified = 2015-07-28T10:14:45
  basename = PYTHONDROPBOX
 extension =
      type = d
      mode = drwxr-xr-x
parentpath = /Users/peterdalloz/Dropbox/
  fullpath = /Users/peterdalloz/Dropbox/PYTHONDROPBOX/

    fileid = 2
      name = .DS_Store
     bytes = 14340
     depth = 1
  accessed = 2015-08-15T14:42:25
  modified = 2015-08-11T10:20:52
  basename = .DS_Store
 extension =
      type = f
      mode = -rw-r--r--
parentpath = /Users/peterdalloz/Dropbox/PYTHONDROPBOX/
  fullpath = /Users/peterdalloz/Dropbox/PYTHONDROPBOX/.DS_Store

    fileid = 3
      name = getMssql.py
     bytes = 694
     depth = 1
  accessed = 2015-08-07T17:31:52
  modified = 2013-09-08T22:37:24
  basename = getMssql
 extension = .py
      type = f
      mode = -rw-r--r--
parentpath = /Users/peterdalloz/Dropbox/PYTHONDROPBOX/
  fullpath = /Users/peterdalloz/Dropbox/PYTHONDROPBOX/getMssql.py

Each value is output on a separate line, prefixed by the field name.

  %mode list

CRAB> select * from files limit 3;
fileid,name,bytes,depth,accessed,modified,basename,extension,type,mode,parentpath,fullpath
1,PYTHONDROPBOX,782,0,2015-08-15T17:52:12,2015-07-28T10:14:45,PYTHONDROPBOX,,d,drwxr-xr-x,/Users/peterdalloz/Dropbox/,/Users/peterdalloz/Dropbox/PYTHONDROPBOX/
2,.DS_Store,14340,1,2015-08-15T14:42:25,2015-08-11T10:20:52,.DS_Store,,f,-rw-r--r--,/Users/peterdalloz/Dropbox/PYTHONDROPBOX/,/Users/peterdalloz/Dropbox/PYTHONDROPBOX/.DS_Store
3,getMssql.py,694,1,2015-08-07T17:31:52,2013-09-08T22:37:24,getMssql,.py,f,-rw-r--r--,/Users/peterdalloz/Dropbox/PYTHONDROPBOX/,/Users/peterdalloz/Dropbox/PYTHONDROPBOX/getMssql.py

%nullvalue string

Change text printed in place of null values in query results
Default is '', an empty string. Use %show to see current setting.

  %nullvalue " \t "

%online

Open etia.co.uk in your default browser for Crab news, documentation and pricing.

%output filename|stdout

Redirect output to a file, or reset output back to the screen
Use this command as an alternative to Crab's writeln function when you want to write query results to a file, or when you use exec or writeln commands on query results with many rows. These commands send output to the screen for each row operated on, which may clutter your screen or slow down performance.

NB %output overwrites files if they already exist.

Use %show to see current setting.


EXAMPLES

  %output /dev/null

  select exec('cp', fullpath, '/Users/petergibbons/tpsreports/'||name||'-'||fileid) from fileslines 
  where parentpath = '/Users/petergibbons/LR427/' and data like '%cents%' 
  group by fullpath;

  %output stdout

  %mode list
  %output payer_payee.csv

  -- transpose csv file and strip header
  select group_concat(itemn(data, i, ','))
  from fileslines join iterator on i <= itemcount(data, ',') 
  where name = 'payee_payer.csv'  and linenumber > 1
  group by i;

  %output stdout

%quit

Exit Crab. Data will be retained until overwritten by the next scan.

If you start Crab again without giving a scan path, you'll be able to continue querying the same data.

%read filename

Read and execute Crab commands and SQL from a text file.
We recommend a .crab extension for Crab script files, but it's not compulsory.


EXAMPLES

  CRAB> %read listdups.crab

  $ crab -batch ~/myproject '%read listdups.crab'

%refresh

Update the files table with the latest contents of the filesystem by re-scanning the same path(s)

Use %show to see most recent scan path.

  crab ~/myproject/*.txt

  crab ~/myproject/

%schema [table]

Show SQL schema for the specified table and related indexes.
If no table is specified, show them all.

  ?files

  ?fileslines

%separator string

Change the separator used between fields for output in list mode, and for %import of text files

Default is ','

Use %show to see the current separator.

You can use quotes and c-style escapes.

  %separator ,

  %separator '\t'

%show [settingName]

Show the current value of each of Crab's settings

Specify a name to see the current value for that setting only, otherwise Crab lists them all.


Example

CRAB> %show
  DATABASE: /Users/peterdalloz/wholedisk.crdb
 SCAN PATH: /
 MAX DEPTH: None
 TIMESTAMP: 2015-08-11 09:19
      CASE: filenames are not case sensitive
     cruft: on
      echo: off
   explain: off
   headers: on
      mode: dict
 nullvalue: ""
    output: stdout
 separator: ","
     width: 
  matchsep: "|"
exceptions: off
  encoding: utf8 (Errors replace)
   license: Unlicensed.  Expires 1/1/2016.
CRAB> 

%tables

List the tables in the database

See %schema command
To see tables, columns and indexes

%version

Displays the Crab version number, and which versions of the APSW and SQLite libraries it was built on.

See Acknowledgements
For thank you's to Roger Binns of APSW fame, and the SQLite team

%width

Sets column widths for column output mode

You can set column widths by column number, or by the alias or field name of the expression displayed

Default width is 10.

EXAMPLES

Default widths

CRAB> %mode column
CRAB> select fullpath, bytes from files limit 4;
fullpath    bytes     
/Users/pet  1972      
/Users/pet  43012     
/Users/pet  12661     
/Users/pet  283       

Set width of first column to 80, second column to 20

CRAB> %width 80 20
CRAB> select fullpath, bytes from files limit 4;
fullpath                                                                          bytes               
/Users/peterdalloz/myproject/                                                     1972                
/Users/peterdalloz/myproject/.DS_Store                                            43012               
/Users/peterdalloz/myproject/DriftOffBookProcessingV3.py                          12661               
/Users/peterdalloz/myproject/ZZZBookChoiceTableViewController.h                   283                 
CRAB> select bytes, fullpath from files limit 4;
bytes                                                                             fullpath            
1972                                                                              /Users/peterdalloz/m
43012                                                                             /Users/peterdalloz/m
12661                                                                             /Users/peterdalloz/m
283                                                                               /Users/peterdalloz/m

Set column widths by field name

CRAB> %width fullpath=80 bytes=10
CRAB> select bytes, fullpath from files limit 4;
bytes       fullpath                                                                        
1972        /Users/peterdalloz/myproject/                                                   
43012       /Users/peterdalloz/myproject/.DS_Store                                          
12661       /Users/peterdalloz/myproject/DriftOffBookProcessingV3.py                        
283         /Users/peterdalloz/myproject/ZZZBookChoiceTableViewController.h                 
CRAB> select fullpath, bytes from files limit 4;
fullpath                                                                          bytes     
/Users/peterdalloz/myproject/                                                     1972      
/Users/peterdalloz/myproject/.DS_Store                                            43012     
/Users/peterdalloz/myproject/DriftOffBookProcessingV3.py                          12661     
/Users/peterdalloz/myproject/ZZZBookChoiceTableViewController.h                   283