Database-based file system

[iansjack]

Further details here of how sqlite uses temp files: https://www.sqlite.org/tempfiles.html

[eekee]

Further details here of how sqlite uses temp files: https://www.sqlite.org/tempfiles.html

Thanks. So no tempfiles means:

• Risk of corruption on power loss. Many filesystems have that problem anyway. Not a problem if you give it a journal partition
• No WAL, which doesn't seem necessary for this use anyway

The following will only work if there is enough memory:

• UPDATE and INSERT within BEGIN...COMMIT
• Some subqueries
• ORDER BY and GROUP BY, unless suitable indexes are generated prior to the query
• Compound queries containing DISTINCT, EXCEPT, and INTERSECT
• UNION, unless in the form UNION ALL
• VACUUM, which rebuilds the entire database into a whole new file
• If the journal is not in memory, queries which address multiple databases may not be atomic across the whole set of databases

For "enough memory", how about a massive virtual memory partition? That doesn't require a filesystem either.

You know, that page looks like a list of Terrible Bad Things at first glance, but it's not so bad when you look closer. For instance, section 2.4 is irrelevant if the journal is in memory. Section 3 explains most of the temp files can go in memory.

I guess the "tiny memory footprint" would blown away if you're operating without a filesystem. Or maybe not "when there aren't that many files in it to begin with". I dunno, I can't say more without learning SQL.

[Ethin]

Just curious what your reservations are, Iansjack? I haven't tried something like this in a real test case; if I do create an SQLite3-backed file system, I'll no doubt use Fuse to start off and see how that works with files, small to massive.
I could certainly take advantage of SQLites VFS layer and just make SATA/ATAPI calls underneath to make the FS work. If we could figure out the right normal form for files and directories, we could take advantage of a select statement when searching with files, which may or may not make general searching and file retrieval far faster. I wonder if I should test it just to see how it performs?

[LtG]

Just curious what your reservations are, Iansjack? I haven't tried something like this in a real test case; if I do create an SQLite3-backed file system, I'll no doubt use Fuse to start off and see how that works with files, small to massive.
I could certainly take advantage of SQLites VFS layer and just make SATA/ATAPI calls underneath to make the FS work. If we could figure out the right normal form for files and directories, we could take advantage of a select statement when searching with files, which may or may not make general searching and file retrieval far faster. I wonder if I should test it just to see how it performs?

What benefit do you get from that?

To get good performance you need to create an index, once you accept that, then the question becomes, SQL index or more specific index? Guess which has better performance.

Also worth mentioning, all statements (including mine) are BS, measure if you have the time and want the truth. But don't measure SQL against some bad FS, create a good FS with a good index (better than SQL) and then you should find that the good FS has best performance and the other two are in some order which doesn't matter anymore.

[Ethin]

Just curious what your reservations are, Iansjack? I haven't tried something like this in a real test case; if I do create an SQLite3-backed file system, I'll no doubt use Fuse to start off and see how that works with files, small to massive.
I could certainly take advantage of SQLites VFS layer and just make SATA/ATAPI calls underneath to make the FS work. If we could figure out the right normal form for files and directories, we could take advantage of a select statement when searching with files, which may or may not make general searching and file retrieval far faster. I wonder if I should test it just to see how it performs?

What benefit do you get from that?

To get good performance you need to create an index, once you accept that, then the question becomes, SQL index or more specific index? Guess which has better performance.

Also worth mentioning, all statements (including mine) are BS, measure if you have the time and want the truth. But don't measure SQL against some bad FS, create a good FS with a good index (better than SQL) and then you should find that the good FS has best performance and the other two are in some order which doesn't matter anymore.

I don't believe anyone's statement in this topic is BS. Everyone's input is very valuable, since this is purely a concept and has yet to be brought into existence as a thing.
I don't know how good an SQL index is compared to a specialized index in regards to performance; that would need to be tested and benchmarked.
Even if this is brought into existence and doesn't become super popular, it would be an interesting learning exercise -- especially if I use an implementation like Fuse as a backbone. Right now the idea is purely theoretical -- though, according to posts in this topic, it has been done before; I don't know how successful said attempts were. Even if they are't used by modern computers (i.e. available out of the box/native support) those attempts may still be in use in particular places.

[iansjack]

You are right. The best test of your idea is to implement it.

Let us know how you get on.