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
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
%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
%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.
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.
%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
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
%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
%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
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>
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
© 2019 Etia UK