Changes between Version 4 and Version 5 of appdevelopment

Timestamp:

09/30/25 18:44:37 (4 weeks ago)

Author:

155036

Comment:

--

appdevelopment

@@ -123 +123 @@
         out = event_create(payload)  # повик на p_event_create
         return Response(out, status=201)
+}}}
+
+=== p_event_create_bulk ===
+{{{
+CREATE OR REPLACE PROCEDURE p_event_bulk_create(
+  IN  _items jsonb,
+  INOUT cur refcursor
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  -- Validate top-level shape
+  IF jsonb_typeof(_items) <> 'array' THEN
+    RAISE EXCEPTION 'payload must be a JSON array';
+  END IF;
+
+  OPEN cur FOR
+  WITH ins AS (
+      INSERT INTO events (
+        sql_date, is_root_event, event_code, goldstein_scale,
+        num_mentions, num_sources, num_articles, avg_tone,
+        month_year, year, fraction_date
+      )
+      SELECT
+        (x->>'sql_date')::date,
+        COALESCE((x->>'is_root_event')::boolean, TRUE),
+        (x->>'event_code')::text,
+        NULLIF(x->>'goldstein_scale','')::numeric,
+        NULLIF(x->>'num_mentions','')::int,
+        NULLIF(x->>'num_sources','')::int,
+        NULLIF(x->>'num_articles','')::int,
+        NULLIF(x->>'avg_tone','')::numeric,
+        to_char((x->>'sql_date')::date, 'MMYYYY'),
+        EXTRACT(YEAR FROM (x->>'sql_date')::date)::int,
+        EXTRACT(DOY FROM (x->>'sql_date')::date) / 365.0
+      FROM jsonb_array_elements(_items) AS x
+      RETURNING global_event_id, sql_date, event_code
+  )
+  SELECT jsonb_build_object(
+    'ok', true,
+    'inserted_count', (SELECT count(*) FROM ins),
+    'inserted_ids', (SELECT jsonb_agg(global_event_id) FROM ins)
+  ) AS j;
+
+EXCEPTION
+  WHEN OTHERS THEN
+    -- Any error will abort the outer transaction unless handled
+    RAISE;
+END;
+$$;
+}}}
+
+=== p_event_create_update_delete_bulk ===
+{{{
+
+-- Multiple operations in one go: INSERT + UPDATE + DELETE
+-- Payload shape:
+-- {
+--   "insert": [
+--     {
+--       "sql_date": "2025-09-01",
+--       "is_root_event": true,
+--       "event_code": "E123",
+--       "goldstein_scale": 1.5,
+--       "num_mentions": 5,
+--       "num_sources": 2,
+--       "num_articles": 3,
+--       "avg_tone": 0.75
+--     }, ...
+--   ],
+--   "update": [
+--     {
+--       "global_event_id": 42,
+--       "sql_date": "2025-09-02",          
+--       "num_mentions": 7,
+--       "avg_tone": 0.1
+--     }, ...
+--   ],
+--   "delete": [ 10, 11, 12 ]      
+-- }
+
+CREATE OR REPLACE PROCEDURE p_event_bulk_apply(
+  IN  _payload jsonb,
+  INOUT cur refcursor DEFAULT NULL
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  v_has_insert boolean := (_payload ? 'insert');
+  v_has_update boolean := (_payload ? 'update');
+  v_has_delete boolean := (_payload ? 'delete');
+BEGIN
+  -- Basic validation
+  IF _payload IS NULL OR jsonb_typeof(_payload) <> 'object' THEN
+    RAISE EXCEPTION 'payload must be a JSON object with optional keys: insert, update, delete';
+  END IF;
+  IF v_has_insert AND jsonb_typeof(_payload->'insert') <> 'array' THEN
+    RAISE EXCEPTION '"insert" must be an array';
+  END IF;
+  IF v_has_update AND jsonb_typeof(_payload->'update') <> 'array' THEN
+    RAISE EXCEPTION '"update" must be an array';
+  END IF;
+  IF v_has_delete AND jsonb_typeof(_payload->'delete') <> 'array' THEN
+    RAISE EXCEPTION '"delete" must be an array of IDs';
+  END IF;
+
+  -- Perform all operations in a single statement block using CTEs
+  IF cur IS NULL THEN cur := 'cur_event_bulk_apply'; END IF;
+  OPEN cur FOR
+  WITH
+  -- 1) INSERT many (optional)
+  ins AS (
+    SELECT * FROM (
+      INSERT INTO events (
+        sql_date, is_root_event, event_code, goldstein_scale,
+        num_mentions, num_sources, num_articles, avg_tone,
+        month_year, year, fraction_date
+      )
+      SELECT
+        (x->>'sql_date')::date,
+        COALESCE((x->>'is_root_event')::boolean, TRUE),
+        UPPER((x->>'event_code')::text),
+        NULLIF(x->>'goldstein_scale','')::numeric,
+        NULLIF(x->>'num_mentions','')::int,
+        NULLIF(x->>'num_sources','')::int,
+        NULLIF(x->>'num_articles','')::int,
+        NULLIF(x->>'avg_tone','')::numeric,
+        TO_CHAR((x->>'sql_date')::date, 'MMYYYY'),
+        EXTRACT(YEAR FROM (x->>'sql_date')::date)::int,
+        EXTRACT(DOY FROM (x->>'sql_date')::date)::numeric / 365.0
+      FROM jsonb_array_elements(COALESCE(_payload->'insert', '[]'::jsonb)) AS x
+      RETURNING global_event_id
+    ) t
+  ),
+  upd_src AS (
+    SELECT *
+    FROM jsonb_to_recordset(COALESCE(_payload->'update','[]'::jsonb)) AS r(
+      global_event_id int,
+      sql_date date,
+      is_root_event boolean,
+      event_code text,
+      goldstein_scale numeric(5,2),
+      num_mentions int,
+      num_sources int,
+      num_articles int,
+      avg_tone numeric(5,2)
+    )
+  ),
+  upd AS (
+    UPDATE events e
+    SET
+      sql_date       = COALESCE(u.sql_date, e.sql_date),
+      is_root_event  = COALESCE(u.is_root_event, e.is_root_event),
+      event_code     = COALESCE(UPPER(u.event_code), e.event_code),
+      goldstein_scale= COALESCE(u.goldstein_scale, e.goldstein_scale),
+      num_mentions   = COALESCE(u.num_mentions, e.num_mentions),
+      num_sources    = COALESCE(u.num_sources,  e.num_sources),
+      num_articles   = COALESCE(u.num_articles, e.num_articles),
+      avg_tone       = COALESCE(u.avg_tone,     e.avg_tone),
+      month_year     = CASE WHEN u.sql_date IS NULL THEN e.month_year ELSE TO_CHAR(u.sql_date,'MMYYYY') END,
+      year           = CASE WHEN u.sql_date IS NULL THEN e.year ELSE EXTRACT(YEAR FROM u.sql_date)::int END,
+      fraction_date  = CASE WHEN u.sql_date IS NULL THEN e.fraction_date ELSE (EXTRACT(DOY FROM u.sql_date)::numeric / 365.0) END
+    FROM upd_src u
+    WHERE e.global_event_id = u.global_event_id
+    RETURNING e.global_event_id
+  ),
+  -- 3) DELETE many (optional)
+  del_ids AS (
+    SELECT (x)::int AS id
+    FROM jsonb_array_elements_text(COALESCE(_payload->'delete','[]'::jsonb)) AS x
+  ),
+  del AS (
+    DELETE FROM events e
+    USING del_ids d
+    WHERE e.global_event_id = d.id
+    RETURNING e.global_event_id
+  )
+  SELECT jsonb_build_object(
+    'ok', true,
+    'inserted_count', (SELECT COUNT(*) FROM ins),
+    'inserted_ids',   COALESCE((SELECT jsonb_agg(global_event_id) FROM ins), '[]'::jsonb),
+    'updated_count',  (SELECT COUNT(*) FROM upd),
+    'updated_ids',    COALESCE((SELECT jsonb_agg(global_event_id) FROM upd), '[]'::jsonb),
+    'deleted_count',  (SELECT COUNT(*) FROM del),
+    'deleted_ids',    COALESCE((SELECT jsonb_agg(global_event_id) FROM del), '[]'::jsonb)
+  ) AS j;
+
+
+EXCEPTION
+  WHEN OTHERS THEN
+    RAISE;
+END;
+$$;
 }}}
 
@@ -183 +376 @@
 $$;
 }}}
+
 
 === p_event_get ===