4 @brief GRASS SQL Builder
11 python sqlbuilder.py vector_map
14 (C) 2007-2009 by the GRASS Development Team
16 This program is free software under the GNU General Public
17 License (>=v2). Read the file COPYING that comes with GRASS
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>
31 gettext.install(
'grasswxpy', os.path.join(os.getenv(
"GISBASE"),
'locale'), unicode=
True)
42 """!SQL Frame class"""
43 def __init__(self, parent, title, vectmap, id = wx.ID_ANY,
44 layer = 1, qtype =
"select", evtheader =
None):
46 wx.Frame.__init__(self, parent, id, title)
48 self.SetIcon(wx.Icon(os.path.join(globalvar.ETCICONDIR,
'grass_sql.ico'),
60 self.mapname, self.
mapset = self.vectmap.split(
"@", 1)
72 self.SetTitle(_(
"GRASS SQL Builder (%(type)s): vector map <%(map)s>") % \
73 {
'type' : self.qtype.upper(),
'map' : self.
vectmap })
75 self.
panel = wx.Panel(parent = self, id = wx.ID_ANY)
79 self.statusbar.SetStatusText(_(
"SQL statement not verified"), 0)
84 """!Do dialog layout"""
86 pagesizer = wx.BoxSizer(wx.VERTICAL)
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),
95 flag=wx.EXPAND | wx.ALL,
102 sqlbox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
103 label =
" %s " % _(
"Query"))
104 sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL)
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)
115 sqlboxsizer.Add(item = self.
text_sql, flag = wx.EXPAND)
121 self.btn_clear.SetToolTipString(_(
"Set SQL statement to default"))
124 self.btn_verify.SetToolTipString(_(
"Verify SQL statement"))
126 self.btn_apply.SetToolTipString(_(
"Apply SQL statement and close the dialog"))
128 self.btn_close.SetToolTipString(_(
"Close the dialog"))
143 for key, value
in self.btn_lv.iteritems():
144 btn = wx.Button(parent = self.
panel, id = wx.ID_ANY,
146 self.
btn_lv[key].append(btn.GetId())
148 buttonsizer = wx.FlexGridSizer(cols = 4, hgap = 5, vgap = 5)
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))
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))
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))
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))
174 hsizer = wx.BoxSizer(wx.HORIZONTAL)
176 columnsbox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
177 label =
" %s " % _(
"Columns"))
178 columnsizer = wx.StaticBoxSizer(columnsbox, wx.VERTICAL)
180 choices = self.dbInfo.GetColumns(self.
tablename),
181 style = wx.LB_MULTIPLE)
182 columnsizer.Add(item = self.
list_columns, proportion = 1,
185 radiosizer = wx.BoxSizer(wx.HORIZONTAL)
187 label =
" %s " % _(
"Add on double-click"),
188 choices = [_(
"columns"), _(
"values")])
189 self.radio_cv.SetSelection(1)
190 radiosizer.Add(item = self.
radio_cv, proportion = 1,
191 flag = wx.ALIGN_CENTER_HORIZONTAL | wx.EXPAND, border = 5)
193 columnsizer.Add(item = radiosizer, proportion = 0,
194 flag = wx.TOP | wx.EXPAND, border = 5)
198 valuesbox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
199 label =
" %s " % _(
"Values"))
200 valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL)
203 style = wx.LB_MULTIPLE)
204 valuesizer.Add(item = self.
list_values, proportion = 1,
208 label = _(
"Get all values"))
209 self.btn_unique.Enable(
False)
211 label = _(
"Get sample"))
212 self.btn_uniquesample.Enable(
False)
214 buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL)
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)
220 valuesizer.Add(item = buttonsizer3, proportion = 0,
221 flag = wx.TOP, border = 5)
225 hsizer.Add(item = columnsizer, proportion = 1,
227 hsizer.Add(item = valuesizer, proportion = 1,
231 label = _(
"Close dialog on apply"))
232 self.close_onapply.SetValue(
True)
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)
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)
247 flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
255 for key, value
in self.btn_lv.iteritems():
256 self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.
OnAddMark)
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)
263 self.list_columns.Bind(wx.EVT_LISTBOX, self.
OnAddColumn)
264 self.list_values.Bind(wx.EVT_LISTBOX, self.
OnAddValue)
266 self.text_sql.Bind(wx.EVT_TEXT, self.
OnText)
268 self.panel.SetAutoLayout(
True)
269 self.panel.SetSizer(pagesizer)
270 pagesizer.Fit(self.
panel)
273 self.SetMinSize((660, 525))
274 self.SetClientSize(self.panel.GetSize())
275 self.CenterOnParent()
278 """!Get unique values"""
281 idx = self.list_columns.GetSelections()[0]
282 column = self.list_columns.GetString(idx)
284 self.list_values.Clear()
287 self.list_values.Clear()
289 querystring =
"SELECT %s FROM %s" % (column, self.
tablename)
291 data = grass.db_select(table = self.
tablename,
294 driver = self.driver)
298 desc = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.
layer))[column]
301 for item
in sorted(
map(desc[
'ctype'], data)):
302 if justsample
and i < 256
or \
304 if desc[
'type'] !=
'character':
306 self.list_values.Append(item)
312 """!Get sample values"""
316 """!Add column name to the query"""
317 idx = self.list_columns.GetSelections()
319 column = self.list_columns.GetString(i)
320 self.
_add(element =
'column', value = column)
322 if not self.btn_uniquesample.IsEnabled():
323 self.btn_uniquesample.Enable(
True)
324 self.btn_unique.Enable(
True)
328 selection = self.list_values.GetSelections()
334 value = self.list_values.GetString(idx)
335 idx = self.list_columns.GetSelections()[0]
336 column = self.list_columns.GetString(idx)
338 ctype = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.
layer))[column][
'type']
340 if ctype ==
'character':
341 value =
"'%s'" % value
343 self.
_add(element =
'value', value = value)
348 for key, value
in self.btn_lv.iteritems():
349 if event.GetId() == value[1]:
353 self.
_add(element =
'mark', value = mark)
355 def _add(self, element, value):
356 """!Add element to the query
358 @param element element to add (column, value)
360 sqlstr = self.text_sql.GetValue()
362 if element ==
'column':
363 if self.radio_cv.GetSelection() == 0:
365 idx2 = sqlstr.lower().find(
'from')
366 colstr = sqlstr[idx1:idx2].strip()
370 cols = colstr.split(
',')
379 newsqlstr =
'SELECT ' +
','.join(cols) +
' ' + sqlstr[idx2:]
382 if sqlstr.lower().find(
'where') < 0:
383 newsqlstr +=
' WHERE'
385 newsqlstr +=
' ' + value
387 elif element ==
'value':
388 newsqlstr = sqlstr +
' ' + value
389 elif element ==
'mark':
390 newsqlstr = sqlstr +
' ' + value
393 self.text_sql.SetValue(newsqlstr)
396 """!Return SQL statement"""
397 return self.text_sql.GetValue().strip().replace(
"\n",
" ")
400 """!Return True if the dialog will be close on apply"""
401 return self.close_onapply.IsChecked()
404 """Query string changed"""
405 if len(self.text_sql.GetValue()) > 0:
406 self.btn_verify.Enable(
True)
408 self.btn_verify.Enable(
False)
411 """Apply button pressed"""
415 if self.close_onapply.IsChecked():
421 """!Verify button pressed"""
422 ret, msg = gcmd.RunCommand(
'db.select',
425 sql = self.text_sql.GetValue(),
427 driver = self.driver,
431 self.statusbar.SetStatusText(_(
"SQL statement is not valid"), 0)
433 message = _(
"SQL statement is not valid.\n\n%s") % msg)
435 self.statusbar.SetStatusText(_(
"SQL statement is valid"), 0)
438 """!Clear button pressed"""
439 if self.qtype.lower() ==
"select":
440 self.text_sql.SetValue(
"SELECT * FROM %s" % self.
tablename)
442 self.text_sql.SetValue(
"")
445 """!Close button pressed"""
453 if __name__ ==
"__main__":
454 if len(sys.argv) != 2:
455 print >>sys.stderr, __doc__
459 sqlb =
SQLFrame(parent =
None, title = _(
'SQL Builder'), vectmap = sys.argv[1])