pysql.pysqlshell

1 #!/usr/bin/python 2 # -*- coding: utf-8 -*- 3 4 """User interaction with pysql 5 @author: Sébastien Renard (sebastien.renard@digitalfox.org) 6 @author: Sébastien Delcros (Sebastien.Delcros@gmail.com) 7 @license: GNU GPL V3 8 """ 9 10 # Python imports: 11 import cmd 12 import sys 13 import os 14 import readline 15 from re import findall, match, sub 16 from os.path import expandvars 17 from time import sleep, time 18 from getpass import getpass 19 import csv 20 21 # Pysql imports: 22 from pysqldb import PysqlDb, BgQuery 23 import pysqlfunctions 24 import pysqlgraphics 25 import pysqlaudit 26 from pysqlexception import PysqlException, PysqlNotImplemented, PysqlOptionParserNormalExitException 27 from pysqlconf import PysqlConf 28 from pysqlcolor import BOLD, CYAN, GREEN, GREY, RED, RESET 29 from pysqlhelpers import itemLength, removeComment, printStackTrace, setTitle, getTitle, getTermWidth, WaitCursor 30 from pysqloptionparser import PysqlOptionParser 31

32 -class PysqlShell(cmd.Cmd):

33 """Main class that handle user interaction""" 34

35 - def __init__(self, completekey='tab', stdin=None, stdout=None, silent=False, argv=[]):

36 """Shell initialisation""" 37 38 # Instance attributes 39 self.db = None # Db connection object 40 self.fetching = False # Indicate if a request is running 41 self.multilineCmd = False # Indicate if the user is in a multiline command 42 self.plBloc = False # Indicate if the user is in a PL/SQL bloc 43 self.comment = False # Indicate if the user is in an SQL multiline comment 44 self.cmdBuffer = [] # Command buffer for multiline command (list of line) 45 self.lastStatement = "" # Last statement executed 46 self.tnsnamesAvailable = None # possible to read tnsnames.ora for completion? 47 self.conf = None # Handle to pysql configuration instance 48 self.cmds = [] # List of defined cmds 49 self.bgQueries = [] # List of bg queries threads 50 self.exceptions = [] # List of PysqlException encountered 51 self.useCompletion = True # Indicate if we should use completion with "tab" 52 self.showBanner = not silent # Indicate if intro banner should be displayed 53 self.showPrompt = not silent # Indicate if prompt should be displayed 54 self.trace = {} # Store session trace statistics between two call to trace command 55 self.rc = 0 # Shell exit code 56 self.oldTermName = "" # Old terminal name 57 self.waitCursor = None # Waiting cursor thread handler 58 self.tty = sys.stdin.isatty() # Indicate if user interactivity is possible or not. 59 self.allowAnimatedCursor = True # Enable or not animated cursor. Useful for test. 60 self.notConnectedPrompt = RED + _("(not connected) ") + RESET 61 62 # Reads conf 63 self.conf = PysqlConf.getConfig() 64 65 # Are we in tty (user interaction) or not (script, pipe...) ? 66 # If not, doesn't use completion in script neither cursor animation 67 if not self.tty: 68 self.useCompletion = False 69 self.allowAnimatedCursor = False 70 71 # Calls father constructor 72 cmd.Cmd.__init__(self, "tab", stdin, stdout) 73 74 # setup readline completion delimiter - remove dash (-) as separator 75 readline.set_completer_delims(readline.get_completer_delims().replace("-", "")) 76 77 # Keep old term name 78 self.oldTermName = getTitle() 79 80 if len(argv) == 0: 81 # No argv given. 82 self.__setPrompt() 83 else: 84 # connectString was given as argument, connects to Oracle 85 try: 86 self.do_connect(" ".join(argv)) 87 except PysqlException, e: 88 # Connection failed, starts not connected and warns user 89 print RED + BOLD + _("\nConnection failed:\n\t %s") % e + RESET 90 self.exceptions.append(e) 91 self.db = None 92 self.__setPrompt() 93 except KeyboardInterrupt: 94 print RED + BOLD + _("Break !") + RESET 95 #TODO: validates this case (direct call of __exit() is not good) 96 self.__exit()

97

98 - def preloop(self):

99 """Prepares shell interactive loop""" 100 # Builds the list of commands 101 self.cmds = [i[3:] for i in self.get_names() if i.startswith("do_")] 102 self.cmds.remove("explain") # explain command use multine 103 self.cmds.remove("csv") # so does csv 104 if self.showBanner: 105 banner = _("\nWelcome to pysql shell\n") 106 banner += _("""Type "help" for some help.\nUse Tab for completion\n""") 107 print banner

108

109 - def loop(self):

110 """Starts shell interactive loop""" 111 try: 112 self.cmdloop() 113 except KeyboardInterrupt: 114 # Does not work when a connection is made to Oracle 115 # Question asked to cx_Oracle developer. Waiting for answer. 116 print RED + BOLD + _("Break !") + RESET 117 self.showBanner = False 118 self.loop()

119

120 - def postloop(self):

121 """End of command loop""" 122 # Restore original terminal title 123 setTitle(self.oldTermName, self.conf.getCodec())

124

125 - def emptyline(self):

126 """Fetches next result if a request is running 127 or do nothning""" 128 if self.fetching: 129 try: 130 self.__fetchNext() 131 except PysqlException, e: 132 self.fetching = False 133 raise PysqlException(e)

134

135 - def onecmd(self, line):

136 """This method is subclassed just to be 137 able to encapsulate it with a try/except bloc""" 138 try: 139 return cmd.Cmd.onecmd(self, line) 140 except PysqlOptionParserNormalExitException: 141 # Do nothing, we are just catching parser exit function when help is called 142 pass 143 except PysqlException, e: 144 print RED + BOLD + "*** " + _("Pysql error") + " ***\n\t%s" % e + RESET 145 self.exceptions.append(e) 146 if e.oraCode == "ORA-03114": # Not connected to Oracle 147 self.db = None 148 except KeyboardInterrupt: 149 print RED + BOLD + _("Break !") + RESET 150 except StandardError, e: 151 # Just a hook for a more pleasant error handling 152 print RED + BOLD + _("\n==> Unhandled error. Sorry <==") + RESET 153 printStackTrace()

154

155 - def precmd(self, line):

156 """Hook executed just before any command execution. 157 This is used to parse command and dispatch it to Oracle or internal pysql functions""" 158 159 # Decode line from user encoding to unicode 160 line = line.decode(self.conf.getCodec()) 161 162 if self.conf.get("echo") == "yes": 163 # Echo line to stdout 164 print line 165 166 line, self.comment = removeComment(line, self.comment) 167 168 # Removes leading and trailing whitespace 169 line = line.strip() 170 171 # Does nothing for blank line or EOF 172 if len(line) == 0 or line == "EOF": 173 return line 174 175 # The @ is a shortcut to the script command 176 if line[0] == "@": 177 return "script " + line[1:] 178 179 firstWord = line.split()[0] 180 181 # Substitute alias with real function name 182 if self.aliases.has_key(firstWord): 183 line = line.replace(firstWord, self.aliases[firstWord], 1) 184 firstWord = self.aliases[firstWord] 185 186 # Pysql command are single line 187 if (firstWord in self.cmds or line[0] == "!") and not self.multilineCmd: 188 # ; is not needed but we remove it if exists 189 if firstWord != "set": 190 # Don't strip for set command else we cannot use a parameter with a ; at the end ! 191 line = line.rstrip(";") 192 return line 193 194 if firstWord.lower() in ("declare", "begin"): 195 # PL/SQL Bloc detected 196 self.plBloc = True 197 elif firstWord == "/" and not self.plBloc and not self.multilineCmd: 198 # Repeats the last statement 199 if self.lastStatement: 200 return self.lastStatement.rstrip(";") 201 else: 202 return "" 203 204 if (line[-1] in [";", "&", "/"] and not self.plBloc) or (line[-1] == "/" and self.plBloc): 205 # End of command line detected 206 # Removes trailing / and ; 207 line = line.rstrip(";") 208 line = line.rstrip("/") 209 self.cmdBuffer.append(line) 210 line = " ".join(self.cmdBuffer) 211 self.cmdBuffer = [] 212 self.__setPrompt() # back to std prompt 213 if self.multilineCmd: 214 # Puts the whole command line into history 215 try: 216 length = readline.get_current_history_length() 217 if length > 1: 218 readline.replace_history_item(length - 1, line) # put the complete cmd in history 219 except AttributeError: 220 # Windows readline does not have those advanced functions... Sad world 221 pass 222 self.multilineCmd = False 223 self.plBloc = False 224 else: 225 # Checks sql given is not too dumb (check only the begining !) 226 if not self.multilineCmd: 227 result = match("(.+?)\s.+", line) 228 if result: 229 firstWord = result.group(1) 230 else: 231 firstWord = line 232 if not self.multilineCmd \ 233 and firstWord.lower() not in ("select", "insert", "update", "delete", 234 "alter", "truncate", "drop", "begin", 235 "declare", "comment", "create", "grant", 236 "revoke", "analyze", "explain", "csv"): 237 print RED + BOLD + _("""Unknown command or sql order. Type "help" for help""") + RESET 238 else: 239 # Bufferise the command and wait for the rest 240 self.multilineCmd = True 241 self.cmdBuffer.append(line) 242 self.fetching = False # Cancel previous fetching if any 243 self.__setPrompt(multiline=True) 244 try: 245 length = readline.get_current_history_length() 246 if length > 1: 247 # Removes partial line from history 248 readline.remove_history_item(length - 1) 249 except AttributeError: 250 # Windows readline does not have those advanced functions... Sad world 251 pass 252 # In both case, do nothing, so the line is set to blank 253 line = "" 254 return line

255

256 - def postcmd(self, stop, line):

257 """Hook executed just after command processing. 258 Used to notify running and finished background queries 259 @return: stop flag to end loop""" 260 if self.waitCursor: 261 self.waitCursor.stop() # Stop any running cursor 262 self.waitCursor = None 263 if self.multilineCmd: 264 self.__setPrompt(multiline=True) 265 else: 266 queries = [i for i in self.bgQueries if not i.isAlive()] 267 if len(queries) != 0: 268 self.__setPrompt(finishedQuery=True) 269 else: 270 self.__setPrompt() 271 return stop

272

273 - def default(self, arg):

274 """Default method if no command is recognized. 275 We assume it is a pure SQL request""" 276 if arg == "EOF": 277 return self.__exit() 278 else: 279 if self.tty: 280 self.__executeSQL(arg, output="tty") 281 else: 282 self.__executeSQL(arg, output="notty")

283

284 - def do_help(self, arg):

