3 ms·

The filter[0] attribute with "smudge" and "clean" would be a better solution for storing SQLite into git. [0] - https://git-scm.com/docs/gitattributes#_filter

The filter[0] attribute with "smudge" and "clean" would be a better solution for storing SQLite into git. [0] - https://git-scm.com/docs/gitattributes#_filter https://git-scm.com/docs/gitattributes#_filter

michaelmior 3y ago
This is what I was expecting. Store the dump in git but then have the binary in the working directory.
massysett 3y ago
Why is this better?
- sshine 3y ago
  Maintaining a big, binary blob will make the git history grow in proportion to the size of the binary blob, not the size of the logical/abstract diff, regardless of how comprehensible the diff looks. To minimize the disk space to only the changes, you want to store the changes and not the complete, binary before/after images. By filtering, you reduce the stored object to the difference.
- rjmunro 3y ago
  You won't be storing the sqlite file in git. So you won't be storing indexes or un-vacuumed data in git. You also won't be storing a database with some of it's rows locked or anything like that. Git will be able to compress the text better, and compress between versions better. Merging and resolving conflicts would have a chance of working.
chrismorgan 3y ago
But the key disadvantage is that now getting started with the repository requires more effort: you must have sqlite3 installed locally, and you must apply certain config values manually (and “remember to run this helper script” or “the first time you try running it it’ll⸺” are still manual). (This is for security reasons—you don’t want commands like clone and diff to be able to execute arbitrary code without your knowledge. Hence the attributes/config split.) Otherwise, you’ll find that your .sqlite file is SQL instead of a SQLite database, and if you try running stuff while it’s in that condition you’ll get “file is not a database” errors from SQLite.
- redleader55 3y ago
  You are right, there is a bootstrap cost and potential security implications. In general, also, .gitattributes is pretty evil, but: 1. don't store binary files in Git 2. don't run programs that keep a git file open for too long, otherwise clowntown. For example, you run git pull while GnuCash keeps the file open and then you loose all your updates when you save (also a problem with the code below, but fixable). The correct solution to this is running the "smudge" and "clean" operations as a backup and restore operation, outside the repo, when starting and closing GnuCash, in this case: ``` #!/bin/bash cat $1.sql > $1.sqlite3 gnucash $1.sqlite3 echo ".dump" | sqlite3 $1.sqlite3 > $1.sql # optionally: # git add $1.sql # git commit -m "Update $(date) $1.sql" ```
  - chrisweekly 3y ago
    ".gitattributes is pretty evil" Could you please expand on this? I've been working with git for many years, and IME .gitattributes files are useful, sometimes essential, as the standard way to manage repo-level settings -- especially helpful for larger projects with heterogenous contributors. What am I missing?
    - redleader55 3y ago
      In general, I believe in not giving people enough rope to hang themselves, but hiding the rope under a lock (Porcelain commands) that you open by entering complicated commands which weird syntax (Plumbing commands). .gitattributes are "evil", because they "lie" about what's in the repo. You could use .gitattributes for simple things like line endings on different platforms (Windows vs Unix), but you could also use it for more complex flows where the file you have locally and the file you store in the repo are completely different - filter feature. This would be "magic"/hidden/implicit, rather than explicit for the user who won't understand why github.com shows something and their local filesystem shows something else.
bambax 3y ago
Ah yes, I made a similar observation here https://news.ycombinator.com/item?id=38111408 https://news.ycombinator.com/item?id=38111408 It's better to store text in git IMHO.
- rewmie 3y ago
  > It's better to store text in git IMHO. I think the bias towards text files in Git is only a reflection of Git configuring its defaults to handle text files, such as the diff tool and also file type. If you add a custom file type and configure it to not handle it as text, which includes specifying git attributes to prevent it to update things like newline characters, then Git works just as well. https://git-scm.com/docs/gitattributes https://git-scm.com/docs/gitattributes
  - avar 3y ago
    It doesn't, what you see when you display the content is only 1/2 of it, the other half is how the system is able to store the content through delta-compression. Here "text" or "binary" is a bit of a red herring, what's really important is whether it's diff-able. But in 99% of cases "binary" and "text" are synonyms for "un-diffable" and "diffable".
  - aseipp 3y ago
    Diffs are purely a UI mechanic of Git. Computed on demand, but they don't really exist at the data layer, where blobs are stored. That's the real spot where all the problems are. Binaries can't realistically be packed and compressed most of the time in Git's design, so the net result is there's basically a copy of every version of that file in your repo, forever. That 10MiB binary got modified 5 times? That's 50MiB of bloat that is stuck in your git repository, forever, until the end of time. It doesn't matter if you delete it. Space inefficiency like this is a core issue that compounds many problems in practice. Then there's the fact a lot of the algorithms start falling over. For example, Git rebases touch the disk multiple times (patch application, update the index) for every commit in the series you rebase. This gets very expensive when the working tree is filled with tons of blobs and the repository is large (many files, even 99.9% small ones) and the series is long. I sort of doubt most programmers want to write/rewrite 50TiB of binary files in their Git repo every day. Some certainly do I guess, but I suspect most of us just want to shove a few dozen reference PDFs and a CAD file or two into their repository, maybe some zip files or .so files that get auto-updated, and use their basic workflows without things having performance cliffs. Some will want to store game assets, which is harder. But today it's mostly unsatisfying for anything but the smallest and most glacially-moving binary files.
