Geeky stuff about the blog server's database

| No Comments

Who knows how many of you will find this interesting, but I'll talk about it anyway. I've been delving into the database that holds all the blog server's data - all the blogs, their settings, entries, comments, all the users, security permissions, and all that.

The designers of Movable Type did a good job with their database; everything is efficiently linked together and easy to understand. If I want a count of all unapproved comments on the system? One simple SQL query, bam, it's done. Listing of all blogs ranked by the number of entries? slightly more complicated query, bam, done. Add myself to the permissions list for an old blog with an insane number of comments? Simple SQL insert, bam, I'm in. Hmmm, those comments are all spam except for ones from WOU IP addresses? Simple SQL delete statement, double-check it to make totally sure it's only going to wipe the ones that need it, bam, done. Say good bye to lots of spam that was cluttering the system; when I started this, there were over 25,000 comments in the database, but after checking them out, I found I could delete all but 3000 or so.

Oddly enough, all the spam was concentrated in about 15 blogs. I cleaned out all of them that didn't seem to currently be in use, but there are a couple that I didn't touch because people had been updating them recently.

I'm thinking about setting up some webpages to report blog use; now that I have the database figured, I can easily make a page to display links to the ten most recent blog entries, or list the most active blogs, or whatever. Now I need to write up this week's FAQ, which will talk about some of the new features on the blog server; since hardly anybody has asked me any actual questions, it'll probably be a short one. I'll intro the features I think are most interesting and/or useful and leave it at that.

Leave a comment

About this Entry

This page contains a single entry by published on March 29, 2007 3:36 PM.

Ahhh, junk filter! was the previous entry in this blog.

FAQ: Blog Server Upgrade is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.