DBを使ったIPアドレス管理~解説編~

2010 年 9 月 10 日 by 山平

前々回(DBを使ったIPアドレス管理~任意のレンジで抽出する~)、前回(DBを使ったIPアドレス管理~積集合でレンジを表現する~)と2回にわたってIPアドレスをDBで管理する方法を検討しました。
改めて読み返してみると、2回目の説明が少なすぎるように思いましたので、少し解説したいと思います。
1回目の内容についてはそれなりに解説できていること、2回目の内容のほとんどの考え方は1回目の解説で理解できることから、2回目の「利用可能なレンジの一覧」を取得するSQLについて解説します。

利用可能なレンジの一覧を取得するSQLは添付ファイルのIPv4Range#get_usable_blockメソッド内に記述されています。

[SQL]
SELECT G.`network`, count(G.`network`) FROM (
SELECT B.* FROM (
SELECT
o1.value AS o1, o2.value AS o2, o3.value AS o3, o4.value AS o4
,((((( o1.value * 256 + o2.value) * 256) + o3.value) * 256) + o4.value) AS `long`
,((((( o1.value * 256 + o2.value) * 256) + o3.value) * 256) + o4.value) & #{mask[:long]} AS `network`
FROM `octet` AS o1, `octet` AS o2, `octet` AS o3, `octet` AS o4
WHERE
o1.value BETWEEN #{network[:dot][0]} AND #{broadcast[:dot][0]}
AND o2.value BETWEEN #{network[:dot][1]} AND #{broadcast[:dot][1]}
AND o3.value BETWEEN #{network[:dot][2]} AND #{broadcast[:dot][2]}
AND o4.value BETWEEN #{network[:dot][3]} AND #{broadcast[:dot][3]}
AND ((((( o1.value * 256 + o2.value) * 256) + o3.value) * 256) + o4.value)
BETWEEN #{network[:long]} AND #{broadcast[:long]}
) AS B
WHERE NOT EXISTS(
SELECT 1 FROM `range` AS R WHERE B.long BETWEEN R.`network` AND R.`broadcast`
)
ORDER BY B.o1, B.o2, B.o3, B.o4
) AS G
GROUP BY G.`network` HAVING count(G.`network`) >= #{hosts}
ORDER BY G.`network`
[/SQL]

これを内側から順に読み解くと、

  1. オクテット4つの積集合(全IPアドレス)を作成→”B”
    1. 計算処理簡略化のためにロングIPアドレスも算出しておく
    2. 引数のレンジでネットワークアドレスも算出しておく
    3. レコードが多すぎるので(引数のレンジで)大まかに範囲を絞る
  2. “B”を厳密な条件で絞って引数のレンジ内で未使用の全IPアドレス集合にする→”G”
    1. すでに割り当てられたレンジの範囲に含まれているレコードを除く
    2. アドレスの若い順にソート(主に確認目的)
  3. “G”から利用可能なレンジのみ抽出する
    1. “G”をネットワークアドレスでグループ化
    2. グループ化した件数で未使用かどうかを判断して抽出する

という処理を行っています。

グループ化しているので、抽出結果には利用可能な各レンジのネットワークアドレスしか抽出されません。
これに引数のblock(利用するIPアドレスのレンジの幅をあらわすプレフィックス)を与えることで要求された結果を得ることができます。

少し余談になりますが、テーブル上に人間用のドットIPアドレスと計算用のロングIPアドレスの両方を持っているため、アドレス表記の変換処理がしばしば発生します。
本質的でない処理が点在するとバグの元になりますので、IPv4Rangeクラスでは、アドレス計算用のメソッドを作成しています。

IPv4Range::long_to_dot
ロングIPからドットIP(配列)を取得します
IPv4Range::dot_to_long
ドットIP(配列)からロングIPを取得します
IPv4Range::get_ip_info
ドットIPのレンジからIPアドレス、ネットマスク、ネットワークアドレス、ホストアドレス、ブロードキャストそれぞれのドットIP(配列)とロングIPの両方を取得します

以上です。

タグ:

TrackBack