Geografic-Information-System/gui/wxpython/gui_modules/sqlbuilder.py

"""
MODULE:    sqlbuilder.py

CLASSES:
    * SQLFrame

PURPOSE:   GRASS SQL Builder

           Usage:
           sqlbuilder.py table_name

AUTHOR(S): GRASS Development Team
           Original author: Jachym Cepicky <jachym.cepicky gmail.com>
           Various updates: Martin Landa <landa.martin gmail.com>,
                            Hamish Bowman <hamish_b yahoo com>

COPYRIGHT: (C) 2007-2009 by the GRASS Development Team

           This program is free software under the GNU General Public
           License (>=v2). Read the file COPYING that comes with GRASS
           for details.
"""

import wx
import os,sys
import time

from grass.script import core as grass

import gcmd
import globalvar

imagePath = os.path.join(globalvar.ETCWXDIR)
sys.path.append(imagePath)
import images
imagepath = images.__path__[0]
sys.path.append(imagepath)

class SQLFrame(wx.Frame):
    """!SQL Frame class"""
    def __init__(self, parent, id, title, vectmap, qtype="select"):

        wx.Frame.__init__(self, parent, id, title)

        self.SetTitle(_("GRASS SQL Builder: %s") % (qtype.upper()))
        self.SetIcon(wx.Icon(os.path.join(globalvar.ETCICONDIR, 'grass_sql.ico'), wx.BITMAP_TYPE_ICO))

        #
        # variables
        #
        self.vectmap = vectmap
        if not "@" in self.vectmap:
            self.vectmap = self.vectmap + "@" + grass.gisenv()['MAPSET']

        self.mapname, self.mapset = self.vectmap.split("@")

        #FIXME: pass layer number to v.db.connect ???
        ret = gcmd.RunCommand('v.db.connect',
                      quiet = True,
                      read = True,
                      flags = 'g',
                      fs = '|',
                      map = self.vectmap)
        for line in ret.splitlines():
            self.layer,self.tablename, self.column, self.database, self.driver =\
                line.strip().split("|")

        self.qtype = qtype      # type of the uqery: SELECT, UPDATE, DELETE, ...
        self.column_names = []  # array with column names
        self.columns = {}       # array with colum properties
        self.colvalues = []     # array with uniqe values in selected column
        self.heading = ""
        self.parent = parent

        if self.qtype.lower()=="select":
            self.heading = "SELECT * FROM %s WHERE" % self.tablename


        # Init
        self.GetColumns()


        #
        # Buttons
        #
        self.btn_clear = wx.Button(self, -1, "Clear")
        self.btn_verify = wx.Button(self, -1, "Verify")
        #self.btn_help = wx.Button(self, -1, "Help")
        # self.btn_load = wx.Button(self, -1, "Load")
        # self.btn_save = wx.Button(self, -1, "Save")
        self.btn_apply = wx.Button(self, -1, "Apply")
        self.btn_close = wx.Button(self, -1, "Close")
        self.btn_uniqe = wx.Button(self, -1, "Get all values")
        self.btn_uniqesample = wx.Button(self, -1, "Get sample")

        self.btn_is = wx.Button(self, -1, "=")
        self.btn_isnot = wx.Button(self, -1, "!=")
        self.btn_like = wx.Button(self, -1, "LIKE")
        self.btn_gt = wx.Button(self, -1, ">")
        self.btn_gtis = wx.Button(self, -1, ">=")
        self.btn_lt = wx.Button(self, -1, "<")
        self.btn_ltis = wx.Button(self, -1, "<=")
        self.btn_or = wx.Button(self, -1, "OR")
        self.btn_not = wx.Button(self, -1, "NOT")
        self.btn_and = wx.Button(self, -1, "AND")
        self.btn_brackets = wx.Button(self, -1, "()")
        self.btn_prc = wx.Button(self, -1, "%")

        #
        # Text labels
        #
        #self.label_headding = wx.StaticText(self, -1, '')

        #
        # Textareas
        #
        self.text_sql = wx.TextCtrl(self, -1, '', size=(-1,75),style=wx.TE_MULTILINE)

        #
        # List Boxes
        #
        self.list_columns = wx.ListBox(self, -1, wx.DefaultPosition, (-1, -1), self.columns.keys(), wx.LB_MULTIPLE|wx.LB_SORT)
        self.list_values = wx.ListBox(self, -1, wx.DefaultPosition, (-1, -1), self.colvalues, wx.LB_MULTIPLE|wx.LB_SORT)

        #
        # Bindings
        #
        self.btn_uniqe.Bind(wx.EVT_BUTTON, self.GetUniqueValues)
        self.btn_uniqesample.Bind(wx.EVT_BUTTON, self.GetSampleValues)
        self.btn_is.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_isnot.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_like.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_gt.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_gtis.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_or.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_lt.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_ltis.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_not.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_brackets.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_prc.Bind(wx.EVT_BUTTON, self.AddMark)
        self.btn_and.Bind(wx.EVT_BUTTON, self.AddMark)
        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.AddColumnName)
        self.list_values.Bind(wx.EVT_LISTBOX, self.AddValue)

        self.__doLayout()

    def __doLayout(self):
        databasebox = wx.StaticBox(self, -1, "Database connection")
        databaseboxsizer = wx.StaticBoxSizer(databasebox,wx.VERTICAL)
        dbstr = "Database: %s\n" % (self.database)
        dbstr += "Driver: %s\n" % (self.driver)
        dbstr += "Table: %s" % (self.tablename)
        databaseboxsizer.Add(wx.StaticText(self,-1,dbstr), flag=wx.EXPAND)

        sqlbox = wx.StaticBox(self, -1, "%s" % self.heading)
        sqlboxsizer = wx.StaticBoxSizer(sqlbox,wx.VERTICAL)
        sqlboxsizer.Add(self.text_sql, flag=wx.EXPAND)


        pagesizer = wx.BoxSizer(wx.VERTICAL)

        buttonsizer1 = wx.GridBagSizer(2,2)
        buttonsizer1.Add(self.btn_clear, (0,0))
        buttonsizer1.Add(self.btn_verify, (0,1))
        #buttonsizer1.Add(self.btn_help,  (0,2))
        #buttonsizer1.Add(self.btn_load,  (0,2))
        # buttonsizer1.Add(self.btn_save,  (0,3))
        buttonsizer1.Add(self.btn_apply, (0,2))

        buttonsizer2 = wx.GridBagSizer(2, 2)
        buttonsizer2.Add(self.btn_is, (0,0))
        buttonsizer2.Add(self.btn_isnot, (1,0))
        buttonsizer2.Add(self.btn_like, (2, 0))

        buttonsizer2.Add(self.btn_gt, (0, 1))
        buttonsizer2.Add(self.btn_gtis, (1, 1))
        buttonsizer2.Add(self.btn_or, (2, 1))

        buttonsizer2.Add(self.btn_lt, (0, 2))
        buttonsizer2.Add(self.btn_ltis, (1, 2))
        buttonsizer2.Add(self.btn_not, (2, 2))

        buttonsizer2.Add(self.btn_brackets, (0, 3))
        buttonsizer2.Add(self.btn_prc, (1, 3))
        buttonsizer2.Add(self.btn_and, (2, 3))

        buttonsizer3 = wx.GridSizer(4, 3, 3, 3)
        buttonsizer3.Add(self.btn_apply,0,wx.RIGHT,5)
        buttonsizer3.Add(self.btn_close,0,wx.RIGHT,5)

        buttonsizer4 = wx.BoxSizer(wx.HORIZONTAL)
        buttonsizer4.Add(self.btn_uniqesample,0,flag=wx.ALIGN_CENTER_HORIZONTAL,border=5)
        buttonsizer4.Add(self.btn_uniqe,0,flag=wx.ALIGN_CENTER_HORIZONTAL,border=5)

        hsizer1 = wx.BoxSizer(wx.HORIZONTAL)
        #hsizer2 = wx.BoxSizer(wx.HORIZONTAL)

        columnsbox = wx.StaticBox(self,-1,"Columns: ")
        valuesbox = wx.StaticBox(self,-1,"Values: ")
        #hsizer1.Add(wx.StaticText(self,-1, "Unique values: "), border=0, proportion=1)
        columnsizer = wx.StaticBoxSizer(columnsbox,wx.VERTICAL)
        valuesizer = wx.StaticBoxSizer(valuesbox,wx.VERTICAL)
        columnsizer.Add(self.list_columns,  flag=wx.EXPAND,)
        valuesizer.Add(self.list_values,  flag=wx.EXPAND)
        self.list_columns.SetMinSize((-1,130))
        self.list_values.SetMinSize((-1,100))
        valuesizer.Add(buttonsizer4)
        hsizer1.Add(columnsizer,border=0, proportion=1)
        hsizer1.Add(valuesizer,border=0, proportion=1)

        pagesizer.Add(databaseboxsizer,flag=wx.EXPAND,border=5)
        pagesizer.Add(hsizer1, 1,flag=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(buttonsizer2, 0, wx.ALIGN_CENTER_HORIZONTAL|wx.TOP, border=5)
        pagesizer.Add(sqlboxsizer, flag=wx.EXPAND,border=5)
        pagesizer.Add(buttonsizer1, 0, wx.ALIGN_CENTER_HORIZONTAL|wx.TOP, border=5)
        pagesizer.Add(buttonsizer3, proportion=0, flag=wx.TOP, border=5)
        self.SetAutoLayout(True)
        self.SetSizer(pagesizer)
        pagesizer.Fit(self)
        #pagesizer.SetSizeHints(self)
        self.Layout()
        self.Show(True)

    def GetColumns(self):
        """!Get columns"""
        ret = gcmd.RunCommand('db.describe',
                              quiet = True,
                              read = True,
                              flags = 'c',
                              table = self.tablename,
                              database = self.database,
                              driver = self.driver)
        
        for line in ret.splitlines():
            # skip ncols and nrows lines
            linetype = line.strip().split(":")[0]
            if linetype == "ncols" or linetype == "nrows":
                continue

            num, name, ctype, length = line.strip().split(":")
            name.strip()
            #self.columns_names.append(name)
            self.columns[name] = {'type' : ctype}


    def GetUniqueValues(self,event,justsample=False):
        vals = []
        try:
            idx = self.list_columns.GetSelections()[0]
        except:
            return
        self.list_values.Clear()
        column = self.list_columns.GetString(idx)
        i = 0
        querystring = "SELECT %s FROM %s" % (column, self.tablename)

        ret = gcmd.RunCommand('db.select',
                              read = True,
                              quiet = True,
                              flags = 'c',
                              database = self.database,
                              driver = self.driver,
                              sql = querystring)

        for line in ret.splitlines():
            if justsample and i < 256 or \
               not justsample:
                self.list_values.Insert(line.strip(),0)
            else:
                break
            i += 1

    def GetSampleValues(self,event):
        self.GetUniqueValues(None,True)

    def AddColumnName(self,event):
        idx = self.list_columns.GetSelections()[0]
        column = self.list_columns.GetString(idx)
        self.__addSomething(column)

    def AddValue(self,event):
        idx = self.list_values.GetSelections()[0]
        value = self.list_values.GetString(idx)
        idx = self.list_columns.GetSelections()[0]
        column = self.list_columns.GetString(idx)

        if self.columns[column]['type'].lower().find("chara") > -1:
            value = "'%s'" % value
        self.__addSomething(value)

    def AddMark(self,event):
        if event.GetId() == self.btn_is.GetId(): mark = "="
        elif event.GetId() == self.btn_isnot.GetId(): mark = "!="
        elif event.GetId() == self.btn_like.GetId(): mark = "LIKE"
        elif event.GetId() == self.btn_gt.GetId(): mark = ">"
        elif event.GetId() == self.btn_gtis.GetId(): mark = ">="
        elif event.GetId() == self.btn_lt.GetId(): mark = "<"
        elif event.GetId() == self.btn_ltis.GetId(): mark =  "<="
        elif event.GetId() == self.btn_or.GetId(): mark =  "OR"
        elif event.GetId() == self.btn_not.GetId(): mark = "NOT"
        elif event.GetId() == self.btn_and.GetId(): mark = "AND"
        elif event.GetId() == self.btn_brackets.GetId(): mark = "()"
        elif event.GetId() == self.btn_prc.GetId(): mark = "%"
        self.__addSomething(mark)


    def __addSomething(self,what):
        sqlstr = self.text_sql.GetValue()
        newsqlstr = ''
        position = self.text_sql.GetPosition()[0]
        selection = self.text_sql.GetSelection()

        newsqlstr = sqlstr[:position]
        try:
            if newsqlstr[-1] != " ":
                newsqlstr += " "
        except:
            pass

        newsqlstr += what
        newsqlstr += " "+sqlstr[position:]

        self.text_sql.SetValue(newsqlstr)
        # FIXME: cursor position is messed up
        #   see also   http://trac.wxwidgets.org/ticket/10051
        #DEBUG: print "before: %d" % (position)
        self.text_sql.SetInsertionPoint(position)
        #? self.text_sql.SetInsertionPoint(position+len(what))
        #? self.text_sql.SetInsertionPointEnd()
        #DEBUG: print "len(what)=%d" % (len(what))
        #DEBUG: print "after: %d" % (self.text_sql.GetPosition()[0])
        #DEBUG: print " "

    def OnApply(self,event):
        if self.parent:
            try:
                self.parent.text_query.SetValue= self.text_sql.GetValue().strip().replace("\n"," ")
            except:
                pass

    def OnVerify(self,event):
        if self.text_sql.GetValue():
            querystring = "SELECT * FROM %s WHERE %s" % \
                          (self.tablename,
                           self.text_sql.GetValue().strip().replace("\n"," "))
            # FIXME: LOG
            # print self.text_sql.GetValue().strip().replace("\n"," "), "not correct!"

            print "Testing [%s] ..." % (querystring)
            ret, msg = gcmd.RunCommand('db.select',
                                  getErrorMsg = True,
                                  verbose = True,
                                  flags = 't',
                                  driver = self.driver,
                                  database = self.database,
                                  sql = querystring)
            print msg, " "
            if ret == 0:
                pass

    def OnClear(self, event):
        self.text_sql.SetValue("")

    def OnClose(self,event):
        self.Destroy()

if __name__ == "__main__":

    if len(sys.argv) != 2:
        print >>sys.stderr, __doc__
        sys.exit()

    app = wx.App(0)
    sqlb = SQLFrame(None, -1, 'SQL Builder',sys.argv[1])
    app.MainLoop()