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 Function to convert from access table structure to a temporary

19 dnl table structure that's easier to work with.

20 dnl

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

23 dnl m4 includes

24 include(`copyright.m4')

25 include(`constants.m4')

26 include(`macros.m4')dnl

27 include(`grants.m4')

29 dnl Reset our search path so we pick up the conversion schema.

30 define(`gmi_function_search_path',

31 `prepared_data, conversion, gombemi, pg_temp')

33 CREATE OR REPLACE FUNCTION prepared_data.prepare_data()

34 RETURNS INT

35 LANGUAGE plpgsql

36 SECURITY DEFINER

37 gmi_function_set_search_path

38 AS $$

39 -- Copy access data structures from the conversion schema

40 -- into a better format in the prepared_data schema.

41 --

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

43 --

44 -- Syntax: select prepare_data();

45 --

46 -- Arguments:

47 -- fromdate Date to start copy (inclusive)

48 -- todate Date to stop copy (inclusive)

49 --

50 -- Remarks:

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

52 --

53 -- Destroy's what's in the prepared_data schema.

54 --

55 -- Stops as soon as a transaction fails.

57 DECLARE

59 BEGIN

61 raise notice 'deleting';

63 delete from prepared_data.follow_5_minute_group;

64 delete from prepared_data.follow_5_minute;

65 delete from prepared_data.follow_1_minute_sibling;

66 delete from prepared_data.follow_1_minute_mother;

67 delete from prepared_data.follow_1_minute_infant;

68 delete from prepared_data.follow_1_minute;

69 delete from prepared_data.follow_page;

70 delete from prepared_data.follow;

71 delete from prepared_data.crfp;

72 delete from prepared_data.page_followtype;

74 /*

75 vacuum full prepared_data.follow_5_minute_group;

76 vacuum full prepared_data.follow_5_minute;

77 vacuum full prepared_data.follow_1_minute_sibling;

78 vacuum full prepared_data.follow_1_minute_mother;

79 vacuum full prepared_data.follow_1_minute_infant;

80 vacuum full prepared_data.follow_1_minute;

81 vacuum full prepared_data.follow;

82 */

84 -- crfp

85 raise notice 'crfp';

87 -- Convert focal_mom from a name into an animid.

88 insert into prepared_data.crfp (

89 tableid

90 ,"year"

91 ,"follow_date"

92 ,"focal_mom"

93 ,"focal_infant"

94 ,"siblings"

95 ,"researcher"

96 ,"pages"

97 ,"start_entry_date"

98 ,"end_entry_date"

99 ,"enterer"

100 ,"follow_status"

101 ,"entry_notes"

102 ,"group_comp_problem"

103 ,"computer"

104 ,"checked_over"

105 ,"also_checked_by"

106 ,"last_checked_by_date"

107 ,"absent_mother_infant"

108 ,"group_blank"

109 ,"protocol"

110 ,"template"

111 ,"temptype"

112 ,"scan_checked"

113 ,"paper_follow_location"

114 ,"inf_birthdate"

115 ,"infant_age"

116 )

117 select

118 tableid

119 ,"Year"

120 ,"Follow Date"

121 ,biography_data.animid

122 ,"Focal infant"

123 ,"Siblings"

124 ,"Researcher"

125 ,"# pages"

126 ,"Start Entry Date"

127 ,"End Entry Date"

128 ,"Enterer"

129 ,"Follow Status"

130 ,"Entry Notes"

131 ,"Group comp problem?"

132 ,"Computer"

133 ,"Checked Over"

134 ,"Also Checked By"

135 ,"Last Checked By Date"

136 ,"Absent Mother/Infant"

137 ,"Group_blank"

138 ,"Protocol"

139 ,"Template"

140 ,"TempType"

141 ,"Scan Checked"

142 ,"Paper Follow Location"

143 ,"Inf birthdate"

144 ,"Infant Age"

145 from conversion.cross_reference_forprogram

146 join biography_data

147 ON (upper(trim(biography_data.animname))

148 = upper(trim(conversion.cross_reference_forprogram."Focal mom")));

149

150 IF (select count(*)

151 from conversion.cross_reference_forprogram)

152 != (select count(*)

153 from prepared_data.crfp) THEN

154 RAISE EXCEPTION integrity_constraint_violation USING

155 MESSAGE = 'Error looking up cross_reference_forprogram."Focal mom" on biography_data'

156 , DETAIL = 'Missing (Focal mom) = ('

157 || (SELECT conversion.cross_reference_forprogram."Focal mom"

158 FROM conversion.cross_reference_forprogram

159 WHERE NOT EXISTS (

160 SELECT 1

161 FROM gombemi.biography_data

162 WHERE upper(trim(gombemi.biography_data.animname))

163 = upper(trim(conversion.cross_reference_forprogram."Focal mom"))

164 LIMIT 1)

165 LIMIT 1)

166 || ')';

167 END IF;

168

169

170 -- page_followtype

171 raise notice 'page_followtype';

172

173 insert into prepared_data.page_followtype (

174 tableid

175 ,date

176 ,mom

177 ,page

178 ,temptype

179 ,done

180 )

181 (select tableid

182 ,date

183 ,mom

184 ,page

185 ,temptype

186 ,FALSE

187 from conversion.page_followtype);

188

189

190 -- follow

191 raise notice 'follow';

192

193 insert into prepared_data.follow (

194 tableid

195 ,"follow_date"

196 ,"mother_id"

197 ,"infant_id"

198 ,"infant2_id"

199 ,"sibling_id"

200 ,"sibling2_id"

201 ,"conducted_by"

202 ,"entered_by"

203 ,"sheet_id"

204 ,"left_group_id"

205 ,"right_group_id"

206 ,"follow_status"

207 ,"follow_id"

208 ,"time_mode"

209 ,"ampm"

210 , done

211 )

212 (select tableid

213 ,"Follow_date"

214 ,"Mother_id"

215 ,"Infant_id"

216 ,"Infant2_id"

217 ,"Sibling_id"

218 ,"Sibling2_id"

219 ,"Conducted_by"

220 ,"Entered_by"

221 ,"Sheet_id"

222 ,"Left_group_id"

223 ,"Right_group_id"

224 ,"Follow_status"

225 ,"Follow_id"

226 ,"Time_mode"

227 ,"AMPM"

228 , false

229 from conversion.follow);

230

231 -- follow_page

232 raise notice 'follow_page';

233

234 insert into prepared_data.follow_page (

235 tableid

236 ,"follow_id"

237 ,"page_number"

238 ,"location"

239 ,"video"

240 ,"page_id"

241 ,done

242 )

243 select

244 tableid

245 ,"Follow_id"

246 ,"Page_number"

247 ,"Location"

248 ,"Video"

249 ,"Page_id"

250 ,FALSE

251 from conversion.follow_page;

252

253

254 -- follow_1_minute

255 raise notice 'follow_1_minute';

256

257 insert into prepared_data.follow_1_minute (

258 tableid

259 ,"page_id"

260 ,"interval_number"

261 ,"interval_time"

262 ,"bad_observation"

263 ,"rain"

264 ,"comment"

265 ,"commentrtf"

266 ,"interval_id"

267 ,follow_id

268 , done

269 )

270 (select

271 tableid

272 ,"Page_id"

273 ,"Interval_number"

274 ,"Interval_time"

275 ,"Bad_observation"

276 ,"Rain"

277 ,"Comment"

278 ,"CommentRTF"

279 ,"Interval_id"

280 ,split_part("Interval_id", '_', 1)

281 || '_'

282 || split_part("Interval_id", '_', 2)

283 , false

284 from conversion.follow_1_minute);

285

286

287 -- follow_1_minute_infant

288 raise notice 'follow_1_minute_infant';

289

290 insert into prepared_data.follow_1_minute_infant (

291 tableid

292 ,"interval_id"

293 ,"infant_id"

294 ,"nursing"

295 ,"eating"

296 ,"walks"

297 ,"playing"

298 ,"resting"

299 ,"in_contact"

300 ,"within_reach"

301 ,"within_0_5_meters"

302 ,"between_5_15_meters"

303 ,"more_than_15_meters"

304 ,"rides_on_stomach"

305 ,"rides_on_back"

306 ,"dangles"

307 ,"contacts_other"

308 ,"grooms_other"

309 ,"groomed_by"

310 ,"plays"

311 ,"displays"

312 ,"refused"

313 ,"cries"

314 ,"vocalizations"

315 ,"begs"

316 ,"aggression"

317 ,"infant_interval_id"

318 , follow_id

319 , done

320 )

321 (select

322 tableid

323 ,"Interval_id"

324 ,"Infant_id"

325 ,"Nursing"

326 ,"Eating"

327 ,"Walks"

328 ,"Playing"

329 ,"Resting"

330 ,"In_contact"

331 ,"Within_reach"

332 ,"Within_0_5_meters"

333 ,"Between_5_15_meters"

334 ,"More_than_15_meters"

335 ,"Rides_on_stomach"

336 ,"Rides_on_back"

337 ,"Dangles"

338 ,"Contacts_other"

339 ,"Grooms_other"

340 ,"Groomed_by"

341 ,"Plays"

342 ,"Displays"

343 ,"Refused"

344 ,"Cries"

345 ,"Vocalizations"

346 ,"Begs"

347 ,"Aggression"

348 ,"Infant_interval_id"

349 ,split_part("Interval_id", '_', 1)

350 || '_'

351 || split_part("Interval_id", '_', 2)

352 , false

353 from conversion.follow_1_minute_infant);

354

355

356

357 -- follow_1_minute_mother

358 raise notice 'follow_1_minute_mother';

359

360 insert into prepared_data.follow_1_minute_mother (

361 tableid

362 ,"interval_id"

363 ,"mother_id"

364 ,"behavior"

365 ,"grooms_other"

366 ,"groomed_by"

367 ,"vocalizations"

368 ,follow_id

369 ,done

370 )

371 select

372 tableid

373 ,"Interval_id"

374 ,"Mother_id"

375 ,"Behavior"

376 ,"Grooms_other"

377 ,"Groomed_by"

378 ,"Vocalizations"

379 ,split_part("Interval_id", '_', 1)

380 || '_'

381 || split_part("Interval_id", '_', 2)

382 ,false

383 from conversion.follow_1_minute_mother;

384

385

386 -- follow_1_minute_sibling

387 raise notice 'follow_1_minute_sibling';

388

389 insert into prepared_data.follow_1_minute_sibling (

390 tableid

391 ,"interval_id"

392 ,"sibling_id"

393 ,"bad_observation"

394 ,"behavior"

395 ,"distance_from_mother"

396 ,"vocalizations"

397 ,"plays"

398 ,"grooms_other"

399 ,"groomed_by"

400 ,"distance_from_infant"

401 ,"sibling_interval_id"

402 ,follow_id

403 ,done

404 )

405 select

406 tableid

407 ,"Interval_id"

408 ,"Sibling_id"

409 ,"Bad_observation"

410 ,"Behavior"

411 ,"Distance_from_mother"

412 ,"Vocalizations"

413 ,"Plays"

414 ,"Grooms_other"

415 ,"Groomed_by"

416 ,"Distance_from_infant"

417 ,"Sibling_interval_id"

418 ,split_part("Interval_id", '_', 1)

419 || '_'

420 || split_part("Interval_id", '_', 2)

421 ,false

422 from conversion.follow_1_minute_sibling;

423

424 -- Go through the follow_1_minute_sibling and where

425 -- the 'Multi' behavior code is found add grooms other,

426 -- plays, and groomed by as is found in the data.

427

428 -- follow_1_minute_sibling Multi -> Grooming (based on grooms_other)

429 UPDATE prepared_data.follow_1_minute_sibling

430 SET behavior = CASE

431 WHEN TRIM(TRAILING FROM behavior) ~ ';$'

432 THEN TRIM(TRAILING FROM behavior) || 'Grooming'

433 ELSE

434 TRIM(TRAILING FROM behavior) || ';Grooming'

435 END

436 WHERE behavior ~ '(?:^|;)[[:blank:]]*Multi[[:blank:]]*(?:$|;)'

437 AND TRIM(BOTH FROM grooms_other) != ''

438 AND grooms_other IS NOT NULL

439 AND behavior !~ '(?:^|;)[[:blank:]]*Grooming[[:blank:]]*(?:$|;)';

440

441 -- follow_1_minute_sibling Multi -> Groomed (based on groomed_by)

442 UPDATE prepared_data.follow_1_minute_sibling

443 SET behavior = CASE

444 WHEN TRIM(TRAILING FROM behavior) ~ ';$'

445 THEN TRIM(TRAILING FROM behavior) || 'Groomed'

446 ELSE

447 TRIM(TRAILING FROM behavior) || ';Groomed'

448 END

449 WHERE behavior ~ '(?:^|;)[[:blank:]]*Multi[[:blank:]]*(?:$|;)'

450 AND TRIM(BOTH FROM groomed_by) != ''

451 AND groomed_by IS NOT NULL

452 AND behavior !~ '(?:^|;)[[:blank:]]*Groomed[[:blank:]]*(?:$|;)';

453

454 -- follow_1_minute_sibling Multi -> Play (based on plays)

455 UPDATE prepared_data.follow_1_minute_sibling

456 SET behavior = CASE

457 WHEN TRIM(TRAILING FROM behavior) ~ ';$'

458 THEN TRIM(TRAILING FROM behavior) || 'Play'

459 ELSE

460 TRIM(TRAILING FROM behavior) || ';Play'

461 END

462 WHERE behavior ~ '(?:^|;)[[:blank:]]*Multi[[:blank:]]*(?:$|;)'

463 AND TRIM(BOTH FROM plays) != ''

464 AND plays IS NOT NULL

465 AND behavior !~ '(?:^|;)[[:blank:]]*Play[[:blank:]]*(?:$|;)';

466

467

468 -- follow_5_minute

469 raise notice 'follow_5_minute';

470

471 insert into prepared_data.follow_5_minute(

472 tableid

473 ,"page_id"

474 ,"interval_number"

475 ,"interval_time"

476 ,"mother_feeding"

477 ,"food_part_initials"

478 ,"food_name"

479 ,"infant_feeding"

480 ,"strange_chimp"

481 ,"other_animals"

482 ,"interval_id"

483 ,follow_id

484 ,done

485 )

486 select

487 tableid

488 ,"Page_id"

489 ,"Interval_number"

490 ,"Interval_time"

491 ,"Mother_feeding"

492 ,"Food_part_initials"

493 ,"Food_name"

494 ,"Infant_feeding"

495 ,"Strange_chimp"

496 ,"Other_animals"

497 ,"Interval_id"

498 ,split_part("Interval_id", '_', 1)

499 || '_'

500 || split_part("Interval_id", '_', 2)

501 ,false

502 from conversion.follow_5_minute;

503

504

505 -- follow_5_minute_group

506 raise notice 'follow_5_minute_group';

507

508 insert into prepared_data.follow_5_minute_group (

509 tableid

510 ,"interval_id"

511 ,"position_number"

512 ,"chimp_id"

513 ,"present"

514 ,"position_id"

515 ,follow_id

516 , done

517 )

518 select

519 tableid

520 ,"Interval_id"

521 ,"Position_number"

522 ,"Chimp_id"

523 ,"Present"

524 ,"Position_id"

525 ,split_part("Interval_id", '_', 1)

526 || '_'

527 || split_part("Interval_id", '_', 2)

528 ,false

529 from conversion.follow_5_minute_left_group

530 union all

531 select

532 tableid + 10000000

533 ,"Interval_id"

534 ,"Position_number"

535 ,"Chimp_id"

536 ,"Present"

537 ,"Position_id"

538 ,split_part("Interval_id", '_', 1)

539 || '_'

540 || split_part("Interval_id", '_', 2)

541 ,false

542 from conversion.follow_5_minute_right_group;

543

544 /*

545 vacuum analyze prepared_data.follow_5_minute_group;

546 vacuum analyze prepared_data.follow_5_minute;

547 vacuum analyze prepared_data.follow_1_minute_sibling;

548 vacuum analyze prepared_data.follow_1_minute_mother;

549 vacuum analyze prepared_data.follow_1_minute_infant;

550 vacuum analyze prepared_data.follow_1_minute;

551 vacuum analyze prepared_data.follow;

552 */

553 RETURN 0;

554

555 END;

556 $$;

557

558

559 grant_func_priv(`prepared_data.prepare_data()')