If this is posted by the author looking for feedback:

1) WebDAV is a much better choice than FUSE. FUSE is a good concept, but buggy and poorly-implemented. Things like sshfs can break in very bad ways if e.g. there is a network connectivity issue. Not a hack.

2) Writes seem like a very bad idea. Keep those out unless you come up with a clean way to handle them (which seems difficult if not impossible given the differences in FS versus relational abstractions, especially with regards to data validation). Not a limitation.

In other words, the "hacks" seem like design choices a good architect would likely have made. Continuing:

3) The major use-case I have is if I have a small (

4) I think a major theoretical question is how to fuse the two models. I would like to be able to do 'generic' things like the above on databases, while still being able to be relational.

5) I don't have an answer to the above, but perhaps natural first step might be to allow something like queries or virtual tables to sit on the file system:

wddbfs --anonymous --db-path=/path/to/an/example/database/like/Chinook_Sqlite.sqlite

wddbfs_query myjoin "SELECT * FROM table_1, table_2 WHERE table_1.id=table_2.id"

And voila! A /virtual/myjoin.csv file pops up.

(Even more) half-baked thoughts:

There might be more clever ways to do it too. I'm thinking through half-baked thoughts on how to make files and tab completion work. My half-baked thoughts are moving towards something like:

wddbfs_SELECT * from Customer.tsv\, Employee.tsv WHERE

But I don't like all the potential bugs with escaping. I'm also thinking about when output wants to go to the console versus into a virtual table.

That's a pretty spicy opinion. WebDAV is slow; FUSE works great. Use interrupt mounts for things like sshfs (or NFS, for that matter).

> Writes seem like a very bad idea. Keep those out unless you come up with a clean way to handle them (which seems difficult if not impossible given the differences in FS versus relational abstractions, especially with regards to data validation).

You could imagine supporting appending new records in json format to the json/jsonl files in a pretty clean way, but I agree it seems well out of scope. And removing records seems basically impossible.

I have had problems with particular fuse implementations (sshfs included, s3fs especially), but I've never had trouble writing FUSE implementations that behave correctly. Is there something deficient in the spec that you want to call out, or is the idea just that WebDAV carefully built networking into its core concepts, where you have to do a lot of extra work to squeeze those ideas correctly into a FUSE implementation?

Ha. I would bet anything you could adopt my MySQL findall() to SQLite for the purpose. https://stackoverflow.com/a/68915413/308851

I just can't decide if you're a sci-fi supervillain, a political thriller supervillain, or a Despicable Me supervillain.

;-)

Oh well, there's my FUSE rant for the day, I guess. Now, on to what I came to comment on....

I'm indifferent on the WebDAV vs FUSE thing, actually. But if the author wanted a nicer way to query a bunch of text files, where each line would roughly represent what a db row would, and there's whatever random delimiter character in there (comma, tab, pipe, or even those weird ASCII control characters nobody ever knows or remember exist for the purpose [0]), then I think he gave in and went to a database too soon. Besides the ubiquitous GNU textutils, there are tons of tools out there for working with record-oriented CSV and TSV files. [1] There's even `jq` if you're a real masochist and want to use json as your text-based data storage, query, and interchange format.

Now you've got me wondering if I was lucky in not having any significant issues with FUSE. But, as far as OP and the technical problems they were facing, I think it was a little premature to jump to `sqlite`. All those things they put down to "clunkiness" really just makes me thing that `sqlite` + WebDAV ended up being one of the members of the set of simple, elegant, and wrong solutions most problems seem to have. ¯\_(ツ)_/¯

---

[0]: https://stackoverflow.com/a/18782271

[1]: https://stackabuse.com/running-sql-on-csv-data-data-conversi...

For your model to work, what would be needed is the reverse: a system which allows me to interact with TSV files with SQL.

For 98% of uses of SQLite, performance and storage efficiency don't matter; we're talking about dozens or hundreds of records. It doesn't need to be well-implemented from that perspective. It does need to be robust. For example, databases solve a lot of problems especially around consistency in threaded systems which this would need to handle as well (although a KISS solution, such as handling all access from one thread / process, and simply blocking other requests during transactions, would be fine).

Something like that would likely eat SQLite's lunch.

q seems close. It'd need to support writes, table creation, and all the other stuff, perhaps be more actively maintained, provide documented API (not just command-line) access, and work with things like ORMs.

Several such systems exist, one of which is SQLite. https://www.sqlite.org/csv.html

I don't know if there's a virtual table extension for tab separated values, but SQLite imports them just fine.

ClickHouse and DuckDB can both do this. Pretty sure AWS has some built-in stuff for this too.

That said, I'm reading their documentation right now, and I think one or the other might slot into a different use-case I have where I do have rather large data. I'd last looked at this space quite a number of years ago, and both are way ahead of what I'd recalled.

That’s a peculiar critique of FUSE. You could write the same thing about NFS or SMB (but especially NFS).

If I want the 50,004,123,121th byte of a file from a disk, that's very fast. If I want the same for a virtual object from an HTTP server, object store, virtual table, etc. it literally involves creating the whole object, and stepping through it byte-by-byte until I get there.

If the next request is doing the same 1k ahead, on a disk, that's probably in cache, and if not, I can get there quickly. If this was a SQL query, I probably need to redo the whole thing.

You get a natural explosion from O(1) to O(n) in many common cases, and for something like a complex SQL query, it can be much, much worse.

FUSE is much, much easier to work with. The protocol (even v4) should also be less chatty, and if performance is your goal there are projects like ExtFuse that can move some caching into the kernel via eBPF which I don't think would be possible with NFS.

If you poke around other user space file systems (sshfs, cephfs, objectivefs, etc) they're all FUSE.

https://github.com/Airsequel/SQLiteDAV

My mapping is: table -> dir, row -> dir, cell -> file

At such a set of features I would prefer a tool that converts databases to a directory of real csv and jsonl files, at least there are no performance issues to worry about

I use DBeaver to inspect SQLite files, and to also work with Postgres databases.

I kind of miss MySQL Workbench, but MySQL is pretty dead to me. And SQL Server Management Studio is a relic that keeps being updated.

I also sometimes make dashboards from SQLite files using Grafana, but the time functions for SQLite are pretty bad.

like is this really meaningful improvement

    $ tail -n 3 Chinook_Sqlite.sqlite/Album.tsv

    $ sqlite3 -tabs Chinook_Sqlite.sqlite 'select * from Album' | tail -n 3