반응형
본 포스트에는 C나 MySQL에서 사용되고 있는 INET_ATON()함수와 ATON_INET()함수를 MS SQL Server에서 구현해 보도록 하겠다.

이에 앞서 먼저 INET_ATON()함수와 ATON_INET()함수에 대해 간략하게 설명하도록 하겠다. 
이 함수들은 원래 특정 IP Address에 매핑되는 지역정보를 출력하기 위해 유래되었다. 
예를 들어 '192.15.10.125는 미국이다' 혹은 '192.15.10.125는 미국의 Broomfield다' 라는 지역정보를 조회하기 위해서...

이를 구현하기 위해서는 먼저 아래와 같이 특정 IP 대역과 지역정보가 대응되는 Database가 필요하다.
대역의 시작 IP

대역의 마지막 IP

나라

192.168.0.1

192.168.10.23

KR

192.168.10.24

192.168.101.255

US

192.168.102.1

192.168.102.10

CN



하지만 아시다시피 IPV4는 3옥텟으로 구분되어 있기 때문에 이를 그대로 Database화 시키면 아래와 같이 엉뚱한 정보를 조회하는 
결과를 낳게 된다.

-- 테스트 테이블 작성

CREATE TABLE ipcountry (

    startip VARCHAR(15)

   ,endip   VARCHAR(15)

   ,country VARCHAR(2)

);

GO


-- 테스트 데이터 입력

INSERT INTO ipcountry VALUES ('192.168.0.1', '192.168.10.23', 'KR' );

INSERT INTO ipcountry VALUES ('192.168.10.24', '192.168.101.255', 'US' );

INSERT INTO ipcountry VALUES ('192.168.102.1', '192.168.102.10', 'CN' );

GO


SELECT *

  FROM ipcountry


startip         endip           country

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

192.168.0.1     192.168.10.23   KR

192.168.10.24   192.168.101.255 US

192.168.102.1   192.168.102.10  CN


-- 데이터 조회

SELECT *

  FROM ipcountry

 WHERE '192.168.102.5' BETWEEN startip AND endip

GO


--> 출력 결과

startip         endip           country

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



이를 해결하기 위해 어떤 똑똑한(?) 사람이 3옥텟으로 구분되어 있는 IP Adress를 다음과 같은 계산식으로 주소를 변환하여 
Database화를 시켜 범위 검색이 가능하게 하였다. 

192.15.10.125 를 변환할 경우
Segment(s)

Weight(w)

s * Power(256, w)

192

3

3221225472

15

2

983040

10

1

2560

125

0

125


IP Number

3222211197



이 공식을 이용하여 다시 테이블을 작성하면 아래와 같다.

-- 테스트 테이블 작성

CREATE TABLE ipcountry2 (

    startip VARCHAR(15)

   ,endip   VARCHAR(15)

   ,startipnum BIGINT

   ,endipnum   BIGINT

   ,country VARCHAR(2)

);

GO


-- 테스트 데이터 입력

INSERT INTO ipcountry2 VALUES ('192.168.0.1', '192.168.10.23', 3232235521, 3232238103, 'KR' );

INSERT INTO ipcountry2 VALUES ('192.168.10.24', '192.168.101.255', 3232238104, 3232261631, 'US' );

INSERT INTO ipcountry2 VALUES ('192.168.102.1', '192.168.102.10' , 3232261633, 3232261642, 'CN');

GO


SELECT *

  FROM ipcountry2


startip         endip           startipnum           endipnum             country

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

192.168.0.1     192.168.10.23   3232235521           3232238103           KR

192.168.10.24   192.168.101.255 3232238104           3232261631           US

192.168.102.1   192.168.102.10  3232261633           3232261642           CN



-- 데이터 조회

SELECT *

  FROM ipcountry2

 WHERE 3232261637 BETWEEN startipnum AND endipnum -- 3232261637 = '192.168.102.5'


--> 출력 결과

startip         endip           startipnum           endipnum             country

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

