'''
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 )

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)

OK.