Can anyone suggest a good way (or tool) to version control a SQLite database (schema only)? I'm trying to version control a SQLite database and the only option I can find involves using GIT to version control the whole file, but I'm not interested in the data at this point, just the schema changes.
任何人都可以建议一个好的方法（或工具）来控制SQLite数据库（仅限架构）吗？我正在尝试对SQLite数据库进行版本控制，我可以找到的唯一选项是使用GIT对版本控制整个文件，但此时我对数据不感兴趣，只是模式更改。
Any suggestions?
有什么建议么？
Thanks :-)
谢谢 ：-）
3 个解决方案
#1
7
I have a two fold answer. If your sqlite is light enough and infrequently updated, you can add it into the repository without too many repercussions/issues.
我有两个答案。如果您的sqlite足够轻且不经常更新，您可以将其添加到存储库中，而不会产生太多的影响/问题。
But readablity goes down for diffs since it is stored as a binary.
但是可读性因差异而存在，因为它存储为二进制文件。
sqlite3 git diff
Here is how to get git to show you diffs nicely:
以下是如何让git很好地展示你的差异：
https://gist.github.com/peteristhegreat/a028bc3b588baaea09ff67f405af2909
https://gist.github.com/peteristhegreat/a028bc3b588baaea09ff67f405af2909
git config diff.sqlite3.textconv 'sqlite3 $1 .dump'
echo '*.db diff=sqlite3' >> $(git rev-parse --show-toplevel)/.gitattributes
Now when your sqlite db file changes you can see the change with git diff.
现在，当您的sqlite db文件发生更改时，您可以通过git diff查看更改。
If you wanted to only see the diff of the schema, you just change .dump to .schema and it should only do the create calls and skip the inserts.
如果您只想查看模式的差异，只需将.dump更改为.schema，它只应执行create调用并跳过插入。
sqlite3 conversion in and out of the repository with clean/smudge
If you want your db file to get pushed into the repository as sql instead of as a db, you can use the clean/smudge mechanisms in git.
如果您希望将db文件作为sql而不是作为db推送到存储库中，则可以使用git中的clean / smudge机制。
https://git-scm.com/book/en/v2/Customizing-Git-Git-Attributes#Keyword-Expansion
https://git-scm.com/book/en/v2/Customizing-Git-Git-Attributes#Keyword-Expansion
https://github.com/gilesbowkett/git-smudge-and-clean
https://github.com/gilesbowkett/git-smudge-and-clean
I haven't tried it yet, but basically whenever you run across a file that is a db, git commits the stripped down version of the file (as sql commands) using the sqlite3 $1 .schema export. And then when you checkout that file from your database, it gets converted back to a db using cat $1 | sqlite3.
我还没有尝试过，但基本上每当你遇到一个db文件时，git就会使用sqlite3 $ 1 .schema export提交文件的精简版本（作为sql命令）。然后当您从数据库中检出该文件时，它将使用cat $ 1 |转换回数据库sqlite3的。
sqlite3 always keep the newest file
Right way of tracking a sqlite3 binary file in git?
在git中跟踪sqlite3二进制文件的正确方法？
.gitattributes
mysqlite3.db merge=keepTheir
Hope that helps.
希望有所帮助。
#2
3
There is a command line utility documented here here on SQLite.org called sqldiff.exe. It provides various options including comparing schema. To configure git to use sqldiff instead of the built in diff tool check out this discussion: How do I view 'git diff' output with a visual diff program?. Unfortunately it looks like its not a trivial task.
SQLite.org上有一个名为sqldiff.exe的命令行实用程序。它提供了各种选项，包括比较模式。要配置git以使用sqldiff而不是内置的diff工具，请查看以下讨论：如何使用visual diff程序查看'git diff'输出？不幸的是，它看起来不是一项微不足道的任务。
Edit: It looks like the only way to get the sqldiff tool is to download the full source (all the way at the bottom of the downloads page) and compile it.
编辑：看起来获取sqldiff工具的唯一方法是下载完整的源代码（一直到下载页面的底部）并编译它。
#3
1
From the sqlite documentation, you can extract the schema information from the sqlite_master hidden table. (https://www.sqlite.org/fileformat2.html#sqlite_master)
从sqlite文档中，您可以从sqlite_master隐藏表中提取架构信息。 （https://www.sqlite.org/fileformat2.html#sqlite_master）
You can save this content in a text file in GIT, this should give you a way to track changes to the schema.
您可以将此内容保存在GIT中的文本文件中，这样可以为您提供跟踪架构更改的方法。
#1
7
I have a two fold answer. If your sqlite is light enough and infrequently updated, you can add it into the repository without too many repercussions/issues.
我有两个答案。如果您的sqlite足够轻且不经常更新，您可以将其添加到存储库中，而不会产生太多的影响/问题。
But readablity goes down for diffs since it is stored as a binary.
但是可读性因差异而存在，因为它存储为二进制文件。
sqlite3 git diff
Here is how to get git to show you diffs nicely:
以下是如何让git很好地展示你的差异：
https://gist.github.com/peteristhegreat/a028bc3b588baaea09ff67f405af2909
https://gist.github.com/peteristhegreat/a028bc3b588baaea09ff67f405af2909
git config diff.sqlite3.textconv 'sqlite3 $1 .dump'
echo '*.db diff=sqlite3' >> $(git rev-parse --show-toplevel)/.gitattributes
Now when your sqlite db file changes you can see the change with git diff.
现在，当您的sqlite db文件发生更改时，您可以通过git diff查看更改。
If you wanted to only see the diff of the schema, you just change .dump to .schema and it should only do the create calls and skip the inserts.
如果您只想查看模式的差异，只需将.dump更改为.schema，它只应执行create调用并跳过插入。
sqlite3 conversion in and out of the repository with clean/smudge
If you want your db file to get pushed into the repository as sql instead of as a db, you can use the clean/smudge mechanisms in git.
如果您希望将db文件作为sql而不是作为db推送到存储库中，则可以使用git中的clean / smudge机制。
https://git-scm.com/book/en/v2/Customizing-Git-Git-Attributes#Keyword-Expansion
https://git-scm.com/book/en/v2/Customizing-Git-Git-Attributes#Keyword-Expansion
https://github.com/gilesbowkett/git-smudge-and-clean
https://github.com/gilesbowkett/git-smudge-and-clean
I haven't tried it yet, but basically whenever you run across a file that is a db, git commits the stripped down version of the file (as sql commands) using the sqlite3 $1 .schema export. And then when you checkout that file from your database, it gets converted back to a db using cat $1 | sqlite3.
我还没有尝试过，但基本上每当你遇到一个db文件时，git就会使用sqlite3 $ 1 .schema export提交文件的精简版本（作为sql命令）。然后当您从数据库中检出该文件时，它将使用cat $ 1 |转换回数据库sqlite3的。
sqlite3 always keep the newest file
Right way of tracking a sqlite3 binary file in git?
在git中跟踪sqlite3二进制文件的正确方法？
.gitattributes
mysqlite3.db merge=keepTheir
Hope that helps.
希望有所帮助。
#2
3
There is a command line utility documented here here on SQLite.org called sqldiff.exe. It provides various options including comparing schema. To configure git to use sqldiff instead of the built in diff tool check out this discussion: How do I view 'git diff' output with a visual diff program?. Unfortunately it looks like its not a trivial task.
SQLite.org上有一个名为sqldiff.exe的命令行实用程序。它提供了各种选项，包括比较模式。要配置git以使用sqldiff而不是内置的diff工具，请查看以下讨论：如何使用visual diff程序查看'git diff'输出？不幸的是，它看起来不是一项微不足道的任务。
Edit: It looks like the only way to get the sqldiff tool is to download the full source (all the way at the bottom of the downloads page) and compile it.
编辑：看起来获取sqldiff工具的唯一方法是下载完整的源代码（一直到下载页面的底部）并编译它。
#3
1
From the sqlite documentation, you can extract the schema information from the sqlite_master hidden table. (https://www.sqlite.org/fileformat2.html#sqlite_master)
从sqlite文档中，您可以从sqlite_master隐藏表中提取架构信息。 （https://www.sqlite.org/fileformat2.html#sqlite_master）
You can save this content in a text file in GIT, this should give you a way to track changes to the schema.
您可以将此内容保存在GIT中的文本文件中，这样可以为您提供跟踪架构更改的方法。