# fselect

Find files with SQL-like queries

### Basic usage

    fselect [ARGS] COLUMN[, COLUMN...] [from ROOT[, ROOT...]] [where EXPR] [order by COLUMNS] [limit N] [into FORMAT]

You write SQL-like query, that's it.

**fselect** command itself is like a first keyword (`select`, i.e., *file select*).
But if you'll put one more `select` behind occasionally, that's not a problem.

Next you put columns you are interested in. It could be file name or path, size, modification date, etc.
See full list of possible columns. You can add columns with arbitrary text (put in quotes if it contains spaces). A few functions (aggregating and formatting) are there for your service. You can use arithmetic expressions when it makes sense.

Where to search? Specify with `from` keyword. You can list one or more directories separated with comma.
If you leave the `from`, then current directory will be processed.

What to search? Use `where` with any number of conditions.

Order results like in real SQL with `order by`. All columns are supported for ordering by, 
as well as `asc`/`desc` parameters and positional numeric shortcuts.

Limiting search results is possible with `limit`. Formatting options are supported with `into` keyword.

If you want to use operators containing `>` or `<`, 
put the whole query into the double quotes. 
This will protect query from the shell and output redirection.
The same applies to queries with parentheses or `*`, `?` and other special shell
metacharacters.

It's ok to use any metacharacters in interactive mode.

### It's not a real SQL

Directories to search at are listed with comma separators.
In a real SQL such syntax would make a cross product. Here it means just search at A, next at B, and so on.

String literals don't really need quotes. 
You will need to put them just in case you query something with spaces inside. 
And yes, you should use quotes for glob-patterns or regular expressions in the query 
on Linux or Mac OS to prevent parameter expansion from the shell. 
If you are on Windows, feel free to omit most of the quotes.

Commas for column separation aren't needed as well. Column aliasing (with or without `as` keyword) is not supported.

`into` keyword specifies output format, not output table.

Joins, unions, and subselects are not supported (yet?).

### Columns and fields

* `name`
* `path`
* `abspath`
* `size`
* `hsize` or `fsize`
* `uid`
* `gid`
* `user`
* `group`
* `created`
* `accessed`
* `modified`
* `is_dir`
* `is_file`
* `is_symlink`
* `is_pipe` or `is_fifo`
* `is_character` or `is_char`
* `is_block`
* `is_socket`
* `device` (Linux only)
* `inode` (Linux only)
* `blocks` (Linux only)
* `hardlinks` (Linux only)
* `mode`
* `user_read`
* `user_write`
* `user_exec`
* `user_all`
* `group_read`
* `group_write`
* `group_exec`
* `group_all`
* `other_read`
* `other_write`
* `other_exec`
* `other_all`
* `suid`
* `sgid`
* `is_hidden`
* `has_xattrs`
* `is_shebang`
* `width`
* `height`
* `duration`
* `mp3_bitrate` or `bitrate`
* `mp3_freq` or `freq`
* `mp3_title` or `title`
* `mp3_artist` or `artist`
* `mp3_album` or `album`
* `mp3_genre` or `genre`
* `mp3_year`
* `exif_datetime`
* `exif_altitude` or `exif_alt`
* `exif_latitude` or `exif_lat`
* `exif_longitude` or `exif_lng` or `exif_lon`
* `exif_make`
* `exif_model`
* `exif_software`
* `exif_version`
* `mime`
* `is_binary`
* `is_text`
* `line_count`
* `is_archive`
* `is_audio`
* `is_book`
* `is_doc`
* `is_image`
* `is_source`
* `is_video`
* `sha1`
* `sha2_256` or `sha256`
* `sha2_512` or `sha512`
* `sha3_512` or `sha3`

### Functions

#### Aggregate functions

Queries using these functions return only one result row.

| Function | Meaning | Example |
| --- | --- | --- |
| AVG | Average of all values | `select avg(size) from /home/user/Downloads` |
| COUNT | Number of all values | `select count(*) from /home/user/Downloads` |
| MAX | Maximum value | `select max(size) from /home/user/Downloads` |
| MIN | Minimum value | `select min(size) from /home/user where size gt 0` |
| SUM | Sum of all values | `select sum(size) from /home/user/Downloads` |
| STDDEV_POP, STDDEV or STD | Population standard deviation, the square root of variance | `select stddev_pop(size) from /home/user/Downloads` |
| STDDEV_SAMP | Sample standard deviation, the square root of sample variance | `select stddev_samp(size) from /home/user/Downloads` |
| VAR_POP or VARIANCE | Population variance | `select var_pop(size) from /home/user/Downloads` |
| VAR_SAMP | Sample variance | `select var_samp(size) from /home/user/Downloads` |

#### Date functions

Used mostly for formatting results.

