aboutsummaryrefslogtreecommitdiffstats
path: root/notes/projects/radius/sql/ippool-functions.sql
diff options
context:
space:
mode:
Diffstat (limited to 'notes/projects/radius/sql/ippool-functions.sql')
-rw-r--r--notes/projects/radius/sql/ippool-functions.sql92
1 files changed, 48 insertions, 44 deletions
diff --git a/notes/projects/radius/sql/ippool-functions.sql b/notes/projects/radius/sql/ippool-functions.sql
index dc91396..7ec18a6 100644
--- a/notes/projects/radius/sql/ippool-functions.sql
+++ b/notes/projects/radius/sql/ippool-functions.sql
@@ -175,7 +175,7 @@ BEGIN
175 PERFORM network 175 PERFORM network
176 FROM netblock 176 FROM netblock
177 WHERE netblock.network >>= assignIp 177 WHERE netblock.network >>= assignIp
178 AND netblock.assignable = FALSE; 178 AND netblock.busyout = TRUE;
179 IF FOUND THEN 179 IF FOUND THEN
180 RETURN FALSE; 180 RETURN FALSE;
181 END IF; 181 END IF;
@@ -228,7 +228,7 @@ CREATE OR REPLACE FUNCTION ip_audit
228 poolName VARCHAR(32), 228 poolName VARCHAR(32),
229 regionName VARCHAR(32), 229 regionName VARCHAR(32),
230 ipAddress INET, 230 ipAddress INET,
231 assignable BOOLEAN, 231 busyout BOOLEAN,
232 assigned BOOLEAN, 232 assigned BOOLEAN,
233 modified TIMESTAMP WITH TIME ZONE, 233 modified TIMESTAMP WITH TIME ZONE,
234 nasName VARCHAR(32), 234 nasName VARCHAR(32),
@@ -244,9 +244,9 @@ BEGIN
244 SELECT DISTINCT pool.name AS poolName, 244 SELECT DISTINCT pool.name AS poolName,
245 region.name AS regionName, 245 region.name AS regionName,
246 ip.address AS address, 246 ip.address AS address,
247 TRUE = ALL 247 TRUE = ANY
248 ( 248 (
249 SELECT nb2.assignable 249 SELECT nb2.busyout
250 FROM netblock AS nb2 250 FROM netblock AS nb2
251 WHERE ip.address <<= nb2.network 251 WHERE ip.address <<= nb2.network
252 ) AS assignable, 252 ) AS assignable,
@@ -561,7 +561,7 @@ CREATE OR REPLACE FUNCTION netblock_add
561 netAddress CIDR, 561 netAddress CIDR,
562 poolName VARCHAR(32), 562 poolName VARCHAR(32),
563 regionName VARCHAR(32) DEFAULT NULL, 563 regionName VARCHAR(32) DEFAULT NULL,
564 netAssignable BOOLEAN DEFAULT TRUE, 564 netBusyout BOOLEAN DEFAULT FALSE,
565 netDesc VARCHAR(128) DEFAULT NULL, 565 netDesc VARCHAR(128) DEFAULT NULL,
566 addIpEntries BOOLEAN DEFAULT TRUE 566 addIpEntries BOOLEAN DEFAULT TRUE
567) 567)
@@ -600,13 +600,13 @@ BEGIN
600 ( network, 600 ( network,
601 poolId, 601 poolId,
602 regionId, 602 regionId,
603 assignable, 603 busyout,
604 description 604 description
605 ) 605 )
606 VALUES ( netAddress, 606 VALUES ( netAddress,
607 netPoolId, 607 netPoolId,
608 netRegionId, 608 netRegionId,
609 netAssignable, 609 netBusyout,
610 netDesc 610 netDesc
611 ) 611 )
612 RETURNING id INTO returnId; 612 RETURNING id INTO returnId;
@@ -636,7 +636,7 @@ CREATE OR REPLACE FUNCTION netblock_audit
636 poolName VARCHAR(32), 636 poolName VARCHAR(32),
637 regionName VARCHAR(32), 637 regionName VARCHAR(32),
638 network CIDR, 638 network CIDR,
639 assignable BOOLEAN, 639 busyout BOOLEAN,
640 ipAssigned BIGINT, 640 ipAssigned BIGINT,
641 ipUnassigned BIGINT, 641 ipUnassigned BIGINT,
642 ipAvailable BIGINT, 642 ipAvailable BIGINT,
@@ -653,7 +653,7 @@ BEGIN
653 SELECT DISTINCT pool.name AS poolName, 653 SELECT DISTINCT pool.name AS poolName,
654 region.name AS regionname, 654 region.name AS regionname,
655 netblock.network AS network, 655 netblock.network AS network,
656 netblock.assignable AS assignable, 656 netblock.busyout AS busyout,
657 ( 657 (
658 SELECT count(address) 658 SELECT count(address)
659 FROM ip 659 FROM ip
@@ -671,7 +671,7 @@ BEGIN
671 FROM ip 671 FROM ip
672 INNER JOIN netblock AS nb2 672 INNER JOIN netblock AS nb2
673 ON nb2.id = ip.netblockId 673 ON nb2.id = ip.netblockId
674 WHERE nb2.assignable IS TRUE 674 WHERE nb2.busyout IS FALSE
675 AND ip.address <<= netblock.network 675 AND ip.address <<= netblock.network
676 AND ip.nasId IS NULL 676 AND ip.nasId IS NULL
677 ) AS ipAvailable, 677 ) AS ipAvailable,
@@ -705,10 +705,10 @@ CREATE OR REPLACE FUNCTION region_audit
705 ( 705 (
706 poolName VARCHAR(32), 706 poolName VARCHAR(32),
707 regionName VARCHAR(32), 707 regionName VARCHAR(32),
708 ipAssigned BIGINT, 708 ipAssignedFree BIGINT,
709 ipUnassigned BIGINT, 709 ipAssignedBusy BIGINT,
710 ipAvailable BIGINT, 710 ipAvailable BIGINT,
711 ipUnassignable BIGINT, 711 ipBusyout BIGINT,
712 ipTotal BIGINT, 712 ipTotal BIGINT,
713 description TEXT 713 description TEXT
714 ) 714 )
@@ -724,21 +724,23 @@ BEGIN
724 ( 724 (
725 SELECT COUNT( address ) 725 SELECT COUNT( address )
726 FROM ip 726 FROM ip
727 INNER JOIN netblock AS nb2 727 INNER JOIN netblock AS nb2
728 ON nb2.id = ip.netblockId 728 ON nb2.id = ip.netblockId
729 WHERE nb2.regionId = region.id 729 WHERE nb2.regionId = region.id
730 AND nb2.poolId = pool.id 730 AND nb2.poolId = pool.id
731 AND ip.nasId IS NOT NULL 731 AND nb2.busyout IS FALSE
732 ) AS ipAssigned, 732 AND ip.nasId IS NOT NULL
733 ( 733 ) AS ipAssignedFree,
734 (
734 SELECT COUNT( address ) 735 SELECT COUNT( address )
735 FROM ip 736 FROM ip
736 INNER JOIN netblock AS nb2 737 INNER JOIN netblock AS nb2
737 ON nb2.id = ip.netblockId 738 ON nb2.id = ip.netblockId
738 WHERE nb2.regionId = region.id 739 WHERE nb2.regionId = region.id
739 AND nb2.poolId = pool.id 740 AND nb2.poolId = pool.id
740 AND ip.nasId IS NULL 741 AND nb2.busyout IS TRUE
741 ) AS ipUnassigned, 742 AND ip.nasId IS NOT NULL
743 ) AS ipAssignedBusy,
742 ( 744 (
743 SELECT COUNT( address ) 745 SELECT COUNT( address )
744 FROM ip 746 FROM ip
@@ -746,7 +748,7 @@ BEGIN
746 ON nb2.id = ip.netblockId 748 ON nb2.id = ip.netblockId
747 WHERE nb2.regionId = region.id 749 WHERE nb2.regionId = region.id
748 AND nb2.poolId = pool.id 750 AND nb2.poolId = pool.id
749 AND nb2.assignable IS TRUE 751 AND nb2.busyout IS FALSE
750 AND ip.nasId IS NULL 752 AND ip.nasId IS NULL
751 ) AS ipAvailable, 753 ) AS ipAvailable,
752 ( 754 (
@@ -756,8 +758,8 @@ BEGIN
756 ON nb2.id = ip.netblockId 758 ON nb2.id = ip.netblockId
757 WHERE nb2.regionId = region.id 759 WHERE nb2.regionId = region.id
758 AND nb2.poolId = pool.id 760 AND nb2.poolId = pool.id
759 AND nb2.assignable IS FALSE 761 AND nb2.busyout IS TRUE
760 ) AS ipUnassignable, 762 ) AS ipBusyout,
761 ( 763 (
762 SELECT COUNT( address ) 764 SELECT COUNT( address )
763 FROM ip 765 FROM ip
@@ -784,21 +786,23 @@ BEGIN
784 ( 786 (
785 SELECT COUNT( address ) 787 SELECT COUNT( address )
786 FROM ip 788 FROM ip
787 INNER JOIN netblock AS nb2 789 INNER JOIN netblock AS nb2
788 ON nb2.id = ip.netblockId 790 ON nb2.id = ip.netblockId
789 WHERE nb2.regionId IS NULL 791 WHERE nb2.regionId IS NULL
790 AND nb2.poolId = pool.id 792 AND nb2.poolId = pool.id
791 AND ip.nasId IS NOT NULL 793 AND nb2.busyout IS FALSE
792 ) AS ipAssigned, 794 AND ip.nasId IS NOT NULL
795 ) AS ipAssignFree,
793 ( 796 (
794 SELECT COUNT( address ) 797 SELECT COUNT( address )
795 FROM ip 798 FROM ip
796 INNER JOIN netblock AS nb2 799 INNER JOIN netblock AS nb2
797 ON nb2.id = ip.netblockId 800 ON nb2.id = ip.netblockId
798 WHERE nb2.regionId IS NULL 801 WHERE nb2.regionId IS NULL
799 AND nb2.poolId = pool.id 802 AND nb2.poolId = pool.id
800 AND ip.nasId IS NULL 803 AND nb2.busyout IS TRUE
801 ) AS ipUnassigned, 804 AND ip.nasId IS NOT NULL
805 ) AS ipAssignFree,
802 ( 806 (
803 SELECT COUNT( address ) 807 SELECT COUNT( address )
804 FROM ip 808 FROM ip
@@ -806,7 +810,7 @@ BEGIN
806 ON nb2.id = ip.netblockId 810 ON nb2.id = ip.netblockId
807 WHERE nb2.regionId IS NULL 811 WHERE nb2.regionId IS NULL
808 AND nb2.poolId = pool.id 812 AND nb2.poolId = pool.id
809 AND nb2.assignable IS TRUE 813 AND nb2.busyout IS FALSE
810 AND ip.nasId IS NULL 814 AND ip.nasId IS NULL
811 ) AS ipAvailable, 815 ) AS ipAvailable,
812 ( 816 (
@@ -816,8 +820,8 @@ BEGIN
816 ON nb2.id = ip.netblockId 820 ON nb2.id = ip.netblockId
817 WHERE nb2.regionId IS NULL 821 WHERE nb2.regionId IS NULL
818 AND nb2.poolId = pool.id 822 AND nb2.poolId = pool.id
819 AND nb2.assignable IS FALSE 823 AND nb2.busyout IS TRUE
820 ) AS ipUnassignable, 824 ) AS ipBusyout,
821 ( 825 (
822 SELECT COUNT( address ) 826 SELECT COUNT( address )
823 FROM ip 827 FROM ip