pgworksheet_1.9/pgworksheet

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