pysql.pysqlfunctions

1 #!/usr/bin/python 2 # -*- coding: utf-8 -*- 3 4 """ This module defines all high level functions of 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 # pylint: disable-msg=E1101 11 12 # Python imports: 13 import os, re 14 from os import getenv, unlink 15 from difflib import ndiff 16 try: 17 from hashlib import md5 18 except ImportError: 19 # before python 2.5 hashlib did not exist 20 from md5 import md5 21 22 # Pysql imports: 23 from pysqlqueries import * 24 from pysqlexception import PysqlException, PysqlNotImplemented, PysqlActionDenied 25 from pysqloraobjects import * 26 from pysqlcolor import * 27 from pysqlconf import PysqlConf 28 from pysqldb import PysqlDb 29 from pysqlhelpers import colorDiff, convert, addWildCardIfNeeded, generateWhere 30 31 # High level pysql functions

32 -def count(db, objectName):

33 """Counts rows in a table 34 @arg objectName: table/view/M.View name 35 @arg db: connection object 36 @return: number of rows (int)""" 37 # Gets the object type and owner 38 oraObject = OraObject(objectName=objectName) 39 oraObject.guessInfos(db) 40 # Tries to resolve synonym and describe the target 41 if oraObject.getType() == "SYNONYM": 42 oraObject = oraObject.getTarget(db) 43 if oraObject.getType() == "SYNONYM": 44 # cannot desc, too much synonym recursion 45 raise PysqlException(_("Too much synonym recursion")) 46 if oraObject.getType() not in ("TABLE", "VIEW", "MATERIALIZED VIEW"): 47 raise PysqlException(_("Cannot count rows of such object : %s") % oraObject.getType()) 48 return oraObject.getRowCount(db)

49

50 -def compare(schemaA, schemaB):

51 """Compares two Oracle schema and return the difference""" 52 # First, compare list of tables 53 tables = {} # Store list of schema tables (key is schema) 54 dbList = {} # Store list of connect object to schema (key is schema) 55 inAnotInB = [] # List of tables found in schema A but no present in schema B 56 inBnotInA = [] # List of tables found in schema B but no present in schema A 57 inAandB = [] # List of tables found in both schema A and schema B 58 diffForAandB = {} # Store common tables diff (key is table name) 59 60 for schema in (schemaA, schemaB): 61 dbList[schema] = PysqlDb(schema) 62 keyword = searchObjectSql["table"][1] 63 whereClause = """%s like '%%'""" % keyword 64 sql = searchObjectSql["table"][0] % ( 65 whereClause, schema.split("/")[0].upper(), keyword) 66 result = dbList[schema].executeAll(sql) 67 tables[schema] = [i[1] for i in result] 68 69 for item in list(ndiff(tables[schemaA], tables[schemaB])): 70 if item[0] == " ": 71 inAandB.append(item[2:]) 72 elif item[0] == "-": 73 inAnotInB.append(item[2:]) 74 item = "=" + item[1:] 75 elif item[0] == "+": 76 inBnotInA.append(item[2:]) 77 elif item[0] == "?": 78 pass # diff helper control caracter to detail previous line. 79 else: 80 raise PysqlException(_("unknown diff control caracter (%s)") % item[0]) 81 # Compare tables found in both schema A and schema B 82 for tableName in inAandB: 83 diffForAandB[tableName] = compareTables(schemaA, schemaB, tableName, tableName, dbList, data=False) 84 return (inAnotInB, inBnotInA, diffForAandB)

85

86 -def compareTables(schemaA, schemaB, tableNameA, tableNameB, dbList=None, data=False):

