Mysql combines the method of using Database analysis tool Schemaspy _mysql

Recently, I worked with a bunch of old clients and they let them take the time to tell them about the new tools they used in their new environment. One of the very useful tools that I found was schemaspy.

Schemaspy is a Java-developed tool (requiring support from Java 5 or later), primarily to analyze metadata for data models in a database, and to generate browser-based visualization. Click to understand the hierarchy of the datasheet, parent-child table relationships, and so on, mainly through the HTML link or entity diagram to express. It is also designed to help resolve slow errors in database association failures caused by constraints.

One thing I like about the ERD tool is the ability to quickly generate a graphical representation that shows the database that the table is referenced the most, and those tables that have been replaced can be deleted (why do you want to save the data you don't need)? ）。 By understanding database table relationships rather than just looking at query statements, this can speed up the pace of start and also promote the development process to make it more efficient. Another advantage of Schemaspy is that he is a command-line tool that generates HTML pages after each execution, so it is particularly suitable for crontab, making it possible to adjust the page automatically when the model changes. It's good, isn't it?

This tool is simple to use and can help you do a lot of things. Basically, if your database has a foreign key, you steal music, if not, not too desperate, you have more work to wait for you. In most cases, schemspy can do a good job, but sometimes you need to give it a little bit of information in the form of a metadata file.

Install Schemaspy and its related

Download Schemaspy The latest version of the jar file (the version written in this article is 5.0.0)
Make sure you have a Java JRE that can run on your operating platform
You need a Java driver for your database-I'm using the MySQL connector/j
Install Graphiz Package

Creating mysql.properties Files

When the demand is not clear, I like to build it so that the command line can be short and tidy. This is the property file I used for my Percona Server 5.6.10 sandbox:

Copy Code code as follows:

Description=mysql
Driver=com.mysql.jdbc.driver
Connectionspec=jdbc:mysql://127.0.0.1:5610/schemaspy
Driverpath=/usr/share/java/mysql-connector-java.jar

Examples of schemas:

CREATE TABLE ' parent ' (
 ' parent_id ' int () unsigned not NULL auto_increment,
 PRIMARY KEY (' parent_id ')
) ENG Ine=innodb DEFAULT charset=latin1

CREATE TABLE ' child_a ' (
 ' id ' int (a) unsigned not NULL auto_increment,
 ' parent_id ' int () unsigned default NULL,
 PRIMARY KEY (' id ')
engine=innodb default Charset=latin1

CREATE TABLE ' child_b ' (
 ' id ' int () unsigned not NULL auto_increment,
 ' parent_id ' int (a) unsigned DEFAULT NULL,
   primary key (' id '),
 key ' parent_id ' (' parent_id '),
 CONSTRAINT ' child_b_ibfk_1 ' FOREIGN KEY (' parent_id ') REFERENCES ' parent ' (' parent_id ')
engine=innodb DEFAULT charset=latin1

Note that there is an implied relationship in the table child_a to parent through parent_id as a foreign key, but an explicit foreign key parent_id is established in table Child_b. This affects how Schemaspy identifies relationships and whether you need to provide a hint.
run Schemaspy (with foreign key)

If there is no metadata file, you run Schemaspy, you will be able to see the relationship between the parent table and the Child_b table as shown in the following illustration:

Table child_a can also be added to show if your tag points to their relationship box, but this usually works only when your child table and parent table use exactly the same name (parent_id in this case). Your environment looks like a lot of stores, in the parent table the name is ID, in his child table they represent for parent_id--this scenario may be familiar to the developer, but it doesn't help schemaspy at all. That's when you need meta data files to do your thing.

Configuring Metadata Files

The metadata file is in an XML format. The following configuration instance is used to link the child_a table to the parent tables.

<?xml version= "1.0" encoding= "UTF-8"?> <schemameta xmlns:xsi=
"http://www.w3.org/2001/" Xmlschema-instance "
xsi:nonamespaceschemalocation=" http://schemaspy.sourceforge.net/xmlschema/2011/02/05/ Schemaspy.meta.xsd ">
<comments>
Main Production Database. Percona, Michael Coburn
</comments>
<tables>
<table name= "parent" comments= "parent table ">
<column name=" parent_id "primarykey=" true ">
</column>
</table>
< Table Name= "child_a" comments= "non-fk relationship, implicit relationship to Parent" >
<column name= "id" Primarykey= "true" >
</column>
<column name= "parent_id" >
<foreignkey table= " Parent "column=" parent_id "/>
</column>
</table>
</tables>
</ Schemameta>

More complex configuration examples can be accessed.
Run Schemaspy

The following syntax is to connect to the MySQL database, and Schemasy is well supported for most popular relational data.

Java-jar schemaspy_5.0.0.jar-t mysql-u msandbox_ro-p Msandbox-meta Schemaspy.meta.xml-o

The-o option identifies the Schemaspy output file directory, to determine that the directory can be manipulated by the Web server. What I need to mention is that I create a mysql.properties file in the directory where the jar package is located so that I don't have to enter any server: port and database name information.
Browse Schemaspy Output

The complete relational view of the three tables is shown in the following illustration:

Finishing Thoughts

Although I mentioned the relationship bar, Schemaspy is the most useful feature. To avoid the illusion of Schemaspy only this feature, I'd like to mention more about the function bar:

• Table-Name, number of children, parent table, column count, row count, and annotations-a large number of high-level preview table size and a quick way is to navigate to a specific table with the browse search properties.
• Limit--Lists the explicit foreign key restrictions in the database (this does not include restrictions that are configured through metadata files!) ）。
• Exception--The relationship of a possible field/table based on the name, a table without indexes, identified as a nullable field and a unique field (spit it out!) ), a single column table, the name of the child growth field, a table with a null character instead of the actual sqlnull value, which can be used as a general database check or as a browse for detail requirements When a significant error occurs.
• Field--a list of all the fields in the library, it's easy to see if there are any explicit limitations to ignore by name filtering, and then write them down in your metadata file.
• Contribution-This is a free software, and John Currier asks for your donation, and it generally has more reason to spend more time and energy to maintain the software than his wife's complaining.

Finally, don't forget to automate by writing the Schemaspy to crontab.

What if you are using other ERD tools, compared to the schemaspy phase? Please speak freely through comments. Thank you for having the patience to finish reading this article.