def refresh(self):
self.tree.clear()
for table in self.table_manager.table_names or []:
try:
rset = self.editor.executeManagementQuery(
self.show_query % {
'table': table.replace("`", "``"),
'schema': self.schema.replace("`", "``")
}, 0)
except grt.DBError as err:
log_warning("Error querying index info for %s.%s: %s\n" %
(self.schema, table, err[0]))
continue
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
node.set_icon_path(0, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
field = field_obj['field']
except:
field = field_obj
if ctype == mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(
i,
rset.intFieldValueByName(field) or 0)
else:
if type(field) is int:
node.set_string(
i,
rset.stringFieldValue(field) or ""
if format_func is None else format_func(
rset.stringFieldValue(field)))
else:
node.set_string(
i,
rset.stringFieldValueByName(field) or ""
if format_func is None else format_func(
rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
self.refresh_row_count()
def query_server_info(self):
self.server_variables = {}
result = self.exec_query("SHOW VARIABLES")
if not result:
# Didn't get the server variables. Assuming the server is stopped
self.last_known_server_running_status = ("stopped", time.time())
return
while result and result.nextRow():
self.server_variables[result.stringByName(
"Variable_name")] = result.stringByName("Value")
self.status_variables_time = time.time()
self.status_variables = {}
result = self.exec_query("SHOW GLOBAL STATUS")
while result and result.nextRow():
self.status_variables[result.stringByName(
"Variable_name")] = result.stringByName("Value")
# check version
if self.server_variables:
self.raw_version = self.server_variables["version"]
self.target_version = Version.fromstr(self.raw_version)
if self.server_profile.server_version != self.raw_version: # Update profile version with live data from server
log_debug(
'%s.connect_sql(): The server version stored in the server instance profile was "%s". '
'Changed it to the version reported by the server: "%s"\n'
% (self.__class__.__name__,
self.server_profile.server_version, self.raw_version))
self.server_profile.server_version = self.raw_version
if self.target_version and self.target_version.is_supported_mysql_version_at_least(
5, 1, 5):
# The command to retrieve plugins was 'SHOW PLUGIN' for v. [5.1.5, 5.1.9)
# and was changed to 'SHOW PLUGINS' from MySQL Server v. 5.1.9 onwards:
plugin_var = 'PLUGINS' if self.target_version.is_supported_mysql_version_at_least(
5, 1, 9) else 'PLUGIN'
result = self.exec_query('SHOW %s' % plugin_var)
# check whether Windows authentication plugin is available
while result and result.nextRow():
name = result.stringByName("Name")
status = result.stringByName("Status")
plugin_type = result.stringByName("Type")
if status == "ACTIVE":
self.server_active_plugins.add((name, plugin_type))
if "offline_mode" in self.server_variables and self.server_variables[
"offline_mode"] == "ON":
#We're in offline mode, need to change server status
self.last_known_server_running_status = ("offline", time.time())
else:
self.last_known_server_running_status = ("running", time.time())
def get_query(self):
cols = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
try:
cols.append("`%s`" % field_obj['field'])
except:
cols.append("`%s`" % field_obj)
return self.show_query % {
'schema': self.schema,
'columns': ", ".join(cols)
}
def get_profiles_for(system, version):
path = mforms.App.get().get_resource_path("mysql.profiles")
if not path:
path = mforms.App.get().get_resource_path("")
if not path:
log_error("Could not find mysql.profiles dir\n")
return []
path += "/mysql.profiles"
version = Version.fromstr(version or "5.6")
files = [f for f in os.listdir(path) if f.endswith(".xml")]
profiles = []
matched_profiles = []
for f in files:
data = grt.unserialize(os.path.join(path, f))
if data and data.has_key("sys.system") and data["sys.system"] == system:
profiles.append(data)
profile_version = Version.fromstr(data.get("serverVersion"))
if version.majorNumber == profile_version.majorNumber or version.minorNumber == profile_version.minorNumber:
matched_profiles.append(data)
if matched_profiles:
return matched_profiles
return profiles
def get_profiles_for(system, version):
path = mforms.App.get().get_resource_path("mysql.profiles")
if not path:
path = mforms.App.get().get_resource_path("")
if not path:
log_error("Could not find mysql.profiles dir\n")
return []
path += "/mysql.profiles"
version = Version.fromstr(version or "5.6")
files = [f for f in os.listdir(path) if f.endswith(".xml")]
profiles = []
matched_profiles = []
for f in files:
data = grt.unserialize(os.path.join(path, f))
if data and "sys.system" in data and data["sys.system"] == system:
profiles.append(data)
profile_version = Version.fromstr(data.get("serverVersion"))
if version.majorNumber == profile_version.majorNumber or version.minorNumber == profile_version.minorNumber:
matched_profiles.append(data)
if matched_profiles:
return matched_profiles
return profiles
def get_query(self):
if len(
self.columns
) == 0: # Probably user doesn't have privileges to list the privilege tables.
return []
output = []
fields = []
fields_where = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
field = None
try:
field = field_obj['field']
except:
field = field_obj
if field == "scope":
continue
if field not in ['Db']:
fields.append(field)
if field not in ['User', 'Host', 'Db']:
fields_where.append("%s = 'Y'" % field.replace(" ", "_"))
output.append(
"SELECT '<global>' as Db,%(sel_fields)s FROM mysql.user WHERE %(where_fields)s"
% {
'sel_fields': ",".join(fields),
'where_fields': " OR ".join(fields_where)
})
output.append(
"SELECT Db,%(sel_fields)s FROM mysql.db WHERE '%(schema)s' like db"
% {
'sel_fields': ",".join(fields),
'schema': self.schema
})
return output
def __init__(self, owner):
WizardPage.__init__(self,
owner,
"Configure Import Settings",
wide=True)
self.last_analyze_status = False
self.input_file_type = 'csv'
self.active_module = self.main.formats[0] # csv
self.encoding_list = {
'cp1250 (windows-1250)': 'cp1250',
'latin2 (iso8859-2)': 'iso8859_2',
'latin1 (iso8859-1)': 'latin_1',
'utf-8': 'utf-8',
'utf-16': 'utf-16'
}
self.dest_cols = []
self.column_mapping = []
self.ds_show_count = 0
self.df_show_count = 0
self.opts_mapping = {}
self.is_server_5_7 = Version.fromgrt(
self.main.editor.serverVersion
).is_supported_mysql_version_at_least(Version.fromstr("5.7.5"))
def __init__(self, owner):
WizardPage.__init__(self, owner, "Configure Import Settings", wide=True)
self.last_analyze_status = False
self.input_file_type = 'csv'
self.active_module = self.main.formats[0] # csv
self.encoding_list = {'cp1250 (windows-1250)':'cp1250',
'latin2 (iso8859-2)':'iso8859_2',
'latin1 (iso8859-1)':'latin_1',
'utf-8':'utf-8',
'utf-16':'utf-16'}
self.dest_cols = []
self.column_mapping = []
self.ds_show_count = 0
self.df_show_count = 0
self.opts_mapping = {}
self.is_server_5_7 = Version.fromgrt(self.main.editor.serverVersion).is_supported_mysql_version_at_least(Version.fromstr("5.7.5"))
def __init__(self, editor, schema):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.schema = schema
self.editor = editor
self.target_version = Version.fromgrt(editor.serverVersion)
self.main = mforms.newBox(False)
self.add(self.main, True, True)
self.error_heading = mforms.newLabel("")
self.error_heading.set_style(mforms.BoldStyle)
self.error_body = mforms.newLabel("")
self.error_box = mforms.newBox(False)
self.error_box.set_spacing(8)
self.error_box.set_padding(8)
self.error_box.add(self.error_heading, True, False)
self.error_box.add(self.error_body, True, False)
self.add(self.error_box, True, False)
self.error_box.show(False)
self.main.set_padding(8)
self.main.set_spacing(8)
self.tree = mforms.newTreeNodeView(mforms.TreeFlatList
| mforms.TreeAltRowColors
| mforms.TreeShowColumnLines)
self.tree.set_selection_mode(mforms.TreeSelectMultiple)
#Check if there is method to load the columns, if not, skip.
if hasattr(self, "preload_columns") and callable(
getattr(self, "preload_columns")):
self.preload_columns()
for field, type, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
self.tree.add_column(type, caption, width, False)
self.tree.end_columns()
self.tree.set_allow_sorting(True)
self.main.add(self.tree, True, True)
self.menu = mforms.newContextMenu()
self.menu.add_will_show_callback(self.menu_will_show)
self.tree.add_activated_callback(self.on_activate)
self.tree.set_context_menu(self.menu)
self.icon_path = mforms.App.get().get_resource_path(self.klass +
".16x16.png")
self.bad_icon_path = mforms.App.get().get_resource_path(
self.bad_icon_path)
self.row_count = mforms.newLabel("")
self.row_count.set_text("")
self.refresh_btn = mforms.newButton()
self.refresh_btn.set_text("Refresh")
self.refresh_btn.add_clicked_callback(self.refresh)
self.bbox = mforms.newBox(True)
self.bbox.set_spacing(8)
self.main.add(self.bbox, False, True)
self.bbox.add(self.row_count, False, True)
self.bbox.add_end(self.refresh_btn, False, True)
for caption, callback_name in self.actions:
if not caption:
self.bbox.add(mforms.newLabel(" "), False, True)
continue
btn = mforms.newButton()
btn.set_text(caption)
btn.add_clicked_callback(getattr(self, callback_name))
self.bbox.add(btn, False, True)
def __init__(self, conn):
mforms.Form.__init__(self, None)
self._conn = conn
self.versionstr = conn.parameterValues.get("serverVersion", None)
try:
self.version = Version.fromstr(self.versionstr)
except:
self.version = None
self.set_title("Password Expired")
vbox = mforms.newBox(False)
vbox.set_padding(20)
vbox.set_spacing(18)
user = conn.parameterValues["userName"]
l = newLabel(
"Password for MySQL account '%s'@%s expired.\nPlease pick a new password:"******"Mysql@", "")))
l.set_style(mforms.BoldStyle)
vbox.add(l, False, True)
box = mforms.newTable()
box.set_padding(1)
box.set_row_count(3)
box.set_column_count(2)
box.set_column_spacing(7)
box.set_row_spacing(8)
hbox = mforms.newBox(True)
hbox.set_spacing(12)
icon = mforms.newImageBox()
icon.set_image(mforms.App.get().get_resource_path("wb_lock.png"))
hbox.add(icon, False, True)
hbox.add(box, True, True)
vbox.add(hbox, False, True)
self.old_password = mforms.newTextEntry(mforms.PasswordEntry)
self.old_password.set_name("Old Password")
box.add(newLabel("Old Password:"******"New Password")
box.add(newLabel("New Password:"******"Confirm Password")
box.add(newLabel("Confirm:", True), 0, 1, 2, 3,
mforms.HFillFlag | mforms.VFillFlag)
box.add(self.confirm, 1, 2, 2, 3,
mforms.HFillFlag | mforms.HExpandFlag)
self.legacy = mforms.newCheckBox()
self.legacy.set_text("This server version is < 5.7")
if self.version:
self.legacy.set_active(
not self.version.is_supported_mysql_version_at_least(5, 7))
self.legacy.show(False)
else:
self.legacy.show(True)
vbox.add(self.legacy, mforms.HFillFlag | mforms.VFillFlag)
bbox = newBox(True)
bbox.set_spacing(8)
self.ok = newButton()
self.ok.set_text("OK")
self.cancel = newButton()
self.cancel.set_text("Cancel")
mforms.Utilities.add_end_ok_cancel_buttons(bbox, self.ok, self.cancel)
vbox.add_end(bbox, False, True)
self.set_content(vbox)
self.set_size(500, 300)
self.center()
class ObjectManager(mforms.Box):
filter = None
icon_column = None
bad_icon_path = "task_error.png"
node_name = None
actions = []
def __init__(self, editor, schema):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.schema = schema
self.editor = editor
self.target_version = Version.fromgrt(editor.serverVersion)
self.main = mforms.newBox(False)
self.add(self.main, True, True)
self.error_heading = mforms.newLabel("")
self.error_heading.set_style(mforms.BoldStyle)
self.error_body = mforms.newLabel("")
self.error_box = mforms.newBox(False)
self.error_box.set_spacing(8)
self.error_box.set_padding(8)
self.error_box.add(self.error_heading, True, False)
self.error_box.add(self.error_body, True, False)
self.add(self.error_box, True, False)
self.error_box.show(False)
self.main.set_padding(8)
self.main.set_spacing(8)
self.tree = mforms.newTreeNodeView(mforms.TreeFlatList
| mforms.TreeAltRowColors
| mforms.TreeShowColumnLines)
self.tree.set_selection_mode(mforms.TreeSelectMultiple)
#Check if there is method to load the columns, if not, skip.
if hasattr(self, "preload_columns") and callable(
getattr(self, "preload_columns")):
self.preload_columns()
for field, type, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
self.tree.add_column(type, caption, width, False)
self.tree.end_columns()
self.tree.set_allow_sorting(True)
self.main.add(self.tree, True, True)
self.menu = mforms.newContextMenu()
self.menu.add_will_show_callback(self.menu_will_show)
self.tree.add_activated_callback(self.on_activate)
self.tree.set_context_menu(self.menu)
self.icon_path = mforms.App.get().get_resource_path(self.klass +
".16x16.png")
self.bad_icon_path = mforms.App.get().get_resource_path(
self.bad_icon_path)
self.row_count = mforms.newLabel("")
self.row_count.set_text("")
self.refresh_btn = mforms.newButton()
self.refresh_btn.set_text("Refresh")
self.refresh_btn.add_clicked_callback(self.refresh)
self.bbox = mforms.newBox(True)
self.bbox.set_spacing(8)
self.main.add(self.bbox, False, True)
self.bbox.add(self.row_count, False, True)
self.bbox.add_end(self.refresh_btn, False, True)
for caption, callback_name in self.actions:
if not caption:
self.bbox.add(mforms.newLabel(" "), False, True)
continue
btn = mforms.newButton()
btn.set_text(caption)
btn.add_clicked_callback(getattr(self, callback_name))
self.bbox.add(btn, False, True)
def show_error(self, title, msg):
self.main.show(False)
self.error_box.show(True)
self.error_heading.set_text(title)
self.error_body.set_text(msg)
def refresh_row_count(self):
self.row_count.set_text("Count: %d" % self.tree.count())
def on_activate(self, node, col):
from sqlide_tableman_ext import show_table_inspector
if show_table_inspector is not None:
if self.klass == 'db.Table':
show_table_inspector(
self.editor,
[(self.schema, node.get_string(
self.name_column).encode("utf8"))])
elif self.klass == 'db.Index' and hasattr(self,
'parent_name_column'):
show_table_inspector(
self.editor,
[(self.schema, node.get_string(
self.parent_name_column).encode("utf8"))], "indexes")
def menu_will_show(self, item):
# item is the parent node which will be None when the
# context menu has just being opened.
# So when the call is done for a sub-menu, no reset is needed.
if item is None:
self.menu.remove_all()
if item is None:
parent_nodes = {}
selection = grt.List()
pobj = None
for node in self.tree.get_selection():
name = node.get_string(self.name_column)
obj = grt.classes.db_query_LiveDBObject()
obj.name = name
obj.schemaName = self.schema
obj.type = self.klass
if hasattr(self, 'parent_name_column'):
parent_name = node.get_string(self.parent_name_column)
if parent_nodes.has_key(parent_name):
obj.owner = pobj
else:
pobj = grt.classes.db_query_LiveDBObject()
obj.owner = pobj
pobj.type = 'db.Table'
pobj.name = parent_name
pobj.schemaName = self.schema
parent_nodes[parent_name] = pobj
selection.append(obj)
if not selection and self.node_name:
obj = grt.classes.db_query_LiveDBObject()
obj.schemaName = self.schema
obj.type = self.node_name
selection.append(obj)
sobj = grt.classes.db_query_LiveDBObject()
sobj.schemaName = self.schema
sobj.name = self.schema
sobj.type = "db.Schema"
obj.owner = sobj
separator = mforms.newMenuItem("", mforms.SeparatorMenuItem)
separator.set_name("bottom_plugins_separator")
self.menu.add_item(separator)
self.menu.add_item_with_title("Refresh", self.refresh,
"refresh")
args = grt.Dict()
args["selection"] = selection
args["menu"] = mforms.togrt(self.menu, "ContextMenu")
args['schema_inspector'] = True
NotificationCenter().send("GRNLiveDBObjectMenuWillShow",
self.editor, args)
def get_query(self):
cols = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
try:
cols.append("`%s`" % field_obj['field'])
except:
cols.append("`%s`" % field_obj)
return self.show_query % {
'schema': self.schema,
'columns': ", ".join(cols)
}
def preload_data(self, query):
try:
rset = self.editor.executeManagementQuery(query, 0)
except grt.DBError, e:
if e.args[1] == 1044 or e.args[1] == 1142:
mforms.Utilities.show_error(
"Access Error",
"The current user does not have enough privileges to execute %s.\n\n%s"
% (query, e.args[0]), "OK", "", "")
else:
mforms.Utilities.show_error(
"MySQL Error",
"An error occurred retrieving information about the schema.\nQuery: %s\nError: %s"
% (query, e.args[0]), "OK", "", "")
return
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
if self.is_row_corrupted(rset):
print rset.stringFieldValueByName("Name"), "IS CORRUPTED"
node.set_icon_path(self.icon_column, self.bad_icon_path)
elif self.icon_column is not None:
node.set_icon_path(self.icon_column, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
field = field_obj['field']
except:
field = field_obj
if ctype is mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(i,
rset.intFieldValueByName(field) or 0)
elif ctype is mforms.LongIntegerColumnType:
if type(field) is int:
node.set_long(
i, long(rset.stringFieldValue(field) or 0))
else:
node.set_long(
i,
long(rset.stringFieldValueByName(field) or 0))
else:
if type(field) is int:
node.set_string(
i,
rset.stringFieldValue(field) or ""
if format_func is None else format_func(
rset.stringFieldValue(field)))
else:
node.set_string(
i,
rset.stringFieldValueByName(field) or ""
if format_func is None else format_func(
rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
def start_import(self):
if not self._last_analyze:
return False
if self._new_table:
if not self.prepare_new_table():
return False
if self._truncate_table:
self.update_progress(0.0, "Truncate table")
self._editor.executeManagementCommand(
"TRUNCATE TABLE %s" % self._table_w_prefix, 1)
result = True
with open(self._filepath, 'rb') as csvfile:
self.update_progress(0.0, "Prepare Import")
dest_col_order = list(
set([i['dest_col'] for i in self._mapping if i['active']]))
query = """PREPARE stmt FROM 'INSERT INTO %s (%s) VALUES(%s)'""" % (
self._table_w_prefix, ",".join([
"`%s`" % col for col in dest_col_order
]), ",".join(["?" for i in dest_col_order]))
col_order = dict([(i['dest_col'], i['col_no'])
for i in self._mapping if i['active']])
col_type = dict([(i['dest_col'], i['type']) for i in self._mapping
if i['active']])
is_server_5_7 = self._targetVersion.is_supported_mysql_version_at_least(
Version.fromstr("5.7.5"))
self._editor.executeManagementCommand(query, 1)
try:
is_header = self.has_header
reader = UniReader(csvfile,
self.dialect,
encoding=self._encoding)
self._max_rows = os.path.getsize(self._filepath)
self.update_progress(0.0, "Begin Import")
for row in reader:
if self._thread_event and self._thread_event.is_set():
self._editor.executeManagementCommand(
"DEALLOCATE PREPARE stmt", 1)
log_debug2("Worker thread was stopped by user")
self.update_progress(
round(self._current_row / self._max_rows, 2),
"Import stopped by user request")
return False
self._current_row = float(csvfile.tell())
if is_header:
is_header = False
continue
for i, col in enumerate(col_order):
if col_order[col] >= len(row):
log_error("Can't find col: %s in row: %s" %
(col_order[col], row))
result = False
break
val = row[col_order[col]]
col_name = col_order[col]
if col_type[col] == "geometry":
if is_server_5_7:
val = """ST_GeomFromText("%s")""" % row[
col_name]
else:
val = """GeomFromText("%s")""" % row[col_name]
self._editor.executeManagementCommand(
"""SET @a%d = %s """ % (i, val), 0)
else:
if col_type[col] == 'double':
val = row[col_name].replace(
self._decimal_separator, '.')
elif col_type[col] == 'datetime':
val = datetime.datetime.strptime(
row[col_name], self._date_format).strftime(
"%Y-%m-%d %H:%M:%S")
if hasattr(val, "replace"):
val = val.replace("\\",
"\\\\").replace("'", "\\'")
if self.options['nullwordaskeyword'][
'value'] == "y" and val.upper() == "NULL":
self._editor.executeManagementCommand(
"""SET @a%d = NULL """ % (i), 0)
else:
self._editor.executeManagementCommand(
"""SET @a%d = '%s' """ % (i, val), 0)
else:
try:
self._editor.executeManagementCommand(
"EXECUTE stmt USING %s" % ", ".join([
'@a%d' % i
for i, col in enumerate(col_order)
]), 0)
self.item_count = self.item_count + 1
self.update_progress(
round(self._current_row / self._max_rows, 2),
"Data import")
except Exception, e:
log_error("Row import failed with error: %s" % e)
self.update_progress(
round(self._current_row / self._max_rows, 2),
"Row import failed with error: %s" % e)
result = False
self.update_progress(1.0, "Import finished")
except Exception, e:
import traceback
log_debug3("Import failed traceback: %s" %
traceback.format_exc())
log_error("Import failed: %s" % e)
def __init__(self, editor, schema):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.schema = schema
self.editor = editor
self.target_version = Version.fromgrt(editor.serverVersion)
self.main = mforms.newBox(False)
self.add(self.main, True, True)
self.error_heading = mforms.newLabel("")
self.error_heading.set_style(mforms.BoldStyle)
self.error_body = mforms.newLabel("")
self.error_box = mforms.newBox(False)
self.error_box.set_spacing(8)
self.error_box.set_padding(8)
self.error_box.add(self.error_heading, True, False)
self.error_box.add(self.error_body, True, False)
self.add(self.error_box, True, False)
self.error_box.show(False)
self.main.set_padding(8)
self.main.set_spacing(8)
self.tree = mforms.newTreeView(mforms.TreeFlatList|mforms.TreeAltRowColors|mforms.TreeShowColumnLines)
self.tree.set_selection_mode(mforms.TreeSelectMultiple)
#Check if there is method to load the columns, if not, skip.
if hasattr(self, "preload_columns") and callable(getattr(self, "preload_columns")):
self.preload_columns()
for field, type, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
self.tree.add_column(type, caption, width, False)
self.tree.end_columns()
self.tree.set_allow_sorting(True)
self.main.add(self.tree, True, True)
self.menu = mforms.newContextMenu()
self.menu.add_will_show_callback(self.menu_will_show)
self.tree.add_activated_callback(self.on_activate)
self.tree.set_context_menu(self.menu)
self.icon_path = mforms.App.get().get_resource_path(self.klass+".16x16.png")
self.bad_icon_path = mforms.App.get().get_resource_path(self.bad_icon_path)
self.row_count = mforms.newLabel("")
self.row_count.set_text("");
self.refresh_btn = mforms.newButton()
self.refresh_btn.set_text("Refresh")
self.refresh_btn.add_clicked_callback(self.refresh)
self.bbox = mforms.newBox(True)
self.bbox.set_spacing(8)
self.main.add(self.bbox, False, True)
self.bbox.add(self.row_count, False, True)
self.bbox.add_end(self.refresh_btn, False, True)
for caption, callback_name in self.actions:
if not caption:
self.bbox.add(mforms.newLabel(" "), False, True)
continue
btn = mforms.newButton()
btn.set_text(caption)
btn.add_clicked_callback(getattr(self, callback_name))
self.bbox.add(btn, False, True)
def start_import(self):
if not self._last_analyze:
return False
if self._new_table:
if not self.prepare_new_table():
return False
if self._truncate_table:
self.update_progress(0.0, "Truncate table")
self._editor.executeManagementCommand("TRUNCATE TABLE %s" % self._table_w_prefix, 1)
result = True
with open(self._filepath, 'rb') as csvfile:
self.update_progress(0.0, "Prepare Import")
dest_col_order = list(set([i['dest_col'] for i in self._mapping if i['active']]))
query = """PREPARE stmt FROM 'INSERT INTO %s (%s) VALUES(%s)'""" % (self._table_w_prefix, ",".join(["`%s`" % col for col in dest_col_order]), ",".join(["?" for i in dest_col_order]))
col_order = dict([(i['dest_col'], i['col_no']) for i in self._mapping if i['active']])
col_type = dict([(i['dest_col'], i['type']) for i in self._mapping if i['active']])
is_server_5_7 = self._targetVersion.is_supported_mysql_version_at_least(Version.fromstr("5.7.5"))
self._editor.executeManagementCommand(query, 1)
try:
is_header = self.has_header
reader = UniReader(csvfile, self.dialect, encoding=self._encoding)
self._max_rows = os.path.getsize(self._filepath)
self.update_progress(0.0, "Begin Import")
for row in reader:
if self._thread_event and self._thread_event.is_set():
self._editor.executeManagementCommand("DEALLOCATE PREPARE stmt", 1)
log_debug2("Worker thread was stopped by user")
self.update_progress(round(self._current_row / self._max_rows, 2), "Import stopped by user request")
return False
self._current_row = float(csvfile.tell())
if is_header:
is_header = False
continue
for i, col in enumerate(col_order):
if col_order[col] >= len(row):
log_error("Can't find col: %s in row: %s" % (col_order[col], row))
result = False
break
val = row[col_order[col]]
col_name = col_order[col]
if col_type[col] == "geometry":
if is_server_5_7:
val = """ST_GeomFromText("%s")""" % row[col_name]
else:
val = """GeomFromText("%s")""" % row[col_name]
self._editor.executeManagementCommand("""SET @a%d = %s """ % (i, val), 0)
else:
if col_type[col] == 'double':
val = row[col_name].replace(self._decimal_separator, '.')
elif col_type[col] == 'datetime':
val = datetime.datetime.strptime(row[col_name], self._date_format).strftime("%Y-%m-%d %H:%M:%S")
if hasattr(val, "replace"):
val = val.replace("\\", "\\\\").replace("'", "\\'")
self._editor.executeManagementCommand("""SET @a%d = '%s' """ % (i, val), 0)
else:
try:
self._editor.executeManagementCommand("EXECUTE stmt USING %s" % ", ".join(['@a%d' % i for i, col in enumerate(col_order)]), 0)
self.item_count = self.item_count + 1
self.update_progress(round(self._current_row / self._max_rows, 2), "Data import")
except Exception, e:
log_error("Row import failed with error: %s" % e)
self.update_progress(round(self._current_row / self._max_rows, 2), "Row import failed with error: %s" % e)
result = False
self.update_progress(1.0, "Import finished")
except Exception, e:
import traceback
log_debug3("Import failed traceback: %s" % traceback.format_exc())
log_error("Import failed: %s" % e)
def preload_data(self, query):
try:
rset = self.editor.executeManagementQuery(query, 0)
except grt.DBError as e:
if e.args[1] == 1044 or e.args[1] == 1142:
mforms.Utilities.show_error(
"Access Error",
"The current user does not have enough privileges to execute %s.\n\n%s"
% (query, e.args[0]), "OK", "", "")
else:
mforms.Utilities.show_error(
"MySQL Error",
"An error occurred retrieving information about the schema.\nQuery: %s\nError: %s"
% (query, e.args[0]), "OK", "", "")
return
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
if self.is_row_corrupted(rset):
node.set_icon_path(self.icon_column, self.bad_icon_path)
elif self.icon_column is not None:
node.set_icon_path(self.icon_column, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
except:
pass
try:
field = field_obj['field']
except:
if self.target_version.is_supported_mysql_version_at_least(
8, 0, 0) and type(field) is str:
field = field_obj.upper()
else:
field = field_obj
if ctype is mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(i,
rset.intFieldValueByName(field) or 0)
elif ctype is mforms.LongIntegerColumnType:
if type(field) is int:
node.set_long(
i, int(rset.stringFieldValue(field) or 0))
else:
node.set_long(
i,
int(rset.stringFieldValueByName(field) or 0))
else:
if type(field) is int:
node.set_string(
i,
rset.stringFieldValue(field) or ""
if format_func is None else format_func(
rset.stringFieldValue(field)))
else:
node.set_string(
i,
rset.stringFieldValueByName(field) or ""
if format_func is None else format_func(
rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
def get_query(self):
if len(self.columns) == 0: # Probably user doesn't have privileges to list the privilege tables.
return []
output = []
fields = []
fields_where = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
field = None
try:
field = field_obj['field']
except:
field = field_obj
if field == "scope":
continue
if field not in ['Db']:
fields.append(field)
if field not in ['User','Host','Db']:
fields_where.append("%s = 'Y'" % field.replace(" ","_"))
output.append("SELECT '<global>' as Db,%(sel_fields)s FROM mysql.user WHERE %(where_fields)s" % {'sel_fields' : ",".join(fields), 'where_fields' : " OR ".join(fields_where)})
output.append("SELECT Db,%(sel_fields)s FROM mysql.db WHERE '%(schema)s' like db" % {'sel_fields' : ",".join(fields), 'schema' : self.schema})
return output
def refresh(self):
self.tree.clear()
for table in self.table_manager.table_names or []:
try:
rset = self.editor.executeManagementQuery(self.show_query % {'table' : table.replace("`", "``"), 'schema' : self.schema.replace("`", "``")}, 0)
except grt.DBError, err:
log_warning("Error querying index info for %s.%s: %s\n" % (self.schema, table, err[0]))
continue
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
node.set_icon_path(0, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
field = field_obj['field']
except:
field = field_obj
if ctype == mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(i, rset.intFieldValueByName(field) or 0)
else:
if type(field) is int:
node.set_string(i, rset.stringFieldValue(field) or "" if format_func is None else format_func(rset.stringFieldValue(field)))
else:
node.set_string(i, rset.stringFieldValueByName(field) or "" if format_func is None else format_func(rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
def get_query(self):
cols = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
try:
cols.append("`%s`" % field_obj['field'])
except:
cols.append("`%s`" % field_obj)
return self.show_query % {'schema' : self.schema, 'columns' : ", ".join(cols)}
