dnl Copyright (C) 2013 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 Function to convert group composition from access table structure
dnl to new table structure.
dnl
dnl Karl O. Pinc
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`types.m4')
include(`macros.m4')dnl
dnl Reset our search path so we pick up the conversion schema.
define(`gmi_function_search_path', `prepared_data, gombemi, pg_temp')
CREATE OR REPLACE FUNCTION prepared_data.convert_access_gc(fromdate DATE
, todate DATE)
RETURNS INT
LANGUAGE plpgsql
SECURITY DEFINER
gmi_function_set_search_path
AS $$
-- Copy access data group comp structures into new gombemi data structures.
--
-- GPL_notice(` --', `2013', `The Meme Factory, Inc. http://www.meme.com/')
--
-- Syntax: select convert_access_gc(fromdate, todate);
--
-- Arguments:
-- fromdate Date to start copy (inclusive)
-- todate Date to stop copy (inclusive)
--
-- Remarks:
-- One-off program. Once it works we won't run it again.
--
-- Copies a day a a time.
--
-- Stops as soon as a transaction fails.
DECLARE
converted INT := 0;
this_gcstatus fieldfollows.gcstatus%TYPE;
got_gc BOOLEAN;
this_sheetid sheets.sheetid%TYPE;
this_ffid fieldfollows.ffid%TYPE;
rows_found INT;
this_follow RECORD;
this_follow_5_minute RECORD;
this_follow_5_minute_group RECORD;
this_uncertain BOOLEAN;
this_baboons BOOLEAN;
these_chimpids VARCHAR(gmi_animid_len)[];
i INT;
this_chimpid raw_groupmembers.chimpid%TYPE;
BEGIN
FOR this_follow
IN SELECT *
FROM follow
WHERE fromdate <= follow.follow_date
AND follow.follow_date <= todate
ORDER BY follow.follow_date, follow.follow_id
LOOP
-- Find the follows.gcstatus of the follow.
SELECT crfp.group_blank
INTO this_gcstatus
FROM crfp
WHERE crfp.follow_date = this_follow.follow_date
AND crfp.focal_mom = this_follow.mother_id;
IF NOT FOUND THEN
RAISE EXCEPTION integrity_constraint_violation USING
MESSAGE = 'Cannot lookup gcstatus (group_blank) for follow on cross_reference_for_program'
, DETAIL = 'Value (Follow_id) = ('
|| this_follow.follow_id
|| '): Value (date) = ('
|| this_follow.follow_date
|| '): Mother = (' || this_follow.mother_id
|| ')';
END IF;
IF this_gcstatus IS NULL THEN
RAISE EXCEPTION integrity_constraint_violation USING
MESSAGE = 'gcstatus (group_blank) for follow on cross_reference_forprogram is wrong'
, DETAIL = 'Value (Follow_id) = ('
|| this_follow.follow_id
|| '): Value (date) = ('
|| this_follow.follow_date
|| '): Mother = (' || this_follow.mother_id
|| '): group_blank is NULL';
END IF;
got_gc := FALSE;
FOR this_follow_5_minute
IN SELECT *
FROM follow_5_minute
WHERE follow_5_minute.follow_id = this_follow.follow_id
ORDER BY follow_5_minute.interval_time DESC
LOOP
--
-- follow_5_minute:
-- tableid | Page_id | Interval_number | Interval_time | Mother_feeding | Food_part_initials | Food_name | Infant_feeding | Strange_chimp | Other_animals | Interval_id
-- 1 | 11/08/2010_GM_01 | 1 | 2010-11-08 09:00:00 | - | | | - | | | 11/08/2010_GM_01_01
-- Check for already converted.
IF this_follow_5_minute.done THEN
RAISE EXCEPTION 'Date %: Mom %: Interval_ID %: follow_5_minute row already converted'
, this_follow.follow_date
, this_follow.mother_id
, this_follow_5_minute.interval_id;
END IF;
-- Find the sheet.
-- Technically, it's the page/collector that's unique. But
-- in the access data it's enough to have a unique page.
SELECT sheets.sheetid, sheets.ffid
INTO this_sheetid, this_ffid
FROM follows
JOIN followparts ON (followparts.followid = follows.followid)
JOIN followroles ON (followroles.role = followparts.role)
JOIN intervals ON (intervals.followid = follows.followid)
JOIN sheets ON (sheets.sheetid = intervals.sheetid)
WHERE follows.date = this_follow.follow_date
AND followparts.chimpid
= convert_animid(this_follow.mother_id,
this_follow.follow_date)
AND followroles.function = 'gmi_mom'
AND sheets.page = SPLIT_PART(this_follow_5_minute.page_id
, '_', 3)::smallint;
GET DIAGNOSTICS rows_found = ROW_COUNT;
CASE
WHEN rows_found = 0 THEN
RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Unable to find row on SHEETS for this follow for this page to match follow_5_minute row'
, this_follow.follow_id
, this_follow_5_minute.interval_id
, this_follow_5_minute.page_id;
RETURN NULL;
WHEN rows_found > 1 THEN
RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: More than one SHEETS row found for this follow for this page to match follow_5_minute row'
, this_follow.follow_id
, this_follow_5_minute.interval_id
, this_follow_5_minute.page_id;
RETURN NULL;
ELSE
NULL;
END CASE;
-- Intepret the other_animals column.
CASE
WHEN this_follow_5_minute.other_animals = 'Y' THEN
this_baboons = TRUE;
WHEN this_follow_5_minute.other_animals = '?' THEN
this_baboons = NULL;
WHEN this_follow_5_minute.other_animals IS NULL THEN
this_baboons = FALSE;
ELSE
RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Other_animals %: Unknown value for other_animals for this follow_5_minute_group'
, this_follow.follow_id
, this_follow_5_minute.interval_id
, this_follow_5_minute.page_id
, this_follow_5_minute.other_animals;
RETURN NULL;
END CASE;
-- Create the RAW_GROUPS row.
INSERT INTO raw_groups (time, baboons, sheetid)
VALUES (this_follow_5_minute.interval_time
, this_baboons
, this_sheetid);
-- Interpret the strange_chimp flag.
IF this_follow_5_minute.strange_chimp IS NOT NULL THEN
CASE this_follow_5_minute.strange_chimp
WHEN 'Y' THEN
this_uncertain := FALSE;
WHEN '?' THEN
this_uncertain := TRUE;
ELSE
RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Strange_chimp %: Unknown value for strange_chimp for this follow_5_minute'
, this_follow.follow_id
, this_follow_5_minute.interval_id
, this_follow_5_minute.page_id
, this_follow_5_minute.strange_chimp;
RETURN NULL;
END CASE;
-- Insert raw_groupmembers row.
INSERT INTO raw_groupmembers (rgid
, chimpid
, origin
, uncertain
, swelling)
VALUES (CURRVAL('raw_groups_rgid_seq'::regclass)
, 'MGE'
, 'G' -- is this right?
, this_uncertain
, NULL);
END IF;
-- Remember the chimpids in the group comp.
-- Don't worrry about MGE since she's never a mom or infant.
these_chimpids := ARRAY[]::VARCHAR(gmi_animid_len)[];
i := 0;
FOR this_follow_5_minute_group
IN SELECT *
FROM follow_5_minute_group
WHERE follow_5_minute_group.interval_id
= this_follow_5_minute.interval_id
ORDER BY follow_5_minute_group.chimp_id
LOOP
-- Check for already converted.
IF this_follow_5_minute_group.done THEN
RAISE EXCEPTION 'Date %: Mom %: Interval_ID %: Chimp_id %: follow_5_minute_group row already converted'
, this_follow.follow_date
, this_follow.mother_id
, this_follow_5_minute.interval_id
, this_follow_5_minute_group.chimp_id;
END IF;
--
-- follow_5_minute_left_group:
-- tableid | Interval_id | Position_number | Chimp_id | Present | Position_id
-- 1 | 11/03/2010_GA_01_01 | 1 | FD | Y | 11/03/2010_GA_01_01_01
--
-- follow_5_minute_right_group:
-- tableid | Interval_id | Position_number | Chimp_id | Present | Position_id
-- 1 | 01/01/1997_SA_01_02 | 16 | SW | Y | 01/01/1997_SA_01_02_16
-- Interpret the present flag.
CASE this_follow_5_minute_group.present
WHEN 'Y' THEN
this_uncertain = FALSE;
WHEN '?' THEN
this_uncertain = TRUE;
ELSE
RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Animid %: Present %: Unknown value for present for this follow_5_minute_group individual'
, this_follow.follow_id
, this_follow_5_minute.interval_id
, this_follow_5_minute.page_id
, this_follow_5_minute_group.animid
, this_follow_5_minute_group.present;
RETURN NULL;
END CASE;
-- Remember the chimpid.
these_chimpids[i] := convert_animid(
this_follow_5_minute_group.chimp_id
, this_follow.follow_date);
-- Insert raw_groupmembers row.
INSERT INTO raw_groupmembers (rgid
, chimpid
, origin
, uncertain
, swelling)
VALUES (CURRVAL('raw_groups_rgid_seq'::regclass)
, these_chimpids[i]
, 'G' -- is this right?
, this_uncertain
, NULL);
-- Note follow 5 minute group converted.
UPDATE follow_5_minute_group
SET done = true
WHERE tableid = this_follow_5_minute_group.tableid;
i := i + 1;
END LOOP; -- over follow_5_minute_group
-- Go through the members of the group comp and make sure
-- the data is correct.
FOREACH this_chimpid IN ARRAY these_chimpids LOOP
-- Ensure the mom is in the group comp.
PERFORM mom_in_gc(this_chimpid
, CURRVAL('raw_groups_rgid_seq'::regclass)::INT
, this_follow.follow_date);
-- Ensure that young infants are in the group comp.
PERFORM infants_in_gc(this_chimpid
, CURRVAL('raw_groups_rgid_seq'::regclass)::INT
, this_follow.follow_date);
-- Ensure all social partners are in the group comp.
PERFORM add_social_partners(
this_chimpid
, CURRVAL('raw_groups_rgid_seq'::regclass)::INT);
END LOOP;
-- Note follow 5 minute converted.
UPDATE follow_5_minute
SET done = true
WHERE tableid = this_follow_5_minute.tableid;
-- We got a group comp for the follow.
got_gc := TRUE;
END LOOP; -- over follow_5_minute
IF got_gc THEN
IF this_gcstatus = 'N' THEN
RAISE EXCEPTION integrity_constraint_violation USING
MESSAGE = 'gcstatus (group_blank) for follow on cross_reference_forprogram is wrong'
, DETAIL = 'Value (Follow_id) = ('
|| this_follow.follow_id
|| '): Value (date) = ('
|| this_follow.follow_date
|| '): Mother = (' || this_follow.mother_id
|| '): group_blank = (' || this_gcstatus
|| '): Group compositions exist for this follow';
END IF;
-- Give the follow a status.
UPDATE fieldfollows
SET gcstatus = this_gcstatus
WHERE ffid = this_ffid;
ELSE -- didn't find any group comp.
IF this_gcstatus != 'N' THEN
RAISE EXCEPTION integrity_constraint_violation USING
MESSAGE = 'gcstatus (group_blank) for follow on cross_reference_forprogram is wrong'
, DETAIL = 'Value (Follow_id) = ('
|| this_follow.follow_id
|| '): Value (date) = ('
|| this_follow.follow_date
|| '): Mother = (' || this_follow.mother_id
|| '): group_blank = (' || this_gcstatus
|| '): No group composition exists for this follow';
END IF;
END IF;
-- Keep track of how many follows we converted.
converted := converted + 1;
END LOOP;
RETURN converted;
END;
$$;
grant_func_priv(`prepared_data.convert_access_gc(DATE, DATE)')