Package helpers :: Module SyncDb
[hide private]

Source Code for Module helpers.SyncDb

  1  #! /usr/bin/env python 
  2  #------------------------------------------------------------------------------ 
  3  #$Id: SyncDb.py 10194 2014-01-21 12:28:15Z RossCollins $ 
  4  """ 
  5     Fills an empty database with the latest data in the WSA/VSA. Will also 
  6     update the database schema and/or recreate the database from scratch. By 
  7     default, the full date range of B{non-deprecated} data is synchronised. 
  8   
  9     @note: For the detection tables of deep surveys, i.e. UDS and DXS, only the 
 10            detections from the deep stacks are synchronised, as these are the 
 11            only ones that contribute to source tables. If you specify the 
 12            -a/--all option, then all detections are included. 
 13   
 14     @todo: Due to the way indices are defined this may be faster if we were able 
 15            to make the primary keys the real primary keys, so instead of just 
 16            querying MFD on multiframeID, do it on the seemingly unnecessary 
 17            multiframeID and extNum. However, not sure how to do within a NOT IN 
 18            (SELECT attr FROM...) context. 
 19     @todo: Optimise date range updates. 
 20   
 21     @author: R.S. Collins 
 22     @org:    WFAU, IfA, University of Edinburgh 
 23  """ 
 24  #------------------------------------------------------------------------------ 
 25  from __future__      import division, print_function 
 26  from future_builtins import map, zip 
 27  import os 
 28   
 29  from   wsatools.CLI                 import CLI 
 30  import wsatools.CSV                     as csv 
 31  import wsatools.DataFactory             as df 
 32  from   wsatools.DbConnect.CuSession import CuSession 
 33  import wsatools.DbConnect.DbConstants   as dbc 
 34  from   wsatools.DbConnect.DbSession import Database, DbSession, odbc, \ 
 35                                             SelectSQL, bulkCopy, Ingester, \ 
 36                                             Outgester 
 37  import wsatools.DbConnect.Schema        as schema 
 38  from   wsatools.Logger              import Logger 
 39  from   wsatools.SystemConstants     import SystemConstants 
 40  #------------------------------------------------------------------------------ 
 41   
