ipcalc using mysql function

Aku cuba menggunakan sepenuhnya function mysql yang ada. Salah satunya penggunaan ipcalc; ipcalc ini dalam fedora ada (/bin/ipcalc), gunanya untuk calculate network address.

INSTALL:
1) Copy code dekat bawah
2) Connect ke mysql atau guna phpmyadmin
3) paste

Anda boleh check ade 3 functions yang akan dicreate
1) ipcalc
2) mask2prefix = convert netmask to prefix
3) prefix2mask = convert prefix to netmask

CODE:

-- ipcalc: calculate IPv4 address
-- nawawi <mohd.nawawi(at)gmail.com>
-- http://www.ronggeg.net/

DELIMITER //

CREATE FUNCTION `mask2prefix`(mask VARCHAR(15)) RETURNS bigint(10)
    DETERMINISTIC
BEGIN

DECLARE m BIGINT(10);
DECLARE c INT;

SET m=INET_ATON(mask);
SET c=0;
WHILE(m > 0) DO
 SET m=m << 1 & 0xffffffff;
 SET c=c+1;
END WHILE;
RETURN c;
END//

CREATE FUNCTION `prefix2mask`(prefix INT(3)) RETURNS varchar(15) CHARSET utf8
    DETERMINISTIC
BEGIN
RETURN INET_NTOA(~((1 << (32 - prefix)) - 1) & 0xffffffff);
END//

CREATE FUNCTION `ipcalc`(opt CHAR(1), ip VARCHAR(15), mask VARCHAR(15)) RETURNS varchar(15) CHARSET utf8
    DETERMINISTIC
BEGIN

DECLARE i BIGINT(10);
DECLARE m BIGINT(10);
DECLARE r BIGINT(10);

SET i=(INET_ATON(ip));
SET m=(INET_ATON(mask));

CASE opt
 WHEN 'p' THEN RETURN mask2prefix(mask);
 WHEN 'n' THEN SET r=i & m;
 WHEN 'b' THEN SET r=(i & m) | ~m & 0xffffffff;
ELSE
 SET r=NULL;
END CASE;
RETURN INET_NTOA(r);

END//

DELIMITER ;

CARA GUNA:
ipcalc(option,ip,netmask);
option: n = network, b = broadcast, p = prefix

mysql> select ipcalc('n','192.168.0.1','255.255.255.0') as network;
+-------------+
| network     |
+-------------+
| 192.168.0.0 |
+-------------+
1 row in set (0.00 sec)

mysql> select ipcalc('b','192.168.0.1','255.255.255.0') as broadcast;
+---------------+
| broadcast     |
+---------------+
| 192.168.0.255 |
+---------------+
1 row in set (0.00 sec)

mysql> select ipcalc('p','192.168.0.1','255.255.255.0') as prefix;
+--------+
| prefix |
+--------+
| 24     |
+--------+
1 row in set (0.00 sec)

mysql> select ipcalc('n','192.168.0.1',prefix2mask(24)) as network;
+-------------+
| network     |
+-------------+
| 192.168.0.0 |
+-------------+
1 row in set (0.00 sec)

mysql> select ipcalc('b','192.168.0.1',prefix2mask(24)) as broadcast;
+---------------+
| broadcast     |
+---------------+
| 192.168.0.255 |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE  `kejap` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use kejap;
Database changed
mysql> CREATE TABLE  kejap.ip (ip VARCHAR( 15 ) NOT NULL ,mask VARCHAR( 15 ) NOT NULL) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO  kejap.ip (ip,mask) VALUES ('192.168.0.1',  '255.255.255.0'), ('192.168.2.1',  '255.255.255.0');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select ip,mask,ipcalc('n',ip,mask) as network from ip;
+-------------+---------------+-------------+
| ip          | mask          | network     |
+-------------+---------------+-------------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0 |
| 192.168.2.1 | 255.255.0.0   | 192.168.0.0 |
+-------------+---------------+-------------+
2 rows in set (0.00 sec)

mysql> select ip,mask,ipcalc('n',ip,mask) as network,mask2prefix(mask) as prefix from ip;
+-------------+---------------+-------------+--------+
| ip          | mask          | network     | prefix |
+-------------+---------------+-------------+--------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0 |     24 |
| 192.168.2.1 | 255.255.0.0   | 192.168.0.0 |     16 |
+-------------+---------------+-------------+--------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE `ip` CHANGE `ip` `ip` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, CHANGE `mask` `mask` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
    -> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select ip,mask,CONCAT(ipcalc('n',ip,mask),'/',mask) as network,mask2prefix(mask) as prefix from ip;
+-------------+---------------+---------------------------+--------+
| ip          | mask          | network                   | prefix |
+-------------+---------------+---------------------------+--------+
| 192.168.0.1 | 255.255.255.0 | 192.168.0.0/255.255.255.0 |     24 |
| 192.168.2.1 | 255.255.0.0   | 192.168.0.0/255.255.0.0   |     16 |
+-------------+---------------+---------------------------+--------+
2 rows in set (0.00 sec)

Selamat mencuba!