285 """ 286 Overload do_help to show help from the command parser if it exists: 287 if there is a parser_foo() method, assume this method returns a 288 PysqlOptionParser for the do_foo() method and show the help of the 289 parser, instead of standard help (do_foo() docstring or help_foo()) 290 """ 291 if self.aliases.has_key(arg): 292 arg = self.aliases[arg] 293 if hasattr(self, "parser_" + arg): 294 parserMethod = getattr(self, "parser_" + arg) 295 parserMethod().print_help(sys.stderr) 296 else: 297 cmd.Cmd.do_help(self, arg)

298

299 - def completenames(self, text, *ignored):

300 """Complete commands names. Same as Cmd.cmd one but with support 301 for command aliases""" 302 dotext = 'do_' + text 303 names = [a[3:] for a in self.get_names() if a.startswith(dotext)] 304 names.extend([a for a in self.aliases.keys() if a.startswith(text)]) 305 return names

306

307 - def completedefault(self, text, line, begidx, endidx):

308 """pysql specific completion with self.completeList""" 309 if not self.useCompletion: 310 return 311 # Keyword detection not very smart... but work for simple case 312 lastKeyWord = line[:begidx].split()[-1].lower() 313 if lastKeyWord in ["select", "where", "by", 314 "sum(", "abs(", "round(", "upper(", "lower(", "set"]: 315 themes = ["columns"] 316 elif lastKeyWord in ["update"]: 317 themes = ["table"] 318 elif lastKeyWord in ["from", "into"]: 319 themes = ["table", "view", "synonym"] 320 elif lastKeyWord == "index": 321 themes = ["index"] 322 elif lastKeyWord == "table": 323 themes = ["table"] 324 elif lastKeyWord == "sequence": 325 themes = ["sequence"] 326 elif lastKeyWord == "directory": 327 themes = ["directory"] 328 elif lastKeyWord == "index": 329 themes = ["trigger"] 330 elif lastKeyWord == "view": 331 themes = ["view"] 332 elif lastKeyWord == "user": 333 themes = ["user"] 334 else: 335 themes = ["table", "view", "synonym", "columns", 336 "directory", "sequence", "user"] 337 # Separates text from his prefix 338 if text.count(".") == 1: 339 prefix, text = text.split(".") 340 prefix += "." # Doesn't forget the dot for the prefix 341 else: 342 prefix = "" 343 return self.__getCompletionItems(text, themes, prefix)

344

345 - def complete_connect(self, text, line, begidx, endidx):

346 """Completion of SID for connect method""" 347 if not self.useCompletion: 348 return 349 # Completes only on SID (after the @) 350 if line.count("@"): 351 sid = line.split("@")[-1] 352 else: 353 # No @, cannot complete. 354 return [] 355 356 if self.tnsnamesAvailable is None: 357 # First try to open tnsnames.ora 358 try: 359 tnsnames = file(expandvars("$ORACLE_HOME/network/admin/tnsnames.ora")).readlines() 360 self.conf.completeLists["SID"] = sum([findall("^(\w+)\s*=", line) for line in tnsnames], []) 361 self.tnsnamesAvailable = True 362 except Exception, e: 363 # Do not raise a PysqlException (useless) 364 print RED + BOLD + _("Cannot open tnsnames.ora file (%s)") % e + RESET 365 self.tnsnamesAvailable = False 366 if self.tnsnamesAvailable: 367 return self.__getCompletionItems(sid, ["SID"]) 368 else: 369 return []

370

371 - def complete_desc(self, text, line, begidx, endidx):

372 """Completion for command desc""" 373 if not self.useCompletion: 374 return 375 return self.__getCompletionItems(text, ["table", "view", "index", 376 "synonym", "sequence", 377 "tablespace", "datafile" 378 "directory"])

379

380 - def complete_edit(self, text, line, begidx, endidx):

381 """Completion for command edit""" 382 if not self.useCompletion: 383 return 384 return self.__getCompletionItems(text, ["view", "package"])

385

386 - def complete_get(self, text, line, begidx, endidx):

387 """Completion for command get""" 388 if not self.useCompletion: 389 return 390 return self.__getCompletionItems(text, ["parameters"])

391

392 - def complete_set(self, text, line, begidx, endidx):

393 """Completion for command set""" 394 if not self.useCompletion: 395 return 396 return self.__getCompletionItems(text, ["parameters"])

397

398 - def complete_library(self, text, line, begidx, endidx):

399 """Completion for library command""" 400 return [k for k in self.conf.sqlLibrary.keys() if k.startswith(text)]

401 402 # Command line definitions 403 # Connection stuff

404 - def do_connect(self, arg):

405 """Connect to instance""" 406 self.__checkArg(arg, ">=1") 407 arg = arg.split() 408 409 try: 410 self.__disconnect() 411 except PysqlException, e: 412 print RED + BOLD + _("Error while closing previous connection:\n\t %s") % e + RESET 413 self.exceptions.append(e) 414 self.db = None 415 if len(arg) == 1: 416 mode = "" 417 elif arg[1].lower() == "sysdba" or (len(arg) > 2 and " ".join(arg[1:3]).lower() == "as sysdba"): 418 mode = "sysdba" 419 elif arg[1].lower() == "sysoper" or (len(arg) > 2 and " ".join(arg[1:3]).lower() == "as sysoper"): 420 mode = "sysoper" 421 else: 422 mode = "" 423 print RED + BOLD + _("Invalid Oracle mode: %s (ignored)") % (" ".join(arg[1:])) + RESET 424 self.__connect(arg[0], mode)

425

426 - def do_disconnect(self, arg):

427 """Disconnect from instance""" 428 try: 429 self.__disconnect() 430 except PysqlException, e: 431 self.db = None 432 # Proxy the exception for standard handling 433 raise PysqlException(e)

434 435 # Completion, history and sql library

436 - def do_showCompletion(self, arg):

437 """Shows completion list""" 438 for theme in self.conf.completeLists.keys(): 439 print GREEN + "***** " + theme + " *****" + RESET 440 self.__displayCol(self.conf.completeLists[theme]) 441 print

442

443 - def do_history(self, arg):

444 """Display shell history""" 445 self.__checkArg(arg, "<=1") 446 #TODO: move depth to pysqlConfig 447 #BUG: if history is short stupid things are printed ! 448 depth = 20 # Number of history item to be displayed 449 try: 450 length = readline.get_current_history_length() # Length of current history in readline buffer 451 except AttributeError: 452 message = _("History not available on Windows platform (readline limitation)") 453 raise PysqlException(message) 454 arg = arg.split() 455 if len(arg) == 0: 456 # Display history 457 for i in xrange(depth): 458 position = length - depth + i 459 # Data is already encoded 460 print "%d: %s " % (position, readline.get_history_item(position)) 461 elif len(arg) == 1: 462 # Executes the nth command 463 try: 464 position = int(arg[0]) 465 except ValueError: 466 raise PysqlException(_("Argument must be an integer")) 467 468 if position > length: 469 raise PysqlException(_("This is the future ! Cannot execute. Sorry")) 470 elif position <= 0: 471 raise PysqlException(_("Argument should be stricly positive")) 472 else: 473 command = readline.get_history_item(position) 474 command = self.precmd(command) 475 print command 476 self.onecmd(command) 477 else: 478 raise PysqlException(_("See help history for usage"))

479

480 - def do_library(self, arg):

481 """Manage user sql request library""" 482 nArgs = len(arg.split()) 483 if nArgs == 0: 484 # Shows all sql library 485 self.__displayTab(self.conf.sqlLibrary.items(), (_("Name"), _("SQL request"))) 486 elif nArgs == 1: 487 # Recalls a request 488 if self.conf.sqlLibrary.has_key(arg): 489 readline.add_history(self.conf.sqlLibrary[arg]) 490 print GREEN + \ 491 _("SQL request was loaded in your history. Use up arrow to get it now") \ 492 + RESET 493 else: 494 msg = _("Request %s does not exist. ") % name + \ 495 _("""Type "lib" without argument to see all requests""") 496 raise PysqlException(msg) 497 elif nArgs > 1: 498 # First argument is name and second argument can be sql or keyword remove 499 name = arg.split()[0] 500 text = " ".join(arg.split()[1:]) 501 if text == "remove": 502 if self.conf.sqlLibrary.has_key(name): 503 del self.conf.sqlLibrary[name] 504 print GREEN + _("Request has been removed") + RESET 505 else: 506 msg = _("Request %s does not exist. ") % name + \ 507 _("""Type "lib" without argument to see all requests""") 508 raise PysqlException(msg) 509 else: 510 # Add request 511 self.conf.sqlLibrary[name] = text 512 print GREEN + _("Request has been saved") + RESET

513 514 # background queries

515 - def do_bg(self, arg):

516 """Manages background queries""" 517 arg = arg.split() 518 if len(arg) == 0: 519 # Shows background queries 520 result = [(i.getName(), i.query, not i.isAlive(), i.error) for i in self.bgQueries] 521 self.__displayTab(result, [_("#"), _("SQL request"), _("Finished?"), _("Error")]) 522 elif len(arg) == 1: 523 # Finds the thread 524 bgQuery = [i for i in self.bgQueries if i.getName() == arg[0]] 525 if len(bgQuery) == 1: 526 bgQuery = bgQuery[0] 527 # Waits for query ending 528 self.__animateCursor() 529 bgQuery.join() 530 # Gets this query in foreground 531 if bgQuery.query.upper().split()[0].startswith("SELECT"): 532 self.db = bgQuery.db 533 self.__toScreen(bgQuery.result, bgQuery.moreRows) 534 else: 535 print GREEN + _("Statement executed") + RESET 536 # Removes bg query from list 537 self.bgQueries.remove(bgQuery) 538 else: 539 raise PysqlException(_("Unknown background query. Use bg without arg to see all queries")) 540 else: 541 raise PysqlException(_("See help bg for description"))

542 543 # Transactions stuff

544 - def do_commit(self, arg):

545 """Commits pending transaction""" 546 self.__checkConnection() 547 self.__animateCursor() 548 self.db.commit() 549 print GREEN + _("Commit completed") + RESET

550

551 - def do_rollback(self, arg):

552 """Rolls back pending transaction""" 553 self.__checkConnection() 554 self.__animateCursor() 555 self.db.rollback() 556 print GREEN + _("Rollback completed") + RESET

557 558 # Sysoper stuff

559 - def do_startup(self, arg):

560 """Starts database up (Oracle v10R2 or upper required)""" 561 self.__checkConnection() 562 self.__checkArg(arg, "<=1") 563 if arg == "mount": 564 self.db.startup("mount") 565 print GREEN + _("Database mounted") + RESET 566 else: 567 self.db.startup("normal") 568 print GREEN + _("Database opened") + RESET

569

570 - def do_shutdown(self, arg):

