db_triggers.py

# Generated by Django 2.2.8 on 2020-07-10 13:12

from django.db import migrations


def reverse(apps, schema_editor):
    pass


class Migration(migrations.Migration):
    dependencies = [
        ("household", "0011_migration"),
    ]

    operations = [
        migrations.RunSQL(
            "ALTER TABLE household_household ADD unicef_id_index SERIAL"
        ),
        migrations.RunSQL(
            "ALTER TABLE household_individual ADD unicef_id_index SERIAL"
        ),
        migrations.RunSQL(
            """
        CREATE OR REPLACE FUNCTION create_hh_unicef_id() RETURNS trigger
            LANGUAGE plpgsql
            AS $$
        begin
          NEW.unicef_id := format('HH-%s-%s',NEW.country,LPAD(format('%s',NEW.unicef_id_index),8,'0'));
          return NEW;
        end
        $$;

        CREATE TRIGGER create_hh_unicef_id BEFORE INSERT ON household_household FOR EACH ROW EXECUTE PROCEDURE create_hh_unicef_id();
        """,
        ),
        migrations.RunSQL(
            """
        CREATE OR REPLACE FUNCTION create_ii_unicef_id() RETURNS trigger
            LANGUAGE plpgsql
            AS $$
        begin
          NEW.unicef_id := format('IND-%s-%s',TO_CHAR(NEW.first_registration_date, 'yy'), LPAD(format('%s',NEW.unicef_id_index),8,'0'));
          return NEW;
        end
        $$;

        CREATE TRIGGER create_ii_unicef_id BEFORE INSERT ON household_individual FOR EACH ROW EXECUTE PROCEDURE create_ii_unicef_id();
        """,
        ),
        migrations.RunSQL(
            "UPDATE household_household SET unicef_id = format('HH-%s-%s',country ,LPAD(format('%s',unicef_id_index),8,'0'))"
        ),
        migrations.RunSQL(
            "UPDATE household_individual SET unicef_id = format('IND-%s-%s',TO_CHAR(first_registration_date, 'yy'), LPAD(format('%s',unicef_id_index),8,'0'))"
        ),
    ]