GRASS Programmer's Manual  6.4.2(2012)
 All Data Structures Namespaces Files Functions Variables Typedefs Enumerations Enumerator Macros Pages
sqlbuilder.py
Go to the documentation of this file.
1 """!
2 @package sqlbuilder.py
3 
4 @brief GRASS SQL Builder
5 
6 Classes:
7  - SQLFrame
8 
9 Usage:
10 @code
11 python sqlbuilder.py vector_map
12 @endcode
13 
14 (C) 2007-2009 by the GRASS Development Team
15 
16 This program is free software under the GNU General Public
17 License (>=v2). Read the file COPYING that comes with GRASS
18 for details.
19 
20 @author Jachym Cepicky <jachym.cepicky gmail.com> (original author)
21 @author Martin Landa <landa.martin gmail.com>
22 @author Hamish Bowman <hamish_b yahoo com>
23 """
24 
25 import os
26 import sys
27 import time
28 
29 ### i18N
30 import gettext
31 gettext.install('grasswxpy', os.path.join(os.getenv("GISBASE"), 'locale'), unicode=True)
32 
33 import globalvar
34 import wx
35 
36 import grass.script as grass
37 
38 import gcmd
39 import dbm_base
40 
41 class SQLFrame(wx.Frame):
42  """!SQL Frame class"""
43  def __init__(self, parent, title, vectmap, id = wx.ID_ANY,
44  layer = 1, qtype = "select", evtheader = None):
45 
46  wx.Frame.__init__(self, parent, id, title)
47 
48  self.SetIcon(wx.Icon(os.path.join(globalvar.ETCICONDIR, 'grass_sql.ico'),
49  wx.BITMAP_TYPE_ICO))
50 
51  self.parent = parent
52  self.evtHeader = evtheader
53 
54  #
55  # variables
56  #
57  self.vectmap = vectmap # fullname
58  if not "@" in self.vectmap:
59  self.vectmap = self.vectmap + "@" + grass.gisenv()['MAPSET']
60  self.mapname, self.mapset = self.vectmap.split("@", 1)
61 
62  # db info
63  self.layer = layer
65  self.tablename = self.dbInfo.GetTable(self.layer)
66  self.driver, self.database = self.dbInfo.GetDbSettings(self.layer)
67 
68  self.qtype = qtype # type of query: SELECT, UPDATE, DELETE, ...
69  self.colvalues = [] # array with unique values in selected column
70 
71  # set dialog title
72  self.SetTitle(_("GRASS SQL Builder (%(type)s): vector map <%(map)s>") % \
73  { 'type' : self.qtype.upper(), 'map' : self.vectmap })
74 
75  self.panel = wx.Panel(parent = self, id = wx.ID_ANY)
76 
77  # statusbar
78  self.statusbar = self.CreateStatusBar(number=1)
79  self.statusbar.SetStatusText(_("SQL statement not verified"), 0)
80 
81  self._doLayout()
82 
83  def _doLayout(self):
84  """!Do dialog layout"""
85 
86  pagesizer = wx.BoxSizer(wx.VERTICAL)
87 
88 
89  # dbInfo
90  databasebox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
91  label = " %s " % _("Database connection"))
92  databaseboxsizer = wx.StaticBoxSizer(databasebox, wx.VERTICAL)
93  databaseboxsizer.Add(item=dbm_base.createDbInfoDesc(self.panel, self.dbInfo, layer = self.layer),
94  proportion=1,
95  flag=wx.EXPAND | wx.ALL,
96  border=3)
97 
98  #
99  # text areas
100  #
101  # sql box
102  sqlbox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
103  label = " %s " % _("Query"))
104  sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL)
105 
106  self.text_sql = wx.TextCtrl(parent = self.panel, id = wx.ID_ANY,
107  value = '', size = (-1, 50),
108  style=wx.TE_MULTILINE)
109  if self.qtype.lower() == "select":
110  self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
111  self.text_sql.SetInsertionPointEnd()
112  self.text_sql.SetToolTipString(_("Example: %s") % "SELECT * FROM roadsmajor WHERE MULTILANE = 'no' OR OBJECTID < 10")
113  wx.CallAfter(self.text_sql.SetFocus)
114 
115  sqlboxsizer.Add(item = self.text_sql, flag = wx.EXPAND)
116 
117  #
118  # buttons
119  #
120  self.btn_clear = wx.Button(parent = self.panel, id = wx.ID_CLEAR)
121  self.btn_clear.SetToolTipString(_("Set SQL statement to default"))
122  self.btn_verify = wx.Button(parent = self.panel, id = wx.ID_ANY,
123  label = _("Verify"))
124  self.btn_verify.SetToolTipString(_("Verify SQL statement"))
125  self.btn_apply = wx.Button(parent = self.panel, id = wx.ID_APPLY)
126  self.btn_apply.SetToolTipString(_("Apply SQL statement and close the dialog"))
127  self.btn_close = wx.Button(parent = self.panel, id = wx.ID_CLOSE)
128  self.btn_close.SetToolTipString(_("Close the dialog"))
129 
130  self.btn_lv = { 'is' : ['=', ],
131  'isnot' : ['!=', ],
132  'like' : ['LIKE', ],
133  'gt' : ['>', ],
134  'ge' : ['>=', ],
135  'lt' : ['<', ],
136  'le' : ['<=', ],
137  'or' : ['OR', ],
138  'not' : ['NOT', ],
139  'and' : ['AND', ],
140  'brac' : ['()', ],
141  'prc' : ['%', ] }
142 
143  for key, value in self.btn_lv.iteritems():
144  btn = wx.Button(parent = self.panel, id = wx.ID_ANY,
145  label = value[0])
146  self.btn_lv[key].append(btn.GetId())
147 
148  buttonsizer = wx.FlexGridSizer(cols = 4, hgap = 5, vgap = 5)
149  buttonsizer.Add(item = self.btn_clear)
150  buttonsizer.Add(item = self.btn_verify)
151  buttonsizer.Add(item = self.btn_apply)
152  buttonsizer.Add(item = self.btn_close)
153 
154  buttonsizer2 = wx.GridBagSizer(5, 5)
155  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['is'][1]), pos = (0,0))
156  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['isnot'][1]), pos = (1,0))
157  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['like'][1]), pos = (2, 0))
158 
159  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['gt'][1]), pos = (0, 1))
160  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['ge'][1]), pos = (1, 1))
161  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['or'][1]), pos = (2, 1))
162 
163  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['lt'][1]), pos = (0, 2))
164  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['le'][1]), pos = (1, 2))
165  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['not'][1]), pos = (2, 2))
166 
167  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['brac'][1]), pos = (0, 3))
168  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['prc'][1]), pos = (1, 3))
169  buttonsizer2.Add(item = self.FindWindowById(self.btn_lv['and'][1]), pos = (2, 3))
170 
171  #
172  # list boxes (columns, values)
173  #
174  hsizer = wx.BoxSizer(wx.HORIZONTAL)
175 
176  columnsbox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
177  label = " %s " % _("Columns"))
178  columnsizer = wx.StaticBoxSizer(columnsbox, wx.VERTICAL)
179  self.list_columns = wx.ListBox(parent = self.panel, id = wx.ID_ANY,
180  choices = self.dbInfo.GetColumns(self.tablename),
181  style = wx.LB_MULTIPLE)
182  columnsizer.Add(item = self.list_columns, proportion = 1,
183  flag = wx.EXPAND)
184 
185  radiosizer = wx.BoxSizer(wx.HORIZONTAL)
186  self.radio_cv = wx.RadioBox(parent = self.panel, id = wx.ID_ANY,
187  label = " %s " % _("Add on double-click"),
188  choices = [_("columns"), _("values")])
189  self.radio_cv.SetSelection(1) # default 'values'
190  radiosizer.Add(item = self.radio_cv, proportion = 1,
191  flag = wx.ALIGN_CENTER_HORIZONTAL | wx.EXPAND, border = 5)
192 
193  columnsizer.Add(item = radiosizer, proportion = 0,
194  flag = wx.TOP | wx.EXPAND, border = 5)
195  # self.list_columns.SetMinSize((-1,130))
196  # self.list_values.SetMinSize((-1,100))
197 
198  valuesbox = wx.StaticBox(parent = self.panel, id = wx.ID_ANY,
199  label = " %s " % _("Values"))
200  valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL)
201  self.list_values = wx.ListBox(parent = self.panel, id = wx.ID_ANY,
202  choices = self.colvalues,
203  style = wx.LB_MULTIPLE)
204  valuesizer.Add(item = self.list_values, proportion = 1,
205  flag = wx.EXPAND)
206 
207  self.btn_unique = wx.Button(parent = self.panel, id = wx.ID_ANY,
208  label = _("Get all values"))
209  self.btn_unique.Enable(False)
210  self.btn_uniquesample = wx.Button(parent = self.panel, id = wx.ID_ANY,
211  label = _("Get sample"))
212  self.btn_uniquesample.Enable(False)
213 
214  buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL)
215  buttonsizer3.Add(item = self.btn_uniquesample, proportion = 0,
216  flag = wx.ALIGN_CENTER_HORIZONTAL | wx.RIGHT, border = 5)
217  buttonsizer3.Add(item = self.btn_unique, proportion = 0,
218  flag = wx.ALIGN_CENTER_HORIZONTAL)
219 
220  valuesizer.Add(item = buttonsizer3, proportion = 0,
221  flag = wx.TOP, border = 5)
222 
223  # hsizer1.Add(wx.StaticText(self.panel,-1, "Unique values: "), border=0, proportion=1)
224 
225  hsizer.Add(item = columnsizer, proportion = 1,
226  flag = wx.EXPAND)
227  hsizer.Add(item = valuesizer, proportion = 1,
228  flag = wx.EXPAND)
229 
230  self.close_onapply = wx.CheckBox(parent = self.panel, id = wx.ID_ANY,
231  label = _("Close dialog on apply"))
232  self.close_onapply.SetValue(True)
233 
234  pagesizer.Add(item = databaseboxsizer,
235  flag = wx.ALL | wx.EXPAND, border = 5)
236  pagesizer.Add(item = hsizer, proportion = 1,
237  flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
238  # pagesizer.Add(self.btn_uniqe,0,wx.ALIGN_LEFT|wx.TOP,border=5)
239  # pagesizer.Add(self.btn_uniqesample,0,wx.ALIGN_LEFT|wx.TOP,border=5)
240  pagesizer.Add(item = buttonsizer2, proportion = 0,
241  flag = wx.ALIGN_CENTER_HORIZONTAL)
242  pagesizer.Add(item = sqlboxsizer, proportion = 0,
243  flag = wx.EXPAND | wx.LEFT | wx.RIGHT, border = 5)
244  pagesizer.Add(item = buttonsizer, proportion = 0,
245  flag = wx.ALIGN_RIGHT | wx.ALL, border = 5)
246  pagesizer.Add(item = self.close_onapply, proportion = 0,
247  flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
248 
249  #
250  # bindings
251  #
252  self.btn_unique.Bind(wx.EVT_BUTTON, self.OnUniqueValues)
253  self.btn_uniquesample.Bind(wx.EVT_BUTTON, self.OnSampleValues)
254 
255  for key, value in self.btn_lv.iteritems():
256  self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.OnAddMark)
257 
258  self.btn_close.Bind(wx.EVT_BUTTON, self.OnClose)
259  self.btn_clear.Bind(wx.EVT_BUTTON, self.OnClear)
260  self.btn_verify.Bind(wx.EVT_BUTTON, self.OnVerify)
261  self.btn_apply.Bind(wx.EVT_BUTTON, self.OnApply)
262 
263  self.list_columns.Bind(wx.EVT_LISTBOX, self.OnAddColumn)
264  self.list_values.Bind(wx.EVT_LISTBOX, self.OnAddValue)
265 
266  self.text_sql.Bind(wx.EVT_TEXT, self.OnText)
267 
268  self.panel.SetAutoLayout(True)
269  self.panel.SetSizer(pagesizer)
270  pagesizer.Fit(self.panel)
271 
272  self.Layout()
273  self.SetMinSize((660, 525))
274  self.SetClientSize(self.panel.GetSize())
275  self.CenterOnParent()
276 
277  def OnUniqueValues(self, event, justsample = False):
278  """!Get unique values"""
279  vals = []
280  try:
281  idx = self.list_columns.GetSelections()[0]
282  column = self.list_columns.GetString(idx)
283  except:
284  self.list_values.Clear()
285  return
286 
287  self.list_values.Clear()
288 
289  querystring = "SELECT %s FROM %s" % (column, self.tablename)
290 
291  data = grass.db_select(table = self.tablename,
292  sql = querystring,
293  database = self.database,
294  driver = self.driver)
295  if not data:
296  return
297 
298  desc = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.layer))[column]
299 
300  i = 0
301  for item in sorted(map(desc['ctype'], data)):
302  if justsample and i < 256 or \
303  not justsample:
304  if desc['type'] != 'character':
305  item = str(item)
306  self.list_values.Append(item)
307  else:
308  break
309  i += 1
310 
311  def OnSampleValues(self, event):
312  """!Get sample values"""
313  self.OnUniqueValues(None, True)
314 
315  def OnAddColumn(self, event):
316  """!Add column name to the query"""
317  idx = self.list_columns.GetSelections()
318  for i in idx:
319  column = self.list_columns.GetString(i)
320  self._add(element = 'column', value = column)
321 
322  if not self.btn_uniquesample.IsEnabled():
323  self.btn_uniquesample.Enable(True)
324  self.btn_unique.Enable(True)
325 
326  def OnAddValue(self, event):
327  """!Add value"""
328  selection = self.list_values.GetSelections()
329  if not selection:
330  event.Skip()
331  return
332 
333  idx = selection[0]
334  value = self.list_values.GetString(idx)
335  idx = self.list_columns.GetSelections()[0]
336  column = self.list_columns.GetString(idx)
337 
338  ctype = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.layer))[column]['type']
339 
340  if ctype == 'character':
341  value = "'%s'" % value
342 
343  self._add(element = 'value', value = value)
344 
345  def OnAddMark(self, event):
346  """!Add mark"""
347  mark = None
348  for key, value in self.btn_lv.iteritems():
349  if event.GetId() == value[1]:
350  mark = value[0]
351  break
352 
353  self._add(element = 'mark', value = mark)
354 
355  def _add(self, element, value):
356  """!Add element to the query
357 
358  @param element element to add (column, value)
359  """
360  sqlstr = self.text_sql.GetValue()
361  newsqlstr = ''
362  if element == 'column':
363  if self.radio_cv.GetSelection() == 0: # -> column
364  idx1 = len('select')
365  idx2 = sqlstr.lower().find('from')
366  colstr = sqlstr[idx1:idx2].strip()
367  if colstr == '*':
368  cols = []
369  else:
370  cols = colstr.split(',')
371  if value in cols:
372  cols.remove(value)
373  else:
374  cols.append(value)
375 
376  if len(cols) < 1:
377  cols = ['*',]
378 
379  newsqlstr = 'SELECT ' + ','.join(cols) + ' ' + sqlstr[idx2:]
380  else: # -> where
381  newsqlstr = sqlstr
382  if sqlstr.lower().find('where') < 0:
383  newsqlstr += ' WHERE'
384 
385  newsqlstr += ' ' + value
386 
387  elif element == 'value':
388  newsqlstr = sqlstr + ' ' + value
389  elif element == 'mark':
390  newsqlstr = sqlstr + ' ' + value
391 
392  if newsqlstr:
393  self.text_sql.SetValue(newsqlstr)
394 
395  def GetSQLStatement(self):
396  """!Return SQL statement"""
397  return self.text_sql.GetValue().strip().replace("\n"," ")
398 
399  def CloseOnApply(self):
400  """!Return True if the dialog will be close on apply"""
401  return self.close_onapply.IsChecked()
402 
403  def OnText(self, event):
404  """Query string changed"""
405  if len(self.text_sql.GetValue()) > 0:
406  self.btn_verify.Enable(True)
407  else:
408  self.btn_verify.Enable(False)
409 
410  def OnApply(self, event):
411  """Apply button pressed"""
412  if self.evtHeader:
413  self.evtHeader(event = 'apply')
414 
415  if self.close_onapply.IsChecked():
416  self.Destroy()
417 
418  event.Skip()
419 
420  def OnVerify(self, event):
421  """!Verify button pressed"""
422  ret, msg = gcmd.RunCommand('db.select',
423  getErrorMsg = True,
424  table = self.tablename,
425  sql = self.text_sql.GetValue(),
426  flags = 't',
427  driver = self.driver,
428  database = self.database)
429 
430  if ret != 0 and msg:
431  self.statusbar.SetStatusText(_("SQL statement is not valid"), 0)
432  gcmd.GError(parent = self,
433  message = _("SQL statement is not valid.\n\n%s") % msg)
434  else:
435  self.statusbar.SetStatusText(_("SQL statement is valid"), 0)
436 
437  def OnClear(self, event):
438  """!Clear button pressed"""
439  if self.qtype.lower() == "select":
440  self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
441  else:
442  self.text_sql.SetValue("")
443 
444  def OnClose(self, event):
445  """!Close button pressed"""
446  if self.evtHeader:
447  self.evtHeader(event = 'close')
448 
449  self.Destroy()
450 
451  event.Skip()
452 
453 if __name__ == "__main__":
454  if len(sys.argv) != 2:
455  print >>sys.stderr, __doc__
456  sys.exit()
457 
458  app = wx.App(0)
459  sqlb = SQLFrame(parent = None, title = _('SQL Builder'), vectmap = sys.argv[1])
460  sqlb.Show()
461 
462  app.MainLoop()