Find files with SQL

fsql Build Status Go Report Card

Search through your filesystem with SQL-esque queries.

Contents

Demo

fsql.gif

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.

Owner
kashav
a friendly fellow
kashav
Comments
  • Hash support

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

  • Simplify query structure

    Simplify query structure

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

      $ fsql "name, size, ... FROM . WHERE ..."
      $ fsql "FROM . WHERE ..."
      
    • [x] Make WHERE clause optional (#8).

    • [x] Quote-less queries:

      $ fsql SELECT \* FROM . WHERE ...
      $ fsql all FROM . WHERE ...
      
    • [x] Make FROM clause optional, directory should default to the current directory (./).

  • Use regex to detect exclusions instead of string.Contains

    Use regex to detect exclusions instead of string.Contains

    I added a struct which uses a regex to determine whether or not a file should be excluded.

    if .git is excluded, it excludes .git and .git/*, and will not exclude .gitignore

    in general if you have a list of exclusions .git, .gitignore the generated regexp will look like: ^\.git$|^\.git/.*$|^\.gitignore$|^\.gitignore/.*$

    Fixes: https://github.com/kshvmdn/fsql/issues/5

  • Hash support

    Hash support

    For #9 (started in #30).

    Adds SHA1 hash support to SELECT and WHERE clause. Should be very easy to extend this to other hashing algorithms (we'll just need to add them to transform.FindHash).

    Some basic examples (note that there's no support for hash length in conditions):

    > SELECT name, hash FROM .
    > SELECT name, SHA1(hash) FROM .
    > SELECT name, SHA1(hash, 20) FROM .
    
    > SELECT all FROM . WHERE hash = e950e1594893642a9629a0a0d9d920fec8e8e73f
    > SELECT all FROM . WHERE SHA1(hash) = ./fsql.go
    

    Also introduces an evaluate package which is in charge of all relevant comparison/evaluation work, updated structure:

    . (fsql)
    ├── cmd
    │   └── fsql (main)
    ├── evaluate
    ├── parser
    ├── prompt
    ├── query
    ├── tokenizer
    ├── transform
    
  • Go vendoring?

    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 -

    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.

  • Allow attribute transformations in SELECT clause

    Allow attribute transformations in SELECT clause

    Implements transformation on attributes. Currently only implements format(size,'kb'/'mb'/'gb'), upper, fullpath but other functions can easily be added. Example query:

    fsql "select format(size,'kb'), upper(fullpath(name)) from ."

  • Attribute modifiers

    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 https://github.com/kshvmdn/fsql/issues/15#issuecomment-301967875, 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).

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

    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?

  • Release Version to allow addition to Homebrew

    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.

  • Excluding directories also excludes similarly-named files

    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.

  • Feature request: limit number of results

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

  • feature request: plugin API for extending the fsql abilities

    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

  • SELECT query does not print folders from recursive paths.

    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
    
  • Add support for UPDATE / DELETE operations

    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.

  • `fsql 1gb"` doesn't work">

    `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
    
Related tags
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

Oct 20, 2021
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

Jan 25, 2022
Use SQL to data from CSV files. Open source CLI. No DB required.
Use SQL to data from CSV files. Open source CLI. No DB required.

CSV Plugin for Steampipe Use SQL to query data from CSV files. Get started → Documentation: Table definitions & examples Community: Slack Channel Get

Nov 10, 2022
Get data from .csv files use SQL-like queries.

csvql Get data from .csv files use SQL-like queries. Задание Необходимо написать консольную программу, которая по заданному клиентом запросу осуществл

Dec 7, 2021
Opionated sql formatter for use with .go files containing backticked queries

fumpt-the-sql Opionated sql formatter for use with .go files containing backticked queries. Uses https://sqlformat.darold.net/ for the actual sql form

Dec 10, 2021
Querycrate - A simple library that loads and keeps SQL queries from files

QueryCrate A simple library for loading & getting string queries from files. How

Feb 15, 2022
Go package for sharding databases ( Supports every ORM or raw SQL )
Go package for sharding databases ( Supports every ORM or raw SQL )

Octillery Octillery is a Go package for sharding databases. It can use with every OR Mapping library ( xorm , gorp , gorm , dbr ...) implementing data

Dec 16, 2022
Prep finds all SQL statements in a Go package and instruments db connection with prepared statements

Prep Prep finds all SQL statements in a Go package and instruments db connection with prepared statements. It allows you to benefit from the prepared

Dec 10, 2022
pggen - generate type safe Go methods from Postgres SQL queries

pggen - generate type safe Go methods from Postgres SQL queries pggen is a tool that generates Go code to provide a typesafe wrapper around Postgres q

Jan 3, 2023
🐳 A most popular sql audit platform for mysql
🐳 A most popular sql audit platform for mysql

?? A most popular sql audit platform for mysql

Jan 6, 2023
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jan 7, 2023
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in

Jul 1, 2022
Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

?? Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

Nov 9, 2022
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

Jan 9, 2023
auto generate sql from gorm model struct

gorm2sql: auto generate sql from gorm model struct A Swiss Army Knife helps you generate sql from gorm model struct. Installation go get github.com/li

Dec 22, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

Dec 26, 2022
Fluent SQL generation for golang

Squirrel is "complete". Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork

Dec 29, 2022
SQL Optimizer And Rewriter
SQL Optimizer And Rewriter

文档 | FAQ | 变更记录 | 路线图 | English SOAR SOAR(SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化工具。 由小米人工智能与云平台的数据库团队开发与维护。 功能特点 跨平台支持(支持 Linux, Mac 环境,Wind

Jan 4, 2023
Additions to Go's database/sql for super fast performance and convenience.

gocraft/dbr (database records) gocraft/dbr provides additions to Go's database/sql for super fast performance and convenience. $ go get -u github.com/

Jan 1, 2023