Представление адресов IPv4/IPv6 в Oracle

Какой тип данных или метод представления сетевых адресов в Oracle является подходящим, какие адреса могут быть IPv4 или IPv6.

Есть два подхода:

1. только хранение.
2. сохранение обычного представления

Только для хранения. Адрес IPV4 должен быть целым числом (достаточно 32 бита). Для IP V6, 128 бит, подойдет INTEGER (аналогичный Number(38)). Конечно, это хранение. Этот подход исходит из того, что представление является делом приложения.

Если выбрать противоположную стратегию хранения обычного представления, необходимо убедиться, что адреса IP V4 и IPV6 имеют только одно традиционное (строковое) представление. Это хорошо известно для ipV4. Что касается IPV6, то здесь тоже стандартный формат.

Я отдаю предпочтение первой стратегии. В худшем случае вы можете применить гибридный подход (хотя и некислотный) и хранить как двоичное, так и ascii-представление рядом с «приоритетом» двоичного значения.

Однако ни одна строка не содержит адреса как v4, так и v6.

Стандартное представление адреса IPV4 в формате IPV6: ::ffff:192.0.2.128.

Я не знаю контекста, но я бы зарезервировал 2 столбца, один для IPV4, а другой для отдельного адреса ipV6.

Обновление
Следуя хорошему комментарию @sleepyMonad, я хотел бы отметить, что вместо типа данных Number предпочтительнее использовать тип данных INTEGER, который с радостью вместит максимальное возможное значение, которое может быть выражено 128-битным целым числом 'ff...ff' (для которого потребуется 39 десятичных цифр). 38 — это наивысшая степень числа от 0 до 9, которое может быть закодировано 128 битами, но при этом можно вставить максимальное беззнаковое значение для 2**128 - 1 ( десятичное число 340282366920938463463374607431768211455). Вот небольшой тест, иллюстрирующий эту возможность.

create table test (
  id integer primary key,
  ipv6_address_bin INTEGER );