571 """Shuts database down (Oracle v10R2 or upper required)""" 572 self.__checkConnection() 573 self.__checkArg(arg, "<=1") 574 if arg == "abort": 575 self.db.shutdown("abort") 576 elif arg == "immediate": 577 self.db.shutdown("immediate") 578 else: 579 self.db.shutdown("normal") 580 print GREEN + _("Instance shut down") + RESET

581 582 # High level functions 583

584 - def do_count(self, arg):

585 """Counts segment lines""" 586 self.__checkConnection() 587 self.__checkArg(arg, "==1") 588 self.__animateCursor() 589 result = pysqlfunctions.count(self.db, arg) 590 print result

591

592 - def do_compare(self, arg):

593 """Compares schema or object structure and data""" 594 self.__checkArg(arg, ">=2") 595 schemaNames = [] # Password striped connection string to schema 596 tableNames = [] 597 schemas = [] # Complete connect string to schema 598 withData = False # Compares only structure (false) or data ? 599 600 arg = arg.split() 601 602 if arg[0] == "data": 603 withData = True 604 arg.pop(0) 605 elif arg[0] == "structure": 606 withData = False 607 arg.pop(0) 608 609 for item in arg: 610 result = match("(.+?)/(.+?)@(\w+):?(.*)", item) 611 if result: 612 # Schema given 613 schemaNames.append(result.group(1) + "@" + result.group(3)) 614 if result.group(4): 615 # Table name also given 616 tableNames.append(result.group(4)) 617 schemas.append(item.split(":")[0]) 618 else: 619 schemas.append(item) 620 else: 621 # Only tablename given 622 tableNames.append(item) 623 624 if not schemas: 625 # We assume schema is current schema 626 self.__checkConnection() 627 schemas = ["A", "B"] 628 # We create two new connexion to avoid cursor clash 629 dbList = { schemas[0] : PysqlDb(self.db.getConnectString()), 630 schemas[1] : PysqlDb(self.db.getConnectString()) } 631 else: 632 # Connection will be created later by compareTables(...) 633 dbList = None 634 635 if tableNames: 636 # We are just comparing two tables 637 if len(tableNames) != 2: 638 raise PysqlException(_("Cannot compare a table and a schema!")) 639 result = pysqlfunctions.compareTables(schemas[0], schemas[1], 640 tableNames[0], tableNames[1], 641 dbList, data=withData) 642 if result: 643 print CYAN + \ 644 """Table %s (marked with "-") differ from table %s (marked with "+")""" \ 645 % (tableNames[0], tableNames[1]) + RESET 646 print "\n".join(result) 647 else: 648 print _("Tables are identical") 649 else: 650 # We have to compare the whole schema 651 result = pysqlfunctions.compare(schemas[0], schemas[1]) 652 653 print GREEN + "**** " + _("Tables found in %s but not in %s ") + "****" \ 654 % (schemaNames[0], schemaNames[1]) + RESET 655 print ", ".join(result[0]) 656 print 657 658 print GREEN + "**** " + _("Tables found in %s but not in %s ") + "****" \ 659 % (schemaNames[1], schemaNames[0]) + RESET 660 print ", ".join(result[1]) 661 print 662 663 print GREEN + "**** " + _("Tables identical in both schema ") + "****" + RESET 664 print ", ".join([i[0] for i in result[2].items() if not i[1]]) 665 print 666 667 print GREEN + "**** " + _("Tables not identical in both schema") + "****" + RESET 668 for tableName, tableDiff in result[2].items(): 669 if tableDiff: 670 print CYAN + _("""Table %s differ from schema %s""") \ 671 % (tableName, schemaNames[0]), 672 print _("""(marked with "-") and schema %s (marked with "+")""") \ 673 % schemaNames[1] + RESET 674 print "\n".join(tableDiff) 675 print

676

677 - def parser_describe(self):

678 parser = PysqlOptionParser() 679 parser.set_usage(CYAN + "desc[ribe] " + _("[options] <object name>") + RESET) 680 parser.set_description(_("Describes any Oracle object")) 681 parser.add_option("-d", "--details", dest="printDetails", 682 default=False, action="store_true", 683 help=_("displays detailed information about the object")) 684 parser.add_option("-t", "--stats", dest="printStats", 685 default=False, action="store_true", 686 help=_("displays statistics on the object if any")) 687 parser.add_option("-s", "--sort", dest="sort", 688 default=False, action="store_true", 689 help=_("sorts column alphabetically instead of Oracle native order")) 690 return parser

691

692 - def do_describe(self, arg):

693 """Emulates the sqlplus desc function""" 694 parser = self.parser_describe() 695 options, args = parser.parse_args(arg) 696 self.__checkConnection() 697 self.__checkArg(args, ">=1") 698 # Gives method pointer to desc function to allow it to update completelist 699 (header, result) = pysqlfunctions.desc(self.db, " ".join(args), 700 completeMethod=self.__addToCompleteList, 701 printDetails=options.printDetails, 702 printStats=options.printStats, 703 sort=options.sort) 704 self.__displayTab(result, header)

705 706 # Audit functions

707 - def parser_addmrpt(self):

708 parser = PysqlOptionParser() 709 parser.set_usage(CYAN + "addm[rpt] " + _("[options]") + RESET) 710 parser.set_description(_("Generates tuning advice report based on AWR statistics. ") + 711 _("10g or upper is required. ") + 712 _("Before starting, please ensure that you have the required license to use it.")) 713 # Oracle only supports TEXT but HTML and XML will be added in next releases 714 #parser.add_option("-t", "--type", dest="type", 715 #default="TEXT", 716 #help=_("output type: HTML | XML | TEXT (default)")) 717 parser.add_option("-l", "--level", dest="level", 718 default="TYPICAL", 719 help=_("level: BASIC | TYPICAL (default) | ALL")) 720 parser.add_option("-b", "--begin", dest="begin_snap", 721 default=u"0", 722 help=_("begin snapshot identifier")) 723 parser.add_option("-e", "--end", dest="end_snap", 724 default=u"0", 725 help=_("end snapshot identifier")) 726 parser.add_option("-o", "--output-file", dest="filename", 727 default=u"", 728 help=_("output file")) 729 return parser

730

731 - def do_addmrpt(self, arg):

732 """Generates ADDM report""" 733 parser = self.parser_addmrpt() 734 options, args = parser.parse_args(arg) 735 self.__checkConnection() 736 self.__checkArg(arg, "<=6") 737 if self.tty: 738 if options.begin_snap == "0": 739 (numDays, options.begin_snap) = self.__askForSnapshotId(0, _("begin")) 740 if options.end_snap == "0": 741 (numDays, options.end_snap) = self.__askForSnapshotId(numDays, _("end")) 742 else: 743 # Not a tty, so command line must be complete. 744 if options.begin_snap == "0" or options.end_snap == "0": 745 raise PysqlException(_("Missing arguments. Please, specify snaphsot identifiers.")) 746 self.__animateCursor() # Only after user interaction 747 result = pysqlaudit.addmReport(self.db, 748 options.begin_snap, 749 options.end_snap, 750 #text=options.text, 751 level=options.level) 752 if options.filename == "": 753 self.__toScreen(result, moreRows=False, header=False) 754 else: 755 self.__toCsv(result, options.filename, header=False) 756 print GREEN + _("(Completed)") + RESET

757

758 - def parser_awrrpt(self):

759 parser = PysqlOptionParser() 760 parser.set_usage(CYAN + "awr[rpt] " + _("[options]") + RESET) 761 parser.set_description(_("Generates performance report based on AWR statistics. ") + 762 _("10g or upper is required. ") + 763 _("Before starting, please ensure that you have the required license to use it.")) 764 parser.add_option("-t", "--type", dest="type", 765 default="TEXT", 766 help=_("output type: HTML | TEXT (default)")) 767 parser.add_option("-b", "--begin", dest="begin_snap", 768 default=u"0", 769 help=_("begin snapshot identifier")) 770 parser.add_option("-e", "--end", dest="end_snap", 771 default=u"0", 772 help=_("end snapshot identifier")) 773 parser.add_option("-o", "--output-file", dest="filename", 774 default=u"", 775 help=_("output file")) 776 return parser

777

778 - def do_awrrpt(self, arg):

779 """Generates AWR report""" 780 parser = self.parser_awrrpt() 781 options, args = parser.parse_args(arg) 782 self.__checkConnection() 783 self.__checkArg(arg, "<=8") 784 if self.tty: 785 if options.begin_snap == "0": 786 (numDays, options.begin_snap) = self.__askForSnapshotId(0, _("begin")) 787 if options.end_snap == "0": 788 (numDays, options.end_snap) = self.__askForSnapshotId(numDays, _("end")) 789 else: 790 # Not a tty, so command line must be complete. 791 if options.begin_snap == "0" or options.end_snap == "0": 792 raise PysqlException(_("Missing arguments. Please, specify snaphsot identifiers.")) 793 self.__animateCursor() # Only after user interaction 794 result = pysqlaudit.awrReport(self.db, 795 options.type, 796 options.begin_snap, 797 options.end_snap) 798 if options.filename == "": 799 self.__toScreen(result, moreRows=False, header=False) 800 else: 801 self.__toCsv(result, options.filename, header=False) 802 print GREEN + _("(Completed)") + RESET

803

804 - def parser_sqltune(self):

805 parser = PysqlOptionParser() 806 parser.set_usage(CYAN + "sqltune " + _("[options] <sql statement>") + RESET) 807 parser.set_description(_("Generates tuning advice report for an SQL query. ") + 808 _("10g or upper is required. ")) 809 # Oracle only supports TEXT but HTML and XML will be added in next releases 810 #parser.add_option("-t", "--type", dest="type", 811 #default="TEXT", 812 #help=_("output type: HTML | XML | TEXT (default)")) 813 parser.add_option("-l", "--level", dest="level", 814 default="TYPICAL", 815 help=_("level: BASIC | TYPICAL (default) | ALL")) 816 parser.add_option("-o", "--output-file", dest="filename", 817 default=u"", 818 help=_("output file")) 819 return parser

820

821 - def do_sqltune(self, arg):

822 """Generates SQL tuning advice""" 823 parser = self.parser_sqltune() 824 options, args = parser.parse_args(arg) 825 self.__checkConnection() 826 self.__checkArg(arg, ">1") 827 self.__animateCursor() # Only after user interaction 828 result = pysqlaudit.sqlTune(self.db, " ".join(args), 829 #text=options.text, 830 level=options.level) 831 if options.filename == "": 832 self.__toScreen(result, moreRows=False, header=False) 833 else: 834 self.__toCsv(result, options.filename, header=False) 835 print GREEN + _("(Completed)") + RESET

836

837 - def parser_durpt(self):

