Merge ~cjwatson/launchpad:db-plpython3 into launchpad:db-devel
- Git
- lp:~cjwatson/launchpad
- db-plpython3
- Merge into db-devel
Proposed by
Colin Watson
Status: | Merged |
---|---|
Approved by: | Colin Watson |
Approved revision: | 519d66f3aae79cc1d7dc66ef27a8d72b79872308 |
Merge reported by: | Otto Co-Pilot |
Merged at revision: | not available |
Proposed branch: | ~cjwatson/launchpad:db-plpython3 |
Merge into: | launchpad:db-devel |
Diff against target: |
771 lines (+765/-0) 1 file modified
database/schema/patch-2210-46-0.sql (+765/-0) |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
William Grant | db | Approve | |
Review via email: mp+418572@code.launchpad.net |
Commit message
Replace PL/Python 2 functions using PL/Python 3
Description of the change
This is a prerequisite for bootstrapping Launchpad on Ubuntu 20.04, where PL/Python 2 is unavailable.
The effective diff introduced by this commit is as follows: https:/
To post a comment you must log in.
Revision history for this message
William Grant (wgrant) : | # |
review:
Approve
(db)
Revision history for this message
Colin Watson (cjwatson) : | # |
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | diff --git a/database/schema/patch-2210-46-0.sql b/database/schema/patch-2210-46-0.sql |
2 | new file mode 100644 |
3 | index 0000000..00039ea |
4 | --- /dev/null |
5 | +++ b/database/schema/patch-2210-46-0.sql |
6 | @@ -0,0 +1,765 @@ |
7 | +-- Copyright 2021 Canonical Ltd. This software is licensed under the |
8 | +-- GNU Affero General Public License version 3 (see the file LICENSE). |
9 | + |
10 | +SET client_min_messages=ERROR; |
11 | + |
12 | +-- Replace all PL/Python2 functions with PL/Python3 equivalents. |
13 | + |
14 | +CREATE EXTENSION IF NOT EXISTS plpython3u WITH SCHEMA pg_catalog; |
15 | + |
16 | +COMMENT ON EXTENSION plpython3u IS 'PL/Python3U untrusted procedural language'; |
17 | + |
18 | +CREATE OR REPLACE FUNCTION _ftq(text) RETURNS text |
19 | + LANGUAGE plpython3u IMMUTABLE STRICT |
20 | + AS $_$ |
21 | + import re |
22 | + |
23 | + # I think this method would be more robust if we used a real |
24 | + # tokenizer and parser to generate the query string, but we need |
25 | + # something suitable for use as a stored procedure which currently |
26 | + # means no external dependencies. |
27 | + |
28 | + query = args[0] |
29 | + ## plpy.debug('1 query is %s' % repr(query)) |
30 | + |
31 | + # Replace tsquery operators with ' '. '<' begins all the phrase |
32 | + # search operators, and a standalone '>' is fine. |
33 | + query = re.sub('[|&!<]', ' ', query) |
34 | + |
35 | + # Normalize whitespace |
36 | + query = re.sub("\s+"," ", query) |
37 | + |
38 | + # Convert AND, OR, NOT to tsearch2 punctuation |
39 | + query = re.sub(r"\bAND\b", "&", query) |
40 | + query = re.sub(r"\bOR\b", "|", query) |
41 | + query = re.sub(r"\bNOT\b", " !", query) |
42 | + ## plpy.debug('2 query is %s' % repr(query)) |
43 | + |
44 | + # Deal with unwanted punctuation. |
45 | + # ':' is used in queries to specify a weight of a word. |
46 | + # '\' is treated differently in to_tsvector() and to_tsquery(). |
47 | + punctuation = r'[:\\]' |
48 | + query = re.sub(r"%s+" % (punctuation,), " ", query) |
49 | + ## plpy.debug('3 query is %s' % repr(query)) |
50 | + |
51 | + # Now that we have handle case sensitive booleans, convert to lowercase |
52 | + query = query.lower() |
53 | + |
54 | + # Remove unpartnered bracket on the left and right |
55 | + query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query) |
56 | + query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query) |
57 | + |
58 | + # Remove spurious brackets |
59 | + query = re.sub(r"\(([^\&\|]*?)\)", r" \1 ", query) |
60 | + ## plpy.debug('5 query is %s' % repr(query)) |
61 | + |
62 | + # Insert & between tokens without an existing boolean operator |
63 | + # ( not proceeded by (|&! |
64 | + query = re.sub(r"(?<![\(\|\&\!])\s*\(", "&(", query) |
65 | + ## plpy.debug('6 query is %s' % repr(query)) |
66 | + # ) not followed by )|& |
67 | + query = re.sub(r"\)(?!\s*(\)|\||\&|\s*$))", ")&", query) |
68 | + ## plpy.debug('6.1 query is %s' % repr(query)) |
69 | + # Whitespace not proceded by (|&! not followed by &| |
70 | + query = re.sub(r"(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query) |
71 | + ## plpy.debug('7 query is %s' % repr(query)) |
72 | + |
73 | + # Detect and repair syntax errors - we are lenient because |
74 | + # this input is generally from users. |
75 | + |
76 | + # Fix unbalanced brackets |
77 | + openings = query.count("(") |
78 | + closings = query.count(")") |
79 | + if openings > closings: |
80 | + query = query + " ) "*(openings-closings) |
81 | + elif closings > openings: |
82 | + query = " ( "*(closings-openings) + query |
83 | + ## plpy.debug('8 query is %s' % repr(query)) |
84 | + |
85 | + # Strip ' character that do not have letters on both sides |
86 | + query = re.sub(r"((?<!\w)'|'(?!\w))", "", query) |
87 | + |
88 | + # Brackets containing nothing but whitespace and booleans, recursive |
89 | + last = "" |
90 | + while last != query: |
91 | + last = query |
92 | + query = re.sub(r"\([\s\&\|\!]*\)", "", query) |
93 | + ## plpy.debug('9 query is %s' % repr(query)) |
94 | + |
95 | + # An & or | following a ( |
96 | + query = re.sub(r"(?<=\()[\&\|\s]+", "", query) |
97 | + ## plpy.debug('10 query is %s' % repr(query)) |
98 | + |
99 | + # An &, | or ! immediatly before a ) |
100 | + query = re.sub(r"[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query) |
101 | + ## plpy.debug('11 query is %s' % repr(query)) |
102 | + |
103 | + # An &,| or ! followed by another boolean. |
104 | + query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query) |
105 | + ## plpy.debug('12 query is %s' % repr(query)) |
106 | + |
107 | + # Leading & or | |
108 | + query = re.sub(r"^[\s\&\|]+", "", query) |
109 | + ## plpy.debug('13 query is %s' % repr(query)) |
110 | + |
111 | + # Trailing &, | or ! |
112 | + query = re.sub(r"[\&\|\!\s]+$", "", query) |
113 | + ## plpy.debug('14 query is %s' % repr(query)) |
114 | + |
115 | + # If we have nothing but whitespace and tsearch2 operators, |
116 | + # return NULL. |
117 | + if re.search(r"^[\&\|\!\s\(\)]*$", query) is not None: |
118 | + return None |
119 | + |
120 | + ## plpy.debug('15 query is %s' % repr(query)) |
121 | + |
122 | + return query or None |
123 | + $_$; |
124 | + |
125 | +CREATE OR REPLACE FUNCTION assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean |
126 | + LANGUAGE plpython3u STABLE |
127 | + AS $$ |
128 | + rv = plpy.execute(""" |
129 | + SELECT * FROM LaunchpadDatabaseRevision |
130 | + WHERE major=%d AND minor=%d AND patch=%d |
131 | + """ % (major, minor, patch)) |
132 | + if len(rv) == 0: |
133 | + raise Exception( |
134 | + 'patch-%d-%02d-%d not applied.' % (major, minor, patch)) |
135 | + else: |
136 | + return True |
137 | +$$; |
138 | + |
139 | +CREATE OR REPLACE FUNCTION valid_bug_name(text) RETURNS boolean |
140 | + LANGUAGE plpython3u IMMUTABLE STRICT |
141 | + AS $_$ |
142 | + import re |
143 | + name = args[0] |
144 | + pat = r"^[a-z][a-z0-9+\.\-]+$" |
145 | + if re.match(pat, name): |
146 | + return 1 |
147 | + return 0 |
148 | +$_$; |
149 | + |
150 | +CREATE OR REPLACE FUNCTION valid_name(text) RETURNS boolean |
151 | + LANGUAGE plpython3u IMMUTABLE STRICT |
152 | + AS $$ |
153 | + import re |
154 | + name = args[0] |
155 | + pat = r"^[a-z0-9][a-z0-9\+\.\-]*\Z" |
156 | + if re.match(pat, name): |
157 | + return 1 |
158 | + return 0 |
159 | +$$; |
160 | + |
161 | +CREATE OR REPLACE FUNCTION debversion_sort_key(version text) RETURNS text |
162 | + LANGUAGE plpython3u IMMUTABLE STRICT |
163 | + AS $_$ |
164 | + # If this method is altered, then any functional indexes using it |
165 | + # need to be rebuilt. |
166 | + import re |
167 | + |
168 | + VERRE = re.compile("(?:([0-9]+):)?(.+?)(?:-([^-]+))?$") |
169 | + |
170 | + MAP = "0123456789ABCDEFGHIJKLMNOPQRSTUV" |
171 | + |
172 | + epoch, version, release = VERRE.match(args[0]).groups() |
173 | + key = [] |
174 | + for part, part_weight in ((epoch, 3000), (version, 2000), (release, 1000)): |
175 | + if not part: |
176 | + continue |
177 | + i = 0 |
178 | + l = len(part) |
179 | + while i != l: |
180 | + c = part[i] |
181 | + if c.isdigit(): |
182 | + key.append(part_weight) |
183 | + j = i |
184 | + while i != l and part[i].isdigit(): i += 1 |
185 | + key.append(part_weight+int(part[j:i] or "0")) |
186 | + elif c == "~": |
187 | + key.append(0) |
188 | + i += 1 |
189 | + elif c.isalpha(): |
190 | + key.append(part_weight+ord(c)) |
191 | + i += 1 |
192 | + else: |
193 | + key.append(part_weight+256+ord(c)) |
194 | + i += 1 |
195 | + if not key or key[-1] != part_weight: |
196 | + key.append(part_weight) |
197 | + key.append(part_weight) |
198 | + key.append(1) |
199 | + |
200 | + # Encode our key and return it |
201 | + # |
202 | + result = [] |
203 | + for value in key: |
204 | + if not value: |
205 | + result.append("000") |
206 | + else: |
207 | + element = [] |
208 | + while value: |
209 | + element.insert(0, MAP[value & 0x1F]) |
210 | + value >>= 5 |
211 | + element_len = len(element) |
212 | + if element_len < 3: |
213 | + element.insert(0, "0"*(3-element_len)) |
214 | + elif element_len == 3: |
215 | + pass |
216 | + elif element_len < 35: |
217 | + element.insert(0, MAP[element_len-4]) |
218 | + element.insert(0, "X") |
219 | + elif element_len < 1027: |
220 | + element.insert(0, MAP[(element_len-4) & 0x1F]) |
221 | + element.insert(0, MAP[(element_len-4) & 0x3E0]) |
222 | + element.insert(0, "Y") |
223 | + else: |
224 | + raise ValueError("Number too large") |
225 | + result.extend(element) |
226 | + return "".join(result) |
227 | +$_$; |
228 | + |
229 | +CREATE OR REPLACE FUNCTION ftiupdate() RETURNS trigger |
230 | + LANGUAGE plpython3u |
231 | + AS $_$ |
232 | + new = TD["new"] |
233 | + args = TD["args"][:] |
234 | + |
235 | + # Short circuit if none of the relevant columns have been |
236 | + # modified and fti is not being set to NULL (setting the fti |
237 | + # column to NULL is thus how we can force a rebuild of the fti |
238 | + # column). |
239 | + if TD["event"] == "UPDATE" and new["fti"] != None: |
240 | + old = TD["old"] |
241 | + relevant_modification = False |
242 | + for column_name in args[::2]: |
243 | + if new[column_name] != old[column_name]: |
244 | + relevant_modification = True |
245 | + break |
246 | + if not relevant_modification: |
247 | + return "OK" |
248 | + |
249 | + # Generate an SQL statement that turns the requested |
250 | + # column values into a weighted tsvector |
251 | + sql = [] |
252 | + for i in range(0, len(args), 2): |
253 | + sql.append( |
254 | + "setweight(to_tsvector('default', coalesce(" |
255 | + "substring(ltrim($%d) from 1 for 2500),''))," |
256 | + "CAST($%d AS \"char\"))" % (i + 1, i + 2)) |
257 | + args[i] = new[args[i]] |
258 | + |
259 | + sql = "SELECT %s AS fti" % "||".join(sql) |
260 | + |
261 | + # Execute and store in the fti column |
262 | + plan = plpy.prepare(sql, ["text", "char"] * (len(args) // 2)) |
263 | + new["fti"] = plpy.execute(plan, args, 1)[0]["fti"] |
264 | + |
265 | + # Tell PostgreSQL we have modified the data |
266 | + return "MODIFY" |
267 | +$_$; |
268 | + |
269 | +CREATE OR REPLACE FUNCTION ftq(text) RETURNS tsquery |
270 | + LANGUAGE plpython3u IMMUTABLE STRICT |
271 | + AS $_$ |
272 | + p = plpy.prepare( |
273 | + "SELECT to_tsquery('default', _ftq($1)) AS x", ["text"]) |
274 | + query = plpy.execute(p, args, 1)[0]["x"] |
275 | + return query or None |
276 | + $_$; |
277 | + |
278 | +CREATE OR REPLACE FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text |
279 | + LANGUAGE plpython3u IMMUTABLE |
280 | + AS $$ |
281 | + # If this method is altered, then any functional indexes using it |
282 | + # need to be rebuilt. |
283 | + import re |
284 | + import datetime |
285 | + |
286 | + date_expected, name = args |
287 | + |
288 | + def substitute_filled_numbers(match): |
289 | + return match.group(0).zfill(5) |
290 | + |
291 | + name = re.sub('\d+', substitute_filled_numbers, name) |
292 | + if date_expected is None: |
293 | + # NULL dates are considered to be in the future. |
294 | + date_expected = datetime.datetime(datetime.MAXYEAR, 1, 1) |
295 | + return '%s %s' % (date_expected, name) |
296 | +$$; |
297 | + |
298 | +CREATE OR REPLACE FUNCTION mv_validpersonorteamcache_emailaddress() RETURNS trigger |
299 | + LANGUAGE plpython3u SECURITY DEFINER |
300 | + AS $_$ |
301 | + # This trigger function keeps the ValidPersonOrTeamCache materialized |
302 | + # view in sync when updates are made to the EmailAddress table. |
303 | + # Note that if the corresponding person is a team, changes to this table |
304 | + # have no effect. |
305 | + PREF = 4 # Constant indicating preferred email address |
306 | + |
307 | + if "delete_plan" not in SD: |
308 | + param_types = ["int4"] |
309 | + |
310 | + SD["is_team"] = plpy.prepare(""" |
311 | + SELECT teamowner IS NOT NULL AS is_team FROM Person WHERE id = $1 |
312 | + """, param_types) |
313 | + |
314 | + SD["delete_plan"] = plpy.prepare(""" |
315 | + DELETE FROM ValidPersonOrTeamCache WHERE id = $1 |
316 | + """, param_types) |
317 | + |
318 | + SD["insert_plan"] = plpy.prepare(""" |
319 | + INSERT INTO ValidPersonOrTeamCache (id) VALUES ($1) |
320 | + """, param_types) |
321 | + |
322 | + SD["maybe_insert_plan"] = plpy.prepare(""" |
323 | + INSERT INTO ValidPersonOrTeamCache (id) |
324 | + SELECT Person.id |
325 | + FROM Person |
326 | + JOIN EmailAddress ON Person.id = EmailAddress.person |
327 | + LEFT OUTER JOIN ValidPersonOrTeamCache |
328 | + ON Person.id = ValidPersonOrTeamCache.id |
329 | + WHERE Person.id = $1 |
330 | + AND ValidPersonOrTeamCache.id IS NULL |
331 | + AND status = %(PREF)d |
332 | + AND merged IS NULL |
333 | + -- AND password IS NOT NULL |
334 | + """ % vars(), param_types) |
335 | + |
336 | + def is_team(person_id): |
337 | + """Return true if person_id corresponds to a team""" |
338 | + if person_id is None: |
339 | + return False |
340 | + return plpy.execute(SD["is_team"], [person_id], 1)[0]["is_team"] |
341 | + |
342 | + class NoneDict: |
343 | + def __getitem__(self, key): |
344 | + return None |
345 | + |
346 | + old = TD["old"] or NoneDict() |
347 | + new = TD["new"] or NoneDict() |
348 | + |
349 | + #plpy.info("old.id == %s" % old["id"]) |
350 | + #plpy.info("old.person == %s" % old["person"]) |
351 | + #plpy.info("old.status == %s" % old["status"]) |
352 | + #plpy.info("new.id == %s" % new["id"]) |
353 | + #plpy.info("new.person == %s" % new["person"]) |
354 | + #plpy.info("new.status == %s" % new["status"]) |
355 | + |
356 | + # Short circuit if neither person nor status has changed |
357 | + if old["person"] == new["person"] and old["status"] == new["status"]: |
358 | + return |
359 | + |
360 | + # Short circuit if we are not mucking around with preferred email |
361 | + # addresses |
362 | + if old["status"] != PREF and new["status"] != PREF: |
363 | + return |
364 | + |
365 | + # Note that we have a constraint ensuring that there is only one |
366 | + # status == PREF email address per person at any point in time. |
367 | + # This simplifies our logic, as we know that if old.status == PREF, |
368 | + # old.person does not have any other preferred email addresses. |
369 | + # Also if new.status == PREF, we know new.person previously did not |
370 | + # have a preferred email address. |
371 | + |
372 | + if old["person"] != new["person"]: |
373 | + if old["status"] == PREF and not is_team(old["person"]): |
374 | + # old.person is no longer valid, unless they are a team |
375 | + plpy.execute(SD["delete_plan"], [old["person"]]) |
376 | + if new["status"] == PREF and not is_team(new["person"]): |
377 | + # new["person"] is now valid, or unchanged if they are a team |
378 | + plpy.execute(SD["insert_plan"], [new["person"]]) |
379 | + |
380 | + elif old["status"] == PREF and not is_team(old["person"]): |
381 | + # No longer valid, or unchanged if they are a team |
382 | + plpy.execute(SD["delete_plan"], [old["person"]]) |
383 | + |
384 | + elif new["status"] == PREF and not is_team(new["person"]): |
385 | + # May now be valid, or unchanged if they are a team. |
386 | + plpy.execute(SD["maybe_insert_plan"], [new["person"]]) |
387 | +$_$; |
388 | + |
389 | +CREATE OR REPLACE FUNCTION mv_validpersonorteamcache_person() RETURNS trigger |
390 | + LANGUAGE plpython3u SECURITY DEFINER |
391 | + AS $_$ |
392 | + # This trigger function could be simplified by simply issuing |
393 | + # one DELETE followed by one INSERT statement. However, we want to minimize |
394 | + # expensive writes so we use this more complex logic. |
395 | + PREF = 4 # Constant indicating preferred email address |
396 | + |
397 | + if "delete_plan" not in SD: |
398 | + param_types = ["int4"] |
399 | + |
400 | + SD["delete_plan"] = plpy.prepare(""" |
401 | + DELETE FROM ValidPersonOrTeamCache WHERE id = $1 |
402 | + """, param_types) |
403 | + |
404 | + SD["maybe_insert_plan"] = plpy.prepare(""" |
405 | + INSERT INTO ValidPersonOrTeamCache (id) |
406 | + SELECT Person.id |
407 | + FROM Person |
408 | + LEFT OUTER JOIN EmailAddress |
409 | + ON Person.id = EmailAddress.person AND status = %(PREF)d |
410 | + LEFT OUTER JOIN ValidPersonOrTeamCache |
411 | + ON Person.id = ValidPersonOrTeamCache.id |
412 | + WHERE Person.id = $1 |
413 | + AND ValidPersonOrTeamCache.id IS NULL |
414 | + AND merged IS NULL |
415 | + AND (teamowner IS NOT NULL OR EmailAddress.id IS NOT NULL) |
416 | + """ % vars(), param_types) |
417 | + |
418 | + new = TD["new"] |
419 | + old = TD["old"] |
420 | + |
421 | + # We should always have new, as this is not a DELETE trigger |
422 | + assert new is not None, 'New is None' |
423 | + |
424 | + person_id = new["id"] |
425 | + query_params = [person_id] # All the same |
426 | + |
427 | + # Short circuit if this is a new person (not team), as it cannot |
428 | + # be valid until a status == 4 EmailAddress entry has been created |
429 | + # (unless it is a team, in which case it is valid on creation) |
430 | + if old is None: |
431 | + if new["teamowner"] is not None: |
432 | + plpy.execute(SD["maybe_insert_plan"], query_params) |
433 | + return |
434 | + |
435 | + # Short circuit if there are no relevant changes |
436 | + if (new["teamowner"] == old["teamowner"] |
437 | + and new["merged"] == old["merged"]): |
438 | + return |
439 | + |
440 | + # This function is only dealing with updates to the Person table. |
441 | + # This means we do not have to worry about EmailAddress changes here |
442 | + |
443 | + if (new["merged"] is not None or new["teamowner"] is None): |
444 | + plpy.execute(SD["delete_plan"], query_params) |
445 | + else: |
446 | + plpy.execute(SD["maybe_insert_plan"], query_params) |
447 | +$_$; |
448 | + |
449 | +CREATE OR REPLACE FUNCTION name_blacklist_match(text, integer) RETURNS integer |
450 | + LANGUAGE plpython3u STABLE STRICT SECURITY DEFINER |
451 | + SET search_path TO 'public' |
452 | + AS $_$ |
453 | + import re |
454 | + name = args[0] |
455 | + user_id = args[1] |
456 | + |
457 | + # Initialize shared storage, shared between invocations. |
458 | + if "regexp_select_plan" not in SD: |
459 | + |
460 | + # All the blacklist regexps except the ones we are an admin |
461 | + # for. These we do not check since they are not blacklisted to us. |
462 | + SD["regexp_select_plan"] = plpy.prepare(""" |
463 | + SELECT id, regexp FROM NameBlacklist |
464 | + WHERE admin IS NULL OR admin NOT IN ( |
465 | + SELECT team FROM TeamParticipation |
466 | + WHERE person = $1) |
467 | + ORDER BY id |
468 | + """, ["integer"]) |
469 | + |
470 | + # Storage for compiled regexps |
471 | + SD["compiled"] = {} |
472 | + |
473 | + # admins is a celebrity and its id is immutable. |
474 | + admins_id = plpy.execute( |
475 | + "SELECT id FROM Person WHERE name='admins'")[0]["id"] |
476 | + |
477 | + SD["admin_select_plan"] = plpy.prepare(""" |
478 | + SELECT TRUE FROM TeamParticipation |
479 | + WHERE |
480 | + TeamParticipation.team = %d |
481 | + AND TeamParticipation.person = $1 |
482 | + LIMIT 1 |
483 | + """ % admins_id, ["integer"]) |
484 | + |
485 | + # All the blacklist regexps except those that have an admin because |
486 | + # members of ~admin can use any name that any other admin can use. |
487 | + SD["admin_regexp_select_plan"] = plpy.prepare(""" |
488 | + SELECT id, regexp FROM NameBlacklist |
489 | + WHERE admin IS NULL |
490 | + ORDER BY id |
491 | + """, ["integer"]) |
492 | + |
493 | + |
494 | + compiled = SD["compiled"] |
495 | + |
496 | + # Names are never blacklisted for Lauchpad admins. |
497 | + if user_id is not None and plpy.execute( |
498 | + SD["admin_select_plan"], [user_id]).nrows() > 0: |
499 | + blacklist_plan = "admin_regexp_select_plan" |
500 | + else: |
501 | + blacklist_plan = "regexp_select_plan" |
502 | + |
503 | + for row in plpy.execute(SD[blacklist_plan], [user_id]): |
504 | + regexp_id = row["id"] |
505 | + regexp_txt = row["regexp"] |
506 | + if (compiled.get(regexp_id) is None |
507 | + or compiled[regexp_id][0] != regexp_txt): |
508 | + regexp = re.compile(regexp_txt, re.IGNORECASE | re.VERBOSE) |
509 | + compiled[regexp_id] = (regexp_txt, regexp) |
510 | + else: |
511 | + regexp = compiled[regexp_id][1] |
512 | + if regexp.search(name) is not None: |
513 | + return regexp_id |
514 | + return None |
515 | +$_$; |
516 | + |
517 | +CREATE OR REPLACE FUNCTION person_sort_key(displayname text, name text) RETURNS text |
518 | + LANGUAGE plpython3u IMMUTABLE STRICT |
519 | + AS $$ |
520 | + # NB: If this implementation is changed, the person_sort_idx needs to be |
521 | + # rebuilt along with any other indexes using it. |
522 | + import re |
523 | + |
524 | + try: |
525 | + strip_re = SD["strip_re"] |
526 | + except KeyError: |
527 | + strip_re = re.compile("(?:[^\w\s]|[\d_])") |
528 | + SD["strip_re"] = strip_re |
529 | + |
530 | + displayname, name = args |
531 | + |
532 | + # Strip noise out of displayname. We do not have to bother with |
533 | + # name, as we know it is just plain ascii. |
534 | + displayname = strip_re.sub('', displayname.lower()) |
535 | + return "%s, %s" % (displayname.strip(), name) |
536 | +$$; |
537 | + |
538 | +CREATE OR REPLACE FUNCTION sane_version(text) RETURNS boolean |
539 | + LANGUAGE plpython3u IMMUTABLE STRICT |
540 | + AS $_$ |
541 | + import re |
542 | + if re.search("""^(?ix) |
543 | + [0-9a-z] |
544 | + ( [0-9a-z] | [0-9a-z.-]*[0-9a-z] )* |
545 | + $""", args[0]): |
546 | + return 1 |
547 | + return 0 |
548 | +$_$; |
549 | + |
550 | +CREATE OR REPLACE FUNCTION sha1(text) RETURNS character |
551 | + LANGUAGE plpython3u IMMUTABLE STRICT |
552 | + AS $$ |
553 | + import hashlib |
554 | + return hashlib.sha1(args[0].encode()).hexdigest() |
555 | +$$; |
556 | + |
557 | +CREATE OR REPLACE FUNCTION ulower(text) RETURNS text |
558 | + LANGUAGE plpython3u IMMUTABLE STRICT |
559 | + AS $$ |
560 | + return args[0].lower() |
561 | +$$; |
562 | + |
563 | +CREATE OR REPLACE FUNCTION update_database_stats() RETURNS void |
564 | + LANGUAGE plpython3u SECURITY DEFINER |
565 | + SET search_path TO 'public' |
566 | + AS $_$ |
567 | + import re |
568 | + import subprocess |
569 | + |
570 | + # Prune DatabaseTableStats and insert current data. |
571 | + # First, detect if the statistics have been reset. |
572 | + stats_reset = plpy.execute(""" |
573 | + SELECT * |
574 | + FROM |
575 | + pg_catalog.pg_stat_user_tables AS NowStat, |
576 | + DatabaseTableStats AS LastStat |
577 | + WHERE |
578 | + LastStat.date_created = ( |
579 | + SELECT max(date_created) FROM DatabaseTableStats) |
580 | + AND NowStat.schemaname = LastStat.schemaname |
581 | + AND NowStat.relname = LastStat.relname |
582 | + AND ( |
583 | + NowStat.seq_scan < LastStat.seq_scan |
584 | + OR NowStat.idx_scan < LastStat.idx_scan |
585 | + OR NowStat.n_tup_ins < LastStat.n_tup_ins |
586 | + OR NowStat.n_tup_upd < LastStat.n_tup_upd |
587 | + OR NowStat.n_tup_del < LastStat.n_tup_del |
588 | + OR NowStat.n_tup_hot_upd < LastStat.n_tup_hot_upd) |
589 | + LIMIT 1 |
590 | + """, 1).nrows() > 0 |
591 | + if stats_reset: |
592 | + # The database stats have been reset. We cannot calculate |
593 | + # deltas because we do not know when this happened. So we trash |
594 | + # our records as they are now useless to us. We could be more |
595 | + # sophisticated about this, but this should only happen |
596 | + # when an admin explicitly resets the statistics or if the |
597 | + # database is rebuilt. |
598 | + plpy.notice("Stats wraparound. Purging DatabaseTableStats") |
599 | + plpy.execute("DELETE FROM DatabaseTableStats") |
600 | + else: |
601 | + plpy.execute(""" |
602 | + DELETE FROM DatabaseTableStats |
603 | + WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC' |
604 | + - CAST('21 days' AS interval)); |
605 | + """) |
606 | + # Insert current data. |
607 | + plpy.execute(""" |
608 | + INSERT INTO DatabaseTableStats |
609 | + SELECT |
610 | + CURRENT_TIMESTAMP AT TIME ZONE 'UTC', |
611 | + schemaname, relname, seq_scan, seq_tup_read, |
612 | + coalesce(idx_scan, 0), coalesce(idx_tup_fetch, 0), |
613 | + n_tup_ins, n_tup_upd, n_tup_del, |
614 | + n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, |
615 | + last_autovacuum, last_analyze, last_autoanalyze |
616 | + FROM pg_catalog.pg_stat_user_tables; |
617 | + """) |
618 | + |
619 | + # Prune DatabaseCpuStats. Calculate CPU utilization information |
620 | + # and insert current data. |
621 | + plpy.execute(""" |
622 | + DELETE FROM DatabaseCpuStats |
623 | + WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC' |
624 | + - CAST('21 days' AS interval)); |
625 | + """) |
626 | + dbname = plpy.execute( |
627 | + "SELECT current_database() AS dbname", 1)[0]['dbname'] |
628 | + ps = subprocess.Popen( |
629 | + ["ps", "-C", "postgres", "--no-headers", "-o", "cp,args"], |
630 | + stdin=subprocess.PIPE, stdout=subprocess.PIPE, |
631 | + stderr=subprocess.STDOUT) |
632 | + stdout, stderr = ps.communicate() |
633 | + cpus = {} |
634 | + # We make the username match non-greedy so the trailing \d eats |
635 | + # trailing digits from the database username. This collapses |
636 | + # lpnet1, lpnet2 etc. into just lpnet. |
637 | + ps_re = re.compile( |
638 | + r"(?m)^\s*(\d+)\spostgres:\s(\w+?)\d*\s%s\s" % dbname) |
639 | + for ps_match in ps_re.finditer(stdout): |
640 | + cpu, username = ps_match.groups() |
641 | + cpus[username] = int(cpu) + cpus.setdefault(username, 0) |
642 | + cpu_ins = plpy.prepare( |
643 | + "INSERT INTO DatabaseCpuStats (username, cpu) VALUES ($1, $2)", |
644 | + ["text", "integer"]) |
645 | + for cpu_tuple in cpus.items(): |
646 | + plpy.execute(cpu_ins, cpu_tuple) |
647 | +$_$; |
648 | + |
649 | +CREATE OR REPLACE FUNCTION valid_absolute_url(text) RETURNS boolean |
650 | + LANGUAGE plpython3u IMMUTABLE STRICT |
651 | + AS $$ |
652 | + from urllib.parse import urlparse, uses_netloc |
653 | + # Extend list of schemes that specify netloc. |
654 | + if 'bzr' not in uses_netloc: |
655 | + uses_netloc.insert(0, 'bzr') |
656 | + uses_netloc.insert(0, 'bzr+ssh') |
657 | + uses_netloc.insert(0, 'ssh') # Mercurial |
658 | + (scheme, netloc, path, params, query, fragment) = urlparse(args[0]) |
659 | + return bool(scheme and netloc) |
660 | +$$; |
661 | + |
662 | +CREATE OR REPLACE FUNCTION valid_branch_name(text) RETURNS boolean |
663 | + LANGUAGE plpython3u IMMUTABLE STRICT |
664 | + AS $$ |
665 | + import re |
666 | + name = args[0] |
667 | + pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z" |
668 | + if re.match(pat, name): |
669 | + return 1 |
670 | + return 0 |
671 | +$$; |
672 | + |
673 | +CREATE OR REPLACE FUNCTION valid_cve(text) RETURNS boolean |
674 | + LANGUAGE plpython3u IMMUTABLE STRICT |
675 | + AS $_$ |
676 | + import re |
677 | + name = args[0] |
678 | + pat = r"^(19|20)\d{2}-\d{4,}$" |
679 | + if re.match(pat, name): |
680 | + return 1 |
681 | + return 0 |
682 | +$_$; |
683 | + |
684 | +CREATE OR REPLACE FUNCTION valid_debian_version(text) RETURNS boolean |
685 | + LANGUAGE plpython3u IMMUTABLE STRICT |
686 | + AS $_$ |
687 | + import re |
688 | + m = re.search("""^(?ix) |
689 | + ([0-9]+:)? |
690 | + ([0-9a-z][a-z0-9+:.~-]*?) |
691 | + (-[a-z0-9+.~]+)? |
692 | + $""", args[0]) |
693 | + if m is None: |
694 | + return 0 |
695 | + epoch, version, revision = m.groups() |
696 | + if not epoch: |
697 | + # Can''t contain : if no epoch |
698 | + if ":" in version: |
699 | + return 0 |
700 | + if not revision: |
701 | + # Can''t contain - if no revision |
702 | + if "-" in version: |
703 | + return 0 |
704 | + return 1 |
705 | +$_$; |
706 | + |
707 | +CREATE OR REPLACE FUNCTION valid_fingerprint(text) RETURNS boolean |
708 | + LANGUAGE plpython3u IMMUTABLE STRICT |
709 | + AS $$ |
710 | + import re |
711 | + if re.match(r"[\dA-F]{40}", args[0]) is not None: |
712 | + return 1 |
713 | + else: |
714 | + return 0 |
715 | +$$; |
716 | + |
717 | +CREATE OR REPLACE FUNCTION valid_git_repository_name(text) RETURNS boolean |
718 | + LANGUAGE plpython3u IMMUTABLE STRICT |
719 | + AS $$ |
720 | + import re |
721 | + name = args[0] |
722 | + pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z" |
723 | + if not name.endswith(".git") and re.match(pat, name): |
724 | + return 1 |
725 | + return 0 |
726 | +$$; |
727 | + |
728 | +CREATE OR REPLACE FUNCTION valid_keyid(text) RETURNS boolean |
729 | + LANGUAGE plpython3u IMMUTABLE STRICT |
730 | + AS $$ |
731 | + import re |
732 | + if re.match(r"[\dA-F]{8}", args[0]) is not None: |
733 | + return 1 |
734 | + else: |
735 | + return 0 |
736 | +$$; |
737 | + |
738 | +CREATE OR REPLACE FUNCTION valid_regexp(text) RETURNS boolean |
739 | + LANGUAGE plpython3u IMMUTABLE STRICT |
740 | + AS $$ |
741 | + import re |
742 | + try: |
743 | + re.compile(args[0]) |
744 | + except Exception: |
745 | + return False |
746 | + else: |
747 | + return True |
748 | +$$; |
749 | + |
750 | +CREATE OR REPLACE FUNCTION version_sort_key(version text) RETURNS text |
751 | + LANGUAGE plpython3u IMMUTABLE STRICT |
752 | + AS $$ |
753 | + # If this method is altered, then any functional indexes using it |
754 | + # need to be rebuilt. |
755 | + import re |
756 | + |
757 | + [version] = args |
758 | + |
759 | + def substitute_filled_numbers(match): |
760 | + # Prepend "~" so that version numbers will show up first |
761 | + # when sorted descending, i.e. [3, 2c, 2b, 1, c, b, a] instead |
762 | + # of [c, b, a, 3, 2c, 2b, 1]. "~" has the highest ASCII value |
763 | + # of visible ASCII characters. |
764 | + return '~' + match.group(0).zfill(5) |
765 | + |
766 | + return re.sub('\d+', substitute_filled_numbers, version) |
767 | +$$; |
768 | + |
769 | +DROP EXTENSION plpythonu; |
770 | + |
771 | +INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 46, 0); |