darcs - gombemi

2 --

3 -- This file is part of Gombe-MI.

4 --

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

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

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

8 -- (at your option) any later version.

9 --

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

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

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

13 -- GNU General Public License for more details.

14 --

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

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

17 --

18 -- SQL to test the biography view.

19 --

20 -- All data appearing in this work are fictitious. Any resemblance to

21 -- actual data, living or archived, is purely coincidental.

22 --

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

24 --

25 -- Bugs:

26 -- The regression test system does not like it that timestamps change.

27 -- Ignore the problem by not displaying the import_datetime column.

29 --

30 -- Main tables.

31 --

33 select 'Test insert with no twins.';

35 -- biography

36 insert into biography (

37 animid

38 , animname

39 , birthgroup

40 , bgcertainty

41 , sex

42 , momid

43 , firstborn

44 , birthdate

45 , bdmin

46 , bdmax

47 , bddist

48 , entrydate

49 , entrytype

50 , departdate

51 , departdateerror

52 , departtype)

53 values (

54 'MOM'

55 , 'Some mother'

56 , 'U'

57 , 'C'

58 , 'F'

59 , NULL

60 , 'U'

61 , '1955-01-01'

62 , '1955-01-01'

63 , '1955-01-01'

64 , 'R'

65 , '1960-01-01'

66 , 'B'

67 , '1995-01-01'

68 , 0

69 , 'D')

70 ,( 'KID'

71 , 'Some offspring'

72 , 'K'

73 , 'C'

74 , 'M'

75 , 'MOM'

76 , 'Y'

77 , '1965-01-01'

78 , '1965-01-01'

79 , '1965-01-01'

80 , 'R'

81 , '1965-01-01'

82 , 'B'

83 , '2010-01-01'

84 , 0

85 , 'D')

86 ,( 'DAD'

87 , 'Some dad'

88 , 'M'

89 , 'C'

90 , 'M'

91 , NULL

92 , 'U'

93 , '1954-01-01'

94 , '1954-01-01'

95 , '1954-01-01'

96 , 'R'

97 , '1960-01-01'

98 , 'B'

99 , '2005-01-01'

100 , 0

101 , 'D')

102 ,( 'MGE'

103 , 'Dummy variable - stranger'

104 , NULL

105 , NULL

106 , 'F'

107 , NULL

108 , NULL

109 , NULL

110 , NULL

111 , NULL

112 , NULL

113 , NULL

114 , NULL

115 , NULL

116 , NULL

117 , NULL);

118

119 select animid

120 , animname

121 , birthgroup

122 , bgcertainty

123 , sex

124 , momid

125 , firstborn

126 , birthdate

127 , bdmin

128 , bdmax

129 , bddist

130 , entrydate

131 , entrytype

132 , departdate

133 , departdateerror

134 , departtype

135 from biography_data order by animid;

136 select * from twins order by animid;

137

138

139 -- Test insert with twins.

140

141 select 'Test insert with twins.';

142

143 -- biography

144 insert into biography (

145 animid

146 , animname

147 , birthgroup

148 , bgcertainty

149 , sex

150 , momid

151 , firstborn

152 , birthdate

153 , bdmin

154 , bdmax

155 , bddist

156 , entrydate

157 , entrytype

158 , departdate

159 , departdateerror

160 , departtype

161 , twin)

162 values

163 ( 'T1'

164 , 'Some twin'

165 , 'K'

166 , 'C'

167 , 'M'

168 , 'MOM'

169 , 'Y'

170 , '1972-01-01'

171 , '1972-01-01'

172 , '1972-01-01'

173 , 'R'

174 , '1972-01-01'

175 , 'B'

176 , '2010-01-01'

177 , 0

178 , 'D'

179 , 'T2')

180 ,( 'T2'

181 , 'Some other twin'

182 , 'K'

183 , 'C'

184 , 'M'

185 , 'MOM'

186 , 'Y'

187 , '1972-01-01'

188 , '1972-01-01'

189 , '1972-01-01'

190 , 'R'

191 , '1972-01-01'

192 , 'B'

193 , '2010-01-01'

194 , 0

195 , 'D'

196 , 'T1');

197

198 select animid

199 , animname

200 , birthgroup

201 , bgcertainty

202 , sex

203 , momid

204 , firstborn

205 , birthdate

206 , bdmin

207 , bdmax

208 , bddist

209 , entrydate

210 , entrytype

211 , departdate

212 , departdateerror

213 , departtype

214 from biography_data order by animid;

215 select 'Should have twins on TWINS.';

216 select * from twins order by animid;

217

218 -- Test selection.

219 select 'selecting the biography view';

220 select animid

221 , animname

222 , birthgroup

223 , bgcertainty

224 , sex

225 , momid

226 , firstborn

227 , birthdate

228 , bdmin

229 , bdmax

230 , bddist

231 , entrydate

232 , entrytype

233 , departdate

234 , departdateerror

235 , departtype

236 , twin

237 from biography order by animid;

238

239 -- Try updating biography.

240 -- Get rid of twins.

241 select 'Test update to delete twins.';

242 update biography

243 set twin = NULL

244 where animid = 'T1' or animid = 'T2';

245

246 select 'No twins should be left on TWINS.';

247 select animid

248 , animname

249 , birthgroup

250 , bgcertainty

251 , sex

252 , momid

253 , firstborn

254 , birthdate

255 , bdmin

256 , bdmax

257 , bddist

258 , entrydate

259 , entrytype

260 , departdate

261 , departdateerror

262 , departtype

263 from biography_data order by animid;

264 select * from twins order by animid;

265

266 -- Update biography.

267 -- Add twins back.

268 select 'Test update to add twins back.';

269 begin;

270 update biography

271 set twin = 'T2'

272 where animid = 'T1';

273 update biography

274 set twin = 'T1'

275 where animid = 'T2';

276 commit;

277

278 select 'The twins should be back on TWINS.';

279 select animid

280 , animname

281 , birthgroup

282 , bgcertainty

283 , sex

284 , momid

285 , firstborn

286 , birthdate

287 , bdmin

288 , bdmax

289 , bddist

290 , entrydate

291 , entrytype

292 , departdate

293 , departdateerror

294 , departtype

295 from biography_data order by animid;

296 select * from twins order by animid;

297

298 --

299 -- Delete from biography.

300 --

301 select 'Test delete of biography';

302 delete

303 from biography

304 where animid = 'T1' or animid = 'T2';

305

306 select 'No twins should be left, anywhere.';

307 select animid

308 , animname

309 , birthgroup

310 , bgcertainty

311 , sex

312 , momid

313 , firstborn

314 , birthdate

315 , bdmin

316 , bdmax

317 , bddist

318 , entrydate

319 , entrytype

320 , departdate

321 , departdateerror

322 , departtype

323 from biography_data order by animid;

324 select * from twins order by animid;