""" @package dbmgr.sqlbuilder @brief GRASS SQL Select/Update Builder Classes: - sqlbuilder::SQLBuilder - sqlbuilder::SQLBuilderSelect - sqlbuilder::SQLBuilderUpdate Usage: @code python sqlbuilder.py select|update vector_map @endcode (C) 2007-2014 by the GRASS Development Team This program is free software under the GNU General Public License (>=v2). Read the file COPYING that comes with GRASS for details. @author Jachym Cepicky (original author) @author Martin Landa @author Hamish Bowman @author Refactoring, SQLBUilderUpdate by Stepan Turek (GSoC 2012, mentor: Martin Landa) """ from __future__ import print_function import os import sys from core import globalvar from core.utils import _ import wx from grass.pydispatch.signal import Signal from core.gcmd import RunCommand, GError, GMessage from dbmgr.vinfo import CreateDbInfoDesc, VectorDBInfo, GetUnicodeValue import grass.script as grass class SQLBuilder(wx.Frame): """SQLBuider class Base class for classes, which builds SQL statements. """ def __init__(self, parent, title, vectmap, modeChoices, id=wx.ID_ANY, layer=1): wx.Frame.__init__(self, parent, id, title) self.SetIcon(wx.Icon(os.path.join(globalvar.ICONDIR, 'grass_sql.ico'), wx.BITMAP_TYPE_ICO)) self.parent = parent # variables self.vectmap = vectmap # fullname if not "@" in self.vectmap: self.vectmap = grass.find_file( self.vectmap, element='vector')['fullname'] if not self.vectmap: grass.fatal(_("Vector map <%s> not found") % vectmap) self.mapname, self.mapset = self.vectmap.split("@", 1) # db info self.layer = layer self.dbInfo = VectorDBInfo(self.vectmap) self.tablename = self.dbInfo.GetTable(self.layer) self.driver, self.database = self.dbInfo.GetDbSettings(self.layer) self.colvalues = [] # array with unique values in selected column self.panel = wx.Panel(parent=self, id=wx.ID_ANY) # statusbar self.statusbar = self.CreateStatusBar(number=1) self._doLayout(modeChoices) self.panel.SetAutoLayout(True) self.panel.SetSizer(self.pagesizer) self.pagesizer.Fit(self.panel) self.SetMinSize((400, 600)) self.SetClientSize(self.panel.GetSize()) self.CenterOnParent() def _doLayout(self, modeChoices): """Do dialog layout""" self.pagesizer = wx.BoxSizer(wx.VERTICAL) # dbInfo databasebox = wx.StaticBox(parent=self.panel, id=wx.ID_ANY, label=" %s " % _("Database connection")) databaseboxsizer = wx.StaticBoxSizer(databasebox, wx.VERTICAL) databaseboxsizer.Add( CreateDbInfoDesc( self.panel, self.dbInfo, layer=self.layer), proportion=1, flag=wx.EXPAND | wx.ALL, border=3) # # text areas # # sql box sqlbox = wx.StaticBox(parent=self.panel, id=wx.ID_ANY, label=" %s " % _("Query")) sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL) self.text_sql = wx.TextCtrl(parent=self.panel, id=wx.ID_ANY, value='', size=(-1, 50), style=wx.TE_MULTILINE) self.text_sql.SetInsertionPointEnd() wx.CallAfter(self.text_sql.SetFocus) sqlboxsizer.Add(self.text_sql, flag=wx.EXPAND) # # buttons # self.btn_clear = wx.Button(parent=self.panel, id=wx.ID_CLEAR) self.btn_clear.SetToolTipString(_("Set SQL statement to default")) self.btn_apply = wx.Button(parent=self.panel, id=wx.ID_APPLY) self.btn_apply.SetToolTipString( _("Apply SQL statement in Attribute Table Manager")) self.btn_close = wx.Button(parent=self.panel, id=wx.ID_CLOSE) self.btn_close.SetToolTipString(_("Close the dialog")) self.btn_logic = {'is': ['=', ], 'isnot': ['!=', ], 'like': ['LIKE', ], 'gt': ['>', ], 'ge': ['>=', ], 'lt': ['<', ], 'le': ['<=', ], 'or': ['OR', ], 'not': ['NOT', ], 'and': ['AND', ], 'brac': ['()', ], 'prc': ['%', ]} self.btn_logicpanel = wx.Panel(parent=self.panel, id=wx.ID_ANY) for key, value in self.btn_logic.iteritems(): btn = wx.Button(parent=self.btn_logicpanel, id=wx.ID_ANY, label=value[0]) self.btn_logic[key].append(btn.GetId()) self.buttonsizer = wx.FlexGridSizer(cols=4, hgap=5, vgap=5) self.buttonsizer.Add(self.btn_clear) self.buttonsizer.Add(self.btn_apply) self.buttonsizer.Add(self.btn_close) btn_logicsizer = wx.GridBagSizer(5, 5) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['is'][1]), pos=( 0, 0)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['isnot'][1]), pos=( 1, 0)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['like'][1]), pos=( 2, 0)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['gt'][1]), pos=( 0, 1)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['ge'][1]), pos=( 1, 1)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['or'][1]), pos=( 2, 1)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['lt'][1]), pos=( 0, 2)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['le'][1]), pos=( 1, 2)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['not'][1]), pos=( 2, 2)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['brac'][1]), pos=( 0, 3)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['prc'][1]), pos=( 1, 3)) btn_logicsizer.Add( self.FindWindowById( self.btn_logic['and'][1]), pos=( 2, 3)) self.btn_logicpanel.SetSizer(btn_logicsizer) # # list boxes (columns, values) # self.hsizer = wx.BoxSizer(wx.HORIZONTAL) columnsbox = wx.StaticBox(parent=self.panel, id=wx.ID_ANY, label=" %s " % _("Columns")) columnsizer = wx.StaticBoxSizer(columnsbox, wx.VERTICAL) self.list_columns = wx.ListBox( parent=self.panel, id=wx.ID_ANY, choices=self.dbInfo.GetColumns( self.tablename), style=wx.LB_MULTIPLE) columnsizer.Add(self.list_columns, proportion=1, flag=wx.EXPAND) modesizer = wx.BoxSizer(wx.VERTICAL) self.mode = wx.RadioBox(parent=self.panel, id=wx.ID_ANY, label=" %s " % _("Interactive insertion"), choices=modeChoices, style=wx.RA_SPECIFY_COLS, majorDimension=1) self.mode.SetSelection(1) # default 'values' modesizer.Add(self.mode, proportion=1, flag=wx.ALIGN_CENTER_HORIZONTAL | wx.EXPAND, border=5) # self.list_columns.SetMinSize((-1,130)) # self.list_values.SetMinSize((-1,100)) self.valuespanel = wx.Panel(parent=self.panel, id=wx.ID_ANY) valuesbox = wx.StaticBox(parent=self.valuespanel, id=wx.ID_ANY, label=" %s " % _("Values")) valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL) self.list_values = wx.ListBox(parent=self.valuespanel, id=wx.ID_ANY, choices=self.colvalues, style=wx.LB_MULTIPLE) valuesizer.Add(self.list_values, proportion=1, flag=wx.EXPAND) self.valuespanel.SetSizer(valuesizer) self.btn_unique = wx.Button(parent=self.valuespanel, id=wx.ID_ANY, label=_("Get all values")) self.btn_unique.Enable(False) self.btn_uniquesample = wx.Button( parent=self.valuespanel, id=wx.ID_ANY, label=_("Get sample")) self.btn_uniquesample.SetToolTipString( _("Get first 256 unique values as sample")) self.btn_uniquesample.Enable(False) buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL) buttonsizer3.Add(self.btn_uniquesample, proportion=0, flag=wx.ALIGN_CENTER_HORIZONTAL | wx.RIGHT, border=5) buttonsizer3.Add(self.btn_unique, proportion=0, flag=wx.ALIGN_CENTER_HORIZONTAL) valuesizer.Add(buttonsizer3, proportion=0, flag=wx.TOP, border=5) # go to gotosizer = wx.BoxSizer(wx.HORIZONTAL) self.goto = wx.TextCtrl( parent=self.valuespanel, id=wx.ID_ANY, style=wx.TE_PROCESS_ENTER) gotosizer.Add(wx.StaticText(parent=self.valuespanel, id=wx.ID_ANY, label=_("Go to:")), proportion=0, flag=wx.ALIGN_CENTER_VERTICAL | wx.RIGHT, border=5) gotosizer.Add(self.goto, proportion=1, flag=wx.EXPAND) valuesizer.Add(gotosizer, proportion=0, flag=wx.ALL | wx.EXPAND, border=5) self.hsizer.Add(columnsizer, proportion=1, flag=wx.EXPAND) self.hsizer.Add(self.valuespanel, proportion=1, flag=wx.EXPAND) self.close_onapply = wx.CheckBox(parent=self.panel, id=wx.ID_ANY, label=_("Close dialog on apply")) self.close_onapply.SetValue(True) self.pagesizer.Add(databaseboxsizer, flag=wx.ALL | wx.EXPAND, border=5) self.pagesizer.Add( modesizer, proportion=0, flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border=5) self.pagesizer.Add( self.hsizer, proportion=1, flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border=5) # self.pagesizer.Add(self.btn_uniqe,0,wx.ALIGN_LEFT|wx.TOP,border=5) # self.pagesizer.Add(self.btn_uniqesample,0,wx.ALIGN_LEFT|wx.TOP,border=5) self.pagesizer.Add(self.btn_logicpanel, proportion=0, flag=wx.ALIGN_CENTER_HORIZONTAL) self.pagesizer.Add(sqlboxsizer, proportion=0, flag=wx.EXPAND | wx.LEFT | wx.RIGHT, border=5) self.pagesizer.Add(self.buttonsizer, proportion=0, flag=wx.ALIGN_RIGHT | wx.ALL, border=5) self.pagesizer.Add( self.close_onapply, proportion=0, flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND, border=5) # # bindings # self.mode.Bind(wx.EVT_RADIOBOX, self.OnMode) # self.text_sql.Bind(wx.EVT_ACTIVATE, self.OnTextSqlActivate)TODO self.btn_unique.Bind(wx.EVT_BUTTON, self.OnUniqueValues) self.btn_uniquesample.Bind(wx.EVT_BUTTON, self.OnSampleValues) for key, value in self.btn_logic.iteritems(): self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.OnAddMark) self.btn_close.Bind(wx.EVT_BUTTON, self.OnClose) self.btn_clear.Bind(wx.EVT_BUTTON, self.OnClear) self.btn_apply.Bind(wx.EVT_BUTTON, self.OnApply) self.list_columns.Bind(wx.EVT_LISTBOX, self.OnAddColumn) self.list_values.Bind(wx.EVT_LISTBOX, self.OnAddValue) self.goto.Bind(wx.EVT_TEXT, self.OnGoTo) self.goto.Bind(wx.EVT_TEXT_ENTER, self.OnAddValue) def OnUniqueValues(self, event, justsample=False): """Get unique values""" vals = [] try: idx = self.list_columns.GetSelections()[0] column = self.list_columns.GetString(idx) except: self.list_values.Clear() return self.list_values.Clear() data = grass.db_select( sql="SELECT %s FROM %s" % (column, self.tablename), database=self.database, driver=self.driver, sep='{_sep_}') if not data: return desc = self.dbInfo.GetTableDesc( self.dbInfo.GetTable(self.layer))[column] i = 0 for item in sorted(set(map(lambda x: desc['ctype'](x[0]), data))): if justsample and i > 255: break if desc['type'] != 'character': item = str(item) else: item = GetUnicodeValue(item) self.list_values.Append(item) i += 1 def OnSampleValues(self, event): """Get sample values""" self.OnUniqueValues(None, True) def OnAddColumn(self, event): """Add column name to the query""" idx = self.list_columns.GetSelections() for i in idx: column = self.list_columns.GetString(i) self._add(element='column', value=column) if not self.btn_uniquesample.IsEnabled(): self.btn_uniquesample.Enable(True) self.btn_unique.Enable(True) def OnAddValue(self, event): """Add value""" selection = self.list_values.GetSelections() if not selection: event.Skip() return idx = selection[0] value = self.list_values.GetString(idx) idx = self.list_columns.GetSelections()[0] column = self.list_columns.GetString(idx) ctype = self.dbInfo.GetTableDesc( self.dbInfo.GetTable( self.layer))[column]['type'] if ctype == 'character': value = "'%s'" % value self._add(element='value', value=value) def OnGoTo(self, event): # clear all previous selections for item in self.list_values.GetSelections(): self.list_values.Deselect(item) gotoText = event.GetString() lenLimit = len(gotoText) found = idx = 0 for item in self.list_values.GetItems(): if item[:lenLimit] == gotoText: found = idx break idx += 1 if found > 0: self.list_values.SetSelection(found) def OnAddMark(self, event): """Add mark""" mark = None if self.btn_logicpanel and \ self.btn_logicpanel.IsShown(): btns = self.btn_logic elif self.btn_arithmeticpanel and \ self.btn_arithmeticpanel.IsShown(): btns = self.btn_arithmetic for key, value in btns.iteritems(): if event.GetId() == value[1]: mark = value[0] break self._add(element='mark', value=mark) def GetSQLStatement(self): """Return SQL statement""" return self.text_sql.GetValue().strip().replace("\n", " ") def OnClose(self, event): self.Destroy() event.Skip() class SQLBuilderSelect(SQLBuilder): """Class for building SELECT SQL statement""" def __init__(self, parent, vectmap, id=wx.ID_ANY, layer=1, evtHandler=None): self.evtHandler = evtHandler # set dialog title title = _("GRASS SQL Builder (%(type)s) - <%(map)s>") % \ {'type': "SELECT", 'map': vectmap} modeChoices = [_("Column to show (SELECT clause)"), _("Constraint for query (WHERE clause)")] SQLBuilder.__init__(self, parent, title, vectmap, id=wx.ID_ANY, modeChoices=modeChoices, layer=layer) def _doLayout(self, modeChoices): """Do dialog layout""" SQLBuilder._doLayout(self, modeChoices) self.text_sql.SetValue("SELECT * FROM %s" % self.tablename) self.text_sql.SetToolTipString( _("Example: %s") % "SELECT * FROM roadsmajor WHERE MULTILANE = 'no' OR OBJECTID < 10") self.btn_verify = wx.Button(parent=self.panel, id=wx.ID_ANY, label=_("Verify")) self.btn_verify.SetToolTipString(_("Verify SQL statement")) self.buttonsizer.Insert(1, self.btn_verify) self.text_sql.Bind(wx.EVT_TEXT, self.OnText) self.btn_verify.Bind(wx.EVT_BUTTON, self.OnVerify) self.text_sql.SetInsertionPoint(self.text_sql.GetLastPosition()) self.statusbar.SetStatusText(_("SQL statement not verified"), 0) def OnApply(self, event): """Apply button pressed""" if self.evtHandler: self.evtHandler(event='apply') if self.close_onapply.IsChecked(): self.Destroy() event.Skip() def OnClear(self, event): """Clear button pressed""" self.text_sql.SetValue("SELECT * FROM %s" % self.tablename) def OnMode(self, event): """Adjusts builder for chosen mode""" if self.mode.GetSelection() == 0: self.valuespanel.Hide() self.btn_logicpanel.Hide() elif self.mode.GetSelection() == 1: self.valuespanel.Show() self.btn_logicpanel.Show() self.pagesizer.Layout() def OnText(self, event): """Query string changed""" if len(self.text_sql.GetValue()) > 0: self.btn_verify.Enable(True) else: self.btn_verify.Enable(False) def OnVerify(self, event): """Verify button pressed""" ret, msg = RunCommand('db.select', getErrorMsg=True, table=self.tablename, sql=self.text_sql.GetValue(), flags='t', driver=self.driver, database=self.database) if ret != 0 and msg: self.statusbar.SetStatusText(_("SQL statement is not valid"), 0) GError(parent=self, message=_("SQL statement is not valid.\n\n%s") % msg) else: self.statusbar.SetStatusText(_("SQL statement is valid"), 0) def _add(self, element, value): """Add element to the query :param element: element to add (column, value) """ sqlstr = self.text_sql.GetValue() curspos = self.text_sql.GetInsertionPoint() newsqlstr = '' if element == 'column': if self.mode.GetSelection() == 0: # -> column idx1 = len('select') idx2 = sqlstr.lower().find('from') colstr = sqlstr[idx1:idx2].strip() if colstr == '*': cols = [] else: cols = colstr.split(',') if value in cols: cols.remove(value) else: cols.append(value) if len(cols) < 1: cols = ['*', ] newsqlstr = 'SELECT ' + ','.join(cols) + ' ' curspos = len(newsqlstr) newsqlstr += sqlstr[idx2:] else: # -> where newsqlstr = '' if sqlstr.lower().find('where') < 0: newsqlstr += ' WHERE' newsqlstr += ' ' + value curspos = self.text_sql.GetLastPosition() + len(newsqlstr) newsqlstr = sqlstr + newsqlstr elif element in ['value', 'mark']: addstr = ' ' + value + ' ' newsqlstr = sqlstr[:curspos] + addstr + sqlstr[curspos:] curspos += len(addstr) if newsqlstr: self.text_sql.SetValue(newsqlstr) wx.CallAfter(self.text_sql.SetFocus) self.text_sql.SetInsertionPoint(curspos) def CloseOnApply(self): """Return True if the dialog will be close on apply""" return self.close_onapply.IsChecked() def OnClose(self, event): """Close button pressed""" if self.evtHandler: self.evtHandler(event='close') SQLBuilder.OnClose(self, event) class SQLBuilderUpdate(SQLBuilder): """Class for building UPDATE SQL statement""" def __init__(self, parent, vectmap, id=wx.ID_ANY, layer=1, column=None): self.column = column # set dialog title title = _("GRASS SQL Builder (%(type)s) - <%(map)s>") % \ {'type': "UPDATE", 'map': vectmap} modeChoices = [_("Column to set (SET clause)"), _("Constraint for query (WHERE clause)"), _("Calculate column value to set")] SQLBuilder.__init__(self, parent, title, vectmap, id=wx.ID_ANY, modeChoices=modeChoices, layer=layer) # signals self.sqlApplied = Signal("SQLBuilder.sqlApplied") if parent: # TODO: replace by giface self.sqlApplied.connect(parent.Update) def _doLayout(self, modeChoices): """Do dialog layout""" SQLBuilder._doLayout(self, modeChoices) self.initText = "UPDATE %s SET" % self.tablename if self.column: self.initText += " %s = " % self.column self.text_sql.SetValue(self.initText) self.btn_arithmetic = {'eq': ['=', ], 'brac': ['()', ], 'plus': ['+', ], 'minus': ['-', ], 'divide': ['/', ], 'multiply': ['*', ]} self.btn_arithmeticpanel = wx.Panel(parent=self.panel, id=wx.ID_ANY) for key, value in self.btn_arithmetic.iteritems(): btn = wx.Button(parent=self.btn_arithmeticpanel, id=wx.ID_ANY, label=value[0]) self.btn_arithmetic[key].append(btn.GetId()) btn_arithmeticsizer = wx.GridBagSizer(hgap=5, vgap=5) btn_arithmeticsizer.Add( self.FindWindowById( self.btn_arithmetic['eq'][1]), pos=( 0, 0)) btn_arithmeticsizer.Add( self.FindWindowById( self.btn_arithmetic['brac'][1]), pos=( 1, 0)) btn_arithmeticsizer.Add( self.FindWindowById( self.btn_arithmetic['plus'][1]), pos=( 0, 1)) btn_arithmeticsizer.Add( self.FindWindowById( self.btn_arithmetic['minus'][1]), pos=( 1, 1)) btn_arithmeticsizer.Add( self.FindWindowById( self.btn_arithmetic['divide'][1]), pos=( 0, 2)) btn_arithmeticsizer.Add( self.FindWindowById( self.btn_arithmetic['multiply'][1]), pos=( 1, 2)) self.btn_arithmeticpanel.SetSizer(btn_arithmeticsizer) self.pagesizer.Insert(3, self.btn_arithmeticpanel, proportion=0, flag=wx.ALIGN_CENTER_HORIZONTAL) self.funcpanel = wx.Panel(parent=self.panel, id=wx.ID_ANY) self._initSqlFunctions() funcsbox = wx.StaticBox(parent=self.funcpanel, id=wx.ID_ANY, label=" %s " % _("Functions")) funcsizer = wx.StaticBoxSizer(funcsbox, wx.VERTICAL) self.list_func = wx.ListBox(parent=self.funcpanel, id=wx.ID_ANY, choices=self.sqlFuncs['sqlite'].keys(), style=wx.LB_SORT) funcsizer.Add(self.list_func, proportion=1, flag=wx.EXPAND) self.funcpanel.SetSizer(funcsizer) self.hsizer.Insert(2, self.funcpanel, proportion=1, flag=wx.EXPAND) self.list_func.Bind(wx.EVT_LISTBOX, self.OnAddFunc) for key, value in self.btn_arithmetic.iteritems(): self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.OnAddMark) self.mode.SetSelection(0) self.OnMode(None) self.text_sql.SetInsertionPoint(self.text_sql.GetLastPosition()) def OnApply(self, event): """Apply button pressed""" ret, msg = RunCommand('db.execute', getErrorMsg=True, parent=self, stdin=self.text_sql.GetValue(), input='-', driver=self.driver, database=self.database) if ret != 0 and msg: self.statusbar.SetStatusText(_("SQL statement was not applied"), 0) else: self.statusbar.SetStatusText(_("SQL statement applied"), 0) self.sqlApplied.emit() def OnClear(self, event): """Clear button pressed""" self.text_sql.SetValue(self.initText) def OnMode(self, event): """Adjusts builder for chosen mode""" if self.mode.GetSelection() == 0: self.valuespanel.Hide() self.btn_logicpanel.Hide() self.btn_arithmeticpanel.Hide() self.funcpanel.Hide() elif self.mode.GetSelection() == 1: self.valuespanel.Show() self.btn_logicpanel.Show() self.btn_arithmeticpanel.Hide() self.funcpanel.Hide() elif self.mode.GetSelection() == 2: self.valuespanel.Hide() self.btn_logicpanel.Hide() self.btn_arithmeticpanel.Show() self.funcpanel.Show() self.pagesizer.Layout() def OnAddFunc(self, event): """Add function to the query""" if self.driver == 'dbf': GMessage( parent=self, message=_( "Dbf driver does not support usage of SQL functions.")) return idx = self.list_func.GetSelections() for i in idx: func = self.sqlFuncs['sqlite'][self.list_func.GetString(i)][0] self._add(element='func', value=func) def _add(self, element, value): """Add element to the query :param element: element to add (column, value) """ sqlstr = self.text_sql.GetValue() curspos = self.text_sql.GetInsertionPoint() newsqlstr = '' if element in ['value', 'mark', 'func'] or \ (element == 'column' and self.mode.GetSelection() == 2): addstr = ' ' + value + ' ' newsqlstr = sqlstr[:curspos] + addstr + sqlstr[curspos:] curspos += len(addstr) elif element == 'column': if self.mode.GetSelection() == 0: # -> column idx1 = sqlstr.lower().find('set') + len('set') idx2 = sqlstr.lower().find('where') if idx2 >= 0: colstr = sqlstr[idx1:idx2].strip() else: colstr = sqlstr[idx1:].strip() cols = [col.split('=')[0].strip() for col in colstr.split(',')] if unicode(value) in cols: self.text_sql.SetInsertionPoint(curspos) wx.CallAfter(self.text_sql.SetFocus) return if colstr: colstr += ',' colstr = ' ' + colstr colstr += ' ' + value + '= ' newsqlstr = sqlstr[:idx1] + colstr if idx2 >= 0: newsqlstr += sqlstr[idx2:] curspos = idx1 + len(colstr) elif self.mode.GetSelection() == 1: # -> where newsqlstr = '' if sqlstr.lower().find('where') < 0: newsqlstr += ' WHERE' newsqlstr += ' ' + value curspos = self.text_sql.GetLastPosition() + len(newsqlstr) newsqlstr = sqlstr + newsqlstr if newsqlstr: self.text_sql.SetValue(newsqlstr) wx.CallAfter(self.text_sql.SetFocus) self.text_sql.SetInsertionPoint(curspos) def _initSqlFunctions(self): self.sqlFuncs = {} # TODO add functions for other drivers self.sqlFuncs['sqlite'] = { 'ABS': ['ABS()'], 'LENGTH': ['LENGTH()'], 'LOWER': ['LOWER()'], 'LTRIM': ['LTRIM(,)'], 'MAX': ['MAX()'], 'MIN': ['MIN()'], 'RTRIM': ['RTRIM(,)'], 'SUBSTR': ['SUBSTR (,[,])'], 'TRIM': ['TRIM (,)'] } if __name__ == "__main__": if len(sys.argv) not in [3, 4]: print(__doc__, file=sys.stderr) sys.exit() if len(sys.argv) == 3: layer = 1 else: layer = int(sys.argv[3]) if sys.argv[1] == 'select': sqlBuilder = SQLBuilderSelect elif sys.argv[1] == 'update': sqlBuilder = SQLBuilderUpdate else: print(__doc__, file=sys.stderr) sys.exit() app = wx.App(0) sqlb = sqlBuilder(parent=None, vectmap=sys.argv[2], layer=layer) sqlb.Show() app.MainLoop()