[PYTHON] Automatic conversion from MySQL Workbench mwb file to sql file
Documents for whom?
Person who defines DB table (ER diagram creation) using MySQL Workbench
Someone who keeps the mwb file under version control, but can't see the diffs (because it's binary)
Someone working hard to parse the internal xml to treat mwb as text
Problems managing table definitions with mwb
For example, suppose you create a ʻuser table as shown below, save it in the ʻapp.mwb file, and do git add, git commit.
Well, then development continued and we decided to add the ʻarticle table.  Suppose you overwrite ʻapp.mwb with git commit -u.
Now, what do you see when you look at this commit log after a while? You might see the difference if you grab the files as of both revisions and check the contents in Workbench, but usually the only thing you can easily see is the diff in the text file. (It would be nice if there was an mwb diff like the image diff on github, but there seems to be no such thing at the moment)
If mwb is binary, it should be text
It is possible to zip the mwb file itself (which is actually a zipped xml file) to retrieve the internal table definitions, but MySQL Workbench actually has a Lua`` Python scripting interface. This time, we will use this to convert mwb → sql. After that, it seems good to hook at the timing of git commit and automatically put the sql file under version control. Please devise how to handle it on the version control system.
important point
- A "display" is required to launch the MySQL Workbench GUI even when using only the script interface
- Few documentation for using the scripting interface
- When exporting SQL from GUI, you can finely control the output contents, but when dumping from the script interface, you can only output in a fixed format.
For the first issue, I don't want to include the X WIndow System just to use MySQL Workbench, so I use the Xvfb virtual framebuffer. (In the case of Windows, the screen will appear for a moment, but let's give up)
Regarding the second, I made it while looking at the code of MySQL Workbench itself, so I can not guarantee that it is really correct.
Regarding the third, for example, adding / not adding a DROP TABLE statement can be controlled from the GUI, but the method of operating from a script is currently unknown. However, this time the purpose is to convert the difference into text and check it easily, so it does not matter much.
code
The following repositories have the necessary code, samples, and usage. A Python script is also embedded in the main body, mwb2sql.sh, but it actually fits in about 10 lines.
https://github.com/tomoemon/mwb2sql
Description
environment
-
WIndows (confirmed operation on Windows7)
-
Linux (confirmed operation on Ubuntu12.04)
-
Here, we will explain how to run it on Linux without displaying the GUI. See the README in the above repository for easier on Windows.
Installation
- mysql-workbench 5.2
- Xvfb
Start-up
Enable virtual display on Xvfb. When you start Xvfb, you may get a message about fonts, but I don't care.
$ Xvfb :1 &
[dix] Could not init font path element /usr/share/fonts/X11/cyrillic, removing from list!
In this state, start MySQL Workbench with a 1: 1 virtual framebuffer. Since the Python script for dumping the model is written in mwb2sql.sh, when you start it as follows, ʻa.sql contains all the schemas defined in test.mwb`. The CREATE statement for the table is printed.
$ DISPLAY=:1 sh mwb2sql.sh test.mwb a.sql
It uses a virtual framebuffer, so it should work fine even if you're connecting via ssh.
The following content is output to ʻa.sql`.
python
-- ----------------------------------------------------------------------------
-- MySQL Workbench Migration
-- Migrated Schemata: mydb
-- Source Schemata:
-- Created: Thu Aug 01 02:32:15 2013
-- ----------------------------------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;;
-- ----------------------------------------------------------------------------
-- Schema mydb
-- ----------------------------------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
-- ----------------------------------------------------------------------------
-- Table mydb.table1
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
`id` INT NOT NULL ,
`name` VARCHAR(45) NULL ,
`created` DATETIME NULL ,
`updated` DATETIME NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
SET FOREIGN_KEY_CHECKS = 1;;
Cooperation with git hook
Finally, let's combine this script with git hook to automatically commit a textualized sql file. Put the git_pre-commit file on the repository as .git / hooks / pre-commit in the repository you are currently using for development.
Since there is no specification to use the virtual framebuffer in the hook script, export the DISPLAY specification. You can rewrite the hook script.
$ export DISPLAY=:1
In this state, try git add, git commit any mwb file in the repository under development. A file like test.mwb.__auto_generated__.sql should be created and automatically committed. In this case it's a client-side hook, but you can use it as a server-side hook.
Bonus: For those who use Workbench only for drawing ER diagrams
It's a lot more stable than before, but it's still more likely to fall ** with a little operation, so it's not recommended for use as a tool just for drawing diagrams. However, there are many functions unique to Workbench, such as synchronizing the actual table on the DB server with the model on mwb and taking the difference, so if you use Workbench, it is recommended to try using those functions as well. To do.
Recommended Posts