shadiakiki1986/db-diff

db-diff

Console application to version control database table contents and show their daily diff, _(*1)

Does it in 3 steps, _(*2)

1. Set up PHP-ODBC connections and store your database connection in /etc/odbc.ini
2. export table from database via ODBC connection to git via git-rest-api server
3. export table again after edits
4. get git diff results

Running periodic exports gives more points in time to diff against, _(*3)

Similar tools (didn't try them) * Red gate: similar to this repo, can version control "static data" * git-sql: diff of sql data dumps * Nayjest/db-diff: diff of two tables with the same structure * PM-Connect/db-diff: diff of two database structures, _(*4)

Usage

(Example usage: docker-db-diff ), _(*5)

1. Requirements:, _(*6)

   1. PDO-ODBC driver and a ready connection
   2. a node-git-rest-api server with deep-diff-yml configured for yml files

2. Set env var for git-rest-api server, _(*7)

export DBDIFF_GRAPI_HOST=http://localhost:8082

1. Synopsis

./bin/pdo-git export [--init] <DSN> <DB>..<TABLE>
./bin/pdo-git post-commit --format [html,console,json] -- <DSN> <DB>..<TABLE>

Note: the <DB>..<TABLE> notation is SQLServer-specific. For MySql, use <DB>.<TABLE> (single dot), _(*8)

Reset:, _(*9)

./bin/pdo-git admin git:deleteAll

Testing

1. (optional) Set up a mysql database to test against locally:

docker run \
  -e MYSQL_RANDOM_ROOT_PASSWORD=yes \
  -e MYSQL_DATABASE=mf \
  -e MYSQL_USER=user \
  -e MYSQL_PASSWORD=password \
  -v $PWD/tests/initdb.d:/docker-entrypoint-initdb.d \
  -p 3306:3306 \
  mysql:8

Test with, _(*10)

mysql --host 127.0.0.1 --user user --password
> use mf;
> select * from t1

1. (optional) Run the git-rest-api server locally:

docker-compose up git

1. (optional) Set up dev env using option 1 or 2 below
2. Install dependencies: composer install
3. Run tests: composer test

Option 1: Dev env with vagrant

cd exporter
vagrant up
VAGRANT> composer test

Option 2: Manually

Install odbc drivers like docker-php7...: [sudo] apt-get unixodbc unixodbc-dev tdsodbc php7.0-odbc, _(*11)

Test installation with : php -i|grep odbc, _(*12)

Install mysql-odbc drivers 1. ubuntu 13.04: apt-get install libmyodbc 2. ubuntu 16.04:, _(*13)

wget https://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.7-linux-ubuntu16.04-x86-64bit.tar.gz
tar -xzf mysql-connector-odbc-5.3.7-linux-ubuntu16.04-x86-64bit.tar.gz
cd mysql.../lib
cp * /usr/lib/x86_64.../odbc/

Set up odbc configuration, _(*14)

cp etc/odbc.dev.ini /etc/
cp etc/odbcinst.dev.ini /etc/

Test with, _(*15)

isql MarketflowAcc user password