#!/usr/bin/env python
# -*- coding: utf-8 -*-

# ***************************************************************************
# *   Copyright (C) 2012, Paul Lutus                                        *
# *                                                                         *
# *   This program is free software; you can redistribute it and/or modify  *
# *   it under the terms of the GNU General Public License as published by  *
# *   the Free Software Foundation; either version 2 of the License, or     *
# *   (at your option) any later version.                                   *
# *                                                                         *
# *   This program is distributed in the hope that it will be useful,       *
# *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
# *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
# *   GNU General Public License for more details.                          *
# *                                                                         *
# *   You should have received a copy of the GNU General Public License     *
# *   along with this program; if not, write to the                         *
# *   Free Software Foundation, Inc.,                                       *
# *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
# ***************************************************************************

from __future__ import unicode_literals

import re,sys,os,datetime, time, base64, MySQLdb, warnings, webbrowser, codecs

from optparse import OptionParser

try:
  from gi.repository import GObject, Gtk, Gdk, GdkPixbuf, Pango
except:
  import  GObject, Gtk, Gdk, GdkPixbuf, Pango

# this is required for correct thread behavior

GObject.threads_init()

# embedded icon, created with:
# base64 fn.png > out.b64

class Icon:
  icon = """iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAMAAABEpIrGAAAAM1BMVEU4AAAGCAQfIB4vMC5ERUNu
VjFfYV+QeVaDhIKjo6CMsNu3urmtxOHIzc7H1eXW2tnk5uSKW6MPAAAAAXRSTlMAQObYZgAAAAFi
S0dEAIgFHUgAAAAJcEhZcwAAA3YAAAN2AX3VgswAAAAHdElNRQfcCQoVARNMzeXlAAABKElEQVQ4
y5WTW5KFIAxEzUMMKiT7X+0ELijoramZ/rGKHEl3jMvyL2nTfApQH8wi8aiKUVx3nWUBYg4ppaMp
gosbEaIABT+1QQ4HCA0gRBTvnAaZqlxXACBJ91ffV9PsAF42EEOv1fqeTzVp7j8Ehft6tbw7FkcA
MFxNtPaYAQTCMKX4AHQBiOQ+p7rOgBCx3EWzrMcMCLuNDpznmc0BHIAUGCm2Kdme7QmoBiJK47Rn
wA/Yv9hvgEXG0agDMANWbBwtRs5PoMyQ2W1oGbWn0AEAT1F0OMAlRfYUEwCtd0RcYzfxDbCwblu3
OQN9H2Td2hX5AZRF0EqsXiwuXkBZZ6ei99vP095AW/ok5J/r5aGPzwk99GES7hTOaNIvKbAJZt3A
d/3lh/8BcMwcOeaTsGQAAAAASUVORK5CYII="""

class ExportTable:
  utf8_block = '<?xml version="1.0" encoding="UTF-8"?>'
  head_block = """
    <meta http-equiv="content-type" content="application/xhtml+xml; charset=UTF-8" />
    <style type='text/css'>
    /* for DBClient-generated tables */
     html,body,table,select,p,div,span,td,li {
       font-family: Verdana, Tahoma, Helvetica, Arial;
       font-size: 14px;
     }
     table.dbclient {
       border-collapse:collapse;
       border:1px solid black;
     }
     .dbclient tr td {
        border-left:1px solid #000000;
        padding-right:4px;
        padding-left:4px;
        vertical-align:top;
        %s
     }
     .dbclient tr td.ra { text-align:right }
     .dbclient tr th {
        padding-right:4px;
        padding-left:4px;
        border:1px solid #000000;
        background:#ffffe0;
        font-weight: bold;
        text-align: center;
        %s
     }
     .dbclient tr.row0 { background:#eff1f1; }
     .dbclient tr.row1 { background:#f8f8f8; }
     </style>
    """
    
  def __init__(self,dbclient,dbname,tablename,tabledesc,tabledata,query_select = None):
    self.dbclient = dbclient
    self.dbname = dbname
    self.tablename = tablename
    self.tabledesc = tabledesc
    self.tabledata = tabledata
    self.query_select = query_select
    
  def wrap_tag(self,tag,data,mod = '',lf = False):
    if(len(mod) > 0): mod = ' ' + mod
    output = '<%s%s>%s</%s>' % (tag,mod,data,tag)
    if(lf):
      output = '\n' + output + '\n'
    return output
    
  def beautify_xhtml(self,data, linebreaks = False):
    xml = []
    if(linebreaks):
      # each tag on a separate line
      data = re.sub("<","\n<",data)
      data = re.sub(">",">\n",data)
      data = re.sub("\n+","\n",data)
    tab = 0
    array = data.split("\n")
    for record in array:
      record = record.strip()
      if(len(record) > 0): # no blank lines
        outc = len(re.findall("</|/>",record))
        inc = len(re.findall("<\w",record))
        net = inc - outc
        tab += net if(net < 0) else 0
        xml.append(("  " * tab) + record)
        tab += net if(net > 0) else 0
    if(tab != 0):
      sys.stderr.write("Error: tag mismatch: %d\n" % tab)
    return "\n".join(xml) + '\n'

  def html(self,start_col = 0,wrap = True):
    page = ''
    row = ''
    column_mod = []
    title = ''
    for x,record in enumerate(self.tabledesc):
      if(self.query_select == None or self.query_select(x)):
        title += self.wrap_tag('th',record[0])
        column_mod.append(('class="ra"','')[re.search('text|varchar',record[1]) != None])
    page += self.wrap_tag('tr',title)
    for y,record in enumerate(self.tabledata):
      row = ''
      # must skip included index number
      for x,field in enumerate(record[start_col::]):
        if(field == None):
          field = 'NULL'
        field = re.sub('<','&lt;',field)
        field = re.sub('>','&gt;',field)
        if(re.search('\n|\t',field)):
          field = re.sub('(\t.*?\n)','<li>\\1</li>',field)
          field = re.sub('(?s)(<li>.*</li>)','<ul>\\1</ul>',field)
          field = re.sub('\n','<br/>\n',field)
        # turn URL references into hyperlinks, but only
        # those not already in a tag
        field = re.sub(r'(?<!href=")(https?://\S+)',r'<a href="\1">\1</a>',field)
        row += self.wrap_tag('td',field,column_mod[x])
      mod = 'class="row%d"' % (y % 2)
      page += self.wrap_tag('tr',row,mod)
    # page += self.wrap_tag('tr',title)
    page = self.wrap_tag('table',page,'class="dbclient"')
    page = self.wrap_tag('body',page)
    nowrap = ('white-space:nowrap;','')[wrap]
    head = self.head_block % (nowrap,nowrap)
    head += self.wrap_tag('title','%s.%s' % (self.dbname, self.tablename))
    page = self.wrap_tag('head',head) + page
    page = self.utf8_block + self.wrap_tag('html',page)
    page = self.beautify_xhtml(page,True)
    return page
    
  def csv(self,cstart = 0):
    result = ''
    row = []
    for x,record in enumerate(self.tabledesc):
      if(self.query_select == None or self.query_select(x)):
        row.append(record[0])
    result += '\t'.join(row) + '\n'
    for record in self.tabledata:
      row = []
      for field in record[cstart::]:
        if(field == None):
          field = 'NULL'
        # don't let these into a tab-delimited
        # plain-text table
        field = re.sub(r'\\',r'\\\\',field)
        field = re.sub(r'\n',r'\\n',field)
        field = re.sub(r'\t',r'\\t',field)
        row.append(field)
      result += '\t'.join(row) + '\n'
    return result.encode('utf-8')
    
