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, showDbInfo=False):
"""Do dialog layout"""
self.pagesizer = wx.BoxSizer(wx.VERTICAL)
# dbInfo
if showDbInfo:
databasebox = 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 = StaticBox(parent=self.panel, id=wx.ID_ANY,
label=" %s " % _("Query"))
sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL)
self.text_sql = 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 = Button(parent=self.panel, id=wx.ID_CLEAR)
self.btn_clear.SetToolTip(_("Set SQL statement to default"))
self.btn_apply = Button(parent=self.panel, id=wx.ID_APPLY)
self.btn_apply.SetToolTip(_("Apply SQL statement"))
self.btn_close = Button(parent=self.panel, id=wx.ID_CLOSE)
self.btn_close.SetToolTip(_("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 six.iteritems(self.btn_logic):
btn = 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 = 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)
if modeChoices:
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 = 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 = Button(parent=self.valuespanel, id=wx.ID_ANY,
label=_("Get all values"))
self.btn_unique.Enable(False)
self.btn_uniquesample = Button(
parent=self.valuespanel,
id=wx.ID_ANY,
label=_("Get sample"))
self.btn_uniquesample.SetToolTip(
_("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 = TextCtrl(
parent=self.valuespanel,
id=wx.ID_ANY,
style=wx.TE_PROCESS_ENTER)
gotosizer.Add(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)
if showDbInfo:
self.pagesizer.Add(databaseboxsizer,
flag=wx.ALL | wx.EXPAND, border=5)
if modeChoices:
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
#
if modeChoices:
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 six.iteritems(self.btn_logic):
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()
sql = "SELECT DISTINCT {column} FROM {table} ORDER BY {column}".format(
column=column, table=self.tablename)
if justsample:
sql += " LIMIT {}".format(255)
data = grass.db_select(
sql=sql,
database=self.database,
driver=self.driver,
sep='{_sep_}')
if not data:
return
desc = self.dbInfo.GetTableDesc(
self.dbInfo.GetTable(self.layer))[column]
i = 0
items = []
for item in data: #sorted(set(map(lambda x: desc['ctype'](x[0]), data))):
if desc['type'] not in ('character', 'text'):
items.append(str(item[0]))
else:
items.append(u"'{}'".format(GetUnicodeValue(item[0])))
i += 1
self.list_values.AppendItems(items)
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']
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
string = False
for item in self.list_values.GetItems():
if idx == 0 and item.startswith("'"):
string = True
if string:
item = item[1:-1] # strip "'"
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 six.iteritems(btns):
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()
