Escaping ActiveRecord

What is wrong with ActiveRecord?

def self.visible_for user
  groups       = Arel::Table.new(:groups)
  projects     = Arel::Table.new(:projects)
  roles        = Arel::Table.new(:roles)
  user_roles   = Arel::Table.new(:roles_users)
  roles2       = Arel::Table.new(:roles).alias
  user_roles2  = Arel::Table.new(:roles_users).alias

  join_roles = groups.create_on \
    groups[:id].eq(roles[:resource_id])
    .and(roles[:resource_type].eq('Group'))
  outer_join_roles = projects.create_join(roles, join_roles, Arel::Nodes::OuterJoin)

  join_user_roles = roles.create_on(user_roles[:role_id].eq(roles[:id]))
  outer_join_user_roles = projects.create_join(user_roles, join_user_roles, Arel::Nodes::OuterJoin)

  join_projects  = groups.create_on \
    groups[:project_id].eq(projects[:id])
  outer_join_projects = groups.create_join(projects, join_projects, Arel::Nodes::OuterJoin)

  join_roles2 = groups.create_on \
    projects[:id].eq(roles2[:resource_id])
    .and(roles2[:resource_type].eq('Project'))
  outer_join_roles2 = groups.create_join(roles2, join_roles2, Arel::Nodes::OuterJoin)

  join_user_roles2 = roles2.create_on(user_roles2[:role_id].eq(roles2[:id]))
  outer_join_user_roles2 = groups.create_join(user_roles2, join_user_roles2, Arel::Nodes::OuterJoin)

  uniq.
    joins(outer_join_roles).
    joins(outer_join_user_roles).
    joins(outer_join_projects).
    joins(outer_join_roles2).
    joins(outer_join_user_roles2).
    where(user_roles[:user_id].eq(user.id).or(user_roles2[:user_id].eq(user.id)))
end

The "Rails" Way

def self.visible_for user
  sql = <<EOF
    SELECT DISTINCT groups.*
    FROM groups

    LEFT OUTER JOIN projects ON groups.project_id = projects.id

    LEFT OUTER JOIN roles ON groups.id = roles.resource_id
                          AND roles.resource_type = 'Group'
    LEFT OUTER JOIN roles_users ON roles_users.role_id = roles.id

    LEFT OUTER JOIN roles roles_2 ON projects.id = roles_2.resource_id
                                  AND roles_2.resource_type = 'Project'
    LEFT OUTER JOIN roles_users roles_users_2 ON roles_users_2.role_id = roles_2.id

    WHERE groups.deleted_at IS NULL
      AND (roles_users.user_id = ? OR roles_users_2.user_id = ?)
EOF
  where([sql, user.id, user.id])
end

The "Database" Way

"But we might change database one day"

- Did no one, ever...

So start using your database!

module ActiveRecord::SecureToken::ClassMethods
  def generate_unique_secure_token(attribute)
    10.times do |i|
      SecureRandom.hex(12).tap do |token|
        if exists?(attribute => token)
          raise "Couldn't generate a unique token in 10 attempts!" if i == 9
        else
          return token
        end
      end
    end
  end
end

The "Rails" Way

module ActiveRecord::SecureToken::ClassMethods

  def generate_unique_secure_token
    SecureRandom.base58(24)
  end

end

The "Rails" Way

ALTER TABLE my_table ADD COLUMN uid text NOT NULL DEFAULT uuid_generate_v4();
CREATE FUNCTION make_uid() RETURNS text AS $$
DECLARE
    new_uid text;
    done bool;
BEGIN
    done := false;
    WHILE NOT done LOOP
        new_uid := uuid_generate_v4();
        done    := NOT exists(SELECT 1 FROM my_table WHERE uid=new_uid);
    END LOOP;
    RETURN new_uid;
END;
$$ LANGUAGE PLPGSQL VOLATILE;

ALTER TABLE my_table ADD COLUMN uid text NOT NULL DEFAULT make_uid();

The "Database" Way

Scoped

auto-increment

The "Rails" Way

thread.increment!(:comment_sequence)
comment.scoped_id = thread.comment_sequence

The "Database" Way

ALTER TABLE comments ADD COLUMN sequence_id SERIAL;
CREATE SEQUENCE comments_for_post_1_sequence;

ALTER TABLE comments
ADD COLUMN scoped_id integer NOT NULL DEFAULT nextval('comments_for_post_1_sequence');

ALTER SEQUENCE comments_for_post_1_sequence OWNED BY comments.scoped_id;

Random records

The "Rails" Way

comment = Comment.order("RANDOM()").limit(10)
comment_ids = Comment.pluck(:id).shuffle[0..4]
Comment.where(id: comment_ids)

The "Database" Way

select * from comments
where id in (
  select round(random() * (select count(1) from logs)::integer)::integer as id
  from generate_series(1, 20)
  group by id -- Discard duplicates
)
limit 10

Counter cache

The "Rails" Way

class Comment < ActiveRecord::Base  
  belongs_to :post, counter_cache: true
end

class Post < ActiveRecord::Base  
end

```
CREATE TABLE posts (  
  id              serial PRIMARY KEY,
  comments_count  integer NOT NULL DEFAULT 0
);

CREATE TABLE comments (  
  id       serial PRIMARY KEY,
  post_id  integer NOT NULL REFERENCES posts(id),
);
```

comment = Comment.create(post: post)
comment.update_attribute(:post_id, 42)

The "Database" Way

CREATE TRIGGER update_post_comments_count  
  AFTER DELETE OR INSERT OR UPDATE ON comments
  FOR EACH ROW EXECUTE PROCEDURE counter_cache('posts', 'comments_count', 'post_id');
