8 ms·

I'm currently working on an application where I use SQLite as the file format. I want to keep a usual workflow for users where you can make edit to your documen

by p4bl0 3y ago

I'm currently working on an application where I use SQLite as the file format. I want to keep a usual workflow for users where you can make edit to your document and it only changes the file when you save it. So to open a file I copy it into the :memory: database [1], then the user can do whatever manipulation they want and I can directly make the change in the database I don't need to have a model of the document other than its database format. And to save the document I VACUUM [2] it back to the database file. It works quite well, at least for reasonably sized file (which is always the case for my app) :). [1] https://www.sqlite.org/inmemorydb.html https://www.sqlite.org/inmemorydb.html [2] https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html

nyanpasu64 3y ago
> The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space. > The VACUUM INTO command works the same way except that it uses the file named on the INTO clause in place of the temporary database and omits the step of copying the vacuumed database back over top of the original database. Do you use VACUUM (uses a write-ahead log to survive power-off) or VACUUM INTO (as far as I can tell, it doesn't survive power-off during writing, and might corrupt the existing file contents if the filename already exists)?
- justsomehnguy 3y ago
  >> The file named by the INTO clause must not previously exist, or else it must be an empty file, or the VACUUM INTO command will fail with an error. EDIT: there is no difference between VACUUM/VACUUM INTO - they both write to a new file (COW) it's just VACUUM [NOT INTO] does mv temp.sqlite originalfile.sqlite after that, while VACUUM INTO does not.
nextaccountic 3y ago
This means that like a regular app, you lose data if the app crashes or there is a power loss. It's much better to save after each operation in a temporary place (probably in ~/.local/share/application/yourapp, using XDG directories), and when the user clicks save, just copy the file into the desired location. That way, if there is a power loss and you reopen the app, it opens right back where it was doing (losing maybe he last few seconds of changes, but not all unsaved data)
- avereveard 3y ago
  Good ole .filename.swp
- Someone 3y ago
  > and when the user clicks save, just copy the file into the desired location. To be perfectly safe, you want to rename it, not copy it. If there’s a power loss during copying, you may endcup with corrupted data. Renaming is, to coin a phrase, “more atomic” than copying (on Linux, the OS says it is atomic. ISO C says it, too, but POSIX doesn’t (https://pubs.opengroup.org/onlinepubs/000095399/functions/rename.html https://pubs.opengroup.org/onlinepubs/000095399/functions/re...: “This rename() function is equivalent for regular files to that defined by the ISO C standard. Its inclusion here expands that definition to include actions on directories and specifies behavior when the new parameter names a file that already exists. That specification requires that the action of the function be atomic”) Also, filesystems may have bugs, hardware may lie about syncing to disk, and network shares can be finicky. Doing this properly isn’t as easy as one would think. You’ve to make sure to sync the file to be written and you’ll have to handle the case where the save location is on a different file system than your temporary file. If so, you’ll have to create a copy on that file system first. I think many tools do not check whether they need to work cross filesystem and just write their scratch files to the save directory with a different name and then rename them. Of course, that means you always need twice the disk space on the target disk to do a save. That used to be a problem almost everywhere, but nowadays mostly is restricted to embedded systems and USB sticks. In this case, however, SQLite will do a lot for you, and probably better than you would do it. It claims (https://www.sqlite.org/atomiccommit.html#_multi_file_commit https://www.sqlite.org/atomiccommit.html#_multi_file_commit): “SQLite allows a single database connection to talk to two or more database files simultaneously through the use of the ATTACH DATABASE command. When multiple database files are modified within a single transaction, all files are updated atomically. In other words, either all of the database files are updated or else none of them are. Achieving an atomic commit across multiple database files is more complex that doing so for a single file. This section describes how SQLite works that bit of magic.” However, about VACUUM INTO, it says (https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html): “The VACUUM INTO command is transactional in the sense that the generated output database is a consistent snapshot of the original database. However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt. Also, SQLite does not invoke fsync() or FlushFileBuffers() on the generated database to ensure that it has reached non-volatile storage before completing.” So, I don’t think doing “VACUUM INTO” is sufficient to guarantee that you get a good copy of your data on disk.
rewmie 3y ago
> I can directly make the change in the database I don't need to have a model of the document other than its database format. I don't get your point. Are you saying that you don't need to have a model of the document other than the model of the document? What's the nuance I'm missing?
- socksy 3y ago
  I suppose this is in the context where you will be syncing up the changes to a backend server which will also be storing the document in an SQL database. Normally, you might expect that data format on the client to be JSON/XML/something else, and you'd need to maintain logic that marshalls the document representation SQL <-> In-memory representation <-> Disk format. With SQL on the client, in theory you only now need to maintain SQL <-> In-memory representation Obviously I'm skirting over the format you would use to send either entire documents or partial updates of documents over the wire.
- torstenvl 3y ago
  An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk. Programmers often have to make software design decisions around how to represent a file in memory in order to manipulate it. For example, if I'm writing an HTML editor, should I mostly treat it like a text file (maybe a gap buffer) with syntax highlighting and auto indentation as an afterthought? Or should I maybe load the whole thing into a tree? What are the robustness and performance characteristics of each? The commenter above was saying that using SQLite made that decision easy. He could keep traditional (or "atavistic" per the commenter upthread, depending on your perspective) load/save semantics while also making the data model easy to work with.
  - rewmie 3y ago
    > An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk. The whole point of my remark is that the domain model and the export document format are two entirely separate things.
remram 3y ago
Why not use a transaction?
- p4bl0 3y ago
  A single transaction for the whole user session? That seems a bad idea. Also I'm not sure you can do transactions during another transaction, and I need them for other purpose, i.e., for what they were designed to do (doing changes in multiple tables that need to stay consistent).
  - subleq 3y ago
    It’s exactly what transactions are for. A nested transaction is called a savepoint, which sqlite does support.
  - deleted 3y ago
    [deleted]
rakoo 3y ago
Why do you use a secondary, volatile database ? Performance-wise you won't gain a lot more (we're talking about a user editing a file, so not even 1 write per second). A proposal: write directly, and automatically in the database. No more Save button. There are multiple advantages: - the system is crash-resistant. I like taking the approach of CouchDB where the only correct way to close the system is to crash it. That way a crash is an expected situation that you actually account for, not a special case that you might forget - there is only one database. Less code, fewer bugs. - it is safe. A write to SQLite works or doesn't work, there is no in-between. As said in the VACUUM doc you point to: "However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt" - it is how SQLite was intended to work. And because of that, you won't have to think about it for the lifetime of SQLite
- knome 3y ago
  a save button is still good, as it allows you to keep specific checkpoints. but the save button could simply tag specific save points in a larger table. if the format can roll up changes to compress them, they also indicate where which variants need to be kept indefinitely.
  - rakoo 3y ago
    In this view the save action is more like a commit, where the user manually checkpoints and also offers a simple description of why this is an important point. But in my view all intermediary points also need to be saved, because the user might have forgotten to explicitly checkpoint, and might still want some undo/redo capability that is more granular than just checkpoints.
  - theamk 3y ago
    Auto-save is nice, but I think it works much better when it's a separate file. This way "main" document file (which might be checked in to git, or shared via dropbox or read by some document) only contains nice, clean, saved version. And yet if your computer crashes for whatever reason, the data is still not lost and can be trivially recovered.
ilyt 3y ago
We used something similar (DB doing caching run in memory but saved periodically on disk) but with backup API https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
liuliu 3y ago
Maybe simpler? When open the DB, change it to WAL mode, turn off the automatic checkpoint https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint When user saves, you just checkpointing the file, merging it back into the main database.
deleted 3y ago
[deleted]
hot_gril 3y ago
Btw, Apple's CoreData, commonly used by iPhone and Mac apps, uses SQLite by default. That part works fine, so you can study it if you'd like and ignore all the bad parts built on top (ORM, MVC framework, etc).

by p4bl0 3y ago

nyanpasu64 3y ago

> The VACUUM command works by copying the contents of the database into a temporary database file and then overwriting the original with the contents of the temporary file. When overwriting the original, a rollback journal or write-ahead log WAL file is used just as it would be for any other database transaction. This means that when VACUUMing a database, as much as twice the size of the original database file is required in free disk space. > The VACUUM INTO command works the same way except that it uses the file named on the INTO clause in place of the temporary database and omits the step of copying the vacuumed database back over top of the original database. Do you use VACUUM (uses a write-ahead log to survive power-off) or VACUUM INTO (as far as I can tell, it doesn't survive power-off during writing, and might corrupt the existing file contents if the filename already exists)?

justsomehnguy 3y ago
>> The file named by the INTO clause must not previously exist, or else it must be an empty file, or the VACUUM INTO command will fail with an error. EDIT: there is no difference between VACUUM/VACUUM INTO - they both write to a new file (COW) it's just VACUUM [NOT INTO] does mv temp.sqlite originalfile.sqlite after that, while VACUUM INTO does not.

nextaccountic 3y ago

This means that like a regular app, you lose data if the app crashes or there is a power loss. It's much better to save after each operation in a temporary place (probably in ~/.local/share/application/yourapp, using XDG directories), and when the user clicks save, just copy the file into the desired location. That way, if there is a power loss and you reopen the app, it opens right back where it was doing (losing maybe he last few seconds of changes, but not all unsaved data)

avereveard 3y ago
Good ole .filename.swp
Someone 3y ago
> and when the user clicks save, just copy the file into the desired location. To be perfectly safe, you want to rename it, not copy it. If there’s a power loss during copying, you may endcup with corrupted data. Renaming is, to coin a phrase, “more atomic” than copying (on Linux, the OS says it is atomic. ISO C says it, too, but POSIX doesn’t (https://pubs.opengroup.org/onlinepubs/000095399/functions/rename.html https://pubs.opengroup.org/onlinepubs/000095399/functions/re...: “This rename() function is equivalent for regular files to that defined by the ISO C standard. Its inclusion here expands that definition to include actions on directories and specifies behavior when the new parameter names a file that already exists. That specification requires that the action of the function be atomic”) Also, filesystems may have bugs, hardware may lie about syncing to disk, and network shares can be finicky. Doing this properly isn’t as easy as one would think. You’ve to make sure to sync the file to be written and you’ll have to handle the case where the save location is on a different file system than your temporary file. If so, you’ll have to create a copy on that file system first. I think many tools do not check whether they need to work cross filesystem and just write their scratch files to the save directory with a different name and then rename them. Of course, that means you always need twice the disk space on the target disk to do a save. That used to be a problem almost everywhere, but nowadays mostly is restricted to embedded systems and USB sticks. In this case, however, SQLite will do a lot for you, and probably better than you would do it. It claims (https://www.sqlite.org/atomiccommit.html#_multi_file_commit https://www.sqlite.org/atomiccommit.html#_multi_file_commit): “SQLite allows a single database connection to talk to two or more database files simultaneously through the use of the ATTACH DATABASE command. When multiple database files are modified within a single transaction, all files are updated atomically. In other words, either all of the database files are updated or else none of them are. Achieving an atomic commit across multiple database files is more complex that doing so for a single file. This section describes how SQLite works that bit of magic.” However, about VACUUM INTO, it says (https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html): “The VACUUM INTO command is transactional in the sense that the generated output database is a consistent snapshot of the original database. However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt. Also, SQLite does not invoke fsync() or FlushFileBuffers() on the generated database to ensure that it has reached non-volatile storage before completing.” So, I don’t think doing “VACUUM INTO” is sufficient to guarantee that you get a good copy of your data on disk.

rewmie 3y ago

> I can directly make the change in the database I don't need to have a model of the document other than its database format. I don't get your point. Are you saying that you don't need to have a model of the document other than the model of the document? What's the nuance I'm missing?

socksy 3y ago
I suppose this is in the context where you will be syncing up the changes to a backend server which will also be storing the document in an SQL database. Normally, you might expect that data format on the client to be JSON/XML/something else, and you'd need to maintain logic that marshalls the document representation SQL <-> In-memory representation <-> Disk format. With SQL on the client, in theory you only now need to maintain SQL <-> In-memory representation Obviously I'm skirting over the format you would use to send either entire documents or partial updates of documents over the wire.
torstenvl 3y ago
An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk. Programmers often have to make software design decisions around how to represent a file in memory in order to manipulate it. For example, if I'm writing an HTML editor, should I mostly treat it like a text file (maybe a gap buffer) with syntax highlighting and auto indentation as an afterthought? Or should I maybe load the whole thing into a tree? What are the robustness and performance characteristics of each? The commenter above was saying that using SQLite made that decision easy. He could keep traditional (or "atavistic" per the commenter upthread, depending on your perspective) load/save semantics while also making the data model easy to work with.
- rewmie 3y ago
  > An in-memory data model often differs from the serialized data as it exists on disk. For example, emacs uses a gap buffer for text files; but it outputs plain linear text to disk. The whole point of my remark is that the domain model and the export document format are two entirely separate things.

remram 3y ago

Why not use a transaction?

p4bl0 3y ago
A single transaction for the whole user session? That seems a bad idea. Also I'm not sure you can do transactions during another transaction, and I need them for other purpose, i.e., for what they were designed to do (doing changes in multiple tables that need to stay consistent).
- subleq 3y ago
  It’s exactly what transactions are for. A nested transaction is called a savepoint, which sqlite does support.
- deleted 3y ago
  [deleted]

rakoo 3y ago

Why do you use a secondary, volatile database ? Performance-wise you won't gain a lot more (we're talking about a user editing a file, so not even 1 write per second). A proposal: write directly, and automatically in the database. No more Save button. There are multiple advantages: - the system is crash-resistant. I like taking the approach of CouchDB where the only correct way to close the system is to crash it. That way a crash is an expected situation that you actually account for, not a special case that you might forget - there is only one database. Less code, fewer bugs. - it is safe. A write to SQLite works or doesn't work, there is no in-between. As said in the VACUUM doc you point to: "However, if the VACUUM INTO command is interrupted by an unplanned shutdown or power lose, then the generated output database might be incomplete and corrupt" - it is how SQLite was intended to work. And because of that, you won't have to think about it for the lifetime of SQLite

knome 3y ago
a save button is still good, as it allows you to keep specific checkpoints. but the save button could simply tag specific save points in a larger table. if the format can roll up changes to compress them, they also indicate where which variants need to be kept indefinitely.
- rakoo 3y ago
  In this view the save action is more like a commit, where the user manually checkpoints and also offers a simple description of why this is an important point. But in my view all intermediary points also need to be saved, because the user might have forgotten to explicitly checkpoint, and might still want some undo/redo capability that is more granular than just checkpoints.
- theamk 3y ago
  Auto-save is nice, but I think it works much better when it's a separate file. This way "main" document file (which might be checked in to git, or shared via dropbox or read by some document) only contains nice, clean, saved version. And yet if your computer crashes for whatever reason, the data is still not lost and can be trivially recovered.

ilyt 3y ago

We used something similar (DB doing caching run in memory but saved periodically on disk) but with backup API https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html

liuliu 3y ago

Maybe simpler? When open the DB, change it to WAL mode, turn off the automatic checkpoint https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint https://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint When user saves, you just checkpointing the file, merging it back into the main database.

deleted 3y ago

[deleted]

hot_gril 3y ago

Btw, Apple's CoreData, commonly used by iPhone and Mac apps, uses SQLite by default. That part works fine, so you can study it if you'd like and ignore all the bad parts built on top (ORM, MVC framework, etc).