Package wsatools :: Package DbConnect :: Module Schema
[hide private]

Source Code for Module wsatools.DbConnect.Schema

   1  #------------------------------------------------------------------------------ 
   2  #$Id: Schema.py 9997 2013-08-08 20:05:00Z RossCollins $ 
   3  """ 
   4     Provides access to the database schema. As described in the collection of 
   5     C{*.sql} files in the C{sql} directory. This allows CUs to become schema 
   6     driven. 
   7   
   8     Usage 
   9     ===== 
  10   
  11     Case 1 - Retrieving table schema details 
  12     ---------------------------------------- 
  13   
  14     Call the L{parseTables()} function to retrieve a dictionary of table schemas 
  15     from a supplied .sql schema file, e.g. :: 
  16   
  17       import wsatools.DbConnect.Schema as schema 
  18       tableSchema = schema.parseTables("WSA_MultiframeSchema.sql") 
  19       print(tableSchema[0].name) 
  20       for column in tableSchema[0].columns: 
  21           print(column.name, column.dataType) 
  22   
  23     Read documentation for L{Table} and L{Attribute} classes to see what data is 
  24     available and how to access it. 
  25   
  26     Case 2 - Retrieving table index details 
  27     --------------------------------------- 
  28   
  29     Call the L{parseIndices()} function to retrieve a dictionary of table 
  30     indices, e.g. :: 
  31   
  32       import wsatools.DbConnect.Schema    as schema 
  33       from   wsatools.SystemConstants import WsaConstants 
  34       idxInfo = schema.parseIndices(WsaConstants.indexScript) 
  35       db.addIndex(idxInfo["lasSource"][0]) 
  36       # to apply just the first or to all indices to lasSource: 
  37       db.createObjects(idxInfo["lasSource"]) 
  38   
  39     Read documentation for L{Index} class to see what data is available and how 
  40     to access it. 
  41   
  42     Case 3 - Retrieving view schema details 
  43     --------------------------------------- 
  44   
  45     Call the L{parseViews()} function to retrieve an ordered list of view 
  46     schemas, e.g. :: 
  47   
  48       import wsatools.DbConnect.Schema as schema 
  49       db.createObjects(schema.parseViews()) 
  50   
  51     Schema Objects 
  52     ============== 
  53   
  54     G{classtree: _Schema} 
  55   
  56     @group Errors & Exceptions: MismatchError, ParsingError 
  57   
  58     @author: R.S. Collins 
  59     @org:    WFAU, IfA, University of Edinburgh 
  60   
  61     @todo: Reduce code replication in all parse*() objects 
  62     @todo: Better exception handling on parsing - return file and line number, 
  63            as well as line where problem occurred. Also handling missing files 
  64            better? 
  65     @todo: Rename Attribute Column? 
  66     @todo: Give Attributes create/dropSQL() methods so that add/drop 
  67            column can work like createObject/dropObject. 
  68  """ 
  69  #------------------------------------------------------------------------------ 
  70  from __future__ import division, print_function 
  71   
  72  from   collections import defaultdict 
  73  from   operator    import attrgetter 
  74  import os 
  75   
  76  import wsatools.CSV                   as csv 
  77  import wsatools.DbConnect.DbConstants as dbc 
  78  from   wsatools.Logger            import Logger 
  79  # @@TODO: WsaConstants imported for backwards compatibility only... 
  80  from   wsatools.SystemConstants   import SystemConstants, WsaConstants 
  81  import wsatools.Utilities             as utils 
  82  #------------------------------------------------------------------------------ 
  83   
