Git join SQLite table

Question

I have a log file stored in a SQLite database that I would like to distribute to the git repository.

Later, I would like the changes to the log file to merge automatically with the original.

Will this work? Will the automatic merge of binary files into a SQLite file most often?

Answer 1

I'm not sure if git is really a tool for your work. git is a distributed source code management tool, not a database replication tool.

The only automatic merge that git will try to do is merge text files. The log file is (usually) a text file, so why not put it directly in git and not the database first?

Answer 2

You need to define custom merge and reverse options in the git configuration, and then use the attributes to link them to the files.

It just makes it easy to merge text in landfills, so it could very well produce complete nonsense. You absolutely must check his work to make sure that he has done the right thing. . However, if you lose your boredom, you can easily get rid of simple mergers.

In your .git / config:

[merge "sqlite3"] name = sqlite3 merge driver driver = merge-sqlite3 %O %A %B [diff "sqlite3"] name = sqlite3 diff driver command = diff-sqlite3 

in .gitattributes:

 signons.sqlite diff=sqlite3 merge=sqlite3 

And somewhere in your path called diff-sqlite3

 #!/usr/bin/perl -w use File::Temp qw/ :POSIX /; use IPC::Run qw/run/ ; @ARGV == 7 or die sprintf 'wtf %s', join(' ', @ARGV); my ($name, $x, $y) = ($ARGV[0], $ARGV[1], $ARGV[4]); my ($a, $b); eval { $a = tmpnam(); $b = tmpnam(); run ['sqlite3', $x, '.dump'], '>', $a or die 'sqlite3 failed'; run ['sqlite3', $y, '.dump'], '>', $b or die 'sqlite3 failed'; print "diff-sqlite3 a/$name b/$name\n"; run ['diff', '-u', $a, $b, '--label', "a/$name", '--label', "b/$name"], '>', \*STDOUT; unlink $a; unlink $b; 1; } or do { unlink $a if defined $a; unlink $b if defined $b; die $@ ; } 

also in your path with the name merge-sqlite3

 #!/usr/bin/perl -w use File::Temp qw/ :POSIX /; use IPC::Run qw/run/ ; @ARGV == 3 or die sprintf 'wtf %s', join(' ', @ARGV); my ($o, $a, $b) = @ARGV; print "MERGEING SQLITE FILES $o $a $b\n"; eval { $ad = tmpnam(); $bd = tmpnam(); $od = tmpnam(); run ['sqlite3', $o, '.dump'], '>', $od or die 'sqlite3 failed'; run ['sqlite3', $a, '.dump'], '>', $ad or die 'sqlite3 failed'; run ['sqlite3', $b, '.dump'], '>', $bd or die 'sqlite3 failed'; run ['merge', $ad, $od, $bd] or do { my $newname = "$a.dump"; my $n = 0; while (-e $newname) { ++$n; $newname = "$a.dump.$n"; } print "merge failed, saving dump in $newname\n"; rename $ad, $newname; undef $ad; die 'merge failed'; }; unlink $a or die $!; my $err; run ['sqlite3', $a], '>', \*STDOUT, '2>', \$err, '<', $ad; if ('' ne $err) { print STDERR $err; die 'sqlite3 failed'; } unlink $ad if defined $ad; unlink $bd; unlink $od; 1; } or do { unlink $ad if defined $ad; unlink $bd if defined $bd; unlink $od if defined $od; die $@ ; } 

I just hacked them right now, now you may have to smooth out the kinks.

see http://git-scm.com/docs/gitattributes and http://git-scm.com/docs/git-config

Answer 3

I doubt that any universal version control system (git, svn, cvs, etc.) can process the database in the ways you describe. If you insist on using git to combine the databases, it is best to convert the database to a text file, combine the text file, and recreate the database. For example,

 sqlite3 .dump > dump_file.txt 

can create all the sql instructions needed to rebuild the database, then you make the material for the dumped file, and then create the sqlite database with

 sqlite3 newdatabase.db < modified_dump_file.txt 

You should be able to automate this with a kind of git hook (I'm not too familiar with git).

Answer 4

Despite the fact that this question was asked 8 years ago, I released a tool that does exactly what you ask. It uses a custom diff driver, using the sqlite project tool 'sqldiff', UUID as primary keys, and leaves the sqlite rowid. It is still in alpha, so reviews are welcome.

https://github.com/cannadayr/git-sqlite

Answer 5

It is not possible to combine binaries correctly in the general case, so git cannot and will not do this.

With some effort, you can use git to dump the version database, but apart from very simple cases, you will have to do more than just use direct dumps. You need to think about how sorted rows are sorted based on your key columns, at least. In addition, you will receive false conflicts or merges that create syntactically valid dumps representing the garbage database.

F.ex., if different versions of a string with the same key are displayed in different areas of the line of different dump versions, git might consider it reasonable to keep both of them. The resulting dump would have two representations of the same string, which is nonsense.

In short, you are likely to be unhappy with trying to save a version of the database using version control.

Answer 6

I redefined the diff driver above in the shell script and found that it does not work properly in all cases. The script assumes that the first two parameters give the diff files, but according to man git parameters given to the script are as follows:

path old-file old-hex old-hex new-file new-hex new-hex new-mode

Here is the diff that did this for me:

 #!/bin/sh FILE_PATH=$1 OLD_FILE=$2 OLD_HEX=$3 OLD_MODE=$4 NEW_FILE=$5 NEW_HEX=$6 NEW_MODE=$7 A=`tempfile` B=`tempfile` test -f ${A} && test -f ${B} || exit 1 sqlite3 ${OLD_FILE} .dump > ${A} && sqlite3 ${NEW_FILE} .dump > ${B} && diff -u ${A} ${B} --label "${FILE_PATH}@${OLD_HEX}" --label "${FILE_PATH}@${NEW_HEX}" rm ${A} rm ${B}