87 """ 88 Compares structure or data of tableA from schemaA with tableB from schemaB 89 This is a wrapper to either compareTableStructure or compareTableData. 90 @arg schemaA: connection string to the schema A 91 @arg schemaB: connection string to the schema B 92 @arg tableNameA: name of the table in schema A 93 @arg tableNameB: name of the table in schema B 94 @dbList: hash list of PysqlDb object (keys are A & B). If None, new connections are opened. 95 @arg data: if true, compare data else, compare structure 96 """ 97 if dbList: 98 # Convert schema name to anonymous A & B to avoid problem when schema are equal 99 dbList["A"] = dbList[schemaA] 100 dbList["B"] = dbList[schemaB] 101 else: 102 dbList = {} # Create new hash to store list of connect object to schema (key is schema) 103 dbList["A"] = PysqlDb(schemaA) 104 dbList["B"] = PysqlDb(schemaB) 105 106 if data: 107 return compareTableData(schemaA, schemaB, tableNameA, tableNameB, dbList) 108 else: 109 return compareTableStructure(schemaA, schemaB, tableNameA, tableNameB, dbList)

110

111 -def compareTableStructure(schemaA, schemaB, tableNameA, tableNameB, dbList):

112 """ 113 Compares structure of tableA from schemaA with tableB from schemaB 114 @arg schemaA: connection string to the schema A 115 @arg schemaB: connection string to the schema B 116 @tableNameA: name of the table in schema A 117 @tableNameB: name of the table in schema B 118 @dbList: hash list of PysqlDb object (keys are A & B). 119 """ 120 tableDesc = {} # Store the current table desc for each schema (key is schema) 121 for schema, tableName in (("A", tableNameA), ("B", tableNameB)): 122 #BUG: format is ugly. use/merge with __displayTab algo ?? 123 tableDesc[schema] = [" ".join([str(i) for i in line]) 124 for line in desc(dbList[schema], tableName, None, False)[1]] 125 if not tableDesc[schema]: 126 raise PysqlException(_("Could not find table %s") % tableName) 127 128 if tableDesc["A"] == tableDesc["B"]: 129 result = None 130 else: 131 result = colorDiff(ndiff(tableDesc["A"], tableDesc["B"])) 132 return result

133

134 -def compareTableData(schemaA, schemaB, tableNameA, tableNameB, dbList):

