1"""
2@package dbmgr.sqlbuilder
3
4@brief GRASS SQL Select/Update Builder
5
6Classes:
7 - sqlbuilder::SQLBuilder
8 - sqlbuilder::SQLBuilderSelect
9 - sqlbuilder::SQLBuilderUpdate
10
11Usage:
12@code
13python sqlbuilder.py select|update vector_map
14@endcode
15
16(C) 2007-2014 by the GRASS Development Team
17
18This program is free software under the GNU General Public License
19(>=v2). Read the file COPYING that comes with GRASS for details.
20
21@author Jachym Cepicky <jachym.cepicky gmail.com> (original author)
22@author Martin Landa <landa.martin gmail.com>
23@author Hamish Bowman <hamish_b yahoo.com>
24@author Refactoring, SQLBUilderUpdate by Stepan Turek <stepan.turek seznam.cz> (GSoC 2012, mentor: Martin Landa)
25"""
26
27from __future__ import print_function
28
29import os
30import sys
31import six
32
33from core import globalvar
34import wx
35
36from grass.pydispatch.signal import Signal
37
38from core.gcmd import RunCommand, GError, GMessage
39from dbmgr.vinfo import CreateDbInfoDesc, VectorDBInfo, GetUnicodeValue
40from gui_core.wrap import ApplyButton, Button, ClearButton, CloseButton, \
41    TextCtrl, StaticText, StaticBox
42
43import grass.script as grass
44
45
46class SQLBuilder(wx.Frame):
47    """SQLBuider class
48    Base class for classes, which builds SQL statements.
49    """
50
51    def __init__(self, parent, title, vectmap, modeChoices=[], id=wx.ID_ANY,
52                 layer=1):
53        wx.Frame.__init__(self, parent, id, title)
54
55        self.SetIcon(wx.Icon(os.path.join(globalvar.ICONDIR, 'grass_sql.ico'),
56                             wx.BITMAP_TYPE_ICO))
57
58        self.parent = parent
59
60        # variables
61        self.vectmap = vectmap  # fullname
62        if not "@" in self.vectmap:
63            self.vectmap = grass.find_file(
64                self.vectmap, element='vector')['fullname']
65            if not self.vectmap:
66                grass.fatal(_("Vector map <%s> not found") % vectmap)
67        self.mapname, self.mapset = self.vectmap.split("@", 1)
68
69        # db info
70        self.layer = layer
71        self.dbInfo = VectorDBInfo(self.vectmap)
72        self.tablename = self.dbInfo.GetTable(self.layer)
73
74        self.driver, self.database = self.dbInfo.GetDbSettings(self.layer)
75
76        self.colvalues = []     # array with unique values in selected column
77
78        self.panel = wx.Panel(parent=self, id=wx.ID_ANY)
79
80        # statusbar
81        self.statusbar = self.CreateStatusBar(number=1)
82
83        self._doLayout(modeChoices)
84
85        self.panel.SetAutoLayout(True)
86        self.panel.SetSizer(self.pagesizer)
87        self.pagesizer.Fit(self.panel)
88
89        self.SetMinSize((400, 600))
90        self.SetClientSize(self.panel.GetSize())
91        self.CenterOnParent()
92
93    def _doLayout(self, modeChoices, showDbInfo=False):
94        """Do dialog layout"""
95
96        self.pagesizer = wx.BoxSizer(wx.VERTICAL)
97
98        # dbInfo
99        if showDbInfo:
100            databasebox = StaticBox(parent=self.panel, id=wx.ID_ANY,
101                                    label=" %s " % _("Database connection"))
102            databaseboxsizer = wx.StaticBoxSizer(databasebox, wx.VERTICAL)
103            databaseboxsizer.Add(
104                CreateDbInfoDesc(
105                    self.panel,
106                    self.dbInfo,
107                    layer=self.layer),
108                proportion=1,
109                flag=wx.EXPAND | wx.ALL,
110                border=3)
111
112        #
113        # text areas
114        #
115        # sql box
116        sqlbox = StaticBox(parent=self.panel, id=wx.ID_ANY,
117                           label=" %s " % _("Query"))
118        sqlboxsizer = wx.StaticBoxSizer(sqlbox, wx.VERTICAL)
119
120        self.text_sql = TextCtrl(parent=self.panel, id=wx.ID_ANY,
121                                    value='', size=(-1, 50),
122                                    style=wx.TE_MULTILINE)
123
124        self.text_sql.SetInsertionPointEnd()
125        wx.CallAfter(self.text_sql.SetFocus)
126
127        sqlboxsizer.Add(self.text_sql, flag=wx.EXPAND)
128
129        #
130        # buttons
131        #
132        self.btn_clear = ClearButton(parent=self.panel)
133        self.btn_clear.SetToolTip(_("Set SQL statement to default"))
134        self.btn_apply = ApplyButton(parent=self.panel)
135        self.btn_apply.SetToolTip(_("Apply SQL statement"))
136        self.btn_close = CloseButton(parent=self.panel)
137        self.btn_close.SetToolTip(_("Close the dialog"))
138
139        self.btn_logic = {'is': ['=', ],
140                          'isnot': ['!=', ],
141                          'like': ['LIKE', ],
142                          'gt': ['>', ],
143                          'ge': ['>=', ],
144                          'lt': ['<', ],
145                          'le': ['<=', ],
146                          'or': ['OR', ],
147                          'not': ['NOT', ],
148                          'and': ['AND', ],
149                          'brac': ['()', ],
150                          'prc': ['%', ]}
151
152        self.btn_logicpanel = wx.Panel(parent=self.panel, id=wx.ID_ANY)
153        for key, value in six.iteritems(self.btn_logic):
154            btn = Button(parent=self.btn_logicpanel, id=wx.ID_ANY,
155                            label=value[0])
156            self.btn_logic[key].append(btn.GetId())
157
158        self.buttonsizer = wx.FlexGridSizer(cols=4, hgap=5, vgap=5)
159        self.buttonsizer.Add(self.btn_clear)
160        self.buttonsizer.Add(self.btn_apply)
161        self.buttonsizer.Add(self.btn_close)
162
163        btn_logicsizer = wx.GridBagSizer(5, 5)
164        btn_logicsizer.Add(
165            self.FindWindowById(
166                self.btn_logic['is'][1]), pos=(
167                0, 0))
168        btn_logicsizer.Add(
169            self.FindWindowById(
170                self.btn_logic['isnot'][1]), pos=(
171                1, 0))
172        btn_logicsizer.Add(
173            self.FindWindowById(
174                self.btn_logic['like'][1]), pos=(
175                2, 0))
176
177        btn_logicsizer.Add(
178            self.FindWindowById(
179                self.btn_logic['gt'][1]), pos=(
180                0, 1))
181        btn_logicsizer.Add(
182            self.FindWindowById(
183                self.btn_logic['ge'][1]), pos=(
184                1, 1))
185        btn_logicsizer.Add(
186            self.FindWindowById(
187                self.btn_logic['or'][1]), pos=(
188                2, 1))
189
190        btn_logicsizer.Add(
191            self.FindWindowById(
192                self.btn_logic['lt'][1]), pos=(
193                0, 2))
194        btn_logicsizer.Add(
195            self.FindWindowById(
196                self.btn_logic['le'][1]), pos=(
197                1, 2))
198        btn_logicsizer.Add(
199            self.FindWindowById(
200                self.btn_logic['not'][1]), pos=(
201                2, 2))
202
203        btn_logicsizer.Add(
204            self.FindWindowById(
205                self.btn_logic['brac'][1]), pos=(
206                0, 3))
207        btn_logicsizer.Add(
208            self.FindWindowById(
209                self.btn_logic['prc'][1]), pos=(
210                1, 3))
211        btn_logicsizer.Add(
212            self.FindWindowById(
213                self.btn_logic['and'][1]), pos=(
214                2, 3))
215
216        self.btn_logicpanel.SetSizer(btn_logicsizer)
217
218        #
219        # list boxes (columns, values)
220        #
221        self.hsizer = wx.BoxSizer(wx.HORIZONTAL)
222
223        columnsbox = StaticBox(parent=self.panel, id=wx.ID_ANY,
224                               label=" %s " % _("Columns"))
225        columnsizer = wx.StaticBoxSizer(columnsbox, wx.VERTICAL)
226        self.list_columns = wx.ListBox(
227            parent=self.panel,
228            id=wx.ID_ANY,
229            choices=self.dbInfo.GetColumns(
230                self.tablename),
231            style=wx.LB_MULTIPLE)
232        columnsizer.Add(self.list_columns, proportion=1,
233                        flag=wx.EXPAND)
234
235        if modeChoices:
236            modesizer = wx.BoxSizer(wx.VERTICAL)
237
238            self.mode = wx.RadioBox(parent=self.panel, id=wx.ID_ANY,
239                                    label=" %s " % _("Interactive insertion"),
240                                    choices=modeChoices,
241                                    style=wx.RA_SPECIFY_COLS,
242                                    majorDimension=1)
243
244            self.mode.SetSelection(1)  # default 'values'
245            modesizer.Add(self.mode, proportion=1,
246                          flag=wx.EXPAND, border=5)
247
248        # self.list_columns.SetMinSize((-1,130))
249        # self.list_values.SetMinSize((-1,100))
250
251        self.valuespanel = wx.Panel(parent=self.panel, id=wx.ID_ANY)
252        valuesbox = StaticBox(parent=self.valuespanel, id=wx.ID_ANY,
253                              label=" %s " % _("Values"))
254        valuesizer = wx.StaticBoxSizer(valuesbox, wx.VERTICAL)
255        self.list_values = wx.ListBox(parent=self.valuespanel, id=wx.ID_ANY,
256                                      choices=self.colvalues,
257                                      style=wx.LB_MULTIPLE)
258        valuesizer.Add(self.list_values, proportion=1,
259                       flag=wx.EXPAND)
260        self.valuespanel.SetSizer(valuesizer)
261
262        self.btn_unique = Button(parent=self.valuespanel, id=wx.ID_ANY,
263                                    label=_("Get all values"))
264        self.btn_unique.Enable(False)
265        self.btn_uniquesample = Button(
266            parent=self.valuespanel,
267            id=wx.ID_ANY,
268            label=_("Get sample"))
269        self.btn_uniquesample.SetToolTip(
270            _("Get first 256 unique values as sample"))
271        self.btn_uniquesample.Enable(False)
272
273        buttonsizer3 = wx.BoxSizer(wx.HORIZONTAL)
274        buttonsizer3.Add(self.btn_uniquesample, proportion=0,
275                         flag=wx.RIGHT, border=5)
276        buttonsizer3.Add(self.btn_unique, proportion=0)
277
278        valuesizer.Add(buttonsizer3, proportion=0,
279                       flag=wx.TOP, border=5)
280
281        # go to
282        gotosizer = wx.BoxSizer(wx.HORIZONTAL)
283        self.goto = TextCtrl(
284            parent=self.valuespanel,
285            id=wx.ID_ANY,
286            style=wx.TE_PROCESS_ENTER)
287        gotosizer.Add(StaticText(parent=self.valuespanel, id=wx.ID_ANY,
288                                 label=_("Go to:")), proportion=0,
289                      flag=wx.ALIGN_CENTER_VERTICAL | wx.RIGHT, border=5)
290        gotosizer.Add(self.goto, proportion=1,
291                      flag=wx.EXPAND)
292        valuesizer.Add(gotosizer, proportion=0,
293                       flag=wx.ALL | wx.EXPAND, border=5)
294
295        self.hsizer.Add(columnsizer, proportion=1,
296                        flag=wx.EXPAND)
297        self.hsizer.Add(self.valuespanel, proportion=1,
298                        flag=wx.EXPAND)
299
300        self.close_onapply = wx.CheckBox(parent=self.panel, id=wx.ID_ANY,
301                                         label=_("Close dialog on apply"))
302        self.close_onapply.SetValue(True)
303
304        if showDbInfo:
305            self.pagesizer.Add(databaseboxsizer,
306                               flag=wx.ALL | wx.EXPAND, border=5)
307        if modeChoices:
308            self.pagesizer.Add(
309                modesizer,
310                proportion=0,
311                flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND,
312                border=5)
313        self.pagesizer.Add(
314            self.hsizer,
315            proportion=1,
316            flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND,
317            border=5)
318        # self.pagesizer.Add(self.btn_uniqe,0,wx.ALIGN_LEFT|wx.TOP,border=5)
319        # self.pagesizer.Add(self.btn_uniqesample,0,wx.ALIGN_LEFT|wx.TOP,border=5)
320        self.pagesizer.Add(self.btn_logicpanel, proportion=0,
321                           flag=wx.ALIGN_CENTER_HORIZONTAL)
322        self.pagesizer.Add(sqlboxsizer, proportion=0,
323                           flag=wx.EXPAND | wx.LEFT | wx.RIGHT, border=5)
324        self.pagesizer.Add(self.buttonsizer, proportion=0,
325                           flag=wx.ALIGN_RIGHT | wx.ALL, border=5)
326        self.pagesizer.Add(
327            self.close_onapply,
328            proportion=0,
329            flag=wx.LEFT | wx.RIGHT | wx.BOTTOM | wx.EXPAND,
330            border=5)
331
332        #
333        # bindings
334        #
335        if modeChoices:
336            self.mode.Bind(wx.EVT_RADIOBOX, self.OnMode)
337        # self.text_sql.Bind(wx.EVT_ACTIVATE, self.OnTextSqlActivate)TODO
338
339        self.btn_unique.Bind(wx.EVT_BUTTON, self.OnUniqueValues)
340        self.btn_uniquesample.Bind(wx.EVT_BUTTON, self.OnSampleValues)
341
342        for key, value in six.iteritems(self.btn_logic):
343            self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.OnAddMark)
344
345        self.btn_close.Bind(wx.EVT_BUTTON, self.OnClose)
346        self.btn_clear.Bind(wx.EVT_BUTTON, self.OnClear)
347        self.btn_apply.Bind(wx.EVT_BUTTON, self.OnApply)
348
349        self.list_columns.Bind(wx.EVT_LISTBOX, self.OnAddColumn)
350        self.list_values.Bind(wx.EVT_LISTBOX, self.OnAddValue)
351        self.goto.Bind(wx.EVT_TEXT, self.OnGoTo)
352        self.goto.Bind(wx.EVT_TEXT_ENTER, self.OnAddValue)
353
354    def OnUniqueValues(self, event, justsample=False):
355        """Get unique values"""
356        vals = []
357        try:
358            idx = self.list_columns.GetSelections()[0]
359            column = self.list_columns.GetString(idx)
360        except:
361            self.list_values.Clear()
362            return
363
364        self.list_values.Clear()
365
366        sql = "SELECT DISTINCT {column} FROM {table} ORDER BY {column}".format(
367            column=column, table=self.tablename)
368        if justsample:
369            sql += " LIMIT {}".format(255)
370        data = grass.db_select(
371            sql=sql,
372            database=self.database,
373            driver=self.driver,
374            sep='{_sep_}')
375        if not data:
376            return
377
378        desc = self.dbInfo.GetTableDesc(
379            self.dbInfo.GetTable(self.layer))[column]
380
381        i = 0
382        items = []
383        for item in data: #sorted(set(map(lambda x: desc['ctype'](x[0]), data))):
384            if desc['type'] not in ('character', 'text'):
385                items.append(str(item[0]))
386            else:
387                items.append(u"'{}'".format(GetUnicodeValue(item[0])))
388            i += 1
389
390        self.list_values.AppendItems(items)
391
392    def OnSampleValues(self, event):
393        """Get sample values"""
394        self.OnUniqueValues(None, True)
395
396    def OnAddColumn(self, event):
397        """Add column name to the query"""
398        idx = self.list_columns.GetSelections()
399        for i in idx:
400            column = self.list_columns.GetString(i)
401            self._add(element='column', value=column)
402
403        if not self.btn_uniquesample.IsEnabled():
404            self.btn_uniquesample.Enable(True)
405            self.btn_unique.Enable(True)
406
407    def OnAddValue(self, event):
408        """Add value"""
409        selection = self.list_values.GetSelections()
410        if not selection:
411            event.Skip()
412            return
413
414        idx = selection[0]
415        value = self.list_values.GetString(idx)
416        idx = self.list_columns.GetSelections()[0]
417        column = self.list_columns.GetString(idx)
418
419        ctype = self.dbInfo.GetTableDesc(
420            self.dbInfo.GetTable(
421                self.layer))[column]['type']
422
423        self._add(element='value', value=value)
424
425    def OnGoTo(self, event):
426        # clear all previous selections
427        for item in self.list_values.GetSelections():
428            self.list_values.Deselect(item)
429
430        gotoText = event.GetString()
431        lenLimit = len(gotoText)
432        found = idx = 0
433        string = False
434        for item in self.list_values.GetItems():
435            if idx == 0 and item.startswith("'"):
436                string = True
437            if string:
438                item = item[1:-1] # strip "'"
439            if item[:lenLimit] == gotoText:
440                found = idx
441                break
442            idx += 1
443
444        if found > 0:
445            self.list_values.SetSelection(found)
446
447    def OnAddMark(self, event):
448        """Add mark"""
449        mark = None
450        if self.btn_logicpanel and \
451           self.btn_logicpanel.IsShown():
452            btns = self.btn_logic
453        elif self.btn_arithmeticpanel and \
454                self.btn_arithmeticpanel.IsShown():
455            btns = self.btn_arithmetic
456
457        for key, value in six.iteritems(btns):
458            if event.GetId() == value[1]:
459                mark = value[0]
460                break
461
462        self._add(element='mark', value=mark)
463
464    def GetSQLStatement(self):
465        """Return SQL statement"""
466        return self.text_sql.GetValue().strip().replace("\n", " ")
467
468    def OnClose(self, event):
469        self.Destroy()
470        event.Skip()
471
472
473class SQLBuilderSelect(SQLBuilder):
474    """Class for building SELECT SQL statement"""
475
476    def __init__(self, parent, vectmap, id=wx.ID_ANY,
477                 layer=1, evtHandler=None):
478
479        self.evtHandler = evtHandler
480
481        # set dialog title
482        title = _("GRASS SQL Builder (%(type)s) - <%(map)s>") % \
483            {'type': "SELECT", 'map': vectmap}
484
485        modeChoices = [_("Column to show (SELECT clause)"),
486                       _("Constraint for query (WHERE clause)")]
487
488        SQLBuilder.__init__(self, parent, title, vectmap, id=wx.ID_ANY,
489                            modeChoices=modeChoices, layer=layer)
490
491    def _doLayout(self, modeChoices):
492        """Do dialog layout"""
493
494        SQLBuilder._doLayout(self, modeChoices)
495
496        self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
497        self.text_sql.SetToolTip(
498            _("Example: %s") %
499            "SELECT * FROM roadsmajor WHERE MULTILANE = 'no' OR OBJECTID < 10")
500
501        self.btn_verify = Button(parent=self.panel, id=wx.ID_ANY,
502                                    label=_("Verify"))
503        self.btn_verify.SetToolTip(_("Verify SQL statement"))
504
505        self.buttonsizer.Insert(1, self.btn_verify)
506
507        self.text_sql.Bind(wx.EVT_TEXT, self.OnText)
508        self.btn_verify.Bind(wx.EVT_BUTTON, self.OnVerify)
509
510        self.text_sql.SetInsertionPoint(self.text_sql.GetLastPosition())
511        self.statusbar.SetStatusText(_("SQL statement not verified"), 0)
512
513    def OnApply(self, event):
514        """Apply button pressed"""
515        if self.evtHandler:
516            self.evtHandler(event='apply')
517
518        if self.close_onapply.IsChecked():
519            self.Destroy()
520
521        event.Skip()
522
523    def OnClear(self, event):
524        """Clear button pressed"""
525        self.text_sql.SetValue("SELECT * FROM %s" % self.tablename)
526
527    def OnMode(self, event):
528        """Adjusts builder for chosen mode"""
529        if self.mode.GetSelection() == 0:
530            self.valuespanel.Hide()
531            self.btn_logicpanel.Hide()
532        elif self.mode.GetSelection() == 1:
533            self.valuespanel.Show()
534            self.btn_logicpanel.Show()
535        self.pagesizer.Layout()
536
537    def OnText(self, event):
538        """Query string changed"""
539        if len(self.text_sql.GetValue()) > 0:
540            self.btn_verify.Enable(True)
541        else:
542            self.btn_verify.Enable(False)
543
544    def OnVerify(self, event):
545        """Verify button pressed"""
546        ret, msg = RunCommand('db.select',
547                              getErrorMsg=True,
548                              table=self.tablename,
549                              sql=self.text_sql.GetValue(),
550                              flags='t',
551                              driver=self.driver,
552                              database=self.database)
553
554        if ret != 0 and msg:
555            self.statusbar.SetStatusText(_("SQL statement is not valid"), 0)
556            GError(parent=self,
557                   message=_("SQL statement is not valid.\n\n%s") % msg)
558        else:
559            self.statusbar.SetStatusText(_("SQL statement is valid"), 0)
560
561    def _add(self, element, value):
562        """Add element to the query
563
564        :param element: element to add (column, value)
565        """
566        sqlstr = self.text_sql.GetValue()
567        curspos = self.text_sql.GetInsertionPoint()
568        newsqlstr = ''
569        if element == 'column':
570            if self.mode.GetSelection() == 0:  # -> column
571                idx1 = len('select')
572                idx2 = sqlstr.lower().find('from')
573                colstr = sqlstr[idx1:idx2].strip()
574                if colstr == '*':
575                    cols = []
576                else:
577                    cols = colstr.split(',')
578                if value in cols:
579                    cols.remove(value)
580                else:
581                    cols.append(value)
582
583                if len(cols) < 1:
584                    cols = ['*', ]
585                newsqlstr = 'SELECT ' + ','.join(cols) + ' '
586                curspos = len(newsqlstr)
587                newsqlstr += sqlstr[idx2:]
588            else:  # -> where
589                newsqlstr = ''
590                if sqlstr.lower().find('where') < 0:
591                    newsqlstr += ' WHERE'
592                newsqlstr += ' ' + value
593                curspos = self.text_sql.GetLastPosition() + len(newsqlstr)
594                newsqlstr = sqlstr + newsqlstr
595
596        elif element in ['value', 'mark']:
597            addstr = ' ' + value + ' '
598            newsqlstr = sqlstr[:curspos] + addstr + sqlstr[curspos:]
599            curspos += len(addstr)
600
601        if newsqlstr:
602            self.text_sql.SetValue(newsqlstr)
603
604        wx.CallAfter(self.text_sql.SetFocus)
605        self.text_sql.SetInsertionPoint(curspos)
606
607    def CloseOnApply(self):
608        """Return True if the dialog will be close on apply"""
609        return self.close_onapply.IsChecked()
610
611    def OnClose(self, event):
612        """Close button pressed"""
613        if self.evtHandler:
614            self.evtHandler(event='close')
615
616        SQLBuilder.OnClose(self, event)
617
618
619class SQLBuilderUpdate(SQLBuilder):
620    """Class for building UPDATE SQL statement"""
621
622    def __init__(self, parent, vectmap, id=wx.ID_ANY,
623                 layer=1, column=None):
624
625        self.column = column
626        # set dialog title
627        title = _("GRASS SQL Builder (%(type)s) - <%(map)s>") % \
628            {'type': "UPDATE", 'map': vectmap}
629
630        modeChoices = [_("Column to set (SET clause)"),
631                       _("Constraint for query (WHERE clause)"),
632                       _("Calculate column value to set")]
633
634        SQLBuilder.__init__(self, parent, title, vectmap, id=wx.ID_ANY,
635                            modeChoices=modeChoices, layer=layer)
636
637        # signals
638        self.sqlApplied = Signal("SQLBuilder.sqlApplied")
639        if parent:  # TODO: replace by giface
640            self.sqlApplied.connect(parent.Update)
641
642    def _doLayout(self, modeChoices):
643        """Do dialog layout"""
644
645        SQLBuilder._doLayout(self, modeChoices)
646
647        self.initText = "UPDATE %s SET" % self.tablename
648        if self.column:
649            self.initText += " %s = " % self.column
650
651        self.text_sql.SetValue(self.initText)
652
653        self.btn_arithmetic = {'eq': ['=', ],
654                               'brac': ['()', ],
655                               'plus': ['+', ],
656                               'minus': ['-', ],
657                               'divide': ['/', ],
658                               'multiply': ['*', ]}
659
660        self.btn_arithmeticpanel = wx.Panel(parent=self.panel, id=wx.ID_ANY)
661
662        for key, value in six.iteritems(self.btn_arithmetic):
663            btn = Button(parent=self.btn_arithmeticpanel, id=wx.ID_ANY,
664                            label=value[0])
665            self.btn_arithmetic[key].append(btn.GetId())
666
667        btn_arithmeticsizer = wx.GridBagSizer(hgap=5, vgap=5)
668
669        btn_arithmeticsizer.Add(
670            self.FindWindowById(
671                self.btn_arithmetic['eq'][1]), pos=(
672                0, 0))
673        btn_arithmeticsizer.Add(
674            self.FindWindowById(
675                self.btn_arithmetic['brac'][1]), pos=(
676                1, 0))
677
678        btn_arithmeticsizer.Add(
679            self.FindWindowById(
680                self.btn_arithmetic['plus'][1]), pos=(
681                0, 1))
682        btn_arithmeticsizer.Add(
683            self.FindWindowById(
684                self.btn_arithmetic['minus'][1]), pos=(
685                1, 1))
686
687        btn_arithmeticsizer.Add(
688            self.FindWindowById(
689                self.btn_arithmetic['divide'][1]), pos=(
690                0, 2))
691        btn_arithmeticsizer.Add(
692            self.FindWindowById(
693                self.btn_arithmetic['multiply'][1]), pos=(
694                1, 2))
695
696        self.btn_arithmeticpanel.SetSizer(btn_arithmeticsizer)
697
698        self.pagesizer.Insert(3, self.btn_arithmeticpanel,
699                              proportion=0, flag=wx.ALIGN_CENTER_HORIZONTAL)
700
701        self.funcpanel = wx.Panel(parent=self.panel, id=wx.ID_ANY)
702        self._initSqlFunctions()
703        funcsbox = StaticBox(parent=self.funcpanel, id=wx.ID_ANY,
704                             label=" %s " % _("Functions"))
705        funcsizer = wx.StaticBoxSizer(funcsbox, wx.VERTICAL)
706        self.list_func = wx.ListBox(parent=self.funcpanel, id=wx.ID_ANY,
707                                    choices=list(self.sqlFuncs['sqlite'].keys()),
708                                    style=wx.LB_SORT)
709
710        funcsizer.Add(self.list_func, proportion=1,
711                      flag=wx.EXPAND)
712
713        self.funcpanel.SetSizer(funcsizer)
714
715        self.hsizer.Insert(2, self.funcpanel,
716                           proportion=1, flag=wx.EXPAND)
717
718        self.list_func.Bind(wx.EVT_LISTBOX, self.OnAddFunc)
719        for key, value in six.iteritems(self.btn_arithmetic):
720            self.FindWindowById(value[1]).Bind(wx.EVT_BUTTON, self.OnAddMark)
721        self.mode.SetSelection(0)
722        self.OnMode(None)
723        self.text_sql.SetInsertionPoint(self.text_sql.GetLastPosition())
724
725    def OnApply(self, event):
726        """Apply button pressed"""
727
728        ret, msg = RunCommand('db.execute',
729                              getErrorMsg=True,
730                              parent=self,
731                              stdin=self.text_sql.GetValue(),
732                              input='-',
733                              driver=self.driver,
734                              database=self.database)
735
736        if ret != 0 and msg:
737            self.statusbar.SetStatusText(_("SQL statement was not applied"), 0)
738        else:
739            self.statusbar.SetStatusText(_("SQL statement applied"), 0)
740
741        self.sqlApplied.emit()
742
743    def OnClear(self, event):
744        """Clear button pressed"""
745        self.text_sql.SetValue(self.initText)
746
747    def OnMode(self, event):
748        """Adjusts builder for chosen mode"""
749        if self.mode.GetSelection() == 0:
750            self.valuespanel.Hide()
751            self.btn_logicpanel.Hide()
752            self.btn_arithmeticpanel.Hide()
753            self.funcpanel.Hide()
754        elif self.mode.GetSelection() == 1:
755            self.valuespanel.Show()
756            self.btn_logicpanel.Show()
757            self.btn_arithmeticpanel.Hide()
758            self.funcpanel.Hide()
759        elif self.mode.GetSelection() == 2:
760            self.valuespanel.Hide()
761            self.btn_logicpanel.Hide()
762            self.btn_arithmeticpanel.Show()
763            self.funcpanel.Show()
764        self.pagesizer.Layout()
765
766    def OnAddFunc(self, event):
767        """Add function to the query"""
768
769        if self.driver == 'dbf':
770            GMessage(
771                parent=self,
772                message=_(
773                    "Dbf driver does not support usage of SQL functions."))
774            return
775
776        idx = self.list_func.GetSelections()
777        for i in idx:
778            func = self.sqlFuncs['sqlite'][self.list_func.GetString(i)][0]
779            self._add(element='func', value=func)
780
781    def _add(self, element, value):
782        """Add element to the query
783
784        :param element: element to add (column, value)
785        """
786        sqlstr = self.text_sql.GetValue()
787        curspos = self.text_sql.GetInsertionPoint()
788        newsqlstr = ''
789
790        if element in  ['value', 'mark', 'func'] or \
791                (element == 'column' and self.mode.GetSelection() == 2):
792            addstr = ' ' + value + ' '
793            newsqlstr = sqlstr[:curspos] + addstr + sqlstr[curspos:]
794            curspos += len(addstr)
795        elif element == 'column':
796            if self.mode.GetSelection() == 0:  # -> column
797                idx1 = sqlstr.lower().find('set') + len('set')
798                idx2 = sqlstr.lower().find('where')
799
800                if idx2 >= 0:
801                    colstr = sqlstr[idx1:idx2].strip()
802                else:
803                    colstr = sqlstr[idx1:].strip()
804
805                cols = [col.split('=')[0].strip() for col in colstr.split(',')]
806                if value in cols:
807                    self.text_sql.SetInsertionPoint(curspos)
808                    wx.CallAfter(self.text_sql.SetFocus)
809                    return
810                if colstr:
811                    colstr += ','
812                colstr = ' ' + colstr
813                colstr += ' ' + value + '= '
814                newsqlstr = sqlstr[:idx1] + colstr
815                if idx2 >= 0:
816                    newsqlstr += sqlstr[idx2:]
817                curspos = idx1 + len(colstr)
818
819            elif self.mode.GetSelection() == 1:  # -> where
820                newsqlstr = ''
821                if sqlstr.lower().find('where') < 0:
822                    newsqlstr += ' WHERE'
823                newsqlstr += ' ' + value
824                curspos = self.text_sql.GetLastPosition() + len(newsqlstr)
825                newsqlstr = sqlstr + newsqlstr
826
827        if newsqlstr:
828            self.text_sql.SetValue(newsqlstr)
829
830        wx.CallAfter(self.text_sql.SetFocus)
831        self.text_sql.SetInsertionPoint(curspos)
832
833    def _initSqlFunctions(self):
834
835        self.sqlFuncs = {}
836        # TODO add functions for other drivers
837        self.sqlFuncs['sqlite'] = {
838            'ABS': ['ABS()'],
839            'LENGTH': ['LENGTH()'],
840            'LOWER': ['LOWER()'],
841            'LTRIM': ['LTRIM(,)'],
842            'MAX': ['MAX()'],
843            'MIN': ['MIN()'],
844            'RTRIM': ['RTRIM(,)'],
845            'SUBSTR': ['SUBSTR (,[,])'],
846            'TRIM': ['TRIM (,)']
847        }
848
849class SQLBuilderWhere(SQLBuilder):
850    """Class for building SELECT SQL WHERE statement"""
851
852    def __init__(self, parent, vectmap, id=wx.ID_ANY,
853                 layer=1):
854
855        title = _("GRASS SQL Builder (%(type)s) - <%(map)s>") % \
856                {'type': "WHERE", 'map': vectmap}
857
858        super(SQLBuilderWhere, self).__init__(
859            parent, title, vectmap, id=wx.ID_ANY,
860            layer=layer)
861
862    def OnClear(self, event):
863        self.text_sql.SetValue('')
864
865    def OnApply(self, event):
866        self.parent.SetValue(self.text_sql.GetValue())
867
868        if self.close_onapply.IsChecked():
869            self.Destroy()
870
871        event.Skip()
872
873    def _add(self, element, value):
874        """Add element to the query
875
876        :param element: element to add (column, value)
877        """
878        sqlstr = self.text_sql.GetValue()
879        inspoint = self.text_sql.GetInsertionPoint()
880
881        newsqlstr = ''
882        if inspoint > 0 and sqlstr[inspoint-1] != ' ':
883            newsqlstr += ' '
884        newsqlstr += value
885        if inspoint < len(sqlstr):
886            newsqlstr += ' ' if sqlstr[inspoint] != ' ' else ''
887
888        if newsqlstr:
889            self.text_sql.SetValue(sqlstr[:inspoint] + newsqlstr + sqlstr[inspoint:])
890            self.text_sql.SetInsertionPoint(inspoint + len(newsqlstr))
891
892        wx.CallAfter(self.text_sql.SetFocus)
893
894
895if __name__ == "__main__":
896    if len(sys.argv) not in [3, 4]:
897        print(__doc__, file=sys.stderr)
898        sys.exit()
899
900    if len(sys.argv) == 3:
901        layer = 1
902    else:
903        layer = int(sys.argv[3])
904
905    if sys.argv[1] == 'select':
906        sqlBuilder = SQLBuilderSelect
907    elif sys.argv[1] == 'update':
908        sqlBuilder = SQLBuilderUpdate
909    else:
910        print(__doc__, file=sys.stderr)
911        sys.exit()
912
913    app = wx.App(0)
914    sqlb = sqlBuilder(parent=None, vectmap=sys.argv[2], layer=layer)
915    sqlb.Show()
916
917    app.MainLoop()
918