#!/usr/bin/python3

import ibm_db
import getopt
import sys
import os
from toposort import toposort_flatten

db = None
host = "localhost"
port = "50000"
user = None
pwd = None
outfile = None
targetdb = None

try:
    opts, args = getopt.getopt(sys.argv[1:], "h:d:P:u:p:o:t:")
except getopt.GetoptError:
    sys.exit(-1)
for o, a in opts:
    if o == "-d":
        db = a
    if o == "-h":
        host = a
    if o == "-P":
        port = a
    if o == "-u":
        user = a
    if o == "-p":
        pwd = a
    if o == "-t":
        targetdb = a


if db is None or user is None or pwd is None or targetdb is None:
    print("Usage: DBMove.py [-h <host> -P <port>] -d <db> -u <user> -p <pwd> -t <target>")
    sys.exit(1)

db = db.upper()
targetdb = targetdb.upper()

cfg = (db, host, port, user, pwd)
conn = ibm_db.connect("DATABASE=%s; HOSTNAME=%s; PORT=%s; PROTOCOL=TCPIP; UID=%s; PWD=%s" % cfg, "", "")

get_db_type = "values nya.get_db_type()"

find_edges = """
SELECT rtrim(t.tabschema) || '.' || rtrim(t.tabname)
     , coalesce(rtrim(r.reftabschema) || '.' || rtrim(r.reftabname), 'dummy')
FROM syscat.tables t
LEFT JOIN syscat.references r
    ON (t.tabschema, t.tabname) = (r.tabschema, r.tabname)
WHERE t.tabschema not like 'SYS%' 
  AND t.type = 'T'
  AND rtrim(t.tabschema) not like 'NYA_%' 
  AND t.tabschema <> 'TMP'
ORDER BY 1
"""
  
identity_skip = """
select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.columns
where identity = 'Y' and generated = 'D'
"""

stmt = ibm_db.prepare(conn, get_db_type)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
db_type = tpl[0]

edges = dict()
stmt = ibm_db.prepare(conn, find_edges)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
    n1, n2 = tpl
    try:
        edges[n1].add(n2)
    except KeyError:
        edges[n1] = set()
        edges[n1].add(n2)

    tpl = ibm_db.fetch_tuple(stmt)
sorted_nodes = list(toposort_flatten(edges))

# print(sorted_nodes)


identity_skip_arr = []
edges = dict()
stmt = ibm_db.prepare(conn, identity_skip)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
    identity_skip_arr.append(tpl[0])
    tpl = ibm_db.fetch_tuple(stmt)

# print(identity_skip)
os.makedirs(db, exist_ok=True)
export_file = open("%s/export.sql" % db, "w")
load_file = open("%s/load.sql" % db, "w")
export_file.write("connect to %s;\n" % db)
load_file.write("connect to %s;\n" % targetdb)
if db_type == "N":
    load_file.write("""set integrity for nya.person off;\n""")
    load_file.write("""alter table nya.person 
                            alter column EMAIL_UC drop generated 
                            alter column NORMALIZED_FIRSTNAME drop generated 
                            alter column NORMALIZED_LASTNAME drop generated;\n""")
    load_file.write("""set integrity for nya.person immediate checked;\n""")

for t in sorted_nodes:
    if t == "dummy":
        continue
    export_file.write("export to %s.ixf of ixf lobs to . modified by codepage=819 messages export_%s.msg select * from %s;\n" % (t,t,t))
    identityskip = "identityoverride"
    if t in identity_skip_arr:
        identityskip = " "

    load_file.write("load from %s.ixf of ixf lobs from . modified by generatedoverride %s messages load_%s.msg replace into %s;\n" % (t, identityskip, t, t))

if db_type == "N":
    load_file.write("""set integrity for nya.person off;\n""")
    load_file.write("""alter table nya.person 
                            alter column EMAIL_UC set generated always as ( upper(email)) 
                            alter column NORMALIZED_FIRSTNAME set generated always as ( NYA.REMOVE_DIACRITICS( FIRSTNAME ) ) 
                            alter column NORMALIZED_LASTNAME set generated always as ( NYA.REMOVE_DIACRITICS( LASTNAME ) );\n""")
    load_file.write("""set integrity for nya.person immediate checked force generated;\n""")
    load_file.write("""echo set integrity for all tables;\n""")

export_file.write("connect reset;\n")
load_file.write("connect reset;\n")

