Effective database usage with Ruby on Rails

[genkio]

study notes taken from the Effective database usage with Ruby on Rails course

Data Modelling

The unexpected nil

# problem: @book.title.truncate(100) -> NoMethodError
# investigate in rails console
pp Book.all.map {|x| [x.id, x.title]}

# fix #1 at display time in the view
@book.title.to_s.truncate(100)

# fix #2 with validation in the model
validates :title, presence: true, if: ->(record) { record.nil? }
# this solution does not prevent a nil record being created
book = Book.new
book.save(validate: false)

# the right way to do it is to fix with a schema change via migration
class AddNotNullToBlurb < ActiveRecord::Migration
  def change
    Book.update_all({title: ''}, {title: nil})
    change_column :books, :title, :text, null: false, default: ''
  end
end

Missing parent

# in the view, `book.author.name` lead to undefined method `name` for nil:NilClass
# fix #1 in the model
class Author < ActiveRecord::Base
  has_many :books, dependent: :destroy
  # or has_many :books, dependent: :delete_all # with better performance cause it will skip callbacks
end

# fix #2, to prevent associated record be deleted in the first place
class Author < ActiveRecord::Base
  has_many :books, dependent: :restrict_with_error
end

# fix #3, to add foreign key constraint
class AddFkToBooks < ActiveRecord::Migration
  def up
    execute <<-SQL
      ALTER TABLE books
      ADD CONSTRAINT fk_books_on_author_id FOREIGN KEY (author_id)
      REFERENCES authors (id)
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE books
      DROP CONSTRAINT fk_books_on_author_id
    SQL
  end
end

Duplicate data

# fix via migration
class AddBookTitleUniqueKey < ActiveRecord::Migration
  def change
    # find existing duplicated data
    dups = Book.find_by_sql <<-SQL
      select * from books where title in
        (select title from books group by title having count(title) > 1);
    SQL

    # remove existing duplicated data
    dups = dups.group_by(&:title)
    dups.each do |title, books|
      books.sort_by(&:created_at).drop(1).each(&:destroy)
    end

    # add unique index
    add_index :books, :title, unique: true
  end
end

# then to update the model class to make it more exception handling friendly
class Book < ActiveRecord::Base
  include RetryMethods

  validates :title, presence: true, uniqueness: true
  belongs_to :author

  def save_with_retry_on_unique(*args)
    retry_on_exception(ActiveRecord::RecordNotUnique) do
      save(*args)
    end
  end
end

# models/concerns/retry_methods.rb
module RetryMethods
  def retry_on_exception(ex, n = 1, &block)
    begin
      count = 1
      block.call
    rescue
      if count <= note
        count += 1
        retry
      else
        raise
      end
    end
  end
end

Reactive integrity tests

# lib/integrity_checker.rb
require 'minitest/autorun'
require File.expand_path('../../config/environment', __FILE__)

class IntegerityChecker < MiniTest::Unit::TestCase
  MAX_ERRORS = 50
  def test_books_are_valid
    errors = []
    Book.find_each do |book|
      next if book.valid?

      errors << [book.id, book.errors.full_messages]
      break if errors.size > MAX_ERRORS
    end
    assert_equal [], errors
  end

  def test_book_title_are_not_nil
    assert_equal 0, Book.where(title: nil).count
  end
end

Concurrency

Optimistic locking with ActiveRecord

# step 1. add the lock_version column
class AddLockVersionToBooks < ActiveRecord::Migration
  def change
    add_column :books, :lock_version, :integer, null: false, default: 0
  end
end
# rake db:migrate

# understand better the sql difference after lock_version added
# UPDATE "books" SET "title" = "new title", "lock_version" = 1 WHERE ("books"."id" = 2 AND "books"."lock_version" = 0)

# step 2. update the UI, to add the hidden_field for lock_version
# views/books/_form.html.erb (removed <%= %> for better markdown formatting)
f.hidden_field :lock_version

# step 3. include lock_version as part of the update in the controller
# controllers/books_controller.rb
def book_params
  params.require(:book).permit(:title, :author_id, :lock_version)
end

# step 4. conflict resolution, to deal with ActiveRecord::StaleObjectError exception
# controllers/books_controller.rb
def update
  if @book.update(book_params)
    redirect_to @book, notice: 'Book was successfully updated.'
  else
    render action: 'edit'
  end