-- Let's enter 2**128 - 1 in the nueric field
insert into test (id, ipv6_address_bin) values ( 1, to_number ( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ) ;

-- retrieve it to make sure it's not "truncated".
select to_char ( ipv6_address_bin, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ) from test where id = 1 ;
-- yields 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

select to_char ( ipv6_address_bin ) from test where id = 1 ;
-- yields 340282366920938463463374607431768211455

select LOG(2, ipv6_address_bin) from test where id = 1 ;
-- yields 128

select LOG(10, ipv6_address_bin) from test where id = 1 ;
-- yields > 38

Сохраняйте в RAW.

RAW - это массив байтов переменной длины, поэтому....

• просто рассматривайте IPv4 как массив из 4 байтов
• и IPv6 как массив из 16 байт

...и сохранить любой из них непосредственно в RAW(16).

RAW может быть проиндексирован, быть PK, UNIQUE или FOREIGN KEY, поэтому вы можете делать все, что обычно делаете с VARCHAR2 или INT/NUMBER/DECIMAL, но с меньшими затратами на преобразование и хранение.

Чтобы проиллюстрировать накладные расходы хранения INT по сравнению с RAW, рассмотрим следующий пример:

CREATE TABLE IP_TABLE (
    ID INT PRIMARY KEY,
    IP_RAW RAW(16), 
    IP_INT INT
);

INSERT INTO IP_TABLE (ID, IP_RAW, IP_INT) VALUES (
    1,
    HEXTORAW('FFFFFFFF'),
    TO_NUMBER('FFFFFFFF', 'XXXXXXXX')
);

INSERT INTO IP_TABLE (ID, IP_RAW, IP_INT) VALUES (
    2,
    HEXTORAW('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'),
    TO_NUMBER('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
);

SELECT VSIZE(IP_RAW), VSIZE(IP_INT), IP_TABLE.*  FROM IP_TABLE;

Результат (под Oracle 10.2):

table IP_TABLE created.
1 rows inserted.
1 rows inserted.
VSIZE(IP_RAW)          VSIZE(IP_INT)          ID                     IP_RAW                           IP_INT                 
---------------------- ---------------------- ---------------------- -------------------------------- ---------------------- 
4                      6                      1                      FFFFFFFF                         4294967295             
16                     21                     2                      FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 340282366920938463463374607431768211455 

@Alain Pannetier (потому что я пока не могу комментировать): тип данных ANSI INTEGER сопоставляется с NUMBER (38) в Oracle в соответствии с http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54335. Ниже таблицы вы найдете информацию о том, что NUMBER обеспечивает только 126-битную двоичную точность, чего недостаточно для 128-битного IPv6-адреса. Максимальное значение может храниться нормально, но будут адреса, которые округляются до следующего меньшего значения.

Внутренний числовой формат: ROUND((длина(p)+s)/2))+1 (http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype)..htm#i16209).

Обновление: После того, как я снова повозился с проблемой, я нашел решение, которое позволяет высокопроизводительно запрашивать сети, содержащие адрес IPv6: сохранять адреса IPv6 и маски подсети в столбцах RAW(16) и сравните их, используя UTL_RAW.BIT_AND:

SELECT name, DECODE(UTL_RAW.BIT_AND('20010DB8000000000000000000000001', ipv6_mask), ipv6_net, 1, 0)
FROM ip_net
WHERE ipv6_net IS NOT NULL;

вы также можете использовать собственный объект оракула.

SQL>set SERVEROUTPUT on
SQL>drop table test;

Table dropped.

SQL>drop type body inaddr;

Type body dropped.

SQL>drop type inaddr;

Type dropped.

SQL>create type inaddr as object
  2  ( /* TODO enter attribute and method declarations here */
  3  A number(5),
  4  B number(5),
  5  C number(5),
  6  D number(5),
  7  E number(5),
  8  F number(5),
  9  G number(5),
 10  H NUMBER(5),
 11  MAP MEMBER FUNCTION display RETURN VARCHAR2,
 12  MEMBER FUNCTION toString( SELF IN INADDR , CONTRACT BOOLEAN DEFAULT TRUE) RETURN VARCHAR2,
 13  CONSTRUCTOR FUNCTION INADDR(SELF IN OUT NOCOPY INADDR, INADDRASSTRING VARCHAR2)  RETURN SELF AS RESULT
 14  
 15  ) NOT FINAL;
 16  /

SP2-0816: Type created with compilation warnings

SQL>
SQL>
SQL>CREATE TYPE BODY INADDR AS
  2  
  3  MAP MEMBER FUNCTION display RETURN VARCHAR2
  4  IS BEGIN
  5  return tostring(FALSE);
  6  END;
  7  
  8  
  9  MEMBER FUNCTION TOSTRING( SELF IN  INADDR , CONTRACT BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 IS
 10  IP4 VARCHAR2(6) := 'FM990';
 11  ip6 varchar2(6) := 'FM0XXX';
 12    BEGIN
 13  IF CONTRACT THEN
 14    ip6 := 'FMXXXX';
 15  end if;
 16  
 17  IF CONTRACT AND A =0 AND B=0 AND C = 0 AND D=0 AND E =0 AND F = 65535 THEN --ipv4
 18      RETURN  '::FFFF:'||TO_CHAR(TRUNC(G/256),'FM990.')||TO_CHAR(MOD(G,256),'FM990.')||TO_CHAR(TRUNC(H/256),'FM990.')||TO_CHAR(MOD(H,256),'FM990');
 19  ELSE
 20      RETURN
 21  TO_CHAR(A,ip6)||':'||
 22  TO_CHAR(B,IP6)||':'||
 23  TO_CHAR(C,ip6)||':'||
 24  TO_CHAR(D,ip6)||':'||
 25  TO_CHAR(E,ip6)||':'||
 26  TO_CHAR(F,ip6)||':'||
 27  TO_CHAR(G,ip6)||':'||
 28  TO_CHAR(H,ip6);
 29  end if;
 30    end;
 31  
 32      CONSTRUCTOR FUNCTION inaddr(SELF IN OUT NOCOPY inaddr, inaddrasstring VARCHAR2)
 33                                 RETURN SELF AS RESULT IS
 34      begin
 35          if instr(inaddrasstring,'.') > 0 then
 36            --ip4
 37  null;
 38              a := 0;
 39              B := 0;
 40              C := 0;
 41              D := 0;
 42              E := 0;
 43              F := TO_NUMBER('FFFF', 'XXXX');
 44              G := TO_NUMBER(TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,1,'i',1),'999'),'FM0X')
 45  ||TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,2,'i',1),'999'),'FM0X')
 46  ,'XXXX');
 47              h := TO_NUMBER(TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,3,'i',1),'999'),'FM0X')
 48  ||TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3})',1,4,'i',1),'999'),'FM0X')
 49  ,'XXXX');
 50  
 51          ELSIF instr(inaddrasstring,':') > 0 then
 52              --ip6
 53              a := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,1,'i',1),'XXXX');
 54              b := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,2,'i',1),'XXXX');
 55              c := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,3,'i',1),'XXXX');
 56              d := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,4,'i',1),'XXXX');
 57              E := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,5,'i',1),'XXXX');
 58              f := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,6,'i',1),'XXXX');
 59              g := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,7,'i',1),'XXXX');
 60              H := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,8,'i',1),'XXXX');
 61          end if;
 62  
 63          RETURN;
 64      END;
 65  end;
 66  /

