kashav / fsql

Search for files using a fun query language

fsql

Search through your filesystem with SQL-esque queries.

Contents

• Demo
• Installation
• Usage
• Query Syntax
• Examples
• Contribute
• License

Demo

Installation

Binaries

View latest release.

Via Go

$ go get -u -v github.com/kashav/fsql/...
$ which fsql
$GOPATH/bin/fsql

Via Homebrew

$ brew install fsql
$ which fsql
/usr/local/bin/fsql

Build manually

$ git clone https://github.com/kashav/fsql.git $GOPATH/src/github.com/kashav/fsql
$ cd $_ # $GOPATH/src/github.com/kashav/fsql
$ make
$ ./fsql

Usage

fsql expects a single query via stdin. You may also choose to use fsql in interactive mode.

View the usage dialogue with the -help flag.

$ fsql -help
usage: fsql [options] [query]
  -v  print version and exit (shorthand)
  -version
      print version and exit

Query syntax

In general, each query requires a SELECT clause (to specify which attributes will be shown), a FROM clause (to specify which directories to search), and a WHERE clause (to specify conditions to test against).

>>> SELECT attribute, ... FROM source, ... WHERE condition;

You may choose to omit the SELECT and WHERE clause.

If you're providing your query via stdin, quotes are not required, however you'll have to escape reserved characters (e.g. *, <, >, etc).

Attribute

Currently supported attributes include name, size, time, hash, mode.

Use all or * to choose all; if no attribute is provided, this is chosen by default.

Examples:

Each group features a set of equivalent clauses.

>>> SELECT name, size, time ...
>>> name, size, time ...

>>> SELECT all FROM ...
>>> all FROM ...
>>> FROM ...

Source

Each source should be a relative or absolute path to a directory on your machine.

Source paths may include environment variables (e.g. $GOPATH) or tildes (~). Use a hyphen (-) to exclude a directory. Source paths also support usage of glob patterns.

In the case that a directory begins with a hyphen (e.g. -foo), use the following to include it as a source:

>>> ... FROM ./-foo ...

Examples:

>>> ... FROM . ...

