Is "It's not a real SQL" a feature?

pavlus commented 3 years ago

I was thinking about more advanced queries and metadata, and some of them, along with some existing, are playing nicely with more traditional SQL syntax, but it could complicate writing queries for users, or very much complicate parsing and introduce unexpected behavior, if relaxed syntax remained.

Examples:

Arrays

is_image, is_video, etc could be global array variables containing extensions of files, so is_video = true becomes something like ext in _video_exts

Relations

mp3_* are implying that we parse only MP3 ID3 tags, but there are similar metadata available in OGG, FLAC and other files. We could have mp3, vorbis_comments, etc. relations with a single view like tags coalescing data from columns in those relations, (btw, images and videos can have tags too).
Glob expansion for relations

select path, tags.* from ~/Music/ -- simple way to display all tags for audio files.

GROUP BY

Currently aggregation is total, we cannot define grouping criteria, if we could, such use-cases would be possible:
select tags.album, count(tags.title) from ~/Music/ group by tags.album -- list number of titles in each album.
select ext, format_size(sum(size)), count(ext) from ~/ group by ext -- statistics of disc usage by file extension.
Aliases

From the top of my head would only affect naming of fields in JSON output, or CSV headers, if there were csv-with-headers output option, but with support for tuples, we could also do something like this: select path, (width, height) as dimensions from ~/Pictures/ where ext in _image_exts into json, which could produce output like this:
```
[
{
"path": "~/Pictures/cats.jpg",
"dimensions": {
"width": 1024,
"height": 768
},
},
{
"path": "~/Pictures/dogs.jpg",
"dimensions": {
"width": 640,
"height": 480
}
}
]
```

shah commented 2 years ago

I just discovered this fantastic tool and found myself thinking the same thing @pavlus - it would be great if we could offer fselect to our data scientists that already know SQL but are not good at the CLI. For now fselect is already much better than using find at the CLI but the improvements you've suggested in this ticket would be ideal.

rickological commented 2 years ago

I've also very recently discovered this tool and I love it - and for me GROUP BY to work with the aggregate functions it already has would be amazing. I'm new to Rust (from PHP) and learning lots but at present adding the above might be a bit beyond my ability. I am still going to try tho!

jhspetersson / fselect