2 @package dbmgr.sqlbuilder
4 @brief GRASS SQL Builder
11 python sqlbuilder.py vector_map
14 (C) 2007-2009, 2011 by the GRASS Development Team
16 This program is free software under the GNU General Public License
17 (>=v2). Read the file COPYING that comes with GRASS for details.
19 @author Jachym Cepicky <jachym.cepicky gmail.com> (original author)
20 @author Martin Landa <landa.martin gmail.com>
21 @author Hamish Bowman <hamish_b yahoo com>
27 if __name__ ==
"__main__":
28 sys.path.append(os.path.join(os.getenv(
'GISBASE'),
'etc',
'gui',
'wxpython'))
29 from core
import globalvar
33 from dbmgr.vinfo
import createDbInfoDesc, VectorDBInfo
38 """!SQL Frame class"""
39 def __init__(self, parent, title, vectmap, id = wx.ID_ANY,
40 layer = 1, qtype =
"select", evtHandler =
None):
42 wx.Frame.__init__(self, parent, id, title)
44 self.SetIcon(wx.Icon(os.path.join(globalvar.ETCICONDIR,
'grass_sql.ico'),
55 self.
vectmap = grass.find_file(self.
vectmap, element =
'vector')[
'fullname']
56 self.mapname, self.
mapset = self.vectmap.split(
"@", 1)
68 self.SetTitle(_(
"GRASS SQL Builder (%(type)s): vector map <%(map)s>") % \
69 {
'type' : self.qtype.upper(),
'map' : self.
vectmap })
71 self.
panel = wx.Panel(parent = self, id = wx.ID_ANY)
75 self.statusbar.SetStatusText(_(
"SQL statement not verified"), 0)
80 """!Do dialog layout"""
82 pagesizer = wx.BoxSizer(wx.VERTICAL)
86 databasebox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
87 label =
" %s " % _(
"Database connection"))
88 databaseboxsizer = wx.StaticBoxSizer(databasebox, wx.VERTICAL)
91 flag=wx.EXPAND | wx.ALL,
98 sqlbox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
99 label =
" %s " % _(
"Query"))
100 sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL)
103 value =
'', size = (-1, 50),
104 style=wx.TE_MULTILINE)
105 if self.qtype.lower() ==
"select":
106 self.text_sql.SetValue(
"SELECT * FROM %s" % self.
tablename)
107 self.text_sql.SetInsertionPointEnd()
108 self.text_sql.SetToolTipString(_(
"Example: %s") %
"SELECT * FROM roadsmajor WHERE MULTILANE = 'no' OR OBJECTID < 10")
109 wx.CallAfter(self.text_sql.SetFocus)
111 sqlboxsizer.Add(item = self.
text_sql, flag = wx.EXPAND)
117 self.btn_clear.SetToolTipString(_(
"Set SQL statement to default"))
120 self.btn_verify.SetToolTipString(_(
"Verify SQL statement"))
122 self.btn_apply.SetToolTipString(_(
"Apply SQL statement and close the dialog"))
124 self.btn_close.SetToolTipString(_(
"Close the dialog"))
139 for key, value
in self.btn_lv.iteritems():
140 btn = wx.Button(parent = self.
panel, id = wx.ID_ANY,
142 self.
btn_lv[key].append(btn.GetId())
144 buttonsizer = wx.FlexGridSizer(cols = 4, hgap = 5, vgap = 5)
150 buttonsizer2 = wx.GridBagSizer(5, 5)
151 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'is'][1]), pos = (0,0))
152 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'isnot'][1]), pos = (1,0))
153 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'like'][1]), pos = (2, 0))
155 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'gt'][1]), pos = (0, 1))
156 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'ge'][1]), pos = (1, 1))
157 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'or'][1]), pos = (2, 1))
159 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'lt'][1]), pos = (0, 2))
160 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'le'][1]), pos = (1, 2))
161 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'not'][1]), pos = (2, 2))
163 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'brac'][1]), pos = (0, 3))
164 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'prc'][1]), pos = (1, 3))
165 buttonsizer2.Add(item = self.FindWindowById(self.
btn_lv[
'and'][1]), pos = (2, 3))
170 hsizer = wx.BoxSizer(wx.HORIZONTAL)
172 columnsbox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
173 label =
" %s " % _(
"Columns"))
174 columnsizer = wx.StaticBoxSizer(columnsbox, wx.VERTICAL)
176 choices = self.dbInfo.GetColumns(self.
tablename),
177 style = wx.LB_MULTIPLE)
178 columnsizer.Add(item = self.
list_columns, proportion = 1,
181 radiosizer = wx.BoxSizer(wx.HORIZONTAL)
183 label =
" %s " % _(
"Add on double-click"),
184 choices = [_(
"columns"), _(
"values")])
185 self.radio_cv.SetSelection(1)
186 radiosizer.Add(item = self.
radio_cv, proportion = 1,
187 flag = wx.ALIGN_CENTER_HORIZONTAL | wx.EXPAND, border = 5)
189 columnsizer.Add(item = radiosizer, proportion = 0,
190 flag = wx.TOP | wx.EXPAND, border = 5)
194 valuesbox = wx.StaticBox(parent = self.
panel, id = wx.ID_ANY,
195 label =
" %s " % _(
"Values"))
196 valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL)
199 style = wx.LB_MULTIPLE)
200 valuesizer.Add(item = self.
list_values, proportion = 1,
204 label = _(
"Get all values"))
205 self.btn_unique.Enable(
False)
207 label = _(
"Get sample"))
208 self.btn_uniquesample.Enable(
False)
210 buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL)
212 flag = wx.ALIGN_CENTER_HORIZONTAL | wx.RIGHT, border = 5)
213 buttonsizer3.Add(item = self.
btn_unique, proportion = 0,
214 flag = wx.ALIGN_CENTER_HORIZONTAL)
216 valuesizer.Add(item = buttonsizer3, proportion = 0,
217 flag = wx.TOP, border = 5)
221 hsizer.Add(item = columnsizer, proportion = 1,
223 hsizer.Add(item = valuesizer, proportion = 1,
227 label = _(
"Close dialog on apply"))
228 self.close_onapply.SetValue(
True)
230 pagesizer.Add(item = databaseboxsizer,
231 flag = wx.ALL | wx.EXPAND, border = 5)
232 pagesizer.Add(item = hsizer, proportion = 1,
233 flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
236 pagesizer.Add(item = buttonsizer2, proportion = 0,
237 flag = wx.ALIGN_CENTER_HORIZONTAL)
238 pagesizer.Add(item = sqlboxsizer, proportion = 0,
239 flag = wx.EXPAND | wx.LEFT | wx.RIGHT, border = 5)
240 pagesizer.Add(item = buttonsizer, proportion = 0,
241 flag = wx.ALIGN_RIGHT | wx.ALL, border = 5)
243 flag = wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border = 5)
251 for key, value
in self.btn_lv.iteritems():
252 self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.
OnAddMark)
254 self.btn_close.Bind(wx.EVT_BUTTON, self.
OnClose)
255 self.btn_clear.Bind(wx.EVT_BUTTON, self.
OnClear)
256 self.btn_verify.Bind(wx.EVT_BUTTON, self.
OnVerify)
257 self.btn_apply.Bind(wx.EVT_BUTTON, self.
OnApply)
259 self.list_columns.Bind(wx.EVT_LISTBOX, self.
OnAddColumn)
260 self.list_values.Bind(wx.EVT_LISTBOX, self.
OnAddValue)
262 self.text_sql.Bind(wx.EVT_TEXT, self.
OnText)
264 self.panel.SetAutoLayout(
True)
265 self.panel.SetSizer(pagesizer)
266 pagesizer.Fit(self.
panel)
269 self.SetMinSize((660, 525))
270 self.SetClientSize(self.panel.GetSize())
271 self.CenterOnParent()
274 """!Get unique values"""
277 idx = self.list_columns.GetSelections()[0]
278 column = self.list_columns.GetString(idx)
280 self.list_values.Clear()
283 self.list_values.Clear()
285 data = grass.db_select(sql =
"SELECT %s FROM %s" % (column, self.
tablename),
287 driver = self.driver)
291 desc = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.
layer))[column]
294 for item
in sorted(
set(map(
lambda x: desc[
'ctype'](x[0]), data))):
295 if justsample
and i > 255:
298 if desc[
'type'] !=
'character':
300 self.list_values.Append(item)
304 """!Get sample values"""
308 """!Add column name to the query"""
309 idx = self.list_columns.GetSelections()
311 column = self.list_columns.GetString(i)
312 self.
_add(element =
'column', value = column)
314 if not self.btn_uniquesample.IsEnabled():
315 self.btn_uniquesample.Enable(
True)
316 self.btn_unique.Enable(
True)
320 selection = self.list_values.GetSelections()
326 value = self.list_values.GetString(idx)
327 idx = self.list_columns.GetSelections()[0]
328 column = self.list_columns.GetString(idx)
330 ctype = self.dbInfo.GetTableDesc(self.dbInfo.GetTable(self.
layer))[column][
'type']
332 if ctype ==
'character':
333 value =
"'%s'" % value
335 self.
_add(element =
'value', value = value)
340 for key, value
in self.btn_lv.iteritems():
341 if event.GetId() == value[1]:
345 self.
_add(element =
'mark', value = mark)
347 def _add(self, element, value):
348 """!Add element to the query
350 @param element element to add (column, value)
352 sqlstr = self.text_sql.GetValue()
354 if element ==
'column':
355 if self.radio_cv.GetSelection() == 0:
357 idx2 = sqlstr.lower().find(
'from')
358 colstr = sqlstr[idx1:idx2].strip()
362 cols = colstr.split(
',')
371 newsqlstr =
'SELECT ' +
','.join(cols) +
' ' + sqlstr[idx2:]
374 if sqlstr.lower().find(
'where') < 0:
375 newsqlstr +=
' WHERE'
377 newsqlstr +=
' ' + value
379 elif element ==
'value':
380 newsqlstr = sqlstr +
' ' + value
381 elif element ==
'mark':
382 newsqlstr = sqlstr +
' ' + value
385 self.text_sql.SetValue(newsqlstr)
388 """!Return SQL statement"""
389 return self.text_sql.GetValue().strip().replace(
"\n",
" ")
392 """!Return True if the dialog will be close on apply"""
393 return self.close_onapply.IsChecked()
396 """Query string changed"""
397 if len(self.text_sql.GetValue()) > 0:
398 self.btn_verify.Enable(
True)
400 self.btn_verify.Enable(
False)
403 """Apply button pressed"""
407 if self.close_onapply.IsChecked():
413 """!Verify button pressed"""
417 sql = self.text_sql.GetValue(),
419 driver = self.driver,
423 self.statusbar.SetStatusText(_(
"SQL statement is not valid"), 0)
424 GError(parent = self,
425 message = _(
"SQL statement is not valid.\n\n%s") % msg)
427 self.statusbar.SetStatusText(_(
"SQL statement is valid"), 0)
430 """!Clear button pressed"""
431 if self.qtype.lower() ==
"select":
432 self.text_sql.SetValue(
"SELECT * FROM %s" % self.
tablename)
434 self.text_sql.SetValue(
"")
437 """!Close button pressed"""
445 if __name__ ==
"__main__":
446 if len(sys.argv) != 2:
447 print >>sys.stderr, __doc__
451 gettext.install(
'grasswxpy', os.path.join(os.getenv(
"GISBASE"),
'locale'), unicode=
True)
454 sqlb =
SQLFrame(parent =
None, title = _(
'SQL Builder'), vectmap = sys.argv[1])
def OnAddColumn(self, event)
Add column name to the query.
def _doLayout(self)
Do dialog layout.
def OnUniqueValues
Get unique values.
def OnClose(self, event)
Close button pressed.
def OnAddValue(self, event)
Add value.
def CloseOnApply(self)
Return True if the dialog will be close on apply.
def OnSampleValues(self, event)
Get sample values.
def OnClear(self, event)
Clear button pressed.
def _add(self, element, value)
Add element to the query.
def OnAddMark(self, event)
Add mark.
def RunCommand(prog, flags="", overwrite=False, quiet=False, verbose=False, parent=None, read=False, stdin=None, getErrorMsg=False, kwargs)
Run GRASS command.
def createDbInfoDesc(panel, mapDBInfo, layer)
Create database connection information content.
def GetSQLStatement(self)
Return SQL statement.
def OnVerify(self, event)
Verify button pressed.