Fix builds page to correctly display unbuilt package count

16 files changed, 476 insertions, 184 deletions

diff --git a/INSTALL b/INSTALL
index 14e9ae9..cdb1663 100644
--- a/INSTALL
+++ b/INSTALL
@@ -3,6 +3,9 @@ opposed to a "development" one (see INSTALL-DEV for the latter). Here we assume
 you are using a systemd-based distribution. If not, then you will need to
 replace systemctl commands with the equivalent init.d ones.
 
+The below instructions include steps for setting up brep as the build2 build
+bot controller. This functionality is optional and if not needed, then the
+corresponding steps can be omitted.
 
 1. Create 'brep' User
 
@@ -31,7 +34,13 @@ a) Install a C++ compiler using your distribution's package.
 
    Also make sure the pkg-config (or one of its replacements) is installed.
 
-b) Install PostgreSQL 9.x and Apache2 using your distribution's packages.
+b) Install PostgreSQL 9.3 or above (including the contrib package containing
+   the postgres_fdw extension) as well as Apache2 using your distribution's
+   packages. Below are the names of these packages for some distributions:
+
+   Debian/Ubuntu: postgresql-server    postgresql-contrib    apache2
+   Fedora/RHEL:   postgresql-server    postgresql-contrib    httpd
+   FreeBSD:       postgresqlXY-server  postgresqlXY-contrib  apache24
 
    Also check that the files in /home/brep are readable by "others". If they
    are not, then run the following command to grant Apache2 read access:
@@ -52,7 +61,7 @@ c) Install PostgreSQL and Apache2 development files. Specifically, we need
 
    Debian/Ubuntu: libpq-dev            libapr1-dev  apache2-dev
    Fedora/RHEL:   posqtgresql-devel    apr-devel    httpd-devel
