From 5bfefd061d4e7281fde2d3b1a0f11b4f93d83814 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Tue, 8 Dec 2015 19:32:53 +0000
Subject: Compile raw SQL into the library with pre-built hashes

---
 gentoobrowse-api/service/Jamfile.jam               | 24 +++++++++
 gentoobrowse-api/service/abstractDatabaseClient.h  | 10 ++--
 gentoobrowse-api/service/embed.cpp.m4              | 12 +++++
 gentoobrowse-api/service/embed.h.m4                | 12 +++++
 gentoobrowse-api/service/portageimpl.cpp           | 58 ++++++----------------
 .../service/sql/portage/findPackage.sql            |  5 ++
 .../service/sql/portage/getAllCategories.sql       |  3 ++
 .../service/sql/portage/getCategoriesInSuper.sql   |  4 ++
 .../service/sql/portage/getCategory.sql            |  3 ++
 .../sql/portage/getOverviewCategoriesInSuper.sql   |  7 +++
 .../service/sql/portage/getPackage.sql             |  3 ++
 .../service/sql/portage/getPackagesInCategory.sql  |  4 ++
 .../service/sql/portage/getPackagesSearch.sql      |  5 ++
 13 files changed, 104 insertions(+), 46 deletions(-)
 create mode 100644 gentoobrowse-api/service/embed.cpp.m4
 create mode 100644 gentoobrowse-api/service/embed.h.m4
 create mode 100644 gentoobrowse-api/service/sql/portage/findPackage.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getAllCategories.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getCategoriesInSuper.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getCategory.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getPackage.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getPackagesInCategory.sql
 create mode 100644 gentoobrowse-api/service/sql/portage/getPackagesSearch.sql

diff --git a/gentoobrowse-api/service/Jamfile.jam b/gentoobrowse-api/service/Jamfile.jam
index 45b656d..ff311a0 100644
--- a/gentoobrowse-api/service/Jamfile.jam
+++ b/gentoobrowse-api/service/Jamfile.jam
@@ -1,5 +1,9 @@
+import generators ;
+import type ;
+
 lib gentoobrowse-service :
 	[ glob *.cpp ]
+	[ glob-tree *.sql ]
 	:
 	<library>..//adhocutil
 	<library>..//dbppcore
@@ -18,3 +22,23 @@ lib gentoobrowse-service :
 	<library>../api//gentoobrowse-api
 	;
 
