Git of dcc - anni/blob - priv/repo/migrations/20200508092434_update_counter_cache_table.exs

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