GRASS Programmer's Manual  6.4.2(2012)
sqlbuilder.py
Go to the documentation of this file.
00001 """!
00002 @package sqlbuilder.py
00003 
00004 @brief GRASS SQL Builder
00005 
00006 Classes:
00007  - SQLFrame
00008 
00009 Usage:
00010 @code
00011 python sqlbuilder.py vector_map
00012 @endcode
00013 
00014 (C) 2007-2009 by the GRASS Development Team
00015 
00016 This program is free software under the GNU General Public
00017 License (>=v2). Read the file COPYING that comes with GRASS
00018 for details.
00019 
00020 @author Jachym Cepicky <jachym.cepicky gmail.com> (original author)
00021 @author Martin Landa <landa.martin gmail.com>
00022 @author Hamish Bowman <hamish_b yahoo com>
00023 """
00024 
00025 import os
00026 import sys
00027 import time
00028 
00029 ### i18N
00030 import gettext
00031 gettext.install('grasswxpy', os.path.join(os.getenv("GISBASE"), 'locale'), unicode=True)
00032 
00033 import globalvar
00034 import wx
00035 
00036 import grass.script as grass
00037 
00038 import gcmd
00039 import dbm_base
00040 
00041 class SQLFrame(wx.Frame):
00042     """!SQL Frame class"""
00043     def __init__(self, parent, title, vectmap, id = wx.ID_ANY,
00044                  layer = 1, qtype = "select", evtheader = None):
00045         
00046         wx.Frame.__init__(self, parent, id, title)
00047         
00048         self.SetIcon(wx.Icon(os.path.join(globalvar.ETCICONDIR, 'grass_sql.ico'),
00049                              wx.BITMAP_TYPE_ICO))
00050         
00051         self.parent = parent
00052         self.evtHeader = evtheader
00053 
00054         #
00055         # variables
00056         #
00057         self.vectmap = vectmap # fullname
00058         if not "@" in self.vectmap:
00059             self.vectmap = self.vectmap + "@" + grass.gisenv()['MAPSET']
00060         self.mapname, self.mapset = self.vectmap.split("@", 1)
00061         
00062         # db info
00063         self.layer = layer
00064         self.dbInfo = dbm_base.VectorDBInfo(self.vectmap)
00065         self.tablename = self.dbInfo.GetTable(self.layer)
00066         self.driver, self.database = self.dbInfo.GetDbSettings(self.layer)
00067         
00068         self.qtype = qtype      # type of query: SELECT, UPDATE, DELETE, ...
00069         self.colvalues = []     # array with unique values in selected column
00070 
00071         # set dialog title
00072         self.SetTitle(_("GRASS SQL Builder (%(type)s): vector map <%(map)s>") % \
00073                           { 'type' : self.qtype.upper(), 'map' : self.vectmap })
00074         
00075         self.panel = wx.Panel(parent = self, id = wx.ID_ANY)
00076 
00077         # statusbar
00078         self.statusbar = self.CreateStatusBar(number=1)
00079         self.statusbar.SetStatusText(_("SQL statement not verified"), 0)
00080        
00081         self._doLayout()
00082 
00083     def _doLayout(self):
00084         """!Do dialog layout"""
00085       
00086         pagesizer = wx.BoxSizer(wx.VERTICAL)
00087 
00088         
00089         # dbInfo
00090         databasebox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
00091                                    label = " %s " % _("Database connection"))
00092         databaseboxsizer = wx.StaticBoxSizer(databasebox, wx.VERTICAL)
00093         databaseboxsizer.Add(item=dbm_base.createDbInfoDesc(self.panel, self.dbInfo, layer = self.layer),
00094                              proportion=1,
00095                              flag=wx.EXPAND | wx.ALL,
00096                              border=3)
00097 
00098         #
00099         # text areas
00100         #
00101         # sql box
00102         sqlbox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
00103                               label = " %s " % _("Query"))
00104         sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL)
00105 
00106         self.text_sql = wx.TextCtrl(parent = self.panel, id = wx.ID_ANY,
00107                                     value = '', size = (-1, 50),
00108                                     style=wx.TE_MULTILINE)
00109         if self.qtype.lower() == "select":
00110             self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
00111         self.text_sql.SetInsertionPointEnd()
00112         self.text_sql.SetToolTipString(_("Example: %s") % "SELECT * FROM roadsmajor WHERE MULTILANE = 'no' OR OBJECTID < 10")
00113         wx.CallAfter(self.text_sql.SetFocus)
00114 
00115         sqlboxsizer.Add(item = self.text_sql, flag = wx.EXPAND)
00116         
00117         #
00118         # buttons
00119         #
00120         self.btn_clear  = wx.Button(parent = self.panel, id = wx.ID_CLEAR)
00121         self.btn_clear.SetToolTipString(_("Set SQL statement to default"))
00122         self.btn_verify = wx.Button(parent = self.panel, id = wx.ID_ANY,
00123                                     label = _("Verify"))
00124         self.btn_verify.SetToolTipString(_("Verify SQL statement"))
00125         self.btn_apply  = wx.Button(parent = self.panel, id = wx.ID_APPLY)
00126         self.btn_apply.SetToolTipString(_("Apply SQL statement and close the dialog"))
00127         self.btn_close  = wx.Button(parent = self.panel, id = wx.ID_CLOSE)
00128         self.btn_close.SetToolTipString(_("Close the dialog"))
00129         
00130         self.btn_lv = { 'is'    : ['=', ],
00131                         'isnot' : ['!=', ],
00132                         'like'  : ['LIKE', ],
00133                         'gt'    : ['>', ],
00134                         'ge'    : ['>=', ],
00135                         'lt'    : ['<', ],
00136                         'le'    : ['<=', ],
00137                         'or'    : ['OR', ],
00138                         'not'   : ['NOT', ],
00139                         'and'   : ['AND', ],
00140                         'brac'  : ['()', ],
00141                         'prc'   : ['%', ] }
00142         
00143         for key, value in self.btn_lv.iteritems():
00144             btn = wx.Button(parent = self.panel, id = wx.ID_ANY,
00145                             label = value[0])
00146             self.btn_lv[key].append(btn.GetId())
00147         
00148         buttonsizer = wx.FlexGridSizer(cols = 4, hgap = 5, vgap = 5)
00149         buttonsizer.Add(item = self.btn_clear)
00150         buttonsizer.Add(item = self.btn_verify)
00151         buttonsizer.Add(item = self.btn_apply)
00152         buttonsizer.Add(item = self.btn_close)
00153         
00154         buttonsizer2 = wx.GridBagSizer(5, 5)
00155         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['is'][1]), pos = (0,0))
00156         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['isnot'][1]), pos = (1,0))
00157         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['like'][1]), pos = (2, 0))
00158 
00159         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['gt'][1]), pos = (0, 1))
00160         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['ge'][1]), pos = (1, 1))
00161         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['or'][1]), pos = (2, 1))
00162 
00163         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['lt'][1]), pos = (0, 2))
00164         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['le'][1]), pos = (1, 2))
00165         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['not'][1]), pos = (2, 2))
00166 
00167         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['brac'][1]), pos = (0, 3))
00168         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['prc'][1]), pos = (1, 3))
00169         buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['and'][1]), pos = (2, 3))
00170         
00171         #
00172         # list boxes (columns, values)
00173         #
00174         hsizer = wx.BoxSizer(wx.HORIZONTAL)
00175         
00176         columnsbox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
00177                                   label = " %s " % _("Columns"))
00178         columnsizer = wx.StaticBoxSizer(columnsbox, wx.VERTICAL)
00179         self.list_columns = wx.ListBox(parent = self.panel, id = wx.ID_ANY,
00180                                        choices = self.dbInfo.GetColumns(self.tablename),
00181                                        style = wx.LB_MULTIPLE)
00182         columnsizer.Add(item = self.list_columns, proportion = 1,
00183                         flag = wx.EXPAND)
00184 
00185         radiosizer = wx.BoxSizer(wx.HORIZONTAL)
00186         self.radio_cv = wx.RadioBox(parent = self.panel, id = wx.ID_ANY,
00187                                     label = " %s " % _("Add on double-click"),
00188                                     choices = [_("columns"), _("values")])
00189         self.radio_cv.SetSelection(1) # default 'values'
00190         radiosizer.Add(item = self.radio_cv, proportion = 1,
00191                        flag = wx.ALIGN_CENTER_HORIZONTAL | wx.EXPAND, border = 5)
00192 
00193         columnsizer.Add(item = radiosizer, proportion = 0,
00194                         flag = wx.TOP | wx.EXPAND, border = 5)
00195         # self.list_columns.SetMinSize((-1,130))
00196         # self.list_values.SetMinSize((-1,100))
00197 
00198         valuesbox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
00199                                  label = " %s " % _("Values"))
00200         valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL)
00201         self.list_values = wx.ListBox(parent = self.panel, id = wx.ID_ANY,
00202                                       choices = self.colvalues,
00203                                       style = wx.LB_MULTIPLE)
00204         valuesizer.Add(item = self.list_values, proportion = 1,
00205                        flag = wx.EXPAND)
00206         
00207         self.btn_unique = wx.Button(parent = self.panel, id = wx.ID_ANY,
00208                                     label = _("Get all values"))
00209         self.btn_unique.Enable(False)
00210         self.btn_uniquesample = wx.Button(parent = self.panel, id = wx.ID_ANY,
00211                                           label = _("Get sample"))
00212         self.btn_uniquesample.Enable(False)
00213 
00214         buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL)
00215         buttonsizer3.Add(item = self.btn_uniquesample, proportion = 0,
00216                          flag = wx.ALIGN_CENTER_HORIZONTAL | wx.RIGHT, border = 5)
00217         buttonsizer3.Add(item = self.btn_unique, proportion = 0,
00218                          flag = wx.ALIGN_CENTER_HORIZONTAL)
00219 
00220         valuesizer.Add(item = buttonsizer3, proportion = 0,
00221                        flag = wx.TOP, border = 5)
00222         
00223         # hsizer1.Add(wx.StaticText(self.panel,-1, "Unique values: "), border=0, proportion=1)
00224  
00225         hsizer.Add(item = columnsizer, proportion = 1,
00226                    flag = wx.EXPAND)
00227         hsizer.Add(item = valuesizer, proportion = 1,
00228                    flag = wx.EXPAND)
00229         
00230         self.close_onapply = wx.CheckBox(parent = self.panel, id = wx.ID_ANY,
00231                                          label = _("Close dialog on apply"))
00232         self.close_onapply.SetValue(True)
00233  
00234         pagesizer.Add(item = databaseboxsizer,
00235                       flag = wx.ALL | wx.EXPAND, border = 5)
00236         pagesizer.Add(item = hsizer, proportion = 1,
00237                       flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
00238         # pagesizer.Add(self.btn_uniqe,0,wx.ALIGN_LEFT|wx.TOP,border=5)
00239         # pagesizer.Add(self.btn_uniqesample,0,wx.ALIGN_LEFT|wx.TOP,border=5)
00240         pagesizer.Add(item = buttonsizer2, proportion = 0,
00241                       flag = wx.ALIGN_CENTER_HORIZONTAL)
00242         pagesizer.Add(item = sqlboxsizer, proportion = 0,
00243                       flag = wx.EXPAND | wx.LEFT | wx.RIGHT, border = 5)
00244         pagesizer.Add(item = buttonsizer, proportion = 0,
00245                       flag = wx.ALIGN_RIGHT | wx.ALL, border = 5)
00246         pagesizer.Add(item = self.close_onapply, proportion = 0,
00247                       flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
00248 
00249         #
00250         # bindings
00251         #
00252         self.btn_unique.Bind(wx.EVT_BUTTON,       self.OnUniqueValues)
00253         self.btn_uniquesample.Bind(wx.EVT_BUTTON, self.OnSampleValues)
00254         
00255         for key, value in self.btn_lv.iteritems():
00256             self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.OnAddMark)
00257         
00258         self.btn_close.Bind(wx.EVT_BUTTON,       self.OnClose)
00259         self.btn_clear.Bind(wx.EVT_BUTTON,       self.OnClear)
00260         self.btn_verify.Bind(wx.EVT_BUTTON,      self.OnVerify)
00261         self.btn_apply.Bind(wx.EVT_BUTTON,       self.OnApply)
00262 
00263         self.list_columns.Bind(wx.EVT_LISTBOX,   self.OnAddColumn)
00264         self.list_values.Bind(wx.EVT_LISTBOX,    self.OnAddValue)
00265         
00266         self.text_sql.Bind(wx.EVT_TEXT,          self.OnText)
00267         
00268         self.panel.SetAutoLayout(True)
00269         self.panel.SetSizer(pagesizer)
00270         pagesizer.Fit(self.panel)
00271         
00272         self.Layout()
00273         self.SetMinSize((660, 525))
00274         self.SetClientSize(self.panel.GetSize())
00275         self.CenterOnParent()
00276         
00277     def OnUniqueValues(self, event, justsample = False):
00278         """!Get unique values"""
00279         vals = []
00280         try:
00281             idx = self.list_columns.GetSelections()[0]
00282             column = self.list_columns.GetString(idx)
00283         except:
00284             self.list_values.Clear()
00285             return
00286         
00287         self.list_values.Clear()
00288         
00289         querystring = "SELECT %s FROM %s" % (column, self.tablename)
00290         
00291         data = grass.db_select(table = self.tablename,
00292                                sql = querystring,
00293                                database = self.database,
00294                                driver = self.driver)
00295         if not data:
00296             return
00297 
00298         desc = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.layer))[column]
00299         
00300         i = 0
00301         for item in sorted(map(desc['ctype'], data)):
00302             if justsample and i < 256 or \
00303                not justsample:
00304                 if desc['type'] != 'character':
00305                     item = str(item)
00306                 self.list_values.Append(item)
00307             else:
00308                 break
00309             i += 1
00310         
00311     def OnSampleValues(self, event):
00312         """!Get sample values"""
00313         self.OnUniqueValues(None, True)
00314 
00315     def OnAddColumn(self, event):
00316         """!Add column name to the query"""
00317         idx = self.list_columns.GetSelections()
00318         for i in idx:
00319             column = self.list_columns.GetString(i)
00320             self._add(element = 'column', value = column)
00321         
00322         if not self.btn_uniquesample.IsEnabled():
00323             self.btn_uniquesample.Enable(True)
00324             self.btn_unique.Enable(True)
00325         
00326     def OnAddValue(self, event):
00327         """!Add value"""
00328         selection = self.list_values.GetSelections()
00329         if not selection:
00330             event.Skip()
00331             return
00332 
00333         idx = selection[0]
00334         value = self.list_values.GetString(idx)
00335         idx = self.list_columns.GetSelections()[0]
00336         column = self.list_columns.GetString(idx)
00337         
00338         ctype = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.layer))[column]['type']
00339         
00340         if ctype == 'character':
00341             value = "'%s'" % value
00342         
00343         self._add(element = 'value', value = value)
00344 
00345     def OnAddMark(self, event):
00346         """!Add mark"""
00347         mark = None
00348         for key, value in self.btn_lv.iteritems():
00349             if event.GetId() == value[1]:
00350                 mark = value[0]
00351                 break
00352         
00353         self._add(element = 'mark', value = mark)
00354 
00355     def _add(self, element, value):
00356         """!Add element to the query
00357 
00358         @param element element to add (column, value)
00359         """
00360         sqlstr = self.text_sql.GetValue()
00361         newsqlstr = ''
00362         if element == 'column':
00363             if self.radio_cv.GetSelection() == 0: # -> column
00364                 idx1 = len('select')
00365                 idx2 = sqlstr.lower().find('from')
00366                 colstr = sqlstr[idx1:idx2].strip()
00367                 if colstr == '*':
00368                     cols = []
00369                 else:
00370                     cols = colstr.split(',')
00371                 if value in cols:
00372                         cols.remove(value)
00373                 else:
00374                     cols.append(value)
00375                 
00376                 if len(cols) < 1:
00377                     cols = ['*',]
00378                 
00379                 newsqlstr = 'SELECT ' + ','.join(cols) + ' ' + sqlstr[idx2:]
00380             else: # -> where
00381                 newsqlstr = sqlstr
00382                 if sqlstr.lower().find('where') < 0:
00383                     newsqlstr += ' WHERE'
00384                 
00385                 newsqlstr += ' ' + value
00386         
00387         elif element == 'value':
00388             newsqlstr = sqlstr + ' ' + value
00389         elif element == 'mark':
00390             newsqlstr = sqlstr + ' ' + value
00391         
00392         if newsqlstr:
00393             self.text_sql.SetValue(newsqlstr)
00394 
00395     def GetSQLStatement(self):
00396         """!Return SQL statement"""
00397         return self.text_sql.GetValue().strip().replace("\n"," ")
00398     
00399     def CloseOnApply(self):
00400         """!Return True if the dialog will be close on apply"""
00401         return self.close_onapply.IsChecked()
00402     
00403     def OnText(self, event):
00404         """Query string changed"""
00405         if len(self.text_sql.GetValue()) > 0:
00406             self.btn_verify.Enable(True)
00407         else:
00408             self.btn_verify.Enable(False)
00409         
00410     def OnApply(self, event):
00411         """Apply button pressed"""
00412         if self.evtHeader:
00413             self.evtHeader(event = 'apply')
00414 
00415         if self.close_onapply.IsChecked():
00416             self.Destroy()
00417             
00418         event.Skip()
00419     
00420     def OnVerify(self, event):
00421         """!Verify button pressed"""
00422         ret, msg = gcmd.RunCommand('db.select',
00423                                    getErrorMsg = True,
00424                                    table = self.tablename,
00425                                    sql = self.text_sql.GetValue(),
00426                                    flags = 't',
00427                                    driver = self.driver,
00428                                    database = self.database)
00429         
00430         if ret != 0 and msg:
00431             self.statusbar.SetStatusText(_("SQL statement is not valid"), 0)
00432             gcmd.GError(parent = self,
00433                         message = _("SQL statement is not valid.\n\n%s") % msg)
00434         else:
00435             self.statusbar.SetStatusText(_("SQL statement is valid"), 0)
00436                         
00437     def OnClear(self, event):
00438         """!Clear button pressed"""
00439         if self.qtype.lower() == "select":
00440             self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
00441         else:
00442             self.text_sql.SetValue("")
00443     
00444     def OnClose(self, event):
00445         """!Close button pressed"""
00446         if self.evtHeader:
00447             self.evtHeader(event = 'close')
00448         
00449         self.Destroy()
00450         
00451         event.Skip()
00452         
00453 if __name__ == "__main__":
00454     if len(sys.argv) != 2:
00455         print >>sys.stderr, __doc__
00456         sys.exit()
00457     
00458     app = wx.App(0)
00459     sqlb = SQLFrame(parent = None, title = _('SQL Builder'), vectmap = sys.argv[1])
00460     sqlb.Show()
00461     
00462     app.MainLoop()
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Defines