From c7d9b6020f41ef64d9315ec0bf3e2b4a1c59f67b Mon Sep 17 00:00:00 2001
From: Karen Arutyunov <karen@codesynthesis.com>
Date: Mon, 16 Aug 2021 12:00:39 +0300
Subject: Add selected_package_prerequisites(configuration,prerequisite) index
 to speed up query_dependents()

---
 bpkg/database.cxx | 28 ++++++++++++++++++++++++++--
 bpkg/package.hxx  |  2 +-
 bpkg/package.xml  |  2 ++
 3 files changed, 29 insertions(+), 3 deletions(-)

diff --git a/bpkg/database.cxx b/bpkg/database.cxx
index 3d83de8..e91e668 100644
--- a/bpkg/database.cxx
+++ b/bpkg/database.cxx
@@ -55,8 +55,8 @@ namespace bpkg
 
   // Register the data migration functions.
   //
-  // NOTE: remember to qualify table names with \"main\". if using native
-  // statements.
+  // NOTE: remember to qualify table/index names with \"main\". if using
+  // native statements.
   //
   template <odb::schema_version v>
   using migration_entry = odb::data_migration_entry<v, DB_SCHEMA_VERSION_BASE>;
@@ -87,6 +87,18 @@ namespace bpkg
                 "SET configuration = '" + sl->uuid.string () + "'");
   });
 
+  static const migration_entry<10>
+  migrate_v10 ([] (odb::database& db)
+  {
+    // Create the multi-column index for the configuration and prerequisite
+    // columns of the selected_package_prerequisites table.
+    //
+    db.execute (
+      "CREATE INDEX "
+      "\"main\".selected_package_prerequisites_configuration_prerequisite_i "
+      "ON selected_package_prerequisites (configuration, prerequisite)");
+  });
+
   static inline path
   cfg_path (const dir_path& d, bool create)
   {
@@ -175,6 +187,18 @@ namespace bpkg
 
           schema_catalog::create_schema (*this);
 
+          // To speed up the query_dependents() function create the multi-
+          // column index for the configuration and prerequisite columns of
+          // the selected_package_prerequisites table.
+          //
+          // @@ Use ODB pragma if/when support for container indexes is added.
+          //
+          execute (
+            "CREATE INDEX "
+            "selected_package_prerequisites_configuration_prerequisite_i "
+            "ON selected_package_prerequisites (configuration, "
+            "prerequisite)");
+
           persist (*create); // Also assigns link id.
 
           // Cache the configuration information.
diff --git a/bpkg/package.hxx b/bpkg/package.hxx
index 1a62cd9..da5c64a 100644
--- a/bpkg/package.hxx
+++ b/bpkg/package.hxx
@@ -27,7 +27,7 @@
 //
 #define DB_SCHEMA_VERSION_BASE 6
 
-#pragma db model version(DB_SCHEMA_VERSION_BASE, 9, closed)
+#pragma db model version(DB_SCHEMA_VERSION_BASE, 10, closed)
 
 namespace bpkg
 {
diff --git a/bpkg/package.xml b/bpkg/package.xml
index edb6d09..e5b28c9 100644
--- a/bpkg/package.xml
+++ b/bpkg/package.xml
@@ -1,4 +1,6 @@
 <changelog xmlns="http://www.codesynthesis.com/xmlns/odb/changelog" database="sqlite" version="1">
+  <changeset version="10"/>
+
   <changeset version="9">
     <add-table name="main.configuration" kind="object">
       <column name="id" type="INTEGER" null="true"/>
-- 
cgit v1.1