#!/usr/bin/env python
#
# Database explorer - send SQL querries to a mySQL databse and show results in a grid.
# (c) 2002 cliechti@gmx.net

import MySQLdb

## import all of the wxPython GUI package
from wxPython.wx import *
from wxPython.grid import *

#----------------------------------------------------------------
class SetupDialog(wxDialog):
    """a dialog with fields to specify a database"""
    
    def __init__(self, parent, dbprops):
        wxDialog.__init__(self, parent, -1, "Connect to MySQL Database")

        #text inputs
        self.host = wxTextCtrl(self, -1,     dbprops['host'], size=wxSize(200, -1))
        self.database = wxTextCtrl(self, -1, dbprops['db'], size=wxSize(200, -1))
        self.username = wxTextCtrl(self, -1, dbprops['user'], size=wxSize(200, -1))
        self.password = wxTextCtrl(self, -1, dbprops['passwd'], size=wxSize(200, -1), style=wxTE_PASSWORD)

        #buttons
        cancel = wxButton(self, wxID_CANCEL, "Cancel")
        ok     = wxButton(self, wxID_OK, "OK")
        ok.SetDefault()

        #show textinputs with labels above
        sizer = wxBoxSizer(wxVERTICAL)
        sizer.Add(wxStaticText(self, -1, "Host"))
        sizer.Add(self.host)
        sizer.Add(wxStaticText(self, -1, "Database"))
        sizer.Add(self.database)
        sizer.Add(wxStaticText(self, -1, "Username"))
        sizer.Add(self.username)
        sizer.Add(wxStaticText(self, -1, "Password"))
        sizer.Add(self.password)

        EVT_SET_FOCUS(self.host, self.OnFocus)
        EVT_SET_FOCUS(self.database, self.OnFocus)
        EVT_SET_FOCUS(self.username, self.OnFocus)
        EVT_SET_FOCUS(self.password, self.OnFocus)

        #align buttons horizontally in a separate sizer
        botsizer = wxBoxSizer(wxHORIZONTAL)
        botsizer.Add(ok , 0 , wxALIGN_RIGHT)
        botsizer.Add(10, 10, 0, 1)
        botsizer.Add(cancel,0 , wxALIGN_RIGHT)

        #add buttons after a small gap
        sizer.Add(20,5, 0, 1)
        sizer.Add(botsizer, 0, wxALIGN_RIGHT)

        #use another sizer to make a border around everything
        box = wxBoxSizer(wxHORIZONTAL)
        box.Add(sizer,0,wxALL, 10)
        self.SetAutoLayout(1)
        self.SetSizer(box)
        box.Fit(self)

        #finish gui
        self.CenterOnParent()
        self.host.SetFocus()

    def OnFocus(self, event):
        event.GetEventObject().SetSelection(0,-1)

class View(wxListCtrl):
    def __init__(self, parent):
        wxListCtrl.__init__(self, parent, -1,
                            style=wxLC_REPORT|wxSUNKEN_BORDER|wxLC_VIRTUAL|wxLC_VRULES,#|wxLC_HRULES,
                            size=(400,400))

        EVT_LIST_ITEM_SELECTED(self, self.GetId(), self.OnItemSelected)
        EVT_LIST_ITEM_ACTIVATED(self, self.GetId(), self.OnItemActivated)
        self.sqlresult = []

    def OnItemSelected(self, event):
        self.currentItem = event.m_itemIndex
##        print ('OnItemSelected: "%s", "%s", "%s", "%s"\n' %
##                           (self.currentItem,
##                            self.GetItemText(self.currentItem),
##                            self.getColumnText(self.currentItem, 1),
##                            self.getColumnText(self.currentItem, 2)))

    def OnItemActivated(self, event):
        self.currentItem = event.m_itemIndex
        self.log.WriteText("OnItemActivated: %s\n" % self.GetItemText(self.currentItem))

    #---------------------------------------------------
    # These methods are callbacks for implementing the
    # "virtualness" of the list...
    def OnGetItemText(self, row, col):
        #print row, col
        try:
            return str(self.sqlresult[row][col])
        except IndexError:
            return ''

    def OnGetItemImage(self, item):
        return 0

    def OnGetItemAttr(self, item):
        return None