class GenericControl:
  def __init__(self,grid,dbclient):
    self.dbclient = dbclient
    self.grid = grid

  # make PgUp and PgDn keys move between edit fields
  def change_control_fields(self,w,ev,ef,sw,no_shift = False):
    r = False
    if(ef in self.clist):
      k = Gdk.keyval_name(ev.keyval)
      if(re.search('Page_Down',k)):
        return self.control_traverse(1,ef,sw)
      elif(re.search('Page_Up',k)):
        return self.control_traverse(-1,ef,sw)
      elif(re.search('Return',k)):
        if(no_shift or ev.state & Gdk.ModifierType.SHIFT_MASK):
          r = self.commit()
    self.auto_scroll(ef,sw)
    return r
          
  def control_traverse(self,n,ef,sw):
    i = self.clist.index(ef) + n
    # wrap around
    i %= len(self.clist)
    dest = self.clist[i]
    dest.grab_focus()
    self.auto_scroll(dest,sw)
    return True
    
  def auto_scroll(self,te,sw):
    GObject.timeout_add(200, self.auto_scroll_delayed,te,sw)
    
  def auto_scroll_delayed(self,te,sw):
    ea = te.get_allocation()
    ea_h = ea.y + ea.height + 4
    wa_v = sw.get_vadjustment().get_value()
    wa_h = sw.get_allocation().height
    if(ea_h > (wa_h + wa_v)):
      sw.get_vadjustment().set_value(ea_h - wa_h)
    elif(ea.y - 8 < wa_v):
      sw.get_vadjustment().set_value(ea.y - 8)
    
class QueryEntry:
  def __init__(self,parent,grid,dbclient,x,y):
    tooltip = 'Relate the "%s" field argument to any predecessor by logical ' % parent.field_name
    self.parent = parent
    self.dbclient = dbclient
    self.or_cb = Gtk.RadioButton.new_from_widget(None)
    self.or_cb.set_active(False)
    self.or_cb.set_visible(True)
    self.or_cb.set_tooltip_text(tooltip + 'OR')
    self.or_cb.connect(('clicked'),dbclient.test_instant_query_mode)
    self.and_cb = Gtk.RadioButton.new_from_widget(self.or_cb)
    self.and_cb.set_visible(True)
    self.and_cb.set_active(True)
    self.and_cb.set_tooltip_text(tooltip + 'AND')
    self.and_cb.connect(('clicked'),dbclient.test_instant_query_mode)
    grid.attach(self.and_cb,x,y,1,1)
    grid.attach(self.or_cb,x+1,y,1,1)
    self.te = Gtk.Entry()
    self.te.set_tooltip_text('Enter "%s" field argument\nNo entry: accept this field\nEnter or Shift-Enter executes query\nPgUp and PgDn move between fields' % parent.field_name)
    self.te.modify_font(dbclient.mono_font)
    self.te.set_hexpand(True)
    self.te.set_visible(True)
    self.te.connect('key-press-event',parent.change_control_fields,self.te,dbclient.k_query_scrolledwindow,True)
    grid.attach(self.te,x+2,y,1,1)
    self.te.connect('activate',dbclient.perform_query)
    parent.query_controls.append(self)
    parent.clist.append(self.te)
    
  def text(self):
    return self.dbclient.toUnicodeWhenPossible(self.te.get_text().strip())
    
  def blank(self):
    return (len(self.text().strip()) == 0)
    
  def clear(self):
    self.te.set_text('')
    self.and_cb.set_active(True)
    
  def search(self,s,accept,blank):
    ss = self.text()
    if(len(ss) == 0):
      return accept,blank
    else:
      result = re.search(ss,s) != None
      # if this is the first non-blank argument
      # then don't apply the chain logical operator
      if(blank):
        return result, False
      else:
        if(self.and_cb.get_active()):
          return (accept and result), False
        else:
          return (accept or result), False
          
  def mysql_query_logic(self,args):
    text = self.text()
    if(len(text) == 0):
      return args
    else:
      #text = self.dbclient.my_escape(text)
      # no recognizable operator?
      if(not re.search('(?i)regexp|>|=|<|like|between',text)):
        # use the regexp operator by default
        text = 'REGEXP %s' % text
      not_str = ('',' NOT ')[self.dbclient.inverse]
      args += self.get_logic(args)
      args += '%s `%s` %s' % (not_str,self.parent.field_name,text)
      return args
      
  def get_logic(self,arg = ''):
    if(len(arg) == 0): return ''
    return (' OR ',' AND ')[self.and_cb.get_active() == True] 
    
class QueryControl(GenericControl):
  def __init__(self,y,title,grid,dbclient,free_form = False):
    GenericControl.__init__(self,grid,dbclient)
    self.clist = dbclient.query_field_list
    self.field_name = title[0]
    self.te = False
    if(free_form):
      lbl = Gtk.Label()
      lbl.set_markup('<b>%s</b>' % self.field_name)
      lbl.set_alignment(0,0.5)
      lbl.set_visible(True)
      grid.attach(lbl,0,y,1,1)
      self.te = Gtk.Entry()
      self.te.set_visible(True)
      self.te.set_tooltip_text('Enter free-form query including field names\nNo entry: accept this field\nEnter or Shift-Enter executes')
      self.te.modify_font(dbclient.mono_font)
      self.te.set_hexpand(True)
      dbclient.k_query_grid.attach(self.te,3,y,4,1)
      self.te.connect('key-press-event',self.change_control_fields,self.te,dbclient.k_query_scrolledwindow,True)
      self.te.set_margin_top(1)
      self.clist.append(self.te)
      dbclient.freeform_sql_entry = self.te
    else: # not the special free-form case
      lbl = Gtk.Label(self.field_name)
      lbl.set_visible(True)
      lbl.set_alignment(0,0.5)
      lbl.set_tooltip_text('All controls on this row relate to the "%s" field' % self.field_name)
      grid.attach(lbl,0,y,1,1)
      self.query_controls = []
      self.entry1 = QueryEntry(self,grid,dbclient,1,y)
      self.entry2 = QueryEntry(self,grid,dbclient,4,y)
      self.select_checkbutton = Gtk.CheckButton('')
      self.select_checkbutton.set_visible(True)
      self.select_checkbutton.set_active(True)
      self.select_checkbutton.set_tooltip_text('Include "%s" field in result table' % self.field_name)
      self.select_checkbutton.set_alignment(0.5,0.5)
      grid.attach(self.select_checkbutton,7,y,1,1)
      self.select_checkbutton.connect(('clicked'),dbclient.test_instant_query_mode)

  def search(self,s,accept,blank):
    for item in self.query_controls:
      accept,blank = item.search(s,accept,blank)
    return accept,blank
    
      
  def clear(self,reset = False):
    for control in self.query_controls:
      control.clear()
    if(reset):
      self.select_checkbutton.set_active(True)
      
  def blank(self):
      return self.entry1.blank() and self.entry2.blank()
  
  def mysql_search_logic(self,args):
    arg1 = self.entry1.mysql_query_logic('')
    arg2 = self.entry2.mysql_query_logic('')
    if(len(arg1) > 0 and len(arg2) > 0):
      args += '%s (%s %s %s)' % (self.entry1.get_logic(args),arg1,self.entry2.get_logic(arg1),arg2)
    else:
      for item in self.query_controls:
        args = item.mysql_query_logic(args)
    return args

  def key_press_handler(self,w,ev,*args):
    return self.change_control_fields(self,ev)
    
  def commit(self):
    self.dbclient.perform_query()
    return True   
    
