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

Ответ 1

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

Существует два подхода:

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

Только для хранения. 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, я хотел бы указать, что вместо типа данных "Число" предпочтительнее использовать тип данных 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 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 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

Ответ 2

Сохраните его в 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 

Ответ 3

@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;

Ответ 4

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

NUMBER (p, s) где: p - точность... Oracle гарантирует переносимость номеров с точностью до 20 базовых 100 цифр, что эквивалентно 39 или 40 десятичным разрядам в зависимости от положение десятичной точки.

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

Ответ 5

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

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

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

Ответ 6

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

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

В документе Oracle Database и IPv6 Statement of Direction описано:

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 разделителей между этими группами.