pysql.pysqlhelpers

1 #!/usr/bin/python 2 # -*- coding: utf-8 -*- 3 4 """This module defines some common useful helper functions 5 @author: Sébastien Renard (sebastien.renard@digitalfox.org) 6 @license: GNU GPL V3 7 """ 8 9 # Python imports: 10 import os 11 import sys 12 import traceback 13 from re import match, sub 14 import datetime 15 from cx_Oracle import LOB 16 from cStringIO import StringIO 17 from time import sleep 18 from threading import Thread, Lock 19 20 # Pysql imports: 21 from pysqlexception import PysqlException, PysqlActionDenied 22 from pysqlcolor import BOLD, CYAN, GREEN, GREY, RED, RESET 23 import pysqlupdate 24 25 # Help functions

26 -def addWildCardIfNeeded(aString, wildcard="%"):

27 """If aString does not have any wildcard, add one at the begining and one at the end""" 28 if not aString.count(wildcard): 29 return wildcard + aString + wildcard 30 else: 31 return aString

32

33 -def colorDiff(diff):

34 """Transforms poorly readable ndiff output into colored diff 35 @arg diff: generator returned by difflib.ndiff() function 36 @return: list of string diffs with color to highlight diff 37 """ 38 result = [] 39 for item in diff: 40 if item[0] == "?": # Get diff hint to colorise the previous line 41 previous = result[-1] # The line above we want to colorise 42 i = 0 43 coloring = False # flag to start/stop coloring 44 newPrevious = [] # the new previous line with color 45 for character in item: 46 if character == "\n": 47 # End of change. Add end of previous 48 newPrevious.append(previous[i:]) 49 else: 50 if character in ("-", "^", "+"): 51 if not coloring: 52 newPrevious.append(RED) 53 coloring = True 54 else: 55 if coloring: 56 newPrevious.append(RESET) 57 coloring = False 58 newPrevious.append(previous[i]) 59 i += 1 60 newPrevious.append(RESET) # Always stop color at end of line 61 result[-1] = "".join(newPrevious) # Create new colorize line 62 else: 63 result.append(item) # Just store simple line (equal, + or -) 64 return result

65 66

67 -def convert(value, unit):

68 """Converts an number in Bytes into a bigger unit. 69 Beware of any data loss""" 70 unit = unit.lower() 71 if unit == "b": 72 return value 73 elif unit == "kb": 74 return float(value) / 1024 75 elif unit == "mb": 76 return float(value) / 1024 / 1024 77 elif unit == "gb": 78 return float(value) / 1024 / 1024 / 1024 79 elif unit == "tb": 80 return float(value) / 1024 / 1024 / 1024 / 1024 81 elif unit == "pb": 82 return float(value) / 1024 / 1024 / 1024 / 1024 / 1024 83 else: 84 raise PysqlException(_("Unit not handled: %s") % unit)

85

86 -def getProg(availables, given, default):

87 """Checks if 'given' graphviz program is in the 'availables' list 88 Raises an exception if program is not available or return the prog (default if given is 'auto' 89 @arg availables: dict of graphviz availables program as return by find_graphivz() 90 @arg given: program choose by user 91 @arg default: program used if user choose default""" 92 if given == "auto": 93 given = default 94 if not availables.has_key(given): 95 raise PysqlActionDenied(given + 96 _(" is not installed. Get it at http://www.graphviz.org/Download.php")) 97 else: 98 return given

99

100 -def itemLength(item):

101 """Compute length of a result set item""" 102 if item is None: 103 return 0 104 elif isinstance(item, (int, float, long, datetime.datetime)): 105 return len(str(item)) 106 elif isinstance(item, LOB): 107 return item.size() 108 else: 109 return len(item)

110

111 -def generateWhere(keyword, filterClause):

