I spent most of today working on my story website. It's a pain in the neck to maintain, especially in updates, and it needs more smarts. A lot more smarts. I decided to see how much lifting it would take to get all of my stories, regardless of series or arc, into a database using Ruby.

Right now the "database of stories" is a collection of flat files. This isn't tragic; flat files are fast to deploy and are easily human-readable, but they can become dispersed and are clunky to update. I suppose I could have come up with some kind of "pulsing" mechanism that would update from a core source tree every day, but that was just unsatisfying. What I wanted was a way to keep the collection updated that could also do important things like next/back for episodes of a series, and could maintain a list of stories that had not yet been released, and wouldn't make them available to the index before the release date.

Step one: create the database.

I like Ruby almost as much as I like Python, and eruby just makes my life so much easier. When there's a full-blown eruby implementation for Python, I'll go for it.

This database is completely unnormal and it won't be normalized for a while. This is just the first step, but I want to show how easy it is to create a database in ruby if you have rails installed, but don't really want to use rails. It's this simple:

require 'rubygems'
require 'active_record'

ActiveRecord::Base.establish_connection({
      :adapter => "sqlite3",
      :dbfile => "stories.sqlite"
})

begin
  ActiveRecord::Schema.drop_table('stories')
rescue
  nil
end

ActiveRecord::Schema.define do
  create_table "stories", :force => true do |t|
    t.column "seq", :string, :limit => 100, :null => false
    t.column "title", :string, :limit => 75, :null => false
    t.column "series", :string, :limit => 75, :null => false
    t.column "arc", :string, :limit => 75, :default => '', :null => false
    t.column "body", :text , :null => false
    t.column "pubdate", :date, :default => 0
    t.column "wordcount", :integer, :default => 0
    t.column "cookie", :string, :limit => 40, :null => false
    t.column "codes", :string, :limit => 40, :null => false
  end
end

By importing ActiveRecord, you get access to all of the things ActiveRecord can do without needing rails. First, I establish a connection, drop the table if I need to, and then build myself a new one. Viola', instant SQL, and the builder code is pretty darned database-agnostic.

Ruby purists will tell me that I should just use migrations. I don't want to use migrations for this project; it's not the goal for this project. Since the define() is empty, I won't get a schema versioning table, which was a goal for the experiment this time around. And as I said, there's no normalization here. That's okay. Maybe someday. Maybe I'll never need it.