dnl Copyright (C) 2014 The Meme Factory, Inc. http://www.meme.com/
dnl
dnl This file is part of Gombe-MI.
dnl
dnl Gombe-MI is free software; you can redistribute it and/or modify
dnl it under the terms of the GNU General Public License as published by
dnl the Free Software Foundation; either version 3 of the License, or
dnl (at your option) any later version.
dnl
dnl This program is distributed in the hope that it will be useful,
dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
dnl GNU General Public License for more details.
dnl
dnl You should have received a copy of the GNU General Public License
dnl along with Gombe-MI. If not, see .
dnl
dnl Functions to convert food and fishing from comments.
dnl
dnl Bugs:
dnl It's grody to have so many macros but we're only running this once.
dnl
dnl Karl O. Pinc
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`types.m4')
include(`macros.m4')dnl
dnl The behavior code for eating.
define(`_ca_eating', `UE')
dnl plpgsql fragment for processing a focal's food comment
dnl
dnl Syntax: _process_eating_focal(focal, prefix)
dnl
dnl focal Either "mom", "infant", or "sib".
dnl prefix Either "MA", "INF", or "SIB".
dnl
changequote({,})
define({_process_eating_focal},{
-- Is the $1 eating?
SELECT events.eid, events.uncertain
INTO this_eid, this_uncertain
FROM events
JOIN parts ON (parts.eid = events.eid)
JOIN chimpids ON (chimpids.chimpid = parts.participant)
WHERE events.intid = this_intid
AND events.behavior = '_ca_eating'
AND chimpids.animid = $1
LIMIT 1;
IF FOUND THEN
-- There is an eating behavior.
makeup_food := FALSE;
-- See if we have a food comment applicable to the $1.
l := 0;
FOR foodcomment IN
SELECT regexp_matches(this_comment
-- don't try to split foodpart
, '(ALL|$2)FOOD!\[([^+]*)\+([^]]*)\]'
, 'g')
LOOP
l := l + 1;
IF l > 1 THEN
EXIT;
END IF;
END LOOP;
IF l = 0 THEN
-- We do not have a food comment.
IF NOT($1_eating_or_fishing)
-- or the page has changed and the food behavior is
-- not continuous.
OR ($1_page != this_page
AND allfood_page != this_page
AND ($1_last_btime IS NULL
OR $1_last_btime + '1 minute'::INTERVAL
!= this_time)) THEN
IF $1_page = this_page
AND allfood_page != this_page THEN
-- The $1 already has a food for the page.
NULL;
ELSIF allfood_page = this_page
AND $1_page != this_page THEN
-- The $1 is eating the allfood that everybody else is eating.
$1_foodparts := allfood_foodparts;
$1_foodkind := allfood_foodkind;
$1_food_changed := FALSE; -- May have changed, we don't care.
ELSIF this_uncertain THEN
-- When the eating is uncertain we can have no food comment.
makeup_food := TRUE;
ELSE
-- We must have a food comment to know what the $1 is eating.
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| textualize(`this_comment')
|| '): No eating food comment for $1';
END IF;
END IF;
ELSIF l = 1 THEN
-- We have a food comment.
$1_food_comment := TRUE;
-- (We don't care if we had a food comment already or not.)
new_foodparts := TRIM(BOTH FROM foodcomment[2]);
new_foodkind := TRIM(BOTH FROM foodcomment[3]);
IF ($1_foodparts != new_foodparts OR $1_foodparts IS NULL
OR $1_foodkind != new_foodkind OR $1_foodkind IS NULL) THEN
$1_food_changed := TRUE;
$1_foodparts := new_foodparts;
$1_foodkind := new_foodkind;
END IF;
-- Remember what kind of food comment we have.
IF foodcomment[1] = 'ALL' THEN
allfood_found := TRUE;
allfood_page := this_page;
allfood_foodparts := new_foodparts;
allfood_foodkind := new_foodkind;
mom_page := 0; -- hack
infant_page := 0; -- hack
sib_page := 0; -- hack
ELSE
focalfood_found := TRUE;
$1_page := this_page;
allfood_page := 0; -- hack
END IF;
ELSE
-- We have too many food comments.
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| this_comment
|| '): Too many food comments for $1';
END IF;
-- Process the $1's foodcomment.
$1_processed := TRUE;
converted := converted + 1;
-- Update the old event with the new food code.
UPDATE events
SET behavior = 'E'
WHERE events.eid = this_eid;
IF makeup_food THEN
-- Insert a made up EATS row.
INSERT INTO eats (eid, foodkind, foodpart)
VALUES (this_eid, 'UNRECORDED', 'NR');
ELSE
-- Go through the $1's foodparts.
these_foodparts := regexp_split_to_array($1_foodparts, ';');
FOREACH this_foodpart IN ARRAY these_foodparts LOOP
this_foodpart := TRIM(BOTH FROM this_foodpart);
-- Create the EATS row.
INSERT INTO eats (eid, foodkind, foodpart)
SELECT this_eid, $1_foodkind, foodpart_map.newfp
FROM foodpart_map
WHERE foodpart_map.oldfp = this_foodpart;
IF NOT FOUND THEN
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| this_comment
|| '): Foodpart for $1 in comment ('
|| this_foodpart
|| ') is not a known old foodpart code';
END IF;
END LOOP; -- Over $1 foodparts
END IF;
$1_eating := TRUE;
$1_last_btime := this_time;
ELSE -- No $1 eating behavior.
-- Somebody's got a behavior
$1_eating := FALSE;
END IF;
})dnl
changequote(`,')dnl
dnl plpgsql fragment for processing a focal's food comment
dnl
dnl Syntax: _process_fishing_focal(focal, prefix)
dnl
dnl focal Either "mom", "infant", or "sib".
dnl prefix Either "MA", "INF", or "SIB".
dnl
dnl This is almost identical to _process_eating_focal,
dnl the changes are in the behavior codes, error messages
dnl and inserting into FISHED,
dnl but it's too much trouble to abstract out the
dnl difference.
dnl
changequote({,})
define({_process_fishing_focal},{
-- Is the $1 fishing?
fishing_result := 'N'; -- not recorded
IF '$2' = 'INF' THEN
-- Infant fishing is recorded in the comments.
fishing_found := FALSE;
-- Ensure we capture the beginning and end of the comment
-- by putting a space on both ends.
devbeh_parts := REGEXP_SPLIT_TO_ARRAY(' ' || new_comment || ' '
, 'DEVBEH!\[[^]]*]');
IF ARRAY_LENGTH(devbeh_parts, 1) > 2 THEN
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| textualize(`this_comment')
|| '): More than one DEVBEH![...] in fishing food comment for $1';
ELSE
IF devbeh_parts[1] != (' ' || new_comment || ' ') THEN
-- There is a devbeh comment.
-- Get the content of the devbeh.
-- The existing Vua devbeh content does not contain
-- multiple entries, separated by semi-colon, so don't
-- worry about this.
<>
FOR tmp_array
IN SELECT REGEXP_MATCHES(new_comment , 'DEVBEH!\[([^]]*)]', 'g')
LOOP
devbeh_whole_content := tmp_array[1];
FOREACH devbeh_content
IN ARRAY REGEXP_SPLIT_TO_ARRAY(TRIM(BOTH FROM devbeh_whole_content)
, ';')
LOOP
devbeh_content := TRIM(BOTH FROM devbeh_content);
IF devbeh_content ~* ('^' || infant || ' vua') THEN
-- This is a fishing devbeh comment.
fishing_found := TRUE;
IF devbeh_content ~* ('^' || infant || ' vua$') THEN
-- unknown success
NULL;
ELSIF devbeh_content
~* ('^' || infant || ' vua' || ', negative results?$') THEN
fishing_result := 'U';
ELSE
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = ('
|| this_time
|| '): Value (intervals.comment) = ('
|| textualize(`this_comment')
|| '): Unrecognized Vua DEVBEH![...]';
END IF;
-- Get rid of the VUA devbeh.
new_comment := TRIM(BOTH FROM
ARRAY_TO_STRING(devbeh_parts, ''));
-- See if a Other events row exists for the infant's vua
SELECT events.eid, events.uncertain
INTO this_eid, this_uncertain
FROM events
JOIN parts ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid = parts.participant)
WHERE events.intid = this_intid
AND events.behavior = 'O'
AND chimpids.animid = infant
AND events.comment
= 'DEVBEH![' || devbeh_whole_content || ']'
LIMIT 1;
IF FOUND THEN
NULL;
-- -- We don't need the comment any more.
-- UPDATE events
-- SET comment = NULL
-- WHERE events.eid = this_eid;
ELSE
-- We don't have an events row. Make one.
this_uncertain
:= (SELECT NOT EXISTS
(SELECT 1
FROM events
JOIN parts
ON (parts.eid = events.eid)
JOIN chimpids
ON (chimpids.chimpid
= parts.participant)
WHERE events.intid = this_intid
AND NOT(events.uncertain)
AND chimpids.animid = infant
LIMIT 1));
INSERT INTO events
(intid, behavior, baboon
, uncertain
, comment)
VALUES (this_intid, 'F', FALSE
, this_uncertain
, 'DEVBEH![' || devbeh_whole_content || ']');
this_eid := currval('events_eid_seq'::REGCLASS);
-- And we need a corresponding parts row.
INSERT INTO parts (eid, part, participant)
VALUES (this_eid, 'S', infant);
END IF;
EXIT devbehs; -- Loop over DEVBEHs
END IF;
END LOOP; -- over ; parsed devbeh content
END LOOP devbehs; -- Over DEVEBHs
END IF;
END IF;
ELSE
-- Mom and sib have events rows for vua.
SELECT events.eid
INTO this_eid
FROM events
JOIN parts ON (parts.eid = events.eid)
JOIN chimpids ON (chimpids.chimpid = parts.participant)
WHERE events.intid = this_intid
AND events.behavior = 'VUA'
AND chimpids.animid = $1
LIMIT 1;
fishing_found := FOUND;
END IF;
IF fishing_found THEN
-- There is a fishing behavior.
makeup_food := FALSE;
-- See if we have a food comment applicable to the $1.
l := 0;
FOR foodcomment IN
SELECT regexp_matches(this_comment
-- don't try to split foodpart
, '(ALL|$2)FOOD!\[([^+]*)\+([^]]*)\]'
, 'g')
LOOP
l := l + 1;
IF l > 1 THEN
EXIT;
END IF;
END LOOP;
IF l = 0 THEN
-- We do not have a food comment.
IF NOT($1_eating_or_fishing)
-- or the page has changed and the food behavior is
-- not continuous.
OR ($1_page != this_page
AND allfood_page != this_page
AND ($1_last_btime IS NULL
OR $1_last_btime + '1 minute'::INTERVAL
!= this_time)) THEN
IF $1_page = this_page
AND allfood_page != this_page THEN
-- The $1 already has a food for the page.
NULL;
ELSIF allfood_page = this_page
AND $1_page != this_page THEN
-- The $1 is fishing the allfood that everybody else is fishing.
$1_foodparts := allfood_foodparts;
$1_foodkind := allfood_foodkind;
$1_food_changed := FALSE; -- May have changed, we don't care.
ELSIF this_uncertain THEN
-- When the eating is uncertain we can have no food comment.
makeup_food := TRUE;
ELSE
-- We must have a food comment to know what the $1 is fishing for.
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| textualize(`this_comment')
|| '): No fishing food comment for $1';
END IF;
END IF;
ELSIF l = 1 THEN
-- We have a food comment.
$1_food_comment := TRUE;
-- (We don't care if we had a food comment already or not.)
new_foodparts := TRIM(BOTH FROM foodcomment[2]);
new_foodkind := TRIM(BOTH FROM foodcomment[3]);
IF ($1_foodparts != new_foodparts OR $1_foodparts IS NULL
OR $1_foodkind != new_foodkind OR $1_foodkind IS NULL) THEN
$1_food_changed := TRUE;
$1_foodparts := new_foodparts;
$1_foodkind := new_foodkind;
END IF;
-- Remember what kind of food comment we have.
IF foodcomment[1] = 'ALL' THEN
allfood_found := TRUE;
allfood_page := this_page;
allfood_foodparts := new_foodparts;
allfood_foodkind := new_foodkind;
mom_page := 0; -- hack
infant_page := 0; -- hack
sib_page := 0; -- hack
ELSE
focalfood_found := TRUE;
$1_page := this_page;
allfood_page := 0; -- hack
END IF;
ELSE
-- We have too many food comments.
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| this_comment
|| '): Too many food comments for $1';
END IF;
-- Process the $1's foodcomment.
$1_processed := TRUE;
converted := converted + 1;
-- Update the old event with the new food code.
UPDATE events
SET behavior = 'F'
WHERE events.eid = this_eid;
IF makeup_food THEN
-- Insert a made up FISHED row.
INSERT INTO fished (eid, foodkind, foodpart, result)
VALUES (this_eid, 'UNRECORDED', 'NR', 'N');
ELSE
-- Go through the $1's foodparts.
these_foodparts := regexp_split_to_array($1_foodparts, ';');
FOREACH this_foodpart IN ARRAY these_foodparts LOOP
this_foodpart := TRIM(BOTH FROM this_foodpart);
-- Create the FISHED row.
INSERT INTO fished (eid, foodkind, foodpart
, result)
SELECT this_eid, $1_foodkind, foodpart_map.newfp
, fishing_result
FROM foodpart_map
WHERE foodpart_map.oldfp = this_foodpart;
IF NOT FOUND THEN
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| this_comment
|| '): Foodpart for $1 in comment ('
|| this_foodpart
|| ') is not a known old foodpart code';
END IF;
END LOOP; -- Over $1 foodparts
END IF;
$1_fishing := TRUE;
$1_last_btime := this_time;
ELSE -- No $1 fishing behavior.
-- Somebody's got a behavior
$1_fishing := FALSE;
END IF;
})dnl
changequote(`,')dnl
CREATE OR REPLACE FUNCTION prepared_data.convert_food(fromdate DATE,
todate DATE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
gmi_function_set_search_path
AS $$
-- Convert food from comments and unconverted eating behavior.
--
-- GPL_notice(` --', `2014', `The Meme Factory, Inc. http://www.meme.com/')
--
-- Syntax: select convert_food(fromdate, todate);
--
-- Arguments:
-- fromdate Date of earliest follows to convert (inclusive).
-- todate Date of latest follows to convert (inclusive).
--
-- Remarks:
-- Converts food for all follows on the date.
-- One-off program. Once it works we won't run it again.
DECLARE
r INT;
this_followid follows.followid%TYPE;
BEGIN
r := 0;
FOR this_followid IN
SELECT follows.followid
FROM follows
WHERE follows.date >= fromdate
AND follows.date <= todate
ORDER BY follows.date, follows.followid
LOOP
r := r + convert_food(this_followid);
END LOOP;
RETURN r;
END;
$$;
grant_func_priv(`prepared_data.convert_food(DATE, DATE)')
CREATE OR REPLACE FUNCTION prepared_data.convert_food(followdate DATE
, mom VARCHAR(gmi_animid_len))
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
gmi_function_set_search_path
AS $$
-- Convert food from comments and unconverted eating behavior.
--
-- GPL_notice(` --', `2014', `The Meme Factory, Inc. http://www.meme.com/')
--
-- Syntax: select convert_food(followdate, mom);
--
-- Arguments:
-- followdate Date of follow
-- mom chimpid of mom.
--
-- Remarks:
-- One-off program. Once it works we won't run it again.
DECLARE
this_followid follows.followid%TYPE;
BEGIN
-- Find the follow.
SELECT follows.followid
INTO this_followid
FROM follows
JOIN followparts ON (followparts.followid = follows.followid)
JOIN followroles ON (followroles.role = followparts.role)
JOIN chimpids AS fcid ON (fcid.chimpid = followparts.chimpid)
JOIN chimpids AS this_cid ON (this_cid.animid = fcid.animid)
WHERE follows.date = followdate
AND followroles.function = 'gmi_mom'
AND this_cid.chimpid = mom;
IF NOT FOUND THEN
return 0;
END IF;
RETURN convert_food(this_followid);
END;
$$;
grant_func_priv(`prepared_data.convert_food(DATE, VARCHAR(gmi_animid_len))')
CREATE OR REPLACE FUNCTION prepared_data.convert_food(this_followid INT)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
gmi_function_set_search_path
AS $$
-- Convert food from comments and unconverted eating behavior.
--
-- GPL_notice(` --', `2014', `The Meme Factory, Inc. http://www.meme.com/')
--
-- Syntax: select convert_food(followid);
--
-- Arguments:
-- followid Followid of follow to convert.
--
-- Remarks:
-- One-off program. Once it works we won't run it again.
DECLARE
converted INT := 0;
this_intid intervals.intid%TYPE;
this_time intervals.time%TYPE;
this_comment intervals.comment%TYPE;
this_page sheets.page%TYPE;
badobs BOOLEAN;
mom biography_data.animid%TYPE;
sib biography_data.animid%TYPE;
infant biography_data.animid%TYPE;
mom_last_btime intervals.time%TYPE;
sib_last_btime intervals.time%TYPE;
infant_last_btime intervals.time%TYPE;
mom_eating BOOLEAN;
sib_eating BOOLEAN;
infant_eating BOOLEAN;
mom_fishing BOOLEAN;
sib_fishing BOOLEAN;
infant_fishing BOOLEAN;
mom_eating_or_fishing BOOLEAN;
sib_eating_or_fishing BOOLEAN;
infant_eating_or_fishing BOOLEAN;
mom_foodkind eats.foodkind%TYPE;
infant_foodkind eats.foodkind%TYPE;
sib_foodkind eats.foodkind%TYPE;
mom_foodparts TEXT;
infant_foodparts TEXT;
sib_foodparts TEXT;
new_foodkind TEXT;
new_foodparts TEXT;
mom_page sheets.page%TYPE;
infant_page sheets.page%TYPE;
sib_page sheets.page%TYPE;
mom_processed BOOLEAN;
infant_processed BOOLEAN;
sib_processed BOOLEAN;
mom_food_comment BOOLEAN;
infant_food_comment BOOLEAN;
sib_food_comment BOOLEAN;
mom_food_changed BOOLEAN;
infant_food_changed BOOLEAN;
sib_food_changed BOOLEAN;
allfood_found BOOLEAN;
focalfood_found BOOLEAN;
fishing_found BOOLEAN;
fishing_result fished.result%TYPE;
allfood_page INT;
allfood_foodkind TEXT;
allfood_foodparts TEXT;
devbeh_parts TEXT[];
tmp_array TEXT[];
devbeh_content TEXT;
devbeh_whole_content TEXT;
this_eid events.eid%TYPE;
this_uncertain events.uncertain%TYPE;
foodcomment TEXT[];
makeup_food BOOLEAN;
l INT;
these_foodparts TEXT[];
this_foodpart TEXT;
comment_parts TEXT[];
new_comment TEXT;
food_fragment TEXT[];
new_fragment TEXT;
fixed_comment TEXT;
p INT;
BEGIN
-- Create a temporary table to map old foodpart codes to new.
CREATE TEMP TABLE IF NOT EXISTS
foodpart_map (oldfp TEXT PRIMARY KEY, newfp TEXT)
ON COMMIT DROP;
PERFORM 1
FROM foodpart_map
LIMIT 1;
IF NOT FOUND THEN
INSERT INTO foodpart_map (oldfp, newfp)
VALUES
('S', 'IN')
, ('G', 'BA')
, ('MIFUPA', 'BO')
, ('M', 'FL')
, ('T', 'FR')
, ('J', 'LE')
, ('N', 'ME')
, ('NONE', 'NC')
, ('NA', 'NC')
, ('D', 'IN')
, ('B', 'PI')
, ('MIZIZI', 'RO')
, ('U', 'SA')
, ('MB', 'SE')
, ('C', 'SH')
, ('W', 'TE')
, ('MITI', 'TR')
, ('UNRECORDED', 'NR')
, ('UNKNOWN', 'UNK');
END IF;
-- Find mom, infant, and sib.
SELECT chimpids.animid
INTO mom
FROM followparts
JOIN followroles ON (followroles.role = followparts.role)
JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)
WHERE followparts.followid = this_followid
AND followroles.function = 'gmi_mom'
LIMIT 1;
SELECT chimpids.animid
INTO infant
FROM followparts
JOIN followroles ON (followroles.role = followparts.role)
JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)
WHERE followparts.followid = this_followid
AND followroles.function = 'gmi_infant'
LIMIT 1;
SELECT chimpids.animid
INTO sib
FROM followparts
JOIN followroles ON (followroles.role = followparts.role)
JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)
WHERE followparts.followid = this_followid
AND followroles.function = 'gmi_sib'
LIMIT 1;
-- Setup for loop.
mom_eating := FALSE;
infant_eating := FALSE;
sib_eating := FALSE;
mom_fishing := FALSE;
infant_fishing := FALSE;
sib_fishing := FALSE;
mom_eating_or_fishing := FALSE;
infant_eating_or_fishing := FALSE;
sib_eating_or_fishing := FALSE;
mom_page := 0;
infant_page := 0;
sib_page := 0;
allfood_page := 0;
mom_last_btime := NULL; -- time of last eating or fishing behavior
sib_last_btime := NULL;
infant_last_btime := NULL;
-- Go through all the intervals for the follow by time.
FOR this_intid, this_time, this_comment, this_page IN
SELECT intervals.intid, intervals.time, intervals.comment, sheets.page
FROM intervals
JOIN sheets ON (sheets.sheetid = intervals.sheetid)
WHERE intervals.followid = this_followid
ORDER BY intervals.time
LOOP
new_comment := this_comment;
mom_food_changed := FALSE;
infant_food_changed := FALSE;
sib_food_changed := FALSE;
mom_food_comment := FALSE;
infant_food_comment := FALSE;
sib_food_comment := FALSE;
-- Is the interval a badobs (no events)?
PERFORM 1
FROM events
JOIN parts ON (parts.eid = events.eid)
JOIN chimpids ON (chimpids.chimpid = parts.participant)
WHERE events.intid = this_intid
LIMIT 1;
badobs := NOT(FOUND);
mom_processed := FALSE;
infant_processed := FALSE;
sib_processed := FALSE;
-- badobs rows are ignored.
IF NOT(badobs) THEN
allfood_found := FALSE;
focalfood_found := FALSE;
-- Do the mom.
_process_eating_focal(`mom', `MA')
_process_fishing_focal(`mom', `MA')
mom_eating_or_fishing := mom_eating OR mom_fishing;
-- Do the infant.
_process_eating_focal(`infant', `INF')
_process_fishing_focal(`infant', `INF')
infant_eating_or_fishing := infant_eating OR infant_fishing;
-- Do the sib.
_process_eating_focal(`sib', `SIB')
_process_fishing_focal(`sib', `SIB')
sib_eating_or_fishing := sib_eating OR sib_fishing;
IF allfood_found AND focalfood_found THEN
-- Can't have an allfood comment with a MA/INF/SIB food comment.
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| this_comment
|| '): Cannot have both an ALLFOOD and a MA/INF/SIBFOOD in the comment';
END IF;
END IF; -- badobs row
-- See that we have comments for the food of all focals
-- if any of the focal's foods have changed.
IF (mom_food_changed
OR infant_food_changed
OR sib_food_changed)
AND ((mom_eating_or_fishing AND NOT(mom_food_comment))
OR (infant_eating_or_fishing AND NOT(infant_food_comment))
OR (sib_eating_or_fishing AND NOT(sib_food_comment))) THEN
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): Value (intervals.comment) = ('
|| this_comment
|| '): One of the focals has changed foodpart/foodkind but what is eaten by the other focals is not restated in the comments';
END IF;
-- Clean up the comment.
IF mom_processed OR infant_processed OR sib_processed THEN
-- Get rid of any ALLFOOD comment that exists.
comment_parts := regexp_split_to_array(new_comment
, 'ALLFOOD!\[([^+]*)\+([^]]*)\]');
IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN
new_comment := ARRAY_TO_STRING(comment_parts, '');
END IF;
END IF;
IF mom_processed THEN
-- Get rid of any MAFOOD comment that exists.
comment_parts := regexp_split_to_array(new_comment
, 'MAFOOD!\[([^+]*)\+([^]]*)\]');
IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN
new_comment := ARRAY_TO_STRING(comment_parts, '');
END IF;
END IF;
IF infant_processed THEN
-- Get rid of any INFFOOD comment that exists.
comment_parts := regexp_split_to_array(new_comment
, 'INFFOOD!\[([^+]*)\+([^]]*)\]');
IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN
new_comment := ARRAY_TO_STRING(comment_parts, '');
END IF;
END IF;
IF sib_processed THEN
-- Get rid of any SIBFOOD comment that exists.
comment_parts := regexp_split_to_array(new_comment
, 'SIBFOOD!\[([^+]*)\+([^]]*)\]');
IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN
new_comment := ARRAY_TO_STRING(comment_parts, '');
END IF;
END IF;
-- Save the comment with the food stuff removed.
IF new_comment != this_comment THEN
IF TRIM(BOTH FROM new_comment) = '' THEN
new_comment := NULL;
END IF;
-- Leave comments untouched.
-- UPDATE intervals
-- SET comment = new_comment
-- WHERE intervals.intid = this_intid;
END IF;
IF SUBSTRING(new_comment FROM 'FOOD!\[') IS NOT NULL THEN
-- Change the food looking comment to a FOODRELATED comment.
fixed_comment := this_comment;
FOR food_fragment IN
SELECT REGEXP_MATCHES(
new_comment
, '((?:(?:ALL)|(?:MA)|(?:INF)|(?:SIB))FOOD!\[[^]]*])'
, 'g')
LOOP
new_fragment := food_fragment[1];
IF SUBSTRING(new_fragment FOR 2) = 'MA' THEN
new_fragment := 'MAFOODRELATED' || SUBSTRING(new_fragment FROM 7);
ELSE
new_fragment := SUBSTRING(new_fragment FOR 3)
|| 'FOODRELATED'
|| SUBSTRING(new_fragment FROM 8);
END IF;
p := POSITION(food_fragment[1] IN fixed_comment);
l := CHAR_LENGTH(food_fragment[1]);
fixed_comment := SUBSTRING(fixed_comment FOR p - 1)
|| new_fragment
|| SUBSTRING(fixed_comment FROM p + l);
-- Change the comment in the new_comment too, just so we've
-- something to test to see if there's something foodlike
-- left in the comments.
p := POSITION(food_fragment[1] IN new_comment);
new_comment := SUBSTRING(new_comment FOR p - 1)
|| new_fragment
|| SUBSTRING(new_comment FROM p + l);
END LOOP;
-- Update the comment in the interval.
UPDATE intervals
SET comment = fixed_comment
WHERE intervals.intid = this_intid;
IF SUBSTRING(new_comment FROM 'FOOD!\[') IS NOT NULL THEN
RAISE EXCEPTION data_exception USING
MESSAGE = 'Error found in comment'
, DETAIL = 'Key (intervals.intid) = (' || this_intid
|| '): Value (intervals.followid) = ('
|| this_followid
|| '): Value (follows.date) = ('
|| (SELECT follows.date
FROM follows
WHERE follows.followid = this_followid
LIMIT 1)
|| '): Value mother (followparts.chimpid) = ('
|| mom
|| '): Value (intervals.time) = (' || this_time
|| '): New Value For (intervals.comment) = ('
|| fixed_comment
|| '): A food-looking comment remains but there is no _ca_eating, or VUA behavior (EVENTS row) or DEVBEH Vua comment for the corresponding focal for the interval';
END IF;
END IF;
END LOOP; -- Over intervals for the follow/day.
RETURN converted;
END;
$$;
grant_func_priv(`prepared_data.convert_food(INT)')