From 4d1af61908010f802077ce9bb4d7434b5d541968 Mon Sep 17 00:00:00 2001
From: Nicolas Boisselier <nicolas.boisselier@semantico.com>
Date: Mon, 27 Jun 2016 17:07:11 +0100
Subject: [PATCH] 2mysql net.sh

---
 etc/dbs/ui.php          |  2 +-
 etc/profile.d/net.sh    | 17 +++++++++++++++++
 lib/mysql/functions.sql | 26 ++++++++++++++++++++++++++
 lib/php/db/table.php    |  1 +
 4 files changed, 45 insertions(+), 1 deletion(-)
 create mode 100644 etc/profile.d/net.sh

diff --git a/etc/dbs/ui.php b/etc/dbs/ui.php
index 7679f3bc..fa9e3df0 100644
--- a/etc/dbs/ui.php
+++ b/etc/dbs/ui.php
@@ -145,7 +145,7 @@ $CONF['_ui'] = array(
       },
 
     ),
-    'network_ips' => array('sql' => "SELECT (SELECT id FROM network WHERE network <>'' AND network IS NOT NULL AND cidr_range(network,ip) ORDER BY hidden LIMIT 1) as idnetwork,code,network,iddc,count(*) as count FROM view_ip_detail JOIN network ON idnetwork=network.id GROUP BY idnetwork HAVING idnetwork NOT NULL ORDER BY count DESC"),
+    'network_ips' => array('sql' => "SELECT (SELECT id FROM network WHERE network <>'' AND network IS NOT NULL AND cidr_range(network,ip) ORDER BY hidden LIMIT 1) as idnetwork,code,network,iddc,count(*) as count FROM view_ip_detail JOIN network ON idnetwork=network.id GROUP BY idnetwork HAVING idnetwork IS NOT NULL ORDER BY count DESC"),
     'view_puppet_error' => array('sql' => "SELECT idnode ,idcompany ,iddc ,value as last_run FROM fact JOIN node ON id=idnode WHERE name='last_run' AND substr(value,1,10) <='".date('Y-m-d', strtotime('-3 days'))."'"),
     'view_backup_error' => array('sql' => "SELECT idnode,idcompany,iddc,server,max(backup.date) as date FROM backup JOIN node ON id=idnode WHERE NOT EXISTS (SELECT 1 FROM node_decommission nd WHERE nd.id=node.id) GROUP BY idnode HAVING date <= '".date('Y-m-d', strtotime('-2 days'))."' ORDER BY backup.date")
 
diff --git a/etc/profile.d/net.sh b/etc/profile.d/net.sh
new file mode 100644
index 00000000..f0f5496d
--- /dev/null
+++ b/etc/profile.d/net.sh
@@ -0,0 +1,17 @@
+mask2cdr ()
+{
+   # Assumes there's no "255." after a non-255 byte in the mask
+   local x=${1##*255.}
+   set -- 0^^^128^192^224^240^248^252^254^ $(( (${#1} - ${#x})*2 )) ${x%%.*}
+   x=${1%%$3*}
+   echo $(( $2 + (${#x}/4) ))
+}
+
+
+cdr2mask ()
+{
+   # Number of args to shift, 255..255, first non-255 byte, zeroes
+   set -- $(( 5 - ($1 / 8) )) 255 255 255 255 $(( (255 << (8 - ($1 % 8))) & 255 )) 0 0 0
+   [ $1 -gt 1 ] && shift $1 || shift
+   echo ${1-0}.${2-0}.${3-0}.${4-0}
+}
diff --git a/lib/mysql/functions.sql b/lib/mysql/functions.sql
index fd4198ae..90b96a4f 100644
--- a/lib/mysql/functions.sql
+++ b/lib/mysql/functions.sql
@@ -13,6 +13,31 @@ BEGIN
   RETURN (INET_ATON(ip) & INET_ATON(netmask)) = INET_ATON(subnet);
 END$$
 
+-- See: https://mebsd.com/cheat-sheets/broadcast-from-network-cidr-equation-examples.html
+-- find the broadcast address from 192.168.32.64/26:
+-- 192.168.32.64 + (2^(32-26)) - 1 = 192.168.32.127
+-- RETURNS varchar(20)
+DROP FUNCTION IF EXISTS cidr_range$$
+CREATE FUNCTION cidr_range (cidr VARCHAR(20), ip VARCHAR(20)) RETURNS TINYINT(1)
+DETERMINISTIC
+BEGIN
+  DECLARE subnet int unsigned;
+  DECLARE netmask int unsigned;
+  SET subnet = INET_ATON(substr(cidr,1,position('/' IN cidr)-1));
+  SET netmask = INET_ATON(CONCAT('255.255.255.',(255 << (8 - (substr(cidr,position('/' IN cidr) + 1) % 8)) & 255 )));
+  RETURN (INET_ATON(ip) & netmask) = subnet;
+END$$
+-- select cidr_range('10.3.8.0/22','10.3.8.1');
+
+DROP FUNCTION IF EXISTS cidr2network$$
+CREATE FUNCTION cidr2network (ipadd VARCHAR(15), mask VARCHAR(2)) RETURNS VARCHAR(15)
+RETURN INET_NTOA(
+    INET_ATON(ipadd) &
+    CONV(CONCAT(REPEAT(1, mask), REPEAT(0, 32 - mask)), 2, 10)
+);
+$$
+
+/*
 DROP FUNCTION IF EXISTS INET_CTOA$$
 DELIMITER ;;
 CREATE FUNCTION `INET_CTOA`(`paramCIDR` int) RETURNS varchar(15) CHARSET utf8
@@ -28,4 +53,5 @@ CREATE FUNCTION `INET_CTOA`(`paramCIDR` int) RETURNS varchar(15) CHARSET utf8
       RETURN INET_NTOA(`netmask`);
     END IF;
 END ;;
+*/
 DELIMITER ;
diff --git a/lib/php/db/table.php b/lib/php/db/table.php
index 086ecd21..b3c144cc 100644
--- a/lib/php/db/table.php
+++ b/lib/php/db/table.php
@@ -142,6 +142,7 @@ Class Table extends nb {
       return 'CREATE VIEW '.$this->sql_name().' AS SELECT'.preg_replace('/^CREATE\s+.*?\s+AS\s+.*?SELECT/i','',$this->sql());
       $sql = $this->sql();
       if ( strpos('CREATE ',$sql) !==0 )  $sql = 'CREATE VIEW '.$this->sql_name().' AS '.$sql;
+      #$sql_replace = $this->db()->type('create',false);
       return 'CREATE VIEW '.$this->sql_name().' AS '.$this->sql();
     }
 
-- 
2.47.3