darcs - gombemi

3 dnl

4 dnl This file is part of Gombe-MI.

5 dnl

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

7 dnl it under the terms of the GNU General Public License as published by

8 dnl the Free Software Foundation; either version 3 of the License, or

9 dnl (at your option) any later version.

10 dnl

11 dnl This program is distributed in the hope that it will be useful,

12 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of

13 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

14 dnl GNU General Public License for more details.

15 dnl

16 dnl You should have received a copy of the GNU General Public License

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

18 dnl

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

20 dnl

21 dnl Macros used to create tables.

22 dnl

23 dnl

24 dnl Define m4 macros so we don't have to type so much.

25 dnl (Or at least type stuff that's more fun.)

26 dnl

27 dnl Watch out for using the single quote char as it has speical

28 dnl meaning to m4.

29 dnl

30 dnl The dates and timestamps should probably have some sort of

31 dnl reasonable constraints on them to prevent operator error.

32 dnl (Like entering dates from the last century.)

33 dnl

34 dnl Bugs:

35 dnl

36 changequote([{[,]}])dnl m4 foolery so includes include only once.

37 dnl Once the macro is in the text, change the quotes back

38 ifdef([{[_tablemacros.m4]}], [{[changequote(`,')]}], [{[dnl

39 changequote(`,')dnl

40 dnl

41 dnl Standard test for having already included the file.

42 define(`_tablemacros.m4')dnl

43 dnl

44 dnl Don't output anything while defining macros.

45 divert(-1)

47 dnl Make a table with/without a INTEGER SERIAL id.

48 dnl Syntax: serial_table(tablename, id_datatype, tablecols)

49 changequote([,])

50 define([serial_table],[

51 ifelse($2, [INTEGER],changequote([,])dnl Remove braces surrounding expanded text

52 [dnl

53 dnl Integer key, use SERIAL datatype

54 CREATE TABLE $1 (

55 id SERIAL PRIMARY KEY,

56 ]changequote(`,')dnl See above.

57 , changequote([,])dnl See above.

58 [dnl

59 dnl Non-INTEGER key, simulate SERIAL.

60 CREATE SEQUENCE $1_id_seq;

61 CREATE TABLE $1 (

62 id $2 PRIMARY KEY NOT NULL DEFAULT nextval('$1_id_seq'),

63 ]changequote(`,')dnl See above.

64 )dnl

65 $3dnl

66 );

67 grant_priv($1)

68 ])

69 changequote(`,')

72 dnl A hierarchical table.

73 dnl Syntax: hier_table(tablename, id_datatype, parent, parent_datatype)

74 define(`hier_table',`

75 serial_table($1, $2, `dnl

76 descr TEXT NOT NULL,

77 $3 $4 NOT NULL CONSTRAINT "$3 must be on $3" REFERENCES $3

78 ')

79 ')

81 dnl Create a support table.

82 dnl Syntax: support_table(tablename, id_name, id_datatype, id_constraint)

83 define(`support_table',`

84 CREATE TABLE $1 (

85 $2 $3 PRIMARY KEY NOT NULL $4,

86 descr TEXT NOT NULL UNIQUE

87 empty_string_check(descr)

88 );

89 grant_priv($1)

90 ')

92 --

93 -- Data types/column definitions

94 --

96 -- Sname

97 define(`sname_column', `sname_type_column(sname, Sname, NULL)')dnl

99 dnl A Sname column with a different name.

100 dnl Syntax: sname_type_column(colname, Colname, null)

101 dnl

102 dnl colname The name of the column in lower case.

103 dnl Colname The name of the column in camelcaps.

104 dnl null Either `NULL' or `NOT NULL'.

105 dnl

106 define(`sname_type_column', `

107 $1 CHAR(3) $3

108 CONSTRAINT "$2 on BIOGRAPH" REFERENCES biograph(sname)')dnl

109

110 -- Sname -- on MATUREDATES

111 -- There's a few tables that require a maturity date.

112 define(`maturedates_sname_column', `

113 sname CHAR(3) NOT NULL

114 CONSTRAINT "Sname on MATUREDATES" REFERENCES maturedates(sname)')dnl

115

116 --

117 -- Constraints

118 --

119

120 -- A column cannot contain the empty string or nothing but spaces.

121 --

122 -- Syntax: empty_string_check(col)

123 --

124 -- Input:

125 -- col Name of the column to check.

126 --

127 changequote([,])

128 define([empty_string_check], [ CONSTRAINT "$1: Cannot be empty or nothing but spaces"

129 CHECK(trim(from $1) <> '')])dnl

130 changequote(`,')dnl See above.

131

132 -- A column cannot contain nothing but spaces.

133 --

134 -- Syntax: no_spaces_check(col)

135 --

136 -- Input:

137 -- col Name of the column to check.

138 --

139 changequote([,])

140 define([no_spaces_check], [ CONSTRAINT "$1: Cannot be nothing but spaces"

141 CHECK($1 IS NULL OR $1 = '' OR trim(from $1) <> '')])dnl

142 changequote(`,')dnl See above.

143

144 -- A column cannot contain any spaces.

145 --

146 -- Syntax: space_not_allowed_check(col)

147 --

148 -- Input:

149 -- col Name of the column to check.

150 --

151 changequote([,])

152 define([space_not_allowed_check], [ CONSTRAINT "$1: Cannot contain any spaces"

153 CHECK($1 IS NULL OR POSITION(' ' IN $1) = 0)])dnl

154 changequote(`,')dnl See above.

155

156

157 -- A time-ish column cannot contain seconds or fractions of a second.

158 --

159 -- Syntax: no_seconds_check(col)

160 --

161 -- Input:

162 -- col Name of the column to check.

163 --

164 changequote([,])

165 define([no_seconds_check], [ CONSTRAINT "$1: Cannot contain seconds or fractions thereof"

166 CHECK (EXTRACT(SECONDS FROM $1) = 0)])dnl

167 changequote(`,')dnl See above

168

169 -- A pair of columns must both be either NULL or non-NULL.

170 --

171 -- Syntax: null_iff_null(col1, col2)

172 --

173 -- Input:

174 -- col1 Name of one of the columns.

175 -- col2 Name of the other column.

176 --

177 changequote([,])

178 define([null_iff_null], [ CONSTRAINT "$1 must be NULL if and only if $2 is NULL"

179 CHECK(($1 IS NULL AND $2 IS NULL)

180 OR ($1 IS NOT NULL AND $2 IS NOT NULL))])dnl

181 changequote(`,')dnl See above

182

183 -- One column must be NULL if another is.

184 --

185 -- Syntax: null_if_null(col1, col2)

186 --

187 -- Input:

188 -- col1 This column must be null if col2 is null.

189 -- col2 Name of the referenced column.

190 --

191 changequote([,])

192 define([null_if_null], [ CONSTRAINT "$1 must be NULL if $2 is NULL"

193 CHECK(($2 IS NULL AND $1 IS NULL)

194 OR $2 IS NOT NULL)])dnl

195 changequote(`,')dnl See above

196

197 -- One column must be NULL if and only if another is not TRUE.

198 --

199 -- Syntax: null_iff_not_true(col1, col2)

200 --

201 -- Input:

202 -- col1 This column must be null if col2 is NULL or TRUE.

203 -- col2 Name of the referenced column.

204 --

205 changequote([,])

206 define([null_iff_not_true], [ CONSTRAINT "$1 must be NULL if and only if $2 is not TRUE"

207 CHECK((NOT($2) AND $1 IS NULL)

208 OR ($2 AND $2 IS NOT NULL AND $1 IS NOT NULL))])dnl

209 changequote(`,')dnl See above

210

211

212 -- One column must be NULL if another is not TRUE.

213 --

214 -- Syntax: null_if_not_true(col1, col2)

215 --

216 -- Input:

217 -- col1 This column must be null if col2 is NULL or TRUE.

218 -- col2 Name of the referenced column.

219 --

220 changequote([,])

221 define([null_if_not_true], [ CONSTRAINT "$1 must be NULL if $2 is not TRUE"

222 CHECK((NOT($2) AND $1 IS NULL)

223 OR $2)])dnl

224 changequote(`,')dnl See above

225

226 --

227 -- Triggers

228 --

229

230 --

231 -- We can't use a regular REFERENCES constraint with AnimID because

232 -- we regularly replace the content of BIOGRAPHY_DATA and we

233 -- don't want to have to defer every one of these constraints until

234 -- transaction commit. Instead, we use 2 triggers, one on

235 -- BIOGRAPHY_DATA that's deferred and another on the referencing

236 -- table that's not. Rather than use a pl/pgsql EXECUTE statement

237 -- we use m4 to define each pair of triggers.

238 --

239 -- This is done in the table creation code because it's expected

240 -- to be a "set it and forget it" type of constraint.

241 --

242

243 -- A column references BIOGRAPY_DATA.AnimID.

244 --

245 -- Syntax: animid_ref(table, col, key)

246 --

247 -- Input:

248 -- table The table which has the referencing column.

249 -- col Name of the referenceing column.

250 -- key (Optional) Name of the column which keys the table.

251 -- When present key is shown in error message.

252 --

253 -- Remarks:

254 -- Creates the following:

255 -- $1__$2_animid_ref_func() Function to check animid exists.

256 -- $1__$2_animid_refd_func() Function to check animid is not referenced.

257 -- 0_$1__$2_animid_ref_trigger Trigger to call ref function.

258 -- 0_$1__$2_animid_refd_trigger Trigger to call refd function.

259 --

260 -- Triggers begin with a0_ so they execute before other triggers.

261 --

262 changequote([,])

263 define([animid_ref], [

264

265 include(`globalmacros.m4')

266

267 SELECT '$1__$2_animid_ref_func' AS function;

268 CREATE OR REPLACE FUNCTION $1__$2_animid_ref_func()

269 RETURNS trigger

270 LANGUAGE plpgsql

271 gmi_function_set_search_path

272 AS $$

273 -- Function for $1 insert and update to check

274 -- $2 references to BIOGRAPHY_DATA.animid.

275 --

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

277 --

278 BEGIN

279

280 IF NEW.$2 IS NOT NULL THEN

281 PERFORM 1

282 FROM biography_data

283 WHERE biography_data.animid = NEW.$2

284 LIMIT 1;

285 IF NOT FOUND THEN

286 RAISE foreign_key_violation

287 USING MESSAGE = 'Error on insert or update of $1'

288 , DETAIL = ifelse(`$3', `', `', '`Key ($3) = (''` || NEW.$3 || ''`): ''` || ')

289 'Value ($2) = ('

290 || NEW.$2

291 || ') is not a BIOGRAPHY_DATA.AnimID';

292 RETURN NULL;

293 END IF;

294 END IF;

295

296 RETURN NULL;

297 END;

298 $$;

299

300

301 SELECT '$1__$2_animid_refd_func' AS function;

302 CREATE OR REPLACE FUNCTION $1__$2_animid_refd_func()

303 RETURNS trigger

304 LANGUAGE plpgsql

305 gmi_function_set_search_path

306 AS $$

307 -- Function for BIOGRAPHY_DATA delete to check for dangling

308 -- references from $1.$2.

309 --

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

311 --

312 -- Remarks:

313 -- Fired on transaction commit so that we can replace the content

314 -- of BIOGRAPHY_DATA in a single transaction.

315 --

316 BEGIN

317

318 -- See if the BIOGRAPHY_DATA row still exists.

319 PERFORM 1

320 FROM biography_data

321 WHERE biography_data.animid = OLD.animid;

322 IF FOUND THEN

323 -- The deleted row was subsequently re-inserted.

324 -- Nothing to do.

325 RETURN NULL;

326 END IF;

327

328 -- The deleted Animid must not exist on $1.

329 PERFORM 1

330 FROM $1

331 WHERE $1.$2 = OLD.animid

332 LIMIT 1;

333 IF FOUND THEN

334 RAISE foreign_key_violation

335 USING MESSAGE = 'Error on commit of BIOGRAPHY_DATA after delete'

336 , DETAIL = 'Key (AnimID) = ('

337 || OLD.animid

338 || ') is still referenced by $1.$2';

339 RETURN NULL;

340 END IF;

341

342 RETURN NULL;

343 END;

344 $$;

345

346

347 SELECT 'a0_$1__$2_animid_ref_trigger' AS trigger;

348 CREATE TRIGGER a0_$1__$2_animid_ref_trigger

349 AFTER INSERT OR UPDATE

350 ON $1 FOR EACH ROW

351 EXECUTE PROCEDURE $1__$2_animid_ref_func();

352

353 SELECT 'a0_$1__$2_animid_refd_trigger' AS trigger;

354 CREATE CONSTRAINT TRIGGER a0_$1__$2_animid_refd_trigger

355 AFTER DELETE

356 ON biography_data

357 DEFERRABLE INITIALLY DEFERRED

358 FOR EACH ROW

359 EXECUTE PROCEDURE $1__$2_animid_refd_func();

360

361 ])dnl

362 changequote(`,')dnl See above

363

364 -- Done defining macros.

365 divert`'dnl

366

367 ]}])dnl End of ifdef over the whole file.