From 70c1cdfd8f34472761fe5ec97f0713990c1b4f5b Mon Sep 17 00:00:00 2001
From: Karen Arutyunov <karen@codesynthesis.com>
Date: Wed, 5 Sep 2018 21:23:41 +0300
Subject: Add multi-tenancy support

---
 libbrep/package-extra.sql | 57 ++++++++++++++++++++++++++++++-----------------
 1 file changed, 36 insertions(+), 21 deletions(-)

(limited to 'libbrep/package-extra.sql')

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;
 
-- 
cgit v1.1

