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 Run_integrity_queries function.

19 dnl

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

21 dnl

22 dnl

23 dnl m4 includes

24 include(`copyright.m4')

25 include(`constants.m4')

26 include(`macros.m4')

27 dnl

29 dnl plpgsql fragment to define columns returned

30 dnl

31 dnl Syntax: _return_cols()

32 dnl

33 dnl

34 changequote({,})

35 define({_return_cols},{

36 iwid integrity_warnings.iwid%TYPE

37 , iqname integrity_warnings.iqname%TYPE

38 , first_seen integrity_warnings.first_seen%TYPE

39 , last_seen integrity_warnings.last_seen%TYPE

40 , category integrity_warnings.category%TYPE

41 , warning_id integrity_warnings.warning_id%TYPE

42 , warning_message integrity_warnings.warning_message%TYPE

43 , notes integrity_warnings.notes%TYPE

44 })dnl

45 changequote(`,')dnl

48 dnl plpgsql fragment to put exception diagnostic info into vars.

49 dnl

50 dnl Syntax: _get_exception_info(msg, detail, hint, context)

51 dnl

52 dnl Input:

53 dnl names of variables in which to save exception info

54 dnl

55 changequote({,})

56 define({_get_exception_info},{

57 GET STACKED DIAGNOSTICS

58 $1 = MESSAGE_TEXT

59 , $2 = PG_EXCEPTION_DETAIL

60 , $3 = PG_EXCEPTION_HINT

61 , $4 =PG_EXCEPTION_CONTEXT;

63 IF $2 <> '' THEN

64 $1 := $1 || E'\n';

65 $2 := 'The detail is: ' || $2;

66 END IF;

67 IF $3 <> '' THEN

68 $2 := $2 || E'\n';

69 $3 := 'The hint is: ' || $3;

70 END IF;

71 $3 := $3 || E'\n';

72 IF $4 <> '' THEN

73 $4 := E'\nAdditional diagnostic information: ' || $4;

74 END IF;

75 })dnl

76 changequote(`,')dnl

79 CREATE OR REPLACE FUNCTION run_integrity_queries()

80 RETURNS TABLE(_return_cols())

81 LANGUAGE plpgsql

82 SECURITY INVOKER

83 gmi_function_set_search_path

84 AS $$

85 -- Run all queries in INTEGRITY_QUERIES.

86 --

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

88 --

89 -- Syntax: run_integrity_queries()

90 --

91 -- Arguments:

92 --

93 -- Remarks:

94 --

95 -- Bugs:

97 DECLARE

99 BEGIN

100

101 RETURN QUERY EXECUTE 'SELECT * FROM run_integrity_queries(

102 $RIQ$SELECT integrity_queries.iqname

103 FROM integrity_queries$RIQ$)';

104 END;

105 $$;

106 grant_func_priv(`run_integrity_queries()')

107

108

109 CREATE OR REPLACE FUNCTION run_integrity_queries(query TEXT)

110 RETURNS TABLE(_return_cols())

111 LANGUAGE plpgsql

112 SECURITY INVOKER

113 gmi_function_set_search_path

114 AS $$

115 -- Run all queries in INTEGRITY_QUERIES.

116 --

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

118 --

119 -- Syntax: run_integrity_queries(query)

120 --

121 -- Arguments:

122 -- query A query returning 1 column, of INTEGRITY_QUERY.IQName values.

123 --

124 -- Remarks:

125 --

126 -- Bugs:

127

128 DECLARE

129

130 this_iqname integrity_queries.iqname%TYPE;

131 saved_query integrity_queries.query%TYPE;

132

133 id TEXT;

134 msg TEXT;

135

136 my_message_text TEXT;

137 my_pg_exception_detail TEXT;

138 my_pg_exception_hint TEXT;

139 my_pg_exception_context TEXT;

140

141 exception_caught BOOLEAN := FALSE;

142

143 BEGIN

144 -- Run the supplied query and iterate over the saved queries.

145 FOR this_iqname IN EXECUTE query LOOP

146

147 -- Save all the existing warning ids for the saved query

148 CREATE TEMP TABLE _riq_warning_ids AS

149 SELECT integrity_warnings.warning_id

150 FROM integrity_warnings

151 WHERE integrity_warnings.iqname = this_iqname;

152

153 CREATE UNIQUE INDEX _riq_warning_ids_warning_id

154 ON _riq_warning_ids (warning_id);

155

156 -- Get the saved query

157 SELECT integrity_queries.query

158 INTO saved_query

159 FROM integrity_queries

160 WHERE integrity_queries.iqname = this_iqname;

161

162 IF NOT FOUND THEN

163 RAISE EXCEPTION data_exception USING

164 MESSAGE = 'Error in run_integrity_queries(TEXT)'

165 , DETAIL = 'Supplied query returned a value ('

166 || textualize(`this_iqname')

167 || ') that is not an existing'

168 || ' INTEGRITY_QUERIES.IQName value';

169 RETURN;

170 END IF;

171

172 BEGIN

173 FOR id, msg IN EXECUTE saved_query LOOP

174 -- Remember that we processed the id.

175 DELETE FROM _riq_warning_ids

176 WHERE _riq_warning_ids.warning_id = id;

177

178 IF FOUND THEN

179 -- Update the existing integrity_warnings row.

180 UPDATE integrity_warnings

181 SET last_seen = localtimestamp

182 WHERE integrity_warnings.iqname = this_iqname

183 AND integrity_warnings.warning_id = id;

184 ELSE

185 -- Make a new integrity_warnings row.

186 INSERT INTO integrity_warnings

187 (iqname, first_seen, last_seen, warning_id, warning_message)

188 VALUES (this_iqname, localtimestamp, localtimestamp, id, msg);

189 END IF;

190

191 END LOOP;

192 EXCEPTION

193 -- We want all of these to trap most that could go wrong when a

194 -- user-supplied bit of sql is executed:

195 -- data_exception (22000) bad expressions (divide by 0, etc.)

196 -- integrity_constraint_violation (23000) violating insert/update

197 -- invalid_sql_statement_name (26000)

198 -- sql_routine_exception (2F000)

199 -- syntax_error_or_access_rule_violation (42000)

200 WHEN feature_not_supported

201 OR sql_statement_not_yet_complete

202 OR data_exception

203 OR integrity_constraint_violation

204 OR invalid_sql_statement_name

205 OR sql_routine_exception

206 OR syntax_error_or_access_rule_violation THEN

207 _get_exception_info(my_message_text,

208 my_pg_exception_detail,

209 my_pg_exception_hint,

210 my_pg_exception_context)

211 exception_caught := TRUE;

212 RAISE EXCEPTION data_exception USING

213 MESSAGE = 'run_integrity_queries:'

214 || ' Error executing stored INTEGRITY_QUERIES.Query'

215 , DETAIL = 'Problem in the saved query with IQName = ('

216 || this_iqname || E'):\n'

217 || 'The error is: ' || my_message_text

218 || my_pg_exception_detail

219 || my_pg_exception_hint

220 || E'The query is:\n'

221 || saved_query

222 || my_pg_exception_context;

223 END;

224

225 -- Note that we've processed the saved query.

226 UPDATE integrity_queries

227 SET last_run = localtimestamp

228 , first_run = COALESCE(first_run, localtimestamp)

229 WHERE integrity_queries.iqname = this_iqname;

230

231 -- Delete old query results that are not found this time.

232 DELETE FROM integrity_warnings

233 USING _riq_warning_ids

234 WHERE integrity_warnings.iqname = this_iqname

235 AND integrity_warnings.warning_id = _riq_warning_ids.warning_id;

236

237 -- Cleanup

238 DROP TABLE _riq_warning_ids CASCADE;

239

240 -- Return the rows found with their new values.

241 RETURN QUERY

242 SELECT integrity_warnings.iwid

243 , integrity_warnings.iqname

244 , integrity_warnings.first_seen

245 , integrity_warnings.last_seen

246 , integrity_warnings.category

247 , integrity_warnings.warning_id

248 , integrity_warnings.warning_message

249 , integrity_warnings.notes

250 FROM integrity_warnings

251 WHERE integrity_warnings.iqname = this_iqname

252 AND integrity_warnings.resolved IS NULL

253 AND (integrity_warnings.deferred_to IS NULL

254 OR integrity_warnings.deferred_to <= localtimestamp);

255 END LOOP; -- Over iqname

256

257 EXCEPTION

258 -- We want all of these to trap most that could go wrong when a

259 -- user-supplied bit of sql is executed:

260 -- data_exception (22000) bad expressions (divide by 0, etc.)

261 -- integrity_constraint_violation (23000) violating insert/update

262 -- invalid_sql_statement_name (26000)

263 -- sql_routine_exception (2F000)

264 -- syntax_error_or_access_rule_violation (42000)

265 WHEN feature_not_supported

266 OR sql_statement_not_yet_complete

267 OR data_exception

268 OR integrity_constraint_violation

269 OR invalid_sql_statement_name

270 OR sql_routine_exception

271 OR syntax_error_or_access_rule_violation THEN

272 IF exception_caught THEN

273 GET STACKED DIAGNOSTICS

274 my_message_text = MESSAGE_TEXT

275 , my_pg_exception_detail = PG_EXCEPTION_DETAIL;

276 RAISE EXCEPTION data_exception USING

277 MESSAGE = my_message_text

278 , DETAIL = my_pg_exception_detail;

279 ELSE

280 _get_exception_info(my_message_text,

281 my_pg_exception_detail,

282 my_pg_exception_hint,

283 my_pg_exception_context)

284 RAISE EXCEPTION data_exception USING

285 MESSAGE = 'run_integrity_queries:'

286 || ' Error executing supplied query argument'

287 , DETAIL = E'\nThe error is: ' || my_message_text

288 || my_pg_exception_detail

289 || my_pg_exception_hint

290 || E'The query is:\n'

291 || query

292 || my_pg_exception_context;

293 END IF;

294 -- WHEN OTHERS THEN

295 -- RAISE EXCEPTION data_exception USING

296 -- MESSAGE = 'SQLSTATE ' || SQLSTATE || ': SQLERRM ' || SQLERRM;

297 END;

298 $$;

299 grant_func_priv(`run_integrity_queries(TEXT)')