class Frame(wxFrame):
    ID_INPUT    = wxNewId()
    ID_BUTTON   = wxNewId()
    M_OPEN      = wxNewId()
    M_EXIT      = wxNewId()
    ID_VIEW     = wxNewId()

    def __init__(self, parent, id, title = "MySql Querry"):
        # First, call the base class' __init__ method to create the frame
        wxFrame.__init__(self, parent, id, title, wxPoint(100, 100), wxSize(100, 100))

        #variables
        self.db = None
        self.c = None
        self.dbprops = {'host': 'localhost', 'db':'test', 'user':'', 'passwd':''}
        self.history = ["show tables;"]

        #menu
        menu = wxMenu()
        menu.Append(self.M_OPEN, "&Connect...")
        menu.Append(self.M_EXIT, "E&xit")
        EVT_MENU(self, self.M_OPEN, self.OnMenuConnect)
        EVT_MENU(self, self.M_EXIT, self.OnCloseWindow)
        menuBar = wxMenuBar()
        menuBar.Append(menu, "&File");
        self.SetMenuBar(menuBar)

        #GUI
        panel = wxPanel(self, 0)
        self.inp = wxComboBox(panel, self.ID_INPUT, "",
                              choices = self.history,
                              style=wxCB_DROPDOWN|wxCB_SORT,
                              size=wxSize(400,-1))
        EVT_COMBOBOX(panel, self.ID_INPUT, self.OnSelect) 

        button = wxButton(panel, self.ID_BUTTON, "Execute" )
        button.SetDefault()
        EVT_BUTTON(panel, self.ID_BUTTON, self.OnEnter )

        topsizer = wxBoxSizer(wxHORIZONTAL)
        topsizer.Add(wxStaticText(panel, -1, "SQL: "), 0, wxALIGN_CENTER)
        topsizer.Add(self.inp, 1)
        topsizer.Add(button, 0)

        self.list = View(panel)
        
        sizer = wxBoxSizer(wxVERTICAL)
        sizer.Add(topsizer, 0, wxEXPAND)
        sizer.Add(self.list, 1, wxEXPAND)

        panel.SetAutoLayout(1)
        panel.SetSizer(sizer)
        sizer.Fit(panel)

        basesizer = wxBoxSizer(wxVERTICAL)
        basesizer.Add(panel, 1, wxEXPAND)
        self.SetAutoLayout(1)
        self.SetSizer(basesizer)
        self.Fit()

        #create a statusbar
        sb = self.CreateStatusBar(1)
        sb.SetStatusWidths([-1])
        self.SetStatusText('Not connected', 0)      #display connection params on startup

        #show connection dialog
        self.OnMenuConnect()



    def UpdateList(self):
        self.list.ClearAll()
        if self.c.description:
            desc = map( lambda x: "%s" % (x[0],), self.c.description)
            self.list.InsertColumn(0, desc[0])
            for i in range(1,len(desc)):
                self.list.InsertColumn(i, desc[i])
        
            line = 0
            self.list.sqlresult = result = self.c.fetchall()
            self.list.SetItemCount(len(result))
            self.SetStatusText('Ok, %d rows' % len(result),0)
##            for row in result:       #get results
##                self.list.InsertStringItem(line,str(row[0]))
##                for i in range(1,len(row)):
##                    self.list.SetStringItem(line,i,str(row[i]))
##                line += 1
            for i in range(len(self.c.description)):
                self.list.SetColumnWidth(i, wxLIST_AUTOSIZE)
        else:
            self.SetStatusText('Ok, empty result')

    def OnEnter(self, event=None):
        """SQL querry has been entered - execute request and show results"""
        if self.c is None:
            self.SetStatusText('Connect to Database first!!', 0)
        else:
            querry = self.inp.GetValue()            #get querry string
            try:
                self.c.execute(querry)              #execute querry
            except MySQLdb.MySQLError, msg:
                self.SetStatusText(str(msg),0)      #show error messages
            else:
                if querry not in self.history:      #save successful querries if not yet in list
                    self.inp.Append(querry)         #save for later
                    self.history.append(querry)
                self.UpdateList()

    def OnSelect(self, event):
        """Entry from history selected"""
        self.inp.SetValue(event.GetString())    #get entry from history

##    def OnClick(self, event):
##        """insert text from selected cell in input field"""
##        text = str(self.list.GetCellValue(event.GetRow(), event.GetCol()))
##        clip = wxClipboard()
##        clip.Open()
##        clip.SetData( wxTextDataObject(text) )
##        clip.Close()
##        #self.inp.Paste()


    def OnMenuConnect(self, event=None):
        """Connect to a new database"""
        dlg = SetupDialog(self, self.dbprops)
        id = dlg.ShowModal()
        if id == wxID_OK:
            self.dbprops['host']    = dlg.host.GetValue()
            self.dbprops['db']      = dlg.database.GetValue()
            self.dbprops['user']    = dlg.username.GetValue()
            self.dbprops['passwd']  = dlg.password.GetValue()
            try:
                self.db = MySQLdb.connect(**self.dbprops)
                self.c = self.db.cursor()
            except MySQLdb.MySQLError, msg:
                self.db = self.c = None             #forget about old one to not confuse the user
                self.SetStatusText(str(msg),0)      #show error messages
            else:
                self.SetStatusText('Ok, new database is "%(db)s" on "%(host)s"' % self.dbprops,0)
        else:
            self.SetStatusText('No changes',0)
        dlg.Destroy()

    def OnCloseWindow(self, event=None):
        self.Destroy()



#---------------------------------------------------------------------------
# Every wxWindows application must have a class derived from wxApp
class App(wxApp):
    # wxWindows calls this method to initialize the application
    def OnInit(self):
        # Create an instance of our customized Frame class
        frame = Frame(NULL, -1)
        frame.Show(true)
        # Tell wxWindows that this is our main window
        self.SetTopWindow(frame)
        # Return a success flag
        return true

#---------------------------------------------------------------------------
if __name__ == "__main__":
    app = App(0)    # Create an instance of the application class
    app.MainLoop()     # Tell it to start processing events