Updating ippool schema.

4 files changed, 76 insertions, 11 deletions

diff --git a/notes/projects/radius/sql/ippool-database.sql b/notes/projects/radius/sql/ippool-database.sql
new file mode 100644
index 0000000..ed188cd
--- /dev/null
+++ b/notes/projects/radius/sql/ippool-database.sql
@@ -0,0 +1,27 @@
+/*
+ *  IP Engineering RADIUS Servers
+ *
+ *  ippool-database.sql - builds roles and database for RADIUS IP Pools
+ *
+ */
+
+
+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-demo.sql b/notes/projects/radius/sql/ippool-demo.sql
index e6f0d9f..9bf7edc 100644
--- a/notes/projects/radius/sql/ippool-demo.sql
+++ b/notes/projects/radius/sql/ippool-demo.sql
@@ -1,17 +1,30 @@
+/*
+ *  IP Engineering RADIUS Servers
+ *
+ *  ippool-demo.sql - inserts demo data into  RADIUS IP Pools database
+ *
+ */
+
+
+SET ROLE radius_ippool;
+
 
 INSERT INTO pool ( name ) VALUES
    ( 'dhcp' ),
    ( 'pppoe' );
 
+
 INSERT INTO region ( name ) VALUES
    ( 'anchorage' ),
    ( 'soldotna' ),
    ( 'fairbanks' );
 
+
 INSERT INTO nas ( name, regionid ) VALUES
    ( 'br1.cwc', (SELECT id FROM region WHERE name LIKE 'anchorage') ),
    ( 'br1.sol', (SELECT id FROM region WHERE name LIKE 'soldotna') );
 
+
 INSERT INTO block ( poolId, regionId, net, description ) VALUES
    (  ( SELECT id FROM pool   WHERE name LIKE 'dhcp' ),
       ( SELECT id FROM region WHERE name LIKE 'anchorage' ),
@@ -22,3 +35,5 @@ INSERT INTO block ( poolId, regionId, net, description ) VALUES
       '209.112.128.0/24',
       'User Blocks' );
 
+
+/* end of sql */
diff --git a/notes/projects/radius/sql/ippool-schema.sql b/notes/projects/radius/sql/ippool-schema.sql
index 9b57676..485a5cc 100644
--- a/notes/projects/radius/sql/ippool-schema.sql
+++ b/notes/projects/radius/sql/ippool-schema.sql
@@ -1,3 +1,13 @@
+/*
+ *  IP Engineering RADIUS Servers
+ *
+ *  ippool-schema.sql - builds database schema for RADIUS IP Pools
+ *
+ */
+
+
+SET ROLE radius_ippool;
+
 
 CREATE TABLE IF NOT EXISTS pool
 (
@@ -28,7 +38,7 @@ CREATE TABLE IF NOT EXISTS block
    net             INET NOT NULL UNIQUE,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    description     VARCHAR(32),
-   FOREIGN KEY (poolId)       REFERENCES pool     (id),
+   FOREIGN KEY (poolId)     REFERENCES pool   (id),
    FOREIGN KEY (regionId)   REFERENCES region (id)
 );
 
@@ -38,24 +48,17 @@ CREATE TABLE IF NOT EXISTS address
    poolId          INTEGER NOT NULL,
    assigned        BOOLEAN NOT NULL DEFAULT FALSE,
    lastChanged     timestamp WITH TIME ZONE  NOT NULL DEFAULT TIMESTAMP 'epoch',
-   currentUser     VARCHAR(32),
-   currentCircuit  VARCHAR(64),
+   username        VARCHAR(64),
    FOREIGN KEY (poolId)   REFERENCES pool (id)
 );
 
 
-ALTER TABLE pool    OWNER TO postgres;
-ALTER TABLE region  OWNER TO postgres;
-ALTER TABLE nas     OWNER TO postgres;
-ALTER TABLE block   OWNER TO postgres;
-ALTER TABLE address OWNER TO postgres;
-
-
 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, currentUser, currentCircuit );
+   assigned, lastChanged, lastuser );
 
 
+/* end of sql */
diff --git a/notes/projects/radius/sql/ippool.md b/notes/projects/radius/sql/ippool.md
new file mode 100644
index 0000000..ad8328c
--- /dev/null
+++ b/notes/projects/radius/sql/ippool.md
@@ -0,0 +1,20 @@
+
+IP Engineering RADIUS IP Pool Database
+======================================
+
+Objectives
+----------
+
+   * Psuedo static IP addresses
+   * Per NAS Preference for geographic regions
+   * Ability to deavtivate/busy out specific netblocks
+
+Files
+----
+
+   * ippool.md           - This file
+   * ippool-database.sql - Creates database
+   * ippool-schema.sql   - Creates database schema (tables, indexes, etc)
+   * ippool-demo.sql     - Loads demo data
+
+