838 parser = PysqlOptionParser() 839 parser.set_usage(CYAN + "durpt " + _("[options]") + RESET) 840 parser.set_description(_("Generates storage report based on segment statistics. ") + 841 _("DBA grants are required. ")) 842 parser.add_option("-s", "--segment-type", dest="type", 843 default=u"both", 844 help=_("type of segment: TABLE | INDEX | BOTH (default)")) 845 parser.add_option("-t", "--tablespace", dest="tbs", 846 default=u"%", 847 help=_("filters by tablespace")) 848 parser.add_option("-u", "--user", dest="user", 849 default=u"%", 850 help=_("filters by user")) 851 parser.add_option("-n", "--nbLines", dest="nbLines", 852 default= -1, 853 help=_("filters resultset to the n first rows")) 854 parser.add_option("-o", "--output-file", dest="filename", 855 default=u"", 856 help=_("output file") + _(" (1 distinct file per segment type)")) 857 return parser

858

859 - def do_durpt(self, arg):

860 """Generates disk usage report""" 861 parser = self.parser_durpt() 862 options, args = parser.parse_args(arg) 863 self.__checkConnection() 864 self.__checkArg(arg, "<=8") 865 self.__animateCursor() 866 for type in ("table", "index"): 867 if options.type.lower() in ("both", type): 868 (result, header) = pysqlaudit.duReport(self.db, 869 type, 870 options.tbs.upper().replace('*', '%'), 871 options.user.upper().replace('*', '%'), 872 int(options.nbLines)) 873 if options.filename == "": 874 print GREEN + "***** " + type.upper() + " *****" + RESET 875 self.__displayTab(result, header) 876 print 877 else: 878 if options.filename.split(".")[-1] == "csv": 879 filename = options.filename.replace(".csv", "_%s.csv" % type.lower()) 880 elif options.filename.split(".")[-1] == "CSV": 881 filename = options.filename.replace(".CSV", "_%s.CSV" % type.upper()) 882 else: 883 filename = options.filename + "_" + type 884 self.__toCsv(result, filename) 885 print GREEN + _("(Completed)") + RESET

886

887 - def do_assmrpt(self, arg):

888 """Generates ASSM report""" 889 self.__checkConnection() 890 self.__checkArg(arg, "=1") 891 self.__animateCursor() 892 (result, header) = pysqlaudit.assmReport(self.db, arg) 893 self.__displayTab(result, header)

894 895 # Graphic functions

896 - def parser_datamodel(self):

897 parser = PysqlOptionParser() 898 parser.set_usage(CYAN + "datamodel " + _("[options] [filters on table name]]") + RESET) 899 parser.set_description( 900 _("Extracts the datamodel of a user filtered on selected table pattern. ") + 901 _("The generation of the output is powered by Graphviz (http://www.graphviz.org)") 902 ) 903 if self.db: 904 defaultUser = self.db.getUsername() 905 else: 906 defaultUser = "" 907 parser.add_option("-c", "--columns", dest="columns", 908 default=False, action="store_true", 909 help=_("also draws table's columns")) 910 parser.add_option("-u", "--user", dest="user", 911 default=defaultUser, 912 help=_("user owner of tables (schema)")) 913 return parser

914

915 - def do_datamodel(self, arg):

916 """Exports a datamodel as a picture""" 917 self.__checkConnection() 918 parser = self.parser_datamodel() 919 options, args = parser.parse_args(arg) 920 pysqlgraphics.datamodel(self.db, options.user.upper(), 921 tableFilter=" ".join(args), 922 withColumns=options.columns)

923

924 - def parser_dependencies(self):

925 parser = PysqlOptionParser() 926 parser.set_usage(CYAN + "dep[endencies] " + _("[options] <object name>") + RESET) 927 parser.set_description( 928 _("Displays object dependencies as a picture. ") + 929 _("The generation of the output is powered by Graphviz (http://www.graphviz.org)") 930 ) 931 directions = ("onto", "from", "both") 932 parser.add_option("-d", "--direction", dest="direction", 933 default="both", type="choice", 934 metavar="<direction>", choices=directions, 935 help=_("direction of dependency tracking: %s" % ", ".join(directions))) 936 parser.add_option("-r", "--recursion", dest="maxDepth", 937 default=self.conf.get("graph_depmaxdepth"), type="int", 938 help=_("maximum level of recursion")) 939 parser.add_option("-n", "--nodes", dest="maxNodes", 940 default=self.conf.get("graph_depmaxnodes"), type="int", 941 help=_("maximum number of nodes on graph")) 942 return parser

943

944 - def do_dependencies(self, arg):

945 """Exports object dependencies as a picture""" 946 self.__checkConnection() 947 parser = self.parser_dependencies() 948 options, args = parser.parse_args(arg) 949 self.__checkArg(args, "=1") 950 pysqlgraphics.dependencies(self.db, args[0], 951 options.direction, 952 options.maxDepth, 953 options.maxNodes)

954

955 - def parser_diskusage(self):

956 parser = PysqlOptionParser() 957 parser.set_usage(CYAN + "diskusage|du " + _("[options] <schema name>") + RESET) 958 parser.set_description( 959 _("Extracts the physical storage of a user as a picture based on Oracle statistics. ") + 960 _("The generation of the output is powered by Graphviz (http://www.graphviz.org)") 961 ) 962 parser.add_option("-i", "--index", dest="index", 963 default=False, action="store_true", 964 help=_("also draws index segment")) 965 parser.add_option("-p", "--percent", dest="percent", 966 default=False, action="store_true", 967 help=_("draws object sizes against others")) 968 969 return parser

970

971 - def do_diskusage(self, arg):

972 """Exports disk usage as a picture""" 973 self.__checkConnection() 974 parser = self.parser_diskusage() 975 options, args = parser.parse_args(arg) 976 try: 977 user = args[0] 978 except IndexError: 979 user = self.db.getUsername() 980 pysqlgraphics.diskusage(self.db, user.upper(), options.index, options.percent)

981

982 - def do_ddl(self, arg):

983 """Prints Oracle object DDL""" 984 self.__checkConnection() 985 self.__checkArg(arg, "==1") 986 self.__animateCursor() 987 result = pysqlfunctions.ddl(self.db, arg) 988 if result is None: 989 print CYAN + _("(no result)") + RESET 990 else: 991 print result.rstrip(" ").rstrip("\n") + ";"

992

993 - def do_edit(self, arg):

994 """Edits properties of an Orale object or last SQL statement""" 995 self.__checkConnection() 996 nArgs = len(arg.split()) 997 if nArgs == 0: 998 # Edits last statement 999 result = pysqlfunctions.editor(self.lastStatement + ";") 1000 if result: 1001 result = result.rstrip("\n") 1002 readline.add_history(result) 1003 self.lastStatement = result 1004 elif nArgs == 1: 1005 # Edits Oracle object 1006 if pysqlfunctions.edit(self.db, arg): 1007 print GREEN + _("(Update successful)") + RESET 1008 else: 1009 print CYAN + _("(no result)") + RESET 1010 else: 1011 raise PysqlException(_("Incorrect arguments. See help edit"))

1012

1013 - def do_execute(self, arg):

1014 """ Emulates sqlplus execute""" 1015 self.__checkConnection() 1016 self.__checkArg(arg, ">=1") 1017 line = "begin\n" + arg + ";\nend;\n" 1018 self.__executeSQL(line)

1019

1020 - def do_explain(self, arg):

1021 """Explain SQL exec plan""" 1022 self.__checkConnection() 1023 self.__checkArg(arg, ">1") 1024 self.__animateCursor() 1025 importantWords = ["TABLE ACCESS FULL", "FULL SCAN"] 1026 result = pysqlfunctions.explain(self.db, arg) 1027 for line in result: 1028 line = line[0] 1029 for word in importantWords: 1030 line = sub("(.*)(" + word + ")(.*)", r"\1" + RED + r"\2" + RESET + r"\3", line) 1031 print line

1032

1033 - def parser_session(self):

1034 parser = PysqlOptionParser() 1035 parser.set_usage(CYAN + "session " + _("[options] <session id>") + RESET) 1036 parser.set_description(_("Displays Oracle sessions. ") + 1037 _("If a session id is provided, display detailed session informations, ") + 1038 _("else display all session summary")) 1039 parser.add_option("-a", "--all", dest="all", 1040 default=False, action="store_true", 1041 help=_("displays all foreground sessions, both active and inactive")) 1042 parser.add_option("-s", "--search", dest="search", 1043 action="append", 1044 help=_("filters session display with given search term. Multiple searches can be given to make 'and' search")) 1045 return parser

1046

1047 - def do_session(self, arg):

1048 """Display Oracle session""" 1049 self.__checkConnection() 1050 parser = self.parser_session() 1051 options, args = parser.parse_args(arg) 1052 if options.all and args: 1053 print CYAN + _("Note: the all (-a / --all) option is useless when display one session") + RESET 1054 if not args: 1055 # Lists all session 1056 (header, result) = pysqlfunctions.sessions(self.db, all=options.all, search=options.search) 1057 self.__displayTab(result, header) 1058 else: 1059 sessionId = args[0] 1060 # Displays details about one session 1061 print CYAN + "*****" + _("Input/Output statistics") + "*****" + RESET 1062 result = pysqlfunctions.sessionStat(self.db, sessionId, stat="ios") 1063 self.__displayTab(result, self.db.getDescription()) 1064 print CYAN + "*****" + _("Wait events") + "*****" + RESET 1065 result = pysqlfunctions.sessionStat(self.db, sessionId, stat="waitEvents") 1066 self.__displayTab(result, self.db.getDescription()) 1067 print CYAN + "*****" + _("Current statement") + "*****" + RESET 1068 result = pysqlfunctions.sessionStat(self.db, sessionId, stat="currentStatement") 1069 if result and result[0][0]: 1070 result = "".join([i[0] for i in result]) # Merge all in one string 1071 result = sub("\s+", " ", result) # Strip extra spaces 1072 print result 1073 try: 1074 if not result.upper().startswith("ALTER"): 1075 self.do_explain(result) 1076 except PysqlException, e: 1077 # Should be a privilege exception. Delay error at this end 1078 print _("Cannot explain plan (%s)") % e 1079 else: 1080 print _("No statement") 1081 print CYAN + "*****" + _("Open cursors") + "*****" + RESET 1082 result = pysqlfunctions.sessionStat(self.db, sessionId, stat="openCursors") 1083 self.__displayTab(result, self.db.getDescription()) 1084 print CYAN + "*****" + _("Locks") + "*****" + RESET 1085 result = pysqlfunctions.sessionStat(self.db, sessionId, stat="locks") 1086 self.__displayTab(result, self.db.getDescription())

1087

1088 - def parser_kill(self):

1089 parser = PysqlOptionParser() 1090 parser.set_usage(CYAN + "kill [options] " + _("<session-id> <session-serial>") + RESET) 1091 parser.set_description(_("Kills the session given in parameter. ") + 1092 _("Uses the 'session' command to find session-id and session-serial (two first columns)")) 1093 parser.add_option("-i", "--immediate", dest="immediate", 1094 default=False, action="store_true", 1095 help=_("kills immediatly the current session")) 1096 return parser