135 """ 136 Compares data of tableA from schemaA with tableB from schemaB 137 @arg schemaA: connection string to the schema A 138 @arg schemaB: connection string to the schema B 139 @tableNameA: name of the table in schema A 140 @tableNameB: name of the table in schema B 141 @dbList: hash list of PysqlDb object (keys are A & B). 142 """ 143 # Check that table structure (columns names & type) are similar 144 tableStruct = {} # Store table structure (columns names & tupe) for each schema (key is schema) 145 tablePK = {} # Store table primary key list for each schema (key is schema) 146 tableNCol = {} # Store table number of column for each schema (key is schema) 147 for schema, tableName in (("A", tableNameA), ("B", tableNameB)): 148 table = OraObject(dbList[schema].getUsername(), tableName) 149 table.guessInfos(dbList[schema]) 150 if table.getType() == "TABLE": 151 # Get PK and number of columns 152 tablePK[schema] = table.getPrimaryKeys(dbList[schema]) 153 tableNCol[schema] = table.getNumberOfColumns(dbList[schema]) 154 # Get only column name (0) and column type (1) 155 tableStruct[schema] = [[i[0], i[1]] for i in table.getTableColumns(dbList[schema])] 156 else: 157 raise PysqlException(_("%s does not seem to be a table in %s") % 158 (tableName, dbList[schema].getConnectString())) 159 160 if tableStruct["A"] != tableStruct["B"]: 161 raise PysqlException( 162 _("Unable to compare data of tables that do not have same structure (columns name and type)")) 163 164 if tablePK["A"] == tablePK["B"] and tablePK["A"]: # identical and not None 165 order = "order by %s" % (", ".join(tablePK["A"])) 166 else: 167 order = "order by %s" % ", ".join(str(i + 1) for i in range(tableNCol["A"])) 168 for schema, tableName in (("A", tableNameA), ("B", tableNameB)): 169 # test cursor size. Should make a quick bench to choose the good one 170 dbList[schema].execute("select * from %s %s" % (tableName, order), fetch=False, cursorSize=10000) 171 result = {} # Store current fecth. Key is A or B 172 moreRows = {} # Flag to indicate there's more rows in cursor. Key is A or B 173 moreRows["A"] = True 174 moreRows["B"] = True 175 diff = [] # Store diff lines in this list 176 while moreRows["A"] and moreRows["B"]: 177 for schema in ("A", "B"): 178 result[schema], moreRows[schema] = dbList[schema].fetchNext() 179 if result[schema]: 180 #TODO: performance of this part is very very bad 181 result[schema] = [" ".join([str(i) for i in line]) 182 for line in result[schema]] 183 for line in colorDiff(ndiff(result["A"], result["B"])): 184 if line[0] != " ": 185 if diff and line[2:] == diff[-1][2:]: 186 diff.pop() # simple double removing for one line decay only 187 else: 188 diff.append(line) 189 for sign, schema in (("-", "A"), ("+", "B")): 190 while moreRows[schema]: 191 result[schema], moreRows[schema] = dbList[schema].fetchNext() 192 result[schema] = [" ".join([str(i) for i in line]) 193 for line in result[schema]] # This code should be factorised with above 194 diff.append("%s %s" % (sign, result[schema])) 195 # Make a second pass to remove doublon accross two resultset 196 #BUG: does not work in all case 197 oldSign = "" 198 newSign = "" 199 oldBuffer = [] 200 newBuffer = [] 201 newBlock = True # Flag to indicate we have to start a new matching block 202 i = 0 203 diff.append(" ") # Add a mark to allow final lines processing 204 toBeRemoved = [] # List of item index to be removed 205 for line in diff: 206 newSign = line[0] 207 if oldSign == newSign or newBlock: 208 # Append to new Buffer 209 newBuffer.append(line[2:]) 210 newBlock = False 211 else: 212 if newBuffer == oldBuffer: 213 # Detect doublons 214 for j in range(len(newBuffer) * 2): 215 toBeRemoved.append(i - j - 1) 216 newBlock = True 217 # Changing to next block 218 oldBuffer = newBuffer 219 newBuffer = [line[2:]] 220 oldSign = newSign 221 i += 1 222 diff = [diff[i] for i in xrange(len(diff) - 1) if i not in toBeRemoved] 223 return diff

224

225 -def ddl(db, objectName):

226 """Gets the ddl of an object 227 @return: ddl as string""" 228 oraObject = OraObject(objectName=objectName) 229 oraObject.guessInfos(db) 230 if oraObject.getType() == "": 231 return None 232 else: 233 return oraObject.getDDL(db)

234

235 -def desc(db, objectName, completeMethod=None, printDetails=True, printStats=False, sort=False):

