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