複雑なわりには、使いどころが微妙(´・ω・`)
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 が長いし、可読性が悪い(´・ω・`)
ユーザ定義関数も使わず、もっと簡単にソートできないものか・・・。