1097

1098 - def do_kill(self, arg):

1099 """Kill sessions""" 1100 self.__checkConnection() 1101 parser = self.parser_kill() 1102 options, args = parser.parse_args(arg) 1103 self.__checkArg(args, "==2") 1104 pysqlfunctions.killSession(self.db, ",".join(args), immediate=options.immediate) 1105 print GREEN + _("Kill signal has been sent to the session") + RESET

1106

1107 - def do_lock(self, arg):

1108 """Display instance lock""" 1109 self.__checkConnection() 1110 self.__checkArg(arg, "==0") 1111 (header, result) = pysqlfunctions.lock(self.db) 1112 self.__addToCompleteList([i[0] for i in result], "columns") # buggy ! 1113 self.__displayTab(result, header)

1114

1115 - def do_trace(self, sid):

1116 """Trace a session""" 1117 self.__checkConnection() 1118 self.__checkArg(sid, "==1") 1119 try: 1120 if self.trace.has_key(sid): 1121 # Ends trace capture and display result 1122 ios = list(pysqlfunctions.sessionStat(self.db, sid, "ios")[0]) 1123 iosHeader = self.db.getDescription() 1124 waits = list(pysqlfunctions.sessionStat(self.db, sid, "waitEvents")[0]) 1125 waitsHeader = self.db.getDescription() 1126 def conv(item): 1127 """Sub function to convert str to int and NULL to 0""" 1128 if item is None: 1129 return 0 1130 else: 1131 try: 1132 return int(item) 1133 except ValueError: 1134 return 0

1135 ios = [conv(i) for i in ios] 1136 waits = [conv(i) for i in waits] 1137 self.trace[sid][0] = [conv(i) for i in self.trace[sid][0]] 1138 self.trace[sid][1] = [conv(i) for i in self.trace[sid][1]] 1139 resultIos = [] 1140 resultWaits = [] 1141 for i in xrange(len(ios)): 1142 resultIos.append(ios[i] - self.trace[sid][0][i]) 1143 resultWaits.append(waits[i] - self.trace[sid][1][i]) 1144 print CYAN + "*****" + _("Input/Output delta for session %s") % sid + "*****" + RESET 1145 self.__displayTab([resultIos], iosHeader) 1146 print CYAN + "*****" + _("Wait events delta for session %s") % sid + "*****" + RESET 1147 self.__displayTab([resultWaits], waitsHeader) 1148 # Removes trace point for this session 1149 del self.trace[sid] 1150 else: 1151 # Starts trace capture 1152 print CYAN + _("Starting trace capture for session %s") % sid + RESET 1153 print CYAN + _("""Type "trace %s" again to stop trace on this sesssion""") % sid + RESET 1154 ios = list(pysqlfunctions.sessionStat(self.db, sid, "ios")[0]) 1155 waits = list(pysqlfunctions.sessionStat(self.db, sid, "waitEvents")[0]) 1156 # Stores to trace dict to compute diff on next trace call 1157 self.trace[sid] = [ios, waits] 1158 except IndexError: 1159 msg = _("Session %s does not exist or you are not allowed to see session details") % sid 1160 raise PysqlException(msg)

1161

1162 - def do_pkgtree(self, arg):

1163 """Display PL/SQL package call tree""" 1164 self.__checkConnection() 1165 self.__checkArg(arg, "==1") 1166 #raise PysqlNotImplemented() 1167 pysqlgraphics.pkgTree(self.db, arg)

1168 1169 # Oracle object searching (in alphabectic order)

1170 - def do_datafile(self, arg):

1171 """Display datafile""" 1172 self.__searchObjet("datafile", arg)

1173

1174 - def do_directory(self, arg):

1175 """Display directories""" 1176 self.__searchObjet("directory", arg)

1177

1178 - def do_function(self, arg):

1179 """Display functions""" 1180 self.__searchObjet("function", arg)

1181

1182 - def do_index(self, arg):

1183 """Display indexes""" 1184 self.__searchObjet("index", arg)

1185

1186 - def do_package(self, arg):

1187 """Display PL/SQL packages""" 1188 self.__searchObjet("package", arg)

1189

1190 - def do_procedure(self, arg):

1191 """Display PL/SQL procedures""" 1192 self.__searchObjet("procedure", arg)

1193

1194 - def do_segment(self, arg):

1195 """Display segments (tables, index)""" 1196 print CYAN + "***** " + _("Tables") + " *****" + RESET 1197 self.__searchObjet("table", arg) 1198 print CYAN + "\n***** " + _("Indexes") + " *****" + RESET 1199 self.__searchObjet("index", arg)

1200

1201 - def do_sequence(self, arg):

1202 """Display sequences""" 1203 self.__searchObjet("sequence", arg)

1204

1205 - def do_table(self, arg):

1206 """Display tables""" 1207 self.__searchObjet("table", arg)

1208

1209 - def do_trigger(self, arg):

1210 """Display triggers""" 1211 self.__searchObjet("trigger", arg)

1212

1213 - def do_view(self, arg):

1214 """Display view""" 1215 self.__searchObjet("view", arg)

1216

1217 - def do_tablespace(self, arg):

1218 """Display tablespaces""" 1219 self.__searchObjet("tablespace", arg)

1220

1221 - def do_user(self, arg):

1222 """Display users (aka schema)""" 1223 self.__searchObjet("user", arg)

1224

1225 - def do_profile(self, arg):

1226 """Display profiles""" 1227 self.__searchObjet("profile", arg)

1228

1229 - def do_role(self, arg):

1230 """Display roles""" 1231 self.__searchObjet("role", arg)

1232 1233 # Cursor manipulation

1234 - def do_last(self, arg):

1235 """Display last lines of query set""" 1236 self.__checkConnection() 1237 self.__checkArg(arg, "<=1") 1238 self.__animateCursor() 1239 try: 1240 nbLines = int(arg) 1241 if nbLines < self.conf.get("fetchSize"): 1242 # Don't fetch too small (perf purpose) 1243 fetchSize = self.conf.get("fetchSize") 1244 else: 1245 fetchSize = nbLines 1246 except (ValueError, TypeError): 1247 nbLines = self.conf.get("fetchSize") 1248 fetchSize = nbLines 1249 1250 moreRows = True 1251 result = [] 1252 while moreRows: 1253 previousResult = result 1254 (result, moreRows) = self.db.fetchNext(fetchSize) 1255 previousResult.extend(result) 1256 result = previousResult[-nbLines:] 1257 self.__displayTab(result, self.db.getDescription())

1258

1259 - def do_next(self, arg):

1260 """Display next lines of query set""" 1261 self.__checkConnection() 1262 self.__checkArg(arg, "<=1") 1263 self.__animateCursor() 1264 try: 1265 nbLines = int(arg) 1266 except (ValueError, TypeError): 1267 nbLines = 0 1268 self.__fetchNext(nbLines)

1269 1270 # Parameters handling

1271 - def do_get(self, arg):

1272 """Get pysql parameter""" 1273 if arg in ("all", ""): 1274 result = self.conf.getAll() 1275 # Converts all to str to avoid strange alignement 1276 for i in xrange(len(result)): 1277 result[i] = [str(result[i][j]) for j in xrange(len(result[i]))] 1278 self.__addToCompleteList([i[0] for i in result], "parameters") 1279 self.__displayTab(result, 1280 [_("Parameter"), _("User defined value"), _("Default value")]) 1281 else: 1282 print self.conf.get(arg)

1283

1284 - def do_set(self, arg):

1285 """Set a pysql parameter""" 1286 if arg == "": 1287 self.do_get("all") 1288 else: 1289 try: 1290 (key, value) = arg.split("=") 1291 self.conf.set(key, value) 1292 except ValueError, e: 1293 self.help_set()

1294

1295 - def do_write(self, arg):

1296 """Write configuration to disk""" 1297 self.conf.write()

1298 1299 # Shell execution

1300 - def do_shell(self, arg):

1301 """Execute a shell command or open a shell""" 1302 # An empty command line enables to open a subshell 1303 if arg == "": 1304 if os.name == "posix": 1305 arg = os.environ["SHELL"] 1306 elif os.name == "nt": 1307 arg = "cmd" 1308 else: 1309 raise PysqlNotImplemented() 1310 # Running command line 1311 exitStatus = os.system(arg) 1312 # Display exit status if an error occurred 1313 if exitStatus != 0: 1314 print CYAN + _("Exited with code ") + str(exitStatus) + RESET

1315

1316 - def do_lls(self, arg):

1317 """A simple local ls""" 1318 self.__checkArg(arg, "<=1") 1319 if os.name == "posix": 1320 cmd = "ls " 1321 elif os.name == "nt": 1322 cmd = "dir " 1323 else: 1324 raise PysqlNotImplemented() 1325 # Running command line 1326 if len(arg) == 0: 1327 exitStatus = os.system(cmd) 1328 else: 1329 exitStatus = os.system(cmd + arg) 1330 # Display exit status if an error occurred 1331 if exitStatus != 0: 1332 print CYAN + "Exited with code " + str(exitStatus) + RESET

1333

1334 - def do_lcd(self, arg):

1335 """Change local directory""" 1336 self.__checkArg(arg, "<=1") 1337 if arg == "": 1338 if os.name == "posix": 1339 arg = os.environ["HOME"] 1340 elif os.name == "nt": 1341 arg = os.environ["HOMEDRIVE"] + os.environ["HOMEPATH"] 1342 else: 1343 raise PysqlNotImplemented() 1344 try: 1345 os.chdir(arg) 1346 except OSError: 1347 raise PysqlException(_("No such directory"))

1348

1349 - def do_lpwd(self, arg):

1350 """Display current work directory""" 1351 self.__checkArg(arg, "==0") 1352 print os.getcwd()

1353 1354 1355 # Script execution

1356 - def do_script(self, arg):

1357 """Execute an external sql file, similar to sql*plus @""" 1358 self.__checkConnection() 1359 self.__checkArg(arg, "==1") 1360 try: 1361 # If file does not exist, tries with .sql extension 1362 if os.access(arg, os.R_OK): 1363 fileName = arg 1364 else: 1365 fileName = arg + ".sql" 1366 script = file(fileName, "r") # File is closed by GC 1367 for line in script.readlines(): 1368 line = line.rstrip("\n") 1369 line = self.precmd(line) 1370 self.onecmd(line) 1371 self.postcmd(None, line) 1372 except IOError, e: 1373 raise PysqlException(e)

1374 1375 # Command repeating

1376 - def do_watch(self, arg):

