Changes between Version 1 and Version 2 of appdevelopment

Timestamp:

09/24/25 23:50:19 (5 weeks ago)

Author:

155036

Comment:

--

appdevelopment

@@ -1 @@
-== Напреден апликативен развој ==
+= Напреден апликативен развој — Anomalyzer =
+
+
+
+Имплементирани се:
+ * **Изведени колони** (year, month_year, fraction_date) преку BEFORE тригер  
+ * **Автоматска дневна агрегaција** во табела `events_daily_agg` по INSERT/UPDATE/DELETE  
+ * **Бизнис правила** со CHECK и тригер (граници на tone; non-root настаните мора да имаат mentions)  
+ * **Оптимизација на перформанси** со индекси за чести join/filter полиња  
+
+== Тригери ==
+
+=== BEFORE INSERT/UPDATE на events ===
+
+Нормализација на кодови, проверка на валидност и автоматско пополнување на деривирани полиња (`year`, `month_year`, `fraction_date`).  
+
+{{{
+CREATE OR REPLACE FUNCTION trg_events_before_ins_upd()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  IF NEW.event_code IS NOT NULL THEN
+    NEW.event_code := upper(NEW.event_code);
+  END IF;
+
+  IF NEW.avg_tone IS NOT NULL AND (NEW.avg_tone < -10 OR NEW.avg_tone > 10) THEN
+    RAISE EXCEPTION 'avg_tone out of range [-10,10]: %', NEW.avg_tone;
+  END IF;
+
+  IF NEW.sql_date IS NOT NULL THEN
+    NEW.year := EXTRACT(YEAR FROM NEW.sql_date)::int;
+    NEW.month_year := to_char(NEW.sql_date, 'MMYYYY');
+    NEW.fraction_date := EXTRACT(DOY FROM NEW.sql_date) / 365.0;
+  END IF;
+
+  IF NEW.is_root_event IS FALSE AND COALESCE(NEW.num_mentions, 0) <= 0 THEN
+    RAISE EXCEPTION 'non-root events must have num_mentions > 0';
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER events_before_ins_upd
+BEFORE INSERT OR UPDATE ON events
+FOR EACH ROW
+EXECUTE FUNCTION trg_events_before_ins_upd();
+}}}
+
+=== AFTER INSERT/UPDATE/DELETE на events ===
+
+Автоматско пресметување на дневна агрегaција и чување во табелата `events_daily_agg`.  
+
+{{{
+CREATE TABLE IF NOT EXISTS events_daily_agg (
+  sql_date DATE PRIMARY KEY,
+  events_count INTEGER NOT NULL,
+  avg_tone NUMERIC(6,4)
+);
+
+CREATE OR REPLACE FUNCTION trg_events_after_ins_upd_del()
+RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  WITH s AS (
+    SELECT d AS sql_date, COUNT(*) AS events_count, AVG(avg_tone) AS avg_tone
+    FROM (SELECT COALESCE(NEW.sql_date, OLD.sql_date) AS d) q
+    JOIN events e ON e.sql_date = q.d
+    GROUP BY d
+  )
+  INSERT INTO events_daily_agg(sql_date, events_count, avg_tone)
+  SELECT sql_date, events_count, avg_tone FROM s
+  ON CONFLICT (sql_date) DO UPDATE
+  SET events_count = EXCLUDED.events_count,
+      avg_tone     = EXCLUDED.avg_tone;
+
+  IF NOT EXISTS (
+    SELECT 1 FROM events WHERE sql_date = COALESCE(NEW.sql_date, OLD.sql_date)
+  ) THEN
+    DELETE FROM events_daily_agg WHERE sql_date = COALESCE(NEW.sql_date, OLD.sql_date);
+  END IF;
+
+  RETURN NULL;
+END;
+$$;
+
+CREATE TRIGGER events_after_ins
+AFTER INSERT ON events
+FOR EACH ROW
+EXECUTE FUNCTION trg_events_after_ins_upd_del();
+
+CREATE TRIGGER events_after_upd
+AFTER UPDATE ON events
+FOR EACH ROW
+EXECUTE FUNCTION trg_events_after_ins_upd_del();
+
+CREATE TRIGGER events_after_del
+AFTER DELETE ON events
+FOR EACH ROW
+EXECUTE FUNCTION trg_events_after_ins_upd_del();
+}}}
+
+== Трансакции ==
+
+CRUD операциите врз `events` се извршуваат во рамки на трансакициски блокови.  
+Во Django, ова се постигнува со `@transaction.atomic`, што овозможува автоматски ROLLBACK при грешка (нпр. кога тригер ќе подигне исклучок).
+
+Пример:  
+
+{{{
+# src/api/views/events.py
+from django.db import transaction
+from rest_framework.response import Response
+from rest_framework.views import APIView
+from .services.events import event_create
+
+class EventListCreate(APIView):
+    @transaction.atomic
+    def post(self, request):
+        payload = request.data
+        out = event_create(payload)  # повик на p_event_create
+        return Response(out, status=201)
+}}}
+
+== Индекси ==
+
+За перформанси при пребарување и join операции, креирани се индекси:  
+
+{{{
+CREATE INDEX ix_event_details_event ON event_details (global_event_id);
+CREATE INDEX ix_event_details_actor ON event_details (actor_id);
+CREATE INDEX ix_event_details_loc   ON event_details (location_id);
+
+CREATE INDEX ix_notifications_user  ON notifications (user_id, notification_date);
+CREATE INDEX ix_notifications_event ON notifications (event_id);
+
+CREATE INDEX ix_predictions_event   ON predictions (event_id);
+CREATE INDEX ix_predictions_actor   ON predictions (actor_id);
+
+CREATE INDEX ix_analytics_event_actor ON event_analytics (event_id, actor_id, date);
+CREATE INDEX ix_conflict_pair_date    ON conflict_risk (actor1_id, actor2_id, predicted_date);
+}}}
+
+== Складирани процедури ==
+
+Имплементирани се CRUD процедури за `events`, со JSON резултат погоден за директен REST одговор.
+
+=== p_event_create ===
+{{{
+CREATE OR REPLACE PROCEDURE p_event_create(
+  IN  p_sql_date date,
+  IN  p_is_root_event boolean,
+  IN  p_event_code varchar(10),
+  IN  p_goldstein_scale numeric(5,2) DEFAULT NULL,
+  IN  p_num_mentions int DEFAULT NULL,
+  IN  p_num_sources  int DEFAULT NULL,
+  IN  p_num_articles int DEFAULT NULL,
+  IN  p_avg_tone    numeric(5,2) DEFAULT NULL,
+  INOUT p_result refcursor DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE v_id int;
+BEGIN
+  INSERT INTO events (
+    sql_date, month_year, year, fraction_date, is_root_event, event_code,
+    goldstein_scale, num_mentions, num_sources, num_articles, avg_tone
+  )
+  VALUES (
+    p_sql_date, TO_CHAR(p_sql_date,'MMYYYY'),
+    EXTRACT(YEAR FROM p_sql_date)::int,
+    (EXTRACT(DOY FROM p_sql_date)::numeric / 365),
+    COALESCE(p_is_root_event, TRUE), p_event_code,
+    p_goldstein_scale, p_num_mentions, p_num_sources, p_num_articles, p_avg_tone
+  )
+  RETURNING global_event_id INTO v_id;
+
+  IF p_result IS NULL THEN p_result := 'cur_event_create'; END IF;
+  OPEN p_result FOR SELECT to_jsonb(e) AS j FROM events e WHERE e.global_event_id = v_id;
+END;
+$$;
+}}}
+
+=== p_event_get ===
+{{{
+CREATE OR REPLACE PROCEDURE p_event_get(
+  IN  p_event_id int,
+  INOUT p_result refcursor DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  IF p_result IS NULL THEN p_result := 'cur_event_get'; END IF;
+  OPEN p_result FOR
+    SELECT COALESCE(
+             (SELECT to_jsonb(e) FROM events e WHERE e.global_event_id = p_event_id),
+             jsonb_build_object('ok', false, 'error', 'not_found', 'id', p_event_id)
+           ) AS j;
+END;
+$$;
+}}}
+
+=== p_event_list ===
+{{{
+CREATE OR REPLACE PROCEDURE p_event_list(
+  IN  p_limit int DEFAULT 50,
+  IN  p_offset int DEFAULT 0,
+  INOUT p_result refcursor DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  IF p_result IS NULL THEN p_result := 'cur_event_list'; END IF;
+  OPEN p_result FOR
+    WITH rows AS (
+      SELECT to_jsonb(e) AS j
+      FROM events e
+      ORDER BY e.global_event_id DESC
+      LIMIT GREATEST(p_limit,0) OFFSET GREATEST(p_offset,0)
+    )
+    SELECT jsonb_build_object(
+      'ok', true,
+      'limit', p_limit,
+      'offset', p_offset,
+      'results', COALESCE(jsonb_agg(j), '[]'::jsonb)
+    ) AS j
+    FROM rows;
+END;
+$$;
+}}}
+
+=== p_event_update ===
+{{{
+CREATE OR REPLACE PROCEDURE p_event_update(
+  IN  p_event_id int,
+  IN  p_sql_date date DEFAULT NULL,
+  IN  p_is_root_event boolean DEFAULT NULL,
+  IN  p_event_code varchar(10) DEFAULT NULL,
+  IN  p_goldstein_scale numeric(5,2) DEFAULT NULL,
+  IN  p_num_mentions int DEFAULT NULL,
+  IN  p_num_sources  int DEFAULT NULL,
+  IN  p_num_articles int DEFAULT NULL,
+  IN  p_avg_tone    numeric(5,2) DEFAULT NULL,
+  INOUT p_result refcursor DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE v_exists boolean;
+BEGIN
+  SELECT EXISTS(SELECT 1 FROM events WHERE global_event_id = p_event_id) INTO v_exists;
+  IF NOT v_exists THEN
+    IF p_result IS NULL THEN p_result := 'cur_event_update'; END IF;
+    OPEN p_result FOR
+      SELECT jsonb_build_object('ok', false, 'error', 'not_found', 'id', p_event_id) AS j;
+    RETURN;
+  END IF;
+
+  UPDATE events SET
+    sql_date       = COALESCE(p_sql_date, sql_date),
+    is_root_event  = COALESCE(p_is_root_event, is_root_event),
+    event_code     = COALESCE(p_event_code, event_code),
+    goldstein_scale= COALESCE(p_goldstein_scale, goldstein_scale),
+    num_mentions   = COALESCE(p_num_mentions, num_mentions),
+    num_sources    = COALESCE(p_num_sources,  num_sources),
+    num_articles   = COALESCE(p_num_articles, num_articles),
+    avg_tone       = COALESCE(p_avg_tone,     avg_tone),
+    month_year     = CASE WHEN p_sql_date IS NULL THEN month_year ELSE TO_CHAR(p_sql_date,'MMYYYY') END,
+    year           = CASE WHEN p_sql_date IS NULL THEN year ELSE EXTRACT(YEAR FROM p_sql_date)::int END,
+    fraction_date  = CASE WHEN p_sql_date IS NULL THEN fraction_date ELSE (EXTRACT(DOY FROM p_sql_date)::numeric / 365) END
+  WHERE global_event_id = p_event_id;
+
+  IF p_result IS NULL THEN p_result := 'cur_event_update'; END IF;
+  OPEN p_result FOR SELECT to_jsonb(e) AS j FROM events e WHERE e.global_event_id = p_event_id;
+END;
+$$;
+}}}
+
+=== p_event_delete ===
+{{{
+CREATE OR REPLACE PROCEDURE p_event_delete(
+  IN  p_event_id int,
+  INOUT p_result refcursor DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE v_ct int;
+BEGIN
+  DELETE FROM events WHERE global_event_id = p_event_id;
+  GET DIAGNOSTICS v_ct = ROW_COUNT;
+
+  IF p_result IS NULL THEN p_result := 'cur_event_delete'; END IF;
+  IF v_ct = 0 THEN
+    OPEN p_result FOR
+      SELECT jsonb_build_object('ok', false, 'error', 'not_found', 'id', p_event_id) AS j;
+  ELSE
+    OPEN p_result FOR
+      SELECT jsonb_build_object('ok', true, 'deleted_id', p_event_id) AS j;
+  END IF;
+END;
+$$;
+}}}
+
+== Заклучок ==
+
+Со оваа комбинација на тригери, трансакициски операции, индекси и складирани процедури, Django апликацијата обезбедува:  
+ * Автоматско ажурирање на агрегирани табели 
+ * Валидација на бизнис правила на податочно ниво  
+ * Оптимизирано пребарување со индекси  
+ * Стандарден JSON излез преку PostgreSQL процедури