Network Data Manipulation

Machbase supports various network data types and control functions using SELECT statements.

Table of Contents

IPv4​

INSERT

Syntax:

INSERT INTO table\_name VALUES \(value1,value2,value3,...\);

Examples:

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

Syntax:

SELECT column\_name,column\_name FROM table\_name;

Example:

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

Syntax:

INSERT INTO table\_name VALUES \(value1,value2,value3,...\);

Example:

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

Syntax:

SELECT column\_name,column\_name FROM table\_name;

Example:

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.
mach>

Network Mask

Network mask checks whether a list of specific addresses is included in a specific range in the network-related programs.
Machbase supports the network mask type and related operators.

Format of Network Mask

Just like the ordinary network representation, it is expressed in the form of adding "/" symbol and the number of bit strings at the end of network addresses.

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

Network Mask Operator

CONTAINS

The operator should have a network mask on the left and network address data type on the right. That is, it is checked whether the address type on the right side is included in the network range on the left side. 'NOT' can be used together.

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

As opposed to CONTAINS, this operator must have a network mask on the right and a network address data type on the left. That is, it checks whether the left address type is included in the right network mask range. It was created primarily to support the usual operator order in which the user column appears before the subject and the subject appears at the back.

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

With network mask, it can select data and check the values of sample insert and results.

Example:

​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&gt; 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&gt; 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&gt; 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&gt; SELECT addr4 FROM ip\_table WHERE addr4 CONTAINED '192.0.10.0/24';

addr4

----------------------------

192.0.10.1

\[1\] row\(s\) selected.

mach&gt; 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&gt; 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&gt; 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&gt; SELECT addr6 FROM ip\_table WHERE 'FFFF::192.0.10.0/120' CONTAINS addr6;

addr6

------------------------------------------------

ffff::c000:a01

\[1\] row\(s\) selected.

mach&gt; 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&gt; SELECT addr6 FROM ip\_table WHERE 'FFFF::192.128.99.0/32' not CONTAINS addr6;

addr6

-------------------------------------

\[0\] row\(s\) selected.

mach&gt; 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&gt; 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&gt; SELECT addr6 FROM ip\_table WHERE addr6 CONTAINED 'FFFF::192.0.10.0/120';

addr6

-------------------------------------

ffff::c000:a01

\[1\] row\(s\) selected.

mach&gt; SELECT addr6 FROM ip\_table WHERE addr6 not CONTAINED 'FFFF::192.128.99.0/32';

addr6

-------------------------------------

\[0\] row\(s\) selected.

results matching ""

    No results matching ""