236 """Describes an object 237 @param objectName: object to be described 238 @return: header and resultset of definition as a tuple (header, definition) 239 ==> This function should be split in two parts: one in pysqlOraObjects for object self description 240 the other one here as a describe function that encapsulate pysqlOraObjects manipulation""" 241 242 header = [] 243 result = [] 244 245 # Reads conf 246 conf = PysqlConf.getConfig() 247 unit = conf.get("unit") # Unit used to format data 248 249 # Look for object type if given 250 matchResult = re.match("(.*) $(.+)$", objectName) 251 if matchResult: 252 oraObject = OraObject(objectName=matchResult.group(1), 253 objectType=matchResult.group(2)) 254 else: 255 oraObject = OraObject(objectName=objectName) 256 257 # Gets the object type and owner 258 oraObjectSet = oraObject.guessInfos(db, interactive=True) 259 260 261 if len(oraObjectSet) == 1: 262 oraObject = oraObjectSet.pop() 263 elif len(oraObjectSet) > 1: 264 print CYAN + _("Got multiple result:") + "\n-" + RESET, 265 print "\n- ".join([str(x) for x in oraObjectSet]) 266 # Looking for own object 267 ownOraObjects = [o for o in oraObjectSet if o.getOwner() == db.getUsername().upper()] 268 publicOraObjects = [o for o in oraObjectSet if o.getOwner() == "PUBLIC"] 269 if len(ownOraObjects) == 1: 270 oraObject = ownOraObjects.pop() 271 print BOLD + RED + _("Defaulting to own object: %s") % oraObject + RESET 272 # Looking for public objects 273 elif len(publicOraObjects) == 1: 274 oraObject = publicOraObjects.pop() 275 print BOLD + RED + _("Defaulting to public object: %s") % oraObject + RESET 276 else: 277 # No result 278 return ([], []) 279 280 # Object or type unknown? 281 if oraObject.getType() == "": 282 return ([], []) 283 284 # Tries to resolve synonym and describe the target 285 if oraObject.getType() == "SYNONYM": 286 oraObject = oraObject.getTarget(db) 287 if oraObject.getType() == "SYNONYM": 288 # cannot desc, too much synonym recursion 289 return ([], []) 290 291 # Guess object status 292 oraObject.guessStatus(db) 293 294 # Displays some information about the object 295 if printDetails: 296 print CYAN + _("Name") + "\t: " + oraObject.getName() + RESET 297 print CYAN + _("Type") + "\t: " + oraObject.getType() + RESET 298 print CYAN + _("Owner") + "\t: " + oraObject.getOwner() + RESET 299 if oraObject.getStatus() in ("INVALID", "OFFLINE", "UNUSED"): 300 print CYAN + _("Status") + "\t\t: " + BOLD + RED + oraObject.getStatus() + RESET 301 else: 302 print CYAN + _("Status") + "\t\t: " + oraObject.getStatus() + RESET 303 if oraObject.getType() in ("TABLE", "TABLE PARTITION", "VIEW", "MATERIALIZED VIEW"): 304 try: 305 print CYAN + _("Comment") + "\t: " + oraObject.getComment(db) + RESET 306 except PysqlException: 307 print CYAN + _("Comment") + "\t: " + _("<unable to get comment>") + RESET 308 if oraObject.getType() not in ("DATA FILE", "TABLESPACE", "USER"): 309 try: 310 print CYAN + _("Created on") + "\t: " + oraObject.getCreated(db) + RESET 311 except PysqlException: 312 print CYAN + _("Created on") + "\t: " + _("<unable to get date of creation>") + RESET 313 try: 314 print CYAN + _("Last DDL on") + "\t: " + oraObject.getLastDDL(db) + RESET 315 except PysqlException: 316 print CYAN + _("Last DDL on") + "\t: " + _("<unable to get date of last DDL modification>") + RESET 317 318 # Displays some statistics about the object 319 if printStats: 320 if oraObject.getType() in ("TABLE", "TABLE PARTITION"): 321 try: 322 print ORANGE + _("Last analyzed on") + ": " + str(oraObject.getLastAnalyzed(db)) + RESET 323 except PysqlException: 324 print CYAN + _("Last analyzed on") + "\t: " + _("<unable to get date of last statistics computation>") + RESET 325 try: 326 print ORANGE + _("Nb rows") + "\t\t: " + str(oraObject.getNumRows(db)) + RESET 327 except PysqlException: 328 print CYAN + _("Nb rows") + "\t: " + _("<unable to get number of rows>") + RESET 329 try: 330 print ORANGE + _("Nb used blocks") + "\t: " + str(oraObject.getUsedBlocks(db)) + RESET 331 except PysqlException: 332 print ORANGE + _("Nb used blocks") + "\t: " + _("<unable to get number of used blocks>") + RESET 333 try: 334 print ORANGE + _("Avg row length") + "\t: " + str(oraObject.getAvgRowLength(db)) + RESET 335 except PysqlException: 336 print CYAN + _("Avg row length") + "\t: " + _("<unable to get average row length") + RESET 337 338 # Evaluates object type (among the 24 defined) 339 if oraObject.getType() in ("TABLE" , "TABLE PARTITION"): 340 header = [_("Name"), _("Type"), _("Null?"), _("Comments"), _("Indexes")] 341 columns = oraObject.getTableColumns(db, sort) 342 343 # Gets indexed columns of the table 344 indexedColumns = oraObject.getIndexedColumns(db) 345 # Format index this way: index_name(index_position) 346 #TODO: handle database encoding instead of using just str() 347 indexedColumns = [[i[0], i[1] + "(" + str(i[2]) + ")"] for i in indexedColumns] 348 for column in columns: 349 column = list(column) # change tuple to list 350 indexInfo = [i[1] for i in indexedColumns if i[0] == column[0]] 351 column.append(", ".join(indexInfo)) 352 result.append(column) 353 # Adds to complete list 354 if completeMethod is not None: 355 completeMethod([i[0] for i in result], "columns") 356 357 elif oraObject.getType() in ("VIEW", "MATERIALIZED VIEW"): 358 header = [_("Name"), _("Type"), _("Null?"), _("Comments")] 359 result = oraObject.getTableColumns(db) 360 # Adds to complete list 361 if completeMethod is not None: 362 completeMethod([i[0] for i in result], "columns") 363 364 elif oraObject.getType() == "CONSUMER GROUP": 365 raise PysqlNotImplemented() 366 367 elif oraObject.getType() == "CONTEXT": 368 raise PysqlNotImplemented() 369 370 elif oraObject.getType() == "DATA FILE": 371 header = [_("Tablespace"), _("Size (%s)") % unit.upper(), _("Free (%s)") % unit.upper(), _("%Used")] 372 size = convert(oraObject.getAllocatedBytes(db), unit) 373 free = convert(oraObject.getFreeBytes(db), unit) 374 if size != 0: 375 used = 100 - float(100 * free) / size 376 else: 377 used = 0 378 if printDetails: 379 print CYAN + _("Tablespace: ") + oraObject.getTablespace(db).getName() + RESET 380 result = [[oraObject.getTablespace(db).getName(), round(size, 2), round(free, 2), round(used, 2)]] 381 382 elif oraObject.getType() == "DATABASE LINK": 383 header = [_("Target")] 384 result = [[oraObject.getRemoteUser(db) + "@" + oraObject.getRemoteHost(db)]] 385 386 elif oraObject.getType() == "DIRECTORY": 387 header = [_("Path")] 388 result = [[oraObject.getPath(db)]] 389 390 elif oraObject.getType() == "EVALUATION CONTEXT": 391 raise PysqlNotImplemented() 392 393 elif oraObject.getType()in("FUNCTION", "PACKAGE", "PROCEDURE"): 394 header = [_("#"), _("Source")] 395 result = oraObject.getSource(db) 396 397 elif oraObject.getType() == "INDEX": 398 header = [_("Property"), _("Value")] 399 result = oraObject.getProperties(db) 400 401 elif oraObject.getType() == "INDEX PARTITION": 402 raise PysqlNotImplemented() 403 404 elif oraObject.getType() == "INDEXTYPE": 405 raise PysqlNotImplemented() 406 407 elif oraObject.getType() == "JAVA CLASS": 408 raise PysqlNotImplemented() 409 410 elif oraObject.getType() == "JAVA DATA": 411 raise PysqlNotImplemented() 412 413 elif oraObject.getType() == "JAVA RESOURCE": 414 raise PysqlNotImplemented() 415 416 elif oraObject.getType() == "LIBRARY": 417 raise PysqlNotImplemented() 418 419 elif oraObject.getType() == "OPERATOR": 420 raise PysqlNotImplemented() 421 422 elif oraObject.getType() == "PACKAGE BODY": 423 raise PysqlNotImplemented() 424 425 elif oraObject.getType() == "SEQUENCE": 426 header = [_("Last"), _("Min"), _("Max"), _("Step")] 427 result = [[oraObject.getLast(db), oraObject.getMin(db), oraObject.getMax(db), oraObject.getStep(db)]] 428 429 elif oraObject.getType() == "TABLE PARTITION": 430 raise PysqlNotImplemented() 431 432 elif oraObject.getType() == "TABLESPACE": 433 oraObject.updateDatafileList(db) 434 header = [_("Datafile"), _("Size (%s)") % unit.upper(), _("Free (%s)") % unit.upper(), _("%Used")] 435 result = [[]] 436 totalSize = 0 437 totalFree = 0 438 totalUsed = 0 439 for datafile in oraObject.getDatafiles(): 440 name = datafile.getName() 441 size = convert(datafile.getAllocatedBytes(db), unit) 442 free = convert(datafile.getFreeBytes(db), unit) 443 if size != 0: 444 used = 100 - float(100 * free) / size 445 else: 446 used = 0 447 result.append([name, round(size, 2), round(free, 2), round(used, 2)]) 448 totalSize += size 449 totalFree += free 450 if totalSize != 0: 451 totalUsed = 100 - float(100 * totalFree) / totalSize 452 else: 453 totalUsed = 0 454 if len(oraObject.getDatafiles()) > 1: 455 result[0] = ["> " + _("TOTAL"), round(totalSize, 2), round(totalFree, 2), round(totalUsed, 2)] 456 else: 457 result.pop(0) 458 459 elif oraObject.getType() == "TRIGGER": 460 oraObject.updateTable(db) 461 header = [_("Status"), _("Table"), _("Type"), _("Event"), _("Body")] 462 result = [[oraObject.getStatus(db), oraObject.getTable(db).getFullName(), 463 oraObject.getTriggerType(db), oraObject.getEvent(db), 464 oraObject.getBody(db).replace("\n", " ")]] 465 466 elif oraObject.getType() == "USER": 467 oraObject.updateTablespaceList(db) 468 header = [_("Tablespace"), _("Default?"), _("#Tables"), _("#Indexes")] 469 result = [[]] 470 totalTables = 0 471 totalIndexes = 0 472 defaultTbs = oraObject.getDefaultTablespace(db) 473 #tempTbs = oraObject.getTempTablespace(db) 474 for tablespace in oraObject.getTablespaces(): 475 name = tablespace.getName() 476 nbTables = oraObject.getNbTables(db, tablespace=tablespace.getName()) 477 nbIndexes = oraObject.getNbIndexes(db, tablespace=tablespace.getName()) 478 if name == defaultTbs: 479 defstr = u"*" 480 else: 481 defstr = u"" 482 result.append([name, defstr, nbTables, nbIndexes]) 483 totalTables += nbTables 484 totalIndexes += nbIndexes 485 if len(oraObject.getTablespaces()) > 1: 486 result[0] = ["> " + _("TOTAL"), u"", totalTables, totalIndexes] 487 else: 488 result.pop(0) 489 else: 490 raise PysqlException(_("Type not handled: %s") % oraObject.getType()) 491 return (header, result)

