Package pysql :: Module pysqloraobjects
[hide private]
[frames] | no frames]

Source Code for Module pysql.pysqloraobjects

   1  #!/usr/bin/python 
   2  # -*- coding: utf-8 -*- 
   3   
   4  """This module defines some classes to make easier Oracle object manipulation 
   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  # Pysql imports: 
  13  from pysqlqueries import * 
  14  from pysqlexception import PysqlException, PysqlNotImplemented, PysqlActionDenied 
  15  import pysqlhelpers 
  16   
17 -class OraObject:
18 """Father of all pysql Oracle objects"""
19 - def __init__(self, objectOwner="", objectName="", objectType="", objectStatus=""):
20 """Object creation""" 21 self.setOwner(objectOwner) # Set owner first because setName may override 22 self.setName(objectName) 23 self.setType(objectType) 24 self.setStatus(objectStatus)
25
26 - def __str__(self):
27 """String representation (mostly used for debug purpose)""" 28 return self.getOwner() + "." + self.getName() + " (" + self.getType() + ")"
29
30 - def __eq__(self, other):
31 """Define equal operator. Object are identical if they have same name, type and owner""" 32 if hash(self) == hash(other): 33 return True 34 else: 35 return False
36
37 - def __hash__(self):
38 """Unique hash of object. Used to determine if objects are identical and to create 39 set of unique object""" 40 return hash("%s.%s.%s" % (self.getType(), self.getOwner(), self.getName()))
41
42 - def getCopy(self):
43 """@return: a deep copy of the current object""" 44 return OraObject(objectOwner=self.getOwner(), objectName=self.getName(), objectType=self.getType())
45
46 - def getName(self):
47 """@return: object name (str)""" 48 return self.objectName
49
50 - def getFullName(self):
51 """@return: object name prefixed with owner name (str) 52 Ex. "scott.my_table" """ 53 return self.getOwner() + "." + self.getName()
54
55 - def getType(self):
56 """@return: object type (str)""" 57 return self.objectType
58
59 - def getOwner(self):
60 """@return: object owner (str)""" 61 return self.objectOwner
62
63 - def getStatus(self):
64 """@return: object status (str)""" 65 return self.objectStatus
66
67 - def setName(self, objectName):
68 """ Sets name (and owner if name is given like "user.object")""" 69 if objectName == "": 70 raise PysqlException("Object name must be defined!") 71 if objectName.startswith("/"): 72 # This should be a datafile 73 #TODO: check if fully compliant with Windows 74 self.objectName = objectName 75 elif objectName.count(".") == 1: 76 (owner, name) = objectName.split(".") 77 self.setOwner(owner) 78 self.objectName = pysqlhelpers.upperIfNoQuotes(name) 79 else: 80 # Default to simple setName 81 self.objectName = pysqlhelpers.upperIfNoQuotes(objectName)
82
83 - def setType(self, objectType):
84 """Sets the object type 85 @param objectType: Oracle object type as defined in Oracle dynamic views 86 @type objectType: str 87 """ 88 if objectType is None: 89 self.objectType = "" 90 else: 91 self.objectType = objectType.upper() 92 # Transtypes to object type if possible 93 if self.objectType == "DATABASE LINK": 94 self.__class__ = OraDBLink 95 elif self.objectType == "DATA FILE": 96 self.__class__ = OraDatafile 97 elif self.objectType == "DIRECTORY": 98 self.__class__ = OraDirectory 99 elif self.objectType == "FUNCTION": 100 self.__class__ = OraFunction 101 elif self.objectType in ("INDEX", "INDEX PARTITION"): 102 self.__class__ = OraIndex 103 elif self.objectType == "MATERIALIZED VIEW": 104 self.__class__ = OraMaterializedView 105 elif self.objectType == "PACKAGE": 106 self.__class__ = OraPackage 107 elif self.objectType == "PACKAGE BODY": 108 self.__class__ = OraPackageBody 109 elif self.objectType == "PROCEDURE": 110 self.__class__ = OraProcedure 111 elif self.objectType == "SEQUENCE": 112 self.__class__ = OraSequence 113 elif self.objectType == "SYNONYM": 114 self.__class__ = OraSynonym 115 elif self.objectType in ("TABLE", "TABLE PARTITION"): 116 self.__class__ = OraTable 117 elif self.objectType == "TABLESPACE": 118 self.__class__ = OraTablespace 119 elif self.objectType == "TRIGGER": 120 self.__class__ = OraTrigger 121 elif self.objectType == "VIEW": 122 self.__class__ = OraView 123 elif self.objectType == "USER": 124 self.__class__ = OraUser
125
126 - def setOwner(self, objectOwner):
127 """Sets the object owner. Name is uppercased if quote are not given""" 128 self.objectOwner = pysqlhelpers.upperIfNoQuotes(objectOwner)
129
130 - def setStatus(self, objectStatus):
131 self.objectStatus = objectStatus
132
133 - def guessStatus(self, db):
134 """guess the object status""" 135 status = db.executeAll(guessInfoSql["objectStatusFromName"], [self.getName()]) 136 self.setStatus(status[0][0])
137
138 - def guessInfos(self, db, interactive=False):
139 """Guesses and sets object type, owner and status 140 @param db: Connection to Oracle 141 @type db: PysqlDb instance 142 @param interactive: should we prompt user if multiple results are found? (default is False) 143 @type interactive: bool 144 @return: True if type and owner are guessed. In interactive mode, returns list of objects found 145 """ 146 #TODO: this code should be factorized 147 result = set() # Store here all guessInfos results 148 currentUsername = db.getUsername().upper() 149 name = self.getName() 150 owner = self.getOwner() 151 152 # Assume object is in current schema 153 if owner == "": 154 objectType = db.executeAll(guessInfoSql["typeFromNameAndOwner"], [name, currentUsername]) 155 for type in objectType: 156 if interactive: 157 result.add(OraObject(currentUsername, name, type[0])) 158 else: 159 self.setOwner(currentUsername) 160 self.setName(name) 161 self.setType(type[0]) 162 self.guessStatus(db) 163 return True 164 # Default to public 165 owner = u"PUBLIC" 166 167 objectType = db.executeAll(guessInfoSql["typeFromNameAndOwner"], [name, owner]) 168 for type in objectType: 169 if interactive: 170 result.add(OraObject(owner, name, type[0])) 171 else: 172 self.setOwner(owner) 173 self.setName(name) 174 self.setType(type[0]) 175 self.guessStatus(db) 176 return True 177 178 # Try SYS objects 179 owner = u"SYS" 180 try: 181 objectType = db.executeAll(guessInfoSql["typeFromNameAndSYS"], [name]) 182 except PysqlException: 183 objectType = db.executeAll(guessInfoSql["typeFromNameAndOwner"], [name, owner]) 184 for type in objectType: 185 if interactive: 186 result.add(OraObject(owner, name, type[0])) 187 else: 188 self.setOwner(owner) 189 self.setName(name) 190 self.setType(type[0]) 191 self.guessStatus(db) 192 return True 193 194 # Try user, tablespace and so on 195 for name in (self.getName(), self.getName().upper()): 196 try: 197 objectType = db.executeAll(guessInfoSql["otherTypeFromName"], [name]) 198 except PysqlException: 199 objectType = [] 200 for type in objectType: 201 if interactive: 202 result.add(OraObject(owner, name, type[0])) 203 else: 204 self.setOwner(owner) 205 self.setName(name) 206 self.setType(type[0]) 207 self.guessStatus(db) 208 return True 209 210 if interactive: 211 # If type is already defined, filter out object that does not match 212 if self.getType(): 213 result = set([o for o in result if o.getType() == self.getType()]) 214 return result 215 else: 216 #Giving up. 217 return False
218
219 - def getCreated(self, db):
220 """@return: date of creation of the object""" 221 if self.getOwner() == "": 222 owner = db.getUsername().upper() 223 result = db.executeAll(tabularSql["createdFromOwnerAndName"], [owner, self.getName()]) 224 else: 225 try: 226 result = db.executeAll(tabularSql["createdFromDBAAndName"], 227 [self.getOwner(), self.getName()]) 228 except PysqlException: 229 result = db.executeAll(tabularSql["createdFromOwnerAndName"], 230 [self.getOwner(), self.getName()]) 231 if len(result) == 1: 232 #TODO: use database encoding instead of just using str() 233 return str(result[0][0]) 234 else: 235 raise PysqlException(_("Cannot get the date of creation on object %s") % self.getName())
236
237 - def getLastDDL(self, db):
238 """@return: date of last DDL modification of the object""" 239 if self.getOwner() == "": 240 owner = db.getUsername().upper() 241 result = db.executeAll(tabularSql["lastDDLFromOwnerAndName"], [owner, self.getName()]) 242 else: 243 try: 244 result = db.executeAll(tabularSql["lastDDLFromDBAAndName"], 245 [self.getOwner(), self.getName()]) 246 except PysqlException: 247 result = db.executeAll(tabularSql["lastDDLFromOwnerAndName"], 248 [self.getOwner(), self.getName()]) 249 if len(result) == 1: 250 #TODO: use database encoding instead of just using str() 251 return str(result[0][0]) 252 else: 253 raise PysqlException(_("Cannot get the date of last DDL modification on object %s") % self.getName())
254
255 - def getDDL(self, db):
256 """@return: SQL needed to create this object as a str""" 257 if self.getType() == "": 258 raise PysqlException(_("Object type is not defined")) 259 if self.getOwner() == "": 260 owner = db.getUsername().upper() 261 else: 262 owner = self.getOwner() 263 result = db.executeAll(metadataSql["ddlFromTypeNameAndOwner"], [self.getType(), self.getName(), owner]) 264 if len(result) == 0: 265 return None 266 else: 267 return result[0][0]
268 269 ##############################################################################
270 -class OraSegment(OraObject):
271 """Father of tables and indexes"""
272 - def getTablespace(self, db):
273 """@return: tablespace name that contains this segment""" 274 raise PysqlNotImplemented()
275
276 -class OraTabular(OraObject):
277 """Father of tables, partitioned tables, views, materialized views. All objects that 278 have rows and lines. 279 The name is not very sexy. Anybody has a better choice?""" 280
281 - def __init__(self, objectOwner="", objectName=""):
282 """Tabular object creation""" 283 OraObject.__init__(self, objectOwner, objectName, "")
284
285 - def getRowCount(self, db):
286 """@return: row count (select count(*) from ...)""" 287 owner = self.getOwner() 288 if owner == "": 289 owner = db.getUsername().upper() 290 return db.executeAll(u"""select count(*) from %s."%s" """ % (owner, self.getName()))[0][0]
291
292 - def getComment(self, db):
293 """@return: db comment of the object""" 294 if self.getOwner() == "": 295 owner = db.getUsername().upper() 296 result = db.executeAll(tabularSql["commentFromOwnerAndName"], [owner, self.getName()]) 297 else: 298 try: 299 result = db.executeAll(tabularSql["commentFromDBAAndName"], 300 [self.getOwner(), self.getName()]) 301 except PysqlException: 302 result = db.executeAll(tabularSql["commentFromOwnerAndName"], 303 [self.getOwner(), self.getName()]) 304 if len(result) == 1: 305 #TODO: use database encoding instead of just using str() 306 return str(result[0][0]) 307 else: 308 raise PysqlException(_("Cannot get the comment on object %s") % self.getName())
309
310 - def getTableColumns(self, db, sort=False):
311 """Gets table or view columns 312 @param sort: sort column in alphabetic order instead of Oracle order. Default is false 313 @type sort: bool 314 @return: array of column_name, columns_type, comments 315 """ 316 if sort: 317 sortCondition = " order by 1" 318 else: 319 sortCondition = "" 320 321 if self.getOwner() == "": 322 owner = db.getUsername().upper() 323 columns = db.executeAll(tabularSql["columnsFromOwnerAndName"] + sortCondition, 324 [owner, self.getName()]) 325 else: 326 try: 327 columns = db.executeAll(tabularSql["columnsFromDBAAndName"] + sortCondition, 328 [self.getOwner(), self.getName()]) 329 except PysqlException: 330 columns = db.executeAll(tabularSql["columnsFromOwnerAndName"] + sortCondition, 331 [self.getOwner(), self.getName()]) 332 if len(columns) == 0: 333 return (None, None, None) 334 else: 335 return columns
336
337 - def getNumberOfColumns(self, db):
338 """@return: the number (int) of columns of the table/view""" 339 if self.getOwner() == "": 340 owner = db.getUsername().upper() 341 return db.executeAll(tabularSql["numberOfColumnsFromOwnerAndName"], 342 [self.getOwner(), self.getName()])[0][0]
343
344 -class OraDatafile(OraObject):
345 """Datafile"""
346 - def __init__(self, datafileOwner="", datafileName=""):
347 """Datafile creation""" 348 OraObject.__init__(self, datafileOwner, datafileName, u"DATA FILE")
349
350 - def getTablespace(self, db):
351 """@return: tablespace""" 352 result = db.executeAll(datafileSql["tablespaceFromName"], [self.getName()]) 353 if len(result) == 0: 354 return None 355 else: 356 return OraTablespace(tablespaceName=result[0][0])
357
358 - def getAllocatedBytes(self, db):
359 """@return: number of bytes currently allocated in the data file""" 360 result = db.executeAll(datafileSql["allocatedBytesFromName"], [self.getName()]) 361 if len(result) == 0: 362 raise PysqlException(_("Data file %s does not exist") % self.getName()) 363 elif result[0][0] is None: 364 msg = _("Insufficient privileges") 365 raise PysqlException(msg) 366 else: 367 return int(result[0][0])
368
369 - def getFreeBytes(self, db):
370 """@return: number of bytes currently free in the data file""" 371 result = db.executeAll(datafileSql["freeBytesFromName"], [self.getName()]) 372 if len(result) == 0: 373 raise PysqlException(_("Data file %s does not exist") % self.getName()) 374 else: 375 return int(result[0][0])
376
377 - def guessStatus(self, db):
378 """Guess datafile status""" 379 status = db.executeAll(guessInfoSql["dbfStatusFromName"], [self.getName()]) 380 self.setStatus(status[0][0])
381 411
412 -class OraDirectory(OraObject):
413 """Oracle directory object"""
414 - def __init__(self, directoryOwner="", directoryName=""):
415 """Directory creation""" 416 OraObject.__init__(self, directoryOwner, directoryName, u"DIRECTORY")
417
418 - def getPath(self, db):
419 """Gets the OS path of the directory object 420 @return: full path (str)""" 421 result = db.executeAll(directorySql["pathFromName"], [self.getName()]) 422 if len(result) == 0: 423 return "" 424 else: 425 return result[0][0]
426
427 -class OraIndex(OraSegment):
428 """Oracle index"""
429 - def __init__(self, indexOwner="", indexName=""):
430 """Index creation""" 431 OraObject.__init__(self, indexOwner, indexName, u"INDEX")
432
433 - def getProperties(self, db):
434 """Returns index following properties : 435 Index_type, uniqueness, table_owner, table_name, compression, leaf_blocks, destincts_keys 436 avg_lef_blocks_per_leys as a list of (property_name, property_value)""" 437 if self.getOwner() == "": 438 owner = db.getUsername().upper() 439 else: 440 owner = self.getOwner() 441 result = db.executeAll(indexSql["propertiesFromOwnerAndName"], [owner, self.getName()]) 442 result.insert(0, db.getDescription()) 443 444 if not result: 445 return None 446 # Transpose the result 447 result = [[result[i][j] for i in range(len(result))] for j in range(len(result[0]))] 448 449 # Add indexed columns 450 indexedColumns = self.getIndexedColumns(db) 451 result.append([_("Indexed Columns"), ", ".join(["%s(%s)" % (i[0], i[1]) for i in indexedColumns])]) 452 453 return result
454
455 - def getIndexedColumns(self, db):
456 """Returns indexed columns as a list of (column_name, column_position)""" 457 if self.getOwner() == "": 458 owner = db.getUsername().upper() 459 else: 460 owner = self.getOwner() 461 return db.executeAll(indexSql["indexedColumnsFromOwnerAndName"], [owner, self.getName()])
462
463 -class OraMaterializedView(OraTabular, OraSegment):
464 """Oracle materialized view"""
465 - def __init__(self, mviewOwner="", mviewName=""):
466 """Materialized view creation""" 467 OraObject.__init__(self, mviewOwner, mviewName, u"MATERIALIZED VIEW")
468
469 - def getSQL(self, db):
470 """@return: SQL code behind the materialized view""" 471 if self.getOwner() == "": 472 owner = db.getUsername().upper() 473 else: 474 owner = self.getOwner() 475 result = db.executeAll(mviewSql["queryFromOwnerAndName"], [owner, self.getName()]) 476 if len(result) == 0: 477 return "" 478 else: 479 return result[0][1]
480
481 -class OraStoredObject(OraObject):
482 """Oracle package body, header for stored procedure and stored functions"""
483 - def getSQL(self, db):
484 """Gets object SQL source code 485 @return: source code (str)""" 486 result = self.getSQLAsList(db) 487 # Transform list of list of str to str 488 return "".join(result)
489
490 - def getSQLAsList(self, db):
491 """Gets object SQL source code 492 @return: source code (list of str)""" 493 if self.getType() == "": 494 raise PysqlException("object type is not defined !") 495 if self.getOwner() == "": 496 owner = db.getUsername().upper() 497 else: 498 owner = self.getOwner() 499 result = db.executeAll(storedObjectSql["sourceFromOwnerAndNameAndType"], 500 [owner, self.getName(), self.getType()]) 501 return [i[0] for i in result]
502
503 - def setSQL(self, db, sql):
504 """Sets the object SQL code. 505 @arg sql: source code 506 @type sql: str 507 """ 508 raise PysqlNotImplemented()
509
510 - def _getSQL(self, db):
511 """Common method used by getSQL and getSQLAsList"""
512
513 -class OraProcedure(OraStoredObject):
514 """Oracle stored procedure"""
515 - def __init__(self, procedureOwner, procedureName):
516 OraObject.__init__(self, procedureOwner, procedureName, u"PROCEDURE")
517
518 - def getSource(self, db):
519 """Gets source code 520 @return: array of source line 521 """ 522 if self.getOwner() == "": 523 owner = db.getUsername().upper() 524 else: 525 owner = self.getOwner() 526 source = db.executeAll(packageSql["sourceFromOwnerAndName"], [owner, self.getName()]) 527 if len(source) == 0: 528 return (None) 529 else: 530 return source
531
532 -class OraFunction(OraStoredObject):
533 """Oracle stored function"""
534 - def __init__(self, procedureOwner, procedureName):
535 OraObject.__init__(self, procedureOwner, procedureName, u"FUNCTION")
536
537 - def getSource(self, db):
538 """Gets source code 539 @return: array of source line 540 """ 541 if self.getOwner() == "": 542 owner = db.getUsername().upper() 543 else: 544 owner = self.getOwner() 545 source = db.executeAll(packageSql["sourceFromOwnerAndName"], [owner, self.getName()]) 546 if len(source) == 0: 547 return (None) 548 else: 549 return source
550
551 -class OraPackage(OraStoredObject):
552 """Oracle Package"""
553 - def __init__(self, packageOwner, packageName):
554 OraObject.__init__(self, packageOwner, packageName, u"PACKAGE")
555
556 - def getProcedures(self, db):
557 """Gets procedure names 558 @return: array of procedure_name 559 """ 560 if self.getOwner() == "": 561 owner = db.getUsername().upper() 562 else: 563 owner = self.getOwner() 564 columns = db.executeAll(packageSql["proceduresFromOwnerAndName"], [owner, self.getName()]) 565 if len(columns) == 0: 566 return (None) 567 else: 568 return columns
569
570 - def getSource(self, db):
571 """Gets source code 572 @return: array of source line 573 """ 574 if self.getOwner() == "": 575 owner = db.getUsername().upper() 576 else: 577 owner = self.getOwner() 578 source = db.executeAll(packageSql["sourceFromOwnerAndName"], [owner, self.getName()]) 579 if len(source) == 0: 580 return (None) 581 else: 582 return source
583
584 -class OraPackageBody(OraStoredObject):
585 """Oracle stored package body"""
586 - def __init__(self, packageOwner, packageName):
587 OraObject.__init__(self, packageOwner, packageName, u"PACKAGE BODY")
588
589 -class OraSequence(OraObject):
590 """Oracle sequence"""
591 - def __init__(self, sequenceOwner="", sequenceName=""):
592 """Sequence creation""" 593 OraObject.__init__(self, sequenceOwner, sequenceName, u"SEQUENCE")
594
595 - def getLast(self, db):
596 """Gets the last value of the sequence object 597 @return: full path (str)""" 598 if self.getOwner() == "": 599 owner = db.getUsername().upper() 600 else: 601 owner = self.getOwner() 602 result = db.executeAll(sequenceSql["lastFromOwnerAndName"], [owner, self.getName()]) 603 if len(result) == 0: 604 return "" 605 else: 606 return result[0][1]
607
608 - def getMin(self, db):
609 """Gets the min value of the sequence object 610 @return: full path (str)""" 611 if self.getOwner() == "": 612 owner = db.getUsername().upper() 613 else: 614 owner = self.getOwner() 615 616 result = db.executeAll(sequenceSql["minFromOwnerAndName"], [owner, self.getName()]) 617 if len(result) == 0: 618 return "" 619 else: 620 return result[0][1]
621
622 - def getMax(self, db):
623 """Gets the max value of the sequence object 624 @return: full path (str)""" 625 if self.getOwner() == "": 626 owner = db.getUsername().upper() 627 else: 628 owner = self.getOwner() 629 result = db.executeAll(sequenceSql["maxFromOwnerAndName"], [owner, self.getName()]) 630 if len(result) == 0: 631 return "" 632 else: 633 return result[0][1]
634
635 - def getStep(self, db):
636 """Gets the step value of the sequence object 637 @return: full path (str)""" 638 if self.getOwner() == "": 639 owner = db.getUsername().upper() 640 else: 641 owner = self.getOwner() 642 result = db.executeAll(sequenceSql["stepFromOwnerAndName"], [owner, self.getName()]) 643 if len(result) == 0: 644 return "" 645 else: 646 return result[0][1]
647
648 -class OraSynonym(OraObject):
649 """Oracle synonym"""
650 - def __init__(self, synonymOwner=u"PUBLIC", synonymName=""):
651 """Synonym creation""" 652 self.setName(synonymName) 653 self.setOwner(synonymOwner) 654 self.setType(u"SYNONYM")
655
656 - def getTarget(self, db, recursionStep=0):
657 """Finds the oracle object targeted by this synonym. 658 If the target is a synonym, recurse to find the real object. 659 @return: Returns the synonym target as an OraObject object 660 """ 661 recursionLimit = 4 # Maximum recursion allowed 662 if self.getOwner() == "": 663 owner = db.getUsername().upper() 664 else: 665 owner = self.getOwner() 666 667 result = db.executeAll(synonymSql["targetFromOwnerAndName"], [owner, self.getName().upper()]) 668 if len(result) == 0: 669 return None 670 # if more than one synonym, takes the first (other public and equal) 671 oraObject = OraObject(objectOwner=result[0][0], objectName=result[0][1]) 672 oraObject.guessInfos(db) 673 674 if oraObject.getType() == "": 675 raise PysqlActionDenied(_("Unable to resolve system synonyms")) 676 elif oraObject.getType() == "SYNONYM": 677 recursionStep += 1 678 # Checks that we do not recurse too much 679 if recursionStep > recursionLimit: 680 print "[DEBUG] More than %d synonyms imbricated... Maybe a circular reference?" \ 681 % recursionLimit 682 return oraObject 683 else: 684 # Recurses to find the real target 685 return oraObject.getTarget(db, recursionStep) 686 else: 687 return oraObject
688
689 -class OraTable(OraTabular, OraSegment):
690 """Oracle table"""
691 - def __init__(self, tableOwner="", tableName=""):
692 """Table creation""" 693 OraObject.__init__(self, tableOwner, tableName, u"TABLE")
694
695 - def getIndexedColumns(self, db):
696 """Gets all table's indexed columns 697 @return: array with column_name, index_name and index_position""" 698 if self.getOwner() == "": 699 owner = db.getUsername().upper() 700 else: 701 owner = self.getOwner() 702 result = db.executeAll(tableSql["indexedColFromOwnerAndName"], [owner, self.getName()]) 703 return result
704
705 - def getPrimaryKeys(self, db):
706 """Gets table primary key column name 707 @return: list of columns used in primary key. Empty list if not PK found""" 708 if self.getOwner() == "": 709 owner = db.getUsername().upper() 710 else: 711 owner = self.getOwner() 712 713 result = db.executeAll(tableSql["primaryKeyFromOwnerAndName"], [owner, self.getName()]) 714 if result: 715 return [i[0] for i in result] 716 else: 717 return None
718
719 - def getLastAnalyzed(self, db):
720 """Gets date of last statistics computation""" 721 if self.getOwner() == "": 722 owner = db.getUsername().upper() 723 else: 724 owner = self.getOwner() 725 result = db.executeAll(tableSql["lastAnalyzedFromOwnerAndName"], [owner, self.getName()]) 726 if len(result) == 0: 727 return "" 728 else: 729 return result[0][0]
730
731 - def getNumRows(self, db):
732 """Gets number of rows from table's statistics""" 733 if self.getOwner() == "": 734 owner = db.getUsername().upper() 735 else: 736 owner = self.getOwner() 737 result = db.executeAll(tableSql["numRowsFromOwnerAndName"], [owner, self.getName()]) 738 if len(result) == 0: 739 return "" 740 else: 741 return result[0][0]
742
743 - def getAvgRowLength(self, db):
744 """Gets average length of a single row from table's statistics""" 745 if self.getOwner() == "": 746 owner = db.getUsername().upper() 747 else: 748 owner = self.getOwner() 749 result = db.executeAll(tableSql["avgRowLengthFromOwnerAndName"], [owner, self.getName()]) 750 if len(result) == 0: 751 return "" 752 else: 753 return result[0][0]
754
755 - def getUsedBlocks(self, db):
756 """Gets number of used blocks from table's statistics""" 757 if self.getOwner() == "": 758 owner = db.getUsername().upper() 759 else: 760 owner = self.getOwner() 761 result = db.executeAll(tableSql["usedBlocksFromOwnerAndName"], [owner, self.getName()]) 762 if len(result) == 0: 763 return "" 764 else: 765 return result[0][0]
766
767 - def getNeededBlocks(self, db):
768 """Gets number of really used blocks from rowids""" 769 if self.getOwner() == "": 770 owner = db.getUsername().upper() 771 else: 772 owner = self.getOwner() 773 result = db.executeAll(tableSql["neededBlocksFromOwnerAndName"] % (owner, self.getName())) 774 if len(result) == 0: 775 return "" 776 else: 777 return result[0][0]
778
779 - def isPartitioned(self, db):
780 """Gets True if the table is partitioned 781 @return: true if table is partitioned, false otherwise""" 782 if self.getOwner() == "": 783 owner = db.getUsername().upper() 784 else: 785 owner = self.getOwner() 786 result = db.executeAll(tableSql["isPartitionedFromOwnerAndName"], [owner, self.getName()]) 787 if len(result) == 0: 788 return "" 789 else: 790 return (result[0][0] == "YES")
791
792 -class OraTablespace(OraObject):
793 """Tablespace""" 794
795 - def __init__(self, tablespaceOwner="", tablespaceName=""):
796 """Tablespace creation""" 797 OraObject.__init__(self, tablespaceOwner, tablespaceName, u"TABLESPACE") 798 self.datafiles = []
799
800 - def updateDatafileList(self, db):
801 """Gets list of the data files which compose the tablespace""" 802 self.datafiles = [] 803 if self.getName() != "": 804 datafileNameList = db.executeAll(tablespaceSql["datafilesFromName"], [self.getName()]) 805 if len(datafileNameList) == 0: 806 # Tries upper case 807 self.setName(self.getName().upper()) 808 datafileNameList = db.executeAll(tablespaceSql["datafilesFromName"], [self.getName()]) 809 if len(datafileNameList) == 0: 810 return 811 # Transposes datafile names vector 812 datafileNames = [i[0] for i in datafileNameList] 813 # Fills data file list 814 for fileName in datafileNames: 815 self.datafiles.append(OraDatafile("", fileName))
816
817 - def getAllocatedBytes(self, db):
818 """@return: number of bytes currently allocated in the tablespace""" 819 nbBytes = 0 820 for datafile in self.datafiles: 821 nbBytes += datafile.getAllocatedBytes(db) 822 return nbBytes
823
824 - def getFreeBytes(self, db):
825 """@return: number of bytes currently free in the tablespace""" 826 nbBytes = 0 827 for datafile in self.datafiles: 828 nbBytes += datafile.getFreeBytes(db) 829 return nbBytes
830
831 - def getDatafiles(self):
832 """@return: list of datafiles (updateDatafileList must be called before !)""" 833 return self.datafiles
834
835 - def guessStatus(self, db):
836 """guess tablespace status""" 837 status = db.executeAll(guessInfoSql["tbsStatusFromName"], [self.getName()]) 838 self.setStatus(status[0][0])
839 840
841 -class OraTrigger(OraObject):
842 """Trigger""" 843
844 - def __init__(self, triggerOwner="", triggerName=""):
845 """Trigger creation""" 846 OraObject.__init__(self, triggerOwner, triggerName, u"TRIGGER") 847 self.table = None
848
849 - def updateTable(self, db):
850 """Gets the triggered table""" 851 self.table = None 852 if self.getOwner() == "": 853 owner = db.getUsername().upper() 854 else: 855 owner = self.getOwner() 856 result = db.executeAll(triggerSql["tableFromOwnerAndName"], [owner, self.getName()]) 857 self.table = OraTable(tableOwner=result[0][0], tableName=result[0][1])
858
859 - def getBody(self, db):
860 """@return: trigger body""" 861 if self.getOwner() == "": 862 owner = db.getUsername().upper() 863 else: 864 owner = self.getOwner() 865 result = db.executeAll(triggerSql["bodyFromOwnerAndName"], [owner, self.getName()]) 866 if len(result) == 0: 867 return "" 868 else: 869 return result[0][0]
870
871 - def getEvent(self, db):
872 """@return: trigger type (BEFORE/AFTER, STATEMENT/ROW)""" 873 if self.getOwner() == "": 874 owner = db.getUsername().upper() 875 else: 876 owner = self.getOwner() 877 result = db.executeAll(triggerSql["eventFromOwnerAndName"], [owner, self.getName()]) 878 if len(result) == 0: 879 return "" 880 else: 881 return result[0][0]
882
883 - def getStatus(self, db):
884 """@return: trigger status (ENABLED/DISABLED)""" 885 if self.getOwner() == "": 886 owner = db.getUsername().upper() 887 else: 888 owner = self.getOwner() 889 result = db.executeAll(triggerSql["statusFromOwnerAndName"], [owner, self.getName()]) 890 if len(result) == 0: 891 return "" 892 else: 893 return result[0][0]
894
895 - def getTable(self, db):
896 """@return: triggered table (OraTable)""" 897 if self.table is None: 898 self.updateTable(db) 899 return self.table
900
901 - def getTriggerType(self, db):
902 """@return: triggering event (INSERT, DELETE or UPDATE)""" 903 if self.getOwner() == "": 904 owner = db.getUsername().upper() 905 else: 906 owner = self.getOwner() 907 result = db.executeAll(triggerSql["typeFromOwnerAndName"], [owner, self.getName()]) 908 if len(result) == 0: 909 return "" 910 else: 911 return result[0][0]
912
913 -class OraUser(OraObject):
914 """User"""
915 - def __init__(self, userOwner="", userName=""):
916 """Directory creation""" 917 OraObject.__init__(self, userOwner, userName, u"USER") 918 self.tablespaces = []
919
920 - def updateTablespaceList(self, db):
921 """Gets list of the tablespace which stores user segments""" 922 self.tablespaces = [] 923 if self.getName() != "": 924 tablespaceNameList = db.executeAll(userSql["tablespaceFromName"], [self.getName()]) 925 if len(tablespaceNameList) == 0: 926 # Tries upper case 927 self.setName(self.getName().upper()) 928 tablespaceNameList = db.executeAll(userSql["tablespaceFromName"], [self.getName()]) 929 if len(tablespaceNameList) == 0: 930 return 931 # Transposes datafile names vector 932 tablespaceNames = [i[0] for i in tablespaceNameList] 933 # Fills data file list 934 for tablespaceName in tablespaceNames: 935 self.tablespaces.append(OraTablespace("", tablespaceName))
936
937 - def getTablespaces(self):
938 """@return: list of tablespaces (updateTablespaceList must be called before !)""" 939 return self.tablespaces
940
941 - def getDefaultTablespace(self, db):
942 """@return: default tablespace name of the user""" 943 self.setName(self.getName().upper()) 944 result = db.executeAll(userSql["defaultTbsFromName"], [self.getName()]) 945 if len(result) == 0: 946 return "" 947 else: 948 return result[0][0]
949
950 - def getTempTablespace(self, db):
951 """@return: temporary tablespace name of the user""" 952 self.setName(self.getName().upper()) 953 result = db.executeAll(userSql["tempTbsFromName"], [self.getName()]) 954 if len(result) == 0: 955 return "" 956 else: 957 return result[0][0]
958
959 - def getNbTables(self, db, tablespace=u"%"):
960 """@return: number of tables owned by the user""" 961 self.setName(self.getName().upper()) 962 result = db.executeAll(userSql["nbTablesFromNameAndTbs"], [self.getName(), tablespace]) 963 if len(result) == 0: 964 return "" 965 else: 966 return result[0][0]
967
968 - def getNbIndexes(self, db, tablespace=u"%"):
969 """@return: number of indexes owned by the user""" 970 self.setName(self.getName().upper()) 971 result = db.executeAll(userSql["nbIndexesFromNameAndTbs"], [self.getName(), tablespace]) 972 if len(result) == 0: 973 return "" 974 else: 975 return result[0][0]
976
977 - def guessStatus(self, db):
978 """Guess user status""" 979 status = db.executeAll(guessInfoSql["userStatusFromName"], [self.getName()]) 980 self.setStatus(status[0][0])
981
982 -class OraView(OraTabular):
983 """Oracle view"""
984 - def __init__(self, viewOwner="", viewName=""):
985 """View creation""" 986 OraObject.__init__(self, viewOwner, viewName, u"VIEW")
987
988 - def getSQL(self, db):
989 """@return: SQL code behind the view""" 990 if self.getOwner() == "": 991 owner = db.getUsername().upper() 992 else: 993 owner = self.getOwner() 994 result = db.executeAll(viewSql["queryFromOwnerAndName"], [owner, self.getName()]) 995 if len(result) == 0: 996 return "" 997 else: 998 return result[0][1]
999
1000 - def setSQL(self, db, sql):
1001 """@return: True if succeeded in editing SQL code behind the view, False otherwise""" 1002 if sql == "": 1003 raise PysqlException(_("SQL code of the view cannot be empty")) 1004 if self.getOwner() == "": 1005 db.execute(viewSql["replaceQueryFromName"] % (self.getName(), sql), fetch=False) 1006 else: 1007 db.execute(viewSql["replaceQueryFromFullName"] % (self.getFullName(), sql), fetch=False)
1008