-
Notifications
You must be signed in to change notification settings - Fork 0
/
writerSql.py
91 lines (73 loc) · 3.42 KB
/
writerSql.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
__author__ = 'frasiek'
from iwriter import iwriter
class writerSql(iwriter):
def __init__(self):
self.queries = []
self.mismatchFieldInstances = []
def missingTable(self, message, missing_table, missing_fields):
pkFields = []
query = "CREATE TABLE `"+missing_table+"` ("
for missing_field in missing_fields:
if "PRI" in missing_fields[missing_field].info['COLUMN_KEY']:
pkFields.append("`"+missing_fields[missing_field].info['COLUMN_NAME']+"`")
nullV = " NOT NULL "
if missing_fields[missing_field].info['IS_NULLABLE'].lower() == 'yes':
nullV = " NULL "
defaultV = ""
if missing_fields[missing_field].info['COLUMN_DEFAULT'] is not None:
defaultV = " DEFAULT '"+missing_fields[missing_field].info['COLUMN_DEFAULT']+"' "
query += "`" + missing_fields[missing_field].info['COLUMN_NAME'] + "` "
query += missing_fields[missing_field].info['COLUMN_TYPE'] + " "
query += nullV + defaultV + " "+missing_field.info['EXTRA'] + ", "
query = query[0:-2]
if len(pkFields) > 0:
query += "PRIMARY KEY ("+(", ".join(pkFields))+")"
query += ")"
self.queries.append(query)
def reduntantTable(self, message, redundant_table):
self.queries.append("DROP TABLE `"+redundant_table+"`")
def missingField(self, message, table, missing_field, tableInfo, descHasPk):
nullV = " NOT NULL "
if missing_field.info['IS_NULLABLE'].lower() == 'yes':
nullV = " NULL "
defaultV = ""
if missing_field.info['COLUMN_DEFAULT'] is not None:
defaultV = " DEFAULT '"+missing_field.info['COLUMN_DEFAULT']+"' "
dropPk = ''
if descHasPk:
dropPk = " , DROP PRIMARY KEY "
self.queries.append("ALTER TABLE `"+table+"` ADD COLUMN `" +
missing_field.info['COLUMN_NAME']+"` " +
missing_field.info['COLUMN_TYPE']+" " +
nullV +
defaultV+" "+missing_field.info['EXTRA'] +
dropPk + tableInfo.getPkSql(", ")
)
def redundantField(self, message, table, redundant_field):
self.queries.append("ALTER TABLE `"+table+"` DROP COLUMN `"+redundant_field+"`")
def mismatchField(self, message, table, source_field, tableInfo, descHasPk):
if table+"_"+source_field.info['COLUMN_NAME'] in self.mismatchFieldInstances:
return
self.mismatchFieldInstances.append(table+"_"+source_field.info['COLUMN_NAME'])
nullV = " NOT NULL "
if source_field.info['IS_NULLABLE'].lower() == 'yes':
nullV = " NULL "
defaultV = ""
if source_field.info['COLUMN_DEFAULT'] is not None:
defaultV = " DEFAULT '"+source_field.info['COLUMN_DEFAULT']+"' "
dropPk = ''
if descHasPk:
dropPk = " , DROP PRIMARY KEY "
self.queries.append("ALTER TABLE `"+table+"` CHANGE COLUMN "+
"`"+source_field.info['COLUMN_NAME']+"` "+
"`"+source_field.info['COLUMN_NAME']+"` "+
source_field.info['COLUMN_TYPE']+" "+
nullV+
defaultV+" "+source_field.info['EXTRA']+
dropPk + tableInfo.getPkSql(", ")
)
def getText(self):
return str(self.queries)
def runSql(self, connection):
for query in self.queries:
connection.execute(query)