def setUpClass(cls):
try:
engine = getEngineFromFile(cls.CREDFILE)
except IOError:
raise unittest.SkipTest("%s: No credentials file %s, skipping tests." %
(cls.__name__, cls.CREDFILE))
username = engine.url.username
testId = int(time.time() * 10.0)
cls.dbName = "%s_test_%d" % (username, testId)
# make temporary database
createDb(engine, cls.dbName)
# make engine with database name
cls.engine = getEngineFromFile(cls.CREDFILE, database=cls.dbName)
# load scripts
scripts = ["lsstSchema4mysqlPT1_2.sql",
"setup_perRunTablesS12_lsstsim.sql",
"setup_storedFunctions.sql"]
sqldir = os.path.join(lsst.utils.getPackageDir("cat"), "sql")
for script in scripts:
loadSqlScript(cls.engine, os.path.join(sqldir, script))
def setUpClass(cls):
try:
engine = getEngineFromFile(cls.CREDFILE)
except IOError:
raise unittest.SkipTest(
"%s: No credentials file %s, skipping tests." %
(cls.__name__, cls.CREDFILE))
username = engine.url.username
testId = int(time.time() * 10.0)
cls.dbName = "%s_test_%d" % (username, testId)
# make temporary database
createDb(engine, cls.dbName)
# make engine with database name
cls.engine = getEngineFromFile(cls.CREDFILE, database=cls.dbName)
# load scripts
scripts = [
"lsstSchema4mysqlPT1_2.sql", "setup_perRunTablesS12_lsstsim.sql",
"setup_storedFunctions.sql"
]
sqldir = os.path.join(lsst.utils.getPackageDir("cat"), "sql")
for script in scripts:
loadSqlScript(cls.engine, os.path.join(sqldir, script))
def __init__(self, credFileName, logger=log):
self._log = logger
engine = getEngineFromFile(credFileName)
dbName = "{}_fitsTest".format(engine.url.username)
self._conn = getEngineFromFile(credFileName, database=dbName).connect()
sql = "SET time_zone = '+0:00'"
try:
self._log.info(sql)
self._conn.execute(sql)
except SQLAlchemyError as e:
self._log.info("Db engine error %s", e)
def __init__(self, credFileName, logger=log):
self._log = logger
engine = getEngineFromFile(credFileName)
dbName = "{}_fitsTest".format(engine.url.username)
self._conn = getEngineFromFile(credFileName, database=dbName).connect()
sql = "SET time_zone = '+0:00'"
try:
self._log.info(sql)
self._conn.execute(sql)
except SQLAlchemyError as e:
self._log.info("Db engine error %s", e)
def testOverridingUrl(self):
"""
Test overwriting values from config file.
"""
engine = getEngineFromFile(self.CREDFILE,
username="******",
password="******")
self.assertEqual(engine.url.username, "peter")
self.assertEqual(engine.url.password, "hi")
engine = getEngineFromFile(self.CREDFILE, host="lsst125", port="1233")
self.assertEqual(engine.url.host, "lsst125")
self.assertEqual(engine.url.port, "1233")
engine = getEngineFromFile(self.CREDFILE, database="myBestDB")
self.assertEqual(engine.url.database, "myBestDB")
def testCheckExists(self):
"""
Test checkExist for databases and tables.
"""
conn = self._engine.connect()
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createDb(conn, self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createTable(conn, "t1", "(i int)", self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
# utils.useDb(conn, self._dbA)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
self.assertTrue(utils.tableExists(conn, "t1"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.dropDb(conn, self._dbA)
conn.close()
def testConn_invalidSocket(self):
# make sure retry is disabled, otherwise it wil try to reconnect
# (it will assume the server is down and socket valid).
engine = getEngineFromFile(self.CREDFILE,
host="localhost",
query={"unix_socket": "/x/sock"})
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testCheckExists(self):
"""
Test checkExist for databases and tables.
"""
conn = self._engine.connect()
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createDb(conn, self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.createTable(conn, "t1", "(i int)", self._dbA)
self.assertTrue(utils.dbExists(conn, self._dbA))
self.assertFalse(utils.dbExists(conn, "bla"))
self.assertTrue(utils.tableExists(conn, "t1", self._dbA))
# utils.useDb(conn, self._dbA)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
self.assertTrue(utils.tableExists(conn, "t1"))
self.assertFalse(utils.tableExists(conn, "bla"))
self.assertFalse(utils.tableExists(conn, "bla", "blaBla"))
utils.dropDb(conn, self._dbA)
self.assertFalse(utils.userExists(conn, "d_Xx_u12my", "localhost"))
self.assertTrue(utils.userExists(conn, "root", "localhost"))
conn.close()
def testLoadDataInFile(self):
"""
Testing "LOAD DATA INFILE..."
"""
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, '1\n2\n3\n4\n4\n4\n5\n3\n')
os.close(fd)
query = self._engine.url.query.copy()
query['local_infile'] = '1'
conn = getEngineFromFile(self.CREDFILE, query=query).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
x = conn.execute("SELECT COUNT(*) FROM t1")
self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0])
self.assertEqual(
3,
conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0])
# let's add some confusing data to the loaded file, it will get truncated
f = open(fN, 'w')
f.write('11,12,13,14\n2')
f.close()
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testLoadDataInFile(self):
"""
Testing "LOAD DATA INFILE..."
"""
fd, fN = tempfile.mkstemp(suffix=".csv", text=True)
os.write(fd, '1\n2\n3\n4\n4\n4\n5\n3\n')
os.close(fd)
query = self._engine.url.query.copy()
query['local_infile'] = '1'
conn = getEngineFromFile(self.CREDFILE, query=query).connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
x = conn.execute("SELECT COUNT(*) FROM t1")
self.assertEqual(8, conn.execute("SELECT COUNT(*) FROM t1").first()[0])
self.assertEqual(3, conn.execute("SELECT COUNT(*) FROM t1 WHERE i=4").first()[0])
# let's add some confusing data to the loaded file, it will get truncated
f = open(fN, 'w')
f.write('11,12,13,14\n2')
f.close()
conn.execute("LOAD DATA LOCAL INFILE '%s' INTO TABLE t1" % fN)
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def testBasicOptionFileConn(self):
conn = getEngineFromFile(self.CREDFILE+".ini").connect()
utils.createDb(conn, self._dbA)
utils.useDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)")
utils.dropDb(conn, self._dbA)
conn.close()
def dbDestroyCreate(credFile, code, logger=log):
'''Open the database userDb, delete tables, then re-create them.
Returns dbName (or None if code was not passed)
'''
conn = getEngineFromFile(credFile).connect()
dbName = "{}_fitsTest".format(conn.engine.url.username)
if (code == "DELETE"):
utils.dropDb(conn, dbName, mustExist=False)
utils.createDb(conn, dbName)
conn = getEngineFromFile(credFile, database=dbName).connect()
for q in schemaToCreate:
logger.info(q)
conn.execute(q)
logger.info("DbSetup done")
def dbDestroyCreate(credFile, code, logger=log):
'''Open the database userDb, delete tables, then re-create them.
Returns dbName (or None if code was not passed)
'''
conn = getEngineFromFile(credFile).connect()
dbName = "{}_fitsTest".format(conn.engine.url.username)
if (code == "DELETE"):
utils.dropDb(conn, dbName, mustExist=False)
utils.createDb(conn, dbName)
conn = getEngineFromFile(credFile, database=dbName).connect()
for q in schemaToCreate:
logger.info(q)
conn.execute(q)
logger.info("DbSetup done")
def testConn_badSocketGoodHostPort(self):
# invalid socket, but good host/port
conn = getEngineFromFile(self.CREDFILE,
host='127.0.0.1',
query={
"unix_socket": "/x/sock"
}).connect()
conn.close()
def testOverridingUrl(self):
"""
Test overwriting values from config file.
"""
engine = getEngineFromFile(self.CREDFILE+".ini",
username="******",
password="******")
self.assertEqual(engine.url.username, "peter")
self.assertEqual(engine.url.password, "hi")
engine = getEngineFromFile(self.CREDFILE+".ini",
host="lsst125",
port="1233")
self.assertEqual(engine.url.host, "lsst125")
self.assertEqual(engine.url.port, "1233")
engine = getEngineFromFile(self.CREDFILE+".ini",
database="myBestDB")
self.assertEqual(engine.url.database, "myBestDB")
def addProject(self, name):
"""
Add project.
@param name the name
"""
conn = getEngineFromFile(self._msMysqlAuthF).connect()
ret = conn.execute(
"SELECT COUNT(*) FROM Project WHERE projectName=%s", (name,))
if ret.scalar() == 1:
raise MetaBException(MetaBException.PROJECT_EXISTS, name)
conn.execute("INSERT INTO Project(projectName) VALUES(%s)", (name,))
def addInstitution(self, name):
"""
Add institution.
@param name the name
"""
conn = getEngineFromFile(self._msMysqlAuthF).connect()
ret = conn.execute(
"SELECT COUNT(*) FROM Institution WHERE instName=%s", (name,))
if ret.scalar() == 1:
raise MetaBException(MetaBException.INST_EXISTS, name)
conn.execute("INSERT INTO Institution(instName) VALUES(%s)", (name,))
def __init__(self, credFileName, database, table, columns, dataRoot, logger):
self._log = logger
self._table = table
self._columns = columns
self._conn = getEngineFromFile(credFileName, database=database).connect()
self._dataRoot = dataRoot
sql = "SET time_zone = '+0:00'"
try:
self._log.info(sql)
self._conn.execute(sql)
except SQLAlchemyError as e:
self._log.error("Db engine error %s" % e)
def setUp(self):
self._engine = getEngineFromFile(self.CREDFILE)
self._dbA = "%s_dbWrapperTestDb_A" % self._engine.url.username
self._dbB = "%s_dbWrapperTestDb_B" % self._engine.url.username
self._dbC = "%s_dbWrapperTestDb_C" % self._engine.url.username
conn = self._engine.connect()
if utils.dbExists(conn, self._dbA):
utils.dropDb(conn, self._dbA)
if utils.dbExists(conn, self._dbB):
utils.dropDb(conn, self._dbB)
if utils.dbExists(conn, self._dbC):
utils.dropDb(conn, self._dbC)
conn.close()
def testLoadSqlScriptWithDb(self):
f, fN = tempfile.mkstemp(suffix=".csv", text=True)
f = open(fN,'w')
f.write("create table t(i int, d double);\n")
f.write("insert into t values (1, 1.1), (2, 2.2);\n")
f.close()
conn = getEngineFromFile(self.CREDFILE+".ini").connect()
utils.createDb(conn, self._dbA)
loadSqlScript(
fN, username=self._user, host=self._host, port=self._port, db=self._dbA)
self.assertEqual(3, conn.execute("select sum(i) from %s.t" % self._dbA).first()[0])
utils.dropDb(conn, self._dbA)
conn.close()
os.remove(fN)
def setUp(self):
self._engine = getEngineFromFile(self.CREDFILE)
self._dbA = "%s_dbWrapperTestDb_A" % self._engine.url.username
self._dbB = "%s_dbWrapperTestDb_B" % self._engine.url.username
self._dbC = "%s_dbWrapperTestDb_C" % self._engine.url.username
conn = self._engine.connect()
if utils.dbExists(conn, self._dbA):
utils.dropDb(conn, self._dbA)
if utils.dbExists(conn, self._dbB):
utils.dropDb(conn, self._dbB)
if utils.dbExists(conn, self._dbC):
utils.dropDb(conn, self._dbC)
conn.close()
def testLoadSqlScriptFromPath(self):
conn = getEngineFromFile(self.CREDFILE).connect()
dbName = "%s_dbWrapperTestDb" % conn.engine.url.username
commands = ["create database %s;" % dbName,
"use %s;" % dbName,
"create table t(i int);",
"insert into t values (1), (2), (2), (5);"]
# make file but pass the name of that file to loadSqlScript
script = tempfile.NamedTemporaryFile()
script.write('\n'.join(commands))
script.seek(0)
utils.loadSqlScript(conn, script.name)
utils.dropDb(conn, dbName)
def testLoadSqlScriptFromPath(self):
conn = getEngineFromFile(self.CREDFILE).connect()
dbName = "%s_dbWrapperTestDb" % conn.engine.url.username
commands = [
"create database %s;" % dbName,
"use %s;" % dbName, "create table t(i int);",
"insert into t values (1), (2), (2), (5);"
]
# make file but pass the name of that file to loadSqlScript
script = tempfile.NamedTemporaryFile()
script.write('\n'.join(commands))
script.seek(0)
utils.loadSqlScript(conn, script.name)
utils.dropDb(conn, dbName)
def testListTables(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbA)
ret = utils.listTables(conn, self._dbA)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
ret = utils.listTables(conn, self._dbB)
self.assertEqual(len(ret), 0)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
ret = utils.listTables(conn)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
utils.dropDb(conn, self._dbA)
def testListTables(self):
conn = self._engine.connect()
utils.createDb(conn, self._dbA)
utils.createTable(conn, "t1", "(i int)", self._dbA)
utils.createTable(conn, "t2", "(i int)", self._dbA)
ret = utils.listTables(conn, self._dbA)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
ret = utils.listTables(conn, self._dbB)
self.assertEqual(len(ret), 0)
conn = getEngineFromFile(self.CREDFILE, database=self._dbA).connect()
ret = utils.listTables(conn)
self.assertEqual(len(ret), 2)
self.assertIn("t1", ret)
self.assertIn("t2", ret)
utils.dropDb(conn, self._dbA)
def addUser(self, muName, fName, lName, affil, email):
"""
Add user.
@param muName MySQL user name
@param fName first name
@param lName last name
@param affil short name of the affilliation (home institution)
@param email email address
"""
conn = getEngineFromFile(self._msMysqlAuthF).connect()
cmd = "SELECT instId FROM Institution WHERE instName = %s"
instId = conn.execute(cmd, (affil,)).scalar()
if instId is None:
raise MetaBException(MetaBException.INST_NOT_FOUND, affil)
cmd = "INSERT INTO User(mysqlUserName, firstName, lastName, email, instId) "
cmd += "VALUES(%s, %s, %s, %s, %s)"
conn.execute(cmd, (muName, fName, lName, email, instId))
def __init__(self, credFileName, database, table, columns, dataRoot,
butlerPolicy, butlerKeys, logger):
"""Instantiate W13Db object with credential for database, butler
configuration, and logger.
Parameters
----------
credFileName : str
The connection for accessing image metadata
database : str
the datbase connection string.
table : str
The table name.
columns : str
The database columns.
dataRoot : str
root for the butler.
bulterPolicy : str
The butler policy.
butlerKeys : str
The bulter keys for this image data source.
logger : obj
The logger to be used.
"""
self._log = logger
self.conn = getEngineFromFile(credFileName, database=database).connect()
self.butlerget = ButlerGet(dataRoot, butlerPolicy, butlerKeys, logger)
self.metaservget = MetaservGet(self.conn, table, columns, logger)
self.imagegetter = ImageGetter(self.butlerget, self.metaservget, logger)
try:
sql = "SET time_zone = '+0:00'"
self._log.info(sql)
self.conn.execute(sql)
except SQLAlchemyError as e:
self._log.error("Db engine error %s" % e)
from flask import Flask, request
import json
import logging as log
import os
import sys
from lsst.dax.dbserv import dbREST_v0
from lsst.dax.imgserv import imageREST_v0
from lsst.dax.metaserv import metaREST_v0
from lsst.db.engineFactory import getEngineFromFile
defaults_file = "~/.lsst/dbAuth-dbServ.ini"
engine = getEngineFromFile(defaults_file)
app = Flask(__name__)
app.config["default_engine"] = engine
app.config["dax.imgserv.default_source"] = "/lsst7/releaseW13EP"
@app.route('/')
def getRoot():
fmt = request.accept_mimetypes.best_match(['application/json', 'text/html'])
if fmt == 'text/html':
return ("LSST Web Service here. I currently support: "
"<a href='meta'>/meta</a>, "
"<a href='image'>/image</a>, "
"<a href='db'>/db</a.")
return "LSST Web Service here. I currently support: /meta, /image, /db."
@author Jacek Becla, SLAC
"""
from flask import Flask, request
import json
import logging as log
import sys
from lsst.dax.metaserv import api_v0, api_v1
from lsst.db.engineFactory import getEngineFromFile
app = Flask(__name__)
# Configure Engine
defaults_file = "~/.lsst/metaserv.ini"
engine = getEngineFromFile(defaults_file)
app.config["default_engine"] = engine
@app.route('/')
def route_root():
fmt = request.accept_mimetypes.best_match(['application/json', 'text/html'])
s = '''Test server for testing metadata. Try adding /meta to URI.
'''
if fmt == "text/html":
return s
return json.dumps(s)
@app.route('/meta')
def route_meta():
def testConn_wrongPortNo(self):
engine = getEngineFromFile(self.CREDFILE, port=1579)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testConn_invalidHost2(self):
engine = getEngineFromFile(self.CREDFILE, host="dummyHost", port=3036)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def run(self):
credFileName = "~/.lsst/dbAuth-dbServ.ini"
engine = getEngineFromFile(credFileName)
dbName = "{}_fitsTest".format(engine.url.username)
metaDb = MetadataFitsDb(credFileName)
resp = None
try:
resp = self.client.search(WATCH_FOLDER, version="current", site="all",
query="scanStatus = 'UNSCANNED'", max_num=1000)
except DcException as error:
if hasattr(error, "message"):
log.warn("Error occurred:\nMessage: %s", error.message)
if hasattr(error, "type"):
log.warn("Type: %s", error.type)
if hasattr(error, "cause"):
log.warn("Cause: %s", error.cause)
else:
# Should have content
log.warn(error.content)
sys.exit(1)
results = unpack(resp.content)
for dataset in results:
locations = dataset.locations
check_location = None
for location in locations:
if location.site == WATCH_SITE:
check_location = location
break
file_path = check_location.resource
dataset_path = dataset.path
stat = os.stat(file_path)
cksum = self.get_cksum(file_path)
# Note: While there may only be one version of a dataset,
# we tie the metadata to versionMetadata
scan_result = {}
scan_result["size"] = stat.st_size
scan_result["checksum"] = str(cksum)
# UTC datetime in ISO format (Note: We need Z to denote UTC Time Zone)
scan_result["locationScanned"] = datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%SZ')
scan_result["scanStatus"] = "OK"
md = self.get_metadata(file_path)
if md:
scan_result["versionMetadata"] = md
try:
log.debug("patch_resp %s", str(file_path))
patch_resp = self.client.patch_dataset(dataset_path, scan_result,
versionId=dataset.versionId, site=WATCH_SITE)
log.debug("Inserting %s", str(file_path))
fileId = metaDb.insertFile(file_path)
metadata = {"fileId":fileId}
md_patch = {}
md_patch["versionMetadata"] = metadata
md_patch_resp = self.client.patch_dataset(dataset_path, md_patch,
versionId=dataset.versionId)
log.info("Inserted %d %s", fileId, str(file_path))
except DcException as err:
log.warn("Encountered error while updating dataset %s", str(file_path), err)
def testConn_invalidPortNo(self):
engine = getEngineFromFile(self.CREDFILE, port=987654)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def __init__(self, config_path):
self.engine = getEngineFromFile(config_path)
def addDbDescr(self, dbName, schemaFile, level, dataRel, owner,
accessibility, projectName, dbMysqlAuthF):
"""
Add a database along with additional schema description provided through
@schemaFile.
@param dbName database name
@param schemaFile ascii file containing schema with description
@param level level (e.g., L1, L2, L3)
@param dataRel data release
@param owner owner of the database
@param accessibility accessibility of the database (pending/public/private)
@param projectName name of the project the db is associated with
@param dbMysqlAuthF mysql auth file for the db that we are adding
The function connects to two database servers:
a) one that has the database that is being loaded
b) one that has the metaserv database
If they are both on the same server, the connection is reused.
The course of action:
* connect to the server that has database that is being loaded
* parse the ascii schema file
* fetch schema information from the information_schema
* do the matching, add info fetched from information_schema to the
in memory structure produced by parsing ascii schema file
* fetch schema description and version (which is kept as data inside
a special table in the database that is being loaded). Ignore if it
does not exist.
* Capture information from mysql auth file about connection information
* connect to the metaserv database
* validate owner, project (these must be loaded into metaserv prior to
calling this function)
* load all the information into metaserv in various tables (Repo,
DDT_Table, DDT_Column)
It raises following MetaBEXceptions:
* DB_DOES_NOT_EXISTS if database dbName does not exist
* NOT_MATCHING if the database schema and ascii schema don't match
* TB_NOT_IN_DB if the table is described in ascii schema, but it is missing
in the database
* COL_NOT_IN_TB if the column is described in ascii schema, but it is
missing in the database
* COL_NOT_IN_FL if the column is in the database schema, but not in ascii
schema
* Db object can throw various DbException and MySQL exceptions
"""
# Connect to the server that has database that is being added
conn = getEngineFromFile(dbMysqlAuthF).connect()
if not utils.dbExists(conn, dbName):
self._log.error("Db '%s' not found.", dbName)
raise MetaBException(MetaBException.DB_DOES_NOT_EXIST, dbName)
# Parse the ascii schema file
theTable = parseSchema(schemaFile)
# Fetch the schema information from the database
ret = conn.execute(
"SELECT table_name, column_name, ordinal_position "
"FROM information_schema.COLUMNS WHERE "
"TABLE_SCHEMA = %s ORDER BY table_name", (dbName,))
# Count the number of columns in the ascii file
nColumns = sum(len(t["columns"]) for t in theTable.values())
# Check if the number of columns matches
if nColumns != ret.rowcount:
self._log.error("Number of columns in ascii file "
"(%d) != number of columns in db (%d)", nColumns, ret.rowcount)
raise MetaBException(MetaBException.NOT_MATCHING)
rows = ret.fetchall()
# Fetch ordinal_positions from information_schema and add it to "theTable"
for (tName, cName, ordP) in rows:
t = theTable.get(tName, None)
if not t:
self._log.error(
"Table '%s' not found in db, present in ascii file.", tName)
raise MetaBException(MetaBException.TB_NOT_IN_DB, tName)
foundColumn = False
for c in t["columns"]:
if c["name"] == cName:
foundColumn = True
c["ord_pos"] = int(ordP)
break
if not foundColumn:
self._log.error(
"Column '%s.%s' not found in db, present in ascii file.",
tName, cName)
raise MetaBException(MetaBException.COL_NOT_IN_TB, cName, tName)
# Check if we covered all columns
for t in theTable:
for c in theTable[t]["columns"]:
if "ord_pos" not in c:
self._log.error(
"Column '%s.%s' not found in ascii file, present in db.",
t, c)
raise MetaBException(MetaBException.COL_NOT_IN_FL, str(c), str(t))
# Get schema description and version, it is ok if it is missing
ret = conn.execute(
"SELECT version, descr FROM %s.ZZZ_Schema_Description" % dbName)
if ret.rowcount != 1:
self._log.error(
"Db '%s' does not contain schema version/description", dbName)
schemaVersion = "unknown"
schemaDescr = ""
else:
(schemaVersion, schemaDescr) = ret.first()
# This can be sometimes handy for debugging. (uncomment import too)
# pp = pprint.PrettyPrinter(indent=2)
# pp.pprint(theTable)
# Get host/port from engine
host = conn.engine.url.host
port = conn.egine.url.port
# Now, we will be talking to the metaserv database, so change
# connection as needed
if self._msMysqlAuthF != dbMysqlAuthF:
conn = getEngineFromFile(self._msMysqlAuthF).connect()
# get ownerId, this serves as validation that this is a valid owner name
ret = conn.execute("SELECT userId FROM User WHERE mysqlUserName = %s",
(owner,))
if ret.rowcount != 1:
self._log.error("Owner '%s' not found.", owner)
raise MetaBException(MetaBException.OWNER_NOT_FOUND, owner)
ownerId = ret.scalar()
# get projectId, this serves as validation that this is a valid project name
ret = conn.execute("SELECT projectId FROM Project WHERE projectName =%s",
(projectName,))
if ret.rowcount != 1:
self._log.error("Project '%s' not found.", owner)
raise MetaBException(MetaBException.PROJECT_NOT_FOUND, projectName)
projectId = ret.scalar()
# Finally, save things in the MetaServ database
cmd = "INSERT INTO Repo(url, projectId, repoType, lsstLevel, dataRelease, "
cmd += "version, shortName, description, ownerId, accessibility) "
cmd += "VALUES('/dummy',%s,'db',%s,%s,%s,%s,%s,%s,%s) "
opts = (projectId, level, dataRel, schemaVersion, dbName, schemaDescr,
ownerId, accessibility)
results = conn.execute(cmd, opts)
repoId = results.lastrowid
cmd = "INSERT INTO DbRepo(dbRepoId, dbName, connHost, connPort) "
cmd += "VALUES(%s,%s,%s,%s)"
conn.execute(cmd, (repoId, dbName, host, port))
for t in theTable:
cmd = 'INSERT INTO DDT_Table(dbRepoId, tableName, descr) '
cmd += 'VALUES(%s, %s, %s)'
results = conn.execute(cmd, (repoId, t,
theTable[t].get("description", "")))
tableId = results.lastrowid
isFirst = True
for c in theTable[t]["columns"]:
if isFirst:
cmd = 'INSERT INTO DDT_Column(columnName, tableId, '
cmd += 'ordinalPosition, descr, ucd, units) VALUES '
opts = ()
isFirst = False
else:
cmd += ', '
cmd += '(%s, %s, %s, %s, %s, %s)'
opts += (c["name"], tableId, c["ord_pos"],
c.get("description", ""), c.get("ucd", ""),
c.get("unit", ""))
conn.execute(cmd, opts)
def testConn_badSocketGoodHostPort(self):
# invalid socket, but good host/port
conn = getEngineFromFile(self.CREDFILE, host='127.0.0.1', query={"unix_socket": "/x/sock"}).connect()
conn.close()
def testConn_invalidPortNo(self):
engine = getEngineFromFile(self.CREDFILE, port=987654)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testConn_wrongPortNo(self):
engine = getEngineFromFile(self.CREDFILE, port=1579)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
(4, 'S13_v0.7'),
(5, 'jacek_db1x'),
(6, 'john_tmpDb5')''',
'''INSERT INTO DDT_Table(tableId, dbMetaId, tableName, descr) VALUES
(1, 5, 'Object', 'my object tablexx'),
(2, 5, 'Source', 'source table'),
(3, 6, 'DeepSource', 'deep src')''',
'''INSERT INTO DDT_Column(columnId, columnName, tableId, descr, ucd, units) VALUES
( 1, 'oId', 1, 'the object id, PK', 'meta.id;src', ''),
( 2, 'PS_ra', 1, 'right ascension', 'pos.eq.ra', 'degree'),
( 3, 'PS_decl',1, 'declination', 'pos.eq.dec', 'degree'),
( 4, 'flux', 1, 'measured flux', 'phot.count', 'nmgy'),
( 5, 'sId', 2, 'source id, PK', 'meta.id;src', ''),
( 6, 'oId', 2, 'ptr to object', 'meta.id;src', ''),
( 7, 'ra', 2, 'right ascension', 'pos.eq.ra', 'degree'),
( 8, 'decl', 2, 'declination', 'pos.eq.dec', 'degree'),
( 9, 'flux', 2, 'measured flux', 'phot.count', 'nmgy'),
(10, 'dsId', 3, 'deep src id, PK', 'meta.id;src', ''),
(11, 'flags', 3, 'my flags', '', '')''')
conn = getEngineFromFile("~/.lsst/dbAuth-metaServ.txt").connect()
utils.dropDb(conn, "metaServ_core", mustExist=False)
utils.createDb(conn, "metaServ_core")
utils.loadSqlScript(conn, "sql/global.sql")
utils.loadSqlScript(conn, "sql/dbRepo.sql")
utils.loadSqlScript(conn, "sql/fileRepo.sql")
utils.useDb(conn, "metaServ_core")
for q in queries:
conn.execute(q)
def testConn_invalidHost2(self):
engine = getEngineFromFile(self.CREDFILE, host="dummyHost", port=3036)
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
def testConn_invalidSocket(self):
# make sure retry is disabled, otherwise it wil try to reconnect
# (it will assume the server is down and socket valid).
engine = getEngineFromFile(self.CREDFILE, host="localhost",
query={"unix_socket": "/x/sock"})
self.assertRaises(sqlalchemy.exc.OperationalError, engine.connect)
'''INSERT INTO DDT_Table(tableId, dbMetaId, tableName, descr) VALUES
(1, 5, 'Object', 'my object tablexx'),
(2, 5, 'Source', 'source table'),
(3, 6, 'DeepSource', 'deep src')''',
'''INSERT INTO DDT_Column(columnId, columnName, tableId, descr, ucd, units) VALUES
( 1, 'oId', 1, 'the object id, PK', 'meta.id;src', ''),
( 2, 'PS_ra', 1, 'right ascension', 'pos.eq.ra', 'degree'),
( 3, 'PS_decl',1, 'declination', 'pos.eq.dec', 'degree'),
( 4, 'flux', 1, 'measured flux', 'phot.count', 'nmgy'),
( 5, 'sId', 2, 'source id, PK', 'meta.id;src', ''),
( 6, 'oId', 2, 'ptr to object', 'meta.id;src', ''),
( 7, 'ra', 2, 'right ascension', 'pos.eq.ra', 'degree'),
( 8, 'decl', 2, 'declination', 'pos.eq.dec', 'degree'),
( 9, 'flux', 2, 'measured flux', 'phot.count', 'nmgy'),
(10, 'dsId', 3, 'deep src id, PK', 'meta.id;src', ''),
(11, 'flags', 3, 'my flags', '', '')'''
)
conn = getEngineFromFile("~/.lsst/dbAuth-metaServ.txt").connect()
utils.dropDb(conn, "metaServ_core", mustExist=False)
utils.createDb(conn, "metaServ_core")
loadSqlScript(conn, "sql/global.sql")
loadSqlScript(conn, "sql/dbRepo.sql")
loadSqlScript(conn, "sql/fileRepo.sql")
utils.useDb("metaServ_core")
for q in queries:
conn.execute(q)