+path-constant root : . ;
+type.register SQL : sql ;
+type.register HEX : hex ;
+generators.register-standard sql.embed.hex : SQL : HEX ;
+generators.register-standard sql.embed : HEX : CPP H ;
+
+actions sql.embed
+{
+	m4 -DNAME="$(2[1]:B)" "$(root)/embed.h.m4" > "$(1[2])"
+	m4 -DNAME="$(2[1]:B)" "$(root)/embed.cpp.m4" > "$(1[1])"
+}
+
+actions sql.embed.hex
+{
+	xxd -i "$(2)" - | grep , > "$(1[1])"
+}
+
+IMPORT $(__name__) : sql.embed : : sql.embed ;
+IMPORT $(__name__) : sql.embed.hex : : sql.embed.hex ;
+
diff --git a/gentoobrowse-api/service/abstractDatabaseClient.h b/gentoobrowse-api/service/abstractDatabaseClient.h
index 68e30b8..a3072cb 100644
--- a/gentoobrowse-api/service/abstractDatabaseClient.h
+++ b/gentoobrowse-api/service/abstractDatabaseClient.h
@@ -13,19 +13,21 @@ class AbstractDatabaseClient {
 		typedef std::vector<std::size_t> CacheKey;
 		typedef boost::shared_ptr<boost::any> CacheItem;
 
-		template<typename Domain, typename ... Params>
-		Domain inline fetch(const std::string & select, const Params & ... params)
+		template<typename Domain, typename Sql, typename ... Params>
+		Domain inline fetch(const Params & ... params)
 		{
 			CacheKey key;
 			key.reserve(4);
-			keyPushParams(key, select, params...);
+			key.push_back(Sql::hash);
+			key.push_back(typeid(Domain).hash_code());
+			keyPushParams(key, params...);
 			if (auto cached = cache.get(key)) {
 				return boost::any_cast<Domain>(**cached);
 			}
 			Domain d;
 			{
 				auto c = db.get();
-				auto s = DB::SelectCommandPtr(c->newSelectCommand(select));
+				auto s = DB::SelectCommandPtr(c->newSelectCommand(Sql::sql));
 				bind(0, s.get(), params...);
 				d = Slicer::DeserializeAny<Slicer::SqlSelectDeserializer, Domain>(*s);
 			}
diff --git a/gentoobrowse-api/service/embed.cpp.m4 b/gentoobrowse-api/service/embed.cpp.m4
new file mode 100644
index 0000000..43901dd
--- /dev/null
+++ b/gentoobrowse-api/service/embed.cpp.m4
@@ -0,0 +1,12 @@
+define(`name', NAME)
+define(`incl', `#include "'NAME`.h"')
+define(`inclhex', `#include "'NAME`.hex"')
+incl
+
+namespace sql {
+	const std::string name::sql({
+		inclhex	
+			, 0x00});
+	const std::size_t name::hash(std::hash<std::string>()(sql));
+}
+
diff --git a/gentoobrowse-api/service/embed.h.m4 b/gentoobrowse-api/service/embed.h.m4
new file mode 100644
index 0000000..a08a1c5
--- /dev/null
+++ b/gentoobrowse-api/service/embed.h.m4
@@ -0,0 +1,12 @@
+define(`name', NAME)
+#include <functional>
+#include <string>
+
+namespace sql {
+	class name {
+		public:
+			static const std::string sql;
+			static const std::size_t hash;
+	};
+}
+
diff --git a/gentoobrowse-api/service/portageimpl.cpp b/gentoobrowse-api/service/portageimpl.cpp
index 362483b..f7881fa 100644
--- a/gentoobrowse-api/service/portageimpl.cpp
+++ b/gentoobrowse-api/service/portageimpl.cpp
@@ -1,4 +1,12 @@
 #include "portageimpl.h"
+#include <sql/portage/getCategory.h>
+#include <sql/portage/getAllCategories.h>
+#include <sql/portage/getCategoriesInSuper.h>
+#include <sql/portage/getOverviewCategoriesInSuper.h>
+#include <sql/portage/getPackage.h>
+#include <sql/portage/findPackage.h>
+#include <sql/portage/getPackagesInCategory.h>
+#include <sql/portage/getPackagesSearch.h>
 
 Portage::Portage(AdHoc::ResourcePool<DB::Connection> & d) :
 	AbstractDatabaseClient(d)
@@ -8,82 +16,48 @@ Portage::Portage(AdHoc::ResourcePool<DB::Connection> & d) :
 Gentoo::CategoryPtr
 Portage::getCategory(Ice::Int id, const Ice::Current &)
 {
-	return fetch<Gentoo::CategoryPtr>(
-			"SELECT c.categoryid, c.name, c.summary \
-			FROM gentoobrowse.categories c \
-			WHERE c.categoryid = ?", id);
+	return fetch<Gentoo::CategoryPtr, sql::getCategory>(id);
 }
 
 Gentoo::Categories
 Portage::getAllCategories(const Ice::Current &)
 {
-	return fetch<Gentoo::Categories>(
-			"SELECT c.categoryid, c.name, c.summary \
-			FROM gentoobrowse.categories c \
-			ORDER BY c.name");
+	return fetch<Gentoo::Categories, sql::getAllCategories>();
 }
 
 Gentoo::Categories
 Portage::getCategoriesInSuper(const std::string & super, const Ice::Current &)
 {
-	return fetch<Gentoo::Categories>(
-			"SELECT c.categoryid, c.name, c.summary \
-			FROM gentoobrowse.categories c \
-			WHERE c.name LIKE ? || '-%'::text \
-			ORDER BY c.name", super);
+	return fetch<Gentoo::Categories, sql::getCategoriesInSuper>(super);
 }
 
 Gentoo::OverviewCategories
 Portage::getOverviewCategoriesInSuper(const std::string & super, const Ice::Current &)
 {
-	return fetch<Gentoo::OverviewCategories>(
-			"SELECT c.categoryid, c.name, c.summary, COUNT(p.packageid) packagecount \
-			FROM gentoobrowse.categories c \
-				LEFT OUTER JOIN gentoobrowse.packages p \
-					ON p.categoryid = c.categoryid \
-			WHERE c.name LIKE ? || '-%'::text \
-			GROUP BY c.categoryid \
-			ORDER BY c.name", super);
+	return fetch<Gentoo::OverviewCategories, sql::getOverviewCategoriesInSuper>(super);
 }
 
 Gentoo::PackagePtr
 Portage::getPackage(Ice::Int id, const Ice::Current &)
 {
-	return fetch<Gentoo::PackagePtr>(
-			"SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd \
-			FROM gentoobrowse.packages p \
-			WHERE p.packageid = ?", id);
+	return fetch<Gentoo::PackagePtr, sql::getPackage>(id);
 }
 
 IceUtil::Optional<Gentoo::PackagePtr>
 Portage::findPackage(const std::string & cat, const std::string & pkg, const Ice::Current &)
 {
-	return fetch<IceUtil::Optional<Gentoo::PackagePtr>>(
-			"SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd \
-			FROM gentoobrowse.packages p, gentoobrowse.categories c \
-			WHERE c.categoryid = p.categoryid \
-			AND c.name = ? \
-			AND p.name = ?", cat, pkg);
+	return fetch<IceUtil::Optional<Gentoo::PackagePtr>, sql::findPackage>(cat, pkg);
 }
 
 Gentoo::Packages
 Portage::getPackagesInCategory(Ice::Int id, const Ice::Current &)
 {
-	return fetch<Gentoo::Packages>(
-			"SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd \
-			FROM gentoobrowse.packages p \
-			WHERE p.categoryid = ? \
-			ORDER BY p.name", id);
+	return fetch<Gentoo::Packages, sql::getPackagesInCategory>(id);
 }
 
 Gentoo::SearchResultPackages
 Portage::getPackagesSearch(const std::string & query, const Ice::Current &)
 {
-	return fetch<Gentoo::SearchResultPackages>(
-			"SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd, \
-				ts_rank(gentoobrowse.packagefts(p), plainto_tsquery('english', ?)) rank \
-			FROM gentoobrowse.packages p \
-			WHERE gentoobrowse.packagefts(p) @@ plainto_tsquery('english', ?) \
-			ORDER BY rank DESC, p.name", query, query);
+	return fetch<Gentoo::SearchResultPackages, sql::getPackagesSearch>(query, query);
 }
 
diff --git a/gentoobrowse-api/service/sql/portage/findPackage.sql b/gentoobrowse-api/service/sql/portage/findPackage.sql
new file mode 100644
index 0000000..4e305fc
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/findPackage.sql
@@ -0,0 +1,5 @@
+SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd
+FROM gentoobrowse.packages p, gentoobrowse.categories c
+WHERE c.categoryid = p.categoryid
+AND c.name = ?
+AND p.name = ?
diff --git a/gentoobrowse-api/service/sql/portage/getAllCategories.sql b/gentoobrowse-api/service/sql/portage/getAllCategories.sql
new file mode 100644
index 0000000..8bbbc34
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getAllCategories.sql
@@ -0,0 +1,3 @@
+SELECT categoryid, name, summary
+FROM gentoobrowse.categories
+ORDER BY name
diff --git a/gentoobrowse-api/service/sql/portage/getCategoriesInSuper.sql b/gentoobrowse-api/service/sql/portage/getCategoriesInSuper.sql
new file mode 100644
index 0000000..8e81913
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getCategoriesInSuper.sql
@@ -0,0 +1,4 @@
+SELECT c.categoryid, c.name, c.summary
+FROM gentoobrowse.categories c
+WHERE c.name LIKE ? || '-%'::text
+ORDER BY c.name
diff --git a/gentoobrowse-api/service/sql/portage/getCategory.sql b/gentoobrowse-api/service/sql/portage/getCategory.sql
new file mode 100644
index 0000000..27ee14d
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getCategory.sql
@@ -0,0 +1,3 @@
+SELECT c.categoryid, c.name, c.summary
+FROM gentoobrowse.categories c
+WHERE c.categoryid = ?
diff --git a/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql b/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql
new file mode 100644
index 0000000..e971aed
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql
@@ -0,0 +1,7 @@
+SELECT c.categoryid, c.name, c.summary, COUNT(p.packageid) packagecount
+FROM gentoobrowse.categories c
+	LEFT OUTER JOIN gentoobrowse.packages p
+		ON p.categoryid = c.categoryid
+WHERE c.name LIKE ? || '-%'::text
+GROUP BY c.categoryid
+ORDER BY c.name
diff --git a/gentoobrowse-api/service/sql/portage/getPackage.sql b/gentoobrowse-api/service/sql/portage/getPackage.sql
new file mode 100644
index 0000000..212b0c9
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getPackage.sql
@@ -0,0 +1,3 @@
+SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd
+FROM gentoobrowse.packages p
+WHERE p.packageid = ?
diff --git a/gentoobrowse-api/service/sql/portage/getPackagesInCategory.sql b/gentoobrowse-api/service/sql/portage/getPackagesInCategory.sql
new file mode 100644
index 0000000..011d73c
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getPackagesInCategory.sql
@@ -0,0 +1,4 @@
+SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd
+FROM gentoobrowse.packages p
+WHERE p.categoryid = ?
+ORDER BY p.name
diff --git a/gentoobrowse-api/service/sql/portage/getPackagesSearch.sql b/gentoobrowse-api/service/sql/portage/getPackagesSearch.sql
new file mode 100644
index 0000000..fe97cee
--- /dev/null
+++ b/gentoobrowse-api/service/sql/portage/getPackagesSearch.sql
@@ -0,0 +1,5 @@
+SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.maintainer, p.maintainername, p.herd,
+	ts_rank(gentoobrowse.packagefts(p), plainto_tsquery('english', ?)) rank
+FROM gentoobrowse.packages p
+WHERE gentoobrowse.packagefts(p) @@ plainto_tsquery('english', ?)
+ORDER BY rank DESC, p.name
-- 
cgit v1.2.3