From ddebe2d4c7fa4220d2cfad3693edfb15bf7a737c Mon Sep 17 00:00:00 2001
From: Adam Ierymenko <adam.ierymenko@gmail.com>
Date: Tue, 21 Apr 2015 16:41:35 -0700
Subject: Network controller CRUD... :P

---
 controller/schema.sql | 5 ++++-
 1 file changed, 4 insertions(+), 1 deletion(-)

(limited to 'controller/schema.sql')

diff --git a/controller/schema.sql b/controller/schema.sql
index f6d6e7e7..0d17a94b 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -71,9 +71,11 @@ CREATE TABLE Network (
 CREATE TABLE Relay (
   networkId char(16) NOT NULL,
   nodeId char(10) NOT NULL,
-  address varchar(64) NOT NULL
+  phyAddress varchar(64) NOT NULL
 );
 
+CREATE INDEX Relay_networkId ON Relay (networkId);
+
 CREATE UNIQUE INDEX Relay_networkId_nodeId ON Relay (networkId, nodeId);
 
 CREATE TABLE Node (
@@ -86,6 +88,7 @@ CREATE TABLE Node (
 
 CREATE TABLE Rule (
   networkId char(16) NOT NULL,
+  ordering integer NOT NULL DEFAULT(0),
   nodeId char(10),
   vlanId integer,
   vlanPcp integer,
-- 
cgit v1.2.3


From 71f006cbeb9f2f0c437f17a0ef577d6fdfa630f4 Mon Sep 17 00:00:00 2001
From: Adam Ierymenko <adam.ierymenko@gmail.com>
Date: Tue, 21 Apr 2015 18:08:33 -0700
Subject: More CRUD, almost done...

---
 controller/SqliteNetworkController.cpp | 142 ++++++++++++++++++++++++++++-----
 controller/SqliteNetworkController.hpp |   5 ++
 controller/schema.sql                  |   2 +-
 controller/schema.sql.c                |   2 +-
 4 files changed, 128 insertions(+), 23 deletions(-)

(limited to 'controller/schema.sql')

diff --git a/controller/SqliteNetworkController.cpp b/controller/SqliteNetworkController.cpp
index 9bb1fa41..38bb796d 100644
--- a/controller/SqliteNetworkController.cpp
+++ b/controller/SqliteNetworkController.cpp
@@ -172,7 +172,12 @@ SqliteNetworkController::SqliteNetworkController(const char *dbPath) :
 			||(sqlite3_prepare_v2(_db,"SELECT m.authorized,m.activeBridge,n.id,n.lastAt,n.lastSeen,n.firstSeen FROM Member AS m,Node AS n WHERE m.networkId = ? AND n.id = m.nodeId ORDER BY n.id ASC",-1,&_sListNetworkMembers,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT m.authorized,m.activeBridge,n.identity,n.lastAt,n.lastSeen,n.firstSeen FROM Member AS m,Node AS n WHERE m.networkId = ? AND m.nodeId = ?",-1,&_sGetMember2,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT ipNetwork,ipNetmaskBits,ipVersion,active FROM IpAssignmentPool WHERE networkId = ? ORDER BY ipNetwork ASC",-1,&_sGetIpAssignmentPools2,(const char **)0) != SQLITE_OK)
-			||(sqlite3_prepare_v2(_db,"SELECT nodeId,vlanId,vlanPcp,etherType,macSource,macDest,ipSource,ipDest,ipTos,ipProtocol,ipSourcePort,ipDestPort,\"action\" FROM Rule WHERE networkId = ? ORDER BY ordering ASC",-1,&_sListRules,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT ruleId,nodeId,vlanId,vlanPcp,etherType,macSource,macDest,ipSource,ipDest,ipTos,ipProtocol,ipSourcePort,ipDestPort,\"action\" FROM Rule WHERE networkId = ? ORDER BY ruleId ASC",-1,&_sListRules,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"DELETE FROM Rule WHERE networkId = ? AND ruleId = ?",-1,&_sDeleteRule,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"INSERT INTO Rule (networkId,ruleId,nodeId,vlanId,vlanPcP,etherType,macSource,macDest,ipSource,ipDest,ipTos,ipProtocol,ipSourcePort,ipDestPort,\"action\") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",-1,&_sCreateRule,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"INSERT INTO Network (networkId,name,creationTime,revision) VALUES (?,?,?,1)",-1,&_sCreateNetwork,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"UPDATE Network SET ? = ? WHERE networkId = ?",-1,&_sUpdateNetworkField,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT revision FROM Network WHERE id = ?",-1,&_sGetNetworkRevision,(const char **)0) != SQLITE_OK)
 		 ) {
 		sqlite3_close(_db);
 		throw std::runtime_error("SqliteNetworkController unable to initialize one or more prepared statements");
@@ -205,6 +210,11 @@ SqliteNetworkController::~SqliteNetworkController()
 		sqlite3_finalize(_sGetMember2);
 		sqlite3_finalize(_sGetIpAssignmentPools2);
 		sqlite3_finalize(_sListRules);
+		sqlite3_finalize(_sDeleteRule);
+		sqlite3_finalize(_sCreateRule);
+		sqlite3_finalize(_sCreateNetwork);
+		sqlite3_finalize(_sUpdateNetworkField);
+		sqlite3_finalize(_sGetNetworkRevision);
 		sqlite3_close(_db);
 	}
 }
@@ -700,56 +710,58 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpGET(
 					bool firstRule = true;
 					while (sqlite3_step(_sListRules) == SQLITE_ROW) {
 						responseBody.append(firstRule ? "\n\t{\n" : ",{\n");
-						if (sqlite3_column_type(_sListRules,0) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tnodeId: \"%s\",\n",(const char *)sqlite3_column_text(_sListRules,0));
-							responseBody.append(json);
-						}
+						Utils::snprintf(json,sizeof(json),"\t\truleId: %lld,\n",sqlite3_column_int64(_sListRules,0));
+						responseBody.append(json);
 						if (sqlite3_column_type(_sListRules,1) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tvlanId: %d,\n",sqlite3_column_int(_sListRules,1));
+							Utils::snprintf(json,sizeof(json),"\t\tnodeId: \"%s\",\n",(const char *)sqlite3_column_text(_sListRules,1));
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,2) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tvlanPcp: %d,\n",sqlite3_column_int(_sListRules,2));
+							Utils::snprintf(json,sizeof(json),"\t\tvlanId: %d,\n",sqlite3_column_int(_sListRules,2));
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,3) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tetherType: %d,\n",sqlite3_column_int(_sListRules,3));
+							Utils::snprintf(json,sizeof(json),"\t\tvlanPcp: %d,\n",sqlite3_column_int(_sListRules,3));
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,4) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tmacSource: \"%s\",\n",MAC((const char *)sqlite3_column_text(_sListRules,4)).toString().c_str());
+							Utils::snprintf(json,sizeof(json),"\t\tetherType: %d,\n",sqlite3_column_int(_sListRules,4));
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,5) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tmacDest: \"%s\",\n",MAC((const char *)sqlite3_column_text(_sListRules,5)).toString().c_str());
+							Utils::snprintf(json,sizeof(json),"\t\tmacSource: \"%s\",\n",MAC((const char *)sqlite3_column_text(_sListRules,5)).toString().c_str());
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,6) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tipSource: \"%s\",\n",_jsonEscape((const char *)sqlite3_column_text(_sListRules,6)).c_str());
+							Utils::snprintf(json,sizeof(json),"\t\tmacDest: \"%s\",\n",MAC((const char *)sqlite3_column_text(_sListRules,6)).toString().c_str());
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,7) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tipDest: \"%s\",\n",_jsonEscape((const char *)sqlite3_column_text(_sListRules,7)).c_str());
+							Utils::snprintf(json,sizeof(json),"\t\tipSource: \"%s\",\n",_jsonEscape((const char *)sqlite3_column_text(_sListRules,7)).c_str());
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,8) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tipTos: %d,\n",sqlite3_column_int(_sListRules,8));
+							Utils::snprintf(json,sizeof(json),"\t\tipDest: \"%s\",\n",_jsonEscape((const char *)sqlite3_column_text(_sListRules,8)).c_str());
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,9) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tipProtocol: %d,\n",sqlite3_column_int(_sListRules,9));
+							Utils::snprintf(json,sizeof(json),"\t\tipTos: %d,\n",sqlite3_column_int(_sListRules,9));
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,10) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tipSourcePort: %d,\n",sqlite3_column_int(_sListRules,10));
+							Utils::snprintf(json,sizeof(json),"\t\tipProtocol: %d,\n",sqlite3_column_int(_sListRules,10));
 							responseBody.append(json);
 						}
 						if (sqlite3_column_type(_sListRules,11) != SQLITE_NULL) {
-							Utils::snprintf(json,sizeof(json),"\t\tipDestPort: %d,\n",sqlite3_column_int(_sListRules,11));
+							Utils::snprintf(json,sizeof(json),"\t\tipSourcePort: %d,\n",sqlite3_column_int(_sListRules,11));
+							responseBody.append(json);
+						}
+						if (sqlite3_column_type(_sListRules,12) != SQLITE_NULL) {
+							Utils::snprintf(json,sizeof(json),"\t\tipDestPort: %d,\n",sqlite3_column_int(_sListRules,12));
 							responseBody.append(json);
 						}
 						responseBody.append("\t\taction: \"");