1377 """Repeat a command""" 1378 self.__checkConnection() 1379 self.__checkArg(arg, ">=1") 1380 # Checks if interval is given 1381 interval = arg.split()[0] 1382 try: 1383 interval = int(interval) 1384 arg = " ".join(arg.split()[1:]) 1385 except ValueError: 1386 # Default to 3 secondes 1387 interval = 3 1388 try: 1389 while True: 1390 self.onecmd(arg) 1391 sleep(interval) 1392 except KeyboardInterrupt: 1393 # As for now KeyboardInterrupt is never raised 1394 # if if cx_Oracle.connection object is created 1395 # Bug! 1396 print _("exit watch") 1397 pass

1398 1399 # To file

1400 - def do_csv(self, arg):

1401 """Dumps sql request to file""" 1402 self.__checkConnection() 1403 self.__checkArg(arg, ">=3") 1404 (fileName, sql) = match("(.+?)\s(.+)", arg).groups() 1405 self.__executeSQL(sql, output="csv", fileName=fileName)

1406 1407 # Time it!

1408 - def do_time(self, arg):

1409 """Time request execution time""" 1410 self.__checkConnection() 1411 self.__checkArg(arg, ">=3") 1412 self.__animateCursor() 1413 start = time() 1414 self.__executeSQL(arg, output="null") 1415 elapsed = time() - start 1416 print GREEN + _("(Executed in %.1f second(s))") % elapsed + RESET

1417 1418 # Show it!

1419 - def do_show(self, arg):

1420 """Show parameters""" 1421 self.__checkConnection() 1422 self.__checkArg(arg, ">=1") 1423 argList = arg.split() 1424 argList[0] = argList[0].lower() 1425 if argList[0] in ("parameter", "parameters"): 1426 self.__checkArg(arg, "<=2") 1427 param = "" 1428 if len(argList) == 2: 1429 param = argList[1] 1430 (header, result) = pysqlfunctions.showParameter(self.db, param) 1431 self.__displayTab(result, header) 1432 elif argList[0] in ("spparameter", "spparameters"): 1433 self.__checkArg(arg, "<=2") 1434 param = "" 1435 if len(argList) == 2: 1436 param = argList[1] 1437 (header, result) = pysqlfunctions.showServerParameter(self.db, param) 1438 self.__displayTab(result, header) 1439 elif argList[0] == "instance": 1440 self.__checkArg(arg, "==1") 1441 print _("Connected to ") + self.db.getDSN() 1442 elif argList[0] == "version": 1443 self.__checkArg(arg, "==1") 1444 print _("Oracle ") + self.db.getVersion() 1445 else: 1446 print RED + _("Invalid argument") + RESET

1447 1448 # Time to say bye

1449 - def do_exit(self, arg):

1450 """ Close current connection and exit pysql""" 1451 return self.__exit()

1452 1453 # Command help definitions (in alphabetic order)

1454 - def help_assmrpt(self):

1455 """online help""" 1456 print _("Usage:") 1457 print "\t" + CYAN + "assmrpt " + _("<table name>") + RESET 1458 print _("Computes block clustering introduced by Oracle ASSM")

1459

1460 - def help_bg(self):

1461 """online help""" 1462 print _("Usage:") 1463 print "\t" + CYAN + "bg " + _("[order id]") + RESET 1464 print _("Manages background queries") 1465 print 1466 print _("Sample usages:") 1467 print "\t" + _("To display all background queries:") 1468 print "\t\t" + CYAN + "bg" + RESET 1469 print "\t" + _("To call back a background query:") 1470 print "\t\t" + CYAN + "bg " + _("<id>") + RESET

1471

1472 - def help_compare(self):

1473 """online help""" 1474 print _("Usage:") 1475 print "\t" + CYAN + "compare [data] " + _("<user>[/<password>@<SID>][:<table>] <user>[/<password>@<SID>][:<table>]") + RESET 1476 print _("Compares structure or data of two schemas or two tables. Objects could be in two distinct databases.") 1477 print _("By default, only structure is compared.") 1478 print 1479 print _("Sample usages:") 1480 print "\t" + _("To compare two schemas:") 1481 print "\t\t" + CYAN + "compare " + _("user/password@SID user/password@SID") + RESET 1482 print "\t" + _("To compare two tables:") 1483 print "\t\t" + CYAN + "compare " + _("user/password@SID:table user/password@SID:table") + RESET 1484 print "\t" + _("To compare two tables in current schema:") 1485 print "\t\t" + CYAN + "compare " + _("table table") + RESET 1486 print "\t" + _("""To compare table data, use the "data" keyword this way:""") 1487 print "\t\t" + CYAN + "compare data " + _("user/password@SID:table user/password@SID:table") + RESET

1488

1489 - def help_connect(self):

1490 """online help""" 1491 print _("Usage:") 1492 print "\t" + CYAN + "conn[ect] " + _("user[/password][@[host[:port]/]SID]") + " [sysdba|sysoper]" + RESET 1493 print _("Connects to Oracle and closes previous connection if any")

1494

1495 - def help_count(self):

1496 """online help""" 1497 print _("Usage:") 1498 print "\t" + CYAN + "count " + _("<table/view name>") + RESET 1499 print _("Counts the number of lines in a table or a view")

1500

1501 - def help_csv(self):

1502 """online help""" 1503 print _("Usage:") 1504 print "\t" + CYAN + "csv " + _("<output file> <sql query>") + RESET 1505 print _("Dumps sql query to file") 1506 print 1507 print _("Example:") 1508 print "\t" + CYAN + "csv " + _("out.csv select * from dummy;") + RESET

1509

1510 - def help_datafile(self):

1511 """online help""" 1512 self._help_for_search_method("datafile")

1513

1514 - def help_ddl(self):

1515 """online help""" 1516 print _("Usage:") 1517 print "\t" + CYAN + _("ddl <table|view>") + RESET 1518 print _("Prints Oracle object DDL") 1519 print 1520 print _("Example:") 1521 print "\t" + CYAN + _("ddl DUAL") + RESET

1522

1523 - def help_directory(self):

1524 """online help""" 1525 self._help_for_search_method("directory")

1526

1527 - def help_disconnect(self):

1528 """online help""" 1529 print _("Usage:") 1530 print "\t" + CYAN + "disc[connect]" + RESET 1531 print _("Closes current connection if any")

1532

1533 - def help_edit(self):

1534 """online help""" 1535 print _("Usage:") 1536 print "\t" + CYAN + "ed[it] " + _("<object name>") + RESET 1537 print _("Edits (view or modify) an object)") 1538 print _("If no arg is provided, edits last SQL statement")

1539

1540 - def help_exit(self):

1541 """online help""" 1542 print _("Usage:") 1543 print "\t" + CYAN + "exit" + RESET 1544 print _("Well, it seems rather explicit, isn't it?")

1545

1546 - def help_explain(self):

1547 """online help""" 1548 print _("Usage:") 1549 print "\t" + CYAN + "explain " + _("<sql statement>") + RESET 1550 print _("Computes and displays explain plan for the statement")

1551

1552 - def help_function(self):

1553 """online help""" 1554 self._help_for_search_method("function")

1555

1556 - def help_get(self):

1557 """online help""" 1558 print _("Usage:") 1559 print "\t" + CYAN + "get " + _("<key>") + RESET 1560 print _("Prints the value of the parameter <key>") 1561 print _("The special key « all » allows to print all parameters")

1562

1563 - def help_help(self):

1564 """online help""" 1565 print _("Usage:") 1566 print "\t" + CYAN + "help " + _("<pysql command>") + RESET 1567 print _("Brings some help like usage and a short description") 1568 print _("about the command and its parameters")

1569

1570 - def help_history(self):

1571 """online help""" 1572 print _("Usage:") 1573 print "\t" + CYAN + "h[istory] " + _("<n>") + RESET 1574 print _("Without any argument, prints the last 20 commands") 1575 print _("If argument is supplied, executes the nth command")

1576

1577 - def help_index(self):

1578 """online help""" 1579 self._help_for_search_method("index")

1580

1581 - def help_last(self):

1582 """online help""" 1583 print _("Usage:") 1584 print "\t" + CYAN + "last " + _("<number of lines>") + RESET 1585 print _("Fetches all lines of current result set and display only the last lines") 1586 print _("Default number of lines default to cursor array size")

1587

1588 - def help_lcd(self):

1589 """online help""" 1590 print _("Usage:") 1591 print "\t" + CYAN + "lcd " + _("<path>") + RESET 1592 print _("Changes working directory")

1593

1594 - def help_library(self):

1595 """online help""" 1596 print _("Usage:") 1597 print "\t" + CYAN + "lib[rary] " + _("<sqlName> <sqlText>") + RESET 1598 print "\t" + _("Handles user custom sql library. Allows user to save and recall sql requests.") 1599 print 1600 print _("Sample usages: ") 1601 print "\t" + _("To see all saved request:") 1602 print "\t\t" + CYAN + "lib" + RESET 1603 print "\t" + _("To save a request as 'employeeNumber':") 1604 print "\t\t" + CYAN + "lib " + _("employeeNumber select count(*) from employee") + RESET 1605 print "\t" + _("recall a saved request:") 1606 print "\t\t" + CYAN + "lib " + _("employeNumber") + RESET 1607 print "\t" + _("To remove the foo request:") 1608 print "\t\t" + CYAN + "lib " + _("foo remove") + RESET

1609

1610 - def help_lls(self):

1611 """online help""" 1612 print _("Usage:") 1613 print "\t" + CYAN + "lls " + _("[path/][file]") + RESET 1614 print _("Lists directory contents")

1615

1616 - def help_lock(self):

1617 """online help""" 1618 print _("Usage:") 1619 print "\t" + CYAN + "lock" + RESET 1620 print _("Displays the locked objects")

1621

1622 - def help_lpwd(self):

1623 """online help""" 1624 print _("Usage:") 1625 print "\t" + CYAN + "lpwd" + RESET 1626 print _("Prints local directory")

1627

1628 - def help_next(self):

1629 """online help""" 1630 print _("Usage:") 1631 print "\t" + CYAN + "next " + _("<number of lines>") + RESET 1632 print _("Fetches the n next lines of current result set") 1633 print _("Default number of lines default to cursor array size") 1634 print _("Just press enter is equivalent to next without arguments")

1635

1636 - def help_package(self):

1637 """online help""" 1638 self._help_for_search_method("package")

1639

1640 - def help_procedure(self):

1641 """online help""" 1642 self._help_for_search_method("procedure")

1643

1644 - def help_script(self):

1645 """online help""" 1646 print _("Usage:") 1647 print "\t" + CYAN + "@ " + _("<script>") + RESET 1648 print _("Executes a PL/SQL script and displays the output on the standard output")

1649

1650 - def help_segment(self):

1651 """online help""" 1652 self._help_for_search_method("segment")

1653

1654 - def help_sequence(self):

1655 """online help""" 1656 self._help_for_search_method("sequence")

1657

1658 - def help_set(self):

