138 lines
4.1 KiB
SQL
138 lines
4.1 KiB
SQL
ALTER TABLE app.candidate_education DROP CONSTRAINT IF EXISTS ai_data_requires_document;
|
|
ALTER TABLE app.candidate_license DROP CONSTRAINT IF EXISTS ai_data_requires_document;
|
|
ALTER TABLE app.candidate_language DROP CONSTRAINT IF EXISTS ai_data_requires_document;
|
|
ALTER TABLE app.candidate_experience DROP CONSTRAINT IF EXISTS ai_data_requires_document;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM app.candidate_education
|
|
GROUP BY candidate_id
|
|
HAVING COUNT(*) > 1
|
|
) THEN
|
|
RAISE EXCEPTION 'Manual migration required: candidate_education still has multiple rows per candidate.';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM app.candidate_license
|
|
GROUP BY candidate_id
|
|
HAVING COUNT(*) > 1
|
|
) THEN
|
|
RAISE EXCEPTION 'Manual migration required: candidate_license still has multiple rows per candidate.';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM app.candidate_language
|
|
GROUP BY candidate_id
|
|
HAVING COUNT(*) > 1
|
|
) THEN
|
|
RAISE EXCEPTION 'Manual migration required: candidate_language still has multiple rows per candidate.';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM app.candidate_experience
|
|
GROUP BY candidate_id
|
|
HAVING COUNT(*) > 1
|
|
) THEN
|
|
RAISE EXCEPTION 'Manual migration required: candidate_experience still has multiple rows per candidate.';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM app.candidate_document
|
|
WHERE category = 'transcript'::app.candidate_document_type
|
|
) THEN
|
|
RAISE EXCEPTION 'Manual migration required: transcript documents must be re-categorized before applying schema contract.';
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'candidate_education_candidate_id_key'
|
|
) THEN
|
|
ALTER TABLE app.candidate_education
|
|
ADD CONSTRAINT candidate_education_candidate_id_key UNIQUE (candidate_id);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'candidate_license_candidate_id_key'
|
|
) THEN
|
|
ALTER TABLE app.candidate_license
|
|
ADD CONSTRAINT candidate_license_candidate_id_key UNIQUE (candidate_id);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'candidate_language_candidate_id_key'
|
|
) THEN
|
|
ALTER TABLE app.candidate_language
|
|
ADD CONSTRAINT candidate_language_candidate_id_key UNIQUE (candidate_id);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'candidate_experience_candidate_id_key'
|
|
) THEN
|
|
ALTER TABLE app.candidate_experience
|
|
ADD CONSTRAINT candidate_experience_candidate_id_key UNIQUE (candidate_id);
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
ALTER TABLE app.candidate_education
|
|
DROP CONSTRAINT IF EXISTS candidate_education_source_document_id_fkey,
|
|
DROP COLUMN IF EXISTS source_document_id;
|
|
|
|
ALTER TABLE app.candidate_license
|
|
DROP CONSTRAINT IF EXISTS candidate_license_source_document_id_fkey,
|
|
DROP COLUMN IF EXISTS source_document_id;
|
|
|
|
ALTER TABLE app.candidate_language
|
|
DROP CONSTRAINT IF EXISTS candidate_language_source_document_id_fkey,
|
|
DROP COLUMN IF EXISTS source_document_id;
|
|
|
|
ALTER TABLE app.candidate_experience
|
|
DROP CONSTRAINT IF EXISTS candidate_experience_source_document_id_fkey,
|
|
DROP COLUMN IF EXISTS source_document_id;
|
|
|
|
DROP TABLE IF EXISTS app.candidate_transcript CASCADE;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM pg_type
|
|
WHERE typnamespace = 'app'::regnamespace
|
|
AND typname = 'candidate_document_type_v2'
|
|
) THEN
|
|
DROP TYPE app.candidate_document_type_v2;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TYPE app.candidate_document_type_v2 AS ENUM (
|
|
'education',
|
|
'license',
|
|
'language',
|
|
'work_experience'
|
|
);
|
|
|
|
ALTER TABLE app.candidate_document
|
|
ALTER COLUMN category TYPE app.candidate_document_type_v2
|
|
USING category::text::app.candidate_document_type_v2;
|
|
|
|
DROP TYPE app.candidate_document_type;
|
|
ALTER TYPE app.candidate_document_type_v2 RENAME TO candidate_document_type;
|