QQwry到Sqlite3(续)

下面是用于查询转成sqlite3的qqwry的python代码

---

'''
Created on 2010-5-4

@author: Ben

@mail: ben.pang.china@gmail.com
'''

import os, sys, re
import sqlite3
from converter import b2I, dbname

def int2Ip(i):
    """IP字符串转换成IP对象"""
    i = int(i)
    return IP( "{}.{}.{}.{}".format( i >> 24, i  >> 16 & 0xFF, i  >> 8 & 0xFF,  i & 0xFF ) )

def ip2Int(ip):
    """IP字符串转换成Integer"""
    if ipm.match(ip):
        return b2I( reversed(list( map( int, ip.split(".") ) )) )

# local variables
ipm = re.compile( "^((2[0-4]d|25[0-5]|[01]?dd?).){3}(2[0-4]d|25[0-5]|[01]?dd?)$" )
conn = None

def init():
    global conn
    sqlite3.register_converter("ip", int2Ip)
    conn = sqlite3.connect(dbname, detect_types = sqlite3.PARSE_COLNAMES);
    
class IP:
    def __init__(self, ip):
        """IP类,存放IP地址,并验证IP字符串是否有效"""
        if not ipm.match(ip):
            raise ValueError( "invalid ip value:" + ip )
        self.ip = ip
    
    def __str__(self):
        return self.ip

if __name__ == '__main__':
    if os.path.exists(dbname) and len(sys.argv) == 2 and ipm.match(sys.argv[1]):
        init()
        cur = conn.cursor()
        cur.execute("""
        SELECT start_ip as "s [ip]",
        end_ip as "e [ip]",
        country.name,
        area.name
        FROM ipaddr,country,area WHERE (? BETWEEN start_ip AND end_ip) AND cid = country.id AND aid = area.id;
        """, (ip2Int(sys.argv[1]), ) )
        
        for r in cur:
            print("""
            start ip: {} ~ {}
            {}
            """.format(*r))
        
        conn.close()
    else:
        print("not found ipdb for" + dbname, file = sys.stderr )

---

 

很不幸,经过尝试转成sqlite3的QQwry的压缩率并没有我想的那么高,整个库文件有31M,是QQwry原格式的3倍.

 

下面是SQL脚本

---

CREATE TABLE IF NOT EXISTS country (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS area (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS ipaddr (
    start_ip INTEGER PRIMARK KEY,
    end_ip INTEGER NOT NULL UNIQUE,
    cid INTEGER NOT NULL,
    aid INTEGER DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS ipIdx ON ipaddr (start_ip, end_ip)

---

 

因为在SQLITE3里把IP转成integer了,所以可以看到上面的代码中注册了一个convertor用于转换,如果觉得不方便还可以注册funtion viewIP,然后create view,使用注册的函数显示IP,这会增加开销,但可以满足使用习惯

像这样

 

create view ipview as SELECT viewIP(start_ip),... FROM ipaddr....

OK.