5 ms·

I strongly recommend using Sqlite for your own document format. Sqlite is one of the most well-tested pieces of software on earth and ACID-compliant. You can ev

I strongly recommend using Sqlite for your own document format. Sqlite is one of the most well-tested pieces of software on earth and ACID-compliant. You can even make it safer than the default if you don't need maximum performance only need it for storing documents. It is very crash and corruption proof, especially with the full sync option and if you use it from one thread only.

cjfd 7y ago
It has been a while ago so maybe things are better now but I have seen sqlite being a disaster when the times comes to upgrade the schema. Not all of the common operations like deleting/renaming a column are supported. Since it has been a while I don't remember exactly which ones. Then someone writes an sql script to provide this functionality. Then it turns out that a freshly created database using the latest schema is subtly different from one that was created from an older schema and then updated. Things like a column that has a default but only in one of these two cases. Lots of fun, but not really.
- blattimwind 7y ago
  For most uses of SQLite it's acceptable (or even advantageous) to copy the entire database for doing an upgrade, instead of the more RDBMS-y way of using DDL.
  - Hackbraten 7y ago
    Which brings you the additional challenge of how to overwrite the old database file with the new one atomically in the face of crashes.
    - tylerhou 7y ago
      http://man7.org/linux/man-pages/man2/rename.2.html http://man7.org/linux/man-pages/man2/rename.2.html > If newpath already exists, it will be atomically replaced, so that there is no point at which another process attempting to access newpath will find it missing.
      - Hackbraten 7y ago
        Did the original article not specifically make a point about Linux’s `rename` to be atomic only in the happy case and not when a crash happens during the rename?
        jng 7y ago
        Rename is the most atomic you can get on Unix. Original article talks about partial file updates. New SQLITE file + rename should be as failproof as possible. And I’m any case nothing is 100% safe, so redundancy is a hard requirement for real safety.
        waterhouse 7y ago
        I've encountered one gotcha there: SQLite operations may create "-journal" files, and you may have to be careful exactly what files you're copying or moving around. More: https://sqlite.org/howtocorrupt.html https://sqlite.org/howtocorrupt.html , https://sqlite.org/tempfiles.html https://sqlite.org/tempfiles.html
        Hackbraten 7y ago
        I was referring to the last section of the article, the part where it says rename isn’t atomic on crashes. Why not migrate traditionally via DDL in a transaction?
        mehrdadn 7y ago
        It's not clear to me if they're correct about that. The way it's written, it seems to be saying "my read of the POSIX standard suggests rename may not be atomic on crashes", rather than "there are POSIX implementations in common use that have been observed to have non-atomic renames on crashes".
        nine_k 7y ago
        On Windows you will at least have an old version and a new version lying around intact. Would take this over a single corrupted file any day.
- wruza 7y ago
  I think what gp meant is fopen-way of sqlite, not full-schema. Basically, tXML/tJSON singleton and tBlob(id, data) for embedded blobs. That way it will have all journaling, syncing, checksums and other fs wisdom that the article mentioned.
- laurent123456 7y ago
  Do you have any example of what you're describing? I've used SQLite for years, written many migrations and never had such issues. A reasonably popular app I'm working on has so far 25 migration steps [0] and I've never encountered something like a new schema being different from an updated one. I'd expect whatever mistake was made to get to that point could be made in any other RDBMS. 0: https://github.com/laurent22/joplin/blob/805a5399b5288b4a28273e9b5c407af868e7abb3/ReactNativeClient/lib/joplin-database.js#L323 https://github.com/laurent22/joplin/blob/805a5399b5288b4a282...
  - cjfd 7y ago
    After writing the above comment, memory comes back a bit. The problem is that sqlite does not support renaming a column directly. One can find procedures for that on the internet. E.g., https://tableplus.com/blog/2018/04/sqlite-rename-a-column.html https://tableplus.com/blog/2018/04/sqlite-rename-a-column.ht.... However, we also wanted to be able to rename columns where there are foreign key references, for instance references to the table of which a column was renamend. A generic function, not in sql, but in the programming language of our project, in this case c++, was written for this purpose, something like rename_column(table_name, old_column_name, new_column_name). Needless to say the first version of this function was not quite sufficient leading to the problems that I mentioned. In the end I had to improve this function. I remember at the time I was quite pleased with myself figuring out how to do this without temporarily switching off foreign key checking which the first version of the script had.
zwsxedcrfvtgb 7y ago
could you go a little more in depth about your setup? do you mean you add the sqlite library, load the file and then use sqlite to read/write data to a database instead of a text or bin file? I like it... I do simulations in physics and we're really not familiar with these types of things.
- tiborsaas 7y ago
  I do the same to manage a few thousand records with Node.js. I like that I don't depend on a complex DB engine. It's just one file, very compact. I even commit the file to GIT :)
- punnerud 7y ago
  Probably more than 50% of all the Android apps I have decompiled use SQLite for data storage. Some extra time to learn SQL but it will save you a lot of time in the long run. Another benefit compared to ASIC is that you get: File compression, easy to access random parts of the file and fast (random) inserts.