Type body created.

SQL>
SQL>create table test
  2  (id integer primary key,
  3  address inaddr);

Table created.

SQL>
SQL>select * from test;

no rows selected

SQL>
SQL>
SQL>insert into test values (1, INADDR('fe80:0000:0000:0000:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (2, INADDR('192.0.2.128') );

1 row created.

SQL>insert into test values (3, INADDR('20.0.20.1') );

1 row created.

SQL>insert into test values (4, INADDR('fe80:0001:0002:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>insert into test values (5, INADDR('fe80:0003:0002:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (6, INADDR('fe80:0003:0001:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (7, INADDR('fe80:0003:0001:0003:0202:b3ff:fe1e:8328') );

1 row created.

SQL>INSERT INTO TEST VALUES (8, INADDR('dead:beef:f00d:cafe:dea1:aced:b00b:1234') );

1 row created.

SQL>
SQL>COLUMN INET_ADDRESS_SHORT FORMAT A40
SQL>column inet_address_full format a40
SQL>
SQL>select t.address.toString() inet_address_short, t.address.display( ) inet_address_full
  2  from test T
  3  order by t.address ;

INET_ADDRESS_SHORT                       INET_ADDRESS_FULL
---------------------------------------- ----------------------------------------
::FFFF:20.0.20.1                         0000:0000:0000:0000:0000:FFFF:1400:1401
::FFFF:192.0.2.128                       0000:0000:0000:0000:0000:FFFF:C000:0280
DEAD:BEEF:F00D:CAFE:DEA1:ACED:B00B:1234  DEAD:BEEF:F00D:CAFE:DEA1:ACED:B00B:1234
FE80:0:0:0:202:B3FF:FE1E:8329            FE80:0000:0000:0000:0202:B3FF:FE1E:8329
FE80:1:2:3:202:B3FF:FE1E:8329            FE80:0001:0002:0003:0202:B3FF:FE1E:8329
FE80:3:1:3:202:B3FF:FE1E:8328            FE80:0003:0001:0003:0202:B3FF:FE1E:8328
FE80:3:1:3:202:B3FF:FE1E:8329            FE80:0003:0001:0003:0202:B3FF:FE1E:8329
FE80:3:2:3:202:B3FF:FE1E:8329            FE80:0003:0002:0003:0202:B3FF:FE1E:8329

8 rows selected.

SQL>spool off

Я только что собрал это за последний час (и в то же время учил себя предметам), так что я уверен, что это можно улучшить. если я буду делать обновления, я буду размещать их здесь

Я бы предпочел хранить IP-адреса только в строке в формате, возвращаемом SYS_CONTEXT ('USERENV', 'IP_ADDRESS')

В ссылке на SYS_CONTEXT в 11g описывается только возврат по умолчанию длина значения составляет 256 байт и не описывает размер возвращаемого значения именно для контекста «IP_ADDRESS».

В документе база данных Oracle и Заявление о направлении IPv6 описано:

Oracle Database 11g Release 2 поддерживает стандартную нотацию адресов IPv6, указанную в RFC2732. 128-битный IP-адрес обычно представлен в виде 8 групп из 4 шестнадцатеричных цифр с символом «:» в качестве разделителя групп. Ведущие нули в каждой группе удаляются. Например, 1080:0:0:0:8:800:200C:417A будет действительным адресом IPv6. Одно или несколько последовательных нулевых полей могут быть дополнительно сжаты с помощью разделителя «::». Например, 1080::8:800:200C:417A.

Из этих заметок я предпочитаю сделать столбец IP_ADDRESS varchar2(39), чтобы позволить хранить 8 групп по 4 цифры и 7 разделителей между этими группами.

В документации Oracle указано, что INTEGER является псевдонимом NUMBER(38), но это, вероятно, опечатка, поскольку в абзаце выше говорится:

ЧИСЛО(p,s), где: p - точность... Oracle гарантирует переносимость чисел с точностью до 20 знаков по основанию 100, что эквивалентно 39 или 40 десятичным знакам в зависимости от положения десятичной точки.

Таким образом, NUMBER может хранить от 39 до 40 цифр, а INTEGER, скорее всего, является псевдонимом NUMBER (максимальная точность), а не NUMBER (38). Вот почему приведенный пример работает (и он работает, если вы измените INTEGER на NUMBER).

Возможности:

• Сохранить как строку, т. е. VARCHAR2 (пример 1080::8:800:200c:417a)
• Store as numeric value
  • NUMBER data type
  • INTEGER тип данных
• Store as RAW value
  • One RAW value, i.e. RAW(4) or RAW(16) for IPv4 or IPv6 respectively
  • 4 x RAW(1) или 8 x RAW(2) для IPv4 или IPv6 соответственно

Я бы рекомендовал использовать значения RAW, потому что

• Если вы используете строки, вы должны учитывать различные форматы IPv6.

  1080::8:800:200C:417A
  1080::8:800:200c:417a
  1080::8:800:32.12.65.122
  1080:0:0:0:8:800:200C:417A
  1080:0:0:0:0008:0800:200C:417A
  1080:0000:0000:0000:0008:0800:200C:417A
  

  все являются юридическими представлениями одного и того же IP-адреса IPv6. Ваше приложение должно применять общий формат для правильного использования, например. б/у в WHERE состоянии.

• NUMBER/INTEGER значения не имеют смысла без преобразования в удобочитаемый формат. Вы не можете использовать тип данных INTEGER в PL/SQL

  i INTEGER := 2**128-1; -- i.e. ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
  
  -> ORA-06502: PL/SQL: numeric or value error: number precision too large. 
  

• Если вам нужно работать с подсетями, вы не можете использовать функцию BITAND - он также поддерживает числа только до 2^127

• Вы можете использовать функции UTL_RAW UTL_RAW.BIT_AND, UTL_RAW.BIT_COMPLEMENT, UTL_RAW.BIT_OR для подсети операции.

• В случае, если вам приходится иметь дело с действительно большим объемом данных (я говорю о миллиардах строк), может быть полезно разделить IP-адрес на несколько значений RAW, т.е. 4 x RAW(1) или 8 x RAW(2). Такие столбцы будут предопределены для битмап-индексов. сэкономить много места на диске и повысить производительность.