ActivityPub/src/db.ts at f906b3b817726af29a68f3dd15723d2eb49331aa · TryGhost/ActivityPub · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
import Knex from 'knex';

export const client = Knex({
    client: 'mysql2',
    connection: process.env.MYSQL_SOCKET_PATH
        ? {
              socketPath: process.env.MYSQL_SOCKET_PATH,
              user: process.env.MYSQL_USER,
              password: process.env.MYSQL_PASSWORD,
              database: process.env.MYSQL_DATABASE,
              timezone: '+00:00',
          }
        : {
              host: process.env.MYSQL_HOST,
              port: Number.parseInt(process.env.MYSQL_PORT!),
              user: process.env.MYSQL_USER,
              password: process.env.MYSQL_PASSWORD,
              database: process.env.MYSQL_DATABASE,
              timezone: '+00:00',
          },
    pool: {
        min: 1,
        max: 200,
    },
});

interface ActivityJsonLd {
    [key: string]: any;
}

/**
 * @deprecated Do not use this function. Instead, resolve the post via the
 * post repository and use the `replyCount` property
 */
export async function getActivityChildrenCount(activity: ActivityJsonLd) {
    const objectId = activity.object.id;

    const result = await client
        .count('* as count')
        .from('key_value')
        .where(function () {
            // If inReplyTo is a string
            this.where(
                client.raw(
                    `JSON_EXTRACT(value, "$.object.inReplyTo") = "${objectId}"`,
                ),
            );

            // If inReplyTo is an object
            this.orWhere(
                client.raw(
                    `JSON_EXTRACT(value, "$.object.inReplyTo.id") = "${objectId}"`,
                ),
            );
        })
        .andWhere(client.raw(`JSON_EXTRACT(value, "$.type") = "Create"`));

    return result[0].count;
}

/**
 * @deprecated Do not use this function. Instead, resolve the post via the
 * post repository and use the `repostCount` property
 */
export async function getRepostCount(activity: ActivityJsonLd) {
    const objectId = activity.object.id;

    const result = await client
        .count('* as count')
        .from('key_value')
        .where(function () {
            this.where(
                client.raw(
                    `JSON_EXTRACT(value, "$.object.id") = "${objectId}"`,
                ),
            );
        })
        .andWhere(client.raw(`JSON_EXTRACT(value, "$.type") = "Announce"`));

    return result[0].count;
}

export async function getRelatedActivities(
    postUrl: string,
): Promise<{ id: string }[]> {
    return client
        .select(client.raw('JSON_EXTRACT(value, "$.id") as id'))
        .from('key_value')
        .where(function () {
            this.where(
                client.raw('JSON_EXTRACT(value, "$.object.id") = ?', [postUrl]),
            )
                .orWhere(
                    client.raw('JSON_EXTRACT(value, "$.object") = ?', [
                        postUrl,
                    ]),
                )
                .orWhere(
                    client.raw('JSON_EXTRACT(value, "$.id") = ?', [postUrl]),
                );
        }) as unknown as Promise<{ id: string }[]>;
}