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 fully populated conversion schema.

19 dnl

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

21 dnl

22 dnl Mar 1, 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 --

33 -- The gombemi schema where the production system lives.

34 --

36 CREATE SCHEMA conversion;

38 GRANT USAGE ON SCHEMA conversion TO gombemi_editors

39 , gombemi_readers;

41 SET SEARCH_PATH TO conversion;

43 --

44 -- Create all the tables.

45 --

46 begin;

47 -- cross_reference_for_program

48 create table cross_reference_forprogram (

49 tableid serial primary key

50 ,"Year" int

51 ,"Follow Date" date

52 ,"Focal mom" text

53 ,"Focal infant" text

54 ,"Siblings" text

55 ,"Researcher" text

56 ,"# pages" text

57 ,"Start Entry Date" date

58 ,"End Entry Date" date

59 ,"Enterer" text

60 ,"Follow Status" text

61 ,"Entry Notes" text

62 ,"Group comp problem?" text

63 ,"Computer" text

64 ,"Checked Over" text

65 ,"Also Checked By" text

66 ,"Last Checked By Date" text

67 ,"Absent Mother/Infant" text

68 ,"Group_blank" text

69 ,"Protocol" text

70 ,"Template" text

71 ,"TempType" text

72 ,"Scan Checked" text

73 ,"Paper Follow Location" text

74 ,"Inf birthdate" text

75 ,"Infant Age" text

76 );

78 create unique index crfp_date_mom

79 on cross_reference_forprogram("Follow Date", "Focal mom");

81 grant_priv(`cross_reference_forprogram')

82 grant_seq_priv(`cross_reference_forprogram', `tableid')

85 -- page_followtype

86 create table page_followtype (

87 tableid serial primary key

88 ,date date

89 ,mom text

90 ,page text

91 ,temptype text

92 );

94 create index page_followtype_date_mom_page

95 on page_followtype(date, mom, page);

97 grant_priv(`page_followtype')

98 grant_seq_priv(`page_followtype', `tableid')

100

101 -- follow

102 create table follow (

103 tableid serial primary key

104 ,"Follow_date" date

105 ,"Mother_id" varchar

106 ,"Infant_id" varchar

107 ,"Infant2_id" varchar

108 ,"Sibling_id" varchar

109 ,"Sibling2_id" varchar

110 ,"Conducted_by" varchar

111 ,"Entered_by" varchar

112 ,"Sheet_id" int

113 ,"Left_group_id" int

114 ,"Right_group_id" int

115 ,"Follow_status" varchar

116 ,"Follow_id" varchar

117 ,"Time_mode" varchar

118 ,"AMPM" varchar

119 );

120

121 create unique index follow_follow_id on follow ("Follow_id");

122 create index follow_date on follow ("Follow_date");

123

124 grant_priv(`follow')

125 grant_seq_priv(`follow', `tableid')

126

127

128 -- follow_page

129 create table follow_page (

130 tableid serial primary key

131 ,"Follow_id" text

132 ,"Page_number" int

133 ,"Location" text

134 ,"Video" text

135 ,"Page_id" text

136 );

137

138 create index follow_page_follow_id on follow_page("Follow_id");

139 create unique index follow_page_page_id on follow_page ("Page_id");

140

141 grant_priv(`follow_page')

142 grant_seq_priv(`follow_page', `tableid')

143

144

145 -- follow_1_minute

146 create table follow_1_minute (

147 tableid serial primary key

148 ,"Page_id" varchar

149 ,"Interval_number" int

150 ,"Interval_time" timestamp without time zone

151 ,"Bad_observation" varchar

152 ,"Rain" varchar

153 ,"Comment" varchar

154 ,"CommentRTF" varchar

155 ,"Interval_id" varchar

156 );

157

158 create unique index

159 follow_1_minute_interval_id on follow_1_minute ("Interval_id");

160

161 grant_priv(`follow_1_minute')

162 grant_seq_priv(`follow_1_minute', `tableid')

163

164

165 -- follow_1_minute_infant

166 create table follow_1_minute_infant (

167 tableid serial primary key

168 ,"Interval_id" varchar

169 constraint "follow_1_minute_infant.Interval_id must be a follow_1_minute.Interval_id"

170 REFERENCES follow_1_minute("Interval_id")

171 ,"Infant_id" varchar

172 ,"Nursing" varchar

173 ,"Eating" varchar

174 ,"Walks" varchar

175 ,"Playing" varchar

176 ,"Resting" varchar

177 ,"In_contact" varchar

178 ,"Within_reach" varchar

179 ,"Within_0_5_meters" varchar

180 ,"Between_5_15_meters" varchar

181 ,"More_than_15_meters" varchar

182 ,"Rides_on_stomach" varchar

183 ,"Rides_on_back" varchar

184 ,"Dangles" varchar

185 ,"Contacts_other" varchar

186 ,"Grooms_other" varchar

187 ,"Groomed_by" varchar

188 ,"Plays" varchar

189 ,"Displays" varchar

190 ,"Refused" varchar

191 ,"Cries" varchar

192 ,"Vocalizations" varchar

193 ,"Begs" varchar

194 ,"Aggression" varchar

195 ,"Infant_interval_id" varchar

196 );