1659 """online help""" 1660 print _("Usage:") 1661 print "\t" + CYAN + "set " + _("<key>=<value>") + RESET 1662 print _("Sets <value> to the parameter <key>")

1663

1664 - def help_shell(self):

1665 """online help""" 1666 print _("Usage:") 1667 print "\t" + CYAN + "! " + _("<command line>") + RESET 1668 print _("Executes a command into the system terminal (depending on your system profile)") 1669 print _("If no commands are given then a subshell is openned")

1670

1671 - def help_show(self):

1672 """online help""" 1673 print _("Usage:") 1674 print "\t" + CYAN + "show instance" + RESET 1675 print _("Displays the database service name (DSN) of the current connection") 1676 print 1677 print "\t" + CYAN + "show version" + RESET 1678 print _("Displays the database server version") 1679 print 1680 print "\t" + CYAN + "show parameter[s] " + _("<partial parameter name>") + RESET 1681 print _("Looks for session parameters with name like the partial name given.") 1682 print _("Wilcard % can be used.") 1683 print _("If none is provided, pysql adds a % at the begining and the end.") 1684 print 1685 print "\t" + CYAN + "show spparameter[s] " + _("<partial parameter name>") + RESET 1686 print _("Looks for server parameters with name like the partial name given.") 1687 print _("These parameters are defined in spfile. Wilcard % can be used.") 1688 print _("If none is provided, pysql adds a % at the begining and the end.")

1689

1690 - def help_commit(self):

1691 """online help""" 1692 print _("Usage:") 1693 print "\t" + CYAN + "commit" + RESET 1694 print _("Commits pending transaction")

1695

1696 - def help_rollback(self):

1697 """online help""" 1698 print _("Usage:") 1699 print "\t" + CYAN + "rollback" + RESET 1700 print _("Cancels pending transaction")

1701

1702 - def help_shutdown(self):

1703 """online help""" 1704 print _("Usage:") 1705 print "\t" + CYAN + "shutdown [abort|immediate|normal]" + RESET 1706 print _("Shuts instance down")

1707

1708 - def help_startup(self):

1709 """online help""" 1710 print _("Usage:") 1711 print "\t" + CYAN + "startup [mount]" + RESET 1712 print _("Starts instance up")

1713

1714 - def help_table(self):

1715 """online help""" 1716 self._help_for_search_method("table")

1717

1718 - def help_tablespace(self):

1719 """online help""" 1720 self._help_for_search_method("tablespace")

1721

1722 - def help_time(self, arg):

1723 """online help""" 1724 print _("Usage:") 1725 print "\t" + CYAN + "time " + _("<sql query>") + RESET 1726 print _("Time request execution time")

1727

1728 - def help_trigger(self):

1729 """online help""" 1730 self._help_for_search_method("trigger")

1731

1732 - def help_user(self):

1733 """online help""" 1734 self._help_for_search_method("user")

1735

1736 - def help_view(self):

1737 """online help""" 1738 self._help_for_search_method("view")

1739

1740 - def help_watch(self):

1741 """online help""" 1742 print _("Usage:") 1743 print "\t" + CYAN + "watch " + _("<n>") + " " + _("<pysql command or sql order>") + RESET 1744 print _("Repeats the command each n seconds") 1745 print _("If n is ommited, repeat each 3 seconds")

1746

1747 - def help_write(self):

1748 """online help""" 1749 print _("Usage:") 1750 print "\t" + CYAN + "write" + RESET 1751 print _("Writes configuration to disk") 1752 print _("Path is $HOME/.pysql/pysqlrc on Unix, %APPDATA%/pysql/pysqrc on Windows") 1753 print _("This command takes no argument")

1754

1755 - def _help_for_search_method(self, searchObject):

1756 """generic online help all object search method""" 1757 print _("Usage:") 1758 print "\t" + CYAN + _("%s <search pattern on %s name>") % (searchObject, searchObject) + RESET 1759 print _("Looks for %s which match the search pattern") % searchObject 1760 print _("Wilcard % and boolean operators (and/or) can be used.") 1761 print _("If a single word and no % is provided, pysql adds a % at the begining and the end") 1762 print _("Ex. : %s FOO or (BAR%% and %%TEST%%)") % searchObject

1763 1764 1765 # Helper functions (private so start with __ to never override any superclass methods) 1766

1767 - def __animateCursor(self):

1768 """Animate cursor to tell user something is really happening 1769 End of animation and output flushing is done automatically in postcmd hook""" 1770 if self.allowAnimatedCursor: 1771 self.waitCursor = WaitCursor() 1772 self.waitCursor.start()

1773

1774 - def __addToCompleteList(self, wordList, theme="general"):

1775 """Adds wordList the completion list "theme" 1776 @param wordList: list of item to completion 1777 @param theme: string theme 1778 @return: None 1779 """ 1780 if not self.conf.completeLists.has_key(theme): 1781 # Creates the theme 1782 self.conf.completeLists[theme] = [] 1783 1784 for word in [unicode(j).upper() for j in wordList]: 1785 if word not in self.conf.completeLists[theme]: 1786 self.conf.completeLists[theme].append(word) 1787 # Keeps completeList small by truncating to the 100 last words 1788 limit = self.conf.get("completionListSize") 1789 self.conf.completeLists[theme] = self.conf.completeLists[theme][-limit:]

1790

1791 - def __getCompletionItems(self, text, themes=["general"], prefix=""):

1792 """Returns list of item matching text for lists of theme 1793 @param text: word to match for completion 1794 @type text: string 1795 @param themes: list of theme of completion to user 1796 @type themes: list of string 1797 @param prefix: text prefix that should be add to completed text 1798 @return:list of string""" 1799 completeList = [] 1800 for theme in themes: 1801 try: 1802 completeList += self.conf.completeLists[theme] 1803 except KeyError: 1804 # Some theme can be undefined. No pb 1805 pass 1806 return [prefix + i for i in completeList if i.startswith(text.upper())]

1807

1808 - def __connect(self, connectString, mode=""):

1809 """Calls the PysqlDb class to connect to Oracle""" 1810 1811 if connectString == "/" and mode == "sysdba": 1812 self.db = PysqlDb("/", "sysdba") 1813 self.__setPrompt() 1814 return 1815 1816 count = connectString.count("@") 1817 if count == 0: 1818 sid = os.environ["ORACLE_SID"] 1819 elif count == 1: 1820 (connectString, sid) = connectString.split("@") 1821 else: 1822 raise PysqlException(_("Invalid connection string")) 1823 1824 count = connectString.count("/") 1825 if count == 0: 1826 user = connectString 1827 try: 1828 passwd = getpass() 1829 except (Exception): 1830 raise PysqlException(_("Invalid connection string")) 1831 elif count == 1: 1832 (user, passwd) = connectString.split("/") 1833 else: 1834 raise PysqlException(_("Invalid connection string")) 1835 1836 connectString = user + "/" + passwd + "@" + sid 1837 self.db = PysqlDb(connectString, mode) 1838 self.__setPrompt()

1839

1840 - def __disconnect(self):

1841 """Disconnects from Oracle and update prompt""" 1842 if self.db: 1843 self.db.close() 1844 self.db = None 1845 self.__setPrompt()

1846

1847 - def __setPrompt(self, blank=False, multiline=False, finishedQuery=False):

1848 """Sets the prompt according to the connexion state 1849 @param blank: if true, no prompt is issue (default is False) 1850 @param finishedQuery: if true mark prompt with a * to notify a query is finished 1851 @type blank: bool 1852 @type finishedQuery: bool""" 1853 #TODO: do not update title for every line 1854 codec = self.conf.getCodec() 1855 if blank or not self.showPrompt: 1856 prompt = "" 1857 elif multiline: 1858 prompt = "" 1859 else: 1860 if self.db is None: 1861 prompt = self.notConnectedPrompt 1862 # Update the title (without color else it is a huge mess) 1863 setTitle(_("Pysql - Not connected"), codec) 1864 else: 1865 if self.db.getDSN() == "None": 1866 prompt = self.db.getConnectString() + " " 1867 else: 1868 prompt = self.db.getUsername() + "@" + self.db.getDSN() + " " 1869 if finishedQuery: 1870 prompt += "* " 1871 setTitle("Pysql - %s" % prompt, codec) 1872 self.prompt = prompt.encode(codec, "replace")

1873

1874 - def __searchObjet(self, objectType, objectName):

1875 """Searches Oracle object""" 1876 self.__checkConnection() 1877 # Try to find owner if a dot is provided 1878 try: 1879 (objectOwner, objectName) = objectName.split(".") 1880 except ValueError: 1881 objectOwner = self.db.getUsername() # Default is current user 1882 objectOwner = objectOwner.upper() 1883 # If no name if given, searches for all 1884 if objectName == "": 1885 objectName = "%" 1886 if self.conf.get("case_sensitive") == "no": 1887 objectName = objectName.upper() 1888 result = pysqlfunctions.searchObject(self.db, objectType, objectName, objectOwner) 1889 for owner in result.keys(): 1890 print GREEN + "***** " + owner + " *****" + RESET 1891 self.__addToCompleteList(result[owner], objectType) 1892 self.__displayCol(result[owner])

1893

1894 - def __displayCol(self, listOfString):

1895 """Displays on column the list of strings""" 1896 # If terminal width is not set, use a default of 120 (should read real term width !) 1897 termWidth = self.conf.get("termWidth") 1898 if termWidth == "auto": 1899 termWidth = getTermWidth() 1900 #BUG: columnize does not support unicode. 1901 listOfString = [i.encode(self.conf.getCodec(), "replace") for i in listOfString] 1902 self.columnize(listOfString, displaywidth=termWidth)

1903

1904 - def __displayTab(self, array, header=None):

