-
Notifications
You must be signed in to change notification settings - Fork 1
/
erwin_to_nz_sql.py
111 lines (90 loc) · 3.97 KB
/
erwin_to_nz_sql.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
import time
import re
import os
import sys
##import yaml
def islike(table, exclude):
for i in exclude:
if table.startswith(i):
return True
return False
if len(sys.argv) < 2:
print 'Usage: python erwin_to_nz_sql.py path_to_file [table_prefix]'
else:
## definition = yaml.load(open(r"erwin_to_nz_sql.yml"))
## definition_settings = definition['settings']
## table_prefix = definition_settings.get('table_prefix', '')
## table_postfix = definition_settings.get('table_postfix', '')
## all_not_null = definition_settings.get('all_not_null', False)
## distribute_on_pk = definition_settings.get('distribute_on_pk', False)
## max_string_size = definition_settings.get('max_string_size', 4000)
## include = definition_settings.get('include', [])
## exclude = definition_settings.get('exclude', [])
sql_file_path = sys.argv[1]
if len(sys.argv) > 2:
table_prefix = sys.argv[2]
else:
table_prefix = ''
def changetype(t):
m = {}
t = t.replace("DECIMAL(", "NUMERIC(")
t = t.replace("VARCHAR(", "NVARCHAR(")
t = t.replace("VARCHAR2(", "NVARCHAR(")
t = re.sub("TIMESTAMP\(\d+\)", "TIMESTAMP", t)
t = re.sub("CLOB\(\d+\)", "NVARCHAR(4000)", t)
t = re.sub("BLOB\(\d+\)", "NVARCHAR(4000)", t)
t = t.replace("NUMERIC(1)", "BYTEINT")
t = t.replace("NUMERIC(2)", "SMALLINT")
t = t.replace("NUMERIC(3)", "SMALLINT")
t = t.replace("NUMERIC(4)", "SMALLINT")
t = t.replace("NUMERIC(5)", "SMALLINT")
t = t.replace("NUMERIC(6)", "INTEGER")
t = t.replace("NUMERIC(7)", "INTEGER")
t = t.replace("NUMERIC(9)", "INTEGER")
t = t.replace("NUMERIC(10)", "INTEGER")
t = t.replace("NUMERIC(12)", "BIGINT")
t = t.replace("NUMERIC(13)", "BIGINT")
t = t.replace("NUMERIC(15)", "BIGINT")
t = t.replace("NUMERIC(19)", "BIGINT")
return t
if os.path.exists(sql_file_path):
erwin_forward = open(sql_file_path).read()
create_table = re.findall("CREATE\sTABLE.*?\;", erwin_forward, re.MULTILINE + re.DOTALL)
alter_table = re.findall("ALTER\sTABLE.*?;", erwin_forward, re.MULTILINE + re.DOTALL)
print "--", "*" * 40
print "-- %s" % "ERWIN TO NETEZZA SQL @ %s" % time.ctime()
print "--", "*" * 40
print "-- %s" % "TABLES"
print "--", "*" * 40
nima_pk = []
for table in create_table:
table_name = re.split("[\s\n]", table)[2]
print "\n"
print "-" * 20
print "-- %s%s" % (table_prefix, table_name)
print "-" * 20
print "-- DROP TABLE %s%s;" % (table_prefix, table_name)
table2 = changetype(table)
table2 = table2.replace("CREATE TABLE ", "CREATE TABLE %s" % table_prefix)
try:
primary_key = re.findall("PRIMARY\sKEY\s\(.+\)", table2)[0].split("(")[1][:-1]
distribute_on = "(%s)" % primary_key
except:
distribute_on = "RANDOM"
nima_pk.append(table_name)
print re.sub("\)\n\;", ") DISTRIBUTE ON %s;" % distribute_on, table2)
print "\n" * 3
print "--", "*" * 40
print "-- %s" % "FOREIN KEYS"
print "--", "*" * 40
for alter in alter_table:
if "FOREIGN KEY" in alter:
alter = alter.replace("FOREIGN KEY", "CONSTRAINT")
alter = alter.splitlines()
table_name = alter[0].split(" ")[2]
alter[0] = alter[0].replace("ALTER TABLE ", "ALTER TABLE %s" % table_prefix)
alter[1] = alter[1].split(" ")
alter[1].insert(3, "FOREIGN KEY")
alter[1] = " ".join(alter[1])
alter[1] = alter[1].replace("REFERENCES ", "REFERENCES %s" % table_prefix)
print "\n".join(alter)