197

198 create unique index follow_1_minute_infant_infant_interval_id

199 on follow_1_minute_infant ("Infant_interval_id");

200

201 create index follow_1_minute_infant_interval_id

202 on follow_1_minute_infant ("Interval_id");

203

204 grant_priv(`follow_1_minute_infant')

205 grant_seq_priv(`follow_1_minute_infant', `tableid')

206

207

208 -- follow_1_minute_mother

209 create table follow_1_minute_mother (

210 tableid serial primary key

211 ,"Interval_id" varchar

212 constraint "follow_1_minute_mother.Interval_id must be a follow_1_minute.Interval_id"

213 REFERENCES follow_1_minute("Interval_id")

214 ,"Mother_id" varchar

215 ,"Behavior" varchar

216 ,"Grooms_other" varchar

217 ,"Groomed_by" varchar

218 ,"Vocalizations" varchar

219 );

220

221 create index follow_1_minute_mother_interval_id

222 on follow_1_minute_mother ("Interval_id");

223

224 grant_priv(`follow_1_minute_mother')

225 grant_seq_priv(`follow_1_minute_mother', `tableid')

226

227

228 -- follow_1_minute_sibling

229 create table follow_1_minute_sibling (

230 tableid serial primary key

231 ,"Interval_id" varchar

232 constraint "follow_1_minute_sibling.Interval_id must be a follow_1_minute.Interval_id"

233 REFERENCES follow_1_minute("Interval_id")

234 ,"Sibling_id" varchar

235 ,"Bad_observation" varchar

236 ,"Behavior" varchar

237 ,"Distance_from_mother" varchar

238 ,"Vocalizations" varchar

239 ,"Plays" varchar

240 ,"Grooms_other" varchar

241 ,"Groomed_by" varchar

242 ,"Distance_from_infant" varchar

243 ,"Sibling_interval_id" varchar

244 );

245

246 create unique index follow_1_minute_sibling_sibling_interval_id

247 on follow_1_minute_sibling ("Sibling_interval_id");

248

249 create index follow_1_minute_sibling_interval_id

250 on follow_1_minute_sibling ("Interval_id");

251

252 grant_priv(`follow_1_minute_sibling')

253 grant_seq_priv(`follow_1_minute_sibling', `tableid')

254

255

256 -- follow_5_minute

257 create table follow_5_minute (

258 tableid serial primary key

259 ,"Page_id" varchar

260 ,"Interval_number" int

261 ,"Interval_time" timestamp without time zone

262 ,"Mother_feeding" varchar

263 ,"Food_part_initials" varchar

264 ,"Food_name" varchar

265 ,"Infant_feeding" varchar

266 ,"Strange_chimp" varchar

267 ,"Other_animals" varchar

268 ,"Interval_id" varchar

269 );

270

271 create unique index follow_5_minute_interval_id

272 on follow_5_minute ("Interval_id");

273

274 grant_priv(`follow_5_minute')

275 grant_seq_priv(`follow_5_minute', `tableid')

276

277

278 -- follow_5_minute_left_group

279 create table follow_5_minute_left_group (

280 tableid serial primary key

281 ,"Interval_id" varchar

282 constraint "follow_5_minute_left_group.Interval_id must be a follow_5_minute.Interval_id"

283 REFERENCES follow_5_minute("Interval_id")

284 ,"Position_number" int

285 ,"Chimp_id" varchar

286 ,"Present" varchar

287 ,"Position_id" varchar

288 );

289

290 create index follow_5_minute_left_group_interval_id

291 on follow_5_minute_left_group ("Interval_id");

292

293 grant_priv(`follow_5_minute_left_group')

294 grant_seq_priv(`follow_5_minute_left_group', `tableid')

295

296

297 -- follow_5_minute_right_group

298 create table follow_5_minute_right_group (

299 tableid serial primary key

300 ,"Interval_id" varchar

301 constraint "follow_5_minute_right_group.Interval_id must be a follow_5_minute.Interval_id"

302 REFERENCES follow_5_minute("Interval_id")

303 ,"Position_number" int

304 ,"Chimp_id" varchar

305 ,"Present" varchar

306 ,"Position_id" varchar

307 );

308

309 create index follow_5_minute_right_group_interval_id

310 on follow_5_minute_right_group ("Interval_id");

311

312 grant_priv(`follow_5_minute_right_group')

313 grant_seq_priv(`follow_5_minute_right_group', `tableid')

314

315 commit;