90 lines
5.8 KiB
Python
90 lines
5.8 KiB
Python
"""Convert the supplied baseline to versioned, master-linked SQL. No database writes."""
|
|
import re
|
|
from pathlib import Path
|
|
|
|
root = Path(__file__).resolve().parents[1]
|
|
source = Path(r'C:/Users/macbook/.codex/attachments/8528a9d2-b934-4bb3-a132-a18b7e13414d/Pasted text.txt')
|
|
text = source.read_text(encoding='utf-8-sig')
|
|
parts = re.split(r'5\.10\.[1-6]\. [^\r\n]+\r?\n', text)[1:]
|
|
assert len(parts) == 6
|
|
parts[-1] = parts[-1].split('COMMIT;')[0]
|
|
extensions = {
|
|
'qualification_documents': ('qualification_document_details', 'documents', {'storage_key','original_filename','mime_type','sha256_checksum'}),
|
|
'consents': ('consent_details', 'consents', {'purpose_code','status','granted_at','revoked_at'}),
|
|
'skills': ('skill_details', 'skills', {'code','name_pt'}),
|
|
'occupations': ('occupation_details', 'occupations', {'code','name_pt'}),
|
|
'education_programs': ('program_details', 'courses', {'institution_master_id','code','title_pt','status'}),
|
|
'human_resource_needs': ('human_resource_need_details', 'labour_demands', {'occupation_id','project_master_id','location_master_id','qualification_level','estimated_quantity','needed_from','needed_until','source_name','status'}),
|
|
'educational_opportunities': ('educational_opportunity_details', 'education_investment_proposals', {'title_pt','description_pt','indicative_value','currency','status'}),
|
|
}
|
|
renames = {k:v[0] for k,v in extensions.items()}
|
|
user_columns = ['user_master_id','created_by','updated_by','approved_by','validated_by','verified_by','uploaded_by','reviewed_by','responsible_user_id','actor_user_id','institution_operator_user_id']
|
|
masters = {**{k:('users','bigint') for k in user_columns},
|
|
'sector_master_id':('sectors','bigint'), 'company_master_id':('organizations','uuid'),
|
|
'organisation_master_id':('organizations','uuid'), 'institution_master_id':('training_providers','uuid'),
|
|
'program_master_id':('courses','uuid'), 'project_master_id':('projects','uuid'),
|
|
'cluster_master_id':('economic_clusters','uuid'), 'corridor_master_id':('development_corridors','uuid'),
|
|
'zee_master_id':('investment_zones','uuid'), 'zfi_master_id':('investment_zones','uuid'), 'location_master_id':('geo_locations','uuid')}
|
|
out = root/'database/sql/skills-education'
|
|
out.mkdir(parents=True,exist_ok=True)
|
|
all_tables=[]
|
|
for number, sql in enumerate(parts,1):
|
|
for original,(new,master,omit) in extensions.items():
|
|
pattern = rf'CREATE TABLE skills_edu\.{original} \(\n(.*?)\n\);'
|
|
def adapt(match):
|
|
lines=match.group(1).splitlines()
|
|
result=[]
|
|
for line in lines:
|
|
field=line.strip().split()[0]
|
|
if field in omit: continue
|
|
if original=='human_resource_needs' and 'CHECK (needed_until' in line: continue
|
|
if original=='education_programs' and 'UNIQUE (institution_master_id' in line: continue
|
|
if original=='qualification_documents' and 'UNIQUE (storage_key)' in line: continue
|
|
if original=='consents' and ('CHECK (expires_at' in line or 'CHECK (revoked_at' in line): continue
|
|
if field=='id': line=f' id uuid PRIMARY KEY REFERENCES public.{master}(id) ON DELETE RESTRICT,'
|
|
result.append(line)
|
|
return 'CREATE TABLE skills_edu.'+new+' (\n'+'\n'.join(result).rstrip().rstrip(',')+'\n);'
|
|
sql=re.sub(pattern,adapt,sql,flags=re.S)
|
|
# Foreign keys for shared catalogues always point at the existing masters.
|
|
for original,master in [('skills','skills'),('occupations','occupations'),('education_programs','courses')]:
|
|
sql=sql.replace(f'REFERENCES skills_edu.{original}(id)',f'REFERENCES public.{master}(id)')
|
|
for original,new in renames.items():
|
|
sql=re.sub(rf'skills_edu\.{original}\b',f'skills_edu.{new}',sql)
|
|
sql=re.sub(r'^CREATE INDEX idx_(hr_needs_search|hr_needs_dates|programs_institution_status|opportunities_context).*?;\n','',sql,flags=re.M)
|
|
sql=re.sub(r'^CREATE INDEX idx_(documents_checksum|consents_profile_purpose).*?;\n','',sql,flags=re.M)
|
|
sql=re.sub(r'^COMMENT ON COLUMN skills_edu\.qualification_document_details\.storage_key.*?;\n','',sql,flags=re.M)
|
|
for column,(master,typ) in masters.items():
|
|
sql=re.sub(rf'\b{column} uuid\b', f'{column} {typ}', sql)
|
|
tables=re.findall(r'CREATE TABLE skills_edu\.(\w+)',sql)
|
|
all_tables += tables
|
|
for table in tables:
|
|
body=re.search(rf'CREATE TABLE skills_edu\.{table} \((.*?)\n\);',sql,re.S).group(1)
|
|
for column,(master,typ) in masters.items():
|
|
if re.search(rf'^ {column} ',body,re.M):
|
|
sql+=f'\nALTER TABLE skills_edu.{table} ADD FOREIGN KEY ({column}) REFERENCES public.{master}(id) ON DELETE RESTRICT;\n'
|
|
if number == 3:
|
|
sql += '''
|
|
CREATE FUNCTION skills_edu.validate_consent_dates() RETURNS trigger LANGUAGE plpgsql SET search_path=pg_catalog AS $$
|
|
DECLARE granted timestamptz; subject text;
|
|
BEGIN
|
|
SELECT granted_at,subject_ref INTO granted,subject FROM public.consents WHERE id=NEW.id;
|
|
IF subject IS DISTINCT FROM NEW.profile_id::text OR granted IS NULL OR (NEW.expires_at IS NOT NULL AND NEW.expires_at<=granted) THEN
|
|
RAISE EXCEPTION 'Consent master profile or validity mismatch' USING ERRCODE='23514';
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER validate_consent_master BEFORE INSERT OR UPDATE ON skills_edu.consent_details
|
|
FOR EACH ROW EXECUTE FUNCTION skills_edu.validate_consent_dates();
|
|
'''
|
|
(out/f'{number:02d}.sql').write_text(sql,encoding='utf-8')
|
|
migration=f'''<?php
|
|
|
|
return new class extends \\App\\Support\\SkillsEducationMigration
|
|
{{
|
|
protected int $block = {number};
|
|
protected array $tables = {str(tables).replace('[','[').replace(']',']')};
|
|
}};
|
|
'''
|
|
(root/f'database/migrations/2026_09_30_10{number:02d}00_skills_education_{number}.php').write_text(migration,encoding='utf-8')
|
|
print(f'Prepared {len(parts)} versioned blocks, {len(all_tables)} tables; existing catalogues remain masters.')
|