foobarbecue 3y ago
Is there a web interface like GitHub that supports this? I wanted to use it with GitHub Enterprise a few years ago and found that it meant I couldn't see diffs properly in GitHub.
JNRowe 3y ago
You have to be careful with this, as using .dump as your filter will not necessarily get you an equivalent database on restore. For example, .dump will not include database metadata such as application_id¹ or user_version²(perhaps other things too but these are the ones that have bitten me before). The custom diff driver in the OP will not reflect changes to those values either obviously, but at least it won't lose them in the actual database file stored in the git repository. ¹ https://sqlite.org/pragma.html#pragma_application_id https://sqlite.org/pragma.html#pragma_application_id ² https://sqlite.org/pragma.html#pragma_user_version https://sqlite.org/pragma.html#pragma_user_version
- OskarS 3y ago
  Is there no capability in SQLite to dump these things as well? Seems like a pretty big thing missing if that's the case, a thing I would expect SQLite to support pretty well.
  - JNRowe 3y ago
    Not as far as I'm aware. You can query much of that stuff using the CLI with the .dbinfo command, but support for that appears to be dependent on build options so may not be available. Another option might be to provide multiple commands("sqlite my.db 'PRAGMA user_version' .dump"), but you'd have to remember to update scripts if you suddenly started using other things in your app too. I'll note that the metadata is handled correctly by the CLI's .backup command or you can "VACUUM INTO <somewhere>"¹, so if you're just attempting to use .dump for the sake of backup there are probably better options available anyway. But yeah, I was surprised when I chased down a bug that was caused by a missing application_id following a restore from .dump output. I did a search at the time and noticed it had been reported to the sqlite folks on their forums a few times, so presumably I'm missing the reasoning behind not including it. ¹ https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html
    - o11c 3y ago
      Not sure if this actually does more, but remember you can do: sqlite> select * from pragma_application_id(); 0 sqlite> select * from pragma_user_version(); 0

michaelmior 3y ago

This is what I was expecting. Store the dump in git but then have the binary in the working directory.

massysett 3y ago

Why is this better?

sshine 3y ago
Maintaining a big, binary blob will make the git history grow in proportion to the size of the binary blob, not the size of the logical/abstract diff, regardless of how comprehensible the diff looks. To minimize the disk space to only the changes, you want to store the changes and not the complete, binary before/after images. By filtering, you reduce the stored object to the difference.
rjmunro 3y ago
You won't be storing the sqlite file in git. So you won't be storing indexes or un-vacuumed data in git. You also won't be storing a database with some of it's rows locked or anything like that. Git will be able to compress the text better, and compress between versions better. Merging and resolving conflicts would have a chance of working.

chrismorgan 3y ago

But the key disadvantage is that now getting started with the repository requires more effort: you must have sqlite3 installed locally, and you must apply certain config values manually (and “remember to run this helper script” or “the first time you try running it it’ll⸺” are still manual). (This is for security reasons—you don’t want commands like clone and diff to be able to execute arbitrary code without your knowledge. Hence the attributes/config split.) Otherwise, you’ll find that your .sqlite file is SQL instead of a SQLite database, and if you try running stuff while it’s in that condition you’ll get “file is not a database” errors from SQLite.

redleader55 3y ago
You are right, there is a bootstrap cost and potential security implications. In general, also, .gitattributes is pretty evil, but: 1. don't store binary files in Git 2. don't run programs that keep a git file open for too long, otherwise clowntown. For example, you run git pull while GnuCash keeps the file open and then you loose all your updates when you save (also a problem with the code below, but fixable). The correct solution to this is running the "smudge" and "clean" operations as a backup and restore operation, outside the repo, when starting and closing GnuCash, in this case: ``` #!/bin/bash cat $1.sql > $1.sqlite3 gnucash $1.sqlite3 echo ".dump" | sqlite3 $1.sqlite3 > $1.sql # optionally: # git add $1.sql # git commit -m "Update $(date) $1.sql" ```
- chrisweekly 3y ago
  ".gitattributes is pretty evil" Could you please expand on this? I've been working with git for many years, and IME .gitattributes files are useful, sometimes essential, as the standard way to manage repo-level settings -- especially helpful for larger projects with heterogenous contributors. What am I missing?
  - redleader55 3y ago
    In general, I believe in not giving people enough rope to hang themselves, but hiding the rope under a lock (Porcelain commands) that you open by entering complicated commands which weird syntax (Plumbing commands). .gitattributes are "evil", because they "lie" about what's in the repo. You could use .gitattributes for simple things like line endings on different platforms (Windows vs Unix), but you could also use it for more complex flows where the file you have locally and the file you store in the repo are completely different - filter feature. This would be "magic"/hidden/implicit, rather than explicit for the user who won't understand why github.com shows something and their local filesystem shows something else.