1905 """Displays in tabular the array using correct width for each column""" 1906 termWidth = self.conf.get("termWidth") # Terminal maximum width 1907 if termWidth == "auto": 1908 termWidth = getTermWidth() 1909 widthMin = int(self.conf.get("widthMin")) # Minimum size of the column 1910 transpose = (self.conf.get("transpose") == "yes") 1911 colsep = self.conf.get("colsep") 1912 if colsep == "space": 1913 colsep = " " 1914 1915 # Should output be shrinked to fit terminal width? 1916 if header and self.tty: 1917 # Uses configuration value 1918 shrink = (self.conf.get("shrink") == "yes") 1919 else: 1920 # Disables shrinking if isn't a tty 1921 shrink = False 1922 1923 nbLine = len(array) 1924 if len(array) == 0: 1925 print CYAN + _("(no result)") + RESET 1926 return 1927 nbColumn = len(array[0]) # Yes, we suppose it to be a real array 1928 1929 if header: 1930 # Adds description header 1931 array.insert(0, header) 1932 nbLine += 1 1933 1934 if transpose: 1935 # Transposes result! 1936 array = [[array[i][j] for i in range(len(array))] for j in range(nbColumn)] 1937 # Computes new nbColumn & nbLine 1938 nbLine = nbColumn 1939 nbColumn = len(array[0]) 1940 1941 # Convert None to NULL 1942 for i in xrange(nbLine): 1943 for j in xrange(nbColumn): 1944 if array[i][j] is None: 1945 #array[i][j] = "NULL" 1946 array[i][j] = "" 1947 1948 # Computes width max of each column (comprehension list are cool) 1949 width = [max([itemLength(i[j]) for i in array]) for j in range(nbColumn)] 1950 shrinked = False # have we shrinked the result set ? 1951 widthMax = max(width) 1952 if shrink: 1953 while sum(width) + nbColumn >= termWidth and widthMax > widthMin: 1954 # Result set too large, need to shrink a little 1955 # Shrinking the bigger by 1 character 1956 shrinked = True 1957 widthMax = max(width) 1958 width[width.index(widthMax)] = widthMax - 1 1959 1960 # If header, add pretty line just above 1961 if header and not transpose: 1962 array.insert(1, ["-"*width[i] for i in range(nbColumn)]) 1963 1964 # Goes for printing 1965 coloredLines = 0 1966 for line in array: 1967 if header and coloredLines < 2 and not transpose: 1968 # Colorizes header only 1969 sys.stdout.write(GREY + BOLD) 1970 coloredLines += 1 1971 for i in range(nbColumn): 1972 if header and i == 0 and transpose: 1973 # colorize the first column 1974 sys.stdout.write(GREY + BOLD) 1975 # Quite stupid to test this for each line... 1976 #TODO: Should be done one time before looping on each line 1977 if isinstance(line[i], (int, long, float)): 1978 sys.stdout.write(str(line[i])[:width[i]].rjust(width[i])) 1979 else: 1980 sys.stdout.write(line[i][:width[i]].ljust(width[i]).replace('\r', ' ')) 1981 if header and i == 0 and transpose: 1982 print RESET, 1983 sys.stdout.write(colsep) # Adds colsep 1984 print RESET 1985 if shrinked: 1986 # Warns the user 1987 print CYAN + _("(some columns have been shrinked to fit your terminal size)") + RESET

1988

1989 - def __checkConnection(self):

1990 """Raises an exception is there's no connection defined 1991 The test is light (db object defined), no real connection test 1992 is done.""" 1993 if self.db is None: 1994 raise PysqlException(_("Not connected to Oracle"))

1995

1996 - def __checkArg(self, arg, argTest):

1997 """Checks if arg respect argTest else raise a PysqlException 1998 @param arg: argument to check. Blank is the arg separator 1999 @type arg: str or list of str 2000 @param argTest: test with syntaxe like: ">2", "==1", "<=3" 2001 @type argTest: str 2002 @return: None 2003 """ 2004 #TODO: move this to helpers 2005 if match("=\d+", argTest): 2006 # Bouh, replace the single = by == 2007 argTest = "=" + argTest 2008 if isinstance(arg, basestring): 2009 arg = arg.split() 2010 try: 2011 if not eval(unicode(len(arg)) + argTest): 2012 raise PysqlException(_("Invalid argument. Use help <command name> for usage")) 2013 except SyntaxError, e: 2014 raise PysqlException(_("Invalid syntax for argument checking"))

2015

2016 - def __executeSQL(self, sql, output="tty", fileName="pysql.csv"):

2017 """Executes SQL request 2018 @param sql: SQL request to executed 2019 @type sql: str 2020 @param output: output type. Only affect select queries. Null means all result are sent to paradise 2021 @type output: str (screen, csv, xml or null) 2022 @param fileName: name of the file for csv and xml extract 2023 @type fileName: str""" 2024 2025 self.__checkConnection() 2026 if len(sql) < 2: 2027 raise PysqlException(_("SQL command is too short")) 2028 2029 self.__animateCursor() 2030 2031 # Saves it for further editing (with edit command for example) or recall with / 2032 self.lastStatement = sql 2033 2034 # Background query? 2035 if sql[-1] == "&": 2036 sql = sql.rstrip("&") 2037 query = BgQuery(self.db.getConnectString(), sql, self.exceptions) 2038 query.start() 2039 self.bgQueries.append(query) 2040 print GREEN + _("Background query launched") + RESET 2041 return 2042 2043 # Choosing command with the first keyword 2044 keyword = sql.upper().split()[0] 2045 if keyword.startswith("SELECT"): 2046 if output == "tty": 2047 (result, moreRows) = self.db.execute(sql) 2048 self.__toScreen(result, moreRows) 2049 elif output == "notty": 2050 (result, moreRows) = self.db.execute(sql, fetch=False) 2051 self.__toScreen(result, False) 2052 elif output == "csv": 2053 (result, moreRows) = self.db.execute(sql, fetch=False) 2054 self.__toCsv(result, fileName) 2055 print GREEN + _("(Completed)") + RESET 2056 elif output == "xml": 2057 raise PysqlNotImplemented() 2058 elif output == "null": 2059 self.db.execute(sql, fetch=False) 2060 for i in self.db.getCursor().fetchmany(): 2061 pass 2062 else: 2063 raise PysqlException(_("Unknown output type!")) 2064 elif keyword.startswith("INSERT"): 2065 lines = self.db.execute(sql) 2066 print GREEN + unicode(lines) + _(" line(s) inserted") + RESET 2067 elif keyword.startswith("UPDATE"): 2068 lines = self.db.execute(sql) 2069 print GREEN + unicode(lines) + _(" line(s) updated") + RESET 2070 elif keyword.startswith("DELETE"): 2071 lines = self.db.execute(sql) 2072 print GREEN + unicode(lines) + _(" line(s) deleted") + RESET 2073 elif (keyword.startswith("DROP") 2074 or keyword.startswith("CREATE") 2075 or keyword.startswith("TRUNCATE") 2076 or keyword.startswith("ALTER") 2077 or keyword.startswith("ANALYZE") 2078 or keyword.startswith("BEGIN") 2079 or keyword.startswith("DECLARE") 2080 or keyword.startswith("COMMENT") 2081 or keyword.startswith("EXECUTE") 2082 or keyword.startswith("GRANT") 2083 or keyword.startswith("REVOKE")): 2084 self.db.execute(sql) 2085 print GREEN + _("Statement executed") + RESET 2086 result = self.db.getServerOuput() 2087 # Print the ouput (if exist) 2088 for line in result: 2089 print line 2090 else: 2091 print RED + BOLD + _("""Unknown command or sql order. Type "help" for help""") + RESET

2092

2093 - def __toScreen(self, result, moreRows, header=True):

2094 """Displays first part of fetch on screen 2095 @param result: array of tabular data 2096 @type result: list of list of str 2097 @param moreRows: indicates if there's more data to fetching 2098 @type moreRows: bool 2099 @param header: indicates if header must be displayed or not 2100 @type header: bool 2101 """ 2102 if result: 2103 if header: 2104 self.__displayTab(result, self.db.getDescription()) 2105 else: 2106 self.__displayTab(result) 2107 else: 2108 print CYAN + _("(no result)") + RESET 2109 2110 if moreRows: 2111 self.fetching = True 2112 print CYAN + _("(press enter to see next results)") + RESET 2113 else: 2114 self.fetching = False

2115

2116 - def __toCsv(self, result, fileName, header=True):

2117 """Writes query result to a file""" 2118 try: 2119 fileHandle = file(fileName, "w") 2120 csv_writer = csv.writer(fileHandle, dialect="excel") 2121 if header: 2122 csv_writer.writerow(self.db.getDescription()) # Header 2123 for line in result: 2124 csv_writer.writerow(line) 2125 except Exception, e: 2126 raise PysqlException(e) 2127 fileHandle.close()

2128

2129 - def __fetchNext(self, nbLines=0):

2130 """ Fetches next result of current cursor""" 2131 (result, moreRows) = self.db.fetchNext(nbLines) 2132 self.__toScreen(result, moreRows)

2133

2134 - def __exit(self):

2135 """ Closes current connection and exits pysql""" 2136 if len(self.exceptions) > 0: 2137 print CYAN + "******* " + _("Error sum up") + " *******" + RESET 2138 errors = [(e.getTimeStamp(), e.msg.replace("\n", " "), e.oraCode) for e in self.exceptions] 2139 self.__displayTab(errors, (_("Date"), _("Error message"), _("Oracle error Code"))) 2140 2141 if self.showBanner: 2142 print CYAN + "\n\n" + _("Bye !") + "\n" + RESET 2143 2144 rc = 0 2145 # Flushes completion cache to disk 2146 try: 2147 self.conf.writeCache() 2148 except PysqlException, e: 2149 print e 2150 rc = 1 2151 # Flushes history to disk 2152 try: 2153 self.conf.writeHistory() 2154 except PysqlException, e: 2155 print e 2156 rc = 1 2157 # Flushes sql library to disk 2158 try: 2159 self.conf.writeSqlLibrary() 2160 except PysqlException, e: 2161 print e 2162 rc = 1 2163 try: 2164 self.__disconnect() 2165 except PysqlException, e: 2166 print e 2167 rc = 1 2168 2169 self.rc = rc 2170 return True

2171

2172 - def __askForSnapshotId(self, numDays=0, text=""):

2173 """ Prompts user and asks him to choose a snapshot id""" 2174 if numDays == 0: 2175 try: 2176 answer = raw_input(CYAN + _("Specify the number of days of snapshots to choose from: ") + RESET) 2177 numDays = int(answer) 2178 except (SyntaxError, ValueError), e: 2179 raise PysqlException(_("Invalid number of days")) 2180 if numDays <= 0: 2181 raise PysqlException(_("Invalid number of days")) 2182 result = pysqlaudit.listSnapshotId(self.db, numDays) 2183 self.__toScreen(result, False) 2184 try: 2185 answer = raw_input(CYAN + _("Enter %s snapshot identifier: ") % text + RESET) 2186 return (numDays, str(int(answer))) 2187 except (SyntaxError, ValueError), e: 2188 raise PysqlException(_("Invalid snaphsot id"))

2189 2190 # Complete functions aliases 2191 complete_conn = complete_connect 2192 complete_lib = complete_library 2193 2194 # Functions aliases 2195 aliases = { 2196 "addm" : "addmrpt", 2197 "awr" : "awrrpt", 2198 "conn" : "connect", 2199 "dep" : "dependencies", 2200 "desc" : "describe", 2201 "disc" : "disconnect", 2202 "du" : "diskusage", 2203 "ed" : "edit", 2204 "exec" : "execute", 2205 "h" : "history", 2206 "lib" : "library", 2207 "start": "script", 2208 "q" : "exit", 2209 "quit" : "exit" 2210 } 2211

Source Code for Module pysql.pysqlshell