darcs - gombemi

2 dnl

3 dnl This file is part of GombeMI.

4 dnl

5 dnl GombeMI 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 GombeMI. If not, see <http://www.gnu.org/licenses/>.

17 dnl

18 dnl Creates prepared_data schema and tables therein.

19 dnl

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

21 dnl

22 dnl Mar 7, 2013

23 dnl

24 dnl Remarks:

25 dnl

26 dnl Intended to be piped to psql run as gombemi_admin.

27 dnl m4 includes

28 include(`copyright.m4')

29 include(`constants.m4')

30 include(`grants.m4')

32 DROP SCHEMA IF EXISTS prepared_data CASCADE;

34 CREATE SCHEMA prepared_data;

36 GRANT USAGE ON SCHEMA prepared_data TO gombemi_editors

37 , gombemi_readers;

39 SET SEARCH_PATH TO prepared_data;

41 --

42 -- Create all the tables.

43 --

44 begin;

45 /*

46 drop table if exists prepared_data.follow cascade;

47 drop table if exists prepared_data.follow_1_minute cascade;

48 drop table if exists prepared_data.follow_1_minute_infant cascade;

49 drop table if exists prepared_data.follow_1_minute_mother cascade;

50 drop table if exists prepared_data.follow_1_minute_sibling cascade;

51 drop table if exists prepared_data.follow_5_minute cascade;

52 drop table if exists prepared_data.follow_5_minute_group cascade;

53 */

55 -- cross_reference_for_program

56 create table prepared_data.crfp (

57 tableid serial primary key

58 ,"year" int

59 ,"follow_date" date

60 ,"focal_mom" text

61 ,"focal_infant" text

62 ,"siblings" text

63 ,"researcher" text

64 ,"pages" text

65 ,"start_entry_date" date

66 ,"end_entry_date" date

67 ,"enterer" text

68 ,"follow_status" text

69 ,"entry_notes" text

70 ,"group_comp_problem" text

71 ,"computer" text

72 ,"checked_over" text

73 ,"also_checked_by" text

74 ,"last_checked_by_date" text

75 ,"absent_mother_infant" text

76 ,"group_blank" text

77 ,"protocol" text

78 ,"template" text

79 ,"temptype" text

80 ,"scan_checked" text

81 ,"paper_follow_location" text

82 ,"inf_birthdate" text

83 ,"infant_age" text

84 );

86 create unique index crfp_date_mom

87 on prepared_data.crfp("follow_date", "focal_mom");

89 grant_priv(`prepared_data.crfp')

90 grant_seq_priv(`prepared_data.crfp', `tableid')

93 -- page_followtype

94 create table prepared_data.page_followtype (

95 tableid serial primary key

96 ,date date

97 ,mom text

98 ,page text

99 ,temptype text

100 ,done boolean

101 );

102

103 create index page_followtype_date_mom_page

104 on prepared_data.page_followtype(date, mom, page);

105

106 grant_priv(`prepared_data.page_followtype')

107 grant_seq_priv(`prepared_data.page_followtype', `tableid')

108

109

110 -- follow

111 create table prepared_data.follow (

112 tableid serial primary key

113 ,"follow_date" date

114 ,"mother_id" varchar

115 ,"infant_id" varchar

116 ,"infant2_id" varchar

117 ,"sibling_id" varchar

118 ,"sibling2_id" varchar

119 ,"conducted_by" varchar

120 ,"entered_by" varchar

121 ,"sheet_id" int

122 ,"left_group_id" int

123 ,"right_group_id" int

124 ,"follow_status" varchar

125 ,"follow_id" varchar

126 ,"time_mode" varchar

127 ,"ampm" varchar

128 ,done boolean

129 );

130

131

132 create unique index follow_follow_id on prepared_data.follow ("follow_id");

133 create index follow_date on prepared_data.follow ("follow_date");

134

135 grant_priv(`prepared_data.follow')

136 grant_seq_priv(`prepared_data.follow', `tableid')

137

138

139 -- follow_page

140 create table prepared_data.follow_page (

141 tableid serial primary key

142 ,"follow_id" text

143 constraint "follow_id on follow" references prepared_data.follow(follow_id)

144 ,"page_number" int

145 ,"location" text

146 ,"video" text

147 ,"page_id" text

148 , done boolean

149 );

150

151 create index follow_page_follow_id

152 on prepared_data.follow_page("follow_id");

153 create unique index follow_page_page_id

154 on prepared_data.follow_page ("page_id");

155

156 grant_priv(`follow_page')

157 grant_seq_priv(`follow_page', `tableid')

158

159

160 -- follow_1_minute

161 create table prepared_data.follow_1_minute (

162 tableid serial primary key

163 ,"page_id" varchar

164 constraint "page_id on follow_page" references prepared_data.follow_page(page_id)

165 ,"interval_number" int

166 ,"interval_time" timestamp without time zone

167 ,"bad_observation" varchar

168 ,"rain" varchar

169 ,"comment" varchar

170 ,"commentrtf" varchar

171 ,"interval_id" varchar

172 , follow_id varchar

173 constraint "follow_id on follow" references prepared_data.follow(follow_id)

174 ,done boolean

175 );

176

177 create unique index

178 follow_1_minute_interval_id on prepared_data.follow_1_minute ("interval_id");

179

180 -- Data problem. Let the conversion sort it out.

181 create unique index follow_1_minute_follow_id_interval_time

182 on prepared_data.follow_1_minute (follow_id, interval_time);

183 --create index follow_1_minute_follow_id_interval_time

184 -- on prepared_data.follow_1_minute (follow_id, interval_time);

185

186 grant_priv(`prepared_data.follow_1_minute')

187 grant_seq_priv(`prepared_data.follow_1_minute', `tableid')

188

189

190 -- follow_1_minute_infant

191 create table prepared_data.follow_1_minute_infant (

192 tableid serial primary key

193 ,"interval_id" varchar

194 constraint "follow_1_minute_infant.interval_id must be a follow_1_minute.interval_id"

195 references prepared_data.follow_1_minute("interval_id")

196 ,"infant_id" varchar

197 ,"nursing" varchar

198 ,"eating" varchar

199 ,"walks" varchar

200 ,"playing" varchar

201 ,"resting" varchar

202 ,"in_contact" varchar

203 ,"within_reach" varchar

204 ,"within_0_5_meters" varchar

205 ,"between_5_15_meters" varchar

206 ,"more_than_15_meters" varchar

207 ,"rides_on_stomach" varchar

208 ,"rides_on_back" varchar

209 ,"dangles" varchar

210 ,"contacts_other" varchar

211 ,"grooms_other" varchar

212 ,"groomed_by" varchar

213 ,"plays" varchar

214 ,"displays" varchar

215 ,"refused" varchar

216 ,"cries" varchar

217 ,"vocalizations" varchar

218 ,"begs" varchar

219 ,"aggression" varchar

220 ,"infant_interval_id" varchar

221 , follow_id varchar

222 constraint "follow_id on follow" references prepared_data.follow(follow_id)

223 , done boolean

224 );

225

226 create unique index follow_1_minute_infant_infant_interval_id

227 on prepared_data.follow_1_minute_infant ("infant_interval_id");

228

229 -- Data problems. Let the conversion deal with it.

230 create unique index follow_1_minute_infant_interval_id_infant_id

231 on prepared_data.follow_1_minute_infant ("interval_id", infant_id);

232 --create index follow_1_minute_infant_interval_id_infant_id

233 -- on prepared_data.follow_1_minute_infant ("interval_id", infant_id);

234

235 create unique index follow_1_minute_infant_interval_id

236 on prepared_data.follow_1_minute_infant ("interval_id");

237

238 create index follow_1_minute_infant_follow_id

239 on prepared_data.follow_1_minute_infant (follow_id);

240

241

242 grant_priv(`prepared_data.follow_1_minute_infant')

243 grant_seq_priv(`prepared_data.follow_1_minute_infant', `tableid')

244

245

246 -- follow_1_minute_mother

247

248 create table prepared_data.follow_1_minute_mother (

249 tableid serial primary key

250 ,"interval_id" varchar

251 constraint "follow_1_minute_mother.interval_id must be a follow_1_minute.interval_id"

252 references prepared_data.follow_1_minute("interval_id")

253 ,"mother_id" varchar

254 ,"behavior" varchar

255 ,"grooms_other" varchar

256 ,"groomed_by" varchar

257 ,"vocalizations" varchar

258 , follow_id varchar

259 constraint "follow_id on follow" references prepared_data.follow(follow_id)

260 , done boolean

261 );

262

263 create unique index follow_1_minute_mother_interval_id

264 on prepared_data.follow_1_minute_mother ("interval_id");

265

266 create index follow_1_minute_mother_follow_id

267 on prepared_data.follow_1_minute_mother (follow_id);

268

269 grant_priv(`prepared_data.follow_1_minute_mother')

270 grant_seq_priv(`prepared_data.follow_1_minute_mother', `tableid')

271

272

273 -- follow_1_minute_sibling

274

275 create table prepared_data.follow_1_minute_sibling (

276 tableid serial primary key

277 ,"interval_id" varchar

278 constraint "follow_1_minute_sibling.interval_id must be a follow_1_minute.interval_id"

279 references prepared_data.follow_1_minute("interval_id")

280 ,"sibling_id" varchar

281 ,"bad_observation" varchar

282 ,"behavior" varchar

283 ,"distance_from_mother" varchar

284 ,"vocalizations" varchar

285 ,"plays" varchar

286 ,"grooms_other" varchar

287 ,"groomed_by" varchar

288 ,"distance_from_infant" varchar

289 ,"sibling_interval_id" varchar

290 , follow_id varchar

291 constraint "follow_id on follow" references prepared_data.follow(follow_id)

292 , done boolean

293 );

294

295 create unique index follow_1_minute_sibling_sibling_interval_id

296 on prepared_data.follow_1_minute_sibling ("sibling_interval_id");

297

298 create unique index follow_1_minute_sibling_interval_id_sibling_id

299 on prepared_data.follow_1_minute_sibling ("interval_id", sibling_id);

300

301 create index follow_1_minute_sibling_follow_id

302 on prepared_data.follow_1_minute_sibling (follow_id);

303

304 grant_priv(`prepared_data.follow_1_minute_sibling')

305 grant_seq_priv(`prepared_data.follow_1_minute_sibling', `tableid')

306

307

308 -- follow_5_minute

309

310 create table prepared_data.follow_5_minute (

311 tableid serial primary key

312 ,"page_id" varchar

313 constraint "page_id on follow_page" references prepared_data.follow_page(page_id)

314 ,"interval_number" int

315 ,"interval_time" timestamp without time zone

316 ,"mother_feeding" varchar -- ignore

317 ,"food_part_initials" varchar -- ignore

318 ,"food_name" varchar -- ignore

319 ,"infant_feeding" varchar -- ignore

320 ,"strange_chimp" varchar -- go in as 'MGE' chimp

321 ,"other_animals" varchar -- ignore

322 ,"interval_id" varchar

323 , follow_id varchar

324 constraint "follow_id on follow" references prepared_data.follow(follow_id)

325 , done boolean

326 );

327

328

329 create unique index follow_5_minute_interval_id

330 on prepared_data.follow_5_minute ("interval_id");

331

332 -- Data problems. Deal with it in the conversion.

333 create unique index follow_5_minute_follow_id_interval_time

334 on prepared_data.follow_5_minute (follow_id, interval_time);

335 --create index follow_5_minute_follow_id_interval_time

336 -- on prepared_data.follow_5_minute (follow_id, interval_time);

337

338 grant_priv(`prepared_data.follow_5_minute')

339 grant_seq_priv(`prepared_data.follow_5_minute', `tableid')

340

341

342 -- follow_5_minute_group

343

344 create table prepared_data.follow_5_minute_group (

345 tableid serial primary key

346 ,"interval_id" varchar

347 constraint "follow_5_minute_group.interval_id must be a follow_5_minute.interval_id"

348 references prepared_data.follow_5_minute("interval_id")

349 ,"position_number" int

350 ,"chimp_id" varchar

351 ,"present" varchar

352 ,"position_id" varchar

353 , follow_id varchar

354 constraint "follow_id on follow" references prepared_data.follow(follow_id)

355 , done boolean

356 );

357

358

359 create unique index follow_5_minute_group_interval_id_chimp_id

360 on prepared_data.follow_5_minute_group ("interval_id", chimp_id);

361

362 create index follow_5_minute_group_follow_id

363 on prepared_data.follow_5_minute_group (follow_id);

364

365 grant_priv(`prepared_data.follow_5_minute_group')

366 grant_seq_priv(`prepared_data.follow_5_minute_group', `tableid')

367

368

369 commit;