>>> ... FROM ~/Desktop, ./*/**.go ...

>>> ... FROM $GOPATH, -.git/ ...

Condition

Condition syntax

A single condition is made up of 3 parts: an attribute, an operator, and a value.

• Attribute:

  A valid attribute is any of the following: name, size, mode, time.

• Operator:

  Each attribute has a set of associated operators.

  • name:

    Operator	Description
    =	String equality
    <> / !=	Synonymous to using "NOT ... = ..."
    IN	Basic list inclusion
    LIKE	Simple pattern matching. Use % to match zero, one, or multiple characters. Check that a string begins with a value: <value>%, ends with a value: %<value>, or contains a value: %<value>%.
    RLIKE	Pattern matching with regular expressions.

  • size / time:

    • All basic algebraic operators: >, >=, <, <=, =, and <> / !=.

  • hash:

    • = or <> / !=

  • mode:

    • IS

• Value:

  If the value contains spaces, wrap the value in quotes (either single or double) or backticks.

  The default unit for size is bytes.

  The default format for time is MMM DD YYYY HH MM (e.g. "Jan 02 2006 15 04").

  Use mode to test if a file is regular (IS REG) or if it's a directory (IS DIR).

  Use hash to compute and/or compare the hash value of a file. The default algorithm is SHA1

Conjunction / Disjunction

Use AND / OR to join conditions. Note that precedence is assigned based on order of appearance.

This means WHERE a AND b OR c is not the same as WHERE c OR b AND a. Use parentheses to get around this behaviour, i.e. WHERE a AND b OR c is the same as WHERE c OR (b AND a).

Examples:

>>> ... WHERE name = main.go OR size = 5 ...

>>> ... WHERE name = main.go AND size > 20 ...

Negation

Use NOT to negate a condition. This keyword must precede the condition (e.g. ... WHERE NOT a ...).

Note that negating parenthesized conditions is currently not supported. However, this can easily be resolved by applying De Morgan's laws to your query. For example, ... WHERE NOT (a AND b) ... is logically equivalent to ... WHERE NOT a OR NOT b ... (the latter is actually more optimal, due to lazy evaluation).

Examples:

>>> ... WHERE NOT name = main.go ...

Attribute Modifiers

Attribute modifiers are used to specify how input and output values should be processed. These functions are applied directly to attributes in the SELECT and WHERE clauses.

The table below lists currently-supported modifiers. Note that the first parameter to FORMAT is always the attribute name.

Attribute	Modifier	Supported in SELECT	Supported in WHERE
hash	SHA1(, n)	✔️	✔️
name	UPPER (synonymous to FORMAT(, UPPER))	✔️	✔️
	LOWER (synonymous to FORMAT(, LOWER))	✔️	✔️
	FULLPATH	✔️
	SHORTPATH	✔️
size	FORMAT(, unit)	✔️	✔️
time	FORMAT(, layout)	✔️	✔️

• n:

  Specify the length of the hash value. Use a negative integer or ALL to display all digits.

• unit:

  Specify the size unit. One of: B (byte), KB (kilobyte), MB (megabyte), or GB (gigabyte).

• layout:

  Specify the time layout. One of: ISO, UNIX, or custom. Custom layouts must be provided in reference to the following date: Mon Jan 2 15:04:05 -0700 MST 2006.

Examples:

>>> SELECT SHA1(hash, 20) ...

>>> ... WHERE UPPER(name) ...

>>> SELECT FORMAT(size, MB) ...

>>> ... WHERE FORMAT(time, "Mon Jan 2 2006 15:04:05") ...

Subqueries

Subqueries allow for more complex condition statements. These queries are recursively evaluated while parsing. SELECTing multiple attributes in a subquery is not currently supported; if more than one attribute (or all) is provided, only the first attribute is used.

Support for referencing superqueries is not yet implemented, see #4 if you'd like to help with this.

Examples:

>>> ... WHERE name IN (SELECT name FROM ../foo) ...

Usage Examples

List all attributes of each directory in your home directory (note the escaped *):

$ fsql SELECT \* FROM ~ WHERE mode IS DIR

List the names of all files in the Desktop and Downloads directory that contain csc in the name:

$ fsql "SELECT name FROM ~/Desktop, ~/Downloads WHERE name LIKE %csc%"

List all files in the current directory that are also present in some other directory:

$ fsql
>>> SELECT all FROM . WHERE name IN (
...   SELECT name FROM ~/Desktop/files.bak/
... );

Passing queries via stdin without quotes is a bit of a pain, hopefully the next examples highlight that, my suggestion is to use interactive mode or wrap the query in quotes if you're doing anything with subqueries or attribute modifiers.

List all files named main.go in $GOPATH which are larger than 10.5 kilobytes or smaller than 100 bytes:

$ fsql SELECT all FROM $GOPATH WHERE name = main.go AND \(FORMAT\(size, KB\) \>= 10.5 OR size \< 100\)
$ fsql "SELECT all FROM $GOPATH WHERE name = main.go AND (FORMAT(size, KB) >= 10.5 OR size < 100)"
$ fsql
>>> SELECT
...   all
... FROM
...   $GOPATH
... WHERE
...   name = main.go
...   AND (
...     FORMAT(size, KB) >= 10.5
...     OR size < 100
...   )
... ;

List the name, size, and modification time of JavaScript files in the current directory that were modified after April 1st 2017:

$ fsql SELECT UPPER\(name\), FORMAT\(size, KB\), FORMAT\(time, ISO\) FROM . WHERE name LIKE %.js AND time \> \'Apr 01 2017 00 00\'
$ fsql "SELECT UPPER(name), FORMAT(size, KB), FORMAT(time, ISO) FROM . WHERE name LIKE %.js AND time > 'Apr 01 2017 00 00'"
$ fsql
>>> SELECT
...   UPPER(name),
...   FORMAT(size, KB),
...   FORMAT(time, ISO)
... FROM
...   .
... WHERE
...   name LIKE %.js
...   AND time > 'Apr 01 2017 00 00'
... ;

Contribute

This project is completely open source, feel free to open an issue or submit a pull request.

Before submitting code, please ensure that tests are passing and the linter is happy. The following commands may be of use, refer to the Makefile to see what they do.

$ make install \
       get-tools
$ make fmt \
       vet \
       lint
$ make test \
       coverage
$ make bootstrap-dist \
       dist

License

fsql source code is available under the MIT license.

fsql's Issues

Compatibility with future Go versions

Just a heads up that Go 1.17 (and the just-released 1.16 without first changing GO111MODULE) won't support builds that aren't in module-aware mode.

The master build should work but the last tag (v0.3.1) does not. Is it possible you can tag a new release for this?

https://blog.golang.org/go116-module-changes

missing path as token

Hi could you implementa path toen in order to get it in the output format or in the where clause ?

Implement subqueries

Introduce subqueries to be used in condition statements, example:

"SELECT name FROM foo WHERE name NOT IN (SELECT name FROM ../bar WHERE date < ...)"

This shouldn't be too hard to implement.

First step is to introduce an IN operator for strings/numbers. The operator will return true if current the value is in the provided list.

The subquery should be evaluated individually. It returns a []Result for all files that passed the query (a Result struct holds each of the applicable attributes for a single file). Now we can apply IN to this result list.

Idea from https://news.ycombinator.com/item?id=14344493.

Misc. enhancements

Various enhancements and bugs that I've come across, feel free to comment below (or create a new issue) for suggestions or bugs.

• Bug: Exclude skips files with similar names (e.g. excluding .git results in .gitignore not being listed).
• Add unit tests (test files are empty right now).
• Add support for regex in string comparisons (e.g. ... ENDSWITH jsx?).
• Handle errors more gracefully (instead of just panicking everything).
• Add support for OR / AND / () (for precedence) in condition statements (lexing is already done for these, just need to add the parsers).
• Add support for times/dates (to query file creation/modification time).
• Introduce new attributes to select from (creation/modification time, file mode, basically whatever else os.FileInfo supports).
• Bug: Space-separated queries. Currently something like "... WHERE time > May 1 ..." is broken since we're splitting conditionals by space. Fix by allowing single quotes and backticks in query strings, so something like "... WHERE time > 'May 1' ..." works and evaluates the conditional to have value of "May 1".
• Add NOT operator for negating conditionals.
• Add support for querying and selecting using other size units (only supports bytes right now, add functionality for KB, MB, and GB as well).
• Bug: Selecting from a directory and it's subdirectory results in duplicates and malformed output.

Moved from the README to this issue as of b78a987.

Exclude duplicate entries

Duplicate entries are shown when any of the directories has a leading/trailing slash. This only applies to queries which select from multiple directories with at least one being a child directory of another.

Assume structure is as follows:

.
├── foo
│   ├── ...
│   ├── ...

And you run the following queries

$ fsql "select all from ., foo"
...
$ fsql "select all from ., foo/"
...
$ fsql "select all from ., ./foo"
...
$ fsql "select all from ., ./foo/"
...

You should see that the first doesn't include foo twice, but the next 3 do.

---

This can easily be solved with a new Excluder implementation which maintains a set (map[string]bool) of seen directories to determine if the directory has already been traversed. I'm fairly certain that the best solution for this would be to use regex, but it may actually be more efficient to just add multiple entries to the map per file (no trailing/leading slash, trailing, leading, and both). There's a naive implementation of this in Query.execute.

Very open to hearing other ideas!

tilde home expansion not working for me

fsql "SELECT name, size, time FROM ~/Documents WHERE name LIKE %.js AND time > 'May 15 2017 00 00'"
2017/05/16 07:42:28 no such file or directory: ~/Documents

(exists with newer files there also namely fsql src)

/home/user/blah works fine.

Respecting attribute order

In the scenario:
fsql "select FULLPATH(name), hash from...
I would expect the return to be:
/path hash
instead it's hash /path

running the latest version available via brew.
% fsql -v fsql version 0.3.x, built off master

Support globs in FROM

I know there's intentionally WHERE name LIKE, but would be nice to have glob support in the FROM clause directly.... FROM ./foo/**/*.js ...