-						responseBody.append(_jsonEscape((const char *)sqlite3_column_text(_sListRules,12)));
+						responseBody.append(_jsonEscape((const char *)sqlite3_column_text(_sListRules,13)));
 						responseBody.append("\"\n\t}");
 					}
 
@@ -800,20 +812,108 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpPOST(
 			char nwids[24];
 			Utils::snprintf(nwids,sizeof(nwids),"%.16llx",(unsigned long long)nwid);
 
+			int64_t revision = 0;
+			sqlite3_reset(_sGetNetworkRevision);
+			sqlite3_bind_text(_sGetNetworkRevision,1,nwids,16,SQLITE_STATIC);
+			if (sqlite3_step(_sGetNetworkRevision) == SQLITE_ROW)
+				revision = sqlite3_column_int64(_sGetNetworkRevision,0);
+
 			if (path.size() >= 3) {
+
 				if ((path.size() == 4)&&(path[2] == "member")&&(path[3].length() == 10)) {
 					uint64_t address = Utils::hexStrToU64(path[3].c_str());
 					char addrs[24];
 					Utils::snprintf(addrs,sizeof(addrs),"%.10llx",address);
 
-				} else if (path[2] == "rule") {
+					return handleControlPlaneHttpGET(path,urlArgs,headers,body,responseBody,responseContentType);
+				} // else 404
 
+			} else {
 
-				} else if (path[2] == "ipAssignmentPool") {
+				if (revision <= 0) {
+					sqlite3_reset(_sCreateNetwork);
+					sqlite3_bind_text(_sCreateNetwork,1,nwids,16,SQLITE_STATIC);
+					sqlite3_bind_text(_sCreateNetwork,2,nwids,16,SQLITE_STATIC); // default name, will be changed below if a name is specified in JSON
+					sqlite3_bind_int64(_sCreateNetwork,3,(long long)OSUtils::now());
+					if (sqlite3_step(_sCreateNetwork) != SQLITE_DONE)
+						return 500;
+				}
 
-				} // else 404
-			} else {
+				json_value *j = json_parse(body.c_str(),body.length());
+				if (j) {
+					if (j->type == json_object) {
+						for(unsigned int k=0;k<j->u.object.length;++k) {
+							sqlite3_reset(_sUpdateNetworkField);
+							sqlite3_bind_text(_sUpdateNetworkField,3,nwids,16,SQLITE_STATIC);
+
+							if (!strcmp(j->u.object.values[k].name,"name")) {
+								if ((j->u.object.values[k].value->type == json_string)&&(j->u.object.values[k].value->u.string.ptr[0])) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"name",-1,SQLITE_STATIC);
+									sqlite3_bind_text(_sUpdateNetworkField,2,j->u.object.values[k].value->u.string.ptr,-1,SQLITE_STATIC);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"private")) {
+								if (j->u.object.values[k].value->type == json_boolean) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"private",-1,SQLITE_STATIC);
+									sqlite3_bind_int(_sUpdateNetworkField,2,(j->u.object.values[k].value->u.boolean == 0) ? 0 : 1);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"enableBroadcast")) {
+								if (j->u.object.values[k].value->type == json_boolean) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"enableBroadcast",-1,SQLITE_STATIC);
+									sqlite3_bind_int(_sUpdateNetworkField,2,(j->u.object.values[k].value->u.boolean == 0) ? 0 : 1);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"allowPassiveBridging")) {
+								if (j->u.object.values[k].value->type == json_boolean) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"allowPassiveBridging",-1,SQLITE_STATIC);
+									sqlite3_bind_int(_sUpdateNetworkField,2,(j->u.object.values[k].value->u.boolean == 0) ? 0 : 1);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"v4AssignMode")) {
+								if (j->u.object.values[k].value->type == json_string) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"v4AssignMode",-1,SQLITE_STATIC);
+									sqlite3_bind_text(_sUpdateNetworkField,2,j->u.object.values[k].value->u.string.ptr,-1,SQLITE_STATIC);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"v6AssignMode")) {
+								if (j->u.object.values[k].value->type == json_string) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"v6AssignMode",-1,SQLITE_STATIC);
+									sqlite3_bind_text(_sUpdateNetworkField,2,j->u.object.values[k].value->u.string.ptr,-1,SQLITE_STATIC);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"multicastLimit")) {
+								if (j->u.object.values[k].value->type == json_integer) {
+									sqlite3_bind_text(_sUpdateNetworkField,1,"multicastLimit",-1,SQLITE_STATIC);
+									sqlite3_bind_int(_sUpdateNetworkField,2,(int)j->u.object.values[k].value->u.integer);
+									sqlite3_step(_sUpdateNetworkField);
+								} else return 400;
+							}
+
+							if (!strcmp(j->u.object.values[k].name,"relays")) {
+								if (j->u.object.values[k].value->type == json_array) {
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"ipAssignmentPools")) {
+								if (j->u.object.values[k].value->type == json_array) {
+								} else return 400;
+							}
+							if (!strcmp(j->u.object.values[k].name,"rules")) {
+								if (j->u.object.values[k].value->type == json_array) {
+								} else return 400;
+							}
+						}
+					}
+					json_value_free(j);
+				}
 
