1 files changed, 129 insertions, 0 deletions

diff --git a/notes/projects/radius/sql/untitled.sql b/notes/projects/radius/sql/untitled.sql
new file mode 100644
index 0000000..beacd77
--- /dev/null
+++ b/notes/projects/radius/sql/untitled.sql
@@ -0,0 +1,129 @@
+
+
+SELECT * FROM region_audit()   ORDER BY poolName, regionName;
+SELECT * FROM netblock_audit() ORDER BY poolName, network;
+SELECT * FROM ip_audit()       ORDER BY ipAddress;
+
+
+CREATE TABLE IF NOT EXISTS netblock
+(
+   id               SERIAL,
+   network          CIDR            NOT NULL UNIQUE,
+   poolId           INTEGER         NOT NULL,
+   regionId         INTEGER,
+   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 TABLE IF NOT EXISTS ip
+(
+   address          INET,
+   modified         timestamp       WITH TIME ZONE NOT NULL
+                                    DEFAULT TIMESTAMP 'epoch',
+   username         VARCHAR(64),
+   acctSession      VARCHAR(32),
+   nasId            INTEGER,
+   PRIMARY KEY      ( address ),
+   FOREIGN KEY      ( nasId )
+      REFERENCES    nas
+                    ( id )
+);
+
+
+\c syzdek
+set role wheel;
+drop database radius_ippool;
+
+
+psql -h raddbtest.prv.acsalaska.net -f radius-roles.sql
+psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql 
+psql -h raddbtest.prv.acsalaska.net -f ippool-schema.sql \
+                                    -f ippool-functions.sql radius_ippool
+psql -h raddbtest.prv.acsalaska.net -f ippool-demo.sql      radius_ippool
+
+
+SELECT              ip.address       AS address,
+                    region.name      AS region,
+                    pool.name        AS pool,
+                    ip.modified      AS modified,
+                    ip.username      AS username,
+                    ip.acctsession   AS acctsession
+   FROM             ip
+   INNER JOIN       netblock
+      ON            netblock.network >>= ip.address
+   INNER JOIN       region
+      ON            region.id        =   netblock.regionId
+   INNER JOIN       pool
+      ON            pool.id          =   netblock.poolId
+   INNER JOIN       nas
+      ON            nas.regionId     = region.id
+   WHERE            ip.nasId         IS NULL
+      AND           nas.name         LIKE lower( 'br1.sol' )
+      AND           pool.name        LIKE lower( 'DHCP' )
+      AND           netblock.active  = TRUE
+   ORDER BY         ip.modified ASC
+   LIMIT            1;
+
+
+UPDATE              ip
+   SET              nasId = nas.id,
+                    username  = 'test1'
+   FROM             ip
+   INNER JOIN       netblock
+      ON            netblock.network >>= ip.address
+   INNER JOIN       region
+      ON            region.id        =   netblock.regionId
+   INNER JOIN       pool
+      ON            pool.id          =   netblock.poolId
+   INNER JOIN       nas
+      ON            nas.regionId     = region.id
+   WHERE            ip.nasId         IS NULL
+      AND           nas.name         LIKE 'br1.sol'
+      AND           pool.name        LIKE lower( 'DHCP' )
+      AND           netblock.active  = TRUE
+   ORDER BY         ip.modified ASC
+   LIMIT            1;       
+
+DECLARE
+   blah  := 'test1';
+BEGIN
+   SELECT *, blah, FROM region;
+END;
+
+
+
+
+INSERT INTO    foo
+               ( attr1,
+                 attr2,
+                 attr3
+               )
+   VALUES      ( 'val1',
+                 'val2',
+                 'val3'
+               );
+
+
+DELETE FROM    foo
+   WHERE       attr1 like 'val1';
+
+
+UPDATE         foo
+   SET         attr1 = 'valI',
+               attr2 = 'valII',
+               attr3 = 'valIII'
+   WHERE       attr1 like 'val1';
+
+
+
+
+
+