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

2 files changed, 71 insertions, 75 deletions

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);
diff --git a/controller/schema.sql.c b/controller/schema.sql.c
index 1384b900..0078eac3 100644
--- a/controller/schema.sql.c
+++ b/controller/schema.sql.c
@@ -4,9 +4,30 @@
 "  v varchar(1024) NOT NULL\n"\
 ");\n"\
 "\n"\
+"CREATE TABLE Network (\n"\
+"  id char(16) PRIMARY KEY NOT NULL,\n"\
+"  name varchar(128) NOT NULL,\n"\
+"  private integer NOT NULL DEFAULT(1),\n"\
+"  enableBroadcast integer NOT NULL DEFAULT(1),\n"\
+"  allowPassiveBridging integer NOT NULL DEFAULT(0),\n"\
+"  v4AssignMode varchar(8) NOT NULL DEFAULT('none'),\n"\
+"  v6AssignMode varchar(8) NOT NULL DEFAULT('none'),\n"\
+"  multicastLimit integer NOT NULL DEFAULT(32),\n"\
+"  creationTime integer NOT NULL DEFAULT(0),\n"\
+"  revision integer NOT NULL DEFAULT(1)\n"\
+");\n"\
+"\n"\
+"CREATE TABLE Node (\n"\
+"  id char(10) PRIMARY KEY NOT NULL,\n"\
+"  identity varchar(4096) NOT NULL,\n"\
+"  lastAt varchar(64),\n"\
+"  lastSeen integer NOT NULL DEFAULT(0),\n"\
+"  firstSeen integer NOT NULL DEFAULT(0)\n"\
+");\n"\
+"\n"\
 "CREATE TABLE IpAssignment (\n"\
-"  networkId char(16) NOT NULL,\n"\
-"  nodeId char(10) NOT NULL,\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"\
 "  ip blob(16) NOT NULL,\n"\
 "  ipNetmaskBits integer NOT NULL DEFAULT(0),\n"\
 "  ipVersion integer NOT NULL DEFAULT(4)\n"\
@@ -19,7 +40,7 @@
 "CREATE INDEX IpAssignment_networkId ON IpAssignment (networkId);\n"\
 "\n"\
 "CREATE TABLE IpAssignmentPool (\n"\
-"  networkId char(16) NOT NULL,\n"\
+"  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
 "  ipNetwork blob(16) NOT NULL,\n"\
 "  ipNetmaskBits integer NOT NULL,\n"\
 "  ipVersion integer NOT NULL DEFAULT(4)\n"\
@@ -28,20 +49,19 @@
 "CREATE INDEX IpAssignmentPool_networkId ON IpAssignmentPool (networkId);\n"\
 "\n"\
 "CREATE TABLE Member (\n"\
-"  networkId char(16) NOT NULL,\n"\
-"  nodeId char(10) NOT NULL,\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"\
 "  authorized integer NOT NULL DEFAULT(0),\n"\
-"  activeBridge integer NOT NULL DEFAULT(0)\n"\
+"  activeBridge integer NOT NULL DEFAULT(0),\n"\
+"  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 UNIQUE INDEX Member_networkId_nodeId ON Member (networkId, nodeId);\n"\
-"\n"\
 "CREATE TABLE MulticastRate (\n"\
-"  networkId char(16) NOT NULL,\n"\
+"  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
 "  mgMac char(12) NOT NULL,\n"\
 "  mgAdi integer NOT NULL DEFAULT(0),\n"\
 "  preload integer NOT NULL,\n"\
@@ -51,41 +71,19 @@
 "\n"\
 "CREATE INDEX MulticastRate_networkId ON MulticastRate (networkId);\n"\
 "\n"\
-"CREATE TABLE Network (\n"\
-"  id char(16) PRIMARY KEY NOT NULL,\n"\
-"  name varchar(128) NOT NULL,\n"\
-"  private integer NOT NULL DEFAULT(1),\n"\
-"  enableBroadcast integer NOT NULL DEFAULT(1),\n"\
-"  allowPassiveBridging integer NOT NULL DEFAULT(0),\n"\
-"  v4AssignMode varchar(8) NOT NULL DEFAULT('none'),\n"\
-"  v6AssignMode varchar(8) NOT NULL DEFAULT('none'),\n"\
-"  multicastLimit integer NOT NULL DEFAULT(32),\n"\
-"  creationTime integer NOT NULL DEFAULT(0),\n"\
-"  revision integer NOT NULL DEFAULT(1)\n"\
-");\n"\
-"\n"\
 "CREATE TABLE Relay (\n"\
-"  networkId char(16) NOT NULL,\n"\
-"  nodeId char(10) NOT NULL,\n"\
-"  phyAddress varchar(64) NOT NULL\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"\
+"  phyAddress varchar(64) NOT NULL,\n"\
+"  PRIMARY KEY (networkId, nodeId)\n"\
 ");\n"\
 "\n"\
 "CREATE INDEX Relay_networkId ON Relay (networkId);\n"\
 "\n"\
-"CREATE UNIQUE INDEX Relay_networkId_nodeId ON Relay (networkId, nodeId);\n"\
-"\n"\
-"CREATE TABLE Node (\n"\
-"  id char(10) PRIMARY KEY NOT NULL,\n"\
-"  identity varchar(4096) NOT NULL,\n"\
-"  lastAt varchar(64),\n"\
-"  lastSeen integer NOT NULL DEFAULT(0),\n"\
-"  firstSeen integer NOT NULL DEFAULT(0)\n"\
-");\n"\
-"\n"\
 "CREATE TABLE Rule (\n"\
-"  networkId char(16) NOT NULL,\n"\
+"  networkId char(16) NOT NULL REFERENCES Network(id) ON DELETE CASCADE,\n"\
 "  ruleId integer NOT NULL,\n"\
-"  nodeId char(10),\n"\
+"  nodeId char(10) NOT NULL REFERENCES Node(id) ON DELETE CASCADE,\n"\
 "  vlanId integer,\n"\
 "  vlanPcp integer,\n"\
 "  etherType integer,\n"\