112 """ Generate where clause from pysql syntax to filter Oracle object 113 Pysql syntax : pattern1 or (pattern2 and pattern3). Pattern are all accepted Oracle like pattern 114 @arg filter: pysql where clause syntax as a list of words 115 @arg keyword: the database object name on which filter apply 116 @return: SQL where clause""" 117 result = [] 118 endingParenthisis = 0 119 startsWithParenthisis = True 120 lastWordWasOperand = True 121 parenthisisBalance = 0 122 for word in filterClause.split(): 123 # Keep and remove start & end parenthisis 124 while word.startswith("("): 125 parenthisisBalance += 1 126 startsWithParenthisis = True 127 result.append("(") 128 word = word[1:] 129 while word.endswith(")"): 130 parenthisisBalance -= 1 131 endingParenthisis += 1 132 word = word[:-1] 133 # Handle boolean operator 134 if word.lower() in ("and", "or"): 135 if startsWithParenthisis or lastWordWasOperand: 136 # Operator at begin of phrase (just after parenthisis) 137 # Or two operators following 138 raise PysqlException(_("Operator %s was not expected at word %s") % (word.upper(), len(result) + 1)) 139 result.append(word.lower()) 140 lastWordWasOperand = True 141 startsWithParenthisis = False 142 # Construct like clause 143 elif len(word) > 0 and lastWordWasOperand: 144 if word.startswith("!"): 145 if len(word) > 1 and word[1] == "(": 146 raise PysqlException(_("The ! is not supported before parenthisis")) 147 operand = "not like" 148 word = word[1:] 149 else: 150 operand = "like" 151 lastWordWasOperand = False 152 startsWithParenthisis = False 153 result.append("%s %s '%s'" % (keyword, operand, word)) 154 elif len(word) > 0 and not lastWordWasOperand: 155 # Terms of clause must be separted by operators 156 raise PysqlException(_("Operator (AND/OR) expected at word %s") % (len(result) + 1)) 157 while endingParenthisis > 0: 158 endingParenthisis -= 1 159 result.append(")") 160 if parenthisisBalance != 0: 161 raise PysqlException(_("Unblanced parenthisis (%s)") % parenthisisBalance) 162 return " ".join(result)

163

164 -def removeComment(line, comment=False):

165 """Removes SQL comments from line 166 @arg line: SQL line from which we want to remove comment 167 @arg comment: flag to indicate if we are in the middle of a multiline comment (default is false) 168 @type line: str 169 @type comment: bool 170 @return: line modified (str) and a flag (bool) that indicate if we are in a multiline comment""" 171 # Remove one line comment (-- or /* */) 172 line = sub("\/\*\*\/", " ", line) # Remove /**/ pattern 173 line = sub("\/\*[^+|].*?\*\/", " ", line) # Remove /* ... */ except /*+ ... */ 174 line = sub("--[^+|].*$", "", line) # Remove -- ... except --+ ... 175 line = sub("--$", "", line) # Remove -- at the end of line (stupid but allowed) 176 177 if line == "--": 178 return "", comment 179 180 # Honors multi line SQL comments but do not shoot Oracle hint! 181 # /* comment 182 if match(".*/\*[^+].*", line) or match(".*/\*$", line): 183 # Remove commented part 184 line = sub("/\*[^+|].*", "", line) 185 line = sub("/\*$", "", line) # previous regexp does not match */ at end of line 186 # Starting multiline comment 187 comment = True 188 # comment */ (a /* was give before) 189 elif match(".*\*\/.*", line) and comment: 190 # Remove commented part 191 line = sub(".*\*\/", "", line) 192 # end for multiline comment 193 comment = False 194 elif comment: 195 line = "" 196 return (line, comment)

197

198 -def which(progName):

199 """Mimics the Unix which command 200 @param progName: program name that will be search through PATH 201 @return: full path to program or None if not find in PATH""" 202 for directory in os.getenv("PATH").split(os.pathsep): 203 fullpath = os.path.join(directory, progName) 204 if os.access(fullpath, os.X_OK) and os.path.isfile(fullpath): 205 return fullpath 206 return None

207

208 -def warn(message):

209 """Just print a formated warning message on screen if PYSQL_WARNING env var is set (whatever value) 210 @param message: unicode or str message. Conversion will done with print and default encoding 211 """ 212 if os.environ.has_key("PYSQL_WARNING"): 213 print "%s==>Warning:%s %s%s" % (RED, BOLD, message, RESET)

214

215 -def printStackTrace():