+				return handleControlPlaneHttpGET(path,urlArgs,headers,body,responseBody,responseContentType);
 			}
 
 		} // else 404
diff --git a/controller/SqliteNetworkController.hpp b/controller/SqliteNetworkController.hpp
index 3050f24d..045faf43 100644
--- a/controller/SqliteNetworkController.hpp
+++ b/controller/SqliteNetworkController.hpp
@@ -109,6 +109,11 @@ private:
 	sqlite3_stmt *_sGetMember2;
 	sqlite3_stmt *_sGetIpAssignmentPools2;
 	sqlite3_stmt *_sListRules;
+	sqlite3_stmt *_sDeleteRule;
+	sqlite3_stmt *_sCreateRule;
+	sqlite3_stmt *_sCreateNetwork;
+	sqlite3_stmt *_sUpdateNetworkField;
+	sqlite3_stmt *_sGetNetworkRevision;
 
 	Mutex _lock;
 };
diff --git a/controller/schema.sql b/controller/schema.sql
index 0d17a94b..ef7a2c4a 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -88,7 +88,7 @@ CREATE TABLE Node (
 
 CREATE TABLE Rule (
   networkId char(16) NOT NULL,
-  ordering integer NOT NULL DEFAULT(0),
+  ruleId integer NOT NULL,
   nodeId char(10),
   vlanId integer,
   vlanPcp integer,
diff --git a/controller/schema.sql.c b/controller/schema.sql.c
index 1161451b..0af4eec0 100644
--- a/controller/schema.sql.c
+++ b/controller/schema.sql.c
@@ -89,7 +89,7 @@
 "\n"\
 "CREATE TABLE Rule (\n"\
 "  networkId char(16) NOT NULL,\n"\
-"  ordering integer NOT NULL DEFAULT(0),\n"\
+"  ruleId integer NOT NULL,\n"\
 "  nodeId char(10),\n"\
 "  vlanId integer,\n"\
 "  vlanPcp integer,\n"\
-- 
cgit v1.2.3


From 103dcb072da74e6d074b12f28a5be023c392cf77 Mon Sep 17 00:00:00 2001
From: Adam Ierymenko <adam.ierymenko@gmail.com>
Date: Tue, 21 Apr 2015 19:49:04 -0700
Subject: CRUD

---
 controller/SqliteNetworkController.cpp | 93 ++++++++++++++++++++++++----------
 controller/SqliteNetworkController.hpp |  3 ++
 controller/schema.sql                  |  3 +-
 controller/schema.sql.c                |  3 +-
 4 files changed, 71 insertions(+), 31 deletions(-)

(limited to 'controller/schema.sql')

diff --git a/controller/SqliteNetworkController.cpp b/controller/SqliteNetworkController.cpp
index 655f5c04..6f82c1b5 100644
--- a/controller/SqliteNetworkController.cpp
+++ b/controller/SqliteNetworkController.cpp
@@ -164,21 +164,24 @@ SqliteNetworkController::SqliteNetworkController(const char *dbPath) :
 			||(sqlite3_prepare_v2(_db,"SELECT mgMac,mgAdi,preload,maxBalance,accrual FROM MulticastRate WHERE networkId = ?",-1,&_sGetMulticastRates,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT nodeId FROM Member WHERE networkId = ? AND activeBridge > 0 AND authorized > 0",-1,&_sGetActiveBridges,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT ip,ipNetmaskBits FROM IpAssignment WHERE networkId = ? AND nodeId = ? AND ipVersion = ?",-1,&_sGetIpAssignmentsForNode,(const char **)0) != SQLITE_OK)
-			||(sqlite3_prepare_v2(_db,"SELECT ipNetwork,ipNetmaskBits FROM IpAssignmentPool WHERE networkId = ? AND ipVersion = ? AND active > 0",-1,&_sGetIpAssignmentPools,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT ipNetwork,ipNetmaskBits FROM IpAssignmentPool WHERE networkId = ? AND ipVersion = ?",-1,&_sGetIpAssignmentPools,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT 1 FROM IpAssignment WHERE networkId = ? AND ip = ? AND ipVersion = ?",-1,&_sCheckIfIpIsAllocated,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"INSERT INTO IpAssignment (networkId,nodeId,ip,ipNetmaskBits,ipVersion) VALUES (?,?,?,?,?)",-1,&_sAllocateIp,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"UPDATE Member SET cachedNetconf = ?,cachedNetconfRevision = ? WHERE rowid = ?",-1,&_sCacheNetconf,(const char **)0) != SQLITE_OK)
-			||(sqlite3_prepare_v2(_db,"SELECT nodeId,phyAddress FROM Relay WHERE networkId = ?",-1,&_sGetRelays,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT nodeId,phyAddress FROM Relay WHERE networkId = ? ORDER BY nodeId ASC",-1,&_sGetRelays,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT id FROM Network ORDER BY id ASC",-1,&_sListNetworks,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT m.authorized,m.activeBridge,n.id,n.lastAt,n.lastSeen,n.firstSeen FROM Member AS m,Node AS n WHERE m.networkId = ? AND n.id = m.nodeId ORDER BY n.id ASC",-1,&_sListNetworkMembers,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT m.authorized,m.activeBridge,n.identity,n.lastAt,n.lastSeen,n.firstSeen FROM Member AS m,Node AS n WHERE m.networkId = ? AND m.nodeId = ?",-1,&_sGetMember2,(const char **)0) != SQLITE_OK)
-			||(sqlite3_prepare_v2(_db,"SELECT ipNetwork,ipNetmaskBits,ipVersion,active FROM IpAssignmentPool WHERE networkId = ? ORDER BY ipNetwork ASC",-1,&_sGetIpAssignmentPools2,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT ipNetwork,ipNetmaskBits,ipVersion FROM IpAssignmentPool WHERE networkId = ? ORDER BY ipNetwork ASC",-1,&_sGetIpAssignmentPools2,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT ruleId,nodeId,vlanId,vlanPcp,etherType,macSource,macDest,ipSource,ipDest,ipTos,ipProtocol,ipSourcePort,ipDestPort,\"action\" FROM Rule WHERE networkId = ? ORDER BY ruleId ASC",-1,&_sListRules,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"DELETE FROM Rule WHERE networkId = ? AND ruleId = ?",-1,&_sDeleteRule,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"INSERT INTO Rule (networkId,ruleId,nodeId,vlanId,vlanPcP,etherType,macSource,macDest,ipSource,ipDest,ipTos,ipProtocol,ipSourcePort,ipDestPort,\"action\") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",-1,&_sCreateRule,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"INSERT INTO Network (networkId,name,creationTime,revision) VALUES (?,?,?,1)",-1,&_sCreateNetwork,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"UPDATE Network SET ? = ? WHERE networkId = ?",-1,&_sUpdateNetworkField,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"SELECT revision FROM Network WHERE id = ?",-1,&_sGetNetworkRevision,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT ip,ipNetmaskBits,ipVersion FROM IpAssignment WHERE networkId = ? AND nodeId = ?",-1,&_sGetIpAssignmentsForNode2,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"DELETE FROM Relay WHERE networkId = ?",-1,&_sDeleteRelaysForNetwork,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"INSERT INTO Relay (networkId,nodeId,phyAddress) VALUES (?,?,?)",-1,&_sCreateRelay,(const char **)0) != SQLITE_OK)
 		 ) {
 		sqlite3_close(_db);
 		throw std::runtime_error("SqliteNetworkController unable to initialize one or more prepared statements");
@@ -216,6 +219,9 @@ SqliteNetworkController::~SqliteNetworkController()
 		sqlite3_finalize(_sCreateNetwork);
 		sqlite3_finalize(_sUpdateNetworkField);
 		sqlite3_finalize(_sGetNetworkRevision);
+		sqlite3_finalize(_sGetIpAssignmentsForNode2);
+		sqlite3_finalize(_sDeleteRelaysForNetwork);
+		sqlite3_finalize(_sCreateRelay);
 		sqlite3_close(_db);
 	}
 }
@@ -609,8 +615,8 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpGET(
 							"\tlastAt: \"%s\",\n"
 							"\tlastSeen: %llu,\n"
 							"\tfirstSeen: %llu,\n"
-							"\tidentity: \"%s\"\n"
-							"}\n",
+							"\tidentity: \"%s\",\n"
+							"\tipAssignments: [",
 							addrs,
 							(sqlite3_column_int(_sGetMember2,0) > 0) ? "true" : "false",
 							(sqlite3_column_int(_sGetMember2,1) > 0) ? "true" : "false",
@@ -619,6 +625,21 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpGET(
 							(unsigned long long)sqlite3_column_int64(_sGetMember2,5),
 							_jsonEscape((const char *)sqlite3_column_text(_sGetMember2,2)).c_str());
 						responseBody = json;
+
+						sqlite3_reset(_sGetIpAssignmentsForNode2);
+						sqlite3_bind_text(_sGetIpAssignmentsForNode2,1,nwids,16,SQLITE_STATIC);
+						sqlite3_bind_text(_sGetIpAssignmentsForNode2,2,addrs,10,SQLITE_STATIC);
+						bool firstIp = true;
+						while (sqlite3_step(_sGetIpAssignmentPools2) == SQLITE_ROW) {
+							InetAddress ip((const void *)sqlite3_column_blob(_sGetIpAssignmentsForNode2,0),(sqlite3_column_int(_sGetIpAssignmentsForNode2,2) == 6) ? 16 : 4,(unsigned int)sqlite3_column_int(_sGetIpAssignmentPools2,1));
+							responseBody.append(firstIp ? "\"" : ",\"");
+							firstIp = false;
+							responseBody.append(_jsonEscape(ip.toString()));
+							responseBody.push_back('"');
+						}
+
+						responseBody.append("]\n}\n");
+
 						responseContentType = "application/json";
 						return 200;
 					} // else 404
@@ -699,11 +720,10 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpGET(
 						responseBody.append(firstIpAssignmentPool ? "\n\t\t" : ",\n\t\t");
 						firstIpAssignmentPool = false;
 						InetAddress ipp((const void *)sqlite3_column_blob(_sGetIpAssignmentPools2,0),(sqlite3_column_int(_sGetIpAssignmentPools2,2) == 6) ? 16 : 4,(unsigned int)sqlite3_column_int(_sGetIpAssignmentPools2,1));
-						Utils::snprintf(json,sizeof(json),"{ipNetwork:\"%s\",ipNetmaskBits:%u,ipVersion:%d,active:%s}",
+						Utils::snprintf(json,sizeof(json),"{ipNetwork:\"%s\",ipNetmaskBits:%u,ipVersion:%d}",
 							_jsonEscape(ipp.toIpString()).c_str(),
 							ipp.netmaskBits(),
-							sqlite3_column_int(_sGetIpAssignmentPools2,2),
-							(sqlite3_column_int(_sGetIpAssignmentPools2,3) > 0) ? "true" : "false");
+							sqlite3_column_int(_sGetIpAssignmentPools2,2));
 						responseBody.append(json);
 					}
 					responseBody.append("],\n\trules: [");
@@ -855,59 +875,78 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpPOST(
 									sqlite3_bind_text(_sUpdateNetworkField,2,j->u.object.values[k].value->u.string.ptr,-1,SQLITE_STATIC);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"private")) {
+							} else if (!strcmp(j->u.object.values[k].name,"private")) {
 								if (j->u.object.values[k].value->type == json_boolean) {
 									sqlite3_bind_text(_sUpdateNetworkField,1,"private",-1,SQLITE_STATIC);
 									sqlite3_bind_int(_sUpdateNetworkField,2,(j->u.object.values[k].value->u.boolean == 0) ? 0 : 1);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"enableBroadcast")) {
+							} else if (!strcmp(j->u.object.values[k].name,"enableBroadcast")) {
 								if (j->u.object.values[k].value->type == json_boolean) {
 									sqlite3_bind_text(_sUpdateNetworkField,1,"enableBroadcast",-1,SQLITE_STATIC);
 									sqlite3_bind_int(_sUpdateNetworkField,2,(j->u.object.values[k].value->u.boolean == 0) ? 0 : 1);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"allowPassiveBridging")) {
+							} else if (!strcmp(j->u.object.values[k].name,"allowPassiveBridging")) {
 								if (j->u.object.values[k].value->type == json_boolean) {
 									sqlite3_bind_text(_sUpdateNetworkField,1,"allowPassiveBridging",-1,SQLITE_STATIC);
 									sqlite3_bind_int(_sUpdateNetworkField,2,(j->u.object.values[k].value->u.boolean == 0) ? 0 : 1);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"v4AssignMode")) {
+							} else if (!strcmp(j->u.object.values[k].name,"v4AssignMode")) {
 								if (j->u.object.values[k].value->type == json_string) {
 									sqlite3_bind_text(_sUpdateNetworkField,1,"v4AssignMode",-1,SQLITE_STATIC);
 									sqlite3_bind_text(_sUpdateNetworkField,2,j->u.object.values[k].value->u.string.ptr,-1,SQLITE_STATIC);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"v6AssignMode")) {
+							} else if (!strcmp(j->u.object.values[k].name,"v6AssignMode")) {
 								if (j->u.object.values[k].value->type == json_string) {
 									sqlite3_bind_text(_sUpdateNetworkField,1,"v6AssignMode",-1,SQLITE_STATIC);
 									sqlite3_bind_text(_sUpdateNetworkField,2,j->u.object.values[k].value->u.string.ptr,-1,SQLITE_STATIC);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"multicastLimit")) {
+							} else if (!strcmp(j->u.object.values[k].name,"multicastLimit")) {
 								if (j->u.object.values[k].value->type == json_integer) {
 									sqlite3_bind_text(_sUpdateNetworkField,1,"multicastLimit",-1,SQLITE_STATIC);
 									sqlite3_bind_int(_sUpdateNetworkField,2,(int)j->u.object.values[k].value->u.integer);
 									sqlite3_step(_sUpdateNetworkField);
 								} else return 400;
-							}
-
-							if (!strcmp(j->u.object.values[k].name,"relays")) {
+							} else if (!strcmp(j->u.object.values[k].name,"relays")) {
 								if (j->u.object.values[k].value->type == json_array) {
+									std::map<Address,InetAddress> nodeIdToPhyAddress;
+									for(unsigned int kk=0;kk<j->u.object.values[k].value->u.array.length;++kk) {
+										json_value *relay = j->u.object.values[k].value->u.array.values[kk];
+										const char *address = (const char *)0;
+										const char *phyAddress = (const char *)0;
+										if ((relay)&&(relay->type == json_object)) {
+											for(unsigned int rk=0;rk<relay->u.object.length;++rk) {
+												if ((!strcmp(relay->u.object.values[rk].name,"address"))&&(relay->u.object.values[rk].value->type == json_string))
+													address = relay->u.object.values[rk].value->u.string.ptr;
+												else if ((!strcmp(relay->u.object.values[rk].name,"phyAddress"))&&(relay->u.object.values[rk].value->type == json_string))
+													phyAddress = relay->u.object.values[rk].value->u.string.ptr;
+												else return 400;
+											}
+										}
+										if ((address)&&(phyAddress))
+											nodeIdToPhyAddress[Address(address)] = InetAddress(phyAddress);
+									}
+
+									sqlite3_reset(_sDeleteRelaysForNetwork);
+									sqlite3_bind_text(_sDeleteRelaysForNetwork,1,nwids,16,SQLITE_STATIC);
+									sqlite3_step(_sDeleteRelaysForNetwork);
+
+									for(std::map<Address,InetAddress>::iterator rl(nodeIdToPhyAddress.begin());rl!=nodeIdToPhyAddress.end();++rl) {
+										sqlite3_reset(_sCreateRelay);
+										sqlite3_bind_text(_sCreateRelay,1,nwids,16,SQLITE_STATIC);
+										sqlite3_bind_text(_sCreateRelay,2,rl->first.toString().c_str(),-1,SQLITE_STATIC);
+										sqlite3_bind_text(_sCreateRelay,3,rl->second.toString().c_str(),-1,SQLITE_STATIC);
+										sqlite3_step(_sCreateRelay);
+									}
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"ipAssignmentPools")) {
+							} else if (!strcmp(j->u.object.values[k].name,"ipAssignmentPools")) {
 								if (j->u.object.values[k].value->type == json_array) {
 								} else return 400;
-							}
-							if (!strcmp(j->u.object.values[k].name,"rules")) {
+							} else if (!strcmp(j->u.object.values[k].name,"rules")) {
 								if (j->u.object.values[k].value->type == json_array) {
 								} else return 400;
 							}
diff --git a/controller/SqliteNetworkController.hpp b/controller/SqliteNetworkController.hpp
index 045faf43..9108d240 100644
--- a/controller/SqliteNetworkController.hpp
+++ b/controller/SqliteNetworkController.hpp
@@ -114,6 +114,9 @@ private:
 	sqlite3_stmt *_sCreateNetwork;
 	sqlite3_stmt *_sUpdateNetworkField;
 	sqlite3_stmt *_sGetNetworkRevision;
+	sqlite3_stmt *_sGetIpAssignmentsForNode2;
+	sqlite3_stmt *_sDeleteRelaysForNetwork;
+	sqlite3_stmt *_sCreateRelay;
 
 	Mutex _lock;
 };
