djsipe.com | Web Development

A common problem in complex database systems is an over-abundance of tables, columns, procedures, functions, and the databases that hold them.  Trying to figure out the right columns to use in that reporting query your boss wants by the end of the day can get just a little overwhelming.  Thankfully, there is an easy way to drill down into the structure of your database so you can find just what you’re looking for.

Behold, your information_schema database… The information_schema database is a special database that MySQL uses to keep track of its own internal plumbling.  It holds meta information on every field, column, table, and database on your MySQL server.  For this reason you must treat this database like royalty.  No inserting, updating, or deleting anything unless you want to have to rebuild all your tables.  That said, running selects is harmless enough and if you keep it to that you should be just fine.

Searching For a Column

Here’s a quick way to search for columns in any database by column name:

-- Search for column name containing "bar"
SELECT *
FROM information_schema.COLUMNS
WHERE
    COLUMN_NAME LIKE '%bar%';

Searching For a Table

Need to narrow it down? Search by column name and table name.

-- Search only in tables beginning with "foo" for columns with "bar" in it
SELECT *
FROM information_schema.COLUMNS
WHERE
    COLUMN_NAME LIKE '%bar%' AND
    TABLE_NAME LIKE 'foo%';

Find Columns Used in Stored Procedures or Triggers

Being able to quickly track down all the places a given column can be updated in your database is extremely useful when it comes time to hunt down an illusive bug. Here’s how you can quickly narrow your search to only the stored procedures, functions, and triggers that actually mention your column somewhere in their code:

-- Search all stored procedures and functions for "foobar_column"
SELECT *
FROM information_schema.ROUTINES
WHERE
    ROUTINE_DEFINITION LIKE '%foobar_column%';  

-- Search all triggers for "foobar_column"
SELECT *
FROM information_schema.TRIGGERS
WHERE
    ACTION_STATEMENT LIKE '%foobar_column%';

There’s a whole bunch of other tables to play with in the information_schema table, so dig in… but remember look, don’t touch.

Until about 3 weeks ago, I had almost no idea what “polymorphism” meant—although I knew exactly how to do it. It’s one of those things you’re just going to eventually pick up if you play around with object oriented programming enough, whether you know that’s what it’s called or not. I remember bumping into the term a couple times but the nerd-power behind the lengthy volumes describing it defeated me time and again. Here’s stab at a crude explanation in terms I know I could have understood.

The basic premise of polymorphism, one might argue, can be likened to Plato’s theory of Forms. Plato believed that everything in the world was an imperfect representation of its true Form. A rock on the ground could be thought of as an inexact approximation of its true Form: “Rock”. Likewise, that chair in your room is merely mimicking the perfect, idealized form of a chair. So somewhere—perhaps not in physical form—there exists a pure, perfect, and authoritative version of a chair that all chairs here on Earth mimic, but never quite get 100% right.

It may sound a bit existential, but what Plato was really talking about (from a computer science point of view) was methods of categorization. Think about it. How hard would it be to get a computer to recognize that chair in your room as a chair and not a lamp? By the same token, it’s very easy for us to classify it as a chair. When we classify things on the spot like this, we’re comparing what we see with our own mental vision of what a chair’s “true Form” is.

What is its true Form? What makes a chair a chair, or a door a door? Well, a chair will typically have 3 or more legs, a place to rest your bum, and a back you can lean against. Doors are harder to pin down, but they are generally embedded in walls, and will open somehow to let you go through that wall.

For now, let’s take the example of a door and try to point this ship back towards the topic at hand: polymorphism. Suppose we wanted to build some code to mimic a door. What would it need to do? Ideally it would open. It should probably also be in a wall of some kind. Some basic height and width dimensions might be handy too. So, if I may, this might be your pseudo-code for a door:

door {
action: open();
property: wallType;
property: width;
property: height;
}

What we just pseudo-created is, arguably, the “perfect” Form of a door. Really, we just created a door that’s so generalized it’s pretty much useless. What we really need is an instance of this door, that we can use and go through:

supermarketDoor is-a door {
action: open(
action: slideOpen();
);
property: wallType = "glass";
property: width = "12 feet";
property: height = "8 feet";
}

And that’s it. We just polymorphed. Did you feel it? We can now use our supermarketDoor object. But, from a programming point of view, what allows us to use this door is not that we know how it works. Rather, we can use this door because we know it is a door, and as such has certain characteristics that we know about. We know this door, or any other door for that matter, will be a predicable variation of our original “true Form” of a door. It will open, it will be in a wall of some type, and it will have height and width dimensions. The fact that this door slides open isn’t important, we just want it to open when we tell it to.

So now we can write a quick bit of code that tells a given door to open. And since we know all doors mimic the Form of a door, we can rest assured that our code will always work—no matter what kind of door it’s presented with. This allows us to create a million different types of doors, and never have to touch our original code that opens a door again. That’s the power of polymorphism.