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 __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 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
