This is part 2 of my Sinatra blog tutorial. Today I’m talking about the database layer. Don’t forget to check out the full source of my blog on github.
I used sequel as the ORM for this blog. I had originally wanted to use DataMapper, but I ran into some problems. They’re pretty similar anyway.
First, we need to give the connection information to Sequel. I put my database info in a yaml file because I wanted to be able to pass the command-line migration tool the same info. I require this file from my “controller”. It’s important to remember that the following files aren’t a sinatra standard or anything. You can organize your files however you like.
data/init.rb
require 'rubygems'
require 'sequel'
require 'yaml'
content = File.new("data/database.yml").read
settings = YAML::load content
DB = Sequel.connect "#{settings['adapter']}://#{settings['username']}:#{settings['password']}@#{settings['host']}/#{settings['database']}"
require 'data/models'data/database.yml
adapter: mysql host: localhost username: root password: root database: custom_blog
Here are my models. The syntax of these models will look similar to active record. There aren’t much to them. A few things to notice are that I used simple many_to_many and one_to_many associations.
data/models.rb
require 'rubygems'
require 'sequel'
class Post < Sequel::Model
Page = "page"
Post = "post"
many_to_many :tags
one_to_many :comments
def date
created.strftime "%B %d, %Y"
end
def summary(length=300)
body.gsub(/(<[^>]*>)|\n|\t/s," ")[0..length]
end
def update_title(value)
raise "[ ! ] Could not find title for post" if value.nil?
self.title = value
self.name = value.downcase.gsub(/[^\w]/,"_").gsub(/__/,"")
end
end
class Tag < Sequel::Model
many_to_many :posts, :order => :created.desc
end
class Comment < Sequel::Model
many_to_one :posts
def post
Post[:id => post_id]
end
endThese behave as expected. The syntax is only slightly different from active record. Sequel allows you to chain commands to a dataset together, which makes it easy to create complex queries.
Post.all Post.first.comments Post.filter(:kind => Post::Page).reverse_order(:created).all Post.first.tags
I’ll give you more concrete examples when we cover the controller. The next thing to look at is the migration. Now, I don’t really like migrations, but until these ORMs add support to dynamically update the database schema, I’m stuck with them. Here’s where I’m at currently.
data/001_BaseSchema.rb
require 'data/models'
class BaseSchema < Sequel::Migration
def up
create_table! :posts do
primary_key :id
String :title
Text :body
Time :mtime
Time :created
String :kind, :default => Post::Post
String :file
String :name
end
create_table! :tags do
primary_key :id
String :name
end
create_table! :posts_tags do
primary_key :id
foreign_key :tag_id, :tags
foreign_key :post_id, :posts
end
end
def down
drop_table :posts
drop_table :tags
drop_table :posts_tags
end
enddata/002_Comments.rb
require 'data/models'
class Comments < Sequel::Migration
def up
create_table! :comments do
primary_key :id
foreign_key :post_id, :posts
String :name
String :email
String :url
Text :body
end
end
def down
drop_table :comments
end
endTo run the migrations, you use the sequel command-line tool. Here, I’m telling it to run migrations from my data folder, and to use data/database.yml for connection info.
sequel -m data data/database.yml sequel -m data data/database.yml -M 1 # migrates to 001
That’s it for now!
