From d931d9eb511526dbaf1fc46f20d8a3c2fcda3294 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sun, 29 May 2016 13:49:11 +0100
Subject: Associate bugs with packages

---
 gentoobrowse-api/service/maintenanceBugs.cpp         |  1 +
 gentoobrowse-api/service/maintenanceCommon.cpp       | 16 ++++++++++++++++
 gentoobrowse-api/service/maintenancePackageTree.cpp  |  1 +
 gentoobrowse-api/service/maintenanceimpl.h           |  1 +
 .../service/sql/maintenance/bugAssociate.sql         |  4 ++++
 gentoobrowse-api/service/utils/dbUtils.cpp           | 20 +++++++++++++++++++-
 gentoobrowse-api/service/utils/dbUtils.h             |  1 +
 7 files changed, 43 insertions(+), 1 deletion(-)
 create mode 100644 gentoobrowse-api/service/sql/maintenance/bugAssociate.sql

diff --git a/gentoobrowse-api/service/maintenanceBugs.cpp b/gentoobrowse-api/service/maintenanceBugs.cpp
index 0917f97..88493fd 100644
--- a/gentoobrowse-api/service/maintenanceBugs.cpp
+++ b/gentoobrowse-api/service/maintenanceBugs.cpp
@@ -92,6 +92,7 @@ namespace Gentoo {
 				blp.parse_stream(cs);
 			}
 			dbc->patchTable(&tp);
+			associateBugs(dbc.get());
 		}
 	}
 }
diff --git a/gentoobrowse-api/service/maintenanceCommon.cpp b/gentoobrowse-api/service/maintenanceCommon.cpp
index c05132b..95ebba8 100644
--- a/gentoobrowse-api/service/maintenanceCommon.cpp
+++ b/gentoobrowse-api/service/maintenanceCommon.cpp
@@ -1,6 +1,9 @@
 #include "maintenanceimpl.h"
 #include <Ice/ObjectAdapter.h>
 #include <Ice/Communicator.h>
+#include <tablepatch.h>
+#include "utils/dbUtils.h"
+#include "sql/maintenance/bugAssociate.sql.h"
 
 namespace Gentoo {
 	namespace Service {
@@ -9,6 +12,19 @@ namespace Gentoo {
 		{
 			return c.adapter->getCommunicator()->getProperties();
 		}
+
+		void
+		Maintenance::associateBugs(DB::Connection * dbc)
+		{
+			DB::TablePatch tp;
+			tp.src = Utils::Database::createTempWith(dbc,
+					sql::maintenance::bugAssociate.getSql(), { "bugId", "packageId" });
+			tp.dest = "gentoobrowse.package_bugs";
+			tp.pk = { "bugId", "packageId" };
+			tp.cols = { "bugId", "packageId" };
+			dbc->patchTable(&tp);
+			Utils::Database::drop(dbc, tp.src);
+		}
 	}
 }
 
diff --git a/gentoobrowse-api/service/maintenancePackageTree.cpp b/gentoobrowse-api/service/maintenancePackageTree.cpp
index 8347d07..b7a052e 100644
--- a/gentoobrowse-api/service/maintenancePackageTree.cpp
+++ b/gentoobrowse-api/service/maintenancePackageTree.cpp
@@ -199,6 +199,7 @@ namespace Gentoo {
 			createTempFileList(dbc.get(), tmp);
 			processChanges(dbc.get(), tmp);
 			dbc->execute("SET search_path = public, pg_catalog");
+			associateBugs(dbc.get());
 		}
 	}
 }
