pgworksheet_yvesf/trash/pgworksheet

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8; -*-
   3 #
   4 # PgWorksheet - PostgreSQL Front End
   5 # http://pgworksheet.projects.postgresql.org/
   6 #
   7 # Copyright © 2004-2005 Henri Michelon & CML http://www.e-cml.org/
   8 #
   9 # This program is free software; you can redistribute it and/or
  10 # modify it under the terms of the GNU General Public License
  11 # as published by the Free Software Foundation; either version 2
  12 # of the License, or (at your option) any later version.
  13 #
  14 # This program is distributed in the hope that it will be useful,
  15 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17 # GNU General Public License for more details (read LICENSE.txt).
  18 #
  19 # You should have received a copy of the GNU General Public License
  20 # along with this program; if not, write to the Free Software
  21 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  22 #
  23 # $Id: pgworksheet,v 1.88 2006/01/10 21:31:35 hmichelon Exp $
  24 #
  25 import os
  26 import sys
  27 import string
  28 import gettext
  29 import locale
  30 import ConfigParser
  31 #this is needed for py2exe
  32 if sys.platform == 'win32':
  33   #win32 platform, add the "lib" folder to the system path
  34   os.environ['PATH'] += ";gtk/bin;gtk/lib;postgresql;"
  35   import pygtk
  36 else:
  37   import pygtk
  38   #not win32, ensure version 2.0 of pygtk is imported
  39   pygtk.require('2.0')
  40 import gtk
  41
  42 import pgw
  43 import pgw.UI
  44 import pgw.RunSQL
  45 import pgw.Execute
  46 import pgw.DBConnection
  47
  48 # maximum entries in the SQL queries history
  49 PGW_MAX_HISTORY             = 100
  50 # maximum entries in the connection parameters history
  51 PGW_MAX_CONNECTION_HISTORY  = 5
  52
  53
  54 class PgWorksheet:
  55
  56   def __init__(self, app_name, app_version, pixmap_path, locale_path):
  57     # Initialize I18N
  58     if (pgw.mswindows()):
  59       try:
  60         # try to get the default language
  61         lang = gettext.translation(app_name, locale_path,
  62               [ locale.getdefaultlocale()[0] ])
  63         lang.install()
  64       except IOError:
  65         # fallback to the default method
  66         gettext.bindtextdomain(app_name, locale_path)
  67         gettext.textdomain(app_name)
  68         gettext.install(app_name, locale_path, unicode=1)
  69     else:
  70       gettext.bindtextdomain(app_name, locale_path)
  71       gettext.textdomain(app_name)
  72       gettext.install(app_name, locale_path, unicode=1)
  73     # Build UI
  74     self.ui = pgw.UI.UI(self, app_name, app_version, pixmap_path)
  75     # Default connection state : not connected
  76     self.db = None
  77     self.disconnect()
  78     # Initialize prev/next query lifos
  79     self.prev_statements = []
  80     self.next_statements = []
  81     self.load_history()
  82     self.prev_saved = 1
  83     # Display connection dialog on startup
  84     self.on_menu_connect(None)
  85     # Start application
  86     gtk.main()
  87
  88
  89   def add_prevstatement(self, sql):
  90     """Add a query to the previous queries lifo"""
  91     # do not add the same query two times
  92     if (len(self.prev_statements) > 0):
  93       prev = self.prev_statements[len(self.prev_statements)-1]
  94       if (prev == sql): return
  95     # add the query to the lifo
  96     self.prev_statements.append(sql)
  97     self.ui.enable_prevsql(len(self.prev_statements) > 0)
  98
  99
 100   def get_history_path(self):
 101     """Returns the path to the configuration file"""
 102     return os.path.join(pgw.get_user_configdir(), '.pgworksheet_history');
 103
 104
 105   def save_history(self):
 106     """Save the history in a text file"""
 107     try:
 108       fd = open(self.get_history_path(), 'w')
 109       self.add_prevstatement(self.ui.get_sqlbuffer_text())
 110       for sql in self.prev_statements:
 111         fd.write("\n#$#\n")
 112         fd.write(string.rstrip(sql))
 113       for sql in self.next_statements:
 114         fd.write("\n#$#\n")
 115         fd.write(string.rstrip(sql))
 116       fd.write("\n#$#\n")
 117       fd.close()
 118     except IOError:
 119       pass
 120
 121
 122   def load_history(self):
 123    """Load the history from a text file"""
 124    try:
 125      fd = open(self.get_history_path(), 'r')
 126      sql = ''
 127      count = 0
 128      for line in fd:
 129        line = string.rstrip(line)
 130        if (line == '') :
 131          continue
 132        try:
 133          line = unicode(line, 'UTF-8')
 134        except UnicodeDecodeError:
 135          try:
 136            line = unicode(line, pgw.get_user_encoding())
 137          except UnicodeDecodeError:
 138            pass
 139        if (line == '#$#'):
 140          if (len(sql) > 0):
 141            self.prev_statements.append(sql)
 142            count = count + 1
 143            sql = ''
 144          continue
 145        sql += line
 146        sql += '\n'
 147      if (len(sql) > 0):
 148        self.prev_statements.append(sql)
 149      fd.close()
 150      if (count > PGW_MAX_HISTORY):
 151        self.prev_statements = self.prev_statements[count - PGW_MAX_HISTORY : count]
 152      self.ui.enable_prevsql(len(self.prev_statements) > 0)
 153    except IOError:
 154      pass
 155
 156
 157   def is_connected(self):
 158     """Return TRUE if connected to a database"""
 159     if (self.db is None):
 160       return None
 161     else:
 162       return self.db.is_connected()
 163
 164
 165   def connect(self, host = None, port = None, db = None,
 166               user = None, password = None):
 167     """Connect to a database"""
 168     self.ui.wndmain.window.set_cursor(gtk.gdk.Cursor(gtk.gdk.WATCH))
 169     self.ui.status(_('Trying to connect as <b>%(user)s</b> to <b>%(db)s</b> on <b>%(host)s</b>') %
 170         {'user':user, 'db':db, 'host':host}, _('connecting...'))
 171     while (gtk.events_pending() == True):
 172       gtk.main_iteration_do(False)
 173     # Disconnect the re-connect
 174     self.disconnect()
 175     self.db = pgw.DBConnection.DBConnection(host, port, db, user, password)
 176     # we are connected
 177     if (self.is_connected()):
 178       # update the UI to reflect the connection state
 179       self.ui.enable_disconnect()
 180       self.ui.enable_runsql()
 181       self.ui.status(_('connected as <b>%(user)s</b> to <b>%(db)s</b> on <b>%(host)s</b>') %
 182           {'user':user, 'db':db, 'host':host}, self.db.pgversion())
 183       new_conn = "%s,%s,%s,%s" % (host, port, db, user)
 184       # update the connection history
 185       n = 0
 186       for conn in self.all_connections:
 187         # remove the connection from the history if it already exists
 188         if (conn == new_conn):
 189           self.all_connections.pop(n)
 190           break
 191         n = n + 1
 192       # add the connection to the history, making it the first of the list
 193       self.all_connections.insert(0, new_conn)
 194       # save the connection history in the config file
 195       cp = ConfigParser.ConfigParser()
 196       try:
 197         cp.readfp(open(pgw.get_config_path(), 'r'))
 198       except IOError:
 199         pass
 200       if (not cp.has_section("connections")):
 201         cp.add_section("connections")
 202       n = 0
 203       while ((n <= PGW_MAX_CONNECTION_HISTORY) and
 204              (n < len(self.all_connections))):
 205         cp.set("connections", "conn%d" % (n + 1), self.all_connections[n])
 206         n = n + 1
 207       try:
 208         cp.write(open(pgw.get_config_path(), 'w'))
 209       except IOError:
 210         pass
 211       # ready to type queries, give the focus to the text field
 212       self.ui.setfocus_sqlbuffer()
 213       # initialize the objects used to execute the queries
 214       self.execute = pgw.Execute.Execute(self.db)
 215       self.run = pgw.RunSQL.RunSQL(self.execute,
 216                                   self.ui.sqlview,
 217                                   self.ui.resulttab)
 218     self.ui.wndmain.window.set_cursor(None)
 219
 220
 221   def disconnect(self):
 222     """Disconnect from the current database"""
 223     # disconnect from the database
 224     if (self.is_connected()): self.db.disconnect()
 225     # destroy the objects used for this connection
 226     self.db = None
 227     self.execute = None
 228     self.run = None
 229     # update the UI to reflect the connection state
 230     self.ui.status(_('not connected'), 'PgWorksheet v' + app_version)
 231     self.ui.enable_disconnect(False)
 232     self.ui.enable_runsql(False)
 233
 234
 235   def on_wndmain_destroy(self, widget):
 236     """Called when the application quits"""
 237     self.disconnect()
 238     self.save_history()
 239     gtk.main_quit()
 240     sys.exit(0)
 241
 242
 243   def on_wndmain_delete(self, widget, event):
 244     """Called when the user wants to close the main window"""
 245     return False
 246
 247
 248   def on_menu_connect(self, widget):
 249     """Called when the user want the connection dialog box"""
 250     # fill the connection dialog box with default parameters
 251     if os.environ.has_key('USERNAME'):
 252       self.username = os.environ['USERNAME']
 253     elif os.environ.has_key('USER'):
 254       self.username = os.environ['USER']
 255     else:
 256       self.username = ""
 257     host = 'localhost'
 258     port = '5432'
 259     database = 'template1'
 260     username = 'pgsql'
 261     self.display_connect_dialog(host, port, username, database, 0)
 262
 263
 264   def display_connect_dialog(self, host, port, username, database, overwrite_entry):
 265     # display and execute the connection dialog box
 266     params = self.ui.connect_dialog(self, host, port, username, database, overwrite_entry)
 267     # check if the user have clicked "Cancel"
 268     if (params is not None):
 269       # connect to the database
 270       host, port, username, passwd, database = params;
 271       self.connect(host, port, database, username, passwd)
 272       # error connecting to the database, retry
 273       if (not self.is_connected()):
 274         self.ui.error_box(_('Error connecting to %s:%s@%s:%s') %
 275                            (username, database, host, port))
 276         self.display_connect_dialog(host, port, username, database, 1)
 277
 278
 279   def on_dlgconnect_map(self, widget):
 280     """Called when the connection dialog box is displayed"""
 281     # clear the connections history
 282     self.all_connections = []
 283     # load the connections history from the config file
 284     cp = ConfigParser.ConfigParser()
 285     try :
 286       cp.readfp(open(pgw.get_config_path(), 'r'))
 287       n = 1
 288       while n <= PGW_MAX_CONNECTION_HISTORY:
 289         try:
 290           line = cp.get("connections", "conn%d" % n)
 291           # add the connection to the connections history
 292           self.all_connections.append(line)
 293           host, port, db, user = string.split(line, ',')
 294           # add the connections to the connections history list of the dialog box
 295           self.ui.storeconn.append(["%s:%s@%s" % (user, db, host), line])
 296           n = n + 1
 297         except:
 298           break
 299       # if we have at least one connection in the history, made it the default
 300       if (n > 1) :
 301         # select the last used connection
 302         self.ui.viewconn.set_cursor(self.ui.storeconn.get_path(
 303             self.ui.storeconn.get_iter_first()))
 304     except IOError:
 305       pass
 306
 307
 308   def on_dlgconnect_change(self, treeview):
 309     """Called when the user choose a connection in the connection history list"""
 310     # fill the connection dialog with the selected connection parameters
 311     model, iter = treeview.get_selection().get_selected()
 312     host, port, db, user = string.split(model.get(iter, 1)[0], ',')
 313     self.ui.entry_host.set_text(host)
 314     self.ui.entry_port.set_text(port)
 315     self.ui.entry_database.set_text(db)
 316     self.ui.entry_user.set_text(user)
 317     self.ui.entry_password.set_text('')
 318
 319
 320   def on_menu_disconnect(self, widget):
 321     """Called when the user wants to disconnect from the database"""
 322     self.disconnect()
 323
 324
 325   def on_menu_opensql(self, widget):
 326     """The user wants to open a file with some queries"""
 327     filename = self.ui.file_dialog(_('Select a SQL text file'));
 328     if (filename is not None):
 329       self.ui.undo.lock = True
 330       for handler in self.ui.buffer_handlers:
 331         self.ui.sqlbuffer.handler_block(handler)
 332       self.ui.set_sqlbuffer_text('')
 333       try:
 334         input = open(filename, 'r')
 335         for line in input:
 336           try:
 337             self.ui.sqlbuffer.insert_at_cursor(unicode(line, 'UTF-8'))
 338           except UnicodeDecodeError:
 339             try:
 340               self.ui.sqlbuffer.insert_at_cursor(unicode(line, pgw.get_user_encoding()))
 341             except UnicodeDecodeError:
 342               self.ui.sqlbuffer.insert_at_cursor(line)
 343       except IOError:
 344         self.ui.error_box(_('Error while opening or reading from %s') %filename)
 345       for handler in self.ui.buffer_handlers:
 346         self.ui.sqlbuffer.handler_unblock(handler)
 347       self.ui.undo.reset()
 348       self.ui.undo.lock = False
 349       self.ui.syntax.refresh()
 350       pgw.set_proportional(self.ui.sqlbuffer)
 351
 352
 353   def file_overwrite(self, title):
 354     """Display a "Save As" dialopg box and prompt a confirmation if the selected file exists"""
 355     filename = self.ui.file_dialog(title, gtk.FILE_CHOOSER_ACTION_SAVE,
 356                                    gtk.STOCK_SAVE_AS);
 357     if (filename is not None):
 358       try:
 359         os.stat(filename)
 360         if (self.ui.yesno_box(_('%s already exists, overwrite ?') % filename) ==
 361             gtk.RESPONSE_YES):
 362           return filename
 363         return self.file_overwrite(title)
 364       except OSError: # file does not exists
 365         return filename
 366     return None
 367
 368
 369   def on_menu_savesql(self, widget):
 370     """The user wants to save his queries"""
 371     filename = self.file_overwrite(_('Save SQL queries'))
 372     if (filename is not None):
 373       try:
 374        output = open(filename, 'w')
 375        output.write(self.ui.get_sqlbuffer_text())
 376       except IOError:
 377         self.ui.error_box(_('Error while creating or writing %s') % filename)
 378
 379
 380   def save_list_row(self, model, path, iter, output):
 381     """Save a row of a TreeView in a tabular form"""
 382     col = 0
 383     while (col < model.get_n_columns()):
 384       val = string.replace(model.get_value(iter, col), '"', '\"')
 385       output.write('"' + val + '"')
 386       col = col + 1
 387       if (col < model.get_n_columns()):
 388         output.write('\t')
 389     output.write('\n')
 390
 391
 392   def saveresults(self, widget, output):
 393     """Save the content of a TreeView to a tab separated file"""
 394     widget = widget.get_child()
 395     if (isinstance(widget, gtk.TextView)):
 396       buffer = widget.get_buffer()
 397       output.write(buffer.get_text(buffer.get_start_iter(),
 398                                    buffer.get_end_iter()))
 399     elif (isinstance(widget, gtk.TreeView)):
 400       widget.get_model().foreach(self.save_list_row, output)
 401
 402
 403   def on_menu_saveallresults(self, widget):
 404     """The user wants to save ALL the results"""
 405     if (self.ui.resulttab.get_n_pages() > 0):
 406       filename = self.file_overwrite(_('Save all the results'))
 407       if (filename is not None):
 408         try:
 409           output = open(filename, 'w')
 410           page = 0
 411           while page < self.ui.resulttab.get_n_pages() :
 412             self.saveresults(self.ui.resulttab.get_nth_page(page), output)
 413             page = page + 1
 414         except IOError:
 415           self.ui.error_box(_('Error while creating or writing %s') % filename)
 416
 417
 418   def on_menu_saveresults(self, widget):
 419     """The user wants to save the current result"""
 420     if (self.ui.resulttab.get_n_pages() > 0):
 421       filename = self.file_overwrite(_('Save the results'))
 422       if (filename is not None):
 423         try:
 424           output = open(filename, 'w')
 425           self.saveresults(self.ui.resulttab.get_nth_page(
 426                           self.ui.resulttab.get_current_page()), output)
 427         except IOError:
 428           self.ui.error_box(_('Error while creating or writing %s') % filename)
 429
 430
 431   def on_menu_cut(self, widget):
 432     """Cut text to the clipboard"""
 433     w = self.ui.wndmain.get_focus()
 434     if (isinstance(w, gtk.TextView)):
 435       w.emit('cut-clipboard')
 436
 437
 438   def on_menu_copy(self, widget):
 439     """Copy text to the clipboard"""
 440     w = self.ui.wndmain.get_focus()
 441     if (isinstance(w, gtk.TextView)):
 442       w.emit('copy-clipboard')
 443     elif (isinstance(w, gtk.TreeView)):
 444       model, iter = w.get_selection().get_selected()
 445       if (iter is not None):
 446         col = 0
 447         result = ''
 448         while (col < model.get_n_columns()):
 449           val = string.replace(model.get_value(iter, col), '"', '\"')
 450           result = result + val
 451           col = col + 1
 452           if (col < model.get_n_columns()):
 453             result = result + '\t'
 454         clip = gtk.Clipboard()
 455         clip.set_text(result)
 456
 457
 458   def on_menu_paste(self, widget):
 459     """Paste from the clipboard"""
 460     w = self.ui.wndmain.get_focus()
 461     if (isinstance(w, gtk.TextView)):
 462       w.emit('paste-clipboard')
 463
 464
 465   def on_menu_selectall(self, widget):
 466     """Select the entire text"""
 467     w = self.ui.wndmain.get_focus()
 468     if (isinstance(w, gtk.TextView)):
 469       buffer = w.get_buffer()
 470       buffer.move_mark_by_name('selection_bound', buffer.get_start_iter())
 471       buffer.move_mark_by_name('insert', buffer.get_end_iter())
 472
 473
 474   def on_sqlview_focus_in(self, widget, event):
 475     self.ui.enable_cut()
 476     self.ui.enable_paste()
 477
 478
 479   def on_sqlview_focus_out(self, widget, event):
 480     self.ui.enable_cut(False)
 481     self.ui.enable_paste(False)
 482
 483
 484   def on_sqlview_keypress(self, widget, event):
 485     """Save the last statement in the history
 486     if needed (after an execution"""
 487     if (event is None) : return
 488     if (event.keyval != 65507):
 489       if (self.prev_saved == 0):
 490         self.add_prevstatement(self.ui.get_sqlbuffer_text())
 491         self.prev_saved = 1
 492
 493
 494   def on_menu_about(self, widget):
 495     self.ui.about_dialog()
 496
 497
 498   def on_menu_runsql(self, widget):
 499     """Execute the SQL queries"""
 500     if (not self.is_connected()):
 501       if (self.ui.yesno_box(_('Not connected to a database.\nDo you want to connect now ?')) ==
 502                               gtk.RESPONSE_NO):
 503         return
 504       self.on_menu_connect(widget)
 505       if (not self.is_connected()):
 506         return
 507     self.on_text_change(widget)
 508     self.prev_saved = 0
 509     self.ui.wndmain.window.set_cursor(gtk.gdk.Cursor(gtk.gdk.WATCH))
 510     self.run.run()
 511     self.ui.enable_saveresult(self.ui.resulttab.get_n_pages() > 0)
 512     self.ui.wndmain.window.set_cursor(None)
 513
 514
 515   def on_menu_prevsql(self, widget):
 516     """Display the previous statement from the history"""
 517     self.ui.undo.lock = True
 518     if (len(self.prev_statements) > 0):
 519       s = self.prev_statements.pop()
 520       self.next_statements.append(self.ui.get_sqlbuffer_text())
 521       self.ui.set_sqlbuffer_text(s)
 522       self.prev = s
 523     self.ui.enable_prevsql(len(self.prev_statements) > 0)
 524     self.ui.enable_nextsql(len(self.next_statements) > 0)
 525     self.ui.undo.lock = False
 526
 527
 528   def on_menu_nextsql(self, widget):
 529     """Display the next statement from the history"""
 530     self.ui.undo.lock = True
 531     if (len(self.next_statements) > 0):
 532       s = self.next_statements.pop()
 533       self.prev_statements.append(self.ui.get_sqlbuffer_text())
 534       self.ui.set_sqlbuffer_text(s)
 535       self.prev = s
 536     self.ui.enable_prevsql(len(self.prev_statements) > 0)
 537     self.ui.enable_nextsql(len(self.next_statements) > 0)
 538     self.ui.undo.lock = False
 539
 540
 541   def on_text_change(self, widget):
 542     """The text have been changed after navigation the history"""
 543     if (self.ui.undo.lock):
 544       return
 545     if (len(self.next_statements) > 0):
 546       if (self.next_statements[0] == ''):
 547         self.next_statements.pop(0)
 548       self.prev_statements.append(self.prev)
 549       for i in reversed(self.next_statements):
 550         self.prev_statements.append(i)
 551       self.next_statements = []
 552     self.ui.enable_prevsql(len(self.prev_statements) > 0)
 553     self.ui.enable_nextsql(len(self.next_statements) > 0)
 554
 555
 556 # Application parameters
 557 app_name    = 'pgworksheet'
 558 app_version = '1.8.1'
 559 # Default pixmap path
 560 pixmap_path = '@PIXMAP_PATH@'
 561 # Find current pixmap path
 562 if (not os.access(os.path.join(pixmap_path, 'pgworksheet-32.png'), os.F_OK)):
 563   pixmap_path = os.path.join(sys.prefix, 'share/pixmaps/pgworksheet')
 564 if (not os.access(os.path.join(pixmap_path, 'pgworksheet-32.png'), os.F_OK)):
 565   pixmap_path = os.path.join(os.path.dirname(sys.argv[0]), 'pixmaps/pgworksheet')
 566 # Find current locale path
 567 locale_path = '@LOCALE_PATH@'
 568 if (not os.access(os.path.join(locale_path, 'fr/LC_MESSAGES/pgworksheet.mo'), os.F_OK)):
 569   locale_path = os.path.join(sys.prefix, 'share/locale')
 570 if (not os.access(os.path.join(locale_path, 'fr/LC_MESSAGES/pgworksheet.mo'), os.F_OK)):
 571   locale_path = os.path.join(os.path.dirname(sys.argv[0]), 'locale')
 572 # Start program
 573 p = None
 574 try:
 575   p = PgWorksheet(app_name, app_version, pixmap_path, locale_path)
 576 except KeyboardInterrupt:
 577   if (p is not None):
 578     p.on_wndmain_destroy(None)