Source Code for Module helpers.CopyTable

  1  #! /usr/bin/env python 
  2  #------------------------------------------------------------------------------ 
  3  #$Id: CopyTable.py 9353 2012-08-06 12:53:38Z RossCollins $ 
  4  """ 
  5  Copy table from one DB to another one (with different name) 
  6  """ 
  7  from __future__ import division, print_function 
  8   
  9  from   wsatools.CLI                 import CLI 
 10  from   wsatools.Logger              import Logger 
 11  from   wsatools.DbConnect.DbSession import DbSession, bulkCopy, SelectSQL 
 12  import wsatools.DbConnect.Schema        as schema 
 13   
 14 -class CopyTable(object): 
 15      """ 
 16      """ 
 17   
 18      #-------------------------------------------------------------------------- 
 19      # Define public member variable default values (access as obj.varName) 
 20      # these need to be set from command-line options 
 21      toTable = None 
 22      numberValues = None 
 23      minFrameSetID = None 
 24      maxFrameSetID = None 
 25      #-------------------------------------------------------------------------- 
 26   
 27 -    def __init__(self, fromDB, fromTable, toDB, tableSchemaFile, 
 28                   userName=DbSession.userName): 
 29          """ 
 30          Initialises member data and prepares database connection. 
 31   
 32          @param userName:   Optionally override default database username. 
 33          @type  userName:   str 
 34   
 35          """ 
 36          # Initialise parent class 
 37          #super(CopyTable, self).__init__(fromDB=fromDB, fromTable=fromTable, 
 38          #    toDB=toDB, tableSchemaFile=tableSchemaFile, userName=userName) 
 39   
 40          # Set up parameters 
 41          # SQL query 
 42          self.tableName = fromTable 
 43          # FromDB 
 44          self.archive = DbSession(fromDB, userName=userName) 
 45          self.fromDB = fromDB 
 46          self.tableSchemaFile = tableSchemaFile 
 47          self.outputDB = DbSession(toDB, userName=userName) 
 48          cuEventID = self.archive.query("max(cuEventID)+1", "ArchiveCurationHistory", 
 49                                       firstOnly=True, default=0) 
 50          self.shareFileID = '%sCopyTable%06d' % (self.archive.database, 
 51            cuEventID if cuEventID else 0) 
 52   
 53      #-------------------------------------------------------------------------- 
 54   
 55 -    def run(self): 
 56          """ 
 57          """ 
 58          selectStr = "%s*" % ("top %s " % self.numberValues if self.numberValues else "") 
 59          whereStr = "frameSetID>=%s" % self.minFrameSetID if self.minFrameSetID else "" 
 60          if self.maxFrameSetID: 
 61              whereStr += " AND " if self.minFrameSetID else "" 
 62              whereStr += "frameSetID<=%s" % self.maxFrameSetID 
 63          query = SelectSQL(selectStr, self.fromDB + ".dbo." + self.tableName, whereStr) 
 64          # is small 
 65          print(self.tableName) 
 66          isSmallTable = self.archive.queryNumRows(self.tableName) < 200000000 
 67   
 68          # table Schema 
 69          if self.toTable: 
 70              self.tableSchema = schema.parseTables(self.tableSchemaFile, [self.toTable])[0] 
 71          else: 
 72              self.tableSchema = schema.parseTables(self.tableSchemaFile, [self.tableName])[0] 
 73          #  
 74   
 75          bulkCopy(query=query, 
 76                     tableSchema=self.tableSchema, 
 77                          fromDb=self.archive, 
 78                            toDb=self.outputDB, 
 79                         fileTag=self.shareFileID, 
 80                    isSmallTable=isSmallTable)  # @@GOTCHA: Even small 
 81                      # detection table outgests, without whereChunks have failed 
 82                      # with this turned on, so always turn off now. 
 83  #------------------------------------------------------------------------------ 
 84  # Entry point for script. 
 85   
 86  # Allow module to be imported as well as executed from the command line 
 87  if __name__ == '__main__': 
 88      # Define additional command-line interface options for Cu19 
 89      CLI.progArgs += [ 
 90        CLI.Argument("fromTable", "vvvSource"), 
 91        CLI.Argument("toDB", "ramses10.testVSAnjcVvv"), 
 92        CLI.Argument("tableSchema", "testVSAnjcVvv_vvvSchema.sql")] 
 93   
 94      CLI.progOpts += [ 
 95        CLI.Option('d', "toTable", 
 96          "insert data into a different table name", "NAME"), 
 97        CLI.Option('f', "minFrameSetID", 
 98          "minimum frameSetID", "NUMBER"), 
 99        CLI.Option('n', "numberValues", 
100          "insert number of values", "NUMBER"), 
101        CLI.Option('x', "maxFrameSetID", 
102          "minimum frameSetID", "NUMBER")] 
103   
104      cli = CLI(CopyTable, "$Revision: 9353 $") 
105      Logger.isVerbose = cli.getOpt("verbose") 
106      Logger.addMessage(cli.getProgDetails()) 
107   
108      cu = CopyTable(cli.getArg("database"), cli.getArg("fromTable"), 
109                     cli.getArg("toDB"), cli.getArg("tableSchema"), 
110                     cli.getOpt("user")) 
111   
112   
113      if cli.getOpt("toTable"): 
114          cu.toTable = cli.getOpt("toTable") 
115      if cli.getOpt("numberValues"): 
116          cu.numberValues = int(cli.getOpt("numberValues")) 
117      if cli.getOpt("minFrameSetID"): 
118          cu.minFrameSetID = long(cli.getOpt("minFrameSetID")) 
119      if cli.getOpt("maxFrameSetID"): 
120          cu.maxFrameSetID = long(cli.getOpt("maxFrameSetID")) 
121      cu.run() 
122