[Issue 82]: Make title unique on submission list column

[wwelling]

Submission list column title and predicate are derived without referential integrity from organization workflow field profile gloss and field profile field predicate value.

Here is a sql script to rename all duplicate title to include parenthetical predicate:

WITH submission_list_column_duplicates AS (
    SELECT
        id,
        title,
        predicate,
        COUNT(*) OVER (PARTITION BY title) AS duplicate_count
    FROM
        submission_list_column
)
UPDATE
    submission_list_column slc
SET
    title = CONCAT(slcd.title, ' (', slcd.predicate, ')')
FROM
    submission_list_column_duplicates slcd
WHERE
    slc.id = slcd.id
    AND slcd.duplicate_count > 1;

The database currently has duplicate title Major and ORCID. With the above script they will be updated as follows.

ORCID (dc.creator.orcid)
Major (thesis.degree.discipline)
ORCID (local.etdauthor.orcid)
Major (thesis.degree.major)

The Graduate School and Honors College college organizations under Texas A&M University organization both use thesis.degree.discipline for Major and dc.creator.orcid for ORCID.

Here is a migration script to remove default submission list columns not used:

WITH submissions AS (
  SELECT * FROM submission_list_column WHERE (title = 'ORCID' AND predicate = 'dc.creator.orcid') OR 
  (title = 'Major' AND predicate = 'thesis.degree.major')
)
DELETE FROM submission_list_column_value_path WHERE submission_list_column_id IN (SELECT id FROM submissions);

WITH submissions AS (
  SELECT * FROM submission_list_column WHERE (title = 'ORCID' AND predicate = 'dc.creator.orcid') OR 
  (title = 'Major' AND predicate = 'thesis.degree.major')
)
DELETE FROM weaver_users_submission_view_columns WHERE submission_view_columns_id IN (SELECT id FROM submissions);

WITH submissions AS (
  SELECT * FROM submission_list_column WHERE (title = 'ORCID' AND predicate = 'dc.creator.orcid') OR 
  (title = 'Major' AND predicate = 'thesis.degree.major')
)
DELETE FROM submission_list_column WHERE id IN (SELECT id FROM submissions);

Here is a migration script to update existing organization workflow field profiles to use TAMU custom predicates:

WITH field_predicates AS (
    SELECT * FROM field_predicate WHERE value IN ('thesis.degree.major', 'local.etdauthor.orcid')
),
field_profiles AS (
    SELECT * FROM abstract_field_profile WHERE field_predicate_id IN (SELECT id FROM field_predicates)
)
DELETE FROM workflow_step_aggregate_field_profiles WHERE aggregate_field_profiles_id IN (SELECT id FROM field_profiles);

WITH field_predicates AS (
    SELECT * FROM field_predicate WHERE value IN ('thesis.degree.major', 'local.etdauthor.orcid')
)
DELETE FROM abstract_field_profile WHERE field_predicate_id IN (SELECT id FROM field_predicates);

DELETE FROM field_predicate WHERE value IN ('thesis.degree.major', 'local.etdauthor.orcid');

UPDATE field_predicate SET value = 'local.etdauthor.orcid' WHERE value = 'dc.creator.orcid';

Here is a migration script to update the unique constraint on the table:

DO $$
DECLARE
    v_constraint_name TEXT;
BEGIN
    WITH constraint_info AS (
        SELECT
            conname AS constraint_name,
            conrelid::regclass AS table_name,
            ARRAY(SELECT a.attname
                  FROM unnest(conkey) AS k
                  JOIN pg_attribute AS a ON a.attnum = k AND a.attrelid = conrelid) AS column_names
        FROM
            pg_constraint
        WHERE
            conrelid = 'submission_list_column'::regclass
    ),
    constraints AS (
        SELECT
            ci.constraint_name,
            ci.table_name
        FROM
            constraint_info ci
        WHERE
            ci.column_names::text[] @> ARRAY['title', 'predicate', 'input_type_id']::text[]
    )
    SELECT constraint_name INTO v_constraint_name FROM constraints;

    IF v_constraint_name IS NOT NULL THEN
        EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I', 'submission_list_column', v_constraint_name);
    END IF;

    EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I UNIQUE (%I)', 'submission_list_column', v_constraint_name, 'title');
END $$;