CREATE FUNCTION increment_counter(table_name text, column_name text, id integer, step integer)  
  RETURNS VOID AS $$
    DECLARE
      table_name text  := quote_ident(table_name);
      column_name text := quote_ident(column_name);
      conditions text  := ' WHERE id = $1';
      updates text     := column_name || '=' || column_name || '+' || step;
    BEGIN
      EXECUTE 'UPDATE ' || table_name || ' SET ' || updates || conditions
      USING id;
    END;
  $$ LANGUAGE plpgsql;

The "Database" Way

CREATE FUNCTION counter_cache()  
  RETURNS trigger AS $$
    DECLARE
      table_name text    := quote_ident(TG_ARGV[0]);
      counter_name text  := quote_ident(TG_ARGV[1]);
      fk_name text       := quote_ident(TG_ARGV[2]);
      fk_changed boolean := false;
      fk_value integer;
      record record;
    BEGIN
      IF TG_OP = 'UPDATE' THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name || ' != ' || '($2).' || fk_name
        INTO fk_changed
        USING OLD, NEW;
      END IF;

      IF TG_OP = 'DELETE' OR fk_changed THEN
        record := OLD;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, -1);
      END IF;

      IF TG_OP = 'INSERT' OR fk_changed THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, 1);
      END IF;

      RETURN record;
    END;
  $$ LANGUAGE plpgsql;

Database Views

You know when you need one

# Highest Scoring Talks in PA by Authors named Parker

Feedback.joins(talk: [:author, { club: :city }] )
        .select("feedbacks.talk_id, avg(feedbacks.score) as overall_score")
        .where("cities.state_abbr = ?", "PA")
        .where("authors.name LIKE '%?%'", "Parker")
        .group("feedbacks.talk_id")
        .order("overall_score desc")
        .limit(10)
# 665ms
SELECT feedbacks.talk_id, avg(feedbacks.score) as overall_score
FROM "feedbacks"

INNER JOIN "talks"   ON   "talks"."id" = "feedbacks"."talk_id"
INNER JOIN "authors" ON "authors"."id" = "talks"."author_id"
INNER JOIN "clubs"   ON   "clubs"."id" = "talks"."club_id"
INNER JOIN "cities"  ON  "cities"."id" = "clubs"."city_id"

WHERE (cities.state_abbr = 'PA')
AND   (authors.name LIKE '%Parker%')

GROUP BY feedbacks.talk_id
ORDER BY overall_score desc
LIMIT 10;

* https://github.com/droberts84/materialized-view-demo

Create View

class CreateTalkView < ActiveRecord::Migration
  def up
    connection.execute <<-SQL
      CREATE VIEW v_talks_report AS
        SELECT cities.id as city_id,
               cities.name as city_name,
               cities.state_abbr as state_abbr,
               technologies.id as technology_id,
               clubs.id as club_id,
               clubs.name as club_name,
               talks.id as talk_id,
               talks.name as talk_name,
               authors.id as author_id,
               authors.name as author_name,
               feedback_agg.overall_score as overall_score
        FROM (
          SELECT talk_id, avg(score) as overall_score
          FROM feedbacks
          GROUP BY talk_id
        ) as feedback_agg
        INNER JOIN talks ON feedback_agg.talk_id = talks.id
        INNER JOIN authors ON talks.author_id = authors.id
        INNER JOIN clubs ON talks.club_id = clubs.id
        INNER JOIN cities ON clubs.city_id = cities.id
        INNER JOIN technologies ON clubs.technology_id = technologies.id
    SQL
  end

  def down
    connection.execute 'DROP VIEW IF EXISTS v_talks_report'
  end
end

Encapsulate in

ActiveRecord Model

class TalkReport < ActiveRecord::Base
  # Use associations just like any other ActiveRecord object
  belongs_to :author
  belongs_to :talk
  belongs_to :club
  belongs_to :city
  belongs_to :technology

  # take advantage of talks has_many relationship
  delegate :feedbacks, to: :talk

  self.table_name = 'v_talks_report'

  # views cannot be changed since they are virtual
  def readonly
    true
  end
end

And just use it!

# Highest Scoring Talks in PA by Authors named Parker

TalkReport.where(state_abbr: 'PA')
          .where("author_name LIKE '%Parker%'")
          .order(overall_score: :desc)
          .limit(10)

But there's a catch...

SELECT v_talks_report.*
FROM "v_talks_report"

WHERE state_abbr = 'PA'
AND   author_name LIKE '%Parker%'

ORDER BY overall_score desc
LIMIT 10;

-- 665ms

Materialized Views

How it works?

Create it...

class CreateTalkView < ActiveRecord::Migration
  def up
    connection.execute <<-SQL
      CREATE MATERIALIZED VIEW mv_talks_report AS
      # [...]
    end
  end
end

... and use it!

# Highest Scoring Talks in PA by Authors named Parker

TalkReport.where(state_abbr: 'PA')
          .where("author_name LIKE '%Parker%'")
          .order(overall_score: :desc)
          .limit(10)

# 19ms

But there's always a catch

  • PostgreSQL 9.3+
  • Entire Materialized View must be refreshed
  • Migrations are hard!

Quick fixes

class TalkReport < ActiveRecord::Base
  # [...]

  def self.repopulate
    connection.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY #{table_name}")
  end
end
  • insert / update / delete
  • database triggers
  • incremental updates (not yet)

So, should I hate ActiveRecord?

Nope, just think twice what you're doing!

Thanks!

Escaping ActiveRecord

By Bernard Potocki

Escaping ActiveRecord

  • 491

More from Bernard Potocki