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         token_id smallint;
  29         hostname character varying(255);
  30         visibility_new character varying(64);
  31         visibility_old character varying(64);
  32         actor character varying(255);
  33       BEGIN
  34       SELECT "tokid" INTO "token_id" FROM ts_token_type('default') WHERE "alias" = 'host';
  35       IF TG_OP = 'DELETE' THEN
  36         actor := OLD.actor;
  37       ELSE
  38         actor := NEW.actor;
  39       END IF;
  40       SELECT "token" INTO "hostname" FROM ts_parse('default', actor) WHERE "tokid" = token_id;
  41       IF hostname IS NULL THEN
  42         hostname := split_part(actor, '/', 3);
  43       END IF;
  44       IF TG_OP = 'INSERT' THEN
  45         visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
  46         IF NEW.data->>'type' = 'Create'
  47             AND visibility_new IN ('public', 'unlisted', 'private', 'direct') THEN
  48           EXECUTE format('INSERT INTO "counter_cache" ("instance", %1$I) VALUES ($1, 1)
  49                           ON CONFLICT ("instance") DO
  50                           UPDATE SET %1$I = "counter_cache".%1$I + 1', visibility_new)
  51                           USING hostname;
  52         END IF;
  53         RETURN NEW;
  54       ELSIF TG_OP = 'UPDATE' THEN
  55         visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
  56         visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
  57         IF (NEW.data->>'type' = 'Create')
  58             AND (OLD.data->>'type' = 'Create')
  59             AND visibility_new != visibility_old
  60             AND visibility_new IN ('public', 'unlisted', 'private', 'direct') THEN
  61           EXECUTE format('UPDATE "counter_cache" SET
  62                           %1$I = greatest("counter_cache".%1$I - 1, 0),
  63                           %2$I = "counter_cache".%2$I + 1
  64                           WHERE "instance" = $1', visibility_old, visibility_new)
  65                           USING hostname;
  66         END IF;
  67         RETURN NEW;
  68       ELSIF TG_OP = 'DELETE' THEN
  69         IF OLD.data->>'type' = 'Create' THEN
  70           visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
  71           EXECUTE format('UPDATE "counter_cache" SET
  72                           %1$I = greatest("counter_cache".%1$I - 1, 0)
  73                           WHERE "instance" = $1', visibility_old)
  74                           USING hostname;
  75         END IF;
  76         RETURN OLD;
  77       END IF;
  78       END;
  79     $$
  80     LANGUAGE 'plpgsql';
  81     """
  82     |> execute()
  83
  84     execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
  85
  86     """
  87     CREATE TRIGGER #{@trigger_name}
  88     BEFORE
  89       INSERT
  90       OR UPDATE of recipients, data
  91       OR DELETE
  92     ON activities
  93     FOR EACH ROW
  94       EXECUTE PROCEDURE #{@function_name}();
  95     """
  96     |> execute()
  97   end
  98
  99   def down do
 100     execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
 101     execute("DROP FUNCTION IF EXISTS #{@function_name}()")
 102     drop_if_exists(unique_index(:counter_cache, [:instance]))
 103     drop_if_exists(table(:counter_cache))
 104
 105     create_if_not_exists table(:counter_cache) do
 106       add(:name, :string, null: false)
 107       add(:count, :bigint, null: false, default: 0)
 108     end
 109
 110     create_if_not_exists(unique_index(:counter_cache, [:name]))
 111
 112     """
 113     CREATE OR REPLACE FUNCTION #{@function_name}()
 114     RETURNS TRIGGER AS
 115     $$
 116       DECLARE
 117       BEGIN
 118       IF TG_OP = 'INSERT' THEN
 119           IF NEW.data->>'type' = 'Create' 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           END IF;
 122           RETURN NEW;
 123       ELSIF TG_OP = 'UPDATE' THEN
 124           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
 125              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';
 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 NEW;
 129       ELSIF TG_OP = 'DELETE' THEN
 130           IF OLD.data->>'type' = 'Create' THEN
 131             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) || ''';';
 132           END IF;
 133           RETURN OLD;
 134       END IF;
 135       END;
 136     $$
 137     LANGUAGE 'plpgsql';
 138     """
 139     |> execute()
 140
 141     """
 142     CREATE TRIGGER #{@trigger_name} BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities
 143     FOR EACH ROW
 144     EXECUTE PROCEDURE #{@function_name}();
 145     """
 146     |> execute()
 147   end
 148 end