Extracting table schema from database

As part of rehabilitating an old project I’ve had to go through a fairly sizable MySQL database and extract the table structure. I’m doing this for knowledge capture. Currently the schema is only known to the internals of the software that operates on the database, there’s no documentation to speak of (oh what I’d do to get an ER diagram and some notes from the original developers).

In the scheme of things this is a pretty small database (well two of them actually), with only a few dozen tables. Doing the extraction by hand wouldn’t be overly time consuming but it would be overly mind numbing. And as the adage goes: “anything worth doing once is worth scripting”, so I wrote up a quick shell script that will dump the CREATE TABLE statements for each table in a set of databases.

#  extract table schema from some set of databases
PARAMS="--skip-column-names -u ${DB_USER} -p${DB_PASSWD}"

    | mysql ${PARAMS} 
    | grep ${DB_SELECTOR} 
    | while read DB; do
    echo "CONNECT ${DB}; SHOW TABLES;" 
        | grep "${TABLE_SELECTOR}" 
        | mysql ${PARAMS} 
        | while read TABLE; do
            | mysql ${PARAMS} 
            | sed -e  "s/\\n/\n/g;s/^${TABLE}[t]//;s/AUTO_INCREMENT=[0-9]*//
g" > ${DB}-${TABLE}.sql;

I’m a big fan of piping things into while read VAR statements. I’m sure some hate it since it’s a bit hard to read / see where the loop begins but it seems elegant to me and it’s very useful.

The script is basically three sql commands:

  • The first gets the names of all databases the user has access to. This could feasibly be a large number of databases so we add a “selector” to give us a regex we can use to narrow this set down.
  • The second SQL command gets the tables from the selected database. This is within a while loop so it is executed once for each database. This set of tables is also filtered through another regex.
  • Finally in the third SQL command we dump the create table statement for each table (within yet another while loop).

This gets us the CREATE TABLE statements but MySQL puts some stuff in here we don’t want:

  • It prefixes each CREATE TABLE statement with the table name for some reason
  • It also includes the AUTO_INCREMENT value which we don’t care about either
  • It also doesn’t put new line characters in the output but does put the string “n”.

We pipe this output through a pretty gnarly looking sed find/replace to clean it up. The output is then redirected to a file with a name formatted to contain both the name of database and the name of the table. That’s all there is to it. I’ve put up dummy values for some of the selectors as an example.

The script can be downloaded here.

NOTE: The grep and sed regexs can be pretty confusing because of some bash double quoting and escaping. Knowing the rules for bash escaping is essential … and not covered here 🙂


Nokia N97 v20 firmware update requires Windows

Rant alert!

I was so pumped for this new firmware. Anything that makes my phone run better is a good thing. It’s even better when I can actually install it though! What the crap?!?! That’s right I can’t install it. Nokia is only distributing it initially for installation through their software updater that’s tied to some Microsoft Windows ™ software. Check out the release announcement in the Nokia forums.

Seriously? I’m not sure I follow the logic behind only distributing the update exclusively through the Windows ™ updater initially. Aren’t people running Windows ™ the ones that never update their software anyways? (ouch) The phone has a built in software updater for goodness sake! Sure it’s a big update (they list this as the reason it’s not distributed over the air yet) but so what!?!? I updated my firmware to v12 over the air the same day I bought the phone.

Gah! waiting != cool. For shame Nokia, for shame.

The Joys of Inheriting an Old Web Site

Recently I’ve been helping out a buddy of mine with a website he’s recently taken over. I’ve taken on hosting it and I’m helping getting some “best practices” into their development process. I’m no veteran of open source but I’m a long time spectator (some would say a lurker) so I figured it would be a good learning experience for the both of us.

When we took over the code was pretty much just dumped on our laps. We got a tar ball of the code (well the whole site really), a database dump, and a pat on the back. No install script, no documentation, just 53000 lines of PHP, some HTML and some users … great. We didn’t even know how much PHP code there was beyond a file count till I wrote a little script to count the number of lines of code in the PHP files. This script probably gives an inflated sense of code size since it doesn’t account for comments, whitespace or in line HTML. It’s probably safe to say the line count is a few thousand lines heavy but it’s a good estimate (better than what we started with).

find ./ -name '*.php' | 
  while read FILE; do
    SUM=$((SUM+$(cat $FILE | wc -l)));
    echo $SUM;

Simple right? But pretty useful. I had initially tried to replace the cat $FILE | wc -l with just a wc -l $FILE but I guess wc prints the file name after the line count and I couldn’t figure out how to turn this off. Meh, take the file name away from wc and all is well.

So this how it all started. I’ll be posting periodically as interesting things happen with this project. I’ll post useful scripts, whatever interesting PHP stuff I pick up along the way and anything else that may be interesting. The code is pretty old (dates on some files go back to 2003) so this should be interesting. I’ve even spotted a few frames on the site … yeah.

I’ll try to be constructive as much as possible but I can’t guarantee that I won’t post a few WTFs as I go through the code and find ’em … bad coding makes for good comedy (to the right audience or course).

Stay tuned.

Minimal GStreamer App

So in my last post (way back when) I was talking about transcoding some video. I wanted to use GStreamer to do the job but HandBreak was so much easier (pretty cool app too). So after I got the transcoding working with HandBreak I started playing around with GStreamer to see what it would take to code up a small app to do the job.

The first thing is just to code up a little bit of C to get the video playing. The GStreamer part here is pretty simple. It’s just like the gst-launch command I laid out in my last post. I’ve added a few bells and whistles to get command line parameters and to do some input validation. I’m using as much glib as I can for practice.

The result is a pretty short (for C) application that is a bare-bones GStreamer media player. Naturally you’d want a few additional things from a media player but that’s not our end goal. We want to take the playbin (or something like it) and hook it up to another pipeline of our own design that will encode the video & audio for our specific purposes. This is what’s coming up … soon hopefully. Honestly with the class I’m currently taking at SU it may be a while.

For now read the code and enjoy. It’s available for download below. I even made a Makefile for ya.

Download: gst-play-min.tar.gz