216 """Print stack trace with debug information""" 217 # Just a hook for a more pleasant error handling 218 print "------------------8<-------------------------------------" 219 traceback.print_exc() 220 print "------------------8<-------------------------------------" 221 try: 222 pysqlVersion = pysqlupdate.currentVersion() 223 except PysqlException: 224 pysqlVersion = "unknown" 225 try: 226 import cx_Oracle 227 cxVersion = cx_Oracle.version 228 except Exception: 229 cxVersion = "unknown" 230 print "Pysql release: %s" % pysqlVersion 231 print "cx Oracle release: %s" % cxVersion 232 print "Python release: %s" % sys.version.replace("\n", " ") 233 print 234 print RED + BOLD + "Please, send the text above to pysql@digitalfox.org" + RESET 235 print

236

237 -def setTitle(title, codec):

238 """Sets the window title 239 @param title: window title 240 @type title: unicode string 241 @param codec: codec used to encode string""" 242 if os.name == 'posix' and os.environ["TERM"] == 'xterm' and os.getenv("PYDEVDEBUG", "0") == "0": 243 title = "\033]0;%s\007" % title 244 sys.stdout.write(title.encode(codec, "replace")) 245 elif os.name == "nt": 246 os.system("title %s" % title.encode(codec, "replace"))

247

248 -def getTitle():

249 """Gets the window title 250 @return: str""" 251 if os.name == "posix": 252 # Getting terminal title is.. a mess 253 # An escape code (echo -e '\e[21t') can get it 254 # but is often disabled for safety reason... 255 # Default to basic title 256 title = "%s@%s" % (os.environ.get("USER", "user"), os.popen("hostname").readline().strip()) 257 if os.environ.has_key("DISPLAY"): 258 # Use X windows to get title 259 xtitle = os.popen("xprop -id $WINDOWID WM_NAME").readline().strip() 260 if not ("WM_NAMEAborted" in title or "WM_NAMEAbandon" in title): 261 try: 262 title = xtitle.split("=")[1].lstrip(' ').strip('"') 263 except IndexError: 264 # DISPLAY is not correctly set 265 pass 266 elif os.name == "nt": 267 # Term title need pywin32 on windows... Too much deps for simple need 268 # Using default name instead 269 title = os.environ["ComSpec"] 270 else: 271 # Unknown OS 272 title = "terminal" 273 return title

274

275 -def getTermWidth():

276 """Gets the terminal width. Works only on Unix system. 277 @return: terminal width or "120" is system not supported""" 278 if os.name == "posix": 279 result = os.popen("tput cols").readline().strip() 280 if result: 281 return int(result) 282 else: 283 # Unsupported system, use default 120 284 return 120

285

286 -def upperIfNoQuotes(aString):

287 """Used for Oracle owner and name case policy 288 @param aString: input string to parse 289 @return: Upper case string if no quotes are given, else remove quotes and leave string as is""" 290 if aString.startswith("'") or aString.startswith('"'): 291 return aString.strip("'").strip('"') 292 else: 293 return aString.upper()

294

295 -class WaitCursor(Thread):

296 """A waiting cursor for long operation that 297 catch output and flush it after waiting"""

298 - def __init__(self):

299 self.state = "WAIT" 300 self.lock = Lock() # Lock used to synchronise IO and cursor stop 301 Thread.__init__(self)

302

303 - def run(self):

304 """Method executed when the thread object start() method is called""" 305 306 realStdout = sys.stdout # Backup stdout 307 tmpStdout = StringIO() # Store here all data output during waiting state 308 sys.stdout = tmpStdout # Capture stdout 309 cursorState = ("-", "\\", "|", "/") 310 i = 0 311 self.lock.acquire() 312 while self.state == "WAIT": 313 realStdout.write(cursorState[i % 4]) 314 realStdout.flush() 315 sleep(0.1) 316 realStdout.write("\b") 317 i += 1 318 319 # Restore standard output and print temp data 320 sys.stdout = realStdout 321 sys.stdout.write(tmpStdout.getvalue()) 322 sys.stdout.flush() 323 self.lock.release()

324

325 - def stop(self):

326 self.state = "STOP" 327 self.lock.acquire() # Wait end of IO flush before returning

328

Source Code for Module pysql.pysqlhelpers