From 410865cdebdfd3c7856134111501f574f9ac0a4f Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 13 May 2026 17:58:37 +0000 Subject: [PATCH] Have AGGRESSIONS.Severity validate off the AGG_SEVERITIES table The clean.ae_fight_category column needs to have a single space normalized to NULL. --- conversion/load_aggressions.m4 | 8 +++----- conversion/load_support.sql | 8 ++++++++ db/schemas/sokwedb/tables/create/aggressions.m4 | 9 +++------ doc/src/tables/aggressions.m4 | 7 ++----- include/global_constants.m4 | 10 ---------- 5 files changed, 16 insertions(+), 26 deletions(-) diff --git a/conversion/load_aggressions.m4 b/conversion/load_aggressions.m4 index 2ee424e..c4096f2 100644 --- a/conversion/load_aggressions.m4 +++ b/conversion/load_aggressions.m4 @@ -190,13 +190,11 @@ BEGIN ELSE 'Invalid' -- Blow up with an typecasting error END - , CASE -- severisty + , CASE -- severity WHEN this_ae.ae_fight_category_flag IS NULL THEN - '0' - WHEN this_ae.ae_fight_category_flag = 'X' THEN - '1' + 'unrated' ELSE - 'Invalid' -- Blow up with an typecasting error + this_ae.ae_fight_category_flag END , this_ae.ae_cl_community_id , this_ae.ae_comments; diff --git a/conversion/load_support.sql b/conversion/load_support.sql index d3c7a77..1a01524 100644 --- a/conversion/load_support.sql +++ b/conversion/load_support.sql @@ -22,6 +22,14 @@ SET search_path TO sokwedb, codes, lib, clean, libconv; -- Support tables +-- agg_severities +INSERT INTO agg_severities (severity, description) + VALUES ('unrated', 'Not evaluated for severity') + , ('0', 'Low severity') + , ('1', 'Medium severity') + , ('2', 'High severity') + , ('?', 'Questionable severity'); + -- arrival_sources -- From the follow_arrival part of the MS Word doc for the MS Access db -- And from phone conversations regards: Tiki_Mom, Tiki_GM, Tiki_PM, diff --git a/db/schemas/sokwedb/tables/create/aggressions.m4 b/db/schemas/sokwedb/tables/create/aggressions.m4 index c5e7ffc..698cd1c 100644 --- a/db/schemas/sokwedb/tables/create/aggressions.m4 +++ b/db/schemas/sokwedb/tables/create/aggressions.m4 @@ -36,13 +36,10 @@ CREATE TABLE aggressions ( ,chase BOOLEAN NOT NULL ,contact BOOLEAN NOT NULL ,vocal BOOLEAN NOT NULL - ,severity INTEGER NOT NULL + ,severity TEXT NOT NULL CONSTRAINT - "Severity must be one of: sdb_no_severity, sdb_low_severity, sdb_medium_severity, sdb_high_severity" - CHECK (severity = sdb_no_severity - OR severity = sdb_low_severity - OR severity = sdb_medium_severity - OR severity = sdb_high_severity) + "Severity must an AGG_SEVERITIES.Severity value" + REFERENCES agg_severities ,community TEXT NOT NULL REFERENCES comm_ids ,extractedby TEXT NOT NULL diff --git a/doc/src/tables/aggressions.m4 b/doc/src/tables/aggressions.m4 index e6fadd7..1fa8102 100644 --- a/doc/src/tables/aggressions.m4 +++ b/doc/src/tables/aggressions.m4 @@ -245,11 +245,8 @@ Severity ```````` .. |AGGRESSIONS.Severity_summary| replace:: - An integer indicating the severity of the aggression. - One of: ``sdb_no_severity``, ``sdb_low_severity``, - ``sdb_medium_severity``, ``sdb_high_severity`` - The value ``sdb_no_severity`` indicates the lack of an assigned - severity. + A value indicating the severity of the aggression. + AN |AGG_SEVERITIES|.\ |AGG_SEVERITIES.Severity| value. |AGGRESSIONS.Severity_summary| diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 11de483..8bab738 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -50,16 +50,6 @@ dnl dnl Special values, table-specific -- hardcoded constants dnl -dnl -dnl AGGRESSIONS -dnl - -dnl The Severity levels -define(`sdb_no_severity', -1) -define(`sdb_low_severity', 0) -define(`sdb_medium_severity', 1) -define(`sdb_high_severity', 2) - dnl dnl BIOGRAPHY dnl -- 2.34.1