마크베이스 매뉴얼
주요기능 / 테이블
로그 테이블 (Log Table)
로그 데이터의 추출
네트워크 데이터 타입 / 연산자

네트워크 데이터 타입 / 연산자

마크베이스는 네트워크 데이터 타입을 지원함과 동시에 SELECT 문에서 사용할 수 있는 함수들을 지원한다.

  • IPv4 형식 : 4바이트 주소 타입
  • IPv6 형식 : 16바이트 주소 타입
  • 네트워크 마스크 : IPv4 또는 IPv6 에 대한 네트워크 마스크 지정 형식(/비트수)

IPv4

INSERT 

INSERT INTO table_name VALUES (value1,value2,value3,...);
CREATE TABLE addrtable (addr IPV4);
INSERT  INTO addrtable VALUES ('127.0.0.1');
INSERT  INTO addrtable VALUES ('127.0' || '.0.2');
INSERT  INTO addrtable VALUES ('127.0.0.3');
INSERT  INTO addrtable VALUES ('127.0.0.4');
INSERT  INTO addrtable VALUES ('127.0.0.5');
INSERT  INTO addrtable VALUES ('255.255.255.255');

SELECT 

SELECT  column_name,column_name FROM    table_name;
Mach> SELECT addr FROM addrtable WHERE addr = '127.0.0.3' or addr = '127.0.0.5';
addr           
------------------
127.0.0.5      
127.0.0.3      
[2] row(s) selected.
 
Mach> SELECT addr FROM addrtable WHERE addr > '127.0.0.3' AND addr < '127.0.0.5';
addr           
------------------
127.0.0.4      
[1] row(s) selected.
 
Mach> SELECT addr FROM addrtable WHERE addr <> '127.0.0.3';
addr           
------------------
255.255.255.255
127.0.0.5      
127.0.0.4      
127.0.0.2      
127.0.0.1      
[5] row(s) selected.
 
Mach> SELECT addr FROM addrtable WHERE addr = '127.0.0.*';
addr           
------------------
127.0.0.5      
127.0.0.4      
127.0.0.3      
127.0.0.2      
127.0.0.1      
[5] row(s) selected.
 
Mach> SELECT addr FROM addrtable WHERE addr = '*.0.0.*';
addr           
------------------
127.0.0.5      
127.0.0.4      
127.0.0.3      
127.0.0.2      
127.0.0.1      
[5] row(s) selected.

IPv6

INSERT 

INSERT  INTO    table_name  VALUES  (value1,value2,value3,...);
CREATE TABLE addrtable6 (addr ipv6);
INSERT INTO addrtable6 VALUES ('::0.0.0.0');
INSERT INTO addrtable6 VALUES ('::127.0' || '.0.1');
INSERT INTO addrtable6 VALUES ('::127.0.0.3');
INSERT INTO addrtable6 VALUES ('::127.0.0.4');
INSERT INTO addrtable6 VALUES ('21DA:D3:0:2F3B:2AA:FF:FE28:9C5A');
INSERT INTO addrtable6 VALUES ('::FFFF:255.255.255.255');

SELECT 

SELECT  column_name,column_name FROM    table_name;
Mach> SELECT addr FROM addrtable6 WHERE addr = '::127.0.0.3' or addr = '::127.0.0.5';
addr                                                        
---------------------------------------------------------------
::127.0.0.3                  
[1] row(s) selected.
 
Mach> SELECT addr FROM addrtable6 WHERE addr > '::127.0.0.3' and addr < '::127.0.0.5';
addr                                                        
---------------------------------------------------------------
::127.0.0.4                     
[1] row(s) selected.
 
Mach> SELECT addr FROM addrtable6 WHERE addr <> '::127.0.0.3';
addr                                                        
---------------------------------------------------------------
::ffff:255-255.255.255
21da:d3::2f3b:2aa:ff:fe28:9c5a
::127.0.0.4
::127.0.0.1
::                     
[5] row(s) selected.
 