492

493 -def edit(db, objectName, content=""):

494 """Edits properties of an Oracle object 495 @param objectName: name of the object to edit 496 @return: True if object has been found correctly updated. Else, return False 497 """ 498 # Gets the object type and owner 499 oraObject = OraObject(objectName=objectName) 500 oraObject.guessInfos(db) 501 502 # Object or type unknown? 503 if oraObject.getType() is None: 504 return False 505 506 # Tries to resolve synonym and describe the target 507 if oraObject.getType() == "SYNONYM": 508 oraObject = oraObject.getTarget(db) 509 if oraObject.getType() == "SYNONYM": 510 raise PysqlException(_("Too much synonym recursion")) 511 if content == "": 512 try: 513 content = oraObject.getSQL(db) 514 except AttributeError: 515 raise PysqlNotImplemented() 516 content = editor(content) 517 # Does nothing if data does not change 518 if content is None: 519 return True 520 # Validates it (for view it is SQL code) 521 db.validate(content) 522 # And update it in database 523 try: 524 oraObject.setSQL(db, content) 525 except AttributeError: 526 # SetSQL failed because it is not (yet) implemented 527 raise PysqlNotImplemented() 528 return True

529

530 -def editor(content=""):

531 """Edits content with systemp editor 532 @arg content: initial data to edit. Default is empty string 533 @type content: string 534 @return: None is content does not change, else modified content 535 """ 536 # Which editor, which temporary directory? 537 if os.name == "posix": 538 editorProgram = getenv("EDITOR", "vi") 539 tempDir = "/tmp" 540 elif os.name == "nt": 541 editorProgram = "edit" 542 tempDir = getenv("TEMP", ".") 543 else: 544 raise PysqlException(_("No editors are supported on this platform. Sorry.")) 545 # Computes actual properties md5 546 checkSum = md5(content).hexdigest() 547 try: 548 # Writes actual properties to temp file 549 filePath = os.path.join(tempDir, "pysql-" + str(os.getpid()) + ".tmp") 550 tmp = file(filePath, "w") 551 tmp.write(content) 552 tmp.close() 553 # Lets the user edit it 554 exitStatus = os.system(editorProgram + " " + filePath) 555 if exitStatus != 0: 556 raise PysqlException(_("Editor exited with status %s") % exitStatus) 557 # Updates properties with new value 558 tmp = file(filePath, "r") 559 content = tmp.read() 560 tmp.close() 561 unlink(filePath) 562 except IOError, e: 563 raise PysqlException(_("Error while using temporary file (%s)") % e) 564 if checkSum == md5(content).hexdigest(): 565 return None 566 else: 567 return content

