Changes between Version 1 and Version 2 of Advanced Database Development

Timestamp:

02/10/26 15:18:11 (3 weeks ago)

Author:

231035

Comment:

--

Advanced Database Development

@@ -137 +137 @@
 $$;
 
+DO $$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1
+    FROM information_schema.columns
+    WHERE table_name = 'appointments'
+      AND column_name = 'slot'
+  ) THEN
+    ALTER TABLE appointments
+      ADD COLUMN slot tstzrange
+      GENERATED ALWAYS AS (
+        tstzrange(date_time, date_time + interval '30 minutes', '[)')
+      ) STORED;
+  END IF;
+END $$;
+
+CREATE OR REPLACE FUNCTION petify_trg_appointments_no_overlap()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  
+  IF NEW.status NOT IN ('CONFIRMED','DONE') THEN
+    RETURN NEW;
+  END IF;
+
+
+  IF EXISTS (
+    SELECT 1
+    FROM appointments a
+    WHERE a.responsible_owner_id = NEW.responsible_owner_id
+      AND a.status IN ('CONFIRMED','DONE')
+      AND a.slot && NEW.slot
+      AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
+  ) THEN
+    RAISE EXCEPTION
+      'Overlapping appointment for owner % at %',
+      NEW.responsible_owner_id, NEW.date_time;
+  END IF;
+
+ 
+  IF EXISTS (
+    SELECT 1
+    FROM appointments a
+    WHERE a.animal_id = NEW.animal_id
+      AND a.status IN ('CONFIRMED','DONE')
+      AND a.slot && NEW.slot
+      AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
+  ) THEN
+    RAISE EXCEPTION
+      'Overlapping appointment for animal % at %',
+      NEW.animal_id, NEW.date_time;
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+
 DROP TRIGGER IF EXISTS trg_appointments_enforce ON appointments;
 CREATE TRIGGER trg_appointments_enforce
@@ -143 +202 @@
 FOR EACH ROW
 EXECUTE FUNCTION petify_trg_appointments_enforce();
-}}}
-{{{Stored procedures}}}
-{{{
--- 30min timeslots
-CREATE EXTENSION IF NOT EXISTS btree_gist;
-
-DO $$
-BEGIN
-  IF NOT EXISTS (
-    SELECT 1
-    FROM information_schema.columns
-    WHERE table_name='appointments' AND column_name='slot'
-  ) THEN
-    ALTER TABLE appointments
-      ADD COLUMN slot tstzrange
-      GENERATED ALWAYS AS (tstzrange(date_time, date_time + interval '30 minutes', '[)')) STORED;
-  END IF;
-END $$;
-
-DO $$
-BEGIN
-  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'appointments_no_overlap_per_owner') THEN
-    ALTER TABLE appointments
-      ADD CONSTRAINT appointments_no_overlap_per_owner
-      EXCLUDE USING gist (responsible_owner_id WITH =, slot WITH &&)
-      WHERE (status IN ('CONFIRMED','DONE'));
-  END IF;
-END $$;
-
-DO $$
-BEGIN
-  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'appointments_no_overlap_per_animal') THEN
-    ALTER TABLE appointments
-      ADD CONSTRAINT appointments_no_overlap_per_animal
-      EXCLUDE USING gist (animal_id WITH =, slot WITH &&)
-      WHERE (status IN ('CONFIRMED','DONE'));
-  END IF;
-END $$;
-
-}}}
+
+DROP TRIGGER IF EXISTS trg_appointments_no_overlap ON appointments;
+CREATE TRIGGER trg_appointments_no_overlap
+BEFORE INSERT OR UPDATE
+ON appointments
+FOR EACH ROW
+EXECUTE FUNCTION petify_trg_appointments_no_overlap();
+
+}}}
+
 {{{Views}}}
 {{{