| Function | Meaning | Example |
| --- | --- | --- |
| CURRENT_DATE or CURDATE | Returns current date | `select modified, path where modified = CURDATE()` |
| DAY | Extract day of the month | `select day(modified) from /home/user/Downloads` |
| MONTH | Extract month of the year | `select month(name) from /home/user/Downloads` |
| YEAR | Extract year of the date | `select year(name) from /home/user/Downloads` |
| DOW or DAYOFWEEK | Returns day of the week (1 - Sunday, 2 - Monday, etc.) | `select name, modified, dow(modified) from /home/user/projects/FizzBuzz` |

#### User functions

These are only available on Unix platforms.

| Function | Meaning | Example |
| --- | --- | --- |
| CURRENT_UID  | Current real UID | `select CURRENT_UID()` |
| CURRENT_USER | Current real UID's name | `select CURRENT_USER()` |
| CURRENT_GID  | Current primary GID | `select CURRENT_GID()` |
| CURRENT_GROUP | Current primary GID's name | `select CURRENT_GROUP()` |

#### Xattr functions

Used to check if particular xattr exists, or to get its value.
Supported platforms are Linux, MacOS, FreeBSD, and NetBSD. 

| Function | Meaning | Example |
| --- | --- | --- |
| HAS_XATTR | Check if xattr exists | `select "name, has_xattr(user.test) from /home/user"` |
| XATTR | Get value of xattr | `select "name, xattr(user.test) from /home/user"` |

#### String functions

Used mostly for formatting results.

| Function | Meaning | Example |
| --- | --- | --- |
| LENGTH or LEN | Length of string value | `select length(name) from /home/user/Downloads order by 1 desc limit 10` |
| LOWER or LOWERCASE or LCASE | Convert value to lowercase | `select lower(name) from /home/user/Downloads` |
| UPPER or UPPERCASE or UCASE | Convert value to uppercase | `select upper(name) from /home/user/Downloads` |
| BASE64 | Encode value to Base64 | `select base64(name) from /home/user/Downloads` |
| SUBSTRING or SUBSTR (str, pos, len) | Part of `str` value starting from `pos` of (optionally) `len` characters long. Negative `pos` means starting `pos` characters from the end of the string.  | `select substr(name, 1, 8) from /home/user/Downloads` |
| REPLACE (str, from, to) | Replace all occurrences of `from` by `to` | `select replace(name, metallica, MetaLLicA) from /home/user/Music/Rock` |
| TRIM | Returns string with whitespaces at the beginning and the end stripped | `select trim(title), trim(artist), trim(album) from /home/user/Music into json` |
| LTRIM | Returns string with whitespaces at the beginning stripped | `select ltrim(title) from /home/user/Music into json` |
| RTRIM | Returns string with whitespaces at the end stripped | `select rtrim(title) from /home/user/Music into json` |

#### Japanese string functions

Used for detecting Japanese symbols in file names and such.

| Function | Meaning | Example |
| --- | --- | --- |
| CONTAINS_JAPANESE or JAPANESE | Check if string value contains Japanese symbols | `select japanese(name) from /home/user/Downloads` |
| CONTAINS_KANA or KANA | Check if string value contains kana symbols | `select kana(name) from /home/user/Downloads` |
| CONTAINS_HIRAGANA or HIRAGANA | Check if string value contains hiragana symbols | `select contains_hiragana(name) from /home/user/Downloads` |
| CONTAINS_KATAKANA or KATAKANA | Check if string value contains katakana symbols | `select katakana(name) from /home/user/Downloads` |
| CONTAINS_KANJI or KANJI | Check if string value contains kanji symbols | `select kanji(name) from /home/user/Downloads` |

#### Other functions

| Function | Meaning | Example |
| --- | --- | --- |
| HEX | Convert integer value to hexadecimal representation | `select name, size, hex(size), upper(hex(size)) from /home/user/Downloads` |
| OCT | Convert integer value to octal representation | `select name, size, oct(size) from /home/user/Downloads` |
| CONTAINS | `true` if file contains string, `false` if not | `select contains(TODO) from /home/user/Projects/foo/src` |
| COALESCE | Returns first nonempty expression value | `select name, size, COALESCE(sha256, '---') from /home/user/Downloads` |
| CONCAT | Returns concatenated string of expression values | `select CONCAT('Name is ', name, ' size is ', fsize, '!!!') from /home/user/Downloads` |
| CONCAT_WS | Returns concatenated string of expression values with specified delimiter | `select name, fsize, CONCAT_WS('x', width, height) from /home/user/Images` |
| RANDOM or RAND | Returns random integer (from zero to max int, from zero to *arg*, or from *arg1* to *arg2*) | `select path from /home/user/Music order by RAND()` |
| FORMAT_SIZE | Returns formatted size of a file | `select name, FORMAT_SIZE(size, '%.0') from /home/user/Downloads order by size desc limit 10` |

