1
1
-- Copyright 2009 Canonical Ltd. This software is licensed under the
2
2
-- GNU Affero General Public License version 3 (see the file LICENSE).
4
CREATE OR REPLACE FUNCTION assert_patch_applied(
5
major integer, minor integer, patch integer) RETURNS boolean
6
LANGUAGE plpythonu STABLE AS
9
SELECT * FROM LaunchpadDatabaseRevision
10
WHERE major=%d AND minor=%d AND patch=%d
11
""" % (major, minor, patch))
14
'patch-%d-%02d-%d not applied.' % (major, minor, patch))
19
COMMENT ON FUNCTION assert_patch_applied(integer, integer, integer) IS
20
'Raise an exception if the given database patch has not been applied.';
23
CREATE OR REPLACE FUNCTION sha1(text) RETURNS char(40)
24
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
27
return hashlib.sha1(args[0]).hexdigest()
30
COMMENT ON FUNCTION sha1(text) IS
31
'Return the SHA1 one way cryptographic hash as a string of 40 hex digits';
34
CREATE OR REPLACE FUNCTION null_count(p_values anyarray) RETURNS integer
35
LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT AS
39
v_null_count integer := 0;
41
FOR v_index IN array_lower(p_values,1)..array_upper(p_values,1) LOOP
42
IF p_values[v_index] IS NULL THEN
43
v_null_count := v_null_count + 1;
50
COMMENT ON FUNCTION null_count(anyarray) IS
51
'Return the number of NULLs in the first row of the given array.';
54
CREATE OR REPLACE FUNCTION cursor_fetch(cur refcursor, n integer)
55
RETURNS SETOF record LANGUAGE plpgsql AS
61
FOR count IN 1..n LOOP
62
FETCH FORWARD FROM cur INTO r;
71
COMMENT ON FUNCTION cursor_fetch(refcursor, integer) IS
72
'Fetch the next n items from a cursor. Work around for not being able to use FETCH inside a SELECT statement.';
76
CREATE OR REPLACE FUNCTION replication_lag() RETURNS interval
77
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path TO public AS
82
SELECT INTO v_lag max(st_lag_time) FROM _sl.sl_status;
84
-- Slony-I not installed here - non-replicated setup.
86
WHEN invalid_schema_name THEN
88
WHEN undefined_table THEN
93
COMMENT ON FUNCTION replication_lag() IS
94
'Returns the worst lag time in our cluster, or NULL if not a replicated installation. Only returns meaningful results on the lpmain replication set master.';
97
CREATE OR REPLACE FUNCTION replication_lag(node_id integer) RETURNS interval
98
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path TO public AS
103
SELECT INTO v_lag st_lag_time FROM _sl.sl_status
104
WHERE st_origin = _sl.getlocalnodeid('_sl')
105
AND st_received = node_id;
107
-- Slony-I not installed here - non-replicated setup.
109
WHEN invalid_schema_name THEN
111
WHEN undefined_table THEN
116
COMMENT ON FUNCTION replication_lag(integer) IS
117
'Returns the lag time of the lpmain replication set to the given node, or NULL if not a replicated installation. The node id parameter can be obtained by calling getlocalnodeid() on the relevant database. This function only returns meaningful results on the lpmain replication set master.';
120
CREATE OR REPLACE FUNCTION update_replication_lag_cache() RETURNS boolean
121
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
124
DELETE FROM DatabaseReplicationLag;
125
INSERT INTO DatabaseReplicationLag (node, lag)
126
SELECT st_received, st_lag_time FROM _sl.sl_status
127
WHERE st_origin = _sl.getlocalnodeid('_sl');
129
-- Slony-I not installed here - non-replicated setup.
131
WHEN invalid_schema_name THEN
133
WHEN undefined_table THEN
138
COMMENT ON FUNCTION update_replication_lag_cache() IS
139
'Updates the DatabaseReplicationLag materialized view.';
141
CREATE OR REPLACE FUNCTION update_database_stats() RETURNS void
142
LANGUAGE plpythonu VOLATILE SECURITY DEFINER SET search_path TO public AS
147
# Prune DatabaseTableStats and insert current data.
148
# First, detect if the statistics have been reset.
149
stats_reset = plpy.execute("""
152
pg_catalog.pg_stat_user_tables AS NowStat,
153
DatabaseTableStats AS LastStat
155
LastStat.date_created = (
156
SELECT max(date_created) FROM DatabaseTableStats)
157
AND NowStat.schemaname = LastStat.schemaname
158
AND NowStat.relname = LastStat.relname
160
NowStat.seq_scan < LastStat.seq_scan
161
OR NowStat.idx_scan < LastStat.idx_scan
162
OR NowStat.n_tup_ins < LastStat.n_tup_ins
163
OR NowStat.n_tup_upd < LastStat.n_tup_upd
164
OR NowStat.n_tup_del < LastStat.n_tup_del
165
OR NowStat.n_tup_hot_upd < LastStat.n_tup_hot_upd)
169
# The database stats have been reset. We cannot calculate
170
# deltas because we do not know when this happened. So we trash
171
# our records as they are now useless to us. We could be more
172
# sophisticated about this, but this should only happen
173
# when an admin explicitly resets the statistics or if the
174
# database is rebuilt.
175
plpy.notice("Stats wraparound. Purging DatabaseTableStats")
176
plpy.execute("DELETE FROM DatabaseTableStats")
179
DELETE FROM DatabaseTableStats
180
WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
181
- CAST('21 days' AS interval));
183
# Insert current data.
185
INSERT INTO DatabaseTableStats
187
CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
188
schemaname, relname, seq_scan, seq_tup_read,
189
coalesce(idx_scan, 0), coalesce(idx_tup_fetch, 0),
190
n_tup_ins, n_tup_upd, n_tup_del,
191
n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum,
192
last_autovacuum, last_analyze, last_autoanalyze
193
FROM pg_catalog.pg_stat_user_tables;
196
# Prune DatabaseCpuStats. Calculate CPU utilization information
197
# and insert current data.
199
DELETE FROM DatabaseCpuStats
200
WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
201
- CAST('21 days' AS interval));
203
dbname = plpy.execute(
204
"SELECT current_database() AS dbname", 1)[0]['dbname']
205
ps = subprocess.Popen(
206
["ps", "-C", "postgres", "--no-headers", "-o", "cp,args"],
207
stdin=subprocess.PIPE, stdout=subprocess.PIPE,
208
stderr=subprocess.STDOUT)
209
stdout, stderr = ps.communicate()
211
# We make the username match non-greedy so the trailing \d eats
212
# trailing digits from the database username. This collapses
213
# lpnet1, lpnet2 etc. into just lpnet.
215
r"(?m)^\s*(\d+)\spostgres:\s(\w+?)\d*\s%s\s" % dbname)
216
for ps_match in ps_re.finditer(stdout):
217
cpu, username = ps_match.groups()
218
cpus[username] = int(cpu) + cpus.setdefault(username, 0)
219
cpu_ins = plpy.prepare(
220
"INSERT INTO DatabaseCpuStats (username, cpu) VALUES ($1, $2)",
222
for cpu_tuple in cpus.items():
223
plpy.execute(cpu_ins, cpu_tuple)
226
COMMENT ON FUNCTION update_database_stats() IS
227
'Copies rows from pg_stat_user_tables into DatabaseTableStats. We use a stored procedure because it is problematic for us to grant permissions on objects in the pg_catalog schema.';
229
SET check_function_bodies=false; -- Handle forward references
230
CREATE OR REPLACE FUNCTION update_database_disk_utilization() RETURNS void
231
LANGUAGE sql VOLATILE SECURITY DEFINER SET search_path TO public AS
233
INSERT INTO DatabaseDiskUtilization
235
CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
237
sub_namespace, sub_name,
239
(namespace || '.' || name || COALESCE(
240
'/' || sub_namespace || '.' || sub_name, '')) AS sort,
244
(stat).tuple_percent,
245
(stat).dead_tuple_count,
246
(stat).dead_tuple_len,
247
(stat).dead_tuple_percent,
252
pg_namespace.nspname AS namespace,
253
pg_class.relname AS name,
254
NULL AS sub_namespace,
256
pg_class.relkind AS kind,
257
pgstattuple(pg_class.oid) AS stat
258
FROM pg_class, pg_namespace
260
pg_class.relnamespace = pg_namespace.oid
261
AND pg_class.relkind = 'r'
262
AND pg_table_is_visible(pg_class.oid)
267
pg_namespace_table.nspname AS namespace,
268
pg_class_table.relname AS name,
269
pg_namespace_index.nspname AS sub_namespace,
270
pg_class_index.relname AS sub_name,
271
pg_class_index.relkind AS kind,
272
pgstattuple(pg_class_index.oid) AS stat
274
pg_namespace AS pg_namespace_table,
275
pg_namespace AS pg_namespace_index,
276
pg_class AS pg_class_table,
277
pg_class AS pg_class_index,
280
pg_class_index.relkind = 'i'
281
AND pg_table_is_visible(pg_class_table.oid)
282
AND pg_class_index.relnamespace = pg_namespace_index.oid
283
AND pg_class_table.relnamespace = pg_namespace_table.oid
284
AND pg_index.indexrelid = pg_class_index.oid
285
AND pg_index.indrelid = pg_class_table.oid
291
pg_namespace_table.nspname AS namespace,
292
pg_class_table.relname AS name,
293
pg_namespace_toast.nspname AS sub_namespace,
294
pg_class_toast.relname AS sub_name,
295
pg_class_toast.relkind AS kind,
296
pgstattuple(pg_class_toast.oid) AS stat
298
pg_namespace AS pg_namespace_table,
299
pg_namespace AS pg_namespace_toast,
300
pg_class AS pg_class_table,
301
pg_class AS pg_class_toast
303
pg_class_toast.relnamespace = pg_namespace_toast.oid
304
AND pg_table_is_visible(pg_class_table.oid)
305
AND pg_class_table.relnamespace = pg_namespace_table.oid
306
AND pg_class_toast.oid = pg_class_table.reltoastrelid
312
pg_namespace_table.nspname AS namespace,
313
pg_class_table.relname AS name,
314
pg_namespace_index.nspname AS sub_namespace,
315
pg_class_index.relname AS sub_name,
316
pg_class_index.relkind AS kind,
317
pgstattuple(pg_class_index.oid) AS stat
319
pg_namespace AS pg_namespace_table,
320
pg_namespace AS pg_namespace_index,
321
pg_class AS pg_class_table,
322
pg_class AS pg_class_index,
323
pg_class AS pg_class_toast
325
pg_class_table.relnamespace = pg_namespace_table.oid
326
AND pg_table_is_visible(pg_class_table.oid)
327
AND pg_class_index.relnamespace = pg_namespace_index.oid
328
AND pg_class_table.reltoastrelid = pg_class_toast.oid
329
AND pg_class_index.oid = pg_class_toast.reltoastidxid
332
SET check_function_bodies=true; -- Handle forward references
334
CREATE OR REPLACE FUNCTION getlocalnodeid() RETURNS integer
335
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path TO public AS
340
SELECT INTO v_node_id _sl.getlocalnodeid('_sl');
343
WHEN invalid_schema_name THEN
348
COMMENT ON FUNCTION getlocalnodeid() IS
349
'Return the replication node id for this node, or NULL if not a replicated installation.';
352
CREATE OR REPLACE FUNCTION activity()
353
RETURNS SETOF pg_catalog.pg_stat_activity
354
LANGUAGE SQL VOLATILE SECURITY DEFINER SET search_path TO public AS
357
datid, datname, procpid, usesysid, usename,
359
WHEN current_query LIKE '<IDLE>%'
360
OR current_query LIKE 'autovacuum:%'
364
END AS current_query,
365
waiting, xact_start, query_start,
366
backend_start, client_addr, client_port
367
FROM pg_catalog.pg_stat_activity;
370
COMMENT ON FUNCTION activity() IS
371
'SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.';
374
/* This is created as a function so the same definition can be used with
377
CREATE OR REPLACE FUNCTION valid_name(text) RETURNS boolean
378
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
382
pat = r"^[a-z0-9][a-z0-9\+\.\-]*\Z"
383
if re.match(pat, name):
388
COMMENT ON FUNCTION valid_name(text)
391
Names must contain only lowercase letters, numbers, ., & -. They
392
must start with an alphanumeric. They are ASCII only. Names are useful
393
for mneumonic identifiers such as nicknames and as URL components.
394
This specification is the same as the Debian product naming policy.
396
Note that a valid name might be all integers, so there is a possible
397
namespace conflict if URL traversal is possible by name as well as id.';
400
CREATE OR REPLACE FUNCTION valid_branch_name(text) RETURNS boolean
401
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
405
pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
406
if re.match(pat, name):
411
COMMENT ON FUNCTION valid_branch_name(text)
412
IS 'validate a branch name.
414
As per valid_name, except we allow uppercase and @';
417
CREATE OR REPLACE FUNCTION valid_bug_name(text) RETURNS boolean
418
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
422
pat = r"^[a-z][a-z0-9+\.\-]+$"
423
if re.match(pat, name):
428
COMMENT ON FUNCTION valid_bug_name(text) IS 'validate a bug name
430
As per valid_name, except numeric-only names are not allowed (including
431
names that look like floats).';
434
CREATE OR REPLACE FUNCTION valid_debian_version(text) RETURNS boolean
435
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
438
m = re.search("""^(?ix)
440
([0-9a-z][a-z0-9+:.~-]*?)
445
epoch, version, revision = m.groups()
447
# Can''t contain : if no epoch
451
# Can''t contain - if no revision
457
COMMENT ON FUNCTION valid_debian_version(text) IS 'validate a version number as per Debian Policy';
460
CREATE OR REPLACE FUNCTION sane_version(text) RETURNS boolean
461
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
464
if re.search("""^(?ix)
466
( [0-9a-z] | [0-9a-z.-]*[0-9a-z] )*
472
COMMENT ON FUNCTION sane_version(text) IS 'A sane version number for use by ProductRelease and DistroRelease. We may make it less strict if required, but it would be nice if we can enforce simple version strings because we use them in URLs';
475
CREATE OR REPLACE FUNCTION valid_cve(text) RETURNS boolean
476
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
480
pat = r"^(19|20)\d{2}-\d{4}$"
481
if re.match(pat, name):
486
COMMENT ON FUNCTION valid_cve(text) IS 'validate a common vulnerability number as defined on www.cve.mitre.org, minus the CAN- or CVE- prefix.';
489
CREATE OR REPLACE FUNCTION valid_absolute_url(text) RETURNS boolean
490
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
492
from urlparse import urlparse, uses_netloc
493
# Extend list of schemes that specify netloc. We can drop sftp
494
# with Python 2.5 in the DB.
495
if 'git' not in uses_netloc:
496
uses_netloc.insert(0, 'sftp')
497
uses_netloc.insert(0, 'bzr')
498
uses_netloc.insert(0, 'bzr+ssh')
499
uses_netloc.insert(0, 'ssh') # Mercurial
500
uses_netloc.insert(0, 'git')
501
(scheme, netloc, path, params, query, fragment) = urlparse(args[0])
502
return bool(scheme and netloc)
505
COMMENT ON FUNCTION valid_absolute_url(text) IS 'Ensure the given test is a valid absolute URL, containing both protocol and network location';
508
CREATE OR REPLACE FUNCTION valid_fingerprint(text) RETURNS boolean
509
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
512
if re.match(r"[\dA-F]{40}", args[0]) is not None:
518
COMMENT ON FUNCTION valid_fingerprint(text) IS 'Returns true if passed a valid GPG fingerprint. Valid GPG fingerprints are a 40 character long hexadecimal number in uppercase.';
521
CREATE OR REPLACE FUNCTION valid_keyid(text) RETURNS boolean
522
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
525
if re.match(r"[\dA-F]{8}", args[0]) is not None:
531
COMMENT ON FUNCTION valid_keyid(text) IS 'Returns true if passed a valid GPG keyid. Valid GPG keyids are an 8 character long hexadecimal number in uppercase (in reality, they are 16 characters long but we are using the ''common'' definition.';
534
CREATE OR REPLACE FUNCTION valid_regexp(text) RETURNS boolean
535
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
546
COMMENT ON FUNCTION valid_regexp(text)
547
IS 'Returns true if the input can be compiled as a regular expression.';
550
CREATE OR REPLACE FUNCTION you_are_your_own_member() RETURNS trigger
554
INSERT INTO TeamParticipation (person, team)
555
VALUES (NEW.id, NEW.id);
560
COMMENT ON FUNCTION you_are_your_own_member() IS
561
'Trigger function to ensure that every row added to the Person table gets a corresponding row in the TeamParticipation table, as per the TeamParticipationUsage page on the Launchpad wiki';
563
SET check_function_bodies=false; -- Handle forward references
565
CREATE OR REPLACE FUNCTION is_team(integer) returns boolean
566
LANGUAGE sql STABLE RETURNS NULL ON NULL INPUT AS
568
SELECT count(*)>0 FROM Person WHERE id=$1 AND teamowner IS NOT NULL;
571
COMMENT ON FUNCTION is_team(integer) IS
572
'True if the given id identifies a team in the Person table';
575
CREATE OR REPLACE FUNCTION is_team(text) returns boolean
576
LANGUAGE sql STABLE RETURNS NULL ON NULL INPUT AS
578
SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NOT NULL;
581
COMMENT ON FUNCTION is_team(text) IS
582
'True if the given name identifies a team in the Person table';
585
CREATE OR REPLACE FUNCTION is_person(text) returns boolean
586
LANGUAGE sql STABLE RETURNS NULL ON NULL INPUT AS
588
SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NULL;
591
COMMENT ON FUNCTION is_person(text) IS
592
'True if the given name identifies a person in the Person table';
594
SET check_function_bodies=true;
597
CREATE OR REPLACE FUNCTION is_printable_ascii(text) RETURNS boolean
598
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
602
text = args[0].decode("ASCII")
605
if re.search(r"^[%s]*$" % re.escape(string.printable), text) is None:
610
COMMENT ON FUNCTION is_printable_ascii(text) IS
611
'True if the string is pure printable US-ASCII';
614
CREATE OR REPLACE FUNCTION mv_pillarname_distribution() RETURNS TRIGGER
615
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
618
IF TG_OP = 'INSERT' THEN
619
INSERT INTO PillarName (name, distribution)
620
VALUES (NEW.name, NEW.id);
621
ELSIF NEW.name != OLD.name THEN
622
UPDATE PillarName SET name=NEW.name WHERE distribution=NEW.id;
624
RETURN NULL; -- Ignored - this is an AFTER trigger
628
COMMENT ON FUNCTION mv_pillarname_distribution() IS
629
'Trigger maintaining the PillarName table';
632
CREATE OR REPLACE FUNCTION mv_pillarname_product() RETURNS TRIGGER
633
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
636
IF TG_OP = 'INSERT' THEN
637
INSERT INTO PillarName (name, product, active)
638
VALUES (NEW.name, NEW.id, NEW.active);
639
ELSIF NEW.name != OLD.name OR NEW.active != OLD.active THEN
640
UPDATE PillarName SET name=NEW.name, active=NEW.active
641
WHERE product=NEW.id;
643
RETURN NULL; -- Ignored - this is an AFTER trigger
647
COMMENT ON FUNCTION mv_pillarname_product() IS
648
'Trigger maintaining the PillarName table';
651
CREATE OR REPLACE FUNCTION mv_pillarname_project() RETURNS TRIGGER
652
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
655
IF TG_OP = 'INSERT' THEN
656
INSERT INTO PillarName (name, project, active)
657
VALUES (NEW.name, NEW.id, NEW.active);
658
ELSIF NEW.name != OLD.name or NEW.active != OLD.active THEN
659
UPDATE PillarName SET name=NEW.name, active=NEW.active
660
WHERE project=NEW.id;
662
RETURN NULL; -- Ignored - this is an AFTER trigger
666
COMMENT ON FUNCTION mv_pillarname_project() IS
667
'Trigger maintaining the PillarName table';
670
CREATE OR REPLACE FUNCTION mv_pofiletranslator_translationmessage()
671
RETURNS TRIGGER VOLATILE SECURITY DEFINER SET search_path TO public AS
676
-- If we are deleting a row, we need to remove the existing
677
-- POFileTranslator row and reinsert the historical data if it exists.
678
-- We also treat UPDATEs that change the key (submitter) the same
679
-- as deletes. UPDATEs that don't change these columns are treated like
681
IF TG_OP = 'INSERT' THEN
682
v_trash_old := FALSE;
683
ELSIF TG_OP = 'DELETE' THEN
687
OLD.submitter != NEW.submitter
692
-- Was this somebody's most-recently-changed message?
693
-- If so, delete the entry for that change.
694
DELETE FROM POFileTranslator
695
WHERE latest_message = OLD.id;
697
-- We deleted the entry for somebody's latest contribution.
698
-- Find that person's latest remaining contribution and
699
-- create a new record for that.
700
INSERT INTO POFileTranslator (
701
person, pofile, latest_message, date_last_touched
703
SELECT DISTINCT ON (person, pofile.id)
704
new_latest_message.submitter AS person,
706
new_latest_message.id,
707
greatest(new_latest_message.date_created,
708
new_latest_message.date_reviewed)
710
JOIN TranslationTemplateItem AS old_template_item
711
ON OLD.potmsgset = old_template_item.potmsgset AND
712
old_template_item.potemplate = pofile.potemplate AND
713
pofile.language = OLD.language
714
JOIN TranslationTemplateItem AS new_template_item
715
ON (old_template_item.potemplate =
716
new_template_item.potemplate)
717
JOIN TranslationMessage AS new_latest_message
718
ON new_latest_message.potmsgset =
719
new_template_item.potmsgset AND
720
new_latest_message.language = OLD.language
721
LEFT OUTER JOIN POfileTranslator AS ExistingEntry
722
ON ExistingEntry.person = OLD.submitter AND
723
ExistingEntry.pofile = POFile.id
725
new_latest_message.submitter = OLD.submitter AND
726
ExistingEntry IS NULL
727
ORDER BY new_latest_message.submitter, pofile.id,
728
new_latest_message.date_created DESC,
729
new_latest_message.id DESC;
732
-- No NEW with DELETE, so we can short circuit and leave.
733
IF TG_OP = 'DELETE' THEN
734
RETURN NULL; -- Ignored because this is an AFTER trigger
738
-- Standard 'upsert' loop to avoid race conditions.
740
UPDATE POFileTranslator
742
date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
743
latest_message = NEW.id
744
FROM POFile, TranslationTemplateItem
745
WHERE person = NEW.submitter AND
746
TranslationTemplateItem.potmsgset=NEW.potmsgset AND
747
TranslationTemplateItem.potemplate=pofile.potemplate AND
748
pofile.language=NEW.language AND
749
POFileTranslator.pofile = pofile.id;
751
RETURN NULL; -- Return value ignored as this is an AFTER trigger
755
INSERT INTO POFileTranslator (person, pofile, latest_message)
756
SELECT DISTINCT ON (NEW.submitter, pofile.id)
757
NEW.submitter, pofile.id, NEW.id
758
FROM TranslationTemplateItem
760
ON pofile.language = NEW.language AND
761
pofile.potemplate = translationtemplateitem.potemplate
763
TranslationTemplateItem.potmsgset = NEW.potmsgset;
764
RETURN NULL; -- Return value ignored as this is an AFTER trigger
765
EXCEPTION WHEN unique_violation THEN
772
COMMENT ON FUNCTION mv_pofiletranslator_translationmessage() IS
773
'Trigger maintaining the POFileTranslator table';
775
CREATE OR REPLACE FUNCTION person_sort_key(displayname text, name text)
777
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
779
# NB: If this implementation is changed, the person_sort_idx needs to be
780
# rebuilt along with any other indexes using it.
784
strip_re = SD["strip_re"]
786
strip_re = re.compile("(?:[^\w\s]|[\d_])", re.U)
787
SD["strip_re"] = strip_re
789
displayname, name = args
791
# Strip noise out of displayname. We do not have to bother with
792
# name, as we know it is just plain ascii.
793
displayname = strip_re.sub('', displayname.decode('UTF-8').lower())
794
return ("%s, %s" % (displayname.strip(), name)).encode('UTF-8')
797
COMMENT ON FUNCTION person_sort_key(text,text) IS 'Return a string suitable for sorting people on, generated by stripping noise out of displayname and concatenating name';
800
CREATE OR REPLACE FUNCTION debversion_sort_key(version text) RETURNS text
801
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
803
# If this method is altered, then any functional indexes using it
804
# need to be rebuilt.
807
VERRE = re.compile("(?:([0-9]+):)?(.+?)(?:-([^-]+))?$")
809
MAP = "0123456789ABCDEFGHIJKLMNOPQRSTUV"
811
epoch, version, release = VERRE.match(args[0]).groups()
813
for part, part_weight in ((epoch, 3000), (version, 2000), (release, 1000)):
821
key.append(part_weight)
823
while i != l and part[i].isdigit(): i += 1
824
key.append(part_weight+int(part[j:i] or "0"))
829
key.append(part_weight+ord(c))
832
key.append(part_weight+256+ord(c))
834
if not key or key[-1] != part_weight:
835
key.append(part_weight)
836
key.append(part_weight)
839
# Encode our key and return it
848
element.insert(0, MAP[value & 0x1F])
850
element_len = len(element)
852
element.insert(0, "0"*(3-element_len))
853
elif element_len == 3:
855
elif element_len < 35:
856
element.insert(0, MAP[element_len-4])
857
element.insert(0, "X")
858
elif element_len < 1027:
859
element.insert(0, MAP[(element_len-4) & 0x1F])
860
element.insert(0, MAP[(element_len-4) & 0x3E0])
861
element.insert(0, "Y")
863
raise ValueError("Number too large")
864
result.extend(element)
865
return "".join(result)
868
COMMENT ON FUNCTION debversion_sort_key(text) IS 'Return a string suitable for sorting debian version strings on';
871
CREATE OR REPLACE FUNCTION name_blacklist_match(text, integer) RETURNS int4
872
LANGUAGE plpythonu STABLE RETURNS NULL ON NULL INPUT
873
SECURITY DEFINER SET search_path TO public AS
876
name = args[0].decode("UTF-8")
879
# Initialize shared storage, shared between invocations.
880
if not SD.has_key("regexp_select_plan"):
882
# All the blacklist regexps except the ones we are an admin
883
# for. These we do not check since they are not blacklisted to us.
884
SD["regexp_select_plan"] = plpy.prepare("""
885
SELECT id, regexp FROM NameBlacklist
886
WHERE admin IS NULL OR admin NOT IN (
887
SELECT team FROM TeamParticipation
892
# Storage for compiled regexps
895
# admins is a celebrity and its id is immutable.
896
admins_id = plpy.execute(
897
"SELECT id FROM Person WHERE name='admins'")[0]["id"]
899
SD["admin_select_plan"] = plpy.prepare("""
900
SELECT TRUE FROM TeamParticipation
902
TeamParticipation.team = %d
903
AND TeamParticipation.person = $1
905
""" % admins_id, ["integer"])
907
# All the blacklist regexps except those that have an admin because
908
# members of ~admin can use any name that any other admin can use.
909
SD["admin_regexp_select_plan"] = plpy.prepare("""
910
SELECT id, regexp FROM NameBlacklist
916
compiled = SD["compiled"]
918
# Names are never blacklisted for Lauchpad admins.
919
if user_id is not None and plpy.execute(
920
SD["admin_select_plan"], [user_id]).nrows() > 0:
921
blacklist_plan = "admin_regexp_select_plan"
923
blacklist_plan = "regexp_select_plan"
925
for row in plpy.execute(SD[blacklist_plan], [user_id]):
926
regexp_id = row["id"]
927
regexp_txt = row["regexp"]
928
if (compiled.get(regexp_id) is None
929
or compiled[regexp_id][0] != regexp_txt):
931
regexp_txt, re.IGNORECASE | re.UNICODE | re.VERBOSE
933
compiled[regexp_id] = (regexp_txt, regexp)
935
regexp = compiled[regexp_id][1]
936
if regexp.search(name) is not None:
941
COMMENT ON FUNCTION name_blacklist_match(text, integer) IS 'Return the id of the row in the NameBlacklist table that matches the given name, or NULL if no regexps in the NameBlacklist table match.';
944
CREATE OR REPLACE FUNCTION is_blacklisted_name(text, integer)
945
RETURNS boolean LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT
946
SECURITY DEFINER SET search_path TO public AS
948
SELECT COALESCE(name_blacklist_match($1, $2)::boolean, FALSE);
951
COMMENT ON FUNCTION is_blacklisted_name(text, integer) IS 'Return TRUE if any regular expressions stored in the NameBlacklist table match the givenname, otherwise return FALSE.';
954
CREATE OR REPLACE FUNCTION set_shipit_normalized_address() RETURNS trigger
958
NEW.normalized_address =
960
-- Strip off everything that's not alphanumeric
963
coalesce(NEW.addressline1, '') || ' ' ||
964
coalesce(NEW.addressline2, '') || ' ' ||
965
coalesce(NEW.city, ''),
966
'[^a-zA-Z0-9]+', '', 'g'));
971
COMMENT ON FUNCTION set_shipit_normalized_address() IS 'Store a normalized concatenation of the request''s address into the normalized_address column.';
973
CREATE OR REPLACE FUNCTION generate_openid_identifier() RETURNS text
974
LANGUAGE plpythonu VOLATILE AS
976
from random import choice
978
# Non display confusing characters.
979
chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
981
# Character length of tokens. Can be increased, decreased or even made
982
# random - Launchpad does not care. 7 means it takes 40 bytes to store
983
# a null-terminated Launchpad identity URL on the current domain name.
987
while loop_count < 20000:
988
# Generate a random openid_identifier
989
oid = ''.join(choice(chars) for count in range(length))
991
# Check if the oid is already in the db, although this is pretty
993
rv = plpy.execute("""
994
SELECT COUNT(*) AS num FROM Account WHERE openid_identifier = '%s'
996
if rv[0]['num'] == 0:
1001
'Clash generating unique openid_identifier. '
1002
'Increase length if you see this warning too much.')
1004
"Unable to generate unique openid_identifier. "
1005
"Need to increase length of tokens.")
1011
CREATE OR REPLACE FUNCTION set_openid_identifier() RETURNS trigger
1012
LANGUAGE plpythonu AS
1014
# If someone is trying to explicitly set the openid_identifier, let them.
1015
# This also causes openid_identifiers to be left alone if this is an
1017
if TD['new']['openid_identifier'] is not None:
1020
from random import choice
1022
# Non display confusing characters
1023
chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
1025
# character length of tokens. Can be increased, decreased or even made
1026
# random - Launchpad does not care. 7 means it takes 40 bytes to store
1027
# a null-terminated Launchpad identity URL on the current domain name.
1031
while loop_count < 20000:
1032
# Generate a random openid_identifier
1033
oid = ''.join(choice(chars) for count in range(length))
1035
# Check if the oid is already in the db, although this is pretty
1037
rv = plpy.execute("""
1038
SELECT COUNT(*) AS num FROM Person WHERE openid_identifier = '%s'
1040
if rv[0]['num'] == 0:
1041
TD['new']['openid_identifier'] = oid
1046
'Clash generating unique openid_identifier. '
1047
'Increase length if you see this warning too much.')
1049
"Unable to generate unique openid_identifier. "
1050
"Need to increase length of tokens.")
1054
CREATE OR REPLACE FUNCTION set_bug_date_last_message() RETURNS TRIGGER
1055
LANGUAGE plpgsql VOLATILE SECURITY DEFINER SET search_path TO public AS
1058
IF TG_OP = 'INSERT' THEN
1060
SET date_last_message = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
1061
WHERE Bug.id = NEW.bug;
1064
SET date_last_message = max_datecreated
1066
SELECT BugMessage.bug, max(Message.datecreated) AS max_datecreated
1067
FROM BugMessage, Message
1068
WHERE BugMessage.id <> OLD.id
1069
AND BugMessage.bug = OLD.bug
1070
AND BugMessage.message = Message.id
1071
GROUP BY BugMessage.bug
1073
WHERE Bug.id = MessageSummary.bug;
1075
RETURN NULL; -- Ignored - this is an AFTER trigger
1079
COMMENT ON FUNCTION set_bug_date_last_message() IS 'AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column';
1082
CREATE OR REPLACE FUNCTION set_bug_number_of_duplicates() RETURNS TRIGGER
1083
LANGUAGE plpgsql VOLATILE AS
1086
-- Short circuit on an update that doesn't change duplicateof
1087
IF TG_OP = 'UPDATE' THEN
1088
IF NEW.duplicateof = OLD.duplicateof THEN
1089
RETURN NULL; -- Ignored - this is an AFTER trigger
1093
-- For update or delete, possibly decrement a bug's dupe count
1094
IF TG_OP <> 'INSERT' THEN
1095
IF OLD.duplicateof IS NOT NULL THEN
1096
UPDATE Bug SET number_of_duplicates = number_of_duplicates - 1
1097
WHERE Bug.id = OLD.duplicateof;
1101
-- For update or insert, possibly increment a bug's dupe cout
1102
IF TG_OP <> 'DELETE' THEN
1103
IF NEW.duplicateof IS NOT NULL THEN
1104
UPDATE Bug SET number_of_duplicates = number_of_duplicates + 1
1105
WHERE Bug.id = NEW.duplicateof;
1109
RETURN NULL; -- Ignored - this is an AFTER trigger
1113
COMMENT ON FUNCTION set_bug_number_of_duplicates() IS
1114
'AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column';
1116
CREATE OR REPLACE FUNCTION set_bug_message_count() RETURNS TRIGGER
1120
IF TG_OP = 'UPDATE' THEN
1121
IF NEW.bug = OLD.bug THEN
1122
RETURN NULL; -- Ignored - this is an AFTER trigger.
1126
IF TG_OP <> 'DELETE' THEN
1127
UPDATE Bug SET message_count = message_count + 1
1128
WHERE Bug.id = NEW.bug;
1131
IF TG_OP <> 'INSERT' THEN
1132
UPDATE Bug SET message_count = message_count - 1
1133
WHERE Bug.id = OLD.bug;
1136
RETURN NULL; -- Ignored - this is an AFTER trigger.
1140
COMMENT ON FUNCTION set_bug_message_count() IS
1141
'AFTER UPDATE trigger on BugMessage maintaining the Bug.message_count column';
1144
CREATE OR REPLACE FUNCTION set_date_status_set() RETURNS TRIGGER
1148
IF OLD.status <> NEW.status THEN
1149
NEW.date_status_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
1155
COMMENT ON FUNCTION set_date_status_set() IS 'BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.';
1158
CREATE OR REPLACE FUNCTION ulower(text) RETURNS text
1159
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
1161
return args[0].decode('utf8').lower().encode('utf8')
1164
COMMENT ON FUNCTION ulower(text) IS
1165
'Return the lower case version of a UTF-8 encoded string.';
1168
CREATE OR REPLACE FUNCTION set_bug_users_affected_count() RETURNS TRIGGER
1172
IF TG_OP = 'INSERT' THEN
1173
IF NEW.affected = TRUE THEN
1175
SET users_affected_count = users_affected_count + 1
1176
WHERE Bug.id = NEW.bug;
1179
SET users_unaffected_count = users_unaffected_count + 1
1180
WHERE Bug.id = NEW.bug;
1184
IF TG_OP = 'DELETE' THEN
1185
IF OLD.affected = TRUE THEN
1187
SET users_affected_count = users_affected_count - 1
1188
WHERE Bug.id = OLD.bug;
1191
SET users_unaffected_count = users_unaffected_count - 1
1192
WHERE Bug.id = OLD.bug;
1196
IF TG_OP = 'UPDATE' THEN
1197
IF OLD.affected <> NEW.affected THEN
1198
IF NEW.affected THEN
1200
SET users_affected_count = users_affected_count + 1,
1201
users_unaffected_count = users_unaffected_count - 1
1202
WHERE Bug.id = OLD.bug;
1205
SET users_affected_count = users_affected_count - 1,
1206
users_unaffected_count = users_unaffected_count + 1
1207
WHERE Bug.id = OLD.bug;
1216
COMMENT ON FUNCTION set_bug_message_count() IS
1217
'AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column';
1220
CREATE OR REPLACE FUNCTION set_bugtask_date_milestone_set() RETURNS TRIGGER
1224
IF TG_OP = 'INSERT' THEN
1225
-- If the inserted row as a milestone set, set date_milestone_set.
1226
IF NEW.milestone IS NOT NULL THEN
1228
SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
1229
WHERE BugTask.id = NEW.id;
1233
IF TG_OP = 'UPDATE' THEN
1234
IF OLD.milestone IS NULL THEN
1235
-- If there was no milestone set, check if the new row has a
1236
-- milestone set and set date_milestone_set.
1237
IF NEW.milestone IS NOT NULL THEN
1239
SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
1240
WHERE BugTask.id = NEW.id;
1243
IF NEW.milestone IS NULL THEN
1244
-- If the milestone was unset, clear date_milestone_set.
1246
SET date_milestone_set = NULL
1247
WHERE BugTask.id = NEW.id;
1249
-- Update date_milestone_set if the bug task was
1250
-- targeted to another milestone.
1251
IF NEW.milestone != OLD.milestone THEN
1253
SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
1254
WHERE BugTask.id = NEW.id;
1261
RETURN NULL; -- Ignored - this is an AFTER trigger.
1265
COMMENT ON FUNCTION set_bugtask_date_milestone_set() IS
1266
'Update BugTask.date_milestone_set when BugTask.milestone is changed.';
1268
CREATE OR REPLACE FUNCTION packageset_inserted_trig() RETURNS TRIGGER
1272
-- A new package set was inserted; make it a descendent of itself in
1273
-- the flattened package set inclusion table in order to facilitate
1275
INSERT INTO flatpackagesetinclusion(parent, child)
1276
VALUES (NEW.id, NEW.id);
1281
COMMENT ON FUNCTION packageset_inserted_trig() IS
1282
'Insert self-referencing DAG edge when a new package set is inserted.';
1284
CREATE OR REPLACE FUNCTION packageset_deleted_trig() RETURNS TRIGGER
1288
DELETE FROM flatpackagesetinclusion
1289
WHERE parent = OLD.id AND child = OLD.id;
1291
-- A package set was deleted; it may have participated in package set
1292
-- inclusion relations in a sub/superset role; delete all inclusion
1293
-- relationships in which it participated.
1294
DELETE FROM packagesetinclusion
1295
WHERE parent = OLD.id OR child = OLD.id;
1300
COMMENT ON FUNCTION packageset_deleted_trig() IS
1301
'Remove any DAG edges leading to/from the deleted package set.';
1303
CREATE OR REPLACE FUNCTION packagesetinclusion_inserted_trig() RETURNS TRIGGER
1310
parent_distroseries text;
1311
child_distroseries text;
1313
-- Make sure that the package sets being associated here belong
1314
-- to the same distro series.
1315
IF (SELECT parent.distroseries != child.distroseries
1316
FROM packageset parent, packageset child
1317
WHERE parent.id = NEW.parent AND child.id = NEW.child)
1319
SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
1320
SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
1321
SELECT ds.name INTO parent_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.parent AND ps.distroseries = ds.id;
1322
SELECT ds.name INTO child_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.child AND ps.distroseries = ds.id;
1323
RAISE EXCEPTION 'Package sets % and % belong to different distro series (to % and % respectively) and thus cannot be associated.', child_name, parent_name, child_distroseries, parent_distroseries;
1327
SELECT * FROM flatpackagesetinclusion
1328
WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)
1330
SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
1331
SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
1332
RAISE EXCEPTION 'Package set % already includes %. Adding (% -> %) would introduce a cycle in the package set graph (DAG).', child_name, parent_name, parent_name, child_name;
1335
-- A new package set inclusion relationship was inserted i.e. a set M
1336
-- now includes another set N as a subset.
1337
-- For an explanation of the queries below please see page 4 of
1338
-- "Maintaining Transitive Closure of Graphs in SQL"
1339
-- http://www.comp.nus.edu.sg/~wongls/psZ/dlsw-ijit97-16.ps
1340
CREATE TEMP TABLE tmp_fpsi_new(
1341
parent integer NOT NULL,
1342
child integer NOT NULL);
1344
INSERT INTO tmp_fpsi_new (
1346
X.parent AS parent, NEW.child AS child
1347
FROM flatpackagesetinclusion X WHERE X.child = NEW.parent
1350
NEW.parent AS parent, X.child AS child
1351
FROM flatpackagesetinclusion X WHERE X.parent = NEW.child
1354
X.parent AS parent, Y.child AS child
1355
FROM flatpackagesetinclusion X, flatpackagesetinclusion Y
1356
WHERE X.child = NEW.parent AND Y.parent = NEW.child
1358
INSERT INTO tmp_fpsi_new(parent, child) VALUES(NEW.parent, NEW.child);
1360
INSERT INTO flatpackagesetinclusion(parent, child) (
1362
parent, child FROM tmp_fpsi_new
1364
SELECT F.parent, F.child FROM flatpackagesetinclusion F
1367
DROP TABLE tmp_fpsi_new;
1373
COMMENT ON FUNCTION packagesetinclusion_inserted_trig() IS
1374
'Maintain the transitive closure in the DAG for a newly inserted edge leading to/from a package set.';
1376
CREATE OR REPLACE FUNCTION packagesetinclusion_deleted_trig() RETURNS TRIGGER
1380
-- A package set inclusion relationship was deleted i.e. a set M
1381
-- ceases to include another set N as a subset.
1382
-- For an explanation of the queries below please see page 5 of
1383
-- "Maintaining Transitive Closure of Graphs in SQL"
1384
-- http://www.comp.nus.edu.sg/~wongls/psZ/dlsw-ijit97-16.ps
1385
CREATE TEMP TABLE tmp_fpsi_suspect(
1386
parent integer NOT NULL,
1387
child integer NOT NULL);
1388
CREATE TEMP TABLE tmp_fpsi_trusted(
1389
parent integer NOT NULL,
1390
child integer NOT NULL);
1391
CREATE TEMP TABLE tmp_fpsi_good(
1392
parent integer NOT NULL,
1393
child integer NOT NULL);
1395
INSERT INTO tmp_fpsi_suspect (
1396
SELECT X.parent, Y.child
1397
FROM flatpackagesetinclusion X, flatpackagesetinclusion Y
1398
WHERE X.child = OLD.parent AND Y.parent = OLD.child
1400
SELECT X.parent, OLD.child FROM flatpackagesetinclusion X
1401
WHERE X.child = OLD.parent
1403
SELECT OLD.parent, X.child FROM flatpackagesetinclusion X
1404
WHERE X.parent = OLD.child
1406
SELECT OLD.parent, OLD.child
1409
INSERT INTO tmp_fpsi_trusted (
1410
SELECT parent, child FROM flatpackagesetinclusion
1412
SELECT parent, child FROM tmp_fpsi_suspect
1414
SELECT parent, child FROM packagesetinclusion psi
1415
WHERE psi.parent != OLD.parent AND psi.child != OLD.child
1418
INSERT INTO tmp_fpsi_good (
1419
SELECT parent, child FROM tmp_fpsi_trusted
1421
SELECT T1.parent, T2.child
1422
FROM tmp_fpsi_trusted T1, tmp_fpsi_trusted T2
1423
WHERE T1.child = T2.parent
1425
SELECT T1.parent, T3.child
1426
FROM tmp_fpsi_trusted T1, tmp_fpsi_trusted T2, tmp_fpsi_trusted T3
1427
WHERE T1.child = T2.parent AND T2.child = T3.parent
1430
DELETE FROM flatpackagesetinclusion fpsi
1432
SELECT * FROM tmp_fpsi_good T
1433
WHERE T.parent = fpsi.parent AND T.child = fpsi.child);
1435
DROP TABLE tmp_fpsi_good;
1436
DROP TABLE tmp_fpsi_trusted;
1437
DROP TABLE tmp_fpsi_suspect;
1443
COMMENT ON FUNCTION packagesetinclusion_deleted_trig() IS
1444
'Maintain the transitive closure in the DAG when an edge leading to/from a package set is deleted.';
1447
CREATE OR REPLACE FUNCTION update_branch_name_cache() RETURNS TRIGGER
1451
needs_update boolean := FALSE;
1453
IF TG_OP = 'INSERT' THEN
1454
needs_update := TRUE;
1455
ELSIF (NEW.owner_name IS NULL
1456
OR NEW.unique_name IS NULL
1457
OR OLD.owner_name <> NEW.owner_name
1458
OR OLD.unique_name <> NEW.unique_name
1459
OR (NEW.target_suffix IS NULL <> OLD.target_suffix IS NULL)
1460
OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')
1461
OR OLD.name <> NEW.name
1462
OR OLD.owner <> NEW.owner
1463
OR COALESCE(OLD.product, -1) <> COALESCE(NEW.product, -1)
1464
OR COALESCE(OLD.distroseries, -1) <> COALESCE(NEW.distroseries, -1)
1465
OR COALESCE(OLD.sourcepackagename, -1)
1466
<> COALESCE(NEW.sourcepackagename, -1)) THEN
1467
needs_update := TRUE;
1470
IF needs_update THEN
1472
Person.name AS owner_name,
1473
COALESCE(Product.name, SPN.name) AS target_suffix,
1474
'~' || Person.name || '/' || COALESCE(
1476
Distribution.name || '/' || Distroseries.name
1478
'+junk') || '/' || NEW.name AS unique_name
1479
INTO NEW.owner_name, NEW.target_suffix, NEW.unique_name
1481
LEFT OUTER JOIN DistroSeries ON NEW.distroseries = DistroSeries.id
1482
LEFT OUTER JOIN Product ON NEW.product = Product.id
1483
LEFT OUTER JOIN Distribution
1484
ON Distroseries.distribution = Distribution.id
1485
LEFT OUTER JOIN SourcepackageName AS SPN
1486
ON SPN.id = NEW.sourcepackagename
1487
WHERE Person.id = NEW.owner;
1494
COMMENT ON FUNCTION update_branch_name_cache() IS
1495
'Maintain the cached name columns in Branch.';
1498
CREATE OR REPLACE FUNCTION mv_branch_person_update() RETURNS TRIGGER
1504
IF OLD.id != NEW.id THEN
1505
RAISE EXCEPTION 'Cannot change Person.id';
1507
IF OLD.name != NEW.name THEN
1508
UPDATE Branch SET owner_name = NEW.name WHERE owner = NEW.id;
1514
COMMENT ON FUNCTION mv_branch_person_update() IS
1515
'Maintain Branch name cache when Person is modified.';
1518
CREATE OR REPLACE FUNCTION mv_branch_product_update() RETURNS TRIGGER
1524
IF OLD.id != NEW.id THEN
1525
RAISE EXCEPTION 'Cannot change Product.id';
1527
IF OLD.name != NEW.name THEN
1528
UPDATE Branch SET target_suffix = NEW.name WHERE product=NEW.id;
1534
COMMENT ON FUNCTION mv_branch_product_update() IS
1535
'Maintain Branch name cache when Product is modified.';
1538
CREATE OR REPLACE FUNCTION mv_branch_distroseries_update() RETURNS TRIGGER
1542
IF OLD.id != NEW.id THEN
1543
RAISE EXCEPTION 'Cannot change Distroseries.id';
1545
IF OLD.name != NEW.name THEN
1546
UPDATE Branch SET unique_name = NULL
1547
WHERE Branch.distroseries = NEW.id;
1553
COMMENT ON FUNCTION mv_branch_distroseries_update() IS
1554
'Maintain Branch name cache when Distroseries is modified.';
1557
CREATE OR REPLACE FUNCTION mv_branch_distribution_update() RETURNS TRIGGER
1561
IF OLD.id != NEW.id THEN
1562
RAISE EXCEPTION 'Cannot change Distribution.id';
1564
IF OLD.name != NEW.name THEN
1565
UPDATE Branch SET unique_name = NULL
1567
WHERE Branch.distroseries = Distroseries.id
1568
AND Distroseries.distribution = NEW.id;
1574
COMMENT ON FUNCTION mv_branch_distribution_update() IS
1575
'Maintain Branch name cache when Distribution is modified.';
1578
CREATE OR REPLACE FUNCTION lp_mirror_teamparticipation_ins() RETURNS trigger
1579
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1582
INSERT INTO lp_TeamParticipation SELECT NEW.*;
1583
RETURN NULL; -- Ignored for AFTER triggers.
1587
CREATE OR REPLACE FUNCTION lp_mirror_personlocation_ins() RETURNS trigger
1588
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1591
INSERT INTO lp_PersonLocation SELECT NEW.*;
1592
RETURN NULL; -- Ignored for AFTER triggers.
1596
CREATE OR REPLACE FUNCTION lp_mirror_person_ins() RETURNS trigger
1597
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1600
INSERT INTO lp_Person (
1601
id, displayname, teamowner, teamdescription, name, language, fti,
1602
defaultmembershipperiod, defaultrenewalperiod, subscriptionpolicy,
1603
merged, datecreated, homepage_content, icon, mugshot,
1604
hide_email_addresses, creation_rationale, creation_comment,
1605
registrant, logo, renewal_policy, personal_standing,
1606
personal_standing_reason, mail_resumption_date,
1607
mailing_list_auto_subscribe_policy, mailing_list_receive_duplicates,
1608
visibility, verbose_bugnotifications, account)
1610
NEW.id, NEW.displayname, NEW.teamowner, NULL,
1611
NEW.name, NEW.language, NEW.fti, NEW.defaultmembershipperiod,
1612
NEW.defaultrenewalperiod, NEW.subscriptionpolicy,
1613
NEW.merged, NEW.datecreated, NULL, NEW.icon,
1614
NEW.mugshot, NEW.hide_email_addresses, NEW.creation_rationale,
1615
NEW.creation_comment, NEW.registrant, NEW.logo, NEW.renewal_policy,
1616
NEW.personal_standing, NEW.personal_standing_reason,
1617
NEW.mail_resumption_date, NEW.mailing_list_auto_subscribe_policy,
1618
NEW.mailing_list_receive_duplicates, NEW.visibility,
1619
NEW.verbose_bugnotifications, NEW.account);
1620
RETURN NULL; -- Ignored for AFTER triggers.
1624
CREATE OR REPLACE FUNCTION lp_mirror_account_ins() RETURNS trigger
1625
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1628
INSERT INTO lp_Account (id, openid_identifier)
1629
VALUES (NEW.id, NEW.openid_identifier);
1630
RETURN NULL; -- Ignored for AFTER triggers.
1634
CREATE OR REPLACE FUNCTION lp_mirror_openididentifier_ins() RETURNS trigger
1635
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1638
-- Support obsolete lp_Account.openid_identifier as best we can
1639
-- until ISD migrates to using lp_OpenIdIdentifier.
1640
UPDATE lp_account SET openid_identifier = NEW.identifier
1641
WHERE id = NEW.account;
1643
INSERT INTO lp_account (id, openid_identifier)
1644
VALUES (NEW.account, NEW.identifier);
1647
INSERT INTO lp_OpenIdIdentifier (identifier, account, date_created)
1648
VALUES (NEW.identifier, NEW.account, NEW.date_created);
1650
RETURN NULL; -- Ignored for AFTER triggers.
1654
CREATE OR REPLACE FUNCTION lp_mirror_teamparticipation_upd() RETURNS trigger
1655
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1658
UPDATE lp_TeamParticipation
1663
RETURN NULL; -- Ignored for AFTER triggers.
1667
CREATE OR REPLACE FUNCTION lp_mirror_personlocation_upd() RETURNS trigger
1668
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1671
UPDATE lp_PersonLocation
1673
date_created = NEW.date_created,
1674
person = NEW.person,
1675
latitude = NEW.latitude,
1676
longitude = NEW.longitude,
1677
time_zone = NEW.time_zone,
1678
last_modified_by = NEW.last_modified_by,
1679
date_last_modified = NEW.date_last_modified,
1680
visible = NEW.visible,
1683
RETURN NULL; -- Ignored for AFTER triggers.
1687
CREATE OR REPLACE FUNCTION lp_mirror_person_upd() RETURNS trigger
1688
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1693
displayname = NEW.displayname,
1694
teamowner = NEW.teamowner,
1695
teamdescription = NULL,
1697
language = NEW.language,
1699
defaultmembershipperiod = NEW.defaultmembershipperiod,
1700
defaultrenewalperiod = NEW.defaultrenewalperiod,
1701
subscriptionpolicy = NEW.subscriptionpolicy,
1702
merged = NEW.merged,
1703
datecreated = NEW.datecreated,
1704
homepage_content = NULL,
1706
mugshot = NEW.mugshot,
1707
hide_email_addresses = NEW.hide_email_addresses,
1708
creation_rationale = NEW.creation_rationale,
1709
creation_comment = NEW.creation_comment,
1710
registrant = NEW.registrant,
1712
renewal_policy = NEW.renewal_policy,
1713
personal_standing = NEW.personal_standing,
1714
personal_standing_reason = NEW.personal_standing_reason,
1715
mail_resumption_date = NEW.mail_resumption_date,
1716
mailing_list_auto_subscribe_policy
1717
= NEW.mailing_list_auto_subscribe_policy,
1718
mailing_list_receive_duplicates = NEW.mailing_list_receive_duplicates,
1719
visibility = NEW.visibility,
1720
verbose_bugnotifications = NEW.verbose_bugnotifications,
1721
account = NEW.account
1723
RETURN NULL; -- Ignored for AFTER triggers.
1727
CREATE OR REPLACE FUNCTION lp_mirror_account_upd() RETURNS trigger
1728
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1731
IF OLD.id <> NEW.id OR OLD.openid_identifier <> NEW.openid_identifier THEN
1733
SET id = NEW.id, openid_identifier = NEW.openid_identifier
1736
RETURN NULL; -- Ignored for AFTER triggers.
1740
CREATE OR REPLACE FUNCTION lp_mirror_openididentifier_upd() RETURNS trigger
1741
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1744
IF OLD.identifier <> NEW.identifier THEN
1745
UPDATE lp_Account SET openid_identifier = NEW.identifier
1746
WHERE openid_identifier = OLD.identifier;
1748
UPDATE lp_OpenIdIdentifier
1750
identifier = NEW.identifier,
1751
account = NEW.account,
1752
date_created = NEW.date_created
1753
WHERE identifier = OLD.identifier;
1754
RETURN NULL; -- Ignored for AFTER triggers.
1758
CREATE OR REPLACE FUNCTION lp_mirror_del() RETURNS trigger
1759
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1762
EXECUTE 'DELETE FROM lp_' || TG_TABLE_NAME || ' WHERE id=' || OLD.id;
1763
RETURN NULL; -- Ignored for AFTER triggers.
1767
CREATE OR REPLACE FUNCTION lp_mirror_openididentifier_del() RETURNS trigger
1768
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1771
next_identifier text;
1773
SELECT INTO next_identifier identifier FROM OpenIdIdentifier
1774
WHERE account = OLD.account AND identifier <> OLD.identifier
1775
ORDER BY date_created DESC LIMIT 1;
1777
IF next_identifier IS NOT NULL THEN
1778
UPDATE lp_account SET openid_identifier = next_identifier
1779
WHERE openid_identifier = OLD.identifier;
1781
DELETE FROM lp_account WHERE openid_identifier = OLD.identifier;
1784
DELETE FROM lp_OpenIdIdentifier WHERE identifier = OLD.identifier;
1786
RETURN NULL; -- Ignored for AFTER triggers.
1790
CREATE OR REPLACE FUNCTION add_test_openid_identifier(account_ integer)
1791
RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1794
-- The generated OpenIdIdentifier is not a valid OpenId Identity URL
1795
-- and does not match tokens generated by the Canonical SSO. They
1796
-- are only useful to the test suite, and access to this stored
1797
-- procedure on production does not allow you to compromise
1799
INSERT INTO OpenIdIdentifier (identifier, account)
1800
VALUES ('test' || CAST(account_ AS text), account_);
1803
WHEN unique_violation THEN
1808
COMMENT ON FUNCTION add_test_openid_identifier(integer) IS
1809
'Add an OpenIdIdentifier to an account that can be used to login in the test environment. These identifiers are not usable on production or staging.';
1811
CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded(integer)
1812
RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1815
UPDATE bug SET latest_patch_uploaded =
1816
(SELECT max(message.datecreated)
1817
FROM message, bugattachment
1818
WHERE bugattachment.message=message.id AND
1819
bugattachment.bug=$1 AND
1820
bugattachment.type=1)
1826
CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded_on_insert_update()
1827
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1830
PERFORM bug_update_latest_patch_uploaded(NEW.bug);
1831
RETURN NULL; -- Ignored - this is an AFTER trigger
1836
CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded_on_delete()
1837
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1840
PERFORM bug_update_latest_patch_uploaded(OLD.bug);
1841
RETURN NULL; -- Ignored - this is an AFTER trigger
1846
CREATE OR REPLACE FUNCTION calculate_bug_heat(bug_id integer) RETURNS integer
1847
LANGUAGE plpythonu STABLE RETURNS NULL ON NULL INPUT AS $$
1848
from datetime import datetime
1850
class BugHeatConstants:
1857
def get_max_heat_for_bug(bug_id):
1858
results = plpy.execute("""
1860
GREATEST(Product.max_bug_heat,
1861
DistributionSourcePackage.max_bug_heat))
1864
LEFT OUTER JOIN ProductSeries ON
1865
BugTask.productseries = ProductSeries.id
1866
LEFT OUTER JOIN Product ON (
1867
BugTask.product = Product.id
1868
OR ProductSeries.product = Product.id)
1869
LEFT OUTER JOIN DistroSeries ON
1870
BugTask.distroseries = DistroSeries.id
1871
LEFT OUTER JOIN Distribution ON (
1872
BugTask.distribution = Distribution.id
1873
OR DistroSeries.distribution = Distribution.id)
1874
LEFT OUTER JOIN DistributionSourcePackage ON (
1875
BugTask.sourcepackagename =
1876
DistributionSourcePackage.sourcepackagename)
1878
BugTask.bug = %s""" % bug_id)
1880
return results[0]['max_heat']
1882
# It would be nice to be able to just SELECT * here, but we need the
1883
# timestamps to be in a format that datetime.fromtimestamp() will
1885
bug_data = plpy.execute("""
1890
number_of_duplicates,
1891
users_affected_count,
1892
EXTRACT(epoch from datecreated)
1893
AS timestamp_date_created,
1894
EXTRACT(epoch from date_last_updated)
1895
AS timestamp_date_last_updated,
1896
EXTRACT(epoch from date_last_message)
1897
AS timestamp_date_last_message
1898
FROM Bug WHERE id = %s""" % bug_id)
1900
if bug_data.nrows() == 0:
1901
raise Exception("Bug %s doesn't exist." % bug_id)
1904
if bug['duplicateof'] is not None:
1909
BugHeatConstants.DUPLICATE * bug['number_of_duplicates'])
1910
heat['affected_users'] = (
1911
BugHeatConstants.AFFECTED_USER *
1912
bug['users_affected_count'])
1915
heat['privacy'] = BugHeatConstants.PRIVACY
1916
if bug['security_related']:
1917
heat['security'] = BugHeatConstants.SECURITY
1919
# Get the heat from subscribers, both direct and via duplicates.
1920
subs_from_dupes = plpy.execute("""
1921
SELECT COUNT(DISTINCT BugSubscription.person) AS sub_count
1922
FROM BugSubscription, Bug
1923
WHERE Bug.id = BugSubscription.bug
1924
AND (Bug.id = %s OR Bug.duplicateof = %s)"""
1927
heat['subcribers'] = (
1928
BugHeatConstants.SUBSCRIBER
1929
* subs_from_dupes[0]['sub_count'])
1931
total_heat = sum(heat.values())
1933
# Bugs decay over time. Every day the bug isn't touched its heat
1935
date_last_updated = datetime.fromtimestamp(
1936
bug['timestamp_date_last_updated'])
1937
days_since_last_update = (datetime.utcnow() - date_last_updated).days
1938
total_heat = int(total_heat * (0.99 ** days_since_last_update))
1940
if days_since_last_update > 0:
1941
# Bug heat increases by a quarter of the maximum bug heat
1942
# divided by the number of days since the bug's creation date.
1943
date_created = datetime.fromtimestamp(
1944
bug['timestamp_date_created'])
1946
if bug['timestamp_date_last_message'] is not None:
1947
date_last_message = datetime.fromtimestamp(
1948
bug['timestamp_date_last_message'])
1949
oldest_date = max(date_last_updated, date_last_message)
1951
date_last_message = None
1952
oldest_date = date_last_updated
1954
days_since_last_activity = (datetime.utcnow() - oldest_date).days
1955
days_since_created = (datetime.utcnow() - date_created).days
1956
max_heat = get_max_heat_for_bug(bug_id)
1957
if max_heat is not None and days_since_created > 0:
1959
total_heat + (max_heat * 0.25 / days_since_created))
1961
return int(total_heat)
1964
CREATE OR REPLACE FUNCTION bugmessage_copy_owner_from_message()
1965
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1968
IF TG_OP = 'INSERT' THEN
1969
IF NEW.owner is NULL THEN
1971
SET owner = Message.owner FROM
1973
Message.id = NEW.message AND
1974
BugMessage.id = NEW.id;
1976
ELSIF NEW.message != OLD.message THEN
1978
SET owner = Message.owner FROM
1980
Message.id = NEW.message AND
1981
BugMessage.id = NEW.id;
1983
RETURN NULL; -- Ignored - this is an AFTER trigger
1987
COMMENT ON FUNCTION bugmessage_copy_owner_from_message() IS
1988
'Copies the message owner into bugmessage when bugmessage changes.';
1990
CREATE OR REPLACE FUNCTION message_copy_owner_to_bugmessage()
1991
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
1994
IF NEW.owner != OLD.owner THEN
1996
SET owner = NEW.owner
1998
BugMessage.message = NEW.id;
2000
RETURN NULL; -- Ignored - this is an AFTER trigger
2004
COMMENT ON FUNCTION message_copy_owner_to_bugmessage() IS
2005
'Copies the message owner into bugmessage when message changes.';
2008
CREATE OR REPLACE FUNCTION questionmessage_copy_owner_from_message()
2009
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
2012
IF TG_OP = 'INSERT' THEN
2013
IF NEW.owner is NULL THEN
2014
UPDATE QuestionMessage
2015
SET owner = Message.owner FROM
2017
Message.id = NEW.message AND
2018
QuestionMessage.id = NEW.id;
2020
ELSIF NEW.message != OLD.message THEN
2021
UPDATE QuestionMessage
2022
SET owner = Message.owner FROM
2024
Message.id = NEW.message AND
2025
QuestionMessage.id = NEW.id;
2027
RETURN NULL; -- Ignored - this is an AFTER trigger
2031
COMMENT ON FUNCTION questionmessage_copy_owner_from_message() IS
2032
'Copies the message owner into QuestionMessage when QuestionMessage changes.';
2034
CREATE OR REPLACE FUNCTION message_copy_owner_to_questionmessage()
2035
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
2038
IF NEW.owner != OLD.owner THEN
2039
UPDATE QuestionMessage
2040
SET owner = NEW.owner
2042
QuestionMessage.message = NEW.id;
2044
RETURN NULL; -- Ignored - this is an AFTER trigger
2048
COMMENT ON FUNCTION message_copy_owner_to_questionmessage() IS
2049
'Copies the message owner into questionmessage when message changes.';
2052
CREATE OR REPLACE FUNCTION bug_update_heat_copy_to_bugtask()
2053
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
2056
IF NEW.heat != OLD.heat THEN
2057
UPDATE bugtask SET heat=NEW.heat WHERE bugtask.bug=NEW.id;
2059
RETURN NULL; -- Ignored - this is an AFTER trigger
2063
COMMENT ON FUNCTION bug_update_heat_copy_to_bugtask() IS
2064
'Copies bug heat to bugtasks when the bug is changed. Runs on UPDATE only because INSERTs do not have bugtasks at the point of insertion.';
2066
CREATE OR REPLACE FUNCTION milestone_sort_key(
2067
dateexpected timestamp, name text)
2070
# If this method is altered, then any functional indexes using it
2071
# need to be rebuilt.
2075
date_expected, name = args
2077
def substitute_filled_numbers(match):
2078
return match.group(0).zfill(5)
2080
name = re.sub(u'\d+', substitute_filled_numbers, name)
2081
if date_expected is None:
2082
# NULL dates are considered to be in the future.
2083
date_expected = datetime.datetime(datetime.MAXYEAR, 1, 1)
2084
return '%s %s' % (date_expected, name)
2086
LANGUAGE plpythonu IMMUTABLE;
2088
COMMENT ON FUNCTION milestone_sort_key(timestamp, text) IS
2089
'Sort by the Milestone dateexpected and name. If the dateexpected is NULL, then it is converted to a date far in the future, so it will be sorted as a milestone in the future.';
2092
CREATE OR REPLACE FUNCTION version_sort_key(version text) RETURNS text
2093
LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT AS
2095
# If this method is altered, then any functional indexes using it
2096
# need to be rebuilt.
2101
def substitute_filled_numbers(match):
2102
# Prepend "~" so that version numbers will show up first
2103
# when sorted descending, i.e. [3, 2c, 2b, 1, c, b, a] instead
2104
# of [c, b, a, 3, 2c, 2b, 1]. "~" has the highest ASCII value
2105
# of visible ASCII characters.
2106
return '~' + match.group(0).zfill(5)
2108
return re.sub(u'\d+', substitute_filled_numbers, version)
2111
COMMENT ON FUNCTION version_sort_key(text) IS
2112
'Sort a field as version numbers that do not necessarily conform to debian package versions (For example, when "2-2" should be considered greater than "1:1"). debversion_sort_key() should be used for debian versions. Numbers will be sorted after letters unlike typical ASCII, so that a descending sort will put the latest version number that starts with a number instead of a letter will be at the top. E.g. ascending is [a, z, 1, 9] and descending is [9, 1, z, a].';
4
-- Do nothing. Changes to functions now being done in db patches.
5
SET client_min_messages=ERROR;