84 -class Attribute(object):
85 """ A table column attribute schema. 86 """ 87 # Public member variable default values (access as object.varName to set) 88 dataType = '' #: SQL data type of attribute. 89 defaultStr = '' #: The default constraint SQL value. 90 fitsHDU = 0 #: FITS header index where attribute is found. 91 fitsKeyword = '' #: FITS keyword where attribute comes from. 92 isCatFile = False #: Is the attribute from a catalogue file? 93 name = '' #: Name of attribute. 94 sqlLine = '' #: The raw, unparsed SQL schema line. 95 #: Dictionary of user-defined tags referenced by tag name, eg '--/Q'. 96 tag = {} 97 98 #-------------------------------------------------------------------------- 99
100 - def __init__(self):
101 """ Initialises an empty default table column attribute schema. 102 """ 103 self.tag = dict(Attribute.tag) # Make local dictionary from default
104 105 #-------------------------------------------------------------------------- 106
107 - def __cmp__(self, other):
108 """ Overridden to compare based on the name attribute. """ 109 return cmp(self.name, str(other))
110 111 #-------------------------------------------------------------------------- 112
113 - def __str__(self):
114 """ @return: Attribute's name. 115 @rtype: str 116 """ 117 return self.name
118 119 #-------------------------------------------------------------------------- 120
121 - def getDefaultValue(self):
122 """ @return: Attribute's default value with correct python type. 123 @rtype: str, int or float 124 """ 125 if self.isString(): 126 if self.defaultStr: 127 return self.defaultStr.strip("'") 128 else: 129 return self.tag.get('--/N') or (dbc.charDefault() 130 if 'var' in self.dataType else dbc.dateTimeDefault()) 131 132 elif 'int' in self.dataType: 133 if self.defaultStr: 134 return int(self.defaultStr) 135 136 elif self.tag.get('--/N'): 137 return int(self.tag.get('--/N')) 138 139 else: 140 return {'tinyint': dbc.tinyIntDefault(), 141 'smallint': dbc.smallIntDefault(), 142 'int': dbc.intDefault(), 143 'bigint': dbc.intDefault()}[self.dataType] 144 145 elif self.defaultStr: 146 return float(self.defaultStr) 147 148 elif self.tag.get('--/N'): 149 return float(self.tag.get('--/N')) 150 151 else: 152 return {'real': dbc.realDefault(), 153 'float': dbc.floatDefault()}[self.dataType]
154 155 #-------------------------------------------------------------------------- 156
157 - def isNotFitsKey(self):
158 """ 159 @return: True if this attribute is not read from FITS header keywords. 160 @rtype: bool 161 162 """ 163 return self.tag.has_key('--/Q') and self.tag.get('--/Q') != self.name
164 165 #-------------------------------------------------------------------------- 166
167 - def isParsedFitsKey(self):
168 """ 169 @return: True if FITS header keyword value needs to be parsed. 170 @rtype: bool 171 172 """ 173 return self.tag.get('--/Q') == self.name
174 175 #-------------------------------------------------------------------------- 176
177 - def isString(self):
178 """ @return: True if this attribute is a string. 179 @rtype: bool 180 """ 181 return 'var' in self.dataType or self.dataType == 'datetime'
182 183 #-------------------------------------------------------------------------- 184
185 - def parseDefaultValue(self, value):
186 """ 187 Where a different --/N tag value is supplied to the SQL default, the 188 given value should be replaced by the SQL default if it equals the --/N 189 tag value. 190 191 @param value: Value to parse with the correct Python data type. 192 193 @return: Given value unless it equals the --/N tag default value, in 194 which case return the SQL default. 195 196 """ 197 if self.defaultStr and self.tag.get('--/N') \ 198 and value is not None and self.tag.get('--/N') != self.defaultStr: 199 200 if self.dataType in ('float', 'real'): 201 if abs(value - float(self.tag.get('--/N'))) < 0.1: 202 return self.getDefaultValue() 203 204 else: 205 defValue = self.tag.get('--/N') 206 if 'int' in self.dataType: 207 defValue = int(defValue) 208 209 if value == defValue or ('varchar' in self.dataType 210 and not value.strip()): 211 212 return self.getDefaultValue() 213 214 return value
215 216 #-------------------------------------------------------------------------- 217
218 - def parseValue(self, value):
219 """ 220 Takes a value and parses it to the correct Python data type for this 221 attribute, handling default values and common problems. 222 223 @param value: Value read from e.g. FITS header or table, can be any 224 type, typically string or the correct type. 225 226 @return: Given value unless it equals the --/N tag default value, in 227 which case return the SQL default. If it fails, it returns 228 None. 229 230 """ 231 if value is not None: 232 try: 233 if self.dataType.endswith('int'): 234 try: 235 value = int(value) 236 except ValueError as error: 237 ignoreMsgs = ["cannot convert float NaN to integer", 238 "invalid literal for int()"] 239 if all(msg not in str(error) for msg in ignoreMsgs): 240 raise 241 242 value = utils.parseFloat(value) 243 if value is not None: 244 value = int(value) 245 246 elif self.dataType in ('float', 'real'): 247 value = utils.parseFloat(value) 248 249 elif 'varchar' in self.dataType: 250 if not isinstance(value, str): 251 value = str(value) 252 253 if ',' in value: 254 value = value.replace(',', ';') 255 256 elif self.dataType == 'datetime' and value.endswith('60'): 257 value = value.replace('60', '59.9') 258 259 except Exception: 260 Logger.addMessage("<ERROR> Parsing %s" % self) 261 raise 262 263 return self.parseDefaultValue(value)
264 265 #------------------------------------------------------------------------------ 266
267 -class _Schema(object):
268 """ 269 Abstract base class representing a schema for any generic database object. 270 271 """ 272 # Class constants (access as _Schema.varName) 273 identifier = '' #: SQL identifier for this type of database object. 274 275 # Public member variable default values (access as object.varName to set) 276 name = '' #: The name of the object (case-sensitive - expects mixed case). 277 releasable = True #: Can this object be released? 278 schemaFileName = '' #: Name of .sql schema file where this object lies. 279 280 #-------------------------------------------------------------------------- 281
282 - def __cmp__(self, other):
283 """ Compare based on name. """ 284 return cmp(self.name, str(other))
285 286 #-------------------------------------------------------------------------- 287
288 - def __str__(self):
289 """ @return: Object's name. 290 @rtype: str 291 """ 292 return self.name
293 294 #-------------------------------------------------------------------------- 295
296 - def createCommand(self):
297 """ @return: Unique beginning of a create statement for this object in 298 an SQL script. 299 @rtype: str 300 """ 301 return 'CREATE %s ' % self.identifier
302 303 #-------------------------------------------------------------------------- 304
305 - def createSQL(self):
306 """ @return: Full SQL line to create the object. 307 @rtype: str 308 """ 309 return self.createCommand() + self.name
310 311 #-------------------------------------------------------------------------- 312
313 - def dropSQL(self):
314 """ @return: Full SQL line to drop the object. 315 @rtype: str 316 """ 317 return 'DROP %s %s' % (self.identifier, self.name)
318 319 #-------------------------------------------------------------------------- 320
321 - def parseName(self, line, schemaFileName=schemaFileName):
322 """ 323 Sets the _Schema.name and _Schema.releasable variables by parsing the 324 given line. 325 326 @param line: Line containing the create command for the schema object. 327 @type line: str 328 @param schemaFileName: Optionally set the _Schema.schemaFileName 329 variable too. 330 @type schemaFileName: str 331 332 @return: The line split into a list words, for optional further 333 processing. 334 @rtype: list(str) 335 336 """ 337 self.schemaFileName = schemaFileName or self.schemaFileName 338 self.releasable = line[0].isupper() 339 numCreateWords = len(self.createCommand().split()) 340 words = line.split() 341 try: 342 self.name = words[numCreateWords].split('(', 1)[0].rstrip() 343 except IndexError: 344 raise ParsingError(schemaFileName, self) 345 else: 346 return words
347 348 #------------------------------------------------------------------------------ 349
350 -class _Constraint(_Schema):
351 """ A generic table constraint schema. 352 """ 353 # Class constants (access as _Constraint.varName) 354 identifier = 'CONSTRAINT' #: SQL identifier for this type of db object. 355 constraintType = '' #: SQL ID for the specific type of constraint. 356 # Public member variable default values (access as object.varName to set) 357 attrList = '' #: List of attribute names that make up this constraint. 358 tableName = '' #: Table that constraint applies to. 359 withOpts = '' #: WITH options e.g. "NOCHECK". 360 361 #-------------------------------------------------------------------------- 362
363 - def createSQL(self):
364 """ @return: Full SQL line to create the object. 365 @rtype: str 366 """ 367 return "ALTER TABLE %s%s ADD %s %s %s (%s)" % (self.tableName, 368 " WITH " + self.withOpts if self.withOpts else '', self.identifier, 369 self.name, self.constraintType, self.attrList)
370 371 #-------------------------------------------------------------------------- 372
373 - def dropSQL(self):
374 """ @return: Full SQL line to drop the object. 375 @rtype: str 376 """ 377 return "ALTER TABLE %s DROP %s %s" % (self.tableName, self.identifier, 378 self.name)
379 380 #------------------------------------------------------------------------------ 381
382 -class PrimaryKeyConstraint(_Constraint):
383 """ A primary key constraint schema. 384 """ 385 # Class constants (access as PrimaryKeyConstraint.varName) 386 constraintType = 'PRIMARY KEY' #: SQL ID for specific type of constraint.
387 388 #------------------------------------------------------------------------------ 389
390 -class ForeignKeyConstraint(_Constraint):
391 """ A foreign key constraint schema. 392 """ 393 # Class constants (access as ForeignKeyConstraint.varName) 394 constraintType = 'FOREIGN KEY' #: SQL ID for specific type of constraint. 395 # Public member variable default values (access as object.varName to set) 396 cascadeDeletes = False #: Cascade row deletes to referenced tables? 397 referenceTable = '' #: Table this foreign key references. 398 referenceAttrs = '' #: Attribute(s) this foreign key references. 399 releasable = False #: Can this object be released? 400
401 - def createSQL(self):
402 """ @return: Full SQL line to create the object. 403 @rtype: str 404 """ 405 return super(ForeignKeyConstraint, self).createSQL() + " REFERENCES "\ 406 "%s(%s) " % (self.referenceTable, self.referenceAttrs) + \ 407 "ON UPDATE NO ACTION ON DELETE %s" % ( 408 "CASCADE" if self.cascadeDeletes else "NO ACTION")
409 410 #------------------------------------------------------------------------------ 411
412 -class UniquenessConstraint(_Constraint):
413 """ A uniqueness constraint schema. 414 """ 415 # Class constants (access as UniquenessConstraint.varName) 416 constraintType = 'UNIQUE' #: SQL ID for specific type of constraint.
417 418 #------------------------------------------------------------------------------ 419
420 -class Index(_Schema):
421 """ A database non-clustered index schema. 422 """ 423 # Class constants (access as Index.varName) 424 425 #: SQL identifier for this type of database object. 426 identifier = 'INDEX' 427 428 # Public member variable default values (access as object.varName to set) 429 #: Comma-separated list of attribute names that make up this index. 430 attrList = '' 431 fileGroup = 'Indices_FG' #: File group where index is created. 432 fillFactor = None #: Optional page fill factor - an integer percentage. 433 tableName = '' #: Name of the database table that the index applies to. 434 435 #-------------------------------------------------------------------------- 436
437 - def createCommand(self):
438 """ @return: Unique beginning of a create statement for this object in 439 an SQL script. 440 @rtype: str 441 """ 442 return 'CREATE NONCLUSTERED %s ' % self.identifier
443 444 #-------------------------------------------------------------------------- 445
446 - def createSQL(self):
447 """ @return: Full SQL line to create the object. 448 @rtype: str 449 """ 450 return super(Index, self).createSQL() + ( 451 " ON %s (%s)" % (self.tableName, self.attrList) 452 + (" WITH FILLFACTOR = %s" % self.fillFactor 453 if self.fillFactor is not None else '') 454 + ((" ON %s" % self.fileGroup) 455 if self.fileGroup else ''))
456 457 #-------------------------------------------------------------------------- 458
459 - def dropSQL(self):
460 """ @return: Full SQL line to drop the object. 461 @rtype: str 462 """ 463 return 'DROP %s %s.%s' % (self.identifier, self.tableName, self.name)
464 465 #------------------------------------------------------------------------------ 466
467 -class ClusteredIndex(Index):
468 """ A database clustered index schema. 469 """
470 - def createCommand(self):
471 """ @return: Unique beginning of a create statement for this object in 472 an SQL script. 473 @rtype: str 474 """ 475 return 'CREATE UNIQUE CLUSTERED %s ' % self.identifier
476 477 #------------------------------------------------------------------------------ 478
479 -class MismatchError(Exception):
480 """ Exception thrown if schema mis-matches database. 481 """ 482 errorMsg = "" #: Reported error message. 483 brokenTables = None #: Set of schemas for tables with mismatched schemas. 484
485 - def __init__(self, msg, schema):
486 """ 487 @param msg: Error message. 488 @type msg: str 489 @param schema: Schema that is mismatched. 490 @type schema: list(Table) 491 492 """ 493 self.errorMsg = msg 494 self.brokenTables = set(schema) 495 super(MismatchError, self).__init__( 496 "Schema mismatch in tables: %s.\nSynchronise your database schema " 497 "with the schema described in these files: %s" % (self.errorMsg, 498 ', '.join(set(table.schemaFileName for table in self.brokenTables))))
499 500 #------------------------------------------------------------------------------ 501
502 -class ParsingError(Exception):
503 """ Exception thrown if schema parsing fails. 504 """
505 - def __init__(self, schemaFileName, schemaObject):
506 """ 507 @param schemaFileName: File name of schema being parsed. 508 @type schemaFileName: str 509 @param schemaObject: Schema object that couldn't be parsed. 510 @type schemaObject: _Schema 511 512 """ 513 super(ParsingError, self).__init__( 514 "Error parsing %s. %s %s is malformed." % 515 (schemaFileName, schemaObject.__class__.__name__, schemaObject))
516 517 #------------------------------------------------------------------------------ 518
519 -class Procedure(_Schema):
520 """ 521 A database stored procedure schema. Simply consists of name, argument list 522 and definition. 523 524 """ 525 # Class constants (access as Function.varName) 526 #: SQL identifier for this type of database object. 527 identifier = 'PROCEDURE' 528 529 # Public member variable default values (access as object.varName to set) 530 argList = [] #: List of arguments for this procedure. 531 definition = '' #: Procedure definition. 532
533 - def __init__(self):
534 """ Initialises an empty schema procedure object. 535 """ 536 super(Procedure, self).__init__() 537 self.argList = Procedure.argList[:] # Make local list of defaults
538
539 - def createSQL(self):
540 """ @return: Full SQL line to create the object. 541 @rtype: str 542 """ 543 return super(Procedure, self).createSQL() + " %s AS %s" % ( 544 ', '.join(self.argList), self.definition)
545 546 #------------------------------------------------------------------------------ 547
548 -class Function(Procedure):
549 """ 550 A database stored function schema. Simply consists of name, argument 551 list and definition inherited from the Procedure object, as well as a 552 return data type definition. 553 554 """ 555 # Class constants (access as Function.varName) 556 identifier = 'FUNCTION' #: SQL identifier for this type of db object. 557 558 # Public member variable default values (access as object.varName to set) 559 returns = '' #: Return definition. 560
561 - def createSQL(self):
562 """ @return: Full SQL line to create the object. 563 @rtype: str 564 """ 565 return _Schema.createSQL(self) + "(%s) RETURNS %s AS BEGIN %s END" % ( 566 ', '.join(self.argList), self.returns, self.definition)
567 568 #------------------------------------------------------------------------------ 569
570 -class Table(_Schema):
571 """ 572 A database table schema. Consists primarily of an ordered list of 573 L{Attribute} columns, a list of constraints, and a file group. 574 575 @group Nested Error Exceptions: NotFoundError 576 577 """
578 - class NotFoundError(Exception):
579 """ Exception thrown if a requested table is not in the schema file. 580 """
581 - def __init__(self, fileName, tableName):
582 """ 583 @param fileName: Name of schema .sql file searched for table. 584 @type fileName: str 585 @param tableName: Name of table that cannot be found. 586 @type tableName: str 587 588 """ 589 super(Table.NotFoundError, self).__init__( 590 "Whilst parsing schema file %s cannot find table called %s" 591 % (fileName, tableName))
592 593 #-------------------------------------------------------------------------- 594 # Class constants (access as Table.varName) 595 596 identifier = 'TABLE' #: SQL identifier for this type of database object. 597 598 #-------------------------------------------------------------------------- 599 # Public member variable default values (access as object.varName to set) 600 601 # @@TODO: Combine attribute and columns into an ordered dictionary? 602 #: Dictionary access to specific column attributes referenced by name. 603 attribute = {} 604 columns = [] #: Ordered list of table column attribute objects. 605 constraints = [] #: List of constraints for this table. 606 fileGroup = '' #: Create table in this file group. 607 608 # Private member variables 609 _bytePos = None #: Stores positions of each column in bytes. 610 611 #-------------------------------------------------------------------------- 612
613 - def __init__(self):
614 """ Initialises an empty schema table object. 615 """ 616 super(Table, self).__init__() 617 # Make local lists and dictionaries as copies of the default values 618 self.attribute = dict(Table.attribute) 619 self.columns = Table.columns[:] 620 self.constraints = Table.constraints[:]
621 622 #-------------------------------------------------------------------------- 623
624 - def __len__(self):
625 """ @return: Number of columns in the table. 626 @rtype: int 627 628 @note: Required to test against empty Table objects, with 'not' op. 629 """ 630 return len(self.columns)
631 632 #-------------------------------------------------------------------------- 633
634 - def getBytePos(self, colName=''):
635 """ @return: Position of the given column in the table in number of 636 bytes. Returns table width if column not supplied and None 637 if column not in table. 638 @rtype: int 639 640 @param colName: Name of column (case insensitive). 641 @type colName: str 642 """ 643 if not self._bytePos: 644 self._bytePos = {} 645 pos = 0 646 for column in self.columns: 647 self._bytePos[column.name.lower()] = pos 648 pos += SystemConstants.sqlDataTypeSize[column.dataType] 649 self._bytePos[''] = pos 650 651 return self._bytePos.get(colName.lower())
652 653 #-------------------------------------------------------------------------- 654
655 - def isMfJoin(self):
656 """ @return: True if this table can be joined with table Multiframe. 657 @rtype: bool 658 """ 659 return "multiframeID" in self.columns and self.name != "Multiframe"
660 661 #-------------------------------------------------------------------------- 662
663 - def primaryKey(self, alias=''):
664 """ @return: Primary key for the table expressed as a comma-separated 665 list of attributes. None is returned if no primary key. 666 @rtype: str or None 667 668 @param alias: Optionally append this alias to the attribute list. 669 @type alias: str 670 """ 671 for constraint in self.constraints: 672 if type(constraint) is PrimaryKeyConstraint: 673 if alias: 674 return ', '.join(alias + '.' + column 675 for column in csv.values(constraint.attrList)) 676 else: 677 return constraint.attrList
678 679 #-------------------------------------------------------------------------- 680
681 - def createSQL(self):
682 """ @return: Full SQL line to create the object. 683 @rtype: str 684 """ 685 constraintSql = [ 686 constraint.createSQL().replace("ALTER TABLE %s ADD " % self.name, "") 687 for constraint in self.constraints 688 if type(constraint) is not ForeignKeyConstraint] 689 690 sql = super(Table, self).createSQL() + " (%s)" % \ 691 ", ".join([attr.sqlLine for attr in self.columns] + constraintSql) 692 if self.fileGroup: 693 sql += " ON " + self.fileGroup 694 return sql
695 696 #------------------------------------------------------------------------------ 697
698 -class View(_Schema):
699 """ A database view schema. Simply consists of name and definition. 700 """ 701 # Class constants (access as View.varName) 702 #: SQL identifier for this type of database object. 703 identifier = 'VIEW' 704 705 # Public member variable default values (access as object.varName to set) 706 definition = '' #: View definition in SQL. 707 isSchemaBound = False #: Turn on schemabinding option 708
709 - def createSQL(self):
710 """ @return: Full SQL line to create the object. 711 @rtype: str 712 """ 713 return super(View, self).createSQL() + "%s AS %s" % ( 714 " WITH schemabinding" if self.isSchemaBound else "", self.definition)
715 716 #------------------------------------------------------------------------------ 717
718 -def parseFuncProcs(schemaFileName):
719 """ 720 Parses the supplied .sql schema, returning an ordered list of L{Procedure} 721 and L{Function} objects. 722 723 @param schemaFileName: Name of .sql schema file to parse. 724 @type schemaFileName: str 725 726 @return: A list of L{Procedure} and L{Function} objects ordered as supplied 727 in the script file. 728 @rtype: list(Procedure) 729 730 """ 731 scriptPath = os.path.join(SystemConstants(schemaFileName).sqlScriptPath, 732 schemaFileName) 733 734 curProc = None 735 defList = [] 736 procSchema = [] 737 parsingProc = False 738 parsingDef = False 739 inComment = False 740 for line in file(scriptPath): 741 line = line.strip() 742 if parsingProc: 743 if inComment: 744 inComment = '*/' not in line 745 elif line.upper().startswith('GO'): 746 defStr = ' '.join(defList) 747 curProc.definition = defStr[:defStr.rfind('END')].rstrip() 748 procSchema.append(curProc) 749 parsingProc = False 750 parsingDef = False 751 elif line.upper().startswith('RETURNS'): 752 el = line.split()[1:] + ['--'] 753 curProc.returns = ' '.join(el[:el.index('--')]) 754 elif not line.startswith('--') and not line.startswith('/*'): 755 if parsingDef: 756 el = line.split() + ['--'] 757 defList.append(' '.join(el[:el.index('--')])) 758 elif line.upper().startswith('AS'): 759 parsingDef = True 760 elif type(curProc) is Function: 761 el = line.split() + ['--'] 762 curProc.returns += ' ' + ' '.join(el[:el.index('--')]) 763 elif type(curProc) is Procedure: 764 el = line.split() + ['--'] 765 curProc.argList.append(' '.join(el[:el.index('--')] 766 ).rstrip(',')) 767 elif line.startswith('/*') and '*/' not in line: 768 inComment = True 769 elif line.upper().startswith(Procedure().createCommand()): 770 curProc = Procedure() 771 curProc.parseName(line, schemaFileName) 772 defList = [] 773 parsingProc = True 774 elif line.upper().startswith(Function().createCommand()): 775 curProc = Function() 776 curProc.parseName(line, schemaFileName) 777 try: 778 args = line.split('(', 1)[1] 779 curProc.argList = csv.values(args.rsplit(')', 1)[0]) 780 except IndexError: 781 raise ParsingError(schemaFileName, curProc) 782 else: 783 defList = [] 784 parsingProc = True 785 786 return procSchema
787 788 #------------------------------------------------------------------------------ 789 790 # @@TODO: This default value needs to go - just there for backwards compat.
791 -def parseIndices(schemaFileName=WsaConstants.indexScript):
792 """ 793 Parses the supplied .sql schema, returning a dictionary of L{Index} 794 objects referenced by table name representing the indices defined that 795 schema script for that table. 796 797 @param schemaFileName: Name of .sql schema file to parse. 798 @type schemaFileName: str 799 800 @return: Dictionary of L{Index} object lists for every table, referenced by 801 table name. 802 @rtype: defaultdict(str: list(Index)) 803 804 """ 805 scriptPath = os.path.join(SystemConstants(schemaFileName).sqlScriptPath, 806 schemaFileName) 807 808 indexSchema = defaultdict(list) 809 curIndex = None 810 for line in file(scriptPath): 811 line = line.strip() 812 for Object in [Index, ClusteredIndex]: 813 if line.upper().startswith(Object().createCommand()): 814 curIndex = Object() 815 break 816 817 if curIndex: 818 details = curIndex.parseName(line, schemaFileName) 819 upperDetails = line.upper().split() 820 try: 821 curIndex.tableName = details[upperDetails.index("ON") + 1] 822 curIndex.attrList = line.partition('(')[2].partition(')')[0] 823 try: 824 withPos = upperDetails.index("WITH") 825 except ValueError: 826 pass 827 else: 828 try: 829 onPos = upperDetails.index("ON", withPos + 1) 830 except ValueError: 831 onPos = None 832 833 withStr = ' '.join(details[withPos + 1: onPos])\ 834 .lstrip('(').rstrip(')').upper() 835 836 for option in csv.values(withStr): 837 name, value = option.split('=') 838 if name.strip() == "FILLFACTOR": 839 curIndex.fillFactor = int(value.strip()) 840 841 parts = line.replace(" ON ", " on ").split(" on ") 842 curIndex.fileGroup = (parts[2] if len(parts) == 3 else None) 843 844 except ValueError: 845 raise ParsingError(schemaFileName, curIndex) 846 else: 847 indexSchema[curIndex.tableName].append(curIndex) 848 curIndex = None 849 850 return indexSchema
851 852 #------------------------------------------------------------------------------ 853
854 -def parseTables(schemaFileName, tableList=[], reqAllTables=True):
855 """ 856 Parses the supplied .sql schema file, returning L{Table} objects for every 857 table described in the file. (Unless a subset is requested). 858 859 @note: The .sql file is assumed to be in the 860 L{SystemConstants.sqlScriptPath}. Include any sub-directory names 861 in schemaFileName, e.g. "NonSurvey/WSA_Schema.sql". Use 862 L{DataFactory.ProgrammeTable.getSchemaScript()} for schemaFileName 863 to parse programme schemas (e.g. lasSchema.sql) as this 864 automatically handles non-surveys correctly. 865 866 @param schemaFileName: Name of .sql schema file to parse. 867 @type schemaFileName: str 868 @param tableList: Optional list of tables to be extracted, by 869 default all tables are extracted. 870 @type tableList: list(str) 871 @param reqAllTables: If True, an exception is thrown if any tables in 872 the supplied list are missing from the schema. 873 @type reqAllTables: bool 874 875 @return: A list of L{Table} objects in the order given by the schema file. 876 @rtype: list(Table) 877 878 """ 879 scriptPath = os.path.join(SystemConstants(schemaFileName).sqlScriptPath, 880 schemaFileName) 881 tableSchemas = [] 882 curTable = None 883 parsingTable = False 884 parsingConstraints = True 885 for line in file(scriptPath): 886 line = line.strip() 887 if parsingTable: 888 if line.upper().startswith('CONSTRAINT'): 889 if PrimaryKeyConstraint.constraintType in line.upper(): 890 curCons = PrimaryKeyConstraint() 891 elif UniquenessConstraint.constraintType in line.upper(): 892 curCons = UniquenessConstraint() 893 curCons.name = line.split()[1] 894 curCons.attrList = line.partition('(')[2].partition(')')[0] 895 curCons.tableName = curTable.name 896 if type(curCons) is PrimaryKeyConstraint: 897 # PrimaryKeys need to be applied before others 898 curTable.constraints.insert(0, curCons) 899 else: 900 curTable.constraints.insert(1, curCons) 901 elif line.startswith(')'): 902 if 'ON' in line: 903 curTable.fileGroup = line.split()[-1] 904 tableSchemas.append(curTable) 905 parsingTable = False 906 elif not line.startswith('--'): 907 detailsList = line.split() 908 if len(detailsList) > 1: 909 attr = _parseAttribute(detailsList) 910 curTable.columns.append(attr) 911 curTable.attribute[attr.name] = attr 912 elif line.startswith('--/T Required constraints:'): 913 parsingConstraints = True 914 elif parsingConstraints and line.startswith('-----'): 915 parsingConstraints = False 916 if parsingConstraints: 917 if 'references' in line.lower(): 918 curCons = ForeignKeyConstraint() 919 curCons.tableName = curTable.name 920 curCons.attrList = line.partition('(')[2].partition(')')[0] 921 curCons.referenceTable, \ 922 curCons.referenceAttrs = line.split()[3].split('(') 923 curCons.referenceAttrs = \ 924 curCons.referenceAttrs.rstrip('n').rstrip(')') 925 curCons.cascadeDeletes = (line.strip().lower()[-1] != 'n') 926 curCons.name = 'fk_%s_%s_to_%s_%s' % (curCons.tableName, 927 curCons.attrList.replace(",", "_"), 928 curCons.referenceTable, 929 curCons.referenceAttrs.replace(",", "_")) 930 curTable.constraints.append(curCons) 931 elif line.upper().startswith(Table().createCommand()): 932 curTable = Table() 933 curTable.parseName(line, schemaFileName) 934 parsingTable = not tableList or curTable.name in tableList 935 936 missingTables = set(tableList) - set(table.name for table in tableSchemas) 937 if missingTables: 938 if reqAllTables: 939 raise Table.NotFoundError(schemaFileName, 940 ' or '.join(missingTables)) 941 else: 942 # Prepare with empty tables so that returned product is guaranteed 943 # to contain entries for every requested table, empty or otherwise 944 for tableName in missingTables: 945 Logger.addMessage("<Warning> You haven't added an entry for " 946 "table %s into %s yet!" % (tableName, schemaFileName)) 947 emptyTable = Table() 948 emptyTable.name = tableName 949 tableSchemas.append(emptyTable) 950 951 return (tableSchemas if not tableList else # Order by table list order 952 utils.arbSort(tableSchemas, tableList, key=attrgetter('name'), 953 isFullKeySet=not missingTables))
954 955 #------------------------------------------------------------------------------ 956
957 -def parseViews(schemaFileName):
958 """ 959 Parses the supplied .sql schema, returning an ordered list of L{View} 960 objects. 961 962 @param schemaFileName: Name of .sql schema file to parse. 963 @type schemaFileName: str 964 965 @return: A list of L{View} objects ordered as supplied in the script file. 966 @rtype: list(View) 967 968 """ 969 scriptPath = os.path.join(SystemConstants(schemaFileName).sqlScriptPath, 970 schemaFileName) 971 972 defList = [] 973 curView = None 974 viewSchema = [] 975 parsingView = False 976 for line in file(scriptPath): 977 line = line.strip() 978 if parsingView: 979 if line.startswith('GO'): 980 curView.definition = ' '.join(defList).replace('AS ', '', 1) 981 viewSchema.append(curView) 982 parsingView = False 983 elif line.lower().startswith('with schemabinding'): 984 curView.isSchemaBound = True 985 elif not line.startswith('--') and not line.startswith('/*'): 986 defList.append(' '.join(line.split())) 987 elif line.upper().startswith(View().createCommand()): 988 curView = View() 989 curView.parseName(line, schemaFileName) 990 defList = [] 991 parsingView = True 992 993 return viewSchema
994 995 #------------------------------------------------------------------------------ 996
997 -def _parseAttribute(detailsList):
998 """ 999 Parse the list of properties for this attribute supplied by 1000 L{parseTables()} from the .sql schema file. This consists of both SQL 1001 attribute and user-defined tag properties. 1002 1003 @param detailsList: The unprocessed attribute line from the .sql file 1004 split on whitespace. 1005 @type detailsList: list(str) 1006 1007 @return: The attribute object containing the full schema for this 1008 attribute. 1009 @rtype: Attribute 1010 1011 """ 1012 attr = Attribute() 1013 attr.name = detailsList[0] 1014 attr.dataType = detailsList[1] 1015 1016 # Extract remaining sql details and user details 1017 key = '' # need initial key in case junk exists before 1st key 1018 sqlDetails = [] # temporary list of just SQL details 1019 tagProcessing = False 1020 for index, detail in enumerate(detailsList): 1021 if not tagProcessing and not detail.startswith('--/'): 1022 sqlDetails.append(detail) 1023 if detail.lower() == 'default': 1024 attr.defaultStr = detailsList[index + 1].rstrip(',') 1025 else: 1026 tagProcessing = True 1027 if detail.startswith('--/'): 1028 key = detail 1029 else: 1030 try: 1031 attr.tag[key] += ' ' + detail 1032 except KeyError: 1033 attr.tag[key] = detail 1034 1035 attr.sqlLine = ' '.join(sqlDetails).rstrip(',') 1036 1037 fitsKey = attr.tag.get('--/K', '') 1038 tableField = attr.tag.get('--/F') 1039 if '.' in fitsKey: 1040 location, attr.fitsKeyword = fitsKey.rsplit('.', 1) 1041 elif tableField and '.' in tableField: 1042 attr.fitsHDU = {} 1043 for location in attr.tag.get('--/F').split(','): 1044 if '+' in location: 1045 location = location.split('+')[-1] 1046 1047 hdu, field = location.split('.') 1048 hdu = hdu.upper() 1049 if hdu.startswith("NONAME"): 1050 hdu = int(hdu.replace("NONAME", '')) 1051 1052 field = field.upper() 1053 if field.startswith("COL"): 1054 field = int(field.replace("COL", '')) 1055 1056 # @TODO: Should always be a set, but this keeps backward compat. 1057 if hdu not in attr.fitsHDU: 1058 attr.fitsHDU[hdu] = field 1059 elif isinstance(attr.fitsHDU[hdu], set): 1060 attr.fitsHDU[hdu].add(field) 1061 else: 1062 attr.fitsHDU[hdu] = {attr.fitsHDU[hdu], field} 1063 1064 return attr 1065 else: 1066 location, attr.fitsKeyword = '', fitsKey 1067 1068 if location == '': 1069 attr.fitsHDU = 0 1070 elif location == 'IMAGE': 1071 attr.fitsHDU = 1 1072 elif location == 'BINTABLE': 1073 attr.fitsHDU = 1 1074 attr.isCatFile = True 1075 elif '.' in location: 1076 attr.fitsHDU = location.split('.')[-1].upper() 1077 else: 1078 attr.fitsHDU = -1 1079 1080 return attr
1081 1082 #------------------------------------------------------------------------------ 1083 # Change log: 1084 # 1085 # 27-Feb-2007, RSC: Original version, based on the contents of the 1086 # wsatools.Schema package modules - SchemaParser.py and 1087 # SchemaAttribute.py 1088