export_file.close()
load_file.close()



[((44, 7, 44, 104), 'ibm_db.connect', 'ibm_db.connect', ({(44, 22, 44, 95): "'DATABASE=%s; HOSTNAME=%s; PORT=%s; PROTOCOL=TCPIP; UID=%s; PWD=%s' % cfg", (44, 97, 44, 99): '""""""', (44, 101, 44, 103): '""""""'}, {}), "(\n    'DATABASE=%s; HOSTNAME=%s; PORT=%s; PROTOCOL=TCPIP; UID=%s; PWD=%s' %\n    cfg, '', '')", False, 'import ibm_db\n'), ((66, 7, 66, 40), 'ibm_db.prepare', 'ibm_db.prepare', ({(66, 22, 66, 26): 'conn', (66, 28, 66, 39): 'get_db_type'}, {}), '(conn, get_db_type)', False, 'import ibm_db\n'), ((67, 0, 67, 24), 'ibm_db.execute', 'ibm_db.execute', ({(67, 15, 67, 19): 'stmt', (67, 21, 67, 23): '()'}, {}), '(stmt, ())', False, 'import ibm_db\n'), ((68, 6, 68, 30), 'ibm_db.fetch_tuple', 'ibm_db.fetch_tuple', ({(68, 25, 68, 29): 'stmt'}, {}), '(stmt)', False, 'import ibm_db\n'), ((72, 7, 72, 39), 'ibm_db.prepare', 'ibm_db.prepare', ({(72, 22, 72, 26): 'conn', (72, 28, 72, 38): 'find_edges'}, {}), '(conn, find_edges)', False, 'import ibm_db\n'), ((73, 0, 73, 24), 'ibm_db.execute', 'ibm_db.execute', ({(73, 15, 73, 19): 'stmt', (73, 21, 73, 23): '()'}, {}), '(stmt, ())', False, 'import ibm_db\n'), ((74, 6, 74, 30), 'ibm_db.fetch_tuple', 'ibm_db.fetch_tuple', ({(74, 25, 74, 29): 'stmt'}, {}), '(stmt)', False, 'import ibm_db\n'), ((91, 7, 91, 42), 'ibm_db.prepare', 'ibm_db.prepare', ({(91, 22, 91, 26): 'conn', (91, 28, 91, 41): 'identity_skip'}, {}), '(conn, identity_skip)', False, 'import ibm_db\n'), ((92, 0, 92, 24), 'ibm_db.execute', 'ibm_db.execute', ({(92, 15, 92, 19): 'stmt', (92, 21, 92, 23): '()'}, {}), '(stmt, ())', False, 'import ibm_db\n'), ((93, 6, 93, 30), 'ibm_db.fetch_tuple', 'ibm_db.fetch_tuple', ({(93, 25, 93, 29): 'stmt'}, {}), '(stmt)', False, 'import ibm_db\n'), ((99, 0, 99, 30), 'os.makedirs', 'os.makedirs', (), '', False, 'import os\n'), ((18, 17, 18, 62), 'getopt.getopt', 'getopt.getopt', ({(18, 31, 18, 43): 'sys.argv[1:]', (18, 45, 18, 61): '"""h:d:P:u:p:o:t:"""'}, {}), "(sys.argv[1:], 'h:d:P:u:p:o:t:')", False, 'import getopt\n'), ((38, 4, 38, 15), 'sys.exit', 'sys.exit', ({(38, 13, 38, 14): '(1)'}, {}), '(1)', False, 'import sys\n'), ((83, 10, 83, 34), 'ibm_db.fetch_tuple', 'ibm_db.fetch_tuple', ({(83, 29, 83, 33): 'stmt'}, {}), '(stmt)', False, 'import ibm_db\n'), ((84, 20, 84, 43), 'toposort.toposort_flatten', 'toposort_flatten', ({(84, 37, 84, 42): 'edges'}, {}), '(edges)', False, 'from toposort import toposort_flatten\n'), ((96, 10, 96, 34), 'ibm_db.fetch_tuple', 'ibm_db.fetch_tuple', ({(96, 29, 96, 33): 'stmt'}, {}), '(stmt)', False, 'import ibm_db\n'), ((20, 4, 20, 16), 'sys.exit', 'sys.exit', ({(20, 13, 20, 15): '(-1)'}, {}), '(-1)', False, 'import sys\n')]