568

569 -def explain(db, statement):

570 """Computes and displays explain plan for statement 571 @param statement: sql statement to be explained 572 @return: explain plan (list of string) 573 """ 574 # Compute the explain plan 575 db.execute("explain plan for %s" % statement) 576 return db.executeAll(u"""select plan_table_output 577 from table(dbms_xplan.display('PLAN_TABLE',null,'serial'))""")

578

579 -def lock(db):

580 """Displays locks on objects 581 @return: resultset in tabular format 582 """ 583 header = [_("User"), _("OS user"), _("Mode"), _("Object")] 584 try: 585 result = db.executeAll(u"""SELECT 586 oracle_username, 587 os_user_name, 588 decode(locked_mode, 589 1, 'No Lock', 590 2, 'Row Share', 591 3, 'Row Exclusive', 592 4, 'Share', 593 5, 'Share Row Exclusive', 594 6, 'Exclusive', 595 'NONE') lock_mode, 596 object_name 597 FROM v$locked_object lo, dba_objects o 598 WHERE lo.object_id=o.object_id""") 599 except PysqlException: 600 raise PysqlActionDenied(_("Insufficient privileges")) 601 return (header, result)

602

603 -def sessions(db, all=False, search=None):

604 """Returns top session, filter by "sort" 605 @param all: Show all sessions, not only active (top) session 606 @type all: bool 607 @param search: search session program, name, ouser... that looks like the str given 608 @return: huge resultset in tabular format""" 609 610 header = [_("Id"), _("Serial"), _("Schema"), _("OsUser"), _("Machine"), _("Program"), _("Logged Since"), _("Blk Gets"), _("Cons Gets"), _("Phy Rds"), _("Blk Chg"), _("Cons Chg"), _("CPU(ms)"), _("C PID"), _("S PID"), _("SQL")] 611 612 sessionFilter = [] 613 if not all: 614 sessionFilter.append("a.Status != 'INACTIVE'") 615 if search: 616 for searchTerm in search: 617 searchFilter = [] 618 for term in ("a.SchemaName", "a.Osuser", "a.Machine", "a.Program", "d.sql_text"): 619 searchFilter.append("%s like '%%%s%%'" % (term, searchTerm)) 620 sessionFilter.append("(%s)" % " or ".join(searchFilter)) 621 622 if sessionFilter: 623 whereClause = "and %s" % " and ".join(sessionFilter) 624 else: 625 whereClause = "" 626 try: 627 result = db.executeAll(sessionStatSql["all"] % whereClause) 628 except PysqlException: 629 raise PysqlActionDenied(_("Insufficient privileges")) 630 return (header, result)

