aboutsummaryrefslogtreecommitdiff
path: root/libbrep/package-extra.sql
blob: 5c18da24e5ec08ff015e51bde46cfc7ad7026748 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- This file should be parsable by the brep-migrate utility. To decrease the
-- parser complexity, the following restrictions are placed:
--
-- * comments must start with -- at the beginning of the line (ignoring
--   leading spaces)
-- * only CREATE and DROP statements for FUNCTION, TYPE and FOREIGN TABLE
-- * function bodies must be defined using $$-quoted strings
-- * strings other then function bodies must be quoted with ' or "
-- * statements must end with ";\n"
--

-- There is no need to drop to_tsvector() explicitly, as we can rely on "DROP
-- TYPE IF EXISTS weighted_text CASCADE" statement below, which will drop all
-- objects that depend on this type. Moreover this DROP FUNCTION statement will
-- fail for old versions of PostgreSQL (experienced for 9.2.14) with error:
-- type "weighted_text" does not exist.
--
-- DROP FUNCTION IF EXISTS to_tsvector(IN document weighted_text);
--
DROP FUNCTION IF EXISTS search_packages(IN query tsquery, INOUT name TEXT);
DROP FUNCTION IF EXISTS search_latest_packages(IN query tsquery);
DROP FUNCTION IF EXISTS latest_package(INOUT name TEXT);
DROP FUNCTION IF EXISTS latest_packages();

DROP TYPE IF EXISTS weighted_text CASCADE;
CREATE TYPE weighted_text AS (a TEXT, b TEXT, c TEXT, d TEXT);

-- Return the latest versions of internal packages as a set of package rows.
--
CREATE FUNCTION
latest_packages()
RETURNS SETOF package AS $$
  SELECT p1.*
  FROM package p1 LEFT JOIN package p2 ON (
    p1.internal_repository IS NOT NULL AND p1.name = p2.name AND
    p2.internal_repository IS NOT NULL AND
    (p1.version_epoch < p2.version_epoch OR
     p1.version_epoch = p2.version_epoch AND
     (p1.version_canonical_upstream < p2.version_canonical_upstream OR
      p1.version_canonical_upstream = p2.version_canonical_upstream AND
      (p1.version_canonical_release < p2.version_canonical_release OR
       p1.version_canonical_release = p2.version_canonical_release AND
       p1.version_revision < p2.version_revision))))
  WHERE
    p1.internal_repository IS NOT NULL AND p2.name IS NULL;
$$ LANGUAGE SQL STABLE;

-- Find the latest version of an internal package having the specified name.
-- Return a single row containing the package id, empty row set if the package
-- not found.
--
CREATE FUNCTION
latest_package(INOUT name TEXT,
               OUT version_epoch INTEGER,
               OUT version_canonical_upstream TEXT,
               OUT version_canonical_release TEXT,
               OUT version_revision INTEGER)
RETURNS SETOF record AS $$
  SELECT name, version_epoch, version_canonical_upstream,
         version_canonical_release, version_revision
  FROM latest_packages()
  WHERE name = latest_package.name;
$$ LANGUAGE SQL STABLE;

-- Search for the latest version of an internal packages matching the specified
-- search query. Return a set of rows containing the package id and search
-- rank. If query is NULL, then match all packages and return 0 rank for
-- all rows.
--
CREATE FUNCTION
search_latest_packages(IN query tsquery,
                       OUT name TEXT,
                       OUT version_epoch INTEGER,
                       OUT version_canonical_upstream TEXT,
                       OUT version_canonical_release TEXT,
                       OUT version_revision INTEGER,
                       OUT rank real)
RETURNS SETOF record AS $$
  SELECT name, version_epoch, version_canonical_upstream,
         version_canonical_release, version_revision,
         CASE
	   WHEN query IS NULL THEN 0
-- Weight mapping:           D     C    B    A
	   ELSE ts_rank_cd('{0.05, 0.2, 0.9, 1.0}', search_index, query)
	 END AS rank
  FROM latest_packages()
  WHERE query IS NULL OR search_index @@ query;
$$ LANGUAGE SQL STABLE;

-- Search for packages matching the search query and having the specified name.
-- Return a set of rows containing the package id and search rank. If query
-- is NULL, then match all packages and return 0 rank for all rows.
--
CREATE FUNCTION
search_packages(IN query tsquery,
                INOUT name TEXT,
                OUT version_epoch INTEGER,
                OUT version_canonical_upstream TEXT,
                OUT version_canonical_release TEXT,
                OUT version_revision INTEGER,
                OUT rank real)
RETURNS SETOF record AS $$
  SELECT name, version_epoch, version_canonical_upstream,
         version_canonical_release, version_revision,
         CASE
	   WHEN query IS NULL THEN 0
-- Weight mapping:           D     C    B    A
	   ELSE ts_rank_cd('{0.05, 0.2, 0.9, 1.0}', search_index, query)
	 END AS rank
  FROM package
  WHERE
  internal_repository IS NOT NULL AND name = search_packages.name AND
  (query IS NULL OR search_index @@ query);
$$ LANGUAGE SQL STABLE;

-- Parse weighted_text to tsvector.
--
CREATE FUNCTION
to_tsvector(IN document weighted_text)
RETURNS tsvector AS $$
  SELECT
    CASE
      WHEN document IS NULL THEN NULL
      ELSE
        setweight(to_tsvector(document.a), 'A') ||
	setweight(to_tsvector(document.b), 'B') ||
	setweight(to_tsvector(document.c), 'C') ||
	setweight(to_tsvector(document.d), 'D')
    END
$$ LANGUAGE SQL IMMUTABLE;