Misskey SPAM statistics on database

misskey-spam.SQL

BEGIN TRANSACTION READ WRITE;

-- write helper functions to yume schema
CREATE schema IF NOT EXISTS yume;

-- is default ID
CREATE OR REPLACE FUNCTION yume.is_default_name(text) RETURNS boolean AS $$
SELECT $1 ~ '^[0-9a-z]{10}$' $$ LANGUAGE SQL IMMUTABLE LEAKPROOF;

-- base36 to int
CREATE OR REPLACE FUNCTION yume.base36_to_int(text) RETURNS bigint AS $$
DECLARE result bigint := 0;
c char;
cd int;
len int;
BEGIN 
    len := length($1);
    FOR i IN 1..len LOOP 
        c := substring($1 FROM i FOR 1);
        cd := ascii(c);
        IF cd >= ASCII('0') AND cd <= ASCII('9') 
            THEN result := result * 36 + cd - ASCII('0');
            ELSE 
                IF cd >= ASCII('a') AND cd <= ASCII('z') 
                    THEN result := result * 36 + cd - ASCII('a') + 10;
                    ELSE RAISE EXCEPTION 'Invalid character in base36 string: %', c;
                END IF;
        END IF;
    END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE LEAKPROOF;

-- AIDX index to millis from 2000-01-01
CREATE OR REPLACE FUNCTION yume.aidx_millis(text) RETURNS bigint AS $$
SELECT yume.base36_to_int(SUBSTRING($1 FROM 1 FOR 8));
$$ LANGUAGE SQL IMMUTABLE LEAKPROOF COST 0.5;

-- AIDX index to timestamp
CREATE OR REPLACE FUNCTION yume.aidx_time(text) RETURNS timestamp AS $$
SELECT make_timestamptz(2000, 1, 1, 0, 0, 0, 'UTC') + 
    yume.aidx_millis($1) / 1000 * interval '1 second' +
    yume.aidx_millis($1) % 1000 * interval '1 millisecond';
$$ LANGUAGE SQL IMMUTABLE LEAKPROOF COST 1;

-- hostname last component
CREATE OR REPLACE FUNCTION yume.host_last_component(text) RETURNS text AS $$
SELECT substring((select regexp_matches($1, '\.[a-z]{2,}$'))[1] FROM 2) LIMIT 1;
$$ LANGUAGE SQL IMMUTABLE LEAKPROOF;

-- TLDS with more than 1 component
CREATE TABLE IF NOT EXISTS yume.tld_overrides (
    tld text PRIMARY KEY NOT NULL
);

INSERT INTO yume.tld_overrides(tld) VALUES ('.ne.jp') ON CONFLICT DO NOTHING;
INSERT INTO yume.tld_overrides(tld) VALUES ('.co.jp') ON CONFLICT DO NOTHING;
INSERT INTO yume.tld_overrides(tld) VALUES ('.or.jp') ON CONFLICT DO NOTHING;

-- convert hostname to TLD
CREATE OR REPLACE FUNCTION yume.get_tld(text) RETURNS text AS $$
WITH overridden AS (
    SELECT tld FROM yume.tld_overrides WHERE substring($1 FROM length($1) - length(tld) + 1) = tld
)
SELECT
    CASE
        WHEN EXISTS(SELECT * FROM overridden) THEN (SELECT tld FROM overridden)
        ELSE '.' || yume.host_last_component($1)
    END
END;
$$ LANGUAGE SQL STABLE LEAKPROOF;

COMMIT;


BEGIN TRANSACTION READ ONLY;

-- actually query, to save to csv wrap the query without the last semicolon in '\copy ( ... ) TO '/tmp/spam.csv' WITH (FORMAT CSV, HEADER);

WITH typed AS (
    SELECT
        *,
        (SELECT 
            CASE 
                WHEN
                    cw IS NULL AND
                    TEXT ~ '^(@[a-zA-Z0-9_]+@[a-zA-Z0-9_.]+\s*)+$' AND
                    array_length(note."attachedFileTypes", 1) = 1 
                THEN 'qr'
                WHEN 
                    cw IS NULL AND
                    TEXT ~ 'サイトに核を飛ばすことです！' AND
                    json_array_length(note."mentionedRemoteUsers"::json) >= 5 AND
                    array_length(note."attachedFileTypes", 1) IS NULL
                THEN 'lawyer'
                ELSE NULL
            END
        ) as ty
    FROM note
)
SELECT  
    count(*),
    ty,
    "user".host as src_host,
    yume.get_tld("user".host) as src_tld,
    date_trunc('hour', yume.aidx_time(typed."id")) AS utc_hour,
    json_array_length(typed."mentionedRemoteUsers"::json) AS mentioned
    FROM typed
    INNER JOIN "user" ON "user".id = typed."userId"
    WHERE 
        ty IS NOT NULL AND
        yume.is_default_name("user".username) AND 
        "user".name IS NULL
    GROUP BY utc_hour, ty, mentioned, "user".host
    ORDER BY count DESC;
ROLLBACK;

plot-spam.R

library(tidyverse)
library(cowplot)
library(colorspace)

spam <- read_csv("spam.csv") %>%
    mutate(time = as_datetime(utc_hour)) %>%
    group_by(src_host, ty, time) %>%
    summarize(total_mention = sum(count * (mentioned + 1))) %>%
    group_by(src_host) %>%
    mutate(src_host_count = sum(total_mention)) %>%
    ungroup() %>%
    mutate(src_prop = src_host_count / sum(total_mention)) %>%
    mutate(src_prop_disp = map_chr(src_prop * 100, ~sprintf("%.2f%%", .x))) %>%
    mutate(src_host = str_glue("{src_host} ({src_host_count}, {src_prop_disp})")) %>%
    mutate(src_host = fct_reorder(src_host, -src_host_count))

spam %>% 
    ggplot(aes(x=time, y=total_mention, fill=src_host)) +
    geom_col(position="dodge") +
    scale_x_datetime(date_labels="%Y-%m-%d %H:%M", date_breaks="1 hour") +
    scale_y_log10() +
    scale_fill_discrete_qualitative(palette = "Set3") +
    facet_wrap(~ty, ncol = 1, scale = "free_x") +
    theme_cowplot(font_size = 18) +
    theme(axis.text.x = element_text(angle = 45, hjust = 1)) + 
    guides(fill = guide_legend(ncol = 1)) +
    labs(
        title = "Spam I know on mi.yumechi.jp",
        x = "Hour",
        y = "n_post * (n_mention + 1)",
        fill = "host"
    )



ggsave("spam.png", width=18, height=14, dpi=300, bg="white")
 

spam-yumechi-no-kuni.csv