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)