Windows support?

very cool project

Sorry if this is listed somewhere, but will this work on Windows?

Add -v Version output

Would be nice if -v and --version switches were supported, and printed the version and build date...

aside: should probably publish update instructions go get -u -v ... on the site, since I re-tried the -v alone and nothing happened... ;-)

Improve support for directories/files in current path...

This works...

fsql "SELECT * FROM ./_src WHERE name LIKE %.js"

this does not...

fsql "SELECT * FROM _src WHERE name LIKE %.js"

feature request: plugin API for extending the fsql abilities

Super cool tool you made , thanks @kshvmdn
I would like to see some plugins, (how to extend the fsql)
for example

• git : can find uncommit / unstage files ref: https://github.com/src-d/go-git https://github.com/cloudson/gitql

• tag: can find with macOS file/folder with tag

• exif: can find photos with exif query

Excluding directories also excludes similarly-named files

For example, excluding .git (with -.git) results in .gitignore not being listed.

This can be resolved with excluding with -.git/ instead, but then .git is listed.

Add support for ISO 8601 datetime format

Cannot start commands with `fsql`; have to use $GOPATH/bin/fsql

I seem to have installed fsql using go get, but I’m still not able to begin commands with fsql. I followed the steps as written. To use the program I have to change my directory to the bin folder where I installed Go and then drag the fsql Unix executable from Finder into Terminal. In other words, I ended up using ./bin/fsql <query> instead of fsql <query>. Could there be any steps missing that would make fsql <query> valid?