Mach> SELECT addr FROM addrtable6 WHERE addr >= '21DA::';
addr                                                        
---------------------------------------------------------------
21da:d3::2f3b:2aa:ff:fe28:9c5a                   
[1] row(s) selected.
 
Mach> SELECT addr FROM addrtable6 order by addr desc;
addr                                                        
---------------------------------------------------------------
21da:d3::2f3b:2aa:ff:fe28:9c5a
::ffff:255.255.255.255
::127.0.0.4
::127.0.0.3
::127.0.0.1
::                   
[6] row(s) selected.

네트워크 마스크

네트워크 마스크는 특정 주소가 특정한 네트워크에 포함되는지를 지정하는 표현 형식이다. 마크베이스는 네트워크 마스크 타입과 관련 연산자를 지원한다.

마스크의 표현 형태

일반 네트워크 표현과 마찬가지로 네트워크 주소 마지막에 / 기호와 비트 개수를 표현하는 형식으로 나타낸다.

'192.128.0.0/16'
'FFFF::192.128.99.0/32'

마스크 연산자

CONTAINS

이 연산자는 왼쪽에 네트워크 마스크와 오른쪽에 네트워크 주소 데이터 타입이 나와야 한다. 즉 입력된 주소가 주어진 네트워크 마스크에 포함되는지를 검사한다. NOT 연산자도 함께 사용할 수 있다.

SELECT addr FROM addrtable  WHERE '192.0.0.0/16'    CONTAINS    addr;
SELECT addr FROM addrtable  WHERE '192.128.99.0/32' NOT CONTAINS    addr;

CONTAINED

CONSTAINS와 반대로, 네트워크 주소가 왼쪽, 네트워크 마스크가 오른쪽이다. 왼쪽 주소가 오른쪽 마스크의 일부인지를 검사한다.

SELECT addr FROM addrtable  WHERE addr CONTAINED '192.0.0.0/16';
SELECT addr FROM addrtable  WHERE addr NOT CONTAINED '192.128.99.0/32';

마스크 사용 예제

네트워크 마스크 타입을 이용한 검색의 예는 다음과 같다.

CREATE TABLE ip_table (addr4 IPV4, addr6 IPV6);
 
INSERT INTO ip_table VALUES ('192.0.0.1','FFFF::192.0.0.1');
INSERT INTO ip_table VALUES ('192.0.10.1','FFFF::192.0.10.1');
INSERT INTO ip_table VALUES ('192.128.0.1','FFFF::192.128.0.1');
INSERT INTO ip_table VALUES ('192.128.99.128','FFFF::192.128.99.128');
INSERT INTO ip_table VALUES ('192.128.99.64','FFFF::192.128.99.64');
INSERT INTO ip_table VALUES ('192.128.99.32','FFFF::192.128.99.32');
INSERT INTO ip_table VALUES ('192.128.99.16','FFFF::192.128.99.16');
INSERT INTO ip_table VALUES ('192.128.99.8','FFFF::192.128.99.8');
INSERT INTO ip_table VALUES ('192.128.99.4','FFFF::192.128.99.4');
INSERT INTO ip_table VALUES ('192.128.99.2','FFFF::192.128.99.2');
INSERT INTO ip_table VALUES ('192.128.99.1','FFFF::192.128.99.1');
 
