JDPFu.com 2025

Settings Column Design Concerns

Over the years, the blog DB has been updated time and time again. Along the way, it appears that the software didn’t always behave properly. Some of the settings were stored in the same DB column. I know from working on DBs designed in this manner for clients that this seems like a good idea, initially, but over the long term, this is a failure waiting to happen. If it isn’t an outright failure, it certainly will become slower as the DB becomes larger.

Initially, I was unaware of this part of the DB design when seeking a solution. The first solution attempt below shows that ignorance.

First Fix Try

The first attempt at a fix was to use the Ruby on Rails db:migrate facility. It failed.
Here’s the code:

class InitPasswordForArticles < ActiveRecord::Migrationclass InitPasswordForArticles < ActiveRecord::Migration

class Content < ActiveRecord::Base
end
class Article < Content
end
def self.up
say "Initializes existing articles with a password"
Article.find(:all).each do |art|
if art.password.nil? or art.password.empty?
art.password = ""
art.save!
end
end
end
def self.down
say "There is migration does absolutely nothing"
end
end

The Real Issue

That migration ran and nothing was fixed. In fact, it seemed to break more articles so they could not be viewed or edited. It was scary. Things were getting worse, not better.

I started looking for the password field that was causing the issue. After not finding that field, but having the migration code above which was provided by the core Typo Blog developer, I knew which DB table was involved.

I’m comfortable using a DB management tool, so I

• dumped the schema to a file
• looked at the columns
• performed a few queries to understand where the passwords were being stored.
  Doing this, I found the column I needed to research more about was settings. I used this query to see more about it:
  

  SELECT id, title, settings FROM contents WHERE id > 0;

Seeing that output for the 1450 or so articles plus a little testing based on the values seen inside the settings column, it became clear to me that along the way the values inside the settings column had been screwed up somehow. I was unable to figure out the specific settings held inside the column after looking at the DB and the code. Rather than fight it too much, I found the articles that were working as desired and copied those values in the settings column. This is the SQL UPDATE statement used:

UPDATE contents SET settings = '---
password: ""
';

It is nice to have access to all the articles again and for the sidebar Categories links to work again.