<commit_before>CREATE FUNCTION build_form_tables() RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    form_name             TEXT;
    question_name         TEXT;
    questions_per_table   INTEGER := 200;
    i                     INTEGER;
    table_count           INTEGER;
    cols_plus_vals_clause TEXT := '';
    colnames_clause       TEXT := '';
    insert_col_clause     TEXT := '';
    answer_rec            RECORD;
    last_event            INTEGER;
    values_clause         TEXT;
    tmp                   TEXT;
    table_name            TEXT;
BEGIN
    FOR form_name IN SELECT DISTINCT short_name FROM forms WHERE short_name IS NOT NULL AND short_name != '' AND status = 'Published' LOOP
        -- For each form, find all the question short names that appear on it
        i := 0;
        colnames_clause := '';
        cols_plus_vals_clause := '';
        table_count := 1;
        FOR question_name IN SELECT DISTINCT q.short_name
                    FROM questions q JOIN form_elements fe ON fe.id = q.form_element_id
                    JOIN forms f ON f.id = fe.form_id JOIN answers a ON a.question_id = q.id
                    WHERE f.short_name = form_name
                    AND q.short_name IS NOT NULL
                    AND q.short_name != ''
                    LOOP
            -- Get some number of question short names
            IF colnames_clause != '' THEN
                colnames_clause := colnames_clause || ', ';
                cols_plus_vals_clause := cols_plus_vals_clause || ', ';
            END IF;
            colnames_clause := colnames_clause || quote_literal(question_name);
            cols_plus_vals_clause := cols_plus_vals_clause || quote_ident(question_name) || ' TEXT';
            i := i + 1;

            IF i > questions_per_table THEN
                PERFORM create_single_form_table(form_name, table_count, colnames_clause, cols_plus_vals_clause);
                table_count := table_count + 1;
                colnames_clause := '';
                cols_plus_vals_clause := '';
            END IF;
        END LOOP;
        IF colnames_clause != '' THEN
            PERFORM create_single_form_table(form_name, table_count, colnames_clause, cols_plus_vals_clause);
        END IF;
    END LOOP;
END;
$$;