rescue ActiveRecord::StaleObjectError
  @conflicting = Book.find(@book.id)
  # update the lock_version, othewise any further update will still be failed
  @book.lock_version = @conflicting.lock_version
  render action: 'edit'
end

# views/books/_form.html.erb (removed <%= %> for better markdown formatting)
if @conflicting
  <p>The book was changed by someone else! Please confirm your changes</p>
end

Counters

# naive solution
def increment
  @book.increment!(:in_stock)
  # @book.update_attributes!(in_stock: @book.in_stock + 1)
  redirect_to :back
end

def decrement
  @book.decrement!(:in_stock)
  # @book.update_attributes!(in_stock: @book.in_stock - 1)
  redirect_to :back
end

# the sql behind
# SELECT "books" .* FROM "books" WHERE "books"."id" = $1 LIMIT 1 [["id", "2"]]
# there's a dangerous gap between select and update!
# UPDATE "books" SET "in_stock" = $1 ...

# solution with counters
def increment
  Book.increment_counter(:in_stock, @book.id)
  redirect_to :back
end

def decrement
  Book.decrement_counter(:in_stock, @book.id)
  redirect_to :back
end

# the sql behind
# inline operation
# UPDATE "books" SET "in_stock" = COALESCE("in_stock", 0) - 1 WHERE "books"."id" = 2

Pressimistic locking

# models/book.rb
def discontinue_with_lock!
  transaction do
    lock!
    discontinue!
  end
end

# the sql behind Pressimistic locking `FOR UPDATE`
# SELECT id, title FROM books WHERE id = 1 FOR UPDATE

A real-world pressimistic strategy solution

# models/book.rb
class Book < ActiveRecord::Base
  has_many :reservations

  class NoStack < StandardError; end

  def reserve_the_naive_version
    transaction do
      if in_stock > 0
        # there is a gap!!! between check and update
        reservations.create!(expires_at: 1.day.from_now)
        Book.decrement_counter(:in_stock, id)
      end
    end
  end

  def reserve
    transaction do
      rows_updated = Book.
        where(['id = ? AND in_stock > 0', id]).
        update_all('in_stock = in_stock - 1')
      if rows_updated == 1
        reservations.create!(expires_at: 1.day.from_now)
      else
        raise NoStack
      end
    end
  rescue NoStack
    if Book.expire_old_reserverations > 0
      retry
    end
  end

  def pickup(reservation_id)
    reservations.find(reservation_id).destroy
  rescue ActiveRecord::RecordNotFound
  end

  def abandon(reservation_id)
    transaction do
      reservations.find(reservation_id).destroy
      Book.increment_counter(:in_stock, id)
    end
  rescue ActiveRecord::RecordNotFound
  end

  def self.expire_old_reserverations
    expired = 0
    to_expire = Reservation.where(['expires_at < ?', Time.zone.now])
    to_expire.each do |reservation|
      begin
        transaction do
          reservation.destroy
          Book.increment_counter(:in_stock, reservation.book_id)
          expired += 1
        end
      rescue ActiveRecord::RecordNotFound
      end
    end
    expired
  end

end

Reporting

Creating a star schema

# create a new table for reporting
class AddSalesTable < ActiveRecord::Migration
  def change
    create_table :sales do |t|
      t.datetime :placed_at
      t.integer :revenue
      t.integer :unit_price
      t.integer :quantity

      t.integer :order_line_item_id
      t.integer :order_id
      t.integer :book_id
      t.integer :author_id
      t.integer :buyer_id
      t.string :state, limit: 2
    end
  end
end

# model class
class Sale < ActiveRecord::Base
  def self.create_from_order!(order)
    return if order.canceled_at

    order.line_items.each do |line_item|
      create!(
        order_line_item_id: line_item.id,
        order_id: order.id,
        book_id: line_item.book_id,
        author_id: line_item.book.author_id,
        buyer_id: order.buyer_id,
        state: order.shipping_address.state,
        placed_at: order.placed_at,
        revenue: line_item.unit_price * line_item.quantity,
        unit_price: line_item.unit_price,
        quantity: line_item.quantity
      )
    end
  end
end

# populate table with a rake task
task populate_sales: environment do
  Order.find_each do |order|
    Sale.create_from_order!(order)
  end
end
# rake bookstore:populate_sales

[cmendla]

Thanks - Your description of Pessimistic record locking is very useful.