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' THEN
  47           EXECUTE format('INSERT INTO "counter_cache" ("instance", %1$I) VALUES ($1, 1)
  48                           ON CONFLICT ("instance") DO
  49                           UPDATE SET %1$I = "counter_cache".%1$I + 1', visibility_new)
  50                           USING hostname;
  51         END IF;
  52         RETURN NEW;
  53       ELSIF TG_OP = 'UPDATE' THEN
  54         visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
  55         visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
  56         IF (NEW.data->>'type' = 'Create') and (OLD.data->>'type' = 'Create') and visibility_new != visibility_old THEN
  57           EXECUTE format('UPDATE "counter_cache" SET
  58                           %1$I = greatest("counter_cache".%1$I - 1, 0),
  59                           %2$I = "counter_cache".%2$I + 1
  60                           WHERE "instance" = $1', visibility_old, visibility_new)
  61                           USING hostname;
  62         END IF;
  63         RETURN NEW;
  64       ELSIF TG_OP = 'DELETE' THEN
  65         IF OLD.data->>'type' = 'Create' THEN
  66           visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
  67           EXECUTE format('UPDATE "counter_cache" SET
  68                           %1$I = greatest("counter_cache".%1$I - 1, 0)
  69                           WHERE "instance" = $1', visibility_old)
  70                           USING hostname;
  71         END IF;
  72         RETURN OLD;
  73       END IF;
  74       END;
  75     $$
  76     LANGUAGE 'plpgsql';
  77     """
  78     |> execute()
  79
  80     execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
  81
  82     """
  83     CREATE TRIGGER #{@trigger_name}
  84     BEFORE
  85       INSERT
  86       OR UPDATE of recipients, data
  87       OR DELETE
  88     ON activities
  89     FOR EACH ROW
  90       EXECUTE PROCEDURE #{@function_name}();
  91     """
  92     |> execute()
  93   end
  94
  95   def down do
  96     execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
  97     execute("DROP FUNCTION IF EXISTS #{@function_name}()")
  98     drop_if_exists(unique_index(:counter_cache, [:instance]))
  99     drop_if_exists(table(:counter_cache))
 100
 101     create_if_not_exists table(:counter_cache) do
 102       add(:name, :string, null: false)
 103       add(:count, :bigint, null: false, default: 0)
 104     end
 105
 106     create_if_not_exists(unique_index(:counter_cache, [:name]))
 107
 108     """
 109     CREATE OR REPLACE FUNCTION #{@function_name}()
 110     RETURNS TRIGGER AS
 111     $$
 112       DECLARE
 113       BEGIN
 114       IF TG_OP = 'INSERT' THEN
 115           IF NEW.data->>'type' = 'Create' THEN
 116             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';
 117           END IF;
 118           RETURN NEW;
 119       ELSIF TG_OP = 'UPDATE' THEN
 120           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
 121              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';
 122              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) || ''';';
 123           END IF;
 124           RETURN NEW;
 125       ELSIF TG_OP = 'DELETE' THEN
 126           IF OLD.data->>'type' = 'Create' THEN
 127             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) || ''';';
 128           END IF;
 129           RETURN OLD;
 130       END IF;
 131       END;
 132     $$
 133     LANGUAGE 'plpgsql';
 134     """
 135     |> execute()
 136
 137     """
 138     CREATE TRIGGER #{@trigger_name} BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities
 139     FOR EACH ROW
 140     EXECUTE PROCEDURE #{@function_name}();
 141     """
 142     |> execute()
 143   end
 144 end