darcs - gombemi

2 dnl

3 dnl This file is part of Gombe-MI.

4 dnl

5 dnl Gombe-MI is free software; you can redistribute it and/or modify

6 dnl it under the terms of the GNU General Public License as published by

7 dnl the Free Software Foundation; either version 3 of the License, or

8 dnl (at your option) any later version.

9 dnl

10 dnl This program is distributed in the hope that it will be useful,

11 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of

12 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

13 dnl GNU General Public License for more details.

14 dnl

15 dnl You should have received a copy of the GNU General Public License

16 dnl along with Gombe-MI. If not, see <http://www.gnu.org/licenses/>.

17 dnl

18 dnl Functions to convert food and fishing from comments.

19 dnl

20 dnl Bugs:

21 dnl It's grody to have so many macros but we're only running this once.

22 dnl

23 dnl Karl O. Pinc <kop@meme.com>

25 dnl m4 includes

26 include(`copyright.m4')

27 include(`constants.m4')

28 include(`types.m4')

29 include(`macros.m4')dnl

31 dnl The behavior code for eating.

32 define(`_ca_eating', `UE')

34 dnl plpgsql fragment for processing a focal's food comment

35 dnl

36 dnl Syntax: _process_eating_focal(focal, prefix)

37 dnl

38 dnl focal Either "mom", "infant", or "sib".

39 dnl prefix Either "MA", "INF", or "SIB".

40 dnl

41 changequote({,})

42 define({_process_eating_focal},{

43 -- Is the $1 eating?

44 SELECT events.eid, events.uncertain

45 INTO this_eid, this_uncertain

46 FROM events

47 JOIN parts ON (parts.eid = events.eid)

48 JOIN chimpids ON (chimpids.chimpid = parts.participant)

49 WHERE events.intid = this_intid

50 AND events.behavior = '_ca_eating'

51 AND chimpids.animid = $1

52 LIMIT 1;

54 IF FOUND THEN

55 -- There is an eating behavior.

56 makeup_food := FALSE;

58 -- See if we have a food comment applicable to the $1.

59 l := 0;

60 FOR foodcomment IN

61 SELECT regexp_matches(this_comment

62 -- don't try to split foodpart

63 , '(ALL|$2)FOOD!\[([^+]*)\+([^]]*)\]'

64 , 'g')

65 LOOP

67 l := l + 1;

68 IF l > 1 THEN

69 EXIT;

70 END IF;

71 END LOOP;

73 IF l = 0 THEN

74 -- We do not have a food comment.

75 IF NOT($1_eating_or_fishing)

76 -- or the page has changed and the food behavior is

77 -- not continuous.

78 OR ($1_page != this_page

79 AND allfood_page != this_page

80 AND ($1_last_btime IS NULL

81 OR $1_last_btime + '1 minute'::INTERVAL

82 != this_time)) THEN

84 IF $1_page = this_page

85 AND allfood_page != this_page THEN

86 -- The $1 already has a food for the page.

87 NULL;

89 ELSIF allfood_page = this_page

90 AND $1_page != this_page THEN

91 -- The $1 is eating the allfood that everybody else is eating.

92 $1_foodparts := allfood_foodparts;

93 $1_foodkind := allfood_foodkind;

94 $1_food_changed := FALSE; -- May have changed, we don't care.

96 ELSIF this_uncertain THEN

97 -- When the eating is uncertain we can have no food comment.

98 makeup_food := TRUE;

100 ELSE

101 -- We must have a food comment to know what the $1 is eating.

102 RAISE EXCEPTION data_exception USING

103 MESSAGE = 'Error found in comment'

104 , DETAIL = 'Key (intervals.intid) = (' || this_intid

105 || '): Value (intervals.followid) = ('

106 || this_followid

107 || '): Value (follows.date) = ('

108 || (SELECT follows.date

109 FROM follows

110 WHERE follows.followid = this_followid

111 LIMIT 1)

112 || '): Value mother (followparts.chimpid) = ('

113 || mom

114 || '): Value (intervals.time) = (' || this_time

115 || '): Value (intervals.comment) = ('

116 || textualize(`this_comment')

117 || '): No eating food comment for $1';

118 END IF;

119 END IF;

120

121 ELSIF l = 1 THEN

122 -- We have a food comment.

123 $1_food_comment := TRUE;

124

125 -- (We don't care if we had a food comment already or not.)

126 new_foodparts := TRIM(BOTH FROM foodcomment[2]);

127 new_foodkind := TRIM(BOTH FROM foodcomment[3]);

128

129 IF ($1_foodparts != new_foodparts OR $1_foodparts IS NULL

130 OR $1_foodkind != new_foodkind OR $1_foodkind IS NULL) THEN

131 $1_food_changed := TRUE;

132 $1_foodparts := new_foodparts;

133 $1_foodkind := new_foodkind;

134 END IF;

135

136 -- Remember what kind of food comment we have.

137 IF foodcomment[1] = 'ALL' THEN

138 allfood_found := TRUE;

139 allfood_page := this_page;

140 allfood_foodparts := new_foodparts;

141 allfood_foodkind := new_foodkind;

142 mom_page := 0; -- hack

143 infant_page := 0; -- hack

144 sib_page := 0; -- hack

145 ELSE

146 focalfood_found := TRUE;

147 $1_page := this_page;

148 allfood_page := 0; -- hack

149 END IF;

150 ELSE

151 -- We have too many food comments.

152 RAISE EXCEPTION data_exception USING

153 MESSAGE = 'Error found in comment'

154 , DETAIL = 'Key (intervals.intid) = (' || this_intid

155 || '): Value (intervals.followid) = ('

156 || this_followid

157 || '): Value (follows.date) = ('

158 || (SELECT follows.date

159 FROM follows

160 WHERE follows.followid = this_followid

161 LIMIT 1)

162 || '): Value mother (followparts.chimpid) = ('

163 || mom

164 || '): Value (intervals.time) = (' || this_time

165 || '): Value (intervals.comment) = ('

166 || this_comment

167 || '): Too many food comments for $1';

168 END IF;

169

170 -- Process the $1's foodcomment.

171 $1_processed := TRUE;

172 converted := converted + 1;

173

174 -- Update the old event with the new food code.

175 UPDATE events

176 SET behavior = 'E'

177 WHERE events.eid = this_eid;

178

179 IF makeup_food THEN

180 -- Insert a made up EATS row.

181 INSERT INTO eats (eid, foodkind, foodpart)

182 VALUES (this_eid, 'UNRECORDED', 'NR');

183 ELSE

184 -- Go through the $1's foodparts.

185 these_foodparts := regexp_split_to_array($1_foodparts, ';');

186 FOREACH this_foodpart IN ARRAY these_foodparts LOOP

187 this_foodpart := TRIM(BOTH FROM this_foodpart);

188

189 -- Create the EATS row.

190 INSERT INTO eats (eid, foodkind, foodpart)

191 SELECT this_eid, $1_foodkind, foodpart_map.newfp

192 FROM foodpart_map

193 WHERE foodpart_map.oldfp = this_foodpart;

194

195 IF NOT FOUND THEN

196 RAISE EXCEPTION data_exception USING

197 MESSAGE = 'Error found in comment'

198 , DETAIL = 'Key (intervals.intid) = (' || this_intid

199 || '): Value (intervals.followid) = ('

200 || this_followid

201 || '): Value (follows.date) = ('

202 || (SELECT follows.date

203 FROM follows

204 WHERE follows.followid = this_followid

205 LIMIT 1)

206 || '): Value mother (followparts.chimpid) = ('

207 || mom

208 || '): Value (intervals.time) = (' || this_time

209 || '): Value (intervals.comment) = ('

210 || this_comment

211 || '): Foodpart for $1 in comment ('

212 || this_foodpart

213 || ') is not a known old foodpart code';

214 END IF;

215

216 END LOOP; -- Over $1 foodparts

217 END IF;

218

219 $1_eating := TRUE;

220 $1_last_btime := this_time;

221 ELSE -- No $1 eating behavior.

222

223 -- Somebody's got a behavior

224 $1_eating := FALSE;

225 END IF;

226 })dnl

227 changequote(`,')dnl

228

229 dnl plpgsql fragment for processing a focal's food comment

230 dnl

231 dnl Syntax: _process_fishing_focal(focal, prefix)

232 dnl

233 dnl focal Either "mom", "infant", or "sib".

234 dnl prefix Either "MA", "INF", or "SIB".

235 dnl

236 dnl This is almost identical to _process_eating_focal,

237 dnl the changes are in the behavior codes, error messages

238 dnl and inserting into FISHED,

239 dnl but it's too much trouble to abstract out the

240 dnl difference.

241 dnl

242 changequote({,})

243 define({_process_fishing_focal},{

244 -- Is the $1 fishing?

245 fishing_result := 'N'; -- not recorded

246

247 IF '$2' = 'INF' THEN

248 -- Infant fishing is recorded in the comments.

249 fishing_found := FALSE;

250 -- Ensure we capture the beginning and end of the comment

251 -- by putting a space on both ends.

252 devbeh_parts := REGEXP_SPLIT_TO_ARRAY(' ' || new_comment || ' '

253 , 'DEVBEH!\[[^]]*]');

254 IF ARRAY_LENGTH(devbeh_parts, 1) > 2 THEN

255 RAISE EXCEPTION data_exception USING

256 MESSAGE = 'Error found in comment'

257 , DETAIL = 'Key (intervals.intid) = (' || this_intid

258 || '): Value (intervals.followid) = ('

259 || this_followid

260 || '): Value (follows.date) = ('

261 || (SELECT follows.date

262 FROM follows

263 WHERE follows.followid = this_followid

264 LIMIT 1)

265 || '): Value mother (followparts.chimpid) = ('

266 || mom

267 || '): Value (intervals.time) = (' || this_time

268 || '): Value (intervals.comment) = ('

269 || textualize(`this_comment')

270 || '): More than one DEVBEH![...] in fishing food comment for $1';

271 ELSE

272 IF devbeh_parts[1] != (' ' || new_comment || ' ') THEN

273 -- There is a devbeh comment.

274 -- Get the content of the devbeh.

275 -- The existing Vua devbeh content does not contain

276 -- multiple entries, separated by semi-colon, so don't

277 -- worry about this.

278 <<devbehs>>

279 FOR tmp_array

280 IN SELECT REGEXP_MATCHES(new_comment , 'DEVBEH!\[([^]]*)]', 'g')

281 LOOP

282 devbeh_whole_content := tmp_array[1];

283 FOREACH devbeh_content

284 IN ARRAY REGEXP_SPLIT_TO_ARRAY(TRIM(BOTH FROM devbeh_whole_content)

285 , ';')

286 LOOP

287 devbeh_content := TRIM(BOTH FROM devbeh_content);

288 IF devbeh_content ~* ('^' || infant || ' vua') THEN

289 -- This is a fishing devbeh comment.

290 fishing_found := TRUE;

291

292 IF devbeh_content ~* ('^' || infant || ' vua$') THEN

293 -- unknown success

294 NULL;

295 ELSIF devbeh_content

296 ~* ('^' || infant || ' vua' || ', negative results?$') THEN

297 fishing_result := 'U';

298 ELSE

299 RAISE EXCEPTION data_exception USING

300 MESSAGE = 'Error found in comment'

301 , DETAIL = 'Key (intervals.intid) = (' || this_intid

302 || '): Value (intervals.followid) = ('

303 || this_followid

304 || '): Value (follows.date) = ('

305 || (SELECT follows.date

306 FROM follows

307 WHERE follows.followid = this_followid

308 LIMIT 1)

309 || '): Value mother (followparts.chimpid) = ('

310 || mom

311 || '): Value (intervals.time) = ('

312 || this_time

313 || '): Value (intervals.comment) = ('

314 || textualize(`this_comment')

315 || '): Unrecognized Vua DEVBEH![...]';

316 END IF;

317

318 -- Get rid of the VUA devbeh.

319 new_comment := TRIM(BOTH FROM

320 ARRAY_TO_STRING(devbeh_parts, ''));

321

322 -- See if a Other events row exists for the infant's vua

323 SELECT events.eid, events.uncertain

324 INTO this_eid, this_uncertain

325 FROM events

326 JOIN parts ON (parts.eid = events.eid)

327 JOIN chimpids

328 ON (chimpids.chimpid = parts.participant)

329 WHERE events.intid = this_intid

330 AND events.behavior = 'O'

331 AND chimpids.animid = infant

332 AND events.comment

333 = 'DEVBEH![' || devbeh_whole_content || ']'

334 LIMIT 1;

335

336 IF FOUND THEN

337 NULL;

338 -- -- We don't need the comment any more.

339 -- UPDATE events

340 -- SET comment = NULL

341 -- WHERE events.eid = this_eid;

342 ELSE

343 -- We don't have an events row. Make one.

344 this_uncertain

345 := (SELECT NOT EXISTS

346 (SELECT 1

347 FROM events

348 JOIN parts

349 ON (parts.eid = events.eid)

350 JOIN chimpids

351 ON (chimpids.chimpid

352 = parts.participant)

353 WHERE events.intid = this_intid

354 AND NOT(events.uncertain)

355 AND chimpids.animid = infant

356 LIMIT 1));

357

358 INSERT INTO events

359 (intid, behavior, baboon

360 , uncertain

361 , comment)

362 VALUES (this_intid, 'F', FALSE

363 , this_uncertain

364 , 'DEVBEH![' || devbeh_whole_content || ']');

365

366 this_eid := currval('events_eid_seq'::REGCLASS);

367

368 -- And we need a corresponding parts row.

369 INSERT INTO parts (eid, part, participant)

370 VALUES (this_eid, 'S', infant);

371 END IF;

372

373 EXIT devbehs; -- Loop over DEVBEHs

374 END IF;

375 END LOOP; -- over ; parsed devbeh content

376 END LOOP devbehs; -- Over DEVEBHs

377 END IF;

378 END IF;

379

380 ELSE

381 -- Mom and sib have events rows for vua.

382 SELECT events.eid

383 INTO this_eid

384 FROM events

385 JOIN parts ON (parts.eid = events.eid)

386 JOIN chimpids ON (chimpids.chimpid = parts.participant)

387 WHERE events.intid = this_intid

388 AND events.behavior = 'VUA'

389 AND chimpids.animid = $1

390 LIMIT 1;

391

392 fishing_found := FOUND;

393 END IF;

394

395 IF fishing_found THEN

396 -- There is a fishing behavior.

397 makeup_food := FALSE;

398

399 -- See if we have a food comment applicable to the $1.

400 l := 0;

401 FOR foodcomment IN

402 SELECT regexp_matches(this_comment

403 -- don't try to split foodpart

404 , '(ALL|$2)FOOD!\[([^+]*)\+([^]]*)\]'

405 , 'g')

406 LOOP

407

408 l := l + 1;

409 IF l > 1 THEN

410 EXIT;

411 END IF;

412 END LOOP;

413

414 IF l = 0 THEN

415 -- We do not have a food comment.

416 IF NOT($1_eating_or_fishing)

417 -- or the page has changed and the food behavior is

418 -- not continuous.

419 OR ($1_page != this_page

420 AND allfood_page != this_page

421 AND ($1_last_btime IS NULL

422 OR $1_last_btime + '1 minute'::INTERVAL

423 != this_time)) THEN

424

425 IF $1_page = this_page

426 AND allfood_page != this_page THEN

427 -- The $1 already has a food for the page.

428 NULL;

429

430 ELSIF allfood_page = this_page

431 AND $1_page != this_page THEN

432 -- The $1 is fishing the allfood that everybody else is fishing.

433 $1_foodparts := allfood_foodparts;

434 $1_foodkind := allfood_foodkind;

435 $1_food_changed := FALSE; -- May have changed, we don't care.

436

437 ELSIF this_uncertain THEN

438 -- When the eating is uncertain we can have no food comment.

439 makeup_food := TRUE;

440

441 ELSE

442 -- We must have a food comment to know what the $1 is fishing for.

443 RAISE EXCEPTION data_exception USING

444 MESSAGE = 'Error found in comment'

445 , DETAIL = 'Key (intervals.intid) = (' || this_intid

446 || '): Value (intervals.followid) = ('

447 || this_followid

448 || '): Value (follows.date) = ('

449 || (SELECT follows.date

450 FROM follows

451 WHERE follows.followid = this_followid

452 LIMIT 1)

453 || '): Value mother (followparts.chimpid) = ('

454 || mom

455 || '): Value (intervals.time) = (' || this_time

456 || '): Value (intervals.comment) = ('

457 || textualize(`this_comment')

458 || '): No fishing food comment for $1';

459 END IF;

460 END IF;

461

462 ELSIF l = 1 THEN

463 -- We have a food comment.

464 $1_food_comment := TRUE;

465

466 -- (We don't care if we had a food comment already or not.)

467 new_foodparts := TRIM(BOTH FROM foodcomment[2]);

468 new_foodkind := TRIM(BOTH FROM foodcomment[3]);

469

470 IF ($1_foodparts != new_foodparts OR $1_foodparts IS NULL

471 OR $1_foodkind != new_foodkind OR $1_foodkind IS NULL) THEN

472 $1_food_changed := TRUE;

473 $1_foodparts := new_foodparts;

474 $1_foodkind := new_foodkind;

475 END IF;

476

477 -- Remember what kind of food comment we have.

478 IF foodcomment[1] = 'ALL' THEN

479 allfood_found := TRUE;

480 allfood_page := this_page;

481 allfood_foodparts := new_foodparts;

482 allfood_foodkind := new_foodkind;

483 mom_page := 0; -- hack

484 infant_page := 0; -- hack

485 sib_page := 0; -- hack

486 ELSE

487 focalfood_found := TRUE;

488 $1_page := this_page;

489 allfood_page := 0; -- hack

490 END IF;

491 ELSE

492 -- We have too many food comments.

493 RAISE EXCEPTION data_exception USING

494 MESSAGE = 'Error found in comment'

495 , DETAIL = 'Key (intervals.intid) = (' || this_intid

496 || '): Value (intervals.followid) = ('

497 || this_followid

498 || '): Value (follows.date) = ('

499 || (SELECT follows.date

500 FROM follows

501 WHERE follows.followid = this_followid

502 LIMIT 1)

503 || '): Value mother (followparts.chimpid) = ('

504 || mom

505 || '): Value (intervals.time) = (' || this_time

506 || '): Value (intervals.comment) = ('

507 || this_comment

508 || '): Too many food comments for $1';

509 END IF;

510

511 -- Process the $1's foodcomment.

512 $1_processed := TRUE;

513 converted := converted + 1;

514

515 -- Update the old event with the new food code.

516 UPDATE events

517 SET behavior = 'F'

518 WHERE events.eid = this_eid;

519

520 IF makeup_food THEN

521 -- Insert a made up FISHED row.

522 INSERT INTO fished (eid, foodkind, foodpart, result)

523 VALUES (this_eid, 'UNRECORDED', 'NR', 'N');

524 ELSE

525

526 -- Go through the $1's foodparts.

527 these_foodparts := regexp_split_to_array($1_foodparts, ';');

528 FOREACH this_foodpart IN ARRAY these_foodparts LOOP

529 this_foodpart := TRIM(BOTH FROM this_foodpart);

530

531 -- Create the FISHED row.

532 INSERT INTO fished (eid, foodkind, foodpart

533 , result)

534 SELECT this_eid, $1_foodkind, foodpart_map.newfp

535 , fishing_result

536 FROM foodpart_map

537 WHERE foodpart_map.oldfp = this_foodpart;

538

539 IF NOT FOUND THEN

540 RAISE EXCEPTION data_exception USING

541 MESSAGE = 'Error found in comment'

542 , DETAIL = 'Key (intervals.intid) = (' || this_intid

543 || '): Value (intervals.followid) = ('

544 || this_followid

545 || '): Value (follows.date) = ('

546 || (SELECT follows.date

547 FROM follows

548 WHERE follows.followid = this_followid

549 LIMIT 1)

550 || '): Value mother (followparts.chimpid) = ('

551 || mom

552 || '): Value (intervals.time) = (' || this_time

553 || '): Value (intervals.comment) = ('

554 || this_comment

555 || '): Foodpart for $1 in comment ('

556 || this_foodpart

557 || ') is not a known old foodpart code';

558 END IF;

559

560 END LOOP; -- Over $1 foodparts

561 END IF;

562

563 $1_fishing := TRUE;

564 $1_last_btime := this_time;

565 ELSE -- No $1 fishing behavior.

566

567 -- Somebody's got a behavior

568 $1_fishing := FALSE;

569 END IF;

570 })dnl

571 changequote(`,')dnl

572

573 CREATE OR REPLACE FUNCTION prepared_data.convert_food(fromdate DATE,

574 todate DATE)

575 RETURNS INT

576 LANGUAGE plpgsql

577 SECURITY DEFINER

578 gmi_function_set_search_path

579 AS $$

580 -- Convert food from comments and unconverted eating behavior.

581 --

582 -- GPL_notice(` --', `2014', `The Meme Factory, Inc. http://www.meme.com/')

583 --

584 -- Syntax: select convert_food(fromdate, todate);

585 --

586 -- Arguments:

587 -- fromdate Date of earliest follows to convert (inclusive).

588 -- todate Date of latest follows to convert (inclusive).

589 --

590 -- Remarks:

591 -- Converts food for all follows on the date.

592 -- One-off program. Once it works we won't run it again.

593

594

595 DECLARE

596 r INT;

597 this_followid follows.followid%TYPE;

598

599 BEGIN

600

601 r := 0;

602 FOR this_followid IN

603 SELECT follows.followid

604 FROM follows

605 WHERE follows.date >= fromdate

606 AND follows.date <= todate

607 ORDER BY follows.date, follows.followid

608 LOOP

609

610 r := r + convert_food(this_followid);

611 END LOOP;

612

613 RETURN r;

614 END;

615 $$;

616 grant_func_priv(`prepared_data.convert_food(DATE, DATE)')

617

618

619

620 CREATE OR REPLACE FUNCTION prepared_data.convert_food(followdate DATE

621 , mom VARCHAR(gmi_animid_len))

622 RETURNS INT

623 LANGUAGE plpgsql

624 SECURITY DEFINER

625 gmi_function_set_search_path

626 AS $$

627 -- Convert food from comments and unconverted eating behavior.

628 --

629 -- GPL_notice(` --', `2014', `The Meme Factory, Inc. http://www.meme.com/')

630 --

631 -- Syntax: select convert_food(followdate, mom);

632 --

633 -- Arguments:

634 -- followdate Date of follow

635 -- mom chimpid of mom.

636 --

637 -- Remarks:

638 -- One-off program. Once it works we won't run it again.

639

640

641 DECLARE

642 this_followid follows.followid%TYPE;

643

644 BEGIN

645

646 -- Find the follow.

647 SELECT follows.followid

648 INTO this_followid

649 FROM follows

650 JOIN followparts ON (followparts.followid = follows.followid)

651 JOIN followroles ON (followroles.role = followparts.role)

652 JOIN chimpids AS fcid ON (fcid.chimpid = followparts.chimpid)

653 JOIN chimpids AS this_cid ON (this_cid.animid = fcid.animid)

654 WHERE follows.date = followdate

655 AND followroles.function = 'gmi_mom'

656 AND this_cid.chimpid = mom;

657

658 IF NOT FOUND THEN

659 return 0;

660 END IF;

661

662 RETURN convert_food(this_followid);

663 END;

664 $$;

665 grant_func_priv(`prepared_data.convert_food(DATE, VARCHAR(gmi_animid_len))')

666

667

668 CREATE OR REPLACE FUNCTION prepared_data.convert_food(this_followid INT)

669 RETURNS INT

670 LANGUAGE plpgsql

671 SECURITY DEFINER

672 gmi_function_set_search_path

673 AS $$

674 -- Convert food from comments and unconverted eating behavior.

675 --

676 -- GPL_notice(` --', `2014', `The Meme Factory, Inc. http://www.meme.com/')

677 --

678 -- Syntax: select convert_food(followid);

679 --

680 -- Arguments:

681 -- followid Followid of follow to convert.

682 --

683 -- Remarks:

684 -- One-off program. Once it works we won't run it again.

685

686 DECLARE

687 converted INT := 0;

688

689 this_intid intervals.intid%TYPE;

690 this_time intervals.time%TYPE;

691 this_comment intervals.comment%TYPE;

692 this_page sheets.page%TYPE;

693

694 badobs BOOLEAN;

695

696 mom biography_data.animid%TYPE;

697 sib biography_data.animid%TYPE;

698 infant biography_data.animid%TYPE;

699

700 mom_last_btime intervals.time%TYPE;

701 sib_last_btime intervals.time%TYPE;

702 infant_last_btime intervals.time%TYPE;

703

704 mom_eating BOOLEAN;

705 sib_eating BOOLEAN;

706 infant_eating BOOLEAN;

707

708 mom_fishing BOOLEAN;

709 sib_fishing BOOLEAN;

710 infant_fishing BOOLEAN;

711

712 mom_eating_or_fishing BOOLEAN;

713 sib_eating_or_fishing BOOLEAN;

714 infant_eating_or_fishing BOOLEAN;

715

716 mom_foodkind eats.foodkind%TYPE;

717 infant_foodkind eats.foodkind%TYPE;

718 sib_foodkind eats.foodkind%TYPE;

719

720 mom_foodparts TEXT;

721 infant_foodparts TEXT;

722 sib_foodparts TEXT;

723

724 new_foodkind TEXT;

725 new_foodparts TEXT;

726

727 mom_page sheets.page%TYPE;

728 infant_page sheets.page%TYPE;

729 sib_page sheets.page%TYPE;

730

731 mom_processed BOOLEAN;

732 infant_processed BOOLEAN;

733 sib_processed BOOLEAN;

734

735 mom_food_comment BOOLEAN;

736 infant_food_comment BOOLEAN;

737 sib_food_comment BOOLEAN;

738

739 mom_food_changed BOOLEAN;

740 infant_food_changed BOOLEAN;

741 sib_food_changed BOOLEAN;

742

743 allfood_found BOOLEAN;

744 focalfood_found BOOLEAN;

745

746 fishing_found BOOLEAN;

747 fishing_result fished.result%TYPE;

748

749 allfood_page INT;

750 allfood_foodkind TEXT;

751 allfood_foodparts TEXT;

752

753 devbeh_parts TEXT[];

754 tmp_array TEXT[];

755 devbeh_content TEXT;

756 devbeh_whole_content TEXT;

757

758 this_eid events.eid%TYPE;

759 this_uncertain events.uncertain%TYPE;

760 foodcomment TEXT[];

761 makeup_food BOOLEAN;

762 l INT;

763 these_foodparts TEXT[];

764 this_foodpart TEXT;

765 comment_parts TEXT[];

766 new_comment TEXT;

767

768 food_fragment TEXT[];

769 new_fragment TEXT;

770 fixed_comment TEXT;

771 p INT;

772 BEGIN

773

774 -- Create a temporary table to map old foodpart codes to new.

775 CREATE TEMP TABLE IF NOT EXISTS

776 foodpart_map (oldfp TEXT PRIMARY KEY, newfp TEXT)

777 ON COMMIT DROP;

778

779 PERFORM 1

780 FROM foodpart_map

781 LIMIT 1;

782 IF NOT FOUND THEN

783 INSERT INTO foodpart_map (oldfp, newfp)

784 VALUES

785 ('S', 'IN')

786 , ('G', 'BA')

787 , ('MIFUPA', 'BO')

788 , ('M', 'FL')

789 , ('T', 'FR')

790 , ('J', 'LE')

791 , ('N', 'ME')

792 , ('NONE', 'NC')

793 , ('NA', 'NC')

794 , ('D', 'IN')

795 , ('B', 'PI')

796 , ('MIZIZI', 'RO')

797 , ('U', 'SA')

798 , ('MB', 'SE')

799 , ('C', 'SH')

800 , ('W', 'TE')

801 , ('MITI', 'TR')

802 , ('UNRECORDED', 'NR')

803 , ('UNKNOWN', 'UNK');

804 END IF;

805

806 -- Find mom, infant, and sib.

807 SELECT chimpids.animid

808 INTO mom

809 FROM followparts

810 JOIN followroles ON (followroles.role = followparts.role)

811 JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)

812 WHERE followparts.followid = this_followid

813 AND followroles.function = 'gmi_mom'

814 LIMIT 1;

815

816 SELECT chimpids.animid

817 INTO infant

818 FROM followparts

819 JOIN followroles ON (followroles.role = followparts.role)

820 JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)

821 WHERE followparts.followid = this_followid

822 AND followroles.function = 'gmi_infant'

823 LIMIT 1;

824

825 SELECT chimpids.animid

826 INTO sib

827 FROM followparts

828 JOIN followroles ON (followroles.role = followparts.role)

829 JOIN chimpids ON (chimpids.chimpid = followparts.chimpid)

830 WHERE followparts.followid = this_followid

831 AND followroles.function = 'gmi_sib'

832 LIMIT 1;

833

834 -- Setup for loop.

835 mom_eating := FALSE;

836 infant_eating := FALSE;

837 sib_eating := FALSE;

838

839 mom_fishing := FALSE;

840 infant_fishing := FALSE;

841 sib_fishing := FALSE;

842

843 mom_eating_or_fishing := FALSE;

844 infant_eating_or_fishing := FALSE;

845 sib_eating_or_fishing := FALSE;

846

847 mom_page := 0;

848 infant_page := 0;

849 sib_page := 0;

850

851 allfood_page := 0;

852

853 mom_last_btime := NULL; -- time of last eating or fishing behavior

854 sib_last_btime := NULL;

855 infant_last_btime := NULL;

856

857 -- Go through all the intervals for the follow by time.

858 FOR this_intid, this_time, this_comment, this_page IN

859 SELECT intervals.intid, intervals.time, intervals.comment, sheets.page

860 FROM intervals

861 JOIN sheets ON (sheets.sheetid = intervals.sheetid)

862 WHERE intervals.followid = this_followid

863 ORDER BY intervals.time

864 LOOP

865

866 new_comment := this_comment;

867

868 mom_food_changed := FALSE;

869 infant_food_changed := FALSE;

870 sib_food_changed := FALSE;

871

872 mom_food_comment := FALSE;

873 infant_food_comment := FALSE;

874 sib_food_comment := FALSE;

875

876 -- Is the interval a badobs (no events)?

877 PERFORM 1

878 FROM events

879 JOIN parts ON (parts.eid = events.eid)

880 JOIN chimpids ON (chimpids.chimpid = parts.participant)

881 WHERE events.intid = this_intid

882 LIMIT 1;

883

884 badobs := NOT(FOUND);

885

886 mom_processed := FALSE;

887 infant_processed := FALSE;

888 sib_processed := FALSE;

889 -- badobs rows are ignored.

890 IF NOT(badobs) THEN

891 allfood_found := FALSE;

892 focalfood_found := FALSE;

893

894 -- Do the mom.

895 _process_eating_focal(`mom', `MA')

896 _process_fishing_focal(`mom', `MA')

897 mom_eating_or_fishing := mom_eating OR mom_fishing;

898 -- Do the infant.

899 _process_eating_focal(`infant', `INF')

900 _process_fishing_focal(`infant', `INF')

901 infant_eating_or_fishing := infant_eating OR infant_fishing;

902 -- Do the sib.

903 _process_eating_focal(`sib', `SIB')

904 _process_fishing_focal(`sib', `SIB')

905 sib_eating_or_fishing := sib_eating OR sib_fishing;

906

907 IF allfood_found AND focalfood_found THEN

908 -- Can't have an allfood comment with a MA/INF/SIB food comment.

909 RAISE EXCEPTION data_exception USING

910 MESSAGE = 'Error found in comment'

911 , DETAIL = 'Key (intervals.intid) = (' || this_intid

912 || '): Value (intervals.followid) = ('

913 || this_followid

914 || '): Value (follows.date) = ('

915 || (SELECT follows.date

916 FROM follows

917 WHERE follows.followid = this_followid

918 LIMIT 1)

919 || '): Value mother (followparts.chimpid) = ('

920 || mom

921 || '): Value (intervals.time) = (' || this_time

922 || '): Value (intervals.comment) = ('

923 || this_comment

924 || '): Cannot have both an ALLFOOD and a MA/INF/SIBFOOD in the comment';

925 END IF;

926 END IF; -- badobs row

927

928 -- See that we have comments for the food of all focals

929 -- if any of the focal's foods have changed.

930 IF (mom_food_changed

931 OR infant_food_changed

932 OR sib_food_changed)

933 AND ((mom_eating_or_fishing AND NOT(mom_food_comment))

934 OR (infant_eating_or_fishing AND NOT(infant_food_comment))

935 OR (sib_eating_or_fishing AND NOT(sib_food_comment))) THEN

936 RAISE EXCEPTION data_exception USING

937 MESSAGE = 'Error found in comment'

938 , DETAIL = 'Key (intervals.intid) = (' || this_intid

939 || '): Value (intervals.followid) = ('

940 || this_followid

941 || '): Value (follows.date) = ('

942 || (SELECT follows.date

943 FROM follows

944 WHERE follows.followid = this_followid

945 LIMIT 1)

946 || '): Value mother (followparts.chimpid) = ('

947 || mom

948 || '): Value (intervals.time) = (' || this_time

949 || '): Value (intervals.comment) = ('

950 || this_comment

951 || '): One of the focals has changed foodpart/foodkind but what is eaten by the other focals is not restated in the comments';

952 END IF;

953

954 -- Clean up the comment.

955 IF mom_processed OR infant_processed OR sib_processed THEN

956 -- Get rid of any ALLFOOD comment that exists.

957 comment_parts := regexp_split_to_array(new_comment

958 , 'ALLFOOD!\[([^+]*)\+([^]]*)\]');

959 IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN

960 new_comment := ARRAY_TO_STRING(comment_parts, '');

961 END IF;

962 END IF;

963

964 IF mom_processed THEN

965 -- Get rid of any MAFOOD comment that exists.

966 comment_parts := regexp_split_to_array(new_comment

967 , 'MAFOOD!\[([^+]*)\+([^]]*)\]');

968 IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN

969 new_comment := ARRAY_TO_STRING(comment_parts, '');

970 END IF;

971 END IF;

972

973 IF infant_processed THEN

974 -- Get rid of any INFFOOD comment that exists.

975 comment_parts := regexp_split_to_array(new_comment

976 , 'INFFOOD!\[([^+]*)\+([^]]*)\]');

977 IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN

978 new_comment := ARRAY_TO_STRING(comment_parts, '');

979 END IF;

980 END IF;

981

982 IF sib_processed THEN

983 -- Get rid of any SIBFOOD comment that exists.

984 comment_parts := regexp_split_to_array(new_comment

985 , 'SIBFOOD!\[([^+]*)\+([^]]*)\]');

986 IF ARRAY_LENGTH(comment_parts, 1) > 1 THEN

987 new_comment := ARRAY_TO_STRING(comment_parts, '');

988 END IF;

989 END IF;

990

991 -- Save the comment with the food stuff removed.

992 IF new_comment != this_comment THEN

993 IF TRIM(BOTH FROM new_comment) = '' THEN

994 new_comment := NULL;

995 END IF;

996

997 -- Leave comments untouched.

998 -- UPDATE intervals

999 -- SET comment = new_comment

1000 -- WHERE intervals.intid = this_intid;

1001 END IF;

1002

1003 IF SUBSTRING(new_comment FROM 'FOOD!\[') IS NOT NULL THEN

1004 -- Change the food looking comment to a FOODRELATED comment.

1005

1006 fixed_comment := this_comment;

1007 FOR food_fragment IN

1008 SELECT REGEXP_MATCHES(

1009 new_comment

1010 , '((?:(?:ALL)|(?:MA)|(?:INF)|(?:SIB))FOOD!\[[^]]*])'

1011 , 'g')

1012 LOOP

1013

1014 new_fragment := food_fragment[1];

1015 IF SUBSTRING(new_fragment FOR 2) = 'MA' THEN

1016 new_fragment := 'MAFOODRELATED' || SUBSTRING(new_fragment FROM 7);

1017 ELSE

1018 new_fragment := SUBSTRING(new_fragment FOR 3)

1019 || 'FOODRELATED'

1020 || SUBSTRING(new_fragment FROM 8);

1021 END IF;

1022

1023 p := POSITION(food_fragment[1] IN fixed_comment);

1024 l := CHAR_LENGTH(food_fragment[1]);

1025 fixed_comment := SUBSTRING(fixed_comment FOR p - 1)

1026 || new_fragment

1027 || SUBSTRING(fixed_comment FROM p + l);

1028

1029 -- Change the comment in the new_comment too, just so we've

1030 -- something to test to see if there's something foodlike

1031 -- left in the comments.

1032 p := POSITION(food_fragment[1] IN new_comment);

1033 new_comment := SUBSTRING(new_comment FOR p - 1)

1034 || new_fragment

1035 || SUBSTRING(new_comment FROM p + l);

1036

1037 END LOOP;

1038

1039 -- Update the comment in the interval.

1040 UPDATE intervals

1041 SET comment = fixed_comment

1042 WHERE intervals.intid = this_intid;

1043

1044 IF SUBSTRING(new_comment FROM 'FOOD!\[') IS NOT NULL THEN

1045 RAISE EXCEPTION data_exception USING

1046 MESSAGE = 'Error found in comment'

1047 , DETAIL = 'Key (intervals.intid) = (' || this_intid

1048 || '): Value (intervals.followid) = ('

1049 || this_followid

1050 || '): Value (follows.date) = ('

1051 || (SELECT follows.date

1052 FROM follows

1053 WHERE follows.followid = this_followid

1054 LIMIT 1)

1055 || '): Value mother (followparts.chimpid) = ('

1056 || mom

1057 || '): Value (intervals.time) = (' || this_time

1058 || '): New Value For (intervals.comment) = ('

1059 || fixed_comment

1060 || '): 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';

1061 END IF;

1062

1063 END IF;

1064

1065 END LOOP; -- Over intervals for the follow/day.

1066

1067 RETURN converted;

1068

1069 END;

1070 $$;

1071

1072

1073 grant_func_priv(`prepared_data.convert_food(INT)')