# Fig. 27.27 DatabaseViewer.py
# A GUI Database Viewer

import os
import glob
from Tkinter import *
import tkMessageBox
import Pmw
from gadfly import gadfly

class DatabaseViewer( Frame ):

   def __init__( self ):
      Frame.__init__( self )
          
      self.gadflyPath = "C:\\Python\\Gadfly"  # path to database
      self.databaseDirectories = {} # database locations
      self.databaseNames = []       # list of available databases
      self.connection = None        # connection to database
      self.currentQuery = StringVar()  # current query string
      self.queryDisplay = None         # query results text dialog

      # populate databaseNames list
      os.path.walk( self.gadflyPath, self.getDatabase, 0 )

      # congifure master
      self.master.title( 'Database GUI Viewer' )
      self.master.columnconfigure( 0, weight = 1 )
      self.master.rowconfigure( 0 , weight = 1 )
      self.master.grid()      

      # build GUI components
      self.buildGUI()

   def destroy( self ):
      self.closeDatabase()
      Frame.destroy( self )
         
   def getDatabase( self, path, dirname, names ):
      """Get names of all databases in specified directory."""
      os.chdir( dirname )     # change directories

      # get list of all database files in gadfly directory
      databaseFiles = glob.glob( "*.gfd" )
      
      for database in databaseFiles:
         databaseName = database[ :-4 ]
         self.databaseNames.append( databaseName )
         self.databaseDirectories[ databaseName ] = dirname   

   def createSection( self, parent, labelText, numberOfColumns ):
      """Creates a subsection of a frame."""

      # create enclosing frame
      frame = Frame( parent, relief = 'groove', borderwidth = 5 )
      frame.columnconfigure( tuple( range( numberOfColumns ) ),
         weight = 1 )
                      
      # create label for frame
      label = Label( frame, text = labelText,
         font = ( 'Arial', 12, 'bold' ),  background =
         'dark gray', foreground = 'dark blue', relief = 'ridge',
         borderwidth = 3 )
      label.grid( row = 0, columnspan = numberOfColumns,
         sticky = 'new' )
      
      return frame

   def createButton( self, parent, name, action, gridRow,
      gridColumn ):
      """Creates a button."""
      return Button( parent, text = name, command = action ) \
         .grid( row = gridRow, column = gridColumn )

   def buildGUI( self ):
      """Build database viewer display."""

      #------------------Configure self-----------------------#
      Pmw.initialise()
      self.grid( row = 0, column = 0, sticky = 'nesw' )
      #-------------------------------------------------------#
      
      #-------------Build 'Select Database' Section-----------#
      databaseFrame = self.createSection( self,
         "Select Database", 3 )
      databaseFrame.grid( row = 0, column = 0, sticky = 'nesw',
         columnspan = 2 )
      
      # allows users to select from databases
      self.databaseComboBox = Pmw.ComboBox( databaseFrame,
         scrolledlist_items = self.databaseNames, dropdown = 1 )
      self.databaseComboBox.grid( row = 1, column = 0, padx = 3,
         sticky = 'e' )

      # button to open selected database
      openDatabaseButton = self.createButton( databaseFrame,
         "Open Database", self.openDatabase, 1, 1 )
    
      # button to close current database
      closeDatabaseButton = self.createButton( databaseFrame,
         "Close Database", self.closeDatabase, 1, 2 )
      #-------------------------------------------------------#

      #------------Build 'Table Information' Section----------#
      tableFrame = self.createSection( self,
         "Table Information", 2 )
      tableFrame.grid( row = 1, column = 0, columnspan = 2,
         sticky = 'nesw', pady = 5 )

      # List of available tables
      Label( tableFrame, text = "Available Tables:", relief =
         'raised' ).grid( row = 1, column = 0, sticky = 'nesw' )
      self.tableList = Pmw.ScrolledListBox( tableFrame,
         selectioncommand = self.displayColumnInfo )
      self.tableList.configure( listbox_height = 11 )
      self.tableList.grid( row = 2, column = 0, sticky = 'nesw' )

      # Information about selected table
      Label( tableFrame, text = "Column Names:", relief =
         'raised' ).grid( row = 1, column = 1, sticky = 'nesw' )
      self.tableDisplay = Pmw.ScrolledText( tableFrame,
         text_wrap = 'none' )
      self.tableDisplay.configure( text_height = 13 )
      self.tableDisplay.configure( text_width = 50 )
      self.tableDisplay.grid( row = 2, column = 1,
         sticky = 'nesw' )
      #--------------------------------------------------------#
      
      #-----------Build 'Query Statement' Section--------------#
      queryFrame = self.createSection( self,
         "Query Statement:", 1 )
      queryFrame.grid( row = 2, column = 0, sticky = 'nesw',
         columnspan = 2 )

      # Entry field for query statement
      self.queryEntry = Pmw.EntryField( queryFrame,
         command = self.executeQuery, entry_textvariable =
         self.currentQuery )
      self.queryEntry.grid( row = 1, column = 0, sticky = 'ew',
         pady = 5 )
           
      # Button to execute statement
      queryExecuteButton = self.createButton( queryFrame,
         "Execute Query", self.executeQuery, 2, 0 )
      #-------------------------------------------------------#

   def openDatabase( self ):
      """Open selected database."""

      if self.connection:
         tkMessageBox.showerror( "Database already open",
            "You must close the current database before "
            + "opening a new one." )
         return   

      # open database
      try:

         # get path to specified database
         databaseName = self.databaseComboBox.get()
         databasePathname = os.path.join( self.gadflyPath,
            self.databaseDirectories[ databaseName ] )
         self.connection = gadfly( databaseName, 
            databasePathname )
      except KeyError:
         tkMessageBox.showerror( "Select Database",
            "You must first select a database to open." )
         return
      except:
         tkMessageBox.showerror( "Database Error",
            "Error opening database." )
         return

      # get table info
      cursor = self.connection.cursor()
      cursor.execute( "select * from __table_names__" )
      results = cursor.fetchall()

      # display list of tables
      for item in results:
         self.tableList.insert( 0, item[ 1 ] )

      cursor.close()
      
   def displayColumnInfo( self ):
      """Display columns from selected table."""

      if self.connection == None:
         return
      
      # get a record from this table
      tableName = self.tableList.getcurselection()
      cursor = self.connection.cursor()
      cursor.execute( "select * from %s" % tableName )
      results = cursor.description 

      # display column information
      self.tableDisplay.clear()

      for row in results:
         self.tableDisplay.insert( END, row[ 0 ] + '\n' )

      cursor.close()
 
   def executeQuery( self ):
      """Execute user-entered query statement."""

      # user must open database to execute query
      if self.connection == None:
         tkMessageBox.showerror( "Open Database",
            "You must first select and open a database." )
         return

      cursor = self.connection.cursor()
      
      # execute query, raise exception on error
      try:
         queryString = self.currentQuery.get() # retrieve string
         queryString = queryString.replace( '"', "'" )
         cursor.execute( queryString )
         results = cursor.pp()
      except Exception, errorMessage:
         results = "The SQL statement cannot be executed.\n" \
            + "The full SQL error message is:\n\n%s" \
            % errorMessage
         
      # display results in new window
      if self.queryDisplay:
         self.queryDisplay.destroy()

      self.queryDisplay = Pmw.TextDialog( title =
         "Results for: %s" % queryString, defaultbutton = 0 )
      self.queryDisplay.configure( text_height = 10 )
      self.queryDisplay.configure( text_width = 50 )
      self.queryDisplay.insert( END, results )
      self.queryDisplay.configure( text_state = "disabled" )
      self.queryDisplay.activate( globalMode = 'nograb' )

      cursor.close()
      
   def closeDatabase( self ):
      """Close current database."""

      if self.connection == None:
         return
      
      # close connection and clear display fields
      self.connection.close()
      self.connection = None
      self.databaseComboBox._entryfield.clear()
      self.tableList.clear()
      self.tableDisplay.clear()
         
if __name__ == "__main__":
   DatabaseViewer().mainloop()


########################################################################## 
# (C) Copyright 2001 by Deitel & Associates, Inc. and Prentice Hall.     #
# All Rights Reserved.                                                   #
#                                                                        #
# DISCLAIMER: The authors and publisher of this book have used their     #
# best efforts in preparing the book. These efforts include the          #
# development, research, and testing of the theories and programs        #
# to determine their effectiveness. The authors and publisher make       #
# no warranty of any kind, expressed or implied, with regard to these    #
# programs or to the documentation contained in these books. The authors #
# and publisher shall not be liable in any event for incidental or       #
# consequential damages in connection with, or arising out of, the       #
# furnishing, performance, or use of these programs.                     #
##########################################################################