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