631

632 -def sessionStat(db, sid, stat=None):

633 """Displays detailed statistics for one session 634 @param stat: can be ios, locks, waitEvents, openCurors, details 635 @return: array of results""" 636 # TODO: harden this weak code! 637 if stat is None: 638 return None 639 else: 640 return db.executeAll(sessionStatSql[stat], [sid])

641

642 -def killSession(db, session, immediate=False):

643 """Kills the given sessions 644 @param session: 'session-id,session-serial' 645 @type session: str 646 @param immediate: sends the immediate option to kill 647 @type immediate: bool 648 @return: None but raises an exception if session does not exist 649 """ 650 sql = u"""alter system kill session '%s'""" % session 651 if immediate: 652 sql += u" immediate" 653 try: 654 db.execute(sql) 655 except PysqlException: 656 raise PysqlActionDenied(_("Insufficient privileges"))

657

658 -def showParameter(db, param=""):

659 """Shows the session parameters matching the pattern 'param' 660 @param param: pattern to be matched 661 @type param: str 662 @return: resultset in tabular format 663 """ 664 param = addWildCardIfNeeded(param) 665 header = [_("Name"), _("Type"), _("Value"), _("#"), _("Session?"), _("System?"), _("Comments")] 666 #TODO: move this request to pysqlQueries 667 try: 668 result = db.executeAll("""select name 669 , decode(type, 1, 'BOOLEAN', 2, 'STRING', 3, 'INTEGER', 4, 'PFILE' 670 , 5, 'RESERVED', 6, 'BIG INTEGER', 'UNKNOWN') type 671 , decode(substr(name, 1, 3), 'nls' 672 , (select value from nls_session_parameters 673 where lower(parameter)=name) 674 , value) value 675 , ordinal 676 , isses_modifiable 677 , issys_modifiable 678 , description 679 from v$parameter2 680 where name like '%s' 681 order by 1""" % param) 682 except PysqlException: 683 raise PysqlActionDenied(_("Insufficient privileges")) 684 return (header, result)

