Improve file list merging with second temp table

5 files changed, 15 insertions, 14 deletions

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/fileListCreate.sql
index dd1c9da..d8d627c 100644
--- a/gentoobrowse-api/service/sql/maintenance/fileList.sql
+++ b/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql
@@ -1,4 +1,4 @@
-(
+CREATE TEMPORARY TABLE filelist AS
 SELECT
 	r.repoid,
 	fl.filename,
@@ -7,7 +7,6 @@ SELECT
 	(STRING_TO_ARRAY(fl.filename, '/'))[2:100] pathparts,
 	fl.filesize,
 	fl.filemtime moddate
-FROM filelist fl, gentoobrowse.repos r, gentoobrowse.filetypes ft
+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)