pgworksheet_1.9/pgw/RunSQL.py

   1 # -*- coding: latin-1; -*-
   2 #
   3 # PgWorksheet - PostgreSQL Front End
   4 # http://pgworksheet.projects.postgresql.org/
   5 #
   6 # Copyright © 2004-2008 Henri Michelon & CML http://www.e-cml.org/
   7 #
   8 # This program is free software; you can redistribute it and/or
   9 # modify it under the terms of the GNU General Public License
  10 # as published by the Free Software Foundation; either version 2
  11 # of the License, or (at your option) any later version.
  12 #
  13 # This program is distributed in the hope that it will be useful,
  14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16 # GNU General Public License for more details (read LICENSE.txt).
  17 #
  18 # You should have received a copy of the GNU General Public License
  19 # along with this program; if not, write to the Free Software
  20 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  21 #
  22 # $Id
  23 #
  24 import string
  25 import pygtk
  26 import gtk
  27
  28 import pgw
  29
  30
  31 class RunSQL:
  32   """Execute the SQL buffer and fill the GtkNotebook with the results."""
  33
  34   def __init__(self, execute, txtSQL, tabResult, result):
  35     self.execute = execute
  36     self.txtSQL = txtSQL
  37     self.lblResult = result
  38     self.tabResult = tabResult
  39     self.error_font = {}
  40
  41
  42   def result(self, msg):
  43     """update status bar text"""
  44     self.lblResult.set_markup('<span foreground="#004400">' + msg + '</span>')
  45
  46
  47   def str_limit(self, s, max = 20):
  48     s = string.replace(s, '\\\\', '\\')
  49     if (len(s) > (max + 3)):
  50       return s[:max] + "..."
  51     return s
  52
  53
  54   def error(self, msg, title = None):
  55     """update the error messages"""
  56     if (title is None):
  57       title = '<span foreground="#880000">' + _('Errors') + '</span>'
  58     scroll = gtk.ScrolledWindow()
  59     txt = gtk.TextView()
  60     txt.get_buffer().set_text(unicode(msg, pgw.get_user_encoding()))
  61     pgw.set_proportional(txt.get_buffer())
  62     txt.set_editable(False)
  63     scroll.add(txt)
  64     scroll.show_all()
  65     label = gtk.Label()
  66     label.set_markup(title)
  67     self.tabResult.append_page(scroll, label)
  68
  69
  70   def add_treeview(self, title):
  71     """Add a new tree view"""
  72     scroll = gtk.ScrolledWindow()
  73     tvw = gtk.TreeView()
  74     scroll.add(tvw)
  75     scroll.show()
  76     label = gtk.Label(title)
  77     self.tabResult.append_page(scroll, label)
  78     return (tvw, label)
  79
  80
  81   def fill_treeview(self, sql, result):
  82     """build the tree view columns and rows"""
  83     sql = sql.strip()
  84     treeview, label = self.add_treeview(self.str_limit(sql))
  85     type_str = ""
  86     cell = gtk.CellRendererText()
  87     ncol = 0
  88     for col in result.description:
  89       column = gtk.TreeViewColumn(string.replace(col[0], "_", "__"))
  90       column.pack_start(cell, True)
  91       column.add_attribute(cell, 'text', ncol)
  92       column.set_sort_column_id(ncol)
  93       treeview.append_column(column)
  94       ncol = ncol + 1
  95       if (type_str == ""):
  96         type_str = "str"
  97       else:
  98         type_str = type_str + ",str"
  99       pass
 100
 101     # dynamically create the liststore
 102     code = "liststore = gtk.ListStore(" + type_str + ")"
 103     exec compile(code, "<string>", 'exec')
 104
 105     # fill the tree view
 106     row = result.fetchone()
 107     rows = 0
 108     while ( row is not None ) :
 109       c = 0
 110       while c < len(row):
 111         if (row[c] is None) and type(row) == list:
 112           row[c] = '(null)'
 113         c = c + 1
 114       liststore.append(row)
 115       row = result.fetchone()
 116       rows = rows + 1
 117     result.close()
 118     treeview.set_model(liststore)
 119     treeview.set_reorderable(True)
 120     treeview.columns_autosize()
 121     treeview.show()
 122     label.set_markup(label.get_text() + ' <span foreground="#555555">: ' +
 123                       str(rows) + "</span>")
 124     return rows
 125
 126
 127   def run(self):
 128     """Run the query and update the results"""
 129     # clear the treeviews and the error text buffers
 130     for child in self.tabResult.get_children():
 131       self.tabResult.remove(child)
 132     self.result(_("Please wait, executing the query and fetching the results..."))
 133     # update the display
 134     while (gtk.events_pending() == True):
 135       gtk.main_iteration_do(False)
 136
 137     # execute the query
 138     sqlbuffer=  self.txtSQL.get_buffer()
 139     notices = []
 140     try:
 141       result = self.execute.execute(sqlbuffer)
 142       rows = 0
 143       if (isinstance(result, list)):
 144         # multiple queries and multiple results...
 145         parts = self.execute.split(sqlbuffer)
 146         sqls = []
 147         have_errors = 0
 148         for sql in parts :
 149           sql = string.strip(sql)
 150           if (len(sql) > 0) :
 151             sqls.append(sql)
 152         for res in result:
 153           sql = sqls.pop(0)
 154           try:
 155             if (res['cursor'].description is not None):
 156               rows += self.fill_treeview(sql, res['cursor'])
 157             else:
 158               rows += res['cursor'].rowcount
 159             notices = res['notices']
 160           except KeyError:
 161             try:
 162               self.error(res['text'], "psql : " + self.str_limit(sql))
 163             except KeyError:
 164               notices = res['notices']
 165               self.error(res['error'],
 166                           '<span foreground="#880000">' + _('Errors :') + '</span> ' +
 167                           self.str_limit(sql))
 168               have_errors += 1
 169         self.tabResult.set_current_page(0)
 170         if (rows > 1):
 171           res_str = _("%d results or affected rows") % rows
 172         elif (rows == 1):
 173           res_str = _("1 result or affected row")
 174         else:
 175           res_str = _("No result or no affected row")
 176         if (have_errors > 1) :
 177           res_str += ', <span foreground="#880000">' + \
 178                     _('%d queries failed') % have_errors + '</span>'
 179         elif (have_errors > 0) :
 180           res_str += ', <span foreground="#880000">' +  \
 181                     _('1 query failed') + '</span>'
 182         self.result(res_str)
 183       else: # one query
 184         sql = sqlbuffer.get_text(sqlbuffer.get_start_iter(),
 185                                  sqlbuffer.get_end_iter())
 186         try:
 187           if (result is None):
 188             self.result(_("No result"))
 189           elif (result['cursor'].description is None):
 190             # not a SELECT
 191             rows = result['cursor'].rowcount
 192             notices = result['notices']
 193             if (rows > 1):
 194               self.result(_("%d rows affected") % rows)
 195             elif (rows == 1):
 196               self.result(_("1 row affected"))
 197             else:
 198               self.result(_("No row affected"))
 199           else:
 200             # only one SELECT
 201             rows = self.fill_treeview(sql, result['cursor'])
 202             notices = result['notices']
 203             if (rows > 1):
 204               self.result(_("%d results") % rows)
 205             elif (rows == 1):
 206               self.result(_("1 result"))
 207             else:
 208               self.result(_("No result"))
 209         except KeyError:
 210           try:
 211             self.error(result['text'], "psql : " + self.str_limit(sql))
 212             self.result("")
 213           except KeyError:
 214             self.error(result['error'])
 215             self.result('<span foreground="#880000">' + _('query failed') + '</span>')
 216             notices = result['notices']
 217       buffer = self.txtSQL.get_buffer()
 218       buffer.move_mark_by_name('selection_bound', buffer.get_start_iter());
 219       buffer.move_mark_by_name('insert', buffer.get_end_iter());
 220
 221     except KeyError, errstr:
 222       self.result('<span foreground="#880000">' + _('query failed') + '</span>')
 223       self.error(str(errstr))
 224
 225     if (len(notices) > 0):
 226       msg = ""
 227       while len(notices):
 228         msg += notices.pop()
 229       self.error(msg, '<span foreground="#000088">'+ _("log") + '</span>')
 230
 231     # restore the focus
 232     self.txtSQL.grab_focus()
 233