diff --git a/controller/schema.sql b/controller/schema.sql
index ef7a2c4a..dba93039 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -21,8 +21,7 @@ CREATE TABLE IpAssignmentPool (
   networkId char(16) NOT NULL,
   ipNetwork blob(16) NOT NULL,
   ipNetmaskBits integer NOT NULL,
-  ipVersion integer NOT NULL DEFAULT(4),
-  active integer NOT NULL DEFAULT(1)
+  ipVersion integer NOT NULL DEFAULT(4)
 );
 
 CREATE INDEX IpAssignmentPool_networkId ON IpAssignmentPool (networkId);
diff --git a/controller/schema.sql.c b/controller/schema.sql.c
index 0af4eec0..f606b607 100644
--- a/controller/schema.sql.c
+++ b/controller/schema.sql.c
@@ -22,8 +22,7 @@
 "  networkId char(16) NOT NULL,\n"\
 "  ipNetwork blob(16) NOT NULL,\n"\
 "  ipNetmaskBits integer NOT NULL,\n"\
-"  ipVersion integer NOT NULL DEFAULT(4),\n"\
-"  active integer NOT NULL DEFAULT(1)\n"\
+"  ipVersion integer NOT NULL DEFAULT(4)\n"\
 ");\n"\
 "\n"\
 "CREATE INDEX IpAssignmentPool_networkId ON IpAssignmentPool (networkId);\n"\
-- 
cgit v1.2.3

