git.squeep.com Git - akkoma/blob - priv/repo/migrations/20200109123126_add_counter_cache_table.exs

   1 defmodule Pleroma.Repo.Migrations.AddCounterCacheTable do
   2   use Ecto.Migration
   3
   4   def up do
   5     create_if_not_exists table(:counter_cache) do
   6       add(:name, :string, null: false)
   7       add(:count, :bigint, null: false, default: 0)
   8     end
   9
  10     create_if_not_exists(unique_index(:counter_cache, [:name]))
  11
  12     """
  13     CREATE OR REPLACE FUNCTION update_status_visibility_counter_cache()
  14     RETURNS TRIGGER AS
  15     $$
  16       DECLARE
  17       BEGIN
  18       IF TG_OP = 'INSERT' THEN
  19           IF NEW.data->>'type' = 'Create' THEN
  20             EXECUTE 'INSERT INTO counter_cache (name, count) VALUES (''status_visibility_' || activity_visibility(NEW.actor, NEW.recipients, NEW.data) || ''', 1) ON CONFLICT (name) DO UPDATE SET count = counter_cache.count + 1';
  21           END IF;
  22           RETURN NEW;
  23       ELSIF TG_OP = 'UPDATE' THEN
  24           IF (NEW.data->>'type' = 'Create') and (OLD.data->>'type' = 'Create') and activity_visibility(NEW.actor, NEW.recipients, NEW.data) != activity_visibility(OLD.actor, OLD.recipients, OLD.data) THEN
  25              EXECUTE 'INSERT INTO counter_cache (name, count) VALUES (''status_visibility_' || activity_visibility(NEW.actor, NEW.recipients, NEW.data) || ''', 1) ON CONFLICT (name) DO UPDATE SET count = counter_cache.count + 1';
  26              EXECUTE 'update counter_cache SET count = counter_cache.count - 1 where count > 0 and name = ''status_visibility_' || activity_visibility(OLD.actor, OLD.recipients, OLD.data) || ''';';
  27           END IF;
  28           RETURN NEW;
  29       ELSIF TG_OP = 'DELETE' THEN
  30           IF OLD.data->>'type' = 'Create' THEN
  31             EXECUTE 'update counter_cache SET count = counter_cache.count - 1 where count > 0 and name = ''status_visibility_' || activity_visibility(OLD.actor, OLD.recipients, OLD.data) || ''';';
  32           END IF;
  33           RETURN OLD;
  34       END IF;
  35       END;
  36     $$
  37     LANGUAGE 'plpgsql';
  38     """
  39     |> execute()
  40
  41     """
  42     CREATE TRIGGER status_visibility_counter_cache_trigger BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities
  43     FOR EACH ROW
  44     EXECUTE PROCEDURE update_status_visibility_counter_cache();
  45     """
  46     |> execute()
  47   end
  48
  49   def down do
  50     execute("drop trigger if exists status_visibility_counter_cache_trigger on activities")
  51     execute("drop function if exists update_status_visibility_counter_cache()")
  52     drop_if_exists(unique_index(:counter_cache, [:name]))
  53     drop_if_exists(table(:counter_cache))
  54   end
  55 end