Mach> SELECT addr4 FROM ip_table WHERE '192.0.0.0/16' CONTAINS addr4;
addr4
-----------
192.0.10.1
192.0.0.1
[2] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE '192.128.0.0/16' CONTAINS addr4;
addr4
-----------
192.128.99.1
192.128.99.2
192.128.99.4
192.128.99.8
192.128.99.16
192.128.99.32
192.128.99.64
192.128.99.128
192.128.0.1
[9] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE '192.0.10.0/24' CONTAINS addr4;
addr4
--------------------------------------------------------------------
192.0.10.1
[1] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE '192.128.99.0/31' CONTAINS addr4;
addr4
-------------------------------------------------------
192.128.99.1
[1] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE '192.128.99.0/32' NOT CONTAINS addr4;
addr4
-----------
192.128.99.1
192.128.99.2
192.128.99.4
192.128.99.8
192.128.99.16
192.128.99.32
192.128.99.64
192.128.99.128
192.128.0.1
192.0.10.1
192.0.0.1
[11] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE addr4 CONTAINED '192.0.0.0/16';
addr4
-------------------------------------
192.0.10.1
192.0.0.1
[2] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE addr4 CONTAINED '192.128.0.0/16';
addr4
-------------------------------------
192.128.99.1
192.128.99.2
192.128.99.4
192.128.99.8
192.128.99.16
192.128.99.32
192.128.99.64
192.128.99.128
192.128.0.1
[9] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE addr4 CONTAINED '192.0.10.0/24';
addr4
----------------------------
192.0.10.1
[1] row(s) selected.
 
Mach> SELECT addr4 FROM ip_table WHERE addr4 not CONTAINED '192.128.99.0/32';
addr4
-------------------------------------------------
192.128.99.1
192.128.99.2
192.128.99.4
192.128.99.8
192.128.99.16
192.128.99.32
192.128.99.64
192.128.99.128
192.128.0.1
192.0.10.1
192.0.0.1
[11] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE 'FFFF::192.0.0.0/104' CONTAINS addr6;
addr6
-------------------------------------
ffff::c080:6301
ffff::c080:6302
ffff::c080:6304
ffff::c080:6308
ffff::c080:6310
ffff::c080:6320
ffff::c080:6340
ffff::c080:6380
ffff::c080:1
ffff::c000:a01
ffff::c000:1
[11] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE 'FFFF::192.128.0.0/112' CONTAINS addr6;
addr6
------------------------------------
ffff::c080:6301
ffff::c080:6302
ffff::c080:6304
ffff::c080:6308
ffff::c080:6310
ffff::c080:6320
ffff::c080:6340
ffff::c080:6380
ffff::c080:1
[9] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE 'FFFF::192.0.10.0/120' CONTAINS addr6;
addr6
------------------------------------------------
ffff::c000:a01
[1] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE 'FFFF::192.128.99.0/31' CONTAINS addr6;
addr6
---------------------------------------------
ffff::c080:6301
ffff::c080:6302
ffff::c080:6304
ffff::c080:6308
ffff::c080:6310
ffff::c080:6320
ffff::c080:6340
ffff::c080:6380
ffff::c080:1
ffff::c000:a01
ffff::c000:1
[11] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE 'FFFF::192.128.99.0/32' not CONTAINS addr6;
addr6
-------------------------------------
[0] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE addr6 CONTAINED 'FFFF::192.0.0.0/104';
addr6
-------------------------------------
ffff::c080:6301
ffff::c080:6302
ffff::c080:6304
ffff::c080:6308
ffff::c080:6310
ffff::c080:6320
ffff::c080:6340
ffff::c080:6380
ffff::c080:1
ffff::c000:a01
ffff::c000:1
[11] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE addr6 CONTAINED 'FFFF::192.128.0.0/112';
addr6
-------------------------------------
ffff::c080:6301
ffff::c080:6302
ffff::c080:6304
ffff::c080:6308
ffff::c080:6310
ffff::c080:6320
ffff::c080:6340
ffff::c080:6380
ffff::c080:1
[9] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE addr6 CONTAINED 'FFFF::192.0.10.0/120';
addr6
-------------------------------------
ffff::c000:a01
[1] row(s) selected.
 
Mach> SELECT addr6 FROM ip_table WHERE addr6 not CONTAINED 'FFFF::192.128.99.0/32';
addr6
-------------------------------------
[0] row(s) selected.
최근 업데이트
단순 Join