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 createDbInfoDesc
Create database connection information content. 
def OnUniqueValues
Get unique values. 
def OnVerify
Verify button pressed. 
def OnClose
Close button pressed. 
def _doLayout
Do dialog layout. 
def GetSQLStatement
Return SQL statement. 
def OnClear
Clear button pressed. 
def OnAddColumn
Add column name to the query. 
def _add
Add element to the query. 
def CloseOnApply
Return True if the dialog will be close on apply. 
def RunCommand
Run GRASS command. 
def OnSampleValues
Get sample values.