#!/usr/bin/env python
# -*- coding: latin-1; -*-
#
# PgWorksheet - PostgreSQL Front End
# Version 1.6.1
# http://pgworksheet.sf.net/
#
# Copyright  2004, CML http://www.e-cml.org/
# The PgWorksheet UI is derived from the pgst UI Copyright  2004, Mike McKee
#
# You may change and re-release as commercial and customized versions per the license.
# See LICENSE.txt
#
# $Id: pgworksheet,v 1.48 2004/11/24 06:53:30 hmichelon Exp $
#
import string
import sys
import os
import ConfigParser

import pygtk
pygtk.require('2.0') # please comment this line for cx_Freeze
import gtk
# imports below for cx_Freeze
import gtk.glade
import gtk.gdk
import atk
import gtk._gtk
import pango

from pgw.GladeClass import GladeClass
from pgw.DBConnection import DBConnection
from pgw.Execute import Execute
from pgw.RunSQL import *


class PgWorksheet(GladeClass):
  
  def __init__(self, path):
    # Initialize glade
    GladeClass.__init__(self, path, "pgworksheet.glade", "pgworksheet")
    self.window = self.widget("main")
    self.db = None

    # Connection status in status bar
    statusbar = self.widget("statusbar")
    
    statusbar.hide()
    statusbar.set_border_width(0)
    f = statusbar.get_children()[0]
    f.set_shadow_type(gtk.SHADOW_IN)
    self.status_result = f.get_children()
    self.status_result = self.status_result[0]
    statusbar.set_child_packing(f, gtk.TRUE, gtk.TRUE, 0, gtk.PACK_START)

    self.status_connect = gtk.Label();
    self.status_connect.set_justify(gtk.JUSTIFY_LEFT)
    f = gtk.Frame()
    f.set_shadow_type(gtk.SHADOW_IN)
    f.add(self.status_connect)
    statusbar.add(f)
    statusbar.set_child_packing(f, gtk.FALSE, gtk.FALSE, 0, gtk.PACK_START)

    self.status_version = gtk.Label();
    f = gtk.Frame()
    f.set_shadow_type(gtk.SHADOW_IN)
    f.add(self.status_version)
    statusbar.add(f)
    statusbar.set_child_packing(f, gtk.FALSE, gtk.FALSE, 0, gtk.PACK_START)

    f = gtk.Label("   ");
    statusbar.add(f)
    statusbar.set_child_packing(f, gtk.FALSE, gtk.FALSE, 0, gtk.PACK_START)

    statusbar.show_all()
    self.on_Disconnect(None)
    # change the query font to proportional
    self.txtSQLFont = self.set_proportional("txtSQL")
    # prev/next query lifos
    self.prev_statements = []
    self.next_statements = []
    self.load_history()
    self.prev_saved = 1
    self.connect_server = self.widget("connect_server")
    self.widget("tabResult").set_scrollable(gtk.TRUE)
    # fill the connect_server dialog with last used parameters
    try:
      self.widget("username").set_text(os.environ['USERNAME'])
    except KeyError:
      try:
        self.widget("username").set_text(os.environ['USER'])
      except KeyError:
        pass
      pass
    self.cp = ConfigParser.ConfigParser()
    try :
      self.cp.readfp(open(self.configdir() + os.sep + '.pgworksheet'))
      try:
        self.widget("host").set_text(self.cp.get("server", "host"))
      except:
        pass
      try:
        self.widget("port").set_text(self.cp.get("server", "port"))
      except:
        pass
      try:
        self.widget("database").set_text(self.cp.get("server", "database"))
      except:
        pass
      try:
        self.widget("database").entry.set_text(self.cp.get("server", "database"))
      except:
        pass
      try:
        self.widget("username").set_text(self.cp.get("server", "user"))
      except:
        pass
      maximized = 0
      try:
        maximized = int(self.cp.get("window", "maximized"))
      except:
        pass
      if (maximized):
        self.window.maximize()
      else:  
        width, height = self.window.get_size()
        try:
          width = int(self.cp.get("window", "width"))
        except:
          pass
        try:
          height = int(self.cp.get("window", "height"))
        except:
          pass
        self.window.resize(width, height)
        x, y = self.window.get_position()
        try:
          x = int(self.cp.get("window", "x"))
        except:
          pass
        try:
          y = int(self.cp.get("window", "y"))
        except:
          pass
        self.window.move(x, y)
        try:
           self.widget("vpaned1").set_position(int(self.cp.get("window", "divider")))
        except:
          pass
    except IOError:
      pass
    # prepare and display main and connect_server windows
    self.widget("password").grab_focus()
    self.window.show()
    entry, button = self.widget("database").get_children()
    button.hide()
    self.connect_server.show()


  def is_connected(self):
    """Return TRUE if connected to a database"""
    if (self.db is None):
      return None
    else:
      return self.db.is_connected()


  def configdir(self):
    """Return the directory where the configuration file is stored"""
    if (mswindows()):
      try:
        return os.environ['USERPROFILE']
      except KeyError: 
        try:
          return os.environ['ALLUSERSPROFILE']
	except KeyError:
          return os.environ['SYSTEMROOT']
    else:	    
      return os.environ['HOME']


  def connect(self, host = None, port = None, db = None, user = None, password = None):  
    """Try to connect to a database and save the connection parameters
    for later use"""
    self.on_Disconnect(None)
    self.db = DBConnection(host, port, db, user, password)
    if (self.is_connected()):
      self.widget("mnuDisconnect").set_sensitive(gtk.TRUE)
      self.status("connected as <b>" + user + "</b> to <b>" + db + "</b> on <b>"+ host + "</b>",
                self.db.pgversion())
      if (not self.cp.has_section("server")):
        self.cp.add_section("server")
      self.cp.set("server", "host",  host)
      self.cp.set("server", "port",  port)
      self.cp.set("server", "database",  db)
      self.cp.set("server", "user",  user)
      try:
        self.cp.write(open(self.configdir() + os.sep + '.pgworksheet', "w"))
      except IOError:
        pass
      self.widget("txtSQL").grab_focus()
      self.execute = Execute(self.db)
      self.run = RunSQL(self.execute,
                self.widget("txtSQL"),
                self.widget("tabResult"),
                self.status_result)


  def set_proportional(self, widget):
    """Change the font of a widget to proportional"""
    w = self.widget(widget)
    font = w.get_buffer().create_tag(widget + "Font")
    if (mswindows()):
      font.set_property('font', 'Courier New 10')
    else:
      font.set_property('font', 'Monospace 10')
    w.ensure_style()
    return font
    

  def status(self, connect, version):
    """Update the status bar text"""
    self.status_connect.set_markup(" " + connect + " ")
    self.status_version.set_markup(" " + version + " ")


  def add_prevstatement(self, sql):
    """Add a query to the prev query lifo"""
    if (len(self.prev_statements) > 0):
      prev = self.prev_statements[len(self.prev_statements)-1]
      if (prev == sql):
        return
    self.prev_statements.append(sql)


  def save_history(self):  
    """Save the history in a text file"""
    try:
      fd = open(self.configdir() + os.sep + '.pgworksheet_history', "w")
      self.add_prevstatement(self.get_widget_text("txtSQL"))
      for sql in self.prev_statements:
        fd.write("\n#$#\n")
        fd.write(sql)
      for sql in self.next_statements:
        fd.write("\n#$#\n")
        fd.write(sql)
      fd.close()
    except IOError:
      pass


  def load_history(self):
     """Load the history from a text file"""
     try:
       fd = open(self.configdir() + os.sep + '.pgworksheet_history', "r")
       sql = ""
       for line in fd:
         line = string.strip(line)
         if (line == "") :
           continue
         if (line == "#$#"):
           if (len(sql) > 0):
             self.prev_statements.append(sql)
             sql = ""
           continue
         sql += line
         sql += '\n'
       if (len(sql) > 0):
           self.prev_statements.append(sql)
     except IOError:
       pass


  def on_Paste(self, *args):
    if (self.widget('txtSQL').is_focus()):
      w = self.widget("txtSQL")
      self.on_SQLKeyPress(w, None)
      w.emit('paste-clipboard');


  def on_Copy(self, *args):
    if (self.widget('txtSQL').is_focus()):
      w = self.widget("txtSQL")
      w.emit('copy-clipboard');


  def on_Cut(self, *args):
    if (self.widget('txtSQL').is_focus()):
      w = self.widget("txtSQL")
      self.on_SQLKeyPress(w, None)
      w.emit('cut-clipboard');

    
  def on_SelectAll(self, *args):
    if (self.widget('txtSQL').is_focus()):
      w = self.widget("txtSQL")
    else:
      w = self.widget("txtSQL")
      w.grab_focus()
    buffer = w.get_buffer()
    buffer.move_mark_by_name('selection_bound', buffer.get_start_iter());
    buffer.move_mark_by_name('insert', buffer.get_end_iter());


  def on_RunSQL(self, widget):
    """Run the SQL query"""
    if (not self.is_connected()):
      self.error_box("Not connected to a database")
      return
    self.prev_saved = 0
    self.window.window.set_cursor(gtk.gdk.Cursor(gtk.gdk.WATCH))
    self.run.run()
    self.window.window.set_cursor(None)


  def on_SQLKeyPress(self, widget, event):
    if (event is None) : return
    if (event.keyval != 65507):
      if (self.prev_saved == 0):
        self.add_prevstatement(self.get_widget_text(widget))
        self.prev_saved = 1


  def on_PrevSQL(self, *args):
    if (len(self.prev_statements) > 0):
      s = self.prev_statements.pop()
      self.next_statements.append(self.get_widget_text("txtSQL"))
      self.set_widget_text("txtSQL", s)


  def on_NextSQL(self, *args):
    if (len(self.next_statements) > 0):
      s = self.next_statements.pop()
      self.prev_statements.append(self.get_widget_text("txtSQL"))
      self.set_widget_text("txtSQL", s)


  def on_SetSQLFont(self, *args):
    o = args[0].get_buffer()
    c1 = o.get_start_iter()
    c2 = o.get_end_iter()
    o.apply_tag(self.txtSQLFont,c1,c2)


  def on_Quit(self, widget):
    self.on_Disconnect(None)
    self.save_history()
    gtk.main_quit()

  
  def on_PanedAccept(self, widget, event):
    if (not self.cp.has_section("window")):
      self.cp.add_section("window")
    self.cp.set("window", "divider",  str(widget.get_position()))
    try:
      self.cp.write(open(self.configdir() + os.sep + '.pgworksheet', "w"))
    except IOError:
      pass


  def on_WindowState(self, widget, event):
    if (not self.cp.has_section("window")):
      self.cp.add_section("window")
    if (event.new_window_state == gtk.gdk.WINDOW_STATE_MAXIMIZED):
      self.cp.set("window", "maximized",  "1")
    else:
      self.cp.set("window", "maximized",  "0")
    try:
      self.cp.write(open(self.configdir() + os.sep + '.pgworksheet', "w"))
    except IOError:
      pass


  def on_WindowConfigure(self, widget, event):
    if (not self.cp.has_section("window")):
      self.cp.add_section("window")
    self.cp.set("window", "x",  str(event.x))
    self.cp.set("window", "y",  str(event.y))
    self.cp.set("window", "width",  str(event.width))
    self.cp.set("window", "height",  str(event.height))
    try:
      self.cp.write(open(self.configdir() + os.sep + '.pgworksheet', "w"))
    except IOError:
      pass


  def on_ConnectServer(self, *args):
    entry, button = self.widget("database").get_children()
    button.hide()
    self.connect_server.show_all()


  def on_ConnectCancel(self, *args):
    self.connect_server.hide()


  def on_ConnectDelete(self, *args):
    self.connect_server.hide()
    return 1


  def on_ConnectNow(self, *args):
    self.window.window.set_cursor(gtk.gdk.Cursor(gtk.gdk.WATCH))
    host = self.widget("host").get_text()
    port = self.widget("port").get_text()
    database = self.widget("database").entry.get_text()
    user = self.widget("username").get_text()
    password = self.widget("password").get_text()
    self.connect(host, port, database, user, password)
    self.window.window.set_cursor(None)
    if (self.is_connected()):
      self.connect_server.hide()
    else:
      self.error_box("Error connecting to " + user + ":" + database + "@" + host + ":" + port)
      self.widget("password").grab_focus()


  def on_About(self, *args):
    self.widget("about").show_all()


  def on_AboutClose(self, *args):    
    self.widget("about").hide()


  def on_OpenSQL(self, *args):
    filename = self.openfile_dialog('Select SQL');
    if (filename is not None):
      buf = self.widget("txtSQL").get_buffer();
      buf.set_text("")
      input = open(filename, 'r')
      for line in input:
        buf.insert_at_cursor(unicode(line, get_encoding()))


  def on_Disconnect(self, *args):      
    if (self.is_connected()):
      self.db.disconnect()
    self.db = None
    self.status("not connected", '')
    self.widget("mnuDisconnect").set_sensitive(gtk.FALSE)


  def on_ConnectKeyPress(self, widget, event):
    if ((event.string == "\r") or
        (event.string == "\n")):
      self.widget("btnConnectConnect").emit("clicked")


  def on_DatabaseListClicked(self, widget, event):
    host = self.widget("host").get_text()
    port = self.widget("port").get_text()
    user = self.widget("username").get_text()
    password = self.widget("password").get_text()
    database = self.widget("database").entry.get_text()
    entry, button = self.widget("database").get_children()
    self.window.window.set_cursor(gtk.gdk.Cursor(gtk.gdk.WATCH))
    dbs = [ "can't connet to template1" ]
    self.widget("database").set_popdown_strings(dbs)
    entry.set_text(database)
    self.window.window.set_cursor(None)
    db = DBConnection(host, port, "template1", user, password)
    if (db.is_connected()):
      result = db.query("SELECT datname FROM pg_database");
      try:
        dbs = []
        row = result['cursor'].fetchone()
        while ( row is not None ) :
          dbs.append(row[0]) 
          row = result['cursor'].fetchone()
        self.widget("database").set_popdown_strings(dbs)
        entry.set_text(database)
      except KeyError:
        pass
      db.disconnect()


# create and run the application
PgWorksheet(os.path.dirname(sys.argv[0]))
gtk.main()
