Commands and Settings

Top five commands in bold

! Send the rest of the line to the operating system shell to be executed.
%autoimport filename [table] Import filename to table, automatically detecting separators and datatypes.
%color darkmode | off | default Change Crab color scheme
%cruft on|off If on Crab prints intro text at start up, and progress counts during scanning.
%echo on|off If on each SQL statement is printed before it is executed.
%encoding enc:handler Specify how Crab should map byte sequences to characters when reading and writing files.
%explain on|off If on set output mode suitable for SQL EXPLAIN. If off reset output mode to what it was before.
%header on|off If on display column names in output, when using output mode column or list
%import file table Import delimited data from file to table (e.g. a CSV file)
%license emailAddy licenseKey Enter license key
%matchsep string Change the separator used between groupn results when there are multiple matches for the group
%mode column | dict | line | list Change the layout of query results.
%nullvalue string Change text printed in place of null values in query results
%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
%quit Exit Crab. Data will be retained until overwritten by the next scan.
%read filename Read and execute Crab commands and SQL from a text file.
%refresh Update the files table with the latest contents of the filesystem by re-scanning the same path(s)
%schema [table] Show SQL schema for the specified table and related indexes.
%separator string Change the separator used between fields for output in list mode, and for %import of text files
%show [settingName] Show the current value of each of Crab's settings
%tables List the tables in the database
%version Displays the Crab version number, and which versions of the APSW and SQLite libraries it was built on.
%width Sets column widths for column output mode

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.


Example

E.g. to create a directory without having to exit crab or switch windows

CRAB> ! mkdir ~/Dropbox/backups


Path Abbreviations

Because the line is interpreted by the MacOS shell rather than Crab, you can use path abbreviations such as ~


Syntax

  • ; 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


The darkmode color scheme is easier to read in terminals with a dark background


%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


The
default color scheme is easier to read in terminals with a light background


%color default

  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


Turn off all the colors


%color off

  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



%color off

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.


EXAMPLES

Read and write with utf8 encoding, stop with error on invalid characters

CRAB> %encoding utf8

Read and write with cp1252 encoding, replace invalid characters by placeholder symbol

CRAB> %encoding cp1252:replace

Read and write with utf8 encoding, on invalid character skip to next file when querying fileslines

CRAB> %encoding utf_8:skipfile

Read and write with ascii encoding, ignore any invalid characters

CRAB> %encoding ascii:ignore

Read and write binary using blob objects, with buffer size of 80 bytes

CRAB> %encoding binary:80

Read and write binary using blob objects, whole file as one data row

CRAB> %encoding binary:-1

Copy a file, removing non-ascii characters

%encoding ascii:ignore

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



encodingType

One of these:
ascii, base64_codec, big5, big5hkscs, bz2_codec, cp037, cp1006, cp1026, cp1140, cp1250, cp1251, cp1252, cp1253, cp1254, cp1255, cp1256, cp1257, cp1258, cp424, cp437, cp500, cp737, cp775, cp850, cp852, cp855, cp856, cp857, cp860, cp861, cp862, cp863, cp864, cp865, cp866, cp869, cp874, cp875, cp932, cp949, cp950, euc_jis_2004, euc_jisx0213, euc_jp, euc_kr, gb18030, gb2312, gbk, hex_codec, hz, idna, iso2022_jp, iso2022_jp_1, iso2022_jp_2, iso2022_jp_2004, iso2022_jp_3, iso2022_jp_ext, iso2022_kr, iso8859_10, iso8859_13, iso8859_14, iso8859_15, iso8859_2, iso8859_3, iso8859_4, iso8859_5, iso8859_6, iso8859_7, iso8859_8, iso8859_9, johab, koi8_r, koi8_u, latin_1, mac_cyrillic, mac_greek, mac_iceland, mac_latin2, mac_roman, mac_turkish, mbcs, palmos, ptcp154, punycode, quopri_codec, raw_unicode_escape, rot_13, shift_jis, shift_jis_2004, shift_jisx0213, string_escape, undefined, unicode_escape, unicode_internal, utf_16, utf_16_be, utf_16_le, utf_7, utf_8, uu_codec, zlib_codec


:errorHandler

When you specify an encoding, you can add a colon and an error handler. This tells Crab how to deal with characters that don't match the chosen encoding. One of:
replace, ignore or skipfile

  • 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.


BINARY FILES     (Do not use: this is an experimental feature in test)

There is an additional encoding type
binary that allows you to inspect and match the contents of non-text files, or files of unknown encoding. It causes queries to return data as a blob field rather than a text field. You can cast blob fields as text, do byte-level pattern matching on them, and use the hex(), bytestr() and codestr() functions to display them.

With binary encoding, there is no such thing as a character that doesn't match the encoding. And as well, the data is generally not split into readable lines separated by carriage returns or new lines. In place of an errorHandler, binary encoding accepts an integer buffer size. E.g.

%encoding binary:80

Which specifies a "line length" of 80 bytes
If you use a value of -1 the whole file is read as one long line, which avoids the risk of patterns failing to match across line boundaries at the cost of being difficult to display in query output, and having potentially slow pattern matching for large files.

Examples

1. Hexdump style output from binary file, with regex pattern matching

  
%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.


2. Print 40 bytes of a file, starting at byte 600
  
%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

 
Example


%license headCrab@etia.co.uk a009999_71b42819d3ab456f3d



See %online command
To visit etia website for Crab news, documentation and pricing. Or to buy a license.

See %show command
For current license status.


%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.


You can use quotes and c-style escapes. For example to separate matches by tab

%matchsep "\t"


Do not use choose a
matchsep that might returned as part of a match.


See Crab's groupn function documentation
For information about matching with grouped regular expressions


%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


Comma separated values. Use the
%separator command to change separator, e.g.


%separator '\t'


Turn first row headers on or off with the
%headers command


%nullvalue string

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


You can use quotes and c-style escapes. For example to set nullvalue to space tab space

%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


Avoid screen clutter during exec command

%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


Copy and transpose a CSV file

%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


From the Crab command line: run a script called listdups

CRAB> %read listdups.crab


From the bash command line: scan myproject, run listdups script and return results to command line

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



See -batch startup option
For information about running Crab in batch mode


%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.


Watch out for globbed scan paths

If you refresh a crab database that had a globbed scanpath, such as

crab ~/myproject/*.txt

Then the original list of files matching the glob will be rescanned. This means if you add extra text files to
myproject, they won't be matched by refreshing the database.

If the original scanpath was a directory, e.g.

crab ~/myproject/

then
%refresh will of course update the scan with the latest contents of the directory and subdirectories


User tables are unaffected by refresh

Any addition tables you've created in the .crdb database, will not be affected by
%refresh.


%schema [table]

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


But for files and fileslines schema use the help system

A more convenient way to see the columns in Crab's
files or fileslines table is to use the help system, e.g. from the Crab command line:


?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.


E.g. to set separator to comma

%separator ,


E.g. to set separator to tab

%separator '\t'



See %mode command
%mode
changes output layout, list mode is one of the available options

See -batch startup option
This option defaults to
list mode output when returning results to Bash

See %import command
For importing separator delimited files


%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       


Reset widths to useful standard values

%width standard


Set widths to 100% of actual data widths from files table and fileslines table

%width auto


Reset all widths to default value of 10 characters
  
%width clear




See %mode command
The mode command changes query results layout

See Crab's short function
To trim text to fit in a column of a given width


© 2017 Etia UK