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 __init__(self,
parent,
title,
vectmap,
id=wx.ID_ANY,
layer=1,
qtype="select",
evtHandler=None):
wx.Frame.__init__(self, parent, id, title)
self.SetIcon(
wx.Icon(os.path.join(globalvar.ETCICONDIR, 'grass_sql.ico'),
wx.BITMAP_TYPE_ICO))
self.parent = parent
self.evtHandler = evtHandler
#
# variables
#
self.vectmap = vectmap # fullname
if not "@" in self.vectmap:
self.vectmap = grass.find_file(self.vectmap,
element='vector')['fullname']
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.qtype = qtype # type of query: SELECT, UPDATE, DELETE, ...
self.colvalues = [] # array with unique values in selected column
# set dialog title
self.SetTitle(_("GRASS SQL Builder (%(type)s): vector map <%(map)s>") % \
{ 'type' : self.qtype.upper(), 'map' : self.vectmap })
self.panel = wx.Panel(parent=self, id=wx.ID_ANY)
# statusbar
self.statusbar = self.CreateStatusBar(number=1)
self.statusbar.SetStatusText(_("SQL statement not verified"), 0)
self._doLayout()
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)
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(item=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(item=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(item=self.btn_clear)
self.buttonsizer.Add(item=self.btn_apply)
self.buttonsizer.Add(item=self.btn_close)
btn_logicsizer = wx.GridBagSizer(5, 5)
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['is'][1]),
pos=(0, 0))
btn_logicsizer.Add(item=self.FindWindowById(
self.btn_logic['isnot'][1]),
pos=(1, 0))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['like'][1]),
pos=(2, 0))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['gt'][1]),
pos=(0, 1))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['ge'][1]),
pos=(1, 1))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['or'][1]),
pos=(2, 1))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['lt'][1]),
pos=(0, 2))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['le'][1]),
pos=(1, 2))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['not'][1]),
pos=(2, 2))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['brac'][1]),
pos=(0, 3))
btn_logicsizer.Add(item=self.FindWindowById(self.btn_logic['prc'][1]),
pos=(1, 3))
btn_logicsizer.Add(item=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(item=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(item=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(item=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(item=self.btn_uniquesample,
proportion=0,
flag=wx.ALIGN_CENTER_HORIZONTAL | wx.RIGHT,
border=5)
buttonsizer3.Add(item=self.btn_unique,
proportion=0,
flag=wx.ALIGN_CENTER_HORIZONTAL)
valuesizer.Add(item=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(item=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(item=self.goto, proportion=1, flag=wx.EXPAND)
valuesizer.Add(item=gotosizer,
proportion=0,
flag=wx.ALL | wx.EXPAND,
border=5)
self.hsizer.Add(item=columnsizer, proportion=1, flag=wx.EXPAND)
self.hsizer.Add(item=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(item=databaseboxsizer,
flag=wx.ALL | wx.EXPAND,
border=5)
self.pagesizer.Add(item=modesizer,
proportion=0,
flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND,
border=5)
self.pagesizer.Add(item=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(item=self.btn_logicpanel,
proportion=0,
flag=wx.ALIGN_CENTER_HORIZONTAL)
self.pagesizer.Add(item=sqlboxsizer,
proportion=0,
flag=wx.EXPAND | wx.LEFT | wx.RIGHT,
border=5)
self.pagesizer.Add(item=self.buttonsizer,
proportion=0,
flag=wx.ALIGN_RIGHT | wx.ALL,
border=5)
self.pagesizer.Add(item=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)
self.panel.SetAutoLayout(True)
self.panel.SetSizer(self.pagesizer)
self.pagesizer.Fit(self.panel)
self.Layout()
self.SetMinSize((400, 600))
self.SetClientSize(self.panel.GetSize())
self.CenterOnParent()
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()
def UpdateDialog(self,
map=None,
query=None,
cats=None,
fid=-1,
action=None):
"""Update dialog
:param map: name of vector map
:param query:
:param cats:
:param fid: feature id
:param action: add, update, display or None
:return: True if updated
:return: False
"""
if action:
self.action = action
if action == 'display':
enabled = False
else:
enabled = True
self.closeDialog.Enable(enabled)
self.FindWindowById(wx.ID_OK).Enable(enabled)
if map:
self.map = map
# get layer/table/column information
self.mapDBInfo = VectorDBInfo(self.map)
if not self.mapDBInfo:
return False
self.mapDBInfo.Reset()
layers = self.mapDBInfo.layers.keys() # get available layers
# id of selected line
if query: # select by position
data = self.mapDBInfo.SelectByPoint(query[0], query[1])
self.cats = {}
if data and 'Layer' in data:
idx = 0
for layer in data['Layer']:
layer = int(layer)
if data['Id'][idx] is not None:
tfid = int(data['Id'][idx])
else:
tfid = 0 # Area / Volume
if tfid not in self.cats:
self.cats[tfid] = {}
if layer not in self.cats[tfid]:
self.cats[tfid][layer] = []
cat = int(data['Category'][idx])
self.cats[tfid][layer].append(cat)
idx += 1
else:
self.cats = cats
if fid > 0:
self.fid = fid
elif len(self.cats.keys()) > 0:
self.fid = list(self.cats.keys())[0]
else:
self.fid = -1
if len(self.cats.keys()) == 1:
self.fidMulti.Show(False)
self.fidText.Show(True)
if self.fid > 0:
self.fidText.SetLabel("%d" % self.fid)
else:
self.fidText.SetLabel(_("Unknown"))
else:
self.fidMulti.Show(True)
self.fidText.Show(False)
choices = []
for tfid in self.cats.keys():
choices.append(str(tfid))
self.fidMulti.SetItems(choices)
self.fidMulti.SetStringSelection(str(self.fid))
# reset notebook
self.notebook.DeleteAllPages()
for layer in layers: # for each layer
if not query: # select by layer/cat
if self.fid > 0 and layer in self.cats[self.fid]:
for cat in self.cats[self.fid][layer]:
nselected = self.mapDBInfo.SelectFromTable(
layer,
where="%s=%d" %
(self.mapDBInfo.layers[layer]['key'], cat))
else:
nselected = 0
# if nselected <= 0 and self.action != "add":
# continue # nothing selected ...
if self.action == "add":
if nselected <= 0:
if layer in self.cats[self.fid]:
table = self.mapDBInfo.layers[layer]["table"]
key = self.mapDBInfo.layers[layer]["key"]
columns = self.mapDBInfo.tables[table]
for name in columns.keys():
if name == key:
for cat in self.cats[self.fid][layer]:
self.mapDBInfo.tables[table][name][
'values'].append(cat)
else:
self.mapDBInfo.tables[table][name][
'values'].append(None)
else: # change status 'add' -> 'update'
self.action = "update"
table = self.mapDBInfo.layers[layer]["table"]
key = self.mapDBInfo.layers[layer]["key"]
columns = self.mapDBInfo.tables[table]
for idx in range(len(columns[key]['values'])):
for name in columns.keys():
if name == key:
cat = int(columns[name]['values'][idx])
break
# use scrolled panel instead (and fix initial max height of the
# window to 480px)
panel = scrolled.ScrolledPanel(parent=self.notebook,
id=wx.ID_ANY,
size=(-1, 150))
panel.SetupScrolling(scroll_x=False)
self.notebook.AddPage(page=panel,
text=" %s %d / %s %d" %
(_("Layer"), layer, _("Category"), cat))
# notebook body
border = wx.BoxSizer(wx.VERTICAL)
flexSizer = wx.FlexGridSizer(cols=3, hgap=3, vgap=3)
flexSizer.AddGrowableCol(2)
# columns (sorted by index)
names = [''] * len(columns.keys())
for name in columns.keys():
names[columns[name]['index']] = name
for name in names:
if name == key: # skip key column (category)
continue
vtype = columns[name]['type'].lower()
ctype = columns[name]['ctype']
if columns[name]['values'][idx] is not None:
if not isinstance(columns[name]['ctype'],
six.string_types):
value = str(columns[name]['values'][idx])
else:
value = columns[name]['values'][idx]
else:
value = ''
colName = StaticText(parent=panel,
id=wx.ID_ANY,
label=name)
colType = StaticText(parent=panel,
id=wx.ID_ANY,
label="[%s]:" % vtype)
colValue = TextCtrl(parent=panel,
id=wx.ID_ANY,
value=value)
colValue.SetName(name)
if ctype == int:
colValue.SetValidator(IntegerValidator())
elif ctype == float:
colValue.SetValidator(FloatValidator())
self.Bind(wx.EVT_TEXT, self.OnSQLStatement, colValue)
if self.action == 'display':
colValue.SetWindowStyle(wx.TE_READONLY)
flexSizer.Add(colName,
proportion=0,
flag=wx.ALIGN_CENTER_VERTICAL)
flexSizer.Add(colType,
proportion=0,
flag=wx.ALIGN_CENTER_VERTICAL
| wx.ALIGN_RIGHT)
flexSizer.Add(colValue,
proportion=1,
flag=wx.EXPAND | wx.ALIGN_CENTER_VERTICAL)
# add widget reference to self.columns
columns[name]['ids'].append(
colValue.GetId()) # name, type, values, id
# for each attribute (including category) END
border.Add(flexSizer,
proportion=1,
flag=wx.ALL | wx.EXPAND,
border=5)
panel.SetSizer(border)
# for each category END
# for each layer END
if self.notebook.GetPageCount() == 0:
self.noFoundMsg.Show(True)
else:
self.noFoundMsg.Show(False)
self.Layout()
return True
def __init__(self,
parent,
map,
query=None,
cats=None,
line=None,
style=wx.DEFAULT_DIALOG_STYLE | wx.RESIZE_BORDER,
pos=wx.DefaultPosition,
action="add",
ignoreError=False):
"""Standard dialog used to add/update/display attributes linked
to the vector map.
Attribute data can be selected based on layer and category number
or coordinates.
:param parent:
:param map: vector map
:param query: query coordinates and distance (used for v.edit)
:param cats: {layer: cats}
:param line: feature id (requested for cats)
:param style:
:param pos:
:param action: (add, update, display)
:param ignoreError: True to ignore errors
"""
self.parent = parent # mapdisplay.BufferedWindow
self.map = map
self.action = action
# ids/cats of selected features
# fid : {layer : cats}
self.cats = {}
self.fid = -1 # feature id
# get layer/table/column information
self.mapDBInfo = VectorDBInfo(self.map)
layers = self.mapDBInfo.layers.keys() # get available layers
# check if db connection / layer exists
if len(layers) <= 0:
if not ignoreError:
dlg = wx.MessageDialog(
parent=self.parent,
message=_("No attribute table found.\n\n"
"Do you want to create a new attribute table "
"and defined a link to vector map <%s>?") %
self.map,
caption=_("Create table?"),
style=wx.YES_NO | wx.NO_DEFAULT | wx.ICON_QUESTION)
if dlg.ShowModal() == wx.ID_YES:
lmgr = self.parent.lmgr
lmgr.OnShowAttributeTable(event=None, selection='layers')
dlg.Destroy()
self.mapDBInfo = None
wx.Dialog.__init__(self,
parent=self.parent,
id=wx.ID_ANY,
title="",
style=style,
pos=pos)
# dialog body
mainSizer = wx.BoxSizer(wx.VERTICAL)
# notebook
self.notebook = wx.Notebook(parent=self,
id=wx.ID_ANY,
style=wx.BK_DEFAULT)
self.closeDialog = wx.CheckBox(parent=self,
id=wx.ID_ANY,
label=_("Close dialog on submit"))
self.closeDialog.SetValue(True)
if self.action == 'display':
self.closeDialog.Enable(False)
# feature id (text/choice for duplicates)
self.fidMulti = wx.Choice(parent=self, id=wx.ID_ANY, size=(150, -1))
self.fidMulti.Bind(wx.EVT_CHOICE, self.OnFeature)
self.fidText = StaticText(parent=self, id=wx.ID_ANY)
self.noFoundMsg = StaticText(parent=self,
id=wx.ID_ANY,
label=_("No attributes found"))
self.UpdateDialog(query=query, cats=cats)
# set title
if self.action == "update":
self.SetTitle(_("Update attributes"))
elif self.action == "add":
self.SetTitle(_("Define attributes"))
else:
self.SetTitle(_("Display attributes"))
# buttons
btnCancel = Button(self, wx.ID_CANCEL)
btnReset = Button(self, wx.ID_UNDO, _("&Reload"))
btnSubmit = Button(self, wx.ID_OK, _("&Submit"))
if self.action == 'display':
btnSubmit.Enable(False)
btnSizer = wx.StdDialogButtonSizer()
btnSizer.AddButton(btnCancel)
btnSizer.AddButton(btnReset)
btnSizer.SetNegativeButton(btnReset)
btnSubmit.SetDefault()
btnSizer.AddButton(btnSubmit)
btnSizer.Realize()
mainSizer.Add(self.noFoundMsg,
proportion=0,
flag=wx.EXPAND | wx.ALL,
border=5)
mainSizer.Add(self.notebook,
proportion=1,
flag=wx.EXPAND | wx.ALL,
border=5)
fidSizer = wx.BoxSizer(wx.HORIZONTAL)
fidSizer.Add(StaticText(parent=self,
id=wx.ID_ANY,
label=_("Feature id:")),
proportion=0,
border=5,
flag=wx.ALIGN_CENTER_VERTICAL)
fidSizer.Add(self.fidMulti,
proportion=0,
flag=wx.EXPAND | wx.ALL,
border=5)
fidSizer.Add(self.fidText,
proportion=0,
flag=wx.EXPAND | wx.ALL,
border=5)
mainSizer.Add(fidSizer,
proportion=0,
flag=wx.EXPAND | wx.LEFT | wx.RIGHT,
border=5)
mainSizer.Add(self.closeDialog,
proportion=0,
flag=wx.EXPAND | wx.LEFT | wx.RIGHT,
border=5)
mainSizer.Add(btnSizer,
proportion=0,
flag=wx.EXPAND | wx.ALL | wx.ALIGN_CENTER,
border=5)
# bindigs
btnReset.Bind(wx.EVT_BUTTON, self.OnReset)
btnSubmit.Bind(wx.EVT_BUTTON, self.OnSubmit)
btnCancel.Bind(wx.EVT_BUTTON, self.OnClose)
self.Bind(wx.EVT_CLOSE, self.OnClose)
self.SetSizer(mainSizer)
mainSizer.Fit(self)
# set min size for dialog
w, h = self.GetBestSize()
w += 50
if h < 200:
self.SetMinSize((w, 200))
else:
self.SetMinSize((w, h))
if self.notebook.GetPageCount() == 0:
Debug.msg(2, "DisplayAttributesDialog(): Nothing found!")
class DisplayAttributesDialog(wx.Dialog):
def __init__(self,
parent,
map,
query=None,
cats=None,
line=None,
style=wx.DEFAULT_DIALOG_STYLE | wx.RESIZE_BORDER,
pos=wx.DefaultPosition,
action="add",
ignoreError=False):
"""Standard dialog used to add/update/display attributes linked
to the vector map.
Attribute data can be selected based on layer and category number
or coordinates.
:param parent:
:param map: vector map
:param query: query coordinates and distance (used for v.edit)
:param cats: {layer: cats}
:param line: feature id (requested for cats)
:param style:
:param pos:
:param action: (add, update, display)
:param ignoreError: True to ignore errors
"""
self.parent = parent # mapdisplay.BufferedWindow
self.map = map
self.action = action
# ids/cats of selected features
# fid : {layer : cats}
self.cats = {}
self.fid = -1 # feature id
# get layer/table/column information
self.mapDBInfo = VectorDBInfo(self.map)
layers = self.mapDBInfo.layers.keys() # get available layers
# check if db connection / layer exists
if len(layers) <= 0:
if not ignoreError:
dlg = wx.MessageDialog(
parent=self.parent,
message=_("No attribute table found.\n\n"
"Do you want to create a new attribute table "
"and defined a link to vector map <%s>?") %
self.map,
caption=_("Create table?"),
style=wx.YES_NO | wx.NO_DEFAULT | wx.ICON_QUESTION)
if dlg.ShowModal() == wx.ID_YES:
lmgr = self.parent.lmgr
lmgr.OnShowAttributeTable(event=None, selection='layers')
dlg.Destroy()
self.mapDBInfo = None
wx.Dialog.__init__(self,
parent=self.parent,
id=wx.ID_ANY,
title="",
style=style,
pos=pos)
# dialog body
mainSizer = wx.BoxSizer(wx.VERTICAL)
# notebook
self.notebook = wx.Notebook(parent=self,
id=wx.ID_ANY,
style=wx.BK_DEFAULT)
self.closeDialog = wx.CheckBox(parent=self,
id=wx.ID_ANY,
label=_("Close dialog on submit"))
self.closeDialog.SetValue(True)
if self.action == 'display':
self.closeDialog.Enable(False)
# feature id (text/choice for duplicates)
self.fidMulti = wx.Choice(parent=self, id=wx.ID_ANY, size=(150, -1))
self.fidMulti.Bind(wx.EVT_CHOICE, self.OnFeature)
self.fidText = StaticText(parent=self, id=wx.ID_ANY)
self.noFoundMsg = StaticText(parent=self,
id=wx.ID_ANY,
label=_("No attributes found"))
self.UpdateDialog(query=query, cats=cats)
# set title
if self.action == "update":
self.SetTitle(_("Update attributes"))
elif self.action == "add":
self.SetTitle(_("Define attributes"))
else:
self.SetTitle(_("Display attributes"))
# buttons
btnCancel = Button(self, wx.ID_CANCEL)
btnReset = Button(self, wx.ID_UNDO, _("&Reload"))
btnSubmit = Button(self, wx.ID_OK, _("&Submit"))
if self.action == 'display':
btnSubmit.Enable(False)
btnSizer = wx.StdDialogButtonSizer()
btnSizer.AddButton(btnCancel)
btnSizer.AddButton(btnReset)
btnSizer.SetNegativeButton(btnReset)
btnSubmit.SetDefault()
btnSizer.AddButton(btnSubmit)
btnSizer.Realize()
mainSizer.Add(self.noFoundMsg,
proportion=0,
flag=wx.EXPAND | wx.ALL,
border=5)
mainSizer.Add(self.notebook,
proportion=1,
flag=wx.EXPAND | wx.ALL,
border=5)
fidSizer = wx.BoxSizer(wx.HORIZONTAL)
fidSizer.Add(StaticText(parent=self,
id=wx.ID_ANY,
label=_("Feature id:")),
proportion=0,
border=5,
flag=wx.ALIGN_CENTER_VERTICAL)
fidSizer.Add(self.fidMulti,
proportion=0,
flag=wx.EXPAND | wx.ALL,
border=5)
fidSizer.Add(self.fidText,
proportion=0,
flag=wx.EXPAND | wx.ALL,
border=5)
mainSizer.Add(fidSizer,
proportion=0,
flag=wx.EXPAND | wx.LEFT | wx.RIGHT,
border=5)
mainSizer.Add(self.closeDialog,
proportion=0,
flag=wx.EXPAND | wx.LEFT | wx.RIGHT,
border=5)
mainSizer.Add(btnSizer,
proportion=0,
flag=wx.EXPAND | wx.ALL | wx.ALIGN_CENTER,
border=5)
# bindigs
btnReset.Bind(wx.EVT_BUTTON, self.OnReset)
btnSubmit.Bind(wx.EVT_BUTTON, self.OnSubmit)
btnCancel.Bind(wx.EVT_BUTTON, self.OnClose)
self.Bind(wx.EVT_CLOSE, self.OnClose)
self.SetSizer(mainSizer)
mainSizer.Fit(self)
# set min size for dialog
w, h = self.GetBestSize()
w += 50
if h < 200:
self.SetMinSize((w, 200))
else:
self.SetMinSize((w, h))
if self.notebook.GetPageCount() == 0:
Debug.msg(2, "DisplayAttributesDialog(): Nothing found!")
### self.mapDBInfo = None
def OnSQLStatement(self, event):
"""Update SQL statement"""
pass
def IsFound(self):
"""Check for status
:return: True on attributes found
:return: False attributes not found
"""
return bool(self.mapDBInfo and self.notebook.GetPageCount() > 0)
def GetSQLString(self, updateValues=False):
"""Create SQL statement string based on self.sqlStatement
Show error message when invalid values are entered.
If updateValues is True, update dataFrame according to values
in textfields.
"""
sqlCommands = []
# find updated values for each layer/category
for layer in self.mapDBInfo.layers.keys(): # for each layer
table = self.mapDBInfo.GetTable(layer)
key = self.mapDBInfo.GetKeyColumn(layer)
columns = self.mapDBInfo.GetTableDesc(table)
for idx in range(len(columns[key]['values'])): # for each category
updatedColumns = []
updatedValues = []
for name in columns.keys():
if name == key:
cat = columns[name]['values'][idx]
continue
ctype = columns[name]['ctype']
value = columns[name]['values'][idx]
id = columns[name]['ids'][idx]
try:
newvalue = self.FindWindowById(id).GetValue()
except:
newvalue = self.FindWindowById(id).GetLabel()
if newvalue:
try:
if ctype == int:
newvalue = int(newvalue)
elif ctype == float:
newvalue = float(newvalue)
except ValueError:
GError(
parent=self,
message=
_("Column <%(col)s>: Value '%(value)s' needs to be entered as %(type)s."
) % {
'col': name,
'value': str(newvalue),
'type': columns[name]['type'].lower()
},
showTraceback=False)
sqlCommands.append(None)
continue
else:
if self.action == 'add':
continue
if newvalue != value:
updatedColumns.append(name)
if newvalue == '':
updatedValues.append('NULL')
else:
if ctype != str:
updatedValues.append(str(newvalue))
else:
updatedValues.append(
"'" + newvalue.replace("'", "''") + "'")
columns[name]['values'][idx] = newvalue
if self.action != "add" and len(updatedValues) == 0:
continue
if self.action == "add":
sqlString = "INSERT INTO %s (%s," % (table, key)
else:
sqlString = "UPDATE %s SET " % table
for idx in range(len(updatedColumns)):
name = updatedColumns[idx]
if self.action == "add":
sqlString += name + ","
else:
sqlString += name + "=" + updatedValues[idx] + ","
sqlString = sqlString[:-1] # remove last comma
if self.action == "add":
sqlString += ") VALUES (%s," % cat
for value in updatedValues:
sqlString += value + ","
sqlString = sqlString[:-1] # remove last comma
sqlString += ")"
else:
sqlString += " WHERE %s=%s" % (key, cat)
sqlCommands.append(sqlString)
# for each category
# for each layer END
Debug.msg(3,
"DisplayAttributesDialog.GetSQLString(): %s" % sqlCommands)
return sqlCommands
def OnReset(self, event=None):
"""Reset form"""
for layer in self.mapDBInfo.layers.keys():
table = self.mapDBInfo.layers[layer]["table"]
key = self.mapDBInfo.layers[layer]["key"]
columns = self.mapDBInfo.tables[table]
for idx in range(len(columns[key]['values'])):
for name in columns.keys():
type = columns[name]['type']
value = columns[name]['values'][idx]
if value is None:
value = ''
try:
id = columns[name]['ids'][idx]
except IndexError:
id = wx.NOT_FOUND
if name != key and id != wx.NOT_FOUND:
self.FindWindowById(id).SetValue(str(value))
def OnClose(self, event):
"""Closes dialog and removes query layer.
"""
frame = self.parent.parent
frame.dialogs['attributes'] = None
if hasattr(self, "digit"):
self.parent.digit.GetDisplay().SetSelected([])
if frame.IsAutoRendered():
self.parent.UpdateMap(render=False)
elif frame.IsAutoRendered():
frame.RemoveQueryLayer()
self.parent.UpdateMap(render=True)
if self.IsModal():
self.EndModal(wx.ID_OK)
else:
self.Destroy()
def OnSubmit(self, event):
"""Submit records"""
layer = 1
close = True
enc = GetDbEncoding()
for sql in self.GetSQLString(updateValues=True):
if not sql:
close = False
continue
sql = sql.encode(enc)
driver, database = self.mapDBInfo.GetDbSettings(layer)
Debug.msg(1, "SQL: %s" % sql)
RunCommand('db.execute',
parent=self,
quiet=True,
input='-',
stdin=sql,
driver=driver,
database=database)
layer += 1
if close and self.closeDialog.IsChecked():
self.OnClose(event)
def OnFeature(self, event):
self.fid = int(event.GetString())
self.UpdateDialog(cats=self.cats, fid=self.fid)
def GetCats(self):
"""Get id of selected vector object or 'None' if nothing selected
:param id: if true return ids otherwise cats
"""
if self.fid < 0:
return None
return self.cats[self.fid]
def GetFid(self):
"""Get selected feature id"""
return self.fid
def UpdateDialog(self,
map=None,
query=None,
cats=None,
fid=-1,
action=None):
"""Update dialog
:param map: name of vector map
:param query:
:param cats:
:param fid: feature id
:param action: add, update, display or None
:return: True if updated
:return: False
"""
if action:
self.action = action
if action == 'display':
enabled = False
else:
enabled = True
self.closeDialog.Enable(enabled)
self.FindWindowById(wx.ID_OK).Enable(enabled)
if map:
self.map = map
# get layer/table/column information
self.mapDBInfo = VectorDBInfo(self.map)
if not self.mapDBInfo:
return False
self.mapDBInfo.Reset()
layers = self.mapDBInfo.layers.keys() # get available layers
# id of selected line
if query: # select by position
data = self.mapDBInfo.SelectByPoint(query[0], query[1])
self.cats = {}
if data and 'Layer' in data:
idx = 0
for layer in data['Layer']:
layer = int(layer)
if data['Id'][idx] is not None:
tfid = int(data['Id'][idx])
else:
tfid = 0 # Area / Volume
if tfid not in self.cats:
self.cats[tfid] = {}
if layer not in self.cats[tfid]:
self.cats[tfid][layer] = []
cat = int(data['Category'][idx])
self.cats[tfid][layer].append(cat)
idx += 1
else:
self.cats = cats
if fid > 0:
self.fid = fid
elif len(self.cats.keys()) > 0:
self.fid = list(self.cats.keys())[0]
else:
self.fid = -1
if len(self.cats.keys()) == 1:
self.fidMulti.Show(False)
self.fidText.Show(True)
if self.fid > 0:
self.fidText.SetLabel("%d" % self.fid)
else:
self.fidText.SetLabel(_("Unknown"))
else:
self.fidMulti.Show(True)
self.fidText.Show(False)
choices = []
for tfid in self.cats.keys():
choices.append(str(tfid))
self.fidMulti.SetItems(choices)
self.fidMulti.SetStringSelection(str(self.fid))
# reset notebook
self.notebook.DeleteAllPages()
for layer in layers: # for each layer
if not query: # select by layer/cat
if self.fid > 0 and layer in self.cats[self.fid]:
for cat in self.cats[self.fid][layer]:
nselected = self.mapDBInfo.SelectFromTable(
layer,
where="%s=%d" %
(self.mapDBInfo.layers[layer]['key'], cat))
else:
nselected = 0
# if nselected <= 0 and self.action != "add":
# continue # nothing selected ...
if self.action == "add":
if nselected <= 0:
if layer in self.cats[self.fid]:
table = self.mapDBInfo.layers[layer]["table"]
key = self.mapDBInfo.layers[layer]["key"]
columns = self.mapDBInfo.tables[table]
for name in columns.keys():
if name == key:
for cat in self.cats[self.fid][layer]:
self.mapDBInfo.tables[table][name][
'values'].append(cat)
else:
self.mapDBInfo.tables[table][name][
'values'].append(None)
else: # change status 'add' -> 'update'
self.action = "update"
table = self.mapDBInfo.layers[layer]["table"]
key = self.mapDBInfo.layers[layer]["key"]
columns = self.mapDBInfo.tables[table]
for idx in range(len(columns[key]['values'])):
for name in columns.keys():
if name == key:
cat = int(columns[name]['values'][idx])
break
# use scrolled panel instead (and fix initial max height of the
# window to 480px)
panel = scrolled.ScrolledPanel(parent=self.notebook,
id=wx.ID_ANY,
size=(-1, 150))
panel.SetupScrolling(scroll_x=False)
self.notebook.AddPage(page=panel,
text=" %s %d / %s %d" %
(_("Layer"), layer, _("Category"), cat))
# notebook body
border = wx.BoxSizer(wx.VERTICAL)
flexSizer = wx.FlexGridSizer(cols=3, hgap=3, vgap=3)
flexSizer.AddGrowableCol(2)
# columns (sorted by index)
names = [''] * len(columns.keys())
for name in columns.keys():
names[columns[name]['index']] = name
for name in names:
if name == key: # skip key column (category)
continue
vtype = columns[name]['type'].lower()
ctype = columns[name]['ctype']
if columns[name]['values'][idx] is not None:
if not isinstance(columns[name]['ctype'],
six.string_types):
value = str(columns[name]['values'][idx])
else:
value = columns[name]['values'][idx]
else:
value = ''
colName = StaticText(parent=panel,
id=wx.ID_ANY,
label=name)
colType = StaticText(parent=panel,
id=wx.ID_ANY,
label="[%s]:" % vtype)
colValue = TextCtrl(parent=panel,
id=wx.ID_ANY,
value=value)
colValue.SetName(name)
if ctype == int:
colValue.SetValidator(IntegerValidator())
elif ctype == float:
colValue.SetValidator(FloatValidator())
self.Bind(wx.EVT_TEXT, self.OnSQLStatement, colValue)
if self.action == 'display':
colValue.SetWindowStyle(wx.TE_READONLY)
flexSizer.Add(colName,
proportion=0,
flag=wx.ALIGN_CENTER_VERTICAL)
flexSizer.Add(colType,
proportion=0,
flag=wx.ALIGN_CENTER_VERTICAL
| wx.ALIGN_RIGHT)
flexSizer.Add(colValue,
proportion=1,
flag=wx.EXPAND | wx.ALIGN_CENTER_VERTICAL)
# add widget reference to self.columns
columns[name]['ids'].append(
colValue.GetId()) # name, type, values, id
# for each attribute (including category) END
border.Add(flexSizer,
proportion=1,
flag=wx.ALL | wx.EXPAND,
border=5)
panel.SetSizer(border)
# for each category END
# for each layer END
if self.notebook.GetPageCount() == 0:
self.noFoundMsg.Show(True)
else:
self.noFoundMsg.Show(False)
self.Layout()
return True
def SetColumnValue(self, layer, column, value):
"""Set attrbute value
:param column: column name
:param value: value
"""
table = self.mapDBInfo.GetTable(layer)
columns = self.mapDBInfo.GetTableDesc(table)
for key, col in six.iteritems(columns):
if key == column:
col['values'] = [
col['ctype'](value),
]
break
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 = ClearButton(parent=self.panel)
self.btn_clear.SetToolTip(_("Set SQL statement to default"))
self.btn_apply = ApplyButton(parent=self.panel)
self.btn_apply.SetToolTip(_("Apply SQL statement"))
self.btn_close = CloseButton(parent=self.panel)
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.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.RIGHT,
border=5)
buttonsizer3.Add(self.btn_unique, proportion=0)
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("'{}'".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()
class SQLFrame(wx.Frame):
"""!SQL Frame class"""
def __init__(self,
parent,
title,
vectmap,
id=wx.ID_ANY,
layer=1,
qtype="select",
evtHandler=None):
wx.Frame.__init__(self, parent, id, title)
self.SetIcon(
wx.Icon(os.path.join(globalvar.ETCICONDIR, 'grass_sql.ico'),
wx.BITMAP_TYPE_ICO))
self.parent = parent
self.evtHandler = evtHandler
#
# variables
#
self.vectmap = vectmap # fullname
if not "@" in self.vectmap:
self.vectmap = grass.find_file(self.vectmap,
element='vector')['fullname']
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.qtype = qtype # type of query: SELECT, UPDATE, DELETE, ...
self.colvalues = [] # array with unique values in selected column
# set dialog title
self.SetTitle(_("GRASS SQL Builder (%(type)s): vector map <%(map)s>") % \
{ 'type' : self.qtype.upper(), 'map' : self.vectmap })
self.panel = wx.Panel(parent=self, id=wx.ID_ANY)
# statusbar
self.statusbar = self.CreateStatusBar(number=1)
self.statusbar.SetStatusText(_("SQL statement not verified"), 0)
self._doLayout()
def _doLayout(self):
"""!Do dialog layout"""
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(item=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)
if self.qtype.lower() == "select":
self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
self.text_sql.SetInsertionPointEnd()
self.text_sql.SetToolTipString(
_("Example: %s") %
"SELECT * FROM roadsmajor WHERE MULTILANE = 'no' OR OBJECTID < 10")
wx.CallAfter(self.text_sql.SetFocus)
sqlboxsizer.Add(item=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_verify = wx.Button(parent=self.panel,
id=wx.ID_ANY,
label=_("Verify"))
self.btn_verify.SetToolTipString(_("Verify SQL statement"))
self.btn_apply = wx.Button(parent=self.panel, id=wx.ID_APPLY)
self.btn_apply.SetToolTipString(
_("Apply SQL statement and close the dialog"))
self.btn_close = wx.Button(parent=self.panel, id=wx.ID_CLOSE)
self.btn_close.SetToolTipString(_("Close the dialog"))
self.btn_lv = {
'is': [
'=',
],
'isnot': [
'!=',
],
'like': [
'LIKE',
],
'gt': [
'>',
],
'ge': [
'>=',
],
'lt': [
'<',
],
'le': [
'<=',
],
'or': [
'OR',
],
'not': [
'NOT',
],
'and': [
'AND',
],
'brac': [
'()',
],
'prc': [
'%',
]
}
for key, value in self.btn_lv.iteritems():
btn = wx.Button(parent=self.panel, id=wx.ID_ANY, label=value[0])
self.btn_lv[key].append(btn.GetId())
buttonsizer = wx.FlexGridSizer(cols=4, hgap=5, vgap=5)
buttonsizer.Add(item=self.btn_clear)
buttonsizer.Add(item=self.btn_verify)
buttonsizer.Add(item=self.btn_apply)
buttonsizer.Add(item=self.btn_close)
buttonsizer2 = wx.GridBagSizer(5, 5)
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['is'][1]),
pos=(0, 0))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['isnot'][1]),
pos=(1, 0))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['like'][1]),
pos=(2, 0))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['gt'][1]),
pos=(0, 1))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['ge'][1]),
pos=(1, 1))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['or'][1]),
pos=(2, 1))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['lt'][1]),
pos=(0, 2))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['le'][1]),
pos=(1, 2))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['not'][1]),
pos=(2, 2))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['brac'][1]),
pos=(0, 3))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['prc'][1]),
pos=(1, 3))
buttonsizer2.Add(item=self.FindWindowById(self.btn_lv['and'][1]),
pos=(2, 3))
#
# list boxes (columns, values)
#
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(item=self.list_columns, proportion=1, flag=wx.EXPAND)
radiosizer = wx.BoxSizer(wx.HORIZONTAL)
self.radio_cv = wx.RadioBox(parent=self.panel,
id=wx.ID_ANY,
label=" %s " % _("Add on double-click"),
choices=[_("columns"),
_("values")])
self.radio_cv.SetSelection(1) # default 'values'
radiosizer.Add(item=self.radio_cv,
proportion=1,
flag=wx.ALIGN_CENTER_HORIZONTAL | wx.EXPAND,
border=5)
columnsizer.Add(item=radiosizer,
proportion=0,
flag=wx.TOP | wx.EXPAND,
border=5)
# self.list_columns.SetMinSize((-1,130))
# self.list_values.SetMinSize((-1,100))
valuesbox = wx.StaticBox(parent=self.panel,
id=wx.ID_ANY,
label=" %s " % _("Values"))
valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL)
self.list_values = wx.ListBox(parent=self.panel,
id=wx.ID_ANY,
choices=self.colvalues,
style=wx.LB_MULTIPLE)
valuesizer.Add(item=self.list_values, proportion=1, flag=wx.EXPAND)
self.btn_unique = wx.Button(parent=self.panel,
id=wx.ID_ANY,
label=_("Get all values"))
self.btn_unique.Enable(False)
self.btn_uniquesample = wx.Button(parent=self.panel,
id=wx.ID_ANY,
label=_("Get sample"))
self.btn_uniquesample.Enable(False)
buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL)
buttonsizer3.Add(item=self.btn_uniquesample,
proportion=0,
flag=wx.ALIGN_CENTER_HORIZONTAL | wx.RIGHT,
border=5)
buttonsizer3.Add(item=self.btn_unique,
proportion=0,
flag=wx.ALIGN_CENTER_HORIZONTAL)
valuesizer.Add(item=buttonsizer3, proportion=0, flag=wx.TOP, border=5)
# hsizer1.Add(wx.StaticText(self.panel,-1, "Unique values: "), border=0, proportion=1)
hsizer.Add(item=columnsizer, proportion=1, flag=wx.EXPAND)
hsizer.Add(item=valuesizer, 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)
pagesizer.Add(item=databaseboxsizer, flag=wx.ALL | wx.EXPAND, border=5)
pagesizer.Add(item=hsizer,
proportion=1,
flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND,
border=5)
# pagesizer.Add(self.btn_uniqe,0,wx.ALIGN_LEFT|wx.TOP,border=5)
# pagesizer.Add(self.btn_uniqesample,0,wx.ALIGN_LEFT|wx.TOP,border=5)
pagesizer.Add(item=buttonsizer2,
proportion=0,
flag=wx.ALIGN_CENTER_HORIZONTAL)
pagesizer.Add(item=sqlboxsizer,
proportion=0,
flag=wx.EXPAND | wx.LEFT | wx.RIGHT,
border=5)
pagesizer.Add(item=buttonsizer,
proportion=0,
flag=wx.ALIGN_RIGHT | wx.ALL,
border=5)
pagesizer.Add(item=self.close_onapply,
proportion=0,
flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND,
border=5)
#
# bindings
#
self.btn_unique.Bind(wx.EVT_BUTTON, self.OnUniqueValues)
self.btn_uniquesample.Bind(wx.EVT_BUTTON, self.OnSampleValues)
for key, value in self.btn_lv.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_verify.Bind(wx.EVT_BUTTON, self.OnVerify)
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.text_sql.Bind(wx.EVT_TEXT, self.OnText)
self.panel.SetAutoLayout(True)
self.panel.SetSizer(pagesizer)
pagesizer.Fit(self.panel)
self.Layout()
self.SetMinSize((660, 525))
self.SetClientSize(self.panel.GetSize())
self.CenterOnParent()
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)
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)
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 OnAddMark(self, event):
"""!Add mark"""
mark = None
for key, value in self.btn_lv.iteritems():
if event.GetId() == value[1]:
mark = value[0]
break
self._add(element='mark', value=mark)
def _add(self, element, value):
"""!Add element to the query
@param element element to add (column, value)
"""
sqlstr = self.text_sql.GetValue()
newsqlstr = ''
if element == 'column':
if self.radio_cv.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) + ' ' + sqlstr[idx2:]
else: # -> where
newsqlstr = sqlstr
if sqlstr.lower().find('where') < 0:
newsqlstr += ' WHERE'
newsqlstr += ' ' + value
elif element == 'value':
newsqlstr = sqlstr + ' ' + value
elif element == 'mark':
newsqlstr = sqlstr + ' ' + value
if newsqlstr:
self.text_sql.SetValue(newsqlstr)
def GetSQLStatement(self):
"""!Return SQL statement"""
return self.text_sql.GetValue().strip().replace("\n", " ")
def CloseOnApply(self):
"""!Return True if the dialog will be close on apply"""
return self.close_onapply.IsChecked()
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 OnApply(self, event):
"""Apply button pressed"""
if self.evtHandler:
self.evtHandler(event='apply')
if self.close_onapply.IsChecked():
self.Destroy()
event.Skip()
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 OnClear(self, event):
"""!Clear button pressed"""
if self.qtype.lower() == "select":
self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
else:
self.text_sql.SetValue("")
def OnClose(self, event):
"""!Close button pressed"""
if self.evtHandler:
self.evtHandler(event='close')
self.Destroy()
event.Skip()