commit: 06678fb4ad42fcaecb99eccc2237c3b863a2b9a5
parent 6ccab516a3f62682fe15f8efec36be82acafc155
Author: Tusooa Zhu <tusooa@kazv.moe>
Date: Mon, 11 Jul 2022 14:58:38 -0400
Add function to calculate associated object id
Diffstat:
2 files changed, 111 insertions(+), 0 deletions(-)
diff --git a/priv/repo/migrations/20220711182322_add_associated_object_id_function.exs b/priv/repo/migrations/20220711182322_add_associated_object_id_function.exs
@@ -0,0 +1,37 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.AddAssociatedObjectIdFunction do
+ use Ecto.Migration
+
+ def up do
+ statement = """
+ CREATE OR REPLACE FUNCTION associated_object_id(data jsonb) RETURNS varchar AS $$
+ DECLARE
+ object_data jsonb;
+ BEGIN
+ IF jsonb_typeof(data->'object') = 'array' THEN
+ object_data := data->'object'->0;
+ ELSE
+ object_data := data->'object';
+ END IF;
+
+ IF jsonb_typeof(object_data->'id') = 'string' THEN
+ RETURN object_data->>'id';
+ ELSIF jsonb_typeof(object_data) = 'string' THEN
+ RETURN object_data#>>'{}';
+ ELSE
+ RETURN NULL;
+ END IF;
+ END;
+ $$ LANGUAGE plpgsql IMMUTABLE;
+ """
+
+ execute(statement)
+ end
+
+ def down do
+ execute("DROP FUNCTION IF EXISTS associated_object_id(data jsonb)")
+ end
+end
diff --git a/test/pleroma/activity_test.exs b/test/pleroma/activity_test.exs
@@ -278,4 +278,78 @@ defmodule Pleroma.ActivityTest do
assert Repo.aggregate(Activity, :count, :id) == 2
end
+
+ describe "associated_object_id() sql function" do
+ test "with json object" do
+ %{rows: [[object_id]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ """
+ select associated_object_id('{"object": {"id":"foobar"}}'::jsonb);
+ """
+ )
+
+ assert object_id == "foobar"
+ end
+
+ test "with string object" do
+ %{rows: [[object_id]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ """
+ select associated_object_id('{"object": "foobar"}'::jsonb);
+ """
+ )
+
+ assert object_id == "foobar"
+ end
+
+ test "with array object" do
+ %{rows: [[object_id]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ """
+ select associated_object_id('{"object": ["foobar", {}]}'::jsonb);
+ """
+ )
+
+ assert object_id == "foobar"
+ end
+
+ test "invalid" do
+ %{rows: [[object_id]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ """
+ select associated_object_id('{"object": {}}'::jsonb);
+ """
+ )
+
+ assert is_nil(object_id)
+ end
+
+ test "invalid object id" do
+ %{rows: [[object_id]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ """
+ select associated_object_id('{"object": {"id": 123}}'::jsonb);
+ """
+ )
+
+ assert is_nil(object_id)
+ end
+
+ test "no object field" do
+ %{rows: [[object_id]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ """
+ select associated_object_id('{}'::jsonb);
+ """
+ )
+
+ assert is_nil(object_id)
+ end
+ end
end