SQLiteでIPソート

SQLiteIPアドレスを意図した順にソートする話。

複雑なわりには、使いどころが微妙(´・ω・`)


PostgreSQL では inet 型にCASTすると簡単なものの、SQlite には inet型は無い。

0詰めしてソートしようとしても、PostgreSQL の「split_part」や「lpad」に相当する組み込み関数が無い。


どうにかできないか考えたところ、「SUBSTR」と「INSTR」を使えば何とかできそう。

但し、SQLite の「INSTR」は、バージョン 3.7.15 以降で利用可能。

なお、CentOS 7.2.1511 の標準リポジトリから yum でインストールされている SQLite は下記の通り。

$ sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

CentOS 6.8ではバージョンが低いので未対応。

$ sqlite3 --version
3.6.20


では、SQLite の「SUBSTR」と「INSTR」を使って、テキストとして保存されたIPアドレスを0詰めしてソートしてみる。

準備として、結果を見やすく設定。

sqlite> .header on
sqlite> .mode column
sqlite> .width 15


最初に、普通にソートした場合。

sqlite> SELECT ip FROM network ORDER BY ip;
ip
----------------
10.1.10.10
10.20.10.10
10.3.10.10
10.3.10.5
192.168.1.1
192.168.10.1
192.168.2.1
192.168.20.1
192.168.3.1

上記の通りになるので、各オクテットを0詰めして3桁にしてソートする事を考える。

0詰めする前に、まずはドットで区切る方法・・・

  • 第一オクテットは、最初のドットまでを抽出する
  • 第二オクテットだけを抽出するには、第一オクテットとドットを切り取った後、次のドットまでを抽出する
  • 第三オクテットと第四オクテットも同様。

上記の考え方で、ドットで区切った第一~第四オクテットをカラムとして取得するSQL

SELECT
  SUBSTR(
    ip, 0, INSTR(ip, '.')
  ) AS o1,
  SUBSTR(
    SUBSTR(ip, INSTR(ip, '.')+1),
    0,
    INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')
  ) AS o2,
  SUBSTR(
    SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1),
    0,
    INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')
  ) AS o3,
  SUBSTR(
    SUBSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1),
    0
  ) AS o4
FROM
  network;


o1                o2          o3          o4
----------------  ----------  ----------  ----------
192               168         1           1
192               168         10          1
192               168         2           1
192               168         20          1
192               168         3           1
10                1           10          10
10                20          10          10
10                3           10          10
10                3           10          5


各オクテットを0詰めするには、(LDAPが無いので)'00'という文字列を各オクテットに連結し、SUBSTR で下位3桁とドットを抽出する。

SELECT
  SUBSTR('00'||
    SUBSTR(
      ip, 0, INSTR(ip, '.')+1
    ), -4, 4
  )
  ||
  SUBSTR('00'||
    SUBSTR(
      SUBSTR(ip, INSTR(ip, '.')+1),
      0,
      INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1
    ), -4, 4
  )
  ||
  SUBSTR('00'||
    SUBSTR(
      SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1),
      0,
      INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1
    ), -4, 4
  )
  ||
  SUBSTR('00'||
    SUBSTR(
      SUBSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1),
      0+1
    ), -3, 3
  ) AS ip
FROM
  network
ORDER BY
  ip;


ip
----------------
010.001.010.010
010.003.010.005
010.003.010.010
010.020.010.010
192.168.001.001
192.168.002.001
192.168.003.001
192.168.010.001
192.168.020.001


SQL が長いし、可読性が悪い(´・ω・`)

ユーザ定義関数も使わず、もっと簡単にソートできないものか・・・。