def browse(self):
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_directory(
os.path.dirname(self.exportfile_path.get_string_value()))
extensions = []
for module in self.main.formats:
extensions.append(module.get_file_extension()[0])
filechooser.set_extensions("|".join(extensions),
self.active_module.get_file_extension()[1],
False)
if filechooser.run_modal():
file_path = filechooser.get_path()
self.exportfile_path.set_value(file_path)
self.destination_file_checked = True
global last_location
last_location = file_path
self.confirm_file_overwrite = False
self.get_module()
for opt in self.radio_opts:
if self.active_module and opt[
'name'] == self.active_module.name:
opt['radio'].set_active(True)
self.load_module_options()
def csvDataDictionary(catalog):
csvOut = ""
filechooser = FileChooser(mforms.OpenDirectory)
filechooser.set_extensions("CSV File (*.csv)", "csv")
if filechooser.run_modal():
csvOut = filechooser.get_path()
print "CSV File: %s" % (csvOut)
if len(csvOut) <= 1:
return 1
# iterate through columns from schema
schema = catalog.schemata[0]
for table in schema.tables:
csvFile = open("%s/%s.csv" % (csvOut, table.name), "w")
print >> csvFile, "Name,Data Type,Nullable,PK,FK,Reference,Default,Comment"
fks = table.foreignKeys
for column in table.columns:
pk = ('No', 'Yes')[bool(table.isPrimaryKeyColumn(column))]
is_fk = bool(table.isForeignKeyColumn(column))
fk = ('No', 'Yes')[is_fk]
ref = find_referenced_table(fks, column) if is_fk else ''
nn = ('No', 'Yes')[bool(column.isNotNull)]
print >> csvFile, "%s,\"%s\",%s,%s,%s,%s,\"%s\",%s" % (
column.name, column.formattedType, nn, pk, fk, ref,
column.defaultValue, column.comment)
Utilities.show_message(
"CSVs generated", "CSV data dictionaries from current model generated",
"OK", "", "")
return 0
def generateCSVDataDictionary(catalog):
#choose a file name for the data dictionary
fileName = ""
fileChooser = FileChooser(mforms.SaveFile)
fileChooser.set_extensions("CSV File (*.csv)|*.csv", "csv")
if fileChooser.run_modal():
fileName = fileChooser.get_path()
#static headers
headers = [
'Schema', 'Table', 'Name', 'Data Type', 'Nullable', 'PK', 'FK',
'Default', 'Description', 'Sample Data'
]
#create and open the csv file
with open(fileName, 'wb') as csvfile:
#create a csv writer
csvWriter = csv.writer(csvfile)
#write the headers into the csv file
csvWriter.writerow(headers)
#start of schema iteration
for schema in catalog.schemata:
#start of tables iteration
for table in schema.tables:
#start of columns iteration
for column in table.columns:
isPrimaryKey = ('No', 'Yes')[bool(
table.isPrimaryKeyColumn(column))]
isForeignKey = ('No', 'Yes')[bool(
table.isForeignKeyColumn(column))]
isNotNullable = ('No', 'Yes')[bool(column.isNotNull)]
#write the values in a row in the csv file
csvWriter.writerow([
schema.name, table.name, column.name,
column.formattedType, isNotNullable, isPrimaryKey,
isForeignKey, column.defaultValue
])
#end of columns iteration
#end of tables iteration
#end of schema iteration
#show message for a successful generation of data dictionary
Utilities.show_message("Data dictionary generated",
"CSV format data dictionary generated", "OK", "",
"")
return 0
def importfile_browse(self):
filechooser = FileChooser(mforms.OpenFile)
filechooser.set_directory(os.path.dirname(self.importfile_path.get_string_value()))
extensions = []
for module in self.main.formats:
extensions.append(module.get_file_extension()[0])
filechooser.set_extensions("|".join(extensions), self.main.formats[0].get_file_extension()[1], False)
if filechooser.run_modal():
file_path = filechooser.get_path()
self.importfile_path.set_value(file_path)
global last_location
last_location = file_path
def htmlDataDictionary(catalog):
htmlOut = ""
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_extensions("HTML File (*.html)|*.html", "html")
if filechooser.run_modal():
htmlOut = filechooser.get_path()
print "HTML File: %s" % (htmlOut)
if len(htmlOut) <= 1:
return 1
# iterate through columns from schema
schema = catalog.schemata[0]
htmlFile = open(htmlOut, "w")
print >> htmlFile, "<html><head>"
print >> htmlFile, "<title>Schema Report for database: %s</title>" % (
schema.name)
print >> htmlFile, """<style>
td,th {
text-align:left;
vertical-align:middle;
border: 1px solid;
}
table {
border: none;
border-collapse: collapse;
}
td {
display: block;
float: left;
padding-left: 5px;
padding-right: 5px;
}
</style>
</head>
<body>"""
print >> htmlFile, "<h1>Schema Report for database: %s</h1>" % (
schema.name)
masters = [
"badge_categories", "camp_categories", "camp_procedures", "camps",
"organizations", "permissions", "programs", "provinces", "regions",
"religions", "roles", "schools", "users", "years"
]
print >> htmlFile, "<h1>Master</h1><br>"
draw(htmlFile, schema, masters, True)
print >> htmlFile, "<h1>Transaction</h1><br>"
draw(htmlFile, schema, masters, False)
print >> htmlFile, "</body></html>"
Utilities.show_message("Report generated",
"HTML Report format from current model generated",
"OK", "", "")
return 0
def htmlDataDictionary(catalog):
# Put plugin contents here
mdOut = ""
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_extensions("Markdown File (*.md)|*.md", "md")
if filechooser.run_modal():
mdOut = filechooser.get_path()
print "Markdown File: %s" % (mdOut)
if len(mdOut) <= 1:
return 1
# iterate through columns from schema
schema = catalog.schemata[0]
mdFile = open(mdOut, "w")
print >> mdFile, "# Diccionario de datos"
print >> mdFile, ""
tables = schema.tables
tables = sorted(tables, key=orderTables)
for table in tables:
print >> mdFile, "- [%s](#markdown-header-%s)" % (table.name,
table.name)
print >> mdFile, ""
for table in tables:
print >> mdFile, "## %s" % (table.name)
print >> mdFile, "%s" % (table.comment)
print >> mdFile, ""
print >> mdFile, "|Nombre|Tipo de dato|Nulo|PK|FK|Default|Comentario|"
print >> mdFile, "|------|------------|----|--|--|-------|----------|"
for column in table.columns:
pk = ('No', 'Yes')[bool(table.isPrimaryKeyColumn(column))]
fk = ('No', 'Yes')[bool(table.isForeignKeyColumn(column))]
nn = ('No', 'Yes')[bool(column.isNotNull)]
print >> mdFile, "|%s|%s|%s|%s|%s|%s|%s|" % (
column.name, column.formattedType, nn, pk, fk,
column.defaultValue, column.comment.replace('\n', ''))
print >> mdFile, ""
print >> mdFile, "[Regresar al listado](#markdown-header-diccionario-de-datos)"
print >> mdFile, ""
print >> mdFile, ""
Utilities.show_message("Diccionario de datos creado",
"El archivo markdonw fue generado exitosamente",
"Aceptar", "", "")
return 0
def shapefile_browse(self):
filechooser = FileChooser(mforms.OpenFile)
filechooser.set_directory(os.path.dirname(self.shapefile_path.get_string_value()))
filechooser.set_extensions("Spatial Shape File (*.shp)|*.shp", "shp");
if filechooser.run_modal():
filepath = filechooser.get_path()
filename = os.path.splitext(os.path.basename(filepath))[0]
self.shapefile_path.set_value(filepath)
if os.path.isfile("".join([os.path.dirname(filepath),"/",filename,".dbf"])):
self.dbf_icon.set_image("task_checked%s.png" % os_icon_suffix)
else:
self.dbf_icon.set_image("task_warning%s.png" % os_icon_suffix)
if os.path.isfile("".join([os.path.dirname(filepath),"/",filename,".prj"])):
self.proj_icon.set_image("task_checked%s.png" % os_icon_suffix)
else:
self.proj_icon.set_image("task_warning%s.png" % os_icon_suffix)
self.warning_srs.set_text("Projection file not found, assuming WGS84 Spatial Reference System")
def shapefile_browse(self):
filechooser = FileChooser(mforms.OpenFile)
filechooser.set_directory(os.path.dirname(self.shapefile_path.get_string_value()))
filechooser.set_extensions("Spatial Shape File (*.shp)|*.shp", "shp");
if filechooser.run_modal():
filepath = filechooser.get_path()
filename = os.path.splitext(os.path.basename(filepath))[0]
self.shapefile_path.set_value(filepath)
if os.path.isfile("".join([os.path.dirname(filepath),"/",filename,".dbf"])):
self.dbf_icon.set_image("task_checked%s.png" % os_icon_suffix)
else:
self.dbf_icon.set_image("task_warning%s.png" % os_icon_suffix)
if os.path.isfile("".join([os.path.dirname(filepath),"/",filename,".prj"])):
self.proj_icon.set_image("task_checked%s.png" % os_icon_suffix)
else:
self.proj_icon.set_image("task_warning%s.png" % os_icon_suffix)
self.warning_srs.set_text("Projection file not found, assuming WGS84 Spatial Reference System")
def browse(self):
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_directory(os.path.dirname(self.exportfile_path.get_string_value()))
extensions = []
for module in self.main.formats:
extensions.append(module.get_file_extension()[0])
filechooser.set_extensions("|".join(extensions), self.active_module.get_file_extension()[1], False)
if filechooser.run_modal():
file_path = filechooser.get_path()
self.exportfile_path.set_value(file_path)
self.destination_file_checked = True
global last_location
last_location = file_path
self.confirm_file_overwrite = False
self.get_module()
for opt in self.radio_opts:
if self.active_module and opt['name'] == self.active_module.name:
opt['radio'].set_active(True)
self.load_module_options()
def htmlDataDictionary(catalog):
# Put plugin contents here
htmlOut = ""
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_extensions("HTML File (*.html)|*.html", "html")
if filechooser.run_modal():
htmlOut = filechooser.get_path()
print "HTML File: %s" % (htmlOut)
if len(htmlOut) <= 1:
return 1
# iterate through columns from schema
schema = catalog.schemata[0]
htmlFile = open(htmlOut, "w")
print >> htmlFile, "<html><head>"
print >> htmlFile, "<title>Schema Report for database: %s</title>" % (
schema.name)
print >> htmlFile, """<style>
td,th {
text-align:left;
vertical-align:middle;
}
table {
border-collapse: collapse;
border: 1px solid;
}
caption, th, td {
padding: .2em .8em;
border: 1px solid #000000;
}
caption {
background: #D3D3D3;
font-weight: bold;
font-size: 1.1em;
}
th {
font-weight: bold;
background: #000000;
color: white;
}
td {
background: #FFFFFF;
}
</style>
</head>
<body>"""
print >> htmlFile, "<h1>Schema Report for database: %s</h1>" % (
schema.name)
print >> htmlFile, "<a id=\"home\">Table List </a><br /><ul>"
for table in schema.tables:
print >> htmlFile, "<li><a href=\"#%s\">%s </a></li>" % (table.name,
table.name)
print >> htmlFile, "</ul>"
for table in schema.tables:
print >> htmlFile, "<a id=\"%s\"></a><table style=\"width:100%%\"><caption>Table: %s </caption>" % (
table.name, table.name)
print >> htmlFile, "<tr><td>Table Comments</td><td colspan=\"6\">%s</td></tr>" % (
table.comment)
print >> htmlFile, """<tr><td colspan=\"7\">Columns</td></tr>
<tr>
<th>Name</th>
<th>Data Type</th>
<th>Nullable</th>
<th>PK</th>
<th>FK</th>
<th>Default</th>
<th>Comment</th>
</tr>"""
for column in table.columns:
pk = ('No', 'Yes')[bool(table.isPrimaryKeyColumn(column))]
fk = ('No', 'Yes')[bool(table.isForeignKeyColumn(column))]
nn = ('No', 'Yes')[bool(column.isNotNull)]
print >> htmlFile, "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>" % (
column.name, column.formattedType, nn, pk, fk,
column.defaultValue, column.comment)
print >> htmlFile, "</table><a href=\"#home\">Table List </a></br>"
print >> htmlFile, "</body></html>"
Utilities.show_message("Report generated",
"HTML Report format from current model generated",
"OK", "", "")
return 0
def mysqltoirebird(currentcatalog):
firebirdScript = ""
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_extensions("SQL File (*.sql)|*.sql", "sql");
if filechooser.run_modal():
firebirdScript = filechooser.get_path()
print "HTML File: %s" % (firebirdScript)
if len(firebirdScript) <= 1:
return 1
# iterate through columns from schema
thegenerators = ""
theforeignkey = ""
theprimaarykey = ""
firebirdScript = open(firebirdScript, "w")
print >> firebirdScript, "/* Firebird Script from Mysql Model */"
print >> firebirdScript, "\n"
for schema in grt.root.wb.doc.physicalModels[0].catalog.schemata:
print >> firebirdScript, "CREATE DATABASE '%s.fdb' page_size 8192; " % (schema.name)
print >> firebirdScript, "\n"
print >> firebirdScript, "\n"
for table in schema.tables:
print >> firebirdScript, "CREATE TABLE %s (" % (table.name)
numberofcolumns = len(table.columns)
columncounter = 0
for column in table.columns:
notnull = ('', 'NOT NULL')[bool(column.isNotNull)]
mysqlType = column.formattedType
firebirdType = column.formattedType
if mysqlType == "INT(11)":
firebirdType = "INTEGER"
elif mysqlType == "TINYINT":
firebirdType = "SMALLINT"
print >> firebirdScript, " %s %s %s," % (column.name, firebirdType, notnull)
# if column is defined as AUTO_INCREMENT in mysql then add it to generators
if column.autoIncrement == 1:
thegenerators = thegenerators + "CREATE GENERTATOR GEN_" + table.name + ";" + "\r\n"
# define primary key constraint
theprimarykey = ""
numberofcolumnsinpk = len(table.primaryKey.columns) - 1
primarykeycounter = 0
for primarykeycolumn in table.primaryKey.columns:
theprimarykey = theprimarykey + primarykeycolumn.referencedColumn.name
if primarykeycounter < numberofcolumnsinpk:
theprimarykey = theprimarykey + ","
primarykeycounter = primarykeycounter + 1
if len(table.primaryKey.columns) > 0:
theprimarykey = "CONSTRAINT %s_PK PRIMARY KEY (%s) " % (table.name, theprimarykey)
if len(table.foreignKeys) > 0:
theprimarykey = theprimarykey + ", "
print >> firebirdScript, theprimarykey
# define foreign key constraints
theforeignkey = ""
numberofforeignkeys = len(table.foreignKeys) - 1
foreignkeycounter = 0
for foreignkey in table.foreignKeys:
theforeignkey = theforeignkey + " CONSTRAINT %s_%s_FK FOREIGN KEY (" % (
table.name, foreignkey.referencedTable.name)
for colTable in foreignkey.columns:
theforeignkey = theforeignkey + colTable.name
theforeignkey = theforeignkey + ") REFERENCES " + foreignkey.referencedTable.name + " ( "
for referencedColumn in foreignkey.referencedColumns:
theforeignkey = theforeignkey + referencedColumn.name
theforeignkey = theforeignkey + ")"
if (foreignkeycounter < numberofforeignkeys):
theforeignkey = theforeignkey + ", "
theforeignkey = theforeignkey + "\r\n"
foreignkeycounter = foreignkeycounter + 1
print >> firebirdScript, theforeignkey
print >> firebirdScript, ");"
print >> firebirdScript, "\n"
print >> firebirdScript, "\n"
print >> firebirdScript, "--- GENERATORS"
print >> firebirdScript, thegenerators
print >> firebirdScript, ""
Utilities.show_message("Script Migrated", "Migration from Mysql to Firebird Finished", "OK", "", "")
return 0
def exportSQLServer(catalog):
haveFKeys = 0
version = grt.root.wb.info.version # V.getGlobal("/wb/info/version")
versionNumber = "%d.%d.%d" % (version.majorNumber, version.minorNumber,
version.releaseNumber)
print versionNumber
if validateForSQLServerExport(catalog) != 0:
return 1
#-- we don't have requestFileSave in <= 5.1
# path = Workbench:requestFileSave("Save as", "SQL Files (*.sql)|*.sql")
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_extensions("SQL Files (*.sql)|*.sql", "sql")
filechooser.set_title("Save Microsoft SQL Server Create File")
# fileChooser.set_directory(self.logfile_path)
if filechooser.run_modal():
path = filechooser.get_path()
else:
YesNoBox("Exiting", "Cancel Chosen")
return 0
with open(path, "w+") as file:
#file = io.open(path, "w+")
if (file == None):
YesNoBox("Error", "Cannot open file %s" % (path))
return 1
#end
#-- if (not path:find("\.sql$")) then
#-- truncate db file
#-- file:close()
#-- file = io.popen("SQLServer3 -batch -bail " .. path, "w")
#-- end
info = grt.root.wb.doc.info
file.write(
infoFormat(
"Creator", "MySQL Workbench %s /ExportSQLServer plugin %s" %
(versionNumber, ModuleInfo.version)))
file.write(infoFormat("Author", info.author))
file.write(infoFormat("Caption", info.caption))
file.write(infoFormat("Project", info.project))
file.write(infoFormat("Changed", info.dateChanged))
file.write(infoFormat("Created", info.dateCreated))
file.write(infoFormat("Description", info.description))
#-- loop over all catalogs in schema, find main schema
#-- main schema is first nonempty schema or nonempty schema named "main"
iMain = -1
i = 0
for schema in catalog.schemata:
if (len(schema.tables) > 0):
if (iMain < 0):
iMain = i
#end
if (schema.name == "dbo"): # dbo is SQL Server's main schema.
iMain = i
break
#end
#end
i += 1
#end
if (iMain > -1):
if (exportSchema(file, catalog.schemata[iMain], True) != 0):
print "Error writing schema %s\n" % (
catalog.schemata[iMain].name)
return 1
#end
#end
i = 0
for schema in catalog.schemata:
uniqueId = 1
if (i != iMain):
if (exportSchema(file, schema, False) != 0):
print "Error writing schema %s\n" % (
catalog.schemata[i].name)
return 1
#end
#end
i += 1
#end
print "Export to %s finished.\n" % (path)
return 0
def exportSQLServer(catalog):
haveFKeys = 0
version = grt.root.wb.info.version # V.getGlobal("/wb/info/version")
versionNumber = "%d.%d.%d" % (version.majorNumber, version.minorNumber, version.releaseNumber)
print versionNumber
if validateForSQLServerExport(catalog) != 0:
return 1
#-- we don't have requestFileSave in <= 5.1
# path = Workbench:requestFileSave("Save as", "SQL Files (*.sql)|*.sql")
filechooser = FileChooser(mforms.SaveFile)
filechooser.set_extensions("SQL Files (*.sql)|*.sql", "sql")
filechooser.set_title("Save Microsoft SQL Server Create File")
# fileChooser.set_directory(self.logfile_path)
if filechooser.run_modal():
path = filechooser.get_path()
else:
YesNoBox("Exiting", "Cancel Chosen")
return 0
with open(path, "w+") as file:
#file = io.open(path, "w+")
if (file == None):
YesNoBox("Error", "Cannot open file %s" % (path))
return 1
#end
#-- if (not path:find("\.sql$")) then
#-- truncate db file
#-- file:close()
#-- file = io.popen("SQLServer3 -batch -bail " .. path, "w")
#-- end
info = grt.root.wb.doc.info
file.write(infoFormat("Creator", "MySQL Workbench %s /ExportSQLServer plugin %s" % (versionNumber, ModuleInfo.version)))
file.write(infoFormat("Author", info.author))
file.write(infoFormat("Caption", info.caption))
file.write(infoFormat("Project", info.project))
file.write(infoFormat("Changed", info.dateChanged))
file.write(infoFormat("Created", info.dateCreated))
file.write(infoFormat("Description", info.description))
#-- loop over all catalogs in schema, find main schema
#-- main schema is first nonempty schema or nonempty schema named "main"
iMain = -1
i = 0
for schema in catalog.schemata:
if (len(schema.tables) > 0):
if (iMain < 0):
iMain = i
#end
if (schema.name == "dbo"): # dbo is SQL Server's main schema.
iMain = i
break
#end
#end
i += 1
#end
if (iMain > -1):
if (exportSchema(file, catalog.schemata[iMain], True) != 0):
print "Error writing schema %s\n" % (catalog.schemata[iMain].name)
return 1
#end
#end
i = 0
for schema in catalog.schemata:
uniqueId = 1
if (i != iMain):
if (exportSchema(file, schema, False) != 0):
print "Error writing schema %s\n" % (catalog.schemata[i].name)
return 1
#end
#end
i += 1
#end
print "Export to %s finished.\n" % (path)
return 0