Let's try `FORMAT_SIZE` with different format specifiers: 

| Specifier | Meaning | Output |
| --- | --- | --- |
| `format_size(1678123)` | Default output | 1.60MiB |
| `format_size(1678123, ' ')` | Put a space before units | 1.60 MiB |
| `format_size(1678123, '%.0')` | Round up decimal part | 2MiB |
| `format_size(1678123, '%.1')` | One place for decimal part | 1.6MiB |
| `format_size(1678123, '%.2')` | Two places for decimal part | 1.60MiB |
| `format_size(1678123, '%.2 ')` | Two places for decimal part, and put a space before units | 1.60 MiB |
| `format_size(1678123, '%.2 d')` | Use decimal divider, e.g. 1000-based units, not 1024-based | 1.68 MB |
| `format_size(1678123, '%.2 c')` | Use conventional format, e.g. 1024-based divider, but display 1000-based units | 1.60 MB |
| `format_size(1678123, '%.2 k')` | Display file size in specified unit, this time in kibibytes | 1638.79 KiB |
| `format_size(1678123, '%.2 ck')` | What is a kibibyte? Gimme conventional unit! | 1638.79 KB |
| `format_size(1678123, '%.0 ck')` | And drop this decimal part! | 1639 KB |
| `format_size(1678123, '%.0 kb')` | Use 1000-based kilobyte | 1678 KB |
| `format_size(1678123, '%.0kb')` | Don't put a space | 1678KB |
| `format_size(1678123, '%.0s')` | Use short units | 2M |
| `format_size(1678123, '%.0 s')` | Use short units with a space | 2 M |

### File size units

| Specifier | Meaning | Bytes |
| --- | --- | --- |
| `t` or `tib` | tebibyte | 1024 * 1024 * 1024 * 1024
| `tb` | terabyte | 1000 * 1000 * 1000 * 1000
| `g` or `gib` | gibibyte | 1024 * 1024 * 1024
| `gb` | gigabyte | 1000 * 1000 * 1000
| `m` or `mib` | mebibyte | 1024 * 1024
| `mb` | megabyte | 1000 * 1000
| `k` or `kib` | kibibyte | 1024
| `kb` | kilobyte | 1000

    fselect size, path from /home/user/tmp where size gt 2g
    fselect fsize, path from /home/user/tmp where size = 5mib
    fselect hsize, path from /home/user/tmp where size lt 8kb

### Search roots

    path [option N] [option] [option] [option...][, path2 [option...]]
    
When you put a directory to search at, you can specify some options.

| Option | Meaning |
| --- | --- |
| mindepth N | Minimum search depth. Default is unlimited. Depth 1 means skip one directory level and search further. |
| maxdepth N | Maximum search depth. Default is unlimited. Depth 1 means search the mentioned directory only. Depth 2 means search mentioned directory and its subdirectories. Synonym is `depth`. |
| symlinks | If specified, search process will follow symlinks. Default is not to follow. Synonym is `sym`. |
| archives | Search within archives. Only zip archives are supported. Default is not to include archived content into the search results. Synonym is `arc`. |
| gitignore | Search respects `.gitignore` files found. Synonym is `git`. |
| hgignore | Search respects `.hgignore` files found. Synonym is `hg`. |
| dockerignore | Search respects `.dockerignore` files found. Synonym is `dock`. |
| dfs | Depth-first search mode. |
| bfs | Breadth-first search mode. This is the default. |
| regexp | Use regular expressions to search within multiple roots. Synonym is `rx`. | 

### Operators

* `=` or `==` or `eq`
* `!=` or `<>` or `ne`
* `===`
* `!==`
* `>` or `gt`
* `>=` or `gte` or `ge`
* `<` or `lt`
* `<=` or `lte` or `le`
* `=~` or `~=` or `regexp` or `rx`
* `!=~` or `!~=` or `notrx`
* `like`
* `notlike`

### Date and time specifiers

When you specify inexact date and time with `=` or `!=` operator, **fselect** understands it as an interval.

    fselect path from /home/user where modified = 2017-05-01
    
`2017-05-01` means all day long from 00:00:00 to 23:59:59.

    fselect path from /home/user where modified = '2017-05-01 15'
    
`2017-05-01 15` means one hour from 15:00:00 to 15:59:59.

    fselect path from /home/user where modified ne '2017-05-01 15:10'
    
`2017-05-01 15:10` is a 1-minute interval from 15:10:00 to 15:10:59.

Other operators assume exact date and time, which could be specified in a more free way:

    fselect "path from /home/user where modified === 'apr 1'"
    fselect "path from /home/user where modified gte 'last fri'"
    fselect path from /home/user where modified gte '01/05'

