7 ms·

Wddbfs – Mount a SQLite database as a filesystem

291 points by vitplister 3y ago

account-5 3y ago
This looks pretty cool.
pletnes 3y ago
Wrap in an SSH tunnel and you can do some fun stuff over the network, too.
- rickette 3y ago
  Over the network (when using azure or gcp storage) there's also https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki which doesn't require loading everything in memory.
eternauta3k 3y ago
Why webdav instead of making short sqltocsv, sqltojson, etc scripts? You could make completion work (with some enormous time investment).
- dmd 3y ago
  Because those all exist already, and this is cool?
  - eternauta3k 3y ago
    A fine reason, I just thought you had a niche need prompting you to do it like this.
renonce 3y ago
> Although for now, the whole table gets read into memory for every read so this won’t work well for very large database files. There’s also no write support… yet. 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
AnyTimeTraveler 3y ago
I really like the idea of running a watch on an sqlite table with my common cli tools. If I understand correctly, it is being re-read on every request. Does that mean that changes to the sqlite database will be visible on the next read of a csv file?
blagie 3y ago
This seems really nice! 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 (<1MB) database, and don't know the structure. Lots of tools use small sqlite databases. There is no way to query all tables for something, whereas tools like `find` and `grep` can look through all files. I was recently trying to recover some lost data, and it was a pain to find it. 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.
- alexnewman 3y ago
  Don't people use NFS instead of Fuse now? I'd assume it would have much better performance than webdav and it should handle a bunch of the issues around writes`
  - blagie 3y ago
    I am moving outside of my zone of expertise, but when I last looked (decades ago), systems like NSF assumed a filesystem backing store and thinks about things like byte ranges. There are a lot of operations which file systems support which work very poorly when this is not true, such as seeks, memory-mapped files, etc. 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.
adius 3y ago
I'm also working on something like this: https://github.com/Airsequel/SQLiteDAV https://github.com/Airsequel/SQLiteDAV My mapping is: table -> dir, row -> dir, cell -> file
- zokier 3y ago
  I wonder if mapping index->directory would be the best match, that way you could at least hypothetically reclaim some of the SQL benefits, and the directory entries could have more natural names. You'd need to have slightly different structure for unique vs non-unique indices, but that seems like minor issue.
mharig 3y ago
Nice. I think exposing the tables as csv, tsv, json & jsonl is to much of a cluttering. Format should be a mount option.
- pjerem 3y ago
  It is
jFriedensreich 3y ago
I build a couchDB webdav server back in the day, you could also edit json documents or file blobs directly. the problem i discovered was that all OSes totally staled their webdav support and there are also enough differences between oses to be annoying. In the end to build somthing with great performance you would also need to control the webdav client side and probably build a fuse webdav client. I would have loved to see webdav maturing and becoming what the 9P vision was just for the web, but this obviously never happened as all the applications just went into to the web and used rest intead of webdav and everything else moved to sync protocols that sync to local folders.
- paul_h 3y ago
  I'm with you on wishing WebDAV continued its rollout. These days there are great low-drama server-side deployments like https://github.com/sigoden/dufs https://github.com/sigoden/dufs. It's run relative too - you could habe multiple dufs processes serving up different directories in different ways. But for WebDAV, you can't simply mount that on the client side for every OS that's equally low configutaion. For that reason, I really like sshfs as it can be initiated from the client-side without a lot of config (just a mkdir of the mapped dir), and it's OK most time despite it's lack of speed and multi-day uptime. I'm on a chromebook now and it turns out that Samba is the easiest client-side tech to use for remote file systems. DAv should've been uniquitous.
nikeee 3y ago
Does it support mounting a sqlar file?
loeg 3y ago
Cute, seems legitimately useful, succinct. Not everything has to be super technically challenging to be valuable. I can see how this would be really handy.
- zokier 3y ago
  I think its neat proof of concept, but I struggle to see any case where this would be particularly useful. Or rather when this would be more handy than what sqlite cli already offers. like is this really meaningful improvement $ tail -n 3 Chinook_Sqlite.sqlite/Album.tsv over this? $ sqlite3 -tabs Chinook_Sqlite.sqlite 'select * from Album' | tail -n 3
Nican 3y ago
> the SQL syntax for selecting a few records is much more verbose than head -n or tail -n 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.
- callamdelaney 3y ago
  Could you expand on why the time functions in sqlite are pretty bad?
genocidicbunny 3y ago
Wasn't there an extension that let you mount a filesystem as a table or db in sqlite? I wonder how far you can inception that. Mount a db as a filesystem, then mount that filesystem as a db..etc.
- dvaun 3y ago
  Borrowing concepts from DB2 here
yellowapple 3y ago
I was expecting this to be a way to mount so-called SQL Archives (https://sqlite.org/sqlar.html https://sqlite.org/sqlar.html) but this is just as cool.
T-A 3y ago
> Part of this is avoiding the overhead of figuring out a relational schema, but an equal amount of friction comes from the fact that .sqlite files are just slightly more difficult to inspect I like this solution to that problem: https://sqlitebrowser.org/ https://sqlitebrowser.org/

291 points by vitplister 3y ago

account-5 3y ago

This looks pretty cool.

pletnes 3y ago

Wrap in an SSH tunnel and you can do some fun stuff over the network, too.

rickette 3y ago
Over the network (when using azure or gcp storage) there's also https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki https://sqlite.org/cloudsqlite/doc/trunk/www/index.wiki which doesn't require loading everything in memory.

eternauta3k 3y ago

Why webdav instead of making short sqltocsv, sqltojson, etc scripts? You could make completion work (with some enormous time investment).

dmd 3y ago
Because those all exist already, and this is cool?
- eternauta3k 3y ago
  A fine reason, I just thought you had a niche need prompting you to do it like this.

renonce 3y ago

> Although for now, the whole table gets read into memory for every read so this won’t work well for very large database files. There’s also no write support… yet. 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

AnyTimeTraveler 3y ago

I really like the idea of running a watch on an sqlite table with my common cli tools. If I understand correctly, it is being re-read on every request. Does that mean that changes to the sqlite database will be visible on the next read of a csv file?

blagie 3y ago

This seems really nice! 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 (<1MB) database, and don't know the structure. Lots of tools use small sqlite databases. There is no way to query all tables for something, whereas tools like `find` and `grep` can look through all files. I was recently trying to recover some lost data, and it was a pain to find it. 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.

alexnewman 3y ago
Don't people use NFS instead of Fuse now? I'd assume it would have much better performance than webdav and it should handle a bunch of the issues around writes`
- blagie 3y ago
  I am moving outside of my zone of expertise, but when I last looked (decades ago), systems like NSF assumed a filesystem backing store and thinks about things like byte ranges. There are a lot of operations which file systems support which work very poorly when this is not true, such as seeks, memory-mapped files, etc. 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.