192.168.102.1   192.168.102.10  3232261633           3232261642           CN

하지만, 이렇게 사용하기 위해서는 매번 특정 IP 주소에 대응하는 숫자형 주소로 바꾸어 계산해야 하는 불편함이 있다.
그래서 사용되는 것이 NET_ATON()함수와 ATON_INET()함수이며, 이를 MS SQL Server에서 구현하기 위해 
다음과 같은 함수를 만들어 보았다.

1. 숫자형 IP주소를 IP Address로 변환하는 함수 작성하기

CREATE FUNCTION dbo.Func_ConvertNum2Ip(@ip BIGINT)

RETURNS VARCHAR(15)

AS

BEGIN

    DECLARE @delimiter VARCHAR(1)

          , @SUBNET_MASK INT

          , @idx INT

          , @ipSeg BIGINT

          , @segments VARCHAR(15)


    SET @delimiter = '.'

    SET @SUBNET_MASK = 256

    SET @idx = 1

    SET @ipSeg = 0

    SET @segments = ''


    WHILE (@idx <= 4)

    BEGIN

        SET @ipSeg = CAST(@ip / POWER(@SUBNET_MASK,4-@idx) AS BIGINT)

        SET @ip=@ip - CAST(@ipSeg * POWER(@SUBNET_MASK,4-@idx) AS BIGINT)

        IF (@idx = 1)

            SET @segments = CAST(@ipSeg AS VARCHAR)

        ELSE

            SET @segments = @segments + @delimiter + CAST(@ipSeg AS VARCHAR)

            SET @idx = @idx +1

    END


    RETURN  @segments

END

GO


2. IP Address를 숫자형 IP주소로 변환하는 함수 작성하기

CREATE FUNCTION dbo.Func_ConvertIp2Num(@ip NVARCHAR(15))

RETURNS BIGINT

AS

BEGIN

    DECLARE @delimiter NVARCHAR(1)

          , @SUBNET_MASK INT

          , @idx INT

          , @ipNum FLOAT

          , @textXML XML

    DECLARE @segments TABLE(id INT ,col INT)


    SET @delimiter = '.'

    SET @SUBNET_MASK = 256

    SET @idx = 4

    SET @ipNum = 0

    SELECT    @textXML = CAST('<col>' + REPLACE(@ip, @delimiter, '</col><col>') + '</col>' AS XML);


    INSERT INTO @segments(id, col)

        SELECT ROW_NUMBER () OVER (ORDER BY col) AS id

             , T.col.value('.', 'int') AS col

          FROM @textXML.nodes('/col') T(col)

         ORDER BY id DESC


    SELECT @ipNum = @ipNum + (CAST((col % @SUBNET_MASK) AS FLOAT) * POWER(@SUBNET_MASK,@idx-id))

      FROM @segments


    RETURN CAST(@ipNum AS BIGINT)

END

GO


자 이제, 먼저 테스트에 사용한 질의문을 아래와 같이 수정한 다음 조회을 하면 성공적으로 결과를 얻을 수 있음을 확인할 수 있다.

단, 당연한 이야기이겠지만 대량의 데이터를 조회하기 위해서는 최적의 인덱스 설계가 필요하다.

-- 데이터 조회

SELECT *

  FROM ipcountry2

 WHERE dbo.Func_ConvertIp2Num('192.168.102.5') BETWEEN startipnum AND endipnum


--> 출력 결과

startip         endip           startipnum           endipnum             country

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

192.168.102.1   192.168.102.10  3232261633           3232261642           CN




반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

인덱스 정보를 조회하는 뷰 New  (0) 2011.07.18
MS SQL Server 2005 설치 후 기본적으로 해야 할 일  (0) 2011.07.18
Oracle의 LPAD함수의 기능 구현하기  (0) 2011.07.18
SQL check - idera 사의 무료 모니터링 툴  (0) 2011.07.18
인덱스 조각화(단편화) 정보를 조회하는 뷰 2  (0) 2011.07.18

+ Recent posts

티스토리툴바