GRASS Programmer's Manual
6.4.2(2012)
|
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()