git.squeep.com Git - akkoma/blob - priv/repo/migrations/20200508092434_update_counter_cache_table.exs

   1 defmodule Pleroma.Repo.Migrations.UpdateCounterCacheTable do
   2   use Ecto.Migration
   3
   4   @function_name "update_status_visibility_counter_cache"
   5   @trigger_name "status_visibility_counter_cache_trigger"
   6
   7   def up do
   8     execute("drop trigger if exists #{@trigger_name} on activities")
   9     execute("drop function if exists #{@function_name}()")
  10     drop_if_exists(unique_index(:counter_cache, [:name]))
  11     drop_if_exists(table(:counter_cache))
  12
  13     create_if_not_exists table(:counter_cache) do
  14       add(:instance, :string, null: false)
  15       add(:direct, :bigint, null: false, default: 0)
  16       add(:private, :bigint, null: false, default: 0)
  17       add(:unlisted, :bigint, null: false, default: 0)
  18       add(:public, :bigint, null: false, default: 0)
  19     end
  20
  21     create_if_not_exists(unique_index(:counter_cache, [:instance]))
  22
  23     """
  24     CREATE OR REPLACE FUNCTION #{@function_name}()
  25     RETURNS TRIGGER AS
  26     $$
  27       DECLARE
  28         hostname character varying(255);
  29         visibility_new character varying(64);
  30         visibility_old character varying(64);
  31         actor character varying(255);
  32       BEGIN
  33       IF TG_OP = 'DELETE' THEN
  34         actor := OLD.actor;
  35       ELSE
  36         actor := NEW.actor;
  37       END IF;
  38       hostname := split_part(actor, '/', 3);
  39       IF TG_OP = 'INSERT' THEN
  40         visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
  41         IF NEW.data->>'type' = 'Create'
  42             AND visibility_new IN ('public', 'unlisted', 'private', 'direct') THEN
  43           EXECUTE format('INSERT INTO "counter_cache" ("instance", %1$I) VALUES ($1, 1)
  44                           ON CONFLICT ("instance") DO
  45                           UPDATE SET %1$I = "counter_cache".%1$I + 1', visibility_new)
  46                           USING hostname;
  47         END IF;
  48         RETURN NEW;
  49       ELSIF TG_OP = 'UPDATE' THEN
  50         visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
  51         visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
  52         IF (NEW.data->>'type' = 'Create')
  53             AND (OLD.data->>'type' = 'Create')
  54             AND visibility_new != visibility_old
  55             AND visibility_new IN ('public', 'unlisted', 'private', 'direct') THEN
  56           EXECUTE format('UPDATE "counter_cache" SET
  57                           %1$I = greatest("counter_cache".%1$I - 1, 0),
  58                           %2$I = "counter_cache".%2$I + 1
  59                           WHERE "instance" = $1', visibility_old, visibility_new)
  60                           USING hostname;
  61         END IF;
  62         RETURN NEW;
  63       ELSIF TG_OP = 'DELETE' THEN
  64         IF OLD.data->>'type' = 'Create' THEN
  65           visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
  66           EXECUTE format('UPDATE "counter_cache" SET
  67                           %1$I = greatest("counter_cache".%1$I - 1, 0)
  68                           WHERE "instance" = $1', visibility_old)
  69                           USING hostname;
  70         END IF;
  71         RETURN OLD;
  72       END IF;
  73       END;
  74     $$
  75     LANGUAGE 'plpgsql';
  76     """
  77     |> execute()
  78
  79     execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
  80
  81     """
  82     CREATE TRIGGER #{@trigger_name}
  83     BEFORE
  84       INSERT
  85       OR UPDATE of recipients, data
  86       OR DELETE
  87     ON activities
  88     FOR EACH ROW
  89       EXECUTE PROCEDURE #{@function_name}();
  90     """
  91     |> execute()
  92   end
  93
  94   def down do
  95     execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
  96     execute("DROP FUNCTION IF EXISTS #{@function_name}()")
  97     drop_if_exists(unique_index(:counter_cache, [:instance]))
  98     drop_if_exists(table(:counter_cache))
  99
 100     create_if_not_exists table(:counter_cache) do
 101       add(:name, :string, null: false)
 102       add(:count, :bigint, null: false, default: 0)
 103     end
 104
 105     create_if_not_exists(unique_index(:counter_cache, [:name]))
 106
 107     """
 108     CREATE OR REPLACE FUNCTION #{@function_name}()
 109     RETURNS TRIGGER AS
 110     $$
 111       DECLARE
 112       BEGIN
 113       IF TG_OP = 'INSERT' THEN
 114           IF NEW.data->>'type' = 'Create' THEN
 115             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';
 116           END IF;
 117           RETURN NEW;
 118       ELSIF TG_OP = 'UPDATE' THEN
 119           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
 120              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';
 121              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) || ''';';
 122           END IF;
 123           RETURN NEW;
 124       ELSIF TG_OP = 'DELETE' THEN
 125           IF OLD.data->>'type' = 'Create' THEN
 126             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) || ''';';
 127           END IF;
 128           RETURN OLD;
 129       END IF;
 130       END;
 131     $$
 132     LANGUAGE 'plpgsql';
 133     """
 134     |> execute()
 135
 136     """
 137     CREATE TRIGGER #{@trigger_name} BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities
 138     FOR EACH ROW
 139     EXECUTE PROCEDURE #{@function_name}();
 140     """
 141     |> execute()
 142   end
 143 end