From 3e3992bbab56da69fc0c39e10a11862dea7a5c43 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sun, 8 Aug 2021 09:08:25 +0100
Subject: Create indexes and keys after copying table data

---
 lib/input/sql/selectIndexes.sql  |  7 +++++++
 lib/output/pq/updateDatabase.cpp | 24 +++++++++++++++++++++++-
 lib/output/pq/updateDatabase.h   |  1 +
 test/sql/createTestTable.sql     |  1 +
 4 files changed, 32 insertions(+), 1 deletion(-)
 create mode 100644 lib/input/sql/selectIndexes.sql

diff --git a/lib/input/sql/selectIndexes.sql b/lib/input/sql/selectIndexes.sql
new file mode 100644
index 0000000..f1f3375
--- /dev/null
+++ b/lib/input/sql/selectIndexes.sql
@@ -0,0 +1,7 @@
+SELECT table_name, index_name, 
+	GROUP_CONCAT(column_name ORDER BY seq_in_index) columns, 
+	MIN(non_unique) non_unique 
+FROM information_schema.statistics s 
+WHERE table_schema = DATABASE()
+AND table_name = ?
+GROUP BY table_name, index_name
diff --git a/lib/output/pq/updateDatabase.cpp b/lib/output/pq/updateDatabase.cpp
index ea8797b..46641fc 100644
--- a/lib/output/pq/updateDatabase.cpp
+++ b/lib/output/pq/updateDatabase.cpp
@@ -10,6 +10,7 @@
 #include <input/mysqlRecordSet.h>
 #include <input/replStream.h>
 #include <input/sql/selectColumns.h>
+#include <input/sql/selectIndexes.h>
 #include <input/sql/showMasterStatus.h>
 #include <memory>
 #include <mysql_types.h>
@@ -114,7 +115,6 @@ namespace MyGrate::Output::Pq {
 						ct << " not null";
 					}
 					if (col[3]) {
-						ct << " primary key";
 						tableDef->keys += 1;
 					}
 					tableDef->columns.push_back(
@@ -123,6 +123,7 @@ namespace MyGrate::Output::Pq {
 				ct << ")";
 				this->query(ct.str().c_str());
 				this->copyTableContent(conn, tableName, tableDef);
+				this->copyIndexes(conn, tableName);
 			});
 			tables.emplace(tableName, std::move(tableDef));
 		});
@@ -252,6 +253,27 @@ namespace MyGrate::Output::Pq {
 		fclose(out);
 	}
 
+	void
+	UpdateDatabase::copyIndexes(Input::MySQLConn * conn, const char * tableName)
+	{
+		auto idxs = input::sql::selectIndexes::execute(conn, tableName);
+		for (const auto idx : *idxs) {
+			const auto [name, columns, nonunique]
+					= *idx.create<std::tuple<std::string_view, std::string_view, bool>, 3, 1>();
+			if (nonunique) {
+				query(scprintf<"CREATE INDEX %? ON %?.%?(%?)">(name, schema, tableName, columns).c_str());
+			}
+			else if (name == "PRIMARY") {
+				query(scprintf<"ALTER TABLE %?.%? ADD CONSTRAINT pk_%? PRIMARY KEY(%?)">(
+						schema, tableName, name, columns)
+								.c_str());
+			}
+			else {
+				query(scprintf<"CREATE UNIQUE INDEX %? ON %?.%?(%?)">(name, schema, tableName, columns).c_str());
+			}
+		}
+	}
+
 	void
 	UpdateDatabase::tableMap(MariaDB_Event_Ptr e)
 	{
diff --git a/lib/output/pq/updateDatabase.h b/lib/output/pq/updateDatabase.h
index 716d7f6..63a96b9 100644
--- a/lib/output/pq/updateDatabase.h
+++ b/lib/output/pq/updateDatabase.h
@@ -78,6 +78,7 @@ namespace MyGrate::Output::Pq {
 		static void copyKeys(const Row & r, const TableDefPtr &, std::back_insert_iterator<Row> &&);
 
 		void copyTableContent(Input::MySQLConn *, const char * tableName, const TableDefPtr &);
+		void copyIndexes(Input::MySQLConn *, const char * tableName);
 
 		using Tables = std::map<std::string, TableDefPtr, std::less<>>;
 		Tables tables;
diff --git a/test/sql/createTestTable.sql b/test/sql/createTestTable.sql
index fdc1c06..45f4857 100644
--- a/test/sql/createTestTable.sql
+++ b/test/sql/createTestTable.sql
@@ -10,5 +10,6 @@ CREATE TABLE session(
 	last_action varchar(255) null default null,
 
 	constraint `PRIMARY` primary key(id),
+	key created_time(created, modified),
 	constraint session_id unique(session_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 
cgit v1.2.3