Simplify query structure

• Allow SELECT clause to be omitted (attribute should default to all) (14344414, 14345753).

  $ fsql "name, size, ... FROM . WHERE ..."
  $ fsql "FROM . WHERE ..."

• Make WHERE clause optional (#8).

• Quote-less queries:

  $ fsql SELECT \* FROM . WHERE ...
  $ fsql all FROM . WHERE ...

• Make FROM clause optional, directory should default to the current directory (./).

Failing darwin builds require a bump of x/sys

Darwin builds are failing with:

vendor/golang.org/x/sys/unix/syscall_darwin.1_13.go:29:3: //go:linkname must refer to declared function or variable

This can be fixed with go get -u golang.org/x/sys, but then the compilation fails on slices which require go >= 1.17.

Could you bump both, please?

Hash support

This might be out of scope, but it would be handy to generate the SHA-1 (or other hashes) to output and/or compare with another directory of files (when subqueries are implemented).

Attribute modifiers

A lot of suggestions have revolved around modifying attributes in some way (#3, #15).

Attribute modifiers will apply some change to the attributes in the SELECT and WHERE clauses.

I'm most fond of the following syntax right now. Of the ideas suggested, it's the easiest to parse (particularly for chained modifiers), while still being intuitive. I'm open to ideas if anyone has a suggestion.

SELECT name::upper::fullpath, size::mb FROM . WHERE time::iso > 2017-05-17T06:07:28Z

As I mentioned in #15 (comment), one of the downsides of a syntax like this is that we're moving away from SQL (i'm not entirely sure that this is even a problem).

regression test?

would be good to have a test FS and have test that run on it.
The test FS can be created from the test start.

this will make it easy to spoke regressions as the lib grows.

really useful library.

`fsql "SELECT * from . WHERE size > 1gb"` doesn't work

If I run a query like in the README gif it works:

$ fsql "SELECT * from . WHERE name LIKE mkv AND size > 1gb"
...lots of results

but taking away the name LIKE mkv clause breaks it:

$ fsql "SELECT * from . WHERE size > 1gb"
2017/06/09 22:52:28 strconv.ParseFloat: parsing "1gb": invalid syntax

How to show human readable file sizes?

It would be better to support some function to convert the size unit, e.g. kb/mb/gb. So the sql would be as below
fsql "select name, mb(size) from . where ...."

Binary Download Request

Thank you for a great tool and sharing with the community; this is awesome.
Do you mind creating a binary/executable for Windows?

SELECT query does not print folders from recursive paths.

A simple SELECT query should be able to identify the relative path of a file, which should include its folder name.

Example:

fsql "SELECT * from /Users/---/vagrant/jenkins"

prints:

`-rw-r--r--	5248	Dec  7 14:28:14	b874568	config.xml`

instead of:

-rw-r--r--	5248	Dec  7 14:28:14	b874568	jobs/1/config.xml

Go vendoring?

Would you be willing to start using the Go vendor directory or one of the other Go vendor systems so that we can avoid go resource blocks in the Homebrew formula?

I noticed while doing the upgrade from 0.1.1 to 0.2.0 that fsql no longer builds unless I add a go resource block for gopkg.in/oleiade/lane.v1.

It would be much appreciated if we could avoid this in the formula, as we're trying to eliminate the use of go resource blocks, so having to add them to a formula that doesn't currently need them is :(

Allow search in directories starting with -

As the pathname can contain -, it would be nice if there's explicit method of seeking in such directories.
Or at least noted in README that to search in directory -oops you should do ./-oops.

Difference between this and OSQuery

Hola!

This seems to have a reasonable amount of overlap with the OSQuery project. Why write it anew?

Release Version to allow addition to Homebrew

This is a fantastic repo, and I'd love to add it to homebrew, but it needs a version to be added. If you could tag and release a version, that would be fantastic.

Feature request: limit number of results

It would be nice to do something like

SELECT name FROM /myDir LIMIT 100

This should limit the number of results to maximum 100. This is an interesting feature when working with a huge amount of files.

Maybe even without the need to give a WHERE clause.

Alternative syntax could be:

SELECT TOP 100 name FROM /myDir

Example ese case: I would eventually like to be able to select a maximum of 100 files where the name starts with a certain letter. Ideally it would be possible to give a regex for the name (e.g. like in this library: https://github.com/gahag/FSQL).

Add support for querying file contents

what if i want to find which file contains the specific string? just like in IDE.

Panic querying files larger than 10GB

Query:

select * from / where size > 10GB

Panic:

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x40 pc=0x4c56d1]

goroutine 1 [running]:
main.compare(0xc420012408, 0x4, 0x12, 0xc420012440, 0x2, 0x0, 0x0, 0x0, 0x0)
	/home/michael/Projects/go/src/github.com/kshvmdn/fsql/main.go:55 +0x2e1
github.com/kshvmdn/fsql/query.(*ConditionNode).Evaluate(0xc42000c600, 0x0, 0x0, 0x4e0b20, 0x505c38, 0x7fae1cd0b400)
	/home/michael/Projects/go/src/github.com/kshvmdn/fsql/query/query.go:113 +0x80
main.main.func1(0xc4203db550, 0xf, 0x0, 0x0, 0x56d620, 0xc4203d5ce0, 0x0, 0x0)
	/home/michael/Projects/go/src/github.com/kshvmdn/fsql/main.go:120 +0x162
path/filepath.walk(0xc4203db4b0, 0xd, 0x56e720, 0xc420251520, 0xc42000c620, 0x0, 0x0)
	/usr/lib/go/src/path/filepath/path.go:372 +0x2fe
path/filepath.walk(0xc4203db1f4, 0xa, 0x56e720, 0xc4202505b0, 0xc42000c620, 0x0, 0x0)
	/usr/lib/go/src/path/filepath/path.go:376 +0x414
path/filepath.walk(0xc420013880, 0x5, 0x56e720, 0xc42042dad0, 0xc42000c620, 0x0, 0x0)
	/usr/lib/go/src/path/filepath/path.go:376 +0x414
path/filepath.walk(0xc4200123a8, 0x1, 0x56e720, 0xc420078680, 0xc42000c620, 0x0, 0x20)
	/usr/lib/go/src/path/filepath/path.go:376 +0x414
path/filepath.Walk(0xc4200123a8, 0x1, 0xc42000c620, 0x1, 0xc420096000)
	/usr/lib/go/src/path/filepath/path.go:398 +0x14c
main.main()
	/home/michael/Projects/go/src/github.com/kshvmdn/fsql/main.go:144 +0x3bb

It outputted two files before crashing. Go version 1.8.1 linux/amd64, do you need more information?

Path as attribute

Most of the use cases where I can see fsql being useful, normally involve doing something with the files found in the query, it would be really useful to know their file system location, at the moment additional manipulation is needed to get the path easily.

Adding path attribute as a fully resolved path, opens up a lot of possibilities.

Add support for UPDATE / DELETE operations

Would you mind add update support to this tool.
eg.update dest = 'xxxx' to copy select result file to dest folder.
or delete from where XXX
I just want this tool long ago. Thanks in advance.

Make where clause optional