WordPress Database Finally Unveiled!!!

Sorry for that tabloid title, but I couldn’t resist. I’m going to show you how to view the database design of other people’s systems. It will work for legacy systems as well. Of course, you may call show tables and describe table in the database console but that’s a very rudimentary way to examine the structure. You’ll miss a bird’s eye view of the design which is crucial to understanding a system as a whole.

In this example, I’ll rely on a famous blog engine: WordPress. WordPress uses MySQL to store blog content.

This is a list of stuff that I needed:

1. Access to the database: name, hostname, user and password, in my case: localhost, wp,wp, wp
2. Java Runtime Environment – already have it
3. Vertabelo Reverse Engineering – downloaded from site
4. MySQL JDBC driver – downloaded from site

I’ve put the JDBC driver and reverse engineering jars in the same folder and executed the following command:

java -cp reverse-engineering-1_3_5.jar:mysql-connector-java-5.1.28-bin.jar \
      com.vertabelo.reverse.Main \
      -url jdbc:mysql://localhost/wp \
      -user wp \
      -password wp  \
      -o wp.xml

It printed some info:

No JDBC driver given. Using JDBC driver: org.gjt.mm.mysql.Driver
Reverse engineering MySQL database
Connecting to the database...
Extracting table information
[...]
Database model saved in: /home/nabla/public_html/wp/wp.xml

If you want to know more about reverse engineering, here is a blog post related to this topic: “How to import an existing database into Vertabelo: Reverse Engineering”.

Back to the history. I got a file wp.xml. The file contained a database design. I logged into Vertabelo, created a new model (picked MySQL), and imported the wp.xml file.

Here is what I got:

I put in a little effort and added references, rearranged, and colored tables. It became nice and more readable.

These two examples are live. Mouse over them to play and discover :).

The WordPress database is simple enough to understand without graphical representation. Digging into more complex systems without visual representation of its internal structure can be really painful. Would you explore the jungle without a map and machete? Probably not. Use Vertabelo.

That’s it!