From 895bcdb7cfa178017500c6bf2518e629feafa917 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Thu, 26 May 2016 21:00:40 +0100
Subject: Improve file list merging with second temp table

---
 gentoobrowse-api/service/maintenanceimpl.cpp               | 14 ++++++++------
 .../service/sql/maintenance/createTempFileListTable.sql    |  5 -----
 gentoobrowse-api/service/sql/maintenance/fileList.sql      | 13 -------------
 .../service/sql/maintenance/fileListCreate.sql             | 12 ++++++++++++
 .../service/sql/maintenance/fileListCreatePk.sql           |  1 +
 .../service/sql/maintenance/fileListCreateRaw.sql          |  4 ++++
 6 files changed, 25 insertions(+), 24 deletions(-)
 delete mode 100644 gentoobrowse-api/service/sql/maintenance/createTempFileListTable.sql
 delete mode 100644 gentoobrowse-api/service/sql/maintenance/fileList.sql
 create mode 100644 gentoobrowse-api/service/sql/maintenance/fileListCreate.sql
 create mode 100644 gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql
 create mode 100644 gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql

diff --git a/gentoobrowse-api/service/maintenanceimpl.cpp b/gentoobrowse-api/service/maintenanceimpl.cpp
index d5c6b64..495e908 100644
--- a/gentoobrowse-api/service/maintenanceimpl.cpp
+++ b/gentoobrowse-api/service/maintenanceimpl.cpp
@@ -5,8 +5,9 @@
 #include <buffer.h>
 #include <scopeExit.h>
 #include <boost/filesystem/operations.hpp>
-#include <sql/maintenance/createTempFileListTable.sql.h>
-#include <sql/maintenance/fileList.sql.h>
+#include <sql/maintenance/fileListCreateRaw.sql.h>
+#include <sql/maintenance/fileListCreate.sql.h>
+#include <sql/maintenance/fileListCreatePk.sql.h>
 #include "maintenance/categoryMetaProcessor.h"
 #include "maintenance/packageManifestProcessor.h"
 #include "maintenance/packageMetaProcessor.h"
@@ -135,8 +136,8 @@ namespace Gentoo {
 			dbc->select("SELECT name, path FROM gentoobrowse.repos")->forEachRow<std::string, std::string>([&tmp](auto n, auto p) {
 					boost::filesystem::create_symlink(p, tmp / n);
 				});
-			dbc->execute(sql::maintenance::createTempFileListTable.getSql());
-			auto i = dbc->modify("INSERT INTO filelist(filename, filesize, filemtime) VALUES(?, ?, ?)");
+			dbc->execute(sql::maintenance::fileListCreateRaw.getSql());
+			auto i = dbc->modify("INSERT INTO filelistraw(filename, filesize, filemtime) VALUES(?, ?, ?)");
 			for (boost::filesystem::recursive_directory_iterator d(tmp, boost::filesystem::symlink_option::recurse);
 					d != boost::filesystem::recursive_directory_iterator(); d++) {
 				if (boost::filesystem::is_regular_file(d->status())) {
@@ -158,9 +159,10 @@ namespace Gentoo {
 			for (const auto & fp : fps) {
 				fp.second->prepare(dbc);
 			}
-			DB::StaticSqlWriter src(sql::maintenance::fileList.getSql());
+			dbc->execute(sql::maintenance::fileListCreate.getSql());
+			dbc->execute(sql::maintenance::fileListCreatePk.getSql());
 			DB::TablePatch tp;
-			tp.srcExpr = &src;
+			tp.src = "filelist";
 			tp.dest = "gentoobrowse.files";
 			tp.pk = {"filename"};
 			tp.cols = {"repoid", "filename", "filetypeid", "pathparts", "filesize", "moddate"};
diff --git a/gentoobrowse-api/service/sql/maintenance/createTempFileListTable.sql b/gentoobrowse-api/service/sql/maintenance/createTempFileListTable.sql
deleted file mode 100644
index dd6f33c..0000000
--- a/gentoobrowse-api/service/sql/maintenance/createTempFileListTable.sql
+++ /dev/null
@@ -1,5 +0,0 @@
-CREATE TEMPORARY TABLE filelist(
-		filename TEXT NOT NULL,
-		filesize INTEGER NOT NULL,
-		filemtime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
-		PRIMARY KEY(filename))
diff --git a/gentoobrowse-api/service/sql/maintenance/fileList.sql b/gentoobrowse-api/service/sql/maintenance/fileList.sql
deleted file mode 100644
index dd1c9da..0000000
--- a/gentoobrowse-api/service/sql/maintenance/fileList.sql
+++ /dev/null
@@ -1,13 +0,0 @@
-(
-SELECT
-	r.repoid,
-	fl.filename,
-	ft.filetypeid,
-	ft.processOrder,
-	(STRING_TO_ARRAY(fl.filename, '/'))[2:100] pathparts,
-	fl.filesize,
-	fl.filemtime moddate
-FROM filelist fl, gentoobrowse.repos r, gentoobrowse.filetypes ft
-WHERE (STRING_TO_ARRAY(fl.filename, '/'))[1] = r.name
-AND (STRING_TO_ARRAY(fl.filename, '/'))[2:100] ~ ft.spec
-)
diff --git a/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql b/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql
new file mode 100644
index 0000000..d8d627c
--- /dev/null
+++ b/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql
@@ -0,0 +1,12 @@
+CREATE TEMPORARY TABLE filelist AS
+SELECT
+	r.repoid,
+	fl.filename,
+	ft.filetypeid,
+	ft.processOrder,
+	(STRING_TO_ARRAY(fl.filename, '/'))[2:100] pathparts,
+	fl.filesize,
+	fl.filemtime moddate
+FROM filelistraw fl, gentoobrowse.repos r, gentoobrowse.filetypes ft
+WHERE (STRING_TO_ARRAY(fl.filename, '/'))[1] = r.name
+AND (STRING_TO_ARRAY(fl.filename, '/'))[2:100] ~ ft.spec
diff --git a/gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql b/gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql
new file mode 100644
index 0000000..1e56d8b
--- /dev/null
+++ b/gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql
@@ -0,0 +1 @@
+ALTER TABLE filelist ADD CONSTRAINT pk_filelist PRIMARY KEY(filename)
diff --git a/gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql b/gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql
new file mode 100644
index 0000000..0fa576a
--- /dev/null
+++ b/gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql
@@ -0,0 +1,4 @@
+CREATE TEMPORARY TABLE filelistraw (
+		filename TEXT NOT NULL,
+		filesize INTEGER NOT NULL,
+		filemtime TIMESTAMP WITHOUT TIME ZONE NOT NULL)
-- 
cgit v1.2.3