Cleaning up ippool sql

2 files changed, 222 insertions, 46 deletions

diff --git a/notes/projects/radius/sql/ippool-database.sql b/notes/projects/radius/sql/ippool-database.sql
index ed188cd..00c0889 100644
--- a/notes/projects/radius/sql/ippool-database.sql
+++ b/notes/projects/radius/sql/ippool-database.sql
@@ -6,22 +6,25 @@
  */
 
 
-CREATE ROLE radius_ippool WITH
-   NOSUPERUSER
-   NOCREATEDB
-   NOCREATEROLE
-   NOINHERIT
-   NOLOGIN
-   NOREPLICATION
-   NOBYPASSRLS
-   ADMIN admin;
-
-
-CREATE DATABASE radius_ippool WITH
-   OWNER       = radius_ippool
-   ENCODING    = "UTF8"
-   LC_COLLATE  = "en_US.UTF-8"
-   LC_CTYPE    = "en_US.UTF-8";
+SET ROLE wheel;
+
+
+CREATE ROLE radius_ippool
+  WITH              NOSUPERUSER
+                    NOCREATEDB
+                    NOCREATEROLE
+                    NOINHERIT
+                    NOLOGIN
+                    NOREPLICATION
+                    NOBYPASSRLS
+                    ADMIN           admin;
+
+
+CREATE DATABASE radius_ippool
+  WITH              OWNER           = radius_ippool
+                    ENCODING        = "UTF8"
+                    LC_COLLATE      = "en_US.UTF-8"
+                    LC_CTYPE        = "en_US.UTF-8";
 
 
 /* end of sql */
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 485a5cc..fdc5bee 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -3,62 +3,235 @@
  *
  *  ippool-schema.sql - builds database schema for RADIUS IP Pools
  *
+ *  Usage:  psql -f ippool-schema.sql radius_ippool
  */
 
 
 SET ROLE radius_ippool;
 
 
+-- //////////////
+-- //          //
+-- //  Tables  //
+-- //          //
+-- //////////////
+
+
+/*
+ *  RADIUS Pool Type
+ *
+ *  id
+ *  : The primary key of a specific entry
+ *
+ *  name
+ *  : the pool name/type
+ *
+ *  description
+ *  : human readable description of pool
+ */
 CREATE TABLE IF NOT EXISTS pool
 (
-   id              SERIAL PRIMARY KEY,
-   name            VARCHAR(32) NOT NULL UNIQUE
+   id               SERIAL,
+   name             VARCHAR(32)     NOT NULL UNIQUE,
+   description      VARCHAR(128),
+   PRIMARY KEY      ( id )
 );
+CREATE INDEX
+   IF NOT EXISTS    pool_index
+   ON               pool
+                    ( id );
+
 
+/*
+ *  RADIUS geographic region
+ *
+ *  id
+ *  : The primary key of a specific entry
+ *
+ *  name
+ *  : the region's name/locality
+ *
+ *  description
+ *  : human readable description of region
+ */
 CREATE TABLE IF NOT EXISTS region
 (
-   id              SERIAL PRIMARY KEY,
-   name            VARCHAR(32) NOT NULL,
-   description     VARCHAR(128)
+   id               SERIAL,
+   name             VARCHAR(32)     NOT NULL,
+   description      VARCHAR(128),
+   PRIMARY KEY      ( id )
 );
+CREATE INDEX
+   IF NOT EXISTS    region_index
+   ON               region
+                    ( id );
+
 
+/*
+ *  RADIUS NAS client
+ *
+ *  id
+ *  : The primary key of a specific entry
+ *
+ *  name
+ *  : the NAS truncated DNS name (i.e. br1.cwc)
+ *
+ *  regionId
+ *  : reference to the region in which the NAS is physically located
+ */
 CREATE TABLE IF NOT EXISTS nas
 (
-   id              SERIAL PRIMARY KEY,
-   name            VARCHAR(32) NOT NULL,
-   regionId        INTEGER NOT NULL,
-   FOREIGN KEY (regionId)   REFERENCES region (id)
+   id               SERIAL,
+   name             VARCHAR(32)     NOT NULL,
+   regionId         INTEGER         NOT NULL,
+   PRIMARY KEY      ( id ),
+   FOREIGN KEY      ( regionId )
+      REFERENCES    region
+                    ( id )
 );
+CREATE INDEX
+   IF NOT EXISTS    nas_index
+   ON               nas
+                    ( id,
+                      regionId
+                    );
+
 
