From de697a1c45e8afbdd32687b9b5d203732ce5e62a Mon Sep 17 00:00:00 2001
From: Kees Bos <k.bos@capitar.com>
Date: Thu, 11 Jun 2015 11:35:25 +0200
Subject: Change schema to enforce foreing keys

The foreign keys have 'ON DELETE CASCADE' to simplify the removal
of networks etc. (controller code)

Some unique constraints are replaced with a multi column primary
key.

To update an existing database:
 * install updated binaries
 * stop service
 * sqlite3 controller.db .dump | \
    egrep '((^PRAGMA)|(^BEGIN)|(^INSERT)|(^COMMIT))' | \
    grep -v 'schemaVersion' > data.sql
 * mv controller.db controller.db.backup
 * start service
 * stop service
 * sqlite3 controller.db < data.sql
 * start service
---
 controller/schema.sql | 74 +++++++++++++++++++++++++--------------------------
 1 file changed, 36 insertions(+), 38 deletions(-)

(limited to 'controller/schema.sql')

diff --git a/controller/schema.sql b/controller/schema.sql
index b5646ee9..a3a7bb7a 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -3,9 +3,30 @@ CREATE TABLE Config (
   v varchar(1024) NOT NULL
 );
 
+CREATE TABLE Network (
+  id char(16) PRIMARY KEY NOT NULL,
+  name varchar(128) NOT NULL,
+  private integer NOT NULL DEFAULT(1),
+  enableBroadcast integer NOT NULL DEFAULT(1),
+  allowPassiveBridging integer NOT NULL DEFAULT(0),
+  v4AssignMode varchar(8) NOT NULL DEFAULT('none'),
+  v6AssignMode varchar(8) NOT NULL DEFAULT('none'),
+  multicastLimit integer NOT NULL DEFAULT(32),
+  creationTime integer NOT NULL DEFAULT(0),
+  revision integer NOT NULL DEFAULT(1)
+);
+
+CREATE TABLE Node (
+  id char(10) PRIMARY KEY NOT NULL,
+  identity varchar(4096) NOT NULL,
+  lastAt varchar(64),
+  lastSeen integer NOT NULL DEFAULT(0),
+  firstSeen integer NOT NULL DEFAULT(0)
+);
+
 CREATE TABLE IpAssignment (
-  networkId char(16) NOT NULL,
-  nodeId char(10) NOT NULL,
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
+  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
   ip blob(16) NOT NULL,
   ipNetmaskBits integer NOT NULL DEFAULT(0),
   ipVersion integer NOT NULL DEFAULT(4)
@@ -18,7 +39,7 @@ CREATE INDEX IpAssignment_networkId_nodeId ON IpAssignment (networkId, nodeId);
 CREATE INDEX IpAssignment_networkId ON IpAssignment (networkId);
 
 CREATE TABLE IpAssignmentPool (
-  networkId char(16) NOT NULL,
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
   ipNetwork blob(16) NOT NULL,
   ipNetmaskBits integer NOT NULL,
   ipVersion integer NOT NULL DEFAULT(4)
@@ -27,20 +48,19 @@ CREATE TABLE IpAssignmentPool (
 CREATE INDEX IpAssignmentPool_networkId ON IpAssignmentPool (networkId);
 
 CREATE TABLE Member (
-  networkId char(16) NOT NULL,
-  nodeId char(10) NOT NULL,
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
+  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
   authorized integer NOT NULL DEFAULT(0),
-  activeBridge integer NOT NULL DEFAULT(0)
+  activeBridge integer NOT NULL DEFAULT(0),
+  PRIMARY KEY (networkId, nodeId)
 );
 
 CREATE INDEX Member_networkId ON Member (networkId);
 
 CREATE INDEX Member_networkId_activeBridge ON Member(networkId, activeBridge);
 
-CREATE UNIQUE INDEX Member_networkId_nodeId ON Member (networkId, nodeId);
-
 CREATE TABLE MulticastRate (
-  networkId char(16) NOT NULL,
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
   mgMac char(12) NOT NULL,
   mgAdi integer NOT NULL DEFAULT(0),
   preload integer NOT NULL,
@@ -50,41 +70,19 @@ CREATE TABLE MulticastRate (
 
 CREATE INDEX MulticastRate_networkId ON MulticastRate (networkId);
 
-CREATE TABLE Network (
-  id char(16) PRIMARY KEY NOT NULL,
-  name varchar(128) NOT NULL,
-  private integer NOT NULL DEFAULT(1),
-  enableBroadcast integer NOT NULL DEFAULT(1),
-  allowPassiveBridging integer NOT NULL DEFAULT(0),
-  v4AssignMode varchar(8) NOT NULL DEFAULT('none'),
-  v6AssignMode varchar(8) NOT NULL DEFAULT('none'),
-  multicastLimit integer NOT NULL DEFAULT(32),
-  creationTime integer NOT NULL DEFAULT(0),
-  revision integer NOT NULL DEFAULT(1)
-);
-
 CREATE TABLE Relay (
-  networkId char(16) NOT NULL,
-  nodeId char(10) NOT NULL,
-  phyAddress varchar(64) NOT NULL
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
+  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
+  phyAddress varchar(64) NOT NULL,
+  PRIMARY KEY (networkId, nodeId)
 );
 
 CREATE INDEX Relay_networkId ON Relay (networkId);
 
-CREATE UNIQUE INDEX Relay_networkId_nodeId ON Relay (networkId, nodeId);
-
-CREATE TABLE Node (
-  id char(10) PRIMARY KEY NOT NULL,
-  identity varchar(4096) NOT NULL,
-  lastAt varchar(64),
-  lastSeen integer NOT NULL DEFAULT(0),
-  firstSeen integer NOT NULL DEFAULT(0)
-);
-
 CREATE TABLE Rule (
-  networkId char(16) NOT NULL,
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
   ruleId integer NOT NULL,
-  nodeId char(10),
+  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
   vlanId integer,
   vlanPcp integer,
   etherType integer,
@@ -101,4 +99,4 @@ CREATE TABLE Rule (
   "action" varchar(4096) NOT NULL DEFAULT('accept')
 );
 
-CREATE INDEX Rule_networkId ON Rule (networkId);
\ No newline at end of file
+CREATE INDEX Rule_networkId ON Rule (networkId);
-- 
cgit v1.2.3


From 8a9715f183096e35e964a195b61f84bb263a9f85 Mon Sep 17 00:00:00 2001
From: Adam Ierymenko <adam.ierymenko@gmail.com>
Date: Sat, 13 Jun 2015 10:05:34 +0200
Subject: Rename ruleId to ruleNo and optimize some indexes in Sqlite3 schema.

---
 controller/SqliteNetworkController.cpp | 16 ++++++++--------
 controller/schema.sql                  | 10 +++-------
 controller/schema.sql.c                | 10 +++-------
 service/README.md                      |  4 ++--
 4 files changed, 16 insertions(+), 24 deletions(-)

(limited to 'controller/schema.sql')

diff --git a/controller/SqliteNetworkController.cpp b/controller/SqliteNetworkController.cpp
index 67fe1f9d..5aad49ff 100644
--- a/controller/SqliteNetworkController.cpp
+++ b/controller/SqliteNetworkController.cpp
@@ -167,8 +167,8 @@ SqliteNetworkController::SqliteNetworkController(const char *dbPath) :
 			||(sqlite3_prepare_v2(_db,"SELECT n.id 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 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,\"flags\",invFlags,\"action\" FROM Rule WHERE networkId = ? ORDER BY ruleId ASC",-1,&_sListRules,(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,"SELECT ruleNo,nodeId,vlanId,vlanPcp,etherType,macSource,macDest,ipSource,ipDest,ipTos,ipProtocol,ipSourcePort,ipDestPort,\"flags\",invFlags,\"action\" FROM Rule WHERE networkId = ? ORDER BY ruleNo ASC",-1,&_sListRules,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"INSERT INTO Rule (networkId,ruleNo,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 (id,name,creationTime,revision) VALUES (?,?,?,1)",-1,&_sCreateNetwork,(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,"UPDATE Network SET revision = ? WHERE id = ?",-1,&_sSetNetworkRevision,(const char **)0) != SQLITE_OK)
@@ -855,7 +855,7 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpPOST(
 										json_value *rj = j->u.object.values[k].value->u.array.values[kk];
 										if ((rj)&&(rj->type == json_object)) {
 											struct { // NULL pointers indicate missing or NULL -- wildcards
-												const json_int_t *ruleId;
+												const json_int_t *ruleNo;
 												const char *nodeId;
 												const json_int_t *vlanId;
 												const json_int_t *vlanPcp;
@@ -875,8 +875,8 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpPOST(
 											memset(&rule,0,sizeof(rule));
 
 											for(unsigned int rk=0;rk<rj->u.object.length;++rk) {
-												if ((!strcmp(rj->u.object.values[rk].name,"ruleId"))&&(rj->u.object.values[rk].value->type == json_integer))
-													rule.ruleId = &(rj->u.object.values[rk].value->u.integer);
+												if ((!strcmp(rj->u.object.values[rk].name,"ruleNo"))&&(rj->u.object.values[rk].value->type == json_integer))
+													rule.ruleNo = &(rj->u.object.values[rk].value->u.integer);
 												else if ((!strcmp(rj->u.object.values[rk].name,"nodeId"))&&(rj->u.object.values[rk].value->type == json_string))
 													rule.nodeId = rj->u.object.values[rk].value->u.string.ptr;
 												else if ((!strcmp(rj->u.object.values[rk].name,"vlanId"))&&(rj->u.object.values[rk].value->type == json_integer))
@@ -909,11 +909,11 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpPOST(
 													rule.action = rj->u.object.values[rk].value->u.string.ptr;
 											}
 
-											if ((rule.ruleId)&&(rule.action)&&(rule.action[0])) {
+											if ((rule.ruleNo)&&(rule.action)&&(rule.action[0])) {
 												char mactmp1[16],mactmp2[16];
 												sqlite3_reset(_sCreateRule);
 												sqlite3_bind_text(_sCreateRule,1,nwids,16,SQLITE_STATIC);
-												sqlite3_bind_int64(_sCreateRule,2,*rule.ruleId);
+												sqlite3_bind_int64(_sCreateRule,2,*rule.ruleNo);
 
 												// Optional values: null by default
 												for(int i=3;i<=16;++i)
@@ -1233,7 +1233,7 @@ unsigned int SqliteNetworkController::_doCPGet(
 					bool firstRule = true;
 					while (sqlite3_step(_sListRules) == SQLITE_ROW) {
 						responseBody.append(firstRule ? "\n\t{\n" : ",{\n");
-						Utils::snprintf(json,sizeof(json),"\t\t\"ruleId\": %lld,\n",sqlite3_column_int64(_sListRules,0));
+						Utils::snprintf(json,sizeof(json),"\t\t\"ruleNo\": %lld,\n",sqlite3_column_int64(_sListRules,0));
 						responseBody.append(json);
 						if (sqlite3_column_type(_sListRules,1) != SQLITE_NULL) {
 							Utils::snprintf(json,sizeof(json),"\t\t\"nodeId\": \"%s\",\n",(const char *)sqlite3_column_text(_sListRules,1));
diff --git a/controller/schema.sql b/controller/schema.sql
index a3a7bb7a..25adce40 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -32,12 +32,10 @@ CREATE TABLE IpAssignment (
   ipVersion integer NOT NULL DEFAULT(4)
 );
 
-CREATE INDEX IpAssignment_networkId_ip ON IpAssignment (networkId, ip);
+CREATE UNIQUE INDEX IpAssignment_networkId_ip ON IpAssignment (networkId, ip);
 
 CREATE INDEX IpAssignment_networkId_nodeId ON IpAssignment (networkId, nodeId);
 
-CREATE INDEX IpAssignment_networkId ON IpAssignment (networkId);
-
 CREATE TABLE IpAssignmentPool (
   networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
   ipNetwork blob(16) NOT NULL,
@@ -55,8 +53,6 @@ CREATE TABLE Member (
   PRIMARY KEY (networkId, nodeId)
 );
 
-CREATE INDEX Member_networkId ON Member (networkId);
-
 CREATE INDEX Member_networkId_activeBridge ON Member(networkId, activeBridge);
 
 CREATE TABLE MulticastRate (
@@ -81,7 +77,7 @@ CREATE INDEX Relay_networkId ON Relay (networkId);
 
 CREATE TABLE Rule (
   networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
-  ruleId integer NOT NULL,
+  ruleNo integer NOT NULL,
   nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
   vlanId integer,
   vlanPcp integer,
@@ -99,4 +95,4 @@ CREATE TABLE Rule (
   "action" varchar(4096) NOT NULL DEFAULT('accept')
 );
 
-CREATE INDEX Rule_networkId ON Rule (networkId);
+CREATE UNIQUE INDEX Rule_networkId_ruleNo ON Rule (networkId, ruleNo);
diff --git a/controller/schema.sql.c b/controller/schema.sql.c
index 0078eac3..243d37d5 100644
--- a/controller/schema.sql.c
+++ b/controller/schema.sql.c
@@ -33,12 +33,10 @@
 "  ipVersion integer NOT NULL DEFAULT(4)\n"\
 ");\n"\
 "\n"\
-"CREATE INDEX IpAssignment_networkId_ip ON IpAssignment (networkId, ip);\n"\
+"CREATE UNIQUE INDEX IpAssignment_networkId_ip ON IpAssignment (networkId, ip);\n"\
 "\n"\
 "CREATE INDEX IpAssignment_networkId_nodeId ON IpAssignment (networkId, nodeId);\n"\
 "\n"\
-"CREATE INDEX IpAssignment_networkId ON IpAssignment (networkId);\n"\
-"\n"\
 "CREATE TABLE IpAssignmentPool (\n"\
 "  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
 "  ipNetwork blob(16) NOT NULL,\n"\
@@ -56,8 +54,6 @@
 "  PRIMARY KEY (networkId, nodeId)\n"\
 ");\n"\
 "\n"\
-"CREATE INDEX Member_networkId ON Member (networkId);\n"\
-"\n"\
 "CREATE INDEX Member_networkId_activeBridge ON Member(networkId, activeBridge);\n"\
 "\n"\
 "CREATE TABLE MulticastRate (\n"\
@@ -82,7 +78,7 @@
 "\n"\
 "CREATE TABLE Rule (\n"\
 "  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
-"  ruleId integer NOT NULL,\n"\
+"  ruleNo integer NOT NULL,\n"\
 "  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,\n"\
 "  vlanId integer,\n"\
 "  vlanPcp integer,\n"\
@@ -100,5 +96,5 @@
 "  \"action\" varchar(4096) NOT NULL DEFAULT('accept')\n"\
 ");\n"\
 "\n"\
-"CREATE INDEX Rule_networkId ON Rule (networkId);\n"\
+"CREATE UNIQUE INDEX Rule_networkId_ruleNo ON Rule (networkId, ruleNo);\n"\
 ""
diff --git a/service/README.md b/service/README.md
index acad97a1..2a283cbd 100644
--- a/service/README.md
+++ b/service/README.md
@@ -204,7 +204,7 @@ Relay objects define network-specific preferred relay nodes. Traffic to peers on
 
  * **Note**: at the moment, <u>only rules specifying allowed Ethernet types are used</u>. The database supports a richer rule set, but this is not implemented yet in the client. <u>Other types of rules will have no effect</u> (yet).
 
-Rules are matched in order of ruleId. If no rules match, the default action is 'drop'. To allow all traffic, create a single rule with all *null* fields and an action of 'accept'.
+Rules are matched in order of ruleNo. If no rules match, the default action is 'drop'. To allow all traffic, create a single rule with all *null* fields and an action of 'accept'.
 
 Rule object fields can be *null*, in which case they are omitted from the object. A null field indicates "no match on this criteria."
 
@@ -212,7 +212,7 @@ IP related fields apply only to Ethernet frames of type IPv4 or IPV6. Otherwise
 
 <table>
 <tr><td><b>Field</b></td><td><b>Type</b></td><td><b>Description</b></td></tr>
-<tr><td>ruleId</td><td>integer</td><td>User-defined rule ID and sort order</td></tr>
+<tr><td>ruleNo</td><td>integer</td><td>User-defined rule ID and sort order</td></tr>
 <tr><td>nodeId</td><td>string</td><td>10-digit hex ZeroTier address of node (a.k.a. "port on switch")</td></tr>
 <tr><td>vlanId</td><td>integer</td><td>Ethernet VLAN ID</td></tr>
 <tr><td>vlanPcp</td><td>integer</td><td>Ethernet VLAN priority code point (PCP) ID</td></tr>
-- 
cgit v1.2.3


From 96a58becf8fe10efceab38c49553b009754d2632 Mon Sep 17 00:00:00 2001
From: Adam Ierymenko <adam.ierymenko@gmail.com>
Date: Sat, 13 Jun 2015 11:34:31 +0200
Subject: Gateways support in network controller schema and database (not
 implemented yet in client) toward GitHub issue #178

---
 controller/SqliteNetworkController.cpp | 130 +++++++++++++++++++++++++++++++--
 controller/SqliteNetworkController.hpp |   5 +-
 controller/schema.sql                  |   9 +++
 controller/schema.sql.c                |   9 +++
 node/InetAddress.hpp                   |  10 +++
 node/NetworkConfig.hpp                 |  37 ++++++++++
 6 files changed, 194 insertions(+), 6 deletions(-)

(limited to 'controller/schema.sql')

diff --git a/controller/SqliteNetworkController.cpp b/controller/SqliteNetworkController.cpp
index 5aad49ff..35666fdb 100644
--- a/controller/SqliteNetworkController.cpp
+++ b/controller/SqliteNetworkController.cpp
@@ -179,7 +179,10 @@ SqliteNetworkController::SqliteNetworkController(const char *dbPath) :
 			||(sqlite3_prepare_v2(_db,"DELETE FROM Rule WHERE networkId = ?",-1,&_sDeleteRulesForNetwork,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"INSERT INTO IpAssignmentPool (networkId,ipNetwork,ipNetmaskBits,ipVersion) VALUES (?,?,?,?)",-1,&_sCreateIpAssignmentPool,(const char **)0) != SQLITE_OK)
 			||(sqlite3_prepare_v2(_db,"DELETE FROM Member WHERE networkId = ? AND nodeId = ?",-1,&_sDeleteMember,(const char **)0) != SQLITE_OK)
-			||(sqlite3_prepare_v2(_db,"DELETE FROM Network WHERE id = ?;",-1,&_sDeleteNetworkAndRelated,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"DELETE FROM Network WHERE id = ?",-1,&_sDeleteNetwork,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"SELECT ip,ipVersion,metric FROM Gateway WHERE networkId = ? ORDER BY metric ASC",-1,&_sGetGateways,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"DELETE FROM Gateway WHERE networkId = ?",-1,&_sDeleteGateways,(const char **)0) != SQLITE_OK)
+			||(sqlite3_prepare_v2(_db,"INSERT INTO Gateway (networkId,ip,ipVersion,metric) VALUES (?,?,?,?)",-1,&_sCreateGateway,(const char **)0) != SQLITE_OK)
 		 ) {
 		//printf("!!! %s\n",sqlite3_errmsg(_db));
 		sqlite3_close(_db);
@@ -222,7 +225,10 @@ SqliteNetworkController::~SqliteNetworkController()
 		sqlite3_finalize(_sDeleteIpAssignmentPoolsForNetwork);
 		sqlite3_finalize(_sDeleteRulesForNetwork);
 		sqlite3_finalize(_sCreateIpAssignmentPool);
-		sqlite3_finalize(_sDeleteNetworkAndRelated);
+		sqlite3_finalize(_sDeleteNetwork);
+		sqlite3_finalize(_sGetGateways);
+		sqlite3_finalize(_sDeleteGateways);
+		sqlite3_finalize(_sCreateGateway);
 		sqlite3_close(_db);
 	}
 }
@@ -455,6 +461,52 @@ NetworkController::ResultCode SqliteNetworkController::doNetworkConfigRequest(co
 				netconf[ZT_NETWORKCONFIG_DICT_KEY_RELAYS] = relays;
 		}
 
+		{
+			char tmp[128];
+			std::string gateways;
+			sqlite3_reset(_sGetGateways);
+			sqlite3_bind_text(_sGetGateways,1,network.id,16,SQLITE_STATIC);
+			while (sqlite3_step(_sGetGateways) == SQLITE_ROW) {
+				const unsigned char *ip = (const unsigned char *)sqlite3_column_blob(_sGetGateways,0);
+				switch(sqlite3_column_int(_sGetGateways,1)) { // ipVersion
+					case 4:
+						Utils::snprintf(tmp,sizeof(tmp),"%s%d.%d.%d.%d/%d",
+							(gateways.length() > 0) ? "," : "",
+							(int)ip[0],
+							(int)ip[1],
+							(int)ip[2],
+							(int)ip[3],
+							(int)sqlite3_column_int(_sGetGateways,2)); // metric
+						gateways.append(tmp);
+						break;
+					case 6:
+						Utils::snprintf(tmp,sizeof(tmp),"%s%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x/%d",
+							(gateways.length() > 0) ? "," : "",
+							(int)ip[0],
+							(int)ip[1],
+							(int)ip[2],
+							(int)ip[3],
+							(int)ip[4],
+							(int)ip[5],
+							(int)ip[6],
+							(int)ip[7],
+							(int)ip[8],
+							(int)ip[9],
+							(int)ip[10],
+							(int)ip[11],
+							(int)ip[12],
+							(int)ip[13],
+							(int)ip[14],
+							(int)ip[15],
+							(int)sqlite3_column_int(_sGetGateways,2)); // metric
+						gateways.append(tmp);
+						break;
+				}
+			}
+			if (gateways.length())
+				netconf[ZT_NETWORKCONFIG_DICT_KEY_GATEWAYS] = gateways;
+		}
+
 		if ((network.v4AssignMode)&&(!strcmp(network.v4AssignMode,"zt"))) {
 			std::string v4s;
 
@@ -808,6 +860,31 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpPOST(
 										sqlite3_step(_sCreateRelay);
 									}
 								}
+							} else if (!strcmp(j->u.object.values[k].name,"gateways")) {
+								sqlite3_reset(_sDeleteGateways);
+								sqlite3_bind_text(_sDeleteGateways,1,nwids,16,SQLITE_STATIC);
+								sqlite3_step(_sDeleteGateways);
+								if (j->u.object.values[k].value->type == json_array) {
+									for(unsigned int kk=0;kk<j->u.object.values[k].value->u.array.length;++kk) {
+										json_value *gateway = j->u.object.values[k].value->u.array.values[kk];
+										if ((gateway)&&(gateway->type == json_string)) {
+											InetAddress gwip(gateway->u.string.ptr);
+											int ipVersion = 0;
+											if (gwip.ss_family == AF_INET)
+												ipVersion = 4;
+											else if (gwip.ss_family == AF_INET6)
+												ipVersion = 6;
+											if (ipVersion) {
+												sqlite3_reset(_sCreateGateway);
+												sqlite3_bind_text(_sCreateGateway,1,nwids,16,SQLITE_STATIC);
+												sqlite3_bind_blob(_sCreateGateway,2,gwip.rawIpData(),(gwip.ss_family == AF_INET6) ? 16 : 4,SQLITE_STATIC);
+												sqlite3_bind_int(_sCreateGateway,3,ipVersion);
+												sqlite3_bind_int(_sCreateGateway,4,(int)gwip.metric());
+												sqlite3_step(_sCreateGateway);
+											}
+										}
+									}
+								}
 							} else if (!strcmp(j->u.object.values[k].name,"ipAssignmentPools")) {
 								if (j->u.object.values[k].value->type == json_array) {
 									std::set<InetAddress> pools;
@@ -1027,9 +1104,9 @@ unsigned int SqliteNetworkController::handleControlPlaneHttpDELETE(
 
 			} else {
 
-				sqlite3_reset(_sDeleteNetworkAndRelated);
-				sqlite3_bind_text(_sDeleteNetworkAndRelated,1,nwids,16,SQLITE_STATIC);
-				return ((sqlite3_step(_sDeleteNetworkAndRelated) == SQLITE_DONE) ? 200 : 500);
+				sqlite3_reset(_sDeleteNetwork);
+				sqlite3_bind_text(_sDeleteNetwork,1,nwids,16,SQLITE_STATIC);
+				return ((sqlite3_step(_sDeleteNetwork) == SQLITE_DONE) ? 200 : 500);
 
 			}
 		} // else 404
@@ -1212,6 +1289,49 @@ unsigned int SqliteNetworkController::_doCPGet(
 						responseBody.append(_jsonEscape((const char *)sqlite3_column_text(_sGetRelays,1)));
 						responseBody.append("\"}");
 					}
+					responseBody.append("],\n\t\"gateways\": [");
+
+					sqlite3_reset(_sGetGateways);
+					sqlite3_bind_text(_sGetGateways,1,nwids,16,SQLITE_STATIC);
+					bool firstGateway = true;
+					while (sqlite3_step(_sGetGateways) == SQLITE_ROW) {
+						char tmp[128];
+						const unsigned char *ip = (const unsigned char *)sqlite3_column_blob(_sGetGateways,0);
+						switch(sqlite3_column_int(_sGetGateways,1)) { // ipVersion
+							case 4:
+								Utils::snprintf(tmp,sizeof(tmp),"%s%d.%d.%d.%d/%d\"",
+									(firstGateway) ? "\"" : ",\"",
+									(int)ip[0],
+									(int)ip[1],
+									(int)ip[2],
+									(int)ip[3],
+									(int)sqlite3_column_int(_sGetGateways,2)); // metric
+								break;
+							case 6:
+								Utils::snprintf(tmp,sizeof(tmp),"%s%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x:%.2x%.2x/%d\"",
+									(firstGateway) ? "\"" : ",\"",
+									(int)ip[0],
+									(int)ip[1],
+									(int)ip[2],
+									(int)ip[3],
+									(int)ip[4],
+									(int)ip[5],
+									(int)ip[6],
+									(int)ip[7],
+									(int)ip[8],
+									(int)ip[9],
+									(int)ip[10],
+									(int)ip[11],
+									(int)ip[12],
+									(int)ip[13],
+									(int)ip[14],
+									(int)ip[15],
+									(int)sqlite3_column_int(_sGetGateways,2)); // metric
+								break;
+						}
+						responseBody.append(tmp);
+						firstGateway = false;
+					}
 					responseBody.append("],\n\t\"ipAssignmentPools\": [");
 
 					sqlite3_reset(_sGetIpAssignmentPools2);
diff --git a/controller/SqliteNetworkController.hpp b/controller/SqliteNetworkController.hpp
index 5c92cc0b..d258933d 100644
--- a/controller/SqliteNetworkController.hpp
+++ b/controller/SqliteNetworkController.hpp
@@ -123,7 +123,10 @@ private:
 	sqlite3_stmt *_sDeleteRulesForNetwork;
 	sqlite3_stmt *_sCreateIpAssignmentPool;
 	sqlite3_stmt *_sDeleteMember;
-	sqlite3_stmt *_sDeleteNetworkAndRelated;
+	sqlite3_stmt *_sDeleteNetwork;
+	sqlite3_stmt *_sGetGateways;
+	sqlite3_stmt *_sDeleteGateways;
+	sqlite3_stmt *_sCreateGateway;
 
 	Mutex _lock;
 };
diff --git a/controller/schema.sql b/controller/schema.sql
index 25adce40..809c7161 100644
--- a/controller/schema.sql
+++ b/controller/schema.sql
@@ -24,6 +24,15 @@ CREATE TABLE Node (
   firstSeen integer NOT NULL DEFAULT(0)
 );
 
+CREATE TABLE Gateway (
+  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
+  ip blob(16) NOT NULL,
+  ipVersion integer NOT NULL DEFAULT(4),
+  metric integer NOT NULL DEFAULT(0)
+);
+
+CREATE UNIQUE INDEX Gateway_networkId_ip ON Gateway (networkId, ip);
+
 CREATE TABLE IpAssignment (
   networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
   nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,
diff --git a/controller/schema.sql.c b/controller/schema.sql.c
index 243d37d5..f1c66358 100644
--- a/controller/schema.sql.c
+++ b/controller/schema.sql.c
@@ -25,6 +25,15 @@
 "  firstSeen integer NOT NULL DEFAULT(0)\n"\
 ");\n"\
 "\n"\
+"CREATE TABLE Gateway (\n"\
+"  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
+"  ip blob(16) NOT NULL,\n"\
+"  ipVersion integer NOT NULL DEFAULT(4),\n"\
+"  metric integer NOT NULL DEFAULT(0)\n"\
+");\n"\
+"\n"\
+"CREATE UNIQUE INDEX Gateway_networkId_ip ON Gateway (networkId, ip);\n"\
+"\n"\
 "CREATE TABLE IpAssignment (\n"\
 "  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
 "  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,\n"\
diff --git a/node/InetAddress.hpp b/node/InetAddress.hpp
index 5b725174..16e3f4d5 100644
--- a/node/InetAddress.hpp
+++ b/node/InetAddress.hpp
@@ -265,6 +265,16 @@ struct InetAddress : public sockaddr_storage
 	 */
 	inline unsigned int netmaskBits() const throw() { return port(); }
 
+	/**
+	 * Alias for port()
+	 *
+	 * This just aliases port() because for gateways we use this field to
+	 * store the gateway metric.
+	 *
+	 * @return Gateway metric
+	 */
+	inline unsigned int metric() const throw() { return port(); }
+
 	/**
 	 * Construct a full netmask as an InetAddress
 	 */
diff --git a/node/NetworkConfig.hpp b/node/NetworkConfig.hpp
index 89d1aec5..afbff3bf 100644
--- a/node/NetworkConfig.hpp
+++ b/node/NetworkConfig.hpp
@@ -49,24 +49,61 @@ namespace ZeroTier {
 
 // These dictionary keys are short so they don't take up much room in
 // netconf response packets.
+
+// integer(hex)[,integer(hex),...]
 #define ZT_NETWORKCONFIG_DICT_KEY_ALLOWED_ETHERNET_TYPES "et"
+
+// network ID
 #define ZT_NETWORKCONFIG_DICT_KEY_NETWORK_ID "nwid"
+
+// integer(hex)
 #define ZT_NETWORKCONFIG_DICT_KEY_TIMESTAMP "ts"
+
+// integer(hex)
 #define ZT_NETWORKCONFIG_DICT_KEY_REVISION "r"
+
+// address of member
 #define ZT_NETWORKCONFIG_DICT_KEY_ISSUED_TO "id"
+
+// integer(hex)
 #define ZT_NETWORKCONFIG_DICT_KEY_MULTICAST_LIMIT "ml"
+
+// dictionary of one or more of: MAC/ADI=preload,maxbalance,accrual
 #define ZT_NETWORKCONFIG_DICT_KEY_MULTICAST_RATES "mr"
+
+// 0/1
 #define ZT_NETWORKCONFIG_DICT_KEY_PRIVATE "p"
+
+// text
 #define ZT_NETWORKCONFIG_DICT_KEY_NAME "n"
+
+// text
 #define ZT_NETWORKCONFIG_DICT_KEY_DESC "d"
+
+// IP/bits[,IP/bits,...]
 #define ZT_NETWORKCONFIG_DICT_KEY_IPV4_STATIC "v4s"
+
+// IP/bits[,IP/bits,...]
 #define ZT_NETWORKCONFIG_DICT_KEY_IPV6_STATIC "v6s"
+
+// serialized CertificateOfMembership
 #define ZT_NETWORKCONFIG_DICT_KEY_CERTIFICATE_OF_MEMBERSHIP "com"
+
+// 0/1
 #define ZT_NETWORKCONFIG_DICT_KEY_ENABLE_BROADCAST "eb"
+
+// 0/1
 #define ZT_NETWORKCONFIG_DICT_KEY_ALLOW_PASSIVE_BRIDGING "pb"
+
+// node[,node,...]
 #define ZT_NETWORKCONFIG_DICT_KEY_ACTIVE_BRIDGES "ab"
+
+// node;IP/port[,node;IP/port]
 #define ZT_NETWORKCONFIG_DICT_KEY_RELAYS "rl"
 
+// IP/metric[,IP/metric,...]
+#define ZT_NETWORKCONFIG_DICT_KEY_GATEWAYS "gw"
+
 /**
  * Network configuration received from network controller nodes
  *
-- 
cgit v1.2.3