# to avoid a nasty resizing issue
# the viewport hosting this content
# must be set to resize mode "immediate"
        
class EditControl(GenericControl):
  entry_field_border=2 
  def __init__(self,gx,columndesc,data,grid,dbclient):
    GenericControl.__init__(self,grid,dbclient)
    self.clist = dbclient.edit_field_list
    self.lbl = Gtk.Label(columndesc[0])
    self.lbl.set_visible(True)
    self.lbl.set_alignment(0,0)
    grid.attach(self.lbl,0,gx,1,1)
    self.te = Gtk.TextView()
    self.te.set_hexpand(True)
    self.te.set_visible(True)
    self.te.set_wrap_mode((Gtk.WrapMode.NONE,Gtk.WrapMode.WORD)[dbclient.edit_linewrap])
    self.te.get_buffer().set_text(dbclient.toUnicodeWhenPossible(data))
    self.te.modify_font(dbclient.mono_font)
    self.te.connect('key-press-event',self.change_control_fields,self.te,dbclient.k_edit_scrolledwindow)
    self.te.set_border_width(self.entry_field_border)
    self.te.set_tooltip_text('Edit field "%s"\nShift-Enter commits\nPgUp and PgDn move between fields' % columndesc[0])
    grid.attach(self.te,1,gx,1,1)
    self.te.get_buffer().connect('changed',self.edit_changed)
    self.clist.append(self.te)
    self.changed = False
    self.old_changed = False
    
  def edit_changed(self,*args):
    self.edit_state(True)
    self.te.place_cursor_onscreen()

  def is_changed(self):
    return self.changed;
  
  def edit_clear(self,*args):
    self.edit_state(False)
    
  def edit_state(self,state):
    self.changed = state
    self.dbclient.edit_changed(state)
    self.set_background_color()
    
  def set_background_color(self):
    if(self.changed != self.old_changed):
      self.old_changed = self.changed
      # borrow a color from an unchanging control
      color =  self.dbclient.k_user_entry.get_style().fg
      color =  color[0]
      if(self.changed):
        # use a red foreground to signal
        # an edited field
        color = Gdk.Color(0xf000,0x0000,0x0000)
      self.te.modify_fg(Gtk.StateType.NORMAL,color)
    
  def value(self):
    buff = self.te.get_buffer()
    return buff.get_text(buff.get_start_iter(),buff.get_end_iter(),True)
    
  def key_press_handler(self,w,ev,*args):
    return self.change_control_fields(self,ev)
    
  def commit(self):
    self.dbclient.edit_requery()
    return True
    
  def enable(self,state):
    self.te.set_sensitive(state)
   
