From d607527f052504e3df4d4dfed6e6d3854988c6b6 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 13 May 2026 03:35:58 +0000 Subject: [PATCH] Convert AGGRESSION_EVENT -> AGGRESSIONS --- conversion/Makefile | 10 +- conversion/load_aggressions.m4 | 210 +++++++++++++++++++++++++++++++++ 2 files changed, 219 insertions(+), 1 deletion(-) create mode 100644 conversion/load_aggressions.m4 diff --git a/conversion/Makefile b/conversion/Makefile index a0594da..81fda2e 100644 --- a/conversion/Makefile +++ b/conversion/Makefile @@ -303,7 +303,7 @@ load_data: load_support load_biography load_community_membership \ load_aggression_event_log \ load_follow_to_follows load_follow_to_other \ load_follow_arrival_sanity load_follow_arrival \ - load_finish + load_aggressions load_finish .PHONY: load_support load_support: $(PSQL_DEPENDS) @@ -368,6 +368,14 @@ load_follow_arrival: $(PSQL_DEPENDS) PSQL_SINGLE_TRANS='$(PSQL_SINGLE_TRANS)' \ ./load_chunks.sh load_follow_arrival.m4 clean.follow_arrival +.PHONY: load_aggressions +load_aggressions: $(PSQL_DEPENDS) + PSQL_SETUP='$(PSQL_SETUP)' \ + PSQL_ARGS='$(PSQL_ARGS)' \ + PSQL_SINGLE_TRANS='$(PSQL_SINGLE_TRANS)' \ + ./load_chunks.sh load_aggressions.m4 clean.aggression_event + + .PHONY: load_finish load_finish: $(PSQL_DEPENDS) ( $(PSQL_SETUP) \ diff --git a/conversion/load_aggressions.m4 b/conversion/load_aggressions.m4 new file mode 100644 index 0000000..37e1f84 --- /dev/null +++ b/conversion/load_aggressions.m4 @@ -0,0 +1,210 @@ +-- Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +-- +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as +-- published by the Free Software Foundation, either version 3 of the +-- License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU Affero General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see . +-- +-- Karl O. Pinc + +-- Setup the search path +SET search_path TO sokwedb, codes, lib, clean, libconv; + + +-- +-- aggressions +-- + +-- The processing of aggression_events is done row-by-row. +-- See additional comments on this in load_follow_arrival.m4 + +DO $$ +DECLARE + this_ae clean.aggression_event%ROWTYPE; + related_fid follows.fid%TYPE; + +BEGIN + + FOR this_ae IN + SELECT * + FROM clean.aggression_event + ORDER BY aggression_event.ae_date + , aggression_event.ae_fol_b_focal_id + , aggression_event.ae_time + LIMIT m4_chunk_limit_amount + OFFSET m4_chunk_offset_amount + LOOP + -- Which follow belongs to the aggression_event? + SELECT follows.fid + INTO STRICT related_fid + FROM follows + WHERE follows.focal = this_ae.ae_fol_b_focal_id + AND follows.date = this_ae.ae_date; + + -- aggression_event -> EVENTS + INSERT INTO events ( + fid + , behavior + , start + , stop + , certainty + , notes) + VALUES ( + related_fid + , 'AGG' + , this_ae.ae_time + , this_ae.ae_time + , CASE -- badobs + WHEN this_ae.ae_bad_observation_flag IS NULL THEN + '0' + WHEN this_ae.ae_bad_observation_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , ''); + + -- follow_arrival -> ROLES + -- Actor + INSERT INTO roles ( + eid + , role + , participant) + VALUES ( + CURRVAL('events_eid_seq') + , 'Actor' + , this_ae.ae_b_aggressor_id); + + -- Actee + INSERT INTO roles ( + eid + , role + , participant) + VALUES ( + CURRVAL('events_eid_seq') + , 'Actee' + , this_ae.ae_b_recipient_id); + + -- aggression_event -> AGGRESSIONS + INSERT INTO aggressions( + eid + ,decided + ,multiaggressors + ,multirecipients + ,badobs + ,bristle + ,display + ,chase + ,contact + ,vocal + ,severity + ,community + ,extractedby) + SELECT + CURRVAL('events_eid_seq') + , CASE -- decided + WHEN this_ae.ae_decided_flag IS NULL THEN + '0' + WHEN this_ae.ae_decided_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- multiaggressors + WHEN this_ae.ae_multiple_aggressor_flag IS NULL THEN + '0' + WHEN this_ae.ae_multiple_aggressor_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- multirecipients + WHEN this_ae.ae_multiple_recipient_flag IS NULL THEN + '0' + WHEN this_ae.ae_multiple_recipient_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- badobs + WHEN this_ae.ae_bad_observation_flag IS NULL THEN + '0' + WHEN this_ae.ae_bad_observeation_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- bristle + WHEN this_ae.ae_bristle_flag IS NULL THEN + '0' + WHEN this_ae.ae_bristle_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- display + WHEN this_ae.ae_display_flag IS NULL THEN + '0' + WHEN this_ae.ae_display_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- chase + WHEN this_ae.ae_chase_flag IS NULL THEN + '0' + WHEN this_ae.ae_chase_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- contact + WHEN this_ae.ae_contact_flag IS NULL THEN + '0' + WHEN this_ae.ae_contact_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- contact + WHEN this_ae.ae_contact_flag IS NULL THEN + '0' + WHEN this_ae.ae_contact_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- vocal + WHEN this_ae.ae_vocal_flag IS NULL THEN + '0' + WHEN this_ae.ae_vocal_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , CASE -- severisty + WHEN this_ae.ae_fight_category_flag IS NULL THEN + '0' + WHEN this_ae.ae_fight_category_flag = 'X' THEN + '1' + ELSE + 'Invalid' -- Blow up with an typecasting error + END + , this_ae.ae_cl_community_id + , this_ae.ae_comments; + + END LOOP; +END; +$$; -- aggression_event DO block + +ANALYZE events + , roles + , arrivals; -- 2.34.1