tobias3 7y ago
As a result of the paper linked in this article sqlite has a new setting PRAGMA synchronous=EXTRA; It is not the default (default is usually FULL).
burmecia 7y ago
Totally agree. To make file system crash-proof you really need an ACID compatible underlying storage layer or implement it inside the file system itself. In fact, file system and database are quite similar in case of atomicity and consistency. If an embedded ACID component is too heavy, utilizing a reliable database as storage, for example sqlite, might be a good idea. That’s exactly what I did in ZboxFS (https://github.com/zboxfs/zbox https://github.com/zboxfs/zbox) which can use sqlite as a underlying storage to achieve ACID compatible behaviours.

cjfd 7y ago

It has been a while ago so maybe things are better now but I have seen sqlite being a disaster when the times comes to upgrade the schema. Not all of the common operations like deleting/renaming a column are supported. Since it has been a while I don't remember exactly which ones. Then someone writes an sql script to provide this functionality. Then it turns out that a freshly created database using the latest schema is subtly different from one that was created from an older schema and then updated. Things like a column that has a default but only in one of these two cases. Lots of fun, but not really.

blattimwind 7y ago
For most uses of SQLite it's acceptable (or even advantageous) to copy the entire database for doing an upgrade, instead of the more RDBMS-y way of using DDL.
- Hackbraten 7y ago
  Which brings you the additional challenge of how to overwrite the old database file with the new one atomically in the face of crashes.
  - tylerhou 7y ago
    http://man7.org/linux/man-pages/man2/rename.2.html http://man7.org/linux/man-pages/man2/rename.2.html > If newpath already exists, it will be atomically replaced, so that there is no point at which another process attempting to access newpath will find it missing.
    - Hackbraten 7y ago
      Did the original article not specifically make a point about Linux’s `rename` to be atomic only in the happy case and not when a crash happens during the rename?
      - jng 7y ago
        Rename is the most atomic you can get on Unix. Original article talks about partial file updates. New SQLITE file + rename should be as failproof as possible. And I’m any case nothing is 100% safe, so redundancy is a hard requirement for real safety.
        waterhouse 7y ago
        I've encountered one gotcha there: SQLite operations may create "-journal" files, and you may have to be careful exactly what files you're copying or moving around. More: https://sqlite.org/howtocorrupt.html https://sqlite.org/howtocorrupt.html , https://sqlite.org/tempfiles.html https://sqlite.org/tempfiles.html
        Hackbraten 7y ago
        I was referring to the last section of the article, the part where it says rename isn’t atomic on crashes. Why not migrate traditionally via DDL in a transaction?
        mehrdadn 7y ago
        It's not clear to me if they're correct about that. The way it's written, it seems to be saying "my read of the POSIX standard suggests rename may not be atomic on crashes", rather than "there are POSIX implementations in common use that have been observed to have non-atomic renames on crashes".
      - nine_k 7y ago
        On Windows you will at least have an old version and a new version lying around intact. Would take this over a single corrupted file any day.
wruza 7y ago
I think what gp meant is fopen-way of sqlite, not full-schema. Basically, tXML/tJSON singleton and tBlob(id, data) for embedded blobs. That way it will have all journaling, syncing, checksums and other fs wisdom that the article mentioned.
laurent123456 7y ago
Do you have any example of what you're describing? I've used SQLite for years, written many migrations and never had such issues. A reasonably popular app I'm working on has so far 25 migration steps [0] and I've never encountered something like a new schema being different from an updated one. I'd expect whatever mistake was made to get to that point could be made in any other RDBMS. 0: https://github.com/laurent22/joplin/blob/805a5399b5288b4a28273e9b5c407af868e7abb3/ReactNativeClient/lib/joplin-database.js#L323 https://github.com/laurent22/joplin/blob/805a5399b5288b4a282...
- cjfd 7y ago
  After writing the above comment, memory comes back a bit. The problem is that sqlite does not support renaming a column directly. One can find procedures for that on the internet. E.g., https://tableplus.com/blog/2018/04/sqlite-rename-a-column.html https://tableplus.com/blog/2018/04/sqlite-rename-a-column.ht.... However, we also wanted to be able to rename columns where there are foreign key references, for instance references to the table of which a column was renamend. A generic function, not in sql, but in the programming language of our project, in this case c++, was written for this purpose, something like rename_column(table_name, old_column_name, new_column_name). Needless to say the first version of this function was not quite sufficient leading to the problems that I mentioned. In the end I had to improve this function. I remember at the time I was quite pleased with myself figuring out how to do this without temporarily switching off foreign key checking which the first version of the script had.

zwsxedcrfvtgb 7y ago

could you go a little more in depth about your setup? do you mean you add the sqlite library, load the file and then use sqlite to read/write data to a database instead of a text or bin file? I like it... I do simulations in physics and we're really not familiar with these types of things.

tiborsaas 7y ago
I do the same to manage a few thousand records with Node.js. I like that I don't depend on a complex DB engine. It's just one file, very compact. I even commit the file to GIT :)
punnerud 7y ago
Probably more than 50% of all the Android apps I have decompiled use SQLite for data storage. Some extra time to learn SQL but it will save you a lot of time in the long run. Another benefit compared to ASIC is that you get: File compression, easy to access random parts of the file and fast (random) inserts.

tobias3 7y ago

As a result of the paper linked in this article sqlite has a new setting PRAGMA synchronous=EXTRA; It is not the default (default is usually FULL).

burmecia 7y ago

Totally agree. To make file system crash-proof you really need an ACID compatible underlying storage layer or implement it inside the file system itself. In fact, file system and database are quite similar in case of atomicity and consistency. If an embedded ACID component is too heavy, utilizing a reliable database as storage, for example sqlite, might be a good idea. That’s exactly what I did in ZboxFS (https://github.com/zboxfs/zbox https://github.com/zboxfs/zbox) which can use sqlite as a underlying storage to achieve ACID compatible behaviours.