Launchpad itself

Merge lp:~wgrant/launchpad/db-baseline-2210 into lp:launchpad

  1. db-baseline-2210
  2. Merge into devel
Proposed by William Grant
Status: Merged
Merged at revision: 18888
Proposed branch: lp:~wgrant/launchpad/db-baseline-2210
Merge into: lp:launchpad
Diff against target: 38895 lines (+17239/-16332)
3 files modified
database/schema/Makefile (+2/-2)
database/schema/launchpad-2210-00-0.sql (+17234/-16330)
database/schema/patch-2210-00-0.sql (+3/-0)
To merge this branch: bzr merge lp:~wgrant/launchpad/db-baseline-2210
Reviewer Review Type Date Requested Status
Stuart Bishop (community) Approve
Review via email: mp+363648@code.launchpad.net

Commit message

Rebaseline schema, version 2210.

To post a comment you must log in.
Revision history for this message
Stuart Bishop (stub) wrote :

Yup

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'database/schema/Makefile'
2--- database/schema/Makefile 2018-05-14 13:11:14 +0000
3+++ database/schema/Makefile 2019-02-26 07:46:16 +0000
4@@ -61,9 +61,9 @@
5 # on production. It is generated using newbaseline.py in
6 # bzr+ssh://devpad.canonical.com/code/stub/dbascripts
7 #
8-REV=2209
9+REV=2210
10 BASELINE=launchpad-${REV}-00-0.sql
11-MD5SUM=cc7a493c924196409a22392a16443d52 launchpad-2209-00-0.sql
12+MD5SUM=36ae7078cd41916bbbd9c116b2e6aea7 launchpad-2210-00-0.sql
13
14 default: all
15
16
17=== renamed file 'database/schema/patch-2209-00-0.sql' => 'database/schema/archive/patch-2209-00-0.sql'
18=== renamed file 'database/schema/patch-2209-00-1.sql' => 'database/schema/archive/patch-2209-00-1.sql'
19=== renamed file 'database/schema/patch-2209-00-2.sql' => 'database/schema/archive/patch-2209-00-2.sql'
20=== renamed file 'database/schema/patch-2209-00-3.sql' => 'database/schema/archive/patch-2209-00-3.sql'
21=== renamed file 'database/schema/patch-2209-00-5.sql' => 'database/schema/archive/patch-2209-00-5.sql'
22=== renamed file 'database/schema/patch-2209-00-6.sql' => 'database/schema/archive/patch-2209-00-6.sql'
23=== renamed file 'database/schema/patch-2209-00-7.sql' => 'database/schema/archive/patch-2209-00-7.sql'
24=== renamed file 'database/schema/patch-2209-00-8.sql' => 'database/schema/archive/patch-2209-00-8.sql'
25=== renamed file 'database/schema/patch-2209-00-9.sql' => 'database/schema/archive/patch-2209-00-9.sql'
26=== renamed file 'database/schema/patch-2209-01-0.sql' => 'database/schema/archive/patch-2209-01-0.sql'
27=== renamed file 'database/schema/patch-2209-01-1.sql' => 'database/schema/archive/patch-2209-01-1.sql'
28=== renamed file 'database/schema/patch-2209-02-0.sql' => 'database/schema/archive/patch-2209-02-0.sql'
29=== renamed file 'database/schema/patch-2209-04-0.sql' => 'database/schema/archive/patch-2209-04-0.sql'
30=== renamed file 'database/schema/patch-2209-05-1.sql' => 'database/schema/archive/patch-2209-05-1.sql'
31=== renamed file 'database/schema/patch-2209-06-1.sql' => 'database/schema/archive/patch-2209-06-1.sql'
32=== renamed file 'database/schema/patch-2209-07-0.sql' => 'database/schema/archive/patch-2209-07-0.sql'
33=== renamed file 'database/schema/patch-2209-07-1.sql' => 'database/schema/archive/patch-2209-07-1.sql'
34=== renamed file 'database/schema/patch-2209-08-1.sql' => 'database/schema/archive/patch-2209-08-1.sql'
35=== renamed file 'database/schema/patch-2209-09-0.sql' => 'database/schema/archive/patch-2209-09-0.sql'
36=== renamed file 'database/schema/patch-2209-10-0.sql' => 'database/schema/archive/patch-2209-10-0.sql'
37=== renamed file 'database/schema/patch-2209-11-0.sql' => 'database/schema/archive/patch-2209-11-0.sql'
38=== renamed file 'database/schema/patch-2209-11-1.sql' => 'database/schema/archive/patch-2209-11-1.sql'
39=== renamed file 'database/schema/patch-2209-11-2.sql' => 'database/schema/archive/patch-2209-11-2.sql'
40=== renamed file 'database/schema/patch-2209-11-3.sql' => 'database/schema/archive/patch-2209-11-3.sql'
41=== renamed file 'database/schema/patch-2209-11-4.sql' => 'database/schema/archive/patch-2209-11-4.sql'
42=== renamed file 'database/schema/patch-2209-11-5.sql' => 'database/schema/archive/patch-2209-11-5.sql'
43=== renamed file 'database/schema/patch-2209-12-0.sql' => 'database/schema/archive/patch-2209-12-0.sql'
44=== renamed file 'database/schema/patch-2209-12-1.sql' => 'database/schema/archive/patch-2209-12-1.sql'
45=== renamed file 'database/schema/patch-2209-12-2.sql' => 'database/schema/archive/patch-2209-12-2.sql'
46=== renamed file 'database/schema/patch-2209-12-3.sql' => 'database/schema/archive/patch-2209-12-3.sql'
47=== renamed file 'database/schema/patch-2209-12-4.sql' => 'database/schema/archive/patch-2209-12-4.sql'
48=== renamed file 'database/schema/patch-2209-12-5.sql' => 'database/schema/archive/patch-2209-12-5.sql'
49=== renamed file 'database/schema/patch-2209-14-0.sql' => 'database/schema/archive/patch-2209-14-0.sql'
50=== renamed file 'database/schema/patch-2209-15-0.sql' => 'database/schema/archive/patch-2209-15-0.sql'
51=== renamed file 'database/schema/patch-2209-15-1.sql' => 'database/schema/archive/patch-2209-15-1.sql'
52=== renamed file 'database/schema/patch-2209-15-2.sql' => 'database/schema/archive/patch-2209-15-2.sql'
53=== renamed file 'database/schema/patch-2209-15-3.sql' => 'database/schema/archive/patch-2209-15-3.sql'
54=== renamed file 'database/schema/patch-2209-16-0.sql' => 'database/schema/archive/patch-2209-16-0.sql'
55=== renamed file 'database/schema/patch-2209-16-1.sql' => 'database/schema/archive/patch-2209-16-1.sql'
56=== renamed file 'database/schema/patch-2209-16-2.sql' => 'database/schema/archive/patch-2209-16-2.sql'
57=== renamed file 'database/schema/patch-2209-16-3.sql' => 'database/schema/archive/patch-2209-16-3.sql'
58=== renamed file 'database/schema/patch-2209-16-4.sql' => 'database/schema/archive/patch-2209-16-4.sql'
59=== renamed file 'database/schema/patch-2209-16-5.sql' => 'database/schema/archive/patch-2209-16-5.sql'
60=== renamed file 'database/schema/patch-2209-16-6.sql' => 'database/schema/archive/patch-2209-16-6.sql'
61=== renamed file 'database/schema/patch-2209-16-7.sql' => 'database/schema/archive/patch-2209-16-7.sql'
62=== renamed file 'database/schema/patch-2209-16-8.sql' => 'database/schema/archive/patch-2209-16-8.sql'
63=== renamed file 'database/schema/patch-2209-17-0.sql' => 'database/schema/archive/patch-2209-17-0.sql'
64=== renamed file 'database/schema/patch-2209-17-1.sql' => 'database/schema/archive/patch-2209-17-1.sql'
65=== renamed file 'database/schema/patch-2209-18-0.sql' => 'database/schema/archive/patch-2209-18-0.sql'
66=== renamed file 'database/schema/patch-2209-18-1.sql' => 'database/schema/archive/patch-2209-18-1.sql'
67=== renamed file 'database/schema/patch-2209-18-2.sql' => 'database/schema/archive/patch-2209-18-2.sql'
68=== renamed file 'database/schema/patch-2209-18-3.sql' => 'database/schema/archive/patch-2209-18-3.sql'
69=== renamed file 'database/schema/patch-2209-18-4.sql' => 'database/schema/archive/patch-2209-18-4.sql'
70=== renamed file 'database/schema/patch-2209-19-0.sql' => 'database/schema/archive/patch-2209-19-0.sql'
71=== renamed file 'database/schema/patch-2209-19-1.sql' => 'database/schema/archive/patch-2209-19-1.sql'
72=== renamed file 'database/schema/patch-2209-19-2.sql' => 'database/schema/archive/patch-2209-19-2.sql'
73=== renamed file 'database/schema/patch-2209-19-3.sql' => 'database/schema/archive/patch-2209-19-3.sql'
74=== renamed file 'database/schema/patch-2209-20-0.sql' => 'database/schema/archive/patch-2209-20-0.sql'
75=== renamed file 'database/schema/patch-2209-20-1.sql' => 'database/schema/archive/patch-2209-20-1.sql'
76=== renamed file 'database/schema/patch-2209-21-0.sql' => 'database/schema/archive/patch-2209-21-0.sql'
77=== renamed file 'database/schema/patch-2209-21-1.sql' => 'database/schema/archive/patch-2209-21-1.sql'
78=== renamed file 'database/schema/patch-2209-21-2.sql' => 'database/schema/archive/patch-2209-21-2.sql'
79=== renamed file 'database/schema/patch-2209-21-3.sql' => 'database/schema/archive/patch-2209-21-3.sql'
80=== renamed file 'database/schema/patch-2209-21-4.sql' => 'database/schema/archive/patch-2209-21-4.sql'
81=== renamed file 'database/schema/patch-2209-22-0.sql' => 'database/schema/archive/patch-2209-22-0.sql'
82=== renamed file 'database/schema/patch-2209-23-0.sql' => 'database/schema/archive/patch-2209-23-0.sql'
83=== renamed file 'database/schema/patch-2209-23-1.sql' => 'database/schema/archive/patch-2209-23-1.sql'
84=== renamed file 'database/schema/patch-2209-23-2.sql' => 'database/schema/archive/patch-2209-23-2.sql'
85=== renamed file 'database/schema/patch-2209-23-3.sql' => 'database/schema/archive/patch-2209-23-3.sql'
86=== renamed file 'database/schema/patch-2209-23-4.sql' => 'database/schema/archive/patch-2209-23-4.sql'
87=== renamed file 'database/schema/patch-2209-23-5.sql' => 'database/schema/archive/patch-2209-23-5.sql'
88=== renamed file 'database/schema/patch-2209-24-1.sql' => 'database/schema/archive/patch-2209-24-1.sql'
89=== renamed file 'database/schema/patch-2209-24-2.sql' => 'database/schema/archive/patch-2209-24-2.sql'
90=== renamed file 'database/schema/patch-2209-24-3.sql' => 'database/schema/archive/patch-2209-24-3.sql'
91=== renamed file 'database/schema/patch-2209-25-1.sql' => 'database/schema/archive/patch-2209-25-1.sql'
92=== renamed file 'database/schema/patch-2209-26-0.sql' => 'database/schema/archive/patch-2209-26-0.sql'
93=== renamed file 'database/schema/patch-2209-26-1.sql' => 'database/schema/archive/patch-2209-26-1.sql'
94=== renamed file 'database/schema/patch-2209-26-2.sql' => 'database/schema/archive/patch-2209-26-2.sql'
95=== renamed file 'database/schema/patch-2209-26-3.sql' => 'database/schema/archive/patch-2209-26-3.sql'
96=== renamed file 'database/schema/patch-2209-26-4.sql' => 'database/schema/archive/patch-2209-26-4.sql'
97=== renamed file 'database/schema/patch-2209-26-5.sql' => 'database/schema/archive/patch-2209-26-5.sql'
98=== renamed file 'database/schema/patch-2209-27-1.sql' => 'database/schema/archive/patch-2209-27-1.sql'
99=== renamed file 'database/schema/patch-2209-27-2.sql' => 'database/schema/archive/patch-2209-27-2.sql'
100=== renamed file 'database/schema/patch-2209-27-3.sql' => 'database/schema/archive/patch-2209-27-3.sql'
101=== renamed file 'database/schema/patch-2209-27-4.sql' => 'database/schema/archive/patch-2209-27-4.sql'
102=== renamed file 'database/schema/patch-2209-28-1.sql' => 'database/schema/archive/patch-2209-28-1.sql'
103=== renamed file 'database/schema/patch-2209-28-2.sql' => 'database/schema/archive/patch-2209-28-2.sql'
104=== renamed file 'database/schema/patch-2209-28-4.sql' => 'database/schema/archive/patch-2209-28-4.sql'
105=== renamed file 'database/schema/patch-2209-28-5.sql' => 'database/schema/archive/patch-2209-28-5.sql'
106=== renamed file 'database/schema/patch-2209-28-6.sql' => 'database/schema/archive/patch-2209-28-6.sql'
107=== renamed file 'database/schema/patch-2209-29-0.sql' => 'database/schema/archive/patch-2209-29-0.sql'
108=== renamed file 'database/schema/patch-2209-30-1.sql' => 'database/schema/archive/patch-2209-30-1.sql'
109=== renamed file 'database/schema/patch-2209-30-2.sql' => 'database/schema/archive/patch-2209-30-2.sql'
110=== renamed file 'database/schema/patch-2209-31-1.sql' => 'database/schema/archive/patch-2209-31-1.sql'
111=== renamed file 'database/schema/patch-2209-31-2.sql' => 'database/schema/archive/patch-2209-31-2.sql'
112=== renamed file 'database/schema/patch-2209-31-3.sql' => 'database/schema/archive/patch-2209-31-3.sql'
113=== renamed file 'database/schema/patch-2209-32-0.sql' => 'database/schema/archive/patch-2209-32-0.sql'
114=== renamed file 'database/schema/patch-2209-34-1.sql' => 'database/schema/archive/patch-2209-34-1.sql'
115=== renamed file 'database/schema/patch-2209-35-1.sql' => 'database/schema/archive/patch-2209-35-1.sql'
116=== renamed file 'database/schema/patch-2209-35-2.sql' => 'database/schema/archive/patch-2209-35-2.sql'
117=== renamed file 'database/schema/patch-2209-35-3.sql' => 'database/schema/archive/patch-2209-35-3.sql'
118=== renamed file 'database/schema/patch-2209-35-4.sql' => 'database/schema/archive/patch-2209-35-4.sql'
119=== renamed file 'database/schema/patch-2209-36-0.sql' => 'database/schema/archive/patch-2209-36-0.sql'
120=== renamed file 'database/schema/patch-2209-36-1.sql' => 'database/schema/archive/patch-2209-36-1.sql'
121=== renamed file 'database/schema/patch-2209-37-0.sql' => 'database/schema/archive/patch-2209-37-0.sql'
122=== renamed file 'database/schema/patch-2209-38-0.sql' => 'database/schema/archive/patch-2209-38-0.sql'
123=== renamed file 'database/schema/patch-2209-38-1.sql' => 'database/schema/archive/patch-2209-38-1.sql'
124=== renamed file 'database/schema/patch-2209-38-2.sql' => 'database/schema/archive/patch-2209-38-2.sql'
125=== renamed file 'database/schema/patch-2209-38-3.sql' => 'database/schema/archive/patch-2209-38-3.sql'
126=== renamed file 'database/schema/patch-2209-39-0.sql' => 'database/schema/archive/patch-2209-39-0.sql'
127=== renamed file 'database/schema/patch-2209-39-1.sql' => 'database/schema/archive/patch-2209-39-1.sql'
128=== renamed file 'database/schema/patch-2209-40-0.sql' => 'database/schema/archive/patch-2209-40-0.sql'
129=== renamed file 'database/schema/patch-2209-40-1.sql' => 'database/schema/archive/patch-2209-40-1.sql'
130=== renamed file 'database/schema/patch-2209-40-2.sql' => 'database/schema/archive/patch-2209-40-2.sql'
131=== renamed file 'database/schema/patch-2209-40-3.sql' => 'database/schema/archive/patch-2209-40-3.sql'
132=== renamed file 'database/schema/patch-2209-41-0.sql' => 'database/schema/archive/patch-2209-41-0.sql'
133=== renamed file 'database/schema/patch-2209-41-1.sql' => 'database/schema/archive/patch-2209-41-1.sql'
134=== renamed file 'database/schema/patch-2209-41-2.sql' => 'database/schema/archive/patch-2209-41-2.sql'
135=== renamed file 'database/schema/patch-2209-41-3.sql' => 'database/schema/archive/patch-2209-41-3.sql'
136=== renamed file 'database/schema/patch-2209-41-4.sql' => 'database/schema/archive/patch-2209-41-4.sql'
137=== renamed file 'database/schema/patch-2209-41-5.sql' => 'database/schema/archive/patch-2209-41-5.sql'
138=== renamed file 'database/schema/patch-2209-42-0.sql' => 'database/schema/archive/patch-2209-42-0.sql'
139=== renamed file 'database/schema/patch-2209-43-0.sql' => 'database/schema/archive/patch-2209-43-0.sql'
140=== renamed file 'database/schema/patch-2209-44-0.sql' => 'database/schema/archive/patch-2209-44-0.sql'
141=== renamed file 'database/schema/patch-2209-44-1.sql' => 'database/schema/archive/patch-2209-44-1.sql'
142=== renamed file 'database/schema/patch-2209-44-2.sql' => 'database/schema/archive/patch-2209-44-2.sql'
143=== renamed file 'database/schema/patch-2209-44-3.sql' => 'database/schema/archive/patch-2209-44-3.sql'
144=== renamed file 'database/schema/patch-2209-44-4.sql' => 'database/schema/archive/patch-2209-44-4.sql'
145=== renamed file 'database/schema/patch-2209-45-0.sql' => 'database/schema/archive/patch-2209-45-0.sql'
146=== renamed file 'database/schema/patch-2209-46-0.sql' => 'database/schema/archive/patch-2209-46-0.sql'
147=== renamed file 'database/schema/patch-2209-47-0.sql' => 'database/schema/archive/patch-2209-47-0.sql'
148=== renamed file 'database/schema/patch-2209-47-1.sql' => 'database/schema/archive/patch-2209-47-1.sql'
149=== renamed file 'database/schema/patch-2209-48-0.sql' => 'database/schema/archive/patch-2209-48-0.sql'
150=== renamed file 'database/schema/patch-2209-49-0.sql' => 'database/schema/archive/patch-2209-49-0.sql'
151=== renamed file 'database/schema/patch-2209-49-1.sql' => 'database/schema/archive/patch-2209-49-1.sql'
152=== renamed file 'database/schema/patch-2209-49-2.sql' => 'database/schema/archive/patch-2209-49-2.sql'
153=== renamed file 'database/schema/patch-2209-50-0.sql' => 'database/schema/archive/patch-2209-50-0.sql'
154=== renamed file 'database/schema/patch-2209-51-0.sql' => 'database/schema/archive/patch-2209-51-0.sql'
155=== renamed file 'database/schema/patch-2209-51-1.sql' => 'database/schema/archive/patch-2209-51-1.sql'
156=== renamed file 'database/schema/patch-2209-51-2.sql' => 'database/schema/archive/patch-2209-51-2.sql'
157=== renamed file 'database/schema/patch-2209-52-0.sql' => 'database/schema/archive/patch-2209-52-0.sql'
158=== renamed file 'database/schema/patch-2209-53-0.sql' => 'database/schema/archive/patch-2209-53-0.sql'
159=== renamed file 'database/schema/patch-2209-53-1.sql' => 'database/schema/archive/patch-2209-53-1.sql'
160=== renamed file 'database/schema/patch-2209-53-3.sql' => 'database/schema/archive/patch-2209-53-3.sql'
161=== renamed file 'database/schema/patch-2209-53-4.sql' => 'database/schema/archive/patch-2209-53-4.sql'
162=== renamed file 'database/schema/patch-2209-53-5.sql' => 'database/schema/archive/patch-2209-53-5.sql'
163=== renamed file 'database/schema/patch-2209-53-6.sql' => 'database/schema/archive/patch-2209-53-6.sql'
164=== renamed file 'database/schema/patch-2209-53-7.sql' => 'database/schema/archive/patch-2209-53-7.sql'
165=== renamed file 'database/schema/patch-2209-53-8.sql' => 'database/schema/archive/patch-2209-53-8.sql'
166=== renamed file 'database/schema/patch-2209-53-9.sql' => 'database/schema/archive/patch-2209-53-9.sql'
167=== renamed file 'database/schema/patch-2209-54-0.sql' => 'database/schema/archive/patch-2209-54-0.sql'
168=== renamed file 'database/schema/patch-2209-55-0.sql' => 'database/schema/archive/patch-2209-55-0.sql'
169=== renamed file 'database/schema/patch-2209-56-0.sql' => 'database/schema/archive/patch-2209-56-0.sql'
170=== renamed file 'database/schema/patch-2209-56-1.sql' => 'database/schema/archive/patch-2209-56-1.sql'
171=== renamed file 'database/schema/patch-2209-56-2.sql' => 'database/schema/archive/patch-2209-56-2.sql'
172=== renamed file 'database/schema/patch-2209-56-3.sql' => 'database/schema/archive/patch-2209-56-3.sql'
173=== renamed file 'database/schema/patch-2209-56-4.sql' => 'database/schema/archive/patch-2209-56-4.sql'
174=== renamed file 'database/schema/patch-2209-57-0.sql' => 'database/schema/archive/patch-2209-57-0.sql'
175=== renamed file 'database/schema/patch-2209-58-0.sql' => 'database/schema/archive/patch-2209-58-0.sql'
176=== renamed file 'database/schema/patch-2209-58-1.sql' => 'database/schema/archive/patch-2209-58-1.sql'
177=== renamed file 'database/schema/patch-2209-58-2.sql' => 'database/schema/archive/patch-2209-58-2.sql'
178=== renamed file 'database/schema/patch-2209-58-3.sql' => 'database/schema/archive/patch-2209-58-3.sql'
179=== renamed file 'database/schema/patch-2209-58-4.sql' => 'database/schema/archive/patch-2209-58-4.sql'
180=== renamed file 'database/schema/patch-2209-59-0.sql' => 'database/schema/archive/patch-2209-59-0.sql'
181=== renamed file 'database/schema/patch-2209-59-1.sql' => 'database/schema/archive/patch-2209-59-1.sql'
182=== renamed file 'database/schema/patch-2209-59-2.sql' => 'database/schema/archive/patch-2209-59-2.sql'
183=== renamed file 'database/schema/patch-2209-60-0.sql' => 'database/schema/archive/patch-2209-60-0.sql'
184=== renamed file 'database/schema/patch-2209-61-0.sql' => 'database/schema/archive/patch-2209-61-0.sql'
185=== renamed file 'database/schema/patch-2209-61-1.sql' => 'database/schema/archive/patch-2209-61-1.sql'
186=== renamed file 'database/schema/patch-2209-61-2.sql' => 'database/schema/archive/patch-2209-61-2.sql'
187=== renamed file 'database/schema/patch-2209-61-3.sql' => 'database/schema/archive/patch-2209-61-3.sql'
188=== renamed file 'database/schema/patch-2209-61-4.sql' => 'database/schema/archive/patch-2209-61-4.sql'
189=== renamed file 'database/schema/patch-2209-61-5.sql' => 'database/schema/archive/patch-2209-61-5.sql'
190=== renamed file 'database/schema/patch-2209-61-6.sql' => 'database/schema/archive/patch-2209-61-6.sql'
191=== renamed file 'database/schema/patch-2209-61-7.sql' => 'database/schema/archive/patch-2209-61-7.sql'
192=== renamed file 'database/schema/patch-2209-61-8.sql' => 'database/schema/archive/patch-2209-61-8.sql'
193=== renamed file 'database/schema/patch-2209-61-9.sql' => 'database/schema/archive/patch-2209-61-9.sql'
194=== renamed file 'database/schema/patch-2209-62-0.sql' => 'database/schema/archive/patch-2209-62-0.sql'
195=== renamed file 'database/schema/patch-2209-62-1.sql' => 'database/schema/archive/patch-2209-62-1.sql'
196=== renamed file 'database/schema/patch-2209-64-0.sql' => 'database/schema/archive/patch-2209-64-0.sql'
197=== renamed file 'database/schema/patch-2209-64-1.sql' => 'database/schema/archive/patch-2209-64-1.sql'
198=== renamed file 'database/schema/patch-2209-65-0.sql' => 'database/schema/archive/patch-2209-65-0.sql'
199=== renamed file 'database/schema/patch-2209-66-0.sql' => 'database/schema/archive/patch-2209-66-0.sql'
200=== renamed file 'database/schema/patch-2209-66-1.sql' => 'database/schema/archive/patch-2209-66-1.sql'
201=== renamed file 'database/schema/patch-2209-67-0.sql' => 'database/schema/archive/patch-2209-67-0.sql'
202=== renamed file 'database/schema/patch-2209-67-1.sql' => 'database/schema/archive/patch-2209-67-1.sql'
203=== renamed file 'database/schema/patch-2209-67-2.sql' => 'database/schema/archive/patch-2209-67-2.sql'
204=== renamed file 'database/schema/patch-2209-67-3.sql' => 'database/schema/archive/patch-2209-67-3.sql'
205=== renamed file 'database/schema/patch-2209-68-0.sql' => 'database/schema/archive/patch-2209-68-0.sql'
206=== renamed file 'database/schema/patch-2209-68-1.sql' => 'database/schema/archive/patch-2209-68-1.sql'
207=== renamed file 'database/schema/patch-2209-68-2.sql' => 'database/schema/archive/patch-2209-68-2.sql'
208=== renamed file 'database/schema/patch-2209-69-0.sql' => 'database/schema/archive/patch-2209-69-0.sql'
209=== renamed file 'database/schema/patch-2209-69-1.sql' => 'database/schema/archive/patch-2209-69-1.sql'
210=== renamed file 'database/schema/patch-2209-69-2.sql' => 'database/schema/archive/patch-2209-69-2.sql'
211=== renamed file 'database/schema/patch-2209-69-3.sql' => 'database/schema/archive/patch-2209-69-3.sql'
212=== renamed file 'database/schema/patch-2209-69-4.sql' => 'database/schema/archive/patch-2209-69-4.sql'
213=== renamed file 'database/schema/patch-2209-69-5.sql' => 'database/schema/archive/patch-2209-69-5.sql'
214=== renamed file 'database/schema/patch-2209-69-6.sql' => 'database/schema/archive/patch-2209-69-6.sql'
215=== renamed file 'database/schema/patch-2209-69-7.sql' => 'database/schema/archive/patch-2209-69-7.sql'
216=== renamed file 'database/schema/patch-2209-69-8.sql' => 'database/schema/archive/patch-2209-69-8.sql'
217=== renamed file 'database/schema/patch-2209-69-9.sql' => 'database/schema/archive/patch-2209-69-9.sql'
218=== renamed file 'database/schema/patch-2209-70-0.sql' => 'database/schema/archive/patch-2209-70-0.sql'
219=== renamed file 'database/schema/patch-2209-71-0.sql' => 'database/schema/archive/patch-2209-71-0.sql'
220=== renamed file 'database/schema/patch-2209-71-1.sql' => 'database/schema/archive/patch-2209-71-1.sql'
221=== renamed file 'database/schema/patch-2209-72-0.sql' => 'database/schema/archive/patch-2209-72-0.sql'
222=== renamed file 'database/schema/patch-2209-73-0.sql' => 'database/schema/archive/patch-2209-73-0.sql'
223=== renamed file 'database/schema/patch-2209-73-1.sql' => 'database/schema/archive/patch-2209-73-1.sql'
224=== renamed file 'database/schema/patch-2209-74-0.sql' => 'database/schema/archive/patch-2209-74-0.sql'
225=== renamed file 'database/schema/patch-2209-75-0.sql' => 'database/schema/archive/patch-2209-75-0.sql'
226=== renamed file 'database/schema/patch-2209-75-1.sql' => 'database/schema/archive/patch-2209-75-1.sql'
227=== renamed file 'database/schema/patch-2209-77-0.sql' => 'database/schema/archive/patch-2209-77-0.sql'
228=== renamed file 'database/schema/patch-2209-77-1.sql' => 'database/schema/archive/patch-2209-77-1.sql'
229=== renamed file 'database/schema/patch-2209-77-2.sql' => 'database/schema/archive/patch-2209-77-2.sql'
230=== renamed file 'database/schema/patch-2209-77-3.sql' => 'database/schema/archive/patch-2209-77-3.sql'
231=== renamed file 'database/schema/patch-2209-78-0.sql' => 'database/schema/archive/patch-2209-78-0.sql'
232=== renamed file 'database/schema/patch-2209-78-1.sql' => 'database/schema/archive/patch-2209-78-1.sql'
233=== renamed file 'database/schema/patch-2209-78-2.sql' => 'database/schema/archive/patch-2209-78-2.sql'
234=== renamed file 'database/schema/patch-2209-79-0.sql' => 'database/schema/archive/patch-2209-79-0.sql'
235=== renamed file 'database/schema/patch-2209-80-0.sql' => 'database/schema/archive/patch-2209-80-0.sql'
236=== renamed file 'database/schema/patch-2209-80-1.sql' => 'database/schema/archive/patch-2209-80-1.sql'
237=== renamed file 'database/schema/patch-2209-80-2.sql' => 'database/schema/archive/patch-2209-80-2.sql'
238=== renamed file 'database/schema/patch-2209-81-0.sql' => 'database/schema/archive/patch-2209-81-0.sql'
239=== renamed file 'database/schema/patch-2209-82-0.sql' => 'database/schema/archive/patch-2209-82-0.sql'
240=== renamed file 'database/schema/patch-2209-82-1.sql' => 'database/schema/archive/patch-2209-82-1.sql'
241=== renamed file 'database/schema/patch-2209-83-0.sql' => 'database/schema/archive/patch-2209-83-0.sql'
242=== renamed file 'database/schema/patch-2209-83-1.sql' => 'database/schema/archive/patch-2209-83-1.sql'
243=== renamed file 'database/schema/patch-2209-83-2.sql' => 'database/schema/archive/patch-2209-83-2.sql'
244=== renamed file 'database/schema/patch-2209-83-3.sql' => 'database/schema/archive/patch-2209-83-3.sql'
245=== renamed file 'database/schema/patch-2209-83-4.sql' => 'database/schema/archive/patch-2209-83-4.sql'
246=== renamed file 'database/schema/patch-2209-83-5.sql' => 'database/schema/archive/patch-2209-83-5.sql'
247=== renamed file 'database/schema/patch-2209-83-6.sql' => 'database/schema/archive/patch-2209-83-6.sql'
248=== renamed file 'database/schema/patch-2209-84-0.sql' => 'database/schema/archive/patch-2209-84-0.sql'
249=== renamed file 'database/schema/patch-2209-84-1.sql' => 'database/schema/archive/patch-2209-84-1.sql'
250=== renamed file 'database/schema/patch-2209-85-0.sql' => 'database/schema/archive/patch-2209-85-0.sql'
251=== renamed file 'database/schema/patch-2209-85-1.sql' => 'database/schema/archive/patch-2209-85-1.sql'
252=== renamed file 'database/schema/patch-2209-86-0.sql' => 'database/schema/archive/patch-2209-86-0.sql'
253=== renamed file 'database/schema/launchpad-2209-00-0.sql' => 'database/schema/launchpad-2210-00-0.sql'
254--- database/schema/launchpad-2209-00-0.sql 2015-07-21 09:04:01 +0000
255+++ database/schema/launchpad-2210-00-0.sql 2019-02-26 07:46:16 +0000
256@@ -1,9 +1,11 @@
257--- Generated Tue Dec 6 20:57:32 2011 UTC
258+-- Generated Mon Feb 25 21:35:23 2019 UTC
259
260 SET client_min_messages TO ERROR;
261 SET statement_timeout = 0;
262+SET lock_timeout = 0;
263 SET client_encoding = 'UTF8';
264 SET standard_conforming_strings = off;
265+SELECT pg_catalog.set_config('search_path', '', false);
266 SET check_function_bodies = false;
267 SET client_min_messages = warning;
268 SET escape_string_warning = off;
269@@ -11,56 +13,72 @@
270 CREATE SCHEMA todrop;
271
272
273-CREATE SCHEMA ts2;
274-
275-
276-CREATE PROCEDURAL LANGUAGE plpgsql;
277-
278-
279-CREATE PROCEDURAL LANGUAGE plpythonu;
280-
281-
282-SET search_path = public, pg_catalog;
283-
284-CREATE TYPE debversion;
285-
286-
287-CREATE FUNCTION debversionin(cstring) RETURNS debversion
288+CREATE SCHEMA trgm;
289+
290+
291+CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
292+
293+
294+COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
295+
296+
297+CREATE EXTENSION IF NOT EXISTS plpythonu WITH SCHEMA pg_catalog;
298+
299+
300+COMMENT ON EXTENSION plpythonu IS 'PL/PythonU untrusted procedural language';
301+
302+
303+CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA trgm;
304+
305+
306+COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';
307+
308+
309+CREATE EXTENSION IF NOT EXISTS pgstattuple WITH SCHEMA public;
310+
311+
312+COMMENT ON EXTENSION pgstattuple IS 'show tuple-level statistics';
313+
314+
315+CREATE TYPE public.debversion;
316+
317+
318+CREATE FUNCTION public.debversionin(cstring) RETURNS public.debversion
319 LANGUAGE internal IMMUTABLE STRICT
320 AS $$textin$$;
321
322
323-CREATE FUNCTION debversionout(debversion) RETURNS cstring
324+CREATE FUNCTION public.debversionout(public.debversion) RETURNS cstring
325 LANGUAGE internal IMMUTABLE STRICT
326 AS $$textout$$;
327
328
329-CREATE FUNCTION debversionrecv(internal) RETURNS debversion
330+CREATE FUNCTION public.debversionrecv(internal) RETURNS public.debversion
331 LANGUAGE internal STABLE STRICT
332 AS $$textrecv$$;
333
334
335-CREATE FUNCTION debversionsend(debversion) RETURNS bytea
336+CREATE FUNCTION public.debversionsend(public.debversion) RETURNS bytea
337 LANGUAGE internal STABLE STRICT
338 AS $$textsend$$;
339
340
341-CREATE TYPE debversion (
342+CREATE TYPE public.debversion (
343 INTERNALLENGTH = variable,
344- INPUT = debversionin,
345- OUTPUT = debversionout,
346- RECEIVE = debversionrecv,
347- SEND = debversionsend,
348+ INPUT = public.debversionin,
349+ OUTPUT = public.debversionout,
350+ RECEIVE = public.debversionrecv,
351+ SEND = public.debversionsend,
352 CATEGORY = 'S',
353 ALIGNMENT = int4,
354 STORAGE = extended
355 );
356
357
358-COMMENT ON TYPE debversion IS 'Debian package version number';
359-
360-
361-CREATE TYPE pgstattuple_type AS (
362+COMMENT ON TYPE public.debversion IS 'Debian package version number';
363+
364+
365+CREATE TYPE public.pgstattuple_type AS (
366 table_len bigint,
367 tuple_count bigint,
368 tuple_len bigint,
369@@ -73,77 +91,337 @@
370 );
371
372
373-SET search_path = ts2, pg_catalog;
374-
375-CREATE DOMAIN gtsq AS text;
376-
377-
378-CREATE DOMAIN gtsvector AS pg_catalog.gtsvector;
379-
380-
381-CREATE TYPE statinfo AS (
382- word text,
383- ndoc integer,
384- nentry integer
385-);
386-
387-
388-CREATE TYPE tokenout AS (
389- tokid integer,
390- token text
391-);
392-
393-
394-CREATE TYPE tokentype AS (
395- tokid integer,
396- alias text,
397- descr text
398-);
399-
400-
401-CREATE TYPE tsdebug AS (
402- ts_name text,
403- tok_type text,
404- description text,
405- token text,
406- dict_name text[],
407- tsvector pg_catalog.tsvector
408-);
409-
410-
411-CREATE DOMAIN tsquery AS pg_catalog.tsquery;
412-
413-
414-CREATE DOMAIN tsvector AS pg_catalog.tsvector;
415-
416-
417-SET search_path = public, pg_catalog;
418-
419-CREATE FUNCTION activity() RETURNS SETOF pg_stat_activity
420- LANGUAGE sql SECURITY DEFINER
421- SET search_path TO public
422- AS $$
423- SELECT
424- datid, datname, procpid, usesysid, usename,
425- CASE
426- WHEN current_query LIKE '<IDLE>%'
427- OR current_query LIKE 'autovacuum:%'
428- THEN current_query
429- ELSE
430- '<HIDDEN>'
431- END AS current_query,
432- waiting, xact_start, query_start,
433- backend_start, client_addr, client_port
434- FROM pg_catalog.pg_stat_activity;
435-$$;
436-
437-
438-COMMENT ON FUNCTION activity() IS 'SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.';
439-
440-
441-CREATE FUNCTION add_test_openid_identifier(account_ integer) RETURNS boolean
442- LANGUAGE plpgsql SECURITY DEFINER
443- SET search_path TO public
444+CREATE DOMAIN public.ts2_tsvector AS tsvector;
445+
446+
447+CREATE FUNCTION public._ftq(text) RETURNS text
448+ LANGUAGE plpythonu IMMUTABLE STRICT
449+ AS $_$
450+ import re
451+
452+ # I think this method would be more robust if we used a real
453+ # tokenizer and parser to generate the query string, but we need
454+ # something suitable for use as a stored procedure which currently
455+ # means no external dependancies.
456+
457+ # Convert to Unicode
458+ query = args[0].decode('utf8')
459+ ## plpy.debug('1 query is %s' % repr(query))
460+
461+ # Replace tsquery operators with ' '. '<' begins all the phrase
462+ # search operators, and a standalone '>' is fine.
463+ query = re.sub('[|&!<]', ' ', query)
464+
465+ # Normalize whitespace
466+ query = re.sub("(?u)\s+"," ", query)
467+
468+ # Convert AND, OR, NOT to tsearch2 punctuation
469+ query = re.sub(r"(?u)\bAND\b", "&", query)
470+ query = re.sub(r"(?u)\bOR\b", "|", query)
471+ query = re.sub(r"(?u)\bNOT\b", " !", query)
472+ ## plpy.debug('2 query is %s' % repr(query))
473+
474+ # Deal with unwanted punctuation.
475+ # ':' is used in queries to specify a weight of a word.
476+ # '\' is treated differently in to_tsvector() and to_tsquery().
477+ punctuation = r'[:\\]'
478+ query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
479+ ## plpy.debug('3 query is %s' % repr(query))
480+
481+ # Now that we have handle case sensitive booleans, convert to lowercase
482+ query = query.lower()
483+
484+ # Remove unpartnered bracket on the left and right
485+ query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
486+ query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
487+
488+ # Remove spurious brackets
489+ query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
490+ ## plpy.debug('5 query is %s' % repr(query))
491+
492+ # Insert & between tokens without an existing boolean operator
493+ # ( not proceeded by (|&!
494+ query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
495+ ## plpy.debug('6 query is %s' % repr(query))
496+ # ) not followed by )|&
497+ query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
498+ ## plpy.debug('6.1 query is %s' % repr(query))
499+ # Whitespace not proceded by (|&! not followed by &|
500+ query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
501+ ## plpy.debug('7 query is %s' % repr(query))
502+
503+ # Detect and repair syntax errors - we are lenient because
504+ # this input is generally from users.
505+
506+ # Fix unbalanced brackets
507+ openings = query.count("(")
508+ closings = query.count(")")
509+ if openings > closings:
510+ query = query + " ) "*(openings-closings)
511+ elif closings > openings:
512+ query = " ( "*(closings-openings) + query
513+ ## plpy.debug('8 query is %s' % repr(query))
514+
515+ # Strip ' character that do not have letters on both sides
516+ query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
517+
518+ # Brackets containing nothing but whitespace and booleans, recursive
519+ last = ""
520+ while last != query:
521+ last = query
522+ query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
523+ ## plpy.debug('9 query is %s' % repr(query))
524+
525+ # An & or | following a (
526+ query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
527+ ## plpy.debug('10 query is %s' % repr(query))
528+
529+ # An &, | or ! immediatly before a )
530+ query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
531+ ## plpy.debug('11 query is %s' % repr(query))
532+
533+ # An &,| or ! followed by another boolean.
534+ query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
535+ ## plpy.debug('12 query is %s' % repr(query))
536+
537+ # Leading & or |
538+ query = re.sub(r"(?u)^[\s\&\|]+", "", query)
539+ ## plpy.debug('13 query is %s' % repr(query))
540+
541+ # Trailing &, | or !
542+ query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
543+ ## plpy.debug('14 query is %s' % repr(query))
544+
545+ # If we have nothing but whitespace and tsearch2 operators,
546+ # return NULL.
547+ if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
548+ return None
549+
550+ # Convert back to UTF-8
551+ query = query.encode('utf8')
552+ ## plpy.debug('15 query is %s' % repr(query))
553+
554+ return query or None
555+ $_$;
556+
557+
558+CREATE FUNCTION public.accessartifact_denorm_to_artifacts(artifact_id integer) RETURNS void
559+ LANGUAGE plpgsql
560+ AS $$
561+DECLARE
562+ artifact_row accessartifact%ROWTYPE;
563+BEGIN
564+ SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
565+ IF artifact_row.bug IS NOT NULL THEN
566+ PERFORM bug_flatten_access(artifact_row.bug);
567+ END IF;
568+ IF artifact_row.branch IS NOT NULL THEN
569+ PERFORM branch_denorm_access(artifact_row.branch);
570+ END IF;
571+ IF artifact_row.gitrepository IS NOT NULL THEN
572+ PERFORM gitrepository_denorm_access(artifact_row.gitrepository);
573+ END IF;
574+ IF artifact_row.specification IS NOT NULL THEN
575+ PERFORM specification_denorm_access(artifact_row.specification);
576+ END IF;
577+ RETURN;
578+END;
579+$$;
580+
581+
582+COMMENT ON FUNCTION public.accessartifact_denorm_to_artifacts(artifact_id integer) IS 'Denormalize the policy access and artifact grants to bugs, branches, Git repositories, and specifications.';
583+
584+
585+CREATE FUNCTION public.accessartifact_maintain_denorm_to_artifacts_trig() RETURNS trigger
586+ LANGUAGE plpgsql
587+ AS $$
588+BEGIN
589+ IF TG_OP = 'INSERT' THEN
590+ PERFORM accessartifact_denorm_to_artifacts(NEW.artifact);
591+ ELSIF TG_OP = 'UPDATE' THEN
592+ PERFORM accessartifact_denorm_to_artifacts(NEW.artifact);
593+ IF OLD.artifact != NEW.artifact THEN
594+ PERFORM accessartifact_denorm_to_artifacts(OLD.artifact);
595+ END IF;
596+ ELSIF TG_OP = 'DELETE' THEN
597+ PERFORM accessartifact_denorm_to_artifacts(OLD.artifact);
598+ END IF;
599+ RETURN NULL;
600+END;
601+$$;
602+
603+
604+CREATE FUNCTION public.accessartifactgrant_maintain_accesspolicygrantflat_trig() RETURNS trigger
605+ LANGUAGE plpgsql SECURITY DEFINER
606+ SET search_path TO 'public'
607+ AS $$
608+BEGIN
609+ IF TG_OP = 'INSERT' THEN
610+ INSERT INTO AccessPolicyGrantFlat
611+ (policy, artifact, grantee)
612+ SELECT policy, NEW.artifact, NEW.grantee
613+ FROM AccessPolicyArtifact WHERE artifact = NEW.artifact;
614+ ELSIF TG_OP = 'UPDATE' THEN
615+ IF NEW.artifact != OLD.artifact OR NEW.grantee != OLD.grantee THEN
616+ UPDATE AccessPolicyGrantFlat
617+ SET artifact=NEW.artifact, grantee=NEW.grantee
618+ WHERE artifact = OLD.artifact AND grantee = OLD.grantee;
619+ END IF;
620+ ELSIF TG_OP = 'DELETE' THEN
621+ DELETE FROM AccessPolicyGrantFlat
622+ WHERE artifact = OLD.artifact AND grantee = OLD.grantee;
623+ END IF;
624+ RETURN NULL;
625+END;
626+$$;
627+
628+
629+CREATE FUNCTION public.accesspolicyartifact_maintain_accesspolicyartifactflat_trig() RETURNS trigger
630+ LANGUAGE plpgsql SECURITY DEFINER
631+ SET search_path TO 'public'
632+ AS $$
633+BEGIN
634+ IF TG_OP = 'INSERT' THEN
635+ INSERT INTO AccessPolicyGrantFlat
636+ (policy, artifact, grantee)
637+ SELECT NEW.policy, NEW.artifact, grantee
638+ FROM AccessArtifactGrant WHERE artifact = NEW.artifact;
639+ ELSIF TG_OP = 'UPDATE' THEN
640+ IF NEW.policy != OLD.policy OR NEW.artifact != OLD.artifact THEN
641+ UPDATE AccessPolicyGrantFlat
642+ SET policy=NEW.policy, artifact=NEW.artifact
643+ WHERE policy = OLD.policy AND artifact = OLD.artifact;
644+ END IF;
645+ ELSIF TG_OP = 'DELETE' THEN
646+ DELETE FROM AccessPolicyGrantFlat
647+ WHERE policy = OLD.policy AND artifact = OLD.artifact;
648+ END IF;
649+ RETURN NULL;
650+END;
651+$$;
652+
653+
654+CREATE FUNCTION public.accesspolicygrant_maintain_accesspolicygrantflat_trig() RETURNS trigger
655+ LANGUAGE plpgsql SECURITY DEFINER
656+ SET search_path TO 'public'
657+ AS $$
658+BEGIN
659+ IF TG_OP = 'INSERT' THEN
660+ INSERT INTO AccessPolicyGrantFlat
661+ (policy, grantee) VALUES (NEW.policy, NEW.grantee);
662+ ELSIF TG_OP = 'UPDATE' THEN
663+ IF NEW.policy != OLD.policy OR NEW.grantee != OLD.grantee THEN
664+ UPDATE AccessPolicyGrantFlat
665+ SET policy=NEW.policy, grantee=NEW.grantee
666+ WHERE
667+ policy = OLD.policy
668+ AND grantee = OLD.grantee
669+ AND artifact IS NULL;
670+ END IF;
671+ ELSIF TG_OP = 'DELETE' THEN
672+ DELETE FROM AccessPolicyGrantFlat
673+ WHERE
674+ policy = OLD.policy
675+ AND grantee = OLD.grantee
676+ AND artifact IS NULL;
677+ END IF;
678+ RETURN NULL;
679+END;
680+$$;
681+
682+
683+CREATE FUNCTION public.activity() RETURNS SETOF pg_stat_activity
684+ LANGUAGE plpgsql SECURITY DEFINER
685+ SET search_path TO 'public'
686+ AS $$
687+DECLARE
688+ a pg_stat_activity%ROWTYPE;
689+BEGIN
690+ IF EXISTS (
691+ SELECT 1 FROM pg_attribute WHERE
692+ attrelid =
693+ (SELECT oid FROM pg_class
694+ WHERE relname = 'pg_stat_activity')
695+ AND attname = 'backend_type') THEN
696+ -- >= 10
697+ RETURN QUERY SELECT
698+ datid, datname, pid, usesysid, usename, application_name,
699+ client_addr, client_hostname, client_port, backend_start,
700+ xact_start, query_start, state_change, wait_event_type,
701+ wait_event, state, backend_xid, backend_xmin, backend_type,
702+ CASE
703+ WHEN query LIKE '<IDLE>%'
704+ OR query LIKE 'autovacuum:%'
705+ THEN query
706+ ELSE
707+ '<HIDDEN>'
708+ END AS query
709+ FROM pg_catalog.pg_stat_activity;
710+ ELSIF EXISTS (
711+ SELECT 1 FROM pg_attribute WHERE
712+ attrelid =
713+ (SELECT oid FROM pg_class
714+ WHERE relname = 'pg_stat_activity')
715+ AND attname = 'wait_event_type') THEN
716+ -- >= 9.6
717+ RETURN QUERY SELECT
718+ datid, datname, pid, usesysid, usename, application_name,
719+ client_addr, client_hostname, client_port, backend_start,
720+ xact_start, query_start, state_change, wait_event_type,
721+ wait_event, state, backend_xid, backend_xmin,
722+ CASE
723+ WHEN query LIKE '<IDLE>%'
724+ OR query LIKE 'autovacuum:%'
725+ THEN query
726+ ELSE
727+ '<HIDDEN>'
728+ END AS query
729+ FROM pg_catalog.pg_stat_activity;
730+ ELSIF EXISTS (
731+ SELECT 1 FROM pg_attribute WHERE
732+ attrelid =
733+ (SELECT oid FROM pg_class
734+ WHERE relname = 'pg_stat_activity')
735+ AND attname = 'backend_xid') THEN
736+ -- >= 9.4
737+ RETURN QUERY SELECT
738+ datid, datname, pid, usesysid, usename, application_name,
739+ client_addr, client_hostname, client_port, backend_start,
740+ xact_start, query_start, state_change, waiting, state,
741+ backend_xid, backend_xmin,
742+ CASE
743+ WHEN query LIKE '<IDLE>%'
744+ OR query LIKE 'autovacuum:%'
745+ THEN query
746+ ELSE
747+ '<HIDDEN>'
748+ END AS query
749+ FROM pg_catalog.pg_stat_activity;
750+ ELSE
751+ -- >= 9.2; anything older is unsupported
752+ RETURN QUERY SELECT
753+ datid, datname, pid, usesysid, usename, application_name,
754+ client_addr, client_hostname, client_port, backend_start,
755+ xact_start, query_start, state_change, waiting, state,
756+ CASE
757+ WHEN query LIKE '<IDLE>%'
758+ OR query LIKE 'autovacuum:%'
759+ THEN query
760+ ELSE
761+ '<HIDDEN>'
762+ END AS query
763+ FROM pg_catalog.pg_stat_activity;
764+ END IF;
765+END;
766+$$;
767+
768+
769+COMMENT ON FUNCTION public.activity() IS 'SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.';
770+
771+
772+CREATE FUNCTION public.add_test_openid_identifier(account_ integer) RETURNS boolean
773+ LANGUAGE plpgsql SECURITY DEFINER
774+ SET search_path TO 'public'
775 AS $$
776 BEGIN
777 -- The generated OpenIdIdentifier is not a valid OpenId Identity URL
778@@ -161,10 +439,10 @@
779 $$;
780
781
782-COMMENT ON FUNCTION add_test_openid_identifier(account_ integer) IS '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.';
783-
784-
785-CREATE FUNCTION assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean
786+COMMENT ON FUNCTION public.add_test_openid_identifier(account_ integer) IS '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.';
787+
788+
789+CREATE FUNCTION public.assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean
790 LANGUAGE plpythonu STABLE
791 AS $$
792 rv = plpy.execute("""
793@@ -179,39 +457,98 @@
794 $$;
795
796
797-COMMENT ON FUNCTION assert_patch_applied(major integer, minor integer, patch integer) IS 'Raise an exception if the given database patch has not been applied.';
798-
799-
800-CREATE FUNCTION bug_maintain_bug_summary() RETURNS trigger
801+COMMENT ON FUNCTION public.assert_patch_applied(major integer, minor integer, patch integer) IS 'Raise an exception if the given database patch has not been applied.';
802+
803+
804+CREATE FUNCTION public.branch_denorm_access(branch_id integer) RETURNS void
805+ LANGUAGE sql SECURITY DEFINER
806+ SET search_path TO 'public'
807+ AS $_$
808+ UPDATE branch
809+ SET access_policy = policies[1], access_grants = grants
810+ FROM
811+ build_access_cache(
812+ (SELECT id FROM accessartifact WHERE branch = $1),
813+ (SELECT information_type FROM branch WHERE id = $1))
814+ AS (policies integer[], grants integer[])
815+ WHERE id = $1;
816+$_$;
817+
818+
819+CREATE FUNCTION public.branch_maintain_access_cache_trig() RETURNS trigger
820+ LANGUAGE plpgsql
821+ AS $$
822+BEGIN
823+ PERFORM branch_denorm_access(NEW.id);
824+ RETURN NULL;
825+END;
826+$$;
827+
828+
829+CREATE FUNCTION public.bug_build_access_cache(bug_id integer, information_type integer) RETURNS record
830+ LANGUAGE sql
831+ AS $_$
832+ SELECT build_access_cache(
833+ (SELECT id FROM accessartifact WHERE bug = $1), $2);
834+$_$;
835+
836+
837+COMMENT ON FUNCTION public.bug_build_access_cache(bug_id integer, information_type integer) IS 'Build an access cache for the given bug. Returns ({AccessPolicyArtifact.policy}, {AccessArtifactGrant.grantee}) for private bugs, or (NULL, NULL) for public ones.';
838+
839+
840+CREATE FUNCTION public.bug_flatten_access(bug_id integer) RETURNS void
841+ LANGUAGE sql SECURITY DEFINER
842+ SET search_path TO 'public'
843+ AS $_$
844+ UPDATE bugtaskflat
845+ SET access_policies = policies, access_grants = grants
846+ FROM
847+ build_access_cache(
848+ (SELECT id FROM accessartifact WHERE bug = $1),
849+ (SELECT information_type FROM bug WHERE id = $1))
850+ AS (policies integer[], grants integer[])
851+ WHERE bug = $1;
852+$_$;
853+
854+
855+COMMENT ON FUNCTION public.bug_flatten_access(bug_id integer) IS 'Recalculate the access cache on a bug''s flattened tasks.';
856+
857+
858+CREATE FUNCTION public.bug_maintain_bugtaskflat_trig() RETURNS trigger
859 LANGUAGE plpgsql SECURITY DEFINER
860- SET search_path TO public
861+ SET search_path TO 'public'
862 AS $$
863 BEGIN
864- -- There is no INSERT logic, as a bug will not have any summary
865- -- information until BugTask rows have been attached.
866- IF TG_OP = 'UPDATE' THEN
867- IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof
868- OR OLD.private IS DISTINCT FROM NEW.private
869- OR (OLD.latest_patch_uploaded IS NULL)
870- <> (NEW.latest_patch_uploaded IS NULL) THEN
871- PERFORM unsummarise_bug(OLD);
872- PERFORM summarise_bug(NEW);
873- END IF;
874-
875- ELSIF TG_OP = 'DELETE' THEN
876- PERFORM unsummarise_bug(OLD);
877- END IF;
878-
879- PERFORM bug_summary_flush_temp_journal();
880- RETURN NULL; -- Ignored - this is an AFTER trigger
881+ IF (
882+ NEW.duplicateof IS DISTINCT FROM OLD.duplicateof
883+ OR NEW.owner IS DISTINCT FROM OLD.owner
884+ OR NEW.fti IS DISTINCT FROM OLD.fti
885+ OR NEW.information_type IS DISTINCT FROM OLD.information_type
886+ OR NEW.date_last_updated IS DISTINCT FROM OLD.date_last_updated
887+ OR NEW.heat IS DISTINCT FROM OLD.heat
888+ OR NEW.latest_patch_uploaded IS DISTINCT FROM
889+ OLD.latest_patch_uploaded) THEN
890+ UPDATE bugtaskflat
891+ SET
892+ duplicateof = NEW.duplicateof,
893+ bug_owner = NEW.owner,
894+ fti = NEW.fti,
895+ information_type = NEW.information_type,
896+ date_last_updated = NEW.date_last_updated,
897+ heat = NEW.heat,
898+ latest_patch_uploaded = NEW.latest_patch_uploaded
899+ WHERE bug = OLD.id;
900+ END IF;
901+
902+ IF NEW.information_type IS DISTINCT FROM OLD.information_type THEN
903+ PERFORM bug_flatten_access(OLD.id);
904+ END IF;
905+ RETURN NULL;
906 END;
907 $$;
908
909
910-COMMENT ON FUNCTION bug_maintain_bug_summary() IS 'AFTER trigger on bug maintaining the bugs summaries in bugsummary.';
911-
912-
913-CREATE FUNCTION valid_bug_name(text) RETURNS boolean
914+CREATE FUNCTION public.valid_bug_name(text) RETURNS boolean
915 LANGUAGE plpythonu IMMUTABLE STRICT
916 AS $_$
917 import re
918@@ -223,7 +560,7 @@
919 $_$;
920
921
922-COMMENT ON FUNCTION valid_bug_name(text) IS 'validate a bug name
923+COMMENT ON FUNCTION public.valid_bug_name(text) IS 'validate a bug name
924
925 As per valid_name, except numeric-only names are not allowed (including
926 names that look like floats).';
927@@ -233,7 +570,7 @@
928
929 SET default_with_oids = false;
930
931-CREATE TABLE bug (
932+CREATE TABLE public.bug (
933 id integer NOT NULL,
934 datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
935 name text,
936@@ -241,9 +578,7 @@
937 description text NOT NULL,
938 owner integer NOT NULL,
939 duplicateof integer,
940- fti ts2.tsvector,
941- private boolean DEFAULT false NOT NULL,
942- security_related boolean DEFAULT false NOT NULL,
943+ fti public.ts2_tsvector,
944 date_last_updated timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
945 date_made_private timestamp without time zone,
946 who_made_private integer,
947@@ -255,60 +590,57 @@
948 heat integer DEFAULT 0 NOT NULL,
949 heat_last_updated timestamp without time zone,
950 latest_patch_uploaded timestamp without time zone,
951- access_policy integer,
952+ information_type integer NOT NULL,
953 CONSTRAINT notduplicateofself CHECK ((NOT (id = duplicateof))),
954 CONSTRAINT sane_description CHECK (((ltrim(description) <> ''::text) AND (char_length(description) <= 50000))),
955- CONSTRAINT valid_bug_name CHECK (valid_bug_name(name))
956+ CONSTRAINT valid_bug_name CHECK (public.valid_bug_name(name))
957 );
958
959
960-COMMENT ON TABLE bug IS 'A software bug that requires fixing. This particular bug may be linked to one or more products or source packages to identify the location(s) that this bug is found.';
961-
962-
963-COMMENT ON COLUMN bug.name IS 'A lowercase name uniquely identifying the bug';
964-
965-
966-COMMENT ON COLUMN bug.description IS 'A detailed description of the bug. Initially this will be set to the contents of the initial email or bug filing comment, but later it can be edited to give a more accurate description of the bug itself rather than the symptoms observed by the reporter.';
967-
968-
969-COMMENT ON COLUMN bug.private IS 'Is this bug private? If so, only explicit subscribers will be able to see it';
970-
971-
972-COMMENT ON COLUMN bug.security_related IS 'Is this bug a security issue?';
973-
974-
975-COMMENT ON COLUMN bug.date_last_message IS 'When the last BugMessage was attached to this Bug. Maintained by a trigger on the BugMessage table.';
976-
977-
978-COMMENT ON COLUMN bug.number_of_duplicates IS 'The number of bugs marked as duplicates of this bug, populated by a trigger after setting the duplicateof of bugs.';
979-
980-
981-COMMENT ON COLUMN bug.message_count IS 'The number of messages (currently just comments) on this bugbug, maintained by the set_bug_message_count_t trigger.';
982-
983-
984-COMMENT ON COLUMN bug.users_affected_count IS 'The number of users affected by this bug, maintained by the set_bug_users_affected_count_t trigger.';
985-
986-
987-COMMENT ON COLUMN bug.heat IS 'The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values.';
988-
989-
990-COMMENT ON COLUMN bug.heat_last_updated IS 'The time this bug''s heat was last updated, or NULL if the heat has never yet been updated.';
991-
992-
993-COMMENT ON COLUMN bug.latest_patch_uploaded IS 'The time when the most recent patch has been attached to this bug or NULL if no patches are attached';
994-
995-
996-CREATE FUNCTION bug_row(bug_id integer) RETURNS bug
997+COMMENT ON TABLE public.bug IS 'A software bug that requires fixing. This particular bug may be linked to one or more products or source packages to identify the location(s) that this bug is found.';
998+
999+
1000+COMMENT ON COLUMN public.bug.name IS 'A lowercase name uniquely identifying the bug';
1001+
1002+
1003+COMMENT ON COLUMN public.bug.description IS 'A detailed description of the bug. Initially this will be set to the contents of the initial email or bug filing comment, but later it can be edited to give a more accurate description of the bug itself rather than the symptoms observed by the reporter.';
1004+
1005+
1006+COMMENT ON COLUMN public.bug.date_last_message IS 'When the last BugMessage was attached to this Bug. Maintained by a trigger on the BugMessage table.';
1007+
1008+
1009+COMMENT ON COLUMN public.bug.number_of_duplicates IS 'The number of bugs marked as duplicates of this bug, populated by a trigger after setting the duplicateof of bugs.';
1010+
1011+
1012+COMMENT ON COLUMN public.bug.message_count IS 'The number of messages (currently just comments) on this bugbug, maintained by the set_bug_message_count_t trigger.';
1013+
1014+
1015+COMMENT ON COLUMN public.bug.users_affected_count IS 'The number of users affected by this bug, maintained by the set_bug_users_affected_count_t trigger.';
1016+
1017+
1018+COMMENT ON COLUMN public.bug.heat IS 'The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values.';
1019+
1020+
1021+COMMENT ON COLUMN public.bug.heat_last_updated IS 'The time this bug''s heat was last updated, or NULL if the heat has never yet been updated.';
1022+
1023+
1024+COMMENT ON COLUMN public.bug.latest_patch_uploaded IS 'The time when the most recent patch has been attached to this bug or NULL if no patches are attached';
1025+
1026+
1027+COMMENT ON COLUMN public.bug.information_type IS 'Enum describing what type of information is stored, such as type of private or security related data, and used to determine how to apply an access policy.';
1028+
1029+
1030+CREATE FUNCTION public.bug_row(bug_id integer) RETURNS public.bug
1031 LANGUAGE sql STABLE
1032 AS $_$
1033 SELECT * FROM Bug WHERE id=$1;
1034 $_$;
1035
1036
1037-COMMENT ON FUNCTION bug_row(bug_id integer) IS 'Helper for manually testing functions requiring a bug row as input. eg. SELECT * FROM bugsummary_tags(bug_row(1))';
1038-
1039-
1040-CREATE TABLE bugsummary (
1041+COMMENT ON FUNCTION public.bug_row(bug_id integer) IS 'Helper for manually testing functions requiring a bug row as input. eg. SELECT * FROM bugsummary_tags(bug_row(1))';
1042+
1043+
1044+CREATE TABLE public.bugsummary (
1045 id integer NOT NULL,
1046 count integer DEFAULT 0 NOT NULL,
1047 product integer,
1048@@ -322,12 +654,28 @@
1049 milestone integer,
1050 importance integer NOT NULL,
1051 has_patch boolean NOT NULL,
1052- fixed_upstream boolean NOT NULL,
1053- CONSTRAINT bugtask_assignment_checks CHECK (CASE WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL) WHEN (distroseries IS NOT NULL) THEN true ELSE false END)
1054+ access_policy integer,
1055+ CONSTRAINT bugtask_assignment_checks CHECK (
1056+CASE
1057+ WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL))
1058+ WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL))
1059+ WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL)
1060+ WHEN (distroseries IS NOT NULL) THEN true
1061+ ELSE false
1062+END)
1063 );
1064
1065
1066-CREATE FUNCTION bug_summary_dec(bugsummary) RETURNS void
1067+COMMENT ON TABLE public.bugsummary IS 'A fact table for bug metadata aggregate queries. Each row represents the number of bugs that are in the system addressed by all the dimensions (e.g. product or productseries etc). ';
1068+
1069+
1070+COMMENT ON COLUMN public.bugsummary.sourcepackagename IS 'The sourcepackagename for the aggregate. Counting bugs in a distribution/distroseries requires selecting all rows by sourcepackagename. If this is too slow, add the bug to the NULL row and select with sourcepackagename is NULL to exclude them from the calculations';
1071+
1072+
1073+COMMENT ON COLUMN public.bugsummary.milestone IS 'A milestone present on the bug. All bugs are also aggregated with a NULL entry for milestone to permit querying totals (because the milestone figures cannot be summed as many milestones can be on a single bug)';
1074+
1075+
1076+CREATE FUNCTION public.bug_summary_dec(public.bugsummary) RETURNS void
1077 LANGUAGE sql
1078 AS $_$
1079 -- We own the row reference, so in the absence of bugs this cannot
1080@@ -353,34 +701,47 @@
1081 OR milestone = $1.milestone)
1082 AND importance = $1.importance
1083 AND has_patch = $1.has_patch
1084- AND fixed_upstream = $1.fixed_upstream;
1085+ AND access_policy IS NOT DISTINCT FROM $1.access_policy;
1086 $_$;
1087
1088
1089-COMMENT ON FUNCTION bug_summary_dec(bugsummary) IS 'UPSERT into bugsummary incrementing one row';
1090-
1091-
1092-CREATE FUNCTION bug_summary_flush_temp_journal() RETURNS void
1093+COMMENT ON FUNCTION public.bug_summary_dec(public.bugsummary) IS 'UPSERT into bugsummary incrementing one row';
1094+
1095+
1096+CREATE FUNCTION public.bug_summary_flush_temp_journal() RETURNS void
1097 LANGUAGE plpgsql
1098 AS $$
1099 DECLARE
1100 d bugsummary%ROWTYPE;
1101 BEGIN
1102- -- may get called even though no summaries were made (for simplicity in the
1103- -- callers)
1104+ -- May get called even though no summaries were made (for simplicity in the
1105+ -- callers). We sum the rows here to minimise the number of inserts
1106+ -- into the persistent journal, as it's reasonably likely that we'll
1107+ -- have -1s and +1s cancelling each other out.
1108 PERFORM ensure_bugsummary_temp_journal();
1109- FOR d IN SELECT * FROM bugsummary_temp_journal LOOP
1110- PERFORM bugsummary_journal_ins(d);
1111- END LOOP;
1112+ INSERT INTO BugSummaryJournal(
1113+ count, product, productseries, distribution,
1114+ distroseries, sourcepackagename, viewed_by, tag,
1115+ status, milestone, importance, has_patch, access_policy)
1116+ SELECT
1117+ SUM(count), product, productseries, distribution,
1118+ distroseries, sourcepackagename, viewed_by, tag,
1119+ status, milestone, importance, has_patch, access_policy
1120+ FROM bugsummary_temp_journal
1121+ GROUP BY
1122+ product, productseries, distribution,
1123+ distroseries, sourcepackagename, viewed_by, tag,
1124+ status, milestone, importance, has_patch, access_policy
1125+ HAVING SUM(count) != 0;
1126 TRUNCATE bugsummary_temp_journal;
1127 END;
1128 $$;
1129
1130
1131-COMMENT ON FUNCTION bug_summary_flush_temp_journal() IS 'flush the temporary bugsummary journal into the bugsummary table';
1132-
1133-
1134-CREATE FUNCTION bug_summary_inc(d bugsummary) RETURNS void
1135+COMMENT ON FUNCTION public.bug_summary_flush_temp_journal() IS 'flush the temporary bugsummary journal into the bugsummary table';
1136+
1137+
1138+CREATE FUNCTION public.bug_summary_inc(d public.bugsummary) RETURNS void
1139 LANGUAGE plpgsql
1140 AS $_$
1141 BEGIN
1142@@ -408,7 +769,7 @@
1143 OR milestone = $1.milestone)
1144 AND importance = $1.importance
1145 AND has_patch = $1.has_patch
1146- AND fixed_upstream = $1.fixed_upstream;
1147+ AND access_policy IS NOT DISTINCT FROM $1.access_policy;
1148 IF found THEN
1149 RETURN;
1150 END IF;
1151@@ -419,13 +780,12 @@
1152 INSERT INTO BugSummary(
1153 count, product, productseries, distribution,
1154 distroseries, sourcepackagename, viewed_by, tag,
1155- status, milestone,
1156- importance, has_patch, fixed_upstream)
1157+ status, milestone, importance, has_patch, access_policy)
1158 VALUES (
1159 d.count, d.product, d.productseries, d.distribution,
1160 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
1161- d.status, d.milestone,
1162- d.importance, d.has_patch, d.fixed_upstream);
1163+ d.status, d.milestone, d.importance, d.has_patch,
1164+ d.access_policy);
1165 RETURN;
1166 EXCEPTION WHEN unique_violation THEN
1167 -- do nothing, and loop to try the UPDATE again
1168@@ -435,48 +795,12 @@
1169 $_$;
1170
1171
1172-COMMENT ON FUNCTION bug_summary_inc(d bugsummary) IS 'UPSERT into bugsummary incrementing one row';
1173-
1174-
1175-CREATE FUNCTION bug_summary_temp_journal_ins(d bugsummary) RETURNS void
1176- LANGUAGE plpgsql
1177- AS $$
1178-BEGIN
1179- INSERT INTO BugSummary_Temp_Journal(
1180- count, product, productseries, distribution,
1181- distroseries, sourcepackagename, viewed_by, tag,
1182- status, milestone, importance, has_patch, fixed_upstream)
1183- VALUES (
1184- d.count, d.product, d.productseries, d.distribution,
1185- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
1186- d.status, d.milestone, d.importance, d.has_patch, d.fixed_upstream);
1187- RETURN;
1188-END;
1189-$$;
1190-
1191-
1192-COMMENT ON FUNCTION bug_summary_temp_journal_ins(d bugsummary) IS 'Insert a BugSummary into the temporary journal';
1193-
1194-
1195-CREATE FUNCTION bug_update_heat_copy_to_bugtask() RETURNS trigger
1196- LANGUAGE plpgsql SECURITY DEFINER
1197- SET search_path TO public
1198- AS $$
1199-BEGIN
1200- IF NEW.heat != OLD.heat THEN
1201- UPDATE bugtask SET heat=NEW.heat WHERE bugtask.bug=NEW.id;
1202- END IF;
1203- RETURN NULL; -- Ignored - this is an AFTER trigger
1204-END;
1205-$$;
1206-
1207-
1208-COMMENT ON FUNCTION bug_update_heat_copy_to_bugtask() IS '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.';
1209-
1210-
1211-CREATE FUNCTION bug_update_latest_patch_uploaded(integer) RETURNS void
1212- LANGUAGE plpgsql SECURITY DEFINER
1213- SET search_path TO public
1214+COMMENT ON FUNCTION public.bug_summary_inc(d public.bugsummary) IS 'UPSERT into bugsummary incrementing one row';
1215+
1216+
1217+CREATE FUNCTION public.bug_update_latest_patch_uploaded(integer) RETURNS void
1218+ LANGUAGE plpgsql SECURITY DEFINER
1219+ SET search_path TO 'public'
1220 AS $_$
1221 BEGIN
1222 UPDATE bug SET latest_patch_uploaded =
1223@@ -490,9 +814,9 @@
1224 $_$;
1225
1226
1227-CREATE FUNCTION bug_update_latest_patch_uploaded_on_delete() RETURNS trigger
1228+CREATE FUNCTION public.bug_update_latest_patch_uploaded_on_delete() RETURNS trigger
1229 LANGUAGE plpgsql SECURITY DEFINER
1230- SET search_path TO public
1231+ SET search_path TO 'public'
1232 AS $$
1233 BEGIN
1234 PERFORM bug_update_latest_patch_uploaded(OLD.bug);
1235@@ -501,9 +825,9 @@
1236 $$;
1237
1238
1239-CREATE FUNCTION bug_update_latest_patch_uploaded_on_insert_update() RETURNS trigger
1240+CREATE FUNCTION public.bug_update_latest_patch_uploaded_on_insert_update() RETURNS trigger
1241 LANGUAGE plpgsql SECURITY DEFINER
1242- SET search_path TO public
1243+ SET search_path TO 'public'
1244 AS $$
1245 BEGIN
1246 PERFORM bug_update_latest_patch_uploaded(NEW.bug);
1247@@ -512,9 +836,9 @@
1248 $$;
1249
1250
1251-CREATE FUNCTION bugmessage_copy_owner_from_message() RETURNS trigger
1252+CREATE FUNCTION public.bugmessage_copy_owner_from_message() RETURNS trigger
1253 LANGUAGE plpgsql SECURITY DEFINER
1254- SET search_path TO public
1255+ SET search_path TO 'public'
1256 AS $$
1257 BEGIN
1258 IF TG_OP = 'INSERT' THEN
1259@@ -537,145 +861,98 @@
1260 $$;
1261
1262
1263-COMMENT ON FUNCTION bugmessage_copy_owner_from_message() IS 'Copies the message owner into bugmessage when bugmessage changes.';
1264-
1265-
1266-CREATE FUNCTION bugsubscription_maintain_bug_summary() RETURNS trigger
1267- LANGUAGE plpgsql SECURITY DEFINER
1268- SET search_path TO public
1269- AS $$
1270-BEGIN
1271- -- This trigger only works if we are inserting, updating or deleting
1272- -- a single row per statement.
1273- IF TG_OP = 'INSERT' THEN
1274- IF NOT (bug_row(NEW.bug)).private THEN
1275- -- Public subscriptions are not aggregated.
1276- RETURN NEW;
1277- END IF;
1278- IF TG_WHEN = 'BEFORE' THEN
1279- PERFORM unsummarise_bug(bug_row(NEW.bug));
1280- ELSE
1281- PERFORM summarise_bug(bug_row(NEW.bug));
1282- END IF;
1283- PERFORM bug_summary_flush_temp_journal();
1284- RETURN NEW;
1285- ELSIF TG_OP = 'DELETE' THEN
1286- IF NOT (bug_row(OLD.bug)).private THEN
1287- -- Public subscriptions are not aggregated.
1288- RETURN OLD;
1289- END IF;
1290- IF TG_WHEN = 'BEFORE' THEN
1291- PERFORM unsummarise_bug(bug_row(OLD.bug));
1292- ELSE
1293- PERFORM summarise_bug(bug_row(OLD.bug));
1294- END IF;
1295- PERFORM bug_summary_flush_temp_journal();
1296- RETURN OLD;
1297- ELSE
1298- IF (OLD.person IS DISTINCT FROM NEW.person
1299- OR OLD.bug IS DISTINCT FROM NEW.bug) THEN
1300- IF TG_WHEN = 'BEFORE' THEN
1301- IF (bug_row(OLD.bug)).private THEN
1302- -- Public subscriptions are not aggregated.
1303- PERFORM unsummarise_bug(bug_row(OLD.bug));
1304- END IF;
1305- IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN
1306- -- Public subscriptions are not aggregated.
1307- PERFORM unsummarise_bug(bug_row(NEW.bug));
1308- END IF;
1309- ELSE
1310- IF (bug_row(OLD.bug)).private THEN
1311- -- Public subscriptions are not aggregated.
1312- PERFORM summarise_bug(bug_row(OLD.bug));
1313- END IF;
1314- IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN
1315- -- Public subscriptions are not aggregated.
1316- PERFORM summarise_bug(bug_row(NEW.bug));
1317- END IF;
1318- END IF;
1319- END IF;
1320- PERFORM bug_summary_flush_temp_journal();
1321- RETURN NEW;
1322- END IF;
1323-END;
1324-$$;
1325-
1326-
1327-COMMENT ON FUNCTION bugsubscription_maintain_bug_summary() IS 'AFTER trigger on bugsubscription maintaining the bugs summaries in bugsummary.';
1328-
1329-
1330-CREATE FUNCTION bugsummary_journal_ins(d bugsummary) RETURNS void
1331- LANGUAGE plpgsql
1332- AS $$
1333-BEGIN
1334- IF d.count <> 0 THEN
1335- INSERT INTO BugSummaryJournal (
1336- count, product, productseries, distribution,
1337- distroseries, sourcepackagename, viewed_by, tag,
1338- status, milestone,
1339- importance, has_patch, fixed_upstream)
1340- VALUES (
1341- d.count, d.product, d.productseries, d.distribution,
1342- d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
1343- d.status, d.milestone,
1344- d.importance, d.has_patch, d.fixed_upstream);
1345- END IF;
1346-END;
1347-$$;
1348-
1349-
1350-COMMENT ON FUNCTION bugsummary_journal_ins(d bugsummary) IS 'Add an entry into BugSummaryJournal';
1351-
1352-
1353-CREATE FUNCTION bugsummary_locations(bug_row bug) RETURNS SETOF bugsummary
1354- LANGUAGE plpgsql
1355- AS $$
1356-BEGIN
1357- IF BUG_ROW.duplicateof IS NOT NULL THEN
1358+COMMENT ON FUNCTION public.bugmessage_copy_owner_from_message() IS 'Copies the message owner into bugmessage when bugmessage changes.';
1359+
1360+
1361+CREATE FUNCTION public.bugsummary_journal_bug(bug_row public.bug, _count integer) RETURNS void
1362+ LANGUAGE plpgsql
1363+ AS $$
1364+DECLARE
1365+ btf_row bugtaskflat%ROWTYPE;
1366+BEGIN
1367+ FOR btf_row IN SELECT * FROM bugtaskflat WHERE bug = bug_row.id
1368+ LOOP
1369+ PERFORM bugsummary_journal_bugtaskflat(btf_row, _count);
1370+ END LOOP;
1371+END;
1372+$$;
1373+
1374+
1375+CREATE TABLE public.bugtaskflat (
1376+ bugtask integer NOT NULL,
1377+ bug integer NOT NULL,
1378+ datecreated timestamp without time zone,
1379+ duplicateof integer,
1380+ bug_owner integer NOT NULL,
1381+ fti public.ts2_tsvector,
1382+ information_type integer NOT NULL,
1383+ date_last_updated timestamp without time zone NOT NULL,
1384+ heat integer NOT NULL,
1385+ product integer,
1386+ productseries integer,
1387+ distribution integer,
1388+ distroseries integer,
1389+ sourcepackagename integer,
1390+ status integer NOT NULL,
1391+ importance integer NOT NULL,
1392+ assignee integer,
1393+ milestone integer,
1394+ owner integer NOT NULL,
1395+ active boolean NOT NULL,
1396+ access_policies integer[],
1397+ access_grants integer[],
1398+ latest_patch_uploaded timestamp without time zone,
1399+ date_closed timestamp without time zone
1400+);
1401+
1402+
1403+CREATE FUNCTION public.bugsummary_journal_bugtaskflat(btf_row public.bugtaskflat, _count integer) RETURNS void
1404+ LANGUAGE plpgsql
1405+ AS $$
1406+BEGIN
1407+ PERFORM ensure_bugsummary_temp_journal();
1408+ INSERT INTO BugSummary_Temp_Journal(
1409+ count, product, productseries, distribution,
1410+ distroseries, sourcepackagename, viewed_by, tag,
1411+ status, milestone, importance, has_patch, access_policy)
1412+ SELECT
1413+ _count, product, productseries, distribution,
1414+ distroseries, sourcepackagename, viewed_by, tag,
1415+ status, milestone, importance, has_patch, access_policy
1416+ FROM bugsummary_locations(btf_row);
1417+END;
1418+$$;
1419+
1420+
1421+CREATE FUNCTION public.bugsummary_locations(btf_row public.bugtaskflat) RETURNS SETOF public.bugsummary
1422+ LANGUAGE plpgsql
1423+ AS $$
1424+BEGIN
1425+ IF btf_row.duplicateof IS NOT NULL THEN
1426 RETURN;
1427 END IF;
1428 RETURN QUERY
1429 SELECT
1430 CAST(NULL AS integer) AS id,
1431 CAST(1 AS integer) AS count,
1432- product, productseries, distribution, distroseries,
1433- sourcepackagename, person AS viewed_by, tag, status, milestone,
1434- importance,
1435- BUG_ROW.latest_patch_uploaded IS NOT NULL AS has_patch,
1436- (EXISTS (
1437- SELECT TRUE FROM BugTask AS RBT
1438- WHERE
1439- RBT.bug = tasks.bug
1440- -- This would just be 'RBT.id <> tasks.id', except
1441- -- that the records from tasks are summaries and not
1442- -- real bugtasks, and do not have an id.
1443- AND (RBT.product IS DISTINCT FROM tasks.product
1444- OR RBT.productseries
1445- IS DISTINCT FROM tasks.productseries
1446- OR RBT.distribution IS DISTINCT FROM tasks.distribution
1447- OR RBT.distroseries IS DISTINCT FROM tasks.distroseries
1448- OR RBT.sourcepackagename
1449- IS DISTINCT FROM tasks.sourcepackagename)
1450- -- Flagged as INVALID, FIXCOMMITTED or FIXRELEASED
1451- -- via a bugwatch, or FIXCOMMITTED or FIXRELEASED on
1452- -- the product.
1453- AND ((bugwatch IS NOT NULL AND status IN (17, 25, 30))
1454- OR (bugwatch IS NULL AND product IS NOT NULL
1455- AND status IN (25, 30))))
1456- )::boolean AS fixed_upstream
1457- FROM bugsummary_tasks(BUG_ROW) AS tasks
1458- JOIN bugsummary_tags(BUG_ROW) AS bug_tags ON TRUE
1459- LEFT OUTER JOIN bugsummary_viewers(BUG_ROW) AS bug_viewers ON TRUE;
1460+ bug_targets.product, bug_targets.productseries,
1461+ bug_targets.distribution, bug_targets.distroseries,
1462+ bug_targets.sourcepackagename,
1463+ bug_viewers.viewed_by, bug_tags.tag, btf_row.status,
1464+ btf_row.milestone, btf_row.importance,
1465+ btf_row.latest_patch_uploaded IS NOT NULL AS has_patch,
1466+ bug_viewers.access_policy
1467+ FROM
1468+ bugsummary_targets(btf_row) as bug_targets,
1469+ bugsummary_tags(btf_row) AS bug_tags,
1470+ bugsummary_viewers(btf_row) AS bug_viewers;
1471 END;
1472 $$;
1473
1474
1475-COMMENT ON FUNCTION bugsummary_locations(bug_row bug) IS 'Calculate what BugSummary rows should exist for a given Bug.';
1476-
1477-
1478-CREATE FUNCTION bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer) RETURNS void
1479+CREATE FUNCTION public.bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer) RETURNS void
1480 LANGUAGE plpgsql SECURITY DEFINER
1481- SET search_path TO public
1482+ SET search_path TO 'public'
1483 AS $$
1484 DECLARE
1485 d bugsummary%ROWTYPE;
1486@@ -710,13 +987,13 @@
1487 milestone,
1488 importance,
1489 has_patch,
1490- fixed_upstream
1491+ access_policy
1492 FROM BugSummaryJournal
1493 WHERE id <= max_id
1494 GROUP BY
1495 product, productseries, distribution, distroseries,
1496 sourcepackagename, viewed_by, tag, status, milestone,
1497- importance, has_patch, fixed_upstream
1498+ importance, has_patch, access_policy
1499 HAVING sum(count) <> 0
1500 LOOP
1501 IF d.count < 0 THEN
1502@@ -734,10 +1011,10 @@
1503 $$;
1504
1505
1506-COMMENT ON FUNCTION bugsummary_rollup_journal(batchsize integer) IS 'Collate and migrate rows from BugSummaryJournal to BugSummary';
1507-
1508-
1509-CREATE FUNCTION valid_name(text) RETURNS boolean
1510+COMMENT ON FUNCTION public.bugsummary_rollup_journal(batchsize integer) IS 'Collate and migrate rows from BugSummaryJournal to BugSummary';
1511+
1512+
1513+CREATE FUNCTION public.valid_name(text) RETURNS boolean
1514 LANGUAGE plpythonu IMMUTABLE STRICT
1515 AS $$
1516 import re
1517@@ -749,7 +1026,7 @@
1518 $$;
1519
1520
1521-COMMENT ON FUNCTION valid_name(text) IS 'validate a name.
1522+COMMENT ON FUNCTION public.valid_name(text) IS 'validate a name.
1523
1524 Names must contain only lowercase letters, numbers, ., & -. They
1525 must start with an alphanumeric. They are ASCII only. Names are useful
1526@@ -760,446 +1037,347 @@
1527 namespace conflict if URL traversal is possible by name as well as id.';
1528
1529
1530-CREATE TABLE bugtag (
1531+CREATE TABLE public.bugtag (
1532 id integer NOT NULL,
1533 bug integer NOT NULL,
1534 tag text NOT NULL,
1535- CONSTRAINT valid_tag CHECK (valid_name(tag))
1536-);
1537-
1538-
1539-COMMENT ON TABLE bugtag IS 'Attaches simple text tags to a bug.';
1540-
1541-
1542-COMMENT ON COLUMN bugtag.bug IS 'The bug the tags is attached to.';
1543-
1544-
1545-COMMENT ON COLUMN bugtag.tag IS 'The text representation of the tag.';
1546-
1547-
1548-CREATE FUNCTION bugsummary_tags(bug_row bug) RETURNS SETOF bugtag
1549- LANGUAGE sql STABLE
1550- AS $_$
1551- SELECT * FROM BugTag WHERE BugTag.bug = $1.id
1552- UNION ALL
1553- SELECT NULL::integer, $1.id, NULL::text;
1554-$_$;
1555-
1556-
1557-COMMENT ON FUNCTION bugsummary_tags(bug_row bug) IS 'Return (bug, tag) for all tags + (bug, NULL::text)';
1558-
1559-
1560-CREATE TABLE bugtask (
1561- id integer NOT NULL,
1562- bug integer NOT NULL,
1563- product integer,
1564- distribution integer,
1565- distroseries integer,
1566- sourcepackagename integer,
1567- binarypackagename integer,
1568- status integer NOT NULL,
1569- importance integer DEFAULT 5 NOT NULL,
1570- assignee integer,
1571- date_assigned timestamp without time zone,
1572- datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone),
1573- owner integer NOT NULL,
1574- milestone integer,
1575- bugwatch integer,
1576- fti ts2.tsvector,
1577- targetnamecache text,
1578- date_confirmed timestamp without time zone,
1579- date_inprogress timestamp without time zone,
1580- date_closed timestamp without time zone,
1581- productseries integer,
1582- date_incomplete timestamp without time zone,
1583- date_left_new timestamp without time zone,
1584- date_triaged timestamp without time zone,
1585- date_fix_committed timestamp without time zone,
1586- date_fix_released timestamp without time zone,
1587- date_left_closed timestamp without time zone,
1588- heat_rank integer DEFAULT 0 NOT NULL,
1589- date_milestone_set timestamp without time zone,
1590- heat integer DEFAULT 0 NOT NULL,
1591- CONSTRAINT bugtask_assignment_checks CHECK (CASE WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL) WHEN (distroseries IS NOT NULL) THEN true ELSE false END)
1592-);
1593-
1594-
1595-COMMENT ON TABLE bugtask IS 'Links a given Bug to a particular (sourcepackagename, distro) or product.';
1596-
1597-
1598-COMMENT ON COLUMN bugtask.bug IS 'The bug that is assigned to this (sourcepackagename, distro) or product.';
1599-
1600-
1601-COMMENT ON COLUMN bugtask.product IS 'The product in which this bug shows up.';
1602-
1603-
1604-COMMENT ON COLUMN bugtask.distribution IS 'The distro of the named sourcepackage.';
1605-
1606-
1607-COMMENT ON COLUMN bugtask.sourcepackagename IS 'The name of the sourcepackage in which this bug shows up.';
1608-
1609-
1610-COMMENT ON COLUMN bugtask.binarypackagename IS 'The name of the binary package built from the source package. This column may only contain a value if this bug task is linked to a sourcepackage (not a product)';
1611-
1612-
1613-COMMENT ON COLUMN bugtask.status IS 'The general health of the bug, e.g. Accepted, Rejected, etc.';
1614-
1615-
1616-COMMENT ON COLUMN bugtask.importance IS 'The importance of fixing the bug.';
1617-
1618-
1619-COMMENT ON COLUMN bugtask.assignee IS 'The person who has been assigned to fix this bug in this product or (sourcepackagename, distro)';
1620-
1621-
1622-COMMENT ON COLUMN bugtask.date_assigned IS 'The date on which the bug in this (sourcepackagename, distro) or product was assigned to someone to fix';
1623-
1624-
1625-COMMENT ON COLUMN bugtask.datecreated IS 'A timestamp for the creation of this bug assignment. Note that this is not the date the bug was created (though it might be), it''s the date the bug was assigned to this product, which could have come later.';
1626-
1627-
1628-COMMENT ON COLUMN bugtask.milestone IS 'A way to mark a bug for grouping purposes, e.g. to say it needs to be fixed by version 1.2';
1629-
1630-
1631-COMMENT ON COLUMN bugtask.bugwatch IS 'This column allows us to link a bug
1632-task to a bug watch. In other words, we are connecting the state of the task
1633-to the state of the bug in a different bug tracking system. To the best of
1634-our ability we''ll try and keep the bug task syncronised with the state of
1635-the remote bug watch.';
1636-
1637-
1638-COMMENT ON COLUMN bugtask.targetnamecache IS 'A cached value of the target name of this bugtask, to make it easier to sort and search on the target name.';
1639-
1640-
1641-COMMENT ON COLUMN bugtask.date_confirmed IS 'The date when this bug transitioned from an unconfirmed status to a confirmed one. If the state regresses to a one that logically occurs before Confirmed, e.g., Unconfirmed, this date is cleared.';
1642-
1643-
1644-COMMENT ON COLUMN bugtask.date_inprogress IS 'The date on which this bug transitioned from not being in progress to a state >= In Progress. If the status moves back to a pre-In Progress state, this date is cleared';
1645-
1646-
1647-COMMENT ON COLUMN bugtask.date_closed IS 'The date when this bug transitioned to a resolved state, e.g., Rejected, Fix Released, etc. If the state changes back to a pre-closed state, this date is cleared';
1648-
1649-
1650-COMMENT ON COLUMN bugtask.productseries IS 'The product series to which the bug is targeted';
1651-
1652-
1653-COMMENT ON COLUMN bugtask.date_left_new IS 'The date when this bug first transitioned out of the NEW status.';
1654-
1655-
1656-COMMENT ON COLUMN bugtask.date_triaged IS 'The date when this bug transitioned to a status >= TRIAGED.';
1657-
1658-
1659-COMMENT ON COLUMN bugtask.date_fix_committed IS 'The date when this bug transitioned to a status >= FIXCOMMITTED.';
1660-
1661-
1662-COMMENT ON COLUMN bugtask.date_fix_released IS 'The date when this bug transitioned to a FIXRELEASED status.';
1663-
1664-
1665-COMMENT ON COLUMN bugtask.date_left_closed IS 'The date when this bug last transitioned out of a CLOSED status.';
1666-
1667-
1668-COMMENT ON COLUMN bugtask.heat_rank IS 'The heat bin in which this bugtask appears, as a value from the BugTaskHeatRank enumeration.';
1669-
1670-
1671-COMMENT ON COLUMN bugtask.date_milestone_set IS 'The date when this bug was targed to the milestone that is currently set.';
1672-
1673-
1674-CREATE FUNCTION bugsummary_tasks(bug_row bug) RETURNS SETOF bugtask
1675- LANGUAGE plpgsql STABLE
1676- AS $$
1677-DECLARE
1678- bt bugtask%ROWTYPE;
1679- r record;
1680-BEGIN
1681- bt.bug = BUG_ROW.id;
1682-
1683- -- One row only for each target permutation - need to ignore other fields
1684- -- like date last modified to deal with conjoined masters and multiple
1685- -- sourcepackage tasks in a distro.
1686- FOR r IN
1687- SELECT
1688- product, productseries, distribution, distroseries,
1689- sourcepackagename, status, milestone, importance, bugwatch
1690- FROM BugTask WHERE bug=BUG_ROW.id
1691- UNION -- Implicit DISTINCT
1692- SELECT
1693- product, productseries, distribution, distroseries,
1694- NULL, status, milestone, importance, bugwatch
1695- FROM BugTask WHERE bug=BUG_ROW.id AND sourcepackagename IS NOT NULL
1696- LOOP
1697- bt.product = r.product;
1698- bt.productseries = r.productseries;
1699- bt.distribution = r.distribution;
1700- bt.distroseries = r.distroseries;
1701- bt.sourcepackagename = r.sourcepackagename;
1702- bt.status = r.status;
1703- bt.milestone = r.milestone;
1704- bt.importance = r.importance;
1705- bt.bugwatch = r.bugwatch;
1706- RETURN NEXT bt;
1707- END LOOP;
1708-END;
1709-$$;
1710-
1711-
1712-COMMENT ON FUNCTION bugsummary_tasks(bug_row bug) IS 'Return all tasks for the bug + all sourcepackagename tasks again with the sourcepackagename squashed';
1713-
1714-
1715-CREATE TABLE bugsubscription (
1716- id integer NOT NULL,
1717- person integer NOT NULL,
1718- bug integer NOT NULL,
1719- date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
1720- subscribed_by integer NOT NULL,
1721- bug_notification_level integer DEFAULT 40 NOT NULL
1722-);
1723-
1724-
1725-COMMENT ON TABLE bugsubscription IS 'A subscription by a Person to a bug.';
1726-
1727-
1728-COMMENT ON COLUMN bugsubscription.bug_notification_level IS 'The level of notifications which the Person will receive from this subscription.';
1729-
1730-
1731-CREATE FUNCTION bugsummary_viewers(bug_row bug) RETURNS SETOF bugsubscription
1732- LANGUAGE sql STABLE
1733- AS $_$
1734- SELECT *
1735- FROM BugSubscription
1736- WHERE
1737- bugsubscription.bug=$1.id
1738- AND $1.private IS TRUE;
1739-$_$;
1740-
1741-
1742-COMMENT ON FUNCTION bugsummary_viewers(bug_row bug) IS 'Return (bug, viewer) for all viewers if private, nothing otherwise';
1743-
1744-
1745-CREATE FUNCTION bugtag_maintain_bug_summary() RETURNS trigger
1746- LANGUAGE plpgsql SECURITY DEFINER
1747- SET search_path TO public
1748- AS $$
1749-BEGIN
1750- IF TG_OP = 'INSERT' THEN
1751- IF TG_WHEN = 'BEFORE' THEN
1752- PERFORM unsummarise_bug(bug_row(NEW.bug));
1753- ELSE
1754- PERFORM summarise_bug(bug_row(NEW.bug));
1755- END IF;
1756- PERFORM bug_summary_flush_temp_journal();
1757- RETURN NEW;
1758- ELSIF TG_OP = 'DELETE' THEN
1759- IF TG_WHEN = 'BEFORE' THEN
1760- PERFORM unsummarise_bug(bug_row(OLD.bug));
1761- ELSE
1762- PERFORM summarise_bug(bug_row(OLD.bug));
1763- END IF;
1764- PERFORM bug_summary_flush_temp_journal();
1765- RETURN OLD;
1766- ELSE
1767- IF TG_WHEN = 'BEFORE' THEN
1768- PERFORM unsummarise_bug(bug_row(OLD.bug));
1769- IF OLD.bug <> NEW.bug THEN
1770- PERFORM unsummarise_bug(bug_row(NEW.bug));
1771- END IF;
1772- ELSE
1773- PERFORM summarise_bug(bug_row(OLD.bug));
1774- IF OLD.bug <> NEW.bug THEN
1775- PERFORM summarise_bug(bug_row(NEW.bug));
1776- END IF;
1777- END IF;
1778- PERFORM bug_summary_flush_temp_journal();
1779- RETURN NEW;
1780- END IF;
1781-END;
1782-$$;
1783-
1784-
1785-COMMENT ON FUNCTION bugtag_maintain_bug_summary() IS 'AFTER trigger on bugtag maintaining the bugs summaries in bugsummary.';
1786-
1787-
1788-CREATE FUNCTION bugtask_maintain_bug_summary() RETURNS trigger
1789- LANGUAGE plpgsql SECURITY DEFINER
1790- SET search_path TO public
1791- AS $$
1792-BEGIN
1793- -- This trigger only works if we are inserting, updating or deleting
1794- -- a single row per statement.
1795-
1796- -- Unlike bug_maintain_bug_summary, this trigger does not have access
1797- -- to the old bug when invoked as an AFTER trigger. To work around this
1798- -- we install this trigger as both a BEFORE and an AFTER trigger.
1799- IF TG_OP = 'INSERT' THEN
1800- IF TG_WHEN = 'BEFORE' THEN
1801- PERFORM unsummarise_bug(bug_row(NEW.bug));
1802- ELSE
1803- PERFORM summarise_bug(bug_row(NEW.bug));
1804- END IF;
1805- PERFORM bug_summary_flush_temp_journal();
1806- RETURN NEW;
1807-
1808- ELSIF TG_OP = 'DELETE' THEN
1809- IF TG_WHEN = 'BEFORE' THEN
1810- PERFORM unsummarise_bug(bug_row(OLD.bug));
1811- ELSE
1812- PERFORM summarise_bug(bug_row(OLD.bug));
1813- END IF;
1814- PERFORM bug_summary_flush_temp_journal();
1815- RETURN OLD;
1816-
1817- ELSE
1818- IF (OLD.product IS DISTINCT FROM NEW.product
1819- OR OLD.productseries IS DISTINCT FROM NEW.productseries
1820- OR OLD.distribution IS DISTINCT FROM NEW.distribution
1821- OR OLD.distroseries IS DISTINCT FROM NEW.distroseries
1822- OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename
1823- OR OLD.status IS DISTINCT FROM NEW.status
1824- OR OLD.importance IS DISTINCT FROM NEW.importance
1825- OR OLD.bugwatch IS DISTINCT FROM NEW.bugwatch
1826- OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN
1827-
1828- IF TG_WHEN = 'BEFORE' THEN
1829- PERFORM unsummarise_bug(bug_row(OLD.bug));
1830- IF OLD.bug <> NEW.bug THEN
1831- PERFORM unsummarise_bug(bug_row(NEW.bug));
1832- END IF;
1833- ELSE
1834- PERFORM summarise_bug(bug_row(OLD.bug));
1835- IF OLD.bug <> NEW.bug THEN
1836- PERFORM summarise_bug(bug_row(NEW.bug));
1837- END IF;
1838- END IF;
1839- END IF;
1840- PERFORM bug_summary_flush_temp_journal();
1841- RETURN NEW;
1842- END IF;
1843-END;
1844-$$;
1845-
1846-
1847-COMMENT ON FUNCTION bugtask_maintain_bug_summary() IS 'Both BEFORE & AFTER trigger on bugtask maintaining the bugs summaries in bugsummary.';
1848-
1849-
1850-CREATE FUNCTION calculate_bug_heat(bug_id integer) RETURNS integer
1851- LANGUAGE plpythonu STABLE STRICT
1852- AS $$
1853- from datetime import datetime
1854-
1855- class BugHeatConstants:
1856- PRIVACY = 150
1857- SECURITY = 250
1858- DUPLICATE = 6
1859- AFFECTED_USER = 4
1860- SUBSCRIBER = 2
1861-
1862- def get_max_heat_for_bug(bug_id):
1863- results = plpy.execute("""
1864- SELECT MAX(
1865- GREATEST(Product.max_bug_heat,
1866- DistributionSourcePackage.max_bug_heat))
1867- AS max_heat
1868- FROM BugTask
1869- LEFT OUTER JOIN ProductSeries ON
1870- BugTask.productseries = ProductSeries.id
1871- LEFT OUTER JOIN Product ON (
1872- BugTask.product = Product.id
1873- OR ProductSeries.product = Product.id)
1874- LEFT OUTER JOIN DistroSeries ON
1875- BugTask.distroseries = DistroSeries.id
1876- LEFT OUTER JOIN Distribution ON (
1877- BugTask.distribution = Distribution.id
1878- OR DistroSeries.distribution = Distribution.id)
1879- LEFT OUTER JOIN DistributionSourcePackage ON (
1880- BugTask.sourcepackagename =
1881- DistributionSourcePackage.sourcepackagename)
1882- WHERE
1883- BugTask.bug = %s""" % bug_id)
1884-
1885- return results[0]['max_heat']
1886-
1887- # It would be nice to be able to just SELECT * here, but we need the
1888- # timestamps to be in a format that datetime.fromtimestamp() will
1889- # understand.
1890- bug_data = plpy.execute("""
1891- SELECT
1892- duplicateof,
1893- private,
1894- security_related,
1895- number_of_duplicates,
1896- users_affected_count,
1897- EXTRACT(epoch from datecreated)
1898- AS timestamp_date_created,
1899- EXTRACT(epoch from date_last_updated)
1900- AS timestamp_date_last_updated,
1901- EXTRACT(epoch from date_last_message)
1902- AS timestamp_date_last_message
1903- FROM Bug WHERE id = %s""" % bug_id)
1904-
1905- if bug_data.nrows() == 0:
1906- raise Exception("Bug %s doesn't exist." % bug_id)
1907-
1908- bug = bug_data[0]
1909- if bug['duplicateof'] is not None:
1910- return None
1911-
1912- heat = {}
1913- heat['dupes'] = (
1914- BugHeatConstants.DUPLICATE * bug['number_of_duplicates'])
1915- heat['affected_users'] = (
1916- BugHeatConstants.AFFECTED_USER *
1917- bug['users_affected_count'])
1918-
1919- if bug['private']:
1920- heat['privacy'] = BugHeatConstants.PRIVACY
1921- if bug['security_related']:
1922- heat['security'] = BugHeatConstants.SECURITY
1923-
1924- # Get the heat from subscribers, both direct and via duplicates.
1925- subs_from_dupes = plpy.execute("""
1926- SELECT COUNT(DISTINCT BugSubscription.person) AS sub_count
1927- FROM BugSubscription, Bug
1928- WHERE Bug.id = BugSubscription.bug
1929- AND (Bug.id = %s OR Bug.duplicateof = %s)"""
1930- % (bug_id, bug_id))
1931-
1932- heat['subcribers'] = (
1933- BugHeatConstants.SUBSCRIBER
1934- * subs_from_dupes[0]['sub_count'])
1935-
1936- total_heat = sum(heat.values())
1937-
1938- # Bugs decay over time. Every day the bug isn't touched its heat
1939- # decreases by 1%.
1940- date_last_updated = datetime.fromtimestamp(
1941- bug['timestamp_date_last_updated'])
1942- days_since_last_update = (datetime.utcnow() - date_last_updated).days
1943- total_heat = int(total_heat * (0.99 ** days_since_last_update))
1944-
1945- if days_since_last_update > 0:
1946- # Bug heat increases by a quarter of the maximum bug heat
1947- # divided by the number of days since the bug's creation date.
1948- date_created = datetime.fromtimestamp(
1949- bug['timestamp_date_created'])
1950-
1951- if bug['timestamp_date_last_message'] is not None:
1952- date_last_message = datetime.fromtimestamp(
1953- bug['timestamp_date_last_message'])
1954- oldest_date = max(date_last_updated, date_last_message)
1955- else:
1956- date_last_message = None
1957- oldest_date = date_last_updated
1958-
1959- days_since_last_activity = (datetime.utcnow() - oldest_date).days
1960- days_since_created = (datetime.utcnow() - date_created).days
1961- max_heat = get_max_heat_for_bug(bug_id)
1962- if max_heat is not None and days_since_created > 0:
1963- total_heat = (
1964- total_heat + (max_heat * 0.25 / days_since_created))
1965-
1966- return int(total_heat)
1967-$$;
1968-
1969-
1970-CREATE FUNCTION cursor_fetch(cur refcursor, n integer) RETURNS SETOF record
1971+ CONSTRAINT valid_tag CHECK (public.valid_name(tag))
1972+);
1973+
1974+
1975+COMMENT ON TABLE public.bugtag IS 'Attaches simple text tags to a bug.';
1976+
1977+
1978+COMMENT ON COLUMN public.bugtag.bug IS 'The bug the tags is attached to.';
1979+
1980+
1981+COMMENT ON COLUMN public.bugtag.tag IS 'The text representation of the tag.';
1982+
1983+
1984+CREATE FUNCTION public.bugsummary_tags(btf_row public.bugtaskflat) RETURNS SETOF public.bugtag
1985+ LANGUAGE sql STABLE
1986+ AS $_$
1987+ SELECT * FROM BugTag WHERE BugTag.bug = $1.bug
1988+ UNION ALL
1989+ SELECT NULL::integer, $1.bug, NULL::text;
1990+$_$;
1991+
1992+
1993+CREATE FUNCTION public.bugsummary_targets(btf_row public.bugtaskflat) RETURNS TABLE(product integer, productseries integer, distribution integer, distroseries integer, sourcepackagename integer)
1994+ LANGUAGE sql IMMUTABLE
1995+ AS $_$
1996+ -- Include a sourcepackagename-free task if this one has a
1997+ -- sourcepackagename, so package tasks are also counted in their
1998+ -- distro/series.
1999+ SELECT
2000+ $1.product, $1.productseries, $1.distribution,
2001+ $1.distroseries, $1.sourcepackagename
2002+ UNION -- Implicit DISTINCT
2003+ SELECT
2004+ $1.product, $1.productseries, $1.distribution,
2005+ $1.distroseries, NULL;
2006+$_$;
2007+
2008+
2009+CREATE FUNCTION public.bugsummary_viewers(btf_row public.bugtaskflat) RETURNS TABLE(viewed_by integer, access_policy integer)
2010+ LANGUAGE sql IMMUTABLE
2011+ AS $_$
2012+ SELECT NULL::integer, NULL::integer WHERE $1.information_type IN (1, 2)
2013+ UNION ALL
2014+ SELECT unnest($1.access_grants), NULL::integer
2015+ WHERE $1.information_type NOT IN (1, 2)
2016+ UNION ALL
2017+ SELECT NULL::integer, unnest($1.access_policies)
2018+ WHERE $1.information_type NOT IN (1, 2);
2019+$_$;
2020+
2021+
2022+CREATE FUNCTION public.bugtag_maintain_bug_summary() RETURNS trigger
2023+ LANGUAGE plpgsql SECURITY DEFINER
2024+ SET search_path TO 'public'
2025+ AS $$
2026+BEGIN
2027+ IF TG_OP = 'INSERT' THEN
2028+ IF TG_WHEN = 'BEFORE' THEN
2029+ PERFORM unsummarise_bug(NEW.bug);
2030+ ELSE
2031+ PERFORM summarise_bug(NEW.bug);
2032+ END IF;
2033+ PERFORM bug_summary_flush_temp_journal();
2034+ RETURN NEW;
2035+ ELSIF TG_OP = 'DELETE' THEN
2036+ IF TG_WHEN = 'BEFORE' THEN
2037+ PERFORM unsummarise_bug(OLD.bug);
2038+ ELSE
2039+ PERFORM summarise_bug(OLD.bug);
2040+ END IF;
2041+ PERFORM bug_summary_flush_temp_journal();
2042+ RETURN OLD;
2043+ ELSE
2044+ IF TG_WHEN = 'BEFORE' THEN
2045+ PERFORM unsummarise_bug(OLD.bug);
2046+ IF OLD.bug <> NEW.bug THEN
2047+ PERFORM unsummarise_bug(NEW.bug);
2048+ END IF;
2049+ ELSE
2050+ PERFORM summarise_bug(OLD.bug);
2051+ IF OLD.bug <> NEW.bug THEN
2052+ PERFORM summarise_bug(NEW.bug);
2053+ END IF;
2054+ END IF;
2055+ PERFORM bug_summary_flush_temp_journal();
2056+ RETURN NEW;
2057+ END IF;
2058+END;
2059+$$;
2060+
2061+
2062+COMMENT ON FUNCTION public.bugtag_maintain_bug_summary() IS 'AFTER trigger on bugtag maintaining the bugs summaries in bugsummary.';
2063+
2064+
2065+CREATE FUNCTION public.bugtask_flatten(task_id integer, check_only boolean) RETURNS boolean
2066+ LANGUAGE plpgsql SECURITY DEFINER
2067+ SET search_path TO 'public'
2068+ AS $$
2069+DECLARE
2070+ bug_row Bug%ROWTYPE;
2071+ task_row BugTask%ROWTYPE;
2072+ old_flat_row BugTaskFlat%ROWTYPE;
2073+ new_flat_row BugTaskFlat%ROWTYPE;
2074+ _product_active boolean;
2075+ _access_policies integer[];
2076+ _access_grants integer[];
2077+BEGIN
2078+ -- This is the master function to update BugTaskFlat, but there are
2079+ -- maintenance triggers and jobs on the involved tables that update
2080+ -- it directly. Any changes here probably require a corresponding
2081+ -- change in other trigger functions.
2082+
2083+ SELECT * INTO task_row FROM BugTask WHERE id = task_id;
2084+ SELECT * INTO old_flat_row FROM BugTaskFlat WHERE bugtask = task_id;
2085+
2086+ -- If the task doesn't exist, ensure that there's no flat row.
2087+ IF task_row.id IS NULL THEN
2088+ IF old_flat_row.bugtask IS NOT NULL THEN
2089+ IF NOT check_only THEN
2090+ DELETE FROM BugTaskFlat WHERE bugtask = task_id;
2091+ END IF;
2092+ RETURN FALSE;
2093+ ELSE
2094+ RETURN TRUE;
2095+ END IF;
2096+ END IF;
2097+
2098+ SELECT * FROM bug INTO bug_row WHERE id = task_row.bug;
2099+
2100+ -- If it's a product(series) task, we must consult the active flag.
2101+ IF task_row.product IS NOT NULL THEN
2102+ SELECT product.active INTO _product_active
2103+ FROM product WHERE product.id = task_row.product LIMIT 1;
2104+ ELSIF task_row.productseries IS NOT NULL THEN
2105+ SELECT product.active INTO _product_active
2106+ FROM
2107+ product
2108+ JOIN productseries ON productseries.product = product.id
2109+ WHERE productseries.id = task_row.productseries LIMIT 1;
2110+ END IF;
2111+
2112+ SELECT policies, grants
2113+ INTO _access_policies, _access_grants
2114+ FROM bug_build_access_cache(bug_row.id, bug_row.information_type)
2115+ AS (policies integer[], grants integer[]);
2116+
2117+ -- Compile the new flat row.
2118+ SELECT task_row.id, bug_row.id, task_row.datecreated,
2119+ bug_row.duplicateof, bug_row.owner, bug_row.fti,
2120+ bug_row.information_type, bug_row.date_last_updated,
2121+ bug_row.heat, task_row.product, task_row.productseries,
2122+ task_row.distribution, task_row.distroseries,
2123+ task_row.sourcepackagename, task_row.status,
2124+ task_row.importance, task_row.assignee,
2125+ task_row.milestone, task_row.owner,
2126+ COALESCE(_product_active, TRUE),
2127+ _access_policies,
2128+ _access_grants,
2129+ bug_row.latest_patch_uploaded, task_row.date_closed
2130+ INTO new_flat_row;
2131+
2132+ -- Calculate the necessary updates.
2133+ IF old_flat_row.bugtask IS NULL THEN
2134+ IF NOT check_only THEN
2135+ INSERT INTO BugTaskFlat VALUES (new_flat_row.*);
2136+ END IF;
2137+ RETURN FALSE;
2138+ ELSIF new_flat_row != old_flat_row THEN
2139+ IF NOT check_only THEN
2140+ UPDATE BugTaskFlat SET
2141+ bug = new_flat_row.bug,
2142+ datecreated = new_flat_row.datecreated,
2143+ duplicateof = new_flat_row.duplicateof,
2144+ bug_owner = new_flat_row.bug_owner,
2145+ fti = new_flat_row.fti,
2146+ information_type = new_flat_row.information_type,
2147+ date_last_updated = new_flat_row.date_last_updated,
2148+ heat = new_flat_row.heat,
2149+ product = new_flat_row.product,
2150+ productseries = new_flat_row.productseries,
2151+ distribution = new_flat_row.distribution,
2152+ distroseries = new_flat_row.distroseries,
2153+ sourcepackagename = new_flat_row.sourcepackagename,
2154+ status = new_flat_row.status,
2155+ importance = new_flat_row.importance,
2156+ assignee = new_flat_row.assignee,
2157+ milestone = new_flat_row.milestone,
2158+ owner = new_flat_row.owner,
2159+ active = new_flat_row.active,
2160+ access_policies = new_flat_row.access_policies,
2161+ access_grants = new_flat_row.access_grants,
2162+ date_closed = new_flat_row.date_closed,
2163+ latest_patch_uploaded = new_flat_row.latest_patch_uploaded
2164+ WHERE bugtask = new_flat_row.bugtask;
2165+ END IF;
2166+ RETURN FALSE;
2167+ ELSE
2168+ RETURN TRUE;
2169+ END IF;
2170+END;
2171+$$;
2172+
2173+
2174+COMMENT ON FUNCTION public.bugtask_flatten(task_id integer, check_only boolean) IS 'Create or update a BugTaskFlat row from the source tables. Returns whether the row was up to date. If check_only is true, the row is not brought up to date.';
2175+
2176+
2177+CREATE FUNCTION public.bugtask_maintain_bugtaskflat_trig() RETURNS trigger
2178+ LANGUAGE plpgsql SECURITY DEFINER
2179+ SET search_path TO 'public'
2180+ AS $$
2181+BEGIN
2182+ IF TG_OP = 'INSERT' THEN
2183+ PERFORM bugtask_flatten(NEW.id, FALSE);
2184+ ELSIF TG_OP = 'UPDATE' THEN
2185+ IF NEW.bug != OLD.bug THEN
2186+ RAISE EXCEPTION 'cannot move bugtask to a different bug';
2187+ ELSIF (NEW.product IS DISTINCT FROM OLD.product
2188+ OR NEW.productseries IS DISTINCT FROM OLD.productseries) THEN
2189+ -- product.active may differ. Do a full update.
2190+ PERFORM bugtask_flatten(NEW.id, FALSE);
2191+ ELSIF (
2192+ NEW.datecreated IS DISTINCT FROM OLD.datecreated
2193+ OR NEW.product IS DISTINCT FROM OLD.product
2194+ OR NEW.productseries IS DISTINCT FROM OLD.productseries
2195+ OR NEW.distribution IS DISTINCT FROM OLD.distribution
2196+ OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
2197+ OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
2198+ OR NEW.status IS DISTINCT FROM OLD.status
2199+ OR NEW.importance IS DISTINCT FROM OLD.importance
2200+ OR NEW.assignee IS DISTINCT FROM OLD.assignee
2201+ OR NEW.milestone IS DISTINCT FROM OLD.milestone
2202+ OR NEW.owner IS DISTINCT FROM OLD.owner
2203+ OR NEW.date_closed IS DISTINCT FROM OLD.date_closed) THEN
2204+ -- Otherwise just update the columns from bugtask.
2205+ -- Access policies and grants may have changed due to target
2206+ -- transitions, but an earlier trigger will already have
2207+ -- mirrored them to all relevant flat tasks.
2208+ UPDATE BugTaskFlat SET
2209+ datecreated = NEW.datecreated,
2210+ product = NEW.product,
2211+ productseries = NEW.productseries,
2212+ distribution = NEW.distribution,
2213+ distroseries = NEW.distroseries,
2214+ sourcepackagename = NEW.sourcepackagename,
2215+ status = NEW.status,
2216+ importance = NEW.importance,
2217+ assignee = NEW.assignee,
2218+ milestone = NEW.milestone,
2219+ owner = NEW.owner,
2220+ date_closed = NEW.date_closed
2221+ WHERE bugtask = NEW.id;
2222+ END IF;
2223+ ELSIF TG_OP = 'DELETE' THEN
2224+ PERFORM bugtask_flatten(OLD.id, FALSE);
2225+ END IF;
2226+ RETURN NULL;
2227+END;
2228+$$;
2229+
2230+
2231+CREATE FUNCTION public.bugtaskflat_maintain_bug_summary() RETURNS trigger
2232+ LANGUAGE plpgsql SECURITY DEFINER
2233+ SET search_path TO 'public'
2234+ AS $$
2235+BEGIN
2236+ IF TG_OP = 'INSERT' THEN
2237+ PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
2238+ PERFORM bug_summary_flush_temp_journal();
2239+ ELSIF TG_OP = 'DELETE' THEN
2240+ PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
2241+ PERFORM bug_summary_flush_temp_journal();
2242+ ELSIF
2243+ NEW.product IS DISTINCT FROM OLD.product
2244+ OR NEW.productseries IS DISTINCT FROM OLD.productseries
2245+ OR NEW.distribution IS DISTINCT FROM OLD.distribution
2246+ OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
2247+ OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
2248+ OR NEW.status IS DISTINCT FROM OLD.status
2249+ OR NEW.milestone IS DISTINCT FROM OLD.milestone
2250+ OR NEW.importance IS DISTINCT FROM OLD.importance
2251+ OR NEW.latest_patch_uploaded IS DISTINCT FROM OLD.latest_patch_uploaded
2252+ OR NEW.information_type IS DISTINCT FROM OLD.information_type
2253+ OR NEW.access_grants IS DISTINCT FROM OLD.access_grants
2254+ OR NEW.access_policies IS DISTINCT FROM OLD.access_policies
2255+ OR NEW.duplicateof IS DISTINCT FROM OLD.duplicateof
2256+ THEN
2257+ PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
2258+ PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
2259+ PERFORM bug_summary_flush_temp_journal();
2260+ END IF;
2261+ RETURN NULL;
2262+END;
2263+$$;
2264+
2265+
2266+CREATE FUNCTION public.build_access_cache(art_id integer, information_type integer) RETURNS record
2267+ LANGUAGE plpgsql
2268+ AS $$
2269+DECLARE
2270+ _policies integer[];
2271+ _grants integer[];
2272+ cache record;
2273+BEGIN
2274+ -- If private, grab the access control information.
2275+ -- If public, access_policies and access_grants are NULL.
2276+ -- 3 == PRIVATESECURITY, 4 == USERDATA, 5 == PROPRIETARY
2277+ -- 6 == EMBARGOED
2278+ IF information_type NOT IN (1, 2) THEN
2279+ SELECT COALESCE(array_agg(policy ORDER BY policy), ARRAY[]::integer[])
2280+ INTO _policies FROM accesspolicyartifact WHERE artifact = art_id;
2281+ SELECT COALESCE(array_agg(grantee ORDER BY grantee), ARRAY[]::integer[])
2282+ INTO _grants FROM accessartifactgrant WHERE artifact = art_id;
2283+ END IF;
2284+ cache := (_policies, _grants);
2285+ RETURN cache;
2286+END;
2287+$$;
2288+
2289+
2290+CREATE FUNCTION public.calculate_bug_heat(bug_id integer) RETURNS integer
2291+ LANGUAGE sql STABLE STRICT
2292+ AS $_$
2293+ SELECT
2294+ (CASE information_type WHEN 1 THEN 0 WHEN 2 THEN 250
2295+ WHEN 3 THEN 400 ELSE 150 END)
2296+ + (number_of_duplicates * 6)
2297+ + (users_affected_count * 4)
2298+ + (
2299+ SELECT COUNT(DISTINCT person) * 2
2300+ FROM BugSubscription
2301+ JOIN Bug AS SubBug ON BugSubscription.bug = SubBug.id
2302+ WHERE SubBug.id = $1 OR SubBug.duplicateof = $1)::integer AS heat
2303+ FROM Bug WHERE Bug.id = $1;
2304+$_$;
2305+
2306+
2307+CREATE FUNCTION public.cursor_fetch(cur refcursor, n integer) RETURNS SETOF record
2308 LANGUAGE plpgsql
2309 AS $$
2310 DECLARE
2311@@ -1217,86 +1395,86 @@
2312 $$;
2313
2314
2315-COMMENT ON FUNCTION cursor_fetch(cur refcursor, n integer) IS 'Fetch the next n items from a cursor. Work around for not being able to use FETCH inside a SELECT statement.';
2316-
2317-
2318-CREATE FUNCTION debversion(character) RETURNS debversion
2319+COMMENT ON FUNCTION public.cursor_fetch(cur refcursor, n integer) IS 'Fetch the next n items from a cursor. Work around for not being able to use FETCH inside a SELECT statement.';
2320+
2321+
2322+CREATE FUNCTION public.debversion(character) RETURNS public.debversion
2323 LANGUAGE internal IMMUTABLE STRICT
2324 AS $$rtrim1$$;
2325
2326
2327-CREATE FUNCTION debversion_cmp(version1 debversion, version2 debversion) RETURNS integer
2328+CREATE FUNCTION public.debversion_cmp(version1 public.debversion, version2 public.debversion) RETURNS integer
2329 LANGUAGE c IMMUTABLE STRICT
2330 AS '$libdir/debversion', 'debversion_cmp';
2331
2332
2333-COMMENT ON FUNCTION debversion_cmp(version1 debversion, version2 debversion) IS 'Compare Debian versions';
2334-
2335-
2336-CREATE FUNCTION debversion_eq(version1 debversion, version2 debversion) RETURNS boolean
2337+COMMENT ON FUNCTION public.debversion_cmp(version1 public.debversion, version2 public.debversion) IS 'Compare Debian versions';
2338+
2339+
2340+CREATE FUNCTION public.debversion_eq(version1 public.debversion, version2 public.debversion) RETURNS boolean
2341 LANGUAGE c IMMUTABLE STRICT
2342 AS '$libdir/debversion', 'debversion_eq';
2343
2344
2345-COMMENT ON FUNCTION debversion_eq(version1 debversion, version2 debversion) IS 'debversion equal';
2346-
2347-
2348-CREATE FUNCTION debversion_ge(version1 debversion, version2 debversion) RETURNS boolean
2349+COMMENT ON FUNCTION public.debversion_eq(version1 public.debversion, version2 public.debversion) IS 'debversion equal';
2350+
2351+
2352+CREATE FUNCTION public.debversion_ge(version1 public.debversion, version2 public.debversion) RETURNS boolean
2353 LANGUAGE c IMMUTABLE STRICT
2354 AS '$libdir/debversion', 'debversion_ge';
2355
2356
2357-COMMENT ON FUNCTION debversion_ge(version1 debversion, version2 debversion) IS 'debversion greater-than-or-equal';
2358-
2359-
2360-CREATE FUNCTION debversion_gt(version1 debversion, version2 debversion) RETURNS boolean
2361+COMMENT ON FUNCTION public.debversion_ge(version1 public.debversion, version2 public.debversion) IS 'debversion greater-than-or-equal';
2362+
2363+
2364+CREATE FUNCTION public.debversion_gt(version1 public.debversion, version2 public.debversion) RETURNS boolean
2365 LANGUAGE c IMMUTABLE STRICT
2366 AS '$libdir/debversion', 'debversion_gt';
2367
2368
2369-COMMENT ON FUNCTION debversion_gt(version1 debversion, version2 debversion) IS 'debversion greater-than';
2370-
2371-
2372-CREATE FUNCTION debversion_hash(debversion) RETURNS integer
2373+COMMENT ON FUNCTION public.debversion_gt(version1 public.debversion, version2 public.debversion) IS 'debversion greater-than';
2374+
2375+
2376+CREATE FUNCTION public.debversion_hash(public.debversion) RETURNS integer
2377 LANGUAGE c IMMUTABLE STRICT
2378 AS '$libdir/debversion', 'debversion_hash';
2379
2380
2381-CREATE FUNCTION debversion_larger(version1 debversion, version2 debversion) RETURNS debversion
2382+CREATE FUNCTION public.debversion_larger(version1 public.debversion, version2 public.debversion) RETURNS public.debversion
2383 LANGUAGE c IMMUTABLE STRICT
2384 AS '$libdir/debversion', 'debversion_larger';
2385
2386
2387-CREATE FUNCTION debversion_le(version1 debversion, version2 debversion) RETURNS boolean
2388+CREATE FUNCTION public.debversion_le(version1 public.debversion, version2 public.debversion) RETURNS boolean
2389 LANGUAGE c IMMUTABLE STRICT
2390 AS '$libdir/debversion', 'debversion_le';
2391
2392
2393-COMMENT ON FUNCTION debversion_le(version1 debversion, version2 debversion) IS 'debversion less-than-or-equal';
2394-
2395-
2396-CREATE FUNCTION debversion_lt(version1 debversion, version2 debversion) RETURNS boolean
2397+COMMENT ON FUNCTION public.debversion_le(version1 public.debversion, version2 public.debversion) IS 'debversion less-than-or-equal';
2398+
2399+
2400+CREATE FUNCTION public.debversion_lt(version1 public.debversion, version2 public.debversion) RETURNS boolean
2401 LANGUAGE c IMMUTABLE STRICT
2402 AS '$libdir/debversion', 'debversion_lt';
2403
2404
2405-COMMENT ON FUNCTION debversion_lt(version1 debversion, version2 debversion) IS 'debversion less-than';
2406-
2407-
2408-CREATE FUNCTION debversion_ne(version1 debversion, version2 debversion) RETURNS boolean
2409+COMMENT ON FUNCTION public.debversion_lt(version1 public.debversion, version2 public.debversion) IS 'debversion less-than';
2410+
2411+
2412+CREATE FUNCTION public.debversion_ne(version1 public.debversion, version2 public.debversion) RETURNS boolean
2413 LANGUAGE c IMMUTABLE STRICT
2414 AS '$libdir/debversion', 'debversion_ne';
2415
2416
2417-COMMENT ON FUNCTION debversion_ne(version1 debversion, version2 debversion) IS 'debversion not equal';
2418-
2419-
2420-CREATE FUNCTION debversion_smaller(version1 debversion, version2 debversion) RETURNS debversion
2421+COMMENT ON FUNCTION public.debversion_ne(version1 public.debversion, version2 public.debversion) IS 'debversion not equal';
2422+
2423+
2424+CREATE FUNCTION public.debversion_smaller(version1 public.debversion, version2 public.debversion) RETURNS public.debversion
2425 LANGUAGE c IMMUTABLE STRICT
2426 AS '$libdir/debversion', 'debversion_smaller';
2427
2428
2429-CREATE FUNCTION debversion_sort_key(version text) RETURNS text
2430+CREATE FUNCTION public.debversion_sort_key(version text) RETURNS text
2431 LANGUAGE plpythonu IMMUTABLE STRICT
2432 AS $_$
2433 # If this method is altered, then any functional indexes using it
2434@@ -1365,13 +1543,12 @@
2435 $_$;
2436
2437
2438-COMMENT ON FUNCTION debversion_sort_key(version text) IS 'Return a string suitable for sorting debian version strings on';
2439-
2440-
2441-CREATE FUNCTION ensure_bugsummary_temp_journal() RETURNS void
2442+COMMENT ON FUNCTION public.debversion_sort_key(version text) IS 'Return a string suitable for sorting debian version strings on';
2443+
2444+
2445+CREATE FUNCTION public.ensure_bugsummary_temp_journal() RETURNS void
2446 LANGUAGE plpgsql
2447 AS $$
2448-DECLARE
2449 BEGIN
2450 CREATE TEMPORARY TABLE bugsummary_temp_journal (
2451 LIKE bugsummary ) ON COMMIT DROP;
2452@@ -1383,48 +1560,66 @@
2453 $$;
2454
2455
2456-COMMENT ON FUNCTION ensure_bugsummary_temp_journal() IS 'Create a temporary table bugsummary_temp_journal if it does not exist.';
2457-
2458-
2459-CREATE FUNCTION generate_openid_identifier() RETURNS text
2460+COMMENT ON FUNCTION public.ensure_bugsummary_temp_journal() IS 'Create a temporary table bugsummary_temp_journal if it does not exist.';
2461+
2462+
2463+CREATE FUNCTION public.ftiupdate() RETURNS trigger
2464 LANGUAGE plpythonu
2465- AS $$
2466- from random import choice
2467-
2468- # Non display confusing characters.
2469- chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
2470-
2471- # Character length of tokens. Can be increased, decreased or even made
2472- # random - Launchpad does not care. 7 means it takes 40 bytes to store
2473- # a null-terminated Launchpad identity URL on the current domain name.
2474- length=7
2475-
2476- loop_count = 0
2477- while loop_count < 20000:
2478- # Generate a random openid_identifier
2479- oid = ''.join(choice(chars) for count in range(length))
2480-
2481- # Check if the oid is already in the db, although this is pretty
2482- # unlikely
2483- rv = plpy.execute("""
2484- SELECT COUNT(*) AS num FROM Account WHERE openid_identifier = '%s'
2485- """ % oid, 1)
2486- if rv[0]['num'] == 0:
2487- return oid
2488- loop_count += 1
2489- if loop_count == 1:
2490- plpy.warning(
2491- 'Clash generating unique openid_identifier. '
2492- 'Increase length if you see this warning too much.')
2493- plpy.error(
2494- "Unable to generate unique openid_identifier. "
2495- "Need to increase length of tokens.")
2496-$$;
2497-
2498-
2499-CREATE FUNCTION getlocalnodeid() RETURNS integer
2500+ AS $_$
2501+ new = TD["new"]
2502+ args = TD["args"][:]
2503+
2504+ # Short circuit if none of the relevant columns have been
2505+ # modified and fti is not being set to NULL (setting the fti
2506+ # column to NULL is thus how we can force a rebuild of the fti
2507+ # column).
2508+ if TD["event"] == "UPDATE" and new["fti"] != None:
2509+ old = TD["old"]
2510+ relevant_modification = False
2511+ for column_name in args[::2]:
2512+ if new[column_name] != old[column_name]:
2513+ relevant_modification = True
2514+ break
2515+ if not relevant_modification:
2516+ return "OK"
2517+
2518+ # Generate an SQL statement that turns the requested
2519+ # column values into a weighted tsvector
2520+ sql = []
2521+ for i in range(0, len(args), 2):
2522+ sql.append(
2523+ "setweight(to_tsvector('default', coalesce("
2524+ "substring(ltrim($%d) from 1 for 2500),'')),"
2525+ "CAST($%d AS \"char\"))" % (i + 1, i + 2))
2526+ args[i] = new[args[i]]
2527+
2528+ sql = "SELECT %s AS fti" % "||".join(sql)
2529+
2530+ # Execute and store in the fti column
2531+ plan = plpy.prepare(sql, ["text", "char"] * (len(args)/2))
2532+ new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
2533+
2534+ # Tell PostgreSQL we have modified the data
2535+ return "MODIFY"
2536+$_$;
2537+
2538+
2539+COMMENT ON FUNCTION public.ftiupdate() IS 'Trigger function that keeps the fti tsvector column up to date.';
2540+
2541+
2542+CREATE FUNCTION public.ftq(text) RETURNS tsquery
2543+ LANGUAGE plpythonu IMMUTABLE STRICT
2544+ AS $_$
2545+ p = plpy.prepare(
2546+ "SELECT to_tsquery('default', _ftq($1)) AS x", ["text"])
2547+ query = plpy.execute(p, args, 1)[0]["x"]
2548+ return query or None
2549+ $_$;
2550+
2551+
2552+CREATE FUNCTION public.getlocalnodeid() RETURNS integer
2553 LANGUAGE plpgsql STABLE SECURITY DEFINER
2554- SET search_path TO public
2555+ SET search_path TO 'public'
2556 AS $$
2557 DECLARE
2558 v_node_id integer;
2559@@ -1438,70 +1633,48 @@
2560 $$;
2561
2562
2563-COMMENT ON FUNCTION getlocalnodeid() IS 'Return the replication node id for this node, or NULL if not a replicated installation.';
2564-
2565-
2566-CREATE FUNCTION is_blacklisted_name(text, integer) RETURNS boolean
2567+COMMENT ON FUNCTION public.getlocalnodeid() IS 'Return the replication node id for this node, or NULL if not a replicated installation.';
2568+
2569+
2570+CREATE FUNCTION public.gitrepository_denorm_access(gitrepository_id integer) RETURNS void
2571+ LANGUAGE sql SECURITY DEFINER
2572+ SET search_path TO 'public'
2573+ AS $_$
2574+ UPDATE GitRepository
2575+ SET access_policy = policies[1], access_grants = grants
2576+ FROM
2577+ build_access_cache(
2578+ (SELECT id FROM accessartifact WHERE gitrepository = $1),
2579+ (SELECT information_type FROM gitrepository WHERE id = $1))
2580+ AS (policies integer[], grants integer[])
2581+ WHERE id = $1;
2582+$_$;
2583+
2584+
2585+CREATE FUNCTION public.gitrepository_maintain_access_cache_trig() RETURNS trigger
2586+ LANGUAGE plpgsql
2587+ AS $$
2588+BEGIN
2589+ PERFORM gitrepository_denorm_access(NEW.id);
2590+ RETURN NULL;
2591+END;
2592+$$;
2593+
2594+
2595+CREATE FUNCTION public.is_blacklisted_name(text, integer) RETURNS boolean
2596 LANGUAGE sql STABLE STRICT SECURITY DEFINER
2597- SET search_path TO public
2598+ SET search_path TO 'public'
2599 AS $_$
2600 SELECT COALESCE(name_blacklist_match($1, $2)::boolean, FALSE);
2601 $_$;
2602
2603
2604-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.';
2605-
2606-
2607-CREATE FUNCTION is_person(text) RETURNS boolean
2608- LANGUAGE sql STABLE STRICT
2609- AS $_$
2610- SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NULL;
2611-$_$;
2612-
2613-
2614-COMMENT ON FUNCTION is_person(text) IS 'True if the given name identifies a person in the Person table';
2615-
2616-
2617-CREATE FUNCTION is_printable_ascii(text) RETURNS boolean
2618- LANGUAGE plpythonu IMMUTABLE STRICT
2619- AS $_$
2620- import re, string
2621- try:
2622- text = args[0].decode("ASCII")
2623- except UnicodeError:
2624- return False
2625- if re.search(r"^[%s]*$" % re.escape(string.printable), text) is None:
2626- return False
2627- return True
2628-$_$;
2629-
2630-
2631-COMMENT ON FUNCTION is_printable_ascii(text) IS 'True if the string is pure printable US-ASCII';
2632-
2633-
2634-CREATE FUNCTION is_team(integer) RETURNS boolean
2635- LANGUAGE sql STABLE STRICT
2636- AS $_$
2637- SELECT count(*)>0 FROM Person WHERE id=$1 AND teamowner IS NOT NULL;
2638-$_$;
2639-
2640-
2641-COMMENT ON FUNCTION is_team(integer) IS 'True if the given id identifies a team in the Person table';
2642-
2643-
2644-CREATE FUNCTION is_team(text) RETURNS boolean
2645- LANGUAGE sql STABLE STRICT
2646- AS $_$
2647- SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NOT NULL;
2648-$_$;
2649-
2650-
2651-COMMENT ON FUNCTION is_team(text) IS 'True if the given name identifies a team in the Person table';
2652-
2653-
2654-CREATE FUNCTION lp_mirror_account_ins() RETURNS trigger
2655+COMMENT ON FUNCTION public.is_blacklisted_name(text, integer) IS 'Return TRUE if any regular expressions stored in the NameBlacklist table match the givenname, otherwise return FALSE.';
2656+
2657+
2658+CREATE FUNCTION public.lp_mirror_account_ins() RETURNS trigger
2659 LANGUAGE plpgsql SECURITY DEFINER
2660- SET search_path TO public
2661+ SET search_path TO 'public'
2662 AS $$
2663 BEGIN
2664 INSERT INTO lp_Account (id, openid_identifier)
2665@@ -1511,9 +1684,9 @@
2666 $$;
2667
2668
2669-CREATE FUNCTION lp_mirror_account_upd() RETURNS trigger
2670+CREATE FUNCTION public.lp_mirror_account_upd() RETURNS trigger
2671 LANGUAGE plpgsql SECURITY DEFINER
2672- SET search_path TO public
2673+ SET search_path TO 'public'
2674 AS $$
2675 BEGIN
2676 IF OLD.id <> NEW.id OR OLD.openid_identifier <> NEW.openid_identifier THEN
2677@@ -1526,9 +1699,9 @@
2678 $$;
2679
2680
2681-CREATE FUNCTION lp_mirror_del() RETURNS trigger
2682+CREATE FUNCTION public.lp_mirror_del() RETURNS trigger
2683 LANGUAGE plpgsql SECURITY DEFINER
2684- SET search_path TO public
2685+ SET search_path TO 'public'
2686 AS $$
2687 BEGIN
2688 EXECUTE 'DELETE FROM lp_' || TG_TABLE_NAME || ' WHERE id=' || OLD.id;
2689@@ -1537,9 +1710,9 @@
2690 $$;
2691
2692
2693-CREATE FUNCTION lp_mirror_openididentifier_del() RETURNS trigger
2694+CREATE FUNCTION public.lp_mirror_openididentifier_del() RETURNS trigger
2695 LANGUAGE plpgsql SECURITY DEFINER
2696- SET search_path TO public
2697+ SET search_path TO 'public'
2698 AS $$
2699 DECLARE
2700 next_identifier text;
2701@@ -1562,9 +1735,9 @@
2702 $$;
2703
2704
2705-CREATE FUNCTION lp_mirror_openididentifier_ins() RETURNS trigger
2706+CREATE FUNCTION public.lp_mirror_openididentifier_ins() RETURNS trigger
2707 LANGUAGE plpgsql SECURITY DEFINER
2708- SET search_path TO public
2709+ SET search_path TO 'public'
2710 AS $$
2711 BEGIN
2712 -- Support obsolete lp_Account.openid_identifier as best we can
2713@@ -1584,9 +1757,9 @@
2714 $$;
2715
2716
2717-CREATE FUNCTION lp_mirror_openididentifier_upd() RETURNS trigger
2718+CREATE FUNCTION public.lp_mirror_openididentifier_upd() RETURNS trigger
2719 LANGUAGE plpgsql SECURITY DEFINER
2720- SET search_path TO public
2721+ SET search_path TO 'public'
2722 AS $$
2723 BEGIN
2724 IF OLD.identifier <> NEW.identifier THEN
2725@@ -1604,9 +1777,9 @@
2726 $$;
2727
2728
2729-CREATE FUNCTION lp_mirror_person_ins() RETURNS trigger
2730+CREATE FUNCTION public.lp_mirror_person_ins() RETURNS trigger
2731 LANGUAGE plpgsql SECURITY DEFINER
2732- SET search_path TO public
2733+ SET search_path TO 'public'
2734 AS $$
2735 BEGIN
2736 INSERT INTO lp_Person (
2737@@ -1634,9 +1807,9 @@
2738 $$;
2739
2740
2741-CREATE FUNCTION lp_mirror_person_upd() RETURNS trigger
2742+CREATE FUNCTION public.lp_mirror_person_upd() RETURNS trigger
2743 LANGUAGE plpgsql SECURITY DEFINER
2744- SET search_path TO public
2745+ SET search_path TO 'public'
2746 AS $$
2747 BEGIN
2748 UPDATE lp_Person
2749@@ -1676,9 +1849,9 @@
2750 $$;
2751
2752
2753-CREATE FUNCTION lp_mirror_personlocation_ins() RETURNS trigger
2754+CREATE FUNCTION public.lp_mirror_personlocation_ins() RETURNS trigger
2755 LANGUAGE plpgsql SECURITY DEFINER
2756- SET search_path TO public
2757+ SET search_path TO 'public'
2758 AS $$
2759 BEGIN
2760 INSERT INTO lp_PersonLocation SELECT NEW.*;
2761@@ -1687,9 +1860,9 @@
2762 $$;
2763
2764
2765-CREATE FUNCTION lp_mirror_personlocation_upd() RETURNS trigger
2766+CREATE FUNCTION public.lp_mirror_personlocation_upd() RETURNS trigger
2767 LANGUAGE plpgsql SECURITY DEFINER
2768- SET search_path TO public
2769+ SET search_path TO 'public'
2770 AS $$
2771 BEGIN
2772 UPDATE lp_PersonLocation
2773@@ -1709,9 +1882,9 @@
2774 $$;
2775
2776
2777-CREATE FUNCTION lp_mirror_teamparticipation_ins() RETURNS trigger
2778+CREATE FUNCTION public.lp_mirror_teamparticipation_ins() RETURNS trigger
2779 LANGUAGE plpgsql SECURITY DEFINER
2780- SET search_path TO public
2781+ SET search_path TO 'public'
2782 AS $$
2783 BEGIN
2784 INSERT INTO lp_TeamParticipation SELECT NEW.*;
2785@@ -1720,9 +1893,9 @@
2786 $$;
2787
2788
2789-CREATE FUNCTION lp_mirror_teamparticipation_upd() RETURNS trigger
2790+CREATE FUNCTION public.lp_mirror_teamparticipation_upd() RETURNS trigger
2791 LANGUAGE plpgsql SECURITY DEFINER
2792- SET search_path TO public
2793+ SET search_path TO 'public'
2794 AS $$
2795 BEGIN
2796 UPDATE lp_TeamParticipation
2797@@ -1735,28 +1908,9 @@
2798 $$;
2799
2800
2801-CREATE FUNCTION maintain_transitively_private() RETURNS trigger
2802- LANGUAGE plpgsql
2803- AS $$
2804-BEGIN
2805- IF TG_OP = 'UPDATE' THEN
2806- IF (NEW.stacked_on IS NOT DISTINCT FROM OLD.stacked_on
2807- AND NEW.private IS NOT DISTINCT FROM OLD.private) THEN
2808- RETURN NULL;
2809- END IF;
2810- END IF;
2811- PERFORM update_transitively_private(NEW.id);
2812- RETURN NULL;
2813-END;
2814-$$;
2815-
2816-
2817-COMMENT ON FUNCTION maintain_transitively_private() IS 'Trigger maintaining the Branch transitively_private column';
2818-
2819-
2820-CREATE FUNCTION message_copy_owner_to_bugmessage() RETURNS trigger
2821+CREATE FUNCTION public.message_copy_owner_to_bugmessage() RETURNS trigger
2822 LANGUAGE plpgsql SECURITY DEFINER
2823- SET search_path TO public
2824+ SET search_path TO 'public'
2825 AS $$
2826 BEGIN
2827 IF NEW.owner != OLD.owner THEN
2828@@ -1770,12 +1924,12 @@
2829 $$;
2830
2831
2832-COMMENT ON FUNCTION message_copy_owner_to_bugmessage() IS 'Copies the message owner into bugmessage when message changes.';
2833-
2834-
2835-CREATE FUNCTION message_copy_owner_to_questionmessage() RETURNS trigger
2836+COMMENT ON FUNCTION public.message_copy_owner_to_bugmessage() IS 'Copies the message owner into bugmessage when message changes.';
2837+
2838+
2839+CREATE FUNCTION public.message_copy_owner_to_questionmessage() RETURNS trigger
2840 LANGUAGE plpgsql SECURITY DEFINER
2841- SET search_path TO public
2842+ SET search_path TO 'public'
2843 AS $$
2844 BEGIN
2845 IF NEW.owner != OLD.owner THEN
2846@@ -1789,10 +1943,10 @@
2847 $$;
2848
2849
2850-COMMENT ON FUNCTION message_copy_owner_to_questionmessage() IS 'Copies the message owner into questionmessage when message changes.';
2851-
2852-
2853-CREATE FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text
2854+COMMENT ON FUNCTION public.message_copy_owner_to_questionmessage() IS 'Copies the message owner into questionmessage when message changes.';
2855+
2856+
2857+CREATE FUNCTION public.milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text
2858 LANGUAGE plpythonu IMMUTABLE
2859 AS $$
2860 # If this method is altered, then any functional indexes using it
2861@@ -1813,10 +1967,10 @@
2862 $$;
2863
2864
2865-COMMENT ON FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) IS '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.';
2866-
2867-
2868-CREATE FUNCTION mv_branch_distribution_update() RETURNS trigger
2869+COMMENT ON FUNCTION public.milestone_sort_key(dateexpected timestamp without time zone, name text) IS '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.';
2870+
2871+
2872+CREATE FUNCTION public.mv_branch_distribution_update() RETURNS trigger
2873 LANGUAGE plpgsql
2874 AS $$
2875 BEGIN
2876@@ -1834,10 +1988,10 @@
2877 $$;
2878
2879
2880-COMMENT ON FUNCTION mv_branch_distribution_update() IS 'Maintain Branch name cache when Distribution is modified.';
2881-
2882-
2883-CREATE FUNCTION mv_branch_distroseries_update() RETURNS trigger
2884+COMMENT ON FUNCTION public.mv_branch_distribution_update() IS 'Maintain Branch name cache when Distribution is modified.';
2885+
2886+
2887+CREATE FUNCTION public.mv_branch_distroseries_update() RETURNS trigger
2888 LANGUAGE plpgsql
2889 AS $$
2890 BEGIN
2891@@ -1853,10 +2007,10 @@
2892 $$;
2893
2894
2895-COMMENT ON FUNCTION mv_branch_distroseries_update() IS 'Maintain Branch name cache when Distroseries is modified.';
2896-
2897-
2898-CREATE FUNCTION mv_branch_person_update() RETURNS trigger
2899+COMMENT ON FUNCTION public.mv_branch_distroseries_update() IS 'Maintain Branch name cache when Distroseries is modified.';
2900+
2901+
2902+CREATE FUNCTION public.mv_branch_person_update() RETURNS trigger
2903 LANGUAGE plpgsql
2904 AS $$
2905 DECLARE
2906@@ -1873,10 +2027,10 @@
2907 $$;
2908
2909
2910-COMMENT ON FUNCTION mv_branch_person_update() IS 'Maintain Branch name cache when Person is modified.';
2911-
2912-
2913-CREATE FUNCTION mv_branch_product_update() RETURNS trigger
2914+COMMENT ON FUNCTION public.mv_branch_person_update() IS 'Maintain Branch name cache when Person is modified.';
2915+
2916+
2917+CREATE FUNCTION public.mv_branch_product_update() RETURNS trigger
2918 LANGUAGE plpgsql
2919 AS $$
2920 DECLARE
2921@@ -1893,12 +2047,12 @@
2922 $$;
2923
2924
2925-COMMENT ON FUNCTION mv_branch_product_update() IS 'Maintain Branch name cache when Product is modified.';
2926-
2927-
2928-CREATE FUNCTION mv_pillarname_distribution() RETURNS trigger
2929+COMMENT ON FUNCTION public.mv_branch_product_update() IS 'Maintain Branch name cache when Product is modified.';
2930+
2931+
2932+CREATE FUNCTION public.mv_pillarname_distribution() RETURNS trigger
2933 LANGUAGE plpgsql SECURITY DEFINER
2934- SET search_path TO public
2935+ SET search_path TO 'public'
2936 AS $$
2937 BEGIN
2938 IF TG_OP = 'INSERT' THEN
2939@@ -1912,12 +2066,12 @@
2940 $$;
2941
2942
2943-COMMENT ON FUNCTION mv_pillarname_distribution() IS 'Trigger maintaining the PillarName table';
2944-
2945-
2946-CREATE FUNCTION mv_pillarname_product() RETURNS trigger
2947+COMMENT ON FUNCTION public.mv_pillarname_distribution() IS 'Trigger maintaining the PillarName table';
2948+
2949+
2950+CREATE FUNCTION public.mv_pillarname_product() RETURNS trigger
2951 LANGUAGE plpgsql SECURITY DEFINER
2952- SET search_path TO public
2953+ SET search_path TO 'public'
2954 AS $$
2955 BEGIN
2956 IF TG_OP = 'INSERT' THEN
2957@@ -1932,12 +2086,12 @@
2958 $$;
2959
2960
2961-COMMENT ON FUNCTION mv_pillarname_product() IS 'Trigger maintaining the PillarName table';
2962-
2963-
2964-CREATE FUNCTION mv_pillarname_project() RETURNS trigger
2965+COMMENT ON FUNCTION public.mv_pillarname_product() IS 'Trigger maintaining the PillarName table';
2966+
2967+
2968+CREATE FUNCTION public.mv_pillarname_project() RETURNS trigger
2969 LANGUAGE plpgsql SECURITY DEFINER
2970- SET search_path TO public
2971+ SET search_path TO 'public'
2972 AS $$
2973 BEGIN
2974 IF TG_OP = 'INSERT' THEN
2975@@ -1952,221 +2106,47 @@
2976 $$;
2977
2978
2979-COMMENT ON FUNCTION mv_pillarname_project() IS 'Trigger maintaining the PillarName table';
2980-
2981-
2982-CREATE FUNCTION mv_pofiletranslator_pomsgset() RETURNS trigger
2983- LANGUAGE plpgsql
2984- AS $$
2985-BEGIN
2986- IF TG_OP = 'DELETE' THEN
2987- RAISE EXCEPTION
2988- 'Deletions from POMsgSet not supported by the POFileTranslator materialized view';
2989- ELSIF TG_OP = 'UPDATE' THEN
2990- IF OLD.pofile != NEW.pofile THEN
2991- RAISE EXCEPTION
2992- 'Changing POMsgSet.pofile not supported by the POFileTranslator materialized view';
2993- END IF;
2994- END IF;
2995- RETURN NEW;
2996-END;
2997-$$;
2998-
2999-
3000-COMMENT ON FUNCTION mv_pofiletranslator_pomsgset() IS 'Trigger enforing no POMsgSet deletions or POMsgSet.pofile changes';
3001-
3002-
3003-CREATE FUNCTION mv_pofiletranslator_posubmission() RETURNS trigger
3004- LANGUAGE plpgsql SECURITY DEFINER
3005- AS $$
3006-DECLARE
3007- v_pofile INTEGER;
3008- v_trash_old BOOLEAN;
3009-BEGIN
3010- -- If we are deleting a row, we need to remove the existing
3011- -- POFileTranslator row and reinsert the historical data if it exists.
3012- -- We also treat UPDATEs that change the key (person, pofile) the same
3013- -- as deletes. UPDATEs that don't change these columns are treated like
3014- -- INSERTs below.
3015- IF TG_OP = 'INSERT' THEN
3016- v_trash_old := FALSE;
3017- ELSIF TG_OP = 'DELETE' THEN
3018- v_trash_old := TRUE;
3019- ELSE -- UPDATE
3020- v_trash_old = (
3021- OLD.person != NEW.person OR OLD.pomsgset != NEW.pomsgset
3022- );
3023- END IF;
3024-
3025- IF v_trash_old THEN
3026-
3027- -- Delete the old record.
3028- DELETE FROM POFileTranslator USING POMsgSet
3029- WHERE POFileTranslator.pofile = POMsgSet.pofile
3030- AND POFileTranslator.person = OLD.person
3031- AND POMsgSet.id = OLD.pomsgset;
3032-
3033- -- Insert a past record if there is one.
3034- INSERT INTO POFileTranslator (
3035- person, pofile, latest_posubmission, date_last_touched
3036- )
3037- SELECT DISTINCT ON (POSubmission.person, POMsgSet.pofile)
3038- POSubmission.person, POMsgSet.pofile,
3039- POSubmission.id, POSubmission.datecreated
3040- FROM POSubmission, POMsgSet
3041- WHERE POSubmission.pomsgset = POMsgSet.id
3042- AND POSubmission.pomsgset = OLD.pomsgset
3043- AND POSubmission.person = OLD.person
3044- ORDER BY
3045- POSubmission.person, POMsgSet.pofile,
3046- POSubmission.datecreated DESC, POSubmission.id DESC;
3047-
3048- -- No NEW with DELETE, so we can short circuit and leave.
3049- IF TG_OP = 'DELETE' THEN
3050- RETURN NULL; -- Ignored because this is an AFTER trigger
3051- END IF;
3052- END IF;
3053-
3054- -- Get our new pofile id
3055- SELECT INTO v_pofile POMsgSet.pofile FROM POMsgSet
3056- WHERE POMsgSet.id = NEW.pomsgset;
3057-
3058- -- Standard 'upsert' loop to avoid race conditions.
3059- LOOP
3060- UPDATE POFileTranslator
3061- SET
3062- date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
3063- latest_posubmission = NEW.id
3064- WHERE
3065- person = NEW.person
3066- AND pofile = v_pofile;
3067- IF found THEN
3068- RETURN NULL; -- Return value ignored as this is an AFTER trigger
3069- END IF;
3070-
3071- BEGIN
3072- INSERT INTO POFileTranslator (person, pofile, latest_posubmission)
3073- VALUES (NEW.person, v_pofile, NEW.id);
3074- RETURN NULL; -- Return value ignored as this is an AFTER trigger
3075- EXCEPTION WHEN unique_violation THEN
3076- -- do nothing
3077- END;
3078- END LOOP;
3079-END;
3080-$$;
3081-
3082-
3083-COMMENT ON FUNCTION mv_pofiletranslator_posubmission() IS 'Trigger maintaining the POFileTranslator table';
3084-
3085-
3086-CREATE FUNCTION mv_pofiletranslator_translationmessage() RETURNS trigger
3087- LANGUAGE plpgsql SECURITY DEFINER
3088- SET search_path TO public
3089- AS $$
3090-DECLARE
3091- v_trash_old BOOLEAN;
3092-BEGIN
3093- -- If we are deleting a row, we need to remove the existing
3094- -- POFileTranslator row and reinsert the historical data if it exists.
3095- -- We also treat UPDATEs that change the key (submitter) the same
3096- -- as deletes. UPDATEs that don't change these columns are treated like
3097- -- INSERTs below.
3098- IF TG_OP = 'INSERT' THEN
3099- v_trash_old := FALSE;
3100- ELSIF TG_OP = 'DELETE' THEN
3101- v_trash_old := TRUE;
3102- ELSE -- UPDATE
3103- v_trash_old = (
3104- OLD.submitter != NEW.submitter
3105- );
3106- END IF;
3107-
3108- IF v_trash_old THEN
3109- -- Was this somebody's most-recently-changed message?
3110- -- If so, delete the entry for that change.
3111- DELETE FROM POFileTranslator
3112- WHERE latest_message = OLD.id;
3113- IF FOUND THEN
3114- -- We deleted the entry for somebody's latest contribution.
3115- -- Find that person's latest remaining contribution and
3116- -- create a new record for that.
3117- INSERT INTO POFileTranslator (
3118- person, pofile, latest_message, date_last_touched
3119- )
3120- SELECT DISTINCT ON (person, pofile.id)
3121- new_latest_message.submitter AS person,
3122- pofile.id,
3123- new_latest_message.id,
3124- greatest(new_latest_message.date_created,
3125- new_latest_message.date_reviewed)
3126- FROM POFile
3127- JOIN TranslationTemplateItem AS old_template_item
3128- ON OLD.potmsgset = old_template_item.potmsgset AND
3129- old_template_item.potemplate = pofile.potemplate AND
3130- pofile.language = OLD.language
3131- JOIN TranslationTemplateItem AS new_template_item
3132- ON (old_template_item.potemplate =
3133- new_template_item.potemplate)
3134- JOIN TranslationMessage AS new_latest_message
3135- ON new_latest_message.potmsgset =
3136- new_template_item.potmsgset AND
3137- new_latest_message.language = OLD.language
3138- LEFT OUTER JOIN POfileTranslator AS ExistingEntry
3139- ON ExistingEntry.person = OLD.submitter AND
3140- ExistingEntry.pofile = POFile.id
3141- WHERE
3142- new_latest_message.submitter = OLD.submitter AND
3143- ExistingEntry IS NULL
3144- ORDER BY new_latest_message.submitter, pofile.id,
3145- new_latest_message.date_created DESC,
3146- new_latest_message.id DESC;
3147- END IF;
3148-
3149- -- No NEW with DELETE, so we can short circuit and leave.
3150- IF TG_OP = 'DELETE' THEN
3151- RETURN NULL; -- Ignored because this is an AFTER trigger
3152- END IF;
3153- END IF;
3154-
3155- -- Standard 'upsert' loop to avoid race conditions.
3156- LOOP
3157- UPDATE POFileTranslator
3158- SET
3159- date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
3160- latest_message = NEW.id
3161- FROM POFile, TranslationTemplateItem
3162- WHERE person = NEW.submitter AND
3163- TranslationTemplateItem.potmsgset=NEW.potmsgset AND
3164- TranslationTemplateItem.potemplate=pofile.potemplate AND
3165- pofile.language=NEW.language AND
3166- POFileTranslator.pofile = pofile.id;
3167- IF found THEN
3168- RETURN NULL; -- Return value ignored as this is an AFTER trigger
3169- END IF;
3170-
3171- BEGIN
3172- INSERT INTO POFileTranslator (person, pofile, latest_message)
3173- SELECT DISTINCT ON (NEW.submitter, pofile.id)
3174- NEW.submitter, pofile.id, NEW.id
3175- FROM TranslationTemplateItem
3176- JOIN POFile
3177- ON pofile.language = NEW.language AND
3178- pofile.potemplate = translationtemplateitem.potemplate
3179- WHERE
3180- TranslationTemplateItem.potmsgset = NEW.potmsgset;
3181- RETURN NULL; -- Return value ignored as this is an AFTER trigger
3182- EXCEPTION WHEN unique_violation THEN
3183- -- do nothing
3184- END;
3185- END LOOP;
3186-END;
3187-$$;
3188-
3189-
3190-COMMENT ON FUNCTION mv_pofiletranslator_translationmessage() IS 'Trigger maintaining the POFileTranslator table';
3191-
3192-
3193-CREATE FUNCTION mv_validpersonorteamcache_emailaddress() RETURNS trigger
3194+COMMENT ON FUNCTION public.mv_pillarname_project() IS 'Trigger maintaining the PillarName table';
3195+
3196+
3197+CREATE FUNCTION public.mv_pofiletranslator_translationmessage() RETURNS trigger
3198+ LANGUAGE plpgsql SECURITY DEFINER
3199+ SET search_path TO 'public'
3200+ AS $$
3201+BEGIN
3202+ -- Update any existing entries.
3203+ UPDATE POFileTranslator
3204+ SET date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
3205+ FROM POFile, TranslationTemplateItem
3206+ WHERE person = NEW.submitter AND
3207+ TranslationTemplateItem.potmsgset = NEW.potmsgset AND
3208+ TranslationTemplateItem.potemplate = POFile.potemplate AND
3209+ POFile.language = NEW.language AND
3210+ POFileTranslator.pofile = POFile.id;
3211+
3212+ -- Insert any missing entries.
3213+ INSERT INTO POFileTranslator (person, pofile)
3214+ SELECT DISTINCT NEW.submitter, POFile.id
3215+ FROM TranslationTemplateItem
3216+ JOIN POFile ON
3217+ POFile.language = NEW.language AND
3218+ POFile.potemplate = TranslationTemplateItem.potemplate
3219+ WHERE
3220+ TranslationTemplateItem.potmsgset = NEW.potmsgset AND
3221+ NOT EXISTS (
3222+ SELECT *
3223+ FROM POFileTranslator
3224+ WHERE person = NEW.submitter AND pofile = POFile.id
3225+ );
3226+ RETURN NULL;
3227+END;
3228+$$;
3229+
3230+
3231+COMMENT ON FUNCTION public.mv_pofiletranslator_translationmessage() IS 'Trigger maintaining the POFileTranslator table';
3232+
3233+
3234+CREATE FUNCTION public.mv_validpersonorteamcache_emailaddress() RETURNS trigger
3235 LANGUAGE plpythonu SECURITY DEFINER
3236 AS $_$
3237 # This trigger function keeps the ValidPersonOrTeamCache materialized
3238@@ -2258,10 +2238,10 @@
3239 $_$;
3240
3241
3242-COMMENT ON FUNCTION mv_validpersonorteamcache_emailaddress() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the EmailAddress table';
3243-
3244-
3245-CREATE FUNCTION mv_validpersonorteamcache_person() RETURNS trigger
3246+COMMENT ON FUNCTION public.mv_validpersonorteamcache_emailaddress() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the EmailAddress table';
3247+
3248+
3249+CREATE FUNCTION public.mv_validpersonorteamcache_person() RETURNS trigger
3250 LANGUAGE plpythonu SECURITY DEFINER
3251 AS $_$
3252 # This trigger function could be simplified by simply issuing
3253@@ -2322,12 +2302,12 @@
3254 $_$;
3255
3256
3257-COMMENT ON FUNCTION mv_validpersonorteamcache_person() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the Person table';
3258-
3259-
3260-CREATE FUNCTION name_blacklist_match(text, integer) RETURNS integer
3261+COMMENT ON FUNCTION public.mv_validpersonorteamcache_person() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the Person table';
3262+
3263+
3264+CREATE FUNCTION public.name_blacklist_match(text, integer) RETURNS integer
3265 LANGUAGE plpythonu STABLE STRICT SECURITY DEFINER
3266- SET search_path TO public
3267+ SET search_path TO 'public'
3268 AS $_$
3269 import re
3270 name = args[0].decode("UTF-8")
3271@@ -2396,10 +2376,10 @@
3272 $_$;
3273
3274
3275-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.';
3276-
3277-
3278-CREATE FUNCTION null_count(p_values anyarray) RETURNS integer
3279+COMMENT ON FUNCTION public.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.';
3280+
3281+
3282+CREATE FUNCTION public.null_count(p_values anyarray) RETURNS integer
3283 LANGUAGE plpgsql IMMUTABLE STRICT
3284 AS $$
3285 DECLARE
3286@@ -2416,10 +2396,10 @@
3287 $$;
3288
3289
3290-COMMENT ON FUNCTION null_count(p_values anyarray) IS 'Return the number of NULLs in the first row of the given array.';
3291-
3292-
3293-CREATE FUNCTION packageset_deleted_trig() RETURNS trigger
3294+COMMENT ON FUNCTION public.null_count(p_values anyarray) IS 'Return the number of NULLs in the first row of the given array.';
3295+
3296+
3297+CREATE FUNCTION public.packageset_deleted_trig() RETURNS trigger
3298 LANGUAGE plpgsql
3299 AS $$
3300 BEGIN
3301@@ -2436,10 +2416,10 @@
3302 $$;
3303
3304
3305-COMMENT ON FUNCTION packageset_deleted_trig() IS 'Remove any DAG edges leading to/from the deleted package set.';
3306-
3307-
3308-CREATE FUNCTION packageset_inserted_trig() RETURNS trigger
3309+COMMENT ON FUNCTION public.packageset_deleted_trig() IS 'Remove any DAG edges leading to/from the deleted package set.';
3310+
3311+
3312+CREATE FUNCTION public.packageset_inserted_trig() RETURNS trigger
3313 LANGUAGE plpgsql
3314 AS $$
3315 BEGIN
3316@@ -2453,10 +2433,10 @@
3317 $$;
3318
3319
3320-COMMENT ON FUNCTION packageset_inserted_trig() IS 'Insert self-referencing DAG edge when a new package set is inserted.';
3321-
3322-
3323-CREATE FUNCTION packagesetinclusion_deleted_trig() RETURNS trigger
3324+COMMENT ON FUNCTION public.packageset_inserted_trig() IS 'Insert self-referencing DAG edge when a new package set is inserted.';
3325+
3326+
3327+CREATE FUNCTION public.packagesetinclusion_deleted_trig() RETURNS trigger
3328 LANGUAGE plpgsql
3329 AS $$
3330 BEGIN
3331@@ -2524,10 +2504,10 @@
3332 $$;
3333
3334
3335-COMMENT ON FUNCTION packagesetinclusion_deleted_trig() IS 'Maintain the transitive closure in the DAG when an edge leading to/from a package set is deleted.';
3336-
3337-
3338-CREATE FUNCTION packagesetinclusion_inserted_trig() RETURNS trigger
3339+COMMENT ON FUNCTION public.packagesetinclusion_deleted_trig() IS 'Maintain the transitive closure in the DAG when an edge leading to/from a package set is deleted.';
3340+
3341+
3342+CREATE FUNCTION public.packagesetinclusion_inserted_trig() RETURNS trigger
3343 LANGUAGE plpgsql
3344 AS $$
3345 BEGIN
3346@@ -2598,10 +2578,10 @@
3347 $$;
3348
3349
3350-COMMENT ON FUNCTION packagesetinclusion_inserted_trig() IS 'Maintain the transitive closure in the DAG for a newly inserted edge leading to/from a package set.';
3351-
3352-
3353-CREATE FUNCTION person_sort_key(displayname text, name text) RETURNS text
3354+COMMENT ON FUNCTION public.packagesetinclusion_inserted_trig() IS 'Maintain the transitive closure in the DAG for a newly inserted edge leading to/from a package set.';
3355+
3356+
3357+CREATE FUNCTION public.person_sort_key(displayname text, name text) RETURNS text
3358 LANGUAGE plpythonu IMMUTABLE STRICT
3359 AS $$
3360 # NB: If this implementation is changed, the person_sort_idx needs to be
3361@@ -2623,32 +2603,17 @@
3362 $$;
3363
3364
3365-COMMENT ON FUNCTION person_sort_key(displayname text, name text) IS 'Return a string suitable for sorting people on, generated by stripping noise out of displayname and concatenating name';
3366-
3367-
3368-CREATE FUNCTION pgstattuple(text) RETURNS pgstattuple_type
3369- LANGUAGE c STRICT
3370- AS '$libdir/pgstattuple', 'pgstattuple';
3371-
3372-
3373-CREATE FUNCTION pgstattuple(oid) RETURNS pgstattuple_type
3374- LANGUAGE c STRICT
3375- AS '$libdir/pgstattuple', 'pgstattuplebyid';
3376-
3377-
3378-CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
3379+COMMENT ON FUNCTION public.person_sort_key(displayname text, name text) IS 'Return a string suitable for sorting people on, generated by stripping noise out of displayname and concatenating name';
3380+
3381+
3382+CREATE FUNCTION public.plpgsql_call_handler() RETURNS language_handler
3383 LANGUAGE c
3384 AS '$libdir/plpgsql', 'plpgsql_call_handler';
3385
3386
3387-CREATE FUNCTION plpython_call_handler() RETURNS language_handler
3388- LANGUAGE c
3389- AS '$libdir/plpython', 'plpython_call_handler';
3390-
3391-
3392-CREATE FUNCTION questionmessage_copy_owner_from_message() RETURNS trigger
3393+CREATE FUNCTION public.questionmessage_copy_owner_from_message() RETURNS trigger
3394 LANGUAGE plpgsql SECURITY DEFINER
3395- SET search_path TO public
3396+ SET search_path TO 'public'
3397 AS $$
3398 BEGIN
3399 IF TG_OP = 'INSERT' THEN
3400@@ -2671,12 +2636,12 @@
3401 $$;
3402
3403
3404-COMMENT ON FUNCTION questionmessage_copy_owner_from_message() IS 'Copies the message owner into QuestionMessage when QuestionMessage changes.';
3405-
3406-
3407-CREATE FUNCTION replication_lag() RETURNS interval
3408+COMMENT ON FUNCTION public.questionmessage_copy_owner_from_message() IS 'Copies the message owner into QuestionMessage when QuestionMessage changes.';
3409+
3410+
3411+CREATE FUNCTION public.replication_lag() RETURNS interval
3412 LANGUAGE plpgsql STABLE SECURITY DEFINER
3413- SET search_path TO public
3414+ SET search_path TO 'public'
3415 AS $$
3416 DECLARE
3417 v_lag interval;
3418@@ -2693,12 +2658,12 @@
3419 $$;
3420
3421
3422-COMMENT ON FUNCTION replication_lag() IS '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.';
3423-
3424-
3425-CREATE FUNCTION replication_lag(node_id integer) RETURNS interval
3426+COMMENT ON FUNCTION public.replication_lag() IS '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.';
3427+
3428+
3429+CREATE FUNCTION public.replication_lag(node_id integer) RETURNS interval
3430 LANGUAGE plpgsql STABLE SECURITY DEFINER
3431- SET search_path TO public
3432+ SET search_path TO 'public'
3433 AS $$
3434 DECLARE
3435 v_lag interval;
3436@@ -2717,10 +2682,10 @@
3437 $$;
3438
3439
3440-COMMENT ON FUNCTION replication_lag(node_id integer) IS '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.';
3441-
3442-
3443-CREATE FUNCTION sane_version(text) RETURNS boolean
3444+COMMENT ON FUNCTION public.replication_lag(node_id integer) IS '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.';
3445+
3446+
3447+CREATE FUNCTION public.sane_version(text) RETURNS boolean
3448 LANGUAGE plpythonu IMMUTABLE STRICT
3449 AS $_$
3450 import re
3451@@ -2733,12 +2698,12 @@
3452 $_$;
3453
3454
3455-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';
3456-
3457-
3458-CREATE FUNCTION set_bug_date_last_message() RETURNS trigger
3459+COMMENT ON FUNCTION public.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';
3460+
3461+
3462+CREATE FUNCTION public.set_bug_date_last_message() RETURNS trigger
3463 LANGUAGE plpgsql SECURITY DEFINER
3464- SET search_path TO public
3465+ SET search_path TO 'public'
3466 AS $$
3467 BEGIN
3468 IF TG_OP = 'INSERT' THEN
3469@@ -2763,10 +2728,10 @@
3470 $$;
3471
3472
3473-COMMENT ON FUNCTION set_bug_date_last_message() IS 'AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column';
3474-
3475-
3476-CREATE FUNCTION set_bug_message_count() RETURNS trigger
3477+COMMENT ON FUNCTION public.set_bug_date_last_message() IS 'AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column';
3478+
3479+
3480+CREATE FUNCTION public.set_bug_message_count() RETURNS trigger
3481 LANGUAGE plpgsql
3482 AS $$
3483 BEGIN
3484@@ -2791,10 +2756,10 @@
3485 $$;
3486
3487
3488-COMMENT ON FUNCTION set_bug_message_count() IS 'AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column';
3489-
3490-
3491-CREATE FUNCTION set_bug_number_of_duplicates() RETURNS trigger
3492+COMMENT ON FUNCTION public.set_bug_message_count() IS 'AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column';
3493+
3494+
3495+CREATE FUNCTION public.set_bug_number_of_duplicates() RETURNS trigger
3496 LANGUAGE plpgsql
3497 AS $$
3498 BEGIN
3499@@ -2826,10 +2791,10 @@
3500 $$;
3501
3502
3503-COMMENT ON FUNCTION set_bug_number_of_duplicates() IS 'AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column';
3504-
3505-
3506-CREATE FUNCTION set_bug_users_affected_count() RETURNS trigger
3507+COMMENT ON FUNCTION public.set_bug_number_of_duplicates() IS 'AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column';
3508+
3509+
3510+CREATE FUNCTION public.set_bug_users_affected_count() RETURNS trigger
3511 LANGUAGE plpgsql
3512 AS $$
3513 BEGIN
3514@@ -2878,7 +2843,7 @@
3515 $$;
3516
3517
3518-CREATE FUNCTION set_bugtask_date_milestone_set() RETURNS trigger
3519+CREATE FUNCTION public.set_bugtask_date_milestone_set() RETURNS trigger
3520 LANGUAGE plpgsql
3521 AS $$
3522 BEGIN
3523@@ -2924,10 +2889,10 @@
3524 $$;
3525
3526
3527-COMMENT ON FUNCTION set_bugtask_date_milestone_set() IS 'Update BugTask.date_milestone_set when BugTask.milestone is changed.';
3528-
3529-
3530-CREATE FUNCTION set_date_status_set() RETURNS trigger
3531+COMMENT ON FUNCTION public.set_bugtask_date_milestone_set() IS 'Update BugTask.date_milestone_set when BugTask.milestone is changed.';
3532+
3533+
3534+CREATE FUNCTION public.set_date_status_set() RETURNS trigger
3535 LANGUAGE plpgsql
3536 AS $$
3537 BEGIN
3538@@ -2939,74 +2904,10 @@
3539 $$;
3540
3541
3542-COMMENT ON FUNCTION set_date_status_set() IS 'BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.';
3543-
3544-
3545-CREATE FUNCTION set_openid_identifier() RETURNS trigger
3546- LANGUAGE plpythonu
3547- AS $$
3548- # If someone is trying to explicitly set the openid_identifier, let them.
3549- # This also causes openid_identifiers to be left alone if this is an
3550- # UPDATE trigger.
3551- if TD['new']['openid_identifier'] is not None:
3552- return None
3553-
3554- from random import choice
3555-
3556- # Non display confusing characters
3557- chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
3558-
3559- # character length of tokens. Can be increased, decreased or even made
3560- # random - Launchpad does not care. 7 means it takes 40 bytes to store
3561- # a null-terminated Launchpad identity URL on the current domain name.
3562- length=7
3563-
3564- loop_count = 0
3565- while loop_count < 20000:
3566- # Generate a random openid_identifier
3567- oid = ''.join(choice(chars) for count in range(length))
3568-
3569- # Check if the oid is already in the db, although this is pretty
3570- # unlikely
3571- rv = plpy.execute("""
3572- SELECT COUNT(*) AS num FROM Person WHERE openid_identifier = '%s'
3573- """ % oid, 1)
3574- if rv[0]['num'] == 0:
3575- TD['new']['openid_identifier'] = oid
3576- return "MODIFY"
3577- loop_count += 1
3578- if loop_count == 1:
3579- plpy.warning(
3580- 'Clash generating unique openid_identifier. '
3581- 'Increase length if you see this warning too much.')
3582- plpy.error(
3583- "Unable to generate unique openid_identifier. "
3584- "Need to increase length of tokens.")
3585-$$;
3586-
3587-
3588-CREATE FUNCTION set_shipit_normalized_address() RETURNS trigger
3589- LANGUAGE plpgsql
3590- AS $$
3591- BEGIN
3592- NEW.normalized_address =
3593- lower(
3594- -- Strip off everything that's not alphanumeric
3595- -- characters.
3596- regexp_replace(
3597- coalesce(NEW.addressline1, '') || ' ' ||
3598- coalesce(NEW.addressline2, '') || ' ' ||
3599- coalesce(NEW.city, ''),
3600- '[^a-zA-Z0-9]+', '', 'g'));
3601- RETURN NEW;
3602- END;
3603-$$;
3604-
3605-
3606-COMMENT ON FUNCTION set_shipit_normalized_address() IS 'Store a normalized concatenation of the request''s address into the normalized_address column.';
3607-
3608-
3609-CREATE FUNCTION sha1(text) RETURNS character
3610+COMMENT ON FUNCTION public.set_date_status_set() IS 'BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.';
3611+
3612+
3613+CREATE FUNCTION public.sha1(text) RETURNS character
3614 LANGUAGE plpythonu IMMUTABLE STRICT
3615 AS $$
3616 import hashlib
3617@@ -3014,56 +2915,63 @@
3618 $$;
3619
3620
3621-COMMENT ON FUNCTION sha1(text) IS 'Return the SHA1 one way cryptographic hash as a string of 40 hex digits';
3622-
3623-
3624-CREATE FUNCTION summarise_bug(bug_row bug) RETURNS void
3625- LANGUAGE plpgsql
3626- AS $$
3627-DECLARE
3628- d bugsummary%ROWTYPE;
3629-BEGIN
3630- PERFORM ensure_bugsummary_temp_journal();
3631- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
3632- d.count = 1;
3633- PERFORM bug_summary_temp_journal_ins(d);
3634- END LOOP;
3635-END;
3636-$$;
3637-
3638-
3639-COMMENT ON FUNCTION summarise_bug(bug_row bug) IS 'AFTER summarise a bug row into bugsummary.';
3640-
3641-
3642-CREATE FUNCTION ulower(text) RETURNS text
3643+COMMENT ON FUNCTION public.sha1(text) IS 'Return the SHA1 one way cryptographic hash as a string of 40 hex digits';
3644+
3645+
3646+CREATE FUNCTION public.specification_denorm_access(spec_id integer) RETURNS void
3647+ LANGUAGE sql SECURITY DEFINER
3648+ SET search_path TO 'public'
3649+ AS $_$
3650+ UPDATE specification
3651+ SET access_policy = policies[1], access_grants = grants
3652+ FROM
3653+ build_access_cache(
3654+ (SELECT id FROM accessartifact WHERE specification = $1),
3655+ (SELECT information_type FROM specification WHERE id = $1))
3656+ AS (policies integer[], grants integer[])
3657+ WHERE id = $1;
3658+$_$;
3659+
3660+
3661+CREATE FUNCTION public.specification_maintain_access_cache_trig() RETURNS trigger
3662+ LANGUAGE plpgsql
3663+ AS $$
3664+BEGIN
3665+ PERFORM specification_denorm_access(NEW.id);
3666+ RETURN NULL;
3667+END;
3668+$$;
3669+
3670+
3671+CREATE FUNCTION public.summarise_bug(bug integer) RETURNS void
3672+ LANGUAGE plpgsql
3673+ AS $$
3674+BEGIN
3675+ PERFORM bugsummary_journal_bug(bug_row(bug), 1);
3676+END;
3677+$$;
3678+
3679+
3680+CREATE FUNCTION public.ulower(text) RETURNS text
3681 LANGUAGE plpythonu IMMUTABLE STRICT
3682 AS $$
3683 return args[0].decode('utf8').lower().encode('utf8')
3684 $$;
3685
3686
3687-COMMENT ON FUNCTION ulower(text) IS 'Return the lower case version of a UTF-8 encoded string.';
3688-
3689-
3690-CREATE FUNCTION unsummarise_bug(bug_row bug) RETURNS void
3691+COMMENT ON FUNCTION public.ulower(text) IS 'Return the lower case version of a UTF-8 encoded string.';
3692+
3693+
3694+CREATE FUNCTION public.unsummarise_bug(bug integer) RETURNS void
3695 LANGUAGE plpgsql
3696 AS $$
3697-DECLARE
3698- d bugsummary%ROWTYPE;
3699 BEGIN
3700- PERFORM ensure_bugsummary_temp_journal();
3701- FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
3702- d.count = -1;
3703- PERFORM bug_summary_temp_journal_ins(d);
3704- END LOOP;
3705+ PERFORM bugsummary_journal_bug(bug_row(bug), -1);
3706 END;
3707 $$;
3708
3709
3710-COMMENT ON FUNCTION unsummarise_bug(bug_row bug) IS 'AFTER unsummarise a bug row from bugsummary.';
3711-
3712-
3713-CREATE FUNCTION update_branch_name_cache() RETURNS trigger
3714+CREATE FUNCTION public.update_branch_name_cache() RETURNS trigger
3715 LANGUAGE plpgsql
3716 AS $$
3717 DECLARE
3718@@ -3075,7 +2983,7 @@
3719 OR NEW.unique_name IS NULL
3720 OR OLD.owner_name <> NEW.owner_name
3721 OR OLD.unique_name <> NEW.unique_name
3722- OR (NEW.target_suffix IS NULL <> OLD.target_suffix IS NULL)
3723+ OR ((NEW.target_suffix IS NULL) <> (OLD.target_suffix IS NULL))
3724 OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')
3725 OR OLD.name <> NEW.name
3726 OR OLD.owner <> NEW.owner
3727@@ -3111,12 +3019,12 @@
3728 $$;
3729
3730
3731-COMMENT ON FUNCTION update_branch_name_cache() IS 'Maintain the cached name columns in Branch.';
3732-
3733-
3734-CREATE FUNCTION update_database_disk_utilization() RETURNS void
3735+COMMENT ON FUNCTION public.update_branch_name_cache() IS 'Maintain the cached name columns in Branch.';
3736+
3737+
3738+CREATE FUNCTION public.update_database_disk_utilization() RETURNS void
3739 LANGUAGE sql SECURITY DEFINER
3740- SET search_path TO public
3741+ SET search_path TO 'public'
3742 AS $$
3743 INSERT INTO DatabaseDiskUtilization
3744 SELECT
3745@@ -3136,6 +3044,7 @@
3746 (stat).free_space,
3747 (stat).free_percent
3748 FROM (
3749+ -- Tables
3750 SELECT
3751 pg_namespace.nspname AS namespace,
3752 pg_class.relname AS name,
3753@@ -3151,6 +3060,7 @@
3754
3755 UNION ALL
3756
3757+ -- Indexes
3758 SELECT
3759 pg_namespace_table.nspname AS namespace,
3760 pg_class_table.relname AS name,
3761@@ -3163,12 +3073,15 @@
3762 pg_namespace AS pg_namespace_index,
3763 pg_class AS pg_class_table,
3764 pg_class AS pg_class_index,
3765- pg_index
3766+ pg_index,
3767+ pg_am
3768 WHERE
3769 pg_class_index.relkind = 'i'
3770+ AND pg_am.amname <> 'gin' -- pgstattuple doesn't support GIN
3771 AND pg_table_is_visible(pg_class_table.oid)
3772 AND pg_class_index.relnamespace = pg_namespace_index.oid
3773 AND pg_class_table.relnamespace = pg_namespace_table.oid
3774+ AND pg_class_index.relam = pg_am.oid
3775 AND pg_index.indexrelid = pg_class_index.oid
3776 AND pg_index.indrelid = pg_class_table.oid
3777
3778@@ -3208,20 +3121,22 @@
3779 pg_namespace AS pg_namespace_index,
3780 pg_class AS pg_class_table,
3781 pg_class AS pg_class_index,
3782- pg_class AS pg_class_toast
3783+ pg_class AS pg_class_toast,
3784+ pg_index
3785 WHERE
3786 pg_class_table.relnamespace = pg_namespace_table.oid
3787 AND pg_table_is_visible(pg_class_table.oid)
3788 AND pg_class_index.relnamespace = pg_namespace_index.oid
3789 AND pg_class_table.reltoastrelid = pg_class_toast.oid
3790- AND pg_class_index.oid = pg_class_toast.reltoastidxid
3791+ AND pg_class_index.oid = pg_index.indexrelid
3792+ AND pg_index.indrelid = pg_class_toast.oid
3793 ) AS whatever;
3794 $$;
3795
3796
3797-CREATE FUNCTION update_database_stats() RETURNS void
3798+CREATE FUNCTION public.update_database_stats() RETURNS void
3799 LANGUAGE plpythonu SECURITY DEFINER
3800- SET search_path TO public
3801+ SET search_path TO 'public'
3802 AS $_$
3803 import re
3804 import subprocess
3805@@ -3306,12 +3221,12 @@
3806 $_$;
3807
3808
3809-COMMENT ON FUNCTION update_database_stats() IS '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.';
3810-
3811-
3812-CREATE FUNCTION update_replication_lag_cache() RETURNS boolean
3813+COMMENT ON FUNCTION public.update_database_stats() IS '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.';
3814+
3815+
3816+CREATE FUNCTION public.update_replication_lag_cache() RETURNS boolean
3817 LANGUAGE plpgsql SECURITY DEFINER
3818- SET search_path TO public
3819+ SET search_path TO 'public'
3820 AS $$
3821 BEGIN
3822 DELETE FROM DatabaseReplicationLag;
3823@@ -3329,64 +3244,10 @@
3824 $$;
3825
3826
3827-COMMENT ON FUNCTION update_replication_lag_cache() IS 'Updates the DatabaseReplicationLag materialized view.';
3828-
3829-
3830-CREATE FUNCTION update_transitively_private(start_branch integer, _root_branch integer DEFAULT NULL::integer, _root_transitively_private boolean DEFAULT NULL::boolean) RETURNS void
3831- LANGUAGE plpgsql SECURITY DEFINER
3832- SET search_path TO public
3833- AS $$
3834-DECLARE
3835- root_transitively_private boolean := _root_transitively_private;
3836- root_branch int := _root_branch;
3837-BEGIN
3838- IF root_transitively_private IS NULL THEN
3839- -- We can't just trust the transitively_private flag of the
3840- -- branch we are stacked on, as if we are updating multiple
3841- -- records they will be updated in an indeterminate order.
3842- -- We need a recursive query.
3843- UPDATE Branch SET transitively_private = (
3844- WITH RECURSIVE stacked_branches AS (
3845- SELECT
3846- top_branch.id, top_branch.stacked_on, top_branch.private
3847- FROM Branch AS top_branch
3848- WHERE top_branch.id = start_branch
3849- UNION ALL
3850- SELECT
3851- sub_branch.id, sub_branch.stacked_on, sub_branch.private
3852- FROM stacked_branches, Branch AS sub_branch
3853- WHERE
3854- stacked_branches.stacked_on = sub_branch.id
3855- AND stacked_branches.stacked_on != start_branch
3856- -- Shortcircuit. No need to recurse if already private.
3857- AND stacked_branches.private IS FALSE
3858- )
3859- SELECT COUNT(*) > 0
3860- FROM stacked_branches
3861- WHERE private IS TRUE)
3862- WHERE Branch.id = start_branch
3863- RETURNING transitively_private INTO root_transitively_private;
3864- root_branch := start_branch;
3865- ELSE
3866- -- Now we have calculated the correct transitively_private flag
3867- -- we can trust it.
3868- UPDATE Branch SET
3869- transitively_private = GREATEST(private, root_transitively_private)
3870- WHERE id = root_branch;
3871- END IF;
3872-
3873- -- Recurse to branches stacked on this one.
3874- PERFORM update_transitively_private(
3875- start_branch, id, GREATEST(private, root_transitively_private))
3876- FROM Branch WHERE stacked_on = root_branch AND id != start_branch;
3877-END;
3878-$$;
3879-
3880-
3881-COMMENT ON FUNCTION update_transitively_private(start_branch integer, _root_branch integer, _root_transitively_private boolean) IS 'A branch is transitively private if it is private or is stacked on any transitively private branches.';
3882-
3883-
3884-CREATE FUNCTION valid_absolute_url(text) RETURNS boolean
3885+COMMENT ON FUNCTION public.update_replication_lag_cache() IS 'Updates the DatabaseReplicationLag materialized view.';
3886+
3887+
3888+CREATE FUNCTION public.valid_absolute_url(text) RETURNS boolean
3889 LANGUAGE plpythonu IMMUTABLE STRICT
3890 AS $$
3891 from urlparse import urlparse, uses_netloc
3892@@ -3403,10 +3264,10 @@
3893 $$;
3894
3895
3896-COMMENT ON FUNCTION valid_absolute_url(text) IS 'Ensure the given test is a valid absolute URL, containing both protocol and network location';
3897-
3898-
3899-CREATE FUNCTION valid_branch_name(text) RETURNS boolean
3900+COMMENT ON FUNCTION public.valid_absolute_url(text) IS 'Ensure the given test is a valid absolute URL, containing both protocol and network location';
3901+
3902+
3903+CREATE FUNCTION public.valid_branch_name(text) RETURNS boolean
3904 LANGUAGE plpythonu IMMUTABLE STRICT
3905 AS $$
3906 import re
3907@@ -3418,27 +3279,27 @@
3908 $$;
3909
3910
3911-COMMENT ON FUNCTION valid_branch_name(text) IS 'validate a branch name.
3912+COMMENT ON FUNCTION public.valid_branch_name(text) IS 'validate a branch name.
3913
3914 As per valid_name, except we allow uppercase and @';
3915
3916
3917-CREATE FUNCTION valid_cve(text) RETURNS boolean
3918+CREATE FUNCTION public.valid_cve(text) RETURNS boolean
3919 LANGUAGE plpythonu IMMUTABLE STRICT
3920 AS $_$
3921 import re
3922 name = args[0]
3923- pat = r"^(19|20)\d{2}-\d{4}$"
3924+ pat = r"^(19|20)\d{2}-\d{4,}$"
3925 if re.match(pat, name):
3926 return 1
3927 return 0
3928 $_$;
3929
3930
3931-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.';
3932-
3933-
3934-CREATE FUNCTION valid_debian_version(text) RETURNS boolean
3935+COMMENT ON FUNCTION public.valid_cve(text) IS 'validate a common vulnerability number as defined on www.cve.mitre.org, minus the CAN- or CVE- prefix.';
3936+
3937+
3938+CREATE FUNCTION public.valid_debian_version(text) RETURNS boolean
3939 LANGUAGE plpythonu IMMUTABLE STRICT
3940 AS $_$
3941 import re
3942@@ -3462,10 +3323,10 @@
3943 $_$;
3944
3945
3946-COMMENT ON FUNCTION valid_debian_version(text) IS 'validate a version number as per Debian Policy';
3947-
3948-
3949-CREATE FUNCTION valid_fingerprint(text) RETURNS boolean
3950+COMMENT ON FUNCTION public.valid_debian_version(text) IS 'validate a version number as per Debian Policy';
3951+
3952+
3953+CREATE FUNCTION public.valid_fingerprint(text) RETURNS boolean
3954 LANGUAGE plpythonu IMMUTABLE STRICT
3955 AS $$
3956 import re
3957@@ -3476,10 +3337,27 @@
3958 $$;
3959
3960
3961-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.';
3962-
3963-
3964-CREATE FUNCTION valid_keyid(text) RETURNS boolean
3965+COMMENT ON FUNCTION public.valid_fingerprint(text) IS 'Returns true if passed a valid GPG fingerprint. Valid GPG fingerprints are a 40 character long hexadecimal number in uppercase.';
3966+
3967+
3968+CREATE FUNCTION public.valid_git_repository_name(text) RETURNS boolean
3969+ LANGUAGE plpythonu IMMUTABLE STRICT
3970+ AS $$
3971+ import re
3972+ name = args[0]
3973+ pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
3974+ if not name.endswith(".git") and re.match(pat, name):
3975+ return 1
3976+ return 0
3977+$$;
3978+
3979+
3980+COMMENT ON FUNCTION public.valid_git_repository_name(text) IS 'validate a Git repository name.
3981+
3982+ As per valid_branch_name, except we disallow names ending in ".git".';
3983+
3984+
3985+CREATE FUNCTION public.valid_keyid(text) RETURNS boolean
3986 LANGUAGE plpythonu IMMUTABLE STRICT
3987 AS $$
3988 import re
3989@@ -3490,10 +3368,10 @@
3990 $$;
3991
3992
3993-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.';
3994-
3995-
3996-CREATE FUNCTION valid_regexp(text) RETURNS boolean
3997+COMMENT ON FUNCTION public.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.';
3998+
3999+
4000+CREATE FUNCTION public.valid_regexp(text) RETURNS boolean
4001 LANGUAGE plpythonu IMMUTABLE STRICT
4002 AS $$
4003 import re
4004@@ -3506,10 +3384,10 @@
4005 $$;
4006
4007
4008-COMMENT ON FUNCTION valid_regexp(text) IS 'Returns true if the input can be compiled as a regular expression.';
4009-
4010-
4011-CREATE FUNCTION version_sort_key(version text) RETURNS text
4012+COMMENT ON FUNCTION public.valid_regexp(text) IS 'Returns true if the input can be compiled as a regular expression.';
4013+
4014+
4015+CREATE FUNCTION public.version_sort_key(version text) RETURNS text
4016 LANGUAGE plpythonu IMMUTABLE STRICT
4017 AS $$
4018 # If this method is altered, then any functional indexes using it
4019@@ -3529,10 +3407,10 @@
4020 $$;
4021
4022
4023-COMMENT ON FUNCTION version_sort_key(version text) IS '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].';
4024-
4025-
4026-CREATE FUNCTION you_are_your_own_member() RETURNS trigger
4027+COMMENT ON FUNCTION public.version_sort_key(version text) IS '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].';
4028+
4029+
4030+CREATE FUNCTION public.you_are_your_own_member() RETURNS trigger
4031 LANGUAGE plpgsql
4032 AS $$
4033 BEGIN
4034@@ -3543,873 +3421,116 @@
4035 $$;
4036
4037
4038-COMMENT ON FUNCTION you_are_your_own_member() IS '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';
4039-
4040-
4041-SET search_path = ts2, pg_catalog;
4042-
4043-CREATE FUNCTION _ftq(text) RETURNS text
4044- LANGUAGE plpythonu IMMUTABLE STRICT
4045- AS $_$
4046- import re
4047-
4048- # I think this method would be more robust if we used a real
4049- # tokenizer and parser to generate the query string, but we need
4050- # something suitable for use as a stored procedure which currently
4051- # means no external dependancies.
4052-
4053- # Convert to Unicode
4054- query = args[0].decode('utf8')
4055- ## plpy.debug('1 query is %s' % repr(query))
4056-
4057- # Normalize whitespace
4058- query = re.sub("(?u)\s+"," ", query)
4059-
4060- # Convert AND, OR, NOT and - to tsearch2 punctuation
4061- query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
4062- query = re.sub(r"(?u)\bAND\b", "&", query)
4063- query = re.sub(r"(?u)\bOR\b", "|", query)
4064- query = re.sub(r"(?u)\bNOT\b", " !", query)
4065- ## plpy.debug('2 query is %s' % repr(query))
4066-
4067- # Deal with unwanted punctuation. We convert strings of punctuation
4068- # inside words to a '-' character for the hypenation handling below
4069- # to deal with further. Outside of words we replace with whitespace.
4070- # We don't mess with -&|!()' as they are handled later.
4071- #punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
4072- punctuation = r"[^\w\s\-\&\|\!\(\)']"
4073- query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
4074- query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
4075- ## plpy.debug('3 query is %s' % repr(query))
4076-
4077- # Strip ! characters inside and at the end of a word
4078- query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
4079-
4080- # Now that we have handle case sensitive booleans, convert to lowercase
4081- query = query.lower()
4082-
4083- # Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
4084- # ((foo&bar&baz)|foobarbaz)
4085- def hyphen_repl(match):
4086- bits = match.group(0).split("-")
4087- return "((%s)|%s)" % ("&".join(bits), "".join(bits))
4088- query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
4089- ## plpy.debug('4 query is %s' % repr(query))
4090-
4091- # Any remaining - characters are spurious
4092- query = query.replace('-','')
4093-
4094- # Remove unpartnered bracket on the left and right
4095- query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
4096- query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
4097-
4098- # Remove spurious brackets
4099- query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
4100- ## plpy.debug('5 query is %s' % repr(query))
4101-
4102- # Insert & between tokens without an existing boolean operator
4103- # ( not proceeded by (|&!
4104- query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
4105- ## plpy.debug('6 query is %s' % repr(query))
4106- # ) not followed by )|&
4107- query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
4108- ## plpy.debug('6.1 query is %s' % repr(query))
4109- # Whitespace not proceded by (|&! not followed by &|
4110- query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
4111- ## plpy.debug('7 query is %s' % repr(query))
4112-
4113- # Detect and repair syntax errors - we are lenient because
4114- # this input is generally from users.
4115-
4116- # Fix unbalanced brackets
4117- openings = query.count("(")
4118- closings = query.count(")")
4119- if openings > closings:
4120- query = query + " ) "*(openings-closings)
4121- elif closings > openings:
4122- query = " ( "*(closings-openings) + query
4123- ## plpy.debug('8 query is %s' % repr(query))
4124-
4125- # Strip ' character that do not have letters on both sides
4126- query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
4127-
4128- # Brackets containing nothing but whitespace and booleans, recursive
4129- last = ""
4130- while last != query:
4131- last = query
4132- query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
4133- ## plpy.debug('9 query is %s' % repr(query))
4134-
4135- # An & or | following a (
4136- query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
4137- ## plpy.debug('10 query is %s' % repr(query))
4138-
4139- # An &, | or ! immediatly before a )
4140- query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
4141- ## plpy.debug('11 query is %s' % repr(query))
4142-
4143- # An &,| or ! followed by another boolean.
4144- query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
4145- ## plpy.debug('12 query is %s' % repr(query))
4146-
4147- # Leading & or |
4148- query = re.sub(r"(?u)^[\s\&\|]+", "", query)
4149- ## plpy.debug('13 query is %s' % repr(query))
4150-
4151- # Trailing &, | or !
4152- query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
4153- ## plpy.debug('14 query is %s' % repr(query))
4154-
4155- # If we have nothing but whitespace and tsearch2 operators,
4156- # return NULL.
4157- if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
4158- return None
4159-
4160- # Convert back to UTF-8
4161- query = query.encode('utf8')
4162- ## plpy.debug('15 query is %s' % repr(query))
4163-
4164- return query or None
4165- $_$;
4166-
4167-
4168-CREATE FUNCTION _get_parser_from_curcfg() RETURNS text
4169- LANGUAGE sql IMMUTABLE STRICT
4170- AS $$select prsname::text from pg_catalog.pg_ts_parser p join pg_ts_config c on cfgparser = p.oid where c.oid = show_curcfg();$$;
4171-
4172-
4173-CREATE FUNCTION concat(pg_catalog.tsvector, pg_catalog.tsvector) RETURNS pg_catalog.tsvector
4174- LANGUAGE internal IMMUTABLE STRICT
4175- AS $$tsvector_concat$$;
4176-
4177-
4178-CREATE FUNCTION dex_init(internal) RETURNS internal
4179- LANGUAGE c
4180- AS '$libdir/tsearch2', 'tsa_dex_init';
4181-
4182-
4183-CREATE FUNCTION dex_lexize(internal, internal, integer) RETURNS internal
4184- LANGUAGE c STRICT
4185- AS '$libdir/tsearch2', 'tsa_dex_lexize';
4186-
4187-
4188-CREATE FUNCTION ftiupdate() RETURNS trigger
4189- LANGUAGE plpythonu
4190- AS $_$
4191- new = TD["new"]
4192- args = TD["args"][:]
4193-
4194- # Short circuit if none of the relevant columns have been
4195- # modified and fti is not being set to NULL (setting the fti
4196- # column to NULL is thus how we can force a rebuild of the fti
4197- # column).
4198- if TD["event"] == "UPDATE" and new["fti"] != None:
4199- old = TD["old"]
4200- relevant_modification = False
4201- for column_name in args[::2]:
4202- if new[column_name] != old[column_name]:
4203- relevant_modification = True
4204- break
4205- if not relevant_modification:
4206- return "OK"
4207-
4208- # Generate an SQL statement that turns the requested
4209- # column values into a weighted tsvector
4210- sql = []
4211- for i in range(0, len(args), 2):
4212- sql.append(
4213- "ts2.setweight(ts2.to_tsvector('default', coalesce("
4214- "substring(ltrim($%d) from 1 for 2500),'')),"
4215- "CAST($%d AS \"char\"))" % (i + 1, i + 2))
4216- args[i] = new[args[i]]
4217-
4218- sql = "SELECT %s AS fti" % "||".join(sql)
4219-
4220- # Execute and store in the fti column
4221- plan = plpy.prepare(sql, ["text", "char"] * (len(args)/2))
4222- new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
4223-
4224- # Tell PostgreSQL we have modified the data
4225- return "MODIFY"
4226-$_$;
4227-
4228-
4229-COMMENT ON FUNCTION ftiupdate() IS 'Trigger function that keeps the fti tsvector column up to date.';
4230-
4231-
4232-CREATE FUNCTION ftq(text) RETURNS pg_catalog.tsquery
4233- LANGUAGE plpythonu IMMUTABLE STRICT
4234- AS $_$
4235- import re
4236-
4237- # I think this method would be more robust if we used a real
4238- # tokenizer and parser to generate the query string, but we need
4239- # something suitable for use as a stored procedure which currently
4240- # means no external dependancies.
4241-
4242- # Convert to Unicode
4243- query = args[0].decode('utf8')
4244- ## plpy.debug('1 query is %s' % repr(query))
4245-
4246- # Normalize whitespace
4247- query = re.sub("(?u)\s+"," ", query)
4248-
4249- # Convert AND, OR, NOT and - to tsearch2 punctuation
4250- query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
4251- query = re.sub(r"(?u)\bAND\b", "&", query)
4252- query = re.sub(r"(?u)\bOR\b", "|", query)
4253- query = re.sub(r"(?u)\bNOT\b", " !", query)
4254- ## plpy.debug('2 query is %s' % repr(query))
4255-
4256- # Deal with unwanted punctuation. We convert strings of punctuation
4257- # inside words to a '-' character for the hypenation handling below
4258- # to deal with further. Outside of words we replace with whitespace.
4259- # We don't mess with -&|!()' as they are handled later.
4260- #punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
4261- punctuation = r"[^\w\s\-\&\|\!\(\)']"
4262- query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
4263- query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
4264- ## plpy.debug('3 query is %s' % repr(query))
4265-
4266- # Strip ! characters inside and at the end of a word
4267- query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
4268-
4269- # Now that we have handle case sensitive booleans, convert to lowercase
4270- query = query.lower()
4271-
4272- # Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
4273- # ((foo&bar&baz)|foobarbaz)
4274- def hyphen_repl(match):
4275- bits = match.group(0).split("-")
4276- return "((%s)|%s)" % ("&".join(bits), "".join(bits))
4277- query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
4278- ## plpy.debug('4 query is %s' % repr(query))
4279-
4280- # Any remaining - characters are spurious
4281- query = query.replace('-','')
4282-
4283- # Remove unpartnered bracket on the left and right
4284- query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
4285- query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
4286-
4287- # Remove spurious brackets
4288- query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
4289- ## plpy.debug('5 query is %s' % repr(query))
4290-
4291- # Insert & between tokens without an existing boolean operator
4292- # ( not proceeded by (|&!
4293- query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
4294- ## plpy.debug('6 query is %s' % repr(query))
4295- # ) not followed by )|&
4296- query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
4297- ## plpy.debug('6.1 query is %s' % repr(query))
4298- # Whitespace not proceded by (|&! not followed by &|
4299- query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
4300- ## plpy.debug('7 query is %s' % repr(query))
4301-
4302- # Detect and repair syntax errors - we are lenient because
4303- # this input is generally from users.
4304-
4305- # Fix unbalanced brackets
4306- openings = query.count("(")
4307- closings = query.count(")")
4308- if openings > closings:
4309- query = query + " ) "*(openings-closings)
4310- elif closings > openings:
4311- query = " ( "*(closings-openings) + query
4312- ## plpy.debug('8 query is %s' % repr(query))
4313-
4314- # Strip ' character that do not have letters on both sides
4315- query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
4316-
4317- # Brackets containing nothing but whitespace and booleans, recursive
4318- last = ""
4319- while last != query:
4320- last = query
4321- query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
4322- ## plpy.debug('9 query is %s' % repr(query))
4323-
4324- # An & or | following a (
4325- query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
4326- ## plpy.debug('10 query is %s' % repr(query))
4327-
4328- # An &, | or ! immediatly before a )
4329- query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
4330- ## plpy.debug('11 query is %s' % repr(query))
4331-
4332- # An &,| or ! followed by another boolean.
4333- query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
4334- ## plpy.debug('12 query is %s' % repr(query))
4335-
4336- # Leading & or |
4337- query = re.sub(r"(?u)^[\s\&\|]+", "", query)
4338- ## plpy.debug('13 query is %s' % repr(query))
4339-
4340- # Trailing &, | or !
4341- query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
4342- ## plpy.debug('14 query is %s' % repr(query))
4343-
4344- # If we have nothing but whitespace and tsearch2 operators,
4345- # return NULL.
4346- if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
4347- return None
4348-
4349- # Convert back to UTF-8
4350- query = query.encode('utf8')
4351- ## plpy.debug('15 query is %s' % repr(query))
4352-
4353- p = plpy.prepare("SELECT to_tsquery('default', $1) AS x", ["text"])
4354- query = plpy.execute(p, [query], 1)[0]["x"]
4355- return query or None
4356- $_$;
4357-
4358-
4359-COMMENT ON FUNCTION ftq(text) IS 'Convert a string to an unparsed tsearch2 query';
4360-
4361-
4362-CREATE FUNCTION get_covers(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS text
4363- LANGUAGE c STRICT
4364- AS '$libdir/tsearch2', 'tsa_get_covers';
4365-
4366-
4367-CREATE FUNCTION headline(oid, text, pg_catalog.tsquery, text) RETURNS text
4368- LANGUAGE internal IMMUTABLE STRICT
4369- AS $$ts_headline_byid_opt$$;
4370-
4371-
4372-CREATE FUNCTION headline(oid, text, pg_catalog.tsquery) RETURNS text
4373- LANGUAGE internal IMMUTABLE STRICT
4374- AS $$ts_headline_byid$$;
4375-
4376-
4377-CREATE FUNCTION headline(text, text, pg_catalog.tsquery, text) RETURNS text
4378- LANGUAGE c IMMUTABLE STRICT
4379- AS '$libdir/tsearch2', 'tsa_headline_byname';
4380-
4381-
4382-CREATE FUNCTION headline(text, text, pg_catalog.tsquery) RETURNS text
4383- LANGUAGE c IMMUTABLE STRICT
4384- AS '$libdir/tsearch2', 'tsa_headline_byname';
4385-
4386-
4387-CREATE FUNCTION headline(text, pg_catalog.tsquery, text) RETURNS text
4388- LANGUAGE internal IMMUTABLE STRICT
4389- AS $$ts_headline_opt$$;
4390-
4391-
4392-CREATE FUNCTION headline(text, pg_catalog.tsquery) RETURNS text
4393- LANGUAGE internal IMMUTABLE STRICT
4394- AS $$ts_headline$$;
4395-
4396-
4397-CREATE FUNCTION length(pg_catalog.tsvector) RETURNS integer
4398- LANGUAGE internal IMMUTABLE STRICT
4399- AS $$tsvector_length$$;
4400-
4401-
4402-CREATE FUNCTION lexize(oid, text) RETURNS text[]
4403- LANGUAGE internal STRICT
4404- AS $$ts_lexize$$;
4405-
4406-
4407-CREATE FUNCTION lexize(text, text) RETURNS text[]
4408- LANGUAGE c STRICT
4409- AS '$libdir/tsearch2', 'tsa_lexize_byname';
4410-
4411-
4412-CREATE FUNCTION lexize(text) RETURNS text[]
4413- LANGUAGE c STRICT
4414- AS '$libdir/tsearch2', 'tsa_lexize_bycurrent';
4415-
4416-
4417-CREATE FUNCTION numnode(pg_catalog.tsquery) RETURNS integer
4418- LANGUAGE internal IMMUTABLE STRICT
4419- AS $$tsquery_numnode$$;
4420-
4421-
4422-CREATE FUNCTION parse(oid, text) RETURNS SETOF tokenout
4423- LANGUAGE internal STRICT
4424- AS $$ts_parse_byid$$;
4425-
4426-
4427-CREATE FUNCTION parse(text, text) RETURNS SETOF tokenout
4428- LANGUAGE internal STRICT
4429- AS $$ts_parse_byname$$;
4430-
4431-
4432-CREATE FUNCTION parse(text) RETURNS SETOF tokenout
4433- LANGUAGE c STRICT
4434- AS '$libdir/tsearch2', 'tsa_parse_current';
4435-
4436-
4437-CREATE FUNCTION plainto_tsquery(oid, text) RETURNS pg_catalog.tsquery
4438- LANGUAGE internal IMMUTABLE STRICT
4439- AS $$plainto_tsquery_byid$$;
4440-
4441-
4442-CREATE FUNCTION plainto_tsquery(text, text) RETURNS pg_catalog.tsquery
4443- LANGUAGE c IMMUTABLE STRICT
4444- AS '$libdir/tsearch2', 'tsa_plainto_tsquery_name';
4445-
4446-
4447-CREATE FUNCTION plainto_tsquery(text) RETURNS pg_catalog.tsquery
4448- LANGUAGE internal IMMUTABLE STRICT
4449- AS $$plainto_tsquery$$;
4450-
4451-
4452-CREATE FUNCTION prsd_end(internal) RETURNS void
4453- LANGUAGE c
4454- AS '$libdir/tsearch2', 'tsa_prsd_end';
4455-
4456-
4457-CREATE FUNCTION prsd_getlexeme(internal, internal, internal) RETURNS integer
4458- LANGUAGE c
4459- AS '$libdir/tsearch2', 'tsa_prsd_getlexeme';
4460-
4461-
4462-CREATE FUNCTION prsd_headline(internal, internal, internal) RETURNS internal
4463- LANGUAGE c
4464- AS '$libdir/tsearch2', 'tsa_prsd_headline';
4465-
4466-
4467-CREATE FUNCTION prsd_lextype(internal) RETURNS internal
4468- LANGUAGE c
4469- AS '$libdir/tsearch2', 'tsa_prsd_lextype';
4470-
4471-
4472-CREATE FUNCTION prsd_start(internal, integer) RETURNS internal
4473- LANGUAGE c
4474- AS '$libdir/tsearch2', 'tsa_prsd_start';
4475-
4476-
4477-CREATE FUNCTION querytree(pg_catalog.tsquery) RETURNS text
4478- LANGUAGE internal STRICT
4479- AS $$tsquerytree$$;
4480-
4481-
4482-CREATE FUNCTION rank(real[], pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4483- LANGUAGE internal IMMUTABLE STRICT
4484- AS $$ts_rank_wtt$$;
4485-
4486-
4487-CREATE FUNCTION rank(real[], pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4488- LANGUAGE internal IMMUTABLE STRICT
4489- AS $$ts_rank_wttf$$;
4490-
4491-
4492-CREATE FUNCTION rank(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4493- LANGUAGE internal IMMUTABLE STRICT
4494- AS $$ts_rank_tt$$;
4495-
4496-
4497-CREATE FUNCTION rank(pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4498- LANGUAGE internal IMMUTABLE STRICT
4499- AS $$ts_rank_ttf$$;
4500-
4501-
4502-CREATE FUNCTION rank_cd(real[], pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4503- LANGUAGE internal IMMUTABLE STRICT
4504- AS $$ts_rankcd_wtt$$;
4505-
4506-
4507-CREATE FUNCTION rank_cd(real[], pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4508- LANGUAGE internal IMMUTABLE STRICT
4509- AS $$ts_rankcd_wttf$$;
4510-
4511-
4512-CREATE FUNCTION rank_cd(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4513- LANGUAGE internal IMMUTABLE STRICT
4514- AS $$ts_rankcd_tt$$;
4515-
4516-
4517-CREATE FUNCTION rank_cd(pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4518- LANGUAGE internal IMMUTABLE STRICT
4519- AS $$ts_rankcd_ttf$$;
4520-
4521-
4522-CREATE FUNCTION reset_tsearch() RETURNS void
4523- LANGUAGE c STRICT
4524- AS '$libdir/tsearch2', 'tsa_reset_tsearch';
4525-
4526-
4527-CREATE FUNCTION rewrite(pg_catalog.tsquery, text) RETURNS pg_catalog.tsquery
4528- LANGUAGE internal IMMUTABLE STRICT
4529- AS $$tsquery_rewrite_query$$;
4530-
4531-
4532-CREATE FUNCTION rewrite(pg_catalog.tsquery, pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4533- LANGUAGE internal IMMUTABLE STRICT
4534- AS $$tsquery_rewrite$$;
4535-
4536-
4537-CREATE FUNCTION rewrite_accum(pg_catalog.tsquery, pg_catalog.tsquery[]) RETURNS pg_catalog.tsquery
4538- LANGUAGE c
4539- AS '$libdir/tsearch2', 'tsa_rewrite_accum';
4540-
4541-
4542-CREATE FUNCTION rewrite_finish(pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4543- LANGUAGE c
4544- AS '$libdir/tsearch2', 'tsa_rewrite_finish';
4545-
4546-
4547-CREATE FUNCTION set_curcfg(integer) RETURNS void
4548- LANGUAGE c STRICT
4549- AS '$libdir/tsearch2', 'tsa_set_curcfg';
4550-
4551-
4552-CREATE FUNCTION set_curcfg(text) RETURNS void
4553- LANGUAGE c STRICT
4554- AS '$libdir/tsearch2', 'tsa_set_curcfg_byname';
4555-
4556-
4557-CREATE FUNCTION set_curdict(integer) RETURNS void
4558- LANGUAGE c STRICT
4559- AS '$libdir/tsearch2', 'tsa_set_curdict';
4560-
4561-
4562-CREATE FUNCTION set_curdict(text) RETURNS void
4563- LANGUAGE c STRICT
4564- AS '$libdir/tsearch2', 'tsa_set_curdict_byname';
4565-
4566-
4567-CREATE FUNCTION set_curprs(integer) RETURNS void
4568- LANGUAGE c STRICT
4569- AS '$libdir/tsearch2', 'tsa_set_curprs';
4570-
4571-
4572-CREATE FUNCTION set_curprs(text) RETURNS void
4573- LANGUAGE c STRICT
4574- AS '$libdir/tsearch2', 'tsa_set_curprs_byname';
4575-
4576-
4577-CREATE FUNCTION setweight(pg_catalog.tsvector, "char") RETURNS pg_catalog.tsvector
4578- LANGUAGE internal IMMUTABLE STRICT
4579- AS $$tsvector_setweight$$;
4580-
4581-
4582-CREATE FUNCTION show_curcfg() RETURNS oid
4583- LANGUAGE internal STABLE STRICT
4584- AS $$get_current_ts_config$$;
4585-
4586-
4587-CREATE FUNCTION snb_en_init(internal) RETURNS internal
4588- LANGUAGE c
4589- AS '$libdir/tsearch2', 'tsa_snb_en_init';
4590-
4591-
4592-CREATE FUNCTION snb_lexize(internal, internal, integer) RETURNS internal
4593- LANGUAGE c STRICT
4594- AS '$libdir/tsearch2', 'tsa_snb_lexize';
4595-
4596-
4597-CREATE FUNCTION snb_ru_init(internal) RETURNS internal
4598- LANGUAGE c
4599- AS '$libdir/tsearch2', 'tsa_snb_ru_init';
4600-
4601-
4602-CREATE FUNCTION snb_ru_init_koi8(internal) RETURNS internal
4603- LANGUAGE c
4604- AS '$libdir/tsearch2', 'tsa_snb_ru_init_koi8';
4605-
4606-
4607-CREATE FUNCTION snb_ru_init_utf8(internal) RETURNS internal
4608- LANGUAGE c
4609- AS '$libdir/tsearch2', 'tsa_snb_ru_init_utf8';
4610-
4611-
4612-CREATE FUNCTION spell_init(internal) RETURNS internal
4613- LANGUAGE c
4614- AS '$libdir/tsearch2', 'tsa_spell_init';
4615-
4616-
4617-CREATE FUNCTION spell_lexize(internal, internal, integer) RETURNS internal
4618- LANGUAGE c STRICT
4619- AS '$libdir/tsearch2', 'tsa_spell_lexize';
4620-
4621-
4622-CREATE FUNCTION stat(text) RETURNS SETOF statinfo
4623- LANGUAGE internal STRICT
4624- AS $$ts_stat1$$;
4625-
4626-
4627-CREATE FUNCTION stat(text, text) RETURNS SETOF statinfo
4628- LANGUAGE internal STRICT
4629- AS $$ts_stat2$$;
4630-
4631-
4632-CREATE FUNCTION strip(pg_catalog.tsvector) RETURNS pg_catalog.tsvector
4633- LANGUAGE internal IMMUTABLE STRICT
4634- AS $$tsvector_strip$$;
4635-
4636-
4637-CREATE FUNCTION syn_init(internal) RETURNS internal
4638- LANGUAGE c
4639- AS '$libdir/tsearch2', 'tsa_syn_init';
4640-
4641-
4642-CREATE FUNCTION syn_lexize(internal, internal, integer) RETURNS internal
4643- LANGUAGE c STRICT
4644- AS '$libdir/tsearch2', 'tsa_syn_lexize';
4645-
4646-
4647-CREATE FUNCTION thesaurus_init(internal) RETURNS internal
4648- LANGUAGE c
4649- AS '$libdir/tsearch2', 'tsa_thesaurus_init';
4650-
4651-
4652-CREATE FUNCTION thesaurus_lexize(internal, internal, integer, internal) RETURNS internal
4653- LANGUAGE c STRICT
4654- AS '$libdir/tsearch2', 'tsa_thesaurus_lexize';
4655-
4656-
4657-CREATE FUNCTION to_tsquery(oid, text) RETURNS pg_catalog.tsquery
4658- LANGUAGE internal IMMUTABLE STRICT
4659- AS $$to_tsquery_byid$$;
4660-
4661-
4662-CREATE FUNCTION to_tsquery(text, text) RETURNS pg_catalog.tsquery
4663- LANGUAGE c IMMUTABLE STRICT
4664- AS '$libdir/tsearch2', 'tsa_to_tsquery_name';
4665-
4666-
4667-CREATE FUNCTION to_tsquery(text) RETURNS pg_catalog.tsquery
4668- LANGUAGE internal IMMUTABLE STRICT
4669- AS $$to_tsquery$$;
4670-
4671-
4672-CREATE FUNCTION to_tsvector(oid, text) RETURNS pg_catalog.tsvector
4673- LANGUAGE internal IMMUTABLE STRICT
4674- AS $$to_tsvector_byid$$;
4675-
4676-
4677-CREATE FUNCTION to_tsvector(text, text) RETURNS pg_catalog.tsvector
4678- LANGUAGE c IMMUTABLE STRICT
4679- AS '$libdir/tsearch2', 'tsa_to_tsvector_name';
4680-
4681-
4682-CREATE FUNCTION to_tsvector(text) RETURNS pg_catalog.tsvector
4683- LANGUAGE internal IMMUTABLE STRICT
4684- AS $$to_tsvector$$;
4685-
4686-
4687-CREATE FUNCTION token_type(integer) RETURNS SETOF tokentype
4688- LANGUAGE internal STRICT ROWS 16
4689- AS $$ts_token_type_byid$$;
4690-
4691-
4692-CREATE FUNCTION token_type(text) RETURNS SETOF tokentype
4693- LANGUAGE internal STRICT ROWS 16
4694- AS $$ts_token_type_byname$$;
4695-
4696-
4697-CREATE FUNCTION token_type() RETURNS SETOF tokentype
4698- LANGUAGE c STRICT ROWS 16
4699- AS '$libdir/tsearch2', 'tsa_token_type_current';
4700-
4701-
4702-CREATE FUNCTION ts_debug(text) RETURNS SETOF tsdebug
4703- LANGUAGE sql STRICT
4704- AS $_$
4705-select
4706- (select c.cfgname::text from pg_catalog.pg_ts_config as c
4707- where c.oid = show_curcfg()),
4708- t.alias as tok_type,
4709- t.descr as description,
4710- p.token,
4711- ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary::pg_catalog.text
4712- FROM pg_catalog.pg_ts_config_map AS m
4713- WHERE m.mapcfg = show_curcfg() AND m.maptokentype = p.tokid
4714- ORDER BY m.mapseqno )
4715- AS dict_name,
4716- strip(to_tsvector(p.token)) as tsvector
4717-from
4718- parse( _get_parser_from_curcfg(), $1 ) as p,
4719- token_type() as t
4720-where
4721- t.tokid = p.tokid
4722-$_$;
4723-
4724-
4725-CREATE FUNCTION tsearch2() RETURNS trigger
4726- LANGUAGE c
4727- AS '$libdir/tsearch2', 'tsa_tsearch2';
4728-
4729-
4730-CREATE FUNCTION tsq_mcontained(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS boolean
4731- LANGUAGE internal IMMUTABLE STRICT
4732- AS $$tsq_mcontained$$;
4733-
4734-
4735-CREATE FUNCTION tsq_mcontains(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS boolean
4736- LANGUAGE internal IMMUTABLE STRICT
4737- AS $$tsq_mcontains$$;
4738-
4739-
4740-CREATE FUNCTION tsquery_and(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4741- LANGUAGE internal IMMUTABLE STRICT
4742- AS $$tsquery_and$$;
4743-
4744-
4745-CREATE FUNCTION tsquery_not(pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4746- LANGUAGE internal IMMUTABLE STRICT
4747- AS $$tsquery_not$$;
4748-
4749-
4750-CREATE FUNCTION tsquery_or(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4751- LANGUAGE internal IMMUTABLE STRICT
4752- AS $$tsquery_or$$;
4753-
4754-
4755-SET search_path = public, pg_catalog;
4756-
4757-CREATE OPERATOR > (
4758- PROCEDURE = debversion_gt,
4759- LEFTARG = debversion,
4760- RIGHTARG = debversion,
4761- COMMUTATOR = <,
4762- NEGATOR = >=
4763-);
4764-
4765-
4766-COMMENT ON OPERATOR > (debversion, debversion) IS 'debversion greater-than';
4767-
4768-
4769-CREATE AGGREGATE max(debversion) (
4770- SFUNC = debversion_larger,
4771- STYPE = debversion,
4772- SORTOP = >
4773-);
4774-
4775-
4776-CREATE OPERATOR < (
4777- PROCEDURE = debversion_lt,
4778- LEFTARG = debversion,
4779- RIGHTARG = debversion,
4780- COMMUTATOR = >,
4781- NEGATOR = >=
4782-);
4783-
4784-
4785-COMMENT ON OPERATOR < (debversion, debversion) IS 'debversion less-than';
4786-
4787-
4788-CREATE AGGREGATE min(debversion) (
4789- SFUNC = debversion_smaller,
4790- STYPE = debversion,
4791- SORTOP = <
4792-);
4793-
4794-
4795-SET search_path = ts2, pg_catalog;
4796-
4797-CREATE AGGREGATE rewrite(pg_catalog.tsquery[]) (
4798- SFUNC = rewrite_accum,
4799- STYPE = pg_catalog.tsquery,
4800- FINALFUNC = rewrite_finish
4801-);
4802-
4803-
4804-SET search_path = public, pg_catalog;
4805-
4806-CREATE OPERATOR <= (
4807- PROCEDURE = debversion_le,
4808- LEFTARG = debversion,
4809- RIGHTARG = debversion,
4810- COMMUTATOR = >=,
4811- NEGATOR = >
4812-);
4813-
4814-
4815-COMMENT ON OPERATOR <= (debversion, debversion) IS 'debversion less-than-or-equal';
4816-
4817-
4818-CREATE OPERATOR <> (
4819- PROCEDURE = debversion_ne,
4820- LEFTARG = debversion,
4821- RIGHTARG = debversion,
4822- COMMUTATOR = <>,
4823- NEGATOR = =
4824-);
4825-
4826-
4827-COMMENT ON OPERATOR <> (debversion, debversion) IS 'debversion not equal';
4828-
4829-
4830-CREATE OPERATOR = (
4831- PROCEDURE = debversion_eq,
4832- LEFTARG = debversion,
4833- RIGHTARG = debversion,
4834- COMMUTATOR = =,
4835- NEGATOR = <>
4836-);
4837-
4838-
4839-COMMENT ON OPERATOR = (debversion, debversion) IS 'debversion equal';
4840-
4841-
4842-CREATE OPERATOR >= (
4843- PROCEDURE = debversion_ge,
4844- LEFTARG = debversion,
4845- RIGHTARG = debversion,
4846- COMMUTATOR = <=,
4847- NEGATOR = <
4848-);
4849-
4850-
4851-COMMENT ON OPERATOR >= (debversion, debversion) IS 'debversion greater-than-or-equal';
4852-
4853-
4854-CREATE OPERATOR FAMILY debversion_ops USING btree;
4855-
4856-
4857-CREATE OPERATOR CLASS debversion_ops
4858- DEFAULT FOR TYPE debversion USING btree AS
4859- OPERATOR 1 <(debversion,debversion) ,
4860- OPERATOR 2 <=(debversion,debversion) ,
4861- OPERATOR 3 =(debversion,debversion) ,
4862- OPERATOR 4 >=(debversion,debversion) ,
4863- OPERATOR 5 >(debversion,debversion) ,
4864- FUNCTION 1 debversion_cmp(debversion,debversion);
4865-
4866-
4867-CREATE OPERATOR FAMILY debversion_ops USING hash;
4868-
4869-
4870-CREATE OPERATOR CLASS debversion_ops
4871- DEFAULT FOR TYPE debversion USING hash AS
4872- OPERATOR 1 =(debversion,debversion) ,
4873- FUNCTION 1 debversion_hash(debversion);
4874-
4875-
4876-SET search_path = ts2, pg_catalog;
4877-
4878-CREATE OPERATOR FAMILY tsquery_ops USING btree;
4879-
4880-
4881-CREATE OPERATOR CLASS tsquery_ops
4882- FOR TYPE pg_catalog.tsquery USING btree AS
4883- OPERATOR 1 <(pg_catalog.tsquery,pg_catalog.tsquery) ,
4884- OPERATOR 2 <=(pg_catalog.tsquery,pg_catalog.tsquery) ,
4885- OPERATOR 3 =(pg_catalog.tsquery,pg_catalog.tsquery) ,
4886- OPERATOR 4 >=(pg_catalog.tsquery,pg_catalog.tsquery) ,
4887- OPERATOR 5 >(pg_catalog.tsquery,pg_catalog.tsquery) ,
4888- FUNCTION 1 tsquery_cmp(pg_catalog.tsquery,pg_catalog.tsquery);
4889-
4890-
4891-CREATE OPERATOR FAMILY tsvector_ops USING btree;
4892-
4893-
4894-CREATE OPERATOR CLASS tsvector_ops
4895- FOR TYPE pg_catalog.tsvector USING btree AS
4896- OPERATOR 1 <(pg_catalog.tsvector,pg_catalog.tsvector) ,
4897- OPERATOR 2 <=(pg_catalog.tsvector,pg_catalog.tsvector) ,
4898- OPERATOR 3 =(pg_catalog.tsvector,pg_catalog.tsvector) ,
4899- OPERATOR 4 >=(pg_catalog.tsvector,pg_catalog.tsvector) ,
4900- OPERATOR 5 >(pg_catalog.tsvector,pg_catalog.tsvector) ,
4901- FUNCTION 1 tsvector_cmp(pg_catalog.tsvector,pg_catalog.tsvector);
4902-
4903-
4904-SET search_path = pg_catalog;
4905+COMMENT ON FUNCTION public.you_are_your_own_member() IS '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';
4906+
4907+
4908+CREATE OPERATOR public.> (
4909+ PROCEDURE = public.debversion_gt,
4910+ LEFTARG = public.debversion,
4911+ RIGHTARG = public.debversion,
4912+ COMMUTATOR = OPERATOR(public.<),
4913+ NEGATOR = OPERATOR(public.>=)
4914+);
4915+
4916+
4917+COMMENT ON OPERATOR public.> (public.debversion, public.debversion) IS 'debversion greater-than';
4918+
4919+
4920+CREATE AGGREGATE public.max(public.debversion) (
4921+ SFUNC = public.debversion_larger,
4922+ STYPE = public.debversion,
4923+ SORTOP = OPERATOR(public.>)
4924+);
4925+
4926+
4927+CREATE OPERATOR public.< (
4928+ PROCEDURE = public.debversion_lt,
4929+ LEFTARG = public.debversion,
4930+ RIGHTARG = public.debversion,
4931+ COMMUTATOR = OPERATOR(public.>),
4932+ NEGATOR = OPERATOR(public.>=)
4933+);
4934+
4935+
4936+COMMENT ON OPERATOR public.< (public.debversion, public.debversion) IS 'debversion less-than';
4937+
4938+
4939+CREATE AGGREGATE public.min(public.debversion) (
4940+ SFUNC = public.debversion_smaller,
4941+ STYPE = public.debversion,
4942+ SORTOP = OPERATOR(public.<)
4943+);
4944+
4945+
4946+CREATE OPERATOR public.<= (
4947+ PROCEDURE = public.debversion_le,
4948+ LEFTARG = public.debversion,
4949+ RIGHTARG = public.debversion,
4950+ COMMUTATOR = OPERATOR(public.>=),
4951+ NEGATOR = OPERATOR(public.>)
4952+);
4953+
4954+
4955+COMMENT ON OPERATOR public.<= (public.debversion, public.debversion) IS 'debversion less-than-or-equal';
4956+
4957+
4958+CREATE OPERATOR public.<> (
4959+ PROCEDURE = public.debversion_ne,
4960+ LEFTARG = public.debversion,
4961+ RIGHTARG = public.debversion,
4962+ COMMUTATOR = OPERATOR(public.<>),
4963+ NEGATOR = OPERATOR(public.=)
4964+);
4965+
4966+
4967+COMMENT ON OPERATOR public.<> (public.debversion, public.debversion) IS 'debversion not equal';
4968+
4969+
4970+CREATE OPERATOR public.= (
4971+ PROCEDURE = public.debversion_eq,
4972+ LEFTARG = public.debversion,
4973+ RIGHTARG = public.debversion,
4974+ COMMUTATOR = OPERATOR(public.=),
4975+ NEGATOR = OPERATOR(public.<>)
4976+);
4977+
4978+
4979+COMMENT ON OPERATOR public.= (public.debversion, public.debversion) IS 'debversion equal';
4980+
4981+
4982+CREATE OPERATOR public.>= (
4983+ PROCEDURE = public.debversion_ge,
4984+ LEFTARG = public.debversion,
4985+ RIGHTARG = public.debversion,
4986+ COMMUTATOR = OPERATOR(public.<=),
4987+ NEGATOR = OPERATOR(public.<)
4988+);
4989+
4990+
4991+COMMENT ON OPERATOR public.>= (public.debversion, public.debversion) IS 'debversion greater-than-or-equal';
4992+
4993+
4994+CREATE OPERATOR FAMILY public.debversion_ops USING btree;
4995+
4996+
4997+CREATE OPERATOR CLASS public.debversion_ops
4998+ DEFAULT FOR TYPE public.debversion USING btree FAMILY public.debversion_ops AS
4999+ OPERATOR 1 public.<(public.debversion,public.debversion) ,
5000+ OPERATOR 2 public.<=(public.debversion,public.debversion) ,
The diff has been truncated for viewing.