Hacker News Reader

Comment by redleader55 | Hacker News Reader

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

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.

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"

```

".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?

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.

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_user_version

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.

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

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

Yeah, you could use that as in "sqlite3 t.db 'SELECT format(…) FROM pragma_…' .dump" to create a file that sets application_id/$others and then dumps the tables, which would make restoration from the dump file work in more cases.

It is just another thing to be aware of if you're trying to store your database in your git repository with a filter, and want it to roundtrip correctly.

Ah yes, I made a similar observation here https://news.ycombinator.com/item?id=38111408

It's better to store text in git IMHO.

> 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

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".

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.

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

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.

Why is this better?

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.

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.

Reply on news.ycombinator.com