[More about it](https://github.com/stevedonovan/chrono-english)

**fselect** uses *UK* locale, not American style dates, i.e. `08/02` means *February 8th*.

### Regular expressions ###

[Rust flavor regular expressions](https://docs.rs/regex/1.1.0/regex/#syntax) are used.

### MIME and file types

For MIME guessing use field `mime`. It returns a simple string with deduced MIME type,
which is not always accurate.

    fselect path, mime, is_binary, is_text from /home/user

`is_binary` and `is_text` return `true` or `false` based on MIME type detected. 
Once again, this should not be considered as 100% accurate result, 
or even possible at all to detect correct file type.

Other fields listed below **do NOT** use MIME detection.
Assumptions are being made based on file extension.

The lists below could be edited with the configuration file. 

| Search field | Extensions |
| --- | --- |
| `is_archive` | .7z, .bz2, .bzip2, .gz, .gzip, .lz, .rar, .tar, .xz, .zip |
| `is_audio` | .aac, .aiff, .amr, .flac, .gsm, .m4a, .m4b, .m4p, .mp3, .ogg, .wav, .wma |
| `is_book` | .azw3, .chm, .djvu, .epub, .fb2, .mobi, .pdf |
| `is_doc` | .accdb, .doc, .docm, .docx, .dot, .dotm, .dotx, .mdb, .ods, .odt, .pdf, .potm, .potx, .ppt, .pptm, .pptx, .rtf, .xlm, .xls, .xlsm, .xlsx, .xlt, .xltm, .xltx, .xps |
| `is_image` | .bmp, .gif, .heic, .jpeg, .jpg, .png, .tiff, .webp |
| `is_source` | .asm, .bas, .c, .cc, .ceylon, .clj, .coffee, .cpp, .cs, .d, .dart, .elm, .erl, .go, .groovy, .h, .hh, .hpp, .java, .js, .jsp, .kt, .kts, .lua, .nim, .pas, .php, .pl, .pm, .py, .rb, .rs, .scala, .swift, .tcl, .vala, .vb |
| `is_video` | .3gp, .avi, .flv, .m4p, .m4v, .mkv, .mov, .mp4, .mpeg, .mpg, .webm, .wmv |

    fselect is_archive, path from /home/user
    fselect is_audio, is_video, path from /home/user/multimedia
    fselect path from /home/user where is_doc != 1
    fselect path from /home/user where is_image = false
    fselect path from /home/user where is_video != true

### MP3 support

**fselect** can parse basic MP3 metadata and search by bitrate or sampling frequency of the first frame,
title of the track, artist's name, album, genre, and year.

Duration is measured in seconds.

[List of supported genres](https://docs.rs/mp3-metadata/0.3.0/mp3_metadata/enum.Genre.html)

    fselect duration, bitrate, path from /home/user/music
    fselect mp3_year, album, title from /home/user/music where artist like %Vampire% and bitrate gte 320
    fselect bitrate, freq, path from /home/user/music where genre = Rap or genre = HipHop

### File hashes

| Column | Meaning |
| --- | --- |
| `sha1` | SHA-1 digest of a file|
| `sha2_256` or `sha256` | SHA2-256 digest of a file |
| `sha2_512` or `sha512` | SHA2-512 digest of a file |
| `sha3_512` or `sha3` | SHA3-512 digest of a file |

    fselect path, sha256, 256 from /home/user/archive limit 5
    fselect path from /home/user/Download where sha1 like cb23ef45% 

### Output formats

    ... into FORMAT

| Format | Description |
| --- | --- |
| `tabs` | default, columns are separated with tabulation |
| `lines` | each column goes at a separate line |
| `list` | columns are separated with NULL symbol, similar to `-print0` argument of `find` |
| `csv` | comma-separated columns |
| `json` | array of resulting objects with requested columns |
| `html` | HTML document with table | 

    fselect size, path from /home/user limit 5 into json
    fselect size, path from /home/user limit 5 into csv
    fselect size, path from /home/user limit 5 into html
    fselect path from /home/user into list | xargs -0 grep foobar

### Configuration file

**fselect** tries to create a new configuration file if one doesn't exists.

Usual location on Linux:

    /home/user_name/.config/fselect/config.toml
    
On Windows:
    
    C:\Users\user_name\AppData\Roaming\jhspetersson\fselect\config.toml
    
Fresh config is filled with defaults, feel free to update it.

If no config on the standard paths found, **fselect** checks its presence next to the executable. 
You can also specify config location with runtime option, e.g.:

    fselect --config /home/user_name/fselect_custom.toml name, size from /home/user_name/Music where is_audio = 1

### Command-line arguments

| Argument | Meaning |
| --- | --- |
| `--config` or `-c` or `/config` | Specify config file location |
| `--nocolor` or `--no-color` or `/nocolor` | Disable colors |
| `--help` or `-h` or `/?` or `/h` | Show help and exit |

### Environment variables

**fselect** respects `NO_COLOR` [environment variable](https://no-color.org).