-   FreeBSD:       postgresql94-client  apr          apache24
+   FreeBSD:       postgresqlXY-client  apr          apache24
 
 d) Unless you already have the build2 toolchain installed, download (normally
    from https://download.build2.org) and install build2-toolchain by following
@@ -94,16 +103,36 @@ $ cd .. # Back to brep home.
 
 4. Create PostgreSQL User and Databases
 
+Note that the brep_package and brep_build databases can reside in different
+database instances, potentially on different hosts. If this is the case then
+the following commands must be adjusted accordingly.
+
+Note also that below unless you set a custom password for the brep-build
+database user, any locally logged-in user will be able to login as brep-build
+and gain full access to the brep_package database.
+
 $ sudo sudo -u postgres psql # Note: double sudo is not a mistake.
 
-CREATE DATABASE brep_package TEMPLATE template0 ENCODING 'UTF8'
-LC_COLLATE 'en_US.UTF8' LC_CTYPE 'en_US.UTF8';
-CREATE DATABASE brep_build TEMPLATE template0 ENCODING 'UTF8'
-LC_COLLATE 'en_US.UTF8' LC_CTYPE 'en_US.UTF8';
+CREATE DATABASE brep_package
+  TEMPLATE template0
+  ENCODING 'UTF8'
+  LC_COLLATE 'en_US.UTF8'
+  LC_CTYPE 'en_US.UTF8';
+
+CREATE DATABASE brep_build
+  TEMPLATE template0
+  ENCODING 'UTF8'
+  LC_COLLATE 'en_US.UTF8'
+  LC_CTYPE 'en_US.UTF8';
+
 CREATE USER brep;
+
 GRANT ALL PRIVILEGES ON DATABASE brep_package, brep_build TO brep;
+
 CREATE USER "www-data" INHERIT IN ROLE brep;
 
+CREATE USER "brep-build" INHERIT IN ROLE brep PASSWORD '-';
+
 Exit psql (^D), then make sure the logins work:
 
 $ psql -d brep_package
@@ -117,6 +146,35 @@ $ sudo sudo -u www-data psql -d brep_build
 
 To troubleshoot, see PostgreSQL logs.
 
+Next setup the connection between databases:
+
+$ sudo sudo -u postgres psql -d brep_build
+
+CREATE EXTENSION postgres_fdw;
+
+CREATE SERVER package_server
+  FOREIGN DATA WRAPPER postgres_fdw
+  OPTIONS (dbname 'brep_package', updatable 'false');
+
+GRANT USAGE ON FOREIGN SERVER package_server to brep;
+
+CREATE USER MAPPING FOR PUBLIC
+  SERVER package_server
+  OPTIONS (user 'brep-build', password '-');
+
+Exit psql (^D)
+
+The user brep-build is required (by the postgres_fdw extension) to login with
+password. To accomplish this, add the following line to the PostgreSQL client
+authentication configuration file (pg_hba.conf):
+
+# TYPE  DATABASE      USER        ADDRESS  METHOD
+local   brep_package  brep-build           md5
+
+Restart PostgreSQL:
+
+$ sudo systemctl restart postgresql
+
 
 5. Create Database Schemes and Load Repositories
 
@@ -132,6 +190,7 @@ To verify:
 
 $ psql -d brep_package -c 'SELECT name, summary FROM repository'
 $ psql -d brep_build -c 'SELECT package_name FROM build' # Empty row set.
+$ psql -d brep_build -c 'SELECT DISTINCT name FROM build_package'
 
 
 6. Setup Apache2 Module
@@ -139,9 +198,8 @@ $ psql -d brep_build -c 'SELECT package_name FROM build' # Empty row set.
 $ cp install/share/brep/etc/brep-module.conf config/
 $ edit config/brep-module.conf # Adjust default values if required.
 
-If you are happy to run with the default values, you can instead do:
-
-$ ln -s ../install/share/brep/etc/brep-module.conf config/
+Note that to enable the build2 build bot controller functionality you need to
+set the build-config option in brep-module.conf.
 
 Here we assume you have setup an appropriate Apache2 virtual server. Open the
 corresponding Apache2 .conf file and add the following inside VirtualHost (you
diff --git a/INSTALL-DEV b/INSTALL-DEV
index 8f5ba6f..eb74ff8 100644
--- a/INSTALL-DEV
+++ b/INSTALL-DEV
@@ -28,14 +28,26 @@ group, not user. However, most installations use the same name for both.]
 
 $ sudo sudo -u postgres psql # Note: double sudo is not a mistake.
 
-CREATE DATABASE brep_package TEMPLATE template0 ENCODING 'UTF8'
-LC_COLLATE 'en_US.UTF8' LC_CTYPE 'en_US.UTF8';
-CREATE DATABASE brep_build TEMPLATE template0 ENCODING 'UTF8'
-LC_COLLATE 'en_US.UTF8' LC_CTYPE 'en_US.UTF8';
+CREATE DATABASE brep_package
+  TEMPLATE template0
+  ENCODING 'UTF8'
+  LC_COLLATE 'en_US.UTF8'
+  LC_CTYPE 'en_US.UTF8';
+
+CREATE DATABASE brep_build
+  TEMPLATE template0
+  ENCODING 'UTF8'
+  LC_COLLATE 'en_US.UTF8'
+  LC_CTYPE 'en_US.UTF8';
+
 CREATE USER <user>;
+
 GRANT ALL PRIVILEGES ON DATABASE brep_package, brep_build TO <user>;
+
 CREATE USER "www-data" INHERIT IN ROLE <user>;
 
+CREATE USER "brep-build" INHERIT IN ROLE <user> PASSWORD '-';
+
 Exit psql (^D), then make sure the logins work:
 
 $ psql -d brep_package
@@ -47,6 +59,33 @@ $ sudo sudo -u www-data psql -d brep_package
 $ sudo sudo -u www-data psql -d brep_build
 ^D
 
+$ sudo sudo -u postgres psql -d brep_build
+
+CREATE EXTENSION postgres_fdw;
+
+CREATE SERVER package_server
+  FOREIGN DATA WRAPPER postgres_fdw
+  OPTIONS (dbname 'brep_package', updatable 'false');
+
+GRANT USAGE ON FOREIGN SERVER package_server to <user>;
+
+CREATE USER MAPPING FOR PUBLIC
+  SERVER package_server
+  OPTIONS (user 'brep-build', password '-');
+
+Exit psql (^D)
+
+Add the following lines at the beginning of the PostgreSQL client
+authentication configuration file (pg_hba.conf):
+
+# TYPE  DATABASE      USER        ADDRESS  METHOD
+local   brep_package  brep-build           md5
+
+Restart PostgreSQL (use the second version for systemd):
+
+$ sudo /etc/init.d/postgresql restart
+$ sudo systemctl restart postgresql
+
 To troubleshoot, see PostgreSQL logs, for example:
 
 $ sudo tail -f /var/log/postgresql/*.log
@@ -68,6 +107,7 @@ To verify:
 
 $ psql -d brep_package -c 'SELECT name, summary FROM repository'
 $ psql -d brep_build -c 'SELECT package_name FROM build' # Empty row set.
+$ psql -d brep_build -c 'SELECT DISTINCT name FROM build_package'
 
 
 3. Setup Apache2 Module
@@ -76,7 +116,8 @@ Here we assume Apache2 is installed and you have an appropriate VirtualServer
 ready (the one for the default site is usually a good candidate). Open the
 corresponding Apache2 .conf file and add the following inside VirtualServer,
 replacing <BREP-OUT-ROOT> and <BREP-SRC-ROOT> with the actual absolute paths
-(if you built brep in the source tree, then the two would be the same).
+(if you built brep in the source tree, then the two would be the same), and
+replacing <user> with your login.
 
         # Load the brep module.
         #
@@ -113,6 +154,8 @@ replacing <BREP-OUT-ROOT> and <BREP-SRC-ROOT> with the actual absolute paths
         # Brep module configuration.
         #
         brep-conf <BREP-SRC-ROOT>/etc/brep-module.conf
+	brep-package-db-role <user>
+	brep-build-db-role <user>
 
         # Static brep content (CSS files).
         #
diff --git a/etc/brep-module.conf b/etc/brep-module.conf
index dd8f048..ecac553 100644
--- a/etc/brep-module.conf
+++ b/etc/brep-module.conf
@@ -46,11 +46,15 @@ menu About=?about
 
 
 # The package database connection configuration. By default, brep will try to
-# connect to the local instance of PostgreSQL with the operating system-default
-# mechanism (Unix-domain socket, etc) and use operating system (login) user
-# name and the database called 'brep_package'. See brep(1) for details.
+# connect to the local instance of PostgreSQL with the operating system-
+# default mechanism (Unix-domain socket, etc) and use operating system
+# (login) user name and the database called 'brep_package'. If the role name
+# is not empty then the login user will be switched (with SET ROLE) to this
+# user prior to executing any statements. If not specified, then 'brep' is
+# used. See brep(1) for details.
 #
 # package-db-user
+# package-db-role     brep
 # package-db-password
 # package-db-name     brep_package
 # package-db-host
@@ -137,9 +141,13 @@ menu About=?about
 # The build database connection configuration. By default, brep will try to
 # connect to the local instance of PostgreSQL with the operating system-default
 # mechanism (Unix-domain socket, etc) and use operating system (login) user
-# name and the database called 'brep_build'. See brep(1) for details.
+# name and the database called 'brep_build'. If the role name is not empty
+# then the login user will be switched (with SET ROLE) to this user prior
+# to executing any statements. If not specified, then 'brep' is used. See
+# brep(1) for details.
 #
 # build-db-user
+# build-db-role     brep
 # build-db-password
 # build-db-name     brep_build
 # build-db-host
diff --git a/libbrep/.gitignore b/libbrep/.gitignore
index 3f251f5..48fba7b 100644
--- a/libbrep/.gitignore
+++ b/libbrep/.gitignore
@@ -5,6 +5,8 @@ package.sql
 package-extra.hxx
 
 build-odb.?xx
+build-package-odb.?xx
 build.sql
+build-extra.hxx
 
 version.hxx
diff --git a/libbrep/build-extra.sql b/libbrep/build-extra.sql
new file mode 100644
index 0000000..31736c8
--- /dev/null
+++ b/libbrep/build-extra.sql
@@ -0,0 +1,20 @@
+-- This file should be parsable by the brep-migrate utility. To decrease the
+-- parser complexity, there is a number of restrictions, see package-extra.sql
+-- file for details.
+--
+
+-- The foreign table for build_package object.
+--
+--
+DROP FOREIGN TABLE IF EXISTS build_package;
+
+CREATE FOREIGN TABLE build_package (
+  name TEXT NOT NULL,
+  version_epoch INTEGER NOT NULL,
+  version_canonical_upstream TEXT NOT NULL,
+  version_canonical_release TEXT NOT NULL COLLATE "C",
+  version_revision INTEGER NOT NULL,
+  version_upstream TEXT NOT NULL,
+  version_release TEXT NULL,
+  internal_repository TEXT NULL)
+SERVER package_server OPTIONS (table_name 'package');
diff --git a/libbrep/build-package.hxx b/libbrep/build-package.hxx
new file mode 100644
index 0000000..b0688c2
--- /dev/null
+++ b/libbrep/build-package.hxx
@@ -0,0 +1,56 @@
+// file      : libbrep/build-package.hxx -*- C++ -*-
+// copyright : Copyright (c) 2014-2017 Code Synthesis Ltd
+// license   : MIT; see accompanying LICENSE file
+
+#ifndef LIBBREP_BUILD_PACKAGE_HXX
+#define LIBBREP_BUILD_PACKAGE_HXX
+
+#include <odb/core.hxx>
+
+#include <libbrep/types.hxx>
+#include <libbrep/utility.hxx>
+
+#include <libbrep/common.hxx> // Must be included last (see assert).
+
+namespace brep
+{
+  // This is a "foreign object" that is mapped to the subset of package object
+  // using PostgreSQL foreign table mechanism. Note that since we maintain the
+  // two in sync by hand, we should only a have a minimal subset of "core"
+  // members (ideally just the primary key) that are unlikly to disappear or
+  // change.
+  //
+  // The mapping is established in build-extra.sql.
+  //
+  #pragma db object table("build_package") pointer(shared_ptr) readonly
+  class build_package
+  {
+  public:
+    package_id id;
+    upstream_version version;
+    optional<string> internal_repository;
+
+    // Database mapping.
+    //
+    #pragma db member(id) id column("")
+    #pragma db member(version) set(this.version.init (this.id.version, (?)))
+
+  private:
+    friend class odb::access;
+    build_package () = default;
+  };
+
+  #pragma db view object(build_package)
+  struct build_package_count
+  {
+    size_t result;
+
+    operator size_t () const {return result;}
+
+    // Database mapping.
+    //
+    #pragma db member(result) column("count(" + build_package::id.name + ")")
+  };
+}
+
+#endif // LIBBREP_BUILD_PACKAGE_HXX
diff --git a/libbrep/build.hxx b/libbrep/build.hxx
index dd6f10d..7a231bd 100644
--- a/libbrep/build.hxx
+++ b/libbrep/build.hxx
@@ -17,7 +17,10 @@
 #include <libbrep/types.hxx>
 #include <libbrep/utility.hxx>
 
-#include <libbrep/common.hxx> // Must be included last (see assert).
+// Must be included last (see assert in libbrep/common.hxx).
+//
+#include <libbrep/common.hxx>
+#include <libbrep/build-package.hxx>
 
 // Used by the data migration entries.
 //
@@ -261,6 +264,40 @@ namespace brep
     #pragma db transient
     canonical_version version_;
   };
+
+  // Build of an existing internal package.
+  //
+  #pragma db view                                               \
+    object(build)                                               \
+    object(build_package inner:                                 \
+           build::id.package.name == build_package::id.name &&  \
+           brep::compare_version_eq (build::id.package.version, \
+                                     build_package::id.version, \
+                                     true) &&                   \
+           build_package::internal_repository.is_not_null ())
+  struct package_build
+  {
+    shared_ptr<brep::build> build;
+  };
+
+  #pragma db view                                               \
+    object(build)                                               \
+    object(build_package inner:                                 \
+           build::id.package.name == build_package::id.name &&  \
+           brep::compare_version_eq (build::id.package.version, \
+                                     build_package::id.version, \
+                                     true) &&                   \
+           build_package::internal_repository.is_not_null ())
+  struct package_build_count
+  {
+    size_t result;
+
+    operator size_t () const {return result;}
+
+    // Database mapping.
+    //
+    #pragma db member(result) column("count(" + build::id.package.name + ")")
+  };
 }
 
 #endif // LIBBREP_BUILD_HXX
diff --git a/libbrep/buildfile b/libbrep/buildfile
index b4005ab..1403756 100644
--- a/libbrep/buildfile
+++ b/libbrep/buildfile
@@ -12,24 +12,26 @@ import int_libs += libbutl%lib{butl}
 import int_libs += libbpkg%lib{bpkg}
 import int_libs += libbbot%lib{bbot}
 
-lib{brep}:                          \
-{hxx         cxx}{ build          } \
-{file           }{ build.xml      } \
-{hxx ixx     cxx}{ build-odb      } \
-{hxx         cxx}{ common         } \
-{hxx ixx     cxx}{ common-odb     } \
-{hxx         cxx}{ package        } \
-{file           }{ package.xml    } \
-{hxx ixx     cxx}{ package-odb    } \
-{hxx            }{ package-extra  } \
-{hxx         cxx}{ package-traits } \
-{hxx         cxx}{ database-lock  } \
-{hxx            }{ types          } \
-{hxx            }{ utility        } \
-{hxx            }{ version        } \
-{hxx            }{ wrapper-traits } \
-                   $int_libs        \
-sql{build package package-extra}
+lib{brep}:                             \
+{hxx         cxx}{ build             } \
+{file           }{ build.xml         } \
+{hxx ixx     cxx}{ build-odb         } \
+{hxx            }{ build-package     } \
+{hxx ixx     cxx}{ build-package-odb } \
+{hxx         cxx}{ common            } \
+{hxx ixx     cxx}{ common-odb        } \
+{hxx         cxx}{ package           } \
+{file           }{ package.xml       } \
+{hxx ixx     cxx}{ package-odb       } \
+{hxx            }{ package-extra     } \
+{hxx         cxx}{ package-traits    } \
+{hxx         cxx}{ database-lock     } \
+{hxx            }{ types             } \
+{hxx            }{ utility           } \
+{hxx            }{ version           } \
+{hxx            }{ wrapper-traits    } \
+                   $int_libs           \
+sql{build build-extra package package-extra}
 
 hxx{version}: in{version} $src_root/file{manifest}
 hxx{version}: dist = true
diff --git a/libbrep/odb.sh b/libbrep/odb.sh
index 2897afa..5f5a783 100755
--- a/libbrep/odb.sh
+++ b/libbrep/odb.sh
@@ -38,3 +38,13 @@ $odb $lib -d pgsql --std c++11 --generate-query --generate-schema    \
     --include-with-brackets --include-prefix libbrep                 \
     --guard-prefix LIBBREP \
     build.hxx
+
+$odb $lib -d pgsql --std c++11 --generate-query               \
+    --odb-epilogue '#include <libbrep/wrapper-traits.hxx>'    \
+    --generate-prepared -DLIBODB_BUILD2 -DLIBODB_PGSQL_BUILD2 \
+    -I .. -I ../../libbbot -I ../../libbpkg -I ../../libbutl  \
+    --include-with-brackets --include-prefix libbrep          \
+    --guard-prefix LIBBREP                                    \
+    build-package.hxx
+
+xxd -i <build-extra.sql >build-extra.hxx
diff --git a/libbrep/package-extra.sql b/libbrep/package-extra.sql
index 823c3af..5c18da2 100644
--- a/libbrep/package-extra.sql
+++ b/libbrep/package-extra.sql
@@ -3,7 +3,7 @@
 --
 -- * comments must start with -- at the beginning of the line (ignoring
 --   leading spaces)
--- * only CREATE and DROP statements for FUNCTION and TYPE
+-- * 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"
diff --git a/migrate/migrate.cxx b/migrate/migrate.cxx
index 25b84d0..0fde937 100644
--- a/migrate/migrate.cxx
+++ b/migrate/migrate.cxx
@@ -93,21 +93,23 @@ schema (const char* s, string name)
     {
       string statement (op);
 
-      auto read_until = [&i, &statement](const char stop[2]) -> bool
+      auto read_until = [&i, &statement] (const char stop[2]) -> bool
+      {
+        for (char prev ('\0'), c; i.get (c); prev = c)
         {
-          for (char prev ('\0'), c; i.get (c); prev = c)
-          {
-            statement.push_back (c);
+          statement.push_back (c);
 
-            if (stop[0] == prev && stop[1] == c)
-              return true;
-          }
+          if (stop[0] == prev && stop[1] == c)
+            return true;
+        }
 
-          return false;
-        };
+        return false;
+      };
 
       if (strcasecmp (op.c_str (), "CREATE") == 0)
       {
+        bool valid (true);
+
         string kw;
         i >> kw;
         statement += " " + kw;
@@ -125,7 +127,18 @@ schema (const char* s, string name)
         {
           // Fall through.
         }
+        else if (strcasecmp (kw.c_str (), "FOREIGN") == 0)
+        {
+          i >> kw;
+          statement += " " + kw;
+          valid = strcasecmp (kw.c_str (), "TABLE") == 0;
+
+          // Fall through.
+        }
         else
+          valid = false;
+
+        if (!valid)
         {
           cerr << "error: unexpected CREATE statement" << endl;
           throw failed ();
@@ -321,9 +334,11 @@ try
 #include <libbrep/package-extra.hxx>
       , '\0'};
 
-    schema s (db_schema == "package"
-              ? package_extras
-              : "",
+    static const char build_extras[] = {
+#include <libbrep/build-extra.hxx>
+      , '\0'};
+
+    schema s (db_schema == "package" ? package_extras : build_extras,
               db_schema);
 
     if (create)
diff --git a/mod/database-module.cxx b/mod/database-module.cxx
index 67e4c9d..ab39a18 100644
--- a/mod/database-module.cxx
+++ b/mod/database-module.cxx
@@ -43,6 +43,7 @@ namespace brep
   init (const options::package_db& o, size_t retry)
   {
     package_db_ = shared_database (o.package_db_user (),
+                                   o.package_db_role (),
                                    o.package_db_password (),
                                    o.package_db_name (),
                                    o.package_db_host (),
@@ -89,6 +90,7 @@ namespace brep
     build_conf_map_ = make_shared<conf_map_type> (move (conf_map));
 
     build_db_ = shared_database (dbo.build_db_user (),
+                                 dbo.build_db_role (),
                                  dbo.build_db_password (),
                                  dbo.build_db_name (),
                                  dbo.build_db_host (),
diff --git a/mod/database.cxx b/mod/database.cxx
index 67a862b..813c8cc 100644
--- a/mod/database.cxx
+++ b/mod/database.cxx
@@ -14,6 +14,7 @@ namespace brep
   struct db_key
   {
     string user;
+    string role;
     string password;
     string name;
     string host;
@@ -25,6 +26,7 @@ namespace brep
   {
     int r;
     if ((r = x.user.compare (y.user)) != 0 ||
+        (r = x.role.compare (y.role)) != 0 ||
         (r = x.password.compare (y.password)) != 0 ||
         (r = x.name.compare (y.name)) != 0 ||
         (r = x.host.compare (y.host)))
@@ -35,8 +37,41 @@ namespace brep
 
   using namespace odb;
 
+  class connection_pool_factory: public pgsql::connection_pool_factory
+  {
+  public:
+    connection_pool_factory (string role, size_t max_connections)
+        : pgsql::connection_pool_factory (max_connections),
+          role_ (move (role))
+    {
+    }
+
+    virtual pooled_connection_ptr
+    create () override
+    {
+      pooled_connection_ptr conn (pgsql::connection_pool_factory::create ());
+
+      // Set the serializable isolation level for the subsequent connection
+      // transactions. Note that the SET TRANSACTION command affects only the
+      // current transaction.
+      //
+      conn->execute ("SET default_transaction_isolation=serializable");
+
+      // Change the connection current user to the execution user name.
+      //
+      if (!role_.empty ())
+        conn->execute ("SET ROLE '" + role_ + "'");
+
+      return conn;
+    }
+
+  private:
+    string role_;
+  };
+
   shared_ptr<database>
   shared_database (string user,
+                   string role,
                    string password,
                    string name,
                    string host,
@@ -45,7 +80,10 @@ namespace brep
   {
     static std::map<db_key, weak_ptr<database>> databases;
 
-    db_key k ({move (user), move (password), move (name), host, port});
+    db_key k ({
+      move (user), move (role), move (password),
+      move (name),
+      move (host), port});
 
     auto i (databases.find (k));
     if (i != databases.end ())
@@ -55,7 +93,7 @@ namespace brep
     }
 
     unique_ptr<pgsql::connection_factory>
-      f (new pgsql::connection_pool_factory (max_connections));
+      f (new connection_pool_factory (k.role, max_connections));
 
     shared_ptr<database> d (
       make_shared<pgsql::database> (
@@ -64,7 +102,7 @@ namespace brep
         k.name,
         k.host,
         k.port,
-        "options='-c default_transaction_isolation=serializable'",
+        "",
         move (f)));
 
     databases[move (k)] = d;
diff --git a/mod/database.hxx b/mod/database.hxx
index 623e65b..fdd9bac 100644
--- a/mod/database.hxx
+++ b/mod/database.hxx
@@ -17,6 +17,7 @@ namespace brep
   //
   shared_ptr<odb::core::database>
   shared_database (string user,
+                   string role,
                    string password,
                    string name,
                    string host,
diff --git a/mod/mod-builds.cxx b/mod/mod-builds.cxx
index 354e5f5..979121f 100644
--- a/mod/mod-builds.cxx
+++ b/mod/mod-builds.cxx
@@ -22,8 +22,8 @@
 
 #include <libbrep/build.hxx>
 #include <libbrep/build-odb.hxx>
-#include <libbrep/package.hxx>
-#include <libbrep/package-odb.hxx>
+#include <libbrep/build-package.hxx>
+#include <libbrep/build-package-odb.hxx>
 
 #include <mod/page.hxx>
 #include <mod/options.hxx>
@@ -55,8 +55,6 @@ init (scanner& s)
   options_ = make_shared<options::builds> (
     s, unknown_mode::fail, unknown_mode::fail);
 
-  database_module::init (*options_, options_->package_db_retry ());
-
   if (options_->build_config_specified ())
     database_module::init (static_cast<options::build>    (*options_),
                            static_cast<options::build_db> (*options_),
@@ -98,9 +96,9 @@ build_query (const brep::cstrings& configs, const brep::params::builds& params)
 {
   using namespace brep;
   using query = query<T>;
+  using qb = typename query::build;
 
-  query q (
-    query::id.configuration.in_range (configs.begin (), configs.end ()));
+  query q (qb::id.configuration.in_range (configs.begin (), configs.end ()));
 
   // Note that there is no error reported if the filter parameters parsing
   // fails. Instead, it is considered that no package builds match such a
@@ -111,12 +109,12 @@ build_query (const brep::cstrings& configs, const brep::params::builds& params)
     // Package name.
     //
     if (!params.name ().empty ())
-      q = q && query::id.package.name.like (transform (params.name ()));
+      q = q && qb::id.package.name.like (transform (params.name ()));
 
     // Package version.
     //
     if (!params.version ().empty () && params.version () != "*")
-      q = q && compare_version_eq (query::id.package.version,
+      q = q && compare_version_eq (qb::id.package.version,
                                    version (params.version ()), // May throw.
                                    true);
 
@@ -133,20 +131,19 @@ build_query (const brep::cstrings& configs, const brep::params::builds& params)
       string  tn (tc, 0, p);
       version tv (string (tc, p + 1)); // May throw invalid_argument.
 
-      q = q && query::toolchain_name == tn &&
-        compare_version_eq (query::id.toolchain_version, tv, true);
+      q = q && qb::toolchain_name == tn &&
+        compare_version_eq (qb::id.toolchain_version, tv, true);
     }
 
     // Build configuration name.
     //
     if (!params.configuration ().empty ())
-      q = q && query::id.configuration.like (
-        transform (params.configuration ()));
+      q = q && qb::id.configuration.like (transform (params.configuration ()));
 
     // Build machine name.
     //
     if (!params.machine ().empty ())
-      q = q && query::machine.like (transform (params.machine ()));
+      q = q && qb::machine.like (transform (params.machine ()));
 
     // Build target.
     //
@@ -154,8 +151,8 @@ build_query (const brep::cstrings& configs, const brep::params::builds& params)
 
     if (tg != "*")
       q = q && (tg.empty ()
-                ? query::target.is_null ()
-                : query::target.like (transform (tg)));
+                ? qb::target.is_null ()
+                : qb::target.like (transform (tg)));
 
     // Build result.
     //
@@ -164,12 +161,12 @@ build_query (const brep::cstrings& configs, const brep::params::builds& params)
     if (rs != "*")
     {
       if (rs == "pending")
-        q = q && query::force != "unforced";
+        q = q && qb::force != "unforced";
       else if (rs == "building")
-        q = q && query::state == "building";
+        q = q && qb::state == "building";
       else
       {
-        query sq (query::status == rs);
+        query sq (qb::status == rs);
         result_status st (to_result_status(rs)); // May throw invalid_argument.
 
         if (st != result_status::success)
@@ -184,13 +181,13 @@ build_query (const brep::cstrings& configs, const brep::params::builds& params)
           };
 
           while (next ())
-            sq = sq || query::status == to_string (st);
+            sq = sq || qb::status == to_string (st);
         }
 
         // Note that the result status may present for the building state as
         // well (rebuild).
         //
-        q = q && query::state == "built" && sq;
+        q = q && qb::state == "built" && sq;
       }
     }
   }
@@ -211,10 +208,8 @@ package_query (const brep::params::builds& params)
 
   // Skip external and stub packages.
   //
-  query q (query::package::internal_repository.is_not_null () &&
-           compare_version_ne (query::package::id.version,
-                               wildcard_version,
-                               false));
+  query q (query::internal_repository.is_not_null () &&
+           compare_version_ne (query::id.version, wildcard_version, false));
 
   // Note that there is no error reported if the filter parameters parsing
   // fails. Instead, it is considered that no packages match such a query.
@@ -224,12 +219,12 @@ package_query (const brep::params::builds& params)
     // Package name.
     //
     if (!params.name ().empty ())
-      q = q && query::package::id.name.like (transform (params.name ()));
+      q = q && query::id.name.like (transform (params.name ()));
 
     // Package version.
     //
     if (!params.version ().empty () && params.version () != "*")
-      q = q && compare_version_eq (query::package::id.version,
+      q = q && compare_version_eq (query::id.version,
                                    version (params.version ()), // May throw.
                                    true);
   }
@@ -272,8 +267,7 @@ handle (request& rq, response& rs)
   try
   {
     name_value_scanner s (rq.parameters ());
-    params = params::builds (
-      s, unknown_mode::fail, unknown_mode::fail);
+    params = params::builds (s, unknown_mode::fail, unknown_mode::fail);
   }
   catch (const cli::exception& e)
   {
@@ -303,7 +297,7 @@ handle (request& rq, response& rs)
     <<     DIV(ID="content");
 
   // Return the list of distinct toolchain name/version pairs. The build db
-  // transaction must be started and be the current one.
+  // transaction must be started.
   //
   using toolchains = vector<pair<string, version>>;
 
@@ -408,14 +402,8 @@ handle (request& rq, response& rs)
   {
     transaction t (build_db_->begin ());
 
-    // Having packages and package configuration builds in different
-    // databases, we are unable to filter out builds for non-existent packages
-    // at the query level. Doing that in the C++ code would complicate it
-    // significantly. So we will print all the builds, relying on the sorting
-    // algorithm which will place expired ones at the end of the query result.
-    //
-    count = build_db_->query_value<build_count> (
-      build_query<build_count> (*build_conf_names_, params));
+    count = build_db_->query_value<package_build_count> (
+      build_query<package_build_count> (*build_conf_names_, params));
 
     // Print the filter form.
     //
@@ -429,12 +417,14 @@ handle (request& rq, response& rs)
     // Enclose the subsequent tables to be able to use nth-child CSS selector.
     //
     s << DIV;
-    for (auto& b: build_db_->query<build> (
-           build_query<build> (*build_conf_names_, params) +
+    for (auto& pb: build_db_->query<package_build> (
+           build_query<package_build> (*build_conf_names_, params) +
            "ORDER BY" + query<build>::timestamp + "DESC" +
            "OFFSET" + to_string (page * page_configs) +
            "LIMIT" + to_string (page_configs)))
     {
+      build& b (*pb.build);
+
       string ts (butl::to_string (b.timestamp,
                                   "%Y-%m-%d %H:%M:%S %Z",
                                   true,
@@ -474,25 +464,13 @@ handle (request& rq, response& rs)
     bld_params.machine ().clear ();
     bld_params.result () = "*";
 
-    // Query toolchains, and the number of package configurations being present
-    // in the build database and satisfying the specified filter arguments.
-    // Later we will subtract it from the maximum number of unbuilt package
-    // configurations, to get the real number of them.
+    // Query toolchains, filter build configurations and toolchains, and
+    // create the set of configuration/toolchain combinations, that we will
+    // print for packages. Also calculate the number of unbuilt package
+    // configurations.
     //
     toolchains toolchains;
-    {
-      transaction t (build_db_->begin ());
-      toolchains = query_toolchains ();
-
-      count = build_db_->query_value<build_count> (
-        build_query<build_count> (*build_conf_names_, bld_params));
 
-      t.commit ();
-    }
-
-    // Filter build configurations and toolchains, and create the set of
-    // configuration/toolchain combinations, that we will print for packages.
-    //
     struct config_toolchain
     {
       const string& configuration;
@@ -514,30 +492,41 @@ handle (request& rq, response& rs)
       }
     };
 
+    // Note that config_toolchains contains shallow references to the toolchain
+    // names and versions, and in particular to the selected ones (tc_name and
+    // tc_version).
+    //
     string tc_name;
     version tc_version;
-    const string& tc (params.toolchain ());
+    set<config_toolchain> config_toolchains;
 
-    if (tc != "*")
-    try
     {
-      size_t p (tc.find ('-'));
-      if (p == string::npos)         // Invalid format.
-        throw invalid_argument ("");
+      transaction t (build_db_->begin ());
+      toolchains = query_toolchains ();
 
-      tc_name.assign (tc, 0, p);
-      tc_version = version (string (tc, p + 1)); // May throw invalid_argument.
-    }
-    catch (const invalid_argument&)
-    {
-      // This is unlikely to be the user fault, as he selects the toolchain
-      // from the list.
-      //
-      throw invalid_request (400, "invalid toolchain");
-    }
+      const string& tc (params.toolchain ());
+
+      if (tc != "*")
+      try
+      {
+        size_t p (tc.find ('-'));
+        if (p == string::npos)         // Invalid format.
+          throw invalid_argument ("");
+
+        tc_name.assign (tc, 0, p);
+
+        // May throw invalid_argument.
+        //
+        tc_version = version (string (tc, p + 1));
+      }
+      catch (const invalid_argument&)
+      {
+        // This is unlikely to be the user fault, as he selects the toolchain
+        // from the list.
+        //
+        throw invalid_request (400, "invalid toolchain");
+      }
 
-    set<config_toolchain> config_toolchains;
-    {
       const string& pc (params.configuration ());
       const string& tg (params.target ());
 
@@ -545,7 +534,6 @@ handle (request& rq, response& rs)
       {
         if ((pc.empty () || path_match (pc, c.name)) && // Filter by name.
 
-
             (tg.empty ()                                // Filter by target.
              ? !c.target
              : tg == "*" ||
@@ -563,16 +551,18 @@ handle (request& rq, response& rs)
 
       // Calculate the number of unbuilt package configurations as a
       // difference between the maximum possible number of unbuilt
-      // configurations and the number of configurations being in the built or
-      // building state (see above).
+      // configurations and the number of existing package builds.
       //
-      transaction t (package_db_->begin ());
+      size_t nmax (config_toolchains.size () *
+                   build_db_->query_value<build_package_count> (
+                     package_query<build_package_count> (params)));
 
-      size_t n (config_toolchains.size () *
-                package_db_->query_value<package_version_count> (
-                  package_query<package_version_count> (params)));
+      size_t ncur = build_db_->query_value<package_build_count> (
+        build_query<package_build_count> (*build_conf_names_, bld_params));
+
+      assert (nmax >= ncur);
+      count = nmax - ncur;
 
-      count = n > count ? n - count : 0;
       t.commit ();
     }
 
@@ -588,24 +578,24 @@ handle (request& rq, response& rs)
     // 4: toolchain name
     // 5: toolchain version (descending)
     //
-    // Prepare the package version prepared query.
+    // Prepare the build package prepared query.
     //
     // Note that we can't skip the proper number of packages in the database
     // query for a page numbers greater than one. So we will query packages
     // from the very beginning and skip the appropriate number of them while
     // iterating through the query result.
     //
-    // Note that such an approach has a security implication. An HTTP request
-    // with a large page number will be quite expensive to process, as it
-    // effectively results in traversing all the package versions and all the
+    // Also note that such an approach has a security implication. An HTTP
+    // request with a large page number will be quite expensive to process, as
+    // it effectively results in traversing all the build package and all the
     // built configurations. To address this problem we may consider to reduce
     // the pager to just '<Prev' '1' 'Next>' links, and pass the offset as a
     // URL query parameter. Alternatively, we can invent the page number cap.
     //
-    using pkg_query = query<package_version>;
-    using prep_pkg_query = prepared_query<package_version>;
+    using pkg_query = query<build_package>;
+    using prep_pkg_query = prepared_query<build_package>;
 
-    pkg_query pq (package_query<package_version> (params));
+    pkg_query pq (package_query<build_package> (params));
 
     // Specify the portion. Note that we will still be querying packages in
     // chunks, not to hold locks for too long.
@@ -613,32 +603,35 @@ handle (request& rq, response& rs)
     size_t offset (0);
 
     pq += "ORDER BY" +
-      pkg_query::package::id.name +
-      order_by_version_desc (pkg_query::package::id.version, false) +
+      pkg_query::id.name +
+      order_by_version_desc (pkg_query::id.version, false) +
       "OFFSET" + pkg_query::_ref (offset) + "LIMIT 50";
 
-    connection_ptr pkg_conn (package_db_->connection ());
+    connection_ptr conn (build_db_->connection ());
 
     prep_pkg_query pkg_prep_query (
-      pkg_conn->prepare_query<package_version> (
-        "mod-builds-package-version-query", pq));
+      conn->prepare_query<build_package> ("mod-builds-package-query", pq));
 
     // Prepare the build prepared query.
     //
-    using bld_query = query<build>;
-    using prep_bld_query = prepared_query<build>;
-
-    package_id id; // See the build query.
-
-    const auto& qv (bld_query::id.package.version);
+    // For each package we will generate a set of all possible builds. Then,
+    // iterating over the actual builds for the package we will exclude them
+    // from the set of possible ones. The resulted set represents unbuilt
+    // package configurations, and so will be printed.
+    //
+    using bld_query = query<package_build>;
+    using prep_bld_query = prepared_query<package_build>;
 
     // We will use specific package name and version for each database query.
     //
     bld_params.name ().clear ();
     bld_params.version ().clear ();
 
+    package_id id;
+    const auto& qv (bld_query::build::id.package.version);
+
     bld_query bq (
-      bld_query::id.package.name == bld_query::_ref (id.name) &&
+      bld_query::build::id.package.name == bld_query::_ref (id.name) &&
 
       qv.epoch == bld_query::_ref (id.version.epoch) &&
       qv.canonical_upstream ==
@@ -646,13 +639,11 @@ handle (request& rq, response& rs)
       qv.canonical_release ==
       bld_query::_ref (id.version.canonical_release) &&
       qv.revision == bld_query::_ref (id.version.revision) &&
-      build_query<build_count> (*build_conf_names_, bld_params));
 
-    connection_ptr bld_conn (build_db_->connection ());
+      build_query<package_build> (*build_conf_names_, bld_params));
 
     prep_bld_query bld_prep_query (
-      bld_conn->prepare_query<build> (
-        "mod-builds-package-build-query", bq));
+      conn->prepare_query<package_build> ("mod-builds-build-query", bq));
 
     size_t skip (page * page_configs);
     size_t print (page_configs);
@@ -662,39 +653,37 @@ handle (request& rq, response& rs)
     s << DIV;
     for (bool prn (true); prn; )
     {
-      // Start the package database transaction.
-      //
-      transaction pt (pkg_conn->begin ());
+      transaction t (conn->begin ());
 
-      // Query package versions.
+      // Query (and cache) build packages.
       //
-      auto package_versions (pkg_prep_query.execute ());
+      auto packages (pkg_prep_query.execute ());
 
-      if ((prn = !package_versions.empty ()))
+      if ((prn = !packages.empty ()))
       {
-        offset += package_versions.size ();
-
-        // Start the build database transaction.
-        //
-        transaction bt (bld_conn->begin (), false);
-        transaction::current (bt);
+        offset += packages.size ();
 
         // Iterate over packages and print unbuilt configurations. Skip the
         // appropriate number of them first (for page number greater than one).
         //
-        for (auto& pv: package_versions)
+        for (auto& pv: packages)
         {
           id = move (pv.id);
 
-          // Iterate through the package configuration builds and erase them
-          // from the unbuilt configurations set.
-          //
           // Make a copy for this package.
           //
           auto unbuilt_configs (config_toolchains);
-          for (const auto& b: bld_prep_query.execute ())
+
+          // Iterate through the package configuration builds and erase them
+          // from the unbuilt configurations set.
+          //
+          for (const auto& pb: bld_prep_query.execute ())
+          {
+            build& b (*pb.build);
+
             unbuilt_configs.erase ({
                 b.id.configuration, b.toolchain_name, b.toolchain_version});
+          }
 
           // Print unbuilt package configurations.
           //
@@ -733,14 +722,9 @@ handle (request& rq, response& rs)
           if (!prn)
             break;
         }
-
-        // Commit the build database transaction and switch to the package
-        // database transaction.
-        bt.commit ();
-        transaction::current (pt);
       }
 
-      pt.commit ();
+      t.commit ();
     }
     s << ~DIV;
   }
diff --git a/mod/options.cli b/mod/options.cli
index 05578bf..051081f 100644
--- a/mod/options.cli
+++ b/mod/options.cli
@@ -93,8 +93,16 @@ namespace brep
       string package-db-user
       {
         "<user>",
-        "Package database user name. If not specified, then operating system
-         (login) name is used."
+        "Package database login user name. If not specified, then operating
+         system (login) name is used. See also \c{package-db-role}."
+      }
+
+      string package-db-role = "brep"
+      {
+        "<user>",
+        "Package database execution user name. If not empty then the login
+         user will be switched (with \c{SET ROLE}) to this user prior to
+         executing any statements. If not specified, then \cb{brep} is used."
       }
 
       string package-db-password
@@ -190,8 +198,16 @@ namespace brep
       string build-db-user
       {
         "<user>",
-        "Build database user name. If not specified, then operating system
-         (login) name is used."
+        "Build database login user name. If not specified, then operating
+         system (login) name is used. See also \c{build-db-role}."
+      }
+
+      string build-db-role = "brep"
+      {
+        "<user>",
+        "Build database execution user name. If not empty then the login
+         user will be switched (with \c{SET ROLE}) to this user  prior to
+         executing any statements. If not specified, then \cb{brep} is used."
       }
 
       string build-db-password