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. Image 1.png

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)

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

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

Installation

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

Automatic conversion from MySQL Workbench mwb file to sql file
Conversion from pdf to txt 1 [pdfminer]
Connect to Docker's MySQL container from Flask
Easy conversion from UTC to local time
Connecting from python to MySQL on CentOS 6.4
Import Excel file from Python (register to DB)
From file to graph drawing in Python. Elementary elementary
Script to create FlatBuffers binaries from SQL database
Problems connecting to MySQL from Docker environment (Debian)
[AWS] Migrate data from DynamoDB to Aurora MySQL