diff --git a/gentoobrowse-api/service/maintenanceimpl.h b/gentoobrowse-api/service/maintenanceimpl.h
index 7da0d55..9bd451c 100644
--- a/gentoobrowse-api/service/maintenanceimpl.h
+++ b/gentoobrowse-api/service/maintenanceimpl.h
@@ -35,6 +35,7 @@ namespace Gentoo {
 
 				void refreshPackageTree(const Ice::Current &) override;
 				void refreshBugs(const Ice::Current &) override;
+				void associateBugs(DB::Connection *);
 
 			private:
 				static void createTempFileList(DB::Connection *, const boost::filesystem::path &);
diff --git a/gentoobrowse-api/service/sql/maintenance/bugAssociate.sql b/gentoobrowse-api/service/sql/maintenance/bugAssociate.sql
new file mode 100644
index 0000000..e51d4d9
--- /dev/null
+++ b/gentoobrowse-api/service/sql/maintenance/bugAssociate.sql
@@ -0,0 +1,4 @@
+SELECT b.bugid, p.packageid
+FROM gentoobrowse.bugs b, gentoobrowse.packages p, gentoobrowse.categories c
+WHERE p.categoryid = c.categoryid
+AND gentoobrowse.namedpackages(b.summary) @> ARRAY[c.name || '/' || p.name]
diff --git a/gentoobrowse-api/service/utils/dbUtils.cpp b/gentoobrowse-api/service/utils/dbUtils.cpp
index 1b38da6..ae07ff4 100644
--- a/gentoobrowse-api/service/utils/dbUtils.cpp
+++ b/gentoobrowse-api/service/utils/dbUtils.cpp
@@ -2,6 +2,8 @@
 #include <boost/lexical_cast.hpp>
 #include <boost/algorithm/string/join.hpp>
 #include <tablepatch.h>
+#include <buffer.h>
+#include <atomic>
 
 namespace Gentoo {
 	namespace Utils {
@@ -30,6 +32,22 @@ namespace Gentoo {
 				}
 			}
 
+			std::atomic<uint16_t> tempTableNumber;
+			std::string
+			tempTableName()
+			{
+				return stringbf("_tmpTable_%x", tempTableNumber++);
+			}
+
+			std::string
+			createTempWith(DB::Connection * db, const std::string & sql, const std::set<std::string> & keys)
+			{
+				auto tempTable = tempTableName();
+				db->execute("CREATE TEMPORARY TABLE " + tempTable + " AS  " + sql);
+				db->execute("ALTER TABLE " + tempTable + " ADD CONSTRAINT pk" + tempTable + " PRIMARY KEY (" + boost::algorithm::join(keys, ",") + ")");
+				return tempTable;
+			}
+
 			std::string
 			emptyClone(DB::Connection * db, const std::string & orig)
 			{
@@ -60,7 +78,7 @@ namespace Gentoo {
 				}
 				db->execute("CREATE TEMPORARY TABLE " + tempTable + "(" +
 						boost::join(defs, ",") + ")");
-				return { tempTable, tablePatchInserter(db, tempTable, keys) };	
+				return { tempTable, tablePatchInserter(db, tempTable, keys) };
 			}
 
 			void
diff --git a/gentoobrowse-api/service/utils/dbUtils.h b/gentoobrowse-api/service/utils/dbUtils.h
index b533783..aa46c33 100644
--- a/gentoobrowse-api/service/utils/dbUtils.h
+++ b/gentoobrowse-api/service/utils/dbUtils.h
@@ -13,6 +13,7 @@ namespace Gentoo {
 			bool bindOptionalsS(DB::Command * db, unsigned int c, const std::vector<boost::optional<Glib::ustring> > & vs);
 			void bindOptionalS(DB::Command * db, unsigned int c, const IceUtil::Optional<std::string> & v);
 
+			std::string createTempWith(DB::Connection *, const std::string &, const std::set<std::string> & keys = std::set<std::string>());
 			std::string emptyClone(DB::Connection *, const std::string &);
 			std::pair<std::string, DB::ModifyCommandPtr> customTemp(DB::Connection *, const std::map<std::string, const std::string> & cols);
 			std::pair<std::string, DB::ModifyCommandPtr> namedTemp(DB::Connection *, const std::string &, const std::map<std::string, const std::string> & cols);
-- 
cgit v1.2.3