685

686 -def showServerParameter(db, param=""):

687 """Shows the server parameters matching the pattern 'param' 688 @param param: pattern to be matched 689 @type param: str 690 @return: resultset in tabular format 691 """ 692 param = addWildCardIfNeeded(param) 693 header = [_("Name"), _("Type"), _("Value"), _("#"), _("Used?"), _("Comments")] 694 #TODO: move this request to pysqlQueries 695 try: 696 result = db.executeAll("""select distinct sp.name 697 , decode(p.type, 1, 'BOOLEAN', 2, 'STRING', 3, 'INTEGER', 4, 'PFILE' 698 , 5, 'RESERVED', 6, 'BIG INTEGER', 'UNKNOWN') type 699 , decode(substr(sp.name, 1, 3), 'nls' 700 , (select value from nls_database_parameters 701 where lower(parameter)=sp.name) 702 , sp.value) value 703 , sp.ordinal 704 , sp.isspecified 705 , p.description 706 from v$spparameter sp, v$parameter2 p 707 where sp.name=p.name 708 and sp.name like '%s' 709 order by 1""" % param) 710 except PysqlException: 711 raise PysqlActionDenied(_("Insufficient privileges")) 712 return (header, result)

713 714 # Oracle object searching

715 -def searchObject(db, objectType, objectName, objectOwner):

716 """Searches for Oracle objects by name with wildcard if needed""" 717 result = {} 718 objectType = objectType.lower() 719 try: 720 sql = searchObjectSql[objectType][0] 721 keyword = searchObjectSql[objectType][1] 722 if len(objectName.split()) == 1: 723 # Single word search. Just add wildcart % if needed 724 whereClause = "%s like '%s'" % (keyword, addWildCardIfNeeded(objectName)) 725 else: 726 whereClause = generateWhere(keyword, objectName) 727 objects = db.executeAll(sql % (whereClause, objectOwner, keyword)) 728 except KeyError: 729 raise PysqlException(_("SQL entry not defined for searchObjectSql: %s") % objectType) 730 # Returns a dict with key=schemaNAme and Value=list of object 731 for (owner, name) in objects: 732 if result.has_key(owner): 733 result[owner].append(name) 734 else: 735 result[owner] = [name] 736 return result

737

Source Code for Module pysql.pysqlfunctions