Store question admin names as key format, instead of rewriting them at every callsite

[avaleske]

Currently the question admin name is written as a string with spaces, but then everywhere(?) we use it we use the key form, which has everything stripped and spaces converted to underscores.

What if we just enforce this format on creation instead of rewriting every time we want to use it?

See getNameKey() for the current re-writing https://github.com/civiform/civiform/blob/e34a393dfebab8b889a1afe5e2d3fa67ca8e3e18/server/app/services/question/types/QuestionDefinition.java#L98

Question:

• Is there anywhere that admin see the admin name as they typed it? Maybe the CSV?
• Doing this migration might be kind of tricky, since we couldn't change old applications very easily? unless it's done as a durable job.
• The job should get a lock on the DB to prevent it from running at the same time as an API or CSV export

[gwendolyngoetz]

I've never understood why we don't have a slug saved on the questions table like we do with programs. Would make life much easier than having to compute it all the time.

[avaleske]

a starting point from @gwendolyngoetz, would also need to lowercase the identifier

# --- !Ups
alter table if exists questions
add column if not exists admin_name varchar null

update questions
set admin_name = regexp_replace(regexp_replace(name, '[^a-zA-Z ]', '', 'g'), '\s', '_' , 'g')

alter table if exists questions
alter column admin_name set not null;

# --- !Downs
alter table if exists questions
drop column if exists admin_name;