
import httplib
import logging
import re
import sqlite3
import string
import sys, os

def create_tables(db):
    logging.info("Creating database tables.")
    cursor = db.cursor()
    cursor.execute("CREATE TABLE Classes (Class INTEGER(2) PRIMARY KEY, Name VARCHAR(32));")
    cursor.execute("CREATE TABLE Vendors (Vendor INTEGER(2) PRIMARY KEY, Name VARCHAR(64));")
    cursor.execute("CREATE TABLE Products (Vendor INTEGER(2), Product INTEGER(2), Name VARCHAR(64));")
    cursor.execute("CREATE TABLE Criterias (Class INTEGER(2), Vendor INTEGER(2), Product INTEGER(2), Revision INTEGER(2), Action INTEGER(1));")
    db.commit()
    
def insert_classes(db):

	classes = (
            ( 0x01, "Audio"),
            ( 0x02, "Communication and CDC Control"),
            ( 0x03, "Human Interface Device (HID)"),
            ( 0x05, "Physical Interface Device (PID)"),
            ( 0x06, "Still Imaging"),
            ( 0x07, "Printer"),
            ( 0x08, "Mass Storage"),
            ( 0x09, "USB Hub"),
            ( 0x0a, "CDC-Data"),
            ( 0x0b, "Smart Card"),
            ( 0x0d, "Content Security"),
            ( 0x0e, "Video"),
            ( 0x0f, "Personal Healthcare"),
            ( 0xdc, "Diagnostic Device"),
            ( 0xe0, "Wireless Controller"),
            ( 0xef, "Miscellaneous"),
            ( 0xfe, "Application Specific"),
            ( 0xff, "Vendor Specific"))

	cursor = db.cursor()
	for usbclass in classes:
		cursor.execute("INSERT INTO Classes VALUES(%d, '%s');" % (usbclass[0], usbclass[1]))
	db.commit()

def insert_criteria(db):
    logging.info("Inserting known criterias.")
    cursor = db.cursor()

    cursor.execute("INSERT INTO Criterias VALUES(-1,60186,10000,256,1);")
    cursor.execute("INSERT INTO Criterias VALUES(-1,1118,245,-1,1);")
    cursor.execute("INSERT INTO Criterias VALUES(-1,1133,2245,-1,1);")
    cursor.execute("INSERT INTO Criterias VALUES(-1,1133,2242,5,1);")
    cursor.execute("INSERT INTO Criterias VALUES(8,-1,-1,-1,1);")
    cursor.execute("INSERT INTO Criterias VALUES(7,-1,-1,-1,1);")
    cursor.execute("INSERT INTO Criterias VALUES(-1,-1,-1,-1,0);")
    
    db.commit()

def fetch_usb_list():
    data = None
    conn = httplib.HTTPConnection("www.linux-usb.org")
    conn.request("GET", "/usb.ids")
    resp = conn.getresponse()
    logging.debug("HTTP response status: %d" % resp.status)
    if resp.status == 200:
        data = resp.read()
    conn.close()
    return data

def import_data(db, data):
    logging.info("Importing USB vendors and products to database.")
    vendor_re = re.compile('^([\da-f]{4})\s+(.+)')
    product_re = re.compile('^\t([\da-f]{4})\s+(.+)')
    current_vendor = None
    cursor = db.cursor()
    for line in data.split("\n"):
        vendor = vendor_re.match(line)
        if vendor:
            current_vendor = int(vendor.group(1), 16)
            vendor_name = string.replace(vendor.group(2), "'", "''")
            cursor.execute("INSERT INTO Vendors VALUES(%d, '%s');" % (current_vendor, vendor_name))
        else:
            product = product_re.match(line)
            if product:
                product_name = string.replace(product.group(2), "'", "''")
                cursor.execute("INSERT INTO Products VALUES(%d, %d, '%s');" % (current_vendor, int(product.group(1), 16), product_name))
    db.commit()

def run(output_dir):
    logging.info("Creating editor database started.")
    out_db_file = os.path.join(output_dir, "UsbFilterEditor.db")
    if os.path.exists(out_db_file):
        os.unlink(out_db_file)
    logging.info("Creating db file '%s'"%(out_db_file))
    db = sqlite3.connect(out_db_file)
    create_tables(db)
    insert_classes(db)
    insert_criteria(db)
    data = fetch_usb_list()
    if data:
        import_data(db, data)

if __name__ == "__main__":
    logging.root.level = logging.DEBUG
    output_dir = ""
    if len(sys.argv) >1:
        output_dir = sys.argv[1]
    run(output_dir)