class DBClient(Gtk.Window):
  version = '2.4'
  red_color=Gdk.Color(0xc000,0x0000,0x0000)
  black_color=Gdk.Color(0x0000,0x0000,0x0000)
  selected_row = -1
  edit_changed_flag = False
  inhibit = True
  records = None
  mysql_term_history = []
  mysql_term_index = 0
  instant_query_mode = False
  table_liststore = None
  query_header = ('Field','And','Or','Argument','And','Or','Argument','Inc')
  def __init__(self):
    self.program_title = self.__class__.__name__ + ' Version %s' % self.version
    # turn all warnings into exceptions
    # so they will appear in the log
    warnings.filterwarnings('error')
    self.xmlfile = 'DBClient_gui.glade'
    self.builder = Gtk.Builder()
    self.builder.add_from_file(self.xmlfile)
    # define specific GUI objects as local fields by name
    for obj in self.builder.get_objects():
      try:
        name = Gtk.Buildable.get_name(obj)
        # only those starting with 'k_'
        if(re.search('^k_',name)):
          setattr(self,name,obj)
      except:
        None
    self.k_server_entry.connect('activate',self.execute)
    self.k_user_entry.connect('activate',self.execute)
    self.k_password_entry.connect('activate',self.execute)
    self.k_quit_button.connect('clicked',self.quit)
    self.edit_linewrap = False
    self.k_wrap_checkbutton.set_active(True)
    self.k_edit_mode_checkbutton.connect('clicked',self.set_edit_mode)
    self.k_wrap_checkbutton.connect('clicked',self.setwrap)
    self.k_table_ellipsize_checkbutton.connect('clicked',self.set_table_ellipsize)
    self.k_log_ellipsize_checkbutton.connect('clicked',self.set_log_ellipsize)
    self.k_tabledesc_ellipsize_checkbutton.connect('clicked',self.set_tabledesc_ellipsize)
    self.setwrap()
    self.k_mainwindow.connect('delete_event', self.quit)
    self.k_mainwindow.set_title('%s Copyright 2012, P. Lutus' % self.program_title)
    # posiiton window on screen
    disp = Gdk.Display.get_default()
    screen = Gdk.Display.get_default_screen(disp)
    self.display_width = screen.width()
    self.display_height = screen.height()
    self.k_mainwindow.resize(self.display_width * 2 / 3, self.display_height * 2 / 3)
    self.k_mainwindow.move(self.display_width / 6,self.display_height / 6)
    self.k_mainwindow.set_icon(self.create_icon_from_string(Icon.icon))
    self.k_db_combobox.connect('changed',self.get_table_list)
    self.k_table_combobox.connect('changed',self.set_table_name)
    self.mono_font = Pango.FontDescription('monospace')
    self.k_mysql_term_textview.modify_font(self.mono_font)
    self.k_tabledesc_add_pk_button.connect('clicked',self.ask_add_key_to_table)
    self.k_query_button.connect('clicked',self.perform_query)
    self.k_query_clear_button.connect('clicked',self.clear_query_and_selections)
    self.k_log_clear_button.connect('clicked',self.setup_log_tree_model)
    self.k_table_treeview.connect('cursor_changed',self.row_select_event)
    self.k_commit_button.connect('clicked',self.edit_requery)
    self.k_new_button.connect('clicked',self.new_edit)
    self.k_copy_button.connect('clicked',self.copy_edit)
    self.k_tabledesc_copy_button.connect('clicked',self.clipboard_copy_tabledesc)
    self.k_query_copy_tsv_button.connect('clicked',self.clipboard_copy_tsv)
    self.k_query_copy_html_button.connect('clicked',self.clipboard_copy_html)
    self.k_copy_log_button.connect('clicked',self.clipboard_copy_log)
    self.k_delete_button.connect('clicked',self.delete_edit)
    self.k_mysql_term_execute_button.connect('clicked',self.mysql_term_command)
    self.k_mysql_term_copy_button.connect('clicked',self.mysql_term_copy_to_clipboard)
    self.k_mysql_term_clear_button.connect('clicked',self.mysql_term_clear)
    self.k_help_button.connect('clicked',self.help)
    self.k_copy_query_button.connect('clicked',self.copy_label,self.k_mysql_disp_label)
    self.k_copy_edit_button.connect('clicked',self.copy_label,self.k_edit_disp_label)
    self.k_ext_regex_radiobutton.connect('clicked',self.setup_query_controls)
    self.k_table_treeview.connect('button-press-event',self.table_treeview_changed)
    self.k_instant_query_checkbutton.connect('clicked',self.set_instant_query_mode)
    self.k_invert_query_checkbutton.connect('clicked',self.test_instant_query_mode)
    self.k_cancel_button.connect('clicked',self.cancel_edit)
    self.k_start_button.connect('clicked',self.execute)
    self.k_launch_browser_button.connect('clicked',self.browser_table_display)
    self.k_mysql_term_entry_textview.connect('key-press-event',self.mysql_term_scan_history)
    self.k_mysql_disp_label.set_ellipsize(True)
    self.k_edit_disp_label.set_ellipsize(True)
    # defaults
    self.k_server_entry.set_text('localhost')
    self.k_user_entry.set_text(os.environ['USER'])
    self.setup_log_tree_model()
    self.inhibit = False
    self.setup()
    self.parse_options()
    if(self.records == None):
      self.k_password_entry.grab_focus()
      #self.execute()
        
  def parse_options(self):
    self.parser =  OptionParser()
    self.parser.add_option("-s", "--server", dest="server", help="specify a server")
    self.parser.add_option("-u", "--user", dest="user", help="specify a user")
    self.parser.add_option("-p", "--password", dest="password", help="specify a password (a dialog will appear if one is not provided)")
    self.parser.add_option("-d", "--db", dest="database", help="specify a database")
    self.parser.add_option("-t", "--table", dest="table", help="specify a table")
    self.parser.add_option("-r", "--read",action="store_true",dest="read" , help='Read the specified table (like pressing "Query")')
    self.parser.add_option("-e", "--edit",action="store_true",dest="edit" , help='Enable record editing (off by default, available as a checkbox)')
    self.parser.add_option("-i", "--instant",action="store_true",dest="instant" , help='Set instant query mode (perform query on each selection or control change)')
    (options, args) = self.parser.parse_args()
    if(len(sys.argv) > 1):
      if(options.edit):
        self.k_edit_mode_checkbutton.set_active(True)
      if(options.password):
        pw = options.password
      else:
        pw = self.password_dialog('Need password to execute command-line options')
      self.k_password_entry.set_text(pw)
      if(options.server):
        self.k_user_entry.set_text(options.server)
      if(options.user):
        self.k_user_entry.set_text(options.user)
      self.set_db_list()
      if(options.database):
        self.set_combobox_selection(self.k_db_combobox,options.database)
        self.set_db_list(options.database)
      if(options.table):
        self.set_combobox_selection(self.k_table_combobox,options.table)
        self.set_db_list(options.database,options.table)
      if(options.instant):
        self.k_instant_query_checkbutton.set_active(True)
        self.perform_query()
      if(options.read):
        self.perform_query()
        
  def create_icon_from_string(self,b64s):
    # --- icon from base64 string ---
    # create the string like this:
    # base64 fn.png > out.b64
    contents = base64.b64decode(b64s)
    loader = GdkPixbuf.PixbufLoader()
    loader.write(contents)
    loader.close()
    return loader.get_pixbuf()
          
  def help(self,*args):
    webbrowser.open('http://arachnoid.com/python/DBClient',0,autoraise=True)
    
  def browser_table_display(self,*args):
    if(self.table_liststore != None):
      array = self.read_liststore(self.table_liststore)
      exporter = ExportTable(self,self.db_name, self.table_name,self.table_desc,array,self.test_query_select)
      page = exporter.html(1,self.k_table_ellipsize_checkbutton.get_active()) # skip row number column
      dir_path = os.environ['HOME'] + '/.DBCLient/web_pages'
      try:
        os.makedirs(dir_path)
      except:
        None
      fn = '%s/%s.%s.html' % (dir_path,self.db_name,self.table_name)
      with codecs.open(fn,'w','utf-8') as f:
        f.write(page)
      webbrowser.open(fn,0,autoraise=True)
        
  def set_combobox_selection(self,box,text):
    r = False
    model = box.get_model()
    if(model != None):
      for i,item in enumerate(model):
        if(re.search(text,item[0])):
          r = i
          break
      if(r):
        box.set_active(r)
        
  def clipboard_copy_html(self,*args):
    array = self.read_liststore(self.table_liststore)
    exporter = ExportTable(self,self.db_name, self.table_name,self.table_desc,array,self.test_query_select)
    self.clipboard_copy(exporter.html(1,self.k_table_ellipsize_checkbutton.get_active())) # skip row number column
        
  def clipboard_copy_tsv(self,*args):
    array = self.read_liststore(self.table_liststore)
    exporter = ExportTable(self,self.db_name, self.table_name,self.table_desc,array,self.test_query_select)
    self.clipboard_copy(exporter.csv(1)) # skip row number column
    
  def clipboard_copy_log(self,*args):
    array = self.read_liststore(self.log_liststore,True)
    exporter = ExportTable(self,self.db_name, self.table_name,self.log_desc,array)
    self.clipboard_copy(exporter.csv())
    
  def clipboard_copy_tabledesc(self,*args):
    array = self.read_liststore(self.tabledesc_liststore)
    exporter = ExportTable(self,'','',self.tabledesc_list,array)
    self.clipboard_copy(exporter.csv())
       
  def read_liststore(self,liststore,log_copy = False):
    result = []
    if(len(liststore) > 0):
      for record in liststore:
        row = []
        i = 0
        try:
          while(True):
            item = record[i]
            if(item != None):
              item = self.toUnicodeWhenPossible(item)
            if(log_copy):
              # filter markup
              item = re.sub('</?span.*?>','',item)
            row.append(item)
            i += 1
        except Exception as e:
          None
        result.append(row)
    return result
    
  def clipboard_copy(self,s):
    clip = Gtk.Clipboard.get(Gdk.SELECTION_CLIPBOARD)
    clip.set_text(s,len(s))
        
  def copy_label(self,w,ef,*args):
    self.clipboard_copy(ef.get_text())

  def setup(self,*args):
    self.mysql_error = False
    self.k_noregex_radiobutton.set_active(True)
    self.edit_changed(False)
    self.populate_edit_grid()
    
  def set_edit_mode(self,*args):
    self.edit_mode = self.k_edit_mode_checkbutton.get_active()
    for control in self.edit_control_list:
      control.enable(self.edit_mode)
    for control in (self.k_copy_button,self.k_new_button,
    self.k_delete_button,self.k_wrap_checkbutton):
      control.set_sensitive(self.edit_mode)
    
  def execute(self,*args):
    self.set_db_list()

  # eliminate dups without losing original list order
  def eliminate_dups(self,array):
    result = []
    for item in array:
      if(item not in result):
        result.append(item)
    return result
    
  def mysql_term_fetch_history(self,i):
    # eliminate duplicates
    self.mysql_term_history = self.eliminate_dups(self.mysql_term_history)
    top = len(self.mysql_term_history)
    if(top == 0): return
    self.mysql_term_index += i
    self.mysql_term_index = min(top-1,self.mysql_term_index)
    self.mysql_term_index = max(0,self.mysql_term_index)
    s = self.mysql_term_history[self.mysql_term_index]
    self.k_mysql_term_entry_textview.get_buffer().set_text(s)
    return True
    
  def mysql_term_clear(self,*args):
    self.k_mysql_term_textview.get_buffer().set_text('')
    
  def mysql_term_copy_to_clipboard(self,*args):
    buf = self.k_mysql_term_textview.get_buffer()
    s = buf.get_text(buf.get_start_iter(),buf.get_end_iter(),True)
    self.clipboard_copy(s)
    
  def insert_into_mysql_term(self,s):
    buf = self.k_mysql_term_textview.get_buffer()
    ei = buf.get_end_iter()
    buf.insert(ei,s,-1)
    self.scroll_to_end(self.k_mysql_term_textview)
    
  def insert_into_history(self,s):
    # in order to move this command
    # to the top of the stack ...
    while(s in self.mysql_term_history):
      self.mysql_term_history.remove(s)
    self.mysql_term_history.append(s.strip())
    
  def textview_get_text(self,tv):
    buff = tv.get_buffer()
    return buff.get_text(buff.get_start_iter(),buff.get_end_iter(),True)
    
  def mysql_term_command(self,*args):
    try:
      com = self.textview_get_text(self.k_mysql_term_entry_textview)
      self.k_mysql_term_entry_textview.get_buffer().set_text('')
      self.insert_into_history(com)
      self.insert_into_history('')
      self.mysql_term_index = len(self.mysql_term_history)-1
      db = MySQLdb.connect(host=self.server,user=self.user,passwd=self.password,use_unicode=True,charset='utf8')
      cursor = db.cursor()
      cursor.execute(com)
      result = cursor.fetchall()
      cursor.close()
      db.commit()
      if(result != None):
        for record in result:
          record = re.sub(r'\\n',r'\n',self.toUnicodeWhenPossible(record))
          self.insert_into_mysql_term(record + '\n')
      self.k_mysql_term_entry_textview.grab_focus()
    except Exception as e:
      self.insert_into_mysql_term('< Error: %s >\n' % self.toUnicodeWhenPossible(e))
      
  def mysql_term_scan_history(self,w,ev):
    k = Gdk.keyval_name(ev.keyval)
    if(re.search('Page_Down',k)):
      self.mysql_term_fetch_history(1)
    elif(re.search('Page_Up',k)):
      self.mysql_term_fetch_history(-1)
    elif(re.search('Return',k)):
      if(ev.state & Gdk.ModifierType.SHIFT_MASK):
        self.mysql_term_command()
        return True
    
  def mysql_execute(self,mysql,fetch = True):
    mysql = re.sub(r'\n',r'\\n',mysql)
    for lbl in (self.k_mysql_disp_label,self.k_edit_disp_label):
      lbl.modify_fg(Gtk.StateType.NORMAL,self.black_color)
      lbl.set_text(mysql)
      lbl.set_tooltip_text(mysql)
    self.mysql_error = False
    result = False
    errors = '(no errors or warnings)'
    try:
      db = MySQLdb.connect(host=self.server,user=self.user,passwd=self.password,use_unicode=True,charset='utf8')
      cursor = db.cursor()
      cursor.execute(mysql)
      if(fetch):
        result = cursor.fetchall()
        result = list(result)
      else:
        # an action requiring a database commit
        result = cursor
        db.commit()
      cursor.close()
    except Exception as e:
      self.mysql_error = True
      errors = self.toUnicodeWhenPossible(e)
      for lbl in (self.k_mysql_disp_label,self.k_edit_disp_label):
        lbl.modify_fg(Gtk.StateType.NORMAL,self.red_color)
        s = 'Error: ' + errors + ' (details in log)'
        lbl.set_text(s)
        lbl.set_tooltip_text(s)
    self.log_entry([mysql,errors],self.mysql_error)
    return result
    
  def pango_error_markup(self,s):
    s = re.sub('<','&lt;',s)
    s = re.sub('>','&gt;',s)
    return '<span foreground="#ff0000">' + s + '</span>'
    
  def log_entry(self,array,error):
    array = [time.strftime('%Y-%m-%d %H:%M:%S')] + array
    if(error):
      None #array = [self.pango_error_markup(x) for x in array]  
    self.log_liststore.append(array)
    self.scroll_to_end(self.k_log_scrolledwindow)
    
  def table_treeview_changed(self,*args):
    if(self.edit_changed_flag):
      dlg = Gtk.MessageDialog(
        self.k_mainwindow,
        Gtk.DialogFlags.MODAL,
        Gtk.MessageType.QUESTION,
        Gtk.ButtonsType.NONE,
        'You\'re leaving an in-progress edit. Do you want to save it?'
      )
      dlg.add_button('Yes',Gtk.ResponseType.YES)
      dlg.add_button('No',Gtk.ResponseType.NO)
      dlg.add_button('Cancel',Gtk.ResponseType.CANCEL)
      resp = dlg.run()
      dlg.destroy()
      if(resp == Gtk.ResponseType.CANCEL):
        return True
      elif(resp == Gtk.ResponseType.YES):
        self.commit_edit()
        return False
      elif(resp == Gtk.ResponseType.NO):
        self.clear_edit()
        return False
        
  def my_escape(self,s):
    if(s != None):
      s = self.toUnicodeWhenPossible(s)
      s = re.sub(r'\\',r'\\\\',s)
      s = re.sub(r"'",r"''",s)
    return s
    
  def parse_null_format(self,name, value,assign = False):
    if(value == None):
      if(assign):
        return '`%s` = NULL' % (name)
      else:
        return '`%s` is NULL' % (name)
    else:
      name = self.toUnicodeWhenPossible(name)
      value = self.my_escape(value)
      return '`{0}` = \'{1}\''.format(name,value)

  def commit_edit(self,*args):
    edits = []
    originals = []
    partial_record = []
    full_record = []
    for x,field in enumerate(self.table_desc):
      # interleave edited fields with unchanged ones
      # when not all fields are on display
      control = False;
      if(self.edit_control_hash.has_key(x)):
        control = self.edit_control_hash[x]
        edit = self.edit_control_hash[x].value()
        edit = self.toUnicodeWhenPossible(edit)
        if(edit == 'NULL'):
          edit = None
        partial_record.append(edit)
      else:
        edit = self.selected_record[x]
      full_record.append(edit)
      original = self.selected_record[x]
      # only update changed fields
      if(control and control.is_changed()):
        edits.append(self.parse_null_format(field[0],edit,True))
      originals.append(self.parse_null_format(field[0],original))
    edits = ' , '.join(edits)
    originals = ' AND '.join(originals)
    mysql = 'UPDATE %s.`%s` SET %s WHERE %s' % (self.db_name,self.table_name,edits,originals)
    self.mysql_execute(mysql,False)
    if(not self.mysql_error):
      i = self.selected_row_visual
      if(i >= 0):
        # the liststore gets sorted along with the
        # display sorting, so it needs a visual index
        # but the data array is indexed
        # according to the original query
        # so the edit must include the original index
        # self.table_liststore[i] = [self.selected_row_db] + partial_record
        # all fields
        self.selected_record = full_record
        self.records[self.selected_row_db] = full_record
      self.clear_edit()
    return True
      
  def cancel_edit(self,*args):
    if(self.edit_changed_flag):
      self.clear_edit()
      record = self.records[self.selected_row_db]
      self.populate_edit_grid(record)
    
  def clear_edit(self):
    for item in self.edit_control_hash.values():
      item.edit_clear()
    self.edit_changed(False)
    
  def new_edit(self,*args):
    fields = []
    values = []
    now = datetime.datetime.now()
    for field in self.data_fields:
      # only include fields that don't have default values
      if(field[2] == 'YES'): # this field can be null
        fields.append('`%s`' % field[0]) # field name
        if(field[1] == 'date'):
          values.append(now.strftime('"%Y-%m-%d"'))
        elif(field[1] == 'time'):
          values.append(now.strftime('"%H:%M:%S"'))
        elif(re.search('datetime|timestamp',field[1])):
          values.append(now.strftime('"%Y-%m-%d %H:%M:%S"'))
        elif(re.search('decimal|float|double|int',field[1])):
          values.append('"0"')
        else:
          values.append('""')
    fields = ','.join(fields)
    values = ','.join(values)
    mysql = 'INSERT INTO %s.`%s` (%s) VALUES (%s)' % (self.db_name,self.table_name,fields,values)
    self.mysql_execute(mysql,False)
    self.perform_query()
    self.select_last_table_item()
  
  def select_last_table_item(self):
    self.scroll_to_end(self.k_table_scrolledwindow)  
    i = len(self.table_liststore) - 1
    self.k_table_treeview.set_cursor(i)
   
  def table_has_primary_key(self):
     for record in self.table_desc:
       if(record[3] == 'PRI'):
         return True
     return False
     
  def copy_edit(self,*args):
    if(not self.edit_record_displayed()):
      self.inform_dialog('Copy: no record selected.')
    elif(not self.table_has_primary_key()):
      self.inform_dialog('Copy: cannot safely copy a record\nin a table without a primary key.\nTo add a key to this table,\nchoose the Table Description tab and\nclick the button with a key icon.')
    else:
      values = []
      fields = []
      for x,field in enumerate(self.data_fields):
        fields.append(field[0])
        value = self.selected_record[x]
        if(value == None):
          values.append('NULL')
          isnull = True
        else:
          value = self.my_escape(value)
          values.append("'%s'" % (value))
      fields = ','.join(fields)
      values = ','.join(values)
      mysql = 'INSERT INTO %s.`%s` (%s) VALUES (%s)' % (self.db_name,self.table_name,fields,values)
      self.mysql_execute(mysql,False)
      self.perform_query()
      self.select_last_table_item()
    
  def delete_edit(self,*args):
    if(not self.edit_record_displayed()):
      self.inform_dialog('Delete: no record selected.')
    elif(self.confirm_dialog('OK to delete selected record?')):
      values = []
      for x,field in enumerate(self.table_desc):
        item = self.selected_record[x]
        values.append(self.parse_null_format(field[0], item))
      values = ' AND '.join(values)
      mysql = 'DELETE FROM %s.`%s` where %s' % (self.db_name,self.table_name,values)
      self.mysql_execute(mysql, False)
      self.perform_query()
      
  def edit_requery(self,*args):
    if(self.edit_changed_flag):
      self.commit_edit()
    v = self.k_table_treeview.get_cursor()
    self.perform_query()
    if(v[0] != None):
      i = int(str(v[0]))
      self.k_table_treeview.set_cursor(i)
      
  def get_table_description(self,force = False):
    try:
      if(force or self.table_desc == None):
        self.table_desc = self.mysql_execute('DESCRIBE %s.`%s`' % (self.db_name,self.table_name))
        self.k_tabledesc_add_pk_button.set_sensitive(not self.table_has_primary_key())
        self.data_fields = [x for x in self.table_desc if x[3] != 'PRI']
      return self.table_desc
    except:
      return None
    
  def row_select_event(self,*args):
    if(self.inhibit): return
    self.selected_row_visual = -1
    self.selected_row_db = -1
    tree_sel = self.k_table_treeview.get_selection()
    if(tree_sel != None):
      list_store,iter = tree_sel.get_selected()
      if(list_store != None and iter != None):
        self.erase_grid_display(self.k_edit_grid)
        p = list_store.get_path(iter)
        pi = p.get_indices()
        # self.selected_row_visual is the visual index
        # this is only required to display the edit
        # in the treeview
        self.selected_row_visual = pi[0]
        # the liststore row contains an index
        # into the original database table
        # self.selected_row_db is the db record index
        self.selected_row_db = list_store[iter][0]
        record = self.records[self.selected_row_db]
        self.populate_edit_grid(record)
        self.k_notebook.set_current_page(1)
        
  def edit_changed(self,state):
    self.k_commit_button.set_sensitive(state)
    #self.k_refresh_button.set_sensitive(state)
    self.k_cancel_button.set_sensitive(state)
    self.edit_changed_flag = state
    
  def edit_record_displayed(self):
    return (len(self.edit_control_hash.values()) > 0)
      
  def populate_edit_grid(self,record = None):
    self.selected_record = record
    self.edit_control_hash = {}
    self.edit_control_list = []
    self.edit_field_list = []
    self.erase_grid_display(self.k_edit_grid)
    if(record != None and len(record)> 0):
      table_desc = self.get_table_description(True)
      gx = 0
      for x,desc in enumerate(table_desc):
        if(self.test_query_select(x)):
          item = record[x]
          if(item == None):
            item = 'NULL'
          editc = EditControl(gx,desc,item,self.k_edit_grid,self)
          self.edit_control_hash[x] = editc
          self.edit_control_list.append(editc)
          gx += 1
      self.edit_changed(False)
    else:
      # default display
      lbl = Gtk.Label()
      lbl.set_markup('<b>To edit a record, click it in the top pane.</b>')
      lbl.set_visible(True)
      lbl.set_alignment(0.5,0.5)
      self.k_edit_grid.attach(lbl,0,0,1,1)
    self.set_edit_mode()
  
  def populate_query_grid(self):
    self.inhibit = True
    self.k_table_treeview.set_tooltip_text('Enter a query to read selected table')
    self.query_control_list = []
    self.query_field_list = []
    self.erase_grid_display(self.k_query_grid)
    for x,item in enumerate(self.query_header):
      lbl = Gtk.Label()
      lbl.set_markup('<b>%s</b>' % item)
      lbl.set_visible(True)
      lbl.set_alignment(0.5,0.5)
      self.k_query_grid.attach(lbl,x,0,1,1)
    descs = self.get_table_description()
    for y,desc in enumerate(descs):
      query = QueryControl(y+1,desc,self.k_query_grid,self)
      self.query_control_list.append(query)
    QueryControl(y+2,['Freeform'],self.k_query_grid,self,True)
    
    self.inhibit = False
      
  def setup_query_controls(self,*args):
    try :
      state = self.k_ext_regex_radiobutton.get_active()
      self.freeform_sql_entry.set_sensitive(not state)
      self.test_instant_query_mode()
    except:
      None

  def test_instant_query_mode(self,*args):
    if(self.instant_query_mode):
      self.perform_query()
    
  def set_instant_query_mode(self,*args):
    self.instant_query_mode = self.k_instant_query_checkbutton.get_active()
    
  def select_record(self,record):
    out_rec = []
    for x,field in enumerate(record):
      if(self.test_query_select(x)):
        out_rec.append(field)
    return out_rec
    
    
  def perform_query(self,*args):
    if(self.inhibit): return
    if(len(self.table_desc) != len(self.query_control_list)):
      self.inhibit = True
      self.populate_query_grid()
      self.inhibit = False
    self.k_mysql_disp_label.modify_fg(Gtk.StateType.NORMAL,self.black_color)
    blank_controls = (self.freeform_sql_entry.get_text().strip() == '')
    if(blank_controls):
      for control in self.query_control_list:
        if(not control.blank()):
          blank_controls = False
          break
      self.update_status(0)
    self.select_fields = []
    if(self.db_name == False or self.table_name == False): return
    self.populate_edit_grid(None)
    for query in self.query_control_list:
      if(query.select_checkbutton.get_active()):
        self.select_fields.append(query.field_name)
    self.select_field_string = ','.join(self.select_fields)
    self.inverse = self.k_invert_query_checkbutton.get_active()
    if(self.k_ext_regex_radiobutton.get_active()):
      self.perform_external_query(blank_controls)
    else:
      self.setup_table_tree_model()
      args = ''
      if(not blank_controls):
        args = self.freeform_sql_entry.get_text()
        for query in self.query_control_list:
          args = query.mysql_search_logic(args)
        if(len(args) > 0):
          args = 'WHERE %s' % args
      mysql = 'SELECT * FROM %s.`%s` %s' % (self.db_name,self.table_name,args)
      self.records = self.mysql_execute(mysql)
      if(self.records):
        self.records = list(self.records)
        for y,record in enumerate(self.records):
          select_rec = self.select_record(record)
          self.add_record_to_model(select_rec,self.table_liststore,y)
      else:
        self.records = []
      self.update_status(len(self.records))
    if(len(self.table_liststore) > 0):
      self.k_table_treeview.set_tooltip_text('Click a row to edit it')
        
  def perform_external_query(self,blank_controls):
    self.setup_table_tree_model()
    mysql = 'SELECT * FROM %s.`%s`' % (self.db_name,self.table_name)
    records = self.mysql_execute(mysql)
    self.k_mysql_disp_label.set_text(mysql + ' (external regex)')
    if(records):
      if(blank_controls):
         for y,record in enumerate(records):
           part_record = self.select_record(record)
           self.add_record_to_model(part_record,self.table_liststore,y)
         y += 1
      else:
        y = 0
        for record in records:
          blank = True
          accept = False
          for x,field in enumerate(record):
            if(field != None):
              field = self.toUnicodeWhenPossible(field)
            query = self.query_control_list[x]
            accept,blank = query.search(field,accept,blank)
          if(accept ^ self.inverse):
            part_record = self.select_record(record)
            self.add_record_to_model(part_record,self.table_liststore,y)
            y += 1
      self.update_status(y)
      
  def clear_query_and_selections(self,*args):
    self.reset_query_controls(True)
      
  def clear_query(self,*args):
    self.reset_query_controls()
    
  def reset_query_controls(self,reset_selection = False):
    for query in self.query_control_list:
      query.clear(reset_selection)
    self.freeform_sql_entry.set_text('')
    self.k_invert_query_checkbutton.set_active(False)
    self.k_ext_regex_radiobutton.set_active(False)
    self.test_instant_query_mode()
  
  def set_all_fields_mode(self,*args):
    for query in self.query_control_list:
      query.select_checkbutton.set_active(True)
      
  def get_table_list(self,*args):
    self.inhibit = True
    self.set_db_table_names()
    self.clear_tree_view(self.k_table_treeview)
    records = self.mysql_execute('SHOW TABLES IN %s' % self.db_name)
    self.table_name = False
    self.populate_dropdown(self.k_table_combobox,records)
    self.inhibit = False
    self.populate_edit_grid(None)
    if(self.instant_query_mode):
      self.perform_query()
    else:
      self.set_query_prompt()
    
  def set_table_name(self,*args):
    self.edit_control_hash = {}
    self.set_db_table_names()
    self.get_table_description(True)
    self.set_table_description()
    self.populate_query_grid()
    self.k_notebook.set_current_page(2)
    if(self.instant_query_mode):
      self.perform_query()
    else:
      self.set_query_prompt()    
    
  def read_table(self,*args):
    self.edit_control_hash = {}
    self.set_db_table_names()
    self.setup_table_tree_model(True)
    self.add_records_to_model(self.table_liststore)
    self.populate_query_grid()
    self.populate_edit_grid()
    
  def test_query_select(self,x):
     return (len(self.query_control_list) == 0 or self.query_control_list[x].select_checkbutton.get_active())
     
  def set_query_prompt(self):
    self.inhibit = True
    self.clear_tree_view(self.k_table_treeview)
    liststore = Gtk.ListStore(str)
    self.k_table_treeview.set_model(liststore)
    renderer = Gtk.CellRendererText()
    renderer.set_alignment(0.5,0.5)
    column = Gtk.TreeViewColumn("",renderer,markup=0)
    self.k_table_treeview.append_column(column)
    liststore.append(['<b>To read selected table, click "Query" below</b>'])
    self.inhibit = False
     
  def setup_table_tree_model(self,full = False):
    self.clear_tree_view(self.k_table_treeview)
    self.table_desc = None
    descs = self.get_table_description()
    if(descs != None):
      self.set_table_description()
      coltypes = [int]
      for x,desc in enumerate(descs):
        if(full or self.test_query_select(x)):
          coltypes.append(str)
      # because a treeview can be sorted by clicking on
      # its columns, the table's liststore must carry 
      # an index number that corresponds to the original db 
      # query row, so that an external array can be used 
      # to supply fields no present in a partial-field query
      # so each record that's read into the liststore
      # must have an index added to keep it in sync
      # with the unsorted, external array
      #coltypes.append(int)
      self.table_liststore = Gtk.ListStore(*coltypes)
      self.k_table_treeview.set_model(self.table_liststore)
      col_idx = 1
      #descs = list(descs) + [('my_index','int')]
      for x,desc in enumerate(descs):
        if(full or self.test_query_select(x)):
          renderer = Gtk.CellRendererText()
          renderer.set_property('font',self.mono_font)
          text = (re.search('text|varchar',desc[1]))
          if(not text):
            # right-justify numbers
            renderer.set_alignment(1,0.5)
          title = re.sub('_',' ',desc[0])
          column = Gtk.TreeViewColumn(title,renderer,text=col_idx)
          column.set_resizable(True)
          column.set_expand(True)
          self.k_table_treeview.append_column(column)
          self.table_liststore.set_sort_func(col_idx, self.my_compare, text)
          column.set_sort_column_id(col_idx)
          col_idx += 1
      self.set_table_ellipsize()
          
  # this solves a nasty sorting problem in which
  # numerical fields weren't sorted correctly
      
  def my_compare(self,model, row1, row2, text):
    def format_sort_num(s):
      dl = 16-len(s)
      return '%s%s' % ('0' * dl,s)
    sort_column, _ = model.get_sort_column_id()
    v1 = model.get_value(row1, sort_column)
    v2 = model.get_value(row2, sort_column)
    if(not text):
      v1 = format_sort_num(v1)
      v2 = format_sort_num(v2)
    if v1 < v2:
        return -1
    elif v1 == v2:
        return 0
    else:
        return 1
        
  def setup_log_tree_model(self,*args):
    self.clear_tree_view(self.k_log_treeview)
    coltypes = (str,str,str)
    self.log_liststore = Gtk.ListStore(*coltypes)
    self.k_log_treeview.set_model(self.log_liststore)
    self.log_desc = []
    for x,title in enumerate(('Time','Command','Errors')):
      self.log_desc.append([title])
      renderer = Gtk.CellRendererText()
      renderer.set_property('font',self.mono_font)
      column = Gtk.TreeViewColumn(title,renderer,text=x)
      column.set_resizable(True)
      column.set_expand(True)
      self.k_log_treeview.append_column(column)
    self.set_log_ellipsize()
   
  def add_key_to_table(self):
     self.mysql_execute('ALTER TABLE %s.`%s` ADD COLUMN pk integer NOT NULL AUTO_INCREMENT PRIMARY KEY' % (self.db_name,self.table_name))
     self.get_table_description(True)
     self.perform_query()
    
  def ask_add_key_to_table(self,*args):
    if(self.table_has_primary_key()):
      self.inform_dialog('Add key: this table already has a primary key.')
    else:
      if(self.confirm_dialog('Okay to add primary key to table "%s.%s"?' % (self.db_name,self.table_name))):
        self.add_key_to_table()

  def set_table_description(self):
    self.setup_tabledesc_tree_model()
    for record in self.table_desc:
      self.tabledesc_liststore.append(record)
    
  def setup_tabledesc_tree_model(self,*args):
    self.clear_tree_view(self.k_tabledesc_treeview)
    self.k_tabledesc_label.set_text('Table: %s.%s' % (self.db_name,self.table_name))
    coltypes = [str for i in range(6)]
    self.tabledesc_liststore = Gtk.ListStore(*coltypes)
    self.k_tabledesc_treeview.set_model(self.tabledesc_liststore)
    self.tabledesc_list = []
    for x,title in enumerate(('Field','Type','Can Be Null','Key','Default Value','Extra')):
      self.tabledesc_list.append([title])
      renderer = Gtk.CellRendererText()
      renderer.set_property('font',self.mono_font)
      column = Gtk.TreeViewColumn(title,renderer,text=x)
      column.set_resizable(True)
      column.set_expand(True)
      self.k_tabledesc_treeview.append_column(column)
    self.set_tabledesc_ellipsize()
  
  def set_tabledesc_ellipsize(self,*args):
    self.set_ellipsize(self.k_tabledesc_treeview,self.k_tabledesc_ellipsize_checkbutton.get_active())
        
  def set_table_ellipsize(self,*args):
    self.set_ellipsize(self.k_table_treeview,self.k_table_ellipsize_checkbutton.get_active())
  
  def set_log_ellipsize(self,*args):
    self.set_ellipsize(self.k_log_treeview,self.k_log_ellipsize_checkbutton.get_active())
    
  def set_ellipsize(self,tree,state):
    for col in tree.get_columns():
      for renderer in col.get_cells():
        val = (Pango.EllipsizeMode.NONE,Pango.EllipsizeMode.END)[state]
        renderer.set_property("ellipsize",val)
    tree.columns_autosize()

  def clear_tree_view(self,tv):
    cols = tv.get_columns()
    for col in cols:
      tv.remove_column(col)
     
  def toUnicodeWhenPossible(self,x):
    try:
      return unicode(x,'utf-8')
    except Exception as e:
      try:
        return str(x)
      except Exception as e:
        return x
     
  # the 'y' argument is an index to an external
  # array that maintains synchronization
  # when the treeview is sorted in various ways
  def add_record_to_model(self,record,model,y):
    # include the index as first element
    newrec = [y]
    # convert everything into strings
    for x,field in enumerate(record):
      if(field == None):
        newrec.append('NULL')
      else:
        newrec.append(self.toUnicodeWhenPossible(field))
    model.append(newrec)
      
  def add_records_to_model(self,model):
    self.index_hash = {}
    self.records = self.mysql_execute('SELECT * FROM %s.`%s`' % (self.db_name,self.table_name))
    if(self.records):
      self.records = list(self.records)
    for y,record in enumerate(self.records):
      self.add_record_to_model(record,model,y)
    self.update_status(len(self.records))
    self.edit_changed(False)
        
  def set_db_table_names(self,table = False):
    self.db_name = False
    self.table_name = False
    try:
      i = self.k_db_combobox.get_active()
      self.db_name = self.k_db_combobox.get_model()[i][0]
      j = self.k_table_combobox.get_active()
      self.table_name = self.k_table_combobox.get_model()[j][0]
      if(table):
        self.set_combobox_selection(self.k_table_combobox,table)
    except:
      None
    
  def set_db_list(self, db = False,table = False):
    self.server = self.k_server_entry.get_text()
    self.user = self.k_user_entry.get_text()
    self.password = self.k_password_entry.get_text()
    self.db_name = False
    records = self.mysql_execute('SHOW DATABASES')
    self.populate_dropdown(self.k_db_combobox,records)
    if(db):
      self.set_combobox_selection(self.k_db_combobox,db)
    self.set_db_table_names(table)
    
    
  def erase_event_callback(self,w,*args):
    args[0].remove(w)
    w.unparent() 
  
  def erase_grid_display(self,grid):
    Gtk.Container.foreach(grid,self.erase_event_callback,grid)
      
  def populate_dropdown(self,dd,dblist):
    # this assures a well-behaved but empty list
    # in the event of no records
    liststore = Gtk.ListStore(GObject.TYPE_STRING)
    dd.clear()
    dd.set_model(liststore)
    if(dblist):
      outlist = []
      for record in dblist:
        item = record[0]
        item = re.sub('.*#(.*)','\\1',item)
        outlist.append([item])
      for item in sorted(outlist):
        liststore.append(item)
      dd.clear()
      dd.set_model(liststore)
      dd.set_active(0)
      cell = Gtk.CellRendererText()
      dd.pack_start(cell, True)
      dd.add_attribute(cell, "text", 0)
    
  def update_status(self,y):
    self.k_status_label.set_text('%d matching records' % y)
    
  def setwrap(self,*args):
    self.edit_linewrap = self.k_wrap_checkbutton.get_active()
    #self.row_select_event()
    
  # must delay scroll-to-end
  def scroll_to_end(self,sw):
    GObject.timeout_add(200, self.scroll_to_end_delayed,sw)
    
  def scroll_to_end_delayed(self,*args):
    adj = args[0].get_vadjustment()
    adj.set_value( adj.get_upper() - adj.get_page_size() )
    
  def confirm_dialog(self,msg):
    dlg = Gtk.MessageDialog(
      self.k_mainwindow,
      Gtk.DialogFlags.MODAL,
      Gtk.MessageType.QUESTION,
      Gtk.ButtonsType.YES_NO,
      msg
    )
    resp = dlg.run()
    dlg.destroy()
    return(resp == Gtk.ResponseType.YES)
    
  def response_to_dialog(self,entry, dialog, response):
    dialog.response(response)

  def password_dialog(self,msg):
    dlg = Gtk.MessageDialog(
      None,
      Gtk.DialogFlags.MODAL,
      Gtk.MessageType.QUESTION,
      Gtk.ButtonsType.OK,
      msg
    )
    entry = Gtk.Entry()
    entry.connect("activate", self.response_to_dialog, dlg, Gtk.ResponseType.OK)
    # hide password characters
    entry.set_visibility(False)
    hbox = Gtk.HBox()
    hbox.pack_start(Gtk.Label("Password:"), False, 5, 5)
    hbox.pack_end(entry,0,0,1)
    dlg.vbox.pack_end(hbox, True, True, 0)
    dlg.show_all()
    resp = dlg.run()
    pw = entry.get_text()
    dlg.destroy()
    return(pw)
    
  def inform_dialog(self,msg):
    dlg = Gtk.MessageDialog(
      self.k_mainwindow,
      Gtk.DialogFlags.MODAL,
      Gtk.MessageType.INFO,
      Gtk.ButtonsType.OK,
      msg
    )
    dlg.run()
    dlg.destroy()
    
  def quit(self,*args):
    if(not self.table_treeview_changed()):
      Gtk.main_quit()
      return False
    else:
      return True

  def list_dir(self,obj):
    for item in dir(obj):
      print item

win = DBClient()
win.show_all()
Gtk.main()