"""
once finished import the db using:
mysql your_database < mysql.sql
"""

import urllib
import re
import sys

s1 = "<a onclick=\"javascript:loadClassListFrame\('[^']+'\);\" href=\"([^\"]+)\">([a-z\.]+)</a>"
s2 = "<td class=\"summaryTablePaddingCol\">&nbsp;</td><td class=\"summaryTableSecondCol\"><a href=\"[^\"]+\">([a-zA-Z0-9\-\.]+)</a></td><td class=\"summaryTableLastCol\">"
s3 = "<a class=\"signatureLink\" href=\"[^\"]+\">([^<]+)</a>"
s4 = "<td class=\"summaryTablePaddingCol\">&nbsp;</td><td class=\"summaryTableSecondCol\"><a href=\"([^\"]+)\">([a-zA-Z0-9\-\.]+)</a>"

log = file("mysql.sql", "w")

def init_db():
    log.write("CREATE TABLE packages (id INTEGER PRIMARY KEY, name TEXT);\n")
    log.write("CREATE TABLE classes (id INTEGER PRIMARY KEY, package_id INTEGER, name TEXT);\n")
    log.write("CREATE TABLE methods (id INTEGER PRIMARY KEY, class_id INTEGER, name TEXT);\n")
    

if __name__ == "__main__":

    print "Initializing..."
    init_db()

    packages_uri = "http://livedocs.macromedia.com/flex/2/langref/package-summary.html"
    classes_uri  = "http://livedocs.macromedia.com/flex/2/langref/class-summary.html"

    print "Fetching %s" % packages_uri
    opener = urllib.URLopener()
    f = opener.open(packages_uri)
  
    all_packages = re.findall(s1, f.fp.read(), re.IGNORECASE|re.MULTILINE)
    
    print " "
    for line in all_packages:
        print "INSERT INTO packages (name) VALUES ('%s')" % line[1]
        log.write("INSERT INTO packages (name) VALUES ('%s')\n" % line[1])
    print " "
    
    print "Fetching %s" % classes_uri
    f = opener.open(classes_uri)
    
    all_classes = re.findall(s4, f.fp.read(), re.IGNORECASE|re.MULTILINE)
    
    print " "
    for line in all_classes:
        print "INSERT INTO classes (package_id, name) VALUES ((SELECT id FROM packages WHERE name = '%s'), '%s');" % (".".join(line[0].split("/")[:-1]), line[1])
        log.write("INSERT INTO classes (package_id, name) VALUES ((SELECT id FROM packages WHERE name = '%s'), '%s');\n" % (".".join(line[0].split("/")[:-1]), line[1]))
    print " "    
    
    for line in all_classes:
        href = "http://livedocs.macromedia.com/flex/2/langref/%s" % line[0]
        
        print " "
        print "Fetching %s" % href
        
        class_name = line[1]
        f = opener.open(href)
        
        all_methods = re.findall(s3, f.fp.read(), re.I|re.M);
        for line in all_methods:
            print "INSERT INTO methods (class_id, name) VALUES ((SELECT id FROM classes WHERE name = '%s'), '%s');" % (class_name, line)
            log.write("INSERT INTO methods (class_id, name) VALUES ((SELECT id FROM classes WHERE name = '%s'), '%s');\n" % (class_name, line))
        print " "
        break

    log.write("""CREATE OR REPLACE VIEW view_pc (name) AS SELECT CONCAT(t1.name, '.', t2.name) as name FROM packages as t1, classes as t2 WHERE t1.id = t2.package_id;\n""")
    log.write("""CREATE OR REPLACE VIEW view_cm (name) AS SELECT CONCAT(t1.name, '.', t2.name) as name FROM classes as t1, methods as t2 WHERE t1.id = t2.class_id;\n""")
    log.write("""CREATE OR REPLACE VIEW view_pcm (name) AS SELECT CONCAT(t1.name, '.', t2.name, '.', t3.name) as name FROM packages as t1, classes as t2, methods as t3 WHERE t1.id = t2.package_id and t2.id = t3.class_id;\n""")
    log.close()