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
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. ![Image 2.png](https://qiita-image-store.s3.amazonaws.com/0/3284/1e468b6e-b0f2-4a1a-33cf-6f7efaa5f03a.png) 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)
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.
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.
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
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.
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;;
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.
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