pebkac thoughts

Perhaps you’ve inherited a database from a previous admin? Perhaps you have some database-backed software and you need a better understanding of the schema? You’re in luck! There’s a free Java-based tool called schemaSpy. You can grab schemaSpy from sourceforge. As of this writing the latest is 4.1.1. I’ll wait while you fetch it.

…

Great! Now that you have done so, please also grab the MySQL JDBC connector. You can find that here. As of this writing the latest is 5.1.7. Grab that, too.

I used that earlier today, pointing it at the database for Documenting the American South, but I’m not at liberty to share those pretty results. Instead let’s point schemaSpy at this very blog. First let’s make a directory for the output:

mkdir ~/blogschema

Now we can run schemaSpy and have it put some pretty diagrams and html there. That would look like this:

java -jar schemaSpy_4.1.1.jar -t mysql \
-dp mysql-connector-java-5.1.7/mysql-connector-java-5.1.7-bin.jar \
-hq -o ~/blogschema -host localhost -db WORDPRESSDB \
-u WORDPRESSUSER -p DBPASSWORD

Note that the stuff in caps would match what’s in the wp-config.php for the WordPress instance. Let’s unpack the rest of that a bit. The -t mysql tells schemaSpy we’re looking at a MySQL database, and will fail if you don’t have the matching -dp pointing to the .jar of the connector I had you download. The -hq means “high quality” output from graphviz (which schemaSpy uses to produce the diagrams). You really don’t want the default. Trust me; it’s ugly. After that we specify the output directory, the host that’s running the MySQL database, and the login info. NOTA BENE: you should NOT run this with the password on the command line like this on a shared system where you don’t trust the users or programs, since the password could be seen in the output of ps.

But what if (like me) your database is remote? SSH to the rescue! Open two terminal windows, and in the first, issue the following:

ssh -T -L 3306:localhost:3306 youruser@mysql.example.com

Where yourusername is your username and mysql.example.com is the host running the MySQL database. What if your login shell and the database are on different hosts? That would look like this instead:

ssh -T -L 3306:mysql.example.com:3306 youruser@login.example.com

The -T suppresses tty allocation and the -L says that the next part is the tunnel. The first 3306 means that the local port (local to you) entering the tunnel is 3306, and the second 3306 means that the remote port (the one connecting to mysql.example.com from login.example.com) of the tunnel is also 3306. Great! now this means that we can run the java -jar schemaSpy_4.1.1.jar blob as above on our local host in the other window you opened. Now let’s see what the results look like. Have a look here for that. It would be much more fun to show off the docsouth schema since it has foreign keys that make for pretty diagrams. Hopefully you have such a database you can diagram yourself.

Happy schemaSpy-ing! Leave a comment if anything is problematic or unclear.

I was off by one state (MO). Looks like the Kos map is really slow to load right now, so give it time.

I just wanted to take a moment to express my disappointment in two fellow TriLUG members, Phillip Rhodes and Warren Myers. I have read plenty of intelligent posts from both of them, so their recent anti-Obama posts (posted after the election and syndicated on planet.trilug.org) have been a bit of a surprise.

Phil decried Obama’s call for expanded community service programs for middle and high school students and a tax incentive for college students who do community service. Phil reads the word “mandatory” into text that simply doesn’t support it. I understand you don’t want mandatory service requirements, Phil. Now please understand that Obama is not pushing for mandatory service requirements.

Warren has a 404′d link to change.gov that he claims proves Obama lied about gun control. Here’s a tip: if you want to make an argument, please make a real argument instead of repeating an outlandish NRA talking point. Oh, and it helps to cite something that’s not 404′d. I read the entire NRA post, Warren: it in no way provides evidence that Obama was anything less than forthright and honest.

I know both of these TriLUG folks are not mean or unintelligent people, which is why it makes me sad to see them buying into the paranoia about Obama. If you really want to have an impact on his administration, construct honest and full arguments for reasonable and rational positions. Believe me: his administration will actually listen. If you decide to spend  his administration tilting at windmills, I’m fairly confident you’ll do more damage to your own credibility than his.

You don’t want to end up being the enactment of this comedy:

We’ll know the winner by 9pm Eastern. See the poll closing times map:

EDIT: The map (http://i481.photobucket.com/albums/rr174/sspenight/map590ek2.gif) was dead as of Mon Oct 12 17:00:23 EDT 2009.