+/*
+ *  RADIUS network block
+ *
+ *  id
+ *  : The primary key of a specific entry
+ *
+ *  poolId
+ *  : reference to the pool type of the network
+ *
+ *  regionId
+ *  : reference to the physical region served by the block
+ *
+ *  net
+ *  : network address and network size of block
+ *
+ *  active
+ *  : assignable to customer if true, disabled/inactive if false
+ *
+ *  description
+ *  : human readable description of network block
+ */
 CREATE TABLE IF NOT EXISTS block
 (
-   id              SERIAL PRIMARY KEY,
-   poolId          INTEGER NOT NULL,
-   regionId        INTEGER NOT NULL,
-   net             INET NOT NULL UNIQUE,
-   active          BOOLEAN NOT NULL DEFAULT TRUE,
-   description     VARCHAR(32),
-   FOREIGN KEY (poolId)     REFERENCES pool   (id),
-   FOREIGN KEY (regionId)   REFERENCES region (id)
+   id               SERIAL,
+   poolId           INTEGER         NOT NULL,
+   regionId         INTEGER,
+   net              CIDR            NOT NULL UNIQUE,
+   active           BOOLEAN         NOT NULL DEFAULT TRUE,
+   description      VARCHAR(32),
+   PRIMARY KEY      ( id ),
+   FOREIGN KEY      ( poolId )
+      REFERENCES    pool
+                    ( id ),
+   FOREIGN KEY      ( regionId )
+      REFERENCES    region
+                    ( id )
 );
+CREATE INDEX
+   IF NOT EXISTS    block_index
+   ON               block
+                    ( id,
+                      poolId,
+                      net,
+                      active
+                    );
+
 
-CREATE TABLE IF NOT EXISTS address
+/*
+ *  RADIUS user-assignable IP address
+ *
+ *  address
+ *  : user assignable IP address
+ *
+ *  poolId
+ *  : reference to the pool type of the network
+ *
+ *  assigned
+ *  : TRUE if currently assigned, FALSE if available
+ *
+ *  modified
+ *  : timestamp generated when entry is modified
+ *
+ *  username
+ *  : username currently assigned or last assigned address
+ */
+CREATE TABLE IF NOT EXISTS ip
 (
-   address         INET PRIMARY KEY,
-   poolId          INTEGER NOT NULL,
-   assigned        BOOLEAN NOT NULL DEFAULT FALSE,
-   lastChanged     timestamp WITH TIME ZONE  NOT NULL DEFAULT TIMESTAMP 'epoch',
-   username        VARCHAR(64),
-   FOREIGN KEY (poolId)   REFERENCES pool (id)
+   address          INET,
+   poolId           INTEGER         NOT NULL,
+   assigned         BOOLEAN         NOT NULL DEFAULT FALSE,
+   modified         timestamp       WITH TIME ZONE NOT NULL
+                                    DEFAULT TIMESTAMP 'epoch',
+   username         VARCHAR(64),
+   PRIMARY KEY      ( address ),
+   FOREIGN KEY      ( poolId )
+      REFERENCES    pool
+                    ( id )
 );
+CREATE INDEX
+   IF NOT EXISTS    ip_index
+   ON               ip
+                    ( address,
+                      poolId,
+                      assigned,
+                      modified,
+                      username
+                    );
+
+
+-- /////////////////
+-- //             //
+-- //  Functions  //
+-- //             //
+-- /////////////////
+
+
+/*
+ *  updates timestamp of addresses
+ *
+ *  returns trigger
+ */
+CREATE OR REPLACE FUNCTION ip_timestamp
+( )
+   RETURNS          trigger
+   LANGUAGE         plpgsql
+   AS               $$
+DECLARE
+BEGIN
+
+   -- prevents IP address from being modified
+   IF ( NEW.address != OLD.address ) THEN
+     RAISE EXCEPTION 'address cannot be modified';
+   END IF;
+
+   -- update timestamp
+   NEW.lastModified := current_timestamp;
+
+   RETURN NEW;
+END;
+$$;
+
+
 
+-- ////////////////
+-- //            //
+-- //  Triggers  //
+-- //            //
+-- ////////////////
 
-CREATE INDEX IF NOT EXISTS pool_index    ON pool    ( id );
-CREATE INDEX IF NOT EXISTS region_index  ON region  ( id );
-CREATE INDEX IF NOT EXISTS nas_index     ON nas     ( id, regionId );
-CREATE INDEX IF NOT EXISTS block_index   ON block   ( id, poolId, net, active );
-CREATE INDEX IF NOT EXISTS address_index ON address ( address, poolId,
-   assigned, lastChanged, lastuser );
+-- address table triggers
+CREATE TRIGGER      ip_timestamp
+   BEFORE           UPDATE
+   ON               ip
+   FOR EACH         ROW
+   EXECUTE          PROCEDURE ip_timestamp();
 
 
 /* end of sql */