Напреден развој на датабаза (Тригери и Функции, Индекси)

Тригери + Функции

Сите 4 функции имаат и тригер на insert + update во главните табели

• Лимитира терапевтите да можат да креираат термини за консултации исклучиво за тековни или идни датуми, со што се оневозможува внесување термини со поминат датум.

  CREATE OR REPLACE FUNCTION fn_validate_consultation_slots()
      RETURNS trigger
      LANGUAGE plpgsql
  AS $$
  DECLARE
      d date;
  BEGIN
      IF NEW.consultation_slots IS NULL THEN
          RETURN NEW;
      END IF;
  
      FOREACH d IN ARRAY NEW.consultation_slots
          LOOP
              IF d < CURRENT_DATE THEN
                  RAISE EXCEPTION 'consultation_slots contains past date %', d;
              END IF;
          END LOOP;
  
      RETURN NEW;
  END;
  $$;
  CREATE TRIGGER trg_validate_consultation_slots
      BEFORE INSERT OR UPDATE
      ON therapist
      FOR EACH ROW
  EXECUTE FUNCTION fn_validate_consultation_slots();
  

• Обезбедува логичка поврзаност на датумот на истекување со датумот на консултација: Терапијата не може да истече пред да биде препишана.

  CREATE OR REPLACE FUNCTION fn_validate_therapy_exp_date()
      RETURNS trigger
      LANGUAGE plpgsql
  AS $$
  DECLARE
      consult_date DATE;
  BEGIN
      SELECT date INTO consult_date FROM consultation WHERE id_consultation = NEW.id_consultation;
      IF consult_date IS NOT NULL AND NEW.exp_date < consult_date THEN
          RAISE EXCEPTION 'therapy.exp_date (%) cannot be before consultation date (%)', NEW.exp_date, consult_date;
      END IF;
      RETURN NEW;
  END;
  $$;
  CREATE TRIGGER trg_validate_therapy_exp_date
      BEFORE INSERT OR UPDATE
      ON therapy
      FOR EACH ROW
  EXECUTE FUNCTION fn_validate_therapy_exp_date();
  

• Обезбедува внесувањето на податоци во дневникот да биде возможно само за минати или тековни датуми, со што се спречува корисниците да креираат записи за идни датуми.

  CREATE OR REPLACE FUNCTION fn_validate_diary_not_future()
      RETURNS trigger
      LANGUAGE plpgsql
  AS $$
  BEGIN
      IF NEW.date > CURRENT_DATE THEN
          RAISE EXCEPTION 'diary.date (%) cannot be in the future', NEW.date;
      END IF;
      RETURN NEW;
  END;
  $$;
  CREATE TRIGGER trg_validate_diary_not_future
      BEFORE INSERT OR UPDATE
      ON diary
      FOR EACH ROW
  EXECUTE FUNCTION fn_validate_diary_not_future();
  

• Обезбедува логичка конзистентност помеѓу датумот на плаќање и датумот на самата консултација односно плаќањето за консултацијата не може да се изврши пред нејзиното одржување.

  CREATE OR REPLACE FUNCTION fn_validate_consultation_payment_date()
      RETURNS trigger
      LANGUAGE plpgsql
  AS $$
  BEGIN
      IF NEW.date_of_payment IS NOT NULL AND NEW.date_of_payment < NEW.date THEN
          RAISE EXCEPTION 'consultation.date_of_payment (%) cannot be before consultation.date (%)', NEW.date_of_payment, NEW.date;
      END IF;
      RETURN NEW;
  END;
  $$;
  CREATE TRIGGER trg_validate_consultation_payment_date
      BEFORE INSERT OR UPDATE
      ON consultation
      FOR EACH ROW
  EXECUTE FUNCTION fn_validate_consultation_payment_date();
  

Индекси

• Оптимизирање на квериња кои ги филтрираат или поврзуваат пациентите според нивниот доделен терапевт.

  CREATE INDEX IF NOT EXISTS idx_patient_therapist
      ON patient (id_therapist);
  

• Оптимизирање на кверињата кои ги преземаат блоговите за конкретен пациент, сортирани според датум.

  CREATE INDEX IF NOT EXISTS idx_blog_patient_date
      ON blog (id_patient, date_of_post DESC);
  

• Оптимизирање на кверињата кои ги преземаат коментарите за конкретен блог, сортирани според датум.

  CREATE INDEX IF NOT EXISTS idx_comment_blog_date
      ON comment (id_blog, date_of_comment DESC);
  

• Оптимизирање на кверињата кои ги преземаат консултациите за конкретен пациент, сортирани според датум.

  CREATE INDEX IF NOT EXISTS idx_consultation_patient_date
      ON consultation (id_patient, date DESC);
  

• Оптимизирање на кверињата кои ги преземаат консултациите за конкретен терапевт, сортирани според датум.

  CREATE INDEX IF NOT EXISTS idx_consultation_therapist_date
      ON consultation (id_therapist, date DESC);