diff options
author | Kees Bos <k.bos@capitar.com> | 2015-06-11 11:35:25 +0200 |
---|---|---|
committer | Kees Bos <k.bos@capitar.com> | 2015-06-11 11:35:25 +0200 |
commit | de697a1c45e8afbdd32687b9b5d203732ce5e62a (patch) | |
tree | e238fb1aa3bb944f9f1ffc64f20f8093e495e989 /controller/schema.sql | |
parent | 00aa115898e88f1a979fa3074bbcb25ac8b3ab4c (diff) | |
download | infinitytier-de697a1c45e8afbdd32687b9b5d203732ce5e62a.tar.gz infinitytier-de697a1c45e8afbdd32687b9b5d203732ce5e62a.zip |
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
Diffstat (limited to 'controller/schema.sql')
-rw-r--r-- | controller/schema.sql | 74 |
1 files changed, 36 insertions, 38 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); |