gateway2pg.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# Copyright (C) 2015 Martin Herweg, Martin Spott    m.herweg@gmx.de
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the
# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
#

import os, sys, io
import json, base64, getopt
import httplib
import zipfile
import icu
import psycopg2
from settings import *

from StringIO import StringIO

helptext = "gateway2pg.py -i <ICAO> \ngateway2pg.py -s <sceneryId>"

def fn_pgexec(cur, sql):
    try:
        cur.execute(sql)
    except psycopg2.Error, e:
        print(e)
#        print(sql)
    return cur

def insert_or_update(cur, icao, linearray):
    cur.execute("SELECT icao FROM apt_dat WHERE icao LIKE '%s';" % icao)
#    print(icao,  cur.rowcount)
    if cur.rowcount == 1:
        sql = cur.mogrify("UPDATE apt_dat SET layout = ARRAY[%s] WHERE icao LIKE '%s';" % (linearray, icao))
#        print("sql: %s" % sql)
        print("UPDATE %s" % icao)
        fn_pgexec(cur, sql)
    else:
        sql = cur.mogrify("INSERT INTO apt_dat(icao, layout) VALUES ('%s',  ARRAY[%s]);" % (icao, linearray))
#        print("sql: %s" % sql)
        print("INSERT %s" % icao)
        fn_pgexec(cur, sql)

def main(argv):

    icao = "KATL"
    sid = ""
    sids = []
    author = ""

    try:
        conn = psycopg2.connect(**db_params)
    except:
        print("Cannot connect to database.", db_params)
        sys.exit()
    cur = conn.cursor()

    try:
        opts, args = getopt.getopt(argv, "hi:s:")
    except getopt.GetoptError:
        print(helptext)
        sys.exit(2)
    for opt, arg in opts:
        if opt == "-h":
            print(helptext)
            sys.exit()
        elif opt == "-i":
            icao = str.upper(arg)
        elif opt == "-s":
            sid = arg
    htconn = httplib.HTTPConnection("gateway.x-plane.com")

    if sid == "":

        htconn.request("GET", "/apiv1/airport/%s" % icao)
        r1 = htconn.getresponse()
        r2 = r1.read()
        result = json.loads(r2)
        sid=result["airport"]["recommendedSceneryId"]

        sceneries =  result["airport"]["scenery"]
        for s in sceneries:
            for k2, v2 in s.items():
                if k2 in ["dateDeclined", "dateAccepted", "DateAccepted", "DateApproved", "DateDeclined", "userId", "type"]:
                    pass
                else:
                    print(k2, v2)
            if s["Status"] == "Approved":
                    sids.append(s["sceneryId"])
            if s["sceneryId"] == sid:
                author = s["userName"]
            print("-----------------")

        print("approved scenery ids for %s: %s" % (icao, sids))
        #print("highest approved id:", max(sids))
        print("recommended SceneryId: %s by author: %s" % (sid, author))

    htconn.request("GET", "/apiv1/scenery/%s" % str(sid))
    r1 = htconn.getresponse()
    r2 = r1.read()
    result = json.loads(r2)

    zip_base64 = result["scenery"]["masterZipBlob"]

    zip_blob = base64.b64decode(zip_base64)
    
    print("writing %s.zip" % icao)
#    file = open("%s.zip" % icao, "wb")
#    file.write(zip_blob)
#    file.close()
    zip_bytearray = io.BytesIO(zip_blob)
    zip_fhandle = zipfile.ZipFile(zip_bytearray)

    print("reading %s.zip" % icao)
#    myZip = zipfile.ZipFile("%s.zip" % icao, "r")
    datstring = zip_fhandle.read("%s.dat" % icao)
    try:
	txtstring = zip_fhandle.read("%s.txt" % icao)
    except:
        print "(2D)"
    else:
        print " 3D :-)"

    dat_handle = StringIO()
    dat_handle.write(datstring)
    dat_handle.seek(0)

    print("writing %s.dat" % icao)
#    myZip.extract("%s.dat" % icao)
#
#    print("deleting %s.zip" % icao)
#    os.remove("%s.zip" % icao)
#    print(datstring)

#################################

#I
#1000 Version - data cycle 2013.10, build 20131335, metadata AptXP1000.
#
#1     1906 1 0 LOWI Innsbruck Kranebitten
#01234567890123456789
#          1    ^

# "2015" file:
#A
#1000 Generated by WorldEditor
#
#1   1470 0 0 0B7 Warren-Sugarbush
#01234567890123456789
#          1  ^

#XP10 custom cenery pack:
#A
#1000 Generated by WorldEditor
#
#1   1906 1 0 LOWI Innsbruck Kranebitten
#01234567890123456789
#          1  ^
#    elev 0 0 ICAO Name

#known bugs:
# LOWI
#invalid byte sequence for encoding "UTF8": 0xf6 0x64 0x20 0x74
# EDRZ
#invalid byte sequence for encoding "UTF8": 0xfc
# fix:
# iconv...

    icao = ""
    counter = 0
#    filename = "apt.dat.lowi-in"
    
    # main loop
    for line in dat_handle:
        line = line.replace("\r\n", os.linesep)
        line = line.strip()
#        print(line)
        # 1 for airports, 16 for seaports, ....
        if line.startswith("1 ") or line.startswith("16 ") or line.startswith("17 "):

            #the previous icao:
            if icao != "":
            #for testing
            #if icao == "LOWI":
                # write previous airport to DB
                print(icao, counter)
                counter = counter +1
                insert_or_update(cur, icao, linearray)
                if (counter%1000 == 0):
                    conn.commit()
                    print("=============COMMIT==============")

            #the next airport:
            apt_header = line.split()
            icao = apt_header[4]
            name = ' '.join(apt_header[5:])
            #print(icao, name)
            linearray = "$$%s$$" % line

        else:
            #read all the lines of that airport
            if icao != "" and line != "" and line != "99":
                linearray += ", $$%s$$" % line

    # last airport in apt.dat:

    encoding = icu.CharsetDetector(linearray).detect().getName()
    new_encoding="utf-8"
    if new_encoding.upper() != encoding.upper():
#        encoding = "windows-1250"
#        encoding = "cp1252"
#        encoding = "utf-8"
#        encoding = "latin1"
        print("   --- Converting from file encoding: %s" % str(encoding))
        linearray = unicode(linearray, encoding).encode(new_encoding).replace("\r\n", os.linesep)

    if icao != "":
#        print(linearray)
        insert_or_update(cur, icao, linearray)
        print(icao, counter)

    conn.commit()
    cur.close()
    conn.close()

if __name__ == "__main__":
   main(sys.argv[1:])

# EOF