CREATE FUNCTION create_single_form_table(form_name text, table_count integer, colnames_clause text, cols_plus_vals_clause text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    insert_col_clause TEXT;
    values_clause     TEXT;
    last_event        INTEGER;
    tmp               TEXT;
    answer_rec        RECORD;
    table_name        TEXT;
BEGIN
    table_name := quote_ident('formtable_' || lower(form_name) || '_' || table_count);
    insert_col_clause := '';
    values_clause := '';
    last_event := NULL;
    EXECUTE 'DROP TABLE IF EXISTS ' || table_name;
    RAISE NOTICE 'Creating table %', table_name;
    EXECUTE 'CREATE TABLE ' || table_name || ' (event_id INTEGER, ' || cols_plus_vals_clause || ')';
    tmp := 'SELECT a.event_id, q.short_name, a.text_answer
            FROM answers a JOIN questions q ON (q.id = a.question_id)
            JOIN form_elements fe ON (fe.id = q.form_element_id)
            JOIN forms f ON (fe.form_id = f.id)
            WHERE f.short_name = ' || quote_literal(form_name) || '
            AND q.short_name IN (' || colnames_clause || ')
            ORDER BY event_id';
    RAISE NOTICE 'To insert stuff, running %', tmp;
    FOR answer_rec IN EXECUTE tmp LOOP
        IF last_event IS NOT NULL AND last_event != answer_rec.event_id THEN
            tmp := 'INSERT INTO ' || table_name || ' (' || insert_col_clause || ') VALUES (' || values_clause || ')';
            RAISE NOTICE 'Running %', tmp;
            EXECUTE tmp;
            last_event := answer_rec.event_id;
            insert_col_clause := '';
            values_clause := '';
        END IF;

        IF insert_col_clause != '' THEN
            insert_col_clause := insert_col_clause || ', ';
            values_clause := values_clause || ', ';
        END IF;
        insert_col_clause := insert_col_clause || quote_ident(answer_rec.short_name);
        values_clause := values_clause || quote_literal(answer_rec.text_answer);
    END LOOP;
    IF last_event IS NOT NULL THEN
        tmp := 'INSERT INTO ' || table_name || ' (' || insert_col_clause || ') VALUES (' || values_clause || ')';
        RAISE NOTICE 'Running %', tmp;
        EXECUTE tmp;
    END IF;
END;
$$;
<commit_msg>Working formbuilder table generation functions<commit_after>--
-- PostgreSQL database dump
--

CREATE FUNCTION build_form_tables() RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    form_name             TEXT;
    question_name         TEXT;
    questions_per_table   INTEGER := 200;
    i                     INTEGER;
    table_count           INTEGER;
    cols_plus_vals_clause TEXT := '';
    colnames_clause       TEXT := '';
    insert_col_clause     TEXT := '';
    answer_rec            RECORD;
    last_event            INTEGER;
    values_clause         TEXT;
    tmp                   TEXT;
    table_name            TEXT;
BEGIN
    FOR form_name IN SELECT DISTINCT short_name FROM forms WHERE short_name IS NOT NULL AND short_name != '' ORDER BY short_name LOOP
        -- For each form, find all the question short names that appear on it
        RAISE DEBUG 'Processing form name %', form_name;
        i := 0;
        colnames_clause := '';
        cols_plus_vals_clause := '';
        table_count := 1;
        FOR question_name IN SELECT DISTINCT q.short_name
                    FROM questions q JOIN form_elements fe ON fe.id = q.form_element_id
                    JOIN forms f ON f.id = fe.form_id JOIN answers a ON a.question_id = q.id
                    WHERE f.short_name = form_name
                    AND q.short_name IS NOT NULL
                    AND q.short_name != ''
                    LOOP
            RAISE DEBUG ' ** Processing question % in current form', question_name;
            -- Get some number of question short names
            IF colnames_clause != '' THEN
                colnames_clause := colnames_clause || ', ';
                cols_plus_vals_clause := cols_plus_vals_clause || ', ';
            END IF;
            colnames_clause := colnames_clause || quote_literal(question_name);
            cols_plus_vals_clause := cols_plus_vals_clause || quote_ident(question_name) || ' TEXT';
            i := i + 1;

            IF i > questions_per_table THEN
                PERFORM create_single_form_table(form_name, table_count, colnames_clause, cols_plus_vals_clause);
                table_count := table_count + 1;
                colnames_clause := '';
                cols_plus_vals_clause := '';
            END IF;
        END LOOP;
        IF colnames_clause != '' THEN
            PERFORM create_single_form_table(form_name, table_count, colnames_clause, cols_plus_vals_clause);
        END IF;
    END LOOP;
END;
$$;

CREATE FUNCTION create_single_form_table(form_name text, table_count integer, colnames_clause text, cols_plus_vals_clause text) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    insert_col_clause TEXT;
    values_clause     TEXT;
    last_event        INTEGER;
    tmp               TEXT;
    answer_rec        RECORD;
    table_name        TEXT;
BEGIN
    table_name := quote_ident('formtable_' || lower(form_name) || '_' || table_count);
    insert_col_clause := '';
    values_clause := '';
    last_event := NULL;
    EXECUTE 'DROP TABLE IF EXISTS ' || table_name;
    RAISE DEBUG 'Creating table %', table_name;
    EXECUTE 'CREATE TABLE ' || table_name || ' (event_id INTEGER, ' || cols_plus_vals_clause || ')';
    -- Note that there's no ordering with this DISTINCT ON. There's nothing in
    -- the tables now to prevent multiple questions with the same short name
    -- being answered for the same event, so we've got to prevent it here
    tmp := 'SELECT DISTINCT ON (a.event_id, q.short_name) a.event_id, q.short_name, a.text_answer
            FROM answers a JOIN questions q ON (q.id = a.question_id)
            JOIN form_elements fe ON (fe.id = q.form_element_id)
            JOIN forms f ON (fe.form_id = f.id)
            WHERE f.short_name = ' || quote_literal(form_name) || '
            AND q.short_name IN (' || colnames_clause || ')
            ORDER BY event_id';
    FOR answer_rec IN EXECUTE tmp LOOP
        IF last_event IS NOT NULL AND last_event != answer_rec.event_id THEN
            tmp := 'INSERT INTO ' || table_name || ' (' || insert_col_clause || ') VALUES (' || values_clause || ')';
            RAISE DEBUG 'Running %', tmp;
            EXECUTE tmp;
            insert_col_clause := '';
            values_clause := '';
        END IF;
        last_event := answer_rec.event_id;

        IF insert_col_clause != '' THEN
            insert_col_clause := insert_col_clause || ', ';
            values_clause := values_clause || ', ';
        END IF;
        insert_col_clause := insert_col_clause || quote_ident(answer_rec.short_name);
        values_clause := values_clause || quote_literal(answer_rec.text_answer);
    END LOOP;
    IF last_event IS NOT NULL THEN
        tmp := 'INSERT INTO ' || table_name || ' (' || insert_col_clause || ') VALUES (' || values_clause || ')';
        RAISE DEBUG 'Running %', tmp;
        EXECUTE tmp;
    END IF;
END;
$$;

{'fraction_non_alphanumeric': 0.09022957821676456, 'fraction_numerical': 0.0014949279231179925, 'mean_word_length': 2.0982467747270923, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5756366', 'n_tokens_mistral': 2900, 'n_tokens_neox': 2746, 'n_words': 738}