1 defmodule Pleroma.Repo.Migrations.UpdateCounterCacheTable do
4 @function_name "update_status_visibility_counter_cache"
5 @trigger_name "status_visibility_counter_cache_trigger"
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))
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)
21 create_if_not_exists(unique_index(:counter_cache, [:instance]))
24 CREATE OR REPLACE FUNCTION #{@function_name}()
29 hostname character varying(255);
30 visibility_new character varying(64);
31 visibility_old character varying(64);
32 actor character varying(255);
34 SELECT "tokid" INTO "token_id" FROM ts_token_type('default') WHERE "alias" = 'host';
35 IF TG_OP = 'DELETE' THEN
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);
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)
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)
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)
84 execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
87 CREATE TRIGGER #{@trigger_name}
90 OR UPDATE of recipients, data
94 EXECUTE PROCEDURE #{@function_name}();
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))
105 create_if_not_exists table(:counter_cache) do
106 add(:name, :string, null: false)
107 add(:count, :bigint, null: false, default: 0)
110 create_if_not_exists(unique_index(:counter_cache, [:name]))
113 CREATE OR REPLACE FUNCTION #{@function_name}()
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';
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) || ''';';
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) || ''';';
142 CREATE TRIGGER #{@trigger_name} BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities
144 EXECUTE PROCEDURE #{@function_name}();