SUMMARY: Considering how to use database to store/retrieve data and use it. Installing database inspection tools. Enabling PHP debug output on the server. Creating first table through code from WordPress theme.

Yesterday, I started designing what the task list might look like, and I fell down an implementation rabbithole: how do I define a database schema? I already have some idea of how this works, but I’m not that familiar with programming of databases. So, it’s time to do some digging.

Data Storage

Since I’m using WordPress, it makes sense to store my data in the WordPress database. There are recommended ways to do this in WordPress, as this Codex article on creating tables with plugins describes. Since I’m using a theme, I’m not quite sure what the best way to detect setup and uninstallation.

Best I can tell after doing a bunch of Googling is that setup_theme / after_setup_theme and switch_theme are the hooks we can use to initialize / remove database tables. I spent the rest of the day out how to debug in PHP so I could move forward with this stage.

First, looking at the “Creating Tables with Plugins” article, I’m grabbing the create table and add data to table calls to my _main.php file, which is where I test code before moving them into their final places in the class/file hierarchy. It’s becoming apparent immediately that I need to print out two things: the wpdb reference, and also a MYSQL data structure reference.

On a productivity side-note, hitting this wall after 30 minutes is having a huge demotivational effect on me. I’m getting sleepy and my mind is disinterested / not looking forward to it. But I must push through.

DETOUR: Inspecting the Database…live

I also need to have a means of inspecting the database as changes are made to it. That will probably be PHPMyAdmin, which is a pain to get to via my server admin panel. It might be possible use Access 2007 as a front-end. Let me look into it…OK, apparently I can just download the MySQL Open Database Connectivity (ODBC) driver, install it, and then Access can connect. Let’s see if it works!

• Installed MySQL ODBC driver (64bit)
• Launched Access
• Created a new Access database
• Clicked on External Data, chose MORE
• Created a new Machine Source, selected MySQL ODBC, connected to my server credentials

This allows me to import existing tables and examine them, but it doesn’t show me a “live” view of the database. For that, I probably need to buy something like sqlYog. Let’s see if MySQL has updated their historically-buggy MySQL Administrator…I see something called MySQL Workbench, and Wikipedia indicates it’s well-regarded. Well, let’s try it!

Swanky! I like. Let’s move on.

productivity side-note: starting a new development environment is always so fraught with gathering tools and new techniques, and I find it draining. Getting a code RESULT counts as progress and wakes me up again, so let’s shoot for that next.

So now, we can finally test table create with some code. I’ve modified my main() to call the table set-up code on “after_theme_setup” action hook execution. Let’s see what happens when I run it one time…NOTHING affected in the database table. So running it again, with debug hooks in the creation code to see what’s happening…

DETOUR: Crash Handling

Getting a crash. I’m going to turn on PHP error handling (which is off by default on my server) so I can see error messages interactively. That didn’t seem to work…hmm. So I’m going back to the LOGS now, following the instructions I wrote for this…oops, crashed the server and didn’t realize it with a bad log path. Updating my instructions…ok, now I can see there’s a syntax error in my call of a static class function (oops).

While I had the option to set display_errors on in vhost.conf, I decided to just leave a terminal window open and tail -f php_error_log to watch them appear as things happen. No sense in revealing sensitive path information, potentially, to visitors of my work-in-progress.

RESUME

I can see from my debug output that the table creation code isn’t being called at all. I’m using the action hook after_setup_theme, which I guess is only called when a theme is actually setup for the first time. Looking through the wp-settings.php file, though, I don’t see any conditions that make this so, though…The WP documentation is pretty obtuse on this.

Oh, duh…I just realized that these action hooks won’t be executed because after_setup_theme is executed BEFORE the wp object is initialized. And since the wp object is what loads THIS code (our functions.php for the theme), we’re kind of screwed.

So, instead of relying on WP to trigger the actions, we’ll just have to move all the tests into our initialization routine. By the time it executes, the wp objects are initialized, but the query strings haven’t been parsed for a page (that information is available for template pages).

Had a bit of trouble with the dbDelta function that creates the table. Apparently, it is VERY FUSSY about the formatting of the SQL string it gets.

OK…I have the rudiments of a skeleton database table initialization scheme going, and I am dizzy. I need to take a break, and then rewrite the table version detection and upgrade code. Overkill for now but I think it’s good to think about early on.

To RECAP the lessons of the past 3 hours:

• Made plans to create a database for testing
• Learned how to look at live database via MySQL Workbench
• Learned how to enable PHP Error logging, again, for my server
• Established a dubbing flow. Very rudimentary but it works.
• Used wpdb class and upgrade.php to hand incremental table changes

What I have to do next is properly write a value to a table, then display it on my page. But I need a rest and a break to replenish brain energy.