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