import time
import gobject, gtk, pango, cairo
import gtksourceview2 as gtksourceview
import psycopg2
import psycopg2.extensions
import psycopg2.extras
from pgconsole.config import cfg
from pgconsole.editor import Editor
from pgconsole.dataview import DataView
from pgconsole.database import Database, BadConnectionError, DatabaseError
from pgconsole.settings import Settings
from pgconsole.panedext import HPanedExt, VPanedExt
class PgConsoleApp:
def __init__(self):
self.db = Database()
self.conn = None
win = gtk.Window(gtk.WINDOW_TOPLEVEL)
self.win = win
win.set_title('PostgreSQL Console')
win.set_size_request(300, 200) # minimal size
win.set_default_size(800, 600)
self.restore_window_size()
win.connect("destroy", self.destroy)
win.connect("key_press_event", self.keypress)
win.connect('configure-event', self.on_configure)
# toolbar
toolbar = gtk.Toolbar()
toolbar.set_style(gtk.TOOLBAR_ICONS)
toolbar.set_property("icon-size", gtk.ICON_SIZE_SMALL_TOOLBAR)
tb = gtk.ToolButton(gtk.STOCK_PREFERENCES)
tb.set_label('Settings')
tb.set_tooltip_text('Settings')
tb.connect('clicked', self.settings)
self.tb_settings = tb
toolbar.add(tb)
self.cb_server = gtk.combo_box_entry_new_text()
self.cb_server.set_tooltip_text('Server')
self.cb_server.get_child().set_property("editable", False)
self.cb_server.set_property("add_tearoffs", True)
#self.cb_server.set_property("focus-on-click", False)
self.cb_server.set_property("can-focus", True)
self.cb_server.connect('changed', self.on_server_changed)
self.cb_server.connect("key_press_event", self.toolbar_server_keypress)
ti = gtk.ToolItem()
ti.add(self.cb_server)
toolbar.add(ti)
self.cb_dbname = gtk.combo_box_entry_new_text()
self.cb_dbname.set_tooltip_text('Database')
self.cb_dbname.get_child().set_property("editable", False)
self.cb_dbname.set_property("add_tearoffs", True)
#self.cb_dbname.set_property("focus-on-click", False)
self.cb_dbname.set_property("can-focus", True)
ti = gtk.ToolItem()
ti.add(self.cb_dbname)
toolbar.add(ti)
tb = gtk.ToolButton(gtk.STOCK_CONNECT)
tb.set_label('Connect')
tb.set_tooltip_text('Connect')
tb.connect('clicked', self.connect)
toolbar.add(tb)
self.tb_connect = tb
sep = gtk.SeparatorToolItem()
toolbar.add(sep)
tb = gtk.ToolButton(gtk.STOCK_EXECUTE)
tb.set_label('Execute')
tb.set_tooltip_text('Execute')
tb.connect('clicked', self.execute)
toolbar.add(tb)
tb.set_sensitive(False)
self.tb_execute = tb
tb = gtk.ToolButton(gtk.STOCK_NEW)
tb.set_label('Begin transaction')
tb.set_tooltip_text('Begin transaction')
tb.connect('clicked', self.begin)
tb.set_sensitive(False)
toolbar.add(tb)
self.tb_begin = tb
tb = gtk.ToolButton(gtk.STOCK_APPLY)
tb.set_label('Commit')
tb.set_tooltip_text('Commit')
tb.connect('clicked', self.commit)
tb.set_sensitive(False)
self.tb_commit = tb
toolbar.add(tb)
tb = gtk.ToolButton(gtk.STOCK_CANCEL)
tb.set_label('Rollback')
tb.set_tooltip_text('Rollback')
tb.connect('clicked', self.rollback)
tb.set_sensitive(False)
self.tb_rollback = tb
toolbar.add(tb)
sep = gtk.SeparatorToolItem()
toolbar.add(sep)
# editor
self.editor = Editor()
# data view
self.dataview = DataView()
vbox = gtk.VBox(False, 2)
sep = gtk.SeparatorToolItem()
sep.set_expand(True)
sep.set_draw(False)
toolbar.add(sep)
self.throbber_anim = gtk.gdk.PixbufAnimation('data/Throbber.gif')
self.throbber = gtk.Image()
tb = gtk.ToolItem()
tb.add(self.throbber)
toolbar.add(tb)
sep = gtk.SeparatorToolItem()
sep.set_draw(False)
toolbar.add(sep)
vbox.pack_start(toolbar, False, False, 0)
vpaned = VPanedExt()
vpaned.set_border_width(5)
hpaned = HPanedExt()
hpaned.set_border_width(0)
hpaned.add1(self.editor)
hpaned.child_set_property(self.editor, 'shrink', False)
hpaned.set_snap2(80)
hpaned.set_property('position', 500)
vpaned.add1(hpaned)
vpaned.set_snap1(80)
vpaned.set_property('position', 300)
vpaned.add2(self.dataview)
vpaned.set_snap2(80)
self.vpaned = vpaned
self.hpaned = hpaned
# log
self.logbuf = gtk.TextBuffer()
view = gtk.TextView(self.logbuf)
view.set_editable(False)
font_desc = pango.FontDescription('monospace')
if font_desc:
view.modify_font(font_desc)
sw = gtk.ScrolledWindow()
sw.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
sw.set_shadow_type(gtk.SHADOW_ETCHED_IN)
sw.add(view)
hpaned.add2(sw)
vbox.pack_start(vpaned, padding=0)
win.add(vbox)
self.editor.view.grab_focus()
self.reload_server_list(0)
win.show_all()
self.restore_win_state()
def main(self):
gtk.main()
def destroy(self, widget, data=None):
self.save_win_state()
cfg.save()
gtk.main_quit()
def on_configure(self, w, ev):
cfg.root.window.size.width = ev.width
cfg.root.window.size.height = ev.height
def restore_window_size(self):
self.win.resize(cfg.root.window.size.width, cfg.root.window.size.height)
def save_win_state(self):
cfg.root.window.dividers.verticaldivider = self.vpaned.get_position()
cfg.root.window.dividers.horizontaldivider = self.hpaned.get_position()
cfg.root.window.dividers.editordivider = self.editor.get_position()
def restore_win_state(self):
pos = cfg.root.window.dividers.verticaldivider
if pos >= 0:
self.vpaned.set_position(pos)
pos = cfg.root.window.dividers.horizontaldivider
if pos >= 0:
self.hpaned.set_position(pos)
pos = cfg.root.window.dividers.editordivider
if pos >= 0:
self.editor.set_position(pos)
def get_typename(self, oid, size):
self.curs.execute('SELECT typname FROM pg_type WHERE oid=%s', [oid])
psycopg2.extras.wait_select(self.curs.connection)
row = self.curs.fetchone()
typname = None
if row:
typname = row[0]
if typname == 'int4':
return 'integer'
if typname in ('timestamp', 'interval', 'date'):
return typname
if size and size > 0 and size < 65535:
typname += '(%s)' % size
return typname
def get_conninfo(self, nodb=False):
sel = self.cb_server.get_active()
srv = cfg.servers.server[sel]
if nodb:
dbname = 'postgres'
else:
dbname = self.cb_dbname.get_active_text()
conninfo = 'host=%s port=%s dbname=%s user=%s password=%s' \
% (srv.host, srv.port, dbname, srv.user, srv.password)
return conninfo
def connect(self, w):
conninfo = self.get_conninfo()
if self.conn:
# disconnect
self.db.put_conn(conninfo, self.conn)
self.conn = None
self.cb_server.set_sensitive(True)
self.cb_dbname.set_sensitive(True)
self.tb_connect.set_stock_id(gtk.STOCK_CONNECT)
self.tb_connect.set_label('Connect')
self.tb_connect.set_tooltip_text('Connect')
self.tb_execute.set_sensitive(False)
self.tb_begin.set_sensitive(False)
else:
# connect
self.logbuf.insert(self.logbuf.get_end_iter(), 'Connect %s\n' % conninfo)
try:
self.conn = self.db.get_conn(conninfo)
except DatabaseError as e:
self.logbuf.insert(self.logbuf.get_end_iter(), 'Error:\n%s\n' % e)
return
self.cb_server.set_sensitive(False)
self.cb_dbname.set_sensitive(False)
self.tb_connect.set_stock_id(gtk.STOCK_DISCONNECT)
self.tb_connect.set_label('Disconnect')
self.tb_connect.set_tooltip_text('Disconnect')
self.tb_execute.set_sensitive(True)
self.tb_begin.set_sensitive(True)
def begin(self, w):
self.logbuf.insert(self.logbuf.get_end_iter(), 'Begin transaction\n')
curs = self.conn.cursor()
curs.execute('BEGIN')
psycopg2.extras.wait_select(curs.connection)
self.tb_connect.set_sensitive(False)
self.tb_begin.set_sensitive(False)
self.tb_commit.set_sensitive(True)
self.tb_rollback.set_sensitive(True)
def commit(self, w):
self.logbuf.insert(self.logbuf.get_end_iter(), 'Commit\n')
curs = self.conn.cursor()
curs.execute('COMMIT')
psycopg2.extras.wait_select(curs.connection)
self.tb_connect.set_sensitive(True)
self.tb_begin.set_sensitive(True)
self.tb_commit.set_sensitive(False)
self.tb_rollback.set_sensitive(False)
def rollback(self, w):
self.logbuf.insert(self.logbuf.get_end_iter(), 'Rollback\n')
curs = self.conn.cursor()
curs.execute('ROLLBACK')
psycopg2.extras.wait_select(curs.connection)
self.tb_connect.set_sensitive(True)
self.tb_begin.set_sensitive(True)
self.tb_commit.set_sensitive(False)
self.tb_rollback.set_sensitive(False)
def execute(self, widget):
query = self.editor.get_selection() or self.editor.get_text()
self.tb_connect.set_sensitive(False)
self.tb_execute.set_sensitive(False)
self.tb_begin.set_sensitive(False)
self.tb_commit.set_sensitive(False)
self.tb_rollback.set_sensitive(False)
self.throbber.set_from_animation(self.throbber_anim)
self.curs = self.conn.cursor()
self.t1 = time.time()
try:
self.curs.execute(query)
except (psycopg2.OperationalError, psycopg2.DatabaseError) as e:
self.logbuf.insert(self.logbuf.get_end_iter(), 'Error:\n' + str(e))
return
self.execute_poll()
def execute_poll(self, source=None, cond=None):
try:
state = self.conn.poll()
except (psycopg2.OperationalError, psycopg2.DatabaseError) as e:
self.logbuf.insert(self.logbuf.get_end_iter(), 'Error:\n' + str(e))
return
if state == psycopg2.extensions.POLL_OK:
self.execute_finish()
elif state == psycopg2.extensions.POLL_WRITE:
gobject.io_add_watch(self.conn.fileno(), gobject.IO_OUT, self.execute_poll)
elif state == psycopg2.extensions.POLL_READ:
gobject.io_add_watch(self.conn.fileno(), gobject.IO_IN, self.execute_poll)
else:
self.logbuf.insert(self.logbuf.get_end_iter(), "poll() returned %s" % state)
return
def execute_finish(self):
t2 = time.time()
t = (t2 - self.t1)*1000
self.throbber.clear()
self.logbuf.insert(self.logbuf.get_end_iter(),
'Query successful (%d ms, %d rows)\n' % (t, self.curs.rowcount))
# notices
for n in self.conn.notices:
self.logbuf.insert(self.logbuf.get_end_iter(), n)
if self.curs.rowcount >= 0:
rows = self.curs.fetchall()
names = []
for c in self.curs.description:
name = c[0]
typename = self.get_typename(c[1], c[3])
names += [(name, typename)]
self.dataview.load_data(names, rows)
self.tb_execute.set_sensitive(True)
if self.conn.get_transaction_status() == psycopg2.extensions.TRANSACTION_STATUS_INTRANS:
self.tb_commit.set_sensitive(True)
self.tb_rollback.set_sensitive(True)
else:
self.tb_connect.set_sensitive(True)
self.tb_begin.set_sensitive(True)
def simulate_click(self, tb):
if tb.get_property('sensitive'):
tb.get_child().activate()
def keypress(self, w, event):
keyname = gtk.gdk.keyval_name(event.keyval)
if keyname == 'F1':
self.simulate_click(self.tb_settings)
return True
if keyname == 'F2':
print(self.cb_server.popup())
return True
if keyname == 'F3':
print(self.cb_dbname.popup())
return True
if keyname == 'F4':
self.simulate_click(self.tb_connect)
return True
if keyname == 'F5':
self.simulate_click(self.tb_execute)
return True
if keyname == 'F6':
self.simulate_click(self.tb_begin)
return True
if keyname == 'F7':
self.simulate_click(self.tb_commit)
return True
if keyname == 'F8':
self.simulate_click(self.tb_rollback)
return True
return False
def toolbar_server_keypress(self, w, event):
keyname = gtk.gdk.keyval_name(event.keyval)
if keyname == 'Tab':
self.populate_db_list()
self.cb_dbname.grab_focus()
return True
def on_server_changed(self, w):
if self.cb_server.get_active() != -1:
self.populate_db_list()
def populate_db_list(self):
conninfo = self.get_conninfo(True)
try:
conn = self.db.get_conn(conninfo)
except DatabaseError as e:
self.logbuf.insert(self.logbuf.get_end_iter(), 'Error:\n%s\n' % e)
return
curs = conn.cursor()
curs.execute('SELECT * FROM pg_catalog.pg_database WHERE NOT datistemplate ORDER BY 1')
psycopg2.extras.wait_select(conn)
rows = curs.fetchall()
self.db.put_conn(conninfo, conn)
for i in range(self.cb_dbname.get_model().iter_n_children(None)):
self.cb_dbname.remove_text(0)
for row in rows:
self.cb_dbname.append_text(row[0])
self.cb_dbname.set_active(0)
def reload_server_list(self, sel=None):
# clean
for i in range(self.cb_server.get_model().iter_n_children(None)):
self.cb_server.remove_text(0)
self.cb_server.get_child().set_text('')
try:
# populate
for server in cfg.servers.server:
if str(server.name):
title = '%s (%s)' % (str(server.name), str(server.host))
else:
title = str(server.host)
self.cb_server.append_text(title)
except AttributeError:
pass
if not sel is None:
self.cb_server.set_active(sel)
def settings(self, w):
Settings(self)