Writable views to make denormalization less annoying

active_record_sqlite_writable_views.rb

require 'active_record'
ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'

ActiveRecord::Base.connection.raw_connection.execute_batch <<~SQL
  create table makes(id integer not null primary key, make text);
  create table models(id integer not null primary key, model model);
  create table normalized_cars(id integer not null primary key, make_id int, model_id int);

  create view cars as
    select c.id, makes.make, models.model
    from normalized_cars c
    left join makes on makes.id = c.make_id
    left join models on models.id = c.model_id;

  create trigger change_car_make
    instead of update of make on cars begin
      update normalized_cars set make_id = (select id from makes where make = NEW.make)
      where id = NEW.id;
    end;

  create trigger changed_car_model
    instead of update of model on cars begin
      update normalized_cars set model_id = (select id from models where model = NEW.model)
      where id = NEW.id;
    end;

  create trigger insert_cars
    instead of insert on cars begin
      insert into normalized_cars (make_id, model_id) values (
        (select id from makes where make = NEW.make),
        (select id from models where model = NEW.model)
      );
    end;

  insert into makes (make) values ('toyota'), ('ford');
  insert into models (model) values ('corolla'), ('prius'), ('fusion'), ('mustang');
  insert into cars (make, model) values ('toyota', 'corolla'), ('ford', 'fusion');
SQL


class Car < ActiveRecord::Base
  self.primary_key = :id # b/c views don't have primary keys
end

Car.all
# => [#<Car:0x00000001584840f0 id: 1, make: "toyota", model: "corolla">,
#     #<Car:0x00000001584259d8 id: 2, make: "ford", model: "fusion">]

car = Car.first # => #<Car id: 1, make: "toyota", model: "corolla">
car.update! make: 'ford', model: 'mustang'
car             # => #<Car id: 1, make: "ford", model: "mustang">
  .reload       # => #<Car id: 1, make: "ford", model: "mustang">

Car.all
# => [#<Car:0x00000001583ce778 id: 1, make: "ford", model: "mustang">,
#     #<Car:0x00000001583ce6b0 id: 2, make: "ford", model: "fusion">]

Car.create! make: 'toyota', model: 'prius'
Car.all
# => [#<Car:0x0000000158394348 id: 1, make: "ford", model: "mustang">,
#     #<Car:0x000000015838f708 id: 2, make: "ford", model: "fusion">,
#     #<Car:0x000000015838f5c8 id: 3, make: "toyota", model: "prius">]


# Actual data in the table:
ActiveRecord::Base.connection.raw_connection.execute 'select * from normalized_cars'
# => [{"id"=>1, "make_id"=>2, "model_id"=>4},
#     {"id"=>2, "make_id"=>2, "model_id"=>3},
#     {"id"=>3, "make_id"=>1, "model_id"=>2}]