Occam's razor applies to technology too

January 23, 2008

Sometimes it takes a simple reminder that Occam's razor or the keep it simple stupid principles apply to most things in the technical realm just as it does to the real world.

In the server meltdown last week the data was all recovered but some problems with the backups made it impossible to get all of the file directories. The net result was a friend's WordPress blog melted down. Or at least all the goodies were gone. In the process of renewing the site and getting it updated a tagging plugin no-longer worked. As we talked this afternoon I looked through the database and figured out the schema and what was happening. Quickly enough I hit on a few simple queries and realized I could convert the data.

After another backup of the database it was time to set about migrating the data. The first query is:

INSERT IGNORE into wp_terms (slug, name, term_id) SELECT lower(replace(replace(replace(wp_tags.tag, '.' , ''), "'", ""), "", '')) a, replace(replace(wp_tags.tag, '-', ' '), '_', ' ') b, NULL
FROM wp_tags

That runs perfectly and it was time for step two. Or it was really time for step one. Not a major WordPress user I am not terribly familiar with the community. But it hit me that I was probably not the first person to be doing this. Maybe somebody else had published the SQL to make this process easier. Read more about Occam's razor applies to technology too

MySQL views and MS Access

August 29, 2006

So the CASE... WHEN solution posted previously works very well for MySQL. Lots of nice views popping up and databases going forward.

However, when attempting to link a view as a table in Microsoft Access the calculated columns won't show. It's a real drag as it is such quick work to use views to de-normalize data for backwards compatibility.

Enter the pass-through query which solves this problem as long as having the data in an Access query is suitable.

CASE.... WHEN

August 24, 2006

The answer to my previous post comes in the form of the CASE statement in MySQL.

Basically SELECT (CASE WHEN alt_grade = '' THEN grade ELSE alt_grade END).

On another matter, am I the only one who is having terrible performance from the MySQL site online? During the day it's reminiscent of the good old dial-up-modem days.

Select IF...

August 22, 2006

So today's brain teaser, how to best select either column A or column B in MySQL. Here's the setup. We have three columns that count. They are:

Name
Grade
Alt. Grade

In most rows there is a grade and no alt. grade. But every once in a while there are both a grade and an alt. grade. If it were an either-or case it would be simple. But it's an either-and. The question of the day is how to best accomplish it in MySQL 5.

Upgrading MySQL on Mac OS X Server

August 16, 2006

It is pretty straight forward to migrate an OS X Server's native MySQL 4.1 to a MySQL binary installation of MySQL 5.0. After backing up the databases and /usr/bin install the new binary. Once that is done link /usr/bin/mysql files to /usr/local/mysql/bin.

Two changes are necessary to get everything working. First /etc/my.cnf needs to look at /var/mysql instead of /usr/local/mysql/var for databases. Second since Apple sticks the socket file in /tmp/mysql.sock (come on Apple what are you thinking?) a symbolic link is necessary to have it work properly. The link should point to /var/mysql/mysql.sock (unless you've modified the default config).

The last step is an updatecheck and permissions check.

Selecting the most recent entry for a person in MySQL

July 4, 2004

Here's a snippet of code to get the most recent entry for a person from a list of records with dates. I use this in a situation where I have a list of GPAs for students. The list of GPAs is updated each day for a couple of weeks. The challenge is that some students graduate and are dropped from the list each day so I need to pick the most recent record for each person in the database.

SELECT name, gpa, standing, run_date
FROM `ranking` AS a
WHERE ( standing = '123' OR standing = '122' ) AND a.run_date = (
SELECT MAX( run_date )
FROM `ranking` AS b
WHERE b.name = a.name )
ORDER BY standing, gpa DESC

Pages