News for hackers. Stuff that matters.
Re:Wddbfs – Mount a SQLite database as a filesystem by Paul-Craft on Sunday February 18, 2024 @08:30AM (#39418906)
Huh. I guess I'm one of the few who's had generally good experiences with FUSE, then. I've used several different FUSE filesystems, ranging from disk-based, to network-oriented, to... well, let's just say a bit odd, kind of fun, but probably not particularly useful. I even wrote a couple of the latter myself, just for funsies.

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

Re:Wddbfs – Mount a SQLite database as a filesystem by blagie on Sunday February 18, 2024 @12:34PM (#39421074)
I don't quite agree. There's a reason relational won, and continues to win more than a half-century after it was invented, despite no lack of wannabe usurpers.

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.

Re:Wddbfs – Mount a SQLite database as a filesystem by samatman on Sunday February 18, 2024 @01:39PM (#39421716)
> For your model to work, what would be needed is the reverse: a system which allows me to interact with TSV files with SQL.

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.

Re:Wddbfs – Mount a SQLite database as a filesystem by blagie on Tuesday February 20, 2024 @08:28PM (#39449192)
Interesting. Thank you.

It's intentionally available as a single source file:

https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/mi...

For easy modification to handle TSV, TSVx, JSON, Pandas, YAML, or whatever else my heart desires.

Re:Wddbfs – Mount a SQLite database as a filesystem by gkbrk on Sunday February 18, 2024 @12:37PM (#39421102)
> a system which allows me to interact with TSV files with SQL

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

Re:Wddbfs – Mount a SQLite database as a filesystem by blagie on Sunday February 18, 2024 @12:50PM (#39421224)
I think those are a very different use case. sqlite is used for things like app settings. Both of those are heavyweight monsters designed for rather large data.

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.