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
=== modified file 'database/schema/Makefile'
--- database/schema/Makefile 2018-05-14 13:11:14 +0000
+++ database/schema/Makefile 2019-02-26 07:46:16 +0000
@@ -61,9 +61,9 @@
61# on production. It is generated using newbaseline.py in61# on production. It is generated using newbaseline.py in
62# bzr+ssh://devpad.canonical.com/code/stub/dbascripts62# bzr+ssh://devpad.canonical.com/code/stub/dbascripts
63#63#
64REV=220964REV=2210
65BASELINE=launchpad-${REV}-00-0.sql65BASELINE=launchpad-${REV}-00-0.sql
66MD5SUM=cc7a493c924196409a22392a16443d52 launchpad-2209-00-0.sql66MD5SUM=36ae7078cd41916bbbd9c116b2e6aea7 launchpad-2210-00-0.sql
6767
68default: all68default: all
6969
7070
=== renamed file 'database/schema/patch-2209-00-0.sql' => 'database/schema/archive/patch-2209-00-0.sql'
=== renamed file 'database/schema/patch-2209-00-1.sql' => 'database/schema/archive/patch-2209-00-1.sql'
=== renamed file 'database/schema/patch-2209-00-2.sql' => 'database/schema/archive/patch-2209-00-2.sql'
=== renamed file 'database/schema/patch-2209-00-3.sql' => 'database/schema/archive/patch-2209-00-3.sql'
=== renamed file 'database/schema/patch-2209-00-5.sql' => 'database/schema/archive/patch-2209-00-5.sql'
=== renamed file 'database/schema/patch-2209-00-6.sql' => 'database/schema/archive/patch-2209-00-6.sql'
=== renamed file 'database/schema/patch-2209-00-7.sql' => 'database/schema/archive/patch-2209-00-7.sql'
=== renamed file 'database/schema/patch-2209-00-8.sql' => 'database/schema/archive/patch-2209-00-8.sql'
=== renamed file 'database/schema/patch-2209-00-9.sql' => 'database/schema/archive/patch-2209-00-9.sql'
=== renamed file 'database/schema/patch-2209-01-0.sql' => 'database/schema/archive/patch-2209-01-0.sql'
=== renamed file 'database/schema/patch-2209-01-1.sql' => 'database/schema/archive/patch-2209-01-1.sql'
=== renamed file 'database/schema/patch-2209-02-0.sql' => 'database/schema/archive/patch-2209-02-0.sql'
=== renamed file 'database/schema/patch-2209-04-0.sql' => 'database/schema/archive/patch-2209-04-0.sql'
=== renamed file 'database/schema/patch-2209-05-1.sql' => 'database/schema/archive/patch-2209-05-1.sql'
=== renamed file 'database/schema/patch-2209-06-1.sql' => 'database/schema/archive/patch-2209-06-1.sql'
=== renamed file 'database/schema/patch-2209-07-0.sql' => 'database/schema/archive/patch-2209-07-0.sql'
=== renamed file 'database/schema/patch-2209-07-1.sql' => 'database/schema/archive/patch-2209-07-1.sql'
=== renamed file 'database/schema/patch-2209-08-1.sql' => 'database/schema/archive/patch-2209-08-1.sql'
=== renamed file 'database/schema/patch-2209-09-0.sql' => 'database/schema/archive/patch-2209-09-0.sql'
=== renamed file 'database/schema/patch-2209-10-0.sql' => 'database/schema/archive/patch-2209-10-0.sql'
=== renamed file 'database/schema/patch-2209-11-0.sql' => 'database/schema/archive/patch-2209-11-0.sql'
=== renamed file 'database/schema/patch-2209-11-1.sql' => 'database/schema/archive/patch-2209-11-1.sql'
=== renamed file 'database/schema/patch-2209-11-2.sql' => 'database/schema/archive/patch-2209-11-2.sql'
=== renamed file 'database/schema/patch-2209-11-3.sql' => 'database/schema/archive/patch-2209-11-3.sql'
=== renamed file 'database/schema/patch-2209-11-4.sql' => 'database/schema/archive/patch-2209-11-4.sql'
=== renamed file 'database/schema/patch-2209-11-5.sql' => 'database/schema/archive/patch-2209-11-5.sql'
=== renamed file 'database/schema/patch-2209-12-0.sql' => 'database/schema/archive/patch-2209-12-0.sql'
=== renamed file 'database/schema/patch-2209-12-1.sql' => 'database/schema/archive/patch-2209-12-1.sql'
=== renamed file 'database/schema/patch-2209-12-2.sql' => 'database/schema/archive/patch-2209-12-2.sql'
=== renamed file 'database/schema/patch-2209-12-3.sql' => 'database/schema/archive/patch-2209-12-3.sql'
=== renamed file 'database/schema/patch-2209-12-4.sql' => 'database/schema/archive/patch-2209-12-4.sql'
=== renamed file 'database/schema/patch-2209-12-5.sql' => 'database/schema/archive/patch-2209-12-5.sql'
=== renamed file 'database/schema/patch-2209-14-0.sql' => 'database/schema/archive/patch-2209-14-0.sql'
=== renamed file 'database/schema/patch-2209-15-0.sql' => 'database/schema/archive/patch-2209-15-0.sql'
=== renamed file 'database/schema/patch-2209-15-1.sql' => 'database/schema/archive/patch-2209-15-1.sql'
=== renamed file 'database/schema/patch-2209-15-2.sql' => 'database/schema/archive/patch-2209-15-2.sql'
=== renamed file 'database/schema/patch-2209-15-3.sql' => 'database/schema/archive/patch-2209-15-3.sql'
=== renamed file 'database/schema/patch-2209-16-0.sql' => 'database/schema/archive/patch-2209-16-0.sql'
=== renamed file 'database/schema/patch-2209-16-1.sql' => 'database/schema/archive/patch-2209-16-1.sql'
=== renamed file 'database/schema/patch-2209-16-2.sql' => 'database/schema/archive/patch-2209-16-2.sql'
=== renamed file 'database/schema/patch-2209-16-3.sql' => 'database/schema/archive/patch-2209-16-3.sql'
=== renamed file 'database/schema/patch-2209-16-4.sql' => 'database/schema/archive/patch-2209-16-4.sql'
=== renamed file 'database/schema/patch-2209-16-5.sql' => 'database/schema/archive/patch-2209-16-5.sql'
=== renamed file 'database/schema/patch-2209-16-6.sql' => 'database/schema/archive/patch-2209-16-6.sql'
=== renamed file 'database/schema/patch-2209-16-7.sql' => 'database/schema/archive/patch-2209-16-7.sql'
=== renamed file 'database/schema/patch-2209-16-8.sql' => 'database/schema/archive/patch-2209-16-8.sql'
=== renamed file 'database/schema/patch-2209-17-0.sql' => 'database/schema/archive/patch-2209-17-0.sql'
=== renamed file 'database/schema/patch-2209-17-1.sql' => 'database/schema/archive/patch-2209-17-1.sql'
=== renamed file 'database/schema/patch-2209-18-0.sql' => 'database/schema/archive/patch-2209-18-0.sql'
=== renamed file 'database/schema/patch-2209-18-1.sql' => 'database/schema/archive/patch-2209-18-1.sql'
=== renamed file 'database/schema/patch-2209-18-2.sql' => 'database/schema/archive/patch-2209-18-2.sql'
=== renamed file 'database/schema/patch-2209-18-3.sql' => 'database/schema/archive/patch-2209-18-3.sql'
=== renamed file 'database/schema/patch-2209-18-4.sql' => 'database/schema/archive/patch-2209-18-4.sql'
=== renamed file 'database/schema/patch-2209-19-0.sql' => 'database/schema/archive/patch-2209-19-0.sql'
=== renamed file 'database/schema/patch-2209-19-1.sql' => 'database/schema/archive/patch-2209-19-1.sql'
=== renamed file 'database/schema/patch-2209-19-2.sql' => 'database/schema/archive/patch-2209-19-2.sql'
=== renamed file 'database/schema/patch-2209-19-3.sql' => 'database/schema/archive/patch-2209-19-3.sql'
=== renamed file 'database/schema/patch-2209-20-0.sql' => 'database/schema/archive/patch-2209-20-0.sql'
=== renamed file 'database/schema/patch-2209-20-1.sql' => 'database/schema/archive/patch-2209-20-1.sql'
=== renamed file 'database/schema/patch-2209-21-0.sql' => 'database/schema/archive/patch-2209-21-0.sql'
=== renamed file 'database/schema/patch-2209-21-1.sql' => 'database/schema/archive/patch-2209-21-1.sql'
=== renamed file 'database/schema/patch-2209-21-2.sql' => 'database/schema/archive/patch-2209-21-2.sql'
=== renamed file 'database/schema/patch-2209-21-3.sql' => 'database/schema/archive/patch-2209-21-3.sql'
=== renamed file 'database/schema/patch-2209-21-4.sql' => 'database/schema/archive/patch-2209-21-4.sql'
=== renamed file 'database/schema/patch-2209-22-0.sql' => 'database/schema/archive/patch-2209-22-0.sql'
=== renamed file 'database/schema/patch-2209-23-0.sql' => 'database/schema/archive/patch-2209-23-0.sql'
=== renamed file 'database/schema/patch-2209-23-1.sql' => 'database/schema/archive/patch-2209-23-1.sql'
=== renamed file 'database/schema/patch-2209-23-2.sql' => 'database/schema/archive/patch-2209-23-2.sql'
=== renamed file 'database/schema/patch-2209-23-3.sql' => 'database/schema/archive/patch-2209-23-3.sql'
=== renamed file 'database/schema/patch-2209-23-4.sql' => 'database/schema/archive/patch-2209-23-4.sql'
=== renamed file 'database/schema/patch-2209-23-5.sql' => 'database/schema/archive/patch-2209-23-5.sql'
=== renamed file 'database/schema/patch-2209-24-1.sql' => 'database/schema/archive/patch-2209-24-1.sql'
=== renamed file 'database/schema/patch-2209-24-2.sql' => 'database/schema/archive/patch-2209-24-2.sql'
=== renamed file 'database/schema/patch-2209-24-3.sql' => 'database/schema/archive/patch-2209-24-3.sql'
=== renamed file 'database/schema/patch-2209-25-1.sql' => 'database/schema/archive/patch-2209-25-1.sql'
=== renamed file 'database/schema/patch-2209-26-0.sql' => 'database/schema/archive/patch-2209-26-0.sql'
=== renamed file 'database/schema/patch-2209-26-1.sql' => 'database/schema/archive/patch-2209-26-1.sql'
=== renamed file 'database/schema/patch-2209-26-2.sql' => 'database/schema/archive/patch-2209-26-2.sql'
=== renamed file 'database/schema/patch-2209-26-3.sql' => 'database/schema/archive/patch-2209-26-3.sql'
=== renamed file 'database/schema/patch-2209-26-4.sql' => 'database/schema/archive/patch-2209-26-4.sql'
=== renamed file 'database/schema/patch-2209-26-5.sql' => 'database/schema/archive/patch-2209-26-5.sql'
=== renamed file 'database/schema/patch-2209-27-1.sql' => 'database/schema/archive/patch-2209-27-1.sql'
=== renamed file 'database/schema/patch-2209-27-2.sql' => 'database/schema/archive/patch-2209-27-2.sql'
=== renamed file 'database/schema/patch-2209-27-3.sql' => 'database/schema/archive/patch-2209-27-3.sql'
=== renamed file 'database/schema/patch-2209-27-4.sql' => 'database/schema/archive/patch-2209-27-4.sql'
=== renamed file 'database/schema/patch-2209-28-1.sql' => 'database/schema/archive/patch-2209-28-1.sql'
=== renamed file 'database/schema/patch-2209-28-2.sql' => 'database/schema/archive/patch-2209-28-2.sql'
=== renamed file 'database/schema/patch-2209-28-4.sql' => 'database/schema/archive/patch-2209-28-4.sql'
=== renamed file 'database/schema/patch-2209-28-5.sql' => 'database/schema/archive/patch-2209-28-5.sql'
=== renamed file 'database/schema/patch-2209-28-6.sql' => 'database/schema/archive/patch-2209-28-6.sql'
=== renamed file 'database/schema/patch-2209-29-0.sql' => 'database/schema/archive/patch-2209-29-0.sql'
=== renamed file 'database/schema/patch-2209-30-1.sql' => 'database/schema/archive/patch-2209-30-1.sql'
=== renamed file 'database/schema/patch-2209-30-2.sql' => 'database/schema/archive/patch-2209-30-2.sql'
=== renamed file 'database/schema/patch-2209-31-1.sql' => 'database/schema/archive/patch-2209-31-1.sql'
=== renamed file 'database/schema/patch-2209-31-2.sql' => 'database/schema/archive/patch-2209-31-2.sql'
=== renamed file 'database/schema/patch-2209-31-3.sql' => 'database/schema/archive/patch-2209-31-3.sql'
=== renamed file 'database/schema/patch-2209-32-0.sql' => 'database/schema/archive/patch-2209-32-0.sql'
=== renamed file 'database/schema/patch-2209-34-1.sql' => 'database/schema/archive/patch-2209-34-1.sql'
=== renamed file 'database/schema/patch-2209-35-1.sql' => 'database/schema/archive/patch-2209-35-1.sql'
=== renamed file 'database/schema/patch-2209-35-2.sql' => 'database/schema/archive/patch-2209-35-2.sql'
=== renamed file 'database/schema/patch-2209-35-3.sql' => 'database/schema/archive/patch-2209-35-3.sql'
=== renamed file 'database/schema/patch-2209-35-4.sql' => 'database/schema/archive/patch-2209-35-4.sql'
=== renamed file 'database/schema/patch-2209-36-0.sql' => 'database/schema/archive/patch-2209-36-0.sql'
=== renamed file 'database/schema/patch-2209-36-1.sql' => 'database/schema/archive/patch-2209-36-1.sql'
=== renamed file 'database/schema/patch-2209-37-0.sql' => 'database/schema/archive/patch-2209-37-0.sql'
=== renamed file 'database/schema/patch-2209-38-0.sql' => 'database/schema/archive/patch-2209-38-0.sql'
=== renamed file 'database/schema/patch-2209-38-1.sql' => 'database/schema/archive/patch-2209-38-1.sql'
=== renamed file 'database/schema/patch-2209-38-2.sql' => 'database/schema/archive/patch-2209-38-2.sql'
=== renamed file 'database/schema/patch-2209-38-3.sql' => 'database/schema/archive/patch-2209-38-3.sql'
=== renamed file 'database/schema/patch-2209-39-0.sql' => 'database/schema/archive/patch-2209-39-0.sql'
=== renamed file 'database/schema/patch-2209-39-1.sql' => 'database/schema/archive/patch-2209-39-1.sql'
=== renamed file 'database/schema/patch-2209-40-0.sql' => 'database/schema/archive/patch-2209-40-0.sql'
=== renamed file 'database/schema/patch-2209-40-1.sql' => 'database/schema/archive/patch-2209-40-1.sql'
=== renamed file 'database/schema/patch-2209-40-2.sql' => 'database/schema/archive/patch-2209-40-2.sql'
=== renamed file 'database/schema/patch-2209-40-3.sql' => 'database/schema/archive/patch-2209-40-3.sql'
=== renamed file 'database/schema/patch-2209-41-0.sql' => 'database/schema/archive/patch-2209-41-0.sql'
=== renamed file 'database/schema/patch-2209-41-1.sql' => 'database/schema/archive/patch-2209-41-1.sql'
=== renamed file 'database/schema/patch-2209-41-2.sql' => 'database/schema/archive/patch-2209-41-2.sql'
=== renamed file 'database/schema/patch-2209-41-3.sql' => 'database/schema/archive/patch-2209-41-3.sql'
=== renamed file 'database/schema/patch-2209-41-4.sql' => 'database/schema/archive/patch-2209-41-4.sql'
=== renamed file 'database/schema/patch-2209-41-5.sql' => 'database/schema/archive/patch-2209-41-5.sql'
=== renamed file 'database/schema/patch-2209-42-0.sql' => 'database/schema/archive/patch-2209-42-0.sql'
=== renamed file 'database/schema/patch-2209-43-0.sql' => 'database/schema/archive/patch-2209-43-0.sql'
=== renamed file 'database/schema/patch-2209-44-0.sql' => 'database/schema/archive/patch-2209-44-0.sql'
=== renamed file 'database/schema/patch-2209-44-1.sql' => 'database/schema/archive/patch-2209-44-1.sql'
=== renamed file 'database/schema/patch-2209-44-2.sql' => 'database/schema/archive/patch-2209-44-2.sql'
=== renamed file 'database/schema/patch-2209-44-3.sql' => 'database/schema/archive/patch-2209-44-3.sql'
=== renamed file 'database/schema/patch-2209-44-4.sql' => 'database/schema/archive/patch-2209-44-4.sql'
=== renamed file 'database/schema/patch-2209-45-0.sql' => 'database/schema/archive/patch-2209-45-0.sql'
=== renamed file 'database/schema/patch-2209-46-0.sql' => 'database/schema/archive/patch-2209-46-0.sql'
=== renamed file 'database/schema/patch-2209-47-0.sql' => 'database/schema/archive/patch-2209-47-0.sql'
=== renamed file 'database/schema/patch-2209-47-1.sql' => 'database/schema/archive/patch-2209-47-1.sql'
=== renamed file 'database/schema/patch-2209-48-0.sql' => 'database/schema/archive/patch-2209-48-0.sql'
=== renamed file 'database/schema/patch-2209-49-0.sql' => 'database/schema/archive/patch-2209-49-0.sql'
=== renamed file 'database/schema/patch-2209-49-1.sql' => 'database/schema/archive/patch-2209-49-1.sql'
=== renamed file 'database/schema/patch-2209-49-2.sql' => 'database/schema/archive/patch-2209-49-2.sql'
=== renamed file 'database/schema/patch-2209-50-0.sql' => 'database/schema/archive/patch-2209-50-0.sql'
=== renamed file 'database/schema/patch-2209-51-0.sql' => 'database/schema/archive/patch-2209-51-0.sql'
=== renamed file 'database/schema/patch-2209-51-1.sql' => 'database/schema/archive/patch-2209-51-1.sql'
=== renamed file 'database/schema/patch-2209-51-2.sql' => 'database/schema/archive/patch-2209-51-2.sql'
=== renamed file 'database/schema/patch-2209-52-0.sql' => 'database/schema/archive/patch-2209-52-0.sql'
=== renamed file 'database/schema/patch-2209-53-0.sql' => 'database/schema/archive/patch-2209-53-0.sql'
=== renamed file 'database/schema/patch-2209-53-1.sql' => 'database/schema/archive/patch-2209-53-1.sql'
=== renamed file 'database/schema/patch-2209-53-3.sql' => 'database/schema/archive/patch-2209-53-3.sql'
=== renamed file 'database/schema/patch-2209-53-4.sql' => 'database/schema/archive/patch-2209-53-4.sql'
=== renamed file 'database/schema/patch-2209-53-5.sql' => 'database/schema/archive/patch-2209-53-5.sql'
=== renamed file 'database/schema/patch-2209-53-6.sql' => 'database/schema/archive/patch-2209-53-6.sql'
=== renamed file 'database/schema/patch-2209-53-7.sql' => 'database/schema/archive/patch-2209-53-7.sql'
=== renamed file 'database/schema/patch-2209-53-8.sql' => 'database/schema/archive/patch-2209-53-8.sql'
=== renamed file 'database/schema/patch-2209-53-9.sql' => 'database/schema/archive/patch-2209-53-9.sql'
=== renamed file 'database/schema/patch-2209-54-0.sql' => 'database/schema/archive/patch-2209-54-0.sql'
=== renamed file 'database/schema/patch-2209-55-0.sql' => 'database/schema/archive/patch-2209-55-0.sql'
=== renamed file 'database/schema/patch-2209-56-0.sql' => 'database/schema/archive/patch-2209-56-0.sql'
=== renamed file 'database/schema/patch-2209-56-1.sql' => 'database/schema/archive/patch-2209-56-1.sql'
=== renamed file 'database/schema/patch-2209-56-2.sql' => 'database/schema/archive/patch-2209-56-2.sql'
=== renamed file 'database/schema/patch-2209-56-3.sql' => 'database/schema/archive/patch-2209-56-3.sql'
=== renamed file 'database/schema/patch-2209-56-4.sql' => 'database/schema/archive/patch-2209-56-4.sql'
=== renamed file 'database/schema/patch-2209-57-0.sql' => 'database/schema/archive/patch-2209-57-0.sql'
=== renamed file 'database/schema/patch-2209-58-0.sql' => 'database/schema/archive/patch-2209-58-0.sql'
=== renamed file 'database/schema/patch-2209-58-1.sql' => 'database/schema/archive/patch-2209-58-1.sql'
=== renamed file 'database/schema/patch-2209-58-2.sql' => 'database/schema/archive/patch-2209-58-2.sql'
=== renamed file 'database/schema/patch-2209-58-3.sql' => 'database/schema/archive/patch-2209-58-3.sql'
=== renamed file 'database/schema/patch-2209-58-4.sql' => 'database/schema/archive/patch-2209-58-4.sql'
=== renamed file 'database/schema/patch-2209-59-0.sql' => 'database/schema/archive/patch-2209-59-0.sql'
=== renamed file 'database/schema/patch-2209-59-1.sql' => 'database/schema/archive/patch-2209-59-1.sql'
=== renamed file 'database/schema/patch-2209-59-2.sql' => 'database/schema/archive/patch-2209-59-2.sql'
=== renamed file 'database/schema/patch-2209-60-0.sql' => 'database/schema/archive/patch-2209-60-0.sql'
=== renamed file 'database/schema/patch-2209-61-0.sql' => 'database/schema/archive/patch-2209-61-0.sql'
=== renamed file 'database/schema/patch-2209-61-1.sql' => 'database/schema/archive/patch-2209-61-1.sql'
=== renamed file 'database/schema/patch-2209-61-2.sql' => 'database/schema/archive/patch-2209-61-2.sql'
=== renamed file 'database/schema/patch-2209-61-3.sql' => 'database/schema/archive/patch-2209-61-3.sql'
=== renamed file 'database/schema/patch-2209-61-4.sql' => 'database/schema/archive/patch-2209-61-4.sql'
=== renamed file 'database/schema/patch-2209-61-5.sql' => 'database/schema/archive/patch-2209-61-5.sql'
=== renamed file 'database/schema/patch-2209-61-6.sql' => 'database/schema/archive/patch-2209-61-6.sql'
=== renamed file 'database/schema/patch-2209-61-7.sql' => 'database/schema/archive/patch-2209-61-7.sql'
=== renamed file 'database/schema/patch-2209-61-8.sql' => 'database/schema/archive/patch-2209-61-8.sql'
=== renamed file 'database/schema/patch-2209-61-9.sql' => 'database/schema/archive/patch-2209-61-9.sql'
=== renamed file 'database/schema/patch-2209-62-0.sql' => 'database/schema/archive/patch-2209-62-0.sql'
=== renamed file 'database/schema/patch-2209-62-1.sql' => 'database/schema/archive/patch-2209-62-1.sql'
=== renamed file 'database/schema/patch-2209-64-0.sql' => 'database/schema/archive/patch-2209-64-0.sql'
=== renamed file 'database/schema/patch-2209-64-1.sql' => 'database/schema/archive/patch-2209-64-1.sql'
=== renamed file 'database/schema/patch-2209-65-0.sql' => 'database/schema/archive/patch-2209-65-0.sql'
=== renamed file 'database/schema/patch-2209-66-0.sql' => 'database/schema/archive/patch-2209-66-0.sql'
=== renamed file 'database/schema/patch-2209-66-1.sql' => 'database/schema/archive/patch-2209-66-1.sql'
=== renamed file 'database/schema/patch-2209-67-0.sql' => 'database/schema/archive/patch-2209-67-0.sql'
=== renamed file 'database/schema/patch-2209-67-1.sql' => 'database/schema/archive/patch-2209-67-1.sql'
=== renamed file 'database/schema/patch-2209-67-2.sql' => 'database/schema/archive/patch-2209-67-2.sql'
=== renamed file 'database/schema/patch-2209-67-3.sql' => 'database/schema/archive/patch-2209-67-3.sql'
=== renamed file 'database/schema/patch-2209-68-0.sql' => 'database/schema/archive/patch-2209-68-0.sql'
=== renamed file 'database/schema/patch-2209-68-1.sql' => 'database/schema/archive/patch-2209-68-1.sql'
=== renamed file 'database/schema/patch-2209-68-2.sql' => 'database/schema/archive/patch-2209-68-2.sql'
=== renamed file 'database/schema/patch-2209-69-0.sql' => 'database/schema/archive/patch-2209-69-0.sql'
=== renamed file 'database/schema/patch-2209-69-1.sql' => 'database/schema/archive/patch-2209-69-1.sql'
=== renamed file 'database/schema/patch-2209-69-2.sql' => 'database/schema/archive/patch-2209-69-2.sql'
=== renamed file 'database/schema/patch-2209-69-3.sql' => 'database/schema/archive/patch-2209-69-3.sql'
=== renamed file 'database/schema/patch-2209-69-4.sql' => 'database/schema/archive/patch-2209-69-4.sql'
=== renamed file 'database/schema/patch-2209-69-5.sql' => 'database/schema/archive/patch-2209-69-5.sql'
=== renamed file 'database/schema/patch-2209-69-6.sql' => 'database/schema/archive/patch-2209-69-6.sql'
=== renamed file 'database/schema/patch-2209-69-7.sql' => 'database/schema/archive/patch-2209-69-7.sql'
=== renamed file 'database/schema/patch-2209-69-8.sql' => 'database/schema/archive/patch-2209-69-8.sql'
=== renamed file 'database/schema/patch-2209-69-9.sql' => 'database/schema/archive/patch-2209-69-9.sql'
=== renamed file 'database/schema/patch-2209-70-0.sql' => 'database/schema/archive/patch-2209-70-0.sql'
=== renamed file 'database/schema/patch-2209-71-0.sql' => 'database/schema/archive/patch-2209-71-0.sql'
=== renamed file 'database/schema/patch-2209-71-1.sql' => 'database/schema/archive/patch-2209-71-1.sql'
=== renamed file 'database/schema/patch-2209-72-0.sql' => 'database/schema/archive/patch-2209-72-0.sql'
=== renamed file 'database/schema/patch-2209-73-0.sql' => 'database/schema/archive/patch-2209-73-0.sql'
=== renamed file 'database/schema/patch-2209-73-1.sql' => 'database/schema/archive/patch-2209-73-1.sql'
=== renamed file 'database/schema/patch-2209-74-0.sql' => 'database/schema/archive/patch-2209-74-0.sql'
=== renamed file 'database/schema/patch-2209-75-0.sql' => 'database/schema/archive/patch-2209-75-0.sql'
=== renamed file 'database/schema/patch-2209-75-1.sql' => 'database/schema/archive/patch-2209-75-1.sql'
=== renamed file 'database/schema/patch-2209-77-0.sql' => 'database/schema/archive/patch-2209-77-0.sql'
=== renamed file 'database/schema/patch-2209-77-1.sql' => 'database/schema/archive/patch-2209-77-1.sql'
=== renamed file 'database/schema/patch-2209-77-2.sql' => 'database/schema/archive/patch-2209-77-2.sql'
=== renamed file 'database/schema/patch-2209-77-3.sql' => 'database/schema/archive/patch-2209-77-3.sql'
=== renamed file 'database/schema/patch-2209-78-0.sql' => 'database/schema/archive/patch-2209-78-0.sql'
=== renamed file 'database/schema/patch-2209-78-1.sql' => 'database/schema/archive/patch-2209-78-1.sql'
=== renamed file 'database/schema/patch-2209-78-2.sql' => 'database/schema/archive/patch-2209-78-2.sql'
=== renamed file 'database/schema/patch-2209-79-0.sql' => 'database/schema/archive/patch-2209-79-0.sql'
=== renamed file 'database/schema/patch-2209-80-0.sql' => 'database/schema/archive/patch-2209-80-0.sql'
=== renamed file 'database/schema/patch-2209-80-1.sql' => 'database/schema/archive/patch-2209-80-1.sql'
=== renamed file 'database/schema/patch-2209-80-2.sql' => 'database/schema/archive/patch-2209-80-2.sql'
=== renamed file 'database/schema/patch-2209-81-0.sql' => 'database/schema/archive/patch-2209-81-0.sql'
=== renamed file 'database/schema/patch-2209-82-0.sql' => 'database/schema/archive/patch-2209-82-0.sql'
=== renamed file 'database/schema/patch-2209-82-1.sql' => 'database/schema/archive/patch-2209-82-1.sql'
=== renamed file 'database/schema/patch-2209-83-0.sql' => 'database/schema/archive/patch-2209-83-0.sql'
=== renamed file 'database/schema/patch-2209-83-1.sql' => 'database/schema/archive/patch-2209-83-1.sql'
=== renamed file 'database/schema/patch-2209-83-2.sql' => 'database/schema/archive/patch-2209-83-2.sql'
=== renamed file 'database/schema/patch-2209-83-3.sql' => 'database/schema/archive/patch-2209-83-3.sql'
=== renamed file 'database/schema/patch-2209-83-4.sql' => 'database/schema/archive/patch-2209-83-4.sql'
=== renamed file 'database/schema/patch-2209-83-5.sql' => 'database/schema/archive/patch-2209-83-5.sql'
=== renamed file 'database/schema/patch-2209-83-6.sql' => 'database/schema/archive/patch-2209-83-6.sql'
=== renamed file 'database/schema/patch-2209-84-0.sql' => 'database/schema/archive/patch-2209-84-0.sql'
=== renamed file 'database/schema/patch-2209-84-1.sql' => 'database/schema/archive/patch-2209-84-1.sql'
=== renamed file 'database/schema/patch-2209-85-0.sql' => 'database/schema/archive/patch-2209-85-0.sql'
=== renamed file 'database/schema/patch-2209-85-1.sql' => 'database/schema/archive/patch-2209-85-1.sql'
=== renamed file 'database/schema/patch-2209-86-0.sql' => 'database/schema/archive/patch-2209-86-0.sql'
=== renamed file 'database/schema/launchpad-2209-00-0.sql' => 'database/schema/launchpad-2210-00-0.sql'
--- database/schema/launchpad-2209-00-0.sql 2015-07-21 09:04:01 +0000
+++ database/schema/launchpad-2210-00-0.sql 2019-02-26 07:46:16 +0000
@@ -1,9 +1,11 @@
1-- Generated Tue Dec 6 20:57:32 2011 UTC1-- Generated Mon Feb 25 21:35:23 2019 UTC
22
3SET client_min_messages TO ERROR;3SET client_min_messages TO ERROR;
4SET statement_timeout = 0;4SET statement_timeout = 0;
5SET lock_timeout = 0;
5SET client_encoding = 'UTF8';6SET client_encoding = 'UTF8';
6SET standard_conforming_strings = off;7SET standard_conforming_strings = off;
8SELECT pg_catalog.set_config('search_path', '', false);
7SET check_function_bodies = false;9SET check_function_bodies = false;
8SET client_min_messages = warning;10SET client_min_messages = warning;
9SET escape_string_warning = off;11SET escape_string_warning = off;
@@ -11,56 +13,72 @@
11CREATE SCHEMA todrop;13CREATE SCHEMA todrop;
1214
1315
14CREATE SCHEMA ts2;16CREATE SCHEMA trgm;
1517
1618
17CREATE PROCEDURAL LANGUAGE plpgsql;19CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
1820
1921
20CREATE PROCEDURAL LANGUAGE plpythonu;22COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
2123
2224
23SET search_path = public, pg_catalog;25CREATE EXTENSION IF NOT EXISTS plpythonu WITH SCHEMA pg_catalog;
2426
25CREATE TYPE debversion;27
2628COMMENT ON EXTENSION plpythonu IS 'PL/PythonU untrusted procedural language';
2729
28CREATE FUNCTION debversionin(cstring) RETURNS debversion30
31CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA trgm;
32
33
34COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';
35
36
37CREATE EXTENSION IF NOT EXISTS pgstattuple WITH SCHEMA public;
38
39
40COMMENT ON EXTENSION pgstattuple IS 'show tuple-level statistics';
41
42
43CREATE TYPE public.debversion;
44
45
46CREATE FUNCTION public.debversionin(cstring) RETURNS public.debversion
29 LANGUAGE internal IMMUTABLE STRICT47 LANGUAGE internal IMMUTABLE STRICT
30 AS $$textin$$;48 AS $$textin$$;
3149
3250
33CREATE FUNCTION debversionout(debversion) RETURNS cstring51CREATE FUNCTION public.debversionout(public.debversion) RETURNS cstring
34 LANGUAGE internal IMMUTABLE STRICT52 LANGUAGE internal IMMUTABLE STRICT
35 AS $$textout$$;53 AS $$textout$$;
3654
3755
38CREATE FUNCTION debversionrecv(internal) RETURNS debversion56CREATE FUNCTION public.debversionrecv(internal) RETURNS public.debversion
39 LANGUAGE internal STABLE STRICT57 LANGUAGE internal STABLE STRICT
40 AS $$textrecv$$;58 AS $$textrecv$$;
4159
4260
43CREATE FUNCTION debversionsend(debversion) RETURNS bytea61CREATE FUNCTION public.debversionsend(public.debversion) RETURNS bytea
44 LANGUAGE internal STABLE STRICT62 LANGUAGE internal STABLE STRICT
45 AS $$textsend$$;63 AS $$textsend$$;
4664
4765
48CREATE TYPE debversion (66CREATE TYPE public.debversion (
49 INTERNALLENGTH = variable,67 INTERNALLENGTH = variable,
50 INPUT = debversionin,68 INPUT = public.debversionin,
51 OUTPUT = debversionout,69 OUTPUT = public.debversionout,
52 RECEIVE = debversionrecv,70 RECEIVE = public.debversionrecv,
53 SEND = debversionsend,71 SEND = public.debversionsend,
54 CATEGORY = 'S',72 CATEGORY = 'S',
55 ALIGNMENT = int4,73 ALIGNMENT = int4,
56 STORAGE = extended74 STORAGE = extended
57);75);
5876
5977
60COMMENT ON TYPE debversion IS 'Debian package version number';78COMMENT ON TYPE public.debversion IS 'Debian package version number';
6179
6280
63CREATE TYPE pgstattuple_type AS (81CREATE TYPE public.pgstattuple_type AS (
64 table_len bigint,82 table_len bigint,
65 tuple_count bigint,83 tuple_count bigint,
66 tuple_len bigint,84 tuple_len bigint,
@@ -73,77 +91,337 @@
73);91);
7492
7593
76SET search_path = ts2, pg_catalog;94CREATE DOMAIN public.ts2_tsvector AS tsvector;
7795
78CREATE DOMAIN gtsq AS text;96
7997CREATE FUNCTION public._ftq(text) RETURNS text
8098 LANGUAGE plpythonu IMMUTABLE STRICT
81CREATE DOMAIN gtsvector AS pg_catalog.gtsvector;99 AS $_$
82100 import re
83101
84CREATE TYPE statinfo AS (102 # I think this method would be more robust if we used a real
85 word text,103 # tokenizer and parser to generate the query string, but we need
86 ndoc integer,104 # something suitable for use as a stored procedure which currently
87 nentry integer105 # means no external dependancies.
88);106
89107 # Convert to Unicode
90108 query = args[0].decode('utf8')
91CREATE TYPE tokenout AS (109 ## plpy.debug('1 query is %s' % repr(query))
92 tokid integer,110
93 token text111 # Replace tsquery operators with ' '. '<' begins all the phrase
94);112 # search operators, and a standalone '>' is fine.
95113 query = re.sub('[|&!<]', ' ', query)
96114
97CREATE TYPE tokentype AS (115 # Normalize whitespace
98 tokid integer,116 query = re.sub("(?u)\s+"," ", query)
99 alias text,117
100 descr text118 # Convert AND, OR, NOT to tsearch2 punctuation
101);119 query = re.sub(r"(?u)\bAND\b", "&", query)
102120 query = re.sub(r"(?u)\bOR\b", "|", query)
103121 query = re.sub(r"(?u)\bNOT\b", " !", query)
104CREATE TYPE tsdebug AS (122 ## plpy.debug('2 query is %s' % repr(query))
105 ts_name text,123
106 tok_type text,124 # Deal with unwanted punctuation.
107 description text,125 # ':' is used in queries to specify a weight of a word.
108 token text,126 # '\' is treated differently in to_tsvector() and to_tsquery().
109 dict_name text[],127 punctuation = r'[:\\]'
110 tsvector pg_catalog.tsvector128 query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
111);129 ## plpy.debug('3 query is %s' % repr(query))
112130
113131 # Now that we have handle case sensitive booleans, convert to lowercase
114CREATE DOMAIN tsquery AS pg_catalog.tsquery;132 query = query.lower()
115133
116134 # Remove unpartnered bracket on the left and right
117CREATE DOMAIN tsvector AS pg_catalog.tsvector;135 query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
118136 query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
119137
120SET search_path = public, pg_catalog;138 # Remove spurious brackets
121139 query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
122CREATE FUNCTION activity() RETURNS SETOF pg_stat_activity140 ## plpy.debug('5 query is %s' % repr(query))
123 LANGUAGE sql SECURITY DEFINER141
124 SET search_path TO public142 # Insert & between tokens without an existing boolean operator
125 AS $$143 # ( not proceeded by (|&!
126 SELECT144 query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
127 datid, datname, procpid, usesysid, usename,145 ## plpy.debug('6 query is %s' % repr(query))
128 CASE146 # ) not followed by )|&
129 WHEN current_query LIKE '<IDLE>%'147 query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
130 OR current_query LIKE 'autovacuum:%'148 ## plpy.debug('6.1 query is %s' % repr(query))
131 THEN current_query149 # Whitespace not proceded by (|&! not followed by &|
132 ELSE150 query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
133 '<HIDDEN>'151 ## plpy.debug('7 query is %s' % repr(query))
134 END AS current_query,152
135 waiting, xact_start, query_start,153 # Detect and repair syntax errors - we are lenient because
136 backend_start, client_addr, client_port154 # this input is generally from users.
137 FROM pg_catalog.pg_stat_activity;155
138$$;156 # Fix unbalanced brackets
139157 openings = query.count("(")
140158 closings = query.count(")")
141COMMENT ON FUNCTION activity() IS 'SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.';159 if openings > closings:
142160 query = query + " ) "*(openings-closings)
143161 elif closings > openings:
144CREATE FUNCTION add_test_openid_identifier(account_ integer) RETURNS boolean162 query = " ( "*(closings-openings) + query
145 LANGUAGE plpgsql SECURITY DEFINER163 ## plpy.debug('8 query is %s' % repr(query))
146 SET search_path TO public164
165 # Strip ' character that do not have letters on both sides
166 query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
167
168 # Brackets containing nothing but whitespace and booleans, recursive
169 last = ""
170 while last != query:
171 last = query
172 query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
173 ## plpy.debug('9 query is %s' % repr(query))
174
175 # An & or | following a (
176 query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
177 ## plpy.debug('10 query is %s' % repr(query))
178
179 # An &, | or ! immediatly before a )
180 query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
181 ## plpy.debug('11 query is %s' % repr(query))
182
183 # An &,| or ! followed by another boolean.
184 query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
185 ## plpy.debug('12 query is %s' % repr(query))
186
187 # Leading & or |
188 query = re.sub(r"(?u)^[\s\&\|]+", "", query)
189 ## plpy.debug('13 query is %s' % repr(query))
190
191 # Trailing &, | or !
192 query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
193 ## plpy.debug('14 query is %s' % repr(query))
194
195 # If we have nothing but whitespace and tsearch2 operators,
196 # return NULL.
197 if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
198 return None
199
200 # Convert back to UTF-8
201 query = query.encode('utf8')
202 ## plpy.debug('15 query is %s' % repr(query))
203
204 return query or None
205 $_$;
206
207
208CREATE FUNCTION public.accessartifact_denorm_to_artifacts(artifact_id integer) RETURNS void
209 LANGUAGE plpgsql
210 AS $$
211DECLARE
212 artifact_row accessartifact%ROWTYPE;
213BEGIN
214 SELECT * INTO artifact_row FROM accessartifact WHERE id = artifact_id;
215 IF artifact_row.bug IS NOT NULL THEN
216 PERFORM bug_flatten_access(artifact_row.bug);
217 END IF;
218 IF artifact_row.branch IS NOT NULL THEN
219 PERFORM branch_denorm_access(artifact_row.branch);
220 END IF;
221 IF artifact_row.gitrepository IS NOT NULL THEN
222 PERFORM gitrepository_denorm_access(artifact_row.gitrepository);
223 END IF;
224 IF artifact_row.specification IS NOT NULL THEN
225 PERFORM specification_denorm_access(artifact_row.specification);
226 END IF;
227 RETURN;
228END;
229$$;
230
231
232COMMENT 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.';
233
234
235CREATE FUNCTION public.accessartifact_maintain_denorm_to_artifacts_trig() RETURNS trigger
236 LANGUAGE plpgsql
237 AS $$
238BEGIN
239 IF TG_OP = 'INSERT' THEN
240 PERFORM accessartifact_denorm_to_artifacts(NEW.artifact);
241 ELSIF TG_OP = 'UPDATE' THEN
242 PERFORM accessartifact_denorm_to_artifacts(NEW.artifact);
243 IF OLD.artifact != NEW.artifact THEN
244 PERFORM accessartifact_denorm_to_artifacts(OLD.artifact);
245 END IF;
246 ELSIF TG_OP = 'DELETE' THEN
247 PERFORM accessartifact_denorm_to_artifacts(OLD.artifact);
248 END IF;
249 RETURN NULL;
250END;
251$$;
252
253
254CREATE FUNCTION public.accessartifactgrant_maintain_accesspolicygrantflat_trig() RETURNS trigger
255 LANGUAGE plpgsql SECURITY DEFINER
256 SET search_path TO 'public'
257 AS $$
258BEGIN
259 IF TG_OP = 'INSERT' THEN
260 INSERT INTO AccessPolicyGrantFlat
261 (policy, artifact, grantee)
262 SELECT policy, NEW.artifact, NEW.grantee
263 FROM AccessPolicyArtifact WHERE artifact = NEW.artifact;
264 ELSIF TG_OP = 'UPDATE' THEN
265 IF NEW.artifact != OLD.artifact OR NEW.grantee != OLD.grantee THEN
266 UPDATE AccessPolicyGrantFlat
267 SET artifact=NEW.artifact, grantee=NEW.grantee
268 WHERE artifact = OLD.artifact AND grantee = OLD.grantee;
269 END IF;
270 ELSIF TG_OP = 'DELETE' THEN
271 DELETE FROM AccessPolicyGrantFlat
272 WHERE artifact = OLD.artifact AND grantee = OLD.grantee;
273 END IF;
274 RETURN NULL;
275END;
276$$;
277
278
279CREATE FUNCTION public.accesspolicyartifact_maintain_accesspolicyartifactflat_trig() RETURNS trigger
280 LANGUAGE plpgsql SECURITY DEFINER
281 SET search_path TO 'public'
282 AS $$
283BEGIN
284 IF TG_OP = 'INSERT' THEN
285 INSERT INTO AccessPolicyGrantFlat
286 (policy, artifact, grantee)
287 SELECT NEW.policy, NEW.artifact, grantee
288 FROM AccessArtifactGrant WHERE artifact = NEW.artifact;
289 ELSIF TG_OP = 'UPDATE' THEN
290 IF NEW.policy != OLD.policy OR NEW.artifact != OLD.artifact THEN
291 UPDATE AccessPolicyGrantFlat
292 SET policy=NEW.policy, artifact=NEW.artifact
293 WHERE policy = OLD.policy AND artifact = OLD.artifact;
294 END IF;
295 ELSIF TG_OP = 'DELETE' THEN
296 DELETE FROM AccessPolicyGrantFlat
297 WHERE policy = OLD.policy AND artifact = OLD.artifact;
298 END IF;
299 RETURN NULL;
300END;
301$$;
302
303
304CREATE FUNCTION public.accesspolicygrant_maintain_accesspolicygrantflat_trig() RETURNS trigger
305 LANGUAGE plpgsql SECURITY DEFINER
306 SET search_path TO 'public'
307 AS $$
308BEGIN
309 IF TG_OP = 'INSERT' THEN
310 INSERT INTO AccessPolicyGrantFlat
311 (policy, grantee) VALUES (NEW.policy, NEW.grantee);
312 ELSIF TG_OP = 'UPDATE' THEN
313 IF NEW.policy != OLD.policy OR NEW.grantee != OLD.grantee THEN
314 UPDATE AccessPolicyGrantFlat
315 SET policy=NEW.policy, grantee=NEW.grantee
316 WHERE
317 policy = OLD.policy
318 AND grantee = OLD.grantee
319 AND artifact IS NULL;
320 END IF;
321 ELSIF TG_OP = 'DELETE' THEN
322 DELETE FROM AccessPolicyGrantFlat
323 WHERE
324 policy = OLD.policy
325 AND grantee = OLD.grantee
326 AND artifact IS NULL;
327 END IF;
328 RETURN NULL;
329END;
330$$;
331
332
333CREATE FUNCTION public.activity() RETURNS SETOF pg_stat_activity
334 LANGUAGE plpgsql SECURITY DEFINER
335 SET search_path TO 'public'
336 AS $$
337DECLARE
338 a pg_stat_activity%ROWTYPE;
339BEGIN
340 IF EXISTS (
341 SELECT 1 FROM pg_attribute WHERE
342 attrelid =
343 (SELECT oid FROM pg_class
344 WHERE relname = 'pg_stat_activity')
345 AND attname = 'backend_type') THEN
346 -- >= 10
347 RETURN QUERY SELECT
348 datid, datname, pid, usesysid, usename, application_name,
349 client_addr, client_hostname, client_port, backend_start,
350 xact_start, query_start, state_change, wait_event_type,
351 wait_event, state, backend_xid, backend_xmin, backend_type,
352 CASE
353 WHEN query LIKE '<IDLE>%'
354 OR query LIKE 'autovacuum:%'
355 THEN query
356 ELSE
357 '<HIDDEN>'
358 END AS query
359 FROM pg_catalog.pg_stat_activity;
360 ELSIF EXISTS (
361 SELECT 1 FROM pg_attribute WHERE
362 attrelid =
363 (SELECT oid FROM pg_class
364 WHERE relname = 'pg_stat_activity')
365 AND attname = 'wait_event_type') THEN
366 -- >= 9.6
367 RETURN QUERY SELECT
368 datid, datname, pid, usesysid, usename, application_name,
369 client_addr, client_hostname, client_port, backend_start,
370 xact_start, query_start, state_change, wait_event_type,
371 wait_event, state, backend_xid, backend_xmin,
372 CASE
373 WHEN query LIKE '<IDLE>%'
374 OR query LIKE 'autovacuum:%'
375 THEN query
376 ELSE
377 '<HIDDEN>'
378 END AS query
379 FROM pg_catalog.pg_stat_activity;
380 ELSIF EXISTS (
381 SELECT 1 FROM pg_attribute WHERE
382 attrelid =
383 (SELECT oid FROM pg_class
384 WHERE relname = 'pg_stat_activity')
385 AND attname = 'backend_xid') THEN
386 -- >= 9.4
387 RETURN QUERY SELECT
388 datid, datname, pid, usesysid, usename, application_name,
389 client_addr, client_hostname, client_port, backend_start,
390 xact_start, query_start, state_change, waiting, state,
391 backend_xid, backend_xmin,
392 CASE
393 WHEN query LIKE '<IDLE>%'
394 OR query LIKE 'autovacuum:%'
395 THEN query
396 ELSE
397 '<HIDDEN>'
398 END AS query
399 FROM pg_catalog.pg_stat_activity;
400 ELSE
401 -- >= 9.2; anything older is unsupported
402 RETURN QUERY SELECT
403 datid, datname, pid, usesysid, usename, application_name,
404 client_addr, client_hostname, client_port, backend_start,
405 xact_start, query_start, state_change, waiting, state,
406 CASE
407 WHEN query LIKE '<IDLE>%'
408 OR query LIKE 'autovacuum:%'
409 THEN query
410 ELSE
411 '<HIDDEN>'
412 END AS query
413 FROM pg_catalog.pg_stat_activity;
414 END IF;
415END;
416$$;
417
418
419COMMENT ON FUNCTION public.activity() IS 'SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.';
420
421
422CREATE FUNCTION public.add_test_openid_identifier(account_ integer) RETURNS boolean
423 LANGUAGE plpgsql SECURITY DEFINER
424 SET search_path TO 'public'
147 AS $$425 AS $$
148BEGIN426BEGIN
149 -- The generated OpenIdIdentifier is not a valid OpenId Identity URL427 -- The generated OpenIdIdentifier is not a valid OpenId Identity URL
@@ -161,10 +439,10 @@
161$$;439$$;
162440
163441
164COMMENT 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.';442COMMENT 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.';
165443
166444
167CREATE FUNCTION assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean445CREATE FUNCTION public.assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean
168 LANGUAGE plpythonu STABLE446 LANGUAGE plpythonu STABLE
169 AS $$447 AS $$
170 rv = plpy.execute("""448 rv = plpy.execute("""
@@ -179,39 +457,98 @@
179$$;457$$;
180458
181459
182COMMENT ON FUNCTION assert_patch_applied(major integer, minor integer, patch integer) IS 'Raise an exception if the given database patch has not been applied.';460COMMENT 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.';
183461
184462
185CREATE FUNCTION bug_maintain_bug_summary() RETURNS trigger463CREATE FUNCTION public.branch_denorm_access(branch_id integer) RETURNS void
464 LANGUAGE sql SECURITY DEFINER
465 SET search_path TO 'public'
466 AS $_$
467 UPDATE branch
468 SET access_policy = policies[1], access_grants = grants
469 FROM
470 build_access_cache(
471 (SELECT id FROM accessartifact WHERE branch = $1),
472 (SELECT information_type FROM branch WHERE id = $1))
473 AS (policies integer[], grants integer[])
474 WHERE id = $1;
475$_$;
476
477
478CREATE FUNCTION public.branch_maintain_access_cache_trig() RETURNS trigger
479 LANGUAGE plpgsql
480 AS $$
481BEGIN
482 PERFORM branch_denorm_access(NEW.id);
483 RETURN NULL;
484END;
485$$;
486
487
488CREATE FUNCTION public.bug_build_access_cache(bug_id integer, information_type integer) RETURNS record
489 LANGUAGE sql
490 AS $_$
491 SELECT build_access_cache(
492 (SELECT id FROM accessartifact WHERE bug = $1), $2);
493$_$;
494
495
496COMMENT 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.';
497
498
499CREATE FUNCTION public.bug_flatten_access(bug_id integer) RETURNS void
500 LANGUAGE sql SECURITY DEFINER
501 SET search_path TO 'public'
502 AS $_$
503 UPDATE bugtaskflat
504 SET access_policies = policies, access_grants = grants
505 FROM
506 build_access_cache(
507 (SELECT id FROM accessartifact WHERE bug = $1),
508 (SELECT information_type FROM bug WHERE id = $1))
509 AS (policies integer[], grants integer[])
510 WHERE bug = $1;
511$_$;
512
513
514COMMENT ON FUNCTION public.bug_flatten_access(bug_id integer) IS 'Recalculate the access cache on a bug''s flattened tasks.';
515
516
517CREATE FUNCTION public.bug_maintain_bugtaskflat_trig() RETURNS trigger
186 LANGUAGE plpgsql SECURITY DEFINER518 LANGUAGE plpgsql SECURITY DEFINER
187 SET search_path TO public519 SET search_path TO 'public'
188 AS $$520 AS $$
189BEGIN521BEGIN
190 -- There is no INSERT logic, as a bug will not have any summary522 IF (
191 -- information until BugTask rows have been attached.523 NEW.duplicateof IS DISTINCT FROM OLD.duplicateof
192 IF TG_OP = 'UPDATE' THEN524 OR NEW.owner IS DISTINCT FROM OLD.owner
193 IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof525 OR NEW.fti IS DISTINCT FROM OLD.fti
194 OR OLD.private IS DISTINCT FROM NEW.private526 OR NEW.information_type IS DISTINCT FROM OLD.information_type
195 OR (OLD.latest_patch_uploaded IS NULL)527 OR NEW.date_last_updated IS DISTINCT FROM OLD.date_last_updated
196 <> (NEW.latest_patch_uploaded IS NULL) THEN528 OR NEW.heat IS DISTINCT FROM OLD.heat
197 PERFORM unsummarise_bug(OLD);529 OR NEW.latest_patch_uploaded IS DISTINCT FROM
198 PERFORM summarise_bug(NEW);530 OLD.latest_patch_uploaded) THEN
199 END IF;531 UPDATE bugtaskflat
200532 SET
201 ELSIF TG_OP = 'DELETE' THEN533 duplicateof = NEW.duplicateof,
202 PERFORM unsummarise_bug(OLD);534 bug_owner = NEW.owner,
203 END IF;535 fti = NEW.fti,
204536 information_type = NEW.information_type,
205 PERFORM bug_summary_flush_temp_journal();537 date_last_updated = NEW.date_last_updated,
206 RETURN NULL; -- Ignored - this is an AFTER trigger538 heat = NEW.heat,
539 latest_patch_uploaded = NEW.latest_patch_uploaded
540 WHERE bug = OLD.id;
541 END IF;
542
543 IF NEW.information_type IS DISTINCT FROM OLD.information_type THEN
544 PERFORM bug_flatten_access(OLD.id);
545 END IF;
546 RETURN NULL;
207END;547END;
208$$;548$$;
209549
210550
211COMMENT ON FUNCTION bug_maintain_bug_summary() IS 'AFTER trigger on bug maintaining the bugs summaries in bugsummary.';551CREATE FUNCTION public.valid_bug_name(text) RETURNS boolean
212
213
214CREATE FUNCTION valid_bug_name(text) RETURNS boolean
215 LANGUAGE plpythonu IMMUTABLE STRICT552 LANGUAGE plpythonu IMMUTABLE STRICT
216 AS $_$553 AS $_$
217 import re554 import re
@@ -223,7 +560,7 @@
223$_$;560$_$;
224561
225562
226COMMENT ON FUNCTION valid_bug_name(text) IS 'validate a bug name563COMMENT ON FUNCTION public.valid_bug_name(text) IS 'validate a bug name
227564
228 As per valid_name, except numeric-only names are not allowed (including565 As per valid_name, except numeric-only names are not allowed (including
229 names that look like floats).';566 names that look like floats).';
@@ -233,7 +570,7 @@
233570
234SET default_with_oids = false;571SET default_with_oids = false;
235572
236CREATE TABLE bug (573CREATE TABLE public.bug (
237 id integer NOT NULL,574 id integer NOT NULL,
238 datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,575 datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
239 name text,576 name text,
@@ -241,9 +578,7 @@
241 description text NOT NULL,578 description text NOT NULL,
242 owner integer NOT NULL,579 owner integer NOT NULL,
243 duplicateof integer,580 duplicateof integer,
244 fti ts2.tsvector,581 fti public.ts2_tsvector,
245 private boolean DEFAULT false NOT NULL,
246 security_related boolean DEFAULT false NOT NULL,
247 date_last_updated timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,582 date_last_updated timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
248 date_made_private timestamp without time zone,583 date_made_private timestamp without time zone,
249 who_made_private integer,584 who_made_private integer,
@@ -255,60 +590,57 @@
255 heat integer DEFAULT 0 NOT NULL,590 heat integer DEFAULT 0 NOT NULL,
256 heat_last_updated timestamp without time zone,591 heat_last_updated timestamp without time zone,
257 latest_patch_uploaded timestamp without time zone,592 latest_patch_uploaded timestamp without time zone,
258 access_policy integer,593 information_type integer NOT NULL,
259 CONSTRAINT notduplicateofself CHECK ((NOT (id = duplicateof))),594 CONSTRAINT notduplicateofself CHECK ((NOT (id = duplicateof))),
260 CONSTRAINT sane_description CHECK (((ltrim(description) <> ''::text) AND (char_length(description) <= 50000))),595 CONSTRAINT sane_description CHECK (((ltrim(description) <> ''::text) AND (char_length(description) <= 50000))),
261 CONSTRAINT valid_bug_name CHECK (valid_bug_name(name))596 CONSTRAINT valid_bug_name CHECK (public.valid_bug_name(name))
262);597);
263598
264599
265COMMENT 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.';600COMMENT 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.';
266601
267602
268COMMENT ON COLUMN bug.name IS 'A lowercase name uniquely identifying the bug';603COMMENT ON COLUMN public.bug.name IS 'A lowercase name uniquely identifying the bug';
269604
270605
271COMMENT 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.';606COMMENT 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.';
272607
273608
274COMMENT ON COLUMN bug.private IS 'Is this bug private? If so, only explicit subscribers will be able to see it';609COMMENT 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.';
275610
276611
277COMMENT ON COLUMN bug.security_related IS 'Is this bug a security issue?';612COMMENT 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.';
278613
279614
280COMMENT ON COLUMN bug.date_last_message IS 'When the last BugMessage was attached to this Bug. Maintained by a trigger on the BugMessage table.';615COMMENT 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.';
281616
282617
283COMMENT 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.';618COMMENT 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.';
284619
285620
286COMMENT 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.';621COMMENT ON COLUMN public.bug.heat IS 'The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values.';
287622
288623
289COMMENT 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.';624COMMENT 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.';
290625
291626
292COMMENT ON COLUMN bug.heat IS 'The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values.';627COMMENT 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';
293628
294629
295COMMENT 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.';630COMMENT 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.';
296631
297632
298COMMENT 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';633CREATE FUNCTION public.bug_row(bug_id integer) RETURNS public.bug
299
300
301CREATE FUNCTION bug_row(bug_id integer) RETURNS bug
302 LANGUAGE sql STABLE634 LANGUAGE sql STABLE
303 AS $_$635 AS $_$
304 SELECT * FROM Bug WHERE id=$1;636 SELECT * FROM Bug WHERE id=$1;
305$_$;637$_$;
306638
307639
308COMMENT 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))';640COMMENT 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))';
309641
310642
311CREATE TABLE bugsummary (643CREATE TABLE public.bugsummary (
312 id integer NOT NULL,644 id integer NOT NULL,
313 count integer DEFAULT 0 NOT NULL,645 count integer DEFAULT 0 NOT NULL,
314 product integer,646 product integer,
@@ -322,12 +654,28 @@
322 milestone integer,654 milestone integer,
323 importance integer NOT NULL,655 importance integer NOT NULL,
324 has_patch boolean NOT NULL,656 has_patch boolean NOT NULL,
325 fixed_upstream boolean NOT NULL,657 access_policy integer,
326 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)658 CONSTRAINT bugtask_assignment_checks CHECK (
659CASE
660 WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL))
661 WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL))
662 WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL)
663 WHEN (distroseries IS NOT NULL) THEN true
664 ELSE false
665END)
327);666);
328667
329668
330CREATE FUNCTION bug_summary_dec(bugsummary) RETURNS void669COMMENT 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). ';
670
671
672COMMENT 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';
673
674
675COMMENT 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)';
676
677
678CREATE FUNCTION public.bug_summary_dec(public.bugsummary) RETURNS void
331 LANGUAGE sql679 LANGUAGE sql
332 AS $_$680 AS $_$
333 -- We own the row reference, so in the absence of bugs this cannot681 -- We own the row reference, so in the absence of bugs this cannot
@@ -353,34 +701,47 @@
353 OR milestone = $1.milestone)701 OR milestone = $1.milestone)
354 AND importance = $1.importance702 AND importance = $1.importance
355 AND has_patch = $1.has_patch703 AND has_patch = $1.has_patch
356 AND fixed_upstream = $1.fixed_upstream;704 AND access_policy IS NOT DISTINCT FROM $1.access_policy;
357$_$;705$_$;
358706
359707
360COMMENT ON FUNCTION bug_summary_dec(bugsummary) IS 'UPSERT into bugsummary incrementing one row';708COMMENT ON FUNCTION public.bug_summary_dec(public.bugsummary) IS 'UPSERT into bugsummary incrementing one row';
361709
362710
363CREATE FUNCTION bug_summary_flush_temp_journal() RETURNS void711CREATE FUNCTION public.bug_summary_flush_temp_journal() RETURNS void
364 LANGUAGE plpgsql712 LANGUAGE plpgsql
365 AS $$713 AS $$
366DECLARE714DECLARE
367 d bugsummary%ROWTYPE;715 d bugsummary%ROWTYPE;
368BEGIN716BEGIN
369 -- may get called even though no summaries were made (for simplicity in the717 -- May get called even though no summaries were made (for simplicity in the
370 -- callers)718 -- callers). We sum the rows here to minimise the number of inserts
719 -- into the persistent journal, as it's reasonably likely that we'll
720 -- have -1s and +1s cancelling each other out.
371 PERFORM ensure_bugsummary_temp_journal();721 PERFORM ensure_bugsummary_temp_journal();
372 FOR d IN SELECT * FROM bugsummary_temp_journal LOOP722 INSERT INTO BugSummaryJournal(
373 PERFORM bugsummary_journal_ins(d);723 count, product, productseries, distribution,
374 END LOOP;724 distroseries, sourcepackagename, viewed_by, tag,
725 status, milestone, importance, has_patch, access_policy)
726 SELECT
727 SUM(count), product, productseries, distribution,
728 distroseries, sourcepackagename, viewed_by, tag,
729 status, milestone, importance, has_patch, access_policy
730 FROM bugsummary_temp_journal
731 GROUP BY
732 product, productseries, distribution,
733 distroseries, sourcepackagename, viewed_by, tag,
734 status, milestone, importance, has_patch, access_policy
735 HAVING SUM(count) != 0;
375 TRUNCATE bugsummary_temp_journal;736 TRUNCATE bugsummary_temp_journal;
376END;737END;
377$$;738$$;
378739
379740
380COMMENT ON FUNCTION bug_summary_flush_temp_journal() IS 'flush the temporary bugsummary journal into the bugsummary table';741COMMENT ON FUNCTION public.bug_summary_flush_temp_journal() IS 'flush the temporary bugsummary journal into the bugsummary table';
381742
382743
383CREATE FUNCTION bug_summary_inc(d bugsummary) RETURNS void744CREATE FUNCTION public.bug_summary_inc(d public.bugsummary) RETURNS void
384 LANGUAGE plpgsql745 LANGUAGE plpgsql
385 AS $_$746 AS $_$
386BEGIN747BEGIN
@@ -408,7 +769,7 @@
408 OR milestone = $1.milestone)769 OR milestone = $1.milestone)
409 AND importance = $1.importance770 AND importance = $1.importance
410 AND has_patch = $1.has_patch771 AND has_patch = $1.has_patch
411 AND fixed_upstream = $1.fixed_upstream;772 AND access_policy IS NOT DISTINCT FROM $1.access_policy;
412 IF found THEN773 IF found THEN
413 RETURN;774 RETURN;
414 END IF;775 END IF;
@@ -419,13 +780,12 @@
419 INSERT INTO BugSummary(780 INSERT INTO BugSummary(
420 count, product, productseries, distribution,781 count, product, productseries, distribution,
421 distroseries, sourcepackagename, viewed_by, tag,782 distroseries, sourcepackagename, viewed_by, tag,
422 status, milestone,783 status, milestone, importance, has_patch, access_policy)
423 importance, has_patch, fixed_upstream)
424 VALUES (784 VALUES (
425 d.count, d.product, d.productseries, d.distribution,785 d.count, d.product, d.productseries, d.distribution,
426 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,786 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
427 d.status, d.milestone,787 d.status, d.milestone, d.importance, d.has_patch,
428 d.importance, d.has_patch, d.fixed_upstream);788 d.access_policy);
429 RETURN;789 RETURN;
430 EXCEPTION WHEN unique_violation THEN790 EXCEPTION WHEN unique_violation THEN
431 -- do nothing, and loop to try the UPDATE again791 -- do nothing, and loop to try the UPDATE again
@@ -435,48 +795,12 @@
435$_$;795$_$;
436796
437797
438COMMENT ON FUNCTION bug_summary_inc(d bugsummary) IS 'UPSERT into bugsummary incrementing one row';798COMMENT ON FUNCTION public.bug_summary_inc(d public.bugsummary) IS 'UPSERT into bugsummary incrementing one row';
439799
440800
441CREATE FUNCTION bug_summary_temp_journal_ins(d bugsummary) RETURNS void801CREATE FUNCTION public.bug_update_latest_patch_uploaded(integer) RETURNS void
442 LANGUAGE plpgsql802 LANGUAGE plpgsql SECURITY DEFINER
443 AS $$803 SET search_path TO 'public'
444BEGIN
445 INSERT INTO BugSummary_Temp_Journal(
446 count, product, productseries, distribution,
447 distroseries, sourcepackagename, viewed_by, tag,
448 status, milestone, importance, has_patch, fixed_upstream)
449 VALUES (
450 d.count, d.product, d.productseries, d.distribution,
451 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
452 d.status, d.milestone, d.importance, d.has_patch, d.fixed_upstream);
453 RETURN;
454END;
455$$;
456
457
458COMMENT ON FUNCTION bug_summary_temp_journal_ins(d bugsummary) IS 'Insert a BugSummary into the temporary journal';
459
460
461CREATE FUNCTION bug_update_heat_copy_to_bugtask() RETURNS trigger
462 LANGUAGE plpgsql SECURITY DEFINER
463 SET search_path TO public
464 AS $$
465BEGIN
466 IF NEW.heat != OLD.heat THEN
467 UPDATE bugtask SET heat=NEW.heat WHERE bugtask.bug=NEW.id;
468 END IF;
469 RETURN NULL; -- Ignored - this is an AFTER trigger
470END;
471$$;
472
473
474COMMENT 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.';
475
476
477CREATE FUNCTION bug_update_latest_patch_uploaded(integer) RETURNS void
478 LANGUAGE plpgsql SECURITY DEFINER
479 SET search_path TO public
480 AS $_$804 AS $_$
481BEGIN805BEGIN
482 UPDATE bug SET latest_patch_uploaded =806 UPDATE bug SET latest_patch_uploaded =
@@ -490,9 +814,9 @@
490$_$;814$_$;
491815
492816
493CREATE FUNCTION bug_update_latest_patch_uploaded_on_delete() RETURNS trigger817CREATE FUNCTION public.bug_update_latest_patch_uploaded_on_delete() RETURNS trigger
494 LANGUAGE plpgsql SECURITY DEFINER818 LANGUAGE plpgsql SECURITY DEFINER
495 SET search_path TO public819 SET search_path TO 'public'
496 AS $$820 AS $$
497BEGIN821BEGIN
498 PERFORM bug_update_latest_patch_uploaded(OLD.bug);822 PERFORM bug_update_latest_patch_uploaded(OLD.bug);
@@ -501,9 +825,9 @@
501$$;825$$;
502826
503827
504CREATE FUNCTION bug_update_latest_patch_uploaded_on_insert_update() RETURNS trigger828CREATE FUNCTION public.bug_update_latest_patch_uploaded_on_insert_update() RETURNS trigger
505 LANGUAGE plpgsql SECURITY DEFINER829 LANGUAGE plpgsql SECURITY DEFINER
506 SET search_path TO public830 SET search_path TO 'public'
507 AS $$831 AS $$
508BEGIN832BEGIN
509 PERFORM bug_update_latest_patch_uploaded(NEW.bug);833 PERFORM bug_update_latest_patch_uploaded(NEW.bug);
@@ -512,9 +836,9 @@
512$$;836$$;
513837
514838
515CREATE FUNCTION bugmessage_copy_owner_from_message() RETURNS trigger839CREATE FUNCTION public.bugmessage_copy_owner_from_message() RETURNS trigger
516 LANGUAGE plpgsql SECURITY DEFINER840 LANGUAGE plpgsql SECURITY DEFINER
517 SET search_path TO public841 SET search_path TO 'public'
518 AS $$842 AS $$
519BEGIN843BEGIN
520 IF TG_OP = 'INSERT' THEN844 IF TG_OP = 'INSERT' THEN
@@ -537,145 +861,98 @@
537$$;861$$;
538862
539863
540COMMENT ON FUNCTION bugmessage_copy_owner_from_message() IS 'Copies the message owner into bugmessage when bugmessage changes.';864COMMENT ON FUNCTION public.bugmessage_copy_owner_from_message() IS 'Copies the message owner into bugmessage when bugmessage changes.';
541865
542866
543CREATE FUNCTION bugsubscription_maintain_bug_summary() RETURNS trigger867CREATE FUNCTION public.bugsummary_journal_bug(bug_row public.bug, _count integer) RETURNS void
544 LANGUAGE plpgsql SECURITY DEFINER868 LANGUAGE plpgsql
545 SET search_path TO public869 AS $$
546 AS $$870DECLARE
547BEGIN871 btf_row bugtaskflat%ROWTYPE;
548 -- This trigger only works if we are inserting, updating or deleting872BEGIN
549 -- a single row per statement.873 FOR btf_row IN SELECT * FROM bugtaskflat WHERE bug = bug_row.id
550 IF TG_OP = 'INSERT' THEN874 LOOP
551 IF NOT (bug_row(NEW.bug)).private THEN875 PERFORM bugsummary_journal_bugtaskflat(btf_row, _count);
552 -- Public subscriptions are not aggregated.876 END LOOP;
553 RETURN NEW;877END;
554 END IF;878$$;
555 IF TG_WHEN = 'BEFORE' THEN879
556 PERFORM unsummarise_bug(bug_row(NEW.bug));880
557 ELSE881CREATE TABLE public.bugtaskflat (
558 PERFORM summarise_bug(bug_row(NEW.bug));882 bugtask integer NOT NULL,
559 END IF;883 bug integer NOT NULL,
560 PERFORM bug_summary_flush_temp_journal();884 datecreated timestamp without time zone,
561 RETURN NEW;885 duplicateof integer,
562 ELSIF TG_OP = 'DELETE' THEN886 bug_owner integer NOT NULL,
563 IF NOT (bug_row(OLD.bug)).private THEN887 fti public.ts2_tsvector,
564 -- Public subscriptions are not aggregated.888 information_type integer NOT NULL,
565 RETURN OLD;889 date_last_updated timestamp without time zone NOT NULL,
566 END IF;890 heat integer NOT NULL,
567 IF TG_WHEN = 'BEFORE' THEN891 product integer,
568 PERFORM unsummarise_bug(bug_row(OLD.bug));892 productseries integer,
569 ELSE893 distribution integer,
570 PERFORM summarise_bug(bug_row(OLD.bug));894 distroseries integer,
571 END IF;895 sourcepackagename integer,
572 PERFORM bug_summary_flush_temp_journal();896 status integer NOT NULL,
573 RETURN OLD;897 importance integer NOT NULL,
574 ELSE898 assignee integer,
575 IF (OLD.person IS DISTINCT FROM NEW.person899 milestone integer,
576 OR OLD.bug IS DISTINCT FROM NEW.bug) THEN900 owner integer NOT NULL,
577 IF TG_WHEN = 'BEFORE' THEN901 active boolean NOT NULL,
578 IF (bug_row(OLD.bug)).private THEN902 access_policies integer[],
579 -- Public subscriptions are not aggregated.903 access_grants integer[],
580 PERFORM unsummarise_bug(bug_row(OLD.bug));904 latest_patch_uploaded timestamp without time zone,
581 END IF;905 date_closed timestamp without time zone
582 IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN906);
583 -- Public subscriptions are not aggregated.907
584 PERFORM unsummarise_bug(bug_row(NEW.bug));908
585 END IF;909CREATE FUNCTION public.bugsummary_journal_bugtaskflat(btf_row public.bugtaskflat, _count integer) RETURNS void
586 ELSE910 LANGUAGE plpgsql
587 IF (bug_row(OLD.bug)).private THEN911 AS $$
588 -- Public subscriptions are not aggregated.912BEGIN
589 PERFORM summarise_bug(bug_row(OLD.bug));913 PERFORM ensure_bugsummary_temp_journal();
590 END IF;914 INSERT INTO BugSummary_Temp_Journal(
591 IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN915 count, product, productseries, distribution,
592 -- Public subscriptions are not aggregated.916 distroseries, sourcepackagename, viewed_by, tag,
593 PERFORM summarise_bug(bug_row(NEW.bug));917 status, milestone, importance, has_patch, access_policy)
594 END IF;918 SELECT
595 END IF;919 _count, product, productseries, distribution,
596 END IF;920 distroseries, sourcepackagename, viewed_by, tag,
597 PERFORM bug_summary_flush_temp_journal();921 status, milestone, importance, has_patch, access_policy
598 RETURN NEW;922 FROM bugsummary_locations(btf_row);
599 END IF;923END;
600END;924$$;
601$$;925
602926
603927CREATE FUNCTION public.bugsummary_locations(btf_row public.bugtaskflat) RETURNS SETOF public.bugsummary
604COMMENT ON FUNCTION bugsubscription_maintain_bug_summary() IS 'AFTER trigger on bugsubscription maintaining the bugs summaries in bugsummary.';928 LANGUAGE plpgsql
605929 AS $$
606930BEGIN
607CREATE FUNCTION bugsummary_journal_ins(d bugsummary) RETURNS void931 IF btf_row.duplicateof IS NOT NULL THEN
608 LANGUAGE plpgsql
609 AS $$
610BEGIN
611 IF d.count <> 0 THEN
612 INSERT INTO BugSummaryJournal (
613 count, product, productseries, distribution,
614 distroseries, sourcepackagename, viewed_by, tag,
615 status, milestone,
616 importance, has_patch, fixed_upstream)
617 VALUES (
618 d.count, d.product, d.productseries, d.distribution,
619 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
620 d.status, d.milestone,
621 d.importance, d.has_patch, d.fixed_upstream);
622 END IF;
623END;
624$$;
625
626
627COMMENT ON FUNCTION bugsummary_journal_ins(d bugsummary) IS 'Add an entry into BugSummaryJournal';
628
629
630CREATE FUNCTION bugsummary_locations(bug_row bug) RETURNS SETOF bugsummary
631 LANGUAGE plpgsql
632 AS $$
633BEGIN
634 IF BUG_ROW.duplicateof IS NOT NULL THEN
635 RETURN;932 RETURN;
636 END IF;933 END IF;
637 RETURN QUERY934 RETURN QUERY
638 SELECT935 SELECT
639 CAST(NULL AS integer) AS id,936 CAST(NULL AS integer) AS id,
640 CAST(1 AS integer) AS count,937 CAST(1 AS integer) AS count,
641 product, productseries, distribution, distroseries,938 bug_targets.product, bug_targets.productseries,
642 sourcepackagename, person AS viewed_by, tag, status, milestone,939 bug_targets.distribution, bug_targets.distroseries,
643 importance,940 bug_targets.sourcepackagename,
644 BUG_ROW.latest_patch_uploaded IS NOT NULL AS has_patch,941 bug_viewers.viewed_by, bug_tags.tag, btf_row.status,
645 (EXISTS (942 btf_row.milestone, btf_row.importance,
646 SELECT TRUE FROM BugTask AS RBT943 btf_row.latest_patch_uploaded IS NOT NULL AS has_patch,
647 WHERE944 bug_viewers.access_policy
648 RBT.bug = tasks.bug945 FROM
649 -- This would just be 'RBT.id <> tasks.id', except946 bugsummary_targets(btf_row) as bug_targets,
650 -- that the records from tasks are summaries and not947 bugsummary_tags(btf_row) AS bug_tags,
651 -- real bugtasks, and do not have an id.948 bugsummary_viewers(btf_row) AS bug_viewers;
652 AND (RBT.product IS DISTINCT FROM tasks.product
653 OR RBT.productseries
654 IS DISTINCT FROM tasks.productseries
655 OR RBT.distribution IS DISTINCT FROM tasks.distribution
656 OR RBT.distroseries IS DISTINCT FROM tasks.distroseries
657 OR RBT.sourcepackagename
658 IS DISTINCT FROM tasks.sourcepackagename)
659 -- Flagged as INVALID, FIXCOMMITTED or FIXRELEASED
660 -- via a bugwatch, or FIXCOMMITTED or FIXRELEASED on
661 -- the product.
662 AND ((bugwatch IS NOT NULL AND status IN (17, 25, 30))
663 OR (bugwatch IS NULL AND product IS NOT NULL
664 AND status IN (25, 30))))
665 )::boolean AS fixed_upstream
666 FROM bugsummary_tasks(BUG_ROW) AS tasks
667 JOIN bugsummary_tags(BUG_ROW) AS bug_tags ON TRUE
668 LEFT OUTER JOIN bugsummary_viewers(BUG_ROW) AS bug_viewers ON TRUE;
669END;949END;
670$$;950$$;
671951
672952
673COMMENT ON FUNCTION bugsummary_locations(bug_row bug) IS 'Calculate what BugSummary rows should exist for a given Bug.';953CREATE FUNCTION public.bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer) RETURNS void
674
675
676CREATE FUNCTION bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer) RETURNS void
677 LANGUAGE plpgsql SECURITY DEFINER954 LANGUAGE plpgsql SECURITY DEFINER
678 SET search_path TO public955 SET search_path TO 'public'
679 AS $$956 AS $$
680DECLARE957DECLARE
681 d bugsummary%ROWTYPE;958 d bugsummary%ROWTYPE;
@@ -710,13 +987,13 @@
710 milestone,987 milestone,
711 importance,988 importance,
712 has_patch,989 has_patch,
713 fixed_upstream990 access_policy
714 FROM BugSummaryJournal991 FROM BugSummaryJournal
715 WHERE id <= max_id992 WHERE id <= max_id
716 GROUP BY993 GROUP BY
717 product, productseries, distribution, distroseries,994 product, productseries, distribution, distroseries,
718 sourcepackagename, viewed_by, tag, status, milestone,995 sourcepackagename, viewed_by, tag, status, milestone,
719 importance, has_patch, fixed_upstream996 importance, has_patch, access_policy
720 HAVING sum(count) <> 0997 HAVING sum(count) <> 0
721 LOOP998 LOOP
722 IF d.count < 0 THEN999 IF d.count < 0 THEN
@@ -734,10 +1011,10 @@
734$$;1011$$;
7351012
7361013
737COMMENT ON FUNCTION bugsummary_rollup_journal(batchsize integer) IS 'Collate and migrate rows from BugSummaryJournal to BugSummary';1014COMMENT ON FUNCTION public.bugsummary_rollup_journal(batchsize integer) IS 'Collate and migrate rows from BugSummaryJournal to BugSummary';
7381015
7391016
740CREATE FUNCTION valid_name(text) RETURNS boolean1017CREATE FUNCTION public.valid_name(text) RETURNS boolean
741 LANGUAGE plpythonu IMMUTABLE STRICT1018 LANGUAGE plpythonu IMMUTABLE STRICT
742 AS $$1019 AS $$
743 import re1020 import re
@@ -749,7 +1026,7 @@
749$$;1026$$;
7501027
7511028
752COMMENT ON FUNCTION valid_name(text) IS 'validate a name.1029COMMENT ON FUNCTION public.valid_name(text) IS 'validate a name.
7531030
754 Names must contain only lowercase letters, numbers, ., & -. They1031 Names must contain only lowercase letters, numbers, ., & -. They
755 must start with an alphanumeric. They are ASCII only. Names are useful1032 must start with an alphanumeric. They are ASCII only. Names are useful
@@ -760,446 +1037,347 @@
760 namespace conflict if URL traversal is possible by name as well as id.';1037 namespace conflict if URL traversal is possible by name as well as id.';
7611038
7621039
763CREATE TABLE bugtag (1040CREATE TABLE public.bugtag (
764 id integer NOT NULL,1041 id integer NOT NULL,
765 bug integer NOT NULL,1042 bug integer NOT NULL,
766 tag text NOT NULL,1043 tag text NOT NULL,
767 CONSTRAINT valid_tag CHECK (valid_name(tag))1044 CONSTRAINT valid_tag CHECK (public.valid_name(tag))
768);1045);
7691046
7701047
771COMMENT ON TABLE bugtag IS 'Attaches simple text tags to a bug.';1048COMMENT ON TABLE public.bugtag IS 'Attaches simple text tags to a bug.';
7721049
7731050
774COMMENT ON COLUMN bugtag.bug IS 'The bug the tags is attached to.';1051COMMENT ON COLUMN public.bugtag.bug IS 'The bug the tags is attached to.';
7751052
7761053
777COMMENT ON COLUMN bugtag.tag IS 'The text representation of the tag.';1054COMMENT ON COLUMN public.bugtag.tag IS 'The text representation of the tag.';
7781055
7791056
780CREATE FUNCTION bugsummary_tags(bug_row bug) RETURNS SETOF bugtag1057CREATE FUNCTION public.bugsummary_tags(btf_row public.bugtaskflat) RETURNS SETOF public.bugtag
781 LANGUAGE sql STABLE1058 LANGUAGE sql STABLE
782 AS $_$1059 AS $_$
783 SELECT * FROM BugTag WHERE BugTag.bug = $1.id1060 SELECT * FROM BugTag WHERE BugTag.bug = $1.bug
784 UNION ALL1061 UNION ALL
785 SELECT NULL::integer, $1.id, NULL::text;1062 SELECT NULL::integer, $1.bug, NULL::text;
786$_$;1063$_$;
7871064
7881065
789COMMENT ON FUNCTION bugsummary_tags(bug_row bug) IS 'Return (bug, tag) for all tags + (bug, NULL::text)';1066CREATE FUNCTION public.bugsummary_targets(btf_row public.bugtaskflat) RETURNS TABLE(product integer, productseries integer, distribution integer, distroseries integer, sourcepackagename integer)
7901067 LANGUAGE sql IMMUTABLE
7911068 AS $_$
792CREATE TABLE bugtask (1069 -- Include a sourcepackagename-free task if this one has a
793 id integer NOT NULL,1070 -- sourcepackagename, so package tasks are also counted in their
794 bug integer NOT NULL,1071 -- distro/series.
795 product integer,1072 SELECT
796 distribution integer,1073 $1.product, $1.productseries, $1.distribution,
797 distroseries integer,1074 $1.distroseries, $1.sourcepackagename
798 sourcepackagename integer,1075 UNION -- Implicit DISTINCT
799 binarypackagename integer,1076 SELECT
800 status integer NOT NULL,1077 $1.product, $1.productseries, $1.distribution,
801 importance integer DEFAULT 5 NOT NULL,1078 $1.distroseries, NULL;
802 assignee integer,1079$_$;
803 date_assigned timestamp without time zone,1080
804 datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone),1081
805 owner integer NOT NULL,1082CREATE FUNCTION public.bugsummary_viewers(btf_row public.bugtaskflat) RETURNS TABLE(viewed_by integer, access_policy integer)
806 milestone integer,1083 LANGUAGE sql IMMUTABLE
807 bugwatch integer,1084 AS $_$
808 fti ts2.tsvector,1085 SELECT NULL::integer, NULL::integer WHERE $1.information_type IN (1, 2)
809 targetnamecache text,1086 UNION ALL
810 date_confirmed timestamp without time zone,1087 SELECT unnest($1.access_grants), NULL::integer
811 date_inprogress timestamp without time zone,1088 WHERE $1.information_type NOT IN (1, 2)
812 date_closed timestamp without time zone,1089 UNION ALL
813 productseries integer,1090 SELECT NULL::integer, unnest($1.access_policies)
814 date_incomplete timestamp without time zone,1091 WHERE $1.information_type NOT IN (1, 2);
815 date_left_new timestamp without time zone,1092$_$;
816 date_triaged timestamp without time zone,1093
817 date_fix_committed timestamp without time zone,1094
818 date_fix_released timestamp without time zone,1095CREATE FUNCTION public.bugtag_maintain_bug_summary() RETURNS trigger
819 date_left_closed timestamp without time zone,1096 LANGUAGE plpgsql SECURITY DEFINER
820 heat_rank integer DEFAULT 0 NOT NULL,1097 SET search_path TO 'public'
821 date_milestone_set timestamp without time zone,1098 AS $$
822 heat integer DEFAULT 0 NOT NULL,1099BEGIN
823 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)1100 IF TG_OP = 'INSERT' THEN
824);1101 IF TG_WHEN = 'BEFORE' THEN
8251102 PERFORM unsummarise_bug(NEW.bug);
8261103 ELSE
827COMMENT ON TABLE bugtask IS 'Links a given Bug to a particular (sourcepackagename, distro) or product.';1104 PERFORM summarise_bug(NEW.bug);
8281105 END IF;
8291106 PERFORM bug_summary_flush_temp_journal();
830COMMENT ON COLUMN bugtask.bug IS 'The bug that is assigned to this (sourcepackagename, distro) or product.';1107 RETURN NEW;
8311108 ELSIF TG_OP = 'DELETE' THEN
8321109 IF TG_WHEN = 'BEFORE' THEN
833COMMENT ON COLUMN bugtask.product IS 'The product in which this bug shows up.';1110 PERFORM unsummarise_bug(OLD.bug);
8341111 ELSE
8351112 PERFORM summarise_bug(OLD.bug);
836COMMENT ON COLUMN bugtask.distribution IS 'The distro of the named sourcepackage.';1113 END IF;
8371114 PERFORM bug_summary_flush_temp_journal();
8381115 RETURN OLD;
839COMMENT ON COLUMN bugtask.sourcepackagename IS 'The name of the sourcepackage in which this bug shows up.';1116 ELSE
8401117 IF TG_WHEN = 'BEFORE' THEN
8411118 PERFORM unsummarise_bug(OLD.bug);
842COMMENT 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)';1119 IF OLD.bug <> NEW.bug THEN
8431120 PERFORM unsummarise_bug(NEW.bug);
8441121 END IF;
845COMMENT ON COLUMN bugtask.status IS 'The general health of the bug, e.g. Accepted, Rejected, etc.';1122 ELSE
8461123 PERFORM summarise_bug(OLD.bug);
8471124 IF OLD.bug <> NEW.bug THEN
848COMMENT ON COLUMN bugtask.importance IS 'The importance of fixing the bug.';1125 PERFORM summarise_bug(NEW.bug);
8491126 END IF;
8501127 END IF;
851COMMENT ON COLUMN bugtask.assignee IS 'The person who has been assigned to fix this bug in this product or (sourcepackagename, distro)';1128 PERFORM bug_summary_flush_temp_journal();
8521129 RETURN NEW;
8531130 END IF;
854COMMENT ON COLUMN bugtask.date_assigned IS 'The date on which the bug in this (sourcepackagename, distro) or product was assigned to someone to fix';1131END;
8551132$$;
8561133
857COMMENT 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.';1134
8581135COMMENT ON FUNCTION public.bugtag_maintain_bug_summary() IS 'AFTER trigger on bugtag maintaining the bugs summaries in bugsummary.';
8591136
860COMMENT 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';1137
8611138CREATE FUNCTION public.bugtask_flatten(task_id integer, check_only boolean) RETURNS boolean
8621139 LANGUAGE plpgsql SECURITY DEFINER
863COMMENT ON COLUMN bugtask.bugwatch IS 'This column allows us to link a bug1140 SET search_path TO 'public'
864task to a bug watch. In other words, we are connecting the state of the task1141 AS $$
865to the state of the bug in a different bug tracking system. To the best of1142DECLARE
866our ability we''ll try and keep the bug task syncronised with the state of1143 bug_row Bug%ROWTYPE;
867the remote bug watch.';1144 task_row BugTask%ROWTYPE;
8681145 old_flat_row BugTaskFlat%ROWTYPE;
8691146 new_flat_row BugTaskFlat%ROWTYPE;
870COMMENT 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.';1147 _product_active boolean;
8711148 _access_policies integer[];
8721149 _access_grants integer[];
873COMMENT 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.';1150BEGIN
8741151 -- This is the master function to update BugTaskFlat, but there are
8751152 -- maintenance triggers and jobs on the involved tables that update
876COMMENT 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';1153 -- it directly. Any changes here probably require a corresponding
8771154 -- change in other trigger functions.
8781155
879COMMENT 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';1156 SELECT * INTO task_row FROM BugTask WHERE id = task_id;
8801157 SELECT * INTO old_flat_row FROM BugTaskFlat WHERE bugtask = task_id;
8811158
882COMMENT ON COLUMN bugtask.productseries IS 'The product series to which the bug is targeted';1159 -- If the task doesn't exist, ensure that there's no flat row.
8831160 IF task_row.id IS NULL THEN
8841161 IF old_flat_row.bugtask IS NOT NULL THEN
885COMMENT ON COLUMN bugtask.date_left_new IS 'The date when this bug first transitioned out of the NEW status.';1162 IF NOT check_only THEN
8861163 DELETE FROM BugTaskFlat WHERE bugtask = task_id;
8871164 END IF;
888COMMENT ON COLUMN bugtask.date_triaged IS 'The date when this bug transitioned to a status >= TRIAGED.';1165 RETURN FALSE;
8891166 ELSE
8901167 RETURN TRUE;
891COMMENT ON COLUMN bugtask.date_fix_committed IS 'The date when this bug transitioned to a status >= FIXCOMMITTED.';1168 END IF;
8921169 END IF;
8931170
894COMMENT ON COLUMN bugtask.date_fix_released IS 'The date when this bug transitioned to a FIXRELEASED status.';1171 SELECT * FROM bug INTO bug_row WHERE id = task_row.bug;
8951172
8961173 -- If it's a product(series) task, we must consult the active flag.
897COMMENT ON COLUMN bugtask.date_left_closed IS 'The date when this bug last transitioned out of a CLOSED status.';1174 IF task_row.product IS NOT NULL THEN
8981175 SELECT product.active INTO _product_active
8991176 FROM product WHERE product.id = task_row.product LIMIT 1;
900COMMENT ON COLUMN bugtask.heat_rank IS 'The heat bin in which this bugtask appears, as a value from the BugTaskHeatRank enumeration.';1177 ELSIF task_row.productseries IS NOT NULL THEN
9011178 SELECT product.active INTO _product_active
9021179 FROM
903COMMENT ON COLUMN bugtask.date_milestone_set IS 'The date when this bug was targed to the milestone that is currently set.';1180 product
9041181 JOIN productseries ON productseries.product = product.id
9051182 WHERE productseries.id = task_row.productseries LIMIT 1;
906CREATE FUNCTION bugsummary_tasks(bug_row bug) RETURNS SETOF bugtask1183 END IF;
907 LANGUAGE plpgsql STABLE1184
908 AS $$1185 SELECT policies, grants
909DECLARE1186 INTO _access_policies, _access_grants
910 bt bugtask%ROWTYPE;1187 FROM bug_build_access_cache(bug_row.id, bug_row.information_type)
911 r record;1188 AS (policies integer[], grants integer[]);
912BEGIN1189
913 bt.bug = BUG_ROW.id;1190 -- Compile the new flat row.
9141191 SELECT task_row.id, bug_row.id, task_row.datecreated,
915 -- One row only for each target permutation - need to ignore other fields1192 bug_row.duplicateof, bug_row.owner, bug_row.fti,
916 -- like date last modified to deal with conjoined masters and multiple1193 bug_row.information_type, bug_row.date_last_updated,
917 -- sourcepackage tasks in a distro.1194 bug_row.heat, task_row.product, task_row.productseries,
918 FOR r IN1195 task_row.distribution, task_row.distroseries,
919 SELECT1196 task_row.sourcepackagename, task_row.status,
920 product, productseries, distribution, distroseries,1197 task_row.importance, task_row.assignee,
921 sourcepackagename, status, milestone, importance, bugwatch1198 task_row.milestone, task_row.owner,
922 FROM BugTask WHERE bug=BUG_ROW.id1199 COALESCE(_product_active, TRUE),
923 UNION -- Implicit DISTINCT1200 _access_policies,
924 SELECT1201 _access_grants,
925 product, productseries, distribution, distroseries,1202 bug_row.latest_patch_uploaded, task_row.date_closed
926 NULL, status, milestone, importance, bugwatch1203 INTO new_flat_row;
927 FROM BugTask WHERE bug=BUG_ROW.id AND sourcepackagename IS NOT NULL1204
928 LOOP1205 -- Calculate the necessary updates.
929 bt.product = r.product;1206 IF old_flat_row.bugtask IS NULL THEN
930 bt.productseries = r.productseries;1207 IF NOT check_only THEN
931 bt.distribution = r.distribution;1208 INSERT INTO BugTaskFlat VALUES (new_flat_row.*);
932 bt.distroseries = r.distroseries;1209 END IF;
933 bt.sourcepackagename = r.sourcepackagename;1210 RETURN FALSE;
934 bt.status = r.status;1211 ELSIF new_flat_row != old_flat_row THEN
935 bt.milestone = r.milestone;1212 IF NOT check_only THEN
936 bt.importance = r.importance;1213 UPDATE BugTaskFlat SET
937 bt.bugwatch = r.bugwatch;1214 bug = new_flat_row.bug,
938 RETURN NEXT bt;1215 datecreated = new_flat_row.datecreated,
939 END LOOP;1216 duplicateof = new_flat_row.duplicateof,
940END;1217 bug_owner = new_flat_row.bug_owner,
941$$;1218 fti = new_flat_row.fti,
9421219 information_type = new_flat_row.information_type,
9431220 date_last_updated = new_flat_row.date_last_updated,
944COMMENT ON FUNCTION bugsummary_tasks(bug_row bug) IS 'Return all tasks for the bug + all sourcepackagename tasks again with the sourcepackagename squashed';1221 heat = new_flat_row.heat,
9451222 product = new_flat_row.product,
9461223 productseries = new_flat_row.productseries,
947CREATE TABLE bugsubscription (1224 distribution = new_flat_row.distribution,
948 id integer NOT NULL,1225 distroseries = new_flat_row.distroseries,
949 person integer NOT NULL,1226 sourcepackagename = new_flat_row.sourcepackagename,
950 bug integer NOT NULL,1227 status = new_flat_row.status,
951 date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,1228 importance = new_flat_row.importance,
952 subscribed_by integer NOT NULL,1229 assignee = new_flat_row.assignee,
953 bug_notification_level integer DEFAULT 40 NOT NULL1230 milestone = new_flat_row.milestone,
954);1231 owner = new_flat_row.owner,
9551232 active = new_flat_row.active,
9561233 access_policies = new_flat_row.access_policies,
957COMMENT ON TABLE bugsubscription IS 'A subscription by a Person to a bug.';1234 access_grants = new_flat_row.access_grants,
9581235 date_closed = new_flat_row.date_closed,
9591236 latest_patch_uploaded = new_flat_row.latest_patch_uploaded
960COMMENT ON COLUMN bugsubscription.bug_notification_level IS 'The level of notifications which the Person will receive from this subscription.';1237 WHERE bugtask = new_flat_row.bugtask;
9611238 END IF;
9621239 RETURN FALSE;
963CREATE FUNCTION bugsummary_viewers(bug_row bug) RETURNS SETOF bugsubscription1240 ELSE
964 LANGUAGE sql STABLE1241 RETURN TRUE;
965 AS $_$1242 END IF;
966 SELECT *1243END;
967 FROM BugSubscription1244$$;
968 WHERE1245
969 bugsubscription.bug=$1.id1246
970 AND $1.private IS TRUE;1247COMMENT 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.';
971$_$;1248
9721249
9731250CREATE FUNCTION public.bugtask_maintain_bugtaskflat_trig() RETURNS trigger
974COMMENT ON FUNCTION bugsummary_viewers(bug_row bug) IS 'Return (bug, viewer) for all viewers if private, nothing otherwise';1251 LANGUAGE plpgsql SECURITY DEFINER
9751252 SET search_path TO 'public'
9761253 AS $$
977CREATE FUNCTION bugtag_maintain_bug_summary() RETURNS trigger1254BEGIN
978 LANGUAGE plpgsql SECURITY DEFINER1255 IF TG_OP = 'INSERT' THEN
979 SET search_path TO public1256 PERFORM bugtask_flatten(NEW.id, FALSE);
980 AS $$1257 ELSIF TG_OP = 'UPDATE' THEN
981BEGIN1258 IF NEW.bug != OLD.bug THEN
982 IF TG_OP = 'INSERT' THEN1259 RAISE EXCEPTION 'cannot move bugtask to a different bug';
983 IF TG_WHEN = 'BEFORE' THEN1260 ELSIF (NEW.product IS DISTINCT FROM OLD.product
984 PERFORM unsummarise_bug(bug_row(NEW.bug));1261 OR NEW.productseries IS DISTINCT FROM OLD.productseries) THEN
985 ELSE1262 -- product.active may differ. Do a full update.
986 PERFORM summarise_bug(bug_row(NEW.bug));1263 PERFORM bugtask_flatten(NEW.id, FALSE);
987 END IF;1264 ELSIF (
988 PERFORM bug_summary_flush_temp_journal();1265 NEW.datecreated IS DISTINCT FROM OLD.datecreated
989 RETURN NEW;1266 OR NEW.product IS DISTINCT FROM OLD.product
990 ELSIF TG_OP = 'DELETE' THEN1267 OR NEW.productseries IS DISTINCT FROM OLD.productseries
991 IF TG_WHEN = 'BEFORE' THEN1268 OR NEW.distribution IS DISTINCT FROM OLD.distribution
992 PERFORM unsummarise_bug(bug_row(OLD.bug));1269 OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
993 ELSE1270 OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
994 PERFORM summarise_bug(bug_row(OLD.bug));1271 OR NEW.status IS DISTINCT FROM OLD.status
995 END IF;1272 OR NEW.importance IS DISTINCT FROM OLD.importance
996 PERFORM bug_summary_flush_temp_journal();1273 OR NEW.assignee IS DISTINCT FROM OLD.assignee
997 RETURN OLD;1274 OR NEW.milestone IS DISTINCT FROM OLD.milestone
998 ELSE1275 OR NEW.owner IS DISTINCT FROM OLD.owner
999 IF TG_WHEN = 'BEFORE' THEN1276 OR NEW.date_closed IS DISTINCT FROM OLD.date_closed) THEN
1000 PERFORM unsummarise_bug(bug_row(OLD.bug));1277 -- Otherwise just update the columns from bugtask.
1001 IF OLD.bug <> NEW.bug THEN1278 -- Access policies and grants may have changed due to target
1002 PERFORM unsummarise_bug(bug_row(NEW.bug));1279 -- transitions, but an earlier trigger will already have
1003 END IF;1280 -- mirrored them to all relevant flat tasks.
1004 ELSE1281 UPDATE BugTaskFlat SET
1005 PERFORM summarise_bug(bug_row(OLD.bug));1282 datecreated = NEW.datecreated,
1006 IF OLD.bug <> NEW.bug THEN1283 product = NEW.product,
1007 PERFORM summarise_bug(bug_row(NEW.bug));1284 productseries = NEW.productseries,
1008 END IF;1285 distribution = NEW.distribution,
1009 END IF;1286 distroseries = NEW.distroseries,
1010 PERFORM bug_summary_flush_temp_journal();1287 sourcepackagename = NEW.sourcepackagename,
1011 RETURN NEW;1288 status = NEW.status,
1012 END IF;1289 importance = NEW.importance,
1013END;1290 assignee = NEW.assignee,
1014$$;1291 milestone = NEW.milestone,
10151292 owner = NEW.owner,
10161293 date_closed = NEW.date_closed
1017COMMENT ON FUNCTION bugtag_maintain_bug_summary() IS 'AFTER trigger on bugtag maintaining the bugs summaries in bugsummary.';1294 WHERE bugtask = NEW.id;
10181295 END IF;
10191296 ELSIF TG_OP = 'DELETE' THEN
1020CREATE FUNCTION bugtask_maintain_bug_summary() RETURNS trigger1297 PERFORM bugtask_flatten(OLD.id, FALSE);
1021 LANGUAGE plpgsql SECURITY DEFINER1298 END IF;
1022 SET search_path TO public1299 RETURN NULL;
1023 AS $$1300END;
1024BEGIN1301$$;
1025 -- This trigger only works if we are inserting, updating or deleting1302
1026 -- a single row per statement.1303
10271304CREATE FUNCTION public.bugtaskflat_maintain_bug_summary() RETURNS trigger
1028 -- Unlike bug_maintain_bug_summary, this trigger does not have access1305 LANGUAGE plpgsql SECURITY DEFINER
1029 -- to the old bug when invoked as an AFTER trigger. To work around this1306 SET search_path TO 'public'
1030 -- we install this trigger as both a BEFORE and an AFTER trigger.1307 AS $$
1031 IF TG_OP = 'INSERT' THEN1308BEGIN
1032 IF TG_WHEN = 'BEFORE' THEN1309 IF TG_OP = 'INSERT' THEN
1033 PERFORM unsummarise_bug(bug_row(NEW.bug));1310 PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
1034 ELSE1311 PERFORM bug_summary_flush_temp_journal();
1035 PERFORM summarise_bug(bug_row(NEW.bug));1312 ELSIF TG_OP = 'DELETE' THEN
1036 END IF;1313 PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
1037 PERFORM bug_summary_flush_temp_journal();1314 PERFORM bug_summary_flush_temp_journal();
1038 RETURN NEW;1315 ELSIF
10391316 NEW.product IS DISTINCT FROM OLD.product
1040 ELSIF TG_OP = 'DELETE' THEN1317 OR NEW.productseries IS DISTINCT FROM OLD.productseries
1041 IF TG_WHEN = 'BEFORE' THEN1318 OR NEW.distribution IS DISTINCT FROM OLD.distribution
1042 PERFORM unsummarise_bug(bug_row(OLD.bug));1319 OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
1043 ELSE1320 OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
1044 PERFORM summarise_bug(bug_row(OLD.bug));1321 OR NEW.status IS DISTINCT FROM OLD.status
1045 END IF;1322 OR NEW.milestone IS DISTINCT FROM OLD.milestone
1046 PERFORM bug_summary_flush_temp_journal();1323 OR NEW.importance IS DISTINCT FROM OLD.importance
1047 RETURN OLD;1324 OR NEW.latest_patch_uploaded IS DISTINCT FROM OLD.latest_patch_uploaded
10481325 OR NEW.information_type IS DISTINCT FROM OLD.information_type
1049 ELSE1326 OR NEW.access_grants IS DISTINCT FROM OLD.access_grants
1050 IF (OLD.product IS DISTINCT FROM NEW.product1327 OR NEW.access_policies IS DISTINCT FROM OLD.access_policies
1051 OR OLD.productseries IS DISTINCT FROM NEW.productseries1328 OR NEW.duplicateof IS DISTINCT FROM OLD.duplicateof
1052 OR OLD.distribution IS DISTINCT FROM NEW.distribution1329 THEN
1053 OR OLD.distroseries IS DISTINCT FROM NEW.distroseries1330 PERFORM bugsummary_journal_bugtaskflat(OLD, -1);
1054 OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename1331 PERFORM bugsummary_journal_bugtaskflat(NEW, 1);
1055 OR OLD.status IS DISTINCT FROM NEW.status1332 PERFORM bug_summary_flush_temp_journal();
1056 OR OLD.importance IS DISTINCT FROM NEW.importance1333 END IF;
1057 OR OLD.bugwatch IS DISTINCT FROM NEW.bugwatch1334 RETURN NULL;
1058 OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN1335END;
10591336$$;
1060 IF TG_WHEN = 'BEFORE' THEN1337
1061 PERFORM unsummarise_bug(bug_row(OLD.bug));1338
1062 IF OLD.bug <> NEW.bug THEN1339CREATE FUNCTION public.build_access_cache(art_id integer, information_type integer) RETURNS record
1063 PERFORM unsummarise_bug(bug_row(NEW.bug));1340 LANGUAGE plpgsql
1064 END IF;1341 AS $$
1065 ELSE1342DECLARE
1066 PERFORM summarise_bug(bug_row(OLD.bug));1343 _policies integer[];
1067 IF OLD.bug <> NEW.bug THEN1344 _grants integer[];
1068 PERFORM summarise_bug(bug_row(NEW.bug));1345 cache record;
1069 END IF;1346BEGIN
1070 END IF;1347 -- If private, grab the access control information.
1071 END IF;1348 -- If public, access_policies and access_grants are NULL.
1072 PERFORM bug_summary_flush_temp_journal();1349 -- 3 == PRIVATESECURITY, 4 == USERDATA, 5 == PROPRIETARY
1073 RETURN NEW;1350 -- 6 == EMBARGOED
1074 END IF;1351 IF information_type NOT IN (1, 2) THEN
1075END;1352 SELECT COALESCE(array_agg(policy ORDER BY policy), ARRAY[]::integer[])
1076$$;1353 INTO _policies FROM accesspolicyartifact WHERE artifact = art_id;
10771354 SELECT COALESCE(array_agg(grantee ORDER BY grantee), ARRAY[]::integer[])
10781355 INTO _grants FROM accessartifactgrant WHERE artifact = art_id;
1079COMMENT ON FUNCTION bugtask_maintain_bug_summary() IS 'Both BEFORE & AFTER trigger on bugtask maintaining the bugs summaries in bugsummary.';1356 END IF;
10801357 cache := (_policies, _grants);
10811358 RETURN cache;
1082CREATE FUNCTION calculate_bug_heat(bug_id integer) RETURNS integer1359END;
1083 LANGUAGE plpythonu STABLE STRICT1360$$;
1084 AS $$1361
1085 from datetime import datetime1362
10861363CREATE FUNCTION public.calculate_bug_heat(bug_id integer) RETURNS integer
1087 class BugHeatConstants:1364 LANGUAGE sql STABLE STRICT
1088 PRIVACY = 1501365 AS $_$
1089 SECURITY = 2501366 SELECT
1090 DUPLICATE = 61367 (CASE information_type WHEN 1 THEN 0 WHEN 2 THEN 250
1091 AFFECTED_USER = 41368 WHEN 3 THEN 400 ELSE 150 END)
1092 SUBSCRIBER = 21369 + (number_of_duplicates * 6)
10931370 + (users_affected_count * 4)
1094 def get_max_heat_for_bug(bug_id):1371 + (
1095 results = plpy.execute("""1372 SELECT COUNT(DISTINCT person) * 2
1096 SELECT MAX(1373 FROM BugSubscription
1097 GREATEST(Product.max_bug_heat,1374 JOIN Bug AS SubBug ON BugSubscription.bug = SubBug.id
1098 DistributionSourcePackage.max_bug_heat))1375 WHERE SubBug.id = $1 OR SubBug.duplicateof = $1)::integer AS heat
1099 AS max_heat1376 FROM Bug WHERE Bug.id = $1;
1100 FROM BugTask1377$_$;
1101 LEFT OUTER JOIN ProductSeries ON1378
1102 BugTask.productseries = ProductSeries.id1379
1103 LEFT OUTER JOIN Product ON (1380CREATE FUNCTION public.cursor_fetch(cur refcursor, n integer) RETURNS SETOF record
1104 BugTask.product = Product.id
1105 OR ProductSeries.product = Product.id)
1106 LEFT OUTER JOIN DistroSeries ON
1107 BugTask.distroseries = DistroSeries.id
1108 LEFT OUTER JOIN Distribution ON (
1109 BugTask.distribution = Distribution.id
1110 OR DistroSeries.distribution = Distribution.id)
1111 LEFT OUTER JOIN DistributionSourcePackage ON (
1112 BugTask.sourcepackagename =
1113 DistributionSourcePackage.sourcepackagename)
1114 WHERE
1115 BugTask.bug = %s""" % bug_id)
1116
1117 return results[0]['max_heat']
1118
1119 # It would be nice to be able to just SELECT * here, but we need the
1120 # timestamps to be in a format that datetime.fromtimestamp() will
1121 # understand.
1122 bug_data = plpy.execute("""
1123 SELECT
1124 duplicateof,
1125 private,
1126 security_related,
1127 number_of_duplicates,
1128 users_affected_count,
1129 EXTRACT(epoch from datecreated)
1130 AS timestamp_date_created,
1131 EXTRACT(epoch from date_last_updated)
1132 AS timestamp_date_last_updated,
1133 EXTRACT(epoch from date_last_message)
1134 AS timestamp_date_last_message
1135 FROM Bug WHERE id = %s""" % bug_id)
1136
1137 if bug_data.nrows() == 0:
1138 raise Exception("Bug %s doesn't exist." % bug_id)
1139
1140 bug = bug_data[0]
1141 if bug['duplicateof'] is not None:
1142 return None
1143
1144 heat = {}
1145 heat['dupes'] = (
1146 BugHeatConstants.DUPLICATE * bug['number_of_duplicates'])
1147 heat['affected_users'] = (
1148 BugHeatConstants.AFFECTED_USER *
1149 bug['users_affected_count'])
1150
1151 if bug['private']:
1152 heat['privacy'] = BugHeatConstants.PRIVACY
1153 if bug['security_related']:
1154 heat['security'] = BugHeatConstants.SECURITY
1155
1156 # Get the heat from subscribers, both direct and via duplicates.
1157 subs_from_dupes = plpy.execute("""
1158 SELECT COUNT(DISTINCT BugSubscription.person) AS sub_count
1159 FROM BugSubscription, Bug
1160 WHERE Bug.id = BugSubscription.bug
1161 AND (Bug.id = %s OR Bug.duplicateof = %s)"""
1162 % (bug_id, bug_id))
1163
1164 heat['subcribers'] = (
1165 BugHeatConstants.SUBSCRIBER
1166 * subs_from_dupes[0]['sub_count'])
1167
1168 total_heat = sum(heat.values())
1169
1170 # Bugs decay over time. Every day the bug isn't touched its heat
1171 # decreases by 1%.
1172 date_last_updated = datetime.fromtimestamp(
1173 bug['timestamp_date_last_updated'])
1174 days_since_last_update = (datetime.utcnow() - date_last_updated).days
1175 total_heat = int(total_heat * (0.99 ** days_since_last_update))
1176
1177 if days_since_last_update > 0:
1178 # Bug heat increases by a quarter of the maximum bug heat
1179 # divided by the number of days since the bug's creation date.
1180 date_created = datetime.fromtimestamp(
1181 bug['timestamp_date_created'])
1182
1183 if bug['timestamp_date_last_message'] is not None:
1184 date_last_message = datetime.fromtimestamp(
1185 bug['timestamp_date_last_message'])
1186 oldest_date = max(date_last_updated, date_last_message)
1187 else:
1188 date_last_message = None
1189 oldest_date = date_last_updated
1190
1191 days_since_last_activity = (datetime.utcnow() - oldest_date).days
1192 days_since_created = (datetime.utcnow() - date_created).days
1193 max_heat = get_max_heat_for_bug(bug_id)
1194 if max_heat is not None and days_since_created > 0:
1195 total_heat = (
1196 total_heat + (max_heat * 0.25 / days_since_created))
1197
1198 return int(total_heat)
1199$$;
1200
1201
1202CREATE FUNCTION cursor_fetch(cur refcursor, n integer) RETURNS SETOF record
1203 LANGUAGE plpgsql1381 LANGUAGE plpgsql
1204 AS $$1382 AS $$
1205DECLARE1383DECLARE
@@ -1217,86 +1395,86 @@
1217$$;1395$$;
12181396
12191397
1220COMMENT 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.';1398COMMENT 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.';
12211399
12221400
1223CREATE FUNCTION debversion(character) RETURNS debversion1401CREATE FUNCTION public.debversion(character) RETURNS public.debversion
1224 LANGUAGE internal IMMUTABLE STRICT1402 LANGUAGE internal IMMUTABLE STRICT
1225 AS $$rtrim1$$;1403 AS $$rtrim1$$;
12261404
12271405
1228CREATE FUNCTION debversion_cmp(version1 debversion, version2 debversion) RETURNS integer1406CREATE FUNCTION public.debversion_cmp(version1 public.debversion, version2 public.debversion) RETURNS integer
1229 LANGUAGE c IMMUTABLE STRICT1407 LANGUAGE c IMMUTABLE STRICT
1230 AS '$libdir/debversion', 'debversion_cmp';1408 AS '$libdir/debversion', 'debversion_cmp';
12311409
12321410
1233COMMENT ON FUNCTION debversion_cmp(version1 debversion, version2 debversion) IS 'Compare Debian versions';1411COMMENT ON FUNCTION public.debversion_cmp(version1 public.debversion, version2 public.debversion) IS 'Compare Debian versions';
12341412
12351413
1236CREATE FUNCTION debversion_eq(version1 debversion, version2 debversion) RETURNS boolean1414CREATE FUNCTION public.debversion_eq(version1 public.debversion, version2 public.debversion) RETURNS boolean
1237 LANGUAGE c IMMUTABLE STRICT1415 LANGUAGE c IMMUTABLE STRICT
1238 AS '$libdir/debversion', 'debversion_eq';1416 AS '$libdir/debversion', 'debversion_eq';
12391417
12401418
1241COMMENT ON FUNCTION debversion_eq(version1 debversion, version2 debversion) IS 'debversion equal';1419COMMENT ON FUNCTION public.debversion_eq(version1 public.debversion, version2 public.debversion) IS 'debversion equal';
12421420
12431421
1244CREATE FUNCTION debversion_ge(version1 debversion, version2 debversion) RETURNS boolean1422CREATE FUNCTION public.debversion_ge(version1 public.debversion, version2 public.debversion) RETURNS boolean
1245 LANGUAGE c IMMUTABLE STRICT1423 LANGUAGE c IMMUTABLE STRICT
1246 AS '$libdir/debversion', 'debversion_ge';1424 AS '$libdir/debversion', 'debversion_ge';
12471425
12481426
1249COMMENT ON FUNCTION debversion_ge(version1 debversion, version2 debversion) IS 'debversion greater-than-or-equal';1427COMMENT ON FUNCTION public.debversion_ge(version1 public.debversion, version2 public.debversion) IS 'debversion greater-than-or-equal';
12501428
12511429
1252CREATE FUNCTION debversion_gt(version1 debversion, version2 debversion) RETURNS boolean1430CREATE FUNCTION public.debversion_gt(version1 public.debversion, version2 public.debversion) RETURNS boolean
1253 LANGUAGE c IMMUTABLE STRICT1431 LANGUAGE c IMMUTABLE STRICT
1254 AS '$libdir/debversion', 'debversion_gt';1432 AS '$libdir/debversion', 'debversion_gt';
12551433
12561434
1257COMMENT ON FUNCTION debversion_gt(version1 debversion, version2 debversion) IS 'debversion greater-than';1435COMMENT ON FUNCTION public.debversion_gt(version1 public.debversion, version2 public.debversion) IS 'debversion greater-than';
12581436
12591437
1260CREATE FUNCTION debversion_hash(debversion) RETURNS integer1438CREATE FUNCTION public.debversion_hash(public.debversion) RETURNS integer
1261 LANGUAGE c IMMUTABLE STRICT1439 LANGUAGE c IMMUTABLE STRICT
1262 AS '$libdir/debversion', 'debversion_hash';1440 AS '$libdir/debversion', 'debversion_hash';
12631441
12641442
1265CREATE FUNCTION debversion_larger(version1 debversion, version2 debversion) RETURNS debversion1443CREATE FUNCTION public.debversion_larger(version1 public.debversion, version2 public.debversion) RETURNS public.debversion
1266 LANGUAGE c IMMUTABLE STRICT1444 LANGUAGE c IMMUTABLE STRICT
1267 AS '$libdir/debversion', 'debversion_larger';1445 AS '$libdir/debversion', 'debversion_larger';
12681446
12691447
1270CREATE FUNCTION debversion_le(version1 debversion, version2 debversion) RETURNS boolean1448CREATE FUNCTION public.debversion_le(version1 public.debversion, version2 public.debversion) RETURNS boolean
1271 LANGUAGE c IMMUTABLE STRICT1449 LANGUAGE c IMMUTABLE STRICT
1272 AS '$libdir/debversion', 'debversion_le';1450 AS '$libdir/debversion', 'debversion_le';
12731451
12741452
1275COMMENT ON FUNCTION debversion_le(version1 debversion, version2 debversion) IS 'debversion less-than-or-equal';1453COMMENT ON FUNCTION public.debversion_le(version1 public.debversion, version2 public.debversion) IS 'debversion less-than-or-equal';
12761454
12771455
1278CREATE FUNCTION debversion_lt(version1 debversion, version2 debversion) RETURNS boolean1456CREATE FUNCTION public.debversion_lt(version1 public.debversion, version2 public.debversion) RETURNS boolean
1279 LANGUAGE c IMMUTABLE STRICT1457 LANGUAGE c IMMUTABLE STRICT
1280 AS '$libdir/debversion', 'debversion_lt';1458 AS '$libdir/debversion', 'debversion_lt';
12811459
12821460
1283COMMENT ON FUNCTION debversion_lt(version1 debversion, version2 debversion) IS 'debversion less-than';1461COMMENT ON FUNCTION public.debversion_lt(version1 public.debversion, version2 public.debversion) IS 'debversion less-than';
12841462
12851463
1286CREATE FUNCTION debversion_ne(version1 debversion, version2 debversion) RETURNS boolean1464CREATE FUNCTION public.debversion_ne(version1 public.debversion, version2 public.debversion) RETURNS boolean
1287 LANGUAGE c IMMUTABLE STRICT1465 LANGUAGE c IMMUTABLE STRICT
1288 AS '$libdir/debversion', 'debversion_ne';1466 AS '$libdir/debversion', 'debversion_ne';
12891467
12901468
1291COMMENT ON FUNCTION debversion_ne(version1 debversion, version2 debversion) IS 'debversion not equal';1469COMMENT ON FUNCTION public.debversion_ne(version1 public.debversion, version2 public.debversion) IS 'debversion not equal';
12921470
12931471
1294CREATE FUNCTION debversion_smaller(version1 debversion, version2 debversion) RETURNS debversion1472CREATE FUNCTION public.debversion_smaller(version1 public.debversion, version2 public.debversion) RETURNS public.debversion
1295 LANGUAGE c IMMUTABLE STRICT1473 LANGUAGE c IMMUTABLE STRICT
1296 AS '$libdir/debversion', 'debversion_smaller';1474 AS '$libdir/debversion', 'debversion_smaller';
12971475
12981476
1299CREATE FUNCTION debversion_sort_key(version text) RETURNS text1477CREATE FUNCTION public.debversion_sort_key(version text) RETURNS text
1300 LANGUAGE plpythonu IMMUTABLE STRICT1478 LANGUAGE plpythonu IMMUTABLE STRICT
1301 AS $_$1479 AS $_$
1302 # If this method is altered, then any functional indexes using it1480 # If this method is altered, then any functional indexes using it
@@ -1365,13 +1543,12 @@
1365$_$;1543$_$;
13661544
13671545
1368COMMENT ON FUNCTION debversion_sort_key(version text) IS 'Return a string suitable for sorting debian version strings on';1546COMMENT ON FUNCTION public.debversion_sort_key(version text) IS 'Return a string suitable for sorting debian version strings on';
13691547
13701548
1371CREATE FUNCTION ensure_bugsummary_temp_journal() RETURNS void1549CREATE FUNCTION public.ensure_bugsummary_temp_journal() RETURNS void
1372 LANGUAGE plpgsql1550 LANGUAGE plpgsql
1373 AS $$1551 AS $$
1374DECLARE
1375BEGIN1552BEGIN
1376 CREATE TEMPORARY TABLE bugsummary_temp_journal (1553 CREATE TEMPORARY TABLE bugsummary_temp_journal (
1377 LIKE bugsummary ) ON COMMIT DROP;1554 LIKE bugsummary ) ON COMMIT DROP;
@@ -1383,48 +1560,66 @@
1383$$;1560$$;
13841561
13851562
1386COMMENT ON FUNCTION ensure_bugsummary_temp_journal() IS 'Create a temporary table bugsummary_temp_journal if it does not exist.';1563COMMENT ON FUNCTION public.ensure_bugsummary_temp_journal() IS 'Create a temporary table bugsummary_temp_journal if it does not exist.';
13871564
13881565
1389CREATE FUNCTION generate_openid_identifier() RETURNS text1566CREATE FUNCTION public.ftiupdate() RETURNS trigger
1390 LANGUAGE plpythonu1567 LANGUAGE plpythonu
1391 AS $$1568 AS $_$
1392 from random import choice1569 new = TD["new"]
13931570 args = TD["args"][:]
1394 # Non display confusing characters.1571
1395 chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'1572 # Short circuit if none of the relevant columns have been
13961573 # modified and fti is not being set to NULL (setting the fti
1397 # Character length of tokens. Can be increased, decreased or even made1574 # column to NULL is thus how we can force a rebuild of the fti
1398 # random - Launchpad does not care. 7 means it takes 40 bytes to store1575 # column).
1399 # a null-terminated Launchpad identity URL on the current domain name.1576 if TD["event"] == "UPDATE" and new["fti"] != None:
1400 length=71577 old = TD["old"]
14011578 relevant_modification = False
1402 loop_count = 01579 for column_name in args[::2]:
1403 while loop_count < 20000:1580 if new[column_name] != old[column_name]:
1404 # Generate a random openid_identifier1581 relevant_modification = True
1405 oid = ''.join(choice(chars) for count in range(length))1582 break
14061583 if not relevant_modification:
1407 # Check if the oid is already in the db, although this is pretty1584 return "OK"
1408 # unlikely1585
1409 rv = plpy.execute("""1586 # Generate an SQL statement that turns the requested
1410 SELECT COUNT(*) AS num FROM Account WHERE openid_identifier = '%s'1587 # column values into a weighted tsvector
1411 """ % oid, 1)1588 sql = []
1412 if rv[0]['num'] == 0:1589 for i in range(0, len(args), 2):
1413 return oid1590 sql.append(
1414 loop_count += 11591 "setweight(to_tsvector('default', coalesce("
1415 if loop_count == 1:1592 "substring(ltrim($%d) from 1 for 2500),'')),"
1416 plpy.warning(1593 "CAST($%d AS \"char\"))" % (i + 1, i + 2))
1417 'Clash generating unique openid_identifier. '1594 args[i] = new[args[i]]
1418 'Increase length if you see this warning too much.')1595
1419 plpy.error(1596 sql = "SELECT %s AS fti" % "||".join(sql)
1420 "Unable to generate unique openid_identifier. "1597
1421 "Need to increase length of tokens.")1598 # Execute and store in the fti column
1422$$;1599 plan = plpy.prepare(sql, ["text", "char"] * (len(args)/2))
14231600 new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
14241601
1425CREATE FUNCTION getlocalnodeid() RETURNS integer1602 # Tell PostgreSQL we have modified the data
1603 return "MODIFY"
1604$_$;
1605
1606
1607COMMENT ON FUNCTION public.ftiupdate() IS 'Trigger function that keeps the fti tsvector column up to date.';
1608
1609
1610CREATE FUNCTION public.ftq(text) RETURNS tsquery
1611 LANGUAGE plpythonu IMMUTABLE STRICT
1612 AS $_$
1613 p = plpy.prepare(
1614 "SELECT to_tsquery('default', _ftq($1)) AS x", ["text"])
1615 query = plpy.execute(p, args, 1)[0]["x"]
1616 return query or None
1617 $_$;
1618
1619
1620CREATE FUNCTION public.getlocalnodeid() RETURNS integer
1426 LANGUAGE plpgsql STABLE SECURITY DEFINER1621 LANGUAGE plpgsql STABLE SECURITY DEFINER
1427 SET search_path TO public1622 SET search_path TO 'public'
1428 AS $$1623 AS $$
1429 DECLARE1624 DECLARE
1430 v_node_id integer;1625 v_node_id integer;
@@ -1438,70 +1633,48 @@
1438$$;1633$$;
14391634
14401635
1441COMMENT ON FUNCTION getlocalnodeid() IS 'Return the replication node id for this node, or NULL if not a replicated installation.';1636COMMENT ON FUNCTION public.getlocalnodeid() IS 'Return the replication node id for this node, or NULL if not a replicated installation.';
14421637
14431638
1444CREATE FUNCTION is_blacklisted_name(text, integer) RETURNS boolean1639CREATE FUNCTION public.gitrepository_denorm_access(gitrepository_id integer) RETURNS void
1640 LANGUAGE sql SECURITY DEFINER
1641 SET search_path TO 'public'
1642 AS $_$
1643 UPDATE GitRepository
1644 SET access_policy = policies[1], access_grants = grants
1645 FROM
1646 build_access_cache(
1647 (SELECT id FROM accessartifact WHERE gitrepository = $1),
1648 (SELECT information_type FROM gitrepository WHERE id = $1))
1649 AS (policies integer[], grants integer[])
1650 WHERE id = $1;
1651$_$;
1652
1653
1654CREATE FUNCTION public.gitrepository_maintain_access_cache_trig() RETURNS trigger
1655 LANGUAGE plpgsql
1656 AS $$
1657BEGIN
1658 PERFORM gitrepository_denorm_access(NEW.id);
1659 RETURN NULL;
1660END;
1661$$;
1662
1663
1664CREATE FUNCTION public.is_blacklisted_name(text, integer) RETURNS boolean
1445 LANGUAGE sql STABLE STRICT SECURITY DEFINER1665 LANGUAGE sql STABLE STRICT SECURITY DEFINER
1446 SET search_path TO public1666 SET search_path TO 'public'
1447 AS $_$1667 AS $_$
1448 SELECT COALESCE(name_blacklist_match($1, $2)::boolean, FALSE);1668 SELECT COALESCE(name_blacklist_match($1, $2)::boolean, FALSE);
1449$_$;1669$_$;
14501670
14511671
1452COMMENT 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.';1672COMMENT 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.';
14531673
14541674
1455CREATE FUNCTION is_person(text) RETURNS boolean1675CREATE FUNCTION public.lp_mirror_account_ins() RETURNS trigger
1456 LANGUAGE sql STABLE STRICT
1457 AS $_$
1458 SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NULL;
1459$_$;
1460
1461
1462COMMENT ON FUNCTION is_person(text) IS 'True if the given name identifies a person in the Person table';
1463
1464
1465CREATE FUNCTION is_printable_ascii(text) RETURNS boolean
1466 LANGUAGE plpythonu IMMUTABLE STRICT
1467 AS $_$
1468 import re, string
1469 try:
1470 text = args[0].decode("ASCII")
1471 except UnicodeError:
1472 return False
1473 if re.search(r"^[%s]*$" % re.escape(string.printable), text) is None:
1474 return False
1475 return True
1476$_$;
1477
1478
1479COMMENT ON FUNCTION is_printable_ascii(text) IS 'True if the string is pure printable US-ASCII';
1480
1481
1482CREATE FUNCTION is_team(integer) RETURNS boolean
1483 LANGUAGE sql STABLE STRICT
1484 AS $_$
1485 SELECT count(*)>0 FROM Person WHERE id=$1 AND teamowner IS NOT NULL;
1486$_$;
1487
1488
1489COMMENT ON FUNCTION is_team(integer) IS 'True if the given id identifies a team in the Person table';
1490
1491
1492CREATE FUNCTION is_team(text) RETURNS boolean
1493 LANGUAGE sql STABLE STRICT
1494 AS $_$
1495 SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NOT NULL;
1496$_$;
1497
1498
1499COMMENT ON FUNCTION is_team(text) IS 'True if the given name identifies a team in the Person table';
1500
1501
1502CREATE FUNCTION lp_mirror_account_ins() RETURNS trigger
1503 LANGUAGE plpgsql SECURITY DEFINER1676 LANGUAGE plpgsql SECURITY DEFINER
1504 SET search_path TO public1677 SET search_path TO 'public'
1505 AS $$1678 AS $$
1506BEGIN1679BEGIN
1507 INSERT INTO lp_Account (id, openid_identifier)1680 INSERT INTO lp_Account (id, openid_identifier)
@@ -1511,9 +1684,9 @@
1511$$;1684$$;
15121685
15131686
1514CREATE FUNCTION lp_mirror_account_upd() RETURNS trigger1687CREATE FUNCTION public.lp_mirror_account_upd() RETURNS trigger
1515 LANGUAGE plpgsql SECURITY DEFINER1688 LANGUAGE plpgsql SECURITY DEFINER
1516 SET search_path TO public1689 SET search_path TO 'public'
1517 AS $$1690 AS $$
1518BEGIN1691BEGIN
1519 IF OLD.id <> NEW.id OR OLD.openid_identifier <> NEW.openid_identifier THEN1692 IF OLD.id <> NEW.id OR OLD.openid_identifier <> NEW.openid_identifier THEN
@@ -1526,9 +1699,9 @@
1526$$;1699$$;
15271700
15281701
1529CREATE FUNCTION lp_mirror_del() RETURNS trigger1702CREATE FUNCTION public.lp_mirror_del() RETURNS trigger
1530 LANGUAGE plpgsql SECURITY DEFINER1703 LANGUAGE plpgsql SECURITY DEFINER
1531 SET search_path TO public1704 SET search_path TO 'public'
1532 AS $$1705 AS $$
1533BEGIN1706BEGIN
1534 EXECUTE 'DELETE FROM lp_' || TG_TABLE_NAME || ' WHERE id=' || OLD.id;1707 EXECUTE 'DELETE FROM lp_' || TG_TABLE_NAME || ' WHERE id=' || OLD.id;
@@ -1537,9 +1710,9 @@
1537$$;1710$$;
15381711
15391712
1540CREATE FUNCTION lp_mirror_openididentifier_del() RETURNS trigger1713CREATE FUNCTION public.lp_mirror_openididentifier_del() RETURNS trigger
1541 LANGUAGE plpgsql SECURITY DEFINER1714 LANGUAGE plpgsql SECURITY DEFINER
1542 SET search_path TO public1715 SET search_path TO 'public'
1543 AS $$1716 AS $$
1544DECLARE1717DECLARE
1545 next_identifier text;1718 next_identifier text;
@@ -1562,9 +1735,9 @@
1562$$;1735$$;
15631736
15641737
1565CREATE FUNCTION lp_mirror_openididentifier_ins() RETURNS trigger1738CREATE FUNCTION public.lp_mirror_openididentifier_ins() RETURNS trigger
1566 LANGUAGE plpgsql SECURITY DEFINER1739 LANGUAGE plpgsql SECURITY DEFINER
1567 SET search_path TO public1740 SET search_path TO 'public'
1568 AS $$1741 AS $$
1569BEGIN1742BEGIN
1570 -- Support obsolete lp_Account.openid_identifier as best we can1743 -- Support obsolete lp_Account.openid_identifier as best we can
@@ -1584,9 +1757,9 @@
1584$$;1757$$;
15851758
15861759
1587CREATE FUNCTION lp_mirror_openididentifier_upd() RETURNS trigger1760CREATE FUNCTION public.lp_mirror_openididentifier_upd() RETURNS trigger
1588 LANGUAGE plpgsql SECURITY DEFINER1761 LANGUAGE plpgsql SECURITY DEFINER
1589 SET search_path TO public1762 SET search_path TO 'public'
1590 AS $$1763 AS $$
1591BEGIN1764BEGIN
1592 IF OLD.identifier <> NEW.identifier THEN1765 IF OLD.identifier <> NEW.identifier THEN
@@ -1604,9 +1777,9 @@
1604$$;1777$$;
16051778
16061779
1607CREATE FUNCTION lp_mirror_person_ins() RETURNS trigger1780CREATE FUNCTION public.lp_mirror_person_ins() RETURNS trigger
1608 LANGUAGE plpgsql SECURITY DEFINER1781 LANGUAGE plpgsql SECURITY DEFINER
1609 SET search_path TO public1782 SET search_path TO 'public'
1610 AS $$1783 AS $$
1611BEGIN1784BEGIN
1612 INSERT INTO lp_Person (1785 INSERT INTO lp_Person (
@@ -1634,9 +1807,9 @@
1634$$;1807$$;
16351808
16361809
1637CREATE FUNCTION lp_mirror_person_upd() RETURNS trigger1810CREATE FUNCTION public.lp_mirror_person_upd() RETURNS trigger
1638 LANGUAGE plpgsql SECURITY DEFINER1811 LANGUAGE plpgsql SECURITY DEFINER
1639 SET search_path TO public1812 SET search_path TO 'public'
1640 AS $$1813 AS $$
1641BEGIN1814BEGIN
1642 UPDATE lp_Person1815 UPDATE lp_Person
@@ -1676,9 +1849,9 @@
1676$$;1849$$;
16771850
16781851
1679CREATE FUNCTION lp_mirror_personlocation_ins() RETURNS trigger1852CREATE FUNCTION public.lp_mirror_personlocation_ins() RETURNS trigger
1680 LANGUAGE plpgsql SECURITY DEFINER1853 LANGUAGE plpgsql SECURITY DEFINER
1681 SET search_path TO public1854 SET search_path TO 'public'
1682 AS $$1855 AS $$
1683BEGIN1856BEGIN
1684 INSERT INTO lp_PersonLocation SELECT NEW.*;1857 INSERT INTO lp_PersonLocation SELECT NEW.*;
@@ -1687,9 +1860,9 @@
1687$$;1860$$;
16881861
16891862
1690CREATE FUNCTION lp_mirror_personlocation_upd() RETURNS trigger1863CREATE FUNCTION public.lp_mirror_personlocation_upd() RETURNS trigger
1691 LANGUAGE plpgsql SECURITY DEFINER1864 LANGUAGE plpgsql SECURITY DEFINER
1692 SET search_path TO public1865 SET search_path TO 'public'
1693 AS $$1866 AS $$
1694BEGIN1867BEGIN
1695 UPDATE lp_PersonLocation1868 UPDATE lp_PersonLocation
@@ -1709,9 +1882,9 @@
1709$$;1882$$;
17101883
17111884
1712CREATE FUNCTION lp_mirror_teamparticipation_ins() RETURNS trigger1885CREATE FUNCTION public.lp_mirror_teamparticipation_ins() RETURNS trigger
1713 LANGUAGE plpgsql SECURITY DEFINER1886 LANGUAGE plpgsql SECURITY DEFINER
1714 SET search_path TO public1887 SET search_path TO 'public'
1715 AS $$1888 AS $$
1716BEGIN1889BEGIN
1717 INSERT INTO lp_TeamParticipation SELECT NEW.*;1890 INSERT INTO lp_TeamParticipation SELECT NEW.*;
@@ -1720,9 +1893,9 @@
1720$$;1893$$;
17211894
17221895
1723CREATE FUNCTION lp_mirror_teamparticipation_upd() RETURNS trigger1896CREATE FUNCTION public.lp_mirror_teamparticipation_upd() RETURNS trigger
1724 LANGUAGE plpgsql SECURITY DEFINER1897 LANGUAGE plpgsql SECURITY DEFINER
1725 SET search_path TO public1898 SET search_path TO 'public'
1726 AS $$1899 AS $$
1727BEGIN1900BEGIN
1728 UPDATE lp_TeamParticipation1901 UPDATE lp_TeamParticipation
@@ -1735,28 +1908,9 @@
1735$$;1908$$;
17361909
17371910
1738CREATE FUNCTION maintain_transitively_private() RETURNS trigger1911CREATE FUNCTION public.message_copy_owner_to_bugmessage() RETURNS trigger
1739 LANGUAGE plpgsql
1740 AS $$
1741BEGIN
1742 IF TG_OP = 'UPDATE' THEN
1743 IF (NEW.stacked_on IS NOT DISTINCT FROM OLD.stacked_on
1744 AND NEW.private IS NOT DISTINCT FROM OLD.private) THEN
1745 RETURN NULL;
1746 END IF;
1747 END IF;
1748 PERFORM update_transitively_private(NEW.id);
1749 RETURN NULL;
1750END;
1751$$;
1752
1753
1754COMMENT ON FUNCTION maintain_transitively_private() IS 'Trigger maintaining the Branch transitively_private column';
1755
1756
1757CREATE FUNCTION message_copy_owner_to_bugmessage() RETURNS trigger
1758 LANGUAGE plpgsql SECURITY DEFINER1912 LANGUAGE plpgsql SECURITY DEFINER
1759 SET search_path TO public1913 SET search_path TO 'public'
1760 AS $$1914 AS $$
1761BEGIN1915BEGIN
1762 IF NEW.owner != OLD.owner THEN1916 IF NEW.owner != OLD.owner THEN
@@ -1770,12 +1924,12 @@
1770$$;1924$$;
17711925
17721926
1773COMMENT ON FUNCTION message_copy_owner_to_bugmessage() IS 'Copies the message owner into bugmessage when message changes.';1927COMMENT ON FUNCTION public.message_copy_owner_to_bugmessage() IS 'Copies the message owner into bugmessage when message changes.';
17741928
17751929
1776CREATE FUNCTION message_copy_owner_to_questionmessage() RETURNS trigger1930CREATE FUNCTION public.message_copy_owner_to_questionmessage() RETURNS trigger
1777 LANGUAGE plpgsql SECURITY DEFINER1931 LANGUAGE plpgsql SECURITY DEFINER
1778 SET search_path TO public1932 SET search_path TO 'public'
1779 AS $$1933 AS $$
1780BEGIN1934BEGIN
1781 IF NEW.owner != OLD.owner THEN1935 IF NEW.owner != OLD.owner THEN
@@ -1789,10 +1943,10 @@
1789$$;1943$$;
17901944
17911945
1792COMMENT ON FUNCTION message_copy_owner_to_questionmessage() IS 'Copies the message owner into questionmessage when message changes.';1946COMMENT ON FUNCTION public.message_copy_owner_to_questionmessage() IS 'Copies the message owner into questionmessage when message changes.';
17931947
17941948
1795CREATE FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text1949CREATE FUNCTION public.milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text
1796 LANGUAGE plpythonu IMMUTABLE1950 LANGUAGE plpythonu IMMUTABLE
1797 AS $$1951 AS $$
1798 # If this method is altered, then any functional indexes using it1952 # If this method is altered, then any functional indexes using it
@@ -1813,10 +1967,10 @@
1813$$;1967$$;
18141968
18151969
1816COMMENT 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.';1970COMMENT 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.';
18171971
18181972
1819CREATE FUNCTION mv_branch_distribution_update() RETURNS trigger1973CREATE FUNCTION public.mv_branch_distribution_update() RETURNS trigger
1820 LANGUAGE plpgsql1974 LANGUAGE plpgsql
1821 AS $$1975 AS $$
1822BEGIN1976BEGIN
@@ -1834,10 +1988,10 @@
1834$$;1988$$;
18351989
18361990
1837COMMENT ON FUNCTION mv_branch_distribution_update() IS 'Maintain Branch name cache when Distribution is modified.';1991COMMENT ON FUNCTION public.mv_branch_distribution_update() IS 'Maintain Branch name cache when Distribution is modified.';
18381992
18391993
1840CREATE FUNCTION mv_branch_distroseries_update() RETURNS trigger1994CREATE FUNCTION public.mv_branch_distroseries_update() RETURNS trigger
1841 LANGUAGE plpgsql1995 LANGUAGE plpgsql
1842 AS $$1996 AS $$
1843BEGIN1997BEGIN
@@ -1853,10 +2007,10 @@
1853$$;2007$$;
18542008
18552009
1856COMMENT ON FUNCTION mv_branch_distroseries_update() IS 'Maintain Branch name cache when Distroseries is modified.';2010COMMENT ON FUNCTION public.mv_branch_distroseries_update() IS 'Maintain Branch name cache when Distroseries is modified.';
18572011
18582012
1859CREATE FUNCTION mv_branch_person_update() RETURNS trigger2013CREATE FUNCTION public.mv_branch_person_update() RETURNS trigger
1860 LANGUAGE plpgsql2014 LANGUAGE plpgsql
1861 AS $$2015 AS $$
1862DECLARE2016DECLARE
@@ -1873,10 +2027,10 @@
1873$$;2027$$;
18742028
18752029
1876COMMENT ON FUNCTION mv_branch_person_update() IS 'Maintain Branch name cache when Person is modified.';2030COMMENT ON FUNCTION public.mv_branch_person_update() IS 'Maintain Branch name cache when Person is modified.';
18772031
18782032
1879CREATE FUNCTION mv_branch_product_update() RETURNS trigger2033CREATE FUNCTION public.mv_branch_product_update() RETURNS trigger
1880 LANGUAGE plpgsql2034 LANGUAGE plpgsql
1881 AS $$2035 AS $$
1882DECLARE2036DECLARE
@@ -1893,12 +2047,12 @@
1893$$;2047$$;
18942048
18952049
1896COMMENT ON FUNCTION mv_branch_product_update() IS 'Maintain Branch name cache when Product is modified.';2050COMMENT ON FUNCTION public.mv_branch_product_update() IS 'Maintain Branch name cache when Product is modified.';
18972051
18982052
1899CREATE FUNCTION mv_pillarname_distribution() RETURNS trigger2053CREATE FUNCTION public.mv_pillarname_distribution() RETURNS trigger
1900 LANGUAGE plpgsql SECURITY DEFINER2054 LANGUAGE plpgsql SECURITY DEFINER
1901 SET search_path TO public2055 SET search_path TO 'public'
1902 AS $$2056 AS $$
1903BEGIN2057BEGIN
1904 IF TG_OP = 'INSERT' THEN2058 IF TG_OP = 'INSERT' THEN
@@ -1912,12 +2066,12 @@
1912$$;2066$$;
19132067
19142068
1915COMMENT ON FUNCTION mv_pillarname_distribution() IS 'Trigger maintaining the PillarName table';2069COMMENT ON FUNCTION public.mv_pillarname_distribution() IS 'Trigger maintaining the PillarName table';
19162070
19172071
1918CREATE FUNCTION mv_pillarname_product() RETURNS trigger2072CREATE FUNCTION public.mv_pillarname_product() RETURNS trigger
1919 LANGUAGE plpgsql SECURITY DEFINER2073 LANGUAGE plpgsql SECURITY DEFINER
1920 SET search_path TO public2074 SET search_path TO 'public'
1921 AS $$2075 AS $$
1922BEGIN2076BEGIN
1923 IF TG_OP = 'INSERT' THEN2077 IF TG_OP = 'INSERT' THEN
@@ -1932,12 +2086,12 @@
1932$$;2086$$;
19332087
19342088
1935COMMENT ON FUNCTION mv_pillarname_product() IS 'Trigger maintaining the PillarName table';2089COMMENT ON FUNCTION public.mv_pillarname_product() IS 'Trigger maintaining the PillarName table';
19362090
19372091
1938CREATE FUNCTION mv_pillarname_project() RETURNS trigger2092CREATE FUNCTION public.mv_pillarname_project() RETURNS trigger
1939 LANGUAGE plpgsql SECURITY DEFINER2093 LANGUAGE plpgsql SECURITY DEFINER
1940 SET search_path TO public2094 SET search_path TO 'public'
1941 AS $$2095 AS $$
1942BEGIN2096BEGIN
1943 IF TG_OP = 'INSERT' THEN2097 IF TG_OP = 'INSERT' THEN
@@ -1952,221 +2106,47 @@
1952$$;2106$$;
19532107
19542108
1955COMMENT ON FUNCTION mv_pillarname_project() IS 'Trigger maintaining the PillarName table';2109COMMENT ON FUNCTION public.mv_pillarname_project() IS 'Trigger maintaining the PillarName table';
19562110
19572111
1958CREATE FUNCTION mv_pofiletranslator_pomsgset() RETURNS trigger2112CREATE FUNCTION public.mv_pofiletranslator_translationmessage() RETURNS trigger
1959 LANGUAGE plpgsql2113 LANGUAGE plpgsql SECURITY DEFINER
1960 AS $$2114 SET search_path TO 'public'
1961BEGIN2115 AS $$
1962 IF TG_OP = 'DELETE' THEN2116BEGIN
1963 RAISE EXCEPTION2117 -- Update any existing entries.
1964 'Deletions from POMsgSet not supported by the POFileTranslator materialized view';2118 UPDATE POFileTranslator
1965 ELSIF TG_OP = 'UPDATE' THEN2119 SET date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
1966 IF OLD.pofile != NEW.pofile THEN2120 FROM POFile, TranslationTemplateItem
1967 RAISE EXCEPTION2121 WHERE person = NEW.submitter AND
1968 'Changing POMsgSet.pofile not supported by the POFileTranslator materialized view';2122 TranslationTemplateItem.potmsgset = NEW.potmsgset AND
1969 END IF;2123 TranslationTemplateItem.potemplate = POFile.potemplate AND
1970 END IF;2124 POFile.language = NEW.language AND
1971 RETURN NEW;2125 POFileTranslator.pofile = POFile.id;
1972END;2126
1973$$;2127 -- Insert any missing entries.
19742128 INSERT INTO POFileTranslator (person, pofile)
19752129 SELECT DISTINCT NEW.submitter, POFile.id
1976COMMENT ON FUNCTION mv_pofiletranslator_pomsgset() IS 'Trigger enforing no POMsgSet deletions or POMsgSet.pofile changes';2130 FROM TranslationTemplateItem
19772131 JOIN POFile ON
19782132 POFile.language = NEW.language AND
1979CREATE FUNCTION mv_pofiletranslator_posubmission() RETURNS trigger2133 POFile.potemplate = TranslationTemplateItem.potemplate
1980 LANGUAGE plpgsql SECURITY DEFINER2134 WHERE
1981 AS $$2135 TranslationTemplateItem.potmsgset = NEW.potmsgset AND
1982DECLARE2136 NOT EXISTS (
1983 v_pofile INTEGER;2137 SELECT *
1984 v_trash_old BOOLEAN;2138 FROM POFileTranslator
1985BEGIN2139 WHERE person = NEW.submitter AND pofile = POFile.id
1986 -- If we are deleting a row, we need to remove the existing2140 );
1987 -- POFileTranslator row and reinsert the historical data if it exists.2141 RETURN NULL;
1988 -- We also treat UPDATEs that change the key (person, pofile) the same2142END;
1989 -- as deletes. UPDATEs that don't change these columns are treated like2143$$;
1990 -- INSERTs below.2144
1991 IF TG_OP = 'INSERT' THEN2145
1992 v_trash_old := FALSE;2146COMMENT ON FUNCTION public.mv_pofiletranslator_translationmessage() IS 'Trigger maintaining the POFileTranslator table';
1993 ELSIF TG_OP = 'DELETE' THEN2147
1994 v_trash_old := TRUE;2148
1995 ELSE -- UPDATE2149CREATE FUNCTION public.mv_validpersonorteamcache_emailaddress() RETURNS trigger
1996 v_trash_old = (
1997 OLD.person != NEW.person OR OLD.pomsgset != NEW.pomsgset
1998 );
1999 END IF;
2000
2001 IF v_trash_old THEN
2002
2003 -- Delete the old record.
2004 DELETE FROM POFileTranslator USING POMsgSet
2005 WHERE POFileTranslator.pofile = POMsgSet.pofile
2006 AND POFileTranslator.person = OLD.person
2007 AND POMsgSet.id = OLD.pomsgset;
2008
2009 -- Insert a past record if there is one.
2010 INSERT INTO POFileTranslator (
2011 person, pofile, latest_posubmission, date_last_touched
2012 )
2013 SELECT DISTINCT ON (POSubmission.person, POMsgSet.pofile)
2014 POSubmission.person, POMsgSet.pofile,
2015 POSubmission.id, POSubmission.datecreated
2016 FROM POSubmission, POMsgSet
2017 WHERE POSubmission.pomsgset = POMsgSet.id
2018 AND POSubmission.pomsgset = OLD.pomsgset
2019 AND POSubmission.person = OLD.person
2020 ORDER BY
2021 POSubmission.person, POMsgSet.pofile,
2022 POSubmission.datecreated DESC, POSubmission.id DESC;
2023
2024 -- No NEW with DELETE, so we can short circuit and leave.
2025 IF TG_OP = 'DELETE' THEN
2026 RETURN NULL; -- Ignored because this is an AFTER trigger
2027 END IF;
2028 END IF;
2029
2030 -- Get our new pofile id
2031 SELECT INTO v_pofile POMsgSet.pofile FROM POMsgSet
2032 WHERE POMsgSet.id = NEW.pomsgset;
2033
2034 -- Standard 'upsert' loop to avoid race conditions.
2035 LOOP
2036 UPDATE POFileTranslator
2037 SET
2038 date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
2039 latest_posubmission = NEW.id
2040 WHERE
2041 person = NEW.person
2042 AND pofile = v_pofile;
2043 IF found THEN
2044 RETURN NULL; -- Return value ignored as this is an AFTER trigger
2045 END IF;
2046
2047 BEGIN
2048 INSERT INTO POFileTranslator (person, pofile, latest_posubmission)
2049 VALUES (NEW.person, v_pofile, NEW.id);
2050 RETURN NULL; -- Return value ignored as this is an AFTER trigger
2051 EXCEPTION WHEN unique_violation THEN
2052 -- do nothing
2053 END;
2054 END LOOP;
2055END;
2056$$;
2057
2058
2059COMMENT ON FUNCTION mv_pofiletranslator_posubmission() IS 'Trigger maintaining the POFileTranslator table';
2060
2061
2062CREATE FUNCTION mv_pofiletranslator_translationmessage() RETURNS trigger
2063 LANGUAGE plpgsql SECURITY DEFINER
2064 SET search_path TO public
2065 AS $$
2066DECLARE
2067 v_trash_old BOOLEAN;
2068BEGIN
2069 -- If we are deleting a row, we need to remove the existing
2070 -- POFileTranslator row and reinsert the historical data if it exists.
2071 -- We also treat UPDATEs that change the key (submitter) the same
2072 -- as deletes. UPDATEs that don't change these columns are treated like
2073 -- INSERTs below.
2074 IF TG_OP = 'INSERT' THEN
2075 v_trash_old := FALSE;
2076 ELSIF TG_OP = 'DELETE' THEN
2077 v_trash_old := TRUE;
2078 ELSE -- UPDATE
2079 v_trash_old = (
2080 OLD.submitter != NEW.submitter
2081 );
2082 END IF;
2083
2084 IF v_trash_old THEN
2085 -- Was this somebody's most-recently-changed message?
2086 -- If so, delete the entry for that change.
2087 DELETE FROM POFileTranslator
2088 WHERE latest_message = OLD.id;
2089 IF FOUND THEN
2090 -- We deleted the entry for somebody's latest contribution.
2091 -- Find that person's latest remaining contribution and
2092 -- create a new record for that.
2093 INSERT INTO POFileTranslator (
2094 person, pofile, latest_message, date_last_touched
2095 )
2096 SELECT DISTINCT ON (person, pofile.id)
2097 new_latest_message.submitter AS person,
2098 pofile.id,
2099 new_latest_message.id,
2100 greatest(new_latest_message.date_created,
2101 new_latest_message.date_reviewed)
2102 FROM POFile
2103 JOIN TranslationTemplateItem AS old_template_item
2104 ON OLD.potmsgset = old_template_item.potmsgset AND
2105 old_template_item.potemplate = pofile.potemplate AND
2106 pofile.language = OLD.language
2107 JOIN TranslationTemplateItem AS new_template_item
2108 ON (old_template_item.potemplate =
2109 new_template_item.potemplate)
2110 JOIN TranslationMessage AS new_latest_message
2111 ON new_latest_message.potmsgset =
2112 new_template_item.potmsgset AND
2113 new_latest_message.language = OLD.language
2114 LEFT OUTER JOIN POfileTranslator AS ExistingEntry
2115 ON ExistingEntry.person = OLD.submitter AND
2116 ExistingEntry.pofile = POFile.id
2117 WHERE
2118 new_latest_message.submitter = OLD.submitter AND
2119 ExistingEntry IS NULL
2120 ORDER BY new_latest_message.submitter, pofile.id,
2121 new_latest_message.date_created DESC,
2122 new_latest_message.id DESC;
2123 END IF;
2124
2125 -- No NEW with DELETE, so we can short circuit and leave.
2126 IF TG_OP = 'DELETE' THEN
2127 RETURN NULL; -- Ignored because this is an AFTER trigger
2128 END IF;
2129 END IF;
2130
2131 -- Standard 'upsert' loop to avoid race conditions.
2132 LOOP
2133 UPDATE POFileTranslator
2134 SET
2135 date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
2136 latest_message = NEW.id
2137 FROM POFile, TranslationTemplateItem
2138 WHERE person = NEW.submitter AND
2139 TranslationTemplateItem.potmsgset=NEW.potmsgset AND
2140 TranslationTemplateItem.potemplate=pofile.potemplate AND
2141 pofile.language=NEW.language AND
2142 POFileTranslator.pofile = pofile.id;
2143 IF found THEN
2144 RETURN NULL; -- Return value ignored as this is an AFTER trigger
2145 END IF;
2146
2147 BEGIN
2148 INSERT INTO POFileTranslator (person, pofile, latest_message)
2149 SELECT DISTINCT ON (NEW.submitter, pofile.id)
2150 NEW.submitter, pofile.id, NEW.id
2151 FROM TranslationTemplateItem
2152 JOIN POFile
2153 ON pofile.language = NEW.language AND
2154 pofile.potemplate = translationtemplateitem.potemplate
2155 WHERE
2156 TranslationTemplateItem.potmsgset = NEW.potmsgset;
2157 RETURN NULL; -- Return value ignored as this is an AFTER trigger
2158 EXCEPTION WHEN unique_violation THEN
2159 -- do nothing
2160 END;
2161 END LOOP;
2162END;
2163$$;
2164
2165
2166COMMENT ON FUNCTION mv_pofiletranslator_translationmessage() IS 'Trigger maintaining the POFileTranslator table';
2167
2168
2169CREATE FUNCTION mv_validpersonorteamcache_emailaddress() RETURNS trigger
2170 LANGUAGE plpythonu SECURITY DEFINER2150 LANGUAGE plpythonu SECURITY DEFINER
2171 AS $_$2151 AS $_$
2172 # This trigger function keeps the ValidPersonOrTeamCache materialized2152 # This trigger function keeps the ValidPersonOrTeamCache materialized
@@ -2258,10 +2238,10 @@
2258$_$;2238$_$;
22592239
22602240
2261COMMENT ON FUNCTION mv_validpersonorteamcache_emailaddress() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the EmailAddress table';2241COMMENT ON FUNCTION public.mv_validpersonorteamcache_emailaddress() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the EmailAddress table';
22622242
22632243
2264CREATE FUNCTION mv_validpersonorteamcache_person() RETURNS trigger2244CREATE FUNCTION public.mv_validpersonorteamcache_person() RETURNS trigger
2265 LANGUAGE plpythonu SECURITY DEFINER2245 LANGUAGE plpythonu SECURITY DEFINER
2266 AS $_$2246 AS $_$
2267 # This trigger function could be simplified by simply issuing2247 # This trigger function could be simplified by simply issuing
@@ -2322,12 +2302,12 @@
2322$_$;2302$_$;
23232303
23242304
2325COMMENT ON FUNCTION mv_validpersonorteamcache_person() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the Person table';2305COMMENT ON FUNCTION public.mv_validpersonorteamcache_person() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the Person table';
23262306
23272307
2328CREATE FUNCTION name_blacklist_match(text, integer) RETURNS integer2308CREATE FUNCTION public.name_blacklist_match(text, integer) RETURNS integer
2329 LANGUAGE plpythonu STABLE STRICT SECURITY DEFINER2309 LANGUAGE plpythonu STABLE STRICT SECURITY DEFINER
2330 SET search_path TO public2310 SET search_path TO 'public'
2331 AS $_$2311 AS $_$
2332 import re2312 import re
2333 name = args[0].decode("UTF-8")2313 name = args[0].decode("UTF-8")
@@ -2396,10 +2376,10 @@
2396$_$;2376$_$;
23972377
23982378
2399COMMENT 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.';2379COMMENT 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.';
24002380
24012381
2402CREATE FUNCTION null_count(p_values anyarray) RETURNS integer2382CREATE FUNCTION public.null_count(p_values anyarray) RETURNS integer
2403 LANGUAGE plpgsql IMMUTABLE STRICT2383 LANGUAGE plpgsql IMMUTABLE STRICT
2404 AS $$2384 AS $$
2405DECLARE2385DECLARE
@@ -2416,10 +2396,10 @@
2416$$;2396$$;
24172397
24182398
2419COMMENT ON FUNCTION null_count(p_values anyarray) IS 'Return the number of NULLs in the first row of the given array.';2399COMMENT ON FUNCTION public.null_count(p_values anyarray) IS 'Return the number of NULLs in the first row of the given array.';
24202400
24212401
2422CREATE FUNCTION packageset_deleted_trig() RETURNS trigger2402CREATE FUNCTION public.packageset_deleted_trig() RETURNS trigger
2423 LANGUAGE plpgsql2403 LANGUAGE plpgsql
2424 AS $$2404 AS $$
2425BEGIN2405BEGIN
@@ -2436,10 +2416,10 @@
2436$$;2416$$;
24372417
24382418
2439COMMENT ON FUNCTION packageset_deleted_trig() IS 'Remove any DAG edges leading to/from the deleted package set.';2419COMMENT ON FUNCTION public.packageset_deleted_trig() IS 'Remove any DAG edges leading to/from the deleted package set.';
24402420
24412421
2442CREATE FUNCTION packageset_inserted_trig() RETURNS trigger2422CREATE FUNCTION public.packageset_inserted_trig() RETURNS trigger
2443 LANGUAGE plpgsql2423 LANGUAGE plpgsql
2444 AS $$2424 AS $$
2445BEGIN2425BEGIN
@@ -2453,10 +2433,10 @@
2453$$;2433$$;
24542434
24552435
2456COMMENT ON FUNCTION packageset_inserted_trig() IS 'Insert self-referencing DAG edge when a new package set is inserted.';2436COMMENT ON FUNCTION public.packageset_inserted_trig() IS 'Insert self-referencing DAG edge when a new package set is inserted.';
24572437
24582438
2459CREATE FUNCTION packagesetinclusion_deleted_trig() RETURNS trigger2439CREATE FUNCTION public.packagesetinclusion_deleted_trig() RETURNS trigger
2460 LANGUAGE plpgsql2440 LANGUAGE plpgsql
2461 AS $$2441 AS $$
2462BEGIN2442BEGIN
@@ -2524,10 +2504,10 @@
2524$$;2504$$;
25252505
25262506
2527COMMENT ON FUNCTION packagesetinclusion_deleted_trig() IS 'Maintain the transitive closure in the DAG when an edge leading to/from a package set is deleted.';2507COMMENT 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.';
25282508
25292509
2530CREATE FUNCTION packagesetinclusion_inserted_trig() RETURNS trigger2510CREATE FUNCTION public.packagesetinclusion_inserted_trig() RETURNS trigger
2531 LANGUAGE plpgsql2511 LANGUAGE plpgsql
2532 AS $$2512 AS $$
2533BEGIN2513BEGIN
@@ -2598,10 +2578,10 @@
2598$$;2578$$;
25992579
26002580
2601COMMENT ON FUNCTION packagesetinclusion_inserted_trig() IS 'Maintain the transitive closure in the DAG for a newly inserted edge leading to/from a package set.';2581COMMENT 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.';
26022582
26032583
2604CREATE FUNCTION person_sort_key(displayname text, name text) RETURNS text2584CREATE FUNCTION public.person_sort_key(displayname text, name text) RETURNS text
2605 LANGUAGE plpythonu IMMUTABLE STRICT2585 LANGUAGE plpythonu IMMUTABLE STRICT
2606 AS $$2586 AS $$
2607 # NB: If this implementation is changed, the person_sort_idx needs to be2587 # NB: If this implementation is changed, the person_sort_idx needs to be
@@ -2623,32 +2603,17 @@
2623$$;2603$$;
26242604
26252605
2626COMMENT 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';2606COMMENT 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';
26272607
26282608
2629CREATE FUNCTION pgstattuple(text) RETURNS pgstattuple_type2609CREATE FUNCTION public.plpgsql_call_handler() RETURNS language_handler
2630 LANGUAGE c STRICT
2631 AS '$libdir/pgstattuple', 'pgstattuple';
2632
2633
2634CREATE FUNCTION pgstattuple(oid) RETURNS pgstattuple_type
2635 LANGUAGE c STRICT
2636 AS '$libdir/pgstattuple', 'pgstattuplebyid';
2637
2638
2639CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
2640 LANGUAGE c2610 LANGUAGE c
2641 AS '$libdir/plpgsql', 'plpgsql_call_handler';2611 AS '$libdir/plpgsql', 'plpgsql_call_handler';
26422612
26432613
2644CREATE FUNCTION plpython_call_handler() RETURNS language_handler2614CREATE FUNCTION public.questionmessage_copy_owner_from_message() RETURNS trigger
2645 LANGUAGE c
2646 AS '$libdir/plpython', 'plpython_call_handler';
2647
2648
2649CREATE FUNCTION questionmessage_copy_owner_from_message() RETURNS trigger
2650 LANGUAGE plpgsql SECURITY DEFINER2615 LANGUAGE plpgsql SECURITY DEFINER
2651 SET search_path TO public2616 SET search_path TO 'public'
2652 AS $$2617 AS $$
2653BEGIN2618BEGIN
2654 IF TG_OP = 'INSERT' THEN2619 IF TG_OP = 'INSERT' THEN
@@ -2671,12 +2636,12 @@
2671$$;2636$$;
26722637
26732638
2674COMMENT ON FUNCTION questionmessage_copy_owner_from_message() IS 'Copies the message owner into QuestionMessage when QuestionMessage changes.';2639COMMENT ON FUNCTION public.questionmessage_copy_owner_from_message() IS 'Copies the message owner into QuestionMessage when QuestionMessage changes.';
26752640
26762641
2677CREATE FUNCTION replication_lag() RETURNS interval2642CREATE FUNCTION public.replication_lag() RETURNS interval
2678 LANGUAGE plpgsql STABLE SECURITY DEFINER2643 LANGUAGE plpgsql STABLE SECURITY DEFINER
2679 SET search_path TO public2644 SET search_path TO 'public'
2680 AS $$2645 AS $$
2681 DECLARE2646 DECLARE
2682 v_lag interval;2647 v_lag interval;
@@ -2693,12 +2658,12 @@
2693$$;2658$$;
26942659
26952660
2696COMMENT 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.';2661COMMENT 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.';
26972662
26982663
2699CREATE FUNCTION replication_lag(node_id integer) RETURNS interval2664CREATE FUNCTION public.replication_lag(node_id integer) RETURNS interval
2700 LANGUAGE plpgsql STABLE SECURITY DEFINER2665 LANGUAGE plpgsql STABLE SECURITY DEFINER
2701 SET search_path TO public2666 SET search_path TO 'public'
2702 AS $$2667 AS $$
2703 DECLARE2668 DECLARE
2704 v_lag interval;2669 v_lag interval;
@@ -2717,10 +2682,10 @@
2717$$;2682$$;
27182683
27192684
2720COMMENT 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.';2685COMMENT 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.';
27212686
27222687
2723CREATE FUNCTION sane_version(text) RETURNS boolean2688CREATE FUNCTION public.sane_version(text) RETURNS boolean
2724 LANGUAGE plpythonu IMMUTABLE STRICT2689 LANGUAGE plpythonu IMMUTABLE STRICT
2725 AS $_$2690 AS $_$
2726 import re2691 import re
@@ -2733,12 +2698,12 @@
2733$_$;2698$_$;
27342699
27352700
2736COMMENT 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';2701COMMENT 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';
27372702
27382703
2739CREATE FUNCTION set_bug_date_last_message() RETURNS trigger2704CREATE FUNCTION public.set_bug_date_last_message() RETURNS trigger
2740 LANGUAGE plpgsql SECURITY DEFINER2705 LANGUAGE plpgsql SECURITY DEFINER
2741 SET search_path TO public2706 SET search_path TO 'public'
2742 AS $$2707 AS $$
2743BEGIN2708BEGIN
2744 IF TG_OP = 'INSERT' THEN2709 IF TG_OP = 'INSERT' THEN
@@ -2763,10 +2728,10 @@
2763$$;2728$$;
27642729
27652730
2766COMMENT ON FUNCTION set_bug_date_last_message() IS 'AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column';2731COMMENT ON FUNCTION public.set_bug_date_last_message() IS 'AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column';
27672732
27682733
2769CREATE FUNCTION set_bug_message_count() RETURNS trigger2734CREATE FUNCTION public.set_bug_message_count() RETURNS trigger
2770 LANGUAGE plpgsql2735 LANGUAGE plpgsql
2771 AS $$2736 AS $$
2772BEGIN2737BEGIN
@@ -2791,10 +2756,10 @@
2791$$;2756$$;
27922757
27932758
2794COMMENT ON FUNCTION set_bug_message_count() IS 'AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column';2759COMMENT ON FUNCTION public.set_bug_message_count() IS 'AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column';
27952760
27962761
2797CREATE FUNCTION set_bug_number_of_duplicates() RETURNS trigger2762CREATE FUNCTION public.set_bug_number_of_duplicates() RETURNS trigger
2798 LANGUAGE plpgsql2763 LANGUAGE plpgsql
2799 AS $$2764 AS $$
2800BEGIN2765BEGIN
@@ -2826,10 +2791,10 @@
2826$$;2791$$;
28272792
28282793
2829COMMENT ON FUNCTION set_bug_number_of_duplicates() IS 'AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column';2794COMMENT ON FUNCTION public.set_bug_number_of_duplicates() IS 'AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column';
28302795
28312796
2832CREATE FUNCTION set_bug_users_affected_count() RETURNS trigger2797CREATE FUNCTION public.set_bug_users_affected_count() RETURNS trigger
2833 LANGUAGE plpgsql2798 LANGUAGE plpgsql
2834 AS $$2799 AS $$
2835BEGIN2800BEGIN
@@ -2878,7 +2843,7 @@
2878$$;2843$$;
28792844
28802845
2881CREATE FUNCTION set_bugtask_date_milestone_set() RETURNS trigger2846CREATE FUNCTION public.set_bugtask_date_milestone_set() RETURNS trigger
2882 LANGUAGE plpgsql2847 LANGUAGE plpgsql
2883 AS $$2848 AS $$
2884BEGIN2849BEGIN
@@ -2924,10 +2889,10 @@
2924$$;2889$$;
29252890
29262891
2927COMMENT ON FUNCTION set_bugtask_date_milestone_set() IS 'Update BugTask.date_milestone_set when BugTask.milestone is changed.';2892COMMENT ON FUNCTION public.set_bugtask_date_milestone_set() IS 'Update BugTask.date_milestone_set when BugTask.milestone is changed.';
29282893
29292894
2930CREATE FUNCTION set_date_status_set() RETURNS trigger2895CREATE FUNCTION public.set_date_status_set() RETURNS trigger
2931 LANGUAGE plpgsql2896 LANGUAGE plpgsql
2932 AS $$2897 AS $$
2933BEGIN2898BEGIN
@@ -2939,74 +2904,10 @@
2939$$;2904$$;
29402905
29412906
2942COMMENT ON FUNCTION set_date_status_set() IS 'BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.';2907COMMENT ON FUNCTION public.set_date_status_set() IS 'BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.';
29432908
29442909
2945CREATE FUNCTION set_openid_identifier() RETURNS trigger2910CREATE FUNCTION public.sha1(text) RETURNS character
2946 LANGUAGE plpythonu
2947 AS $$
2948 # If someone is trying to explicitly set the openid_identifier, let them.
2949 # This also causes openid_identifiers to be left alone if this is an
2950 # UPDATE trigger.
2951 if TD['new']['openid_identifier'] is not None:
2952 return None
2953
2954 from random import choice
2955
2956 # Non display confusing characters
2957 chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
2958
2959 # character length of tokens. Can be increased, decreased or even made
2960 # random - Launchpad does not care. 7 means it takes 40 bytes to store
2961 # a null-terminated Launchpad identity URL on the current domain name.
2962 length=7
2963
2964 loop_count = 0
2965 while loop_count < 20000:
2966 # Generate a random openid_identifier
2967 oid = ''.join(choice(chars) for count in range(length))
2968
2969 # Check if the oid is already in the db, although this is pretty
2970 # unlikely
2971 rv = plpy.execute("""
2972 SELECT COUNT(*) AS num FROM Person WHERE openid_identifier = '%s'
2973 """ % oid, 1)
2974 if rv[0]['num'] == 0:
2975 TD['new']['openid_identifier'] = oid
2976 return "MODIFY"
2977 loop_count += 1
2978 if loop_count == 1:
2979 plpy.warning(
2980 'Clash generating unique openid_identifier. '
2981 'Increase length if you see this warning too much.')
2982 plpy.error(
2983 "Unable to generate unique openid_identifier. "
2984 "Need to increase length of tokens.")
2985$$;
2986
2987
2988CREATE FUNCTION set_shipit_normalized_address() RETURNS trigger
2989 LANGUAGE plpgsql
2990 AS $$
2991 BEGIN
2992 NEW.normalized_address =
2993 lower(
2994 -- Strip off everything that's not alphanumeric
2995 -- characters.
2996 regexp_replace(
2997 coalesce(NEW.addressline1, '') || ' ' ||
2998 coalesce(NEW.addressline2, '') || ' ' ||
2999 coalesce(NEW.city, ''),
3000 '[^a-zA-Z0-9]+', '', 'g'));
3001 RETURN NEW;
3002 END;
3003$$;
3004
3005
3006COMMENT ON FUNCTION set_shipit_normalized_address() IS 'Store a normalized concatenation of the request''s address into the normalized_address column.';
3007
3008
3009CREATE FUNCTION sha1(text) RETURNS character
3010 LANGUAGE plpythonu IMMUTABLE STRICT2911 LANGUAGE plpythonu IMMUTABLE STRICT
3011 AS $$2912 AS $$
3012 import hashlib2913 import hashlib
@@ -3014,56 +2915,63 @@
3014$$;2915$$;
30152916
30162917
3017COMMENT ON FUNCTION sha1(text) IS 'Return the SHA1 one way cryptographic hash as a string of 40 hex digits';2918COMMENT ON FUNCTION public.sha1(text) IS 'Return the SHA1 one way cryptographic hash as a string of 40 hex digits';
30182919
30192920
3020CREATE FUNCTION summarise_bug(bug_row bug) RETURNS void2921CREATE FUNCTION public.specification_denorm_access(spec_id integer) RETURNS void
3021 LANGUAGE plpgsql2922 LANGUAGE sql SECURITY DEFINER
3022 AS $$2923 SET search_path TO 'public'
3023DECLARE2924 AS $_$
3024 d bugsummary%ROWTYPE;2925 UPDATE specification
3025BEGIN2926 SET access_policy = policies[1], access_grants = grants
3026 PERFORM ensure_bugsummary_temp_journal();2927 FROM
3027 FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP2928 build_access_cache(
3028 d.count = 1;2929 (SELECT id FROM accessartifact WHERE specification = $1),
3029 PERFORM bug_summary_temp_journal_ins(d);2930 (SELECT information_type FROM specification WHERE id = $1))
3030 END LOOP;2931 AS (policies integer[], grants integer[])
3031END;2932 WHERE id = $1;
3032$$;2933$_$;
30332934
30342935
3035COMMENT ON FUNCTION summarise_bug(bug_row bug) IS 'AFTER summarise a bug row into bugsummary.';2936CREATE FUNCTION public.specification_maintain_access_cache_trig() RETURNS trigger
30362937 LANGUAGE plpgsql
30372938 AS $$
3038CREATE FUNCTION ulower(text) RETURNS text2939BEGIN
2940 PERFORM specification_denorm_access(NEW.id);
2941 RETURN NULL;
2942END;
2943$$;
2944
2945
2946CREATE FUNCTION public.summarise_bug(bug integer) RETURNS void
2947 LANGUAGE plpgsql
2948 AS $$
2949BEGIN
2950 PERFORM bugsummary_journal_bug(bug_row(bug), 1);
2951END;
2952$$;
2953
2954
2955CREATE FUNCTION public.ulower(text) RETURNS text
3039 LANGUAGE plpythonu IMMUTABLE STRICT2956 LANGUAGE plpythonu IMMUTABLE STRICT
3040 AS $$2957 AS $$
3041 return args[0].decode('utf8').lower().encode('utf8')2958 return args[0].decode('utf8').lower().encode('utf8')
3042$$;2959$$;
30432960
30442961
3045COMMENT ON FUNCTION ulower(text) IS 'Return the lower case version of a UTF-8 encoded string.';2962COMMENT ON FUNCTION public.ulower(text) IS 'Return the lower case version of a UTF-8 encoded string.';
30462963
30472964
3048CREATE FUNCTION unsummarise_bug(bug_row bug) RETURNS void2965CREATE FUNCTION public.unsummarise_bug(bug integer) RETURNS void
3049 LANGUAGE plpgsql2966 LANGUAGE plpgsql
3050 AS $$2967 AS $$
3051DECLARE
3052 d bugsummary%ROWTYPE;
3053BEGIN2968BEGIN
3054 PERFORM ensure_bugsummary_temp_journal();2969 PERFORM bugsummary_journal_bug(bug_row(bug), -1);
3055 FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
3056 d.count = -1;
3057 PERFORM bug_summary_temp_journal_ins(d);
3058 END LOOP;
3059END;2970END;
3060$$;2971$$;
30612972
30622973
3063COMMENT ON FUNCTION unsummarise_bug(bug_row bug) IS 'AFTER unsummarise a bug row from bugsummary.';2974CREATE FUNCTION public.update_branch_name_cache() RETURNS trigger
3064
3065
3066CREATE FUNCTION update_branch_name_cache() RETURNS trigger
3067 LANGUAGE plpgsql2975 LANGUAGE plpgsql
3068 AS $$2976 AS $$
3069DECLARE2977DECLARE
@@ -3075,7 +2983,7 @@
3075 OR NEW.unique_name IS NULL2983 OR NEW.unique_name IS NULL
3076 OR OLD.owner_name <> NEW.owner_name2984 OR OLD.owner_name <> NEW.owner_name
3077 OR OLD.unique_name <> NEW.unique_name2985 OR OLD.unique_name <> NEW.unique_name
3078 OR (NEW.target_suffix IS NULL <> OLD.target_suffix IS NULL)2986 OR ((NEW.target_suffix IS NULL) <> (OLD.target_suffix IS NULL))
3079 OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')2987 OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')
3080 OR OLD.name <> NEW.name2988 OR OLD.name <> NEW.name
3081 OR OLD.owner <> NEW.owner2989 OR OLD.owner <> NEW.owner
@@ -3111,12 +3019,12 @@
3111$$;3019$$;
31123020
31133021
3114COMMENT ON FUNCTION update_branch_name_cache() IS 'Maintain the cached name columns in Branch.';3022COMMENT ON FUNCTION public.update_branch_name_cache() IS 'Maintain the cached name columns in Branch.';
31153023
31163024
3117CREATE FUNCTION update_database_disk_utilization() RETURNS void3025CREATE FUNCTION public.update_database_disk_utilization() RETURNS void
3118 LANGUAGE sql SECURITY DEFINER3026 LANGUAGE sql SECURITY DEFINER
3119 SET search_path TO public3027 SET search_path TO 'public'
3120 AS $$3028 AS $$
3121 INSERT INTO DatabaseDiskUtilization3029 INSERT INTO DatabaseDiskUtilization
3122 SELECT3030 SELECT
@@ -3136,6 +3044,7 @@
3136 (stat).free_space,3044 (stat).free_space,
3137 (stat).free_percent3045 (stat).free_percent
3138 FROM (3046 FROM (
3047 -- Tables
3139 SELECT3048 SELECT
3140 pg_namespace.nspname AS namespace,3049 pg_namespace.nspname AS namespace,
3141 pg_class.relname AS name,3050 pg_class.relname AS name,
@@ -3151,6 +3060,7 @@
31513060
3152 UNION ALL3061 UNION ALL
3153 3062
3063 -- Indexes
3154 SELECT3064 SELECT
3155 pg_namespace_table.nspname AS namespace,3065 pg_namespace_table.nspname AS namespace,
3156 pg_class_table.relname AS name,3066 pg_class_table.relname AS name,
@@ -3163,12 +3073,15 @@
3163 pg_namespace AS pg_namespace_index,3073 pg_namespace AS pg_namespace_index,
3164 pg_class AS pg_class_table,3074 pg_class AS pg_class_table,
3165 pg_class AS pg_class_index,3075 pg_class AS pg_class_index,
3166 pg_index3076 pg_index,
3077 pg_am
3167 WHERE3078 WHERE
3168 pg_class_index.relkind = 'i'3079 pg_class_index.relkind = 'i'
3080 AND pg_am.amname <> 'gin' -- pgstattuple doesn't support GIN
3169 AND pg_table_is_visible(pg_class_table.oid)3081 AND pg_table_is_visible(pg_class_table.oid)
3170 AND pg_class_index.relnamespace = pg_namespace_index.oid3082 AND pg_class_index.relnamespace = pg_namespace_index.oid
3171 AND pg_class_table.relnamespace = pg_namespace_table.oid3083 AND pg_class_table.relnamespace = pg_namespace_table.oid
3084 AND pg_class_index.relam = pg_am.oid
3172 AND pg_index.indexrelid = pg_class_index.oid3085 AND pg_index.indexrelid = pg_class_index.oid
3173 AND pg_index.indrelid = pg_class_table.oid3086 AND pg_index.indrelid = pg_class_table.oid
31743087
@@ -3208,20 +3121,22 @@
3208 pg_namespace AS pg_namespace_index,3121 pg_namespace AS pg_namespace_index,
3209 pg_class AS pg_class_table,3122 pg_class AS pg_class_table,
3210 pg_class AS pg_class_index,3123 pg_class AS pg_class_index,
3211 pg_class AS pg_class_toast3124 pg_class AS pg_class_toast,
3125 pg_index
3212 WHERE3126 WHERE
3213 pg_class_table.relnamespace = pg_namespace_table.oid3127 pg_class_table.relnamespace = pg_namespace_table.oid
3214 AND pg_table_is_visible(pg_class_table.oid)3128 AND pg_table_is_visible(pg_class_table.oid)
3215 AND pg_class_index.relnamespace = pg_namespace_index.oid3129 AND pg_class_index.relnamespace = pg_namespace_index.oid
3216 AND pg_class_table.reltoastrelid = pg_class_toast.oid3130 AND pg_class_table.reltoastrelid = pg_class_toast.oid
3217 AND pg_class_index.oid = pg_class_toast.reltoastidxid3131 AND pg_class_index.oid = pg_index.indexrelid
3132 AND pg_index.indrelid = pg_class_toast.oid
3218 ) AS whatever;3133 ) AS whatever;
3219$$;3134$$;
32203135
32213136
3222CREATE FUNCTION update_database_stats() RETURNS void3137CREATE FUNCTION public.update_database_stats() RETURNS void
3223 LANGUAGE plpythonu SECURITY DEFINER3138 LANGUAGE plpythonu SECURITY DEFINER
3224 SET search_path TO public3139 SET search_path TO 'public'
3225 AS $_$3140 AS $_$
3226 import re3141 import re
3227 import subprocess3142 import subprocess
@@ -3306,12 +3221,12 @@
3306$_$;3221$_$;
33073222
33083223
3309COMMENT 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.';3224COMMENT 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.';
33103225
33113226
3312CREATE FUNCTION update_replication_lag_cache() RETURNS boolean3227CREATE FUNCTION public.update_replication_lag_cache() RETURNS boolean
3313 LANGUAGE plpgsql SECURITY DEFINER3228 LANGUAGE plpgsql SECURITY DEFINER
3314 SET search_path TO public3229 SET search_path TO 'public'
3315 AS $$3230 AS $$
3316 BEGIN3231 BEGIN
3317 DELETE FROM DatabaseReplicationLag;3232 DELETE FROM DatabaseReplicationLag;
@@ -3329,64 +3244,10 @@
3329$$;3244$$;
33303245
33313246
3332COMMENT ON FUNCTION update_replication_lag_cache() IS 'Updates the DatabaseReplicationLag materialized view.';3247COMMENT ON FUNCTION public.update_replication_lag_cache() IS 'Updates the DatabaseReplicationLag materialized view.';
33333248
33343249
3335CREATE FUNCTION update_transitively_private(start_branch integer, _root_branch integer DEFAULT NULL::integer, _root_transitively_private boolean DEFAULT NULL::boolean) RETURNS void3250CREATE FUNCTION public.valid_absolute_url(text) RETURNS boolean
3336 LANGUAGE plpgsql SECURITY DEFINER
3337 SET search_path TO public
3338 AS $$
3339DECLARE
3340 root_transitively_private boolean := _root_transitively_private;
3341 root_branch int := _root_branch;
3342BEGIN
3343 IF root_transitively_private IS NULL THEN
3344 -- We can't just trust the transitively_private flag of the
3345 -- branch we are stacked on, as if we are updating multiple
3346 -- records they will be updated in an indeterminate order.
3347 -- We need a recursive query.
3348 UPDATE Branch SET transitively_private = (
3349 WITH RECURSIVE stacked_branches AS (
3350 SELECT
3351 top_branch.id, top_branch.stacked_on, top_branch.private
3352 FROM Branch AS top_branch
3353 WHERE top_branch.id = start_branch
3354 UNION ALL
3355 SELECT
3356 sub_branch.id, sub_branch.stacked_on, sub_branch.private
3357 FROM stacked_branches, Branch AS sub_branch
3358 WHERE
3359 stacked_branches.stacked_on = sub_branch.id
3360 AND stacked_branches.stacked_on != start_branch
3361 -- Shortcircuit. No need to recurse if already private.
3362 AND stacked_branches.private IS FALSE
3363 )
3364 SELECT COUNT(*) > 0
3365 FROM stacked_branches
3366 WHERE private IS TRUE)
3367 WHERE Branch.id = start_branch
3368 RETURNING transitively_private INTO root_transitively_private;
3369 root_branch := start_branch;
3370 ELSE
3371 -- Now we have calculated the correct transitively_private flag
3372 -- we can trust it.
3373 UPDATE Branch SET
3374 transitively_private = GREATEST(private, root_transitively_private)
3375 WHERE id = root_branch;
3376 END IF;
3377
3378 -- Recurse to branches stacked on this one.
3379 PERFORM update_transitively_private(
3380 start_branch, id, GREATEST(private, root_transitively_private))
3381 FROM Branch WHERE stacked_on = root_branch AND id != start_branch;
3382END;
3383$$;
3384
3385
3386COMMENT 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.';
3387
3388
3389CREATE FUNCTION valid_absolute_url(text) RETURNS boolean
3390 LANGUAGE plpythonu IMMUTABLE STRICT3251 LANGUAGE plpythonu IMMUTABLE STRICT
3391 AS $$3252 AS $$
3392 from urlparse import urlparse, uses_netloc3253 from urlparse import urlparse, uses_netloc
@@ -3403,10 +3264,10 @@
3403$$;3264$$;
34043265
34053266
3406COMMENT ON FUNCTION valid_absolute_url(text) IS 'Ensure the given test is a valid absolute URL, containing both protocol and network location';3267COMMENT ON FUNCTION public.valid_absolute_url(text) IS 'Ensure the given test is a valid absolute URL, containing both protocol and network location';
34073268
34083269
3409CREATE FUNCTION valid_branch_name(text) RETURNS boolean3270CREATE FUNCTION public.valid_branch_name(text) RETURNS boolean
3410 LANGUAGE plpythonu IMMUTABLE STRICT3271 LANGUAGE plpythonu IMMUTABLE STRICT
3411 AS $$3272 AS $$
3412 import re3273 import re
@@ -3418,27 +3279,27 @@
3418$$;3279$$;
34193280
34203281
3421COMMENT ON FUNCTION valid_branch_name(text) IS 'validate a branch name.3282COMMENT ON FUNCTION public.valid_branch_name(text) IS 'validate a branch name.
34223283
3423 As per valid_name, except we allow uppercase and @';3284 As per valid_name, except we allow uppercase and @';
34243285
34253286
3426CREATE FUNCTION valid_cve(text) RETURNS boolean3287CREATE FUNCTION public.valid_cve(text) RETURNS boolean
3427 LANGUAGE plpythonu IMMUTABLE STRICT3288 LANGUAGE plpythonu IMMUTABLE STRICT
3428 AS $_$3289 AS $_$
3429 import re3290 import re
3430 name = args[0]3291 name = args[0]
3431 pat = r"^(19|20)\d{2}-\d{4}$"3292 pat = r"^(19|20)\d{2}-\d{4,}$"
3432 if re.match(pat, name):3293 if re.match(pat, name):
3433 return 13294 return 1
3434 return 03295 return 0
3435$_$;3296$_$;
34363297
34373298
3438COMMENT ON FUNCTION valid_cve(text) IS 'validate a common vulnerability number as defined on www.cve.mitre.org, minus the CAN- or CVE- prefix.';3299COMMENT 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.';
34393300
34403301
3441CREATE FUNCTION valid_debian_version(text) RETURNS boolean3302CREATE FUNCTION public.valid_debian_version(text) RETURNS boolean
3442 LANGUAGE plpythonu IMMUTABLE STRICT3303 LANGUAGE plpythonu IMMUTABLE STRICT
3443 AS $_$3304 AS $_$
3444 import re3305 import re
@@ -3462,10 +3323,10 @@
3462$_$;3323$_$;
34633324
34643325
3465COMMENT ON FUNCTION valid_debian_version(text) IS 'validate a version number as per Debian Policy';3326COMMENT ON FUNCTION public.valid_debian_version(text) IS 'validate a version number as per Debian Policy';
34663327
34673328
3468CREATE FUNCTION valid_fingerprint(text) RETURNS boolean3329CREATE FUNCTION public.valid_fingerprint(text) RETURNS boolean
3469 LANGUAGE plpythonu IMMUTABLE STRICT3330 LANGUAGE plpythonu IMMUTABLE STRICT
3470 AS $$3331 AS $$
3471 import re3332 import re
@@ -3476,10 +3337,27 @@
3476$$;3337$$;
34773338
34783339
3479COMMENT 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.';3340COMMENT 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.';
34803341
34813342
3482CREATE FUNCTION valid_keyid(text) RETURNS boolean3343CREATE FUNCTION public.valid_git_repository_name(text) RETURNS boolean
3344 LANGUAGE plpythonu IMMUTABLE STRICT
3345 AS $$
3346 import re
3347 name = args[0]
3348 pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
3349 if not name.endswith(".git") and re.match(pat, name):
3350 return 1
3351 return 0
3352$$;
3353
3354
3355COMMENT ON FUNCTION public.valid_git_repository_name(text) IS 'validate a Git repository name.
3356
3357 As per valid_branch_name, except we disallow names ending in ".git".';
3358
3359
3360CREATE FUNCTION public.valid_keyid(text) RETURNS boolean
3483 LANGUAGE plpythonu IMMUTABLE STRICT3361 LANGUAGE plpythonu IMMUTABLE STRICT
3484 AS $$3362 AS $$
3485 import re3363 import re
@@ -3490,10 +3368,10 @@
3490$$;3368$$;
34913369
34923370
3493COMMENT 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.';3371COMMENT 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.';
34943372
34953373
3496CREATE FUNCTION valid_regexp(text) RETURNS boolean3374CREATE FUNCTION public.valid_regexp(text) RETURNS boolean
3497 LANGUAGE plpythonu IMMUTABLE STRICT3375 LANGUAGE plpythonu IMMUTABLE STRICT
3498 AS $$3376 AS $$
3499 import re3377 import re
@@ -3506,10 +3384,10 @@
3506$$;3384$$;
35073385
35083386
3509COMMENT ON FUNCTION valid_regexp(text) IS 'Returns true if the input can be compiled as a regular expression.';3387COMMENT ON FUNCTION public.valid_regexp(text) IS 'Returns true if the input can be compiled as a regular expression.';
35103388
35113389
3512CREATE FUNCTION version_sort_key(version text) RETURNS text3390CREATE FUNCTION public.version_sort_key(version text) RETURNS text
3513 LANGUAGE plpythonu IMMUTABLE STRICT3391 LANGUAGE plpythonu IMMUTABLE STRICT
3514 AS $$3392 AS $$
3515 # If this method is altered, then any functional indexes using it3393 # If this method is altered, then any functional indexes using it
@@ -3529,10 +3407,10 @@
3529$$;3407$$;
35303408
35313409
3532COMMENT 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].';3410COMMENT 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].';
35333411
35343412
3535CREATE FUNCTION you_are_your_own_member() RETURNS trigger3413CREATE FUNCTION public.you_are_your_own_member() RETURNS trigger
3536 LANGUAGE plpgsql3414 LANGUAGE plpgsql
3537 AS $$3415 AS $$
3538 BEGIN3416 BEGIN
@@ -3543,873 +3421,116 @@
3543$$;3421$$;
35443422
35453423
3546COMMENT 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';3424COMMENT 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';
35473425
35483426
3549SET search_path = ts2, pg_catalog;3427CREATE OPERATOR public.> (
35503428 PROCEDURE = public.debversion_gt,
3551CREATE FUNCTION _ftq(text) RETURNS text3429 LEFTARG = public.debversion,
3552 LANGUAGE plpythonu IMMUTABLE STRICT3430 RIGHTARG = public.debversion,
3553 AS $_$3431 COMMUTATOR = OPERATOR(public.<),
3554 import re3432 NEGATOR = OPERATOR(public.>=)
35553433);
3556 # I think this method would be more robust if we used a real3434
3557 # tokenizer and parser to generate the query string, but we need3435
3558 # something suitable for use as a stored procedure which currently3436COMMENT ON OPERATOR public.> (public.debversion, public.debversion) IS 'debversion greater-than';
3559 # means no external dependancies.3437
35603438
3561 # Convert to Unicode3439CREATE AGGREGATE public.max(public.debversion) (
3562 query = args[0].decode('utf8')3440 SFUNC = public.debversion_larger,
3563 ## plpy.debug('1 query is %s' % repr(query))3441 STYPE = public.debversion,
35643442 SORTOP = OPERATOR(public.>)
3565 # Normalize whitespace3443);
3566 query = re.sub("(?u)\s+"," ", query)3444
35673445
3568 # Convert AND, OR, NOT and - to tsearch2 punctuation3446CREATE OPERATOR public.< (
3569 query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)3447 PROCEDURE = public.debversion_lt,
3570 query = re.sub(r"(?u)\bAND\b", "&", query)3448 LEFTARG = public.debversion,
3571 query = re.sub(r"(?u)\bOR\b", "|", query)3449 RIGHTARG = public.debversion,
3572 query = re.sub(r"(?u)\bNOT\b", " !", query)3450 COMMUTATOR = OPERATOR(public.>),
3573 ## plpy.debug('2 query is %s' % repr(query))3451 NEGATOR = OPERATOR(public.>=)
35743452);
3575 # Deal with unwanted punctuation. We convert strings of punctuation3453
3576 # inside words to a '-' character for the hypenation handling below3454
3577 # to deal with further. Outside of words we replace with whitespace.3455COMMENT ON OPERATOR public.< (public.debversion, public.debversion) IS 'debversion less-than';
3578 # We don't mess with -&|!()' as they are handled later.3456
3579 #punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')3457
3580 punctuation = r"[^\w\s\-\&\|\!\(\)']"3458CREATE AGGREGATE public.min(public.debversion) (
3581 query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)3459 SFUNC = public.debversion_smaller,
3582 query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)3460 STYPE = public.debversion,
3583 ## plpy.debug('3 query is %s' % repr(query))3461 SORTOP = OPERATOR(public.<)
35843462);
3585 # Strip ! characters inside and at the end of a word3463
3586 query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)3464
35873465CREATE OPERATOR public.<= (
3588 # Now that we have handle case sensitive booleans, convert to lowercase3466 PROCEDURE = public.debversion_le,
3589 query = query.lower()3467 LEFTARG = public.debversion,
35903468 RIGHTARG = public.debversion,
3591 # Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to3469 COMMUTATOR = OPERATOR(public.>=),
3592 # ((foo&bar&baz)|foobarbaz)3470 NEGATOR = OPERATOR(public.>)
3593 def hyphen_repl(match):3471);
3594 bits = match.group(0).split("-")3472
3595 return "((%s)|%s)" % ("&".join(bits), "".join(bits))3473
3596 query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)3474COMMENT ON OPERATOR public.<= (public.debversion, public.debversion) IS 'debversion less-than-or-equal';
3597 ## plpy.debug('4 query is %s' % repr(query))3475
35983476
3599 # Any remaining - characters are spurious3477CREATE OPERATOR public.<> (
3600 query = query.replace('-','')3478 PROCEDURE = public.debversion_ne,
36013479 LEFTARG = public.debversion,
3602 # Remove unpartnered bracket on the left and right3480 RIGHTARG = public.debversion,
3603 query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)3481 COMMUTATOR = OPERATOR(public.<>),
3604 query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)3482 NEGATOR = OPERATOR(public.=)
36053483);
3606 # Remove spurious brackets3484
3607 query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)3485
3608 ## plpy.debug('5 query is %s' % repr(query))3486COMMENT ON OPERATOR public.<> (public.debversion, public.debversion) IS 'debversion not equal';
36093487
3610 # Insert & between tokens without an existing boolean operator3488
3611 # ( not proceeded by (|&!3489CREATE OPERATOR public.= (
3612 query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)3490 PROCEDURE = public.debversion_eq,
3613 ## plpy.debug('6 query is %s' % repr(query))3491 LEFTARG = public.debversion,
3614 # ) not followed by )|&3492 RIGHTARG = public.debversion,
3615 query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)3493 COMMUTATOR = OPERATOR(public.=),
3616 ## plpy.debug('6.1 query is %s' % repr(query))3494 NEGATOR = OPERATOR(public.<>)
3617 # Whitespace not proceded by (|&! not followed by &|3495);
3618 query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)3496
3619 ## plpy.debug('7 query is %s' % repr(query))3497
36203498COMMENT ON OPERATOR public.= (public.debversion, public.debversion) IS 'debversion equal';
3621 # Detect and repair syntax errors - we are lenient because3499
3622 # this input is generally from users.3500
36233501CREATE OPERATOR public.>= (
3624 # Fix unbalanced brackets3502 PROCEDURE = public.debversion_ge,
3625 openings = query.count("(")3503 LEFTARG = public.debversion,
3626 closings = query.count(")")3504 RIGHTARG = public.debversion,
3627 if openings > closings:3505 COMMUTATOR = OPERATOR(public.<=),
3628 query = query + " ) "*(openings-closings)3506 NEGATOR = OPERATOR(public.<)
3629 elif closings > openings:3507);
3630 query = " ( "*(closings-openings) + query3508
3631 ## plpy.debug('8 query is %s' % repr(query))3509
36323510COMMENT ON OPERATOR public.>= (public.debversion, public.debversion) IS 'debversion greater-than-or-equal';
3633 # Strip ' character that do not have letters on both sides3511
3634 query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)3512
36353513CREATE OPERATOR FAMILY public.debversion_ops USING btree;
3636 # Brackets containing nothing but whitespace and booleans, recursive3514
3637 last = ""3515
3638 while last != query:3516CREATE OPERATOR CLASS public.debversion_ops
3639 last = query3517 DEFAULT FOR TYPE public.debversion USING btree FAMILY public.debversion_ops AS
3640 query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)3518 OPERATOR 1 public.<(public.debversion,public.debversion) ,
3641 ## plpy.debug('9 query is %s' % repr(query))3519 OPERATOR 2 public.<=(public.debversion,public.debversion) ,
3642
3643 # An & or | following a (
3644 query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
3645 ## plpy.debug('10 query is %s' % repr(query))
3646
3647 # An &, | or ! immediatly before a )
3648 query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
3649 ## plpy.debug('11 query is %s' % repr(query))
3650
3651 # An &,| or ! followed by another boolean.
3652 query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
3653 ## plpy.debug('12 query is %s' % repr(query))
3654
3655 # Leading & or |
3656 query = re.sub(r"(?u)^[\s\&\|]+", "", query)
3657 ## plpy.debug('13 query is %s' % repr(query))
3658
3659 # Trailing &, | or !
3660 query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
3661 ## plpy.debug('14 query is %s' % repr(query))
3662
3663 # If we have nothing but whitespace and tsearch2 operators,
3664 # return NULL.
3665 if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
3666 return None
3667
3668 # Convert back to UTF-8
3669 query = query.encode('utf8')
3670 ## plpy.debug('15 query is %s' % repr(query))
3671
3672 return query or None
3673 $_$;
3674
3675
3676CREATE FUNCTION _get_parser_from_curcfg() RETURNS text
3677 LANGUAGE sql IMMUTABLE STRICT
3678 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();$$;
3679
3680
3681CREATE FUNCTION concat(pg_catalog.tsvector, pg_catalog.tsvector) RETURNS pg_catalog.tsvector
3682 LANGUAGE internal IMMUTABLE STRICT
3683 AS $$tsvector_concat$$;
3684
3685
3686CREATE FUNCTION dex_init(internal) RETURNS internal
3687 LANGUAGE c
3688 AS '$libdir/tsearch2', 'tsa_dex_init';
3689
3690
3691CREATE FUNCTION dex_lexize(internal, internal, integer) RETURNS internal
3692 LANGUAGE c STRICT
3693 AS '$libdir/tsearch2', 'tsa_dex_lexize';
3694
3695
3696CREATE FUNCTION ftiupdate() RETURNS trigger
3697 LANGUAGE plpythonu
3698 AS $_$
3699 new = TD["new"]
3700 args = TD["args"][:]
3701
3702 # Short circuit if none of the relevant columns have been
3703 # modified and fti is not being set to NULL (setting the fti
3704 # column to NULL is thus how we can force a rebuild of the fti
3705 # column).
3706 if TD["event"] == "UPDATE" and new["fti"] != None:
3707 old = TD["old"]
3708 relevant_modification = False
3709 for column_name in args[::2]:
3710 if new[column_name] != old[column_name]:
3711 relevant_modification = True
3712 break
3713 if not relevant_modification:
3714 return "OK"
3715
3716 # Generate an SQL statement that turns the requested
3717 # column values into a weighted tsvector
3718 sql = []
3719 for i in range(0, len(args), 2):
3720 sql.append(
3721 "ts2.setweight(ts2.to_tsvector('default', coalesce("
3722 "substring(ltrim($%d) from 1 for 2500),'')),"
3723 "CAST($%d AS \"char\"))" % (i + 1, i + 2))
3724 args[i] = new[args[i]]
3725
3726 sql = "SELECT %s AS fti" % "||".join(sql)
3727
3728 # Execute and store in the fti column
3729 plan = plpy.prepare(sql, ["text", "char"] * (len(args)/2))
3730 new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
3731
3732 # Tell PostgreSQL we have modified the data
3733 return "MODIFY"
3734$_$;
3735
3736
3737COMMENT ON FUNCTION ftiupdate() IS 'Trigger function that keeps the fti tsvector column up to date.';
3738
3739
3740CREATE FUNCTION ftq(text) RETURNS pg_catalog.tsquery
3741 LANGUAGE plpythonu IMMUTABLE STRICT
3742 AS $_$
3743 import re
3744
3745 # I think this method would be more robust if we used a real
3746 # tokenizer and parser to generate the query string, but we need
3747 # something suitable for use as a stored procedure which currently
3748 # means no external dependancies.
3749
3750 # Convert to Unicode
3751 query = args[0].decode('utf8')
3752 ## plpy.debug('1 query is %s' % repr(query))
3753
3754 # Normalize whitespace
3755 query = re.sub("(?u)\s+"," ", query)
3756
3757 # Convert AND, OR, NOT and - to tsearch2 punctuation
3758 query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
3759 query = re.sub(r"(?u)\bAND\b", "&", query)
3760 query = re.sub(r"(?u)\bOR\b", "|", query)
3761 query = re.sub(r"(?u)\bNOT\b", " !", query)
3762 ## plpy.debug('2 query is %s' % repr(query))
3763
3764 # Deal with unwanted punctuation. We convert strings of punctuation
3765 # inside words to a '-' character for the hypenation handling below
3766 # to deal with further. Outside of words we replace with whitespace.
3767 # We don't mess with -&|!()' as they are handled later.
3768 #punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
3769 punctuation = r"[^\w\s\-\&\|\!\(\)']"
3770 query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
3771 query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
3772 ## plpy.debug('3 query is %s' % repr(query))
3773
3774 # Strip ! characters inside and at the end of a word
3775 query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
3776
3777 # Now that we have handle case sensitive booleans, convert to lowercase
3778 query = query.lower()
3779
3780 # Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
3781 # ((foo&bar&baz)|foobarbaz)
3782 def hyphen_repl(match):
3783 bits = match.group(0).split("-")
3784 return "((%s)|%s)" % ("&".join(bits), "".join(bits))
3785 query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
3786 ## plpy.debug('4 query is %s' % repr(query))
3787
3788 # Any remaining - characters are spurious
3789 query = query.replace('-','')
3790
3791 # Remove unpartnered bracket on the left and right
3792 query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
3793 query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
3794
3795 # Remove spurious brackets
3796 query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
3797 ## plpy.debug('5 query is %s' % repr(query))
3798
3799 # Insert & between tokens without an existing boolean operator
3800 # ( not proceeded by (|&!
3801 query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
3802 ## plpy.debug('6 query is %s' % repr(query))
3803 # ) not followed by )|&
3804 query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
3805 ## plpy.debug('6.1 query is %s' % repr(query))
3806 # Whitespace not proceded by (|&! not followed by &|
3807 query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
3808 ## plpy.debug('7 query is %s' % repr(query))
3809
3810 # Detect and repair syntax errors - we are lenient because
3811 # this input is generally from users.
3812
3813 # Fix unbalanced brackets
3814 openings = query.count("(")
3815 closings = query.count(")")
3816 if openings > closings:
3817 query = query + " ) "*(openings-closings)
3818 elif closings > openings:
3819 query = " ( "*(closings-openings) + query
3820 ## plpy.debug('8 query is %s' % repr(query))
3821
3822 # Strip ' character that do not have letters on both sides
3823 query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
3824
3825 # Brackets containing nothing but whitespace and booleans, recursive
3826 last = ""
3827 while last != query:
3828 last = query
3829 query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
3830 ## plpy.debug('9 query is %s' % repr(query))
3831
3832 # An & or | following a (
3833 query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
3834 ## plpy.debug('10 query is %s' % repr(query))
3835
3836 # An &, | or ! immediatly before a )
3837 query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
3838 ## plpy.debug('11 query is %s' % repr(query))
3839
3840 # An &,| or ! followed by another boolean.
3841 query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
3842 ## plpy.debug('12 query is %s' % repr(query))
3843
3844 # Leading & or |
3845 query = re.sub(r"(?u)^[\s\&\|]+", "", query)
3846 ## plpy.debug('13 query is %s' % repr(query))
3847
3848 # Trailing &, | or !
3849 query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
3850 ## plpy.debug('14 query is %s' % repr(query))
3851
3852 # If we have nothing but whitespace and tsearch2 operators,
3853 # return NULL.
3854 if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
3855 return None
3856
3857 # Convert back to UTF-8
3858 query = query.encode('utf8')
3859 ## plpy.debug('15 query is %s' % repr(query))
3860
3861 p = plpy.prepare("SELECT to_tsquery('default', $1) AS x", ["text"])
3862 query = plpy.execute(p, [query], 1)[0]["x"]
3863 return query or None
3864 $_$;
3865
3866
3867COMMENT ON FUNCTION ftq(text) IS 'Convert a string to an unparsed tsearch2 query';
3868
3869
3870CREATE FUNCTION get_covers(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS text
3871 LANGUAGE c STRICT
3872 AS '$libdir/tsearch2', 'tsa_get_covers';
3873
3874
3875CREATE FUNCTION headline(oid, text, pg_catalog.tsquery, text) RETURNS text
3876 LANGUAGE internal IMMUTABLE STRICT
3877 AS $$ts_headline_byid_opt$$;
3878
3879
3880CREATE FUNCTION headline(oid, text, pg_catalog.tsquery) RETURNS text
3881 LANGUAGE internal IMMUTABLE STRICT
3882 AS $$ts_headline_byid$$;
3883
3884
3885CREATE FUNCTION headline(text, text, pg_catalog.tsquery, text) RETURNS text
3886 LANGUAGE c IMMUTABLE STRICT
3887 AS '$libdir/tsearch2', 'tsa_headline_byname';
3888
3889
3890CREATE FUNCTION headline(text, text, pg_catalog.tsquery) RETURNS text
3891 LANGUAGE c IMMUTABLE STRICT
3892 AS '$libdir/tsearch2', 'tsa_headline_byname';
3893
3894
3895CREATE FUNCTION headline(text, pg_catalog.tsquery, text) RETURNS text
3896 LANGUAGE internal IMMUTABLE STRICT
3897 AS $$ts_headline_opt$$;
3898
3899
3900CREATE FUNCTION headline(text, pg_catalog.tsquery) RETURNS text
3901 LANGUAGE internal IMMUTABLE STRICT
3902 AS $$ts_headline$$;
3903
3904
3905CREATE FUNCTION length(pg_catalog.tsvector) RETURNS integer
3906 LANGUAGE internal IMMUTABLE STRICT
3907 AS $$tsvector_length$$;
3908
3909
3910CREATE FUNCTION lexize(oid, text) RETURNS text[]
3911 LANGUAGE internal STRICT
3912 AS $$ts_lexize$$;
3913
3914
3915CREATE FUNCTION lexize(text, text) RETURNS text[]
3916 LANGUAGE c STRICT
3917 AS '$libdir/tsearch2', 'tsa_lexize_byname';
3918
3919
3920CREATE FUNCTION lexize(text) RETURNS text[]
3921 LANGUAGE c STRICT
3922 AS '$libdir/tsearch2', 'tsa_lexize_bycurrent';
3923
3924
3925CREATE FUNCTION numnode(pg_catalog.tsquery) RETURNS integer
3926 LANGUAGE internal IMMUTABLE STRICT
3927 AS $$tsquery_numnode$$;
3928
3929
3930CREATE FUNCTION parse(oid, text) RETURNS SETOF tokenout
3931 LANGUAGE internal STRICT
3932 AS $$ts_parse_byid$$;
3933
3934
3935CREATE FUNCTION parse(text, text) RETURNS SETOF tokenout
3936 LANGUAGE internal STRICT
3937 AS $$ts_parse_byname$$;
3938
3939
3940CREATE FUNCTION parse(text) RETURNS SETOF tokenout
3941 LANGUAGE c STRICT
3942 AS '$libdir/tsearch2', 'tsa_parse_current';
3943
3944
3945CREATE FUNCTION plainto_tsquery(oid, text) RETURNS pg_catalog.tsquery
3946 LANGUAGE internal IMMUTABLE STRICT
3947 AS $$plainto_tsquery_byid$$;
3948
3949
3950CREATE FUNCTION plainto_tsquery(text, text) RETURNS pg_catalog.tsquery
3951 LANGUAGE c IMMUTABLE STRICT
3952 AS '$libdir/tsearch2', 'tsa_plainto_tsquery_name';
3953
3954
3955CREATE FUNCTION plainto_tsquery(text) RETURNS pg_catalog.tsquery
3956 LANGUAGE internal IMMUTABLE STRICT
3957 AS $$plainto_tsquery$$;
3958
3959
3960CREATE FUNCTION prsd_end(internal) RETURNS void
3961 LANGUAGE c
3962 AS '$libdir/tsearch2', 'tsa_prsd_end';
3963
3964
3965CREATE FUNCTION prsd_getlexeme(internal, internal, internal) RETURNS integer
3966 LANGUAGE c
3967 AS '$libdir/tsearch2', 'tsa_prsd_getlexeme';
3968
3969
3970CREATE FUNCTION prsd_headline(internal, internal, internal) RETURNS internal
3971 LANGUAGE c
3972 AS '$libdir/tsearch2', 'tsa_prsd_headline';
3973
3974
3975CREATE FUNCTION prsd_lextype(internal) RETURNS internal
3976 LANGUAGE c
3977 AS '$libdir/tsearch2', 'tsa_prsd_lextype';
3978
3979
3980CREATE FUNCTION prsd_start(internal, integer) RETURNS internal
3981 LANGUAGE c
3982 AS '$libdir/tsearch2', 'tsa_prsd_start';
3983
3984
3985CREATE FUNCTION querytree(pg_catalog.tsquery) RETURNS text
3986 LANGUAGE internal STRICT
3987 AS $$tsquerytree$$;
3988
3989
3990CREATE FUNCTION rank(real[], pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
3991 LANGUAGE internal IMMUTABLE STRICT
3992 AS $$ts_rank_wtt$$;
3993
3994
3995CREATE FUNCTION rank(real[], pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
3996 LANGUAGE internal IMMUTABLE STRICT
3997 AS $$ts_rank_wttf$$;
3998
3999
4000CREATE FUNCTION rank(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4001 LANGUAGE internal IMMUTABLE STRICT
4002 AS $$ts_rank_tt$$;
4003
4004
4005CREATE FUNCTION rank(pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4006 LANGUAGE internal IMMUTABLE STRICT
4007 AS $$ts_rank_ttf$$;
4008
4009
4010CREATE FUNCTION rank_cd(real[], pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4011 LANGUAGE internal IMMUTABLE STRICT
4012 AS $$ts_rankcd_wtt$$;
4013
4014
4015CREATE FUNCTION rank_cd(real[], pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4016 LANGUAGE internal IMMUTABLE STRICT
4017 AS $$ts_rankcd_wttf$$;
4018
4019
4020CREATE FUNCTION rank_cd(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
4021 LANGUAGE internal IMMUTABLE STRICT
4022 AS $$ts_rankcd_tt$$;
4023
4024
4025CREATE FUNCTION rank_cd(pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
4026 LANGUAGE internal IMMUTABLE STRICT
4027 AS $$ts_rankcd_ttf$$;
4028
4029
4030CREATE FUNCTION reset_tsearch() RETURNS void
4031 LANGUAGE c STRICT
4032 AS '$libdir/tsearch2', 'tsa_reset_tsearch';
4033
4034
4035CREATE FUNCTION rewrite(pg_catalog.tsquery, text) RETURNS pg_catalog.tsquery
4036 LANGUAGE internal IMMUTABLE STRICT
4037 AS $$tsquery_rewrite_query$$;
4038
4039
4040CREATE FUNCTION rewrite(pg_catalog.tsquery, pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4041 LANGUAGE internal IMMUTABLE STRICT
4042 AS $$tsquery_rewrite$$;
4043
4044
4045CREATE FUNCTION rewrite_accum(pg_catalog.tsquery, pg_catalog.tsquery[]) RETURNS pg_catalog.tsquery
4046 LANGUAGE c
4047 AS '$libdir/tsearch2', 'tsa_rewrite_accum';
4048
4049
4050CREATE FUNCTION rewrite_finish(pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4051 LANGUAGE c
4052 AS '$libdir/tsearch2', 'tsa_rewrite_finish';
4053
4054
4055CREATE FUNCTION set_curcfg(integer) RETURNS void
4056 LANGUAGE c STRICT
4057 AS '$libdir/tsearch2', 'tsa_set_curcfg';
4058
4059
4060CREATE FUNCTION set_curcfg(text) RETURNS void
4061 LANGUAGE c STRICT
4062 AS '$libdir/tsearch2', 'tsa_set_curcfg_byname';
4063
4064
4065CREATE FUNCTION set_curdict(integer) RETURNS void
4066 LANGUAGE c STRICT
4067 AS '$libdir/tsearch2', 'tsa_set_curdict';
4068
4069
4070CREATE FUNCTION set_curdict(text) RETURNS void
4071 LANGUAGE c STRICT
4072 AS '$libdir/tsearch2', 'tsa_set_curdict_byname';
4073
4074
4075CREATE FUNCTION set_curprs(integer) RETURNS void
4076 LANGUAGE c STRICT
4077 AS '$libdir/tsearch2', 'tsa_set_curprs';
4078
4079
4080CREATE FUNCTION set_curprs(text) RETURNS void
4081 LANGUAGE c STRICT
4082 AS '$libdir/tsearch2', 'tsa_set_curprs_byname';
4083
4084
4085CREATE FUNCTION setweight(pg_catalog.tsvector, "char") RETURNS pg_catalog.tsvector
4086 LANGUAGE internal IMMUTABLE STRICT
4087 AS $$tsvector_setweight$$;
4088
4089
4090CREATE FUNCTION show_curcfg() RETURNS oid
4091 LANGUAGE internal STABLE STRICT
4092 AS $$get_current_ts_config$$;
4093
4094
4095CREATE FUNCTION snb_en_init(internal) RETURNS internal
4096 LANGUAGE c
4097 AS '$libdir/tsearch2', 'tsa_snb_en_init';
4098
4099
4100CREATE FUNCTION snb_lexize(internal, internal, integer) RETURNS internal
4101 LANGUAGE c STRICT
4102 AS '$libdir/tsearch2', 'tsa_snb_lexize';
4103
4104
4105CREATE FUNCTION snb_ru_init(internal) RETURNS internal
4106 LANGUAGE c
4107 AS '$libdir/tsearch2', 'tsa_snb_ru_init';
4108
4109
4110CREATE FUNCTION snb_ru_init_koi8(internal) RETURNS internal
4111 LANGUAGE c
4112 AS '$libdir/tsearch2', 'tsa_snb_ru_init_koi8';
4113
4114
4115CREATE FUNCTION snb_ru_init_utf8(internal) RETURNS internal
4116 LANGUAGE c
4117 AS '$libdir/tsearch2', 'tsa_snb_ru_init_utf8';
4118
4119
4120CREATE FUNCTION spell_init(internal) RETURNS internal
4121 LANGUAGE c
4122 AS '$libdir/tsearch2', 'tsa_spell_init';
4123
4124
4125CREATE FUNCTION spell_lexize(internal, internal, integer) RETURNS internal
4126 LANGUAGE c STRICT
4127 AS '$libdir/tsearch2', 'tsa_spell_lexize';
4128
4129
4130CREATE FUNCTION stat(text) RETURNS SETOF statinfo
4131 LANGUAGE internal STRICT
4132 AS $$ts_stat1$$;
4133
4134
4135CREATE FUNCTION stat(text, text) RETURNS SETOF statinfo
4136 LANGUAGE internal STRICT
4137 AS $$ts_stat2$$;
4138
4139
4140CREATE FUNCTION strip(pg_catalog.tsvector) RETURNS pg_catalog.tsvector
4141 LANGUAGE internal IMMUTABLE STRICT
4142 AS $$tsvector_strip$$;
4143
4144
4145CREATE FUNCTION syn_init(internal) RETURNS internal
4146 LANGUAGE c
4147 AS '$libdir/tsearch2', 'tsa_syn_init';
4148
4149
4150CREATE FUNCTION syn_lexize(internal, internal, integer) RETURNS internal
4151 LANGUAGE c STRICT
4152 AS '$libdir/tsearch2', 'tsa_syn_lexize';
4153
4154
4155CREATE FUNCTION thesaurus_init(internal) RETURNS internal
4156 LANGUAGE c
4157 AS '$libdir/tsearch2', 'tsa_thesaurus_init';
4158
4159
4160CREATE FUNCTION thesaurus_lexize(internal, internal, integer, internal) RETURNS internal
4161 LANGUAGE c STRICT
4162 AS '$libdir/tsearch2', 'tsa_thesaurus_lexize';
4163
4164
4165CREATE FUNCTION to_tsquery(oid, text) RETURNS pg_catalog.tsquery
4166 LANGUAGE internal IMMUTABLE STRICT
4167 AS $$to_tsquery_byid$$;
4168
4169
4170CREATE FUNCTION to_tsquery(text, text) RETURNS pg_catalog.tsquery
4171 LANGUAGE c IMMUTABLE STRICT
4172 AS '$libdir/tsearch2', 'tsa_to_tsquery_name';
4173
4174
4175CREATE FUNCTION to_tsquery(text) RETURNS pg_catalog.tsquery
4176 LANGUAGE internal IMMUTABLE STRICT
4177 AS $$to_tsquery$$;
4178
4179
4180CREATE FUNCTION to_tsvector(oid, text) RETURNS pg_catalog.tsvector
4181 LANGUAGE internal IMMUTABLE STRICT
4182 AS $$to_tsvector_byid$$;
4183
4184
4185CREATE FUNCTION to_tsvector(text, text) RETURNS pg_catalog.tsvector
4186 LANGUAGE c IMMUTABLE STRICT
4187 AS '$libdir/tsearch2', 'tsa_to_tsvector_name';
4188
4189
4190CREATE FUNCTION to_tsvector(text) RETURNS pg_catalog.tsvector
4191 LANGUAGE internal IMMUTABLE STRICT
4192 AS $$to_tsvector$$;
4193
4194
4195CREATE FUNCTION token_type(integer) RETURNS SETOF tokentype
4196 LANGUAGE internal STRICT ROWS 16
4197 AS $$ts_token_type_byid$$;
4198
4199
4200CREATE FUNCTION token_type(text) RETURNS SETOF tokentype
4201 LANGUAGE internal STRICT ROWS 16
4202 AS $$ts_token_type_byname$$;
4203
4204
4205CREATE FUNCTION token_type() RETURNS SETOF tokentype
4206 LANGUAGE c STRICT ROWS 16
4207 AS '$libdir/tsearch2', 'tsa_token_type_current';
4208
4209
4210CREATE FUNCTION ts_debug(text) RETURNS SETOF tsdebug
4211 LANGUAGE sql STRICT
4212 AS $_$
4213select
4214 (select c.cfgname::text from pg_catalog.pg_ts_config as c
4215 where c.oid = show_curcfg()),
4216 t.alias as tok_type,
4217 t.descr as description,
4218 p.token,
4219 ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary::pg_catalog.text
4220 FROM pg_catalog.pg_ts_config_map AS m
4221 WHERE m.mapcfg = show_curcfg() AND m.maptokentype = p.tokid
4222 ORDER BY m.mapseqno )
4223 AS dict_name,
4224 strip(to_tsvector(p.token)) as tsvector
4225from
4226 parse( _get_parser_from_curcfg(), $1 ) as p,
4227 token_type() as t
4228where
4229 t.tokid = p.tokid
4230$_$;
4231
4232
4233CREATE FUNCTION tsearch2() RETURNS trigger
4234 LANGUAGE c
4235 AS '$libdir/tsearch2', 'tsa_tsearch2';
4236
4237
4238CREATE FUNCTION tsq_mcontained(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS boolean
4239 LANGUAGE internal IMMUTABLE STRICT
4240 AS $$tsq_mcontained$$;
4241
4242
4243CREATE FUNCTION tsq_mcontains(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS boolean
4244 LANGUAGE internal IMMUTABLE STRICT
4245 AS $$tsq_mcontains$$;
4246
4247
4248CREATE FUNCTION tsquery_and(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4249 LANGUAGE internal IMMUTABLE STRICT
4250 AS $$tsquery_and$$;
4251
4252
4253CREATE FUNCTION tsquery_not(pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4254 LANGUAGE internal IMMUTABLE STRICT
4255 AS $$tsquery_not$$;
4256
4257
4258CREATE FUNCTION tsquery_or(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
4259 LANGUAGE internal IMMUTABLE STRICT
4260 AS $$tsquery_or$$;
4261
4262
4263SET search_path = public, pg_catalog;
4264
4265CREATE OPERATOR > (
4266 PROCEDURE = debversion_gt,
4267 LEFTARG = debversion,
4268 RIGHTARG = debversion,
4269 COMMUTATOR = <,
4270 NEGATOR = >=
4271);
4272
4273
4274COMMENT ON OPERATOR > (debversion, debversion) IS 'debversion greater-than';
4275
4276
4277CREATE AGGREGATE max(debversion) (
4278 SFUNC = debversion_larger,
4279 STYPE = debversion,
4280 SORTOP = >
4281);
4282
4283
4284CREATE OPERATOR < (
4285 PROCEDURE = debversion_lt,
4286 LEFTARG = debversion,
4287 RIGHTARG = debversion,
4288 COMMUTATOR = >,
4289 NEGATOR = >=
4290);
4291
4292
4293COMMENT ON OPERATOR < (debversion, debversion) IS 'debversion less-than';
4294
4295
4296CREATE AGGREGATE min(debversion) (
4297 SFUNC = debversion_smaller,
4298 STYPE = debversion,
4299 SORTOP = <
4300);
4301
4302
4303SET search_path = ts2, pg_catalog;
4304
4305CREATE AGGREGATE rewrite(pg_catalog.tsquery[]) (
4306 SFUNC = rewrite_accum,
4307 STYPE = pg_catalog.tsquery,
4308 FINALFUNC = rewrite_finish
4309);
4310
4311
4312SET search_path = public, pg_catalog;
4313
4314CREATE OPERATOR <= (
4315 PROCEDURE = debversion_le,
4316 LEFTARG = debversion,
4317 RIGHTARG = debversion,
4318 COMMUTATOR = >=,
4319 NEGATOR = >
4320);
4321
4322
4323COMMENT ON OPERATOR <= (debversion, debversion) IS 'debversion less-than-or-equal';
4324
4325
4326CREATE OPERATOR <> (
4327 PROCEDURE = debversion_ne,
4328 LEFTARG = debversion,
4329 RIGHTARG = debversion,
4330 COMMUTATOR = <>,
4331 NEGATOR = =
4332);
4333
4334
4335COMMENT ON OPERATOR <> (debversion, debversion) IS 'debversion not equal';
4336
4337
4338CREATE OPERATOR = (
4339 PROCEDURE = debversion_eq,
4340 LEFTARG = debversion,
4341 RIGHTARG = debversion,
4342 COMMUTATOR = =,
4343 NEGATOR = <>
4344);
4345
4346
4347COMMENT ON OPERATOR = (debversion, debversion) IS 'debversion equal';
4348
4349
4350CREATE OPERATOR >= (
4351 PROCEDURE = debversion_ge,
4352 LEFTARG = debversion,
4353 RIGHTARG = debversion,
4354 COMMUTATOR = <=,
4355 NEGATOR = <
4356);
4357
4358
4359COMMENT ON OPERATOR >= (debversion, debversion) IS 'debversion greater-than-or-equal';
4360
4361
4362CREATE OPERATOR FAMILY debversion_ops USING btree;
4363
4364
4365CREATE OPERATOR CLASS debversion_ops
4366 DEFAULT FOR TYPE debversion USING btree AS
4367 OPERATOR 1 <(debversion,debversion) ,
4368 OPERATOR 2 <=(debversion,debversion) ,
4369 OPERATOR 3 =(debversion,debversion) ,
4370 OPERATOR 4 >=(debversion,debversion) ,
4371 OPERATOR 5 >(debversion,debversion) ,
4372 FUNCTION 1 debversion_cmp(debversion,debversion);
4373
4374
4375CREATE OPERATOR FAMILY debversion_ops USING hash;
4376
4377
4378CREATE OPERATOR CLASS debversion_ops
4379 DEFAULT FOR TYPE debversion USING hash AS
4380 OPERATOR 1 =(debversion,debversion) ,
4381 FUNCTION 1 debversion_hash(debversion);
4382
4383
4384SET search_path = ts2, pg_catalog;
4385
4386CREATE OPERATOR FAMILY tsquery_ops USING btree;
4387
4388
4389CREATE OPERATOR CLASS tsquery_ops
4390 FOR TYPE pg_catalog.tsquery USING btree AS
4391 OPERATOR 1 <(pg_catalog.tsquery,pg_catalog.tsquery) ,
4392 OPERATOR 2 <=(pg_catalog.tsquery,pg_catalog.tsquery) ,
4393 OPERATOR 3 =(pg_catalog.tsquery,pg_catalog.tsquery) ,
4394 OPERATOR 4 >=(pg_catalog.tsquery,pg_catalog.tsquery) ,
4395 OPERATOR 5 >(pg_catalog.tsquery,pg_catalog.tsquery) ,
4396 FUNCTION 1 tsquery_cmp(pg_catalog.tsquery,pg_catalog.tsquery);
4397
4398
4399CREATE OPERATOR FAMILY tsvector_ops USING btree;
4400
4401
4402CREATE OPERATOR CLASS tsvector_ops
4403 FOR TYPE pg_catalog.tsvector USING btree AS
4404 OPERATOR 1 <(pg_catalog.tsvector,pg_catalog.tsvector) ,
4405 OPERATOR 2 <=(pg_catalog.tsvector,pg_catalog.tsvector) ,
4406 OPERATOR 3 =(pg_catalog.tsvector,pg_catalog.tsvector) ,
4407 OPERATOR 4 >=(pg_catalog.tsvector,pg_catalog.tsvector) ,
4408 OPERATOR 5 >(pg_catalog.tsvector,pg_catalog.tsvector) ,
4409 FUNCTION 1 tsvector_cmp(pg_catalog.tsvector,pg_catalog.tsvector);
4410
4411
4412SET search_path = pg_catalog;
The diff has been truncated for viewing.