From 0410fd4824fd898fab7815c6ef67b8e39bb6d2b9 Mon Sep 17 00:00:00 2001
From: Adam Ierymenko <adam.ierymenko@gmail.com>
Date: Tue, 28 Jun 2016 12:44:47 -0700
Subject: Refactor recent member request history to fix performance problem in
 controller.

---
 controller/schema.sql | 22 +++++-----------------
 1 file changed, 5 insertions(+), 17 deletions(-)

(limited to 'controller/schema.sql')

diff --git a/controller/schema.sql b/controller/schema.sql
index 3bd263eb..105db924 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -33,23 +33,6 @@ CREATE TABLE Node (
   identity varchar(4096) NOT NULL
 );
 
-CREATE TABLE NodeHistory (
-  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
-  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
-  networkVisitCounter INTEGER NOT NULL DEFAULT(0),
-  networkRequestAuthorized INTEGER NOT NULL DEFAULT(0),
-  requestTime INTEGER NOT NULL DEFAULT(0),
-  clientMajorVersion INTEGER NOT NULL DEFAULT(0),
-  clientMinorVersion INTEGER NOT NULL DEFAULT(0),
-  clientRevision INTEGER NOT NULL DEFAULT(0),
-  networkRequestMetaData VARCHAR(1024),
-  fromAddress VARCHAR(128)
-);
-
-CREATE INDEX NodeHistory_nodeId ON NodeHistory (nodeId);
-CREATE INDEX NodeHistory_networkId ON NodeHistory (networkId);
-CREATE INDEX NodeHistory_requestTime ON NodeHistory (requestTime);
-
 CREATE TABLE IpAssignment (
   networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
   nodeId char(10) REFERENCES Node(id) ON DELETE CASCADE,
@@ -79,12 +62,17 @@ CREATE TABLE Member (
   activeBridge integer NOT NULL DEFAULT(0),
   memberRevision integer NOT NULL DEFAULT(0),
   flags integer NOT NULL DEFAULT(0),
+  lastRequestTime integer NOT NULL DEFAULT(0),
+  lastPowDifficulty integer NOT NULL DEFAULT(0),
+  lastPowTime integer NOT NULL DEFAULT(0),
+  recentHistory blob,
   PRIMARY KEY (networkId, nodeId)
 );
 
 CREATE INDEX Member_networkId_nodeId ON Member(networkId,nodeId);
 CREATE INDEX Member_networkId_activeBridge ON Member(networkId, activeBridge);
 CREATE INDEX Member_networkId_memberRevision ON Member(networkId, memberRevision);
+CREATE INDEX Member_networkId_lastRequestTime ON Member(networkId, lastRequestTime);
 
 CREATE TABLE Route (
   networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
-- 
cgit v1.2.3

