compare two database states and generate sql for difference

[ecommy]

Hello and thank you for your great work!

Scenario:

• I have a wordpress database and I am preparing to install a plugin.
• after installing the plugin I don't know the SQL modifications that were made and it would be cook to have them in a revision because:
• when other developers will pull the code and they'll notice the plugin I may also have configured the plugin and would be nice to have the latest plugin data as a revision without the need to install the plugin themselves.

Do you have any thoughts how to approach the issue?

Thank you, Radu G.

[spotman]

+1 for this feature!

[tarciozemel]

+1

[rexli]

+1 , it is very useful I believe.

[jpSimkins]

+1

[mastilver]

Is someone working on this feature?

What I would recommend is to add these methods to the adapters interface:

/**

• return an array that contains all the primary keys of a table
• @example for table 'comment' return array('idArticle', 'idAuthor')
• @param string $name the name of the table
• @return string[]

*/ public function getPrimaryKey($name);

/**

• return an array of SQL query to populate the given table name
• @exemple for table 'comment' return array('INSERT INTO comment(idArticle, idAuthor, content) VALUES(1, 11, test)', 'INSERT INTO ...', ...)
• @param string $name the name of the table
• @return string[]

*/ public function getData($name);

We should as well add a .dbvignore file:

comment # do not save any data of the comment table comment idAuthor = 10 # do not save comments posted by this user comment idAuthor between 0 and 10 ... etc

[victorstanciu]

Hmm, so what you would need is a "snapshot" feature, that would save the current schema for each table, and that could then be compared with a more recent snapshot?

[spotman]

Exactly! Also the ability of automated creating "sql-patches" between "snapshots" would be perfect. With this feature we can create new versions without writing up SQL-queries (they may be generated automatically).

[AlexDubstone]

I installed dbv today and was hoping to have found a tool that does exactly that. I install a plugin or change the configuration using the backend. I don't know what has changed in the database but it would be so neat if I could pass these changes to my team mates so they don't have to set the same configuration again. Is there any way this could be implemented?

[unglud]

I'm a new in bash scripting, but this can give you some ideas

#!/bin/bash

REVISION_PATH="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/data/meta/revision"
REVISIONS_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/data/revisions/"
CUR_REVISION=$(cat "$REVISION_PATH")
CUR_REVISION=$(($CUR_REVISION+1))

NEW_REVISION="$REVISIONS_DIR"$CUR_REVISION/`date '+%Y-%m-%d'`.sql

mkdir "$REVISIONS_DIR"$CUR_REVISION

mysqldbcompare --server1 root:password@localhost:/tmp/mysql/mysql.sock test_dbv:test_dbv_2 --run-all-tests -d sql -a > "$NEW_REVISION"

I use mysqldbcompare util (http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcompare.html) for generating and creating revision sql file. In my case I have 2 databases: test_dbv - remote DB and test_dbv_2 - local development DB. U can think about it like test_dbv - snapshot before starting any changes, and test_dbv_2 - it's snapshot after changes.

Workflow can look like this:

1. pull from VCS
2. update test_dbv from DBV control panel
3. copy test_dbv to test_dbv_2
4. change anything you like in test_dbv_2
5. then invoke dbv/migrate.sh
6. update test_dbv from DBV control panel
7. commit/push

[DevertNet]

+1 !

[Adoniasv]

+1

[danbrellis]

Any updates or thoughts on this feature? Not sure if you are still actively developing this project. Just curious. Thanks!