42 -class SyncDb(CuSession):
43 """ 44 Updates a database with the latest schema and data from another database up 45 to the given CU level (0 = curation-data; 1 = transfer-data; 3 = metadata; 46 4 = detections; 7 = sources; 16 = neighbours). 47 48 """ 49 # Define class parameters 50 cuNum = 22 51 _autoCommit = True 52 _isPersistent = True 53 54 #-------------------------------------------------------------------------- 55 # Define public member variable default values (access as obj.varName) 56 # these need to be set from command-line options 57 58 #: Copy all data not just non-deprecated data? 59 copyAllData = False 60 #: Copy data up to this curation level. 61 cuLevel = 3 62 #: Copy data between these dates. 63 dateRange = CuSession.sysc.obsCal.dateRange() 64 #: Exclude these non-survey programmes from the world release. 65 excludeProgs = tuple() 66 #: Only copy data through SQL interface - don't do bulk copies? 67 forceSQL = False 68 #: Source database to synchronise from. 69 fromDbPathName = None 70 #: Copy merge log table data when synchronising at any level. 71 includeMergeLog = False 72 #: Just copy metadata and curation tables into a mirror release database. 73 isMetadataMirror = False 74 #: Create a new database before syncing? 75 overwriteDb = False 76 #: Don't synchronise provenance? 77 skipProvenance = False 78 79 # Private member variables 80 #: Connection to the source database (usually the load database). 81 _fromDb = None 82 83 #-------------------------------------------------------------------------- 84
85 - def _onRun(self):
86 """ Synchronises the database. 87 """ 88 self.programmeID = set([self.programmeID] if self.programmeID else 89 self.programme.getProgIDList(onlyNonSurvey=self.onlyNonSurveys)) 90 91 if self.excludeProgs: # Remove those requested 92 self.programmeID -= \ 93 set(self.programme.setProgID(prog) for prog in self.excludeProgs) 94 95 Logger.addMessage("Parsing schemas...") 96 97 idxInfo = schema.parseIndices(self.sysc.indexScript) 98 objectSchema = (schema.parseFuncProcs(self.sysc.procScript) 99 + schema.parseFuncProcs(self.sysc.surveyProcScript) 100 + schema.parseViews(self.sysc.surveyViewScript)) 101 102 if self.overwriteDb: 103 tableSchema = schema.parseTables(self.sysc.curationSchema(), 104 ["FlatFileLookUp"]) 105 else: 106 tableSchema = schema.parseTables(self.sysc.curationSchema()) 107 tableSchema.remove("ArchiveCurationHistory") 108 tableSchema.remove("ProgrammeCurationHistory") 109 110 if self.cuLevel is 0: 111 tableSchema.remove("FlatFileLookUp") 112 113 if self.cuLevel >= 3: 114 metadataTables = schema.parseTables(self.sysc.metadataSchema()) 115 if self.skipProvenance: 116 metadataTables.remove("Provenance") 117 118 mfEsoTable = "MultiframeEsoKeys" 119 if not self.copyAllData and mfEsoTable in metadataTables: 120 metadataTables.remove(mfEsoTable) 121 122 mfdEsoTable = "MultiframeDetectorEsoKeys" 123 if (not self.copyAllData or self.isMetadataMirror) \ 124 and mfdEsoTable in metadataTables: 125 126 metadataTables.remove(mfdEsoTable) 127 128 tableSchema += metadataTables 129 130 if self.cuLevel >= 4 or self.includeMergeLog: 131 # Determine the list of files determining table schemas 132 progScripts = set() 133 for progID in self.programmeID: 134 self.programme.setCurRow(programmeID=progID) 135 progScripts.add(self.programme.getSchemaScript()) 136 if self.cuLevel == 16: 137 progScripts.add(self.programme.getAttr("neighboursSchema")) 138 139 if self.sysc.isVSA() \ 140 and self.sysc.scienceProgs.get("VVV") in self.programmeID: 141 monthlySchemas = [os.path.join(self.sysc.sqlMonthlyDetPath(), f) 142 for f in os.listdir(self.sysc.sqlMonthlyDetPath()) 143 if "vvvSchema20" in f] 144 for monSch in monthlySchemas: 145 month = monSch[monSch.find("vvvSchema") + 9:monSch.rfind('.')] 146 detTables = ["vvvDetectionRaw" + month, 147 "vvvDetectionAstrometry" + month, 148 "vvvDetectionPhotometry" + month] 149 tableSchema += schema.parseTables(monSch, detTables) 150 151 if self.includeMergeLog: 152 mergeLogSchema = [] 153 for script in progScripts: 154 if script.upper() != dbc.charDefault(): 155 mergeLogSchema += schema.parseTables(script) 156 157 for table in list(map(str, mergeLogSchema)): 158 if 'MergeLog' not in table: 159 mergeLogSchema.remove(table) 160 161 tableSchema += mergeLogSchema 162 163 elif self.cuLevel >= 4: 164 # Parse table schemas 165 for script in progScripts: 166 if script.upper() != dbc.charDefault(): 167 tableSchema += schema.parseTables(script) 168 169 if self.cuLevel < 7: 170 phrases = ['Source', 'MergeLog', 'Synoptic', 'Var', 171 'BestMatch', 'BestAper'] 172 173 # NB: Just making a copy of tableSchema so can delete from it 174 for table in list(map(str, tableSchema)): 175 if any(phrase in table for phrase in phrases): 176 tableSchema.remove(table) 177 178 # Open separate connection to VVV if need to copy vvvSchema tables 179 if self.sysc.isVSA() \ 180 and self.sysc.scienceProgs.get("VVV") in self.programmeID: 181 182 # @TODO: VVV should have its own SystemConstants so server 183 # recognition is automatic! 184 dbs = ['.'.join(item) 185 for item in self.sysc.ingestDbForServer.items()] 186 187 vvvDatabase = [db for db in dbs if "VVV" in db].pop() 188 self._fromDbVVV = DbSession(vvvDatabase, 189 autoCommit=self._autoCommit, 190 isTrialRun=self.archive.isTrialRun, 191 userName=dbc.loadServerRwUsername()) 192 193 self._fromDbVVV.enableDirtyRead() 194 195 self._fromDb = DbSession(self.fromDbPathName, 196 autoCommit=self._autoCommit, isTrialRun=self.archive.isTrialRun, 197 userName=dbc.loadServerRwUsername()) 198 199 self._fromDb.enableDirtyRead() 200 201 Logger.addMessage("Synchronising tables...") 202 203 for table in tableSchema: 204 try: # Check database schema 205 self.archive.checkSchema([table]) 206 except schema.MismatchError as error: 207 if not self.overwriteDb: 208 Logger.addMessage("Schema mis-match: %s. " 209 "Dropping and recreating table %s in database %s" % 210 (error.errorMsg, table, self.archive.database)) 211 else: 212 Logger.addMessage("Creating table %s..." % table) 213 try: 214 if self.overwriteDb: 215 self.archive.createTable(table) 216 else: 217 self.archive.createTable(table, tableSchema, 218 overWrite=True) 219 except Exception: 220 Logger.addMessage("<ERROR> Cannot update %s" % table) 221 raise 222 223 if not ("vvvDetection" in table.name and "20" in table.name): 224 self._updateTable(table) 225 226 # Only apply indices when it's a release database 227 if self.isMetadataMirror and idxInfo[table.name]: 228 Logger.addMessage("Applying indices...") 229 self.archive.createObjects(idxInfo[table.name]) 230 231 Logger.addMessage("Creating any new views and stored db functions...") 232 self.archive.createObjects(objectSchema, ignoreNS=True) 233 234 # Update index statistics - not sure if this is needed. Sometimes 235 # indices weren't being used prior to this running. 236 if self.isMetadataMirror: 237 self.archive.updateStatistics() 238 239 Logger.addMessage("done!")
240 241 #-------------------------------------------------------------------------- 242
243 - def _updateTable(self, table):
244 """ 245 Update contents of table. 246 247 @param table: Table details. 248 @type table: Schema.Table 249 250 """ 251 Logger.addMessage("Updating table %s..." % table) 252 253 fromDb = (self._fromDb if not table.name.lower().startswith("vvv") else 254 self._fromDbVVV) 255 256 fromDb.enableDirtyRead() 257 258 sourceTable = table.name 259 mfTable = sourceTable.replace(table.name, "Multiframe") 260 alias = '' 261 columns = "*" 262 key = ('multiframeID' if 'multiframeID' in table.columns else 263 table.primaryKey().split(',')[0]) 264 265 where = '' 266 if not self.overwriteDb: 267 currentData = SelectSQL(key, self.archive.tablePath(table.name)) 268 where = "%s NOT IN (%s)" % (key, currentData) 269 270 if self.programmeID != set(self.programme.getProgIDList()): 271 progSelection = ("programmeID IN (%s, %s)" % 272 (dbc.intDefault(), ', '.join(map(str, self.programmeID)))) 273 progMfs = SelectSQL("multiframeID", 274 table=self._fromDb.tablePath("ProgrammeFrame"), 275 where=progSelection) 276 else: 277 progSelection = "" 278 279 needMultiframeAttrs = \ 280 not (self.copyAllData and self.dateRange == SyncDb.dateRange) 281 282 if table.name == "Provenance": 283 # Provenance SQL is hard-wired, as it is doubly joined with 284 # multiframe and just selecting on date doesn't keep Provenance 285 # completely consistent with multiframe. 286 if progSelection: 287 if where and not where.endswith(" AND "): 288 where += " AND " 289 where += "multiframeID IN (%s)" % progMfs 290 291 if self.dateRange != SyncDb.dateRange: 292 if where and not where.endswith(" AND "): 293 where += " AND " 294 295 dStr = "utdate BETWEEN '%s' AND '%s'" % self.dateRange 296 where += "(utdate='%s' OR %s)"\ 297 % (self.sysc.obsCal.lateDefaultDateTime, dStr) 298 299 if not self.copyAllData: 300 if where and not where.endswith(" AND "): 301 where += " AND " 302 where += "deprecated = 0" 303 304 currentMfs = \ 305 SelectSQL("multiframeID", fromDb.tablePath("Multiframe"), where) 306 307 where = ("combiframeID IN (%s) AND " % currentMfs + 308 "multiframeID IN (%s)" % currentMfs) 309 310 if not self.overwriteDb: 311 where += " AND NOT (combiframeID IN (%s)"\ 312 " AND multiframeID IN (%s))" % ( 313 SelectSQL("combiframeID", self.archive.tablePath("Provenance")), 314 SelectSQL("multiframeID", self.archive.tablePath("Provenance"))) 315 316 elif table.name.endswith("TilePawPrints"): 317 # TilePawPrint SQL is hard-wired as it depends on tileSetID 318 currentTiles = SelectSQL("tileSetID", 319 self.archive.tablePath(table.name.replace("PawPrints", "Set"))) 320 321 where = "tileSetID IN (%s)" % currentTiles 322 323 elif progSelection and 'programmeID' in table.columns and \ 324 not needMultiframeAttrs: 325 where += " AND " if where else "" 326 where += progSelection 327 328 elif ('multiframeID' in table.columns or table.name.endswith("TileSet") 329 and (needMultiframeAttrs or 'programmeID' not in table.columns)): 330 331 alias = "This" 332 columns = "%s.%s" % (alias, columns) 333 if where: 334 where = "%s.%s" % (alias, where) 335 336 if table.name != "Multiframe": 337 mfAlias = 'M' 338 mfIdName = ("multiframeID" 339 if not table.name.endswith("TileSet") else "tlmfID") 340 sourceTable = \ 341 "%s AS %s, %s AS %s" % (sourceTable, alias, mfTable, mfAlias) 342 if where and not where.endswith(" AND "): 343 where += " AND " 344 where += "%s.%s = %s.multiframeID" % (alias, mfIdName, mfAlias) 345 if progSelection: 346 where += " AND " 347 where += (progSelection if 'programmeID' in table.columns 348 else "%s.multiframeID IN (%s)" % (mfAlias, progMfs)) 349 else: 350 mfAlias = alias 351 sourceTable = "%s AS %s" % (sourceTable, mfAlias) 352 if progSelection: 353 if where and not where.endswith(" AND "): 354 where += " AND " 355 where += "%s.multiframeID IN (%s)" % (mfAlias, progMfs) 356 357 if self.dateRange != SyncDb.dateRange: 358 if where and not where.endswith(" AND "): 359 where += " AND " 360 361 dStr = "utdate BETWEEN '%s' AND '%s'" % self.dateRange 362 where += "(%s.utdate='%s' OR %s.%s)"\ 363 % (mfAlias, self.sysc.obsCal.lateDefaultDateTime, mfAlias, 364 dStr) 365 366 if not self.copyAllData: 367 if where and not where.endswith(" AND "): 368 where += " AND " 369 where += "%s.deprecated = 0" % mfAlias 370 if table.name.startswith(("dxsDetection", "udsDetection", 371 "videoDetection")): 372 where += \ 373 " AND (%s.frameType LIKE '%%deep%%' OR %s.frameType=%r)"\ 374 % (mfAlias, mfAlias, dbc.charDefault()) 375 376 try: 377 # Bulk copy is faster for large tables, otherwise SQL copy 378 if not self.forceSQL and (table.name == "FlatFileLookUp" 379 or table.schemaFileName != self.sysc.curationSchema()): 380 381 orderBy = table.primaryKey(alias) + " ASC" 382 sourceTable = ', '.join(fromDb.tablePath(tableName) 383 for tableName in csv.values(sourceTable)) 384 385 Logger.addMessage("Outgesting %s..." % table) 386 bulkCopy(query=SelectSQL(columns, sourceTable, where, orderBy), 387 tableSchema=table, 388 fromDb=fromDb, 389 toDb=self.archive, 390 fileTag=self.shareFileID) 391 else: 392 numRows = fromDb.copyIntoTable( 393 destinationTable=self.archive.tablePath(table.name), 394 sourceTable=sourceTable, 395 columns=columns, 396 where=where) 397 if numRows: 398 Logger.addMessage("%s copied" % numRows) 399 400 except (odbc.DatabaseError, Ingester.IngestError) as error: 401 ignoreCases = ["Column name or number", "Invalid object name"] 402 403 if (isinstance(error, Ingester.IngestError) 404 or any(case in str(error) for case in ignoreCases)) \ 405 and Outgester.tempViewName not in str(error): 406 407 Logger.addMessage( 408 "<WARNING> Cannot update table %s because %s schema differs " 409 "from that of the destination database." % 410 (self.archive.tablePath(table.name), fromDb.database)) 411 412 if self.isMetadataMirror: 413 # As much as it seems like a good idea to at least release 414 # an incomplete database if this happens, I think it is 415 # better to halt and send an error e-mail to the operator. 416 raise 417 else: 418 raise
419 420 #-------------------------------------------------------------------------- 421
422 - def _onException(self):
423 """ Don't update programme curation history on exception - this is just 424 to keep the exception message clear in the displayed log. Following 425 exception the script will probably be re-run anyway. 426 """ 427 super(SyncDb, self)._onException() 428 self.programmeID = set()
429 430 #------------------------------------------------------------------------------ 431
432 -def createDatabase(database, server, cli):
433 """ 434 Creates a new database, overwriting the old one, with all necessary tables 435 and initial curation data. 436 437 @param database: Database object to create. 438 @type database: Database 439 @param server: Name of server to create database on. 440 @type server: str 441 @param cli: Command-line options. 442 @type cli: CLI 443 444 """ 445 # Ensure source database connection can be opened prior to overwriting 446 # the destination database. 447 fromDb = DbSession(cli.getArg("from_database"), autoCommit=True, 448 isTrialRun=cli.getOpt('test'), userName=dbc.loadServerRwUsername()) 449 450 fromDb.enableDirtyRead() 451 452 # And that the curation and metadata schema is up-to-date 453 Logger.addMessage("Parsing schemas...") 454 curationTables = schema.parseTables(SyncDb.sysc.curationSchema()) 455 multiframeTables = schema.parseTables(SyncDb.sysc.metadataSchema()) 456 mirrorTables = curationTables + multiframeTables 457 if cli.getOpt("metadata_mirror"): 458 # Only check for metadata mirrors, as we may be intentionally creating 459 # a database with a different schema for test databases. 460 Logger.addMessage("Checking for database schema mismatches...") 461 try: 462 fromDb.checkSchema(mirrorTables) 463 except schema.MismatchError as error: 464 # Convert to SystemExit to hide traceback and redirect to stderr 465 raise SystemExit("<ERROR> %s" % error) 466 467 adminDb = DbSession(server + '.' + SyncDb.sysc.adminDatabase, autoCommit=True, 468 isTrialRun=cli.getOpt('test'), userName=dbc.loadServerRwUsername()) 469 470 Logger.addMessage("Recreating database %s..." % database) 471 database.create(adminDb, overWrite=True) 472 473 db = DbSession(cli.getArg("to_database"), autoCommit=True, 474 isTrialRun=cli.getOpt('test'), userName=dbc.loadServerRwUsername()) 475 476 permittedUsers = ["wsaro"] 477 if not cli.getOpt("metadata_mirror"): 478 permittedUsers.append("ldservro") 479 480 for user in permittedUsers: 481 db.grantAccess(user) 482 483 # Create curation and metadata tables 484 Logger.addMessage("Creating tables...") 485 for table in mirrorTables: 486 if table.releasable and not table.name.endswith("DetectorEsoKeys") \ 487 or not cli.getOpt("metadata_mirror"): 488 db.createTable(table) 489 490 loadCurationTables(curationTables, fromDb, db, cli) 491 492 if int(cli.getArg("cuID")) in (0, 1): 493 # Insert default rows 494 for table in multiframeTables: 495 if table.name in ("Multiframe", "MultiframeDetector", 496 "CurrentAstrometry", "ProgrammeFrame"): 497 Logger.addMessage("%s..." % table) 498 defs = [column.getDefaultValue() for column in table.columns] 499 Logger.addMessage("%s rows copied." % 500 db.insertData(table.name, defs)) 501 502 # Create survey tables 503 # Release database 504 if not cli.getOpt("metadata_mirror"): 505 Logger.addMessage("Creating survey tables...") 506 createSurveyTables(db, int(cli.getArg("cuID"))) 507 508 return server, database
509 510 #------------------------------------------------------------------------------ 511
512 -def createSurveyTables(db, cuID):
513 """ 514 Creates empty survey data tables. 515 516 @param db: Connection to the database where the tables are to be created. 517 @type db: DbSession 518 @param cuID: Curation ID level to copy data to. If 0 or 1 then non-survey 519 tables are created too, otherwise you have to select an 520 individual non-survey as the programme option to copy its 521 tables. 522 @type cuID: int 523 524 """ 525 programme = df.ProgrammeTable(db) 526 # Determine the list of files determining table schemas 527 progScripts = set() 528 for progID in programme.getAttr("programmeID"): 529 programme.setCurRow(programmeID=progID) 530 # For CU levels above 1, non-survey tables not created because there's 531 # too many, best create on a need-to-have basis. 532 if cuID in (0, 1) or not programme.isNonSurvey(): 533 progScripts.add(programme.getSchemaScript()) 534 progScripts.add(programme.getAttr("neighboursSchema")) 535 536 # Parse table schemas - need calib for test dbs as CU19 releases it 537 tableSchema = schema.parseTables(SyncDb.sysc.calibrationSchema()) 538 for script in progScripts: 539 if script.upper() != dbc.charDefault(): 540 tableSchema += schema.parseTables(script) 541 542 detParts = ("Raw", "Astrometry", "Photometry") 543 for table in tableSchema: 544 db.createTable(table) 545 if cuID in (0, 1) and table.name.endswith(detParts): 546 # Insert default rows 547 defs = [column.getDefaultValue() for column in table.columns] 548 db.insertData(table.name, defs) 549 550 # Rename vvvDetection tables for use with monthly split tables 551 if db.sysc.isVSA() and cuID in (0, 1): 552 for part in detParts: 553 tableName = "vvvDetection" + part 554 db.renameTable(tableName, tableName + "Default")
555 556 #------------------------------------------------------------------------------ 557
558 -def defineDatabase(cli):
559 """ 560 Defines the type of database to be synchronised. 561 562 @param cli: Command-line options. 563 @type cli: CLI 564 565 @return: Server name that hosts the database and the database description. 566 @rtype: tuple(str, Database) 567 568 """ 569 dbName = cli.getArg("to_database").split('.')[-1] 570 sysc = SystemConstants(dbName) 571 if '.' in cli.getArg("to_database"): 572 server = cli.getArg("to_database").split('.')[0] 573 else: 574 server = sysc.loadServer 575 576 dbDir = None 577 filegroups = sysc.metadataFileGroups 578 if not cli.getOpt("metadata_mirror"): 579 filegroups += sysc.surveyFileGroups() 580 else: 581 dbDir = dbName 582 583 # @@TODO: Optimise minimum sizes for test databases, metadata mirrors, 584 # both WSA/VSA 585 database = Database(dbName, 586 volumes=SystemConstants.catServerVolumes(server), 587 dbDir=dbDir, 588 primarySize="100 MB", logSize="10 MB", 589 filegroups=list(zip(filegroups, ["0 MB"] * len(filegroups)))) 590 591 return server, database
592 593 #------------------------------------------------------------------------------ 594
595 -def loadCurationTables(curationTables, fromDb, toDb, cli):
596 """ 597 Loads the curation tables with the initial curation data. 598 599 @param curationTables: List of curation table schemas. 600 @type curationTables: list(schema.Table) 601 @param fromDb: Database connection from which data should be copied. 602 @type fromDb: DbSession 603 @param toDb: Database connection to which data should be copied. 604 @type toDb: DbSession 605 @param cli: Command-line options. 606 @type cli: CLI 607 608 """ 609 # Load curation data into curation tables (not the big log tables) 610 for table in curationTables: 611 if (not cli.getOpt("metadata_mirror") or table.releasable) and \ 612 not table.name.endswith("CurationHistory") \ 613 and table.name != "FlatFileLookUp": 614 Logger.addMessage("%s..." % table) 615 try: 616 Logger.addMessage("%s rows copied." % 617 fromDb.copyIntoTable(toDb.tablePath(table), table)) 618 except odbc.ProgrammingError as error: 619 ignoreCases = ["Column name or number", 620 "Invalid object name"] 621 if any(case in str(error) for case in ignoreCases): 622 msg = ("Cannot update table %s because %s schema differs " 623 "from that of the destination database." 624 % (toDb.tablePath(table), fromDb)) 625 if not CLI.isConfirmed(msg): 626 raise SystemExit("<ERROR> " + msg) 627 else: 628 Logger.addMessage("<WARNING> " + msg) 629 elif "sys.servers" in str(error): 630 Logger.addMessage("Outgesting %s..." % table) 631 bulkCopy(query=SelectSQL('*', fromDb.tablePath(table)), 632 tableSchema=table, fromDb=fromDb, toDb=toDb, 633 fileTag="%s_SyncDb_%s" % (toDb.database, 634 toDb.sysc.tempWorkPath().split('/')[-1])) 635 else: 636 raise
637 638 #------------------------------------------------------------------------------ 639
640 -def releaseDatabase(database, server):
641 """ 642 Copies database from load to release servers and set user name permissions. 643 644 @param database: Database object to create. 645 @type database: Database 646 @param server: Name of server to create database on. 647 @type server: str 648 649 """ 650 Logger.addMessage("Releasing database...") 651 adminDb = DbSession(server + '.' + SyncDb.sysc.adminDatabase, autoCommit=True, 652 userName=dbc.loadServerRwUsername()) 653 654 for pubServer in SyncDb.sysc.publicServers: 655 database.release(adminDb, pubServer) 656 657 Logger.addMessage("done.") 658 permittedUsers = ["wsaro"] 659 if database.name.upper() == "WFCAMOPENTIME": 660 permittedUsers.append("worldwsaro") 661 662 Logger.addMessage("Granting user permissions for " 663 + ', '.join(permittedUsers)) 664 665 for pubServer in SyncDb.sysc.publicServers: 666 db = DbSession(pubServer + '.' + database.name, autoCommit=True, 667 userName=dbc.loadServerRwUsername()) 668 669 for user in permittedUsers: 670 db.grantAccess(user)
671 672 #------------------------------------------------------------------------------ 673 # Entry point for script. 674 675 # Allow module to be imported as well as executed from the command line 676 if __name__ == "__main__": 677 # Define command-line interface settings for SyncDb 678 CLI.progArgs.remove("comment") 679 CLI.progArgs.remove("database") 680 CLI.progArgs += [ 681 CLI.Argument("from_database", "WSA", 682 isValOK=lambda x: x.count('.') <= 1), 683 CLI.Argument("to_database", "TestWSA", 684 isValOK=lambda x: x.count('.') <= 1 685 and not any(x.split('.', 1)[-1].upper().startswith(loadDb) 686 for loadDb in SystemConstants.getAll("loadDatabase"))), 687 CLI.Argument("cuID", '3', isOptional=True, 688 isValOK=lambda x: x.isdigit())] 689 690 CLI.progOpts.remove("curator") 691 CLI.progOpts.remove("user") 692 CLI.progOpts += [ 693 CLI.Option('a', "all", 694 "include deprecated data"), 695 CLI.Option('b', "begin", 696 "observation date of first multiframe to copy e.g. 2004-04-01" 697 " or 20040401, or first semester to release e.g. 05A_SV", 698 "DATE", isValOK=CLI.isDateOK), 699 CLI.Option('e', "end", 700 "observation date of last multiframe to copy e.g. 2006-07-31" 701 " or 20060731, or final semester to copy e.g. 07A", 702 "DATE", isValOK=CLI.isDateOK), 703 CLI.Option('M', "mergelog", 704 "always copy MergeLog table data"), 705 CLI.Option('m', "metadata_mirror", 706 "only metadata and curation tables are mirrored for release database"), 707 CLI.Option('n', "noprov", 708 "don't synchronise Provenance table"), 709 CLI.Option('o', "overwrite", 710 "start anew, overwriting old database"), 711 CLI.Option('p', "progID", 712 "copy data for this programme ID only", 713 "ID"), 714 CLI.Option('s', "sql_copy", 715 "just copy through SQL interface, don't do bulk copies"), 716 CLI.Option('x', "exclude", 717 "excludes non-survey programmes from the world release", 718 "LIST")] 719 720 cli = CLI(SyncDb, "$Revision: 10194 $") 721 Logger.isVerbose = False 722 Logger.addMessage(cli.getProgDetails()) 723 724 server, database = defineDatabase(cli) 725 SyncDb.sysc = SystemConstants(database.name) 726 727 # (Re)create the database first 728 if cli.getOpt("overwrite"): 729 Logger.archive = True 730 createDatabase(database, server, cli) 731 732 SyncDb.isDayStampedLog = (cli.getOpt("metadata_mirror") 733 and "PROPRIETY" in cli.getArg("to_database").upper()) 734 735 # Initialise curation session 736 cu = SyncDb( 737 programmeID=cli.getOpt("progID") if cli.getOpt("progID") else '', 738 database=cli.getArg("to_database"), 739 comment="Updating database with latest values", 740 isTrialRun=cli.getOpt("test"), 741 userName=dbc.loadServerRwUsername()) 742 743 cu.copyAllData = cli.getOpt("all") 744 cu.cuLevel = int(cli.getArg("cuID")) 745 try: 746 cu.dateRange = \ 747 cu.sysc.obsCal.dateRange(cli.getOpt("begin"), cli.getOpt("end")) 748 749 except Exception as error: 750 eType = "Invalid Option" 751 Logger.addExceptionMessage(error, eType) 752 raise SystemExit(eType + ": see log " + cu._log.pathName) 753 754 if cli.getOpt("exclude"): 755 cu.excludeProgs = \ 756 tuple(prog.lower() for prog in csv.values(cli.getOpt("exclude"))) 757 758 cu.forceSQL = cli.getOpt("sql_copy") 759 cu.fromDbPathName = cli.getArg("from_database") 760 cu.includeMergeLog = cli.getOpt("mergelog") 761 cu.isMetadataMirror = cli.getOpt("metadata_mirror") 762 cu.overwriteDb = cli.getOpt("overwrite") 763 cu.skipProvenance = cli.getOpt("noprov") 764 765 # Synchronise database 766 cu.run() 767 768 if cli.getOpt("metadata_mirror") and not cli.getOpt("test"): 769 # Drop db connection before release but keep CuSession object open 770 cu.archive.goOffline() 771 releaseDatabase(database, server) 772 773 #------------------------------------------------------------------------------ 774 # Change log: 775 # 776 # 11-Jul-2006, RSC: Original version. 777