def main(): # Setup command line options parser = argparse.ArgumentParser(description="Program which transposes a key-value table into a table " "where each key is mapped to a column.") addDbOptions(parser) parser.add_argument( "-s", "--skip-keys", dest="skipKeys", help="Comma separated list of metadata keys to omit in the output table") parser.add_argument( "-c", "--compress", dest="compress", action="store_true", help="Lift keys with constant values into a view") parser.add_argument( "database", help="Name of database containing metadata table to transpose") parser.add_argument( "metadataTable", help="Name of metadata table to transpose") parser.add_argument( "idCol", help="Primary key column name for metadata table") parser.add_argument( "outputTable", help="Name of output table to create") ns = parser.parse_args() db, metadataTable, idCol, outputTable = args if DbAuth.available(ns.host, str(ns.port)): ns.user = DbAuth.username(ns.host, str(ns.port)) passwd = DbAuth.password(ns.host, str(ns.port)) elif os.path.exists(os.path.join(os.environ["HOME"], ".mysql.cnf")): passwd = None else: passwd = getpass.getpass("%s's MySQL password: "******",")]) run(ns.host, ns.port, ns.user, passwd, db, metadataTable, idCol, outputTable, skipCols, ns.compress)
def testSetPolicy(self): self.assert_(DbAuth.available("lsst10.ncsa.uiuc.edu", "3306")) self.assertEqual(DbAuth.authString("lsst10.ncsa.uiuc.edu", "3306"), "test:globular.test") self.assertEqual(DbAuth.username("lsst10.ncsa.uiuc.edu", "3306"), "test") self.assertEqual(DbAuth.password("lsst10.ncsa.uiuc.edu", "3306"), "globular.test") self.assert_(DbAuth.available("lsst10.ncsa.uiuc.edu", "3307")) self.assertEqual(DbAuth.authString("lsst10.ncsa.uiuc.edu", "3307"), "boris:natasha") self.assertEqual(DbAuth.username("lsst10.ncsa.uiuc.edu", "3307"), "boris") self.assertEqual(DbAuth.password("lsst10.ncsa.uiuc.edu", "3307"), "natasha") self.assert_(DbAuth.available("lsst9.ncsa.uiuc.edu", "3306")) self.assertEqual(DbAuth.authString("lsst9.ncsa.uiuc.edu", "3306"), "rocky:squirrel") self.assertEqual(DbAuth.username("lsst9.ncsa.uiuc.edu", "3306"), "rocky") self.assertEqual(DbAuth.password("lsst9.ncsa.uiuc.edu", "3306"), "squirrel")
def testSetPolicy(self): self.assertTrue(DbAuth.available("lsst-db.ncsa.illinois.edu", "3306")) self.assertEqual( DbAuth.authString("lsst-db.ncsa.illinois.edu", "3306"), "test:globular.test") self.assertEqual(DbAuth.username("lsst-db.ncsa.illinois.edu", "3306"), "test") self.assertEqual(DbAuth.password("lsst-db.ncsa.illinois.edu", "3306"), "globular.test") self.assertTrue(DbAuth.available("lsst-db.ncsa.illinois.edu", "3307")) self.assertEqual( DbAuth.authString("lsst-db.ncsa.illinois.edu", "3307"), "boris:natasha") self.assertEqual(DbAuth.username("lsst-db.ncsa.illinois.edu", "3307"), "boris") self.assertEqual(DbAuth.password("lsst-db.ncsa.illinois.edu", "3307"), "natasha") self.assertTrue(DbAuth.available("lsst9.ncsa.illinois.edu", "3306")) self.assertEqual(DbAuth.authString("lsst9.ncsa.illinois.edu", "3306"), "rocky:squirrel") self.assertEqual(DbAuth.username("lsst9.ncsa.illinois.edu", "3306"), "rocky") self.assertEqual(DbAuth.password("lsst9.ncsa.illinois.edu", "3306"), "squirrel")
def __init__(self, host, database, user, port=3306, password=None): self.host = host self.port = port self.user = user self.database = database if password is None: if self.host is not None and self.port is not None and \ DbAuth.available(self.host, str(self.port)): self.user = DbAuth.username(self.host, str(self.port)) password = DbAuth.password(self.host, str(self.port)) elif not os.path.exists(os.path.join(os.environ['HOME'], ".my.cnf")): password = getpass.getpass("%s's MySQL password: " % user) self.password = password self.mysqlCmd = ['mysql'] if host is not None: self.mysqlCmd += ['-h', self.host] if port is not None: self.mysqlCmd += ['-P', str(self.port)] if user is not None: self.mysqlCmd += ['-u', self.user] if password is not None: self.mysqlCmd += ['-p' + self.password]
def main(): # Setup command line options parser = argparse.ArgumentParser( description="Program which transposes a key-value table into a table " "where each key is mapped to a column.") addDbOptions(parser) parser.add_argument( "-s", "--skip-keys", dest="skipKeys", help="Comma separated list of metadata keys to omit in the output table" ) parser.add_argument("-c", "--compress", dest="compress", action="store_true", help="Lift keys with constant values into a view") parser.add_argument( "database", help="Name of database containing metadata table to transpose") parser.add_argument("metadataTable", help="Name of metadata table to transpose") parser.add_argument("idCol", help="Primary key column name for metadata table") parser.add_argument("outputTable", help="Name of output table to create") ns = parser.parse_args() db, metadataTable, idCol, outputTable = args if DbAuth.available(ns.host, str(ns.port)): ns.user = DbAuth.username(ns.host, str(ns.port)) passwd = DbAuth.password(ns.host, str(ns.port)) elif os.path.exists(os.path.join(os.environ["HOME"], ".mysql.cnf")): passwd = None else: passwd = getpass.getpass("%s's MySQL password: "******",")]) run(ns.host, ns.port, ns.user, passwd, db, metadataTable, idCol, outputTable, skipCols, ns.compress)
def setUp(self): # Turn on tracing log.Trace.setVerbosity('', 10) log.ScreenLog.createDefaultLog(True, log.Log.INFO) # Eventually, these should be read from a policy somewhere self.dbServer = 'lsst10.ncsa.uiuc.edu' self.dbPort = '3306' self.dbType = 'mysql' if not DbAuth.available(self.dbServer, self.dbPort): self.fail("Cannot access database server %s:%s" % (self.dbServer, self.dbPort)) # Construct test run database name self.runId = DbAuth.username(self.dbServer, self.dbPort) +\ time.strftime("_test_ap_%y%m%d_%H%M%S", time.gmtime()) # Tweak these to run on different input data, or with a different number of slices self.universeSize = 2 self.visitId = 708125 self.filter = 'u' self.ra = 333.880166667 self.dec = -17.7374166667 self.dbUrlPrefix = ''.join( [self.dbType, '://', self.dbServer, ':', self.dbPort, '/']) self.dbUrl = self.dbUrlPrefix + self.runId self.substitutions = { 'visitId': self.visitId, 'filter': self.filter, 'runId': self.runId } # Create a database specifically for the test (copy relevant # tables from the test_ap database) mysqlStatements = [ """CREATE DATABASE %(runId)s""", """USE %(runId)s""", """CREATE TABLE VarObject LIKE test_ap.Object""", """CREATE TABLE NonVarObject LIKE test_ap.Object""", """CREATE TABLE DIASource LIKE test_ap.DIASource""", """CREATE TABLE prv_Filter LIKE test_ap.prv_Filter""", """INSERT INTO prv_Filter SELECT * FROM test_ap.prv_Filter""", """CREATE TABLE _tmp_v%(visitId)d_DIASource LIKE test_ap._tmp_v%(visitId)d_DIASource""", """INSERT INTO _tmp_v%(visitId)d_DIASource SELECT * FROM test_ap._tmp_v%(visitId)d_DIASource""", """CREATE TABLE _tmp_v%(visitId)d_Preds LIKE test_ap._tmp_v%(visitId)d_Preds""", """INSERT INTO _tmp_v%(visitId)d_Preds SELECT * FROM test_ap._tmp_v%(visitId)d_Preds""", """CREATE TABLE _tmpl_MatchPair LIKE test_ap._tmpl_MatchPair""", """CREATE TABLE _tmpl_IdPair LIKE test_ap._tmpl_IdPair""", """CREATE TABLE _tmpl_InMemoryObject LIKE test_ap._tmpl_InMemoryObject""", """CREATE TABLE _tmpl_InMemoryMatchPair LIKE test_ap._tmpl_InMemoryMatchPair""", """CREATE TABLE _tmpl_InMemoryId LIKE test_ap._tmpl_InMemoryId""", """CREATE TABLE _ap_DIASourceToObjectMatches LIKE test_ap._ap_DIASourceToObjectMatches""", """CREATE TABLE _ap_PredToDIASourceMatches LIKE test_ap._ap_PredToDIASourceMatches""", """CREATE TABLE _ap_DIASourceToNewObject LIKE test_ap._ap_DIASourceToNewObject""", """CREATE TABLE _mops_Prediction LIKE test_ap._mops_Prediction""" ] db = DbStorage() db.setPersistLocation(LogicalLocation(self.dbUrlPrefix + 'test_ap')) try: for stmt in mysqlStatements: db.executeSql(stmt % self.substitutions) # Specify list of stages ... self.stages = [ ap.LoadStage, InputStage, ap.MatchDiaSourcesStage, OutputStage, InputStage, ap.MatchMopsPredsStage, OutputStage, ap.StoreStage ] # and read in stage policy for each stage policyDir = os.path.join(os.environ['AP_DIR'], 'pipeline', 'examples', 'policy') self.policies = [ Policy(os.path.join(policyDir, 'LoadStage.paf')), Policy(os.path.join(policyDir, 'MatchDiaSourcesStageInput.paf')), None, Policy( os.path.join(policyDir, 'MatchDiaSourcesStageOutput.paf')), Policy(os.path.join(policyDir, 'MatchMopsPredsStageInput.paf')), None, Policy(os.path.join(policyDir, 'MatchMopsPredsStageOutput.paf')), Policy(os.path.join(policyDir, 'StoreStage.paf')) ] # construct PropertySet for string interpolation psSubs = PropertySet() psSubs.setInt('visitId', self.visitId) psSubs.setString('runId', self.runId) psSubs.setString('filter', self.filter) psSubs.setString('work', '.') psSubs.setString('input', '/tmp') psSubs.setString('output', '/tmp') psSubs.setString('update', '/tmp') psSubs.setString('dbUrl', self.dbUrl) LogicalLocation.setLocationMap(psSubs) except: # cleanup database in case of error db.executeSql("DROP DATABASE %(runId)s" % self.substitutions) raise
def setUp(self): # Turn on tracing log.Trace.setVerbosity('', 10) log.ScreenLog.createDefaultLog(True, log.Log.INFO) # Eventually, these should be read from a policy somewhere self.dbServer = 'lsst10.ncsa.uiuc.edu' self.dbPort = '3306' self.dbType = 'mysql' if not DbAuth.available(self.dbServer, self.dbPort): self.fail("Cannot access database server %s:%s" % (self.dbServer, self.dbPort)) # Construct test run database name self.runId = DbAuth.username(self.dbServer, self.dbPort) +\ time.strftime("_test_ap_%y%m%d_%H%M%S", time.gmtime()) # Tweak these to run on different input data, or with a different number of slices self.universeSize = 2 self.visitId = 708125 self.filter = 'u' self.ra = 333.880166667 self.dec = -17.7374166667 self.dbUrlPrefix = ''.join([self.dbType, '://', self.dbServer, ':', self.dbPort, '/']) self.dbUrl = self.dbUrlPrefix + self.runId self.substitutions = { 'visitId': self.visitId, 'filter': self.filter, 'runId': self.runId } # Create a database specifically for the test (copy relevant # tables from the test_ap database) mysqlStatements = [ """CREATE DATABASE %(runId)s""", """USE %(runId)s""", """CREATE TABLE VarObject LIKE test_ap.Object""", """CREATE TABLE NonVarObject LIKE test_ap.Object""", """CREATE TABLE DIASource LIKE test_ap.DIASource""", """CREATE TABLE prv_Filter LIKE test_ap.prv_Filter""", """INSERT INTO prv_Filter SELECT * FROM test_ap.prv_Filter""", """CREATE TABLE _tmp_v%(visitId)d_DIASource LIKE test_ap._tmp_v%(visitId)d_DIASource""", """INSERT INTO _tmp_v%(visitId)d_DIASource SELECT * FROM test_ap._tmp_v%(visitId)d_DIASource""", """CREATE TABLE _tmp_v%(visitId)d_Preds LIKE test_ap._tmp_v%(visitId)d_Preds""", """INSERT INTO _tmp_v%(visitId)d_Preds SELECT * FROM test_ap._tmp_v%(visitId)d_Preds""", """CREATE TABLE _tmpl_MatchPair LIKE test_ap._tmpl_MatchPair""", """CREATE TABLE _tmpl_IdPair LIKE test_ap._tmpl_IdPair""", """CREATE TABLE _tmpl_InMemoryObject LIKE test_ap._tmpl_InMemoryObject""", """CREATE TABLE _tmpl_InMemoryMatchPair LIKE test_ap._tmpl_InMemoryMatchPair""", """CREATE TABLE _tmpl_InMemoryId LIKE test_ap._tmpl_InMemoryId""", """CREATE TABLE _ap_DIASourceToObjectMatches LIKE test_ap._ap_DIASourceToObjectMatches""", """CREATE TABLE _ap_PredToDIASourceMatches LIKE test_ap._ap_PredToDIASourceMatches""", """CREATE TABLE _ap_DIASourceToNewObject LIKE test_ap._ap_DIASourceToNewObject""", """CREATE TABLE _mops_Prediction LIKE test_ap._mops_Prediction""" ] db = DbStorage() db.setPersistLocation(LogicalLocation(self.dbUrlPrefix + 'test_ap')) try: for stmt in mysqlStatements: db.executeSql(stmt % self.substitutions) # Specify list of stages ... self.stages = [ ap.LoadStage, InputStage, ap.MatchDiaSourcesStage, OutputStage, InputStage, ap.MatchMopsPredsStage, OutputStage, ap.StoreStage ] # and read in stage policy for each stage policyDir = os.path.join(os.environ['AP_DIR'], 'pipeline', 'examples', 'policy') self.policies = [ Policy(os.path.join(policyDir,'LoadStage.paf')), Policy(os.path.join(policyDir,'MatchDiaSourcesStageInput.paf')), None, Policy(os.path.join(policyDir,'MatchDiaSourcesStageOutput.paf')), Policy(os.path.join(policyDir,'MatchMopsPredsStageInput.paf')), None, Policy(os.path.join(policyDir,'MatchMopsPredsStageOutput.paf')), Policy(os.path.join(policyDir,'StoreStage.paf')) ] # construct PropertySet for string interpolation psSubs = PropertySet() psSubs.setInt('visitId', self.visitId) psSubs.setString('runId', self.runId) psSubs.setString('filter', self.filter) psSubs.setString('work', '.') psSubs.setString('input', '/tmp') psSubs.setString('output', '/tmp') psSubs.setString('update', '/tmp') psSubs.setString('dbUrl', self.dbUrl) LogicalLocation.setLocationMap(psSubs) except: # cleanup database in case of error db.executeSql("DROP DATABASE %(runId)s" % self.substitutions) raise