bambax 3y ago

Ah yes, I made a similar observation here https://news.ycombinator.com/item?id=38111408 https://news.ycombinator.com/item?id=38111408 It's better to store text in git IMHO.

rewmie 3y ago
> It's better to store text in git IMHO. I think the bias towards text files in Git is only a reflection of Git configuring its defaults to handle text files, such as the diff tool and also file type. If you add a custom file type and configure it to not handle it as text, which includes specifying git attributes to prevent it to update things like newline characters, then Git works just as well. https://git-scm.com/docs/gitattributes https://git-scm.com/docs/gitattributes
- avar 3y ago
  It doesn't, what you see when you display the content is only 1/2 of it, the other half is how the system is able to store the content through delta-compression. Here "text" or "binary" is a bit of a red herring, what's really important is whether it's diff-able. But in 99% of cases "binary" and "text" are synonyms for "un-diffable" and "diffable".
- aseipp 3y ago
  Diffs are purely a UI mechanic of Git. Computed on demand, but they don't really exist at the data layer, where blobs are stored. That's the real spot where all the problems are. Binaries can't realistically be packed and compressed most of the time in Git's design, so the net result is there's basically a copy of every version of that file in your repo, forever. That 10MiB binary got modified 5 times? That's 50MiB of bloat that is stuck in your git repository, forever, until the end of time. It doesn't matter if you delete it. Space inefficiency like this is a core issue that compounds many problems in practice. Then there's the fact a lot of the algorithms start falling over. For example, Git rebases touch the disk multiple times (patch application, update the index) for every commit in the series you rebase. This gets very expensive when the working tree is filled with tons of blobs and the repository is large (many files, even 99.9% small ones) and the series is long. I sort of doubt most programmers want to write/rewrite 50TiB of binary files in their Git repo every day. Some certainly do I guess, but I suspect most of us just want to shove a few dozen reference PDFs and a CAD file or two into their repository, maybe some zip files or .so files that get auto-updated, and use their basic workflows without things having performance cliffs. Some will want to store game assets, which is harder. But today it's mostly unsatisfying for anything but the smallest and most glacially-moving binary files.

foobarbecue 3y ago

Is there a web interface like GitHub that supports this? I wanted to use it with GitHub Enterprise a few years ago and found that it meant I couldn't see diffs properly in GitHub.

JNRowe 3y ago

You have to be careful with this, as using .dump as your filter will not necessarily get you an equivalent database on restore. For example, .dump will not include database metadata such as application_id¹ or user_version²(perhaps other things too but these are the ones that have bitten me before). The custom diff driver in the OP will not reflect changes to those values either obviously, but at least it won't lose them in the actual database file stored in the git repository. ¹ https://sqlite.org/pragma.html#pragma_application_id https://sqlite.org/pragma.html#pragma_application_id ² https://sqlite.org/pragma.html#pragma_user_version https://sqlite.org/pragma.html#pragma_user_version

OskarS 3y ago
Is there no capability in SQLite to dump these things as well? Seems like a pretty big thing missing if that's the case, a thing I would expect SQLite to support pretty well.
- JNRowe 3y ago
  Not as far as I'm aware. You can query much of that stuff using the CLI with the .dbinfo command, but support for that appears to be dependent on build options so may not be available. Another option might be to provide multiple commands("sqlite my.db 'PRAGMA user_version' .dump"), but you'd have to remember to update scripts if you suddenly started using other things in your app too. I'll note that the metadata is handled correctly by the CLI's .backup command or you can "VACUUM INTO <somewhere>"¹, so if you're just attempting to use .dump for the sake of backup there are probably better options available anyway. But yeah, I was surprised when I chased down a bug that was caused by a missing application_id following a restore from .dump output. I did a search at the time and noticed it had been reported to the sqlite folks on their forums a few times, so presumably I'm missing the reasoning behind not including it. ¹ https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html
  - o11c 3y ago
    Not sure if this actually does more, but remember you can do: sqlite> select * from pragma_application_id(); 0 sqlite> select * from pragma_user_version(); 0