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 group composition from access table structure

19 dnl to new table structure.

20 dnl

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

23 dnl m4 includes

24 include(`copyright.m4')

25 include(`constants.m4')

26 include(`types.m4')

27 include(`macros.m4')dnl

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

30 define(`gmi_function_search_path', `prepared_data, gombemi, pg_temp')

33 CREATE OR REPLACE FUNCTION prepared_data.convert_access_gc(fromdate DATE

34 , todate DATE)

35 RETURNS INT

36 LANGUAGE plpgsql

37 SECURITY DEFINER

38 gmi_function_set_search_path

39 AS $$

40 -- Copy access data group comp structures into new gombemi data structures.

41 --

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

43 --

44 -- Syntax: select convert_access_gc(fromdate, todate);

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 -- Copies a day a a time.

54 --

55 -- Stops as soon as a transaction fails.

57 DECLARE

58 converted INT := 0;

60 this_gcstatus fieldfollows.gcstatus%TYPE;

61 got_gc BOOLEAN;

63 this_sheetid sheets.sheetid%TYPE;

64 this_ffid fieldfollows.ffid%TYPE;

65 rows_found INT;

67 this_follow RECORD;

68 this_follow_5_minute RECORD;

69 this_follow_5_minute_group RECORD;

71 this_uncertain BOOLEAN;

72 this_baboons BOOLEAN;

74 these_chimpids VARCHAR(gmi_animid_len)[];

75 i INT;

76 this_chimpid raw_groupmembers.chimpid%TYPE;

77 BEGIN

79 FOR this_follow

80 IN SELECT *

81 FROM follow

82 WHERE fromdate <= follow.follow_date

83 AND follow.follow_date <= todate

84 ORDER BY follow.follow_date, follow.follow_id

85 LOOP

86 -- Find the follows.gcstatus of the follow.

87 SELECT crfp.group_blank

88 INTO this_gcstatus

89 FROM crfp

90 WHERE crfp.follow_date = this_follow.follow_date

91 AND crfp.focal_mom = this_follow.mother_id;

93 IF NOT FOUND THEN

94 RAISE EXCEPTION integrity_constraint_violation USING

95 MESSAGE = 'Cannot lookup gcstatus (group_blank) for follow on cross_reference_for_program'

96 , DETAIL = 'Value (Follow_id) = ('

97 || this_follow.follow_id

98 || '): Value (date) = ('

99 || this_follow.follow_date

100 || '): Mother = (' || this_follow.mother_id

101 || ')';

102 END IF;

103

104 IF this_gcstatus IS NULL THEN

105 RAISE EXCEPTION integrity_constraint_violation USING

106 MESSAGE = 'gcstatus (group_blank) for follow on cross_reference_forprogram is wrong'

107 , DETAIL = 'Value (Follow_id) = ('

108 || this_follow.follow_id

109 || '): Value (date) = ('

110 || this_follow.follow_date

111 || '): Mother = (' || this_follow.mother_id

112 || '): group_blank is NULL';

113 END IF;

114

115 got_gc := FALSE;

116 FOR this_follow_5_minute

117 IN SELECT *

118 FROM follow_5_minute

119 WHERE follow_5_minute.follow_id = this_follow.follow_id

120 ORDER BY follow_5_minute.interval_time DESC

121 LOOP

122 --

123 -- follow_5_minute:

125 -- 1 | 11/08/2010_GM_01 | 1 | 2010-11-08 09:00:00 | - | | | - | | | 11/08/2010_GM_01_01

126

127 -- Check for already converted.

128 IF this_follow_5_minute.done THEN

129 RAISE EXCEPTION 'Date %: Mom %: Interval_ID %: follow_5_minute row already converted'

130 , this_follow.follow_date

131 , this_follow.mother_id

132 , this_follow_5_minute.interval_id;

133 END IF;

134

135 -- Find the sheet.

136 -- Technically, it's the page/collector that's unique. But

137 -- in the access data it's enough to have a unique page.

138 SELECT sheets.sheetid, sheets.ffid

139 INTO this_sheetid, this_ffid

140 FROM follows

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

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

143 JOIN intervals ON (intervals.followid = follows.followid)

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

145 WHERE follows.date = this_follow.follow_date

146 AND followparts.chimpid

147 = convert_animid(this_follow.mother_id,

148 this_follow.follow_date)

149 AND followroles.function = 'gmi_mom'

150 AND sheets.page = SPLIT_PART(this_follow_5_minute.page_id

151 , '_', 3)::smallint;

152

153

154 GET DIAGNOSTICS rows_found = ROW_COUNT;

155 CASE

156 WHEN rows_found = 0 THEN

157 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'

158 , this_follow.follow_id

159 , this_follow_5_minute.interval_id

160 , this_follow_5_minute.page_id;

161 RETURN NULL;

162 WHEN rows_found > 1 THEN

163 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'

164 , this_follow.follow_id

165 , this_follow_5_minute.interval_id

166 , this_follow_5_minute.page_id;

167 RETURN NULL;

168 ELSE

169 NULL;

170 END CASE;

171

172 -- Intepret the other_animals column.

173 CASE

174 WHEN this_follow_5_minute.other_animals = 'Y' THEN

175 this_baboons = TRUE;

176 WHEN this_follow_5_minute.other_animals = '?' THEN

177 this_baboons = NULL;

178 WHEN this_follow_5_minute.other_animals IS NULL THEN

179 this_baboons = FALSE;

180 ELSE

181 RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Other_animals %: Unknown value for other_animals for this follow_5_minute_group'

182 , this_follow.follow_id

183 , this_follow_5_minute.interval_id

184 , this_follow_5_minute.page_id

185 , this_follow_5_minute.other_animals;

186 RETURN NULL;

187 END CASE;

188

189 -- Create the RAW_GROUPS row.

190 INSERT INTO raw_groups (time, baboons, sheetid)

191 VALUES (this_follow_5_minute.interval_time

192 , this_baboons

193 , this_sheetid);

194

195 -- Interpret the strange_chimp flag.

196 IF this_follow_5_minute.strange_chimp IS NOT NULL THEN

197 CASE this_follow_5_minute.strange_chimp

198 WHEN 'Y' THEN

199 this_uncertain := FALSE;

200 WHEN '?' THEN

201 this_uncertain := TRUE;

202 ELSE

203 RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Strange_chimp %: Unknown value for strange_chimp for this follow_5_minute'

204 , this_follow.follow_id

205 , this_follow_5_minute.interval_id

206 , this_follow_5_minute.page_id

207 , this_follow_5_minute.strange_chimp;

208 RETURN NULL;

209 END CASE;

210

211 -- Insert raw_groupmembers row.

212 INSERT INTO raw_groupmembers (rgid

213 , chimpid

214 , origin

215 , uncertain

216 , swelling)

217 VALUES (CURRVAL('raw_groups_rgid_seq'::regclass)

218 , 'MGE'

219 , 'G' -- is this right?

220 , this_uncertain

221 , NULL);

222 END IF;

223

224 -- Remember the chimpids in the group comp.

225 -- Don't worrry about MGE since she's never a mom or infant.

226 these_chimpids := ARRAY[]::VARCHAR(gmi_animid_len)[];

227 i := 0;

228

229 FOR this_follow_5_minute_group

230 IN SELECT *

231 FROM follow_5_minute_group

232 WHERE follow_5_minute_group.interval_id

233 = this_follow_5_minute.interval_id

234 ORDER BY follow_5_minute_group.chimp_id

235 LOOP

236

237 -- Check for already converted.

238 IF this_follow_5_minute_group.done THEN

239 RAISE EXCEPTION 'Date %: Mom %: Interval_ID %: Chimp_id %: follow_5_minute_group row already converted'

240 , this_follow.follow_date

241 , this_follow.mother_id

242 , this_follow_5_minute.interval_id

243 , this_follow_5_minute_group.chimp_id;

244 END IF;

245

246 --

247 -- follow_5_minute_left_group:

249 -- 1 | 11/03/2010_GA_01_01 | 1 | FD | Y | 11/03/2010_GA_01_01_01

250 --

251 -- follow_5_minute_right_group:

253 -- 1 | 01/01/1997_SA_01_02 | 16 | SW | Y | 01/01/1997_SA_01_02_16

254

255 -- Interpret the present flag.

256 CASE this_follow_5_minute_group.present

257 WHEN 'Y' THEN

258 this_uncertain = FALSE;

259 WHEN '?' THEN

260 this_uncertain = TRUE;

261 ELSE

262 RAISE EXCEPTION 'Follow_id %: Interval_id %: Page_id %: Animid %: Present %: Unknown value for present for this follow_5_minute_group individual'

263 , this_follow.follow_id

264 , this_follow_5_minute.interval_id

265 , this_follow_5_minute.page_id

266 , this_follow_5_minute_group.animid

267 , this_follow_5_minute_group.present;

268 RETURN NULL;

269 END CASE;

270

271 -- Remember the chimpid.

272 these_chimpids[i] := convert_animid(

273 this_follow_5_minute_group.chimp_id

274 , this_follow.follow_date);

275

276 -- Insert raw_groupmembers row.

277 INSERT INTO raw_groupmembers (rgid

278 , chimpid

279 , origin

280 , uncertain

281 , swelling)

282 VALUES (CURRVAL('raw_groups_rgid_seq'::regclass)

283 , these_chimpids[i]

284 , 'G' -- is this right?

285 , this_uncertain

286 , NULL);

287

288 -- Note follow 5 minute group converted.

289 UPDATE follow_5_minute_group

290 SET done = true

291 WHERE tableid = this_follow_5_minute_group.tableid;

292

293 i := i + 1;

294 END LOOP; -- over follow_5_minute_group

295

296 -- Go through the members of the group comp and make sure

297 -- the data is correct.

298

299 FOREACH this_chimpid IN ARRAY these_chimpids LOOP

300 -- Ensure the mom is in the group comp.

301 PERFORM mom_in_gc(this_chimpid

302 , CURRVAL('raw_groups_rgid_seq'::regclass)::INT

303 , this_follow.follow_date);

304 -- Ensure that young infants are in the group comp.

305 PERFORM infants_in_gc(this_chimpid

306 , CURRVAL('raw_groups_rgid_seq'::regclass)::INT

307 , this_follow.follow_date);

308 -- Ensure all social partners are in the group comp.

309 PERFORM add_social_partners(

310 this_chimpid

311 , CURRVAL('raw_groups_rgid_seq'::regclass)::INT);

312 END LOOP;

313

314 -- Note follow 5 minute converted.

315 UPDATE follow_5_minute

316 SET done = true

317 WHERE tableid = this_follow_5_minute.tableid;

318

319 -- We got a group comp for the follow.

320 got_gc := TRUE;

321 END LOOP; -- over follow_5_minute

322

323 IF got_gc THEN

324 IF this_gcstatus = 'N' THEN

325 RAISE EXCEPTION integrity_constraint_violation USING

326 MESSAGE = 'gcstatus (group_blank) for follow on cross_reference_forprogram is wrong'

327 , DETAIL = 'Value (Follow_id) = ('

328 || this_follow.follow_id

329 || '): Value (date) = ('

330 || this_follow.follow_date

331 || '): Mother = (' || this_follow.mother_id

332 || '): group_blank = (' || this_gcstatus

333 || '): Group compositions exist for this follow';

334 END IF;

335

336 -- Give the follow a status.

337 UPDATE fieldfollows

338 SET gcstatus = this_gcstatus

339 WHERE ffid = this_ffid;

340

341 ELSE -- didn't find any group comp.

342 IF this_gcstatus != 'N' THEN

343 RAISE EXCEPTION integrity_constraint_violation USING

344 MESSAGE = 'gcstatus (group_blank) for follow on cross_reference_forprogram is wrong'

345 , DETAIL = 'Value (Follow_id) = ('

346 || this_follow.follow_id

347 || '): Value (date) = ('

348 || this_follow.follow_date

349 || '): Mother = (' || this_follow.mother_id

350 || '): group_blank = (' || this_gcstatus

351 || '): No group composition exists for this follow';

352 END IF;

353 END IF;

354

355 -- Keep track of how many follows we converted.

356 converted := converted + 1;

357 END LOOP;

358

359 RETURN converted;

360

361 END;

362 $$;

363

364

365 grant_func_priv(`prepared_data.convert_access_gc(DATE, DATE)')