def OnExecute(_parentWin, server):
adminspace = server.adminspace
if not adminspace:
adminspace = server.GetPreference("AdminNamespace")
server.GetCursor().ExecuteSingle(
"CREATE SCHEMA %s AUTHORIZATION postgres" %
quoteIdent(adminspace))
server.adminspace = adminspace
adsQuoted = quoteIdent(adminspace)
if not server.fav_table:
fav_table = quoteIdent("Admin_Fav_%s" % server.user)
server.GetCursor().ExecuteSingle("""
CREATE TABLE %(adminspace)s.%(fav_table)s
(dboid OID, favoid OID, favtype CHAR, favgroup TEXT, PRIMARY KEY(dboid, favoid))"""
% {
'adminspace': adsQuoted,
'fav_table': fav_table
})
server.fav_table = "%s.%s" % (adsQuoted, fav_table)
for menu in server.moduleinfo().get('menus', []):
cls = menu['class']
if hasattr(cls, 'DoInstrument'):
cls.DoInstrument(server)
server.RereadServerInfo()
return True
def OnExecute(_parentWin, server):
adminspace=server.adminspace
if not adminspace:
adminspace=server.GetPreference("AdminNamespace")
server.GetCursor().ExecuteSingle("CREATE SCHEMA %s AUTHORIZATION postgres" % quoteIdent(adminspace))
server.adminspace=adminspace
adsQuoted=quoteIdent(adminspace)
if not server.fav_table:
fav_table=quoteIdent("Admin_Fav_%s" % server.user)
server.GetCursor().ExecuteSingle("""
CREATE TABLE %(adminspace)s.%(fav_table)s
(dboid OID, favoid OID, favtype CHAR, favgroup TEXT, PRIMARY KEY(dboid, favoid))""" %
{'adminspace': adsQuoted,
'fav_table': fav_table })
server.fav_table="%s.%s" % (adsQuoted, fav_table)
for menu in server.moduleinfo().get('menus', []):
cls=menu['class']
if hasattr(cls, 'DoInstrument'):
cls.DoInstrument(server)
server.RereadServerInfo()
return True
def NameSql(self):
name=quoteIdent(self.info['name'])
schema=self.info.get('nspname')
if not schema:
return name
elif schema != 'public':
schema=quoteIdent(schema)
return "%s.%s" % (schema, name)
def NameSql(self):
name = quoteIdent(self.info['name'])
schema = self.info.get('nspname')
if not schema:
return name
elif schema != 'public':
schema = quoteIdent(schema)
return "%s.%s" % (schema, name)
def DoInstrument(server):
if not server.info.get('snippet_table'):
snippet_table=quoteIdent("Admin_Snippet_%s" % server.user)
server.GetCursor().ExecuteSingle("""
CREATE TABLE %(adminspace)s.%(snippet_table)s
(id SERIAL PRIMARY KEY, parent INT4 NOT NULL DEFAULT 0, sort FLOAT NOT NULL DEFAULT 0.0, name TEXT, snippet TEXT);""" %
{'adminspace': quoteIdent(server.adminspace),
'snippet_table': snippet_table })
def DoInstrument(server):
if not server.info.get('snippet_table'):
snippet_table=quoteIdent("Admin_Snippet_%s" % server.user)
server.GetCursor().ExecuteSingle("""
CREATE TABLE %(adminspace)s.%(snippet_table)s
(id SERIAL PRIMARY KEY, parent INT4 NOT NULL DEFAULT 0, sort FLOAT NOT NULL DEFAULT 0.0, name TEXT, snippet TEXT);""" %
{'adminspace': quoteIdent(server.adminspace),
'snippet_table': snippet_table })
def GetSql(self):
self.populateColumns()
cols = []
for col in self.columns:
cols.append(
quoteIdent(col['attname']) + ' ' + self.colTypeName(col))
constraints = []
self.populateConstraints()
for constraint in self.constraints:
c = []
for col in constraint['colnames']:
c.append(quoteIdent(col))
if constraint['indisprimary']:
cols.append("PRIMARY KEY(" + ", ".join(c) + ")")
else:
if constraint['type'] == 'index':
info = ['CREATE']
if constraint['indisunique']:
info.append('UNIQUE')
info.append("INDEX")
info.append(constraint['fullname'])
info.append('ON ' + self.NameSql())
info.append("(%s)" % ",".join(c))
constraints.append(" ".join(info) + ";")
elif constraint['type'] == 'foreignkey':
info = [
"ALTER TABLE " + self.NameSql() + "\n ADD CONSTRAINT "
]
info.append(constraint['fullname'])
info.append("REFERENCES " +
quoteIdent(constraint['reftable']))
info.append(
"(%s)" %
",".join(map(quoteIdent, constraint['refcolnames'])))
constraints.append(" ".join(info) + ";")
elif constraint['type'] == 'check':
pass
sql = []
sql.append("CREATE TABLE " + self.NameSql())
sql.append("(")
sql.append(" " + ",\n ".join(cols))
if (self.info['relhasoids']):
sql.append(") WITH OIDs;")
else:
sql.append(");")
sql.append("ALTER TABLE " + self.NameSql() + " OWNER TO " +
quoteIdent(self.info['owner']) + ";")
sql.extend(constraints)
sql.extend(self.getAclDef('relacl', "arwdDxt"))
sql.extend(self.getCommentDef())
return "\n".join(sql)
def DoInstrument(server):
if not server.info.get('querypreset_table'):
querypreset_table=quoteIdent("Admin_QueryPreset_%s" % server.user)
server.GetCursor().ExecuteSingle("""
CREATE TABLE %(adminspace)s.%(querypreset_table)s
(dbname TEXT NOT NULL, tabname TEXT NOT NULL, presetname TEXT NOT NULL,
querylimit INTEGER, filter TEXT, sort TEXT, display TEXT, sql TEXT,
PRIMARY KEY(dbname, tabname, presetname));""" %
{'adminspace': quoteIdent(server.adminspace),
'querypreset_table': querypreset_table })
return True
def Commit(self):
if self.currentRowNo >= 0:
query=pgQuery(self.tableSpecs.tabName, self.tableSpecs.GetCursor())
if self.currentRowNo < len(self.rows):
# UPDATE case
for col in self.colsChanged:
colname=self.colNames[col]
val=self.currentRow[colname]
query.AddColVal(quoteIdent(colname), val)
r=self.rows[self.currentRowNo]
if self.hasoids:
query.AddWhere("oid", r['oid'])
else:
for colname in self.tableSpecs.keyCols:
query.AddWhere(colname, self.currentRow[colname])
query.Update()
self.rows[self.currentRowNo] = self.currentRow
else:
# INSERT case
for colname in self.colNames:
if colname == "oid" and self.hasoids:
continue
value=self.currentRow.get(colname)
if value != None:
query.AddColVal(quoteIdent(colname), self.currentRow[colname])
if self.hasoids:
returning=None
else:
returning=",".join(map(quoteIdent, self.tableSpecs.keyCols))
returned=query.Insert(returning)
if returned != None:
if self.hasoids:
self.currentRow['oid'] = returned
else:
if isinstance(returned, tuple):
for i in range(len(returned)):
self.currentRow[self.tableSpecs.keyCols[i]] = returned[i]
else:
self.currentRow[self.tableSpecs.keyCols[0]] = returned
self.rows.append(self.currentRow)
self.grid.ProcessTableMessage(wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_APPENDED, 1))
self.grid.GetParent().SetStatusText(xlt("%d rows") % len(self.rows), SqlFrame.STATUSPOS_ROWS)
rc=True
else:
rc=False
self.Revert()
return rc
def Commit(self):
if self.currentRowNo >= 0:
query=pgQuery(self.tableSpecs.tabName, self.tableSpecs.GetCursor())
if self.currentRowNo < len(self.rows):
# UPDATE case
for col in self.colsChanged:
colname=self.colNames[col]
val=self.currentRow[colname]
query.AddColVal(quoteIdent(colname), val)
r=self.rows[self.currentRowNo]
if self.hasoids:
query.AddWhere("oid", r['oid'])
else:
for colname in self.tableSpecs.keyCols:
query.AddWhere(colname, self.currentRow[colname])
query.Update()
self.rows[self.currentRowNo] = self.currentRow
else:
# INSERT case
for colname in self.colNames:
if colname == "oid" and self.hasoids:
continue
value=self.currentRow.get(colname)
if value != None:
query.AddColVal(quoteIdent(colname), self.currentRow[colname])
if self.hasoids:
returning=None
else:
returning=",".join(map(quoteIdent, self.tableSpecs.keyCols))
returned=query.Insert(returning)
if returned != None:
if self.hasoids:
self.currentRow['oid'] = returned
else:
if isinstance(returned, tuple):
for i in range(len(returned)):
self.currentRow[self.tableSpecs.keyCols[i]] = returned[i]
else:
self.currentRow[self.tableSpecs.keyCols[0]] = returned
self.rows.append(self.currentRow)
self.grid.ProcessTableMessage(wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_APPENDED, 1))
self.grid.GetParent().SetStatusText(xlt("%d rows") % len(self.rows), SqlFrame.STATUSPOS_ROWS)
rc=True
else:
rc=False
self.Revert()
return rc
def GetSql(self):
sql=[]
params={ "colname": quoteIdent(self.ColName), "oldcol": quoteIdent(self['ColName'].unchangedValue)}
if self.HasChanged("ColName"):
sql.append("RENAME COLUMN %(oldcol)s TO %(colname)s" % params)
if self.HasChanged("NotNull"):
if self.NotNull:
params['val'] = "SET"
else:
params['val'] = "DROP"
sql.append("ALTER COLUMN %(colname)s %(val)s NOT NULL" % params)
if self.HasChanged("DefaultVal"):
if self.DefaultVal:
params['default'] = self.DefaultVal
sql.append("ALTER COLUMN %(colname)s SET DEFAULT %(default)s" % params)
else:
sql.append("ALTER COLUMN (%colname)s DROP DEFAULT" % params)
if self.HasChanged("DataType Collation Length Precision"):
params['type']=self['DataType'].GetValue()
n="ALTER COLUMN %(colname)s TYPE %(type)s" % params
precFlag=self.typeInfo.get(self.DataType)
if precFlag and self.Length:
n += "(%d" % self.Length
if precFlag == 2 and self['Precision'].GetValue():
n += ", %d" % self.Precision
n += ")"
if self.HasChanged("Collation"):
n += " COLLATE %s" % quoteIdent(self['Collation'].GetValue())
sql.append(n)
if self.HasChanged("Statistics"):
params['val'] = self.Statistics
sql.append("ALTER COLUMN %(colname)s SET STATISTICS %(val)d" % params)
# type, len, prec, collate
# if self.HasChanged("Collation"):
# params['val'] = self["Collation"].GetValue()
# sql.append("ALTER COLUMN %(colname)s SET COLLATE \"%(val)d\";" % params)
if sql:
sql=["ALTER TABLE %s\n %s;" % (self.dialog.node.NameSql() , ",\n ".join(sql))]
if self.HasChanged('Comment'):
params['tabname'] = self.dialog.node.NameSql()
params['comment'] = quoteValue(self.Comment)
sql.append("COMMENT ON COLUMN %(tabname)s.%(colname)s IS %(comment)s" % params)
if sql:
return "\n".join(sql)
return ""
def GetSql(self):
self.populateColumns()
cols=[]
for col in self.columns:
cols.append(quoteIdent(col['attname']) + ' ' + self.colTypeName(col));
constraints=[]
self.populateConstraints()
for constraint in self.constraints:
c=[]
for col in constraint['colnames']:
c.append(quoteIdent(col))
if constraint['indisprimary']:
cols.append("PRIMARY KEY("+ ", ".join(c)+")")
else:
if constraint['type'] == 'index':
info=['CREATE']
if constraint['indisunique']:
info.append('UNIQUE')
info.append("INDEX")
info.append(constraint['fullname'])
info.append('ON ' + self.NameSql())
info.append("(%s)" % ",".join(c))
constraints.append(" ".join(info) + ";")
elif constraint['type'] == 'foreignkey':
info=["ALTER TABLE " + self.NameSql() + "\n ADD CONSTRAINT "]
info.append(constraint['fullname'])
info.append("REFERENCES " + quoteIdent(constraint['reftable']))
info.append("(%s)" % ",".join(map(quoteIdent, constraint['refcolnames'])))
constraints.append(" ".join(info) +";")
elif constraint['type'] == 'check':
pass
sql=[]
sql.append("CREATE TABLE " + self.NameSql())
sql.append("(");
sql.append(" " + ",\n ".join(cols))
if (self.info['relhasoids']):
sql.append(") WITH OIDs;")
else:
sql.append(");")
sql.append("ALTER TABLE " + self.NameSql() + " OWNER TO " + quoteIdent(self.info['owner']) + ";")
sql.extend(constraints)
sql.extend(self.getAclDef('relacl', "arwdDxt"))
sql.extend(self.getCommentDef())
return "\n".join(sql);
def Delete(self, rows):
"""
Delete(rows) expects rows in reverse sorted order
"""
query = pgQuery(self.tableSpecs.tabName, self.tableSpecs.GetCursor())
all = []
for row in rows:
wh = []
for colname in self.tableSpecs.keyCols:
wh.append(
"%s=%s" %
(quoteIdent(colname), quoteValue(self.rows[row][colname])))
all.append("(%s)" % " AND ".join(wh))
query.AddWhere("\n OR ".join(all))
rc = query.Delete()
self.grid.Freeze()
self.grid.BeginBatch()
for row in rows:
self.grid.DeleteRows(row, 1, True)
# msg=wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_APPENDED)
self.grid.EndBatch()
self.grid.ForceRefresh()
self.grid.Thaw()
return rc
def GrantCommentSql(self):
str = ""
acl = self.info.get('acl')
if acl:
for grant in shlexSplit(acl[1:-1], ','):
if grant.startswith('='):
user = "******"
rest = grant[1:]
else:
user, rest = shlexSplit(grant, '=')
user = quoteIdent(user)
rights = shlexSplit(rest, '/')[0]
if rights == self.allGrants:
rights = "ALL"
else:
rightlist = []
for right in rights:
rightlist.append(rightString[right])
rights = ",".join(rightlist)
str += "GRANT %s ON %s TO %s\n" % (rights, self.ObjectSql(),
user)
des = self.info.get('description')
if des:
str += "\nCOMMENT ON %s IS %s\n" % (self.ObjectSql(),
quoteValue(des))
return str
def Delete(self, rows):
"""
Delete(rows) expects rows in reverse sorted order
"""
query=pgQuery(self.tableSpecs.tabName, self.tableSpecs.GetCursor())
all=[]
for row in rows:
wh=[]
for colname in self.tableSpecs.keyCols:
wh.append("%s=%s" % (quoteIdent(colname), quoteValue(self.rows[row][colname])))
all.append("(%s)" % " AND ".join(wh))
query.AddWhere("\n OR ".join(all))
rc=query.Delete()
self.grid.Freeze()
self.grid.BeginBatch()
for row in rows:
self.grid.DeleteRows(row, 1, True)
# msg=wx.grid.GridTableMessage(self, wx.grid.GRIDTABLE_NOTIFY_ROWS_APPENDED)
self.grid.EndBatch()
self.grid.ForceRefresh()
self.grid.Thaw()
return rc
def GrantCommentSql(self):
str=""
acl=self.info.get('acl')
if acl:
for grant in shlexSplit(acl[1:-1], ','):
if grant.startswith('='):
user="******"
rest=grant[1:]
else:
user, rest=shlexSplit(grant, '=')
user=quoteIdent(user)
rights=shlexSplit(rest, '/')[0]
if rights == self.allGrants:
rights="ALL"
else:
rightlist=[]
for right in rights:
rightlist.append(rightString[right])
rights=",".join(rightlist)
str += "GRANT %s ON %s TO %s\n" % (rights, self.ObjectSql(), user)
des=self.info.get('description')
if des:
str += "\nCOMMENT ON %s IS %s\n" % (self.ObjectSql(), quoteValue(des))
return str
def getAclDef(self, aclName, allRights='ZZ'):
acls=[]
for acl in shlexSplit(self.info[aclName][1:-1], ','):
if acl.startswith('='):
user='******'
b=shlexSplit(acl[1:], '/')
else:
a=shlexSplit(acl, '=')
user=quoteIdent(a[0])
b=shlexSplit(a[1], '/')
# grantor=b[1]
privileges=""
grants=""
lastP=""
for p in b[0]:
if p == '*':
grants += lastP
else:
lastP=p
privileges += p
def mkGrant(lst, withGrant=""):
if lst == allRights:
pl=['ALL']
else:
pl=[]
for p in b[0]:
pl.append(rightString[p])
return "GRANT " + ",".join(pl) + " ON " + self.ObjectSql() +" TO " + user + withGrant +";"
if privileges:
acls.append(mkGrant(privileges))
if grants:
acls.append(mkGrant(grants, " WITH GRANT OPTION"))
return acls
def RereadServerInfo(self):
parts = [
"""
SELECT name, setting FROM pg_settings
WHERE name in ('autovacuum', 'log_line_prefix', 'log_destination', 'logging_collector', 'log_directory', 'data_directory', 'config_file')
UNION
SELECT 'version', version()
UNION
SELECT 'lastsysoid', datlastsysoid::text FROM pg_database
WHERE datname=%(datname)s
UNION
SELECT proname, proname FROM pg_proc
WHERE proname IN ( %(adminprocs)s ) AND pronamespace=11
UNION
SELECT 'adminspace', nspname FROM pg_namespace WHERE nspname=%(adminspace)s
UNION
SELECT 'fav_table', relname FROM pg_class JOIN pg_namespace nsp ON nsp.oid=relnamespace
WHERE nspname=%(adminspace)s AND relname=%(fav_table)s
""" % {
'datname': quoteValue(self.maintDb),
'adminspace': quoteValue(self.GetPreference("AdminNamespace")),
'fav_table': quoteValue("Admin_Fav_%s" % self.user),
'adminprocs': ", ".join(map(lambda p: "'%s'" % p, adminProcs))
}
]
# check instrumentation of tools
for menu in self.moduleinfo().get('menus', []):
cls = menu['class']
if hasattr(cls, 'GetInstrumentQuery'):
iq = cls.GetInstrumentQuery(self)
if iq:
parts.append(iq)
query = "\nUNION\n".join(parts)
self.info = self.connection.GetCursor().ExecuteDict(query)
self.adminspace = self.info.get('adminspace')
fav_table = self.info.get('fav_table')
if fav_table:
self.fav_table = "%s.%s" % (quoteIdent(
self.adminspace), quoteIdent(fav_table))
else:
self.fav_table = None
def RereadServerInfo(self):
parts=["""
SELECT name, setting FROM pg_settings
WHERE name in ('autovacuum', 'log_line_prefix', 'log_destination', 'logging_collector', 'log_directory', 'data_directory', 'config_file')
UNION
SELECT 'version', version()
UNION
SELECT 'lastsysoid', datlastsysoid::text FROM pg_database
WHERE datname=%(datname)s
UNION
SELECT proname, proname FROM pg_proc
WHERE proname IN ( %(adminprocs)s ) AND pronamespace=11
UNION
SELECT 'adminspace', nspname FROM pg_namespace WHERE nspname=%(adminspace)s
UNION
SELECT 'fav_table', relname FROM pg_class JOIN pg_namespace nsp ON nsp.oid=relnamespace
WHERE nspname=%(adminspace)s AND relname=%(fav_table)s
""" % {'datname': quoteValue(self.maintDb),
'adminspace': quoteValue(self.GetPreference("AdminNamespace")),
'fav_table': quoteValue("Admin_Fav_%s" % self.user),
'adminprocs': ", ".join(map(lambda p: "'%s'" % p, adminProcs))
}]
# check instrumentation of tools
for menu in self.moduleinfo().get('menus', []):
cls=menu['class']
if hasattr(cls, 'GetInstrumentQuery'):
iq=cls.GetInstrumentQuery(self)
if iq:
parts.append(iq)
query="\nUNION\n".join(parts)
self.info=self.connection.GetCursor().ExecuteDict(query)
self.adminspace=self.info.get('adminspace')
fav_table=self.info.get('fav_table')
if fav_table:
self.fav_table="%s.%s" % (quoteIdent(self.adminspace), quoteIdent(fav_table))
else:
self.fav_table=None
def getAclDef(self, aclName, allRights='ZZ'):
acls = []
for acl in shlexSplit(self.info[aclName][1:-1], ','):
if acl.startswith('='):
user = '******'
b = shlexSplit(acl[1:], '/')
else:
a = shlexSplit(acl, '=')
user = quoteIdent(a[0])
b = shlexSplit(a[1], '/')
# grantor=b[1]
privileges = ""
grants = ""
lastP = ""
for p in b[0]:
if p == '*':
grants += lastP
else:
lastP = p
privileges += p
def mkGrant(lst, withGrant=""):
if lst == allRights:
pl = ['ALL']
else:
pl = []
for p in b[0]:
pl.append(rightString[p])
return "GRANT " + ",".join(pl) + " ON " + self.ObjectSql(
) + " TO " + user + withGrant + ";"
if privileges:
acls.append(mkGrant(privileges))
if grants:
acls.append(mkGrant(grants, " WITH GRANT OPTION"))
return acls
def ExpandColDefs(self, cols): return ", ".join( [ "%s as %s" % (c[0], quoteIdent(c[1])) for c in cols])
def __init__(self, parentWin, node, params={}):
SqlFrame.__init__(self, parentWin, xlt("Query Tool"), "SqlQuery")
self.server=node.GetServer()
self.application="%s Query Tool" % adm.appTitle
snippet_table=self.server.info.get('snippet_table')
if self.server.adminspace and snippet_table:
self.snippet_table="%s.%s" % (quoteIdent(self.server.adminspace), quoteIdent(snippet_table))
else:
self.snippet_table=None
dbName=params.get('dbname')
if not dbName:
if hasattr(node, "GetDatabase"):
dbName=node.GetDatabase().name
else:
dbName=self.server.maintDb
self.worker=None
self.sqlChanged=False
self.previousCols=[]
self.fileManager=FileManager(self, adm.config)
toolbar=self.toolbar
toolbar.Add(self.OnFileOpen, xlt("Load from file"),"file_open")
toolbar.Add(self.OnFileSave, xlt("Save to file"), "file_save")
toolbar.Add(self.OnToggleSnippets, xlt("Show snippets browser"), "snippets")
toolbar.AddSeparator()
toolbar.Add(self.OnCopy, xlt("Copy"), "clip_copy")
toolbar.Add(self.OnCut, xlt("Cut"), "clip_cut")
toolbar.Add(self.OnPaste, xlt("Paste"), "clip_paste")
toolbar.Add(self.OnClear, xlt("Clear"), "edit_clear")
toolbar.AddSeparator()
toolbar.Add(self.OnUndo, xlt("Undo"), "edit_undo")
toolbar.Add(self.OnRedo, xlt("Redo"), "edit_redo")
# toolbar.Add((self.OnFind, xlt("Find"), "edit_find")
toolbar.AddSeparator()
cbClass=xmlres.getControlClass("whComboBox")
allDbs=self.server.GetConnectableDbs()
size=max(map(lambda db: toolbar.GetTextExtent(db)[0], allDbs))
BUTTONOFFS=30
self.databases=cbClass(toolbar, size=(size+BUTTONOFFS, -1))
self.databases.Append(allDbs)
self.databases.Append(xlt("Connect..."))
self.databases.SetStringSelection(dbName)
self.OnChangeDatabase()
self.databases.Bind(wx.EVT_COMBOBOX, self.OnChangeDatabase)
toolbar.Add(self.OnExecuteQuery, xlt("Execute Query"), "query_execute")
toolbar.Add(self.OnExplainQuery, xlt("Explain Query"), "query_explain")
toolbar.Add(self.OnCancelQuery, xlt("Execute Query"), "query_cancel")
toolbar.AddControl(self.databases)
toolbar.AddSeparator()
toolbar.Add(self.OnAddSnippet, xlt("Add snippet"), "snippet_add")
toolbar.Add(self.OnReplaceSnippet, xlt("Replace snippet"), "snippet_replace")
toolbar.Realize()
menubar=wx.MenuBar()
self.filemenu=menu=Menu(self)
menu.Add(self.OnFileOpen, xlt("&Open"), xlt("Open query file"))
menu.AppendMenu(-1, xlt("Open recent..."), self.fileManager.GetRecentFilesMenu())
menu.Add(self.OnFileInsert, xlt("&Insert"), xlt("Insert query file"))
menu.Add(self.OnFileSave, xlt("&Save"), xlt("Save current file"))
menu.Add(self.OnFileSaveAs, xlt("Save &as.."), xlt("Save file under new name"))
menu.AppendSeparator()
# menu.Add(xlt("Preferences"), xlt("Preferences"), self.OnPreferences)
menu.Add(self.OnClose, xlt("Quit SQL"), xlt("Quit Sql"))
menubar.Append(menu, xlt("&File"))
self.viewmenu=menu=Menu(self)
menu.AddCheck(self.OnToggleSnippets, xlt("Snippets"), xlt("Show or hide snippet browser"))
self.registerToggles(True, True)
menubar.Append(self.viewmenu, xlt("&View"))
self.editmenu=menu=Menu(self)
menu.Add(self.OnUndo, xlt("&Undo"), xlt("Undo last action"))
menu.Add(self.OnRedo, xlt("&Redo"), xlt("Redo last action"))
# menu.Add(xlt("&Find"), xlt("Find string"), self.OnFind)
menu.AppendSeparator()
menu.Add(self.OnCut, xlt("Cu&t"), xlt("Cut selected text to clipboard"))
menu.Add(self.OnCopy, xlt("&Copy"), xlt("Copy selected text to clipboard"))
menu.Add(self.OnPaste, xlt("&Paste"), xlt("Paste text from clipboard"))
menu.Add(self.OnClear, xlt("C&lear"), xlt("Clear editor"))
menu.AppendSeparator()
menu.Add(self.OnAddSnippet, xlt("Add snippet"), xlt("Add selected text to snippets"))
menu.Add(self.OnReplaceSnippet, xlt("Modify snippet"), xlt("Replace snippet with selected text"))
menubar.Append(menu, xlt("&Edit"))
self.querymenu=menu=Menu(self)
menu.Add(self.OnExecuteQuery, xlt("Execute"), xlt("Execute query"))
menu.Add(self.OnExplainQuery, xlt("Explain"), xlt("Explain query"))
menu.Add(self.OnCancelQuery, xlt("Cancel"), xlt("Cancel query execution"))
menubar.Append(menu, xlt("&Query"))
self.helpmenu=menu=Menu(self)
menu.Add(self.OnHelp, xlt("Help"), xlt("Show help"), wx.ID_HELP)
menubar.Append(menu, xlt("&Help"))
self.EnableMenu(self.querymenu, self.OnCancelQuery, False)
self.SetMenuBar(menubar)
ah=AcceleratorHelper(self)
ah.Add(wx.ACCEL_CTRL, 'X', self.OnCut)
ah.Add(wx.ACCEL_CTRL, 'C', self.OnCopy)
ah.Add(wx.ACCEL_CTRL, 'V', self.OnPaste)
ah.Add(wx.ACCEL_NORMAL,wx.WXK_F5, self.OnExecuteQuery)
ah.Add(wx.ACCEL_NORMAL,wx.WXK_F7, self.OnExplainQuery)
ah.Add(wx.ACCEL_ALT,wx.WXK_PAUSE, self.OnCancelQuery)
ah.Realize()
self.editor=SqlEditor(self)
self.editor.SetAcceleratorTable(ah.GetTable())
self.editor.BindProcs(self.OnChangeStc, self.OnStatusPos)
self.manager.AddPane(self.editor, wx.aui.AuiPaneInfo().Top().PaneBorder().Resizable().MinSize((200,100)).BestSize((400,200)).CloseButton(False) \
.Name("sqlQuery").Caption(xlt("SQL Query")))
self.snippets=SnippetTree(self, self.server, self.editor)
self.manager.AddPane(self.snippets, wx.aui.AuiPaneInfo().Left().Top().PaneBorder().Resizable().MinSize((100,100)).BestSize((100,100)).CloseButton(True) \
.Name("snippets").Caption(xlt("SQL Snippets")))
if not self.snippet_table:
self.manager.GetPane("snippets").Show(False)
self.output=wx.Notebook(self)
self.result=SqlResultGrid(self.output)
self.explain = ExplainCanvas(self.output)
self.explain.Hide()
font=self.editor.GetFont()
self.messages=wx.TextCtrl(self.output, style=wx.TE_MULTILINE|wx.TE_READONLY|wx.TE_DONTWRAP)
self.msgHistory=wx.TextCtrl(self.output, style=wx.TE_MULTILINE|wx.TE_READONLY|wx.TE_DONTWRAP)
self.messages.SetFont(font)
self.msgHistory.SetFont(font)
self.output.AddPage(self.result, xlt("Output"))
self.output.AddPage(self.messages, xlt("Messages"))
self.output.AddPage(self.msgHistory, xlt("History"))
self.manager.AddPane(self.output, wx.aui.AuiPaneInfo().Center().MinSize((200,100)).BestSize((400,200)).CloseButton(False) \
.Name("Result").Caption(xlt("Result")).CaptionVisible(False))
self.manager.Bind(wx.aui.EVT_AUI_PANE_CLOSE, self.OnAuiCloseEvent)
self.SetStatus(xlt("ready"))
self.restorePerspective()
self.viewmenu.Check(self.OnToggleSnippets, self.manager.GetPane("snippets").IsShown())
self.OnToggleToolBar()
self.OnToggleStatusBar()
self.updateMenu()
query=params.get('query')
if query:
self.editor.SetText(query)
pos=params.get('errline', -1)
if pos:
line=self.editor.LineFromPosition(int(pos))
self.editor.MarkerSet(line)
msg=params.get('message')
if msg:
self.messages.AppendText(msg)
hint=params.get('hint')
if hint:
self.messages.AppendText("\n\nHINT:\n")
self.messages.AppendText(hint)
self.output.SetSelection(1)
self.Show()
self.editor.SetFocus()
def NameSql(self):
name=quoteIdent(self.info['name'])
schema=self.info.get('nspname')
if not schema or schema == 'public':
return name
return "%s.%s" % (quoteIdent(schema), name)
def TablespaceSql(self):
ts=self.info['spcname']
if ts:
return "TABLESPACE %s" % quoteIdent(ts)
return ""
def ExpandColDefs(self, cols):
return ", ".join(["%s as %s" % (c[0], quoteIdent(c[1])) for c in cols])
def __init__(self, parentWin, node, params={}):
SqlFrame.__init__(self, parentWin, xlt("Query Tool"), "SqlQuery")
self.server=node.GetServer()
self.application="%s Query Tool" % adm.appTitle
snippet_table=self.server.info.get('snippet_table')
if self.server.adminspace and snippet_table:
self.snippet_table="%s.%s" % (quoteIdent(self.server.adminspace), quoteIdent(snippet_table))
else:
self.snippet_table=None
dbName=params.get('dbname')
if not dbName:
if hasattr(node, "GetDatabase"):
dbName=node.GetDatabase().name
else:
dbName=self.server.maintDb
self.worker=None
self.sqlChanged=False
self.previousCols=[]
self.fileManager=FileManager(self, adm.config)
toolbar=self.toolbar
toolbar.Add(self.OnFileOpen, xlt("Load from file"),"file_open")
toolbar.Add(self.OnFileSave, xlt("Save to file"), "file_save")
toolbar.Add(self.OnToggleSnippets, xlt("Show snippets browser"), "snippets")
toolbar.AddSeparator()
toolbar.Add(self.OnCopy, xlt("Copy"), "clip_copy")
toolbar.Add(self.OnCut, xlt("Cut"), "clip_cut")
toolbar.Add(self.OnPaste, xlt("Paste"), "clip_paste")
toolbar.Add(self.OnClear, xlt("Clear"), "edit_clear")
toolbar.AddSeparator()
toolbar.Add(self.OnUndo, xlt("Undo"), "edit_undo")
toolbar.Add(self.OnRedo, xlt("Redo"), "edit_redo")
# toolbar.Add((self.OnFind, xlt("Find"), "edit_find")
toolbar.AddSeparator()
cbClass=xmlres.getControlClass("whComboBox")
allDbs=self.server.GetConnectableDbs()
size=max(map(lambda db: toolbar.GetTextExtent(db)[0], allDbs))
BUTTONOFFS=30
self.databases=cbClass(toolbar, size=(size+BUTTONOFFS, -1))
self.databases.Append(allDbs)
self.databases.Append(xlt("Connect..."))
self.databases.SetStringSelection(dbName)
self.OnChangeDatabase()
self.databases.Bind(wx.EVT_COMBOBOX, self.OnChangeDatabase)
toolbar.Add(self.OnExecuteQuery, xlt("Execute Query"), "query_execute")
toolbar.Add(self.OnExplainQuery, xlt("Explain Query"), "query_explain")
toolbar.Add(self.OnCancelQuery, xlt("Execute Query"), "query_cancel")
toolbar.AddControl(self.databases)
toolbar.AddSeparator()
toolbar.Add(self.OnAddSnippet, xlt("Add snippet"), "snippet_add")
toolbar.Add(self.OnReplaceSnippet, xlt("Replace snippet"), "snippet_replace")
toolbar.Realize()
menubar=wx.MenuBar()
self.filemenu=menu=Menu(self)
menu.Add(self.OnFileOpen, xlt("&Open"), xlt("Open query file"))
menu.AppendMenu(-1, xlt("Open recent..."), self.fileManager.GetRecentFilesMenu())
menu.Add(self.OnFileInsert, xlt("&Insert"), xlt("Insert query file"))
menu.Add(self.OnFileSave, xlt("&Save"), xlt("Save current file"))
menu.Add(self.OnFileSaveAs, xlt("Save &as.."), xlt("Save file under new name"))
menu.AppendSeparator()
# menu.Add(xlt("Preferences"), xlt("Preferences"), self.OnPreferences)
menu.Add(self.OnClose, xlt("Quit SQL"), xlt("Quit Sql"))
menubar.Append(menu, xlt("&File"))
self.viewmenu=menu=Menu(self)
menu.AddCheck(self.OnToggleSnippets, xlt("Snippets"), xlt("Show or hide snippet browser"))
self.registerToggles(True, True)
menubar.Append(self.viewmenu, xlt("&View"))
self.editmenu=menu=Menu(self)
menu.Add(self.OnUndo, xlt("&Undo"), xlt("Undo last action"))
menu.Add(self.OnRedo, xlt("&Redo"), xlt("Redo last action"))
# menu.Add(xlt("&Find"), xlt("Find string"), self.OnFind)
menu.AppendSeparator()
menu.Add(self.OnCut, xlt("Cu&t"), xlt("Cut selected text to clipboard"))
menu.Add(self.OnCopy, xlt("&Copy"), xlt("Copy selected text to clipboard"))
menu.Add(self.OnPaste, xlt("&Paste"), xlt("Paste text from clipboard"))
menu.Add(self.OnClear, xlt("C&lear"), xlt("Clear editor"))
menu.AppendSeparator()
menu.Add(self.OnAddSnippet, xlt("Add snippet"), xlt("Add selected text to snippets"))
menu.Add(self.OnReplaceSnippet, xlt("Modify snippet"), xlt("Replace snippet with selected text"))
menubar.Append(menu, xlt("&Edit"))
self.querymenu=menu=Menu(self)
menu.Add(self.OnExecuteQuery, xlt("Execute"), xlt("Execute query"))
menu.Add(self.OnExplainQuery, xlt("Explain"), xlt("Explain query"))
menu.Add(self.OnCancelQuery, xlt("Cancel"), xlt("Cancel query execution"))
menubar.Append(menu, xlt("&Query"))
self.helpmenu=menu=Menu(self)
menu.Add(self.OnHelp, xlt("Help"), xlt("Show help"), wx.ID_HELP)
menubar.Append(menu, xlt("&Help"))
self.EnableMenu(self.querymenu, self.OnCancelQuery, False)
self.SetMenuBar(menubar)
ah=AcceleratorHelper(self)
ah.Add(wx.ACCEL_CTRL, 'X', self.OnCut)
ah.Add(wx.ACCEL_CTRL, 'C', self.OnCopy)
ah.Add(wx.ACCEL_CTRL, 'V', self.OnPaste)
ah.Add(wx.ACCEL_NORMAL,wx.WXK_F5, self.OnExecuteQuery)
ah.Add(wx.ACCEL_NORMAL,wx.WXK_F7, self.OnExplainQuery)
ah.Add(wx.ACCEL_ALT,wx.WXK_PAUSE, self.OnCancelQuery)
ah.Realize()
self.editor=SqlEditor(self)
self.editor.SetAcceleratorTable(ah.GetTable())
self.editor.BindProcs(self.OnChangeStc, self.OnStatusPos)
self.manager.AddPane(self.editor, wx.aui.AuiPaneInfo().Top().PaneBorder().Resizable().MinSize((200,100)).BestSize((400,200)).CloseButton(False) \
.Name("sqlQuery").Caption(xlt("SQL Query")))
self.snippets=SnippetTree(self, self.server, self.editor)
self.manager.AddPane(self.snippets, wx.aui.AuiPaneInfo().Left().Top().PaneBorder().Resizable().MinSize((100,100)).BestSize((100,100)).CloseButton(True) \
.Name("snippets").Caption(xlt("SQL Snippets")))
if not self.snippet_table:
self.manager.GetPane("snippets").Show(False)
self.output=wx.Notebook(self)
self.result=SqlResultGrid(self.output)
self.explain = ExplainCanvas(self.output)
self.explain.Hide()
font=self.editor.GetFont()
self.messages=wx.TextCtrl(self.output, style=wx.TE_MULTILINE|wx.TE_READONLY|wx.TE_DONTWRAP)
self.msgHistory=wx.TextCtrl(self.output, style=wx.TE_MULTILINE|wx.TE_READONLY|wx.TE_DONTWRAP)
self.messages.SetFont(font)
self.msgHistory.SetFont(font)
self.output.AddPage(self.result, xlt("Output"))
self.output.AddPage(self.messages, xlt("Messages"))
self.output.AddPage(self.msgHistory, xlt("History"))
self.manager.AddPane(self.output, wx.aui.AuiPaneInfo().Center().MinSize((200,100)).BestSize((400,200)).CloseButton(False) \
.Name("Result").Caption(xlt("Result")).CaptionVisible(False))
self.manager.Bind(wx.aui.EVT_AUI_PANE_CLOSE, self.OnAuiCloseEvent)
self.SetStatus(xlt("ready"))
self.restorePerspective()
self.manager.GetPane("Result").Show()
self.manager.Update()
self.viewmenu.Check(self.OnToggleSnippets, self.manager.GetPane("snippets").IsShown())
self.OnToggleToolBar()
self.OnToggleStatusBar()
self.updateMenu()
query=params.get('query')
if query:
self.editor.SetText(query)
pos=params.get('errline', -1)
if pos:
line=self.editor.LineFromPosition(int(pos))
self.editor.MarkerSet(line)
msg=params.get('message')
if msg:
self.messages.AppendText(msg)
hint=params.get('hint')
if hint:
self.messages.AppendText("\n\nHINT:\n")
self.messages.AppendText(hint)
self.output.SetSelection(1)
self.Show()
self.editor.SetFocus()
def NameSql(self):
name = quoteIdent(self.info['name'])
schema = self.info.get('nspname')
if not schema or schema == 'public':
return name
return "%s.%s" % (quoteIdent(schema), name)
def __init__(self, parentWin, connectionPool, name, server):
self.tableSpecs=TableSpecs(connectionPool, name)
self.connectionPool=connectionPool
self.worker=None
self.output=None
self.server=server
querypreset_table=self.server.info.get('querypreset_table')
if self.server.adminspace and querypreset_table:
self.querypreset_table="%s.%s" % (quoteIdent(self.server.adminspace), quoteIdent(querypreset_table))
else:
self.querypreset_table=None
title=xlt("%(appTitle)s Data Tool - %(tableName)s") % {
'appTitle': adm.appTitle, 'tableName': name}
SqlFrame.__init__(self, parentWin, title, "SqlData")
toolbar=self.GetToolBar()
toolbar.Add(self.OnRefresh, xlt("Refresh"), "data_refresh")
toolbar.Add(self.OnCancelRefresh, xlt("Cancel refresh"), "query_cancel")
toolbar.Add(self.OnSave, xlt("Save data"), "data_save")
toolbar.Add(self.OnToggleFilter, xlt("Show filter window"), "filter")
toolbar.AddSeparator()
toolbar.Add(self.OnCopy, xlt("Copy"), "clip_copy")
toolbar.Add(self.OnCut, xlt("Cut"), "clip_cut")
toolbar.Add(self.OnPaste, xlt("Paste"), "clip_paste")
toolbar.Add(self.OnUndo, xlt("Undo"), "edit_undo")
toolbar.AddSeparator()
toolbar.Add(self.OnDelete, xlt("Delete"), "delete")
menubar=wx.MenuBar()
self.filemenu=menu=Menu(self)
menu.Add(self.OnClose, xlt("Quit tool"), xlt("Quit data tool"))
menubar.Append(menu, xlt("&File"))
self.datamenu=menu=Menu(self)
menu.Add(self.OnRefresh, xlt("Refresh"), xlt("Refresh data"))
menu.Add(self.OnCancelRefresh, xlt("Cancel"), xlt("Cancel refresh"))
menu.Add(self.OnSave, xlt("Save"), xlt("Save data"))
menu.Add(self.OnDelete, xlt("Delete"), xlt("Delete row(s)"))
menubar.Append(menu, xlt("&Data"))
self.viewmenu=menu=Menu(self)
menu.AddCheck(self.OnToggleFilter, xlt("Filter"), xlt("Show or hide filter window"))
self.registerToggles(True, True)
menubar.Append(menu, xlt("&View"))
self.editmenu=menu=Menu(self)
menu.Add(self.OnCut, xlt("Cu&t"), xlt("Cut selected data to clipboard"))
menu.Add(self.OnCopy, xlt("&Copy"), xlt("Copy selected data to clipboard"))
menu.Add(self.OnPaste, xlt("&Paste"), xlt("Paste data from clipboard"))
menu.Add(self.OnUndo, xlt("&Undo"), xlt("discard last editing"))
menubar.Append(menu, xlt("&Edit"))
self.helpmenu=menu=Menu(self)
menu.Add(self.OnHelp, xlt("Help"), xlt("Show help"), wx.ID_HELP)
menubar.Append(menu, xlt("&Help"))
self.EnableMenu(self.datamenu, self.OnCancelRefresh, False)
self.SetMenuBar(menubar)
toolbar.Realize()
ah=AcceleratorHelper(self)
ah.Add(wx.ACCEL_CTRL, 'X', self.OnCut)
ah.Add(wx.ACCEL_CTRL, 'C', self.OnCopy)
ah.Add(wx.ACCEL_CTRL, 'V', self.OnPaste)
ah.Add(wx.ACCEL_CTRL, 'S', self.OnSave)
ah.Add(wx.ACCEL_NORMAL,wx.WXK_F5, self.OnRefresh)
ah.Add(wx.ACCEL_ALT,wx.WXK_PAUSE, self.OnCancelRefresh)
ah.Realize()
self.notebook=wx.Notebook(self)
self.notebook.Bind(wx.EVT_NOTEBOOK_PAGE_CHANGED, self.OnCheck)
self.filter=FilterPanel(self, self.notebook)
self.notebook.AddPage(self.filter, xlt("Filter, Order, Limit"))
self.editor=SqlEditor(self.notebook)
self.editor.SetAcceleratorTable(ah.GetTable())
self.notebook.AddPage(self.editor, xlt("Manual SQL"))
self.manager.AddPane(self.notebook, wx.aui.AuiPaneInfo().Top().PaneBorder().Resizable().MinSize((200,200)).BestSize((400,200)).CloseButton(True) \
.Name("filter").Caption(xlt("SQL query parameter")).Hide())
self.output = SqlEditGrid(self, self.tableSpecs)
self.manager.AddPane(self.output, wx.aui.AuiPaneInfo().Center().MinSize((200,100)).BestSize((400,200)).CloseButton(False) \
.Name("Edit Data").Caption(xlt("Edit Data")).CaptionVisible(False))
self.restorePerspective()
self.manager.GetPane("Edit data").Show()
self.manager.Update()
self.manager.Bind(wx.aui.EVT_AUI_PANE_CLOSE, self.OnAuiCloseEvent)
self.viewmenu.Check(self.OnToggleFilter, self.manager.GetPane("filter").IsShown())
self.OnToggleToolBar()
self.OnToggleStatusBar()
self.updateMenu()
self.filter.Go(self.tableSpecs)
if not self.editor.GetText(): # otherwise set from default preset
self.editor.SetText("/*\n%s\n*/\n\n%s" % (xlt(
"Caution: Don't mess with table and column names!\nYou may experience unwanted behaviour or data loss."),
self.filter.GetQuery()))
def TablespaceSql(self):
ts = self.info['spcname']
if ts:
return "TABLESPACE %s" % quoteIdent(ts)
return ""
def GetSql(self):
sql = []
params = {
"colname": quoteIdent(self.ColName),
"oldcol": quoteIdent(self['ColName'].unchangedValue)
}
if self.HasChanged("ColName"):
sql.append("RENAME COLUMN %(oldcol)s TO %(colname)s" % params)
if self.HasChanged("NotNull"):
if self.NotNull:
params['val'] = "SET"
else:
params['val'] = "DROP"
sql.append("ALTER COLUMN %(colname)s %(val)s NOT NULL" % params)
if self.HasChanged("DefaultVal"):
if self.DefaultVal:
params['default'] = self.DefaultVal
sql.append("ALTER COLUMN %(colname)s SET DEFAULT %(default)s" %
params)
else:
sql.append("ALTER COLUMN (%colname)s DROP DEFAULT" % params)
if self.HasChanged("DataType Collation Length Precision"):
params['type'] = self['DataType'].GetValue()
n = "ALTER COLUMN %(colname)s TYPE %(type)s" % params
precFlag = self.typeInfo.get(self.DataType)
if precFlag and self.Length:
n += "(%d" % self.Length
if precFlag == 2 and self['Precision'].GetValue():
n += ", %d" % self.Precision
n += ")"
if self.HasChanged("Collation"):
n += " COLLATE %s" % quoteIdent(self['Collation'].GetValue())
sql.append(n)
if self.HasChanged("Statistics"):
params['val'] = self.Statistics
sql.append("ALTER COLUMN %(colname)s SET STATISTICS %(val)d" %
params)
# type, len, prec, collate
# if self.HasChanged("Collation"):
# params['val'] = self["Collation"].GetValue()
# sql.append("ALTER COLUMN %(colname)s SET COLLATE \"%(val)d\";" % params)
if sql:
sql = [
"ALTER TABLE %s\n %s;" %
(self.dialog.node.NameSql(), ",\n ".join(sql))
]
if self.HasChanged('Comment'):
params['tabname'] = self.dialog.node.NameSql()
params['comment'] = quoteValue(self.Comment)
sql.append(
"COMMENT ON COLUMN %(tabname)s.%(colname)s IS %(comment)s" %
params)
if sql:
return "\n".join(sql)
return ""