diff options
Diffstat (limited to 'libbrep/package-extra.sql')
| -rw-r--r-- | libbrep/package-extra.sql | 57 |
1 files changed, 36 insertions, 21 deletions
diff --git a/libbrep/package-extra.sql b/libbrep/package-extra.sql index bd5a27b..d9930aa 100644 --- a/libbrep/package-extra.sql +++ b/libbrep/package-extra.sql @@ -17,10 +17,15 @@ -- -- DROP FUNCTION IF EXISTS to_tsvector(IN document weighted_text); -- -DROP FUNCTION IF EXISTS search_packages(IN query tsquery, INOUT name CITEXT); -DROP FUNCTION IF EXISTS search_latest_packages(IN query tsquery); -DROP FUNCTION IF EXISTS latest_package(INOUT name CITEXT); -DROP FUNCTION IF EXISTS latest_packages(); +DROP FUNCTION IF EXISTS search_packages(IN query tsquery, + IN tenant TEXT, + IN name CITEXT); + +DROP FUNCTION IF EXISTS search_latest_packages(IN query tsquery, + IN tenant TEXT); + +DROP FUNCTION IF EXISTS latest_package(IN tenant TEXT, IN name CITEXT); +DROP FUNCTION IF EXISTS latest_packages(IN tenant TEXT); DROP TYPE IF EXISTS weighted_text CASCADE; CREATE TYPE weighted_text AS (a TEXT, b TEXT, c TEXT, d TEXT); @@ -28,12 +33,14 @@ 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() +latest_packages(IN tenant TEXT) 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.internal_repository_canonical_name IS NOT NULL AND + p1.tenant = p2.tenant AND + p1.name = p2.name AND + p2.internal_repository_canonical_name 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 @@ -42,23 +49,26 @@ RETURNS SETOF package AS $$ 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; + p1.tenant = latest_packages.tenant AND + p1.internal_repository_canonical_name 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. +-- Find the latest version of an internal package having the specified tenant +-- and name. Return a single row containing the package id, empty row set if +-- the package not found. -- CREATE FUNCTION -latest_package(INOUT name CITEXT, +latest_package(INOUT tenant TEXT, + INOUT name CITEXT, 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, + SELECT tenant, name, version_epoch, version_canonical_upstream, version_canonical_release, version_revision - FROM latest_packages() + FROM latest_packages(latest_package.tenant) WHERE name = latest_package.name; $$ LANGUAGE SQL STABLE; @@ -69,6 +79,7 @@ $$ LANGUAGE SQL STABLE; -- CREATE FUNCTION search_latest_packages(IN query tsquery, + INOUT tenant TEXT, OUT name CITEXT, OUT version_epoch INTEGER, OUT version_canonical_upstream TEXT, @@ -76,23 +87,25 @@ search_latest_packages(IN query tsquery, OUT version_revision INTEGER, OUT rank real) RETURNS SETOF record AS $$ - SELECT name, version_epoch, version_canonical_upstream, + SELECT tenant, 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() + FROM latest_packages(search_latest_packages.tenant) 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. +-- Search for packages matching the search query and having the specified +-- tenant and 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 tenant TEXT, INOUT name CITEXT, OUT version_epoch INTEGER, OUT version_canonical_upstream TEXT, @@ -100,7 +113,7 @@ search_packages(IN query tsquery, OUT version_revision INTEGER, OUT rank real) RETURNS SETOF record AS $$ - SELECT name, version_epoch, version_canonical_upstream, + SELECT tenant, name, version_epoch, version_canonical_upstream, version_canonical_release, version_revision, CASE WHEN query IS NULL THEN 0 @@ -109,7 +122,9 @@ RETURNS SETOF record AS $$ END AS rank FROM package WHERE - internal_repository IS NOT NULL AND name = search_packages.name AND + tenant = search_packages.tenant AND + name = search_packages.name AND + internal_repository_canonical_name IS NOT NULL AND (query IS NULL OR search_index @@ query); $$ LANGUAGE SQL STABLE; |