adius 3y ago

I'm also working on something like this: https://github.com/Airsequel/SQLiteDAV https://github.com/Airsequel/SQLiteDAV My mapping is: table -> dir, row -> dir, cell -> file

zokier 3y ago
I wonder if mapping index->directory would be the best match, that way you could at least hypothetically reclaim some of the SQL benefits, and the directory entries could have more natural names. You'd need to have slightly different structure for unique vs non-unique indices, but that seems like minor issue.

mharig 3y ago

Nice. I think exposing the tables as csv, tsv, json & jsonl is to much of a cluttering. Format should be a mount option.

pjerem 3y ago
It is

jFriedensreich 3y ago

I build a couchDB webdav server back in the day, you could also edit json documents or file blobs directly. the problem i discovered was that all OSes totally staled their webdav support and there are also enough differences between oses to be annoying. In the end to build somthing with great performance you would also need to control the webdav client side and probably build a fuse webdav client. I would have loved to see webdav maturing and becoming what the 9P vision was just for the web, but this obviously never happened as all the applications just went into to the web and used rest intead of webdav and everything else moved to sync protocols that sync to local folders.

paul_h 3y ago
I'm with you on wishing WebDAV continued its rollout. These days there are great low-drama server-side deployments like https://github.com/sigoden/dufs https://github.com/sigoden/dufs. It's run relative too - you could habe multiple dufs processes serving up different directories in different ways. But for WebDAV, you can't simply mount that on the client side for every OS that's equally low configutaion. For that reason, I really like sshfs as it can be initiated from the client-side without a lot of config (just a mkdir of the mapped dir), and it's OK most time despite it's lack of speed and multi-day uptime. I'm on a chromebook now and it turns out that Samba is the easiest client-side tech to use for remote file systems. DAv should've been uniquitous.

nikeee 3y ago

Does it support mounting a sqlar file?

loeg 3y ago

Cute, seems legitimately useful, succinct. Not everything has to be super technically challenging to be valuable. I can see how this would be really handy.

zokier 3y ago
I think its neat proof of concept, but I struggle to see any case where this would be particularly useful. Or rather when this would be more handy than what sqlite cli already offers. like is this really meaningful improvement $ tail -n 3 Chinook_Sqlite.sqlite/Album.tsv over this? $ sqlite3 -tabs Chinook_Sqlite.sqlite 'select * from Album' | tail -n 3

Nican 3y ago

> the SQL syntax for selecting a few records is much more verbose than head -n or tail -n 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.

callamdelaney 3y ago
Could you expand on why the time functions in sqlite are pretty bad?

genocidicbunny 3y ago

Wasn't there an extension that let you mount a filesystem as a table or db in sqlite? I wonder how far you can inception that. Mount a db as a filesystem, then mount that filesystem as a db..etc.

dvaun 3y ago
Borrowing concepts from DB2 here

yellowapple 3y ago

I was expecting this to be a way to mount so-called SQL Archives (https://sqlite.org/sqlar.html https://sqlite.org/sqlar.html) but this is just as cool.

T-A 3y ago

> Part of this is avoiding the overhead of figuring out a relational schema, but an equal amount of friction comes from the fact that .sqlite files are just slightly more difficult to inspect I like this solution to that problem: https://sqlitebrowser.org/ https://sqlitebrowser.org/