6 ms·
Fselect: Find files with SQL-like queries
- leephillips 6y agoI haven’t tried it yet, but his looks potentially super useful.
- freedomben 6y agoIf you're skeptical, I was too. My thoughts before reading the README.md: SQL is nice and all but seems verbose and awkward compared to just using find. My thoughts after reading the README.md: Wow, this tool can query on file format-specific metadata such as ID3 tags in mp3 files and even width/height in pictures. This is really neat. There is a lot of possibility here.
- jbverschoor 6y agoAhh BeFS/BFS. I had my whole MSX-rom collection with file system attributes, including thumbnails
- Crontab 6y agoI loved BeOS/BeFS. I remember exporting ID3 tags from mp3 files into file system attributes and querying them to create playlists. Aside from Haiku, does anyone know if any of the open-source Unix-like operating systems support arbitrary extended file attributes?
- boardwaalk 6y agoIt seems like most of them do[1] (inc. ext4, zfs, btrfs), but they’re fairly limited in size (but should be fine for media tags and such). [1] https://en.wikipedia.org/wiki/Extended_file_attributes#Linux https://en.wikipedia.org/wiki/Extended_file_attributes#Linux
- jbverschoor 6y agoThey do.. but integration is almost nonexistent. All e-mail were separate eml files, with attributes for most of the important data. Same for contacts. In BeOS everything was more a file than in most *nix
- kitd 6y agoNot unix-y but OS/2 had something similar.
- rhizome 6y agoOf course a built-in would be nice, but I think one could get 99% of the way there just with a specialized scanner (that I don't know the name of) that kept its own database, like CTAGs or the old OSX Quicksilver (and probably Spotlight after it).
- cbm-vic-20 6y agoThe BeOS filesystem had something like this built-in, back in the day. https://arstechnica.com/information-technology/2018/07/the-beos-filesystem/ https://arstechnica.com/information-technology/2018/07/the-b...
- tjfontaine 6y agoMy first thought was https://en.wikipedia.org/wiki/WinFS https://en.wikipedia.org/wiki/WinFS
- crazygringo 6y agoI just want to say this is really cool. Honestly I'd love to see SQL (or something like it) become a first-class citizen in operating systems generally -- as a standard way for manipulating files, logs, preference files, etc. To be clear: not turning things into databases (keep logs as logs), but make everything interpretable to SQL. It's bizarre to me that in just a few lines I can achieve magic with a database... but I can't trivially do the same magic with my filesystem. Just like a shell is an integral part of an OS... shouldn't a query language be too? I'd love it if, out-of-the-box, Linux distributions came with a standardized SQL+JSON-over-the-filesystem approach that functioned as a small-scale working database for any tool that wanted to use it as such.
- aratno 6y agoHave you used osquery? Seems like a good chunk of what you’re looking for! https://www.osquery.io/schema/4.7.0/ https://www.osquery.io/schema/4.7.0/
- crazygringo 6y agoIt's halfway what I'm talking about, thanks. Absolutely yes to the idea of being able to query everything! But for me the idea of writing (CREATE, UPDATE, DELETE) would be an integral part of it as well -- whether inserting a property into a JSON file, adding a cronjob as column parameters rather than a text line, or creating files.
- neolog 6y agoThe writing part is what NixOS does (with Nixlang rather than SQL).
- mcint 6y agoSee also https://augeas.net/ https://augeas.net/, which presents structured file contents as navigable paths that you update. Red hatters maintained it (hung out in irc) until cloud container workflows surpassed in-place system administration. It pairs nicely with puppet, and can be used manually.
- dimator 6y agoI've found myself using this rather than come up with the equivalent `find` predicate.
- AtlasBarfed 6y agoI know this is rust and has its own query engine and it is pretty cool, but makes me wonder how much can be done with: 1) dump filesystem (or some other data schema generation) metadata into sqllite 2) pass query to sqllite 3) pass sqllite response to stdout and it would still be faster than the adhoc query engines like this.
- jhayward 6y agoIn my experience the limiting factor in response time is the traversal of the FS/OS structures in your step 1. It seems unlikely that anything this program is doing would be any slower than what you are describing.
- majkinetor 6y agoNot necessarily. On Windows for example there is Everything search engine which scans NTFS table and installs filter driver. Its instant on any disk size. If it were keeping its database in sqlite, we would have exactly what AtlasBarfed suggested.
- jhayward 6y agoI am drawing a distinction between actually using an SQL database as the dynamic attribute store of the file system, vs “dumping the FS to SQLite”, which implies an on-demand traversal to me.
- tyingq 6y agoSqlite does have the fileio extension, which includes an fsdir() function that will traverse a directory. sqlite> .headers on sqlite> .mode column sqlite> SELECT name,mode,mtime FROM fsdir("/usr") where name like '%.h' LIMIT 3; name mode mtime ------------------------ ---------- ---------- /usr/include/_G_config.h 33188 1607359089 /usr/include/aio.h 33188 1607359089 /usr/include/aliases.h 33188 1607359089 It's not nearly as full featured as this Fselect tool, though. I'm somewhat surprised nobody has cloned or updated the extension and added more stat() fields or things like extended file attributes. It doesn't even have the file size.
- Jeff_Brown 6y agoGrep integration would be awesome -- e.g. "find files with name satisfying x and content satisfying y", or (even better) "files in which grep query y is satisfied within n lines of a spot satisfying grep query y".
- TruthWillHurt 6y agoIsn't this what pipes are for?
- tyingq 6y agoIt does support regex expressions directly: ~= | =~ | regexp | rx Used to check if the column value matches the regex pattern !=~ | !~= | notrx Used to check if the column value doesn't match the regex pattern
- rhizome 6y agofind $PATH -regex $FILENAME_REGEX -exec grep $CONTENT_REGEX grep -r [-E] $CONTENT_REGEX --include $FILEGLOB Your bonus case: find $PATH -regex $FILENAME_REGEX \ -exec sh -c "grep [-E] -$NUMBER_OF_CONTEXT_LINES $CONTENT_REGEX1 | grep -l [-E] $CONTENT_REGEX2" The "[-E]" here is just to mention the command switch if you want to use extended regex.
- suifbwish 6y agoAm I the only one that always creates an alias pointing grep to egrep? Not sure why I would ever want my regex to not be extended or why it would be fun to have to have to escape characters by default
- forinti 6y agoI've been wanting to have something like this. There is a nice CPAN module called File::Find::Rule which has similar functionality, but you need to write some code, of course.
- diroussel 6y agoThis is very similar to fsql, which looks similary useful. https://github.com/kashav/fsql https://github.com/kashav/fsql
- tgv 6y agoThis on macOS' Spotlight database...
- taklamundak 6y agoAwesome, loving
- gnufx 6y agoHPC sites are likely to use https://github.com/cea-hpc/robinhood/wiki/Documentation https://github.com/cea-hpc/robinhood/wiki/Documentation. While its user interface isn't SQL, it does store the filesystem metadata in an SQL database (updated from changesets for Lustre filesystems). You don't want to run -- or, specifically, have users run -- ls, find, or du on significant amounts of a typical large filesystem. (As I understand it, you could do something similar to lustre with Isilon/OneFS, for instance.) The metadata store of OrangeFS (PVFS2) is implemented with a database, though a key-value one, and one of the project ideas for it is to search that directly.
- mshockwave 6y agoI'm surprised to see this in the `--help` menu: ``` Japanese string: CONTAINS_JAPANESE Used to check if string value contains Japanese symbols CONTAINS_KANA Used to check if string value contains kana symbols CONTAINS_HIRAGANA Used to check if string value contains hiragana symbols CONTAINS_KATAKANA Used to check if string value contains katakana symbols CONTAINS_KANJI Used to check if string value contains kanji symbols ```
- TruthWillHurt 6y agoThis is amazing. A lot of people forget the fact that the filesystem is in a way a database, and a flexible one at that!
- iwebdevfromhome 6y agoI have a lot of files in my home directory, specially in the code folder where all my projects are, and you can imagine all the files that live in the python virtualenv and node_modules but even so the command finished execution in 45s, I think this is very impressive!
- bdcravens 6y agoColdFusion/CFML has had this capability for a while: Most iterables return a "query" data type, and queries can be queried against using an in-memory SQL engine. https://cfdocs.org/directorylist https://cfdocs.org/directorylist https://cfdocs.org/queryexecute https://cfdocs.org/queryexecute
- samatman 6y agoThe perfect complement for this would be a reboot of the BeOS-style filesystem. For the unaware, BeFS combines the attributes of a hierarchical filesystem and (some of) a relational database. It could do interesting things like store contacts as a zero-contents file which had key-value pairs for things like names and email addresses. There are some open-source reimplementations of it, thanks to the Haiku project; I wonder if they've ever been integrated with the Linux kernel and distros.
- hinkley 6y agoMicrosoft tried this in NT5.0 and ended up shelving it. Trying again was a substantial reason for the delay of one of the more recent versions. Vista perhaps?
- szhu 6y agoReminds me of osquery! Looks like osquery can also be used for file searching, but it's not as optimized for command-line usage. https://blog.kolide.com/the-file-table-in-osquery-is-amazing-99db0f52a066 https://blog.kolide.com/the-file-table-in-osquery-is-amazing...