如何在Linux上比较两个SQLite数据库 [英] How to compare two SQLite databases on Linux
问题描述
使用Linux，我想比较两个具有相同架构的SQLite数据库. 只会有一些区别.
Using Linux, I want to compare two SQLite databases that have the same schema. There will be just a few differences.
是否存在可以输出这些差异的工具? 最好将它们输出到命令行，以便我可以grep/sed它们.
Is there a tool that would output these differences? Preferably output them to the command line, so that I can grep/sed them.
SQLite使用SQL，因此一般的SQL工具也可以使用.
SQLite uses SQL, so a general SQL tool might also do.
推荐答案
请查看 SQLite版本3.8.10 (于2015年5月7日发布).此版本首次包含
Please have a look at the SQLite Release 3.8.10 which was released on May 7, 2015. This release for the first time contains the sqldiff.exe utility program for computing the differences between two SQLite database files. Most likely this program will also be part of future releases.
sqldiff.exe 命令行工具应适用于所有受支持的操作系统，并提供了几个用于更改其输出行为的开关.用法示例:
The sqldiff.exe command-line line tool should work for all supported operating systems and offers several switches for altering its output behavior. Example usage:
sqldiff [options] database1.sqlite database2.sqlite
如果未指定任何选项，则 sqldiff.exe 的输出是将database1.sqlite(源"数据库)转换为database2.sqlite(目标"数据库)的SQL语句. .
If no options are specified, then the output of sqldiff.exe is SQL statements that will transform database1.sqlite (the "source" database) into database2.sqlite (the "destination" database).
但是，也有某些限制.例如， sqldiff.exe 实用程序(至少当前)不会在TRIGGER，VIEW或虚拟表中显示差异.
However, there are also certain limitations. For example, the sqldiff.exe utility (at least currently) does not display differences in TRIGGERs, VIEWs, or virtual tables.
示例命令和输出
我使用了一个简单的键值存储数据库(db1.sqlite)并做了
它的副本(db2.sqlite).然后，我将一对键值对插入
db2.sqlite.之后，我运行了以下命令:
I took a simple key-value store database (db1.sqlite) and made
a copy of it (db2.sqlite). I then inserted one key-value pair into
db2.sqlite. After that I ran the following command:
sqldiff db1.sqlite db2.sqlite
并获得以下输出:
插入my_table(rowid，"key"，value)VALUES(1，'D:\ Test \ Test.txt'，x'aabbccdd');
UPDATE my_table_size SET counter = 1 WHERE rowid = 1;
INSERT INTO my_table(rowid,"key",value) VALUES(1,'D:\Test\Test.txt',x'aabbccdd');
UPDATE my_table_size SET counter=1 WHERE rowid=1;
将键值对插入到my_table后，TRIGGER将自动更新表my_table_size.然后，我再次运行 sqldiff.exe ，但这一次使用db2.sqlite作为第一个参数，而将db1.sqlite作为第二个参数:
The table my_table_size was automatically updated by a TRIGGER after the key-value pair was inserted to my_table. I then ran sqldiff.exe again, but this time with with db2.sqlite as first argument and db1.sqlite as second argument:
sqldiff db2.sqlite db1.sqlite
并获得以下输出:
从my_table WHERE rowid = 1删除；
UPDATE my_table_size SET counter = 0 WHERE rowid = 1;
DELETE FROM my_table WHERE rowid=1;
UPDATE my_table_size SET counter=0 WHERE rowid=1;
sqldiff下载链接
自 SQLite版本3.10.2 于2016年1月20日发布以来，可以直接从 SQLite下载页面.可以在相应操作系统的 sqlite工具归档文件中找到它们(请参见 Precompiled Binaries 部分).例如，以下是指向3.31.1版的 sqlite工具归档文件的链接:
Since SQLite version 3.10.2 which was released on January 20, 2016, the 32-bit binaries for sqldiff can be directly downloaded from the SQLite Download Page. They can be found in the sqlite tools archives for the corresponding operating systems (see the Precompiled Binaries sections). For example, here are the links to the sqlite tools archives of version 3.31.1:
- SQLite tools for Linux
- SQLite tools for OS X
- SQLite tools for Windows
对于3.10.2之前的SQLite版本，SQLite网站托管 sqldiff 的32位二进制文件，但没有链接到它们.以下是3.8.10版的 sqldiff 的链接:
For SQLite versions prior to version 3.10.2, the SQLite website hosts 32-bit binaries for sqldiff, but does not link to them. Here are the links to sqldiff of version 3.8.10:
- sqldiff for Linux
- sqldiff for OS X
- sqldiff for Windows
如果您需要64位二进制文件，则必须下载原始资源和自己编译它们. (文件 sqldiff.c 位于包含源的归档文件的 tool 子目录中.)
If you need 64-bit binaries, then you have to download the raw sources and compile them by yourself. (The file sqldiff.c is located in the tool sub-directory of the archive containing the sources.)
这篇关于如何在Linux上比较两个SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
