class SqlPage:
name="SQL"
order=800
def __init__(self, notebook):
from _sqledit import SqlEditor
self.control=SqlEditor(notebook)
self.control.SetMarginWidth(1, 2)
self.notebook=notebook
self.lastNode=None
def GetControl(self):
return self.control
def Display(self, node, _detached):
if hasattr(node, "GetSql"):
sql=node.GetSql().strip().replace("\n\r", "\n").replace("\r\n", "\n")
else:
sql=xlt("No SQL query available.")
self.control.SetReadOnly(False)
self.control.SetValue(sql)
self.control.SetReadOnly(True)
self.control.SetSelection(0,0)
class FilterPanel(adm.NotebookPanel):
def __init__(self, dlg, notebook):
adm.NotebookPanel.__init__(self, dlg, notebook)
self.Bind("LimitCheck", self.OnLimitCheck)
self.Bind("FilterCheck", self.OnFilterCheck)
self.Bind("FilterValidate", self.OnFilterValidate)
self.FilterValue.BindProcs(self.OnFilterValueChanged, None)
self.Bind("FilterPreset", wx.EVT_COMBOBOX, self.OnPresetSelect)
self.Bind("FilterPreset", wx.EVT_TEXT, self.OnPresetChange)
self.Bind("FilterSave", self.OnFilterSave)
self['SortCols'].Bind(wx.EVT_LISTBOX_DCLICK, self.OnDclickSort)
# TODO unfortunately we need 3.x here
if True: # wx.Platform == "__WXMAC__" and wx.VERSION < (3,0):
event=wx.EVT_LEFT_DOWN
else:
event=wx.EVT_MOTION
self['DisplayCols'].Bind(event, self.OnBeginDrag)
self['SortCols'].Bind(event, self.OnBeginDrag)
self['DisplayCols'].Bind(wx.EVT_CHECKLISTBOX, self.OnClickCol)
self.OnLimitCheck()
self.OnFilterCheck()
self.valid=True
self.dialog=dlg
self.EnableControls("FilterPreset", dlg.querypreset_table)
self.EnableControls("FilterSave", False)
def AddExtraControls(self, res):
self.FilterValue=SqlEditor(self)
res.AttachUnknownControl("FilterValuePlaceholder", self.FilterValue)
self.FilterValue.SetMarginWidth(1, 0)
def OnPresetSelect(self, evt):
preset=self.FilterPreset.strip()
if not preset:
return
query=pgQuery(self.dialog.querypreset_table, self.dialog.server.GetCursor())
query.AddCol('querylimit')
query.AddCol('filter')
query.AddCol('sort')
query.AddCol('display')
query.AddCol('sql')
query.AddWhere('dbname', self.tableSpecs.dbName)
query.AddWhere('tabname', self.tableSpecs.tabName)
query.AddWhere('presetname', preset)
res=query.Select()
for row in res:
limit=row['querylimit']
filter=row['filter']
sort=evalAsPython(row['sort'])
display=evalAsPython(row['display'])
sql=row['sql']
if limit:
self.LimitCheck=True
self.OnLimitCheck()
self.LimitValue=limit
else:
self.LimitCheck=False
if sql:
self.dialog.editor.SetText(sql)
if sort:
sc=self['SortCols']
sc.Clear()
cols=self.tableSpecs.colNames[:]
for col in sort:
if col.endswith(' DESC'): colpure=col[:-5]
else: colpure=col
if colpure in cols:
id=sc.Append(col)
sc.Check(id, True)
cols.remove(colpure)
sc.AppendItems(cols)
if display:
dc=self['DisplayCols']
dc.Clear()
cols=self.tableSpecs.colNames[:]
for col in display:
if col in cols:
id=dc.Append(col)
dc.Check(id, True)
cols.remove(col)
dc.AppendItems(cols)
if filter:
self.FilterCheck=True
self.OnFilterCheck()
self.FilterValue.SetText(filter)
self.OnFilterValidate(evt)
else:
self.FilterCheck=False
break # only one row, hopefully
self.OnPresetChange(evt)
def OnPresetChange(self, evt):
self.EnableControls("FilterSave", self.FilterPreset)
def OnClickCol(self, evt):
if evt.String in self.dialog.tableSpecs.keyCols:
# don't un-display key colums; we need them
evt.EventObject.Check(evt.Selection, True)
pass
def OnBeginDrag(self, evt):
if evt.GetPosition().x < 30 or not evt.LeftDown():
evt.Skip()
return
lb=evt.EventObject
i=lb.HitTest(evt.GetPosition())
if i >= 0:
lb.SetDropTarget(TextDropTarget(lb))
data=wx.PyTextDataObject(str(i))
ds=wx.DropSource(lb)
ds.SetData(data)
ds.DoDragDrop(False)
lb.SetDropTarget(None)
def OnDclickSort(self, evt):
colname=self['SortCols'].GetString(evt.Selection)
if colname.endswith(" DESC"):
colname=colname[:-5]
else:
colname = colname+" DESC"
self['SortCols'].SetString(evt.Selection, colname)
def OnFilterSave(self, evt):
preset=self.FilterPreset
if self.LimitCheck: limit=self.LimitValue
else: limit=None
if self.FilterCheck: filter=self.FilterValue.GetText()
else: filter=None
sort=self['SortCols'].GetCheckedStrings()
display=self['DisplayCols'].GetCheckedStrings()
sql=self.dialog.editor.GetText()
query=pgQuery(self.dialog.querypreset_table, self.dialog.server.GetCursor())
query.AddColVal('querylimit', limit)
query.AddColVal('filter', filter)
query.AddColVal('sort', unicode(sort))
query.AddColVal('display', unicode(display))
query.AddColVal('sql', sql)
fp=self['FilterPreset']
if fp.FindString(preset) < 0:
query.AddColVal('dbname', self.tableSpecs.dbName)
query.AddColVal('tabname', self.tableSpecs.tabName)
query.AddColVal('presetname', preset)
query.Insert()
fp.Append(preset)
else:
query.AddWhere('dbname', self.tableSpecs.dbName)
query.AddWhere('tabname', self.tableSpecs.tabName)
query.AddWhere('presetname', preset)
query.Update()
def OnLimitCheck(self, evt=None):
self.EnableControls("LimitValue", self.LimitCheck)
def OnFilterCheck(self, evt=None):
self.EnableControls("FilterValidate", self.FilterCheck)
self.FilterValue.Enable(self.FilterCheck)
self.OnFilterValueChanged(evt)
def OnFilterValueChanged(self, evt):
self.valid=not self.FilterCheck
self.dialog.updateMenu()
def OnFilterValidate(self, evt):
self.valid=False
sql="EXPLAIN " + self.GetQuery()
cursor=self.tableSpecs.GetCursor()
cursor.ExecuteSet(sql) # will throw and show exception if invalid
self.dialog.SetStatus(xlt("Filter expression valid"))
self.valid=True
self.dialog.updateMenu()
def Go(self, tableSpecs):
self.tableSpecs=tableSpecs
dc=self['DisplayCols']
sc=self['SortCols']
for colName in self.tableSpecs.colNames:
i=dc.Append(colName)
dc.Check(i, True)
i=sc.Append(colName)
if colName in self.tableSpecs.keyCols:
sc.Check(i, True)
if self.dialog.querypreset_table:
query=pgQuery(self.dialog.querypreset_table, self.dialog.server.GetCursor())
query.AddCol('presetname')
query.AddWhere('dbname', self.tableSpecs.dbName)
query.AddWhere('tabname', self.tableSpecs.tabName)
query.AddOrder('presetname')
res=query.Select()
fp=self['FilterPreset']
for row in res:
fp.Append(row[0])
default=fp.FindString('default')
if id >= 0:
fp.SetSelection(default)
self.OnPresetSelect(None)
def GetQuery(self):
query=pgQuery(self.tableSpecs.tabName)
for colName in self['DisplayCols'].GetCheckedStrings():
query.AddCol(colName, True)
for colName in self['SortCols'].GetCheckedStrings():
if colName.endswith(' DESC'):
query.AddOrder(query.quoteIdent(colName[:-5]) + " DESC", False)
else:
query.AddOrder(colName, True)
if self.FilterCheck:
filter=self.FilterValue.GetText().strip()
query.AddWhere(filter)
sql= query.SelectQueryString()
if self.LimitCheck:
sql += "\n LIMIT %d" % self.LimitValue
return sql