otrs_anonymize.sql

BEGIN;
create schema if not exists random;

create temp table word_copy(word TEXT);
COPY word_copy from '/usr/share/dict/ngerman' ENCODING 'UTF8';

create table random.words(id BIGSERIAL PRIMARY KEY, word TEXT);
insert into random.words(word) SELECT word from word_copy;


create or replace function random.random_ids(
    id_count INTEGER, max_num BIGINT, OUT id BIGINT) RETURNS SETOF BIGINT AS $$
DECLARE
BEGIN
    while (id_count > 0) LOOP
        SELECT INTO id ceil(random() * max_num)::BIGINT;
        RETURN NEXT;
        id_count := id_count - 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;


create or replace function random.random_text_generator(
    words INTEGER, sentences INTEGER, paragraphs INTEGER, OUT randomtext TEXT
) AS $$
DECLARE
    t_todo INTEGER;
    t_text TEXT;
    t_sentence_begin TEXT;
    t_count BIGINT;
    t_max BIGINT;
BEGIN
    randomtext := '';

    IF (paragraphs > 0) THEN
        t_todo := paragraphs;
        WHILE (t_todo > 0) LOOP
            t_todo := t_todo - 1;
            randomtext := randomtext || random.random_text_generator(
                words,
                ceil(random() * sentences)::INTEGER,
                0
            );
            IF (t_todo > 0) THEN
                randomtext := randomtext || E'\n\n';
            END IF;
        END LOOP;
    ELSIF (sentences > 0) THEN
        t_todo := sentences;
        WHILE (t_todo > 0) LOOP
            t_todo := t_todo - 1;
            t_text := random.random_text_generator(
                ceil(random() * words)::INTEGER,
                0,
                0
            );

            t_sentence_begin := upper(substring(t_text from 1 for 1));
            t_text := regexp_replace(t_text, '^.', t_sentence_begin);

            randomtext := randomtext || t_text || '.';

            IF (t_todo > 0) THEN
                randomtext := randomtext || ' ';
            END IF;
        END LOOP;
    ELSE
        SELECT INTO t_max max(id) FROM random.words;
        t_todo := words;
        IF (t_todo > 0) THEN
            SELECT
                INTO t_text
                string_agg(w.word, ' ')
            FROM
                (
                    select
                        word as word
                    from
                        random.words
                    WHERE 
                        id in (
                            SELECT id FROM random.random_ids(t_todo, t_max)
                        )
                ) AS w;

            randomtext := randomtext || t_text;
        END IF;
    END IF;

END;
$$ LANGUAGE plpgsql VOLATILE;


SET search_path = otrs, pg_catalog;

UPDATE
    article
SET
    a_body = random.random_text_generator(20, 10, ceil(5*random())::INTEGER),
    a_subject = random.random_text_generator(ceil(20*random())::INTEGER, 0, 0)
;


UPDATE
    ticket
SET
    title = substring(random.random_text_generator(ceil(15*random())::INTEGER, 0, 0) from 1 for 200)
;


UPDATE
    article
SET
    a_from = translate(a_from,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa'),
    a_reply_to = translate(a_reply_to,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa'),
    a_to = translate(a_to,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa'),
    a_cc = translate(a_cc,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa'),
    a_in_reply_to = translate(a_in_reply_to,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa'),
    a_references = translate(a_references,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa'),
    a_message_id = translate(a_message_id,
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC0123456789',
        'axklSDKASDLlsdASDSLls5dv6ncm7zxp80w1ieSS2SDK3KAA4wwuz0cllooKSDa')
;

UPDATE
    article
SET
    a_message_id_md5 = md5(a_message_id);
;

UPDATE
    article_attachment
SET
    filename = 'file-' || article_id::TEXT || '-' || id::TEXT || '.txt',
    content_size = length(r.randomtext),
    content = encode(convert_to(r.randomtext, 'UTF8'), 'base64'),
    content_type = 'text/plain; charset=utf8'
FROM
    random.random_text_generator(20, 10, ceil(5*random())::INTEGER) r
;



COMMIT;