mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
41 lines
1.2 KiB
PL/PgSQL
41 lines
1.2 KiB
PL/PgSQL
|
|
-- Add a short human-readable access code to eligible voters for printed notices
|
|
ALTER TABLE public.election_eligible_voters
|
|
ADD COLUMN IF NOT EXISTS access_code TEXT;
|
|
|
|
-- Create a function to generate a unique 6-char alphanumeric code per election
|
|
CREATE OR REPLACE FUNCTION public.generate_voter_access_code()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = 'public'
|
|
AS $$
|
|
DECLARE
|
|
new_code TEXT;
|
|
code_exists BOOLEAN;
|
|
BEGIN
|
|
LOOP
|
|
new_code := UPPER(SUBSTRING(md5(gen_random_uuid()::text) FROM 1 FOR 6));
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.election_eligible_voters
|
|
WHERE election_id = NEW.election_id AND access_code = new_code
|
|
) INTO code_exists;
|
|
EXIT WHEN NOT code_exists;
|
|
END LOOP;
|
|
NEW.access_code := new_code;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- Auto-generate access codes on insert
|
|
CREATE TRIGGER trg_generate_voter_access_code
|
|
BEFORE INSERT ON public.election_eligible_voters
|
|
FOR EACH ROW
|
|
WHEN (NEW.access_code IS NULL)
|
|
EXECUTE FUNCTION public.generate_voter_access_code();
|
|
|
|
-- Backfill existing voters that have no access code
|
|
UPDATE public.election_eligible_voters
|
|
SET access_code = UPPER(SUBSTRING(md5(gen_random_uuid()::text || id::text) FROM 1 FOR 6))
|
|
WHERE access_code IS NULL;
|